[toc]
MySQL常见查询语句分析工具
MySQL版本5.7.19 innodb引擎
目标
分享对SQL语句的认识和SQL语句的调优工具。
实验环境搭建
CREATE TABLE `t_sales` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '用户名',
`age` int DEFAULT NULL COMMENT '年龄',
`password` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '密码 MD5存储',
`register_time` timestamp NULL DEFAULT NULL COMMENT '注册时间',
`type` int(1) DEFAULT NULL COMMENT '用户类型 1,2,3,4 随机',
PRIMARY KEY (`id`),
KEY `idx_username` (`username`) USING BTREE,
KEY `idx_name_age` (`username`,`age`) USING BTREE
)
DROP procedure salesAdd
create procedure salesAdd()
begin
declare i int default 1;
set autocommit = 0;
while i <= 4000000 do
insert into tea_design.t_sales
(`username`, `age`,`password`,`register_time`,type) values
(concat("jack",i),CEILING(RAND() * 100),MD5(concat("psswe",i)),from_unixtime(unix_timestamp(now()) - floor(rand() * 800000)),floor(1 + rand() * 4));
set i = i + 1;
end while;
set autocommit = 1;
end
call salesAdd();
CREATE TABLE `t2` (
`id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `a` (`a`)
) ENGINE=InnoDB;
drop procedure idata;
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=1000)do
insert into t2 values(i, i, i);
set i=i+1;
end while;
end;;
delimiter ;
call idata();
create table t1 like t2;
insert into t1 (select * from t2 where id<=100)
MySQL语句基本运行流程
查询语句流程
MySQL语句分析方式
explain
explain 输出列
Column | JSON 名称 | 意义 |
---|---|---|
id | select_id | 该SELECT标识符 |
select_type | None | 该SELECT类型 |
table | table_name | 输出行的表 |
partitions | partitions | 匹配的分区 |
type | access_type | 联接类型 |
possible_keys | possible_keys | 可供选择的可能索引 |
key | key | 实际选择的索引 |
key_len | key_length | 所选密钥的长度 |
ref | ref | 与索引比较的列 |
rows | rows | 要检查的行的估计 |
filtered | filtered | 按表条件过滤的行百分比 |
Extra | None | 附加信息 |
- type
先从最佳类型到最差类型介绍 重要且困难
system < const < eq_ref < ref < fulltext < ref_or_null < index_merge < unique_subquery < index_subquery < range < index < ALL
下面只介绍一些比较常见到的
- All
最坏的情况,全表扫描。
- index
该index联接类型是索引树被扫描。这有两种方式:
- 如果索引是查询的覆盖索引,可以满足表中所有需要的数据,则只扫描索引树。在这种情况下,该Extra列显示 Using index。(索引树上有需要的全部数据)
- 使用从索引中读取来执行全表扫描以按索引顺序查找数据行。 Uses index不会出现在 Extra列中。(需要回表)
- range
范围扫描,一个有限制的索引扫描。key 列显示使用了哪个索引。当使用=、 <>、>、>=、、BETWEEN 或者 IN 操作符,用常量比较关键字列
- ref
一种索引访问,它返回所有匹配某个单个值的行。此类索引访问只有当使用非唯一性索引或唯一性索引非唯一性前缀时才会发生。
这个类型跟eq_ref不同的是,它用在关联操作只使用了索引的最左前缀,或者索引不是UNIQUE和PRIMARY KEY。ref可以用于使用=或<=>操作符的带索引的列。
- rows
rows列显示MySQL认为它执行查询时必须检查的行数。这是一个预估值。这里的预估值会包含语句回表的次数,所以有可能比数据还大
在数据库里面,扫描行数是影响执行代价的因素之一。扫描行数并不是唯一的判断标准,优化器还会结合是否使用临时表、是否排序等因素进行综合判断。
扫描行数是怎么判断的?
这个统计信息就是索引的“区分度”。显然,一个索引上不同的值越多,这个索引的区分度就越好。而一个索引上不同的值的个数,称之为“基数”(cardinality)。也就是说,这个基数越大,索引的区分度越好。
我们可以使用 show index 方法,看到一个索引的基数。
show index from t_sales
如果统计信息有误可以通过analyze table t_sales来修正
原本可以执行得很快的 SQL 语句,执行速度却比你预期的慢很多,你应该怎么办呢?
- 方法一:使用force index 强行选择一个索引,这个时候优化器不再评估其他索引的执行代价。
- 方法二:可以考虑修改语句,引导 MySQL 使用我们期望的索引
- 方法三:可以新建一个更合适的索引,来提供给优化器做选择,或删掉误用的索引。
- filtered
该filtered列指示按表条件过滤的表行的估计百分比。最大值为 100,这意味着没有发生行过滤。从 100 开始减小的值表示过滤量增加。 rows显示检查的估计行数,rows× filtered显示与下表连接的行数。例如,如果 rows是 100 并且 filtered是 50.00 (50%),那么与下表连接的行数为 100 × 50% = 50。
- Extra
由于这里值太多,只介绍比较常见的
- Using filesort(需要优化)
说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。mysql中无法利用索引完成的排序操作称为“文件排序”。
排序的时候最好遵循所建索引的顺序与个数否则就可能会出现using filesort
EXPLAIN select * from t_sales order by age,username limit 100
EXPLAIN select * from t_sales order by username,age limit 100
- Using temporary(需要优化)
使用了临时表保存中间结果,mysql在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。
group by一定要遵循所建索引的顺序与个数
EXPLAIN select * from t_sales GROUP by age,username limit 100
EXPLAIN select * from t_sales GROUP by username,age limit 100
- Using index
表示相应的select操作中使用了覆盖索引(covering index),避免访问了表的数据行
如果同时出现using where,表明索引被用来执行索引键值的查找;
如果没有同时出现using where,表明索引用来读取数据而非执行查找动作。
对两个字段建立索引将其中一个字段作为where条件就符合键值查找
- Using join buffer(Block Nested Loop(BNL), Batched Key Access(BLA))(join的时候会出现)
这个稍后再讲
MySQL性能分析框架
show profile
show profiles 显示发送到服务器的最新语句的列表。列表的大小由profiling_history_sizesession 变量控制, 默认值为 15。最大值为 100。将值设置为 0 具有禁用分析的实际效果。
SET profiling = 1;
select * from t_sales GROUP by username,age;
show profiles;
show profile for quert id;
# 也可以看到CPU等资源
show profile block io,cpu,swaps for query 9;
EXPLAIN select * from t_sales GROUP by username,age;
ps:我查这么多数据,会不会把数据库内存打爆?
正常来说查到的数据都是放在结果集中,那么,这个“结果集”存在哪里呢?
实际上,服务端并不需要保存一个完整的结果集。取数据和发数据的流程是这样的:
- 获取一行,写到 net_buffer 中。这块内存的大小是由参数 net_buffer_length 定义的,默认是 16k。
- 重复获取行,直到 net_buffer 写满,调用网络接口发出去。
- 如果发送成功,就清空 net_buffer,然后继续取下一行,并写入 net_buffer。
- 如果发送函数返回 EAGAIN 或 WSAEWOULDBLOCK,就表示本地网络栈(socket send buffer)写满了,进入等待。直到网络栈重新可写,再继续发送。
也就是说,MySQL 是“边读边发的”。这就意味着,如果客户端接收得慢,会导致 MySQL 服务端由于结果发不出去,这个事务的执行时间变长。
如果在服务端 show processlist 看到的结果。State 的值一直处于“Sending to client”,就表示服务器端的网络栈写满了。
如果每次查询的数据很大,需要改用 mysql_use_result 接口了
mysql_store_result,mysql_use_result和mysql_free_result这三个接口可以去下面地址进行了解
https://dev.mysql.com/doc/refman/5.7/en/mysql.html
performance_schema表
Performance Schema 启动配置(要使用 MySQL Performance Schema,必须在服务器启动时启用它以启用事件收集。)
setup_actors表可用于按主机、用户或帐户限制历史事件的收集,以减少运行时开销和在历史记录表中收集的数据量。
性能架构以皮秒(万亿分之一秒)为单位显示事件计时器信息,以将计时数据规范化为标准单位。在以下示例中,值除以 100000000000000,以秒为单位显示数据。值也会被截断为 6 位小数,以便以与 SHOW PROFILE和SHOW PROFILE语句相同的格式显示数据。
-
将历史事件的收集限制为运行查询的用户。默认情况下,setup_actors配置为允许对所有前台线程进行监视和历史事件收集:
SELECT * FROM performance_schema.setup_actors;
±-----±-----±-----±--------±--------+
| HOST | USER | ROLE | ENABLED | HISTORY |
±-----±-----±-----±--------±--------+
| % | % | % | YES | YES |
±-----±-----±-----±--------±--------+
更新setup_actors表中的默认行以禁用所有前台线程的历史事件收集和监视,并插入一个新行,为运行查询的用户启用监视和历史事件收集:
UPDATE performance_schema.setup_actors
SET ENABLED = 'NO', HISTORY = 'NO'
WHERE HOST = '%' AND USER = '%';
INSERT INTO performance_schema.setup_actors
(HOST,USER,ROLE,ENABLED,HISTORY)
VALUES('localhost','test_user','%','YES','YES');
setup_actors表中的数据现在应类似于以下内容:
SELECT * FROM performance_schema.setup_actors;
+-----------+-----------+------+---------+---------+
| HOST | USER | ROLE | ENABLED | HISTORY |
+-----------+-----------+------+---------+---------+
| % | % | % | NO | NO |
| localhost | test_user | % | YES | YES |
+-----------+-----------+------+---------+---------+
-
通过更新setup_instruments表,确保启用了语句和阶段检测。默认情况下,某些工具可能已启用。
UPDATE performance_schema.setup_instruments
SET ENABLED = ‘YES’, TIMED = ‘YES’
WHERE NAME LIKE ‘%statement/%’;UPDATE performance_schema.setup_instruments
SET ENABLED = ‘YES’, TIMED = ‘YES’
WHERE NAME LIKE ‘%stage/%’; -
确保启用了该项和使用者。默认情况下,某些使用者可能已处于启用状态。events_statements_events_stages_
UPDATE performance_schema.setup_consumers
SET ENABLED = ‘YES’
WHERE NAME LIKE ‘%events_statements_%’;UPDATE performance_schema.setup_consumers
SET ENABLED = ‘YES’
WHERE NAME LIKE ‘%events_stages_%’; -
在要监视的用户帐户下,运行要分析的语句。例如:
SELECT * FROM my_demo_test.index_test
WHERE psd = "*C3AB9ECDF746570BBF9DCAA9DB3586D25956DC93"
-
通过查询events_statements_history_long表来标识语句。此步骤类似于运行SHOW PROFILE来标识 .以下查询生成类似于 SHOW配置文件的输出:EVENT_IDQuery_ID
SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXT
FROM performance_schema.events_statements_history_long WHERE SQL_TEXT like ‘%CDF746570%’;
-
查询events_stages_history_long表以检索语句的阶段事件。阶段使用事件嵌套链接到语句。每个阶段事件记录都有一个列,其中包含父语句的 。NESTING_EVENT_IDEVENT_ID
SELECT event_name AS Stage, TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration
FROM performance_schema.events_stages_history_long WHERE NESTING_EVENT_ID=385;
Percona Toolkit的使用
Percona Toolkit简称pt工具—PT-Tools,功能包括检查主从复制的数据一致性、检查重复索引、定位IO占用高的表文件、在线DDL等
- pt-archive ----MySQL的在线归档,无影响生产
- pt-online-schema-change ----在线DDL操作,对上亿的大表加索引加字段且对生产无影响
- pt-query-digest -----慢查询Log的分析。
- pt-duplicate-key-checker —检查数据库重复索引
查看MySQL优化器后执行得SQL语句详情
同时执行下面两个语句可以看到
EXPLAIN EXTENDED + SQL;
SHOW WARNINGS;
select * from t1 join t2 on (t1.a=t2.a) where t2.a>=1000 and t2.a<=20000;
执行后:
/* select#1 */
SELECT
`my_demo_test`.`t1`.`id` AS `id`,
`my_demo_test`.`t1`.`a` AS `a`,
`my_demo_test`.`t1`.`b` AS `b`,
`my_demo_test`.`t2`.`id` AS `id`,
`my_demo_test`.`t2`.`a` AS `a`,
`my_demo_test`.`t2`.`b` AS `b`
FROM
`my_demo_test`.`t1`
JOIN `my_demo_test`.`t2`
WHERE
(
(
`my_demo_test`.`t1`.`a` = `my_demo_test`.`t2`.`a`
)
AND (
`my_demo_test`.`t2`.`a` >= 1000
)
AND (
`my_demo_test`.`t2`.`a` <= 20000
)
)
可以用这种方式去查看语句中是否有可能导致影响性能的sql转换
例如:
- 条件字段函数操作
- 隐式类型转换
- 隐式字符编码转换
例子:
EXPLAIN EXTENDED select "10" > 9;
SHOW WARNINGS;
优化后:
/* select#1 */
SELECT
('10' > 9) AS `"10" > 9`
本身整数的对比会很快,但是转换后会变成字符串型的对比。效率差很远
常见关键字
join
BNL算法是join时完全不使用索引时使用的算法
NLJ算法是join时,被驱动表使用了索引时使用的算法
BKA算法基于MRR(顺序读盘)算法,在开启Mysql支持顺序读盘时,join的时候被驱动表使用了索引时才可以用的上。在MySQL 5.6中默认关闭BKA(MySQL 5.7默认打开)
NLJ是拿着驱动表去匹配被驱动表
BNL是拿着被驱动表去匹配驱动表
EXPLAIN select * from t1 straight_join t2 on (t1.a=t2.a);
Index Nested-Loop Join(NLJ) Join默认用上了被驱动表的索引
EXPLAIN select * from t1 straight_join t2 on (t1.a=t2.b);
Block Nested-Loop Join(BNL) 算法,扫描行数就会过多。尤其是在大表上的 join 操作,这样可能要扫描被驱动表很多次,会占用大量的系统资源。
在决定哪个表做驱动表的时候,应该是两个表按照各自的条件过滤,过滤完成之后,计算参与 join 的各个字段的总数据量,数据量小的那个表,就是“小表”,应该作为驱动表。
如果要使用 BKA 优化算法的话,你需要在执行 SQL 语句之前,先设置
set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
因为BKA要依赖Multi-Range Read(MRR(顺序读盘))
EXPLAIN select * from t2 where a>=1 and a<=100;
BNL 转 BKA
EXPLAIN select * from t1 join t2 on (t1.b=t2.b) where t2.b>=1 and t2.b<=2000;
一些情况下,我们可以直接在被驱动表上建索引,这时就可以直接转成 BKA 算法了。
性能测试:
环境:二十万数据。BNL和BKA和NLJ。b字段无索引,a字段有索引
BNL select * from t1 join t2 on (t1.b=t2.b) where t2.b>=1000 and t2.b<=20000;
NLJ
select * from t1 join t2 on (t1.a=t2.a) where t2.a>=1000 and t2.a<=20000;
BKA
set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
select * from t1 join t2 on (t1.a=t2.a) where t2.a>=1000 and t2.a<=20000;
结论:
- 如果可以使用被驱动表的索引,join 语句还是有其优势的
- 在使用 join 的时候,应该让小表做驱动表。
- 不能使用被驱动表的索引,只能使用 Block Nested-Loop Join 算法,这样的语句就尽量不要使用
- BKA 优化是 MySQL 已经内置支持的,建议默认使用;
- BNL 算法效率低,建议尽量转成 BKA 算法或NLJ。优化的方向就是给被驱动表的关联字段加上索引;
- 效率速度从慢到快 BNL < NLJ < BKA