关于MySQL索引优化那些事

Btree?难道要手写？创建索引等基本语法格式难道写不出来？

所以咱们直接上干活，一个字一个字的码出来的！！！

创建索引的情况？

频繁查询的字段

查询中与其他表关联的字段，将外键关系建立索引

查询中排序的字段，如果排序的字段通过索引访问将大大提高排序的速度

where条件中用到的字段

主键自动会建立唯一索引

不创建索引的情况？

频繁更新的字段

where条件中用不到的字段

表记录过少

比较固定的某一列的值或者重复较多的字段(在这里举个例子：比如某表2K条记录，其中1800个值不同，那么比例大概是90%，此时就适合建立索引，因为很接近1哦)

多表查询优化？

这里不再测试，直接上结论！

对于左连接查询，那么左表作为主表，我们的索引需要加到右表中，若是右连接查询，那么索引需要加入到左表中，而使用inner join时，MySQL会自动选择小结果集的作为驱动表，此时，我们索引应该建立到被驱动表中，原则上是尽量以小表驱动大表。

Explain用法？

咱们先看一下执行后的效果，再一一解释每一个字段。

id：代表语句的执行顺序，若有多个，则id越大越先执行，若相同则顺序执行。

select_type：这里咱们列举几个经常见到的。

SIMPLE：标识这是最简单的select查询，查询中没有子查询或者union等。

PRIMAY：如果是复杂的查询，则代表这是最外的一层查询。

SUBQUERY:在select或者where列表中包含了子查询。

DERIVED：衍生的意思，比如sql语句中From后面包含的子查询就被标记为衍生，如以下结果：

table:标识来自于哪一张表

type：

system：表里只有一行记录，基本上见不到！！！！

const：常量，通过索引一次就可以找到，比如我们将主键放入到了where条件中，那么就相当于一个常量哦，所以查询快，因为主键就相当于唯一索引哦。

eq_ref：对于索引键，表中只有一条记录与它匹配。

ref：对于索引键，表中有多条记录匹配。

range：范围，一般sql中出现in、between等范围的。

index：这就不用说了吧

ALL：全表扫描，最低下的！！

按效率排序依次到最强是：

ALL<INDEX<RANGE<REF<EQ_REF<CONST<SYSTEM

一般来说达到range级别就差不多了，最好能达到REF级别，至于eq_ref等，在实际中，就想想得了。

possible_keys和key：

这两个放到一起说，possible_keys代表可能会用到的索引，什么叫可能呢？就是MYSQL认为会用到的，但是实际上不一定用到，而key则是你一定用到的，所以possible_keys一般作为参考，而key则是肯定用到了的。

ref：显示索引的哪一列被使用，如果可能的话，最好是一个const。

key_len：索引的长度，比如有一个复合索引是三个字段，若只有一个索引有效则len可能是31，两个可能是62，一般用于判断是否失效。

rows：大概所需要读取的行数，越小越好。

Extra：

这个可就多啦，一般分为以下常见的几类。

usingfilesort:文件排序(无法利用索引完成的排序)，经常会出现在order by中，如果出现这个，则效率不好哦！

usingtemporary:使用了临时表保存中间结果，mysql在对查询结果排序的时候使用临时表，效率不好哦！

using index：代表使用了覆盖索引，从索引中直接找到了，效率可以。

using join buffer：使用了连接缓存，一般是出现多个Join，可以通过调节Mysql缓存的方式(慎用)。

案例总结？

有空更新！