前言

  • 每个索引都对应一棵B+树，B+树分为好多层，最下面一层是叶子节点，其余的是内节点。所有用户记录都存储在B+树的叶子节点，所有目录项记录都存储在内节点。
  • InnoDB存储引擎会自动为主键（如果没有它会自动帮我们添加）建立聚簇索引，聚簇索引的叶子节点包含完整的用户记录。
  • 我们可以为自己感兴趣的列建立二级索引，二级索引的叶子节点包含的用户记录由索引列 + 主键组成，所以如果想通过二级索引来查找完整的用户记录的话，需要通过回表操作，也就是在通过二级索引找到主键值之后再到聚簇索引中查找完整的用户记录。
  • B+树中每层节点都是按照索引列值从小到大的顺序排序而组成了双向链表，而且每个页内的记录（不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单链表。如果是联合索引的话，则页面和记录先按照联合索引前面的列排序，如果该列值相同，再按照联合索引后边的列排序。
  • 通过索引查找记录是从B+树的根节点开始，一层一层向下搜索。由于每个页面都按照索引列的值建立了Page Directory（页目录），所以在这些页面中的查找非常快。

7.1 B+树索引示意图简化

create table single_table (
    id int not null auto_increment,
    key1 varchar(100),
    key2 int,
    key3 varchar(100),
    key_part1 varchar(100),
    key_part2 varchar(100),
    key_part3 varchar(100),
    common_field varchar(100),
    primary key (id),
    key idx_key1  (key1),
    unique key uk_key2 (key2),
    key idx_key3 (key3),
    key idx_key_part (key_part1,key_part2,key_part3)
)engine=innodb charset=utf8;

7.2索引的代价

在熟悉了B+树索引原理之后，本篇文章的主题是介绍如何更好的使用索引，虽然索引是个好东西，可不能乱建，在介绍如何更好的使用索引之前先要了解一下使用这玩意儿的代价，它在空间和时间上都会拖后腿：

  • 空间上的代价
      这个是显而易见的，每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会占用16KB的存储空间，一棵很大的B+树由许多数据页组成，那可是很大的一片存储空间呢。
  • 时间上的代价
      每次对表中的数据进行增、删、改操作时，都需要去修改各个B+树索引。而且我们讲过，B+树每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表。不论是叶子节点中的记录，还是内节点中的记录（也就是不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单向链表。而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位，页面分裂、页面回收什么的操作来维护好节点和记录的排序。
    所以说，一个表上索引建的越多，就会占用越多的存储空间，在增删改记录的时候性能就越差。为了能建立又好又少的索引，我们先得学学这些索引在哪些条件下起作用的。

7.3 应用B+树索引

7.3.1扫描区间和边界条件

  • [1,1]是单点扫描
  • [1,1000]范围扫描

  • (-∞,+∞)全表扫描
    并不是所有的搜索条件都可以成为边界条件

    select * from single_table where key1 < 'a' and key3 > 'z' and common_field = 'abc';
  • 如果使用idx_key1执行查询，那么相应的扫描区间是(-∞,'a') 二key3 >'z' and common_field='abc'就是普通的搜索条件，这些搜索条件需要在获取到idx_key1的耳机索引记录后再执行会标操作，在获取到完整的用户记录后才能去判断他们是否成立。
  • 如果使用idx_key3执行查询，那么扫描区间是('z',+∞) key1 <'a' and common_field ='abc'就是普通的搜索条件，同理 获取到耳机索引记录后 回表，最终再判断

对于B+树索引来说，只要索引列和常数使用=,<=>,in,not in,is null,is not null,>,<,>=,<=,between,!=(也是<>)或者like操作符链接起来，就可以产生所谓的扫描 区间。

  • in操作符的寓意与若干个 = 之间用OR链接起来是一样的，都会产生多个单点扫描区、
select *  from single_table where key1 != 'a';
 扫描区间是(-∞, 'a')&('a',+∞)
  • like操作符 只有在匹配完整的字符串或者匹配字符串前缀时才会产生合适的扫描区间。

