在select语句之前增加explain关键字,执行后MySQL就会返回执行计划的信息,而不是执行sql。 explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。
优化的依据:Explain 详解
| 列名 | 描述 |
|---|---|
| id | 在一个大的查询语句中每个SELECT关键字都对应一个唯一的id |
| select_type | SELECT关键字对应的那个查询的类型 |
| table | 表名，数据来自哪张表 |
| partitions | 匹配的分区信息 |
| type | 针对单表的访问方法 |
| possible_keys | 可能用到的索引 |
| key | 实际上使用的索引 |
| key_len | 实际使用到的索引长度 |
| ref | 当使用索引列等值查询时，与索引列进行等值匹配的对象信息 |
| rows | 预估的需要读取的记录条数 |
| filtered | 某个表经过搜索条件过滤后剩余记录条数的百分比 |
| Extra | 一些额外的信息 |
type
表示表的连接类型。以下的连接类型的顺序是从最佳类型到最差类型：
NULL > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
使用的索引至少要达到range 级别
NULL:不用访问表和索引
MySQL能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。
system:表中的数据仅有一行
表中的数据仅有一行，这是const类型的特列，平时不会出现，这个也可以忽略不计。
const:主键或唯一索引与常量值比较,结果只有一行匹配
EXPLAIN SELECT (bbs_city.id) FROM bbs_city WHERE bbs_city.id=1
sql语句查询的结果最多只有一个匹配行，因为只匹配一行数据，所以很快，常用于PRIMARY KEY或者UNIQUE索引的查询，二级索引也可能会出现。
可理解为const是最优化的。
eq_ref:主键或唯一索引，一般在两表连接查询中索引
在连接查询时，如果驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较） 。eq_ref可以用于使用=比较带索引的列。
ref:二级索引等值比较
当通过普通的二级索引列与常量进行等值匹配时来查询某个表。ref可用于=或<或>操作符的带索引的列。
ref_or_null:二级索引 + is null 查询
该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。
在解决子查询中经常使用该联接类型的优化。
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key1 IS NULL;
上面的情况都是很理想的索引使用情况
index_merge:索引合并: index1 =1 or index2 =2
该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素。
一个表两个索引,查询的时候两个索引字段or一下就是index_merge
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';
unique_subquery:子查询用到了唯一索引
该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。
index_subquery:子查询用到了普通索引
该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)
range:范围查询
只检索给定范围的行,使用一个索引来选择行。
index:扫描索引树查询
该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。
索引:KEY idx_key_part(key_part1, key_part2, key_part3)
EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';
上述查询中的查询的字段中只有key_part2一个列，而且where条件中也只有key_part3一个列，这两个列又恰好包含在idx_key_part这个索引中，可是搜索条件key_part3不能直接使用该索引进行ref或者range方式的访问，只能扫描整个idx_key_part索引的记录，所以查询计划的type列的值就是index。
覆盖索引的使用能够减少树的搜索次数，避免了回表，显著提升了查询性能，因此覆盖索引是一个常用的性能优化手段。
ALL:全表扫描
对于每个来自于先前的表的行组合,进行完整的表扫描。（性能最差）
