3、查询截取分析

小表驱动大表

分析

观察,至少跑1天,看看生产的慢SQL情况。
开启慢查询日志,设置阙值,比如超过5秒钟的就是慢SQL,并将它抓取出来。
explain+慢SQL分析
show profile
运维经理 or DBA,进行SQL数据库服务器的参数调优。

总结

慢查询的开启并捕获
explain+慢SQL分析
show profile查询SQL在Mysql服务器里面的执行细节和生命周期情况
SQL数据库服务器的参数调优

优化原则:小表驱动大表,即小的数据集驱动大的数据集（注意:A表与B表的ID字段应建立索引。）
一句话：大in小e

# A表 > B表, 用IN优于EXISTS。
SELECT * FROM `A` WHERE `id` IN (SELECT `id` FROM `B`);
# 等价于：
SELECT `id` FROM `B`;
SELECT * FROM `A` WHERE `A`.`id` = `B`.`id`;

# A表 < B表, 用EXISTS优于IN。
SELECT * FROM `A` WHERE EXISTS(SELECT 1 FROM `B` WHERE `B` `id` = `A`.`id`);
# 等价于：
SELECT * FROM `A`;
SELECT * FROM `B` WHERE `B`.`id` = `A`.`id`;

注意：使用IN子查询时，需要保证子查询语句索引生效

# A表的id字段索引失效（建议使用join关联子查询的方式）
SELECT * FROM `A` WHERE `id` IN (SELECT `id` FROM `B`);

EXISTS

SELECT... FROM table WHERE EXISTS (subquery)

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

提示

EXISTS (subquery)只返回TRUE或FALSE,因此子查询中的 SELECT * 也可以是 SELECT 1 或 SELECT 'X'，官方说法是实际执行时会忽略SELECT清单,因此没有区别
EXISTS 子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比,如果担忧效率问题,可进行实际检验以确定是否有效率问题。
EXISTS 子查询往往也可以用条件表达式、其他子查询或者JOIN来替代,何种最优需要具体问题具体分析

Order By优化

在 MySQL 中，排序操作可以通过两种方式实现：文件排序（FileSort） 或 扫描有序索引排序（Index Scan）。通过合理设计索引，可以将排序与查询共享相同的索引，从而显著提升性能。以下是详细解析：

MySQL 的两种排序方式

（1）文件排序（FileSort）

原理：当无法使用索引时，MySQL 需要将查询结果存储到临时表中，再通过内存或磁盘进行排序。
适用场景：
- 排序字段未建立索引。
- 查询条件或排序字段与现有索引不匹配。
性能影响：文件排序通常较慢，尤其是数据量大时，可能涉及磁盘 I/O 和归并排序。

（2）有序索引排序（Index Scan）

原理：利用 B+ 树索引的有序特性，直接按索引顺序读取数据，无需额外排序。
适用场景：
- 排序字段有索引。
- 查询条件和排序字段与索引的最左前缀匹配。
性能优势：直接从索引中获取有序数据，避免文件排序，速度更快。

如何通过索引优化排序？

（1）单字段排序

场景：ORDER BY column。
索引设计：为排序字段 column 建立索引。

CREATE INDEX idx_salary ON employees(salary);
SELECT * FROM employees ORDER BY salary; -- 可使用索引排序

（2）联合索引排序

场景：ORDER BY a, b。
索引设计：建立联合索引 (a, b)。

CREATE INDEX idx_name_age ON users(name, age);
SELECT * FROM users ORDER BY name, age; -- 可使用索引排序

（3）带查询条件的排序

场景：WHERE a = 1 ORDER BY b。
索引设计：建立联合索引 (a, b)，确保查询条件和排序字段都在索引中。

CREATE INDEX idx_status_created ON orders(status, created_at);
SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at; -- 可使用索引排序

排序与查询共享索引的条件

要让相同的索引同时服务于查询和排序，需满足以下条件：

查询条件字段和排序字段都在索引中。
排序字段的顺序与索引顺序一致（遵循最左前缀法则）。
避免对索引字段使用函数或表达式（否则索引失效）。

示例：联合索引的高效使用

