MySQL索引与优化策略

1. MySQL索引实现

在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的，下面主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。

MyISAM索引实现

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图：

图1

这里设表一共有三列，假设我们以Col1为主键，则图1是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

 

图2 

同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

InnoDB索引实现

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

图3

图3是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，图4为定义在Col3上的一个辅助索引：

图4

这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

2. 高性能的索引策略

2.1 独立的列

“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数。

错误的写法：

select id from tab where id+1=5;

select id,value from tab where to_days(now())-to_days(gmt_created) <=10;

应该养成简化where条件的习惯，始终将索引列单独放在比较符号的一侧。

正确的写法：

select id,value from tab where gmt_created >= DATA_SUB(now(),interval 10 day );

 

2.2 索引选择性

selectivity = distinct Values / total Rows

索引的选择性是指，不重复的索引值和数据表的记录总数的比值。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。

唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

 

2.3 前缀索引

对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。

诀窍在于要选择足够长的前缀以保持较高的选择性，同时又不能太长（以便节约空间）。前缀应该足够长，以使得前缀索引的选择性接近于索引整个列。

 

2.4 多列索引

一个常见的错误是，为每个列创建独立的索引，或者按照错误的顺序创建多列索引。

错误的写法：

create table t (
c1 int,
c2 int,
c3 int,
key(c1),
key(c2),
key(c3)
);

在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能。对于下面的查询where条件，这两个单列索引都是不好的选择：

select film_id, actor_id from table1 where actor_id=1 or film_id=1;

在老的MySQL版本中，MySQL会对这个查询使用全表扫描。除非改写成两个查询UNION的方式。

select film_id, actor_id from table1 where actor_id=1 
union all
select film_id, actor_id from table1 where film_id=1 
and actor_id<>1;

MySQL5.0和更新的版本引入了一种叫“索引合并”的策略，查询能够同时使用这两个单列索引进行扫描，并将结果合并。这种算法有三个变种：OR条件的联合（union），AND条件的相交（intersection），组合前两种情况的联合及相交。索引合并策略有时候是一种优化的结果，但实际上更多时候说明了表上的索引建得很糟糕：

（1）当出现服务器对多个索引做相交操作时（多个AND条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。

（2）当出现服务器对多个索引做联合操作时（多个OR条件），通常需要耗费大量的CPU和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候。

（3）如果在explain中看到有索引合并，应该好好检查一下查询和表的结构，看是不是已经是最优的。

 

2.5 覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，就称之为“覆盖索引”。MySQL利用索引返回select列表中的字段，而不必根据索引再次回表读取数据页。

  select  id,status from tab where id=2

  alter table add key ind_t_id_status (id,status);

不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以MySQL只能用B-Tree索引做覆盖索引。另外，不同的存储引擎实现覆盖索引的方式也不同，而且不是所有的引擎都支持覆盖索引。

 

2.6 组合索引 

和覆盖索引类似对查询语句中多个常用字段建立索引，当然，创建组合索引并不是说就须要将查询条件中的所有字段都放在一个索引中，还应该尽量让一个索引被多个 Query 语句利用，尽量减少同一个表上的索引数量，减少因为数据更新带来的索引更新成本，同时还可以减少因为索引所消耗的存储空间。

 

2.7 尽量避免NULL

（1）尽可能把字段定义为NOT NULL,可以放置一个默认值,如’’,0等。

（2）MySQL 难以优化NULL列。NULL列会使索引统计和值更加复杂。

（3）NULL列需要更多的存储空间，还需要在MYSQL内部进行特殊处理。

（4）NULL列加索引，每条记录都需要一个额外的字节，还导致MyISAM中固定大小的索引变成可变大小的索引。

 

3. 总结

在选择索引和编写利用这些索引的查询时，有以下三个原则：

（1）单行访问是很慢的。最好读取的块中能包含尽可能多所需要的行。使用索引可以创建位置引用以提升效率。

（2）按顺序访问范围数据是很快的，这有两个原因。第一，顺序I/O不需要多次磁盘寻道，所以比随机I/O快很多。第二，如果服务器能够按需要顺序读取数据，那么就不需要额外的排序操作，并且group by 查询也无需在做排序和将行按组进行聚合计算了。

（3）索引覆盖查询是很快的。如果一个索引包含了查询需要的列，那么存储引擎就不需要再回表找行，这避免了大量的单行访问。

总的来说，编写查询语句时应该尽可能选择合适的索引避免单行查找，尽快能使用数据原生顺序从而避免额外的排序操作，并尽可能使用索引覆盖查询。

理解索引是如何工作的非常重要，应该根据这些理解创建最合适的索引，而不是根据一些诸如“在多列索引中将选择性最高的列放在第一列”或“应该为where子句中出现的所有列创建索引”之类的经验法则及其推论。