比较字符串的大小其实就相当于一次比较每个字符的大小。

  • 先比较第一个字符，第一个字符小的那个字符串就小
  • 如果两个字符串的第一个字符相同，再比较第二个，和1一样
  • 以此类推，对于某个索引列来说，字符串前缀相投的记录在由记录组成的单向列表中肯定是相邻的。
    like 'a%'的扫描空间相当于 ['a','b']
    and要求都为true or要求有一个为true

定位key1值的字符串前缀为'a'时的示意图

  1. 所有搜索条件都都可以生成合适的扫描区间

key2 &gt; 100 and key2 &gt; 200

key2 &gt; 100 OR key 2 &gt; 200

  1. 有的搜索条件不能生成合适的扫描区间

    select * from single_table where key2 > 100 and common_field = 'abc';

再使用uk_key2执行查询时，生成的扫描区间是(100,+∞)，但是uk_key2索引不包含common_field，所以此时的扫描区间是 (-∞,+∞)
(100,+∞) and (-∞,+∞) 最终生成的扫描区间是 (-∞,+∞)
如果强制使用uk_key2执行查询，扫描区间是(-∞,+∞)，需要扫描uk_key2的全部耳机索引记录，并且对于获取到的每一条耳机索引记录,都需要执行回表操作。代价大于全表扫描，所以这种情况下是不会考虑使用uk_key2来执行查询的。

  1. 从复杂的搜索条件中找出扫描区间
select * from single_table where
(key1 > 'xyz' and key2 = 748) OR
(key1 > 'xyz' and key2 = 748) OR
(key1 like '%suf' and key1 > 'zzz' and (key2 < 8000 OR common_field = 'abc'));
  • 首先分析搜索条件涉及了那些列，分别建立了哪些索引设计key1、key2、common_field3列，其中key1列有普通的耳机索引 idx_key1，key2列有唯一耳机索引uk_key2

  • 对于哪些可能用到的索引，分析扫描区间

    假设使用idx_key1执行查询

    key1 'xyz' OR ( key1 < 'abc' and key1 > 'lmn' )OR key1 > 'zzz'
