0. SQL语句的执行顺序

1. FROM,明确数据在哪？单表就直接把该表数据加载到内存；两个表就将其笛卡尔积生成临时虚拟表tmp1。
2. ON ，指JOIN后面的ON，把临时表tmp中不符合的记录给过滤掉，形成虚表tmp2。
3. JOIN，根据JOIN的类型来对虚表tmp2做行的增减。(INNER) JOIN不会改动，主要针对的是OUTER JOIN，形成虚表tmp3。
4. WHERE ，对关联后的所有数据即虚表tmp3做过滤操作，形成虚表tmp4。
5. GROUP BY，对数据做聚合操作，必须放在正确的结果集后执行，形成虚表tmp5。
6. HAVING，对group by 聚合后的数据进行过滤操作，形成虚表tmp6。
7. SELECT， 将经过层层过滤后的数据按照指定的列给筛选出来，形成虚表tmp7。
8. DISTINCT， 对虚表tmp7做指定列的去重操作，形成虚表tmp8 。
9. ORDER BY，按照指定的列进行排序，形成结果集tmp9。
10. LIMIT，对结果集做行上的处理，做限制输出，得到最终的结果集tmp10并输出。

1. EXPLAIN 分析SQL的具体执行计划

1. 通过key检查是否命中了索引，如果本身已经添加了索引，也可以判断索引是否有失效的情况
2. 通过type查看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描（显示为 ALL 或 index)。可以通过创建适当的索引来优化查询。
3. 通过extra建议来判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复
   • Using index：表明查询使用了覆盖索引，不用回表，查询效率非常高。
   • Using index condition：表示查询优化器选择使用了索引条件下推这个特性。
   • Using where：表明查询使用了 WHERE 子句进行条件过滤。一般在没有使用到索引的时候会出现。

2. 慢sql的优化分析思路？

1.查看慢查询日志记录，分析慢SQL

• 设置查询时间 set long_query_time = ?，默认是10。
• 开启慢日志SET GLOBAL slow_query_log = "ON";
• 通过慢查询日志（slow log），定位那些执行效率较低的SQL语句，重点关注分析

2.explain查看分析SQL的执行计划

3.show processlist展示当前MySQL正在执行的线程

发现一些状态显示为Lock等的慢查询。

4.设置profiling=1,并执行show profiles

explain只是看到SQL的预估执行计划，而profiling参数可以了解SQL真正的执行线程状态及消耗的时间，记录下每条SQL语句的执行细节和时间,，包括IO，上下文切换，CPU，内存等资源开销。

5.Optimizer Trace分析详情

profile只能查看到SQL的执行耗时，但是无法看到SQL真正执行的过程信息，即不知道MySQL优化器是如何选择执行计划。这时候，我们可以使用Optimizer Trace，它可以跟踪执行语句的解析优化执行的全过程。

6.确定问题并采用相应的措施

• 多数慢SQL都跟索引有关，比如不加索引，索引不生效、不合理等，这时候，我们可以优化索引。
• 我们还可以优化SQL语句，比如一些in元素过多问题（分批），深度分页问题（基于上一次数据过滤等），进行时间分段查询
• SQl没办法很好优化，可以改用ES的方式。
• 如果单表数据量过大导致慢查询，则可以考虑分库分表
• 如果数据库在刷脏页导致慢查询，考虑去优化脏页比例和redo log 写盘速度
• 如果存量数据量太大，考虑是否可以让部分数据归档

3. 大表查询慢常见优化措施

• 合理建立索引。在合适的字段上建立索引，例如在WHERE和ORDER BY命令上涉及的列建立索引，可根据EXPLAIN来查看是否用了索引还是全表扫描
• 对SQL优化。
• 建立分区。对关键字段建立水平分区，比如时间字段，若查询条件往往通过时间范围来进行查询，能提升不少性能
• 利用缓存。利用Redis等缓存热点数据，提高查询效率
• 限定数据的范围。比如：用户在查询历史信息的时候，可以控制在一个月的时间范围内
• 读写分离。经典的数据库拆分方案，主库负责写，从库负责读
• 通过分库分表的方式进行优化，主要有垂直拆分和水平拆分

4. sql语句优化小技巧

• 用具体的字段列表代替 select * 语句
• 尽量用union all代替union，会少一次去重，提高效率
• 避免在where子句中对字段进行表达式操作
• 能用inner join 就不用left join right join，如必须使用 一定要以小表为驱动， （ 内连接会对两个表进行优化，优先把小表放到外边，把大表放到里边。left join 或 right join，不会重新调整顺序）
• 在只需要一条结果的查询中使用 LIMIT 1 可以提高性能。
• 避免使用 % 开头的 LIKE 查询，因为不能使用索引。
• 将多次插入换成批量Insert插入
• 正确使用索引：
  • 避免在索引列上使用函数或进行计算。
  • 为常用查询条件金额连接条件建立索引。只要发现查询较慢，优先检查where条件后面，有没有被创建索引。
  • 遵循最左前缀原则。
  • 更新频繁的列慎用索引
  • 使用覆盖索引，如果查询的所有列都在索引中，那么可以避免回表，提高性能。
• 把IP地址存成 UNSIGNED INT

5. 原本可以执行得很快得SQL 语句，其执行速度却比预期的慢很多

造成原因：

• SQL 语句被堵住了，比如：表锁，行锁等，导致存储引擎不执行对应的 SQL 语句。
• 优化器误判，选错索引
• 表中数据的特点导致的，走了索引，但回表次数庞大。

解决：

• 考虑采用 force index 强行选择一个索引
• 考虑修改语句，引导 MySQL 使用我们期望的索引。比如把“order by b limit 1” 改成 “order by b,a limit 1” ，语义的逻辑是相同的。
• 第三种方法是，在有些场景下，可以新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。
• 如果确定是索引根本没必要，可以考虑删除索引

6. 分页问题

深度分页问题

比如 select * from xxx order by id limit 500000, 10; offset=500000。

当offset非常大时，server层会从引擎层获取到很多无用的数据，而当select后面是*号时，就需要拷贝完整的行信息，拷贝完整数据相比只拷贝行数据里的其中一两个列字段更耗费时间。这就是深度分页问题。

优化方法：

• 延迟关联法，就是把条件转移到主键索引树，然后减少回表。
• 先用子查询查出符合条件的主键，再用主键ID做条件查出所有字段。

超大分页优化

超大分页一般都是在数据量比较大时，我们使用了limit分页查询，并且需要对数据进行排序，这个时候效率就很低，我们可以采用覆盖索引和子查询来解决。

先分页查询数据的id字段，确定了id之后，再用子查询来过滤，只查询这个id列表中的数据就可以了

因为查询id的时候，走的覆盖索引，所以效率可以提升很多。