Mysql索引原理及树 - 索引失效、联合索引ABC几种使用情况 - 《MySql》

索引失效条件导致全表扫描的情况
解决like’%字符串%’时索引不被使用的方法
联合索引ABC详解
联合索引ABC的使用情况
回表和覆盖索引
如何挑选索引
针对索引失效的优化建议：

索引失效条件导致全表扫描的情况

1.在 where 子句中对字段进行 null 值判断（is null、is not null）无法使用索引。（应该null字段设置默认值）。
2.在 where 子句中使用 != 或 <> 操作符的时候。
3.在 where 子句中使用 or 来连接条件。（可以使用union all连接两个查询）。
4.在 where 子句中使用 in(1,2,3) 和 not in。（可以使用between 1 and 3）。
5.在索引列上做任何操作（计算、函数、（自动or手动）类型转换）。
6.字符串不加单引号索引失效。
7.like以通配符开头（’%abc’）。
8.联合索引没有最左匹配原则使用。

解决like’%字符串%’时索引不被使用的方法

使用覆盖索引，给要查询的字段建立索引。

执行这些语句都不会全表扫描，因为要查询字段都在索引里。

联合索引ABC详解

创建联合索引（普通索引、二级索引）

CREATE TABLE myTest(
    id INT NOT NULL auto_increment,
    a VARCHAR(100) NOT NULL,
    b DATE NOT NULL,
    c CHAR(11) NOT NULL,
    d varchar(100) NOT NULL,
    PRIMARY KEY (id),
    KEY idx_a_b_c (a, b, c)
);

注意两点：

表中的主键id列，它是自动递增的整数。所以InnoDB存储引擎会自动为id列建立聚簇索引。
二级索引idx_a_b_c，它由3个列组成的联合索引。这个索引对应的B+树的叶子节点存储的a、b、c和主键id的值，并不会保存d列的值。

一个表中有多少索引就会建立多少棵B+树，次表会为 id的聚簇索引和idx_a_b_c联合索引创建2棵树。
所以：联合索引 idx_a_b_c (a, b, c) 只会创建一棵B+树。
idx_a_b_c (a, b, c) 可以支持a / a,b/ a,b,c 3种组合进行索引查询。相当于创建了三颗索引B+树。

联合索引粗略示意图：

联合索引排序规则：

先按a列的值排序。
如果a列的值相同，则按b列的值进行排序。
如果b列的值也相同，则按照c的值进行排序。

联合索引ABC的使用情况

遵循最左匹配原则（最左前缀法则）在查询的时候如果查询条件精准的匹配到左边连续一列或多列，则次列就能用到索引。
索引的使用传递顺序：a列 -> b列 -> c列。
B+树记录中索引列排序：a列排序 -> b列排序 -> c列排序。

全值匹配

where后的列和索引列一致

(1) select * from myTest where a=1 and b=2 and c=3; —- abc顺序
abc三个索引都在where条件里面用到了，而且都发挥了作用

因为B+树的数据页和记录先是按照a列的值进行排序的，所以先定位a列的值是1的记录位置。
在a列相同的记录里又是按照b列的值进行排序，所以在a列的值是1的记录里又可以快速定位b列的值是2的记录。
a和b列的值都是相同，再按照c列的值排序的，所以联合索引中的三个列都可能被用到。

(2) select * from myTest where c=3 and b=2 and a=1;
where里面的条件顺序在查询之前会被mysql自动优化，效果跟a b c 一样。

最左列匹配

搜索条件中可以不使用全部索引的列，只包含左边的部分列也可以使用部分索引列。
select from myTest where a=1; —- 使用a索引
select from myTest where a=1 and b=2; —- ab索引
(3)select * from myTest where a=1 and c=3; —- ac顺序，只使用a列索引b没有用，所以索引达不到 c ，所以c未使用索引
select * from myTest where b=3 and c=4; —- bc顺序，因为索引列a没有使用，所以这里 bc都未使用索引

匹配列前缀(字符串索引)

为某个列建立索引的意思就是在对应B+树的记录中使用该列的值进行排序，字符串按照字符逐个排序，也就是说字符串的前n个字符，也就是前缀都是排好序的。所以字符串类型索引，只匹配它的前缀也可以快速定位记录。
select from myTest where a like ‘a%’; —- 使用a索引
select from myTest where a like ‘%a%’; —- 不能使用索引，因为中间或者后边的字符不确定顺序

