MySQL索引部分（高频面试题）

目录

1.什么是聚集索引和非聚集索引

2.日常工作中是怎么优化SQL的 

3.什么是聚集索引和非聚集索引

4.Mysql为什么使用B+Tree作为索引结构

5.请说一下Mysql索引的优点和缺点？

 6.索引什么时候失效？

1.什么是聚集索引和非聚集索引

简单来说，聚集索引就是基于主键创建的索引，除了主键索引以外的其他索引，称为非聚集索引，也叫做二级索引。

1. 由于在InnoDB引擎里面，一张表的数据对应的物理文件本身就是按照B+树来组织的一种索引结构，而聚集索引就是按照每张表的主键来构建一颗B+树，然后叶子节点里面存储了这个表的每一行数据记录。
2. 所以基于InnoDB这样的特性，聚集索引并不仅仅是一种索引类型，还代表着一种数据的存储方式。
3. 同时也意味着每个表里面必须要有一个主键，如果没有主键，InnoDB会默认选择或者添加一个隐藏列作为主键索引来存储这个表的数据行。一般情况是建议使用自增id作为主键，这样的话id本身具有连续性使得对应的数据也会按照顺序存储在磁盘上，写入性能和检索性能都很高。否则，如果使用uuid这种随机id，那么在频繁插入数据的时候，就会导致随机磁盘IO，从而导致性能较低。
4. 需要注意的是，InnoDB里面只能存在一个聚集索引，原因很简单，如果存在多个聚集索引，那么意味着这个表里面的数据存在多个副本，造成磁盘空间的浪费，以及数据维护的困难。
5. （如图）由于在InnoDB里面，主键索引表示的是一种数据存储结构，所以如果是基于非聚集索引来查询一条完整的记录，最终还是需要访问主键索引来检索。

---

2.日常工作中是怎么优化SQL的 

1. 加索引，增加索引是一种简单高效的手段，但是需要选择合适的列，同时避免导致索引失效的操作，比如like、函数等。
2. 避免返回不必要的数据列，减少返回的数据列可以增加查询的效率。
3. 根据查询分析器适当优化SQL的结构，比如是否走全表扫描、避免子查询等
4. 分库分表，在单表数据量较大或者并发连接数过高的情况下，通过这种方式可以有效提升查询效率
5. 读写分离，针对读多写少的场景，这样可以保证写操作的数据库承受更小的压力，也可以缓解独占锁和共享锁的竞争。

---

3.什么是聚集索引和非聚集索引

1. 简单来说，聚集索引就是基于主键创建的索引，除了主键索引以外的其他索引，称为非聚集索引，也叫做二级索引。
2. 由于在InnoDB引擎里面，一张表的数据对应的物理文件本身就是按照B+树来组织的一种索引结构，而聚集索引就是按照每张表的主键来构建一颗B+树，然后叶子节点里面存储了这个表的每一行数据记录。
3. 所以基于InnoDB这样的特性，聚集索引并不仅仅是一种索引类型，还代表着一种数据的存储方式。
4. 同时也意味着每个表里面必须要有一个主键，如果没有主键，InnoDB会默认选择或者添加一个隐藏列作为主键索引来存储这个表的数据行。一般情况是建议使用自增id作为主键，这样的话id本身具有连续性使得对应的数据也会按照顺序存储在磁盘上，写入性能和检索性能都很高。否则，如果使用uuid这种随机id，那么在频繁插入数据的时候，就会导致随机磁盘IO，从而导致性能较低。
5. 需要注意的是，InnoDB里面只能存在一个聚集索引，原因很简单，如果存在多个聚集索引，那么意味着这个表里面的数据存在多个副本，造成磁盘空间的浪费，以及数据维护的困难。
6. （如图）由于在InnoDB里面，主键索引表示的是一种数据存储结构，所以如果是基于非聚集索引来查询一条完整的记录，最终还是需要访问主键索引来检索。

---

4.Mysql为什么使用B+Tree作为索引结构

首先，常规的数据库存储引擎，一般都是采用B树或者B+树来实现索引的存储。

（如图）因为B树是一种多路平衡树，用这种存储结构来存储大量数据，它的整个高度会相比二叉树来说，会矮很多。

而对于数据库来说，所有的数据必然都是存储在磁盘上的，而磁盘IO的效率实际上是很低的，特别是在随机磁盘IO的情况下效率更低。

所以树的高度能够决定磁盘IO的次数，磁盘IO次数越少，对于性能的提升就越大，这也是为什么采用B树作为索引存储结构的原因。

 

