1、SQL万能调优步骤
注：本文是基于MySQL5.7.38版本做的所有测试
2、常用SQL介绍
1、查看MySQL服务器信息
show status
查看mysql服务器状态信息，可以有如下用法：
显示数据库的查询，更新，添加，删除的次数
show status like 'com_select';
show status like 'com_update';
show status like 'com_insert';
show status like 'com_delete';
显示mysql数据库的连接数
show status like 'connections';
显示慢查询次数
show status like 'slow_queries';
查看mysql数据库启动了多少时间
show status like 'uptime';
2、查看MySQL版本
select VERSION()
3、慢查询日志
概念：
- 慢查询日志是用来记录执行时间超过指定时间的查询语句。
- 通过慢查询日志，可以查找出哪些查询语句的执行效率很低，以便进行优化。
1、设置慢查询日志
方式1：全局变量设置
该方式数据库重启全部失效，得重新配置
慢查询日志的打开正常情况下，只需要在配置文件中增加slow_query_log = 1配置，即打开慢查询日志，未指定slow_query_log_file的情况下，会自动生成一个以主机名+‘slow’.log 的文件，如下SQL，可以查询是否开启慢查询日志：
show variables like '%slow_query_log%';
执行结果：
慢查询日志存放路径：
ON开启，OFF关闭，这里默认是开启的，真实生产环境最好不要打开，在开启慢日志功能之后，会对 MySQL 的性能造成一定的影响
那如果是关闭的，我们有时候需要打开，去定位一下哪些查询是比较慢的SQL，怎么开启呢？
执行下面SQL即可开启慢查询日志功能：
set global slow_query_log='ON';
还可以设置慢查询日志文件存放路径：
set global slow_query_log_file='D:\\mysql\\data\\slow_sql.log';
然后再执行：show variables like ‘%slow_query%’;就会发现已经开启了，如下：
查询慢查询日志的时间（这个时间的含义是：SQL执行多长时间会被MySQL认为是慢查询，默认是10，单位：秒）
show variables like 'long_query_time';
执行结果：
修改慢查询时间为3秒，也就是说执行查询操作时，超过3秒的SQL，就会被MySQL定位为慢查询了（如果有时候用命令不起作用，那么可以关闭再打开）
set global long_query_time=3;//针对当前窗口失效，再开一个窗口就有效了
set long_query_time=3;//针对当前窗口有效
方式2：配置文件设置
服务器重启不影响
第一步：
MySQL配置文件my.ini中新增下面配置
[mysqld]
slow_query_log = ON
slow_query_log_file = D:\\mysql\\data\\slow_sql.log
long_query_time = 3
第二步：
重启MySQL服务：
service mysqld restart
2、实战
建表
t_user建表语句：
CREATE TABLE `t_user` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`username` varchar(100) DEFAULT NULL COMMENT '姓名',
`sex` varchar(2) DEFAULT NULL COMMENT '性别',
`age` int(3) DEFAULT NULL COMMENT '年龄',
`phone` varchar(12) DEFAULT NULL COMMENT '手机号',
`address` varchar(100) DEFAULT NULL COMMENT '家庭住址',
`deptid` int(11) DEFAULT NULL COMMENT '归属部门ID',
`udesc` varchar(255) DEFAULT NULL COMMENT '个人描述',
`createtime` datetime DEFAULT NULL COMMENT '创建时间',
`school` varchar(255) DEFAULT NULL COMMENT '毕业院校',
`major` varchar(255) DEFAULT NULL COMMENT '专业名称',
`nationality` varchar(255) DEFAULT NULL COMMENT '国籍',
`nation` varchar(255) DEFAULT NULL COMMENT '民族',
`idcard` varchar(255) DEFAULT NULL COMMENT '身份证号码',
`qq` varchar(255) DEFAULT NULL COMMENT 'QQ号',
PRIMARY KEY (`id`),
KEY `idx_user_sex` (`sex`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='用户信息表';
然后向里面添加200万条数据
可以采用Java代码写线程池添加，也可以使用存储过程直接添加，我这里贴一下存储过程吧：
DROP PROCEDURE IF EXISTS `insert_user_procedure`;
CREATE PROCEDURE insert_user_procedure(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE str VARCHAR(26) DEFAULT 'abcdefghijklmnopqrstuvwsyz';
DECLARE startIndex INT DEFAULT 1;#起始索引
DECLARE len INT DEFAULT 1;#字符串长度
WHILE i<=insertCount DO
#产生一个随机整数,作为起始索引
SET startIndex = FLOOR(RAND()*26+1); #1-26
#随机长度
SET len = IF((26-startIndex+1)>20, FLOOR(RAND()*20+1), FLOOR(RAND()*(26-startIndex+1)+1));#1~26-startIndex+1且小于20
#SUBSTR(str,startIndex,len)获取随机长度随机起始索引的字符串
if i % 2 = 0 then
INSERT into t_user (username, sex, age, phone, address, deptid, udesc, createtime, school, major, nationality, nation, idcard, qq)
VALUES (SUBSTR(str, startIndex, len), '男', startIndex, '18082532105', '湖北省武汉市江汉路191号', 25, '我是一个好人', NOW(), '武汉大学', '计算机应用技术', '中国', '汉族', '429004155130213602', '1134135987');
ELSE
INSERT into t_user (username, sex, age, phone, address, deptid, udesc, createtime, school, major, nationality, nation, idcard, qq)
VALUES (SUBSTR(str,startIndex,len), '女', startIndex, '18082532106', '湖北省武汉市江汉路192号', 26, '我是一个老好人', NOW(), '北京大学', '计算机网络技术', '中国', '回族', '429004155130213603', '1134135988');
end if;
set i = i+1;
END WHILE;
END
储存过程新建完成后，调用存储过程：
call insert_user_procedure(2000000);
执行过程非常慢，建议使用线程池造数，非常快
方式1：慢查询日志记录到文件
上面的设置就是将慢查询日志记录到文件中了，所以我们直接运行下面这个SQL测试一下：
select * from t_user where sex = '男' limit 900000, 50;
执行结果：
下面是5.5版本MySQL数据库的执行结果：
下面是5.7版本MySQL数据库的执行结果：
这张表数据有200万，很明显耗时比较多，超过了我设置的3秒钟，那么这条SQL的执行，就会被MySQL认为是慢查询了，就会记录到慢查询日志中了，我们看D盘，果然日志文件中有内容了，如下：
这样我们程序员就只需要设置好时间阀值，可以很轻松的定位到哪些SQL是执行慢的，先定位到SQL，再想办法解决
方式2：慢查询日志记录到表
需要添加一个log_output的配置，就可以将慢查询记录到表中了，配置如下：
slow_query_log = ON
#slow_query_log_file = D:\\mysql\\data\\slow_sql.log
log_output = table
long_query_time = 3
重启一下MySQL服务器
然后再次执行上面那个耗时超过3秒的SQL，执行完毕后，我们查询下慢查询日志表：
select * from mysql.slow_log;
查询结果：
和日志差不多，也记录了我们需要的一些关于慢SQL的相关信息，达到目的了
注意点：
1、如果查询出来的sql_text字段是一个二进制数据的话，可以通过CONVERT函数转换一下，如下：
select CONVERT(sql_text using utf8) as sql_text from mysql.slow_log;
2、慢查询不记录执行失败的查询，比如 long_query_time设置为10秒，一个查询超过了10秒，但是因为其他原因执行失败，MySQL的慢查询将无法记录此查询信息
4、执行计划
1、什么是执行计划
- 执行计划是什么，简单来说就是SQL在数据库中执行时的表现情况
- 当一条SQL下发到数据库的时候，怎么扫描表、怎样使用索引这些行为对用户来说是不知道的，能够明显感受到的，只有查询的时间
- 而执行计划可以提前预估SQL究竟需要运行多长时间、查询中需要涉及到哪些表、走了哪些索引，这些通过优化器经过基于成本和规则的优化后生成的执行计划能够用来进行性能分析和优化
2、执行过程
总体来说，一条SQL查询过程分为下面几个过程：
- 连接：客户端向MySQL服务器发送一条查询请求，与connectors交互，连接池认证相关处理。请求会暂时存放在连接池（connection pool)中并由处理器（Management Serveices & Utilities）管理。当该请求从等待队列进入到处理队列，管理器会将该请求丢给SQL接口（SQL Interface）。
- 缓存：SQL接口接收到请求后会将请求进行hash处理，并与缓存中的结果进行比对，如果命中缓存，则立刻返回存储在缓存中的结果，否则进入下一阶段
- 解析：服务器进行SQL解析(词法语法)、预处理
- 优化：再由优化器生成对应的执行计划
- 执行：MySQL根据执行计划，调用存储引擎的API来执行查询
- 结果：将结果返回给客户端，同时缓存查询结果。
3、MySQL中执行计划
如何使用MySQL执行计划呢，非常简单，直接在SQL前面加上【EXPLAIN】关键字即可，如下：
EXPLAIN select * from t_user where sex = '男'
执行结果如下：
那么结果中有很多指标参数，分别是什么意思呢？
看下面的总结：
我们一般关注：type、key、rows、Extra等字段即可
5、show profile分析
- 当我们在优化sql的时候可以使用explain来查看执行计划，可当根据执行计划优化完毕之后我们的sql的运行时间还是比较长，这时我们可以使用show profile
- 更细粒度的分析，show-profile，可以提高用来分析当前会话中语句执行的资源消耗情况，可以用于SQL调优的测量
1、基本语法
查看profile是否开启，数据库默认是不开启的
show variables like "%pro%";
查询结果：
如果没有开启，那么这样开启：
set profiling = 1;
如果需要关闭的话，执行：
set profiling = 0;
2、耗时查询
执行下面SQL查询耗时：
show profiles;
查询结果：
Query_ID：SQL编号ID
Duration：SQL执行时间
Query：SQL语句
再继续查询细节：
show profile for query 67;
查询结果：
这样就可以查看到各个操作的详情耗时情况了
其中最耗时的一个操作就是【Sending data】，这个操作是最重要的一个过程，含义如下：
- 线程正在读取和处理一条SELECT语句的行，并且将数据发送至客户端
- 由于在此期间会执行大量的磁盘访问，所以这个状态在一个查询的生命周期中经常是耗时最长的。
- 这个字段才是SQL真正运行采集+响应数据的时间，而非executing；
对于一个普通查询来说，这个Sending data参数过大可分为两种情况
- 第一种是SQL本身，比如没有建立正确的索引，索引失效等等情况
- 第二种是响应数据量过大，导致CPU调度时上下文频繁切换
比如：外网使用Navicat连接到远程数据库中，查询一个普通的SQL，在本地MySQL执行速度很快，但是使用远程服务器的MySQL就异常的缓慢；这时若查询profile详情，就会发现大量相应数据传输IO导致频繁的上下文切换消耗了大量的时间
注意：show profile语句已经弃用，并将在以后版本中移除，建议使用 Performance Schema
Performance Schema文档：https://dev.mysql.com/doc/refman/5.7/en/performance-schema-query-profiling.html
6、Trace工具
- MySQL5.6版本后提供了对SQL的跟踪工具trace，通过使用trace可以让我们明白optimizer(优化器)如何选择执行计划的
- mysql最终是否选择走索引或者一张表涉及多个索引，mysql最终如何选择索引，我们可以用trace工具来一查究竟
- 开启trace工具会影响mysql性能，所以只能临时分析sql使用，用完之后立即关闭
1、开启Trace
打开trace，设置格式为JSON，执行下面命令：
set optimizer_trace="enabled=on",end_markers_in_JSON=on;
注意：
MySQL5.6版本后提供了对SQL的跟踪工具trace，通过使用trace可以让我们明白optimizer(优化器)如何选择执行计划的
所以如果你的数据库版本是5.6以下的话，是无法使用Trace工具的
2、案例追踪
打开cmd黑窗口，首先登录MySQL，命令如下：
mysql -uroot -p123456
查看数据库有哪些：
show databases;
然后选择我们的测试数据库test：
use test;
然后我们开启trace：
set optimizer_trace="enabled=on",end_markers_in_JSON=on;
然后就可以执行我们需要执行到SQL了，这里我们再次执行这个比较耗时的SQL：
select * from t_user where sex = '男' limit 900000, 50;
然后再执行下面SQL查询trace字段：
SELECT * FROM information_schema.OPTIMIZER_TRACE;
执行结果：
内容很多，我拷贝出来了，并对关键信息进行了注释，大家看一下即可：
| select * from t_user where sex = '男' limit 900000, 50 | {
"steps": [
{
"join_preparation": { //第一阶段：SQL准备阶段
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `t_user`.`id` AS `id`,`t_user`.`username` AS `username`,`t_user`.`sex` AS `sex`,`t_user`.`age` AS `age`,`t_user`.`phone` AS `phone`,`t_user`.`address` AS `address`,`t_user`.`deptid` AS `deptid`,`t_user`.`udesc` AS `udesc`,`t_user`.`createtime` AS `createtime`,`t_user`.`school` AS `school`,`t_user`.`major` AS `major`,`t_user`.`nationality` AS `nationality`,`t_user`.`nation` AS `nation`,`t_user`.`idcard` AS `idcard`,`t_user`.`qq` AS `qq` from `t_user` where (`t_user`.`sex` = '男') limit 900000,50"
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": { //第二阶段：SQL优化阶段
"select#": 1,
"steps": [
{
"condition_processing": { //条件处理
"condition": "WHERE",
"original_condition": "(`t_user`.`sex` = '男')",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(`t_user`.`sex` = '男')"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(`t_user`.`sex` = '男')"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(`t_user`.`sex` = '男')"
}
] /* steps */
} /* condition_processing */
},
{
"substitute_generated_columns": {
} /* substitute_generated_columns */
},
{
"table_dependencies": [ //表依赖详情
{
"table": "`t_user`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
"ref_optimizer_key_uses": [
{
"table": "`t_user`",
"field": "sex",
"equals": "'男'",
"null_rejecting": false
}
] /* ref_optimizer_key_uses */
},
{
"rows_estimation": [ //预估表的访问成本
{
"table": "`t_user`",
"range_analysis": {
"table_scan": {
"rows": 1924083,
"cost": 2.31e6
} /* table_scan */,
"potential_range_indexes": [ //查询可能使用的索引
{
"index": "PRIMARY", //主键索引
"usable": false, //没有使用该索引
"cause": "not_applicable" //原因：不适用
},
{
"index": "idx_user_sex", //辅助索引
"usable": true, //使用了该索引
"key_parts": [
"sex",
"id"
] /* key_parts */
}
] /* potential_range_indexes */,
"setup_range_conditions": [
] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
} /* group_index_range */,
"analyzing_range_alternatives": { //分析各个索引使用成本
"range_scan_alternatives": [
{
"index": "idx_user_sex",
"ranges": [ //索引使用范围
"男 <= sex <= 男"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": true, //使用该索引获取的记录是否按照主键排序
"using_mrr": true,
"index_only": false, //是否使用覆盖索引
"rows": 962041, //索引扫描行数
"cost": 1.08e6, //索引使用成本
"chosen": true //是否选择该索引
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
} /* analyzing_range_alternatives */,
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "idx_user_sex",
"rows": 962041,
"ranges": [
"男 <= sex <= 男"
] /* ranges */
} /* range_access_plan */,
"rows_for_plan": 962041,
"cost_for_plan": 1.08e6,
"chosen": true
} /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
},
{
"considered_execution_plans": [
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`t_user`",
"best_access_path": { //最优访问路径
"considered_access_paths": [ //最终选择的访问路径
{
"access_type": "ref", //访问类型：为ref
"index": "idx_user_sex",
"rows": 962041,
"cost": 327384,
"chosen": true //确定选择
},
{
"access_type": "range", //访问类型：为range
"range_details": {
"used_index": "idx_user_sex"
} /* range_details */,
"chosen": false, //确定不选择
"cause": "heuristic_index_cheaper"
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 962041,
"cost_for_plan": 327384,
"chosen": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {
"original_condition": "(`t_user`.`sex` = '男')",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`t_user`",
"attached": null
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"refine_plan": [
{
"table": "`t_user`"
}
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": {//第三阶段：SQL执行阶段
"select#": 1,
"steps": [
] /* steps */
} /* join_execution */
}
] /* steps */
} | 0 | 0 |