vlambda博客
学习文章列表

Mysql索引扫盲总结

什么是索引? 索引为什么查询快,索引的数据结构是什么? 聚簇索引/非聚簇索引区别? 什么是覆盖索引? 唯一索引/普通索引? 单列索引/联合索引区别? Full-index全文索引? 什么是下推索引? 什么是最左匹配,查询回表? 哪些字段适合建索引? 为什么一般主键索引最好是自增长的, 尽量短的数值类型? 为什么有些SQL不走索引? 索引的最佳实践?


索引为什么快

索引的本质是空间换时间。
  • +bonus: 加快检索速度,加快多表连接
  • -price: 额外空间开销,维护索引的额外时间开销
所以我们通过索引这个 缓存 来提高数据查询的效率。
假如我们自己设计数据库索引的话,我们会选取什么样的数据结构呢?下面我们来分析下各种查询常见的数据结构的性格,看看选谁是最合适的人选。

数据结构比较

  • 有序数组:等值查询和范围查询场景中的性能就都非常优秀。 特定值查询 用二分法就可以快速得到,这个时间复杂度是 O(log(N))。类似between[x, y]的 范围查询 也比较快,先用值查询二分法找到x, 然后向后遍历,知道找到y。但是他最大的问题是插入或者删除一个新数据,这个新数据后面的整个数组都需要挪动,复杂度是O(N)。
  • HashMap:虽然可以快速定位,值查询的时间复杂度是O(1), 但是Hashmap没有顺序,进行范围查询的话复杂度高是O(N)。
  • 二叉树查找树BST:二叉树的高度不均匀,不能自平衡,查找效率跟数据量有关(树的高度),在极端情况下(插入数据本身就是有序的)这棵树就退化成链表了,查询实际复杂度是O(N)
  • 红黑树:是平衡的BST,性能稳定在O(logN), 但因为是二叉树,树的高度随着数据量增加而增加,并且需要再平衡。适合数据都在内存的情况,比如Java里的HashMap。但是在硬盘寻址的场景下IO成本会比较高。
  • B-Tree:相比二叉树来说是一种多路平衡查询树,但是B树不管叶子节点还是非叶子节点,都会保存数据,这样导致在非叶子节点中能保存的指针数量变少(有些资料也称为扇出),指针少的情况下要保存大量数据,只能增加树的高度,导致IO操作变多,查询性能变低;
  • B+Tree: 从物理存储结构上说是N叉树,B-Tree和B+Tree都以页(4K)来划分节点的大小,但是由于B+Tree的中间节点(非叶子节点)不存储数据,存的是索引信息,索引包含Key和Point指针。因此B+Tree能够在同样大小的节点中,存储更多的key,提高查找效率。
每一个索引在 InnoDB 里面对应一棵 B+ 树。以 InnoDB 的一个整数字段索引为例,这个 N 差不多是 1200。这棵树高是 4 的时候,就可以存 1200 ^(4-1) 个值,这已经 17 亿了。考虑到树根的数据块总是在内存中的,一个 10 亿行的表上一个整数字段的索引,查找一个值最多只需要访问 3 次磁盘。

聚簇索引/非聚簇索引

区别主要看叶子节点存了什么数据:
在 InnoDB 里,索引B+ Tree的叶子节点存储了整行数据的是主键索引,也被称之为聚簇索引。
而索引B+ Tree的叶子节点存储了主键的值的是非主键索引,也被称之为非聚簇索引。
聚簇索引查询相对会更快一些,因为主键索引树的叶子节点直接就是我们要查询的整行数据了。而非主键索引的叶子节点是主键的值,查到主键的值以后,还需要再通过主键的值再进行一次查询(这个过程叫做回表, 也就是查了2个索引树)。

覆盖索引

覆盖索引(covering index)指一个查询语句的执行只用从索引中就能够取得,不必从数据表中读取。覆盖索引不是索引树,是一个结果。当一条查询语句符合覆盖索引条件时,MySQL只需要通过索引就可以返回查询所需要的数据,这样避免了查到索引后再返回表操作,减少I/O提高效率。
例如表T中有一个普通索引 idx_key(key),那么:
  
    
    
  
-- 索引覆盖了
select id from T where key = 'test';

