MySQL索引

发表于 更新于 分类于 阅读次数： Waline： 本文字数： 9.2k 阅读时长 ≈ 8 分钟

索引类似于字典的目录，目的是为了提高查询效率。

如词典的目录、图书的目录等。它们的原理都是一样的，通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是我们总是通过同一种查找方式来锁定数据。

(1) 常见的索引模型

索引的出现是为了提高查询效率，但是实现索引的方式却有很多种，可以用于提高读写效率的数据结构很多，介绍三种常见、也比较简单的数据结构，它们分别是哈希表、有序数组和搜索树。

(1.1) hash索引

哈希表是一种以键 - 值（key-value）存储数据的结构，我们只要输入待查找的键即 key，就可以找到其对应的值即 Value
哈希表这种结构适用于只有等值查询的场景

优点：等值查询快
缺点：不适合范围查询

(1.2) 有序数组

有序数组在等值查询和范围查询场景中的性能就都非常优秀

在需要更新数据的时候就麻烦了，你往中间插入一个记录就必须得挪动后面所有的记录，成本太高。

有序数组索引只适用于静态存储引擎

(1.3) 二叉搜索树

查询的时间复杂度是 O(log(N))
更新的时间复杂度是 O(log(N))

一棵 100 万节点的平衡二叉树，树高 20。一次查询可能需要访问 20 个数据块。在机械硬盘时代，从磁盘随机读一个数据块需要 10 ms 左右的寻址时间。也就是说，对于一个 100 万行的表，如果使用二叉树来存储，单独访问一个行可能需要 20 个 10 ms 的时间，这个查询可真够慢的。

(1.4) N叉搜索树

为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。那么，我们就不应该使用二叉树，而是要使用“N 叉”树。这里，“N 叉”树中的“N”取决于数据块的大小。

以 InnoDB 的一个整数字段索引为例，这个 N 差不多是 1200。(MySQL页大小是16K，主键用bigint类型8字节 指针6字节 16000/(8+6)=1142 ≈ 1200)
这棵树高是 4 的时候，就可以存 1200 的 3 次方个值，这已经 17 亿了。(假设只存一个字段bigint类型)
考虑到树根的数据块总是在内存中的，一个 10 亿行的表上一个整数字段的主键索引，查找一个值最多只需要访问 3 次磁盘。


(2) MySQL索引实现

　MySQL中索引通过B+树实现

(2.1) B+树

　B+树非叶子节点只保存主键和指针
　B+树中只有叶子节点存储真实的数据
　B+树的叶节点之间通过双向链表链接

优点：

　1. IO次数更少: 由于B+树在非叶子节点上只包含主键和指针信息，因此每个节点存储的记录个数更多。因此B+树的高度更低，访问时所需要的IO次数更少。此外，由于每个节点存储的记录数更多，所以对访问局部性原理的利用更好，缓存命中率更高。
　2. 遍历更加方便：B+树的叶子结点都是相链的，因此对整棵树的遍历只需要一次线性遍历叶子结点即可。而且由于数据顺序排列并且相连，所以便于区间查找和搜索。
　3. 更稳定的查询效率：B+树的查询复杂度则稳定为树高，因为所有数据都在叶节点。

缺点： B+ 树维护索引有序性，在插入新值的时候需要做必要的维护，可能导致页分裂，整体空间利用率降低。

(2.2) 使用建议

　1. 主键使用自增主键
　2. 建议字段个数不超过30个，每条数据不要太长

主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小
从性能和存储空间方面考量，自增主键往往是更合理的选择。


(3) MySQL中常见的索引相关知识

(3.1) 主键索引

　主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。

根据主键id查询时，IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小
而 m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。
所以建议每条数据不要太长，这样根据主键id查找数据时IO次数会更少。

(3.2) 二级索引

　非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

create table T (
  ID int primary key,
  k int NOT NULL DEFAULT 0, 
  s varchar(16) NOT NULL DEFAULT '',
  index k(k)
) engine=InnoDB;
insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

