索引
什么是索引?
索引的定义就是帮助存储引擎快速获取数据的一种数据结构,形象的说就是索引是数据的目录。
索引有几种类型?
按照存储引擎来分:B+树索引、Hash索引、Full-Text索引
按照字段特性来分:主键索引、唯一索引、普通索引、前缀索引
其中,三大存储引擎Innodb、Myisam、Memory
都支持B+树索引,只有Memory
支持hash索引,只有Memory
不支持Full-Text索引。
为什么使用B+树作为Innodb的索引数据结构
对比其他数据结构的特点。B+树以其查询效率高、可以根据范围进行查询的特点胜出。
比如:
顺序表和链表,一旦数据量大的时候,查询效率极低。
二叉搜索树,如果数据单一递增,很容易形成一个链表。
跳表,跳表的插入和更新操作复杂度均为Ologn,但是查询操作最坏可能为On。并且伴随着数据量的增大,索引层数会越来越高,最终导致I/O性能越来越低。
如何使用索引?
索引可以加快查找数据的的效率,但是这并不代表可以无限制的创建索引,因为索引的创建和维护都会消耗大量的资源和空间,因此在合适的场景下使用索引才能最大化的提高项目寿命。
索引占用物理空间大
每次更新都会连带索引更新
每次插入和删除都会重新构建索引
什么时候适合使用索引呢?
- 字段不能频繁的进行更新操作。由于需要维护B+树的有序性,每次更新都会导致索引连带更新,造成效率不升反降。
- 多用于where语句查询条件。比如id、name等。如果是需要and进行拼接的sql语句,可以使用前缀索引。
- 对于保证唯一性的字段可以使用唯一索引。
有什么优化索引的方法?
这里说一下几种常见优化索引的方法:
- 前缀索引优化;
- 覆盖索引优化;
- 主键索引最好是自增的;
- 防止索引失效;
前缀索引优化
对于一些字段比较大的值,可以采用前缀索引优化的办法,前提是只通过前缀的部分就能找到这些数据。
使用前缀索引是为了减小索引字段大小,可以增加一个索引页中存储的索引值,有效提高索引的查询速度。在一些大字符串的字段作为索引时,使用前缀索引可以帮助我们减小索引项的大小。
不过,前缀索引有一定的局限性,例如:
- 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、雪花算法来生成主键索引。但这会造成非常大的问题,比如:
- 主键不递增,导致B+树需要维护自身的有序性,造成频繁的移动数据,还有可能造成页分裂。页分裂还有可能会造成大量的内存碎片,导致索引结构不紧凑,从而影响查询效率。
- 主键长度太大,数据量大,存储麻烦。
采用数据库自带的自增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)