SQL优化

优化目的

在我们项目上线初期，可能我们表里面的数据量很小，一些SQL的执行效率对程序运行效率的影响不太明显，但随着时间的积累，业务数据量的增多，SQL的执行效率对程序的运行效率的影响逐渐增大，此时对SQL的优化就很有必要。

优化步骤

第一步：通过慢查日志等定位那些执行效率较低的SQL语句

• 查询日志开启状态

mysql> show variables like 'slow_query%';

查询结果：

image-20211119105117550.png

参数说明：

slow_query_log 慢查询开启状态 slow_query_log_file 慢查询日志存放的位置（这个目录需要MySQL的运行帐号的可写权限，一般设置为MySQL的数据存放目录） long_query_time 查询超过多少秒才记录，可以通过下面的命令查询

• 查询慢sql临界时间

mysql> show variables like 'long_query_time%';

查询结果：

image-20211119105416496.png

这里我们可以看到我们项目的慢日志的临界时间为1s，sql超过1s的就会被记录到慢日志文件里面，方便我们进行相应的优化，当然我们可以自定义超时时间，命令如下：

mysql> set global long_query_time=1;

第二步：通过慢sql日志，用explain进行分析

如：

EXPLAIN SELECT * FROM tb_use_statistics_report WHERE class_id = '11'

查询结果：

image-20211119105808305.png

总共有12列，分别是id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra

列名	解释
id	查询的唯一标识
select_type	查询类型
table	查询的那个表
partitions	匹配的分区
type	join类型
possible_keys	可能使用的索引
key	最终使用的索引
key_len	最终使用的索引的长度
ref	与索引一起被使用的字段或常数
rows	查询扫描的行数，是个估算值
filtered	查询条件所过滤的数据的百分比
Extra	额外的信息

其中在我看来最重要的是type、key列和extra列。

type：由上至下，效率越来越高

• ALL：全表扫描

• index：索引全扫描

• range：索引范围扫描，常用语<、<=、>=、between、in等操作

• ref：使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中

• eq_ref：类似ref，区别在于使用的是唯一索引，使用主键的关联查询

• const/system：单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询

• null：MySQL不访问任何表或索引，直接返回结果

虽然上至下，效率越来越高，但是根据cost模型，假设有两个索引idx1(a, b, c),idx2(a, c)，SQL为select * from t where a = 1 and b in (1, 2) order by c;如果走idx1，那么是type为range，如果走idx2，那么type是ref；当需要扫描的行数，使用idx2大约是idx1的5倍以上时，会用idx1，否则会用idx2

key

key 列显示了 SQL 实际使用索引，通常是 possible_keys 列中的索引之一，MySQL 优化器一般会通过计算扫描行数来选择更适合的索引，如果没有选择索引，则返回 NULL。

当然，MySQL 优化器存在选择索引错误的情况，可以通过修改 SQL 强制MySQL“使用或忽视某个索引”：

强制使用一个索引：FORCE INDEX (index_name)、USE INDEX (index_name)；

强制忽略一个索引：IGNORE INDEX (index_name)。

Extra

表示MySQL解析查询的其他信息。 Extra是EXPLAIN输出中另外一个很重要的列，该列显示MySQL在查询过程中的一些详细信息，MySQL查询优化器执行查询的过程中对查询计划的重要补充信息。

• Using filesort：MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行。

• Using temporary：使用了临时表保存中间结果，性能特别差，需要重点优化

• Using index：表示相应的 select 操作中使用了覆盖索引（Coveing Index）,避免访问了表的数据行，效率不错！如果同时出现 using where，意味着无法直接通过索引查找来查询到符合条件的数据。

• Using index condition：MySQL5.6之后新增的ICP，using index condtion就是使用了ICP（索引下推），在存储引擎层进行数据过滤，而不是在服务层过滤，利用索引现有的数据减少回表的数据。

第3步：show profile 分析

