优化目的
在我们项目上线初期，可能我们表里面的数据量很小，一些SQL的执行效率对程序运行效率的影响不太明显，但随着时间的积累，业务数据量的增多，SQL的执行效率对程序的运行效率的影响逐渐增大，此时对SQL的优化就很有必要。
优化步骤
第一步：通过慢查日志等定位那些执行效率较低的SQL语句
- 查询日志开启状态
mysql> show variables like 'slow_query%';
查询结果：
参数说明：
slow_query_log 慢查询开启状态 slow_query_log_file 慢查询日志存放的位置（这个目录需要MySQL的运行帐号的可写权限，一般设置为MySQL的数据存放目录） long_query_time 查询超过多少秒才记录，可以通过下面的命令查询
- 查询慢sql临界时间
mysql> show variables like 'long_query_time%';
查询结果：
这里我们可以看到我们项目的慢日志的临界时间为1s，sql超过1s的就会被记录到慢日志文件里面，方便我们进行相应的优化，当然我们可以自定义超时时间，命令如下：
mysql> set global long_query_time=1;
第二步：通过慢sql日志，用explain进行分析
如：
EXPLAIN SELECT * FROM tb_use_statistics_report WHERE class_id = '11'
查询结果：
总共有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，数据库集群，读写分离、数据量非常庞大了我们可以考虑分库分表）
数据库与操作系统配置（升级服务器配置）
考虑应用大数据方案
