一、数据库索引覆盖长度¶

在执行计划列中，key_len主要用来判断联合索引覆盖长度（字节），当覆盖长度越长，就表示匹配度更高，回表查询的次数越少；

到底联合索引被覆盖了多少，是可以通过key_len计算出来；

# 联合索引设置
alter table t1 add index id_a_b_c(a列,b列,c列);
# 联合索引应用
select * from t1 where a=xx and b=xx and c=xx
100行 -- 回表100
50行  -- 回表50
10行  -- 回表10

如果全部覆盖到了：长度=a+b+c 即三个列最大预留长度的总和

最大预留长度影响因素？

数据类型：
字符集（GBK：中文每个字符占用2个字节，英文1个字节 /UTF-8：中文每个字符占用3个字节，英文1个字节）
not null 是否可以为空 name

最大预留长度计算结果：不同的数据类型

字段	数据类型	字符集	计算结果
name	char(10)	utf8mb4	最大预留长度=4*10=40
		utf8	最大预留长度=3*10=30
	varcher(10)	utf8mb4	最大预留长度=4*10=40 + 2字节 =42 (1-2字节存储字符长度信息)
		utf8	最大预留长度=3*10=30 + 2字节 =32 (1-2字节存储字符长度信息)
	tinyint	N/A	最大预留长度=1（大约3位数） 2的8次方=256
	int	N/A	最大预留长度=4（大约10位数） 2的32次方=4294967296
	bigint	N/A	最大预留长度=8（大约20位数) 2的64次方=18446744073709551616
	not null	N/A	在没有设置not null时，在以上情况计算结果再+1

实例操作练习：理解key_len索引覆盖长度

创建一个测试数据表：

#

# 常见测试数据表
use test;
create table keylen (
    id int not null primary key auto_increment,
    k1 int not null,
    k2 char(20),
    k3 varchar(30) not null,
    k4 varchar(10)
) charset=utf8mb4;

# 设置表中列索引信息
alter table keylen add index idx(k1,k2,k3,k4);

进行表结构信息与索引设置信息查询：

Day006-数据库服务索引执行计划-图23

当四个索引信息全部覆盖，key_len数值计算结果：

# key_len计算思路
k1 = 4
k2 = 4 * 20 +1 = 81
k3 = 4 * 30 +2 = 122
k4 = 4 * 10 +2 + 1 = 43
sum = 4 + 81 + 122 + 43 = 250

# 进行校验结果
desc select * from keylen where k1=1 and k2='a' and k3='a' and k4='a';

Day006-数据库服务索引执行计划-图24

说明：根据key_len长度数值，理想上是和联合索引的最大预留长度越匹配越好，表示索引都用上了，回表次数自然会少；

二、数据库联合索引应用¶

联合索引可以优化表中多列信息的查询，当需要多列信息查询时最好应用联合索引，不要应用多个单列索引；

在进行联合索引应用设置时，也是需要满足一定规范要求的，即使建立的联合索引，可能某些情况下，联合索引也不能大部分被使用；

因此，建立了联合索引，肯定是希望联合索引走的越多越好，但也有可能联合索引建立存在问题，也会导致查询效率较低；

联合索引建立异常分析思路： 创建好联合索引 + 合理应用联合索引发挥联合索引最大价值

联合索引建立没有问题，但是查询语句书写有问题，导致联合索引应用效果不好；
查询语句书写没有问题，但是联合索引建立有问题，导致数据查询结果性能过低；

联合索引应用要遵循最左原则：（以索引讲解表格进行说明最左原则）

建立索引的时候，最左列使用选择度高（cardinality-重复值少的列/唯一值多的列）的列
执行查询的时候，一定包含索引的最左条件；

Day006-数据库服务索引执行计划-图25

应用情况一：联合索引全部覆盖：

需要满足最左原则；（尽量）
需要定义条件信息时，将所有联合索引条件都引用；（必要）

进行实战测试环境练习，属于联合索引全覆盖情况：

实战测试01-步骤一：删除默认索引

-- 删除原有表中所有索引信息；
mysql> use zq;
mysql> show index from t100w;
mysql> alter table t100w drop index idx_k2;
mysql> show index from t100w;
mysql> desc t100w;

Day006-数据库服务索引执行计划-图26

实战测试01-步骤二：创建测试环境

# 在不满足最左原则创建联合索引
mysql> alter table t100w add index idx(num,k1,k2);
-- 此时key_len的最大预留长度：4+1 + 2*4+1 + 4*4+1 = 31

联合索引创建情况：

Day006-数据库服务索引执行计划-图27

验证索引全覆盖最大预留长度

desc select * from t100w where num=913759 and k1='ej' and k2='EFfg';

最大预留长度验证结果：

Day006-数据库服务索引执行计划-图28

说明：进行联合索引全覆盖时，索引条件的应用顺序是无关的，因为优化器会自动优化索引查询条件应用顺序；

实战测试02-步骤一：获取重复数据信息

mysql> select num,count(*) from t100w group by num having count(*)>1 order by count(*) desc limit 3;
+--------+----------+
| num    | count(*) |
+--------+----------+
| 339934 |       14    |
| 614847 |       12    |
|  65003 |       12     |
+--------+----------+
3 rows in set (0.54 sec)

