MySQL性能【查询截取分析】-白红宇

MySQL性能【查询截取分析】

阅读量：3946 次

发布时间：2019-05-24

本文共 7075 字，大约阅读时间需要 23 分钟。

文章目录

查询优化

小表驱动大表

优化原则：小表驱动大表，即小的数据集驱动大的数据集。

select * from A where id in (select id from B)等价于：for select id from Bfor select * from A where A.id = B.id

当 B 表的数据集必须小于 A 表的数据集时，用 in 优于 exists。

select * from A where exists (select 1 from B where B.id = A.id)等价于：for select * from Afor select * from B where B.id = A.id

当 A 表的数据集小于 B 表的数据集时，用 exists 优先于 in。

EXISTS

select … from table where exists(subquery)

该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果(true 或 false)来决定主查询的数据结果是否得以保留。

orderr by 关键字优化

order by 字句，尽量使用 index 方式排序，避免使用 filesort 方式排序

MySQL 支持两种方式对的排序， FileSort 和 index,index 效率高，它指 MySQL 扫描索引本身完成排序。FileSort 方式效率较低。

Order by 满足两种情况，会使用 Index 方式排序

Order by 语句使用索引最左前列

使用 Where 字句与 Order By 字句条件列组合满足索引最左前列

尽可能在索引列上完成排序操作，遵照索引的最佳左前缀原则。

如果不在索引列上，filesort 有两种算法：mysql 就要启动双路排序和单路排序

双路排序
- MySQL 4.1 之前时使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和 order by 列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重写从列表中读取对应的数据输出。
- 从磁盘取出排序字段，在 buffer 进行排序，再从磁盘取出其他字段。

取一批数据，要对磁盘进行两次扫描，众所周知，I\O是很耗时的，所以在 mysql4.1 之后，出现了第二种改进的算法，就是单路排序。

单路排序
- 从磁盘中读取查询需要的所有列，按照 order by 列在 buffer 对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机 IO 变成了顺序 IO，但是它会使用更多的空间，因为它把每一行都保存在内存中了。

优化策略

增大 sort_buffer_size 参数的设置

增大 max_length_for_sort_data 参数的设置

在这里插入图片描述

group by 关键字优化

group by 实质是先排序后进行分组，遵照索引建的最佳左前缀

当无法使用索引列，增加 max_length_for_sort_data 参数的设置 + 增大 sort_buffer_size 参数的设置

where 高于 having，能写在 where 限定的条件就不要去 having 限定了。

慢查询日志

MySQL 的慢查询日志是 MySQL 提供的一种日志记录，它用来记录在 MySQL 中相应时间超过阈值的语句，具体指运行时间超过 long_query_time 值得 SQL，则会被记录到慢查询日志中。

具体指运行时间超过 long_query_time 值得 SQL，则会被记录到慢查询日志中。long_query_time 得默认值为 10，意思是运行 10 秒以上的语句。

由它来查看哪些 SQL 超过了我们得最大忍耐时间值，比如一条 SQL 执行超过5秒钟，我们就算它是慢 SQL，希望能收集超过5秒得SQL，结合 explain 进行全面分析。

如何使用慢查询日志

默认情况下，MySQL 数据库没有开启慢查询日志，需要我们手动来设置这个参数。

当然，如果不是调优需要得话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。

查看是否开启以及如何开启

默认：show variables like '%slow_query_log%';

开启：set global slow_query_log = 1;

如何永久生效，修改配置文件 my.cnf 即可（不建议长期开启）

修改 my.cnf 文件，【mysqld】下增加或修改参数

slow_query_log 和 slow_query_log_file 后，然后重启MySQL服务器即可。

具体的配置参数如下：

slow_query_log=1slow_query_log_file=/var/lib/mysql/mysql-slow.log

关于慢查询得参数 slow_query_log_file，它指定慢查询日志文件得存放路径，系统默认会给一个缺省的文件 host_name-slow.log（如果没有指定参数 slow_query_log_file的话）

什么样的 SQL 才会记录到慢查询日志中？

这个是由参数 long_query_time 控制，默认情况下 long_query_time 的值为 10 秒。

命令：show variables like 'long_query_time%';

在这里插入图片描述

可以使用命令修改，也可以在 my.cnf 参数里面修改。

假如运行时间正好等于 long_query_time 的情况，并不会被记录下来，也就是说：在 mysql 源码里是判断大于 long_query_time，而非大于等于。

Case 例子

查看当前多少秒算满 > show variables like 'long_query_time%';

设置慢的阈值时间：set global long_query_time=3;

在这里插入图片描述

为什么设置后看不出变化

需要重新连接或新开一个会话才能看到修改值。

show variables like 'long_query_time%;

show global variables like 'long_query_time';

在这里插入图片描述

记录慢 SQL 并后续分析

在这里插入图片描述

查询当前系统中有多少条慢查询记录

show global status like '%Slow_queries%%';

在这里插入图片描述

永久配置慢日志开启

【mysqld】下配置：slow_query_log==1;slow_query_log_file=/var/lib/mysql/atguigu-slow.loglong_query_time=3;log_output=FILE

日志分析工具 mysqldumpslow

在生产环境中，如果要手工分析日志，查找、分析SQL，MySQL提供了日志分析工具 mysqldumpslow。

查看 mysqldumpslow 的帮助指令：mysqldumpslow --help,各个参数的意义如下：

s：是表示按照何种方式排序

c：访问次数

I：锁定时间

r：返回记录

t：查询时间

aI：平均锁定时间

ar：平均返回记录数

at：平均查询时间

t：即为返回前面多少条的数据

g：后边搭配一个正则匹配模式，大小写不敏感的。

常见例子：