（如图）但是在Mysql的InnoDB存储引擎里面，它用了一种增强的B树结构，也就是B+树来作为索引和数据的存储结构。

相比较于B树结构，B+树做了几个方面的优化。

1. B+树的所有数据都存储在叶子节点，非叶子节点只存储索引。
2. 叶子节点中的数据使用双向链表的方式进行关联。

 

使用B+树来实现索引的原因，我认为有几个方面。

1. B+树非叶子节点不存储数据，所以每一层能够存储的索引数量会增加，意味着B+树在层高相同的情况下存储的数据量要比B树要多，使得磁盘IO次数更少。
2. 在Mysql里面，范围查询是一个比较常用的操作，而B+树的所有存储在叶子节点的数据使用了双向链表来关联，所以在查询的时候只需查两个节点进行遍历就行，而B树需要获取所有节点，所以B+树在范围查询上效率更高。
3. 在数据检索方面，由于所有的数据都存储在叶子节点，所以B+树的IO次数会更加稳定一些。
4. 因为叶子节点存储所有数据，所以B+树的全局扫描能力更强一些，因为它只需要扫描叶子节点。但是B树需要遍历整个树。

另外，基于B+树这样一种结构，如果采用自增的整型数据作为主键，还能更好的避免增加数据的时候，带来叶子节点分裂导致的大量运算的问题。

 

---

5.请说一下Mysql索引的优点和缺点？

索引，是一种能够帮助Mysql高效从磁盘上检索数据的一种数据结构。

在Mysql中的InnoDB引擎中，采用了B+树的结构来实现索引和数据的存储

在我看来，Mysql里面的索引的优点有很多

1. 通过B+树的结构来存储数据，可以大大减少数据检索时的磁盘IO次数，从而提升数据查询的性能
2. B+树索引在进行范围查找的时候，只需要找到起始节点，然后基于叶子节点的链表结构往下读取即可，查询效率较高。
3. 通过唯一索引约束，可以保证数据表中每一行数据的唯一性

当然，索引的不合理使用，也会有带来很多的缺点。

1. 数据的增加、修改、删除，需要涉及到索引的维护，当数据量较大的情况下，索引的维护会带来较大的性能开销。
2. 一个表中允许存在一个聚簇索引和多个非聚簇索引，但是索引数不能创建太多，否则造成的索引维护成本过高。
3. 创建索引的时候，需要考虑到索引字段值的分散性，如果字段的重复数据过多，创建索引反而会带来性能降低。

---

 6.索引什么时候失效？

InnoDB引擎里面有两种索引类型，一种是主键索引、一种是普通索引。

InnoDB用了B+树的结构来存储索引数据。

当使用索引列进行数据查询的时候，最终会到主键索引树中查询对应的数据行进行返回。

理论上来说，使用索引列查询，就能很好的提升查询效率，但是不规范的使用

会导致索引失效，从而无法发挥索引本身的价值。

导致索引失效的情况有很多：

1. 在索引列上做运算，比如使用函数，Mysql在生成执行计划的时候，它是根据统计信息来判断是否要使用索引的。

而在索引列上加函数运算，导致Mysql无法识别索引列，也就不会再走索引了。

不过从Mysql8开始，增加了函数索引可以解决这个问题。

1. 在一个由多列构成的组合索引中，需要按照最左匹配法则，也就是从索引的最左列开始顺序检索，否则不会走索引。

在组合索引中，索引的存储结构是按照索引列的顺序来存储的，因此在sql中也需要按照这个顺序才能进行逐一匹配。

否则InnoDB无法识别索引导致索引失效。

1. 当索引列存在隐式转化的时候， 比如索引列是字符串类型，但是在sql查询中没有使用引号。

那么Mysql会自动进行类型转化，从而导致索引失效

1. 在索引列使用不等于号、not查询的时候，由于索引数据的检索效率非常低，因此Mysql引擎会判断不走索引。

1. 使用like通配符匹配后缀%xxx的时候，由于这种方式不符合索引的最左匹配原则，所以也不会走索引。

但是反过来，如果通配符匹配的是前缀xxx%，符合最左匹配，也会走索引。

1. 使用or连接查询的时候，or语句前后没有同时使用索引，那么索引会失效。只有or左右查询字段都是索引列的时候，才会生效。

除了这些场景以外，对于多表连接查询的场景中，连接顺序也会影响索引的使用。

不过最终是否走索引，我们可以使用explain命令来查看sql的执行计划，然后针对性的进行调优即可。