匹配范围值

B+树中所有记录都是按照索引列的值从小到大的顺序排好序的，所以可以使用到索引列的范围查询。
select * from myTest where a > ‘Asa’ and a < ‘Bar’; —- 使用a列索引确定二级索引记录，读取记录的聚簇索引id值回表查询

注意：多个列同时进行范围查找，只有对索引最左边的a列进行范围查找时才能使用索引。
(4)select * from myTest where a > ‘Asa’ and a < ‘Bar’ and b > ‘2021-01-01’;
查询分为两步：

通过条件a > ‘Asa’ AND a < ‘Bar’ 查找a列符合的范围，可能有多条a值不同的记录，
对这些a值不同的记录继续通过b > ‘1980-01-01’条件继续过滤。

精确匹配某一列并范围匹配另外一列

左边的列是精确查找，右边的列可以进行范围查找。
select * from myTest where a=1 and b=2 and c>3; —- abc顺序，a列精准查找，b列精准查找，c在使用范围

注意：匹配的范围列在中间时，后边索引列不是使用到。
select from myTest where a>1 and b=2 and c=3; —- a先进行范围查找使用索引，b和c都不会使用索引
**(5)select from myTest where a=1 and b>2 and c=3; —- *a用到了，b也用到了，c没有用到，b是范围值，也算断点，自身用到了索引
select from myTest where a=1 and b>2 and c>3;
查询分为三步：

a = 1，对a列进行精确查找，使用B+树索引。
b > 2，由于a列是精确查找，所以通过a = 1得到的结果值都相同，会再按照b的值进行排序。所以对b列进行范围查找可以用到B+树索引。
c > 3，因为通过b的范围查找得到的记录中b的值可能不同，c列进行范围查找无法再利用B+树索引，只能遍历上一步查询得到的记录。

用于排序

在MySQL中，把这种在内存中或者磁盘上进行排序的方式统称为文件排序（英文名：filesort），但如果ORDER BY子句里使用到了索引列，就可能省去在内存或文件中排序的步骤，比如：
select * from myTest order by a, b, c limit 10;

查询结果集先按照a值排序，如果a值相同，则按照b排序，如果b的值相同，则按照c排序。因为B+树索引本身就是按照上述规则排好序的，所以可以直接从索引中提取数据，然后进行回表操作取出该索引中不包含的列。

联合索引进行排序注意事项

ORDER BY子句后边列的顺序也必须按照索引列的顺序给出，如果给出ORDER BY c, b, a的顺序，那用不了B+树索引。
ORDER BY a、ORDER BY a, b这种匹配索引左边的列的形式可以使用部分B+树索引。

当联合索引左边列的值为常量，也可以使用后边的列进行排序：
(6) select * from myTest where a=1 order by b; —-a使用索引，b在结果排序中也使用索引，因为a下面任意一段的b是排好序的
select * from myTest where a=1 order by b, c limit 10; —-a使用索引，b和c排序都使用索引

(7) select * from myTest where a=1 order by c;
a用到了索引，但是这个地方c没有发挥排序效果，因为中间断点了，使用 explain 可以看到 filesort

select * from mytable where b=3 order by a;
b没有用到索引，排序中a也没有发挥索引效果

排序时索引失效情况

ASC、DESC混用：
使用联合索引进行排序，要求各个排序列的排序顺序是一致的，要么都是ASC，要么都是DESC。

ORDER BY a, b LIMIT 10; —-直接从索引的最左边开始往右读10行记录。
ORDER BY a DESC, b DESC LIMIT 10; —-直接从索引的最右边开始往左读10行记录。

(8)select * from mytable order by a, b desc limit 10; —-a升序，b降序不会使用索引

排序列包含索引外的列
(9)SELECT * FROM mytable ORDER BY a, d LIMIT 10; —-不使用索引

排序列使用了复杂的表达式：
要想使用索引进行排序，必须保证索引列是以单独列的形式出现，而不是修饰过的形式：
(10)SELECT * FROM mytable ORDER BY UPPER(a) LIMIT 10; —-不使用索引

用于分组

(11)SELECT a, b, c, COUNT(*) FROM mytable GROUP BY a, b, c; —-使用索引
查询相当于做了3次分组：a先分->a相同的分组里再按b分->b相同的分组里再按c分。

