[大数据]近期工作中关于优化SQL的总结

索引失效

建了索引用Explain分析SQL发现没用上，难道是索引失效？索引失效的大部分情况都没有出现。之后才发现两张表的编码格式不一样也会导致索引失效。还有就是字段编码格式不一致也会失效。

用上索引的思路

有一个统计单张表的某个维度的接口，该表的crt_date为yyyymmdd格式，该张表有索引（crt_date），正常来说我们都是只是count就行，那么如何优化呢？我们可以在业务层先查找出来该张表最早的创建时间放入redis缓存，之后用上索引crt_date即可。

order by 分页出现的问题

由于一些批量导入的数据创建时间都是一致的，order by 创建时间，大量一致的时间会出现分页不一致的问题。解决思路：将具有唯一性的字段排序引入需要排序的业务字段

参考：my.oschina.net/u/3677838/b…

? ? ? ? ? ?www.cnblogs.com/zhengxl5566…

in 和 exists

in先执行子查询，子查询查询出数据以后，将前面的查询分为n次普通查询。

适用于外表大而内表小的情况。

exists

先查询外层循环orders，然后会判断是不是存在 order_id 和 users 表中的id相等，相等才保留数据。适用于外表小，内表大的情况。

联合索引

（a，b，c）其实就相当于创建了 a ab abc索引，要遵守最左匹配原则

权限兼容

不用再业务层写 if(admin) 返回对象，if(运营商) 返回对象，用上 choose when otherwise，在业务层直接判断当前用户是否是admin，如果是admin就不传userid，在mapper里判断，如果有userID 就连权限表等操作。

不要在业务层传in

尽量在mapper里 SQL in，否则查看日志全是 in

分析SQL步骤利用Explain

当 Extra 出现using filesort Using join buffer就是要优化的，还有就是查看key是否用用到索引

Explain复习

参考：https://juejin.cn/post/6844904163969630221#heading-34
复制代码

Explain作用

Explain + SQL一起使用时，MySQL会显示来自优化器关于SQL执行的信息。比如可能用到哪些索引，哪些索引又被实际使用。

Explain执行计划包含字段信息如下：分别是 id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra 12个字段。记录常用

一、id

id： ：表示查询中执行select子句或者操作表的顺序，id的值越大，代表优先级越高，越先执行。

二、select_type

select_type：表示 select 查询的类型，主要是用于区分各种复杂的查询，例如：普通查询、联合查询、子查询等

1、SIMPLE

SIMPLE：表示最简单的 select 查询语句，也就是在查询中不包含子查询或者 union交并差集等操作。

2、PRIMARY

PRIMARY：当查询语句中包含任何复杂的子部分，最外层查询则被标记为PRIMARY。

3、SUBQUERY

SUBQUERY：当 select 或 where 列表中包含了子查询，该子查询被标记为：SUBQUERY 。

4、DERIVED

DERIVED：表示包含在from子句中的子查询的select，在我们的 from 列表中包含的子查询会被标记为derived 。

5、UNION

UNION：如果union后边又出现的select 语句，则会被标记为union；若 union 包含在 from 子句的子查询中，外层 select 将被标记为 derived。

三、type

type`：查询使用了何种类型，它在 `SQL`优化中是一个非常重要的指标，以下性能从好到坏依次是：`system`  > `const` > `eq_ref` > `ref`  > `ref_or_null` > `index_merge` > `unique_subquery` > `index_subquery` > `range` > `index` > `ALL
复制代码

1、system

system： 当表仅有一行记录时(系统表)，数据量很少，往往不需要进行磁盘IO，速度非常快。

2、const

const：表示查询时命中 primary key 主键或者 unique 唯一索引，或者被连接的部分是一个常量(const)值。这类扫描效率极高，返回数据量少，速度非常快。

3、eq_ref

eq_ref：查询时命中主键primary key 或者 unique key索引， type 就是 eq_ref。

4、ref

ref：区别于eq_ref ，ref表示使用非唯一性索引，会找到很多个符合条件的行。

5、range

range：使用索引选择行，仅检索给定范围内的行。简单点说就是针对一个有索引的字段，给定范围检索数据。在where语句中使用 bettween...and、<、>、<=、in 等条件查询 type 都是 range。

6、index

index：Index 与ALL 其实都是读全表，区别在于index是遍历索引树读取，而ALL是从硬盘中读取。

7、ALL

ALL：将遍历全表以找到匹配的行，性能最差。

四、possible_keys?

possible_keys：表示在MySQL中通过哪些索引，能让我们在表中找到想要的记录，一旦查询涉及到的某个字段上存在索引，则索引将被列出，但这个索引并不定一会是最终查询数据时所被用到的索引。

五、key

key：区别于possible_keys，key是查询中实际使用到的索引，若没有使用索引，显示为NULL

六、Extra

Extra ：不适合在其他列中显示的信息，Explain 中的很多额外的信息会在 Extra 字段显示

1、Using index

Using index：我们在相应的 select 操作中使用了覆盖索引，通俗一点讲就是查询的列被索引覆盖，使用到覆盖索引查询速度会非常快，SQl优化中理想的状态。

什么又是覆盖索引?

一条 SQL只需要通过索引就可以返回，我们所需要查询的数据（一个或几个字段），而不必通过二级索引，查到主键之后再通过主键查询整行数据（select * ）。

2、Using where

Using where：查询时未找到可用的索引，进而通过where条件过滤获取所需数据，但要注意的是并不是所有带where语句的查询都会显示Using where。

3、Using temporary

Using temporary：表示查询后结果需要使用临时表来存储，一般在排序或者分组查询时用到。

4、Using filesort?

Using filesort：表示无法利用索引完成的排序操作，也就是ORDER BY的字段没有索引，通常这样的SQL都是需要优化的。如果ORDER BY字段有索引就会用到覆盖索引，相比执行速度快很多。

5、Using join buffer?

Using join buffer：在我们联表查询的时候，如果表的连接条件没有用到索引，需要有一个连接缓冲区来存储中间结果。这也是SQL优化中需要注意的地方。

作者：wistchen
链接：https://juejin.cn/post/7028950775258841124
来源：稀土掘金
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。