1 背景
我们的业务服务随着功能规模扩大,用户量扩增,流量的不断的增长,经常会遇到一个问题,就是数据存储服务响应变慢。
导致数据库服务变慢的诱因很多,而RD最重要的工作之一就是找到问题并解决问题。
下面以MySQL为例子,我们从几个角度分析可能产生原因,并讨论解决的方案。
2 定位慢查询的原因并优化
2.1 慢查询的分析
开启SlowLog,默认是关闭的,由参数slow_query_log决定,在MySQL命令终端中输入下面的命令:
# 是否开启,这边为开启,默认情况下是off
set global slow_query_log=on;
# 设置慢查询阈值,单位是 s,默认为10s,这边的意思是查询耗时超过0.5s,便会记录到慢查询日志里面
set global long_query_time=0.5;
# 确定慢查询日志的文件名和路径
mysql> show global variables like 'slow_query_log_file';
+---------------------+-------------------------------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------------------------------+
| slow_query_log_file | /usr/local/mysql/data/MacintoshdeMacBook-Pro-slow.log |
+---------------------+-------------------------------------------------------+
1 row in set (0.00 sec)
# 检查慢查询的详细指标,可以看到下面 slow_query_log = ON,long_query_time = 0.5 ,都是因为我们调整过的
mysql> show global variables like '%quer%';
+----------------------------------------+-------------------------------------------------------+
| Variable_name | Value |
+----------------------------------------+-------------------------------------------------------+
| binlog_rows_query_log_events | OFF |
| ft_query_expansion_limit | 20 |
| have_query_cache | NO |
| log_queries_not_using_indexes | OFF |
| log_throttle_queries_not_using_indexes | 0 |
| long_query_time | 0.500000 |
| query_alloc_block_size | 8192 |
| query_prealloc_size | 8192 |
| slow_query_log | ON |
| slow_query_log_file | /usr/local/mysql/data/MacintoshdeMacBook-Pro-slow.log |
+----------------------------------------+-------------------------------------------------------+
10 rows in set (0.01 sec)
配置好之后,就会按照阈值默认把慢查询日志收集下来,可以到对应的目录下分析具体的慢请求原因。
2.2 使用Explain进行查询语句分析
2.2.1 分析过程举例
很多时候我们在评审RD同学代码和SQL脚本的时候,上下文和使用环境不了解,不能做出很准确的判断。
这时候使用Explain分析SQL的执行计划就显得非常有用,拿到具体环境中Run一下就能看出很多问题。
举个例子:
模拟一个千万级别的雇员表,我们在没有做索引的字段上做一下查询看看,在500W数据中查询一个名叫LsHfFJA的员工,消耗 2.239S ,获取到一条id为4582071的数据。
再看看他的执行计划,扫描了4952492 条数据才找到该行数据:
mysql> explain select * from emp where empname='LsHfFJA';
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | emp | ALL | NULL | NULL | NULL | NULL | 4952492 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set
这就是无索引或者索引不合理的结果,这个时候我们就可以根据实际情况进行查询优化了。
2.2.2 Explain需要关注的指标
比较核心要关注的字段一般有 select_type、type、possible_keys、key、rows、Extra等
我们来一个个说明:
- select_type:代表表示查询中每个select子句的类型,是简单查询还是联合查询还是子查询,一目了然。咱们上面的例子是SIMPLE,代表简单查询,其他枚举参考下列表格:
select_type的值 | 解释 |
---|---|
SIMPLE | 简单查询(不使用关联查询或子查询) |
PRIMARY | 如果包含关联查询或者子查询,则最外层的查询部分标记primary |
UNION | 联合查询(UNION)中第二个及后面的查询 |
DEPENDENT UNION | UNION中的第二个或后面的SELECT语句,取决于外面的查询 |
UNION RESULT | UNION的结果,union语句中第二个select开始后面所有select |
SUBQUERY | 字查询中的第一个擦讯 |
DEPENDENT SUBQUERY | 子查询中的第一个查询,并且依赖外部查询 |
DERIVED | 派生表的SELECT, FROM子句的子查询 |
MATERIALIZED | 被物化的子查询 |
UNCACHEABLE SUBQUERY | 一个子查询的结果不能被缓存,必须重新评估外链接的第一行 |
- type:表示MySQL在表中查找所需数据的方式,也称“访问类型”,咱们上面的例子是All,代表全表扫描,是非常差的模式,其他枚举参考下列表格:
type的值 | 解释 |
---|---|
system | 查询对象表只有一行数据,且只能用于MyISAM和Memory引擎的表,这是最好的情况 |
const | 基于主键或唯一索引查询,最多返回一条结果 |
eq_ref | 类似ref,区别就在使用的索引是唯一索引,对于每个索引键值,表中只有一条记录匹配,简单来说,就是多表连接中使用primary key或者 unique key作为关联条件 |
ref | 表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值 |
fulltext | 全文检索 |
ref_or_null | 表连接类型是ref,但进行扫描的索引列中可能包含NULL值 |
index_merge | 利用多个索引 |
unique_subquery | 子查询中使用唯一索引 |
index_subquery | 子查询中使用普通索引 |
range | 只检索给定范围的行,使用一个索引来选择行 |
index | Full Index Scan,index与ALL区别为index类型只遍历索引树 |
ALL | Full Table Scan, MySQL将遍历全表以找到匹配的行 |
- possible_keys:应该或建议使用的索引
表示MySQL能使用哪个索引在表中找到记录,查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询使用。这个趋向于指导性作用。
- key:实际使用的索引,没有的情况下为NULL
显示MySQL在查询中实际使用的索引,若没有使用索引,显示为NULL
- rows:预估扫描了了多少行,咱们上面的例子 4952492 ,非常不合理。
表示MySQL根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数。基本表现为实际扫描过的行数。
3 一些使用上的规范
3.1 分析是否有不合理的查询
★ 以下是我们团队的准入规范,也是CodeReview 标准。
- 尽量避免使用select *,join语句使用select * 可能导致只需要访问索引即可完成的查询需要回表取数。
一种是可能取出很多不需要的数据,对于宽表来说,这是灾难;一种是尽可能避免回表,因为取一些根本不需要的数据而回表导致性能低下,是很不合算。 - 严禁使用select * from t_name,不加任何where条件,道理一样,这样会变成全表全字段扫描。
- MySQL中的text类型字段存储:
- 不与其他普通字段存放在一起,因为读取效率低,也会影响其他轻量字段存取效率。大宽表、大字段表,整体性能也不好。
- 如果不需要text类型字段,又使用了select *,会让该执行消耗大量io,效率也很低下
- 在取出字段上可以使用相关函数,但应尽可能避免出现 now() , rand() , sysdate() 等不确定结果的函数,在Where条件中的过滤条件字段上严禁使用任何函数,包括数据类型转换函数。大量的计算和转换会造成效率低下,这个在索引那边也描述过了。
- 分页查询语句全部都需要带有排序条件 , 否则很容易引起乱序
- 用in()/union替换or,效率会好一些,并注意in的个数小于300
- 严禁使用%前缀进行模糊前缀查询。
-- 如下,这种查询会导致扫描表:
select a,b,c from t_name where a like '%name';
-- 可以使用%模糊后缀查询如:
select a,b from t_name where a like 'name%';
- 尽量避免使用子查询,可以把子查询优化为join操作,通常子查询在in子句中,且子查询中为简单SQL(不包含union、group by、order by、limit从句)时,才可以把子查询转化为关联查询进行优化。子查询性能差的原因:
- 子查询的结果集无法使用索引,通常子查询的结果集会被存储到临时表中,不论是内存临时表还是磁盘临时表都不会存在索引,所以查询性能会受到一定的影响;
- 特别是对于返回结果集比较大的子查询,其对查询性能的影响也就越大;
- 由于子查询会产生大量的临时表也没有索引,所以会消耗过多的CPU和IO资源,产生大量的慢查询。
- 在多表join中,尽量选取结果集较小的表作为驱动表,来join其他表。
- 分页查询,当limit起点较高时,可先用过滤条件进行过滤,如下。原理参考这篇
-- 如
select a,b,c from t1 limit 10000,20;
-- 优化为:
select a,b,c from t1 where id>10000 limit 20;
3.2 检查是否有不合理的索引使用
建议参考笔者这篇《构建高性能索引(策略篇)》,比较完整
- 索引区分度(> 0.2)
索引必须创建在索引选择性(区分度)较高的列上,选择性的计算方式为:
selecttivity = count(distinct c_name)/count(*) ;
如果区分度结果小于0.2,则不建议在此列上创建索引,否则大概率会拖慢SQL执行
- 遵循最左前缀,将索引区分度最高的放在左边
对于确定需要组成组合索引的多个字段,设计时建议将选择性高的字段靠前放。使用时,组合索引的首字段,必须在where条件中,且需要按照最左前缀规则去匹配。
正确理解和计算索引字段的区分度,文中有计算规则,区分度高的索引,可以快速得定位数据,区分度太低,无法有效的利用索引,可能需要扫描大量数据页,和不使用索引没什么差别。
-
禁止使用外键,可以在程序级别来约束完整性
-
varchar、text类型字段如果需要创建索引,必须使用前缀索引。
前缀索引计算公式如下,calcul_len 是数字,长度为1 ~ c_name字段的最长值,可以逐一比较,对比区分度最高的出来
正确理解和计算前缀索引的字段长度,文中有判断规则,合适的长度要保证高的区分度和最恰当的索引存储容量,只有达到最佳状态,才是保证高效率的索引。
select count(distinct left(`c_name`,calcul_len))/count(*) from t_name;
- 单张表的索引数量理论上应控制在5个以内。经常有大批量插入、更新操作表,应尽量少建索引,索引建立的原则理论上是多读少写的场景。
- ORDER BY,GROUP BY,DISTINCT的字段需要添加在索引的后面,形成覆盖索引
- 联合索引注意最左匹配原则:查询时必须按照从左到右的顺序匹配,MySQL会一直向右匹配索引直到遇到范围查询(>、<、between、like)然后停止匹配。如:
-- 如果建立(depno,empname,job)顺序的索引,job是用不到索引的。
depno=1 and empname>'' and job=1
- 应需而取策略,查询记录的时候,不要一上来就使用*,只取需要的数据,可能的话尽量只利用索引覆盖,可以减少回表操作,提升效率。
- 正确判断是否使用联合索引,应避免索引下推(IPC),减少回表操作,提升效率。
- 避免索引失效的原则:禁止对索引字段使用函数、运算符操作,会使索引失效。这是实际上就是需要保证索引所对应字段的”干净度“。
- 避免非必要的类型转换,字符串字段使用数值进行比较的时候会导致索引无效。
- 模糊查询'%value%'会使索引无效,变为全表扫描,因为无法判断扫描的区间,但是'value%'是可以有效利用索引。
- 索引覆盖排序字段,这样可以减少排序步骤,提升查询效率
- 尽量的扩展索引,非必要不新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可。
举例子:比如一个品牌表,建立的的索引如下,一个主键索引,一个唯一索引
PRIMARYKEY (`id`),
UNIQUEKEY `uni_brand_define` (`app_id`,`define_id`)
实际场景中,建议代码交叉评审,当你同事业务代码中的检索语句如下的时候,应建议调整:
select brand_id,brand_name from ds_brand_system where status=? and define_id=? and app_id=?
建议改成如下:
select brand_id,brand_name from ds_brand_system where app_id=? and define_id=? and status=?
虽然说 MySQL的查询优化器会根据实际索引情况进行顺序优化,所以这边不做强制。但是同等条件下还是按照顺序进行排列,比较清晰,并且节省查询优化器的处理。
4 总结
这边仅仅是从查询语句的角度进行分析,实际上缓存服务变慢的可能性很多,不仅仅是慢查询怎么分析(Slow Log、Explain命令)。还应该全面的分析原因,并给出处理方案,如 分析SQL脚本合理性、建立索引或优化索引、读写分离、垂直+水平分区)、多读少写/冷数据 做缓存、优化数据库的锁竞争、数据库配置调优、硬件资源升级 等等,后面几篇我们慢慢说。
本站文章如无特殊说明,均为本站原创,如若转载,请注明出处:数据库系列:MySQL慢查询分析和性能优化 - Python技术站