1.什么是索引?
相当于一本书的目录。优化查询
2.MySQL 支持的索引类型(算法)
Btree : 平衡多叉树
Rtree : 空间树索引
Hash : HASH索引
fulltext: 全文索引
3.数据查找算法介绍
二叉树
红黑树
Btree: Blance Tree
- BTree 的查找算法(见图)
B-Tree:每次查询都从根节点开始,不能从枝节点或者叶子节点之间直接转换
B+Tree:实际上是在枝节点上添加了双向指针信息,从而减少对根节点的IO消耗
B*tree :实际上是在非根节点上添加了双向指针信息,从而减少对根节点和枝节点的IO消耗
B6E99DF40DF7A76F6F1DA9947B62E5AF.jpg
- MySQL Btree 索引的应用
5.1聚簇索引(聚集索引、主键索引)
前提:
0>InnoDB存储引擎的表才会有聚簇索引
1>有主键,主键就是聚簇索引
2>没有主键,选择唯一键作为聚簇索引
3>生成一个隐藏列(DB_ROW_ID,6字节),作为聚簇索引
作用:
1. 聚簇(区)索引,组织表(IOT): 所有数据在插入时,都按照ID(主键)属性,在相邻数据页上有序存储数据。
2. 加快存储数据,加快通过索引作为查找条件的查询。
参考:
https://dev.mysql.com/doc/refman/5.7/en/innodb-index-types.html
聚簇索引构建过程(见图)
1.png
- 叶子节点 :
由于存储数据时,已经按照ID顺序在各个数据页中有序存储了,所以《原表数据》所在数据页被作为叶子节点。 - 内部节点(非叶子节点--->枝节点):
获取叶子节点ID范围+指针。 - 根节点:
获取非叶子节点 ID范围+指针
5.2辅助索引
构建过程:
1. 叶子节点构建:
提取索引列值+ID ,进行从小到大排序(辅助索引列值),存储到各个数据页,作为叶子节点。
2. 非叶子节点(internel node )
提取下层的辅助索引列值范围+指针。
3. 根节点:
提取下层节点的范围+指针。
对于查询的优化:
1. 通过辅助索引列,进行条件查询,根据辅助索引BTREE快速锁定条件值对应的ID。
2. 通过得出的ID值,回到聚簇索引继续查询到具体的数据行(回表)。
2.png
- 辅助索引分类
6.1. 普通单列
6.2. 联合索引
idx(a,b)提取出来再排序,先按a列排,如果a列用相同的多个值的话,就按照b列的值进行排列(先拿最左列排)
叶子节点:
id+a+b ,按照a和b进行排序,生成叶子节点
枝节点和根节点:
只会包含最左列(a列)的范围+指针(最左原则)
注意: 最左原则
1. 建索引,最左列重复值少的。
2. 查询条件中,必须包含最左列。
6.3. 唯一索引
unique key
6.4. 前缀索引
idex(test(10))
- 索引树高度影响因素
7.1 列值长度
前缀索引。
7.2 数据量
分区表 。
定期归档表。
分布式架构:分库、分表。
7.3 数据类型
定长:char(20)
变长:varchar(20)
- 回表问题的探讨?
什么是回表?
辅助索引查找完成----> 聚簇索引查询过程。
回表会带来的问题?
IO增多: 量、次数
如何减少回表 ?
使用唯一值索引查询
联合索引
覆盖索引:辅助索引完全覆盖到查询结果
- 索引应用
9.1 压测
mysql> source /root/t100w.sql #上传数据
mysql> grant all on *.* to root@'10.0.0.%' identified by '123'; #创建一个远程登陆用户
进行压测
shell> mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k2='780P'" engine=innodb --number-of-queries=2000 -uroot -p123 -h10.0.0.51 -verbose
--concurrency=100 : 模拟同时100会话连接
--create-schema='test' : 操作的库是谁
--query="select * from test.t100w where k2='780P'" :做了什么操作
--number-of-queries=2000 : 一共做了多少次查询
Average number of seconds to run all queries: 719.431 seconds
Minimum number of seconds to run all queries: 719.431 seconds
Maximum number of seconds to run all queries: 719.431 seconds
9.2 查询表的索引
#查看表索引的三种方法
desc t100w;
-----
Key
-----
PK --> 主键(聚簇索引)
MUL --> 辅助索引
UK --> 唯一索引
mysql> show index from t100w;
mysql> show creat table city;
9.3 创建索引
9.3.1 单列辅助索引
select * from test.t100w where k2='780P'
优化方式:
alter table 表名 add index 索引名(列名);
alter table t100w add index idx_k2(k2);
9.3.2 联合索引创建
mysql> alter table t100w add index idx_k1_num(k1,num);
创建联合索引的时候要根据最左原则进行创建
对比k2 和 k1 的重复值,少的放在最所测,优化效果更佳
select count(distinct k1) from t100w;
select count(distinct k2) from t100w;
9.3.3 前缀索引创建
#判断前缀长度多少合适:
select count(distinct(left(name,5))) from city ;
select count(distinct name) from city ;
#创建前缀索引
mysql> alter table city add index idx_n(name(5));
9.4 删除索引
#语法:alter tabel 表名 drop index 索引名;
alter table city drop index idx_n;
- 执行计划查看和分析
10.1 什么是执行计划?
优化器优化后的“执行方案”。
10.2 作用 ?
a. 语句执行之前,通过执行计划,防患于未然。
b. 对于有性能问题的语句,进行分析。得出优化方案。
10.3 获取SQL的执行计划 。
Select 、 update 、 delete
#查看执行计划的两种方法
mysql> explain select * from test.t100w where num=279106 and k2='VWtu';
mysql> desc select * from test.t100w where num=279106 and k2='VWtu';
+----+-------------+-------+------------+------+----------------+------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+----------------+------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | t100w | NULL | ref | ix_k2,idx,idx1 | idx1 | 22 | const,const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+----------------+------+---------+-------------+------+----------+-------+
10.4 执行计划介绍
table : 操作的表
type : 查询索引的类型(ALL、index、range、ref 、eq_ref、const(system))
possible_keys : 可能会走的。
key : 最终选择的索引。
key_len : 联合索引覆盖长度。
rows : 此次查询需要扫描的行数(预估值)。
Extra : 额外信息。
10.5 type 详解
10.5.1 ALL 全表扫描
a. 查询条件没有建索引
mysql> desc t100w;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-----------------------------+
| id | int(11) | YES | | NULL | |
| num | int(11) | YES | | NULL | |
| k1 | char(2) | YES | | NULL | |
| k2 | char(4) | YES | | NULL | |
| dt | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
5 rows in set (0.00 sec)
mysql> desc select * from t100w where k2='780P';
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | t100w | NULL | ALL | NULL | NULL | NULL | NULL | 986679 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
b. 有索引,但查询语句不能走的情况。
mysql> alter table t100w add index idx(k2);
mysql> desc select * from t100w where k2 like '%80P';
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | t100w | NULL | ALL | NULL | NULL | NULL | NULL | 986679 | 11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
mysql> desc select * from t100w where k2 != '780P';
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | t100w | NULL | ALL | idx | NULL | NULL | NULL | 986679 | 73.37 | Using where |
mysql> desc select * from t100w where k2 not in ('780P');
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | t100w | NULL | ALL | idx | NULL | NULL | NULL | 986679 | 73.37 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
10.5.2 index 全索引扫描
mysql> desc select k2 from t100w;
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | t100w | NULL | index | NULL | idx | 17 | NULL | 986679 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+-------------+
10.5.3 range 索引范围扫描
mysql> desc select * from city where id<100;
mysql> desc select * from city where countrycode like 'CH%';
mysql> desc select * from city where countrycode='CHN' or countrycode='USA';
mysql> desc select * from world.city where countrycode in ('CHN','USA');
union all 改写:
desc
select * from city where countrycode='CHN' union all select * from city where countrycode='USA';
注意:
如果重复值过多的话,可能改写的效果不佳。
通过压测: 判断改写效果。
mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='world' --query="select * from world.city where countrycode in ('CHN','USA'); " engine=innodb --number-of-queries=2000 -uroot -p123 -h10.0.0.51 -verbose
mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='world' --query="select * from city where countrycode='CHN' union all select * from city where countrycode='USA'; " engine=innodb --number-of-queries=2000 -uroot -p123 -h10.0.0.51 -verbose
10.5.4 ref : 辅助索引等值查询
mysql> desc select * from city where countrycode='CHN';
10.5.5 eq_ref: 多表连接
非驱动表的连接条件是主键或唯一键。是多表连接中性能最好的查询方法。
拿结果集小的作为i驱动表
mysql> desc select a.name,b.name from city as a join country as b on a.countrycode=b.code where a.population<100;
+----+-------------+-------+------------+--------+-----------------+---------+---------+---------------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+-----------------+---------+---------+---------------------+------+----------+-----------------------+
| 1 | SIMPLE | a | NULL | range | CountryCode,idx | idx | 4 | NULL | 1 | 100.00 | Using index condition |
| 1 | SIMPLE | b | NULL | eq_ref | PRIMARY | PRIMARY | 3 | world.a.CountryCode | 1 | 100.00 | NULL |
+----+-------------+--
#left join 强制左表为驱动表
mysql> desc select a.name,b.name from city as a left join country as b on a.countrycode=b.code;
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------+
| 1 | SIMPLE | a | NULL | ALL | NULL | NULL | NULL | NULL | 4188 | 100.00 | NULL |
| 1 | SIMPLE | b | NULL | eq_ref | PRIMARY | PRIMARY | 3 | world.a.CountryCode | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)
10.5.6 const(system)
主键或唯一键等值查询
mysql> desc select * from city where id=10;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | city | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
10.5.7 NULL
mysql> desc select * from city where id=1000000;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | no matching row in const table |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
10.6 key_len 说明
10.6.0 计算方式
a. 介绍:
索引的应用长度
b. 作用:
判断联合索引的覆盖长度。
idx(a,b,c)
c. 如何计算key_len
总长度的计算:
a+b+c
d. 每个索引列占用多长?
每个列key_len,是这个列的《最大》预留长度 。
影响因素:
1. 数据类型
2. not null
3. 字符集(字符串类型)
计算
create table test (
id int not null primary key auto_increment,
a int not null ,
b char(10) not null ,
c char(5) ,
d varchar(20) not null ,
e varchar(10)
)engine=innodb charset=utf8mb4;
alter table test add index idx(a,b,c,d,e);
4+40+21+82+43=190
计算方法
mysql> desc select * from test where a=1 ;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | test | NULL | ref | idx | idx | 4 | const | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> desc select * from test where a=1 and b='aa';
+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+
| 1 | SIMPLE | test | NULL | ref | idx | idx | 44 | const,const | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> desc select * from test where a=1 and b='aa' and c='aa' ;
+----+-------------+-------+------------+------+---------------+------+---------+-------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------------------+------+----------+-------------+
| 1 | SIMPLE | test | NULL | ref | idx | idx | 65 | const,const,const | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> desc select * from test where a=1 and b='aa' and c='aa' and d='aa' ;
+----+-------------+-------+------------+------+---------------+------+---------+-------------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------------------------+------+----------+-------------+
| 1 | SIMPLE | test | NULL | ref | idx | idx | 147 | const,const,const,const | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------------------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> desc select * from test where a=1 and b='aa' and c='aa' and d='aa' and e='aa';
+----+-------------+-------+------------+------+---------------+------+---------+-------------------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------------------------------+------+----------+-------------+
| 1 | SIMPLE | test | NULL | ref | idx | idx | 190 | const,const,const,const,const | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------------------------------+------+----------+-------------+
10.6.1 联合索引应用细节
a. 联合索引全覆盖
idx(num,k1,k2) 索引 相当于index(num,k1)、index(num)
mysql> desc select * from t100w where num=641631 and k1='At' and k2='rsEF';
mysql> desc select * from t100w where k1='At' and num=641631 and k2='rsEF';
mysql> desc select * from t100w where k1='At' and num=641631 and k2 like 'rsE%';
b. 部分覆盖
mysql> desc select * from t100w where num=641631 and k1='At' ;
mysql> desc select * from t100w where k1='At' and num=641631 ;
#中间缺了k1所以只走num,后面就不能走索引了k2列走不到索引
mysql> desc select * from t100w where num=641631 and k2 like 'rsE%';
mysql> desc select * from t100w where num=641631 and k1 > 'AZ' and k2='rsEF';
mysql> desc select * from t100w where num=641631 and k1 != 'AZ' and k2='rsEF';
c. 完全不覆盖
#索引遵循最左原则的,这里没有num列,所以不会走索引的
mysql> desc select * from t100w where k1='At' and k2 like 'rsE%';
d. 在多子句 必须得使用联合索引
where a order by b
where a group by b order by xxx
10.7 Extra
using where : 此次查询中有部分条件是没有走索引的。
如果出现以上信息,说明where 条件,索引设计问题或者语句有问题。
using filesort : 出现文件排序,order by 、 group by 、 distinct ...
- 索引应用规范总结
11.1 建立索引的原则(DBA运维规范)
(1) 必须要有主键,无关列。
(2) 经常做为where条件列 order by group by join on, distinct 的条件(业务:产品功能+用户行为)
(3) 最好使用唯一值多的列作为索引,如果索引列重复值较多,可以考虑使用联合索引
(4) 列值长度较长的索引列,我们建议使用前缀索引.
(5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit(xxxxx)
(6) 索引维护要避开业务繁忙期,建议用pt-osc
(7) 联合索引最左原则
11.2 不走索引的情况(开发规范)
11.2.1 没有查询条件,或者查询条件没有建立索引
select * from t1 ;
select * from t1 where id=1001 or 1=1;
11.2.2 查询结果集是原表中的大部分数据,应该是15-25%以上。
查询的结果集,超过了总数行数25%,优化器觉得就没有必要走索引了。
MySQL的预读功能有关。
可以通过精确查找范围,达到优化的效果。
1000000
大于 > 500000 and
11.2.3 索引本身失效,统计信息不真实(过旧)
索引有自我维护的能力。
对于表内容变化比较频繁的情况下,有可能会出现索引失效。
一般是删除重建
现象:
有一条select语句平常查询时很快,突然有一天很慢,会是什么原因
select? --->索引失效,统计数据不真实
innodb_index_stats
innodb_table_stats
结局方法
#1.立即更新统计信息为最新的
# mysql> ANALYZE TABLE 表名
mysql> ANALYZE TABLE world.city;
#2.重建索引
11.2.4 查询条件使用函数在索引列上,或者对索引列进行运算,运算包括(+,-,*,/,! 等)
例子:
错误的例子:select * from test where id-1=9;
正确的例子:select * from test where id=10;
算术运算 函数运算 子查询 都会导致索引失效或者不走索引
11.2.5 隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.
11.2.6 <> ,not in 不走索引(辅助索引)
11.2.7 like "%_" 百分号在最前面不走
12. 彩蛋(扩展):优化器针对索引的算法
12.1 自优化能力:
12.1.1 MySQL索引的自优化-AHI(自适应HASH索引)
a. 限制
MySQL的InnoDB引擎,能够手工创建只有Btree。
AHI 只有InnoDB表会有,MySQL自动维护的。
AHI作用:
自动评估"热"的内存索引page,生成HASH索引表。
帮助InnoDB快速读取索引页。加快索引读取的效果。
相当与索引的索引。
4.png
参考文章:
https://dev.mysql.com/doc/refman/5.7/en/innodb-adaptive-hash.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-architecture.html
12.1.2 MySQL索引的自优化-Change buffer
限制:
比如insert,update,delete 操作时会使用change buffer。
对于聚簇索引会直接更新叶子节点。
对于辅助索引,不是实时更新的。
insert into t1 (id,name,age) values(33,'d',18)
在InnoDB 内存结构中,加入了insert buffer(会话),现在版本叫change buffer。
Change buffer 功能是临时缓冲辅助索引需要的数据更新。
当我们需要查询新insert 的数据,会在内存中进行merge(合并)操作,此时辅助索引就是最新的。
参考:
https://dev.mysql.com/doc/refman/5.7/en/innodb-change-buffer.html
12.1.3 8.0 版本索引的新特性
a. 不可见索引。invisable/visable index
针对优化器不可见。但是索引还在磁盘存在,还会自动维护。
对于索引维护时,不确定索引是否还有用。这时可以临时设定为invisable。
b. 倒序索引。
select * from t1 where c = order by a ASC , b desc
idx(c,a, b desc)
12.2 可选的优化器算法-索引
12.2.1 优化器算法查询
select @@optimizer_switch; #查询默认优化器算法=show variables like '%switch';
index_merge=on,
index_merge_union=on,
index_merge_sort_union=on,
index_merge_intersection=on,
engine_condition_pushdown=on,
index_condition_pushdown=on,
mrr=on,mrr_cost_based=on,
block_nested_loop=on,
batched_key_access=off,
materialization=on,
semijoin=on,
loosescan=on,
firstmatch=on,
duplicateweedout=on,
subquery_materialization_cost_based=on,
use_index_extensions=on,
condition_fanout_filter=on,
derived_merge=on
12.2.2 如何修改?
- my.cnf
#修改配置文件
optimizer_switch='batched_key_access=on'
#在线修改
set global optimizer_switch='batched_key_access=on';
- hints 了解一下
SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1
FROM t3 WHERE f1 > 30 AND f1 < 33;
SELECT /*+ BKA(t1) NO_BKA(t2) */ * FROM t1 INNER JOIN t2 WHERE ...;
SELECT /*+ NO_ICP(t1, t2) */ * FROM t1 INNER JOIN t2 WHERE ...;
SELECT /*+ SEMIJOIN(FIRSTMATCH, LOOSESCAN) / * FROM t1 ...;
EXPLAIN SELECT /+ NO_ICP(t1) */ * FROM t1 WHERE ...;
https://dev.mysql.com/doc/refman/5.7/en/optimizer-hints.html
12.2.3 index_condition_pushdown (ICP)
介绍: 索引下推 ,5.6+ 加入的特性
idx(a,b,c)
where a = and b 不等值 and c =
作用: SQL层做完过滤后,只能用a,b的部分辅助索引,将c列条件的过滤下推到engine层,进行再次过滤。排除无用的数据页。
最终去磁盘上拿数据页。
大大减少无用IO的访问。
测试1: ICP开启时
idx(k1,k2)
#开启ICP参数
mysql> SET global optimizer_switch='index_condition_pushdown=ON'
#进行压测测试
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k1 = 'Za' and k2 like '%sE%'" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 1.114 seconds
Minimum number of seconds to run all queries: 1.114 seconds
Maximum number of seconds to run all queries: 1.114 seconds
Number of clients running queries: 100
Average number of queries per client: 20
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 6.945 seconds
Minimum number of seconds to run all queries: 6.945 seconds
Maximum number of seconds to run all queries: 6.945 seconds
Number of clients running queries: 100
Average number of queries per client: 200
测试2:ICP关闭时:
idx(k1,k2)
#开启ICP参数
mysql> SET global optimizer_switch='index_condition_pushdown=OFF'
#进行压测测试
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k1='Za' and k2 like '%sE%'" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 3.125 seconds
Minimum number of seconds to run all queries: 3.125 seconds
Maximum number of seconds to run all queries: 3.125 seconds
Number of clients running queries: 100
Average number of queries per client: 20
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 31.102 seconds
Minimum number of seconds to run all queries: 31.102 seconds
Maximum number of seconds to run all queries: 31.102 seconds
Number of clients running queries: 100
Average number of queries per client: 200
具体参考 :
https://dev.mysql.com/doc/refman/5.7/en/index-condition-pushdown-optimization.html
https://mariadb.com/kb/en/index-condition-pushdown/
12.4 MRR : Multi Range Read
12.4.1 作用: 理论上减少回表。
辅助索引扫描后,得到聚簇索引值,统一缓存到read_rnd_buffer,进行排序,再次回表。
12.4.2 开关方法:
mysql> set global optimizer_switch='mrr=on,mrr_cost_based=off';
12.4.3 区别
具体参考 :
https://dev.mysql.com/doc/refman/5.7/en/mrr-optimization.html
https://mariadb.com/kb/en/multi-range-read-optimization/
压力测试:
alter table world.city add index idx_n(name);
mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='world' --query="select * from world.city where name in ('Aachen','Aalborg','Aba','Abadan','Abaetetuba')" engine=innodb --number-of-queries=20000 -uroot -p123 -verbose
no-mrr:
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 2.909 seconds
Minimum number of seconds to run all queries: 2.909 seconds
Maximum number of seconds to run all queries: 2.909 seconds
Number of clients running queries: 100
Average number of queries per client: 200
mrr:
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 3.384 seconds
Minimum number of seconds to run all queries: 3.384 seconds
Maximum number of seconds to run all queries: 3.384 seconds
Number of clients running queries: 100
Average number of queries per client: 200
优化没有最佳实践。
12.5 SNLJ 普通嵌套循环连接
例子:
A join B
on A.xx = B.yy
where
伪代码:
for each row in A matching range {
block
for each row in B {
A.xx = B.yy ,send to client
}
}
例子:
mysql> desc select * from teacher join course on teacher.tno=course.tno;
优化器默认优化规则:
- 选择驱动表
默认选择方式(非驱动表):
0. 结果集小的表作为驱动表
按照on的条件列,是否有索引,索引的类型选择。
1. 在on条件中,优化器优先选择有索引的列为非驱动表。
2. 如果两个列都有索引,优化器会按照执行的代价去选择驱动表和非驱动表。
for each row in course matching range {
block
for each row in teacher {
course.tno = tracher.tno ,send to client
}
}
关于驱动表选择的优化思路:
理论支撑:
mysql> desc select * from city join country on city.countrycode=country.code ;
mysql> desc select * from city left join country on city.countrycode=country.code ;
查询语句执行代价:
mysql> desc format=json select * from city join country on city.countrycode=country.code ;
mysql> desc format=json select * from city left join country on city.countrycode=country.code ;
实践检验:
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='world' --query="select * from city left join country on city.countrycode=country.code ;" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='world' --query="select * from city join country on city.countrycode=country.code ;" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose
注: 可以通过 left join 强制驱动表。
12.6 BNLJ
在 A和B关联条件匹配时,不再一次一次进行循环。
而是采用一次性将驱动表的关联值和非驱动表匹配.一次性返回结果
主要优化了, CPU消耗,减少了IO次数
In EXPLAIN output,
use of BNL for a table is signified
when the Extra value contains Using join buffer (Block Nested Loop)
12.7 BKA
主要作用,使用来优化非驱动表的关联列有辅助索引。
BNL+ MRR的功能。
开启方式:
mysql> set global optimizer_switch='mrr=on,mrr_cost_based=off';
mysql> set global optimizer_switch='batched_key_access=on';
重新登陆生效。
网友评论