-- 联合索引 (status, created_at)
CREATE INDEX idx_status_created ON orders(status, created_at);

-- 查询和排序均可命中索引
SELECT * FROM orders 
WHERE status = 'shipped' 
ORDER BY created_at; -- 使用索引排序

无法使用索引排序的典型场景

场景	说明
排序字段未建立索引	`ORDER BY name` 但 `name` 无索引。
排序字段与索引顺序不匹配	联合索引 `(a, b)`，但查询 `ORDER BY b, a`。
查询条件未命中索引的最左前缀	联合索引 `(a, b)`，但查询 `WHERE b = 1 ORDER BY a`。
对索引字段使用函数或表达式	`ORDER BY YEAR(created_at)` 或 `ORDER BY LOWER(name)`。

优化建议

创建覆盖索引：
- 确保查询字段和排序字段都包含在索引中，避免回表查询。

CREATE INDEX idx_name_age ON users(name, age);
SELECT name, age FROM users ORDER BY name; -- 覆盖索引，无需回表

使用 EXPLAIN 分析执行计划：
- 检查 Extra 列是否显示 Using index（表示使用索引排序）或 Using filesort（表示文件排序）。

EXPLAIN SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at;

避免隐式类型转换：
- 确保查询条件与索引字段类型一致，例如 WHERE phone = 13800138000（phone 为 VARCHAR）会导致索引失效，应改为 WHERE phone = '13800138000'。
合理设计联合索引：
- 高频查询字段靠左，排序字段靠右。
- 例如，WHERE a = 1 ORDER BY b 的联合索引应为 (a, b)，而非 (b, a)。

SQL模板示例

order by 默认升序asc
避免使用FileSort方式排序
ORDER BY满足两情况，会使用Index方式排序:

ORDER BY语句使用索引需满足最佳左前缀原则
使用Where子句与Order BY子句条件列组合满足索引最佳左前缀原则

# 创建表结构
DROP TABLE IF EXISTS `order_test`;
CREATE TABLE `order_test`
(
    `id`    INT PRIMARY KEY        NOT NULL AUTO_INCREMENT,
    `name`  VARCHAR(25) DEFAULT '' NOT NULL,
    `age`   INT                    NOT NULL,
    `birth` TIMESTAMP              NOT NULL
) COMMENT = '排序测试';

# 插入数据
INSERT INTO `order_test` (`age`, `name`, `birth`) VALUES (22, 'z3', NOW());
INSERT INTO `order_test` (`age`, `name`, `birth`) VALUES (23, 'l4', NOW());
INSERT INTO `order_test` (`age`, `name`, `birth`) VALUES (24, 'w5', NOW());

# 建索引
CREATE INDEX `idx_A_ageBirth` ON `order_test` (`age`, `birth`);

# 分析
EXPLAIN SELECT * FROM `order_test` WHERE `age` > 20 ORDER BY `age`;
+--+-----------+----------+----------+----+--------------+----+-------+----+----+--------+---------------------------+
|id|select_type|table     |partitions|type|possible_keys |key |key_len|ref |rows|filtered|Extra                      |
+--+-----------+----------+----------+----+--------------+----+-------+----+----+--------+---------------------------+
|1 |SIMPLE     |order_test|NULL      |ALL |idx_A_ageBirth|NULL|NULL   |NULL|3   |100     |Using where; Using filesort|
+--+-----------+----------+----------+----+--------------+----+-------+----+----+--------+---------------------------+

EXPLAIN SELECT * FROM `order_test` WHERE `age` > 20 ORDER BY `age`, `birth`;
+--+-----------+----------+----------+----+--------------+----+-------+----+----+--------+---------------------------+
|id|select_type|table     |partitions|type|possible_keys |key |key_len|ref |rows|filtered|Extra                      |
+--+-----------+----------+----------+----+--------------+----+-------+----+----+--------+---------------------------+
|1 |SIMPLE     |order_test|NULL      |ALL |idx_A_ageBirth|NULL|NULL   |NULL|3   |100     |Using where; Using filesort|
+--+-----------+----------+----------+----+--------------+----+-------+----+----+--------+---------------------------+