select * from T where k between 3 and 5;
SQL 查询语句的执行流程：

  1. 在 k 索引树上找到 k=3 的记录，取得 ID = 300；
  2. 再到 ID 索引树查到 ID=300 对应的 R3；
  3. 在 k 索引树取下一个值 k=5，取得 ID=500；
  4. 再回到 ID 索引树查到 ID=500 对应的 R4；
  5. 在 k 索引树取下一个值 k=6，不满足条件，循环结束。　回到主键索引树搜索的过程，我们称为回表。

(3.3) 唯一索引

对于普通索引来说，查找到满足条件的第一个记录后，需要查找下一个记录，直到碰到第一个不满足条件的记录。
对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

普通索引和唯一索引性能差距会有多少呢？答案是，微乎其微。
InnoDB 的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在 InnoDB 中，每个数据页的大小默认是 16KB。

唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用。

(3.4) 覆盖索引

　查询的字段索引上都有，不需要回表，这种索引叫覆盖索引。

　如果执行的语句是 select ID from T where k between 3 and 5，这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引。

　由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

(3.5) 联合索引

　联合索引：根据创建联合索引的顺序，以最左原则进行where检索

(3.6) 最左前缀原则

　索引项是按照索引定义里面出现的字段顺序排序的
　如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

(3.7) 索引下推

　 MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

mysql> select * from tuser where name like '张%' and age=10 and ismale=1;

(4) MySQL 不走索引的情况

　简单地说，对索引做函数操作的都会不走索引。

(4.1) 案例一：条件字段函数操作

对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

假设你现在维护了一个交易系统，其中交易记录表 tradelog 包含交易流水号（tradeid）、交易员 id（operator）、交易时间（t_modified）等字段。为了便于描述，我们先忽略其他字段。这个表的建表语句如下：