回表和覆盖索引

如何挑选索引

建立索引或编写查询语句时注意事项

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

出现在WHERE子句中的列、连接子句中的连接列，出现在ORDER BY或GROUP BY子句中的列。
SELECT b, d FROM mytable WHERE a = ‘ase’;

查询列表中的b、d两列就不需要建立索引，只需要为出现在WHERE子句中的a列创建索引。

为列的基数大的列创建索引

列的基数指的是某一列中不重复数据的个数，最好为那些列的基数大的列建立索引。

比如2, 5, 8, 2, 5, 8, 2, 5, 8，有9条记录，列的基数是3。

索引列的类型尽量小

在定义表结构时要显式的指定列的类型，以整数类型为例，有TINYINT、MEDIUMINT、INT、BIGINT，它们占用的存储空间依次递增。
如果要对某个整数列建立索引的话，在表示的整数范围允许的情况下，尽量让索引列使用较小的类型，比如我们能使用INT就不要使用BIGINT，能使用MEDIUMINT就不要使用INT

数据类型越小，在查询时进行的比较操作越快
数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以存更多记录，从而减少磁盘I/O带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率

只有索引列在比较表达式中单独出现才会使用索引

假设表中有一个整数列col建立索引：

WHERE col * 2 < 4;
WHERE col < 4/2;

结论：如果索引列在比较表达式中不是以单独列的形式出现，而是以某个表达式或者函数调用形式出现，不使用索引。

索引字符串值的前缀(可以只对字符串值的前缀建立索引)

只对字符串的前几个字符进行索引也就是说在二级索引的记录中只保留字符串前几个字符。
比方说我们在建表语句中只对a列的前10个字符进行索引可以这么写：

CREATE TABLE mytable(
    id INT NOT NULL auto_increment,
    a VARCHAR(100) NOT NULL,
    b DATE NOT NULL,
    c CHAR(11) NOT NULL,
    d varchar(100) NOT NULL,
    PRIMARY KEY (id),
    KEY idx_a_b_c (a(10), b, c)
);

a(10)就表示在建立的B+树索引中只保留记录的前10个字符的编码，这种只索引字符串值的前缀的策略被鼓励使用。

索引列前缀对排序的影响

在使用排序时：使用索引列前缀的方式无法支持使用索引排序
SELECT * FROM mytable ORDER BY a LIMIT 10; —-因为二级索引中不包含完成的a信息，无法进行索引排序

主键设置AUTO_INCREMENT属性保证主键插入顺序

建议：为了尽可能少让聚簇索引发生页面分裂和记录移位的情况，建议主键设置AUTO_INCREMENT属性。，让存储引擎自己为表生成主键：

CREATE TABLE mytable(
    id INT NOT NULL auto_increment,
    a VARCHAR(100) NOT NULL,
    PRIMARY KEY (id),
);

避免冗余和重复索引

冗余索引：

CREATE TABLE mytable(
    id INT NOT NULL auto_increment,
    a VARCHAR(100) NOT NULL,
    b DATE NOT NULL,
    c CHAR(11) NOT NULL,
    d varchar(100) NOT NULL,
    PRIMARY KEY (id),
      KEY idx_a (a(10))
    KEY idx_a_b_c (a(10), b, c)
);

idx_a (a(10)) 和 idx_a_b_c (a(10), b, c) 索引冗余，因为后者就包含前者的功能。

重复索引：

CREATE TABLE repeat_index_demo (
    c1 INT PRIMARY KEY,
    c2 INT,
    UNIQUE uidx_c1 (c1),
    INDEX idx_c1 (c1)
);

c1既是主键、又给它定义了一个唯一索引和一个普通索引，主键本身就会生成聚簇索引，所以定义的唯一索引和普通索引是重复的。

参考：
https://blog.csdn.net/houmenghu/article/details/109580196
https://juejin.cn/book/6844733769996304392/section/6844733770046636045

针对索引失效的优化建议：

对于单键索引，尽量选择针对当前query过滤性更好的索引
在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好
在选择组合索引的时候，尽量选择可以能够包含当前query中的where子句中更多字段的索引
在选择组合索引的时候，尽量选择可以能够包含当前query中的where子句中更多字段的索引
尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

索引失效、联合索引ABC几种使用情况