EXPLAIN SELECT * FROM `order_test` WHERE `age` > 20 ORDER BY `birth`;
+--+-----------+----------+----------+----+--------------+----+-------+----+----+--------+---------------------------+
|id|select_type|table     |partitions|type|possible_keys |key |key_len|ref |rows|filtered|Extra                      |
+--+-----------+----------+----------+----+--------------+----+-------+----+----+--------+---------------------------+
|1 |SIMPLE     |order_test|NULL      |ALL |idx_A_ageBirth|NULL|NULL   |NULL|3   |100     |Using where; Using filesort|
+--+-----------+----------+----------+----+--------------+----+-------+----+----+--------+---------------------------+

EXPLAIN SELECT * FROM `order_test` WHERE `age` > 20 ORDER BY `birth`, `age`;
+--+-----------+----------+----------+----+--------------+----+-------+----+----+--------+---------------------------+
|id|select_type|table     |partitions|type|possible_keys |key |key_len|ref |rows|filtered|Extra                      |
+--+-----------+----------+----------+----+--------------+----+-------+----+----+--------+---------------------------+
|1 |SIMPLE     |order_test|NULL      |ALL |idx_A_ageBirth|NULL|NULL   |NULL|3   |100     |Using where; Using filesort|
+--+-----------+----------+----------+----+--------------+----+-------+----+----+--------+---------------------------+

EXPLAIN SELECT * FROM `order_test` ORDER BY `birth`;
+--+-----------+----------+----------+----+-------------+----+-------+----+----+--------+--------------+
|id|select_type|table     |partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra         |
+--+-----------+----------+----------+----+-------------+----+-------+----+----+--------+--------------+
|1 |SIMPLE     |order_test|NULL      |ALL |NULL         |NULL|NULL   |NULL|3   |100     |Using filesort|
+--+-----------+----------+----------+----+-------------+----+-------+----+----+--------+--------------+

EXPLAIN SELECT * FROM `order_test` WHERE `birth` > '2016-01-28 00:00:00' ORDER BY `birth`;
+--+-----------+----------+----------+----+-------------+----+-------+----+----+--------+---------------------------+
|id|select_type|table     |partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra                      |
+--+-----------+----------+----------+----+-------------+----+-------+----+----+--------+---------------------------+
|1 |SIMPLE     |order_test|NULL      |ALL |NULL         |NULL|NULL   |NULL|3   |33.33   |Using where; Using filesort|
+--+-----------+----------+----------+----+-------------+----+-------+----+----+--------+---------------------------+

EXPLAIN SELECT * FROM `order_test` WHERE `birth` > '2016-01-28 00:00:00' ORDER BY `age`;
+--+-----------+----------+----------+----+-------------+----+-------+----+----+--------+---------------------------+
|id|select_type|table     |partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra                      |
+--+-----------+----------+----------+----+-------------+----+-------+----+----+--------+---------------------------+
|1 |SIMPLE     |order_test|NULL      |ALL |NULL         |NULL|NULL   |NULL|3   |33.33   |Using where; Using filesort|
+--+-----------+----------+----------+----+-------------+----+-------+----+----+--------+---------------------------+

EXPLAIN SELECT * FROM `order_test` ORDER BY `age` ASC, `birth` DESC;
+--+-----------+----------+----------+----+-------------+----+-------+----+----+--------+--------------+
|id|select_type|table     |partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra         |
+--+-----------+----------+----------+----+-------------+----+-------+----+----+--------+--------------+
|1 |SIMPLE     |order_test|NULL      |ALL |NULL         |NULL|NULL   |NULL|3   |100     |Using filesort|
+--+-----------+----------+----------+----+-------------+----+-------+----+----+--------+--------------+

order by能使用素引最左前缀

KEY 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使用素引的最左前缀定义为常量,则order by能使用索引

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 = const 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   /* 对于排序来说,多个相等条件也是范围查询 */

文件排序两种算法（FileSort）未使用索引排序

双路排序

MySQL 4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据。
第一次I/O读取行指针和orderby列,对他们进行排序，第二次I/O扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出

