如何优化索引？

前缀索引

这个操作是为了减少索引长度，即占用空间的。这样一个页可以多存一些索引，查找时候就会更快了。但是前缀索引有俩缺点，一个是ORDER BY或GROUP BY时候没法用，另一个是没法用做覆盖索引（因为索引本来自己都不全，没法提供完整信息，还是需要回表的）

覆盖索引

不需要那么多信息时候，可以考虑这一点

主键索引最好是自增的

避免增加数据时候，主键插入在B+树的中间，导致树的复杂结构变化。自增的直接插在最后就行。另外主键别太长，因为它是作为value存到二级索引的B+树叶子节点的，太长了占用空间。

索引最好设置为 NOT NULL

两个原因，一个是NULL会让优化器做选择适合增加复杂度，因为涉及到运算时候，NULL太复杂，比如进行索引统计时，count 会省略值为NULL 的行；另一个是NULL让行数据平白无故要增加记录，至少一个字节，如下图

防止索引失效

索引会失效的情况介绍在这篇文章了，具体用没用到索引，用了啥索引可以用EXPLAIN来看

对于执行计划，参数有：

• possible_keys 字段表示可能用到的索引；
• key 字段表示实际用的索引，如果这一项为 NULL，说明没有使用索引；
  key_len 表示索引的长度；
  rows 表示扫描的数据行数；
  type 表示数据扫描类型，我们需要重点看这个。

type 字段就是描述了找到所需数据时使用的扫描方式是什么，常见扫描类型的执行效率从低到高的顺序为：

• All（全表扫描）；
• index（全索引扫描）；
• range（索引范围扫描）；
• ref（非唯一索引扫描）；
• eq_ref（唯一索引扫描）；
• const（结果只有一条的主键或唯一索引扫描）。

分别介绍一下：
ALL是全表扫描，就是啥索引也不用。index是对索引表进行全扫描，这样做的好处是不再需要对数据进行排序，但是开销依然很大。

—以上是效果不咋样的，下面开始就是能发挥出索引优势的级别了—

range是用到范围了一般是where中有&gt; < in between了，能利用上索引。

ref 类型表示采用了非唯一索引，或者是唯一索引的非唯一性前缀，返回数据返回可能是多条。因为虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。

eq_ref 类型是使用主键或唯一索引时产生的访问方式，通常使用在多表联查中。比如，对两张表进行联查，关联条件是两张表的 user_id 相等，且 user_id 是唯一索引，那么使用 EXPLAIN 进行执行计划查看的时候，type 就会显示 eq_ref。

const 类型表示使用了主键或者唯一索引与常量值进行比较，比如 select name from product where id=1。

需要说明的是 const 类型和 eq_ref 都使用了主键或唯一索引，不过这两个类型有所区别，const 是与常量进行比较，查询效率会更快，而 eq_ref 通常用于多表联查中。

除了关注 type，我们也要关注 extra 显示的结果。

这里说几个重要的参考指标：

• Using filesort ：当查询语句中包含 group by 操作，而且无法利用索引完成排序操作的时候,这时不得不选择相应的排序算法进行，甚至可能会通过文件排序，效率是很低的，所以要避免这种问题的出现。
• Using temporary：使了用临时表保存中间结果，MySQL 在对查询结果排序时使用临时表，常见于排序 order by 和分组查询group by。效率低，要避免这种问题的出现。
• Using index：所需数据只需在索引即可全部获得，不须要再到表中取数据，也就是使用了覆盖索引，避免了回表操作，效率不错

代码007普通

打赏 收藏 海报 链接