5. 查看 SQL 执行成本：SHOW PROFILE
mysql> show variables like 'profiling'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | profiling | OFF | #当前是关闭状态 +---------------+-------+ 1 row in set (0.01 sec) mysql> set profiling = 'ON';#开启 Query OK, 0 rows affected, 1 warning (0.00 sec)
然后执行相关的查询语句。接着看下当前会话下有哪些profiles
mysql> SELECT * FROM student WHERE stuno = 3455655; //... mysql> SELECT * FROM student WHERE name = 'ZfCwDz'; //... mysql> show profiles; +----------+------------+---------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+---------------------------------------------+ | 1 | 0.00133475 | show variables like 'profiling' | | 2 | 0.00021050 | SELECT * FROM student WHERE stuno = 3455655 | | 3 | 0.00053600 | SELECT DATABASE() | | 4 | 0.01693325 | show databases | | 5 | 0.00375125 | show tables | | 6 | 1.75597875 | SELECT * FROM student WHERE stuno = 3455655 | | 7 | 1.11115150 | SELECT * FROM student WHERE name = 'ZfCwDz' | +----------+------------+---------------------------------------------+ 7 rows in set, 1 warning (0.00 sec)
你能看到当前会话一共有7个查询，如果我们想要查看最近一次查询的开销，可以使用
show profile;
我们也可以查看指定的Query ID的开销，只需要后面跟上 for num。也可以查看不同部分的开销，比如CPU、block.io等
show profile cpu,block io for query 7;
通过如果发现上一条 sql 慢的原因在于执行慢（executing字段耗时多），就可以接着用 Explain 进行分析具体的 sql 语句。等后面我们为其建立索引，就可以大大提高效率了
🔊 show profile的常用查询参数：
① ALL：显示所有的开销信息。
② BLOCK IO：显示块 IO 开销。
③ CONTEXT SWITCHES：上下文切换开销。
④ CPU：显示 CPU 开销信息。
⑤ IPC：显示发送和接收开销信息。
⑥ MEMORY：显示内存开销信息。
⑦ PAGE FAULTS：显示页面错误开销信息。
⑧ SOURCE：显示和 Source_function，Source_file，Source_line 相关的开销信息。
⑨ SWAPS：显示交换次数开销信息。
🎨 日常开发需要注意的结论：
① Coverting Heap to MyISAM：查询结果太大，内存不够，正在往磁盘中迁移
② Creating tmp table：创建临时表，先拷贝数据到临时表，用完再删除临时表
③Coping to tmp table on disk：把内存中临时表复制到磁盘上，警惕！
④ locked
如果在 show profile 的查询结果中，出现了以上4条结果中的任何一条。则sql 语句需要优化
最后，还需要注意：
SHOW PROFILE 命令将被弃用，不过我们可以从 information_schema 中的 profiling 数据表进行查看
6. 分析查询语句：EXPLAIN（重点）
6.1 EXPLAIN 概述
https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
https://dev.mysql.com/doc/refman/8.0/en/explain-output.html
3. 版本情况
MySQL 5.6.3以前只能EXPLAIN SELECT ；MYSQL 5.6.3以后就可以EXPLAIN SELECT，UPDATE，DELETE
在5.7以前的版本中，想要显示partitions 需要使用explain partitions命令；想要显示
filtered 需要使用explain extended 命令。在5.7版本后，默认explain直接显示partitions和
filtered中的信息
注意：EXPLAIN 仅仅是查看执行计划，不会真实的执行 sql
6.2 基本语法
EXPLAIN 或 DESCRIBE语句的语法形式如下：
EXPLAIN SELECT select_options 或者 DESCRIBE SELECT select_options
如果我们想看看某个查询的执行计划的话，可以在具体的查询语句前边加一个EXPLAIN ，就像这样：
mysql> EXPLAIN SELECT 1;
EXPLAIN 语句输出的各个列的作用如下：
在这里把它们都列出来只是为了描述一个轮廓，让大家有一个大致的印象。
6.3 数据准备
1. 建表
CREATE TABLE s1 ( id INT AUTO_INCREMENT, key1 VARCHAR(100), key2 INT, key3 VARCHAR(100), key_part1 VARCHAR(100), key_part2 VARCHAR(100), key_part3 VARCHAR(100), common_field VARCHAR(100), PRIMARY KEY (id), INDEX idx_key1 (key1), UNIQUE INDEX idx_key2 (key2), INDEX idx_key3 (key3), INDEX idx_key_part(key_part1, key_part2, key_part3) ) ENGINE=INNODB CHARSET=utf8;
CREATE TABLE s2 ( id INT AUTO_INCREMENT, key1 VARCHAR(100), key2 INT, key3 VARCHAR(100), key_part1 VARCHAR(100), key_part2 VARCHAR(100), key_part3 VARCHAR(100), common_field VARCHAR(100), PRIMARY KEY (id), INDEX idx_key1 (key1), UNIQUE INDEX idx_key2 (key2), INDEX idx_key3 (key3), INDEX idx_key_part(key_part1, key_part2, key_part3) ) ENGINE=INNODB CHARSET=utf8;
注：建两个表方便联合查询
2. 创建存储函数
DELIMITER CREATE FUNCTION rand_string1(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 DELIMITER ;
创建函数，假如报错，需设置参数 log_bin_trust_function_creators，允许创建函数设置
set global log_bin_trust_function_creators=1; # 不加global只是当前窗口有效。
3. 创建存储过程
创建往 s1 表中插入数据的存储过程：
DELIMITER // CREATE PROCEDURE insert_s1 (IN min_num INT (10),IN max_num INT (10)) BEGIN DECLARE i INT DEFAULT 0; SET autocommit = 0; REPEAT SET i = i + 1; INSERT INTO s1 VALUES( (min_num + i), rand_string1(6), (min_num + 30 * i + 5), rand_string1(6), rand_string1(10), rand_string1(5), rand_string1(10), rand_string1(10)); UNTIL i = max_num END REPEAT; COMMIT; END // DELIMITER ;
创建往 s2 表中插入数据的存储过程：
DELIMITER // CREATE PROCEDURE insert_s2 (IN min_num INT (10),IN max_num INT (10)) BEGIN DECLARE i INT DEFAULT 0; SET autocommit = 0; REPEAT SET i = i + 1; INSERT INTO s2 VALUES((min_num + i), rand_string1(6), (min_num + 30 * i + 5), rand_string1(6), rand_string1(10), rand_string1(5), rand_string1(10), rand_string1(10)); UNTIL i = max_num END REPEAT; COMMIT; END // DELIMITER ;
4. 调用存储过程
s1 表数据的添加：加入 1 万条记录：
CALL insert_s1(10001,10000);
s2 表数据的添加：加入 1 万条记录：
CALL insert_s2(10001,10000);
6.4 EXPLAIN 各列作用
为了让大家有比较好的体验，我们调整了下EXPLAIN输出列的顺序。
6.4.1 table
不论我们的查询语句有多复杂，里边儿包含了多少个表 ，到最后也是需要对每个表进行单表访问的，所以 MySQL 规定 EXPLAIN 语句输出的每条记录都对应着某个单表的访问方法，该条记录的 table 列代表着该表的表名（有时不是真实的表名字，可能是简称）。
EXPLAIN SELECT * FROM s1 INNER JOIN s2;
如下图，一张表对应一个记录。
注：临时表也会有对应的记录，比如我们使用UNION时就会出现临时表
6.4.2 id
例1：下面的查询结果，两个记录似乎id都是1.这是为什么呢？
实际上，在查询语句中每出现一个SELECT关键字，MySQL就会为它分配一个唯一的id ，代表着一次查询。这个id 就是 EXPLAIN语句的第一列。
例2：下面的查询中只有一个SELECT，所以EXPLAIN的结果中也就只有一条id为 1 的记录喽~
例3：下面的查询有两个SELECT，所以EXPLAIN的结果中 会有两条记录，且id分别就是1和2喽~ 。其中 s1被称为驱动表，s2被称为 被驱动表
例4：下面这条SQL有一个坑，请注意！！！
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field = 'a');
两个记录的 id 都是 1，小小的眼睛是否充满了大大的疑惑？
这是因为优化器会对上面的 sql 语句进行优化，将其转换为多表连接，而不是子查询。因为子查询其实是一种嵌套查询的情况，其时间复杂度是 O(n^m)，其中 m 是嵌套的层数，而多表查询的时间复杂度是 O(n*m)
例5：再看看 Union 联合查询的情况。
EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
结果是这样，竟然会出现三张表~ Amazing！
这是因为 Union 是取表的并集，需要建临时表进行去重，因此会有三条记录。可以看到第三条记录的 Extra 就标识了它是一张临时表哦。临时表 id 是 Null。
例6：再看看 Union ALL：
EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2;
产生两条记录，因为它不会去重~
💌小结
- id如果相同，可以认为是一组，从上往下顺序执行
- 在所有组中，id值越大，优先级越高，越先执行
- 关注点：id号每个号码，表示一趟独立的查询, 一个sql的查询趟数越少越好