mysql> CREATE TABLE `tradelog` (
  `id` int(11) NOT NULL,
  `tradeid` varchar(32) DEFAULT NULL,
  `operator` int(11) DEFAULT NULL,
  `t_modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tradeid` (`tradeid`),
  KEY `t_modified` (`t_modified`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

假设，现在已经记录了从 2016 年初到 2018 年底的所有数据，运营部门有一个需求是，要统计发生在所有年份中 7 月份的交易记录总数。这个逻辑看上去并不复杂，你的 SQL 语句可能会这么写：

(4.2) 案例二：隐式类型转换

数据类型转换的规则是什么？
为什么有数据类型转换，就需要走全索引扫描？

这里有一个简单的方法，看 select “10” > 9 的结果：
如果规则是“将字符串转成数字”，那么就是做数字比较，结果应该是 1；
如果规则是“将数字转成字符串”，那么就是做字符串比较，结果应该是 0。

select * from tradelog where tradeid=110717;
就知道对于优化器来说，这个语句相当于：
select * from tradelog where CAST(tradid AS signed int) = 110717;

现在，我留给你一个小问题，id 的类型是 int，如果执行下面这个语句，是否会导致全表扫描呢？
select * from tradelog where id=”83126”;

案例三：隐式字符编码转换

mysql> CREATE TABLE `trade_detail` (
  `id` int(11) NOT NULL,
  `tradeid` varchar(32) DEFAULT NULL,
  `trade_step` int(11) DEFAULT NULL, /* 操作步骤 */
  `step_info` varchar(32) DEFAULT NULL, /* 步骤信息 */
  PRIMARY KEY (`id`),
  KEY `tradeid` (`tradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into tradelog values(1, 'aaaaaaaa', 1000, now());
insert into tradelog values(2, 'aaaaaaab', 1000, now());
insert into tradelog values(3, 'aaaaaaac', 1000, now());

insert into trade_detail values(1, 'aaaaaaaa', 1, 'add');
insert into trade_detail values(2, 'aaaaaaaa', 2, 'update');
insert into trade_detail values(3, 'aaaaaaaa', 3, 'commit');
insert into trade_detail values(4, 'aaaaaaab', 1, 'add');
insert into trade_detail values(5, 'aaaaaaab', 2, 'update');
insert into trade_detail values(6, 'aaaaaaab', 3, 'update again');
insert into trade_detail values(7, 'aaaaaaab', 4, 'commit');
insert into trade_detail values(8, 'aaaaaaac', 1, 'add');
insert into trade_detail values(9, 'aaaaaaac', 2, 'update');
insert into trade_detail values(10, 'aaaaaaac', 3, 'update again');
insert into trade_detail values(11, 'aaaaaaac', 4, 'commit');

在这个执行计划里，是从 tradelog 表中取 tradeid 字段，再去 trade_detail 表里查询匹配字段。因此，我们把 tradelog 称为驱动表，把 trade_detail 称为被驱动表，把 tradeid 称为关联字段。

select * from trade_detail where tradeid=$L2.tradeid.value;
select * from trade_detail where CONVERT(traideid USING utf8mb4)=$L2.tradeid.value;

参照前面的两个例子，你肯定就想到了，字符集 utf8mb4 是 utf8 的超集，所以当这两个类型的字符串在做比较的时候，MySQL 内部的操作是，先把 utf8 字符串转成 utf8mb4 字符集，再做比较。
CONVERT() 函数，在这里的意思是把输入的字符串转成 utf8mb4 字符集。

mysql>select l.operator from tradelog l , trade_detail d where d.tradeid=l.tradeid and d.id=4;

alter table trade_detail modify tradeid varchar(32) CHARACTER SET utf8mb4 default null;
mysql> select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2;

(5) SQL查询技巧及原理

(5.1) in和between的区别

下面两条语句有什么区别，为什么都提倡使用2:

select * from T where k in(1,2,3,4,5) ;
select * from T where k between  1 and 5 ;

好问题，
第一个要树搜索5次
第二个搜索一次

(5.2) 索引下推+范围查询问题

老师您好：针对索引下推有两个问题请教您
1.之前一般认为range查询比如 a > 5 and b = '123' 在联合索引（a,b）中b是不起作用的，在ICP下是不是意味着b就可以起到作用了，我们还是应该尽量将查询中用到的字段放入联合索引中。
2.针对1的问题，a > 5 and a < 10 and b='123'在ICP作用下的执行过程是什么样子的？

作者回复: 好问题

  1. 是的
  2. 流程是这样的：
    a) 把 a>5 and b=’123’传入引擎，
    b)引擎找到第一个a>5的行（这里是快速定位），如果发现b<>’123’,找下一个，直到满足b=’123’
    c)把找到的行返回给server层， server层根据a是否小于10决定要不要取下一个

回表

壹笙☞漂泊
总结：
回表：回到主键索引树搜索的过程，称为回表
覆盖索引：某索引已经覆盖了查询需求，称为覆盖索引，例如：select ID from T where k between 3 and 5
在引擎内部使用覆盖索引在索引K上其实读了三个记录，R3~R5(对应的索引k上的记录项)，但对于MySQL的Server层来说，它就是找引擎拿到了两条记录，因此MySQL认为扫描行数是2
最左前缀原则：B+Tree这种索引结构，可以利用索引的”最左前缀”来定位记录
只要满足最左前缀，就可以利用索引来加速检索。
最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符
第一原则是：如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。
索引下推：在MySQL5.6之前，只能从根据最左前缀查询到ID开始一个个回表。到主键索引上找出数据行，再对比字段值。
MySQL5.6引入的索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

课后题：
ca没有必要，cb有必要。因为a、b联合索引，直接查询b不会使用主键联合索引。

疑问：
以前看过某些文章上面写：如果查询顺序和联合索引的顺序不一致，优化器会自动做优化，是这样的吗老师？

作者回复: 是的，查询语句的where里面各个判断调换顺序没关系的

索引-key哈希冲突

请教老师一个问题：如果普通索引的一个值对应多个主键ID 这些主键ID是按有序链表存放的吗？
作者回复: 是的

using index含义

如果既有联合查询，又有基于 a、b 各自的查询呢？查询条件里面只有 b 的语句，是无法使用 (a,b) 这个联合索引的，这时候你不得不维护另外一个索引，也就是说你需要同时维护 (a,b)、(b) 这两个索引。”我是有疑惑地。
建表及索引语句如下：
create table abc(id int primary key,a int,b int,c int) ;
ALTER TABLE abc ADD INDEX idx_a_b_c (a,b,c) ;
这是我的执行计划：explain select * from abc where c=1;
按理说where c=1 不满足最左匹配原则，可是我的执行计划里面走了索引，很困惑，希望老师或者有知道的同学给个解答。

mysql> create table abc(id int primary key,a int,b int,c int) ;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> ALTER TABLE abc ADD INDEX idx_a_b_c (a,b,c) ;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql>  explain select * from abc where c=1;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | abc   | NULL       | index | idx_a_b_c     | idx_a_b_c | 15      | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql>

作者回复: using index表示，使用了覆盖索引，也就是说确实使用了这个索引，但是执行的时候，是在这个索引里“全索引遍历”，这个索引没有起到加速的作用


(6) 索引查询优化案例

(6.1) 选错索引导致查询慢

有个500万的表 分页查询特别慢。

select * 
from table 
where create_time 
and create_time>= 时间戳 and  create_time<=时间戳
and subtype='xx' 
and type='xx' 
and company_id = x 
order by create_time 
limited 90,30 ;

已经建立了组合索引 union_index包括字段 create_time subtype type company_id
但是 explain 发现竟然走了create_time 的索引
语句里加了一个use index(union_index) ，立马好了
真正的解决了客户的实际问题啊。

(6.2) A in () AND B in ()

假如要查 A in () AND B in (), 怎么建索引?

作者回复: 好问题
where A in (a,b,c) AND B in (x,y,z)
会转成

(A=a and B=x) or (A=a and B=y) or (A=a and B=z) or
(A=b and B=x) or (A=b and B=y) or (A=b and B=z) or
(A=c and B=x) or (A=c and B=y) or (A=c and B=z)

(6.3) 删索引快

现在 limit b,a 这种写法，要求按照 b,a 排序，就意味着使用这两个索引都需要排序。
应该是order by b,a吧
另外有个问题请教林老师，根据经验大表增加索引的时候比较慢，这个是理解的，但是删除索引的时候能做到秒删，这个什么原理呢？

作者回复: 是，已经修改了，谢谢。
删除的时候是标记删除，所以很快。
建索引是要扫描数据和真正生成索引树，是会慢些

(6.4) 批量插入速度快

老师你好。我用存储过程插入100000条数据特别慢，后来我set autocommit=0,每1000条才commit，这样就快了。我想不出来这是为什么，求解惑

作者回复: Redo log 和 binlog刷盘次数少了，你把100000个事务变成了100个事务。

(6.5) 存储引擎和Server层交互

请教一个疑问：
存储引擎在找到一行合格的数据后 是先返回在继续查 还是一口气查完在一次性返回？
我的理解是查到一行就返回 不知对否？

作者回复: 对，一条条返回给server层，server层处理完再读下一个


(7) 思考题

InnoDB 表 T，如果你要重建索引 k，你的两个 SQL 语句可以这么写

alter table T drop index k;
alter table T add index(k);

如果你要重建主键索引，也可以这么写：

alter table T drop primary key;
alter table T add primary key(id);

对于上面这两个重建索引的作法，说出你的理解。如果有不合适的，为什么，更好的方法是什么？

重建索引 k 的做法是合理的，可以达到省空间的目的。

重建主键的过程不合理。不论是删除主键还是创建主键，都会将整个表重建。所以连着执行这两个语句的话，第一个语句就白做了。这两个语句，你可以用这个语句代替: alter table T engine=InnoDB。

alter table T engine=InnoDB 是用来释放 delete 操作引起的页的空洞,也就是碎片空间 操作时候尽量避免当前表的dml 操作.
表数据很大情况 建议使用 Percona Toolkit 工具来执行

References

[1] 04 | 深入浅出索引（上） MySQL实战45讲
[2] 05 | 深入浅出索引（下） MySQL实战45讲
[3] 09 | 普通索引和唯一索引，应该怎么选择？
[4] 10 | MySQL为什么有时候会选错索引？
[5] 11 | 怎么给字符串字段加索引？
[6] 18 | 为什么这些SQL语句逻辑相同，性能却差异巨大？
[7] MySQL索引原理及慢查询优化-美团
[8] innodb-index-types