实时分析(show full processlist;)结合延后分析(mysql.slow_log),对SQL语句进行优化
设置慢查询参数
slow_query_log 1
log_queries_not_using_indexes OFF
long_query_time 5
slow_query_log 1 实时分析
查看有哪些线程正在执行
show processlist;
show full processlist;
相比show processlist;我比较喜欢用.因为这个查询可以用where条件
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST where state !='' order by state,time desc,command ;
-- 按照客户端IP对当前连接用户进行分组
SELECT substring_index(Host,':',1) as h,count(Host) as c,user FROM INFORMATION_SCHEMA.PROCESSLIST group by h order by c desc,user;
-- 按用户名对当前连接用户进行分组
SELECT substring_index(Host,':',1) as h,count(Host) as c,user FROM INFORMATION_SCHEMA.PROCESSLIST group by user order by c desc,user;各种耗时SQL对应的特征
- 改表
- Copying to tmp table
- 内存不够用,转成磁盘
- Copying to tmp table on disk
- 传输数据量大
- Reading from net
- Sending data
- 没有索引
- Copying to tmp table
- Sorting result
- Creating sort index
- Sorting result
重点关注这些状态,参考processlist中哪些状态要引起关注进行优化
延后分析
# 建数据库
CREATE TABLE `slow_log_2019-05-30` (
`start_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
`user_host` mediumtext NOT NULL,
`query_time` time(6) NOT NULL,
`lock_time` time(6) NOT NULL,
`rows_sent` int(11) NOT NULL,
`rows_examined` int(11) NOT NULL,
`db` varchar(512) NOT NULL,
`last_insert_id` int(11) NOT NULL,
`insert_id` int(11) NOT NULL,
`server_id` int(10) unsigned NOT NULL,
`sql_text` mediumtext NOT NULL,
`thread_id` bigint(21) unsigned NOT NULL,
KEY `idx_start_time` (`start_time`),
KEY `idx_query_time` (`query_time`),
KEY `idx_lock_time` (`lock_time`),
KEY `idx_rows_examined` (`rows_examined`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- insert into slow_log.slow_log_2019-05-30 select * from mysql.slow_log;
-- truncate table mysql.slow_log ;
select * FROM slow_log.`slow_log_2019-05-30`
where sql_text not like 'xxx`%'
order by query_time desc,query_time desc;按优先级排列,需要关注的列是lock_time,query_time,rows_examined.分析的时候应用二八法则,先找出最坑爹的那部分SQL,率先优化掉,然后不断not like或者删除掉排除掉已经优化好的低效SQL.
低效SQL的优化思路
对于每一个查询,先用explain SQL分析一遍,是比较明智的做法.
一般而言,rows越少越好,提防Extra:Using where这种情况,这种情况一般是扫全表,在数据量大(>10万)的时候考虑增加索引.
慎用子查询
尽力避免嵌套子查询，使用索引来优化它们
EXPLAIN SELECT *
FROM (
SELECT *
FROM `s`.`t`
WHERE status IN (-15, -11)
LIMIT 0, 10
) a
ORDER BY a.modified DESC比如说这种的,根本毫无必要.表面上看,比去掉子查询更快一点,实际上是因为mysql 5.7对子查询进行了优化,生成了Derived table,把结果集做了一层缓存.
按照实际的场景分析发现,status这个字段没有做索引,导致查询变成了全表扫描(using where),加了索引后,问题解决.
json类型
json数据类型,如果存入的JSON很长,读取出来自然越慢.在实际场景中,首先要确定是否有使用这一类型的必要,其次,尽量只取所需字段.
见过这样写的
WHERE j_a like '%"sid":514572%'这种行为明显是对mysql不熟悉,MYSQL是有JSON提取函数的.
WHERE JSON_EXTRACT(j_a, "$[0].sid")=514572;虽然也是全表扫描,但怎么说也比like全模糊查询好吧?
更好的做法,是通过虚拟字段建索引
但是现阶段MYSQL对json的索引做的是不够的,如果json数据列过大,建议还是存MongoDB(见过把12万json存mysql的,那读取速度简直无语).
字符串类型
WHERE a=1用数字给字符串类型的字段赋值会导致该字段上的索引失效.
WHERE a='1'分组查询
group by,count(x),sum(x),慎用.非常消耗CPU
group by
select col_1 from table_a where (col_2 > 7 or mtsp_col_2 > 0) and col_3 = 1 group by col_1这种不涉及聚合查询(count(x),sum(x))的group by明显就是不合理的,去重复查询效果更高点
select distinct(col_1) from table_a where (col_2 > 7 or mtsp_col_2 > 0) and col_3 = 1 limit xxx; count(x),sum(x)
x 这个字段最好带索引,不然就算筛选条件有索引也会很慢
order by x
x这字段最好带上索引,不然show processlist;里面可能会出现大量Creating sort index的结果
组合索引失效
组合索引有个最左匹配原则
KEY 'idx_a' (a,b,c)WHERE b='' and c =''这时组合索引是无效的.
其他
EXPLAIN SQL
DESC SQL# INNODB_TRX表主要是包含了正在InnoDB引擎中执行的所有事务的信息，包括waiting for a lock和running的事务
SELECT * FROM information_schema.INNODB_TRX;
SELECT * FROM information_schema.innodb_locks;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;
