您的位置 首页 数据库

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

 

 

欢迎来撩 : 汇总all

白眉大叔

关于白眉大叔linux云计算: 白眉大叔

热门文章