查询截取分析【MySQL高级篇4】

转x33g5p2x  于2021-12-09 转载在 Mysql  
字(17.4k)|赞(0)|评价(0)|浏览(610)

1、查询优化

1.1、SQL 分析基本操作

  1. 观察一天查看生产环境 SQL 慢的情况；
  2. 开启慢查询日志，设置阈值，如超过 5s 的就是慢查询，并抓取 SQL；
  3. 使用 EXPLAIN + SQL 语句 查看慢SQL；
  4. 使用show profile进行进一步分析；
  5. 运维或DBA 进行 SQL 服务器参数调优；

1.2、小表驱动大表

  • 使用小表驱动大表，类似于多层循环，最外层最好循环次数要少，否则会消耗很长时间。
  • 同理当 MySQL 进行 CRUD 操作时会进行锁表操作时应当使用小的结果集驱动大的结果集。

第一种情况

  1. select * from A where id in (select id from B) 
  2. 等价于
  3. for select id from B
  4. for select * from A where A.id = B.id
  • 当 B表的数据集必须小于 A表的数据集时，使用 in 优于 exists

第二种情况

  1. select * from A where exists (select 1 from B where B.id = A.id) 
  2. 等价于
  3. for select * from A
  4. for select * from B where B.id = A.id
  • 当 A表数据集小于 B表数据集时，使用 exists 优于 in。

exists讲解

exists 语法 select... from table where exists (subquery)

  • 可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE、FALSE）来决定结果是否保留。
  1. exists(subquery)只返回TRUE、FALSE，因此子查询中的条件字段可以是 SELECT 1 或 SELECT X，官方的解释是在实际执行时会忽略 SELECT清单，因此没有区别；
  2. exists子查询的实际执行过程可能经过了优化而不是逐条对比；
  3. exists子查询也可以使用条件表达式、其他子查询或者 JOIN来替代，需要根据具体问题具体分析；

1.3、order by 关键字优化

1. 怎样会产生Using FileSort

  • 如果你建好了索引，并且按照一定的顺序进行了排序，然而后面的order by 排序未按照前面的索引顺序进行排序或者把前面的几个索引不用上直接使用了后面的索引都会导致Using FileSort。

2. ORDER BY简介

  • MySQL支持两种排序，FileSort和Index，Index效率高，它指MySQL扫描索引本身完成排序。FileSort方式效率较低。
  • ORDER BY子句尽量使用index方式进行排序，避免使用FileSort方式排序。
  • ORDER BY满足两种情况，会使用Index方式排序。如下：
  1. ORDER BY语句使用索引最左前列。
  2. 使用WHERE子句与ORDER BY子句条件列组合满足索引最左前列法则。

