第3章 MySQL高级查询截取分析

2022/4/9 19:19:22

编程Tag： mysql 排序查询截取 10 order 0.000000

本文主要是介绍第3章 MySQL高级查询截取分析，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

1.分析慢SQL的步骤

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

总结(大纲)
1. 慢查询的开启并捕获
2. explain +慢SQL分析
3. show Profile 查询 SQL 在 MySQL 数据库中的执行细节和生命周期情况
4. MySQL 数据库服务器的参数调优

2.查询优化

2.1.小表驱动大表

优化原则: 对于 MySQL 数据库而言, 永远都是小表驱动大表

/**
* 举个例子：可以使用嵌套的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

# 优化原则：小表驱动大表，即小的数据集驱动大的数据集

# 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 ) 来决定主查询的数据结果是否得以保留

提示
● EXISTS(subquery) 子查询只返回 true 或者 false ，因此子查询中的 SELECT * 可以是 SELECT 1 OR SELECT X，它们并没有区别
● EXISTS(subquery) 子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担心效率问题，可进行实际检验以确定是否有效率问题
● EXISTS(subquery) 子查询往往也可以用条件表达式，其他子查询或者J0IN替代，何种最优需要具体问题具体分析

2.2.ORDER BY优化

数据准备

CREATE 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 就要启动双路排序算法和单路排序算法

1.双路排序算法:MySQL 4.1之前使用双路排序,字面意思就是两次扫描磁盘,最终得到数据，读取行指针和ORDER BY列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。一句话，从磁盘取排序字段，在 buffer 中进行排序,再从磁盘取其他宇段

取一批数据，要对磁盘进行两次扫描，众所周知，IO 是很耗时的，所以在MySQL 4.1之后，出现了改进的算法，就是单路排序算法

2.单路排序算法:从磁盘读取查询需要的所有列，按照 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 活动和低的处理器使用率

2.3.GORUP BY优化

● GROUP BY 实质是先排序后进行分组，遵照索引建的最佳左前缀
● 当无法使用索引列时，会使用 Using filesort 进行排序，增大max_length_for_sort_data 参数的设置和增大sort_ _buffer_ size 参数的设置，会提高性能
● WHERE 执行顺序高于 HAVING ，能写在 WHERE 限定条件里的就不要写在 HAVING 中了

2.4.总结

为排序使用索引
● MySQL两种排序方式: Using filesort 和 Using Index 扫描有序索引排序
● MySQL 能为排序与查询使用相同的索引，创建的索引既可以用于排序也可以用于查询

/* 创建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)也是范围查询 */

3.慢查询日志.

3.1.基本介绍

慢查询日志是什么?
　　● MySQL 的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值的语句,具体指运行时间超过 long_query_time 值的 SQL，则会被记录到慢查询日志中
　　● long_query_time 的默认值为10，意思是运行10秒以上的语句
　　● 由慢查询日志来查看哪些 SQL 超出了我们的最大忍耐时间值，比如一条SQL执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒钟的 SQL，结合之前 explain 进行全面分析

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

查看慢查询日志是否开以及如何开启
　　● 查看慢查询日志是否开启: SHOW VARIABLES LIKE '%s1ow_query_log%' ;
　　● 开启慢查询日志: SET GLOBAL slow_query_log = 1;。使用该方法开启 MySQL 的慢查询日志只对当前数据库生效,如果 MySQL 重启后失效

# 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/ib/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.sock
Time                 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: 0
use db01;
SET timestamp=1635343413;
/* ApplicationName=IntelliJ IDEA 2021.1.2 */ select sleep(4);

3.2.日志分析工具

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

# 1、mysqldumpslow --help 来查看mysqldumpslow的帮助信息
root@1dcb5644392c:/usr/bin# mysqldumpslow --help
Usage: 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个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/slow.log
 
# 2.2、得到访问次数最多的10个SQL
mysqldumpslow -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

4.批量插入数据脚本

4.1.环境准备

1.建表SQL

/* 1.dept表 */
CREATE TABLE dept (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `deptno` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '部门id',
  `dname` varchar(20) NOT NULL DEFAULT '' COMMENT '部门名字',
  `loc` varchar(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 指定一个参数，否则使用函数会报错

# 在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

4.2.创建函数

# 1、函数：随机产生字符串
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 $$

# 2、函数：随机产生部门编号
DELIMITER $$
CREATE FUNCTION rand_num() RETURNS INT(5)
BEGIN
    DECLARE i INT DEFAULT 0;
    SET i = FLOOR(100 + RAND() * 10);
    RETURN i;
END $$

4.3.创建存储过程

# 1、函数：向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 $$

# 2、函数：向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 $$

4.4.调用存储过程

# 1、调用存储过程向dept表插入10个部门。
DELIMITER ;
CALL insert_dept(100,10);

# 2、调用存储过程向emp表插入50万条数据。
DELIMITER ;
CALL insert_emp(100001,500000);

5.Show Profile

Show Profile
　　MySQL 提供可以用来分析当前会话中语句执行的资源消耗情况
　　可以用于 SQL 的调优的测量。默认情况下，参数处于关闭状态，并保存最近15次的运行结果
分析步骤
1.是否支持，看看当前的 MySQL 版本是否支持

# 查看Show Profile功能是否开启
mysql> SHOW VARIABLES LIKE 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

2.开启 Show Profile 功能，默认是关闭的，使用前需要开启 SET profiling=ON;

# 开启Show Profile功能
mysql> SET profiling=ON;
Query OK, 0 rows affected, 1 warning (0.00 sec)

3.运行SQL

SELECT * FROM `emp` GROUP BY `id`%10 LIMIT 150000;

SELECT * FROM `emp` GROUP BY `id`%20 ORDER BY 5;

4.查看结果,执行 SHOW PROFILES;
　　Duration：持续时间

mysql> 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)

5.诊断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 :显示块I0相关开销 (通用)
　　● CONTEXT SWITCHES : 上下文切换相关开销
　　● CPU : 显示CPU相关开销信息 (通用)
　　● IPC : 显示发送和接收相关开销信息
　　● MEMORY : 显示内存相关开销信息
　　● PAGE FAULTS : 显示页面错误相关开销信息
　　● SOURCE : 显示和Source_function
　　● SWAPS : 显示交换次数相关开销的信息

6、 Show Profile 查询列表，日常开发需要注意的结论
　　● converting HEAP to MyISAM : 查询结果太大，内存都不够用了，往磁盘上搬了
　　● Creating tmp table : 创建临时表(拷贝数据到临时表，用完再删除)，非常耗费数据库性能
　　● Copying to tmp table on disk : 把内存中的临时表复制到磁盘,危险!!!
　　● locked : 死锁

6.全局查询日志

只在测试环境下才可以用

在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;

这篇关于第3章 MySQL高级查询截取分析的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！

第3章 MySQL高级 查询截取分析