MySQL的SQL性能优化
一. 为什么要对SQL进行优化？
公司运营一个产品，在产品初级，可能用户量小，要处理的业务数据也少，某些SQL的执行效率可能对程序的运行效率的影响不太明显，但随着时间的推移，我们要处理的业务数据也越来越多，故此时SQL的执行效率对程序运行效率的影响也越来越大，此时，对SQL的优化就显得很有必要。
二. 优化SQL的一般步骤
-
发现问题：发现存在性能问题的SQL。
-
分析执行计划：所谓执行计划就是MySQL数据库执行SQL的方式。
-
优化索引：判断MySQL数据库是否正确使用索引，若没正确使用索引，那么我们就要对索引进行一定优化。
-
改写SQL：如果单纯的索引优化不能满足我们的需求，那么就要考虑是否要改写SQL，例如把一个复杂SQL分成简单SQL 执行。
当然，如果数据十分庞大，我们还可以进行数据库垂直切分和水平切分的方式对数据库进行优化，这就是题外话了 。
接下来，我们按照这个步骤，一步步分析SQL优化。
2.1 发现问题
发现存在性能问题的SQL有多种渠道，比较常见的有：
-
用户主动上报应用性能问题，但身为开发人员，我们最好要在用户发现问题之前就解决问题，该方式比较被动。
-
分析慢查询日志发现存在问题的SQL，指定一个时间的阈值，如果发现SQL执行超过该时间，就会被记录到该日志当中，我们就可以分析日志来进行优化调整。
-
数据库实时监控长时间运行的SQL，因为慢查询也是需要时间周期的，我们要先生成日志，然后再分析，通过该方法，我们可以实时监控
2.1.1 慢查询日志
默认情况下，MySQL不会启动慢查询日志，需要手动配置。配置如下：
-
set global slow_query_log = [ON|OFF] // 开启/关闭慢查询日志
-
set global slow_query_log_file = 文件位置/文件.log //不使用默认位置, 设置慢查询日志保存位置
-
set global long_query_time = xx.xxxxxx秒 //设置阈值
-
set global log_queries_not_using_indexes = [ON|OFF] //还可以记录未使用索引的SQL, 记录进慢查询日志
同时MySQL官网还给我们提供了分析慢查询日志的工具, 通过mysqldumpslow [OPTS...] [LOGS...] 进行使用
我们还可以使用第三方工具 pt-query-digest [OPTIONS] [FILES] [DSN] 进行分析。
2.1.2 通过实时监控发现问题
SELECT id，'user', 'host', DB , command , 'time' , state , info FROM **information_schema.PROCESSLIST** WHERE TIME ≥=60
information_schema.PROCESSLIST 会记录正在执行的SQL的信息, TIME列就是SQL运行的时长, 我们可以通过设置时长来实时监
控长时间运行的SQL。
2.2 分析执行计划
我们为什么要关注执行计划？
为了了解SQL如何访问表中的数据，为了了解SQL如何使用表中的索引，为了了解SQL所用的查询类型
如何获取执行计划？
EXPLAIN [explaintable_stmt | FOR CONNECTION connection_id ] SQL语句
如何分析执行计划?
首先我们来看一下执行计划里都是什么东西?
EXPLAIN
SELECT course_id,class_name,level_name,title,study_cnt
FROM course a
JOIN class b ON b.class_id = a.class_id
JOIN level c ON c.level_id = a.level_id
WHERE study_cnt > 3000
执行SQL语句后, MySQL给我们返回了那么多字段, 这些字段就组成了MySQL的执行计划, 接下来逐一介绍这些字段
id
id通常为数字，表示查询执行的顺序，当id相同时由上到下执行，id不同时由大到小执行。
select_type
查询类型，表示执行的SQL语句查询的类型是什么，包含以下几种：
SIMPLE 不包含子查询或是UNION操作的查询
PRIMARY 查询中如果包含子查询，那么最外层查询被标记为PRIMARY
SUBQUERY SELECT列表中的子查询
DEPENDENT SUBQUERY 依赖外部结果的子查询
UNION UNION操作的第二个或是之后的查询的值为UNION
DEPENDENT UNION 当UNION作为子查询时，第二或是第二个后的查询的select_type值
UNION RESULT UNION产生的结果集
DERIVED 出现在FROM子句中的子查询
table
指明是从哪个表中获取数据，包括以下几种数据：
表名
<union M,N>由ID为M,N查询union产生的结果集
partitions
分区表
type(重点)
type字段表明了这次查询采用了哪种查询方式，如各种索引，全表扫描，我们进行优化也是要看这一字段
keys系列字段（重点）
possible_key可能用到的索引，查询可能会用到的索引，不会完全使用到。
key实际使用的索引。
key_len实际使用索引的最大长度(如varchar(4))。
ref
指出哪些列或常量被用于索引查找
rows
根据统计信息预估的扫描的行数
filtered
表示返回结果的行数占需读取行数的百分比
extra
不适合在其他列中显示的额外信息：
介绍完了执行计划的字段，那么如何分析执行计划？我们要关注type字段，尽可能让他选用性能高的查询方式，关注keys字段，让查询
用上索引查询，关注rows和filtered，尽可能扫描少的行数，尽可能使得返回的结果行数占比要高。
了解了这下，接下来就要走进SQL优化了！
2.3 SQL优化之索引优化(目标:使得每次执行都用到索引)
索引到底是什么?
类似于书中的目录,告诉存储引擎如何快速查到所需要的数据
Innodb支持的索引类型
Btree索引(广泛,常见),自适应HASH索引,全文索引,空间索引
Btree索引特点:
全值匹配(class_name='mysql' , in('mysql','redis'))
使用in列表无法使用索引是误解,其实是in中东西过多了,mysql优化器才可能使用全表扫描.
索引顺序不一定相同,mysql优化器可以自动调整顺序
应该在什么列上建立索引?
通常情况下:
- where子句中的列,注意选有筛选性的,即重复值少的,如主键列,筛选性不好的列没啥作用
EXPLAIN
SELECT user_nick
FROM user
WHERE sex=1 AND reg_time>'2019-01-01'
select count(distinct sex),count(distinct DATE_FORMAT(reg_time),'%Y-%m-%d'),
COUNT(*),count(distinct sex)/COUNT(*),count(distinct DATE_FORMAT(reg_time),'%Y-%m-%d')/COUNT(*)
from user
create index idx_regtime on user(reg_time)
用sex就没啥用
-
orderby,group by,distinct.orderby
-
多表join关联列
但是要具体情况具体分析
实战:
如何选择复合索引键的顺序?
create index idx on user(a,b,c) 把筛选性好的往前放,好坏依次往下排
索引使用误区
索引不是越多越好,索引太多了那么MySQL形成执行计划的时间也就越长,同样会拖慢效率
2.4 SQL优化之改写SQL
SQL优化还有一种方式为改写SQL，主要遵循以下原则：
-
使用outer join代替not in（mysql8.0开始支持自动转了，现在不用担心~）
-
使用CTE（公共表表达式）代替子查询
-
拆分复杂的大SQL为多个简单的小SQL
-
巧用计算列优化查询
浙公网安备 33010602011771号