3. Case

  1. - 第一条
  2. mysql> explain select * from tblA where age > 20 order by age;
  3. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
  4. | id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                    |
  5. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
  6. |  1 | SIMPLE      | tblA  | NULL       | index | idx_ageBirth  | idx_ageBirth | 9       | NULL |    3 |   100.00 | Using where; Using index |
  7. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
  8. 1 row in set, 1 warning (0.00 sec)
  10. - 第二条
  11. mysql> explain select * from tblA where birth > '2016-01-28 00:00:00' order by age;
  12. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
  13. | id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                    |
  14. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
  15. |  1 | SIMPLE      | tblA  | NULL       | index | NULL          | idx_ageBirth | 9       | NULL |    3 |    33.33 | Using where; Using index |
  16. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
  17. 1 row in set, 1 warning (0.00 sec)
  19. -第三条
  20. mysql> explain select * from tblA where age > 20 order by age,birth;
  21. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
  22. | id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                    |
  23. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
  24. |  1 | SIMPLE      | tblA  | NULL       | index | idx_ageBirth  | idx_ageBirth | 9       | NULL |    3 |   100.00 | Using where; Using index |
  25. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
  26. 1 row in set, 1 warning (0.00 sec)
  1. -第一条
  2. mysql> explain select * from tblA where age > 20 order by birth;
  3. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+------------------------------------------+
  4. | id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                                    |
  5. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+------------------------------------------+
  6. |  1 | SIMPLE      | tblA  | NULL       | index | idx_ageBirth  | idx_ageBirth | 9       | NULL |    3 |   100.00 | Using where; Using index; Using filesort |
  7. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+------------------------------------------+
  8. 1 row in set, 1 warning (0.00 sec)
  10. -第二条
  11. mysql> explain select * from tblA order by birth;
  12. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------------+
  13. | id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                       |
  14. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------------+
  15. |  1 | SIMPLE      | tblA  | NULL       | index | NULL          | idx_ageBirth | 9       | NULL |    3 |   100.00 | Using index; Using filesort |
  16. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------------+
  17. 1 row in set, 1 warning (0.00 sec)
  19. -第三条
  20. mysql> explain select * from tblA where birth > '2016-01-28 00:00:00' order by birth;
  21. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+------------------------------------------+
  22. | id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                                    |
  23. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+------------------------------------------+
  24. |  1 | SIMPLE      | tblA  | NULL       | index | NULL          | idx_ageBirth | 9       | NULL |    3 |    33.33 | Using where; Using index; Using filesort |
  25. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+------------------------------------------+
  26. 1 row in set, 1 warning (0.00 sec)
  28. -第四条
  29. mysql> explain select * from tblA where age > 20 order by birth,age;
  30. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+------------------------------------------+
  31. | id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                                    |
  32. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+------------------------------------------+
  33. |  1 | SIMPLE      | tblA  | NULL       | index | idx_ageBirth  | idx_ageBirth | 9       | NULL |    3 |   100.00 | Using where; Using index; Using filesort |
  34. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+------------------------------------------+
  35. 1 row in set, 1 warning (0.00 sec)
  1. mysql> explain select * from tblA order by age ASC, birth DESC;
  2. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------------+
  3. | id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                       |
  4. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------------+
  5. |  1 | SIMPLE      | tblA  | NULL       | index | NULL          | idx_ageBirth | 9       | NULL |    3 |   100.00 | Using index; Using filesort |
  6. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------------+
  7. 1 row in set, 1 warning (0.00 sec)

4. 怎样避免出现Using FileSort

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

5. FileSort的两种算法

  1. 双路排序
  • MySQL4.1之前是使用双路排序，字面意思是两次扫描磁盘，最终得到数据，读取行指针和order by列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。
  • 从磁盘取出排序字段，在buffer进行排序，再从磁盘取其他字段。
  • 取一批数据，要对磁盘进行两次扫描，众所周知，I\O是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序。
  1. 单路排序

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

6. 单路排序出现的问题

  1. 出现的问题

在sort_buffer中，单路排序比双路排序要占用很多空间，因为单路排序是把所有字段都取出，所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取出sort_buffer容量大小，再排…从而多次I\O。本来想省一次I\O操作，反而导致了大量的I\O操作，反而得不偿失。

  1. 解决方法
  • 尝试提高sort_buffer_size：不管使用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的。
  • 尝试提高max_length_for_sort_data：提高这个参数，会增加改进算法的概率，但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I\O活动和低的处理器使用率。
  • ORDER BY时select * 是一个大忌

7.小结

  • MySQL两种排序方式：文件排序或扫描有序索引排序
  • MySQL能为排序与查询使用相同的索引

举例说明

  1. KEY a_b_c(a,b,c)
  3. -- order by 能使用索引最左前缀
  4. ORDER BY a 
  5. ORDER BY a,b
  6. ORDER BY a,b,c
  7. ORDER BY a DESC,b DESC,c DESC
  9. -- 如果where使用索引的最左前缀定义为常量，则order by 能使用索引
  10. where a = const order by b,c
  11. where a = const and b = const order by c
  12. where a = const order by b,c
  13. where a = const and b > const order by b,c
  15. -- 不能使用索引进行排序
  16. order by a ASC,b DESC,c DESC -- 排序不一致
  17. where g = const order by b,c -- 丢失a索引
  18. where a = const order by c -- 丢失b索引
  19. where a = const order by a,d -- d不是索引的一部分
  20. where a in (...) order by b,c -- 对于排序来说，多个相等的条件也是范围查询。

