mysql大部分性能的问题都出在索引上,因此索引优化是头等大事
1.最左匹配原则
这是索引基本的匹配原则,这里不多说,只要记住索引是要按顺序创建的,mysql是按顺序去b+树上查找的就行
2.Using temporary; Using filesort
往往出现这个都代表着不好的情况,通常是在groupby和orderby的情况下出现的。
-
"Using filesort"表示MySQL必须通过对结果集进行排序来满足GROUP BY操作的要求。这通常发生在没有适用于GROUP BY子句的索引或优化器选择不使用索引的情况下。
-
"Using temporary"表示MySQL需要创建一个临时表来处理GROUP BY操作。这通常发生在GROUP BY子句中包含了多个字段,或者在查询中使用了聚合函数(如SUM、COUNT等)。
一些常见的解决方法包括:
- 确保相关列上存在适当的索引。
- 优化查询以减少GROUP BY子句中的字段数量。
- 考虑使用覆盖索引,即包含所有SELECT列表和GROUP BY子句的索引,以避免回表操作。
- 减少数据量级,如果经过索引过滤后的数据只有几条,那么出现filesort和temporary是完全不用考虑的。
你以为的filesort这些特殊的标记一定会影响性能?其实并不是,我举个例子:
select a,b,c from t where a=1 and b<2 group by c;
- 当我对a,b,c建了索引的时候,上面的sql是会出现temporary和filesort的(这是因为b是范围查询,导致后面c不生效)。
- 而当我对a,c,b建了索引的时候,上面的sql是不会出现temporary和filesort的。
但是。。。如果表里大部分数据都是b>2的,那么第一个索引a,b,c会比第二个索引a,c,b快很多很多,为什么呢?这得从索引执行逻辑来看,第一个索引先是根据a和b过滤了大部分数据,最后剩下1%数据进行temporary和filesort,这是无所谓的。第二个索引是根据a过滤完,还剩99%的数据,再对99%的数据根据c进行group by,尽管没有temporary和filesort,但是这量级太大了,远远没有第一个索引快。
所以,有时候我们看执行效率,不能光看执行计划extra的部分,还要看rows和filtered部分,如果rows很小,那么出现temporary和filesort也是无所谓的。
3.key_len
key_len是执行计划中会出现的一个关键词,这是大多数人会忽略的一个指标,实际上在某些场景下还挺有用,他能帮助我们判断mysql是否按自己的要求去执行索引了。比如上面那个sql。如果a,b走了索引,key_len为18,如果a,b,c都走了索引,则key_len为27.这是因为这里abc都是bigint类型的,所以长度为8,再加上由于default null,所以再加了一个长度。这个指标能帮助我们快速定位索引执行用到了哪些字段。
下面是长度参考表
|类型|长度|
|-|-|-|
|int|4|
|bigint|8|
|tinyint|1|
|smallint|2|
|mediumint|3|
|float |4|
|double |8|
|date |3|
|timestamp|4|
|datetime|5|
text,char,varchar等字符串类型的,需要根据字符集的不同来区分
|字符集|1个字符占用字节数(Maxlen)|
|-|-|-|
|GBK|2||
|UTF8|3||
|UTF8mb4|4||
|latin1|1||
额外的规则是:
- 如果字段是default null的,长度要+1,如果是not null的则不用。
- 如果varchar类型的,需要+2,因为这是这是变长的
所以在UTF8mb4字符集下,一个varchar(50) default null字段key_len的总长度就是50*4+2+1=203
标签:GROUP,filesort,len,索引,key,mysql,temporary,优化 From: https://www.cnblogs.com/leecoder5/p/18420827