白眉大叔 mysql联合索引
联合索引可以优化表中多列信息的查询，当需要多列信息查询时最好应用联合索引，不要应用多个单列索引；
在进行联合索引应用设置时，也是需要满足一定规范要求的，即使建立的联合索引，可能某些情况下，联合索引也不能大部分被使用；
因此，建立了联合索引，肯定是希望联合索引走的越多越好，但也有可能联合索引是否建立存在问题，也会导致查询效率较低；
联合索引建立异常分析思路： 创建好联合索引 + 合理应用联合索引 发挥联合索引最大价值
- 联合索引建立没有问题，但是查询语句书写有问题，导致联合索引应用效果不好；
- 查询语句书写没有问题，但是联合索引建立有问题，导致数据查询结果性能过低；
联合索引应用要遵循最左原则：（以索引讲解表格进行说明最左原则）
- 建立索引的时候，最左列使用选择度高（cardinality-重复值少的列/唯一值多的列）的列
- 执行查询的时候，一定包含索引的最左条件；
应用情况一：联合索引全部覆盖：
应用情况二：联合索引部分覆盖：
应用情况三：联合索引完全不覆盖：
应用情况一：联合索引全部覆盖：
- 需要满足最左原则；（尽量）
- 需要定义条件信息时，将所有联合索引条件都引用；（必要）
进行实战测试环境练习，属于联合索引全覆盖情况：
实战测试01-步骤一：删除默认索引
mysql> use baimei;
mysql> show index from t100w;
mysql> alter table t100w drop index idx_k2;
mysql> show index from t100w;
mysql> desc t100w;
-- 删除原有表中所有索引信息；实战测试01-步骤二：创建测试环境
# 在不满足最左原则创建联合索引
mysql> alter table t100w add index idx(num,k1,k2);
-- 此时key_len的最大预留长度：4+1 + 2*4+1 + 4*4+1 = 31
验证索引全覆盖最大预留长度
desc select * from t100w where num=913759 and k1='ej' and k2='EFfg';
最大预留长度验证结果：
说明：进行联合索引全覆盖时，索引条件的应用顺序是无关的，因为优化器会自动优化索引查询条件应用顺序；
实战测试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';
说明：在进行联合索引全覆盖查询时，
最后一列不是精确匹配查询，而是采取区间范围查询，也可以实现索引全覆盖查询效果；
应用情况二：联合索引部分覆盖：
需要满足最左原则；
需要定义条件信息时，将所有联合索引条件部分引用；进行实战测试环境练习，属性联合索引部分覆盖情况：
实战测试01-步骤一：进行部分查询测试
mysql> desc select * from t100w where num=339934;
查询的结果信息：
实战测试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';
说明：进行联合索引覆盖查询时，区间范围列不是最后一列，索引查询匹配只统计到区间范围匹配(不等值)列，也属于部分覆盖；
实战测试03-步骤一：进行部分查询测试
mysql> desc select * from t100w where num=339934 and k2='ej';
说明：进行联合索引覆盖查询时，查询索引列是不连续的，索引查询匹配只统计到缺失列前，也属于部分覆盖；
应用情况三：联合索引完全不覆盖：
- 需要定义条件信息时，将所有联合索引条件都不做引用；
进行实战测试环境练习，属性联合索引全不覆盖情况：
实战测试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='baimei' --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='baimei' --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
欢迎来撩 ： 汇总all