mysql> select * from t100w where num='339934';
+---------+--------+------+------+---------------------+
| id      | num    | k1   | k2   | dt                  |
+---------+--------+------+------+---------------------+
|  959036 | 339934 | 7X   | jkwx | 2019-08-12 11:52:47 |
|    4277 | 339934 | Ba   | NOpq | 2019-08-12 11:41:21 |
|  185265 | 339934 | BO   | 78Z0 | 2019-08-12 11:43:21 |
|  965745 | 339934 | eL   | Z0wx | 2019-08-12 11:52:52 |
|  987825 | 339934 | fs   | nomn | 2019-08-12 11:53:07 |
|  308385 | 339934 | g1   | deRS | 2019-08-12 11:44:44 |
|  223157 | 339934 | ku   | mn89 | 2019-08-12 11:43:46 |
|  138236 | 339934 | or   | UV45 | 2019-08-12 11:42:51 |
|  765105 | 339934 | rJ   | 89qr | 2019-08-12 11:50:26 |
|  478517 | 339934 | t8   | abef | 2019-08-12 11:46:49 |
|  107745 | 339934 | tZ   | noKL | 2019-08-12 11:42:31 |
|  503036 | 339934 | v3   | BCGH | 2019-08-12 11:47:07 |
|  596385 | 339934 | Yb   | PQqr | 2019-08-12 11:48:17 |
| 1000001 | 339934 | yb   | pqqs | 2022-11-12 12:41:59 |
+---------+--------+------+------+---------------------+

实战测试02-步骤二：插入新的测试数据

mysql> insert into t100w values(1000001,339934,'yb','pqqs',now());
mysql> select * from t100w where num='339934';

实战测试02-步骤三：进行范围索引全覆盖查询

mysql> select * from t100w where num=339934 and k1='yb' and k2 > 'PQqr';
mysql> desc  select * from t100w where num=339934 and k1='yb' and k2 > 'PQqr';

查询的结果信息：

Day006-数据库服务索引执行计划-图29

说明：在进行联合索引全覆盖查询时，最后一列不是精确匹配查询，而是采取区间范围查询，也可以实现索引全覆盖查询效果；

应用情况二：联合索引部分覆盖：

需要满足最左原则；
需要定义条件信息时，将所有联合索引条件部分引用；

进行实战测试环境练习，属性联合索引部分覆盖情况：

实战测试01-步骤一：进行部分查询测试

mysql> desc select * from t100w where num=339934;

查询的结果信息：

Day006-数据库服务索引执行计划-图30

实战测试02-步骤一：临时关闭索引下推

mysql> show variables like '%switch%';
mysql> set global optimizer_switch='index_condition_pushdown=off';
-- 实现测试练习完，需要恢复开启（操作可以省略）

实战测试02-步骤二：进行部分列范围查询

mysql > select * from t100w where num=339934 and k1<'yb' and k2='nokl';
mysql > desc  select * from t100w where num=339934 and k1<'yb' and k2='nokl';

查询的结果信息：

Day006-数据库服务索引执行计划-图31

说明：进行联合索引覆盖查询时，区间范围列不是最后一列，索引查询匹配只统计到区间范围匹配(不等值)列，也属于部分覆盖；

实战测试03-步骤一：进行部分查询测试

mysql> desc select * from t100w where num=339934  and k2='ej';

查询的结果信息：

Day006-数据库服务索引执行计划-图32

说明：进行联合索引覆盖查询时，查询索引列是不连续的，索引查询匹配只统计到缺失列前，也属于部分覆盖；

应用情况三：联合索引完全不覆盖：

需要定义条件信息时，将所有联合索引条件都不做引用；

进行实战测试环境练习，属性联合索引全不覆盖情况：

实战测试01-步骤一：进行索引查询测试

mysql> desc select * from t100w;

实战测试02-步骤一：进行索引查询测试

mysql> desc select * from t100w where num<339934 ;

说明：进行联合索引全不覆盖查询时，区间范围列出现在了第一列，也属于全不覆盖索引

实战测试03-步骤一：进行索引查询测试

mysql> desc select * from t100w where k2='ej';

说明：进行联合索引全不覆盖查询时，缺失最左列索引条件信息时，也属于全不覆盖索引

联合索引最左原则压力测试：

测试情况一：在不满足最左选择度高的情况；

# 创建索引情况
mysql> alter table t100w add index idx(num,k1,k2);

# 执行压力测试命令
[root@xiaoQ-01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='oldboy' --query="select * from t100w where num=339934 and k1='yb' and k2='PQqr';" engine=innodb --number-of-queries=200000 -uroot -p123456 -h192.168.30.101 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
        Running for engine rbose
        Average number of seconds to run all queries: 20.197 seconds
        Minimum number of seconds to run all queries: 20.197 seconds
        Maximum number of seconds to run all queries: 20.197 seconds
        Number of clients running queries: 100
        Average number of queries per client: 2000

测试情况二：在满足最左选择度高的情况；

# 调整索引情况
mysql> alter table t100w drop index idx;
mysql> alter table t100w add index idx(k1,k2,num);

# 执行压力测试命令
[root@xiaoQ-01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='oldboy' --query="select * from t100w where num=339934 and k1='yb' and k2='PQqr';" engine=innodb --number-of-queries=200000 -uroot -p123456 -h192.168.30.101 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
        Running for engine rbose
        Average number of seconds to run all queries: 20.494 seconds
        Minimum number of seconds to run all queries: 20.494 seconds
        Maximum number of seconds to run all queries: 20.494 seconds
        Number of clients running queries: 100
        Average number of queries per client: 2000

MySQL覆盖索引与联合索引：覆盖长度与最左前缀应用

一、数据库索引覆盖长度¶

二、数据库联合索引应用¶

评论区