简而言之：从磁盘取排序字段,在buffer进行排序,再从磁盘取其他字段。

单路排序

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

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

但是用单路有问题!

在sort buffer中,方法B比方法A要多占用很多空间,因为方法B是把所有字段都取出,所以有可能取出的数据的总大小超出了 sort_buffer 的容量,导致每次只能取sort-buffer容量大小的数据,进行排序(创建tmp文件,多路合并),排完再次取sort_buffer容量大小,再排...从而多次I/O。

原本想省一次I/O操作,反而导致了大量的I\O操作,得不偿失。

优化策略

增大 sort_buffer_size 参数的设置
增大 max_length_for_sort_data 参数的设置【相当于门槛, 大于这个值就不会用单路排序】

提高Order By的速度

order by时select * 是一个大忌只Query需要的字段,这点非常重要。在这里的影响是:
1. 当Query的字段大小总和小于 max_length_for_sort_data 而且排序字段不是TEXT|BLOB类型时,会用改进后的算法——单路排序,否则用老算法多路排序。
2. 两种算法的数据都有可能超出 sort_buffer 的容量,超出之后,会创建tmp文件进行合并排序,导致多次I\O,但是用单路排序算法的风险会更大一些,所以要提高 sort_buffer_size
尝试提高 sort_buffer_size
不管用哪种算法,提高这个参数都会提高效率,当然,要根据系统的能力去提高,因为这个参数是针对每个进程的
尝试提高 max_length_for_sort_data
提高这个参数, 会增加用改进算法的概率,但是如果设的太高,数据总容量超出sort_buffer_size的概率就增大,明显症状是高的磁盘I\O活动和低的处理器使用率

Group By关键字优化

group by实质是先排序后进行分组,遵照索引建的最佳左前缀
当无法使用索引列,增大 max_length_for_sort_data 参数的设置+增大 sort_buffer_size 参数的设置

尽量使用where代替having

提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉.下面两个查询返回相同结果但第二个明显就快了许多.

# 低效 50万数据 0.54 sec
SELECT `employee_name`, AVG(`employee_num`)
FROM `employee`
GROUP BY `employee_name`
HAVING `employee_name` = 'pBOqml'
    OR `employee_name` = 'xZDTKR';
    
# 高效 50万数据 0.08 sec
SELECT `employee_name`, AVG(`employee_num`)
FROM `employee`
WHERE `employee_name` = 'pBOqml'
    OR `employee_name` = 'xZDTKR'
GROUP BY `employee_name`;

慢Sql查询日志

是什么

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%'; # 查看是否开启慢查询日志
+-------------------+------------------------------------------------------+
|Variable_name      |Value                                                 |
+-------------------+------------------------------------------------------+
|slow_query_log     |OFF                                                   | # 默认情况下slow_query_log的值为OFF,表示慢查询日志是禁用的,
|slow_query_log_file|/usr/local/mysql/data/localhost-slow.log              |
+-------------------+------------------------------------------------------+

# 可以通过设置slow_query_log的值来开启
SET GLOBAL slow_query_log = 1; # 开启慢查询日志
SET GLOBAL slow_query_log = 0; # 关闭慢查询日志

注意

使用 set global slow_query_log=1 开启了慢查询日志只对当前数据库生效,

如果MySQL重启后则会失效。如果要永久生效,就必须修改配置文件 my.cnf 文件 (其它系统变量也是如此)

关于慢查询的参数slow_query_log_file ,它指定慢查询日志文件的存放路径,系统默认会给一个缺省的文件host_name_slow.log (如果没有指定参数slow_query_log_file的话)

那么开启了慢查询日志后,什么样的SQL才会记录到慢查询日志里面呢?

SHOW VARIABLES LIKE 'long_query_time%'; # 查询慢的阙值时间
+---------------+---------+
|Variable_name  |Value    |
+---------------+---------+
|long_query_time|10.000000| # 这个是由参数long_query_time控制,默认情况下long_query_time的值为10秒,
+---------------+---------+

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

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

设置慢的阙值时间

