MySQLDBA面试常见问题汇总：索引、事务、存储引擎、优化等等1.索引MySQL的索引其实前面已经介绍过很多次了。这里主要介绍一些常用的面试题。1、什么是指数？索引是一种什么样的数据结构？索引是一种数据结构，可以帮助我们快速查找数据。索引的数据结构与具体存储引擎的实现有关。MySQL中使用较多的索引有Hash索引、B+树索引等，而我们常用的InnoDB存储引擎默认的索引实现是：B+树索引。2、Hash索引和B+树有什么区别或者优缺点？首先我们要知道哈希索引和B+树索引的底层实现原理：哈希索引的底层是哈希表。查找的时候，调用一次哈希函数就可以得到对应的键值，然后回表查询得到实际的数据。B+树的底层是一颗多路平衡搜索树。对于每一次查询，都是从根节点开始，只有找到叶节点才能得到查询到的key值，然后根据查询判断是否需要回表查询数据。那么可以看出它们有以下区别：hash索引对于等值查询速度较快（一般情况下），但是不能进行范围查询，因为hash索引经过hash函数索引后，索引的顺序不能与原始订单进行比较。保持一致，不能支持范围查询。B+树的所有节点都遵循（左节点小于父节点，右节点大于父节点，多叉树类似），自然支持范围。哈希索引不支持使用索引进行排序。原理同上。hash索引不支持模糊查询，不支持多列索引的最左前缀匹配。原理也是由于哈希函数的不可预测性。AAAA和AAAAB指数没有相关性。哈希索引无法避免随时回表查询数据，而B+树只有在满足一定条件（聚集索引、覆盖索引等）时，才能通过索引完成查询。虽然哈希索引在等效查询中速度更快，但不稳定。性能是不可预测的。当一个键值存在大量重复时，就会发生哈希冲突，此时效率可能会极差。B+树的查询效率比较稳定，所有的查询都是从根节点到叶子节点，树的高度较低。因此，在大多数情况下，直接选择B+树索引可以获得稳定和更好的查询速度。不需要使用哈希索引。3、当B+树满足聚簇索引和覆盖索引时，不需要回表查询数据，那么什么是聚合聚簇索引呢？在B+树的索引中，叶子节点可以存放当前键值，也可以存放当前键值和整行的数据。这是聚簇索引和非聚簇索引。在InnoDB中，只有主键索引才是聚集索引。如果没有主键，则选择一个唯一键来构建聚集索引。如果没有唯一键，则隐式生成一个键来构建聚簇索引。当查询使用聚簇索引时，在相应的Leaf节点中可以获得整行数据，因此无需再次查询回表。4、非聚集索引一定会回表查询吗？不一定，这个涉及到查询语句需要的字段是否都命中了索引，如果都命中了索引，那么就不用回表查询了。举个简单的例子，假设在employee表的age上建立了一个索引，那么当执行selectagefromemployeewhereage<20的查询时，索引的叶子节点上，已经包含了年龄信息,不会再进行表查询。5、建立索引需要考虑哪些因素？建立索引时，一般要考虑字段的使用频率，往往作为条件查询的字段比较合适。如果需要构建联合索引，还需要考虑联合索引中的顺序。此外，还应考虑其他方面，例如比如防止桌子压力过大。这些都与实际的表结构和查询方式有关。6.什么是联合指数？为什么需要注意联合索引中的顺序呢？MySQL可以使用多个字段同时创建一个索引，称为联合索引。在联合索引中，如果要命中索引，需要按照建索引时字段的顺序，一个一个使用，否则无法命中索引。具体原因是：MySQL在使用索引时需要索引有序。假设现在建立如果选择“姓名、年龄、学校”联合索引，则索引排序为：先按姓名排序，如果姓名相同，再按年龄排序，如果年龄的值相同也相等，然后按学校排序。查询时，此时索引只是按照name严格排序，所以必须先使用name字段进行等值查询，然后对于匹配到的列，按照age字段严格排序，age此时可以使用字段进行索引查找，，等等。因此，在构建联合索引时，要注意索引列的顺序。一般情况下，将查询需求频繁或字段选择性高的列放在前面。此外，您可以根据特殊情况或表结构进行单独查询Adjustment.7。简述mysql中索引、主键、唯一索引、联合索引的区别，以及对数据库性能的影响（从读写两个方面）。索引是一个特殊的文件（InnoDB数据表上的索引是表空间的一个组成部分），它包含指向数据表中所有记录的引用指针。普通索引（由关键字KEY或INDEX定义的索引）的唯一任务是加快对数据的访问。普通索引允许索引数据列包含重复值。如果可以确定一个数据列只会包含彼此不同的值，那么在为这个数据列创建索引时，就应该使用关键字UNIQUE将其定义为唯一索引。也就是说，唯一索引可以保证数据记录的唯一性。主键是一种特殊的唯一索引。一张表中只能定义一个主键索引。主键用于唯一标识一条记录，使用关键字PRIMARYKEY创建。一个索引可以覆盖多个数据列，比如像INDEX(columnA,columnB)这样的索引，就是联合索引。建立索引可以大大提高数据的查询速度，但是会降低插入、删除、更新表的速度，因为在进行这些写操作的时候，还必须对索引文件进行操作。二、事务1、什么是ACID？A=Atomicity：原子性，上面说了，要么全部成功，要么全部失败。不可能只执行一部分操作。C=Consistency：系统（数据库）总是从一个一致的状态转移到另一个一致的状态，不会有中间状态。I=Isolation：隔离：一般来说：一个事务在完全提交之前对其他事务是不可见的。注意，前面一般加红色表示有例外。D=Durability：持久性，一个事务一旦提交，就会一直这样，即使系统崩溃了，也不会影响这个事务的结果。2.有多个交易同时进行会怎样？多个事务并发执行一般会导致以下问题：脏读：A事务读取了B事务未提交的内容，B事务后面回滚。Non-repeatableread::当设置A事务时，只能读取B事务提交的部分，这会造成A事务中的两次查询，结果其实是不一样的，因为B事务在这期间已经提交了。幻读：：A事务读取了一个范围的内容，而事务B在这期间插入了一段数据，造成“幻觉”。3、如何解决这些问题？你了解MySQL的事务隔离级别吗？MySQL的四种隔离级别如下：(1)未提交读(READUNCOMMITTED)这就是上面说的异常。在这个隔离级别下，其他事务可以看到事务中没有提交的部分。所以会造成脏读的问题（读取其他事务未提交的部分，然后事务被回滚）。这种性能水平并没有足够的优势，反而存在很多问题，所以很少使用。(2)READCOMMITTED(READCOMMITTED)其他事务只能读取本事务中已经提交的部分。这种隔离级别存在不可重复读的问题。同一个事务中两次读得到的结果是不同的，因为另一个事务修改了数据。(3)REPEATABLEREAD（可重复读）可重复读隔离级别解决了上面不可重复读的问题（看名字），但是还有一个新的问题，就是幻读。当读取id>10的数据行时，所有行都会被加相应的Read锁。这时有一个异常，就是插入了一个id=11的新事务。因为是新插入的，所以不会触发上面锁的排除。然后进行下一次查询的事务。会发现有一条id=11的数据，但是上次查询操作没有获取到，再插入会出现主键冲突问题。(4)SERIALIZABLE（可序列化）这是最高的隔离级别，可以解决上面提到的所有问题，因为它强制所有操作串行执行，会导致并发性能快速下降，所以不是很常用用过的。4.Innodb默认使用哪种隔离级别？InnoDB默认使用可重复读隔离级别。5、MySQL有哪些锁？像上面这样加锁不会有点影响并发效率吗？从锁的种类来看，有共享锁和排它锁。共享锁：也称为读锁。当用户想要读取数据时，他们给数据加上共享锁。可以同时添加共享锁。独占锁：也叫写锁。当用户要写数据时，给数据加一个排他锁。只能加一个排他锁，与其他排他锁、共享锁互斥。MyISAM支持表锁，InnoDB支持表锁。锁和行锁，默认是行锁表级锁：开销小，加锁速度快，不会死锁。加锁粒度大，锁冲突概率最高，并发量最低。行级锁：高开销、慢锁和死锁。锁强度小，锁冲突概率小，并发度最高。6.锁优化策略读写分离，分段加锁，减少锁持有时间。多个线程尝试以相同的顺序获取资源。锁的粒度不能太大。求精，不然可能线程的锁和释放太多，效率不如一次加大锁。三、存储引擎1、MySQL支持哪些存储引擎？MySQL支持多种存储引擎，如InnoDB、MyISAM、Memory、Archive等，大多数情况下直接选择使用InnoDB引擎是最合适的，InnoDBMySQL默认的存储引擎也是如此。2、InnoDB和MyISAM有什么区别？InnoDB支持事务，而MyISAM不支持事务。InnoDB支持行级锁，而MyISAM支持表级锁。InnoDB支持MVCC，而MyISAM不支持InnoDB。MyISAM不支持InnoDB不支持全文索引，但是MyISAM支持。四、优化1、超大分页如何处理？超大分页一般从两个方向解决。在数据库层面，这是我们主要关注的（虽然效果没那么大），类似于select*fromtablewhereage>20limit1000000,10这种查询其实还有优化的空间。这条语句需要加载1000000条数据，然后基本上全部丢弃。当然只取10是比较慢的，这时候我们可以修改为select*fromtablewhereidin(selectidfromtablewhereage>20limit1000000,10)。这样虽然也是加载了百万级数据，但是由于索引覆盖，所有要查询的字段都在索引中，所以速度会很快。同时，如果id是连续的，我们也可以select*fromtablewhereid>1000000limit10，效率也不错，优化的可能性有很多，但是核心思想是一样的，就是要减少负载数据。从需求的角度，减少这个请求....主要是不做类似的需求（百万页后直接跳转到特定页面。只允许逐页查看或者按照给定的路线，这可预测、可缓存），防止身份泄露和人的持续恶意攻击。解决超大分页的问题，其实主要是靠缓存，可以预见性的提前找到内容，缓存到redis等k-v数据库中，直接返回。2、您是否在为生产环境中耗时的SQL而烦恼？统计太慢查询？您如何优化慢速查询？在业务系统中，除了使用主键进行查询外，其他的都会在测试库上测试其耗时。慢查询的统计主要是运维做的，业务在慢查询中反馈给我们。优化慢查询，首先要了解慢的原因是什么？查询条件没有命中索引？它是否加载了不必要的数据列？还是数据量太大？所以优化也是针对这三个方向，首先分析语句，看是否加载了额外的数据，可能是查询了多余的行并丢弃，或者加载了很多结果中不需要的列，分析并重写语句。分析语句的执行计划，然后获取索引使用情况，然后修改语句或索引，使语句尽可能命中索引。如果无法对语句进行优化，考虑是不是表的数据量太大，如果是的话，可以水平或者垂直分表。3.MySQL数据当数据量增加时，如何优化数据库以供发布系统存储？设计好数据库结构，允许一定的数据冗余，尽量避免join查询，提高效率。选择合适的表字段数据类型和存储引擎，并使用合适的添加索引。mysql库主从读写分离。查找规则，分表，减少单表数据量，提高查询速度。添加缓存机制，如memcached、apc等，对于不经常变化的页面，生成静态页面。编写高效的SQL。例如，SELECT*FROMTABEL更改为SELECTfield_1,field_2,field_3FROMTABLE。四、MySQL在实践中如何优化最好按照以下顺序进行优化：SQL语句和索引优化数据库表结构优化系统配置优化硬件优化五、其他1、MySQL中varchar和char有什么区别。Char是一个固定长度的字段。如果申请char(10)空间，不管实际存了多少内容。这个字段占用10个字符，varchar是可变Long，也就是说申请的只是最大长度，占用的空间是实际字符长度+1，最后一个字符存储用多少空间。从检索效率上来说，char>varchar，所以在使用中，如果确定某个字段的值的长度可以使用char，否则尽量使用varchar。比如存储用户的MD5加密密码，就应该使用char。2.varchar(10)和int(10)是什么意思？varchar中的10表示申请的空间长度，也是可以存储数据的最大长度，而int的10只是表示显示的长度，不足10位补0。即比如说，int(1)和int(10)可以存储数字的大小和占用的空间是一样的，只是在显示的时候是按照长度来显示的。3、MySQL的binlog有几种输入格式？有什么区别？有statement、row和mixed三种格式。在语句模式下，记录单元是一条语句。即每条sql的影响都会被记录下来。由于sql的执行是有上下文的，所以在保存的时候需要保存相关的信息，而一些用到函数之类的语句是无法记录Copy的。行级别，记录单位是每一行的变化，基本可以记录下来。但是由于操作较多，会改变大量的行（比如altertable），所以这种文件方式保存的信息太多，日志量太大。混合。一个折衷方案，普通操作使用语句记录，不能使用语句时使用行。此外，新版MySQL还对行级别做了一些优化。当表结构发生变化时，将记录语句而不是逐行记录。4.你了解表的水平分区和垂直分区吗？水平表分区是逐行表分区。假设我们有一张用户表，主键是自增ID，同时是用户的ID。数据量很大，1亿条如果有多个条目，此时在一个表中查询效果并不理想。我们可以按照主键ID来分表，是按尾号分还是按ID的区间分。假设按照tailNumber0-99分成100张表，那么每张表的数据只有100w。这时候查询效率无疑是可以满足要求的。垂直表按列分成表。假设我们现在有一个文章表。包含字段id-abstract-content。系统中的显示形式是刷新一个列表，只包含标题和摘要。当用户点击文章进入详情时，需要输入文字内容。这时候，如果数据量很大，内容很大，而且是不经常使用的列，会减慢原表的查询速度。我们可以把上表分成两份。id-摘要，id-内容。当用户点击详情时，将检索主键一次性内容就够了。增加的存储容量只是一个很小的主键字段。成本很小。当然，分表其实和业务的关联度很高，分表前一定要做好research和benchmark。不要按照自己的猜测盲目操作。5.三种范式第一范式：每一列不能再拆分。第二种范式：非主键列完全依赖于主键，不能成为主键的一部分。第三范式：非主键列只依赖主键，不依赖其他非主键。在设计数据库结构时，尽量遵守三种范式。如果您不遵守，则必须有充分的理由。例如，性能。事实上，我们经常在数据库设计的性能上妥协
