MySQL索引详解

索引基础

什么是索引

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。

索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。

索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。

通俗来讲，索引类似文章的目录，用来提高查询的效率。

索引分类

常见的索引类型有：主键索引、唯一索引、普通索引、全文索引、组合索引

主键索引

当一张表，把某个列设为主键的时候，则该列就是主键索引

create table a (  
    id int primary key auto_increment,  
    name varchar(20) not null default ''  
);  

这里id就是表的主键，如果当创建表时没有指定主键索引，也可以在创建表之后添加：

alter table table_name add primary key (column_name);

普通索引

用表中的普通列构建的索引，没有任何限制

create index 索引名 on table_name(column1);
​
alter table table_name add index 索引名(column1);

全文索引

全文索引主要针对文本文件，比如文章，标题。

在MySQL5.6之前，只有MyISAM存储引擎支持全文索引，MySQL5.6之后InnoDB存储引擎也支持全文索引。

create table c(  
    id int primary key auto_increment,  
    title varchar(20),  
    content text,  
    fulltext(title,content)  
) engine=myisam charset utf8; 
​
insert into c(title,content) values  
    ('MySQL Tutorial','DBMS stands for DataBase ...'),  
    ('How To Use MySQL Well','After you went through a ...'),  
    ('Optimizing MySQL','In this tutorial we will show ...'),  
    ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),  
    ('MySQL vs. YourSQL','In the following database comparison ...'),  
    ('MySQL Security','When configured properly, MySQL ...');

唯一索引

见名知义，索引列中的值必须是唯一的，但是允许为空值。

d表中name就是唯一索引，相比主键索引，主键字段不能为null，也不能重复

create table d(
    id int primary key auto_increment, 
    name varchar(32) unique
) 

组合索引

用多个列组合构建的索引。

ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3');

组合索引遵循"最左前缀"原则。

使用时最好把最常用作为检索或排序的列放在最左，依次递减。

组合索引相当于建立了col1，col1col2，col1col2col3三个索引，而col2或者col3是不能使用索引的。

在使用组合索引的时候可能因为列名长度过长而导致索引的key太大，导致效率降低。

在允许的情况下，可以只取col1和col2的前几个字符作为索引。

ALTER TABLE 'table_name' ADD INDEX index_name(col1(4),col2(3));

表示使用col1的前4个字符和col2的前3个字符作为索引

索引机制浅析

我们这里先简单剖析一下索引的机制，为接下来的深入做一些铺垫。

索引加快查询的原理

传统的查询方法，是按照表的顺序遍历的，不论查询几条数据，MySQL需要将表的数据从头到尾遍历一遍。

在我们添加完索引之后，MySQL一般通过BTREE算法生成一个索引文件。

在查询数据库时，找到索引文件进行遍历，使用能够大幅地查询的效率的折半查找的方式，找到相应的键从而获取数据。

索引的代价

创建索引是为产生索引文件的，占用磁盘空间。