1.4、group by关键字优化

  1. group by实质是先排序后进行分组，遵照索引建的最佳左前缀法则。
  2. 当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置。
  3. where高于having，能写在where限定的条件就不要去having限定了。

2、慢查询日志

2.1、是什么？

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

2.2、怎么玩？

1.说明

  • 默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。
  • 当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。

2. 查看是否开启及如何开启

  1. --- 查看
  2. show variables like '%slow_query_log%';
  4. -- 查看结果
  5. mysql> show variables like '%slow_query_log%';
  6. +---------------------+-----------------------------------------------------+
  7. | Variable_name       | Value                                               |
  8. +---------------------+-----------------------------------------------------+
  9. | slow_query_log      | OFF                                                 |
  10. | slow_query_log_file | D:\MySQL\mysql-5.7.19\data\LAPTOP-T4PHKJJQ-slow.log |
  11. +---------------------+-----------------------------------------------------+
  12. 2 rows in set, 1 warning (0.00 sec)
  15. --- 开启 使用以下语句开启了慢查询日志只对当前数据库生效，如果MySQL重启后则会失效。
  16. set global slow_query_log = 1;
  18. -- 开启结果
  19. mysql> show variables like '%slow_query_log%';
  20. +---------------------+-----------------------------------------------------+
  21. | Variable_name       | Value                                               |
  22. +---------------------+-----------------------------------------------------+
  23. | slow_query_log      | ON                                                  |
  24. | slow_query_log_file | D:\MySQL\mysql-5.7.19\data\LAPTOP-T4PHKJJQ-slow.log |
  25. +---------------------+-----------------------------------------------------+
  26. 2 rows in set, 1 warning (0.00 sec)
  • 如果想永久生效必须修改配置文件my.cnf（其它系统变量也是如此）。
  • 关于慢查询的参数slow_query_log_file，它指定慢查询日志文件的存放路径，系统默认会给一个缺省的文件host_name-slow.log（如果没有指定参数slow_query_log_file的话）。

3. 开启了慢查询日志之后，什么样的SQL才会记录到慢查询日志里面?

  1. -- 这个是由long_query_time控制，默认情况下是10秒
  2. show variables like 'long_query_time%';
  5. -- 查询结果
  6. mysql> show variables like 'long_query_time%';
  7. +-----------------+-----------+
  8. | Variable_name   | Value     |
  9. +-----------------+-----------+
  10. | long_query_time | 10.000000 |
  11. +-----------------+-----------+
  12. 1 row in set, 1 warning (0.00 sec)
  14. -- 可以使用命令修改，也可以在my.cnf参数里面修改。

4. 设置慢的阀值

  1. -- 设置阀值为3
  2. set global long_query_time = 3;

5. 设置后看不出变化，应该怎么办？

  1. -- 1. 需要重新连接或新开一个会话再使用相关命令才能看到修改值。
  2. mysql> show variables like 'long_query_time%';
  4. -- 2. 
  6. mysql> show global variables like 'long_query_time%';
  8. -- 使用第二种方法的查询结果
  9. mysql> show global variables like 'long_query_time%';
  10. +-----------------+----------+
  11. | Variable_name   | Value    |
  12. +-----------------+----------+
  13. | long_query_time | 3.000000 |
  14. +-----------------+----------+
  15. 1 row in set, 1 warning (0.00 sec)

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

  1. -- 查询命令
  2. show global status like '%slow_queries%';
  4. -- 查询结果
  5. mysql> show global status like '%slow_queries%';
  6. +---------------+-------+
  7. | Variable_name | Value |
  8. +---------------+-------+
  9. | Slow_queries  | 1     |
  10. +---------------+-------+
  11. 1 row in set (0.00 sec)

7. 通过配置文件实现永久修改

