MySQL优化之索引和执行计划

左手的ㄟ右手 2022-05-18 09:58 219阅读 0赞

## [MySQL优化之索引和执行计划][MySQL] ##

## 一、创建索引需要关注什么？ ##

1、关注基数列唯一键的数量；

比如性别，该列只有男女之分，所以性别列基数是2；

2、关注选择性列唯一键与行数的比值，这个比值范围在0~1之前，值越小越好；   
![540235-20161016142900327-1073113606.png][]   
*其实，选择性列唯一键与行数的比值，只要列值区分度越高，这个比值就会相对越小*

3、where like关键字的前面使用%会全表扫描，不走索引   
![540235-20161016142900702-468504998.png][]

4、禁止使用select*，建议使用select <字段…>，因为select *会读取大量数据，不利于索引覆盖技术；

5、大批量导入数据的优化方法：   
（1）先导入数据，等待数据导入完成再创建索引   
（2）批量进行提交

6、在order by查询字段上创建索引可以加快查询速度；

# 二、会用explain命令查看执行计划 #

（1）explain是确定一个查询如何走索引的最简便有效的方法，会有如下几个字段：

![540235-20161016142901014-1873379194.png][]

（2）关注的字段值：

——id字段：表示查询中执行select子句或操作表的顺序。

id如果相同，可以认为是一组，从上往下顺序执行；在所有组中， id值越大，优先级越高，越先执行。

——type字段：查询access的方式；

type=all表示全表扫描数据，不走索引；   
type=index表示full index scan，和all的区别是index   
类型只遍历索引树。   
type=range表示索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于between < >等的查询；   
type=ref表示非唯一性索引扫描，返回匹配某个单独值的所有行。常见于使用非唯一索引或唯一索引的非唯一前缀进行的查找；   
type=eq\_ref表示唯一性索引，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描；   
![540235-20161016142901358-759892169.png][]

——key字段：本次查询最终选择使用哪个索引，NULL表示未使用索引；

——key\_len字段：key\_len显示的值为索引字段的最大可能长度，并非实际长度，即key\_len是根据表定义计算而得，不是通过表内检索出的；

——rows字段：可以理解为查询逻辑读，需要扫描过的记录行数；

——extra字段：额外信息，主要指的fetch data的具体方式：

extra=using index表示相应的select操作中使用了覆盖索引（covering index）;

覆盖索引（covering index）：mysql可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件。包含所有满足查询需要的数据的索引称为“覆盖索引”。
    值得注意！如果要使用覆盖索引，一定要注意select列表中只选择需要的列，不可select *，因为如果将所有字段一起做索引，会导致索引文件过大，查询性能下降。

extra=using where表示提醒我们mysql将用where子句来过滤结果集；   
extra=using tmporary表示mysql需要使用临时表来存储结果集，常见于排序和分组查询。   
extra=using filesort表示文件排序，需要对其优化。mysql中无法利用索引完成的排序操作称为“文件排序”。   
*using tmporary可能是内存临时表也可能是磁盘临时表，如果临时表大小超过tmp\_table\_size大小才会产生基于磁盘的临时表，也就是说，只是通过explain执行计划是无法查看是否用来磁盘临时表的，如果show processlist查看的线程有“Created\_tmp\_disk\_tables”关键字才能代表是用使用了磁盘临时表*

（3）explain的一些使用建议：

（3.1）对不确定执行计划的关键语句上线前务必explain；   
（3.2）type为all的要格外注意，避免全表扫描；   
（3.3）key\_len只能用很少一部分前缀的，要注意索引字段顺序等；   
（3.4）extra里看到using filesort和using tmporary都要尽量优化，这两种fetch方式不应该出现在任何执行频繁的关键语句中。

（4）强制使用索引hint：

select \* from table\_1 force index(xxx)…   
select \* from table\_1 ignore index(yyy)….   
默认情况下，建议使用mysql优化器，不要强制所用或忽略索引

转载：[http://www.cnblogs.com/zhoubaojian/articles/7866256.html][http_www.cnblogs.com_zhoubaojian_articles_7866256.html]

[MySQL]: http://www.cnblogs.com/cjing2011/p/1b13c987ecbdbe89a3a31fe59668e041.html
[540235-20161016142900327-1073113606.png]: /images/20220518/d9d59a93366a46caaeb97c1d4f48e7e4.png
[540235-20161016142900702-468504998.png]: /images/20220518/da04e36fb55e4418bd993a34dad3286d.png
[540235-20161016142901014-1873379194.png]: /images/20220518/392a13778dbb44e3be38a6ba3a64248b.png
[540235-20161016142901358-759892169.png]: /images/20220518/b26d5b54cddf40518a0ce426ba532666.png
[http_www.cnblogs.com_zhoubaojian_articles_7866256.html]: http://www.cnblogs.com/zhoubaojian/articles/7866256.html