查询截取 - 《MySQL》

查询优化-1
查询优化-2
查询优化-3
慢查询日志
批量数据脚本
Show Profile
全局查询日志

查询优化-1

优化策略

永远小表驱动大表。

IN

SELECT * FROM A WHERE id IN (SELECT id FROM B)

等价于：

for select id from B
    for select * from A where A.id = B.id

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

EXISTS

SELECT * FROM A WHERE EXISTS (SELECT 1 FROM B WHERE B.id = A.id)

等价于

for select id from A
    for select * from B = B.id = A.id

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

查询优化-2

优化策略

Order By关键字优化：

Order By子句，尽量使用Index方式排序，避免使用FileSort方式排序。

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

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

案例

SQL脚本：

CREATE TABLE `tb5_a` (
  `age` int NOT NULL,
  `birth` timestamp NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO tb5_a(age, birth) VALUES(17, NOW());
INSERT INTO tb5_a(age, birth) VALUES(18, NOW());
INSERT INTO tb5_a(age, birth) VALUES(19, NOW());
CREATE INDEX idx_a_agebirth ON tb5_a(age, birth);

八个case：

EXPLAIN SELECT * FROM tb5_a WHERE age > 20 ORDER BY age; => 正常
EXPLAIN SELECT * FROM tb5_a WHERE age > 20 ORDER BY birth; => using filesort
EXPLAIN SELECT * FROM tb5_a WHERE age > 20 ORDER BY age, birth; => 正常
EXPLAIN SELECT * FROM tb5_a WHERE age > 20 ORDER BY birth，age; => using filesprt
EXPLAIN SELECT * FROM tb5_a WHERE birth > '2020-3:23 00:00:00' ORDER BY age; => 正常
EXPLAIN SELECT * FROM tb5_a WHERE birth > '2020-3:23 00:00:00' ORDER BY birth; => using filesort
EXPLAIN SELECT * FROM tb5_a WHERE birth > '2020-3:23 00:00:00' ORDER BY age,birth; => 正常
EXPLAIN SELECT * FROM tb5_a WHERE birth > '2020-3:23 00:00:00' ORDER BY birth,age; => using filesort
EXPLAIN SELECT * FROM tb5_a ORDER BY age ASC,birth ASC; => 正常
EXPLAIN SELECT * FROM tb5_a ORDER BY age DESC,birth DESC; => 正常
EXPLAIN SELECT * FROM tb5_a ORDER BY age ASC,birth DESC; => using filesort
EXPLAIN SELECT * FROM tb5_a ORDER BY age DESC,birth ASC; => using filesort

总结

总结：Order By满足两种情况，会使用Index方式排序：

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

注意：如果不在索引列上，filesort有两种算法：

双路排序：两次扫描磁盘获取数据，读取行指针和order by列，对它们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。
单路排序：从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为它把每一行都保存在内存中了。
单路问题：在单路排序中，要占用很多空间，因为需要把所有字段都取出，所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取sort_buffer容量大小，再排，从而导致多次I/O。
优化策略：SQL服务器参数调优，增大sort_buffer_size参数的设置，增大max_length_for_sort_data参数的设置。

提高Order By的速度：

（1）Order By时select *是一个大忌，只查询需要字段，这点非常重要。

（2）尝试提高sort_buffer_size。

（3）尝试提高max_length_for_sort_data。

排序案例

index a_b_c(a, b, c);
-- 使用索引最左前缀
ORDER BY a
ORDER BY a, b
ORDER BY a, b, c
ORDER BY a DESC, b DESC, c DESC
-- WHERE使用索引的最左前缀定义为常量
WHERE a = const ORDER BY b, c
WHERE a = const AND b = const ORDER BY c
WHERE a = const ORDER BY b, c
WHERE a = count AND b > const ORDER BY b, c
-- 不能使用索引进行排序
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  -- 对于排序来说，多个相等条件也是范围查询

查询优化-3

优化策略

Group By关键字优化：

优化策略与Order 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秒以上的语句。
默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。
当然，如果不是调优需要的话，一般不建议启动该参数，因为开启漫长哈讯日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。

配置

查看是否开启慢查询日志和文件位置：

mysql> SHOW VARIABLES LIKE '%SLOW_QUERY_LOG%';
+---------------------+-------------------------------+
| Variable_name       | Value                         |
+---------------------+-------------------------------+
| slow_query_log      | OFF                           |
| slow_query_log_file | /var/lib/mysql/parak-slow.log |
+---------------------+-------------------------------+

开启慢查询日志（只对本次生效，重启后失效）：

SET global slow_query_log = 1;

查看慢查询阈值（默认为10s）：

mysql> SHOW VARIABLES LIKE 'long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

设置慢查询阈值（3s）：

SET global long_query_time = 3;

查看慢查询记录数量：

SHOW global STATUS LIKE '%SLOW_QUERIES%';

永久生效需要修改配置文件/etc/my.cnf，需要在[mysqld]下增加或修改参数：

slow_query_log = 1
slow_query_log_file = /var/lib/mysql/<hostname>-slow.log
long_query_time  = <time>

命令行工具mysqldumpslow

参数：

s：表示按照何种方式排序
c：访问次数
l：锁定时间
r：返回记录
t：查询时间
al：平均锁定时间
ar：平均返回记录数量
at：平均查询时间
t：返回数据数量
g：正则匹配，大小写不敏感

得到返回记录最多的10个SQL：

mysqldumpslow -s r -t 10 /var/lib/mysql/parak-slow.log

得到访问次数最多的10个SQL：

mysqldumpslow -s c -t 10 /var/lib/mysql/parak-slow.log

得到按照时间排序的前10条里面含有左连接的查询语句：

mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/parak-slow.log

建议在使用这些命令时结合 | 和 more使用，防止爆屏。

批量数据脚本

配置

变量log_bin_trust_function_creators：控制是否可以信任存储函数创建者，不会创建写入二进制日志引起不安全事件的存储函数。

查看是否开启：

mysql> SHOW VARIABLES LIKE 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF   |
+---------------------------------+-------+

开启（暂时性开启，永久性依然是修改my.cnf）：

 SET global log_bin_trust_function_creators = 1;

数据准备

CREATE TABLE `tb6_dept` (
  `id` int NOT NULL AUTO_INCREMENT,
  `deptno` mediumint NOT NULL DEFAULT '0',
  `dname` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `loc` varchar(13) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `tb6_emp` (
  `id` int NOT NULL AUTO_INCREMENT,
  `empno` mediumint NOT NULL DEFAULT '0',
  `ename` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `job` varchar(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `mgr` mediumint NOT NULL DEFAULT '0',
  `hiredate` date NOT NULL,
  `sal` decimal(7,2) NOT NULL,
  `comm` decimal(7,2) DEFAULT NULL,
  `deptno` mediumint NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

创建函数

# 随机产生字符串
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
    DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwsyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    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 $$
# 随机产生部门编号
DELIMITER $$
CREATE FUNCTION rand_num() RETURNS INT(5)
BEGIN
    DECLARE i INT DEFAULT 0;
    SET i = FLOOR(100 + RAND() * 10);
    RETURN i;
END $$

创建存储过程

# 向tb6_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 $$
# 向tb6_emp表批量插入
DELIMITER $$
CREATE PROCEDURE insert_emp(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 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_num
    END REPEAT;
    COMMIT;
END $$

批量插入数据

# 向tb6_dept中插入10条数据
DELIMITER ;
CALL insert_dept(100, 10);
# 向tb6_emp中插入50万条数据
DELIMITER ;
CALL insert_emp(100001, 500000);

Show Profile

概述

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

官网：https://dev.mysql.com/doc/refman/8.0/en/show-profile.html

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

配置

查看开启状态：

mysql> SHOW VARIABLES LIKE 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | OFF   |
+---------------+-------+

开启：

SET global profiling = 1;

MySQL8需要关闭依赖检测，即从sql_mode中移除ONLY_FULL_GROUP_BY：

查看sql_mode：

mysql> SELECT @@global.sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@global.sql_mode                                                                                                     |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+

移除ONLY_FULL_GROUP_BY：

SET global @@sql_mode = `STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION`;

相关命令

查看SQL：SHOW PROFILES

诊断SQL：SHOW PROFILE <type ...> FOR QUERY <Query_ID>

可选参数如下：

| 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 死锁

全局查询日志

永久启用

修改my.cnf，设置如下：

# 开启
general_log = 1
# 记录日志文件的路径
general_log_file = /<path>/<name>
# 输出格式
log_output = <.extension>

临时启用

set global general_log = 1;
set global log_output = 'TABLE';

注意

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