MySQL优化之索引和执行计划

MySQL优化之索引和执行计划

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

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

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

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

3、where like关键字的前面使用%会全表扫描，不走索引 

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

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

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

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

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

（2）关注的字段值：

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

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

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

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

——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