查询截取分析

----分析—
1、观察，至少跑1天，看看生产的慢SQL情况。
2、开启慢查询日志，设置阈值，比如超过5秒钟的就是慢SQL，并将它抓取出来
3、explain + 慢SQL分析
4、show profile 查询sql在MySQL服务器里面的执行细节和生命周期情况
5、运维经理 or dba 进行sql数据库服务器的参数调优。

查询优化
永远小表驱动大表

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

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  )

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

注意：A表与B表的ID字段应建索引

order by排序优化

ORDER BY 子句，尽量使用Index方式排序，避免使用FileSort方式排序
建表sql

CREATE TABLE `tb_A` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `birth` datetime DEFAULT NULL COMMENT '生日',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;  

INSERT INTO `data_study`.`tb_A`(`id`, `age`, `birth`) VALUES (1, 22, '2019-01-28 18:07:40');
INSERT INTO `data_study`.`tb_A`(`id`, `age`, `birth`) VALUES (2, 23, '2019-01-28 18:07:51');
INSERT INTO `data_study`.`tb_A`(`id`, `age`, `birth`) VALUES (3, 24, '2019-01-28 18:08:00');  

create index idx_A_age_birth on tb_A(age,birth);

mysql> show index from tb_A;
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tb_A  |          0 | PRIMARY         |            1 | id          | A         |           2 | NULL     | NULL   |      | BTREE      |         |               |
| tb_A  |          1 | idx_A_age_birth |            1 | age         | A         |           3 | NULL     | NULL   | YES  | BTREE      |         |               |
| tb_A  |          1 | idx_A_age_birth |            2 | birth       | A         |           3 | NULL     | NULL   | YES  | BTREE      |         |               |
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.15 sec)

没有出现filesort

mysql> explain select * from tb_A where age > 20 order by age;
+----+-------------+-------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys   | key             | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | tb_A  | NULL       | index | idx_A_age_birth | idx_A_age_birth | 11      | NULL |    3 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+
1 row in set (0.15 sec)  

mysql> explain select * from tb_A where age > 20 order by age,birth;
+----+-------------+-------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys   | key             | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | tb_A  | NULL       | index | idx_A_age_birth | idx_A_age_birth | 11      | NULL |    3 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+
1 row in set (0.15 sec)

出现了filesort

mysql> explain select * from tb_A where age > 20 order by birth;
+----+-------------+-------+------------+-------+-----------------+-----------------+---------+------+------+----------+------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys   | key             | key_len | ref  | rows | filtered | Extra                                    |
+----+-------------+-------+------------+-------+-----------------+-----------------+---------+------+------+----------+------------------------------------------+
|  1 | SIMPLE      | tb_A  | NULL       | index | idx_A_age_birth | idx_A_age_birth | 11      | NULL |    3 |   100.00 | Using where; Using index; Using filesort |
+----+-------------+-------+------------+-------+-----------------+-----------------+---------+------+------+----------+------------------------------------------+
1 row in set (0.15 sec)  

mysql> explain select * from tb_A where age > 20 order by birth,age;
+----+-------------+-------+------------+-------+-----------------+-----------------+---------+------+------+----------+------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys   | key             | key_len | ref  | rows | filtered | Extra                                    |
+----+-------------+-------+------------+-------+-----------------+-----------------+---------+------+------+----------+------------------------------------------+
|  1 | SIMPLE      | tb_A  | NULL       | index | idx_A_age_birth | idx_A_age_birth | 11      | NULL |    3 |   100.00 | Using where; Using index; Using filesort |
+----+-------------+-------+------------+-------+-----------------+-----------------+---------+------+------+----------+------------------------------------------+
1 row in set (0.16 sec)  

mysql> explain select * from tb_A  order by birth;
+----+-------------+-------+------------+-------+---------------+-----------------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys | key             | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------+------------+-------+---------------+-----------------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | tb_A  | NULL       | index | NULL          | idx_A_age_birth | 11      | NULL |    3 |   100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+-----------------+---------+------+------+----------+-----------------------------+
1 row in set (0.15 sec)  

mysql> explain select * from tb_A where birth >'2019-01-28 18:07:40' order by birth;
+----+-------------+-------+------------+-------+---------------+-----------------+---------+------+------+----------+------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key             | key_len | ref  | rows | filtered | Extra                                    |
+----+-------------+-------+------------+-------+---------------+-----------------+---------+------+------+----------+------------------------------------------+
|  1 | SIMPLE      | tb_A  | NULL       | index | NULL          | idx_A_age_birth | 11      | NULL |    3 |    33.33 | Using where; Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+-----------------+---------+------+------+----------+------------------------------------------+
1 row in set (0.17 sec)

没有出现filesort

