什么是慢查询?

慢查询的定义在不同的场景下有不同的限定条件,但是一般都是以查询速度来定义是否为慢查询。其实就是查询很慢,比如常见的分页列表多条件联合+子查询+多表关联+海量数据,常见的电商平台场景最多。我经历过的慢查询定义是2s,超过2s的sql都定义为慢查询。慢查询不仅会大幅度降低用户体验而且非常消耗数据库和系统性能,那么,慢查询要怎么解决呢?

如何优化

分析sql

首先对sql结构分析,相关的表、字段、条件等都是分析的要点,那么怎么分析呢?
对于查询语句,先看索引,所以的建立对于查询效率的提升是很明显的,条件查询比如where、group by等是否使用索引,索引的设置一般都以条件为主,例如select * from user where age>=20 group by address,这里面age和address都是条件,可以在这两个字段上建立索引。
再看查询字段是否多表关联,多表关联的外键和关联关系,再看子查询发散。这些分析都可以通过sql的执行计划一步步看。
复制代码

sql执行计划

这里简单摘录一下如何使用执行计划分析sql

explain select * from user;

通过explain结果，可以看到有id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra字段。
这些字段都有什么用呢?什么含义?

id
代表SQL语句的执行顺序

id如果相同，可以认为是同一组，从上往下顺序执行
id值越大，优先级越高，越先执行；对于子查询，id的序号会递增
复制代码

select_type
select查询的类型，主要用以区别普通查询和联合查询、子查询之类的复杂查询
-   SIMPLE：简单SELECT，不使用UNION或子查询等

-   PRIMARY：子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY

-   UNION：UNION中的第二个或后面的SELECT语句

-   DEPENDENT UNION：UNION中的第二个或后面的SELECT语句，取决于外面的查询

-   UNION RESULT：UNION的结果，union语句中第二个select开始后面所有select

-   SUBQUERY：子查询中的第一个SELECT，结果不依赖于外部查询

-   DEPENDENT SUBQUERY：子查询中的第一个SELECT，依赖于外部查询

-   DERIVED：派生表的SELECT, FROM子句的子查询

-   UNCACHEABLE SUBQUERY：一个子查询的结果不能被缓存，必须重新评估外链接的第一行
复制代码

table
表示这一步SQL所访问的表名称，即这一行的数据来源于哪张表。有时不是真实的表名字，可能是别名，也可能是第几步执行的结果的简称
复制代码

partitions
如果查询是基于分区表的话，会显示查询将访问的分区
复制代码

type

对表的访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。通过type可以快速查看SQL性能。

常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）

ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
index：Full Index Scan，index与ALL区别为index类型只遍历索引树
range：只检索给定范围的行，使用一个索引来选择行
-      ref：表示表的连接匹配条件，即哪些列或常量被用于查找索引列上的值，查指定值，而不是范围
-      eq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
-      const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system
-      NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。
复制代码

possible_keys

显示可能应用的索引，一般一个或者多个。SQL语句涉及到的字段上若存在索引，则该索引被列出，但是不一定被实际使用。如果该列是NULL，则没有相关的索引
复制代码

key

key列显示MySQL实际决定使用的键（索引），大概率包含在possible_keys中。
key的值不一定是possible-keys的子集。SQL语句中的字段，若没有涉及到索引，MySQL选择覆盖索引，则该索引仅出现在key列表中。
如果没有选择索引，键是NULL。
要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
复制代码

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。
key-len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。
复制代码

ref

显示索引的哪一列被使用了。有时候会是一个常量，表示哪些列或常量被用于查找索引列上的值
复制代码

rows

估算结果集行数，表示MySQL根据表统计信息及索引选用情况，估算找到所需记录需要读取的行数。表统计信息中，有一个名词为基数，表示一个索引上不同的值的个数。采样统计的时候，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。
复制代码

filtered

filtered表示返回结果的行数占需读取行数的百分比，filtered列的值越大越好，也是估算值
复制代码

Extra

该列包含MySQL解决查询的详细信息，如是否使用文件等，主要有以下几种情况：

Using where：使用where过滤条件
Using temporary（十死无生的提示，极大影响mysql性能，需要尽快优化）：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by。group by一定要遵循所建索引的顺序。
-    Using filesort（九死一生的提示，需要尽快优化）：当Query中包含 order by 操作，而且无法利用索引完成
    的排序操作称为“文件排序”。排序的时候最好遵循所建索引的顺序否则就可能会出现Using filesort。