索引文件是一个二叉树类型的文件，可想而知我们的DML操作((数据操作语言,对表记录的(增、删、改)操作)同样也会对索引文件进行修改，所以性能会相应的有所下降。

索引存储数据结构

上面已经说到，索引实际上是数据库中满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。

可能我们都知道，MySQL索引是B+树数据结构，当然，实际上索引还有哈希表、有序数组等常见的数据结构。

哈希表

哈希表是一种以键-值（key-value）存储数据的结构，我们只要输入待查找的值即key，就可以找到其对应的值即Value。

哈希的思路很简单，把值放在数组里，用一个哈希函数把key换算成一个确定的位置，然后把value放在数组的这个位置。

不可避免地，多个key值经过哈希函数的换算，会出现同一个值的情况。

处理这种情况的一种方法是，拉出一个链表。

所以，需要注意，哈希表后的链表并不是有序的，区间查询的话需要扫描链表。

所以哈希表这种结构适用于只有等值查询的场景，比如Memcached及其他一些NoSQL引擎。

有序数组

另外一个大家比较熟悉的数组结构，有序数组在等值查询和范围查询场景中的性能都非常优秀。

如果仅仅看查询效率，有序数组是非常棒的数据结构。但是，在需要更新数据的时候就麻烦了，你往中间插入一个记录就必须得挪动后面所有的记录，成本太高。

所以，有序数组索引只适用于静态存储引擎，比如你要保存的是2024年某个城市的所有人口信息，这类不会再修改的数据。

这两种都不是最主要的索引，常见的索引使用的数据结构是树结构。

树是数据结构里相对复杂一些的数据结构，我们来一步步认识索引的树结构。

二分查找

二分查找也称折半查找（Binary Search），它是一种效率较高的查找方法。但是，折半查找要求线性表必须采用顺序存储结构，而且表中元素按关键字有序排列。

查找方法：首先，假设表中元素是按升序排列，将表中间位置记录的关键字与查找关键字比较，如果两者相等，则查找成功；否则利用中间位置记录将表分成前、后两个子表，如果中间位置记录的关键字大于查找关键字，则进一步查找前一子表，否则进一步查找后一子表。重复以上过程，直到找到满足条件的记录，使查找成功，或直到子表不存在为止，此时查找不成功。

上面提到的有序数组的等值查询和比较查询效率非常高，但是更新数据存在问题。

为了支持频繁的修改，比如插入数据，我们需要采用链表。链表的话，如果是单链表，它的查找效率还是不够高。

所以，有没有可以使用二分查找的链表呢?

为了解决这个问题，BST(Binary Search Tree)也就是我们所说的二叉查找树诞生了。

二叉查找树

二叉树具有以下性质：左子树的键值小于根的键值，右子树的键值大于根的键值。

如下图所示就是一棵二叉查找树：

在这种比较平衡的状态下查找时间复杂度是O(log(n))。

但是二叉查找树存在一个问题：在某些极端情况下会退化成链表。

同样是2,3,4,6,7,8这六个数字，如果我们插入的数据刚好是有序的，那它就变成这样

这个时候，二叉查找树查找的时间复杂度就和链表一样，是O(n)。

造成它"叉劈"的原因是什么呢? 因为左右子树深度差太大，这棵树的左子树根本没有节点也就是它不够平衡。

所以，我们有没有左右子树深度相差不是那么大，更加平衡的树呢?

那就就是平衡二叉树，叫做 Balanced binary search trees，或者 AVL 树。

AVL 树

AVL Trees (Balanced binary search trees) 平衡二叉树的定义:左右子树深度差绝对值不能超过 1。

例如左子树的深度是 2，右子树的深度只能是 1 或者 3。

这个时候我们再按顺序插入 2,3,4,6,7,8，就不会"叉劈"

AVL树的平衡是怎么做到的呢？主要用到了两个操作左旋、右旋。

• 插入 1、2、3。 当我们插入了 1、2 之后，如果按照二叉查找树的定义，3 肯定是要在 2 的右边的。

• 这个时候根节点 1 的右节点深度会变成 2，但是左节点的深度是 0，因为它没有子节点，所以就会违反平衡二叉树的定义。

• 那应该怎么办呢?因为它是右节点下面接一个右节点，右–右型，所以这个时候我们要把 2 提上去，这个操作叫做左旋。

• 同样的，如果我们插入3、2、1，这个时候会变成左左型，就会发生右旋操作，把 2提上去。

既然平衡二叉树能保持平衡，不会退化，那么我们用平衡二叉树存储索引可以吗？可以的。

当我们用树的结构来存储索引的时候，访问一个节点就要跟磁盘之间发生一次 IO。

InnoDB 操作磁盘的最小的单位是一页(或者叫一个磁盘块)。

与主存不同，磁盘I/O存在机械运动耗费，因此磁盘I/O的时间消耗是巨大的。

所以如果每个节点存储的数据太少，从索引中找到我们需要的数据，就要访问更多的节点，意味着跟磁盘交互次数就会过多。

那么解决方案是什么？

• 让每个节点存储更多的数据。

• 让节点上有更多的关键字。

节点上的关键字的数量越多，我们的指针数也越多，也就是意味着可以有更多的分叉(我们把它叫做"路数")。

因为分叉数越多，树的深度就会减少(根节点是 0)。这样，树就从瘦高变成了矮胖。

这个时候，我们的树就不再是二叉了，而是多叉，或者叫做多路。

多路平衡查找树（B-Tree）

接下来看一下多路平衡查找树，也就是B树。

B树是一种多叉平衡查找树，如下图主要特点：

• B树的节点中存储着多个元素，每个内节点有多个分叉。

• 节点中的元素包含键值和数据，节点中的键值从大到小排列。也就是说，在所有的节点都储存数据。

• 父节点当中的元素不会出现在子节点中。

• 所有的叶子结点都位于同一层，叶节点具有相同的深度，叶节点之间没有指针连接。

以上图为例，我们来简单看几个查询：

• 如果查找key<17，就走左边子节点；

• 如果查找17<key<35，就走中间子节点；

• 如果查找key>35，就走右边子节点；

• 如果查找key=17，直接命中；

• 如果查找key=35，直接命中；

B树看起来很完美，到这就结束了吗？并没有。

• B树不支持范围查询的快速查找，你想想这么一个情况如果我们想要查找10和35之间的数据，查找到15之后，需要回到根节点重新遍历查找，需要从根节点进行多次遍历，查询效率有待提高。

• 如果data存储的是行记录，行的大小随着列数的增多，所占空间会变大。这时，一个页中可存储的数据量就会变少，树相应就会变高，磁盘IO次数就会变大

所以接下来就引入我们的终极数据结构B+树。

加强版多路平衡查找树(B+Tree)

B+树，作为B树的升级版，在B树基础上，MySQL在B树的基础上继续改造，使用B+树构建索引。

B+树和B树最主要的区别在于非叶子节点是否存储数据的问题

• B树：非叶子节点和叶子节点都会存储数据。

• B+树：只有叶子节点才会存储数据，非叶子节点至存储键值。叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表。

• 

存储容量

举个例子:假设一条记录是 1K，一个叶子节点(一页)可以存储 16 条记录。非叶子节点可以存储多少个指针?

假设索引字段是 bigint 类型，长度为 8 字节。指针大小在 InnoDB 源码中设置为 6 字节，这样一共 14 字节。非叶子节点(一页)可以存储 16384/14=1170 个这样的单元(键值+指针)，代表有 1170 个指针。

树深度为 2 的时候，有 1170^2 个叶子节点，可以存储的数据为 1170*1170*16=21902400

在查找数据时一次页的查找代表一次 IO，也就是说，一张 2000 万左右的表，查询数据最多需要访问 3 次磁盘。

所以在 InnoDB 中 B+ 树深度一般为 1-3 层，它就能满足千万级的数据存储。

查询效率

我们来看一下 B+Tree 的数据搜寻过程:

1） 比如我们要查找 28， 在根节点就找到了键值， 但是因为它不是页子节点， 所以会继续往下搜寻，28 是[28,66)的左闭右开的区间的临界值， 所以会走中间的子节点， 然后继续搜索， 它又是[28,34)的左闭右开的区间的临界值， 所以会走左边的子节点， 最后在叶子节点上找到了需要的数据。 2） 第二个， 如果是范围查询， 比如要查询从 22 到 60 的数据， 当找到 22 之后， 只需要顺着节点和指针顺序遍历就可以一次性访问到所有的数据节点， 这样就极大地提高了区间查询效率（ 不需要返回上层父节点重复遍历查找） 。

B+树特点总结

总结一下，InnoDB 中的 B+Tree 的特点:

1. 它是 B Tree 的变种，B Tree 能解决的问题，它都能解决。B Tree 解决的两大问题是什么?(每个节点存储更多关键字；路数更多)

2. 扫库、扫表能力更强(如果我们要对表进行全表扫描，只需要遍历叶子节点就可以了，不需要遍历整棵 B+Tree 拿到所有的数据)

3. B+Tree 的磁盘读写能力相对于 B Tree 来说更强(根节点和枝节点不保存数据区，所以一个节点可以保存更多的关键字，一次磁盘加载的关键字更多)

4. 排序能力更强(因为叶子节点上有下一个数据区的指针，数据形成了链表)

5. 效率更加稳定(B+Tree 永远是在叶子节点拿到数据，所以 IO 次数是稳定的)

索引使用原则

列的离散度

第一个叫做列的离散度，我们先来看一下列的离散度的公式:

count(distinct(column_name)) : count(*)

列的全部不同值和所有数据行的比例。数据行数相同的情况下，分子越大，列的离散度就越高。

mysql> SELECT * FROM `test`.`user` ORDER BY `id` LIMIT 10 OFFSET 0;
+----+-----------+--------+-------------+
| id | name      | gender | phone       |
+----+-----------+--------+-------------+
|  1 | 秦啭      |      0 | 13601722591 |
|  2 | 李镒榘    |      0 | 15204160836 |
|  3 | 陈艮      |      0 | 13601994087 |
|  4 | 沈夷旌    |      0 | 15507785988 |
|  5 | 朱桐泰    |      1 | 13201268193 |
|  6 | 周韬蕊    |      1 | 15705478612 |
|  7 | 冯叻加    |      0 | 13705834063 |
|  8 | 王焓      |      1 | 15006956358 |
|  9 | 黄芪      |      0 | 15108012536 |
| 10 | 吴笄游    |      0 | 15301860708 |
+----+-----------+--------+-------------+
10 rows in set (0.00 sec)

简单来说，如果列的重复值越多，离散度就越低，重复值越少，离散度就越高。

了解了离散度的概念之后，我们再来思考一个问题，我们在 name 上面建立索引和在 gender 上面建立索引有什么区别。

当我们用在 gender 上建立的索引去检索数据的时候，由于重复值太多，需要扫描的行数就更多。例如，我们现在在 gender 列上面创建一个索引，然后看一下执行计划。

ALTER TABLE user ADD INDEX idx_user_gender (gender); -- 耗时比较久 
EXPLAIN SELECT * FROM `user` WHERE gender = 0;

而 name 的离散度更高，比如"陈艮"的这名字，只需要扫描一行。

ALTER TABLE user ADD INDEX idx_user_name (name); 
EXPLAIN SELECT * FROM `user` WHERE name = '陈艮';

索引的基数与表总行数越接近，列的离散度就越高。

如果在索引 B+Tree 结构里面的重复值太多，MySQL 的优化器发现走索引跟使用全表扫描差不了多少的时候，就算建了索引，也不一定会走索引。

组合索引最左匹配

前面我们说的都是针对单列创建的索引，但有的时候我们的多条件查询的时候，也会建立组合索引。单列索引可以看成是特殊的组合索引。

比如我们在 user 表上面，给 name 和 phone 建立了一个组合索引。

ALTER TABLE user add INDEX comidx_name_phone (name,phone);

组合索引在 B+Tree 中是复合的数据结构，它是按照从左到右的顺序来建立搜索树的 (name 在左边，phone 在右边)。

从这张图可以看出来，name 是有序的，phone 是无序的。当 name 相等的时候，phone 才是有序的。

这个时候我们使用 where name= 'wangwu' and phone = '139xx'去查询数据的时候，B+Tree 会优先比较 name 来确定下一步应该搜索的方向，往左还是往右。

如果 name 相同的时候再比较 phone。但是如果查询条件没有 name，就不知道第一步应该查哪个节点，因为建立搜索树的时候 name 是第一个比较因子，所以用不到索引。

什么时候用到组合索引

所以，我们在建立组合索引的时候，一定要把最常用的列放在最左边。 比如下面的三条语句，能用到组合索引吗?

• 1)使用两个字段，可以用到组合索引:

