索引有许多有点，比如常见的B-TRee索引，是按照顺序存储数据，索引MySQL可以用来做ORDER BY和GROUP BY操作，因为数据是有序的，所以B-Tree也就会将相关的列值都存储在一起。当然因为索引中存储了实际的列值，所以有些查询不需要扫描表，只需要索引就可以完成查询（即SELECT column1而不是SELECT *）。优点总结如下：
- 索引大大减少了服务器需要扫描的数据量。
- 索引可以帮助服务器避免排序和临时表。
- 索引可以将随机I/O变为顺序I/O。
虽然索引优点很明显，但是正确的创建和使用索引也是很关键的，下面介绍一下常见的能提高性能的创建索引方式。
1. 独立的列
查询的时候，要保证查询中的列是独立的，否则MySQL不会使用索引。“独立的列”是指索引列不能够是表达式的一部分，也不能是函数的参数。例如下面这个查询无法使用actor_id列的索引：
SELECT actor_id FROM sakila.actor WHERE actor_id +1 = 5;
其实WHERE中等价于actor_id = 4，但MySQL不会自动解析。所以我们应该养成简化WHERE条件的习惯，始终将索引列单独放在比较符号的一侧。
2. 前缀索引和索引选择性
有时候需要索引很长的字符列，这会让索引变得大且慢，此时通常可以索引开始的部分字符，这样能节省索引空间，提高索引效率。但这会降低索引的选择性。索引选择性是指，不重复的索引值（也称为基数）和数据表的记录总数（#T）的比值，范围从1 / #T到1之间。索引的选择性越高，查询效率越高，因为可以让MySQL在查找时过滤掉更多的行。
所以诀窍在于要选择足够长的前缀以保证较高的选择性，同时又不能太长（节约空间），前缀的“基数”应该接近于完整列的“基数”。选择好前缀索引后，便可以进行创建，语句如下：
mysql> ALTER TABLE city_demo ADD KEY (city(7));
当然前缀索引时一种能使索引更下，更快的有效办法，但另一方面也有其缺点：MySQL无法使用前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描。
3. 多列索引
要知道在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能。MySQL5.0和更新版本引入了一种叫“索引合并”的策略，一定程度上可以使用表上的多个单列索引来定位指定的行。
例如表file_actor在字段file_id和actor_id上各有一个单列索引，但对于下面这个查询WHERE条件，这两个单列索引都不是好的选择：
mysql> SELECT file_id, actor_id FROM sakila.file_actor WHERE actor_id = 1 OR film_id = 1;
在老的MySQL版本中，MySQL对于这个查询会使用全表扫描，除非改写成两个查询UNION的方式：
mysql> SELECT file_id, actor_id FROM sakila.file_actor WHERE actor_id = 1
-> UNION ALL
-> SELECT file_id, actor_id FROM sakila.file_actor WHERE film_id = 1
-> AND actor_id <> 1;
但在MySQL5.0和更新的版本中，查询能够同时使用这两个单列索引进行扫描，并将结果进行合并。这种算法有三个变种：OR条件的联合（union），AND条件的相交（intersection），组合前两种情况的联合及相交。
索引合并策略虽是一种优化结果，但更多说明了表上的索引建的很糟糕：
- 当出现服务器对多个索引做相交操作时（通常由多个AND条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。
- 当服务器需要对多个索引做联合操作时（通常有多个OR条件），通常需要消耗大量的CPU和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候。
4. 合适的索引列顺序
当然这个地方讲索引列顺序适用于B-Tree索引，哈希或者其他类型的索引并不会像B-Tree索引一样按顺序存储数据。
在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，等等。所以索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的ORDER BY、GROUP BY和DISTINCT等子句的查询需求。
当然针对索引的列顺序有一个经验法则：将选择性最高的列放在索引最前列。但是查询性能不只是依赖于所有索引列的选择性（整体基数），也和查询条件的具体值有关，也就是和值的分布有关。举一个例子，假设如下的查询：
SELECT * FROM payment WHERE staff_id = 2 AND customer_id = 584;
那索引应该是（staff_id, customer_id）还是两者相反的顺序呢？可通过一些查询来确定在这个表中值的分布情况，并确定哪个列的选择性更高。而且在这个过程中，我们不能只用某一个特定的查询来看，而是要考虑全局。即我们可以先执行如下查询语句：
SELECT COUNT(DISTINCT staff id)/COUNT(*) AS staff_id_selectivity,
COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,
COUNT(*)
FROM payment
#结果为：
staff_id_selectivity: 0.0001
customer_id_selectivity: 0.0373
COUNT(*): 16049
能够看出customer_id的选择性更高，所以答案是将其作为索引列的第一列：
ALTER TABLE paayment ADD KEY(customer_id, staff_id);
在使用前缀索引时，在某些条件值的基数比正常值高的时候会出现问题。比如某一个索引列有大量重复的值，会导致使用它当索引列的时候几乎没什么用，近似于全表搜索，如下：
#查询语句，MySQL选择(groupId,userId)作为索引
SELECT COUNT(DISTINCT threadId) AS COUNT_VALUE
FROM Message
WHERE (groupId = 10137) AND (userId = 1288826) AND (anonymous = 0)
ORDER BY priority DESC, modifiedDate DESC
#假设它很慢，然后我们用用如下语句分析一下：
SELEECT COUNT(*), SUM(groupId = 10137),
SUM(userId = 1288826), SUM(anonymous = 0)
FROM Message
#最后结果为
count(*): 4142217
sum(groupId = 10137): 4092654
sum(userId = 1288826): 1288496
sum(anonymous = 0): 4141934
所以能看到符合组（groupId）条件几乎满足表中的所有航，符合用户（userId）条件的有130万条记录，也就是说索引基本上没什么用。最后机关关于选择性和基数的经验法则值得去研究和分析，但要记住别忘了WHERE子句中的排序、分组和范围条件等其他因素，这些因素可能对查询的性能造成非常大的影响，所以不能只局限于使用某个值去查看，要基于全局。
5. 聚簇索引
它不算是一种单独的索引类型，而是一种数据存储方式。当表中有聚簇索引时，他的数据行实际上存放在索引的叶子页中。术语“聚簇”表示数据行和相邻的键值紧凑地存储在一起。因为无法同时把数据行存放在两个不容的地方，所以一个表只能有一个聚簇索引。
如图展示了聚簇索引中的记录是如何存放的，要知道，叶子页包含了行的全部数据，但是节点页只包含了索引列。下图中，索引列包含的是整数值。
InnoDB通过主键聚集数据即主键列就是“被索引的列”。如果没有主键，InnoDB会选择一个唯一的非空索引代替，如果没有这样的索引，InnoDB会隐式定义一个主键作为聚簇索引。InnoDB只聚集在同一个页面中的记录，包含相邻键值的页面可能会相距甚远（例如上图中的20和后面的21可能相聚的比较远，但是11到20他们是聚到一起的）。
聚集的数据有一些重要的优点：
- 能把相关数据保存在一起。例如实现电子邮箱时，可根据用户ID来聚集数据，这样只需读取少数数据页就能获取某个用户的全部邮件。
- 数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找更快。
- 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。
充分利用上述优点可极大提升性能，当然它也有一些缺点：
- 插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。但如果不是按照主键顺序家在数据，那么加载完成后最好使用OPTIMIZE TABLE重新组织一下表。
- 更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置。
- 基于聚簇索引的行在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳改行，页分裂会导致表占用更多的磁盘空间。
- 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候（这个地方是和B-Tree数据结构有关，建议自己查阅一下）。
- 二级索引（非聚簇索引）可能比想象的要更大，因为在二级索引的叶子节点包含了饮用含的主键列。
- 二级索引访问需要两次索引查找，而不是一次。因为二级索引中保存的“行指针”并不是指向行的物理位置，而是行的主键值。因此如果通过二级索引查找某一行，存储引擎需要找到二级索引的叶子节点获得对应的主键值，然后根据这个值去聚簇索引中查找对应的航。这里是做了重复的工作，两次B-Tree查找而不是一次。
5.1InnoDB和MyISAN的数据分布对比
聚簇索引和非聚簇索引的数据分布有区别，以及对应的主键索引和二级索引的数据分布也有区别。看一下InnoDB和MyISAM是如何存储下面这个表的：
CREATE TABLE layout_test (
col1 int NOT NULL,
col2 int NOT NULL,
PRIMARY KEY(col1),
KEY(col2)
);
这个表中主键索引是col1，二级索引是col2。看一下MyISAM是如何存储里面的数据的。
MyISAM的数据分布
此引擎的数据分布很简单，按照数据插入的顺序存储在磁盘上。如下图所示：
行的旁边是行号，从0开始递增。因为行是定长的，所以MyISAM可以从表的开头跳过所需的字节找到需要的行，即只需要知道行号就能快速找到数据。这种分布很容易创建索引，下面的图隐藏了页的物理细节，只显示索引中的“节点”，索引中的每个叶子节点包含“行号”，这样也能快速找到数据。其主键索引如下图所示：
那刚才看的是主键索引，在col2列上的索引什么样子的呢，有什么特殊的吗？其实没有：它和其他索引没有什么区别。下图显示了col2列上的索引，类似的，只是叶子节点中的索引列变成了col2，而不是上面的主键col1，另一部分存储的也是行号：
InnoDB的数据分布
因为InnoDB支持聚簇索引，所以使用非常不同的方式存储同样的数据，如图所示：
注意到改图显示了整个表，而不是只有索引，因为在InnoDB中，聚簇索引“就是”表，不像MyISAM中数据和索引是分开的，需要独立的行存储。聚簇索引的每个叶子节点都包含了主键值、事务ID、用于事务和MVCC的回滚指针以及所有的剩余列（此例子是col2）。如果主键是一个列前缀索引，InnoDB也会包含完整的主键列和剩下的其他列。
当然和MyISAM不同的还有，InnoDB的二级索引和聚簇索引很不相同。InnoDB二级索引的叶子节点中存储的不是“行指针”，而是主键值，以此作为指向行的“指针”。这样的策略减少了当出现行移动或者数据页分裂时二级索引的维护工作。使用主键值当做行指针会让二级索引占用更多空间，但换来的好处是InnoDB在移动时无需更新二级索引中的这个“指针”。下图显示了col2索引的结构，每个叶子节点都包含了索引列（col2），紧接着是主键值（col1）。
下图则对比了InnoDB和MyISAM的存储结构，很明显的区别。包括索引的内部结构，以及数据存储方式等。
在InnoDB表中按主键顺序插入行
如果使用的InnoDB表没有什么数据需要聚集，那可以定义一个代理键作为主键，而且要保证和应用无关，最简单的方法是使用AUTO_INCREMENT自增列，这样可以保证数据行是按顺序写入。最好避免随机的聚簇索引，例如使用UUID来作为聚簇索引会很糟糕，因为它使得聚簇索引的插入变得完全随机，这是最坏的情况，使得数据没有任何聚集特性。
我们看一下如果两个表一个是用AUTO_INCREMENT列作为聚簇索引，另一个用UUID时，往表里插入数据时有什么区别。
下图是向自增表中插入数据时的情况，因为主键的值是顺序的，所以InnoDB把每一条记录都存储在上一条记录的后面。当达到页的最大填充因子时（InnoDB默认为页大小的15/16，留出空间用于以后修改），下一条记录就会写入新的页中。一旦数据按照这种顺序的方式加载，主键页就会近似于被顺序的记录填满，也正是期望的结果。
对比一下向UUID表中插入数据的情况，如下图所示。因为新行的主键值不一定比之前插入的大，所以InnoDB无法简单的总是把新行插入到索引的最后，而是需要为新的行寻找合适的位置—通常是已有数据的中间位置并分配空间，会增加很多额外工作，并导致数据分布不够优化。简而言之其缺点可汇总如下：
- 写入的目标页可能已经刷到磁盘上并从缓存中移除，或者还没有被加载到缓存中，所以InnoDB在插入前不得不先找到并从磁盘读取目标页到内存中，会导致大量的随机I/O。
- 因为写入是乱序的，InnoDB不得不频繁的做页分裂操作，以便为新的行分配空间。页分裂会导致移动大量数据，一次插入最少需要修改三个页面而不是一个页（和B-Tree数据结构有关）。
- 由于频繁的页分割，页会变得稀疏并被不规则地填充，最终数据会有碎片。
如果插入了这些随机值，也许需要做一次OPTIMIZE TABLE来重建表并优化页的填充。所以==使用InnoDB时，尽可能按主键顺序插入数据，并尽可能地使用单调增加的聚簇键的值来插入新行。