MySQL执行计划（explain）及索引使用原则

1.explian执行计划

最直接评判一条SQL语句的性能，就是使用explain来查看SQL语句的执行计划。可以说，explain是SQL语句优化的基础。explain的语法很简单，就是在一条SQL语句之前添加explain即可。

explain select col_name for table_name;

用以上两条select语句来简单说明一下explain各个字段的含义；

id:表示SQL的执行顺序，id值越大越先执行；id值相同，按从上到下顺序执行；这边例子比较简单没办法体现出来，可以自行使用explain进行多表查询或者子查询体验一下！

select_type:表示select的类型，常见的取值有simple（简单表）、primary（主查询）、union（第二个或者后面的查询语句）、subquery(select/where之后包含的子查询)，对SQL优化没有任何参考价值；

type：表示连接类型，性能由好到差为null（不查表）>system（查系统表）>const（主键/唯一索引访问时）>eq_ref>ref（非唯一索引查询）>range>index（使用索引，但全表扫描了）>all（全文检索）；执行SQL优化时，尽量往前优化；如上面两张图所示，第一张为全表扫描，type是all，第二张图有一个普通索引id，type为ref;

possible_key：可能用到的索引；

key：使用的索引；

ken_len：索引中使用的字节数，该值为索引字段的最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好；

rows:MySQL执行查询的行数，该值是一个估计值，可能并不总是准确的；

filtered:表示返回结果的行数占需读取行数的百分比，值越大越好；

extra:额外的信息；

2.索引的使用原则

tip：索引是一种数据结构，当我们为某一张数据量很大的表构建索引的时候，我们通常会发现SQL的光标一直在闪动，这是因为我们为表建立索引实际上就是构建一个数据结构，肯定会有一定的耗时的。

2.1、最左前缀法则：

只适用于联合索引；

上图是个简单的例子，我重新建立了联合索引-idx_usr_id_name_age，字段顺序为id,name,age；如果使用该索引，查询条件没有id，则索引失效；如果查询条件只有id和age，则只有id索引会生效，age索引不会生效，即索引部分失效；如果查询条件只有id和name，则索引全部生效；最左前缀法则要求字段存在即可，顺序没要求。

2.2、范围查询

只适用于联合索引；

联合索引中，出现范围查询（>,<），范围查询右侧的列索引失效。

规避方案：业务允许情况下，尽量使用（>=或者<=）

2.3、索引列运算

不要在索引列上进行运算操作，否则索引失效。

2.4、字符串不加引号

如果字符串查询不加引号，则存在隐式转化，索引失效。

2.5、模糊查询

如果只是尾部模糊匹配，索引不会失效；如果头部进行模糊匹配，则索引失效。

2.6、or连接的条件

用or分隔开的条件，如果一侧使用索引，另一侧不使用索引，则索引失效；如果两侧都使用索引，则索引都生效，两侧的索引不一定是同一个索引。

2.7、数据分布影响

如果MySQL的优化器评估使用索引比全表扫描慢，则不使用索引；

2.8、SQL提示

SQL提示是优化数据库的一个重要手段，即在SQL语句中假如一些人为的提示来达到优化操作的目的。

use index(建议使用某个索引)：

explain select * from table_name use index(index_name) where ...;

ignore index(忽略某个索引):

explain select * from table_name ignore index(index_name) where ...;

force index(强制要求使用某个索引):

explain select * from table_name force index(index_name) where ...;

2.9、覆盖索引

尽量使用覆盖索引（查询使用了索引，并且在需要返回的列在改索引中全部能够找到），减少select *。

相信知道这个后，你就懂得大部分的SQL优化方法了。

tip:explain中extra字段在这里发挥了大作用了，extra字段出现

using index condition:查询使用了索引，但需要回表查询数据；

using where;using index：查询使用了索引，但需要的数据在索引列中都能找到，不需要回表查询数据；

2.10、前缀索引

当字段类型为字符串时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。此时，可以只将字符串的一部分前缀建立索引，这样可以大大节约索引空间，从而提高索引效率。

create index idx_xxx on table_name(column(n));
--column(n)指字段名+（截取长度）
--例子：create index idx_user_name on user(name(1)); 

2.11、单列索引与联合索引

在业务场景中，如果存在多个查询条件，考虑针对查询字段建立索引时，建议使用联合索引而非单列索引，原因很简单，因为覆盖索引是不需要回表查询的。