MySQL之高性能的索引策略

一、索引列必须单独

让我们先看看下面的这段MySQL代码：

mysql> SELECT id FROM user WHERE id + 1 = 2;

我们会发现，在上面这段SQL查询语句中，我们给的条件是一个有变量的表达式，如果我们此时的id列上是存在索引的，那上面的语句能不能使用到索引呢？
答案是不能的，因为MySQL无法自动的解析 id + 1 = 2 这个条件语句，尽管我们可以一眼的看出来此时等价于 id = 1，但是这种做法是无法使用到索引的，因此我们在查询的时候，应该使得索引列不能是表达式的一部分，也不能是函数的参数。

二、前缀索引和索引选择性

如果我们需要在某一列，例如存放url的一列数据上添加索引来加快查询的速度，我们先看看url数据的特点，长，类似的还有TEXT类型的数据等，这些都是一些很长，占据很大空间的数据，而且会使得对应的索引大且慢。这时候我们可以使用一些优化的索引策略，例如前缀索引。前缀索引与一般的索引不同，他在查询的时候并不会比对该列数据的所有值，而只是比对它的前面的一部分数据。这样会使得索引变得更加灵活有效率，但是却降低了索引选择性。
什么是索引选择性呢？我们给定一个公示：
索引选择性 = 不同的索引值 / 数据表的记录总数
首先思考，为什么会有所谓的不同索引值和相同的索引值？这都要建立在我们是使用前缀索引这种方式建立索引的基础上。例如有两个数据，“abcalkjsdhgasdfasdf”和“abcalaasdasdqwe”。很显然这两个数据是截然不同的，但是如果我们规定前缀索引的长度是数据的前五个字符，那么会发现这两个数据的索引值都是“abcal”，即这两个数据的索引值是一样的。因此也就降低了索引选择性。简单来说，索引选择性越高，我们通过索引值能查找到唯一的数据的可能性就越大，索引选择性越低，我们通过索引值能查找到的唯一的数据的可能性就越小。那么这是否就意味着前缀索引是一个很差的选择呢？并不，因为一般情况下使用恰当的前缀索引，也是可以准确的进行数据的查询，并且能够节省空间的，而且对于BLOB，TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。

三、多列索引

首先，如果一个数据表有3个列，那么我们为每一个列都单独的创建一个索引，是不是就能够使得在查询的时候，无论进行怎样的查找，我们都能获得最快的效率呢？进行下面的表格建立语句：

mysql> CREATE TABLE temp (c1 INTEGER,c2 INTEGER,KEY (c1),KEY (c2));

事实证明，在实际的操作中，这种为每一个列都建立一个索引的“单纯”的想法，对查找的效率提升非常的有限，与最佳的索引方案往往效率差距了几个量级。
在MySQL5.0之后的版本多出了“索引合并”的策略，一定程度上是帮程序员优化了这种在一个数据表上创建许多单列索引的操作，但是还是不建议使用这种索引策略。在MySQL5.0之前的版本，如果我们为表film_actor的字段film_id和actor_id分别创建一个单列索引，然后执行以下的查询操作：

mysql> SELECT film_id, actor_id FROM film_actor
       WHERE  film_id  = 1 OR actor_id = 1;

事实上，在这个查询语句中，我们所定义的两个单列索引都无法帮忙提高效率，甚至于在老版本的MySQL中，将会使用全表扫描来完成这个查询，这就使得这个索引策略变得完全没有意义。
除非将上面的语句改写成以下的形式：

mysql> SELECT actor_id, film_id FROM film_actor WHERE actor_id = 1
       UNION ALL
       SELECT actor_id, film_id FROM film_actor WHERE film_id = 1

即将查询改写成两个查询的交集，即每个查询都只是用一个列作为判断的条件，拿着整MySQL就会很自然的运用这个列的索引。

因此我们还是建议在需要在多个列上建立索引的时候不要单独的给每个列建立一个索引，而是选择建立一个多列索引。

mysql> CREATE TABLE temp (c1 INTEGER,c2 INTEGER,KEY (c1, c2));

这个时候选择一个合适的索引列顺序就显得非常重要了，因为我们知道，如果我们使用默认的B-Tree建立一个多列索引的话，MySQL会按照我们创建时候指定的顺序建立索引，即先排c1，再在c1排列好的基础上排列c2。
而且查询的时候每次都是从左开始扫描，意味着如果你第一个查询的列并不是索引的最左列，那这个索引对于你来说就形同虚设。
我们看看如下的一个查询语句：

mysql> SELECT * FROM payment WHERE staff_id = 2 AND customer_id = 584;

那我们是应该建立一个(staff_id, customer_id)的多列索引还是将他们的顺序颠倒过来呢？我们可以先看看这两个条件的数据量有多大：

sum(staff_id = 2) = 7992
sum(customer_id = 584) = 30

根据经验，我们应该将索引列customer_id 放到前面，因为对应条件值的数据量更小。因此如何选择索引列的顺序还是应该根据具体的情况来确定，没有唯一的准则。

四、聚簇索引（介绍）

聚簇索引其实只是一种特殊的B-Tree索引，他并不是一种区别于其他索引的单独的索引形式，而是一种存储方式。
当使用聚簇索引的时候，所有的数据都存储在索引树的叶子节点上。
下图展示了聚簇索引中的记录是如何存放的：

在创建聚簇索引时，InnoDB使用主键作为索引列聚集数据。如果数据表没有定义主键，则会选择一个唯一的非空索引代替，如果没有这样的索引InnoDB则会隐式定义一个主键来作为聚簇索引。
聚簇索引有以下的优点：

• 可以吧相关的数据保存在一起。例如实现电子邮箱时，可以根据用户的id来聚集数据，这样只需要从磁盘读取少量的数据页就能获取某个用户的全部邮件，如果没有使用聚簇索引，那么邮件的排列就是混乱的，有可能每封邮件都要导致一次磁盘IO。
• 数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中（普通索引只保存索引和指向数据行的指针，不保存数据），因此聚簇索引的查询会更快。
• 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

同时，聚簇索引也有以下的缺陷：

• 聚簇索引最大限度地提高了IO密集型应用的性能，但如果数据全部存放在内存中，则访问的顺序就没有那么重要了，聚簇索引就没有什么优势了。
• 插入速度严重依赖插入顺序。
• 更新聚簇索引列的代价很高。
• 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临“页分裂”的问题。当行的主键要求必须将这一行插入到某个已满的页面时，存储引擎会将该页分裂成两个页面容纳该行，页分裂将会倒是表占据更多的磁盘空间。
• 聚簇索引可能导致全表扫描变慢，特别是在行比较稀疏的时候，或者由于页分裂导致存储不连续的时候。
• 二级索引（非聚簇索引）可能比想象中的大没因为耳机索引的叶子节点包含了引用行的主键列。
• 二级索引访问需要两次索引查找，而不是一次。

由于聚簇索引的内容比较多，会专门出一篇来较为深入的将聚簇索引。