my.cnf 文件中找到下面的位置复制以下代码，根据自己的实际情况配置参数和日志输出位置。

  1. [mysqld]
  2. slow_query_log=1 -- 开启/关闭慢查询日志
  3. slow_query_log_file=/var/lib/mysql/xxx-slow.log -- 慢查询日志文件输出位置和名称
  4. long_query_time=3 -- 时间阀值
  5. log_output=FILE -- 慢查询日志输出格式

2.3、日志分析工具mysqldumpslow

  • 这是MySQL提供的日志分析工具mysqldumpslow。

1. 查看mysqldumpslow的帮助信息

参数含义
s表示按照何种方式进行排序
c访问次数
l锁定时间
r返回记录
t查询时间
al平均锁定时间
ar平均返回记录数
at平均查询时间
t返回前面多少条数据
g后面搭配一个正则匹配模式，大小写不敏感

2. 工作常用参考

  1. --获取返回记录集中最多的 10 个 SQL
  2. mysqldumpslow -s r -t 10 /var/lib/mysql/test-slow.log
  4. -- 获取访问次数最多的 10 个 SQL
  5. mysqldumpslow -s c -t 10 /var/lib/mysql/test-slow.log
  7. --获取按照时间排序的前 10 条包含左连接的查询语句
  8. mysqldumpslow -s t 10 -g "left join" /var/lib/mysql/test-slow.log
  10. -- 建议在执行以上命令时结合 | more 使用，防止打印的数据过长
  11. mysqldumpslow -s r -t 10 /var/lib/test-slow.log | more

3、批量数据脚本

3.1、建表

