MySQL面试知识点追命连环问（二）事务、索引及SQL优化
目录
上次我们讨论了MySQL的运行流程及原理，字段设计，存储引擎和查询缓存。
这次我们继续来追命连环问关于事务，索引，SQL优化等相关的内容。准备好了吗？
-
事务
-
索引
-
SQL优化
-
常见问题
1. MySQL事务
面试官：你知道事务吗？
我：知道。事务（Transaction）是访问和更新数据库的程序执行单元；
事务中可能包含一个或多个sql语句，这些语句要么都执行，要么都不执行。
事务主要有四大特性。即ACID：原子性，一致性，隔离性和持久性。
原子性：不可分割的操作单元，事务中所有操作，要么全部成功；要么回滚到执行事务之前的状态。
一致性：在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。
隔离性：事务操作之间彼此独立和透明互不影响。如果一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。
持久性：事务一旦提交，其结果就是永久的。即便发生系统故障，也能恢复。
面试官：嗯四大特性说的没错，那你知道高并发场景下事务可能会出现的问题吗？
我：事务并发执行的话确实会产生一些问题。比如说：幻读，脏读，不可重复读。因为隔离性脏写是不会发生的。
脏读：一个事务读取到另一个未提交事务修改的数据。
对于session A来说，读到了session回滚之前的脏数据
不可重复读：多次读取的数据内容不一样。
幻读：前后多次读取，数据总量不一样。
面试管：那什么情况下会出现这些问题呢？
MySQL标准中定义了四种隔离级别，并规定了每种隔离级别下上述几个问题是否存在。
一般来说，隔离级别越低，系统开销越低，可支持的并发越高，但隔离性也越差。隔离级别与读问题的关系如下：
读未提交：脏读，不可重复读，幻读都有可能发生
读已提交：不可重复读，幻读可能发生
可重复读：幻读可能发生
可串行化：都不可能发生
在实际应用中，读未提交在并发时会导致很多问题，而性能相对于其他隔离级别提高却很有限，因此使用较少。
可串行化强制事务串行，并发效率很低，只有当对数据一致性要求极高且可以接受没有并发时使用，因此使用也较少。
因此在大多数数据库系统中，默认的隔离级别是读已提交(如Oracle)或可重复读。
MySQL事务默认的隔离级别是可重复读，而且MySQL可以解决了幻读的问题。
面试官：看来你对事务理解的还不错。那你知道MySQL的另一个重要特性索引吗？
2. MySQL索引
答：索引就是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。
在数据之外，数据库维护这些原来快速查找的索引也是要付出代价的。一是增加了数据库的存储，二是在插入和修改数据时要花费较多的时间（因为索引也要随之变动）。
面试官：那索引是怎样实现的呢？MyISAM和Innodb的实现方式一样吗？
答：不一样的。MyISAM和Innodb虽然都使用B+树作为索引结构，但索引的实现方式还是不一样的。
MyISAM的叶节点的data域存放的是数据记录的地址，而Innodb数据文件本身就是索引文件。
MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。
MyISAM索引
而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。
Innodb索引
因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有）。
如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键。
B+树是一种B树的变种，为有序数组链表+平衡多叉树。基本和B树类似，只有叶子节点存放数据，而且叶子节点之间通过指针相连。
面试官：那为什么索引用B+树呢，B+树有什么优点呢？
1、 B+树的磁盘读写代价更低：B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了。
2、由于B+树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，所以通常B+树用于数据库索引。
面试官：那什么是聚簇索引呢？
聚簇索引是一种数据存储方式，它实际上是在同一个结构中保存了B+树索引和数据行，InnoDB表是按照聚簇索引组织的。
InnoDB通过主键聚簇数据。他使用主键值的大小来进行记录和页的排序。叶子节点存储的是完整的用户记录。
注：聚簇索引不需要我们显示的创建，他是由InnoDB存储引擎自动为我们创建的。如果没有主键，其也会默认创建一个。
但聚簇索引只有在搜索条件为主键是才发挥作用，如果为其他的字段就不行，这个时候就需要普通索引了。
二级索引的叶子节点不再是完整的数据记录，而是字段和主键值。当需要这条记录的其他字段时仍然需要根据这个主键id去查询，这个步骤叫做回表。
聚簇索引表最大限度地提高了I/O密集型应用的性能，但它也有以下几个限制：
-
插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键。
-
更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新的。
-
二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。
面试官：索引有哪些类型？索引越多越好吗？
除了上面说的主键索引和普通索引，还有唯一索引，联合索引和全文索引。
唯一索引：该列具有唯一性的同时又是索引，不允许重复。
全文索引：主要用于文本的查询，它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。
联合索引：对多列值进行一个索引，其效率大于索引合并。需遵循前缀原则。
建索引是有开销的所以也不是越多越好，只要在需要的字段上建立索引。
第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
索引的使用需要注意以下几点：
1.最左前缀原则。一个联合索引（a,b,c）,如果有一个查询条件有a，有b，那么他则走索引，如果有一个查询条件没有a，那么他则不走索引。
2.使用唯一索引。具有多个重复值的列，其索引效果最差。
3.不要过度索引。每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能。在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长。
4、索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
5.一定要设置一个主键。前面聚簇索引说到如果不指定主键，InnoDB会自动为其指定主键，这个我们是看不见的。反正都要生成一个主键的，还不如我们设置，以后在某些搜索条件时还能用到主键的聚簇索引。
6.主键推荐用自增id，而不是uuid。上面的聚簇索引说到每页数据都是排序的，并且页之间也是排序的，如果是uuid，那么其肯定是随机的，其可能从中间插入，导致页的分裂，产生很多表碎片。如果是自增的，那么其有从小到大自增的，有顺序，那么在插入的时候就添加到当前索引的后续位置。当一页写满，就会自动开辟一个新的页。
索引禁忌：
-
不在低区分度的列上建⽴索引，例如“性别”
-
尽量避免%前导的查询，如like “%ab”
-
尽量避免负向查询，如not in/like
-
避免全表扫描和频繁的回表操作
面试官：看来你对索引掌握的很不错啊，那你平常遇到慢查询是怎么优化的呢？
3. SQL优化
SQL语句从客户端经由网络协议到查询缓存，如果没有命中缓存，再经过解析工作，得到准确的SQL然后再来到优化器。
首先，我们知道每一条SQL都有不同的执行方法，要不通过索引，要不通过全表扫描的方式。
影响SQL速度的主要在I/O成本和CPU成本的消耗上。
数据存储在硬盘上，我们想要进行某个操作需要将其加载到内存中，这个过程的时间被称为I/O成本。在内存对结果集进行排序的时间被称为CPU成本。
所以进行sql优化首先进行索引优化，让我们的sql语句尽量走索引而不是走全表扫描的方法。
在平常遇到慢查询时首先去分析慢查询日志，找出慢查询的sql。然后针对这些sql进行分析。常见慢查询主要有以下几种情况：
-
索引没起作用。字段没建立索引，或者是索引没有起作用。使用了like关键字或使用了多列索引的查询语句。
-
数据库结构不合理。合理的数据库结构不仅可以使数据库占用更小的磁盘，也可以让sql执行速度更快。一可以将字段很多的表拆解成多个表。二增加中间表。
-
分解关联查询。将大查询分成多个小查询。
-
优化limit分页。当偏移量非常大时会导致前面查询到的无用数据都要舍弃掉，如果表非常大，且筛选字段没有合适的索引，那么这样的代价是非常高的。如我们下一次的查询能从前一次查询结束后标记的位置开始查找，那将节省很多开销。
4.常见问题
问题一：嗯现在我们来看看具体问题，那你看这条语句会用到索引吗？
以下语句是否会应用索引：SELECT FROM users WHERE YEAR(adddate) < 2007;
答：不会，因为只要列涉及到运算，MySQL就不会使用索引。
问题二：那如果列值为NULL时，查询是否会用到索引？
在MySQL里NULL值的列也是走索引的。当然，如果计划对列进行索引，就要尽量避免把它设置为可空，MySQL难以优化引用了可空列的查询,它会使索引、索引统计和值更加复杂。
问题三：索引一定会提高速度吗？
通常，通过索引查询数据比全表扫描要快。但是我们也必须注意到它的代价。
索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时,索引本身也会被修改. 这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出4,5 次的磁盘I/O. 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢。使用索引查询不一定能提高查询性能。
问题四：如何查询第n高的工资？
SELECT DISTINCT(salary) from employee ORDER BY salary DESC LIMIT n-1,1
问题五：一个6亿的表a，一个3亿的表b，通过外间tid关联，你如何最快的查询出满足条件的第50000到第50200中的这200条数据记录。
1、如果A表TID是自增长,并且是连续的,B表的ID为索引
select * from a,b where a.tid = b.id and a.tid>500000 limit 200;
2、如果A表的TID不是连续的,那么就需要使用覆盖索引。TID要么是主键,要么是辅助索引,B表ID也需要有索引。
select * from b , (select tid from a limit 50000,200) a where b.id = a .tid;
好啦，今天的追命连环问就到这里了，下次继续，如对文章有疑惑或补充的地方欢迎留言交流(●'◡'●)。原创不易，如果对你有帮助的话欢迎点赞！
相关推荐阅读
本专栏主要整理记录后端程序员面试过程中常见的MySQL知识点。包括MySQL索引，MySQL引擎，MySQL语句，MySQL日志及MySQL锁等。
拼多多集团-PDD公司氛围 518人发布
查看40道真题和解析