了解SQL执行的线程的状态及消耗的时间。默认是关闭的，开启语句“set profiling = 1;”

SHOW PROFILES ;
SHOW PROFILE FOR QUERY  #{id};

第4步：trace

trace分析优化器如何选择执行计划，通过trace文件能够进一步了解为什么优先选择A执行计划而不选择B执行计划。

set optimizer_trace="enabled=on";
set optimizer_trace_max_mem_size=1000000;
select * from information_schema.optimizer_trace;

第5步：确定问题并采用相应的措施

• 优化索引

• 优化SQL语句：修改SQL、IN 查询分段、时间查询分段、基于上一次数据过滤

• 改用其他实现方式：ES、数仓等

• 数据碎片处理

优化措施

一、索引

通过explain进行sql分析看我们的检索是否走了索引，看我们的查询字段是否建立了索引，建立的索引是否生效
比如下面两种场景下：
场景一：最左匹配
索引

KEY `idx_classid_userid` (`class_id`,`user_id`)

SQL语句

select * from user  where user_id='xxx';

查询匹配从左往右匹配，要使用user_id走索引，必须查询条件携带class_id或者索引(class_id,user_id)调换前后顺序。

场景一：隐式转换

索引

KEY `idx_mobile` (`mobile`)

SQL语句

select * from user where mobile=12345678901;

隐式转换相当于在索引上做运算，会让索引失效。mobile是字符类型，使用了数字，应该使用字符串匹配，否则MySQL会用到隐式替换，导致索引失效。

在这里我们可以了解下索引失效情况
索引失效情况

情况一：where语句中包含or时，可能会导致索引失效

使用or并不是一定会使索引失效，你需要看or左右两边的查询列是否命中相同的索引。

-- 假设user表中的user_id列有索引，age列没有索引
-- 能命中索引
select * from user where user_id = 1 or user_id = 2;
-- 无法命中索引
select * from user where user_id = 1 or age = 20;
-- 假设age列也有索引的话，依然是无法命中索引的
select * from user where user_id = 1 or age = 20;

可以根据情况尽量使用union all或者in来代替，这两个语句的执行效率也比or好些。

情况二：where语句中索引列使用了负向查询，可能会导致索引失效

负向查询包括：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等。其实负向查询并不绝对会索引失效，这要看MySQL优化器的判断，全表扫描或者走索引哪个成本低了。

情况三：索引字段可以为null，使用is null或is not null时，可能会导致索引失效

其实单个索引字段，使用is null或is not null时，是可以命中索引的。

情况四：在索引列上使用内置函数，一定会导致索引失效

比如下面语句中索引列login_time上使用了函数，会索引失效：

select * from user where DATE_ADD(login_time, INTERVAL 1 DAY) = 7;

情况五：隐式类型转换导致的索引失效

如下面语句中索引列user_id为varchar类型，不会命中索引：

select * from user where user_id = 12;

情况六：对索引列进行运算，一定会导致索引失效

运算如+，-，*，/等，如下：

select * from user where age - 1 = 10;

优化的话，要把运算放在值上，或者在应用程序中直接算好，比如：

select * from user where age = 10 - 1;

情况七：like通配符可能会导致索引失效

like查询以%开头时，会导致索引失效。解决办法有两种：

• 将%移到后面，如：

select * from user where `name` like '李%';

• 利用覆盖索引来命中索引：

select name from user where `name` like '%李%';

情况八：MySQL优化器的最终选择，不走索引
上面有提到，即使完全符合索引生效的场景，考虑到实际数据量等原因，最终是否使用索引还要看MySQL优化器的判断。当然你也可以在sql语句中写明强制走某个索引。

二、深分页

sql

select * from  table  where col1 = 1 order by col2 desc limit 10000, 10;

对于深分页的场景，我们可以先和产品沟通，让产品优化需求，不展示那么多数据，如果必须要展示，可以采用下面两种方式：