mysql> EXPLAIN SELECT * FROM user WHERE name= '陈艮' AND phone = '13601994087';

• 2)使用左边的 name 字段，可以用到组合索引:

mysql> EXPLAIN SELECT * FROM user WHERE name= '陈艮';

• 3)使用右边的 phone 字段，无法使用索引，全表扫描:

mysql> EXPLAIN SELECT * FROM user WHERE phone

如何创建组合索引

当创建(a,b,c)联合索引时，相当于创建了(a)单列索引，(a,b)组合索引以及(a,b,c)组合索引。

想要索引生效的话,只能使用 a和a,b和a,b,c三种组合；当然，b,a也是好使的，因为sql会对它优化。

用 where b=? 和 where b=? and c=? 和 where a=? and c=?是不能使用到索引。

不能不用第一个字段，不能中断。

这里就是 MySQL 组合索引的最左匹配原则。

索引的创建

根据上一节的分析，我们总结出索引创建的一些注意点：

• 1、在用于 where 判断 order 排序和 join 的(on)字段上创建索引

• 2、索引的个数不要过多。浪费空间，更新变慢。

• 3、区分度低的字段，例如性别，不要建索引。离散度太低，导致扫描行数过多。

• 4、频繁更新的值，不要作为主键或者索引。 页分裂

