MySQL常见查询语句分析工具

最新推荐文章于 2024-07-23 17:32:59 发布

weixin_44806531

最新推荐文章于 2024-07-23 17:32:59 发布

阅读量475

点赞数

文章标签： mysql 数据库 java

本文链接：https://blog.csdn.net/weixin_44806531/article/details/131152244

版权

[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语句基本运行流程

查询语句流程

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-X5JtXodK-1686458785570)(https://note.youdao.com/yws/res/79325/B9FD9510841E4E39930484933FA2F959)]

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

下面只介绍一些比较常见到的

最坏的情况,全表扫描。

index

该index联接类型是索引树被扫描。这有两种方式：

如果索引是查询的覆盖索引，可以满足表中所有需要的数据，则只扫描索引树。在这种情况下，该Extra列显示 Using index。(索引树上有需要的全部数据)
使用从索引中读取来执行全表扫描以按索引顺序查找数据行。 Uses index不会出现在 Extra列中。(需要回表)

range

范围扫描，一个有限制的索引扫描。key 列显示使用了哪个索引。当使用=、 <>、>、>=、、BETWEEN 或者 IN 操作符,用常量比较关键字列

一种索引访问，它返回所有匹配某个单个值的行。此类索引访问只有当使用非唯一性索引或唯一性索引非唯一性前缀时才会发生。

这个类型跟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;

在这里插入图片描述