索引失效
建了索引用Explain分析SQL发现没用上,难道是索引失效?索引失效的大部分情况都没有出现。之后才发现两张表的编码格式不一样也会导致索引失效。还有就是字段编码格式不一致也会失效。
用上索引的思路
有一个统计单张表的某个维度的接口,该表的crt_date为yyyymmdd格式,该张表有索引(crt_date),正常来说我们都是只是count就行,那么如何优化呢?我们可以在业务层先查找出来该张表最早的创建时间放入redis缓存,之后用上索引crt_date即可。
order by 分页出现的问题
由于一些批量导入的数据创建时间都是一致的,order by 创建时间,大量一致的时间会出现分页不一致的问题。解决思路:将具有唯一性的字段排序引入需要排序的业务字段
参考:my.oschina.net/u/3677838/b…
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
来源:稀土掘金
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。