mysql 分组查询慢_干掉mysql慢查询

主要思路

实时分析(show full processlist;)结合延后分析(mysql.slow_log),对SQL语句进行优化

实时分析

查看有哪些线程正在执行

1

2show 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

延后分析

设置慢查询参数

1

2

3

4slow_query_log 1

log_queries_not_using_indexes OFF

long_query_time 5

slow_query_log 1# 建数据库

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 byselect 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;

参考链接