高性能索引-高性能索引策略详解数据库

1.独立的列

　　如果查询中的列不是独立的，则MySql就不会使用索引。“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数。

2.前缀索引和索引的选择性

　　对于很长字符列，可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引的效率。但是会降低索引的选择性。索引的选择性是指：不重复的索引值（也称基数）和数据表的总记录数的比值。索引的选择性越高则查询效率越高。诀窍在于选择足够长的前缀以保证较高的选择性，同时又不能太长（以便节省空间）。前缀索引是一种能使索引更小、更快的有效办法，但是另一方面：MySQL无法使用前缀索引做Order BY和Group BY,也无法使用前缀索引做覆盖扫描。

3.多列索引

对于多列索引一个常见的错误就是，为每个列创建独立的索引，或者按照错误的顺序创建索引。在多个列上创建独立的单列索引大部分情况下并不能提高MySQL的查询性能。在MySQL中引入了一种叫做“索引合并（index merge）”的策略，一定程度上可以使用表上的多个单列索引来定位指定的行。例如如下查询：

　　在MySQL5.0和更新的版本中，查询能够同时使用这两个单列索引进行扫描，并将结果合并。通过EXPLAIN查看该语句：

　　索引合并策略有时候是一种优化，但实际上更多表明表上的索引建立的很糟糕：

1. 当出现服务器对多个索引做相交操作时（通常有多个AND条件），通常意味着需要建立一个包含多个相关列的索引，而不是独立的单列索引。
2. 当服务器需要对多个索引做联合操作时，通常需要消耗大量的CPU和内存资源在算法的缓存、排序和合并操作上。特别是当其中有某些索引的选择性不高，需要合并并扫描返回大量的数据时。

　　如果在Explain语句中看到索引合并，应该好好检查一下查询和表的结构。也可以通过参数optimizer_switch来关闭索引合并功能。也可以使用IGNORE_INDEX提示让优化器忽略掉某些索引。

4.选择合适的索引列顺序

　　正确的顺序依赖于使用该索引的查询，并且同时需要考虑如何更好的满足排序和分组的需要。在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列。所以，索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的ORDER BY、GROUP BY和DISTINCT等子句的查询需求。对于如何选择索引顺序有一个经验法则：将选择性较高的列放到索引的最前列。

5.聚族索引

　　聚族索引实际上在同一个结构中保存了B-Tree索引和数据行。当表有聚族（表示数据行和相邻的键值紧密的存储在一起）索引时，它的数据行实际上存放在索引的叶子页中。因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚族索引。因为索引是由存储引擎实现，因此不是所有的存储引擎都支持聚族索引。InnoDB将通过主键聚族数据，如果没有定义一个主键，InnoDB会选择一个唯一的非空索引代替，如果没有这样的索引InnoDB会隐式定义一个主键作为聚族索引。InnoD只会聚集在同一个页面中的记录，因此包含相邻键值的页面可能相距很远。

　　聚族索引有以下优点：

• 可以把相关数据保存在一起，能够减少磁盘I/O.
• 数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中，因此聚族索引中的数据获取通常比非聚族索引更快。
• 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

聚族索引页存在一些缺点：

• 聚族索引提高了I/O密集型应用的性能，但如果数据全部存放内存中，则访问的顺序就没有那个重要了，聚族索引也就没有优势了。
• 插入速度依赖于插入顺序。
• 更新索引列的代价很高，因为会强制InnoDB将每个更新的行移动到新的位置。
• 基于聚族索引的表在插入新行，或者主键被更新需要移动行的时候，可能面临“页分裂”的问题。
• 聚族索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
• 二级索引可能比较大，因为二级索引的叶子节点包含了引用行的主键列。
• 二级索引访问需要两次索引查找，而不是一次。因为二级索引的叶子节点保存的不是指向行的物理位置，而是行的主键值。

4.1InnoDB和MyISAM的数据分布对比

　　聚族索引和非聚族索引的数据分布有区别，以及对应的主键索引和二级索引的数据分布也有区别。如下数据库表（假设数据主键取值为1~10000，随机插入）：

CREATE TABLE layout_test( 
    col1 int NOT NULL, 
    col2 int NOT NULL, 
    PRIMARY KEY(col1), 
    KEY(col2) 
);

 　　MyISAM的数据分布是按照数据插入的顺序存储在磁盘上：

  　　MyISAM的主键索引如图所示：

　　col2列上的索引如下图所示：

　　在MyISAM中，主键索引就是一个名为Primary的唯一非空索引。InnoDB支持聚族索引，InnoDB的数据存储方式如下所示：

　　聚族索引的每一个叶子节点都包含了主键值、事务ID、用于事务和MVCC的回滚指针以及所有剩余的列。如果主键是一个列前缀索引，InnoDB也会包含完整的主键列剩余的其他列。

　　InnoDB二级索引的叶子节点存储的不是“行指针”，而是主键值，并以此作为指向行的“指针”。这样的策略减少了当出现行移动或者页分裂时二级索引的维护工作。使用主键值当作指针会让二级索引占用更多的空间，好处是，InnoDB在移动行时无需更新二级索引的这个“指针”。

　　InnoDB和MyISAM保存数据和索引的区别如下图所示：

　　对于InnoDB的聚族索引的插入，最好要避免随机的聚族索引：它会使得聚族索引的插入变得完全随机，会导致大量的页分裂和碎片。