mysql-slow-query

Waht

slow_log table

table

column	type	null	default	description
start_time	timestamp(6)	NO	CURRENT_TIMESTAMP(6)	Time the query began.
user_host	mediumtext	NO	NULL	User and host combination.
query_time	time(6)	NO	NULL	Total time the query took to execute.
lock_time	time(6)	NO	NULL	Total time the query was locked.
rows_sent	int(11)	NO	NULL	Number of rows sent.
rows_examined	int(11)	NO	NULL	Number of rows examined.
db	varchar(512)	NO	NULL	Default database.
last_insert_id	int(11)	NO	NULL	last_insert_id.
insert_id	int(11)	NO	NULL	Insert id.
server_id	int(10) unsigned	NO	NULL	The server’s id.
sql_text	mediumtext	NO	NULL	Full query.
thread_id	bigint(21) unsigned	NO	NULL	Thread id.
rows_affected	int(11)	NO	NULL	Number of rows affected by an UPDATE or DELETE (from MariaDB 10.1.2)

reference

slow_log https://mariadb.com/kb/en/mysqlslow_log-table/
HOW
Setting

查看相关参数
1
2
show variables like 'long%';
show variables like 'slow%';
- long_query_time,SQL语句执行时间超过此数值,记录日志
- slow_query_log,是否开启慢查询(ON/OFF)
- slow_query_log_file,慢查询日志文件
- slow_launch_time
- log_output,慢查询日志输出方式,可选FILE(文件,通过slow_query_log_file查询),TABLE(数据表是mysql.slow_log表)
设置参数
1
set long_query_time=0.5;
开启慢查询日志,需要设置如下参数:
- long_query_time,慢查询时间
- slow_query_log,是否开启
- log_output,日志输出方式,这个是全局变量.
  通过set global设置,如:
  1
  2
  3
  SET GLOBAl log_output='TABLE';
  或
  SET GLOBAl log_output='FILE';
  Search
通过sql查询
1
select * from mysql.slow_log;
通过文件查询
查询slow_query_log_file参数得到慢查询日志文件所在位置,查看文件.

Solution 性能优化

索引

在无索引的表中查询会进行全表扫描,如果表的数据量很大，则扫描大量的数据，执行效率过
慢，占用数据库连接，连接数堆积很快达到数据库的最大连接数设置，新的应用请求将会被拒绝导致故障发生。
隐式转换也会导致索引无法使用.

创建索引

在经常查询而不经常增删改操作的字段加索引。
order by与group by后应直接使用字段，而且字段应该是索引字段。
一个表上的索引不应该超过6个。
索引字段的长度固定，且长度较短。
索引字段重复不能过多。
在过滤性高的字段上加索引。

使用索引注意事项

使用like关键字时，前置%会导致索引失效。
使用null值会被自动从索引中排除，索引一般不会建立在有空值的列上。
使用or关键字时，or左右字段如果存在一个没有索引，有索引字段也会失效。
使用!=操作符时，将放弃使用索引。因为范围不确定，使用索引效率不高，会被引擎自动改为全表扫描。
不要在索引字段进行运算。
在使用复合索引时，最左前缀原则，查询时必须使用索引的第一个字段，否则索引失效；并且应尽量让字段顺序与索引顺序一致。
避免隐式转换，定义的数据类型与传入的数据类型保持一致。

验证

使用mysql的explain分析sql,查看执行计划.

执行计划参数含义

例如一个sql执行计划如下:

  // type为ALL，是全表扫描，每次执行需要扫描505560行数据
            id: 1
  select_type: SIMPLE
        table: customers
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 505560
        Extra: Using where

增加索引后:

  // 执行计划看到type为ref，基于索引的等值查询，或者表间等值连接
              id: 1
  select_type: SIMPLE
        table: customers
         type: ref
possible_keys: idx_cus
          key: idx_cus
      key_len: 31
          ref: const
         rows: 4555
        Extra: Using index condition

参考

阿里云: https://help.aliyun.com/document_detail/52274.html?spm=a2c4g.11186623.2.8.1b1945135Wahhf

Waht

slow_log table

HOW

Setting

Search

Solution 性能优化

索引

创建索引

使用索引注意事项

验证

参考