• 把上一次的最后一条数据的主键id传过来，然后做“col2 < xxx”处理，但是这种适合于自增ID的
• 采用延迟关联的方式进行处理，减少SQL回表，但是要记得索引需要完全覆盖才有效果，SQL改动如下

SELECT t1.* FROM table t1, (SELECT id FROM table WHERE col1=1 ORDER BY col2 DESC LIMIT 10000,10) t2  WHERE t1.id=t2.id;

三、复杂查询

如果是统计某些数据，可能改用数仓进行解决；如果是业务上就有那么复杂的查询，就不建议继续走SQL了，而是采用其他的方式进行解决，比如使用ES等进行解决。

四、大数据

对于推送业务的数据存储，可能数据量会很大，如果在方案的选择上，最终选择存储在MySQL上，并且做7天等有效期的保存。那么需要注意，频繁的清理数据，会照成数据碎片，需要联系DBA进行数据碎片处理。

五、mysql查询慢优化（从mysql架构出发来看）：

• 看看连接数够不够（从配置进行优化）（优化工具：宝塔）

  查看最大连接数命令：

show  variables like "max_connections"

查看链接超时时间命令

show global  variables like "wait_timeout"

这个问题我们可以从两方面来解决

1、增加服务端可用链接数

2、减少客户端可用链接数（druid、hikari、DBCP、C3P0）

最适合链接数（维护）：（cpu核数*2）+ 1

• C3p0

开源的，成熟的，高并发第三方数据库连接池，作者是 Steve Waldman，相关的文档资料比较完善，大名鼎鼎的hibernate框架就使用了c3p0数据库连接池。 项目地址：http://www.mchange.com/projects/c3p0/index.html

• dbcp 全称是DataBase Connection Pool，它是由Apache开发的一个数据库连接池，在tomcat7版本之前都是使用dbcp作为数据库连接池，不过dbcp性能不太好，apache又开发了tomcat jdbc pool来替代dbcp。 项目地址：http://commons.apache.org/proper/commons-dbcp/ tomcat jdbc pool 由于dbcp的性能不太好，apache又新开发了一款数据库连接池-tomcat jdbc pool，有的地方也称之为JDBC Connection Pool。 项目地址：http://tomcat.apache.org/tomcat-9.0-doc/jdbc-pool.html

• Druid 作者是阿里巴巴的wenshao，号称是Java语言中最好的数据库连接池。Druid能够提供强大的监控和扩展功能。 项目地址：https://github.com/alibaba/druid

• BoneCP 其官方说该数据库连接池性能非常棒，不过现在已经不更新了，转到了HiKariCP上。 项目地址：http://www.jolbox.com/

• HiKariCP Hikari是日语光的意思，作者可能想以此来表达HiKariCP速度之快。比之前的BoneCP性能更加强大，它官方展示了一些性能对比的数据，通过数据可以看出HiKariCP完虐c3p0，dbcp，tomcat jdbc pool等其他数据库连接池。并且它的库文件差不多就130kb，非常轻巧。 项目地址：https://github.com/brettwooldridge/HikariCP

• Proxool 早期的一些项目中使用的多一些，现在该数据库连接池源码已经有一阵子不更新了。 项目地址：http://proxool.sourceforge.net/

六、大表优化

当我们的表的数据量超过一定量时，我们可以考虑进行分区、分库分表。

最后我们总结下优化我们可以从下面几个方面进行入手：

• 优化SQL与索引（是否用到索引、避免全表扫描）

• 表与存储引擎（表结构的设计、用的存储引擎、是否需要分区（数据超过一千万可以考虑分区））

• 数据库与应用架构 （磁盘数据放到缓存里面比如redis，数据库集群，读写分离、数据量非常庞大了我们可以考虑分库分表）

• 数据库与操作系统配置（升级服务器配置）

• 考虑应用大数据方案