MySQL慢查询
2020-12-01 14:36:18 0 举报AI智能生成
MySQL慢查询
作者其他创作
大纲/内容
慢查询参数配置
slow_query_log
是否启用慢查询日志，1为启用，0为禁用
slow_query_log_file
指定慢查询日志文件的路径和名字，可使用绝对路径指定
默认值是'主机名_slow.log'，位于datadir目录
long_query_time
SQL语句运行时间阈值，执行时间大于参数值(单位秒)的语句才会被记录下来
min_examined_row_limit
SQL语句检测的记录数少于设定值的语句不会被记录到慢查询日志，即使这个语句执行时间超过了long_query_time的阈值
log_queries_not_using_indexes
将没有使用索引的语句记录到慢查询日志，1为启用，0为禁用
log_throttle_queries_not_using_indexes
5.6版本设定每分钟记录到日志的未使用索引的语句数目，超过这个数目后只记录语句数量和花费的总时间
mariadb没有这个参数
mariadb没有这个参数
log_slow_admin_statements
5.6版本记录执行缓慢的DDL语句，如alter table,analyze table, check table, create index, drop index, optimize table, repair table等。
mariadb没有这个参数
mariadb没有这个参数
log_slow_slave_statements
log_timestamps
5.7版本新增时间戳所属时区参数
5.6版本记录从库上执行的慢查询语句
mariadb没有这个参数
mariadb没有这个参数
默认值：记录UTC时区的时间戳到慢查询日志
推荐值：system，应修改为记录系统时区
mariadb没有这个参数
log_output
指定慢查询日志的输出方式，从5.5版本开始可以记录到日志文件(FILE，慢查询日志)和数据库表(TABLE，mysql.slow_log)中
推荐值：FILE,TABLE
mysqldumpslow
命令行格式
mysqldumpslow [ OPTS... ] [ LOGS... ]
参数说明
-s 表示按照何种方式排序
-g 后边搭配一个正则匹配模式，大小写不敏感
- c 访问次数
- l 锁定时间
- r 返回记录
- t 查询时间
- al 平均锁定时间
- ar 平均返回记录数
- at 平均查询时间
-g 后边搭配一个正则匹配模式，大小写不敏感
常用命令
得到返回记录集最多的10条SQL：
mysqldumpslow -s r -t 10 /var/lib/mysql/slow.log
得到访问次数最多的10条SQL：
mysqldumpslow -s r -t 10 /var/lib/mysql/slow.log
得到按照时间排序的前10条里面含有左连接的SQL：
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/slow.log
也支持管道符命令
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/slow.log | more
pt-query-digest
命令行格式
pt-query-digest [OPTIONS] [FILES] [DSN]
参数说明
--create-review-table 当使用--review参数把分析结果输出到表中时，如果没有表就自动创建。
--create-history-table 当使用--history参数把分析结果输出到表中时，如果没有表就自动创建。
--filter 对输入的慢查询按指定的字符串进行匹配过滤后再进行分析
--limit限制输出结果百分比或数量，默认值是20,即将最慢的20条语句输出，如果是50%则按总响应时间占比从大到小排序，输出到总和达到50%位置截止。
--host mysql服务器地址
--user mysql用户名
--password mysql用户密码
--history 将分析结果保存到表中，分析结果比较详细，下次再使用--history时，如果存在相同的语句，且查询所在的时间区间和历史表中的不同，则会记录到数据表中，可以通过查询同一CHECKSUM来比较某类型查询的历史变化。
--review 将分析结果保存到表中，这个分析只是对查询条件进行参数化，一个类型的查询一条记录，比较简单。当下次使用--review时，如果存在相同的语句分析，就不会记录到数据表中。
--output 分析结果输出类型，值可以是report(标准分析报告)、slowlog(Mysql slow log)、json、json-anon，一般使用report，以便于阅读。
--since 从什么时间开始分析，值为字符串，可以是指定的某个”yyyy-mm-dd [hh:mm:ss]”格式的时间点，也可以是简单的一个时间值：s(秒)、h(小时)、m(分钟)、d(天)，如12h就表示从12小时前开始统计。
--until 截止时间，配合—since可以分析一段时间内的慢查询。
--create-history-table 当使用--history参数把分析结果输出到表中时，如果没有表就自动创建。
--filter 对输入的慢查询按指定的字符串进行匹配过滤后再进行分析
--limit限制输出结果百分比或数量，默认值是20,即将最慢的20条语句输出，如果是50%则按总响应时间占比从大到小排序，输出到总和达到50%位置截止。
--host mysql服务器地址
--user mysql用户名
--password mysql用户密码
--history 将分析结果保存到表中，分析结果比较详细，下次再使用--history时，如果存在相同的语句，且查询所在的时间区间和历史表中的不同，则会记录到数据表中，可以通过查询同一CHECKSUM来比较某类型查询的历史变化。
--review 将分析结果保存到表中，这个分析只是对查询条件进行参数化，一个类型的查询一条记录，比较简单。当下次使用--review时，如果存在相同的语句分析，就不会记录到数据表中。
--output 分析结果输出类型，值可以是report(标准分析报告)、slowlog(Mysql slow log)、json、json-anon，一般使用report，以便于阅读。
--since 从什么时间开始分析，值为字符串，可以是指定的某个”yyyy-mm-dd [hh:mm:ss]”格式的时间点，也可以是简单的一个时间值：s(秒)、h(小时)、m(分钟)、d(天)，如12h就表示从12小时前开始统计。
--until 截止时间，配合—since可以分析一段时间内的慢查询。
慢查询方式
索引相关问题
索引缺失
对于经常性的查询需求，可以建索引，偶尔查询内容不需要建。
索引重复
主要是复合索引和单列索引有重复的
索引过多
一般单表索引建议在7个以内，尽量不要让索引空间过大，会影响插入性能。
索引优化
对于比较长的varchar类型的字段建索引，可以考虑建截断索引。
截断长度覆盖80%的表数据即可。
截断长度覆盖80%的表数据即可。
索引未使用
对字段进行函数类操作也会导致无法使用索引。
索引匹配度问题
索引字段的重复率过高，导致查询匹配的行数过多
估计行数问题
由于更新删除比较多，数据库中的表的估计行数和实际行数已经不一致。
这个时候需要对数据库的统计信息进行重新计算，主要是重新排列表的数据存储，减小碎片率。
数据的存储空间会变少，查询性能也会有一定的提高。
这个时候需要对数据库的统计信息进行重新计算，主要是重新排列表的数据存储，减小碎片率。
数据的存储空间会变少，查询性能也会有一定的提高。
mysql数据库的删除其实在数据底层，不是物理删除，是会先打一个删除标记。再通过其他进程来定时删除物理存储的。
所以大批量的删除，是不能实时的减小表的空间的。
所以大批量的删除，是不能实时的减小表的空间的。
字段类型隐式转换
举例：查询时把int类型和varchar类型的数字关联错误，导致字段隐式转换
select colID,name,smIconID,extend as singerID
from col
where colType = 2 and extend = 733570
limit 1
from col
where colType = 2 and extend = 733570
limit 1
extend是varchar类型，不能用直接和int类型相等，会涉及到数据类型的隐式转换，导致无法使用索引，改写sql
使用主键进行查找
大批量的更新时尽量使用主键进行查找
尽量关联等式查询条件
select count(*) from sys_msg_log
where cmd != 'CMD_USER' and cmd != 'CMD_SUB_GUIDE' and cmd != 'CMD_UPDATE_APP' and cmd != 'CMD_IMEI'
and cmd not in
(
'CMD_NOTIFY'
,
'CMD_USER'
)
where cmd != 'CMD_USER' and cmd != 'CMD_SUB_GUIDE' and cmd != 'CMD_UPDATE_APP' and cmd != 'CMD_IMEI'
and cmd not in
(
'CMD_NOTIFY'
,
'CMD_USER'
)
这里cmd是否可以改成等于或者in条件，然后加上cmd索引就可以用上了。
倒序问题
低版本的mysql中，对索引字段的倒序排序，会导致无法有效使用索引，从正常的内存排序降级为文件排序了
select m.musicID,m.name,m.albumt,m.cover,m.addDate,s.name as singerNames
from music m LEFT JOIN singer s ON m.singerID = s.singerID
where m.status = 1 and m.cpID = 15531
order by m.musicID desc
limit 0,5
from music m LEFT JOIN singer s ON m.singerID = s.singerID
where m.status = 1 and m.cpID = 15531
order by m.musicID desc
limit 0,5
唯一索引问题
对于唯一索引，需要判断是否需要，唯一索引对于插入的性能影响是最大的，每次单条插入都需要匹配全表查询唯一性。
考虑具体业务规划，唯一性约束通过业务逻辑实现，将数据库端的唯一索引降级为普通的单列索引或者复合索引。
考虑具体业务规划，唯一性约束通过业务逻辑实现，将数据库端的唯一索引降级为普通的单列索引或者复合索引。
单表数据的重复调用问题
当前表只有1行数据，总计有20,942,177次调用。
单行数据的超多次调用，考虑是否为静态资源，可以弄到缓存中
单行数据的超多次调用，考虑是否为静态资源，可以弄到缓存中
SELECT taskID
FROM daily_tasks
FROM daily_tasks
字段长度问题
当前状态：imei，varchar(100)，的唯一索引
注：imei,国际移动设备识别码，由15位数字组成，有些手机是17位，多出来的2个用于记录软件版本。
注：imei,国际移动设备识别码，由15位数字组成，有些手机是17位，多出来的2个用于记录软件版本。
SELECT id
FROM fcm_token0
WHERE imei = ?
FROM fcm_token0
WHERE imei = ?
0 条评论
下一页