• 5、组合索引把散列性高(区分度高)的值放在前面。最左前缀匹配原则

• 6、创建复合索引，而不是修改单列索引。

  组合索引代替多个单列索引（由于MySQL中每次只能使用一个索引，所以经常使用多个条件查询时更适合使用组合索引）

• 7、过长的字段，怎么建立索引?使用短索引。 当字段值比较长的时候，建立索引会消耗很多的空间，搜索起来也会很慢。

  我们可以通过截取字段的前面一部分内容建立索引，这个就叫前缀索引。

create table shop(address varchar(120) not null); 
alter table shop add key (address(12));

• 8、不建议用无序的值(例如身份证、UUID)作为索引当主键具有不确定性。

  会造成叶子节点频繁分裂，出现磁盘存储的碎片化

什么时候会用不到索引

• 1、索引列上使用函数(replace\SUBSTR\CONCAT\sum count avg)、表达式、计算(+ – * /):

explain SELECT * FROM 't2' where id+1 = 4;

• 2、字符串不加引号，出现隐式转换

explain SELECT * FROM 'user' where name = 136; 
​
explain SELECT * FROM 'user' where name = '136';

• 3、like 条件中前面带%

where 条件中 like abc%，like %2673%，like %888 都用不到索引吗?

explain select * from user where name like 'wang%'; 
​
explain select * from user where name like '%wang';

过滤的开销太大，所以无法使用索引。这个时候可以用全文索引。

• 4、负向查询

NOT LIKE 不能:

explain select * from employees where last_name not like 'wang'

!=和 NOT IN 在某些情况下可以:

explain select * from user where id not in (1) 
explain select * from user where id <> 1

• 5.索引不会包含有NULL值的列

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

• 6，排序的索引问题

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