重新认识Database-MySQL③--执行计划慢查询sql执行计划分析 sql执行计划输出比较简单，是否使用索引，

sql执行计划分析

 -- sql执行计划
explain select * from user;
-- 打印sql详细执行情况
explain format=json select * from user;

sql执行计划

输出比较简单，是否使用索引，扫描的大概记录数等。

打印sql详细执行情况

# EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "593997.00"  # sql查询总成本
    },
    "table": {
      "table_name": "user",
      "access_type": "ALL",  #表示全表扫描
      "rows_examined_per_scan": 2916650, #扫描的行数
      "rows_produced_per_join": 2916650,
      "filtered": "100.00",
      "cost_info": {  #耗时成本分析
        "read_cost": "10667.00",  #读成本
        "eval_cost": "583330.00", #预估成本
        "prefix_cost": "593997.00", # 它用在join语句后面子句记录前面子句的成本，由于没有join，此例记录了整个查询的成本
        "data_read_per_join": "1G" #join读取的数据量，由于没join，在此例是总评估需要读取的数据量
      },
      "used_columns": [
        "id",
        "name",
        "age",
        "create_date",
        "email"
      ]
    }
  }
}

慢查询分析

-- 索引，页数据
select * from mysql.innodb_table_stats;

-- 慢查询
show variables like 'slow_query_log';
-- 慢查询时间 默认 10S
show variables like 'long_query_time';
-- 记录sql是否走了索引
show variables like 'log_queries_not_using_indexes';
-- 慢日志输出方式默认文件；也可以file,table
-- set global log_output='FILE,TABLE'
show variables like 'log_output';
-- 数据存放路径
show variables LIKE 'datadir';

找到存放路径，查看具体的慢日志（slow.log结尾的文件），进行分析即可。

mysqldumpslow的命令参数列举如下：

--help 输出帮助信息 -v 输出详细信息
-d 调试
-s          按照什么排序，默认是'at'，显示顺序为倒序
al: 平均锁表时间 ar: 平均结果行数
at: 平均查询时间
c: 次数
l: 锁表时间
r: 总结果行数
t: 总查询时间
-r          正序排序，即从小到大排序 -t NUM       限制显示的条数 -a           显示出数字和字符串，默认数字为 N 字符串为 'S'
-g PATTERN   过滤字符串，后接正则表达式，如'10$' 以10为结尾的条件

window慢日志分析

C:\Program Files\MySQL\MySQL Server 5.7\bin>mysqldumpslow.pl -a -s r -g t -t 10
C:\ProgramData\MySQL\MySQL Server 5.7\Data\CN-20200324GFQS-slow.log >d:\slow.txt

linux分析

/usr/local/mysql/bin/mysqldumpslow -r -s c -a -t 3 -g 'brody' slow.txt

搜索包括关键字 brody的结果，并按照次数正序排序前3条

查询语句各项耗时

-- 查看sql执行每一步的耗时情况 默认0表示未打开
select @@profiling;
-- 打开执行耗时
set profiling=1;

select count(*) from user;
-- 获取执行的query_id
show profiles;
-- 查询阶段简要耗时情况
show profile for query 3;
-- 查询阶段详细耗时情况
show profile all for query 3;

show profiles;

show profile for query 3;

show profile all for query 3;

block 表示磁盘io context 服务线程过多

mysql底层执行语句

非相关子查询

-- 非相关子查询
explain select * from user where id in (select uid from user_ext);
show warnings;

改写成 semi join半连接的方式

/* select#1 */ select `test`.`user`.`id` AS `id`,`test`.`user`.`name` AS `name`,`test`.`user`.`age` AS `age`,`test`.`user`.`create_date` AS `create_date`,`test`.`user`.`email` AS `email` from `test`.`user` 
semi join (`test`.`user_ext`) where (`test`.`user`.`id` = `<subquery2>`.`uid`)