创建dept和emp表

  1. CREATE TABLE `dept` (
  2. `id` INT(11) NOT NULL AUTO_INCREMENT,
  3. `deptName` VARCHAR(30) DEFAULT NULL,
  4. `address` VARCHAR(40) DEFAULT NULL,
  5. ceo INT NULL ,
  6. PRIMARY KEY (`id`)
  7. ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
  8. # 1.1创建员工表
  9. CREATE TABLE `emp` (
  10. `id` INT(11) NOT NULL AUTO_INCREMENT,
  11. `empno` INT NOT NULL ,
  12. `name` VARCHAR(20) DEFAULT NULL,
  13. `age` INT(3) DEFAULT NULL,
  14. `deptId` INT(11) DEFAULT NULL,
  15. PRIMARY KEY (`id`)
  16. ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

3.2、设置参数log_bin_function_creators

  1. -- 查看参数是否开启 
  2. show variables like 'log_bin_trust_function_creators';
  5. -- 查看结果
  6. mysql> show variables like 'log_bin_trust_function_creators';
  7. +---------------------------------+-------+
  8. | Variable_name                   | Value |
  9. +---------------------------------+-------+
  10. | log_bin_trust_function_creators | OFF   |
  11. +---------------------------------+-------+
  12. 1 row in set, 1 warning (0.00 sec)
  14. -- 打开参数 
  15. set global log_bin_trust_function_creators = 1
  18. -- 打开后结果
  19. mysql> show variables like 'log_bin_trust_function_creators';
  20. +---------------------------------+-------+
  21. | Variable_name                   | Value |
  22. +---------------------------------+-------+
  23. | log_bin_trust_function_creators | ON    |
  24. +---------------------------------+-------+
  25. 1 row in set, 1 warning (0.00 sec)
  • 当我们开启二进制日志后，如果变量 log_bin_trust_function_creators的设置为 OFF，则在创建存储函数时会报错 ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)

3.3、创建函数，保证每条数据都不同

1. 创建随机产生字符串函数

  1. DELIMITER $$ -- 修改结束字符，本来是;这样修改后结束字符就变为了$$
  2. CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
  3. BEGIN
  4. -- 定义变量
  5. DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
  6. DECLARE return_str VARCHAR(255) DEFAULT '';
  7. DECLARE i INT DEFAULT 0;
  8. -- 循环开始
  9. WHILE i < n DO
  10. SET return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
  11. SET i = i + 1;
  12. END WHILE;
  13. -- 循环结束
  14. RETURN return_str;-- 返回生成的字符串
  15. END $$

2. 创建随机产生编号函数

  1. DELIMITER $$
  2. CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
  3. BEGIN
  4. -- 声明变量
  5. DECLARE i INT DEFAULT 0;
  6. SET i = FLOOR(from_num +RAND()*(to_num -from_num+1)) ;
  7. RETURN i;-- 返回值
  8. END$$

3. 删除函数

  1. # 删除随机产生字符串的函数
  2. drop function rand_string
  3. # 删除随机产生编号的函数
  4. drop function rand_num;

3.4、创建存储过程

1. 创建想emp表中插入数据的存储过程

  1. DELIMITER $$
  2. -- 存储过程没有返回值
  3. CREATE PROCEDURE insert_emp( START INT , max_num INT )
  4. BEGIN
  5. DECLARE i INT DEFAULT 0;
  6. #set autocommit =0 把autocommit 设置成0
  7. -- 如果不设置为0，它每生成一条数据就会自动提交一次，所以不好，我们设置为统一提交
  8. SET autocommit = 0;
  9. REPEAT -- 循环
  10. SET i = i + 1;
  11. INSERT INTO emp (empno, NAME ,age ,deptid ) VALUES ((START+i) ,rand_string(6) ,rand_num(30,50),rand_num(1,10000));
  12. -- 上面的insert语句调用了字符串函数和随机编号产生函数
  13. UNTIL i = max_num
  14. END REPEAT; -- 循环结束
  15. COMMIT; -- 统一提交
  16. END$$

2. 创建想dept表中插入数据的存储过程

  1. DELIMITER $$
  2. CREATE PROCEDURE `insert_dept`( max_num INT )
  3. BEGIN
  4. DECLARE i INT DEFAULT 0;
  5. SET autocommit = 0;
  6. REPEAT
  7. SET i = i + 1;
  8. INSERT INTO dept ( deptname,address,ceo ) VALUES (rand_string(8),rand_string(10),rand_num(1,500000));
  9. UNTIL i = max_num
  10. END REPEAT;
  11. COMMIT;
  12. END$$

3.5、调用存储过程

1. 调用存储过程向dept表中插入100条数据

  1. DELIMITER ;
  2. CALL insert_dept(100); -- 调用存储过程

2. 调用存储过程向emp表中插入50w条数据

  1. DELIMITER ; -- 重新声明结束符
  2. CALL insert_emp(100000,500000);

4、show profile

4.1、show profile简介

  • 是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量。
  • 默认情况下，参数处于关闭状态，并保存最近15次的运行结果。

4.2、分析步骤

1. 是否支持，看看当前的mysql版本是否支持。

  1. show variables like '%profiling%'
  3. # 查看结果
  4. mysql> show variables like '%profiling%';
  5. +------------------------+-------+
  6. | Variable_name          | Value |
  7. +------------------------+-------+
  8. | have_profiling         | YES   |
  9. | profiling              | OFF   |
  10. | profiling_history_size | 15    |
  11. +------------------------+-------+
  12. 3 rows in set, 1 warning (0.01 sec)

2. 开启功能

  1. set profiling = 1
  2. #默认是关闭，使用前需要开启。
  4. #开启后查看结果
  5. mysql> show variables like '%profiling%';
  6. +------------------------+-------+
  7. | Variable_name          | Value |
  8. +------------------------+-------+
  9. | have_profiling         | YES   |
  10. | profiling              | ON    |
  11. | profiling_history_size | 15    |
  12. +------------------------+-------+
  13. 3 rows in set, 1 warning (0.00 sec)

3. 运行SQL

4. 查看结果

show profile；

  1. mysql> show profiles;
  2. +----------+------------+----------------------------------------------------------+
  3. | Query_ID | Duration   | Query                                                    |
  4. +----------+------------+----------------------------------------------------------+
  5. |        1 | 0.00040950 | show variables like '%profiling%'                        |
  6. |        2 | 0.00006050 | show profies                                             |
  7. |        3 | 0.00008050 | select * from tbl_emp                                    |
  8. |        4 | 0.12290000 | select * from emp group by id limit 150000               |
  9. |        5 | 1.03373300 | select * from emp left join dept on emp.deptid = dept.id |
  10. +----------+------------+----------------------------------------------------------+
  11. 5 rows in set (0.00 sec)

5. 诊断SQL

  1. show profile cpu,block io for query 上一步前面的问题SQL数字号码;
  2. # 显示相关SQL语句的具体信息
  4. # 示例
  5. mysql> show profile cpu,block io for query 3;
  6. +----------------------+----------+----------+------------+--------------+---------------+
  7. | Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
  8. +----------------------+----------+----------+------------+--------------+---------------+
  9. | starting             | 0.000068 | 0.000000 |   0.000000 |         NULL |          NULL |
  10. | checking permissions | 0.000004 | 0.000000 |   0.000000 |         NULL |          NULL |
  11. | checking permissions | 0.000003 | 0.000000 |   0.000000 |         NULL |          NULL |
  12. | Opening tables       | 0.001718 | 0.000000 |   0.000000 |         NULL |          NULL |
  13. | init                 | 0.000023 | 0.000000 |   0.000000 |         NULL |          NULL |
  14. | System lock          | 0.000007 | 0.000000 |   0.000000 |         NULL |          NULL |
  15. | optimizing           | 0.000006 | 0.000000 |   0.000000 |         NULL |          NULL |
  16. | statistics           | 0.000019 | 0.000000 |   0.000000 |         NULL |          NULL |
  17. | preparing            | 0.036385 | 0.000000 |   0.015625 |         NULL |          NULL |
  18. | executing            | 0.000012 | 0.000000 |   0.000000 |         NULL |          NULL |
  19. | Sending data         | 0.000144 | 0.000000 |   0.000000 |         NULL |          NULL |
  20. | end                  | 0.000005 | 0.000000 |   0.000000 |         NULL |          NULL |
  21. | query end            | 0.000016 | 0.000000 |   0.000000 |         NULL |          NULL |
  22. | closing tables       | 0.000011 | 0.000000 |   0.000000 |         NULL |          NULL |
  23. | freeing items        | 0.000090 | 0.000000 |   0.000000 |         NULL |          NULL |
  24. | cleaning up          | 0.000017 | 0.000000 |   0.000000 |         NULL |          NULL |
  25. +----------------------+----------+----------+------------+--------------+---------------+
  26. 16 rows in set, 1 warning (0.00 sec)

status参数信息

参数含义
ALL显示所有的开销信息
BLOCK IO显示块 IO 相关开销信息
CONTEXT SWITCHES显示上下文切换相关开销信息
CPU显示 CPU 相关开销信息
IPC显示发送和接收相关开销信息
MEMORY显示内存相关开销信息
PAGE FAULTS显示页面错误相关开销信息
SOURCE显示和 Source_function，Source_file，Source_file 相关开销信息
SWAPS显示交换次数相关开销信息

6. 日常开发需要注意的结论

  1. converting HEAP to MyISAM： 查询结果过大，内存不够用转而存储到硬盘；
  2. **Create tmp table：**创建临时表，主要作用：拷贝数据到临时表，使用完成后删除临时表；
  3. Copy to tmp table on disk： 将内存中临时表复制到磁盘，严重影响性能；
  4. locked： 锁

5、全局查询日志

5.1、配置启用

在my.cnf文件中设置如下

  1. [mysqld]
  2. # 开启
  3. general_log=1
  4. # 记录日志文件路径
  5. general_log_file=/path/logfile
  6. # 输出格式
  7. log_output=FILE

5.2、编码启用

1. 开启全局慢查询

  1. set global_log=1;
  3. set global_log_output='table';

2. 查看所有的查询记录

  • 开启后所编写的 SQL 语句，将会记录到 MySQL 库中的 general_log 表
  • 使用命令 select * from mysql.general_log查看

5.3、注意事项

不要在生产环境下开启全局查询日志。

版权说明 : 本文为转载文章, 版权归原作者所有 版权申明

原文链接 : https://blog.csdn.net/weixin_56727438/article/details/121723763

内容来源于网络，如有侵权，请联系作者删除！

sqlmysql数据库

相关文章

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新文章

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com