-- 索引没覆盖,需要回表
select * from T where key = 'test';
问题,为什么第一个SQL索引覆盖了?  非聚簇索引的叶子节点存的是id。

唯一索引/普通索引

唯一索引和普通索引在查询和更新的时候区别:
  • 唯一索引找到满足的第一条记录会立马返回,通知检索(因为唯一性的保证)。但是这个区别并没有很大的性能区别,因为Innodb是按照页(默认16KB)读写的,读数据的时候是从B+树的根节点开始搜索,搜索的时候将整个页从硬盘加载到内存。
  • 唯一索引在插入的时候会多做些判断,想要做这个判断就必须先把数据页读入内存。但是普通索引不需要做这个判断,就可以把需要更新的数据做判断:如果数据在内存则直接更新;如果不在也不加载内存,而是先写入change buffer,等下次查询的时候再执行change buffer。这样普通索引会相对性能好一些。但是注意:如果业务场景是写入后立马有查询,其实还是会立马需要把数据页加载到内存,这样的情况下其实并不能带来优化IO的操作。

Full-index全文索引

Mysql 5.6 引入了全文索引Full text index,但是只能适用于分词的情况,如果是匹配字符串的一部分就不适用了。
MySQL支持三种模式的全文检索模式:自然语言模式(IN NATURAL LANGUAGE MODE),即通过MATCH AGAINST 传递某个特定的字符串来进行检索。布尔模式(IN BOOLEAN MODE),可以为检索的字符串增加操作符,例如“+”表示必须包含,“-”表示不包含,“*”表示通配符(这种情况, 即使传递的字符串较小或出现在停词中,也不会被过滤掉),其他还有很多特殊的布尔操作符,可以通过如下参数控制:查询扩展模式(WITH QUERY EXPANSION), 这种模式是自然语言模式下的一个变种,会执行两次检索,第一次使用给定的短语进行检索,第二次是结合第一次相关性比较高的行进行检索。


单列索引/联合索引

对于一个表里的多个列,比如是有些列高频查询,有些列低频查询。如果为每一个低频的列单独建立索引感觉有些浪费,如果不建立索引又只能走全表扫描。所以我们经常用联合索引来解决这个问题,联合索引如 idx_key1_key2_key3(key1,key2,key3) ,相当于创建了 (key1) (key1,key2) (key1,key2,key3) 三个索引,那么在建立联合索引的时候,如何安排索引内的字段顺序?
  • 如果通过调整顺序,可以少维护一个索引,那么这个顺序往往就是需要优先考虑采用
  • 按照字段在查询条件中出现的频度建立索引
我们考虑key1 是最常用的列放最前面,key2和key3不常用。
上面这种建立一个联合索引就实际上包含了3个索引的特性就是 最左匹配原则 。这个最左匹配可以是联合索引的最左 N 个字段,也可以是字符串索引的最左 M 个字符。
总结起来
  1. 索引的匹配规则是左匹配的
  2. 只有复合索引的第一个字段出现在查询条件中,该索引才可能被使用
  3. 有了(A,B,C),就等于同时拥有了(A),(A,B)和 (A,B,C) 三个索引
  4. 只要索引内,开始用范围查询,后面的索引就失效了。**这里注意:**IN 在 where 中,也属于准确查询,不会使后面索引失效。


什么是下推索引?

在MySQL 5.6中,引入了Index Condition Pushdown Optimization 优化。本质是针对那些需要回表查找的部分如果索引里已经包含了该列,那么先在索引里做过滤判断。
以用户表的联合索引(name, age)为例。如果现在有一个需求:检索出表中“名字第一个字是张,而且年龄是 10 岁的所有男孩”。那么,SQL 语句是这么写的:
  
    
    
  
mysql> select * from tuser where name like '张 %' and age=10 and ismale=1;
我们已经知道了前缀索引规则,所以这个语句在搜索索引树的时候,只能用 “张”,找到第一个满足条件的记录 ID3。当然,这还不错,总比全表扫描要好。然后呢?当然是判断其他条件是否满足。在 MySQL 5.6 之前,只能从 ID3 开始一个个回表。到主键索引上找出数据行,再对比字段值。而 MySQL 5.6 引入的索引下推优化(index condition pushdown), 可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。