-    Using Index(效率不错)：
    表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错
    如果同时出现Using Where，表明索引被用来执行索引键值的查找\
    如果没有同时出现Using Where，表明索引用来读取数据而非执行查找工作
-    Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。
-    Impossible where：这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）。
-    Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行
-    No tables used：Query语句中使用from dual 或不含任何from子句

其中Using filesort，Using temporary，Using index最为常见，出现前两种表示是需要优化的地方，出现第三种表示索引效率不错。
复制代码

执行计划分析完了之后基本上sql就没有问题了,索引机制和相关字段优化都应该差不多了,但是查询还是很慢,如子查询发散,表关联,union等这些sql语句是极其不推荐使用的如果非要在数据库层面做这种操作,推荐使用建立视图的方式,但是视图的维护也是一件比较麻烦的事,所以不建议使用复杂的sql查询,可以通过Java的内存来分担一部分sql的负担。

另外,字符串模糊查询是个麻烦的事情,这里推荐使用elasticSearch做字符串分词,字符串倒排索引实现模糊查询还是很方便的,效率也很高。

以上是查询语句,insert和update语句一般很慢的话可以考虑数据库锁和缓存,数据库在刷新脏页，例如 redo log 写满了需要同步到磁盘。执行的时候，遇到锁，如表锁、行锁。

开启慢查询日志

一般测试环境和生产环境是一定要打开的,测试环境是测试阶段接口响应慢排查问题的日志,生产环境就一定要开启了,毕竟用户感觉到很慢的时候无法及时告诉开发啊,但是开发也需要关注这件事,等用户来反映这个问题就属于后知后觉了,线上环境可能会带来巨额损失,那就要扣奖金了诶,为了钱,这个一定是要打开的。

以mysql为例:

方式一：修改配置文件 在 my.ini 增加几行: 主要是慢查询的定义时间（超过2秒就是慢查询），以及慢查询log日志记录（ slow_query_log）

//定义查过多少秒的查询算是慢查询，我这里定义的是2秒 
long_query_time=2 #5.8、5.1等版本配置如下选项 
log-slow-queries="mysql_slow_query.log" #5.5及以上版本配置如下选项 
slow-query-log=On slow_query_log_file="mysql_slow_query. log" //记录下没有使用索引的query log-query-not-using-indexestpspb16glos dndnorte/t
复制代码

优化器

优化器使用MRR
原理： MRR 【Multi-Range Read】将ID或键值读到buffer排序，通过把「随机磁盘读」，转化为「顺序磁盘读」，减少磁盘IO，从而提高了索引查询的性能 。

mysql >set optimizer_switch**=**’mrr=on’;
explain 查看 Extra多了一个MRR
explain select * from stu where age between 10 and 20；

对于 Myisam，在去磁盘获取完整数据之前，会先按照 rowid 排好序，再去顺序的读取磁盘。
对于 Innodb，则会按照聚簇索引键值排好序，再顺序的读取聚簇索引。

磁盘预读： 请求一页的数据时，可以把后面几页的数据也一起返回，放到数据缓冲池中，这样如果下次刚好需要下一页的数据，就不再需要到磁盘读取（局部性原理）
索引本身就是为了减少磁盘 IO，加快查询，而 MRR，则是把索引减少磁盘 IO 的作用，进一步放大

分库分表、读写分离

上面的都是短时间可以优化看到效果的,接下来做优化就是从架构方向考虑。首先是主从集群,读写分离,主库写,从库读,如果涉及到海量数据分析看板报表这种业务的,可以考虑大数据处理,进行数据切片,当然这需要大数据相关的开发,也可以直接买一些国产大数据服务,例如阿里的oceanDB、麒麟数据处理器(kylin)等。如果再有时间的话就分库分表,垂直+水平综合科学地分析过后拆分,这些都是大工程了,改造点比较多,服务之间的接口和持久化、主从同步、缓存等都需要配合改造的,当然,分库分表的表现是长效的,也是必要的,因为在业务刚开始时,业务逻辑和相关第三方对接等都是不确定的,分库分表是在后来数据业务不断积累的情况下开始的。