11.分析慢SQL的步骤
分析
- 观察，至少跑1天，看看生产的慢
SQL情况 - 开启慢查询日志，设置阈值，比如超过5秒钟的就是慢
SQL，并将它抓取出来 explain+ 慢SQL分析show Profile- 运维经理 or DBA，进行
MySQL数据库服务器的参数调优
总结（大纲）
- 慢查询的开启并捕获
explain+ 慢SQL分析show Profile查询SQL在MySQL数据库中的执行细节和生命周期情况MySQL数据库服务器的参数调优12.查询优化
12.1.小表驱动大表
优化原则：对于MySQL数据库而言，永远都是小表驱动大表 ```java /**
- 举个例子：可以使用嵌套的for循环来理解小表驱动大表。
- 以下两个循环结果都是一样的，但是对于MySQL来说不一样，
- 第一种可以理解为，和MySQL建立5次连接每次查询1000次。
第二种可以理解为，和MySQL建立1000次连接每次查询5次。 */ for(int i = 1; i <= 5; i ++){ for(int j = 1; j <= 1000; j++){
} } // ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ for(int i = 1; i <= 1000; i ++){ for(int j = 1; j <= 5; j++){
} }
`**IN**`**和**`**EXISTS**`sql优化原则：小表驱动大表，即小的数据集驱动大的数据集
IN适合B表比A表数据小的情况
SELECT * FROM A WHERE id IN (SELECT id FROM B)
等价于
for SELECT id FROM B for SELECT * FROM A WHERE A.id = B.id
EXISTS适合B表比A表数据大的情况
SELECT * FROM A WHERE EXISTS (SELECT 1 FROM B WHERE B.id = A.id);
等价于
for SELECT FROM A for SELECT FROM B WHERE B.id = A.id
`**EXISTS**`- 语法：`SELECT...FROM tab WHERE EXISTS(subquery);`该语法可以理解为该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（`true`或是`false`）来决定主查询的数据结果是否得以保留<br />**提示**- `EXISTS(subquery)`子查询只返回`true`或者`false`，因此子查询中的`SELECT *`可以是`SELECT 1 OR SELECT X`，它们并没有区别- `EXISTS(subquery)`子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担心效率问题，可进行实际检验以确定是否有效率问题- `EXISTS(subquery)`子查询往往也可以用条件表达式，其他子查询或者`JOIN`替代，何种最优需要具体问题具体分析<a name="d970df52"></a>## 12.2.ORDER BY优化> 数据准备```sqlCREATE TABLE talA (id integer primary key auto_increment,age INT,birth TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);INSERT INTO talA(age) VALUES(18);INSERT INTO talA(age) VALUES(19);INSERT INTO talA(age) VALUES(20);INSERT INTO talA(age) VALUES(21);INSERT INTO talA(age) VALUES(22);INSERT INTO talA(age) VALUES(23);INSERT INTO talA(age) VALUES(24);INSERT INTO talA(age) VALUES(25);-- 创建索引CREATE INDEX idx_talA_age_birth ON `talA`(`age`, `birth`);
案例
/* 1.使用索引进行排序了 不会产生Using filesort */EXPLAIN SELECT * FROM `talA` WHERE `age` > 20 ORDER BY `age`;/* 2.使用索引进行排序了 不会产生Using filesort */EXPLAIN SELECT * FROM `talA` WHERE `age` > 20 ORDER BY `age`,`birth`;/* 3.没有使用索引进行排序 产生了Using filesort */EXPLAIN SELECT * FROM `talA` WHERE `age` > 20 ORDER BY `birth`;/* 4.没有使用索引进行排序 产生了Using filesort */EXPLAIN SELECT * FROM `talA` WHERE `age` > 20 ORDER BY `birth`,`age`;/* 5.没有使用索引进行排序 产生了Using filesort */EXPLAIN SELECT * FROM `talA` ORDER BY `birth`;/* 6.没有使用索引进行排序 产生了Using filesort */EXPLAIN SELECT * FROM `talA` WHERE `birth` > '2020-08-04 07:42:21' ORDER BY `birth`;/* 7.使用索引进行排序了 不会产生Using filesort */EXPLAIN SELECT * FROM `talA` WHERE `birth` > '2020-08-04 07:42:21' ORDER BY `age`;/* 8.没有使用索引进行排序 产生了Using filesort */EXPLAIN SELECT * FROM `talA` ORDER BY `age` ASC, `birth` DESC;
ORDER BY子句，尽量使用索引排序，避免使用Using filesort排序MySQL支持两种方式的排序，FileSort和Index，Index的效率高，它指MySQL扫描索引本身完成排序。FileSort方式效率较低
ORDER BY满足两情况，会使用Index方式排序
**ORDER BY**语句使用索引最左前列- 使用
**WHERE**子句与**ORDER BY**子句条件列组合满足索引最左前列
结论：尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀原则
如果不在索引列上，File Sort有两种算法：MySQL就要启动双路排序算法和单路排序算法
- 双路排序算法：
MySQL4.1之前使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和ORDER BY列，対他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。一句话，从磁盘取排序字段，在**buffer**中进行排序，再从磁盘取其他字段
取一批数据，要对磁盘进行两次扫描，众所周知，IO是很耗时的，所以在MySQL4.1之后，出现了改进的算法，就是单路排序算法
- 单路排序算法：从磁盘读取查询需要的所有列，按照
ORDER BY列在buffer対它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为它把每一行都保存在内存中了
由于单路排序算法是后出的，总体而言效率好过双路排序算法
但是单路排序算法有问题：如果SortBuffer缓冲区太小，导致从磁盘中读取所有的列不能完全保存在SortBuffer缓冲区中，这时候单路复用算法就会出现问题，反而性能不如双路复用算法
单路复用算法的优化策略
- 增大
sort_buffer_size参数的设置 - 增大
max_length_for_sort_data参数的设置
提高ORDER BY排序的速度
ORDER BY时使用SELECT *是大忌，查什么字段就写什么字段，这点非常重要。在这里的影响是：- 当查询的字段大小总和小于
max_length_for_sort_data而且排序字段不是TEXT|BLOB类型时，会使用单路排序算法，否则使用多路排序算法 - 两种排序算法的数据都有可能超出
sort_buffer缓冲区的容量，超出之后，会创建tmp临时文件进行合并排序，导致多次IO，但是单路排序算法的风险会更大一些，所以要增大sort_buffer_size参数的设置
- 当查询的字段大小总和小于
- 尝试提高
sort_buffer_size：不管使用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的 尝试提高
max_length_for_sort_data：提高这个参数，会增加用单路排序算法的概率。但是如果设置的太高，数据总容量sort_buffer_size的概率就增大，明显症状是高的磁盘IO活动和低的处理器使用率12.3.GORUP BY优化
GROUP BY实质是先排序后进行分组，遵照索引建的最佳左前缀- 当无法使用索引列时，会使用
Using filesort进行排序，增大max_length_for_sort_data参数的设置和增大sort_buffer_size参数的设置，会提高性能 WHERE执行顺序高于HAVING，能写在WHERE限定条件里的就不要写在HAVING中了12.4.总结
为排序使用索引
MySQL两种排序方式：Using filesort和Using Index扫描有序索引排序MySQL能为排序与查询使用相同的索引，创建的索引既可以用于排序也可以用于查询 ```sql / 创建a b c三个字段的索引 / idx_table_a_b_c(a, b, c)
/ 1.ORDER BY 能使用索引最左前缀 / ORDER BY a; ORDER BY a, b; ORDER BY a, b, c; ORDER BY a DESC, b DESC, c DESC;
/ 2.如果WHERE子句中使用索引的最左前缀定义为常量，则ORDER BY能使用索引 / WHERE a = ‘Ringo’ ORDER BY b, c; WHERE a = ‘Ringo’ AND b = ‘Tangs’ ORDER BY c; WHERE a = ‘Ringo’ AND b > 2000 ORDER BY b, c;
/ 3.不能使用索引进行排序 / ORDER BY a ASC, b DESC, c DESC; / 排序不一致 / WHERE g = const ORDER BY b, c; / 丢失a字段索引 / WHERE a = const ORDER BY c; / 丢失b字段索引 / WHERE a = const ORDER BY a, d; / d字段不是索引的一部分 / WHERE a IN (…) ORDER BY b, c; / 对于排序来说，多个相等条件(a=1 or a=2)也是范围查询 /
<a name="82fbe3ba"></a># 13.慢查询日志<a name="c9b7b006"></a>## 13.1.基本介绍**慢查询日志是什么**？- `MySQL`的慢查询日志是`MySQL`提供的**一种日志记录**，它用来记录在`MySQL`中响应时间超过阈值的语句，具体指运行时间超过 `**long_query_time**` 值的`SQL`，则会被记录到慢查询日志中- `long_query_time` 的默认值为10，意思是运行10秒以上的语句- 由慢查询日志来查看哪些`SQL`超出了我们的最大忍耐时间值，比如一条SQL执行超过5秒钟，我们就算慢`SQL`，希望能收集超过5秒钟的`SQL`，结合之前 `explain` 进行全面分析特别说明<br />**默认情况下，**`**MySQL**`**数据库没有开启慢查询日志，**需要我们手动来设置这个参数<br />**当然，如果不是调优需要的话，一般不建议启动该参数**，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件> 查看慢查询日志是否开以及如何开启- 查看慢查询日志是否开启：`**SHOW VARIABLES LIKE '%slow_query_log%';**`- 开启慢查询日志：`**SET GLOBAL slow_query_log = 1;**`。**使用该方法开启**`**MySQL**`**的慢查询日志只对当前数据库生效，如果**`**MySQL**`**重启后会失效**```shell# 1、查看慢查询日志是否开启mysql> SHOW VARIABLES LIKE '%slow_query_log%';+---------------------+---------------------------------------+| Variable_name | Value |+---------------------+---------------------------------------+| slow_query_log | OFF || slow_query_log_file | /var/lib/mysql/cess-Ubuntu20-slow.log |+---------------------+---------------------------------------+2 rows in set (0.01 sec)# 2、开启慢查询日志mysql> SET GLOBAL slow_query_log = 1;Query OK, 0 rows affected (0.00 sec)
如果要使慢查询日志永久开启，需要修改my.cnf文件，在[mysqld]下增加修改参数
# my.cnf[mysqld]# 1.这个是开启慢查询。注意ON需要大写slow_query_log=ON# 2.这个是存储慢查询的日志文件。这个文件不存在的话，需要自己创建slow_query_log_file=/var/lib/mysql/slow.log
开启了慢查询日志后，什么样的SQL才会被记录到慢查询日志里面呢？
这个是由参数long_query_time控制的，默认情况下long_query_time的值为10秒
MySQL中查看long_query_time的时间**SHOW VARIABLES LIKE 'long_query_time%';**
# 查看long_query_time 默认是10秒# 只有SQL的执行时间>10才会被记录mysql> SHOW VARIABLES LIKE 'long_query_time%';+-----------------+-----------+| Variable_name | Value |+-----------------+-----------+| long_query_time | 10.000000 |+-----------------+-----------+1 row in set (0.00 sec)mysql> set global long_query_time=3;Query OK, 0 rows affected (0.00 sec)
修改long_query_time的时间，需要在my.cnf修改配置文件
[mysqld]# 这个是设置慢查询的时间，我设置的为1秒long_query_time=1
修改后需要重新连接或新开一个会话才能看到修改值
查新慢查询日志的总记录条数：**SHOW GLOBAL STATUS LIKE '%Slow_queries%';**
mysql> select sleep(4);+----------+| sleep(4) |+----------+| 0 |+----------+1 row in set (4.00 sec)mysql> SHOW GLOBAL STATUS LIKE '%Slow_queries%';+---------------+-------+| Variable_name | Value |+---------------+-------+| Slow_queries | 1 |+---------------+-------+1 row in set (0.00 sec)
打开cat /var/lib/mysql/slow.log
/usr/sbin/mysqld, Version: 5.7.34 (MySQL Community Server (GPL)). started with:Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sockTime Id Command Argument# Time: 2021-10-27T14:03:33.201362Z# User@Host: root[root] @ DESKTOP-2JU9EV3.lan [192.168.199.132] Id: 11# Query_time: 4.001308 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0use db01;SET timestamp=1635343413;/* ApplicationName=IntelliJ IDEA 2021.1.2 */ select sleep(4);
13.2.日志分析工具
日志分析工具 **mysqldumpslow**：在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具 mysqldumpslow
# 1、mysqldumpslow --help 来查看mysqldumpslow的帮助信息root@1dcb5644392c:/usr/bin# mysqldumpslow --helpUsage: mysqldumpslow [ OPTS... ] [ LOGS... ]Parse and summarize the MySQL slow query log. Options are--verbose verbose--debug debug--help write this text to standard output-v verbose-d debug-s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default # 按照何种方式排序al: average lock time # 平均锁定时间ar: average rows sent # 平均返回记录数at: average query time # 平均查询时间c: count # 访问次数l: lock time # 锁定时间r: rows sent # 返回记录t: query time # 查询时间-r reverse the sort order (largest last instead of first)-t NUM just show the top n queries # 返回前面多少条记录-a don't abstract all numbers to N and strings to 'S'-n NUM abstract numbers with at least n digits within names-g PATTERN grep: only consider stmts that include this string-h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),default is '*', i.e. match all-i NAME name of server instance (if using mysql.server startup script)-l don't subtract lock time from total time# 2、 案例# 2.1、得到返回记录集最多的10个SQLmysqldumpslow -s r -t 10 /var/lib/mysql/slow.log# 2.2、得到访问次数最多的10个SQLmysqldumpslow -s c -t 10 /var/lib/mysql/slow.log# 2.3、得到按照时间排序的前10条里面含有左连接的查询语句mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/slow.log# 2.4、另外建议使用这些命令时结合|和more使用，否则出现爆屏的情况mysqldumpslow -s r -t 10 /var/lib/mysql/slow.log | more
14.批量插入数据脚本
14.1.环境准备
- 建表
SQL``sql /* 1.dept表 */ CREATE TABLE dept (idint(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',deptnoint(10) unsigned NOT NULL DEFAULT '0' COMMENT '部门id',dnamevarchar(20) NOT NULL DEFAULT '' COMMENT '部门名字',locvarchar(13) NOT NULL DEFAULT '' COMMENT '部门地址', PRIMARY KEY (id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=’部门表’
/ 2.emp表 /
CREATE TABLE emp (
id int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘主键’,
empno int(10) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘员工编号’,
ename varchar(20) NOT NULL DEFAULT ‘’ COMMENT ‘员工名字’,
job varchar(9) NOT NULL DEFAULT ‘’ COMMENT ‘职位’,
mgr int(10) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘上级编号’,
hiredata date NOT NULL COMMENT ‘入职时间’,
sal decimal(7,2) NOT NULL COMMENT ‘薪水’,
comm decimal(7,2) NOT NULL COMMENT ‘分红’,
deptno int(10) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘部门id’,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=’员工表’
2. 由于开启过慢查询日志，开启了`bin-log`，我们就必须为`function`指定一个参数，否则使用函数会报错```shell# 在mysql中设置# log_bin_trust_function_creators 默认是关闭的 需要手动开启mysql> SHOW VARIABLES LIKE 'log_bin_trust_function_creators';+---------------------------------+-------+| Variable_name | Value |+---------------------------------+-------+| log_bin_trust_function_creators | OFF |+---------------------------------+-------+1 row in set (0.00 sec)mysql> SET GLOBAL log_bin_trust_function_creators=1;Query OK, 0 rows affected (0.00 sec)
上述修改方式MySQL重启后会失败，在my.cnf配置文件下修改永久有效
[mysqld]log_bin_trust_function_creators=ON
14.2.创建函数
# 1、函数：随机产生字符串DELIMITER $$CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)BEGINDECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwsyzABCDEFGHIJKLMNOPQRSTUVWXYZ';DECLARE return_str VARCHAR(255) DEFAULT '';DECLARE i INT DEFAULT 0;WHILE i < n DOSET return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));SET i = i + 1;END WHILE;RETURN return_str;END $$# 2、函数：随机产生部门编号DELIMITER $$CREATE FUNCTION rand_num() RETURNS INT(5)BEGINDECLARE i INT DEFAULT 0;SET i = FLOOR(100 + RAND() * 10);RETURN i;END $$
14.3.创建存储过程
# 1、函数：向dept表批量插入DELIMITER $$CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10))BEGINDECLARE i INT DEFAULT 0;SET autocommit = 0;REPEATSET i = i + 1;INSERT INTO dept(deptno,dname,loc) VALUES((START + i),rand_string(10),rand_string(8));UNTIL i = max_numEND REPEAT;COMMIT;END $$# 2、函数：向emp表批量插入DELIMITER $$CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))BEGINDECLARE i INT DEFAULT 0;SET autocommit = 0;REPEATSET i = i + 1;INSERT INTO emp(empno,ename,job,mgr,hiredata,sal,comm,deptno) VALUES((START + i),rand_string(6),'SALESMAN',0001,CURDATE(),2000,400,rand_num());UNTIL i = max_numEND REPEAT;COMMIT;END $$
14.4.调用存储过程
# 1、调用存储过程向dept表插入10个部门。DELIMITER ;CALL insert_dept(100,10);# 2、调用存储过程向emp表插入50万条数据。DELIMITER ;CALL insert_emp(100001,500000);
15.Show Profile
**Show Profile**MySQL提供可以用来分析当前会话中语句执行的资源消耗情况
可以用于SQL的调优的测量。默认情况下，参数处于关闭状态，并保存最近15次的运行结果
分析步骤
是否支持，看看当前的
MySQL版本是否支持# 查看Show Profile功能是否开启mysql> SHOW VARIABLES LIKE 'profiling';+---------------+-------+| Variable_name | Value |+---------------+-------+| profiling | OFF |+---------------+-------+1 row in set (0.00 sec)
开启
Show Profile功能，默认是关闭的，使用前需要开启**SET profiling=ON;**# 开启Show Profile功能mysql> SET profiling=ON;Query OK, 0 rows affected, 1 warning (0.00 sec)
运行
SQL``sql SELECT * FROMempGROUP BYid`%10 LIMIT 150000;
SELECT * FROM emp GROUP BY id%20 ORDER BY 5;
4. 查看结果，执行`**SHOW PROFILES;**``Duration`：持续时间```shellmysql> SHOW PROFILES;+----------+------------+---------------------------------------------------+| Query_ID | Duration | Query |+----------+------------+---------------------------------------------------+| 1 | 0.00156100 | SHOW VARIABLES LIKE 'profiling' || 2 | 0.56296725 | SELECT * FROM `emp` GROUP BY `id`%10 LIMIT 150000 || 3 | 0.52105825 | SELECT * FROM `emp` GROUP BY `id`%10 LIMIT 150000 || 4 | 0.51279775 | SELECT * FROM `emp` GROUP BY `id`%20 ORDER BY 5 |+----------+------------+---------------------------------------------------+4 rows in set, 1 warning (0.00 sec)
- 诊断SQL，
**SHOW PROFILE cpu,block io FOR QUERY Query_ID;**# 这里的3是第四步中的Query_ID# 可以在SHOW PROFILE中看到一条SQL中完整的生命周期mysql> SHOW PROFILE cpu,block io FOR QUERY 3;+----------------------+----------+----------+------------+--------------+---------------+| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |+----------------------+----------+----------+------------+--------------+---------------+| starting | 0.000097 | 0.000090 | 0.000002 | 0 | 0 || checking permissions | 0.000010 | 0.000009 | 0.000000 | 0 | 0 || Opening tables | 0.000039 | 0.000058 | 0.000000 | 0 | 0 || init | 0.000046 | 0.000046 | 0.000000 | 0 | 0 || System lock | 0.000011 | 0.000000 | 0.000000 | 0 | 0 || optimizing | 0.000005 | 0.000000 | 0.000000 | 0 | 0 || statistics | 0.000023 | 0.000037 | 0.000000 | 0 | 0 || preparing | 0.000014 | 0.000000 | 0.000000 | 0 | 0 || Creating tmp table | 0.000041 | 0.000053 | 0.000000 | 0 | 0 || Sorting result | 0.000005 | 0.000000 | 0.000000 | 0 | 0 || executing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 || Sending data | 0.520620 | 0.516267 | 0.000000 | 0 | 0 || Creating sort index | 0.000060 | 0.000051 | 0.000000 | 0 | 0 || end | 0.000006 | 0.000000 | 0.000000 | 0 | 0 || query end | 0.000011 | 0.000000 | 0.000000 | 0 | 0 || removing tmp table | 0.000006 | 0.000000 | 0.000000 | 0 | 0 || query end | 0.000004 | 0.000000 | 0.000000 | 0 | 0 || closing tables | 0.000009 | 0.000000 | 0.000000 | 0 | 0 || freeing items | 0.000032 | 0.000064 | 0.000000 | 0 | 0 || cleaning up | 0.000019 | 0.000000 | 0.000000 | 0 | 0 |+----------------------+----------+----------+------------+--------------+---------------+20 rows in set, 1 warning (0.00 sec)
Show Profile查询参数备注
ALL：显示所有的开销信息**BLOCK IO**：显示块IO相关开销（通用）CONTEXT SWITCHES：上下文切换相关开销**CPU**：显示CPU相关开销信息（通用）IPC：显示发送和接收相关开销信息MEMORY：显示内存相关开销信息PAGE FAULTS：显示页面错误相关开销信息SOURCE：显示和Source_functionSWAPS：显示交换次数相关开销的信息
6、Show Profile查询列表，日常开发需要注意的结论
**converting HEAP to MyISAM**：查询结果太大，内存都不够用了，往磁盘上搬了**Creating tmp table**：创建临时表（拷贝数据到临时表，用完再删除），非常耗费数据库性能**Copying to tmp table on disk**：把内存中的临时表复制到磁盘，危险！！！**locked**：死锁16.全局查询日志
只在测试环境下才可以用 ```sql 在mysql的my.cnf中，设置如下开启
general_log=1
记录日志文件的路径
general_log_file=/path/logfile
输出格式
log_output=FILE
```sqlset global general_log=1;set global log_output='TABLE';# 此后编写的sql语句，将会记录到mysql库里的general_log表，可以用下面的命令查看select * from mysql.general_log;
若有收获，就点个赞吧
0 人点赞
