MySQL 慢查询
什么是慢查询
慢查询,是指一条sql的执行时间比较长;
这里有两个点需要注意:
1.慢查询,并不是单指查询,CURD所有命令,如果执行的时间较长,都叫慢查询;
2.那多慢才算慢呢?这个你说了算,你要是觉得执行时间超过0.005秒就算慢了,那么0.005秒就是慢查询的评估阈值;如果你觉得0.000000000000秒都算慢,那么就是说,你将每一条查询都认为是慢查询。
慢查询有什么用呢
没啥用,如果没有慢查询才好呢
但是有慢查询的时候,证明该进行优化了
查询相关项的开启状态
#查看是否开启慢查询日志 mysql> show variables like 'slow_query_log'; +----------------+-------+ | Variable_name | Value | +----------------+-------+ | slow_query_log | OFF | +----------------+-------+ 1 row in set (0.06 sec) #查看是否开启没有使用索引的查询 mysql> show variables like '%log_qu%'; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | log_queries_not_using_indexes | OFF | +-------------------------------+-------+ 1 row in set (0.00 sec) #一个查询超过多少秒会被记录到慢查询日志中(如果是0,则表示每条命令都要记录) mysql> show variables like '%long_query%'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+ 1 row in set (0.01 sec)
开启慢查询
#开启慢查询 mysql> set global slow_query_log=ON; Query OK, 0 rows affected (0.07 sec) #查看慢查询状态、以及慢查询的日志文件 mysql> show variables like 'slow_query%'; +---------------------+------------------------------------------+ | Variable_name | Value | +---------------------+------------------------------------------+ | slow_query_log | ON | | slow_query_log_file | /usr/local/mysql/data/localhost-slow.log | +---------------------+------------------------------------------+ 2 rows in set (0.00 sec) #设置查询超时时间,此处设为0,表示将每一条命令都记录到慢查询日志中 mysql> set @@long_query_time = 0.000000; Query OK, 0 rows affected (0.00 sec) mysql> show variables like '%long_query%'; +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | long_query_time | 0.000000 | +-----------------+----------+ 1 row in set (0.00 sec) mysql> set global log_queries_not_using_indexes=on; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'log_queries_not_using_indexes'; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | log_queries_not_using_indexes | ON | +-------------------------------+-------+ 1 row in set (0.00 sec)
测试
mysql> select * from category left join goods on category.id=goods.cate_id order by category.id; +----+---------+------+---------+-------------------+ | id | cate | id | cate_id | name | +----+---------+------+---------+-------------------+ | 1 | food | 2 | 1 | water | | 1 | food | 3 | 1 | rice | | 2 | clothes | 1 | 2 | T-shirt | | 3 | book | 4 | 3 | C++ primer | | 4 | sport | 5 | 4 | basketbal | | 5 | music | 0 | 5 | You Are Not Alone | | 6 | video | NULL | NULL | NULL | +----+---------+------+---------+-------------------+ 7 rows in set (0.00 sec) #查看慢查询日志 localhost:~ root# cat /usr/local/mysql/data/localhost-slow.log # Time: 180730 15:54:14 # User@Host: root[root] @ localhost [] Id: 10 # Query_time: 0.000582 Lock_time: 0.000181 Rows_sent: 7 Rows_examined: 20 SET timestamp=1532937254; select * from category left join goods on category.id=goods.cate_id order by category.id;
使用mysqldumpslow
localhost:~ root# mysqldumpslow -t 10 /usr/local/mysql/data/localhost-slow.log Reading mysql slow query log from /usr/local/mysql/data/localhost-slow.log Count: 10 Time=0.00s (0s) Lock=0.00s (0s) Rows=7.0 (70), root[root]@localhost select * from category left join goods on category.id=goods.cate_id order by category.id
常见的解决慢查询的方式
分析慢查询语句,看是否命中了索引,如果没有索引,则创建响应的索引;
如果建了索引,并且也用到了索引,但是还是很慢,那么就需要看是你一次性操作的数据太多,还是数据库的数据量本身就太大了;
如果是一次性操作的数据太多,可以分批操作;
如果是数据量本身就太大了,那么可以采取分库分表分区的做法
具体优化手段需要根据自己的业务进行考虑。
如需转载,请注明文章出处,谢谢!!!