mysql> explain select * from tb_A where birth >'2019-01-28 18:07:40' order by age;
+----+-------------+-------+------------+-------+---------------+-----------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key             | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-----------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | tb_A  | NULL       | index | NULL          | idx_A_age_birth | 11      | NULL |    3 |    33.33 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-----------------+---------+------+------+----------+--------------------------+
1 row in set (0.18 sec)

出现了filesort

mysql> explain select * from tb_A order by age ASC ,birth DESC;
+----+-------------+-------+------------+-------+---------------+-----------------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys | key             | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------+------------+-------+---------------+-----------------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | tb_A  | NULL       | index | NULL          | idx_A_age_birth | 11      | NULL |    3 |   100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+-----------------+---------+------+------+----------+-----------------------------+
1 row in set (0.16 sec)

MySQL支持两种方式的排序，FileSort和Index，Index效率高，它指MySQL扫描索引本身完成排序。FileSort方式效率低。

Order By满足两种情况，会使用Index方式排序：
① order by语句使用索引最左前列
② 使用where子句与order by子句条件列组合满足索引最左前列

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

---

如果不在索引列上，filesort有两种算法：
mysql就要启动双路排序和单路排序
双路排序：
单路排序：现在用的

优化策略：
增大sort_buffer_size参数的设置
增大max_length_for_sort_data参数的设置

1.Order by时使用select * 是一个大忌只query需要的字段，这点非常重要。在这里的影响是：
1.1当query的字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型时，会用改进后的算法--单路排序，否则用老算法--多路排序。  
1.2这两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O,但是用单路排序算法的风险会更大一些，所以要提高sort_buffer_size.  

2.尝试提高sort_buffer_size  
不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的。  

3.尝试提高max_length_for_sort_data  
提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总容量超出sort_buffer_size的高绿就增大，明显症状是高的磁盘I/O活动和低的处理器使用率

小总结： 为排序使用索引

索引 ： idx_table_a_b_c  

order by 能使用索引最左前缀  
- 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 /*对排序来说，多个相等条件也是范围查询*/

group 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的默认值为10，意思是运行10秒以上的语句。
由他来看哪些SQL超出了我们的最大忍耐时间值，比如一条SQL执行超过5秒，我们就算慢SQL，希望能收集超过5秒的SQL，结合之前explain进行全面分析。

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

#查看是否开启慢查询日志
mysql> show variables like '%slow_query_log%';
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_query_log      | OFF                                  |
| slow_query_log_file | /var/lib/mysql/cc9f87c4ed75-slow.log |
+---------------------+--------------------------------------+
2 rows in set (36.19 sec)  

#开启慢查询日志
mysql> set global slow_query_log=1;
Query OK, 0 rows affected (0.20 sec)
 
mysql> show variables like '%slow_query_log%';
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_query_log      | ON                                   |
| slow_query_log_file | /var/lib/mysql/cc9f87c4ed75-slow.log |
+---------------------+--------------------------------------+
2 rows in set (0.14 sec)

这种方法只对当前数据库生效，且重启mysql后失效,永久有效需要修改配置文件

显示默认设置的时间
mysql> show variables like '%long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.18 sec)  
设置慢查询时间为3秒  
mysql> set global long_query_time=3;
Query OK, 0 rows affected (0.17 sec)
关闭当前回会话，再打开才生效  


显示有多少条慢查询sql 
mysql> show global status like '%slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 3     |
+---------------+-------+
1 row in set (0.07 sec)

日志分析工具mysqldumpslow

---

批量数据脚本
建表sql

CREATE TABLE `dept` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `deptno` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `dname` varchar(20) NOT NULL DEFAULT '',
  `loc` varchar(13) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `emp` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `empno` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '编号',
  `ename` varchar(20) NOT NULL DEFAULT '' COMMENT '名字',
  `job` varchar(9) NOT NULL DEFAULT '' COMMENT '工作',
  `mgr` mediumint(9) NOT NULL DEFAULT '0' COMMENT '上级编号',
  `hiredate` datetime NOT NULL COMMENT '入职时间',
  `sal` decimal(7,2) NOT NULL COMMENT '薪水',
  `comm` decimal(7,2) NOT NULL COMMENT '红利',
  `deptno` mediumint(9) NOT NULL DEFAULT '0' COMMENT '部门编号',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工表';

需要开启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.25 sec)

mysql> set global log_bin_trust_function_creators=1;
Query OK, 0 rows affected (0.15 sec)
 
mysql> show variables like '%log_bin_trust_function_creators%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | ON    |
+---------------------------------+-------+
1 row in set (0.14 sec)

生成随机字符串函数

DELIMITER $$
CREATE FUNCTION rand_string (n INT) RETURNS VARCHAR (255) 
BEGIN
	DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
	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 $$