最终简化成
key1>'xyz OR key1 > 'zzz'

    最终的扫描区间是key1>'xyz'，最终通过二级索引查找，然后回表最后按照搜索条件过滤
    假设使用uk_key2执行查询
    简化后的结果就是 TRUE
    扫描uk_key2的全部二级索引，然后回表。还不如全量扫描！所以是不会用uk_key2索引进行查询的

  1. 使用联合索引执行查询时对应的扫描区间
    联合索引的索引列包含多个列，B+树中的每一层页面以及每个页面中的记录采用的排序规则复杂，以single_table表的idx_key_part联合索引为例，它采用的排序规则如下所示：

    • 先按照key_part1列的值排序；
    • 如果值相同，按照key_part2列的值排序；
    • 如果值相同，按照key_part3的值排序
      索引示意图
    Q1: select * from single_table where key_part1 = 'a';

    由于二级索引记录是按照key_part1排序的索引符合，所以定位到key_part = 'a'条件的第一条记录，然后沿着记录所在的单向列表向后扫描( 如果页面中的记录扫完了，根据叶子节点的双向列表到下一个页面中的第一条记录，继续沿着所在的单向链表向后扫描)知道某条记录不符合key_part1 = 'a'条件为止，当然都要执行回表
    定位符合key_part1 = 'a'条件的记录的过程
    也就是说，扫描区间是['a','a']

    Q2: select * from single_table where key_part1 = 'a' and key_part2 = 'b';

    由于都是有序的所以定位到符合key_part1='a' and key_part2 = 'b'条件的第一条例记录，沿着记录所在的单向链表向后扫描，知道记录不符合key_part1='a' 或者 key_part2='b'条件为止
    定位到符合key_part1='a' and key_part2 = 'b'条件的记录的过程
    形成的扫描区间是[('a,'b'),('a','b')]

    Q3: select * from single_table where key_part1 = 'a' and key_part2 = 'b' and key_part3 = 'c';

    同Q2，Q3形成的扫描区间是[('a','b','c'),('a','b','c')]

    Q4: select * from single_table where key_part1 < 'a';
Q5: select * from single_table where key_part1 = 'a' and key part > 'a' and key_part2< 'd';

    Q4 由于索引是有序的，所以扫描区间是(-∞,'a')
    Q5 能够行程扫描区间(('a','a'),('a','d'));
    Q4
    Q5

    Q6: select * from single_table where key_part2 = 'a';
Q7: select * from single_table where key_part1 = 'a' and key_part3 = 'c';

    Q6：耳机索引记录不是直接按照key_part2列的值排序的，不能通过idx_key_part索引执行查询
    Q7：耳机索引记录是先按照key_part1列的值排序，但是不是直接按照key_part3排序的，不能进一步缩小扫描区间，如果使用idx_key_part索引执行查询，可以定位到符合key_part1='a'条件的第一条记录，然后向后扫描，知道某条记录不符合条件，对应的扫描区间是['a','a']，与key_part3='c'无关

    在使用dix_key_part索引执行查询Q7时，虽然搜索条件key_part3='c'不能作为行程扫描区间的边界条件，但是idx_key_part的二级索引记录是包含key_part3列的，每当从idx_key_part索引的扫描区间['a','a']中获取到一条二级索引的记录是，可以先判断记录是否符合key_part3='c'条件，如果符合条件，执行回表操作；如果不符合就不执行回表，直接下一条记录，这种优化的方式称为索引下推。在MySQL5.6中引入，默认开启。
    Q8：select * from single_table where key_part1 < 'b' and key_part2 = 'a';
Q9:  select * from single_table where key_part1 <= 'b' and key_part2 = 'a';

    Q8

    对于Q8来说 当key_part1<'b'时，key_part2是无序的，所以只能用到部分的联合索引，生成的扫描区间也就是(-∞,'b')。
    Q9
    对于Q9来说 当且仅当key_part1='b'时，key_part2是有序的，所以能用到全部的联合索引。

7.3.2

在MySQL中，在内存或者磁盘中进行排序的方式统称为文件排序。但是如果使用ORDER BY 子句中使用了索引列，就有可能省去在内存或磁盘中排序的步骤。

select * from single_table ORDER BY key_part1,key_part2,key_part3 limit 10;

二级索引的记录本身就是按照上述规则排好序的，使用直接使用二级索引获取10条记录，执行回表操作即可。

  1. 使用联合索引进行排序时的注意事项
    ORDER BY子句后面的列必须按照索引列的顺序给出

    select * from sinle_table where key_part1 = 'a' and key_part2 = 'b' order by key_part3 limit 10;

    key_part1 = 'a' and key_part2 = 'b'的索引记录是按照key_part3列的值进行排序的，所以能用上联合索引。

  2. 不可以使用索引进行排序的几种情况
    （1）ASC、DESC混用
    对于使用联合索引进行排序的场景，要求各个排序列的排序规则是一致的

    如果OREDER BY子句要求降序排序，还能使用索引进行查询么？
    页目录中有个槽的概念。查找当前记录的上一条记录时，找到该记录所在组的第一条记录(记录的next_record)属性找下一条记录，知道某条记录的头信息的n_owned属性值不为0，该记录就是本组中的头。

    两种情况

    • order by k1,k2 limit 10
      从联合索引的最左边那条二级索引记录开始，向右读10挑二级索引记录就可以了。
    • order by k1desc, k2 desc limit 10
      从联合索引的最右边那条耳机索引记录开始，向左读10条记录
      如果先按照k1升序，再按照k2降序，对于这种情况
      order bt k1,k2 desc limit10;
  • 先找到联合索引的最左边那条记录的k1的值(简称为min_value)然后享有找到k1值=min_value的所有耳机索引记录,再从最后一条k1=min_value的耳机索引记录开始，向左找10条二级索引记录
    k1=min_value的值有多少条呢？ 只能一直向右扫描才知道
  • 如果k1的值=min_value的二级索引记录有n条,（n<10）,那就得找到k1=min_value的最后一条二级索引记录的下一条二级索引记录（值为min_value2）,那就得找到k1=min_value2的最后一条二级索引记录开始，向左找10-n条记录

  • 如果k1=min_value2的二级索引记录还不够10条，。。。
    所以这种情况下是不会使用联合索引执行排序操作的

    MySQL8引入了一种成为descending index的特性，支持order by 中asc、desc混用的情况

    ( 2 ) 排序列包含非同一个索引的列
    有时候用来排序的多个列不是同一个索引中的，这种情况不能使用索引进行排序
    ( 3 ) 排序列是某个联合索引的列，但是这些排序列在联合索引中并不连续
    ( 4 ) 用来形成扫描区间的索引列与排序列不同

    select * from single_table where key1 ='a' order by key2 limit 10;

    key1 ='a'用来形成扫描区间，也就是在使用idx_key1执行查询，仅需扫描key1值为'a'的二级索引记录即可。此时无法使用uk_key2执行上述查询。
    ( 5 ) 排序列不是以单独列名的形式出现在order by子句中

    select * from single_table order by upper(key1) limit 10;

    要想使用索引进行排序，必须保证索引列是以单独列名的形式，不能使用修饰过的形式。

7.3.3 索引用于分组

select k1,k2,k3,count(*) from single_table group by k1,k2,k3
  • 先按照k1值把记录进行分组，k1值相同的所有记录划分为一组

7.4 回表的代价

select * from single_table where key1 > 'a' and key1 < 'c';

我们有两种方式执行

  • 以全表扫描的方式执行该查询
    也就是直接扫描去哪不得聚簇索引记录

  • 使用idx_key1执行该索引

    生成扫描区间('a','c')我们需要获取每条二级索引记录应对的聚簇索引记录，也就是执行回表操作，在获取到完整的用户记录后在发送到客户端
    对于使用InnoDB存储引擎的表来说，索引中的数据也都必须存放在磁盘中，等到需要的时候再加载到内存中。
    idx_key1在扫描区间('a','c')中的二级索引记录所在的页面的页号回尽可能相邻。总之在读取扫描区间('a','c')中的二级索引记录时，付出的代价是不叫小的，不过
    ('a','c')的二级索引记录对应的id值的大小是毫无规律的，我们每读取一条二级索引记录，就需要根据id到聚簇索引中执行回表操作，如果对于应的聚簇索引记录所在的页面不在内存中，就需要将该页面从磁盘中加载到内存中。由于要读取很多id值并不连续的聚簇索引记录，而且这些记录分布在不同的数据页中，这些数据页的页号也毫无规律，因此会造成大量的随机IO
    需要执行回表操作的记录越多，索引的查询性能也就越低，某些查询宁愿使用全表扫描也不实用二级索引。

    7.5 更好的创建和使用索引

    7.5.1 只为用于搜索、排序或分组的列创建索引

    我们只为出现在where子句中的列、连续子句中的连续列，或者出现在ORDER BY 或 GROUP BY子句中的列创建索引。

    7.5.2 考虑索引列中不重复值的个数

    通过二级索引+回表的方式执行查询时，某个扫描区间中包含的二级索引记录数量越多，就会导致回表操作的代价越大。如果过多重复值，那么有可能执行太多次的回表操作

    7.5.3 索引列的类型尽量小

    数据类型月销，索引占用的存储空间就越少，在一个数据页内疚可以存放更多的记录，磁盘IO带来的性能锁好也就越小，读写效率也就更高

    7.5.4 为列前缀简历索引

    索引列的字符串前缀其实是排序好的，之江字符串的前几个字符存放到索引中，当列中存储的字符串包含的字符较多时，列前缀简历的索引方式可以明显减少索引大小，不过不支持ORDER BY操作，

    7.5.5 覆盖索引

    为了彻底告别回表操作带来的性能损耗，建议最好在查询列表中只包含索引列
    如果业务需要查询索引列以外的列，还是保证业务需求为重

    7.5.6 索引列在搜索条件中单独出现

    select * from single_table where key2 * 2< 4;

mysql不会简化，直接认为这个搜索条件不能形成合适的扫描区间来减少需要扫描的记录数量，所以会全表查询

7.5.7 新插入记录时逐渐大小对效率的影响

保证主键递增,auto_increment

最后修改：2022 年 06 月 11 日
© 允许规范转载
如果觉得我的文章对你有用，请随意赞赏