SET GLOBAL long_query_time = 3; # 设置慢查询阈值
SHOW VARIABLES LIKE 'long_query_time%'; # 查询慢的阙值时间
+---------------+---------+
|Variable_name  |Value    |
+---------------+---------+
|long_query_time|10.000000|
+---------------+---------+

为什么设置后看不出变化?

需要重新连接或新开一个会话修改值才生效

SHOW VARIABLES LIKE 'long_query_time%'; # 看不出当时修改的值
SHOW GLOBAL VARIABLES LIKE 'long_query_time'; # 可以看出修改后的值

设置long_query_time时间为5秒 (立即生效)

SET long_query_time = 3; # 修改会话变量
+---------------+--------+
|Variable_name  |Value   |
+---------------+--------+
|long_query_time|3.000000|
+---------------+--------+

记录慢SQL并分析

SELECT SLEEP(5); # 等待5秒执行,模拟生产环境慢sql
SHOW GLOBAL STATUS LIKE '%slow_queries%'; # 查询当前系统中有多少条慢查询记录
+-------------+-----+
|Variable_name|Value|
+-------------+-----+
|Slow_queries |1    |
+-------------+-----+

查看慢查询日志文件

[root@localhost]~# cat /usr/local/mysql/data/localhost-slow.log
/usr/local/mysql/bin/mysqld, Version: 5.7.24 (MySQL Server (GPL)). started with:
Tcp port: 3306  Unix socket: /usr/local/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 2021-06-02T07:28:24.401588Z
# User@Host: root[root] @  [192.168.0.52]  Id:    38
# Query_time: 5.039469  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
use big;
SET timestamp=1622618904;
/* ApplicationName=PhpStorm 2021.1.1 */ SELECT SLEEP(5);

配置版

【mysqld】下配置:

slow_query_log=1; # 开启查询日志
# 慢查询日志存储路径
slow_query_log_file=/var/lib/mysq/filename(文件名)-slow.log
long_query_time=3; # 慢查询阙值
log_output=FILE # 日志存储形式

日志分析工具mysqlDumpSlow

在生产环境中,如果要手工分析日志,查找、分析SQL,显然是个体力活,
MySQL提供了日志分析工具 mysqlDumpSlow。

查看mysqldumpslow的帮助信息

# s: 是表示按照何种方式排序;
# c:访问次数
# l:锁定时间
# r:返回记录
# t:查询时间
# al:平均锁定时间
# ar:平均返回记录数
# at:平均查询时间
# t:即为返回前面多少条的数据;
# g:后边搭配一个正则匹配模式,大小写不敏感的;

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

命令行执行工具进行分析

[root@localhost]~# mysqldumpslow -s r -t 10 /usr/local/mysql/data/localhost-slow.log 

