（3）MySQL索引优化

1 为什么要建立索引?

索引是对数据库表的一列或者多列的值进行排序的一种数据结构，使用索引可以高效快速的定位到数据表中的特定信息。在非常大的表中进行查询时，没有索引会遍历整张表，速度会非常慢。索引的原理通过b+树的结构把无序的数据变成有序的查询，具备如下特点：
（1）索引是天然有序的，具备B+树的快速检索。
（2）通过创建唯一索引，保证数据库表中每一行数据的唯一性。
（3）通过索引列对数据进行排序, 当进行分组(groupby)和排序(orderby)子句时, 可以显著的减少分组和排序时间。

2 为什么B+树比B树更适合实现数据库索引？

（1）B+ 树叶子结点之间用链表有序连接，所以扫描全部数据只需扫描一遍叶子结点，利于扫库和范围查询；
（1）B 树由于非叶子结点也存数据，所以只能通过中序遍历按序来扫。也就是说，对于范围查询和有序遍历而言，B+ 树的效率更高。
（1）B+ 树更相比 B 树减少了 I/O 读写的次数。由于索引文件很大因此索引文件存储在磁盘上，B+ 树的非叶子结点只存关键字不存数据，因而单个页可以存储更多的关键字，即一次性读入内存的需要查找的关键字也就越多，磁盘的随机 I/O 读取次数相对就减少了。
（1）B+树的查询效率更加稳定，任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

3 索引有什么缺点?

（1）创建和维护索引需要耗费时间, 数据量增多, 耗费的时间也会增加。
（2）索引提高了查询速度, 却会降低更新表的速度。 在对表中数据进行增删改时, 索引也要动态的维护, 降低了SQL执行效率。
（3）索引使用物理文件存储, 会耗费一定的磁盘空间。

4 什么情况下需要建索引？

（1）主键自动创建唯一索引
（2）较频繁的作为查询条件的字段
（3）查询中排序的字段，查询中统计或者分组的字段

5 什么情况下不适用建立索引？

（1）表记录太少的字段
（2）经常增删改的字段
（3）唯一性太差的字段，不适合单独创建索引，比如性别，民族，政治面貌
（4）定义为text、image和bit的数据类型的列不要建立索引

6 索引主要有哪几种分类？

普通索引: 是最基本的索引，它没有任何限制
唯一索引: 索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一
主键索引: 是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。
组合索引: 一个索引包含多个列，实际开发中推荐使用组合索引。
全文索引: 全文搜索的索引。FULLTEXT 用于搜索很长一篇文章的时候，效果最好。只能用于InnoDB或MyISAM表，只能为CHAR、VARCHAR、TEXT列创建。

主键必唯一，但是唯一索引不一定是主键。一张表上只能有一个主键，但是可以有一个或多个唯一索引。

7 什么是最左匹配原则？

建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。如果创建了(age, name)的组合索引，那么其实相当于创建了(age)、(age, name)两个索引，这被称为最佳左前缀特性。因此我们在创建组合索引时应该将最常用作限制条件的列放在最左边，依次递减。

8 说说索引的设计原则？

（1）索引列的区分度越高，索引的效果越好。比如使用性别这种区分度很低的列作为索引，效果就会很差。
（2）尽量使用短索引，对于较长的字符串进行索引时应该指定一个较短的前缀长度，因为较小的索引涉及到的磁盘I/O较少，查询速度更快。
（3）索引不是越多越好，每个索引都需要额外的物理空间，维护也需要花费时间。
（4）利用最左前缀原则。

9 索引失效场景有哪些？

（1）组合索引未使用最左前缀，例如组合索引（age，name），where name='张三'不会使用索引;
or会使索引失效。如果查询字段相同，也可以使用索引。例如where age=20 or age=30（索引生效），where age=20 or name=‘张三’（这里就算你age和name都单独建索引，还是一样失效）;
（2）如果列类型是字符串，不使用引号。例如where name=张三(索引失效），改成where name=‘张三’(索引有效);
（3）like未使用最左前缀，where A like '%China';
（4）在索引列上做任何操作计算、函数，会导致索引失效而转向全表扫描;
（5）如果mysql估计使用全表扫描要比使用索引快,则不使用索引;

10 MySQL聚簇和非聚簇索引有什么区别

聚簇索引就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分，每张表只能拥有一个聚簇索引。
（1）如果表设置了主键，则主键就是聚簇索引
（2）如果表没有主键，则会默认第一个NOT NULL，且唯一（UNIQUE）的列作为聚簇索引
（3）以上都没有，则会默认创建一个隐藏的row_id作为聚簇索引

聚集索引的叶子节点就是整张表的行记录。InnoDB 主键使用的是聚簇索引。聚集索引要比非聚集索引查询效率高很多。

普通索引也叫二级索引，除聚簇索引外的索引，即非聚簇索引。InnoDB的普通索引叶子节点存储的是主键（聚簇索引）的值，而MyISAM的普通索引存储的是记录指针。

11 什么是回表查询？

非聚簇索引查询就是回表查询。先通过普通索引的值定位聚簇索引值，再通过聚簇索引的值定位行记录数据，需要扫描两次索引B+树，它的性能较扫一遍索引树更低。

12 什么叫索引覆盖？

如果一个查询是先走辅助索引的，那么通过这个辅助索引就直接获取到我们想要的全部数据了，不需要进行回表，这个过程就叫做索引覆盖；

13 为什么推荐使用自增主键作为索引？

主键最好不要使用uuid，因为uuid的值太过离散，不适合排序且可能出现新增加记录的uuid，会插入在索引树中间的位置，出现页分裂（比如之前的索引已经紧凑的排列在一起了，你此时需要在已经紧凑排列好的数据中插入数据就会导致前面已经排好序的索引出现松动和重构排序，但是使用自增id就不会出现这种情况了），导致索引树调整复杂度变大，消耗更多的时间和资源。但是使用自增主键就可以避免出现页分裂，因为自增主键后面的主键值是要比前面的大， 那后来的数据直接放在后面就行；

聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，它会不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但如果是自增的id，它只需要一 页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。

14 谈一下你对MySQL索引的理解？

先说说索引的b+树结构，为什么使用b+树，再说一下聚簇索引，回表和索引覆盖，最后再谈索引失效。

参考
https://www.cnblogs.com/sha-Pao-Zi/p/16314941.html
https://zhuanlan.zhihu.com/p/453658511