mysql 慢查询

1. 慢查询日志的打开
正常情况下，只需要在配置文件中增加slow_query_log = 1配置，即打开慢查询日志，未指定slow_query_log_file的情况下，会自动生成一个以主机名+‘slow’.log 的文件。
show variables like '%slow_query%'

2. 默认情况下记录慢查询的时间阈值为10s
show variables like '%long_query_time%'
--long_query_time 指定超过多少时长的查询需要被记录；

show variables like '%log_output%'

show variables like '%min_examined_row_limit%';
--min_examined_row_limit 超过指定行数的扫描查询开关；默认0，代表不限制扫描函数；

方式一设置开启MySQL慢日志参数：无需重启即可生效，但是重启会导致设置失效
set global long_query_time=0.1;
set global log_queries_not_using_indexes=on;
set global slow_query_log = on;
set log_output = 'FILE,TABLE';

方式二需要修改配置文件 my.ini /my.cnf 配置文件中配置：需要重启 MySQL 才可以生效，命令为 service mysqld restart
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow_query_log_202123.log
log_output = table
long_query_time = 1

可以将慢查询日志同时记录在文件以及 mysql.slow_log

查询慢查询日志：select * from mysql.slow_log
select CONVERT(sql_text USING utf8) sql_text from mysql.slow_log;

start_time 为执行时间，user_host 为用户的主机名，query_time 为查询所花费的时间，
lock_time 为该查询使用锁的时间，rows_sent 为这条查询返回了多少数据给客户端，
rows_examined 表示这条语句扫描了多少行，db 为数据库，sql_text 为这条 SQL，
thread_id 为执行这条查询的线程 id。

查询慢sql 日志文件保存在哪里：show variables like '%slow_query_log_file%'

没有index的查询记录开关
show global variables like '%indexes%';

log_queries_not_using_indexes 是否开启记录没有index 的查询；
log_throttle_queries_not_using_index 做日志记录的流量控制，一分钟可以记录多少条；默认 0 是不限制；

***********explain / desc
MySQL中执行explain或者desc命令查看慢查询语句，可以看出为什么SQL查询慢。如：

explain select * from dbname.tableName
或
desc select * from dbname.tableName

它的输出格式细节可以关注MySQL explain format，在输出中最要注意的是：
1. type 字段值:
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery >
index_subquery > range > index > all
备注：ALL是效率最差；若type 值为 index 或 all，则需要优化
2.Extra 字段值：出现 Using filesort 以及 Using temporary，表示mysql 根本不能使用索引，效率会受到重大影响。应尽可能对此进行优化；
最主要是要关注在orderby和groupby。
Using filesort：表示mysql 会对结果使用一个外部索引排序，而不是从表里按索引次序读到相关内容。
Using temporary：表示mysql 对查询结果排序时使用临时表；常见于 order by 和 group by。

3. key：是否有使用Key，key长度如何

Note: SQL优化是个很复杂的过程，有可能出现拆东墙补西墙的情况：
比如给数据库表加入了索引之后，确实查询快了，可是存储空间加多了，插入删除操作耗时也增加了;
如果在一个写多读少的系统中，执行这种优化可能会起到反效果。
所以优化完之后千万不能大意，要持续监控系统，防止出现引入新瓶颈的情况。

******Mysql慢查询优化方法及优化原则 来自：https://www.jb51.net/article/161441.htm
1、日期大小的比较，传到xml中的日期格式要符合'yyyy-MM-dd'，这样才能走索引，如：'yyyy'改为'yyyy-MM-dd'，'yyyy-MM'改为'yyyy-MM-dd'【这样MYSQL会转换为日期类型】

2、条件语句中无论是等于、还是大于小于，WHERE左侧的条件查询字段不要使用函数或表达式或数学运算

3、WHERE条件语句尝试着调整字段的顺序提升查询速度，如把索引字段放在最前面、把查询命中率高的字段置前等

4、保证优化SQL前后其查询结果是一致的

5、在查询的时候通过将EXPLAIN命令写在查询语句前，测试语句是否有走索引【具体用法百度】

6、禁止使用SELECT * FROM操作，应只返回需要的字段，不需要的字段不要返回

7、可以尝试分解复杂的查询，在应用层面进行表关联，以此代替SQL层面的表关联

8、WHERE子句和ORDER BY子句涉及到的列建索引

9、避免在WHERE子句中对字段进行NULL判断【可以对表字段改造一下，字符串型字段默认值设置为空字符串，数字型字段默认值设置为0，日期型字段默认值设置为1990-01-01等】

10、避免在WHERE子句中使用!=或<>操作符

11、避免在WHERE子句中使用OR操作符

12、BETWEEN AND代替IN

13、LIKE '%abc%'不会走索引，而LIKE 'abc%'会走索引

14、避免对字段进行表达式操作

15、避免对字段进行函数操作

16、GROUP BY操作默认会对GROUP BY后面的字段进行排序，如果你的程序不需要排序，可在GROUP BY语句后面加上ORDER BY NULL去除排序

17、如果是数值型字段，则尽量设计为数值型字段，不要为了方便、为了偷懒而给后面维护的同事埋坑

18、表中所有字段设计为NOT NULL

19、返回条数固定时，用LIMIT语句限制返回记录的条数，如只需要一条记录，或肯定只有一条记录符合条件，那建议加上LIMIT 1

20、对于枚举类型的字段【即有固定罗列值的字段】，建议使用ENUM而不是VARCHAR，如性别、星期、类型、类别等

21、对于存IP地址的字段设计为成UNSIGNED INT型

22、避免在SQL中使用NOW()、CURDATE()、RAND()函数【因为这种方式会导致MYSQL无法使用SQL缓存】，可以转化为通过传入参数的方式

23、对于统计类的查询【如查询连续几个月的数据总量，或查询同比、环比等】，可以通过定时查询并统计到统计表的方式提高查询速度