如何高效地利用MySQL索引

如何高效地利用MySQL索引

为实现MySQL的高性能，我们需要在优化索引、优化查询、优化表结构三个方面努力，这三个方面每个都至关重要。
本文主要讨论索引相关。

1、要想高效利用索引，我们首先要考虑如何正确建立索引。

（1）在经常做搜索的列上，也就是WHERE子句里经常出现的列，考虑加上索引，加快搜索速度。

（2）唯一标识记录的列，应该加上唯一索引，强制该列的唯一性并且加快按该列查找记录的速度。

（3）在内连接使用的列上加上索引，最好是在内连接用到字段都加上，因为MySQL优化器会自动地选择连接顺序，然后观察索引的使用情况，将没用的索引删除即可。

（4）在需要排序的列上加上索引，因为索引本身是按顺序的组织的，它可以避免 filesort，要知道，Server层在进行排序时是在内存中进行的，非常消耗资源。

（5）可以考虑实现覆盖索引，即根据 SELECT 的所有字段上创建联合索引，这样存储引擎只用读取索引而不用去回表查询，极大地减少了对数据表的访问，大大地提高了性能。

（6）对于那些选择性很小的列，比如性别列，增加索引并不能明显加快查询速度，反而该索引会成为表的累赘。

（7）对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的要么数据量相当大，要么取值很少。

（8）当对写性能的要求远远大于读性能时，不应该创建索引。写性能和读性能是互相矛盾的。这是因为，维护一个 B+Tree 成本是非常大的，对索引的写会涉及到页的分裂等。

（9）复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引，否则考虑单字段索引。这还是说明，满足查询性能的前提下，索引越少越好。

（10）如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段。

（11）在用于GROUP BY的列上加上索引，避免使用临时表。

（12）对于较长的字符列，如 char、varchar等，由于字符串的比较相对来说非常耗时，因此考虑使用前缀索引减少索引长度，或者创建自定义哈希索引，将字符串映射成整数，然后以该整数作为索引，同时以字符串的值作为过滤条件。

我们在创建索引时，可以根据下面原则进行简单判断：索引是否将相关记录集合到了一起，从未减少了磁盘I/O，加快搜索速度？索引中数据的排列顺序是否和查找的数据的排列顺序一致，从而避免了Server层的排序？索引中的列是否包含了查询中需要的全部列从而实现了覆盖索引？ 这几个条件层层递进，满足得越多越好。

2、索引正确地建立了，我们还需要正确地使用它们：

（1）使用了运算符 !=，以及关键字not in，not exist，>，<等，总之产生的结果集很大时（也在where条件进行大范围的选择时），往往导致引擎不使用索引而是走全盘扫描。因为如果使用索引会造成大量的随机I/O，得不偿失。

（2）如果对索引列进行运算，如 WHERE substr(name, 1, 3)=‘mark’，存储引擎并不能聪明地判断哪些索引满足等式，因此不能使用到索引。

（3）使用到了LIKE，并且通配符在最前面时，不能使用索引。

（4）对于联合索引 (a, b, c)，如果没用到最左列，那么一般情况下都使用不到索引。但是，比如统计操作 count(*) where a > xxx，是可以使用到该联合索引的。毕竟统计这类操作，它不是检索，并不需要索引完全有序。

（5）对于联合索引，如果某个列使用了范围查找，那么其右边的列都无法作为索引优化查询，但是由于 ICP（Index Condition Pushdown），这些列能作为过滤条件在存储引擎中对数据进行过滤。

（6）如果条件中有 OR，则必须每个OR用到的字段都有索引，否则不能使用任何索引。

（7）想在联合查询中使用索引来避免 filesort，则关联查询中的ORDER BY用到的字段必须全部是第一张表（驱动表）上的。