新建插入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,hiredate,sal,comm,deptno)VALUES((START+i),rand_string(6),'SALESMAN',0001,CURDATE(),2000,400,rand_num());
UNTIL i=max_num
END REPEAT;
COMMIT;
END $$

新建插入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 $$

调用insert_dept函数

mysql> DELIMITER ;
mysql> CALL insert_dept(100,10);
Query OK, 0 rows affected (0.02 sec)

调用insert_emp函数

mysql> CALL insert_emp(100001,500000);
Query OK, 0 rows affected (3 min 25.08 sec)

---

show profile

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

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

分析步骤：
1.是否支持，看看当前的mysql版本是否支持

mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | OFF   |
+---------------+-------+
1 row in set (0.09 sec)

2.开启功能，默认是关闭，使用前需要开启

mysql> set profiling=on;
Query OK, 0 rows affected (0.07 sec)
 
mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | ON    |
+---------------+-------+
1 row in set (0.12 sec)

3.运行SQL

this is incompatible with sql_mode=only_full_group_by
mysql> select id%10, count(*) from emp group by id%10 limit 150000;

4.查看结果，show profiles

mysql> show profiles;
+----------+------------+-------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                           |
+----------+------------+-------------------------------------------------------------------------------------------------+
|        1 | 0.00174150 | show variables like 'profiling'                                                                 |
|        2 | 0.00041225 | select * from tb_emp                                                                            |
|        3 | 0.02150975 | select * from tb_emp e inner join tb_dept d on e.deptId = d.id                                  |
|        4 | 0.00056375 | select * from tb_emp e inner join tb_dept d on e.deptId = d.id                                  |
|        5 | 0.00047600 | select * from tb_emp e inner join tb_dept d on e.deptId = d.id                                  |
|        6 | 0.00069025 | select * from tb_emp e left join tb_dept d on e.deptId = d.id                                   |
|        7 | 0.00035400 | select * from emp group by id%10 limit 150000                                                   |
|        8 | 0.00013125 | select id count(*) from emp group by id%10 limit 150000                                         |
|        9 | 0.00038150 | select id, count(*) from emp group by id%10 limit 150000                                        |
|       10 | 0.39150450 | select id%10, count(*) from emp group by id%10 limit 150000                                     |
|       11 | 0.00034300 | select id%10, empno,ename,job,mgr,hiredate,sal,comm,deptno from emp group by id%10 limit 150000 |
|       12 | 0.38402125 | select id%10, count(*) from emp group by id%10 limit 150000                                     |
|       13 | 0.00028150 | select id%20, count(*) from emp group by id%20 order by 5                                       |
+----------+------------+-------------------------------------------------------------------------------------------------+
13 rows in set (0.10 sec)

5.诊断SQL，show profile cpu,block io for query 上一步前面的问题SQL数字号码；

mysql> show profile cpu,block io for query 10;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000098 | 0.000029 | 0.000064   |            0 |             0 |
| checking permissions | 0.000015 | 0.000004 | 0.000009   |            0 |             0 |
| Opening tables       | 0.000027 | 0.000008 | 0.000018   |            0 |             0 |
| init                 | 0.000030 | 0.000010 | 0.000021   |            0 |             0 |
| System lock          | 0.000018 | 0.000007 | 0.000016   |            0 |             0 |
| optimizing           | 0.000016 | 0.000004 | 0.000007   |            0 |             0 |
| statistics           | 0.000026 | 0.000008 | 0.000018   |            0 |             0 |
| preparing            | 0.000017 | 0.000005 | 0.000012   |            0 |             0 |
| Creating tmp table   | 0.000037 | 0.000011 | 0.000025   |            0 |             0 |
| Sorting result       | 0.000010 | 0.000003 | 0.000006   |            0 |             0 |
| executing            | 0.000008 | 0.000003 | 0.000006   |            0 |             0 |
| Sending data         | 0.390947 | 0.371116 | 0.000000   |            0 |             0 |
| Creating sort index  | 0.000064 | 0.000053 | 0.000000   |            0 |             0 |
| end                  | 0.000011 | 0.000010 | 0.000000   |            0 |             0 |
| query end            | 0.000015 | 0.000016 | 0.000000   |            0 |             0 |
| removing tmp table   | 0.000013 | 0.000013 | 0.000000   |            0 |             0 |
| query end            | 0.000009 | 0.000009 | 0.000000   |            0 |             0 |
| closing tables       | 0.000014 | 0.000013 | 0.000000   |            0 |             0 |
| freeing items        | 0.000080 | 0.000081 | 0.000000   |            0 |             0 |
| cleaning up          | 0.000052 | 0.000052 | 0.000000   |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+
20 rows in set (0.09 sec)

6.日常开发需要注意的结论
出现以下内容，警告：：

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

全局查询日志(只允许测试环境用)