简单聊一下mysql中的索引

索引

什么是索引？

索引的定义就是帮助存储引擎快速获取数据的一种数据结构，形象的说就是索引是数据的目录。

索引有几种类型？

按照存储引擎来分：B+树索引、Hash索引、Full-Text索引

按照字段特性来分：主键索引、唯一索引、普通索引、前缀索引

其中，三大存储引擎Innodb、Myisam、Memory都支持B+树索引，只有Memory支持hash索引，只有Memory不支持Full-Text索引。

为什么使用B+树作为Innodb的索引数据结构

对比其他数据结构的特点。B+树以其查询效率高、可以根据范围进行查询的特点胜出。

比如：

顺序表和链表，一旦数据量大的时候，查询效率极低。

二叉搜索树，如果数据单一递增，很容易形成一个链表。

跳表，跳表的插入和更新操作复杂度均为Ologn，但是查询操作最坏可能为On。并且伴随着数据量的增大，索引层数会越来越高，最终导致I/O性能越来越低。

如何使用索引？

索引可以加快查找数据的的效率，但是这并不代表可以无限制的创建索引，因为索引的创建和维护都会消耗大量的资源和空间，因此在合适的场景下使用索引才能最大化的提高项目寿命。

索引占用物理空间大

每次更新都会连带索引更新

每次插入和删除都会重新构建索引

什么时候适合使用索引呢？

1. 字段不能频繁的进行更新操作。由于需要维护B+树的有序性，每次更新都会导致索引连带更新，造成效率不升反降。
2. 多用于where语句查询条件。比如id、name等。如果是需要and进行拼接的sql语句，可以使用前缀索引。
3. 对于保证唯一性的字段可以使用唯一索引。

有什么优化索引的方法？

这里说一下几种常见优化索引的方法：

• 前缀索引优化；
• 覆盖索引优化；
• 主键索引最好是自增的；
• 防止索引失效；

前缀索引优化

对于一些字段比较大的值，可以采用前缀索引优化的办法，前提是只通过前缀的部分就能找到这些数据。

使用前缀索引是为了减小索引字段大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度。在一些大字符串的字段作为索引时，使用前缀索引可以帮助我们减小索引项的大小。

不过，前缀索引有一定的局限性，例如：

• order by 就无法使用前缀索引；
• 无法把前缀索引用作覆盖索引；

覆盖索引优化

如果一条查询语句，使用多个and进行连接，并且不会频繁的被更改，就可以将这些字段设置为前缀索引。

前缀匹配原则：

当你对 A,B,C 字段设置了前缀索引后

select * from t_table where A = ?

select * from t_table where A = ? AND B = ?

select * from t_table where A = ? AND B = ? AND C = ?

这三条语句在数据量大时都会走该索引。

select * from t_table where B = ?

select * from t_table where B = ? AND C = ?

这两条sql语句并不会走该覆盖索引。

主键索引最好是自增的

其实主键索引一般都是自增的，除非有些人用UUID、雪花算法来生成主键索引。但这会造成非常大的问题，比如：

1. 主键不递增，导致B+树需要维护自身的有序性，造成频繁的移动数据，还有可能造成页分裂。页分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。
2. 主键长度太大，数据量大，存储麻烦。

采用数据库自带的自增id可以完美的解决这两个问题。

如果我们使用自增主键，那么每次插入的新数据就会按顺序添加到当前索引节点的位置，不需要移动已有的数据，当页面写满，就会自动开辟一个新页面。因为每次插入一条新记录，都是追加操作，不需要重新移动数据，因此这种插入数据的方法效率非常高。

索引最好设置为 NOT NULL

这是为什么呢？

因为在mysql中，NULL和空值可不是一个概念，NULL所代表一种特殊的值，因此在查询时都需要单独进行判断一次。

并且NULL因为不是空值，是一种特殊的值，所以他在mysql中还会占有一个字节的空间。

如果你的索引为NULL，会导致在进行sql语句优化器优化的时候更加复杂，难以优化。

防止索引失效

这里简单说一下，发生索引失效的情况：

• 当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；
• 当我们在查询条件中对索引列做了计算、函数、类型转换操作，这些情况下都会造成索引失效；
• 联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。
• 在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

在编写完SQL语句之后，可以在其头部加上explan进行测试，查看是否索引是否失效。

拓展知识

Count(*)的性能如何？

先说结论：Count(*) = Count(1) > Count(id) > Count(name)

一旦在查询的时候看到 * 就会陷入先入为主的思想，查询全部数据怎么可能速度会快呢？我们接下来进行分析。

Count(*)的执行过程

count(*)其实就是count(0)，参数*会在内部转换为0。而0不是字段，不用读取字段值。而且0明显不是NULL值。所以只需要找到一行数据之后就将count变量进行+1。

返回最终的count数给客户端。

Count(1)的执行过程

Count(1)和Count(0)基本差不多，一个流程，因此他们两个的性能一样。

Count(id)的执行过程

因为id是一个字段，并且id是一个主键。当一个表中只有主键字段的时候，count就会按照聚簇索引进行遍历。如果一个表中不仅仅有主键，还有二级索引，那么就会根据二级索引来遍历。他们会遍历数据判断是否为NULL，如果不为NULL才会使count + 1。因为二级索引中叶子节点存储的是主键值，相比主键索引来说字段更少，I/O的效率也就更快。但还是比Count(0)多一个判断NULL的操作，也就是需要读取数据，自然比Count(*)效率低。

Count(name)的执行过程

因为name是一个字段，且name没有索引，因此会走全局搜索。并且还需要对数据进行判NULL操作。因此效率是最低的。

为什么需要循环遍历增加count数？

在Myisam存储引擎中会有一个mata专门存储数据数量row_count。他的count查找效率为O(1)。

但是在Innodb存储引擎中没有，这是因为Innodb支持事务。当高并发的情况出现时，很可能出现查询结果与实际不符的情况。因此遍历可以解决这个问题。

参考资料

索引常见面试题 | 小林coding (xiaolincoding.com)

MySQL 为什么采用 B+树作为索引？5年经验程序员回答让我悟了 - 掘金 (juejin.cn)