mysql联合索引-工具盒子

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