MYSQL基础05-慢查询优化

官网地址：
https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html
https://dev.mysql.com/doc/refman/5.7/en/mysqldumpslow.html

慢查询

查询任务涉及到：网络，CPU计算，生成统计信息和执行计划，锁等待等操作。这些需要内存操作，CPU操作，内存不足导致I/O操作上消耗时间。

优化数据访问

性能低下的主要原因是访问的数据太多
可能存在的问题：

相同数据重复查询，可以利用缓存技术，减少
查找所有的列
查找不需要的记录
衡量查询开销的三个指标：
响应时间
- 服务时间：处理查询所需时间
- 排队时间：等待任务执行的时间
扫描行数
- 通过EXPLAIN 语句返回的type列返回的访问类型
  - 索引的好处是可以查找更少的行
返回的行数

重构查询的方式

可以将SQL复杂查询切割成多个小查询
将一个查询数据很多条的进行分页，成小的查询
可以将多个关联查询切分成多个简单查询；
- 单个表查询有缓存，可以利用到查询缓存的结果；
- 减少了数据库锁竞争
- 减少重复查询的数据

查询执行的基础

客户端发送查询给服务器，服务器先检查缓存是否命中
如果缓存命中则返回查询结果，如果没有命中，服务器进行SQL解析数据处理，再进行优化器生成执行计划
进行数据查询并进行缓存，返回数据

慢查询日志

慢查询文件没有命名情况下默认是 host_name-slow.log

慢查询控制参数

慢查询默认是不统计管理查询语句，如果也要对管理查询语句进行管理，要开启 log_slow_admin_statements 系统变量
慢查询日志需要设置开启，可以通过系统变量 slow_query_log 配置
慢查询日志文件地址，可以通过系统变量slow_query_log查看或配置）
开启慢查询日志，需要通过系统变量 long_query_time 设置慢查询时间秒数，或者开启未使用index查询的系统变量 log_queries_not_using_indexes

show variables like 'slow_query_log';//查看慢查询日志是否开启
show variables like 'slow_query_log_file';//查看慢查询日志文件地址
show variables like 'log_queries_not_using_indexes';//查询未使用索引查询
show global status like 'slow_queries'；//查询慢查询数量

慢查询日志开启

通过设置my.cnf文件配置慢查询的启动状态，慢查询文件日志地址

slow_query_log=ON
slow_query_log_file=/var/lib/mysql/4ad242fa921b-slow.log
long_query_time=2

慢查询日志分析

官方文档地址：https://dev.mysql.com/doc/refman/5.7/en/mysqldumpslow.html

mysqldumpslow

mysqldumpslow 指令用于解析慢查询日志文件，并汇总日志目录

mysqldumpslow [options] [log_file ...]

Reading mysql slow query log from /usr/local/mysql/data/mysqld57-slow.log
Count: 1  Time=4.32s (4s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
 insert into t2 select * from t1

Count: 3  Time=2.53s (7s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
 insert into t2 select * from t1 limit N

Count: 3  Time=2.13s (6s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
 insert into t1 select * from t1

Count 代表这个 SQL 执行了多少次；
Time 代表执行的时间，括号里面是累计时间；
Lock 表示锁定的时间，括号是累计；
Rows 表示返回的记录数，括号是累计。
指令说明：

Option Name	Description
-a	Do not abstract all numbers to N and strings to 'S'
-n	Abstract numbers with at least the specified digits
--debug	Write debugging information
-g	Only consider statements that match the pattern
--help	Display help message and exit
-h	Host name of the server in the log file name
-i	Name of the server instance
-l	Do not subtract lock time from total time
-r	Reverse the sort order
-s	How to sort output
-t	Display only first num queries
--verbose	Verbose mode

SQL优化

IN,EXISTS和关联子查询
- 涉及到IN内部子查询，数据库优化器会自动将IN方法里面的查询转成EXISTS查询
- EXISTS查询时，外层的表查询的type是ALL，所以当外部表很大时，效率会很低；
- 可以通过INNER JOIN或者LEFT JOIN代替IN（）方法的子查询
UNION ALL
- 会创建一个数据查询的临时表
最大值最小值优化
- MIN(),MAX（）查询有的时候不是很好的选择
- 可以通过order by 和limit 1 联合处理
COUNT 统计
- 不统计列为null的值
- COUNT(*) 统计行数
关联查询优化
- ON或USING子句的列上有索引
- 关联顺序，A/B表关联，关联顺序是A/B，可以不用在A表关联上建索引
LIMIT优化
- 可以将LIMIT查询切换成两部分
  - limit 查询数据的主键
  - 查找这些主键的数据