哪些字段适合建索引?

  1. 出现在 SELECT、UPDATE、DELETE 语句的 WHERE 从句中的列
  2. 包含在 ORDER BY、GROUP BY、DISTINCT 中的字段
  3. 并不要将符合 1 和 2 中的字段的列都建立一个索引, 通常将 1、2 中的字段建立联合索引效果更好
  4. 多表 join 的关联列

为什么有些SQL不走索引?

  1. 使用了通配符开头,NOT IN 语句或者
  2. 联合索引的第一个字段查询条件中
  3. 数据引擎的优化器选错了索引(可以适当使用 force index 语句来优化)


为什么一般主键索引最好是自增的, 尽量短的数值类型?

  • 自增
结合B+Tree的特点,自增主键是连续的,在插入过程中尽量减少页分裂,即使要进行页分裂,也只会分裂很少一部分。并且能减少数据的移动,每次插入都是插入到最后。总之就是减少分裂和移动的频率。
由于InnoDB索引的特性,因此如果主索引不是自增的(id作主键),那么每次插入新的数据,都很可能对B+Tree的主索引进行重整,影响性能。因此,尽量以自增id作为InnoDB的主索引。
这就是为什么 主键的Id需求一般是整体趋势递增的原因。
  • 短数
每个非主键索引的叶子节点上都是主键的值。如果用UUID,比如 b8a52179-7d54-46de-b1de-d88911a42790 做主键,那么每个二级索引的叶子节点占用约 36字节,而如果用整型做主键,则只要 4字节,如果是长整型(bigint)则是 8字节。所以,主键长度越小,普通索引的叶子节点就越小,普通索引占用的空间也就越小。
利用了twitter的雪花算法来尽量做到生成 短数字 趋势自增 的的ID。

索引的最佳实践?

要建索引

  1. 定义主键的数据列一定要建立索引。
  2. 定义有外键的数据列一定要建立索引。
  3. 对于经常查询的数据列最好建立索引。
  4. 对于需要在指定范围内的快速或频繁查询的数据列;
  5. 经常用在WHERE子句中的数据列。
  6. 经常出现在关键字order by、group by、distinct后面的字段,建立索引。如果建立的是复合索引,索引的字段顺序要和这些关键字后面的字段顺序一致,否则索引不会被使用。

不要建索引

  1. 对于那些查询中很少涉及的列,重复值比较多的列不要建立索引。
  2. 对于定义为text、image和bit的数据类型的列不要建立索引。
  3. 对于经常存取的列避免建立索引

索引的坑

  1. 限制表上的索引数目。对一个存在大量更新操作的表,所建索引的数目一般不要超过3个,最多不要超过5个。索引虽说提高了访问速度,但太多索引会影响数据的更新操作。
  2. 对复合索引,按照字段在查询条件中出现的频度建立索引。在复合索引中,记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录,系统再按照第二个字段的取值排序,以此类推。因此只有复合索引的第一个字段出现在查询条件中,该索引才可能被使用,因此将应用频度高的字段,放置在复合索引的前面,会使系统最大可能地使用此索引,发挥索引的作用。

索引不会包含有NULL值的列

  1. 只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL

使用短索引(列内容越短越好)

  1. 对列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的列,如果在前10个或20个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

索引列排序

  1. MySQL查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。

like语句操作

  1. 一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 不会使用索引,而like “aaa%”可以使用索引。即:左匹配规则。可以使用reverse函数来支持逆序匹配,从而增强like走索引的可能。
  
    
    
  
ALTER TABLE `T` ADD `reverse_identifier` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci;

select * from T where reverse_identifier like reverse('%SDTE');

不要在列上进行运算

  1. select * from users where YEAR(adddate)<2007; 将在每个行上进行运算,这将导致索引失效而进行全表扫描,因此我们可以改成 select * from users where adddate<‘2007-01-01’;

不使用NOT IN和<>操作

  1. 因为MySQL只对<,<=,=,>,>=,BETWEEN,IN,以及某些时候的LIKE才会使用索引。因为在以通配符 % 和 _ 开头作查询时,MySQL不会使用索引。

有道无术,术可成;有术无道,止于术


好文章,我在看❤️