Reading mysql slow query log from /usr/local/mysql/data/localhost-slow.log
Count: 1  Time=5.04s (5s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@[192.168.0.52]
  /* ApplicationName=PhpStorm N.N.N */ SELECT SLEEP(N)

Died at /usr/local/mysql/bin/mysqldumpslow line 161, <> chunk 1.

批量插入数据脚本

DROP DATABASE IF EXISTS `big`;
CREATE DATABASE `big` DEFAULT CHARACTER SET `utf8mb4` COLLATE `utf8mb4_general_ci`;
USE `big`;
# 建表
CREATE TABLE `department`
(
    `id`              INT(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',
    `department_num`  MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '部门编号',
    `department_name` VARCHAR(25)        NOT NULL DEFAULT '' COMMENT '部门名称',
    `location`        VARCHAR(25)        NOT NULL DEFAULT '' COMMENT '地点'
) ENGINE = INNODB
  DEFAULT CHARSET = `GBK` COMMENT '部门表';

CREATE TABLE `employee`
(
    `id`             INT(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',
    `employee_num`   MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '编号',
    `employee_name`  VARCHAR(25)        NOT NULL COMMENT '名字',
    `job`            VARCHAR(25)        NOT NULL COMMENT '工作',
    `manager`        MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '上级编号',
    `hire_date`      DATE                        DEFAULT NULL COMMENT '入职时间',
    `salary`         DECIMAL(7, 2)      NOT NULL DEFAULT 0 COMMENT '薪水',
    `commission`     DECIMAL(7, 2)      NOT NULL COMMENT '红利',
    `department_num` MEDIUMINT UNSIGNED          DEFAULT 0 COMMENT '部门编号'
) ENGINE = INNODB
  DEFAULT CHARSET `GBK` COMMENT '员工表';

# 结束符设置
DELIMITER $
# 创建随机生成字符串函数，保证每条数据不同
CREATE FUNCTION `rand_string`(`n` INT) RETURNS VARCHAR(255)
BEGIN
    DECLARE `chars_str` VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
    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 $

# 随机生成部门编号
CREATE FUNCTION `rand_num`() RETURNS INT(10)
BEGIN
    DECLARE `i` INT(10) DEFAULT 0;
    SET `i` = FLOOR(RAND() * 10 + 100);
    RETURN `i`;
END $

# 创建往department表插入数据的存储过程
CREATE PROCEDURE `insert_department`(IN `START` INT(10), IN `max` INT(10))
    COMMENT '创建往department表插入数据的存储过程'
BEGIN
    DECLARE `i` INT DEFAULT 0;
    SET autocommit = 0;
    REPEAT
        INSERT INTO `department` (`department_num`, `department_name`, `location`)
        VALUES ((`START` + `i`), `rand_string`(10), `rand_string`(8));
        SET `i` = `i` + 1;
    UNTIL `i` = `max`
        END REPEAT;
    COMMIT;
END $

# 创建往employee表插入数据的存储过程
CREATE PROCEDURE `insert_employee`(IN `START` INT(10), IN `max` INT(10))
    COMMENT '创建往employee表插入数据的存储过程'
BEGIN
    DECLARE `i` INT DEFAULT 0;
    SET autocommit = 0;
    REPEAT
        INSERT INTO `employee` (`employee_num`, `employee_name`, `job`, `manager`, `hire_date`, `salary`, `commission`,
                                `department_num`)
        VALUES ((`START` + `i`), `rand_string`(6), 'SALESMAN', 001, CURDATE(), 2000, 400, `rand_num`());
        SET `i` = `i` + 1;
    UNTIL `i` = `max`
        END REPEAT;
    COMMIT;
END $


# 删除函数
DROP FUNCTION `rand_string`;
DROP FUNCTION `rand_num`;

# 删除存储过程
DROP PROCEDURE `insert_department`;
DROP PROCEDURE `insert_employee`;

# 执行存储过程
CALL `insert_department`(0, 5000000); # 添加500万条数据
CALL `insert_employee`(0, 5000000); # 添加500万条数据

设置参数 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=1

linux环境 etc/my.cnf

[mysqld]
log_bin_trust_function_creators=1

Show Profile(sql化验单)

Mysql Commands#Show Profile

是什么

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

官网

http://dev.mysql.com/doc/refman/5.5/en/show-profile.html

默认情况

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

分析步骤

1.查看

SHOW VARIABLES LIKE '%profiling%'; # 默认关闭，使用前需开启
+----------------------+-----+
|Variable_name         |Value|
+----------------------+-----+
|have_profiling        |YES  |
|profiling             |OFF  | # 默认关闭
|profiling_history_size|15   | # 默认保存最近15次sql
+----------------------+-----+

2.开启

SET profiling = ON;
SHOW VARIABLES LIKE 'profiling';
+-------------+-----+
|Variable_name|Value|
+-------------+-----+
|profiling    |ON   |
+-------------+-----+

3.运行

SELECT * FROM `employee` GROUP BY `id` % 10 LIMIT 150000;
SELECT * FROM `employee` GROUP BY `id` % 20 ORDER BY 5;

4.查看结果并诊断

参数备注

# 显示所有的开销信息
ALL              
# 显示块IO相关开销
BLOCK IO         
# 上下文切换相关开销
CONTEXT SWITCHES 
# 显示CPU相关开销信息
CPU              
# 显示发送和接收相关开销信息
IPC              
# 显示内存相关开销信息
MEMORY           
# 显示页面错误相关开销信息
PAGE FAULTS      
# 显示和source_function, source_file, source_line相关的开销信息
SOURCE           
# 显示交换次数相关开销的信息
SWAPS     


SHOW PROFILES; # 查看结果
+----------+------------+----------------------------------------------------------+
| Query_ID | Duration   | Query                                                    |
+----------+------------+----------------------------------------------------------+
|        1 | 0.01384875 | select @@version_comment limit 1                         |
|        2 | 0.00013000 | SELECT DATABASE()                                        |
|        3 | 0.00081800 | show databases                                           |
|        4 | 0.00047200 | show tables                                              |
|        5 | 4.04759600 | SELECT * FROM `employee` GROUP BY `id` % 10 LIMIT 150000 |
|        6 | 4.12445200 | SELECT * FROM `employee` GROUP BY `id` % 20 ORDER BY 5   |
|        7 | 3.98758550 | SELECT * FROM `employee` GROUP BY `id` % 10 LIMIT 150000 |
|        8 | 4.15598075 | SELECT * FROM `employee` GROUP BY `id` % 20 ORDER BY 5   |
+----------+------------+----------------------------------------------------------+

SHOW PROFILE CPU, BLOCK IO FOR QUERY 5; # 诊断sql（参数5是Query ID）
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000052 | 0.000046 |   0.000001 |            0 |             0 |
| checking permissions | 0.000005 | 0.000005 |   0.000000 |            0 |             0 |
| Opening tables       | 0.000016 | 0.000016 |   0.000000 |            0 |             0 |
| init                 | 0.000019 | 0.000018 |   0.000001 |            0 |             0 |
| System lock          | 0.000006 | 0.000006 |   0.000000 |            0 |             0 |
| optimizing           | 0.000004 | 0.000004 |   0.000000 |            0 |             0 |
| statistics           | 0.000012 | 0.000012 |   0.000000 |            0 |             0 |
| preparing            | 0.000007 | 0.000007 |   0.000000 |            0 |             0 |
| Creating tmp table   | 0.000024 | 0.000023 |   0.000001 |            0 |             0 | # 
| Sorting result       | 0.000003 | 0.000003 |   0.000000 |            0 |             0 |
| executing            | 0.000002 | 0.000002 |   0.000000 |            0 |             0 |
| Sending data         | 4.025431 | 3.692793 |   0.413250 |       513568 |             0 |
| Creating sort index  | 0.000044 | 0.000038 |   0.000001 |            0 |             0 |
| end                  | 0.000004 | 0.000004 |   0.000000 |            0 |             0 |
| query end            | 0.000008 | 0.000007 |   0.000000 |            0 |             0 |
| removing tmp table   | 0.000004 | 0.000005 |   0.000000 |            0 |             0 | # 
| query end            | 0.000003 | 0.000002 |   0.000000 |            0 |             0 |
| closing tables       | 0.000008 | 0.000008 |   0.000000 |            0 |             0 |
| freeing items        | 0.000040 | 0.000000 |   0.000042 |            0 |             0 |
| cleaning up          | 0.021904 | 0.020480 |   0.001448 |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+

5.注意的结论

converting HEAP to MyISAM：查询结果太大,内存都不够用了往磁盘上搬了。
Creating tmp table: 创建临时表(1.拷贝数据到临时表 2.用完再删除)
Copying to tmp table on disk: 把内存中临时表复制到磁盘,危险! ! !
locked

全局查询日志(sql小抄)

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

配置启用

在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语句将会记录到mysql库里的general_log表
SELECT * FROM `mysql`.`general_log`; # 查看
+----------------------------+---------------------------+-----------+-----------+--------------+-------------------------------------+
| event_time                 | user_host                 | thread_id | server_id | command_type | argument                            |
+----------------------------+---------------------------+-----------+-----------+--------------+-------------------------------------+
| 2021-06-02 15:50:55.732710 | root[root] @ localhost [] |        45 |         1 | Query        | SELECT * FROM `mysql`.`general_log` |
+----------------------------+---------------------------+-----------+-----------+--------------+-------------------------------------+
1 row in set (0.00 sec)