// 得到返回记录集最多的 10 个SQLmysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log// 得到访问次数最多的 10 个SQLmysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log// 得到按照时间排序的前 10 条里面含有左连接的查询语句mysqldumpslow -s t -t -g "left join" /var/lib/mysql/atguigu-slow.log// 另外建立在使用这些命令时结合 | 和 more 使用，否则有可能出现爆屏情况mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

批量数据脚本

例如往表中插入 1000W 条数据。

1. 建表

# 新建库create database bigData;use bigData;# 建部门表 deptcreate table dept(id int unsigned primary key auto_increment,deptno mediumint unsigned not null default 0,dname varchar(20) not null default "",loc varchar(13) not null default "")engine=innodb default charset=GBK;# 建员工表 empcreate table emp(id int unsigned primary key auto_increment,empno mediumint unsigned not null default 0,/*编号*/ename varchar(20) not null default "",/*名字*/job varchar(9) not null default "",/*工作*/mgr mediumint unsigned not null default 0,/*上级编号*/hiredate date not null,/*入职时间*/sal decimal(7,2) not null,/*薪水*/comm decimal(7,2) not null,/*红利*/deptno mediumint unsigned not null default 0/*部门编号*/)engine=innodb default charset=gbk;

2. 设置参数 log_bin_trust_function_creators

创建函数，假如报错：This function has none of deterministic…

# 由于开启过慢查询日志，因为我们开启了 bin-log，我们就必须为我们的 function 指定一个参数。show variables like 'log_bin_trust_function_creators';set global log_bin_trust_function_creators=1;# 这样添加了参数以后，如果 mysqld 重启，上述参数又会消失，永久方法：windows：my.ini文件[mysqld]加上log_bin_trust_function_creators=1linux：/etc/my.cnf下my.cnf文件[mysqld]加上log_bin_trust_function_creators=1

3. 创建函数，保证每条数据都不同

随机产生字符串：

DELIMITER $$ CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255) BEGIN 	DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghigklmnopqrstuvwxyzABCDEFGHIGKLMNOPQRSTUVWXYZ'; 	DECLARE return_str VARCHAR(255) DEFAULT ''; 	DECLARE i INT DEFAULT 0; 	WHILE i < n DO 	SET return_str=CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1)); 	SET i = i + 1; 	END WHILE; 	RETURN return_str; END $$# 假如要删除此函数# drop function rand_string;

随机产生部门编号：

DELIMITER $$ CREATE FUNCTION rand_num()RETURNS INT(5)BEGIN 	DECLARE i INT DEFAULT 0;	SET i = FLOOR(100+RAND()*10);	RETURN i;END $$# 假如要删除此函数# drop function rand_num;

4. 创建存储过程

创建往 emp 表中插入数据的存储过程

# 执行存储过程，往 emp 表中添加随机数据DELIMITER $$CREATE PROCEDURE insert_emp(IN START INT(10), IN max_num INT(10))BEGIN	DECLARE i INT DEFAULT 0;	# set autocommit = 0 把 autocommit 设置为0	SET autocommit=0;	REPEAT	SET i = i + 1;	INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES ((START+i), rand_string(6),'SALESMAN',0001,CURDATE(),2000,400,rand_num());	UNTIL i = max_num	END REPEAT;	COMMIT;END $$

创建往 dept 表中插入数据的存储过程

// 执行存储过程，往 dept 表中添加随机数据DELIMITER $$CREATE PROCEDURE insert_dept(IN START INT(10), IN max_num INT(10))BEGIN	DECLARE i INT DEFAULT 0;	SET autocommit = 0;	REPEAT	SET i = i + 1;	INSERT INTO dept (deptno, dname, loc) VALUES ((START+i), rand_string(10), rand_string(8));	UNTIL i = max_num	END REPEAT;	COMMIT;END $$

5. 调用存储过程

先调用 dept

DELIMITER ;CALL insert_dept(100,10);

在调用 emp【机器性能不行的建立插入条数可以适当降低】

DELIMITER ;CALL insert_emp(100001,500000);

在这里插入图片描述

Show Profile

Show Profile 是 mysql 提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于 SQL 的调优的测量

官网：

默认情况下，参数处于关闭状态，并保存最近 15 次的运行结果

Show Profile 分析步骤

是否支持，看看当前版本是否支持

Show variables like 'profiling';

在这里插入图片描述

开启功能，默认关闭，使用前需要开启

set profiling=on;

运行 SQL

查看结果：show profiles;

诊断 SQL：show profile cpu,block io for query 上一步前面的问题 SQL 数字号码；

参数备注：

type：

| ALL --显示所有的开销信息

| BLOCK IO --显示块IO相关开销

| CONTEXT SWITCHES --上下文切换相关开销

| CPU --显示CPU相关开销信息

| IPC --显示发送和接收相关开销信息

| MEMORY --显示内存相关开销信息

| PAGE FAULTS --显示页面错误相关开销信息

| SOURCE --显示和 Source_function，Source_file，Source_line 相关的开销信息

| SWAPS --显示交换次数相关开销的信息

日常开发需要注意的结论

converting HEAP to MyISAM 查询结果太大，内存都不够用了往磁盘上搬了。

Creating tmp table 创建临时表：拷贝数据到临时表；用完再删除。

Copying to tmp table on disk 把内存中临时表复制到磁盘，危险！！！

locked

全局查询日志

千万不要在生产环境开启这个功能。

配置启动

在 mysql 的 my.cnf 中，配置如下：# 开启general_log=1# 记录日志文件的路径general_log_file=/path/logfile# 输出格式log_output=FILE

编码启动

set global general_log=1;set global log_output='TABLE';// 此命令用于查看你编写的 sql 语句select * from mysql.general_log;

永远不要在生产环境开启这个功能。

转载地址：http://hpqwi.baihongyu.com/

你可能感兴趣的文章