创建高性能的索引
1. 索引(在MySQL中也叫做"键(key)")是存储引擎用于快速找到记录的一种数据结构。
2. 索引可以包含一个或多个列的值。如果索引包含多个列，那么列的顺序也十分重要，因为MySQL只能高效低使用索引的最左前缀列。
3. B-Tree索引：当人们谈论索引的时候，如果没有特别执行索引类型，那多半说是B-Tree索引，它使用B-Tree数据结构来存储数据。
1). 可以使用B-Tree索引的查询类型：
a. 全职匹配：全值匹配指的是和索引中的所有列进行匹配。
b. 匹配最左前缀：即只使用索引的第一列。
c. 匹配列前缀：也可以值匹配某一列的值的开头部分。
d. 匹配范围值
e. 精确匹配某一列并范围匹配另外一列
f. 只访问索引的查询：覆盖索引
2). 因为索引树中的节点是有序的，所以除了按值查找之外，索引还可以用于查询中的ORDER BY操作(按顺序查找)。一般来说，如果B-Tree可以按照某种方式查找到值，那么也可以按照这种方式用于排序。
3). B-Tree索引的限制
a. 如果不是按索引的最左列开始查找，则无法使用索引。
b. 不能跳过索引中的列。
c. 如果查询中有某个列的范围查询，则最右边的所有列都无法使用索引优化查询。如果范围查询列值的数量有限，那么可以通过使用多个等于条件来代替范围查找。
4. 哈希索引(hash index)：哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。结构十分紧凑，查询速度非常快。
InnoDB引擎有一个特殊的功能叫做"自适应哈希索引"。当InnoDB注意到某个索引值被使用得非常频繁时，它会在内存中基于B-Tree索引之上再创建一个哈希索引，这样B-Tree索引也就有哈希索引的一些优点。
5. 空间数据索引，全文索引，其他索引类别
6. 索引的优点：索引可以让服务器快速定位到表的指定位置。最常见的B-Tree索引，按照顺序存储数据，所以MySQL可以用来做ORDER BY 和 GROUP BY操作。总结下来，索引有如下三个优点：
1). 索引大大减少了服务器需要扫描的数据量
2). 索引可以帮助服务器避免排序和临时表
3). 索引可以将随机IO变为顺序IO。
7. 三星系统：索引将相关的记录放到一起则获得一星；如果索引中数据顺序和查找中的排序顺序一致则获得二星；如果索引中的列包含了查询中需要的全部列则获得三星。
8. 索引是最好的解决方案吗？对于非常小的表，大部分情况下简单的全表扫描更高效。对于中到大型的表，索引就非常有效。但对于特大型的表，建立和使用索引的代价将随之增长。这种情况下，则需要一种技术
可以直接区分出查询需要的一组数据，而不是一条记录一条记录的匹配。
如果表的数量特别多，可以建立一个元数据信息表，用来查询需要用到的某些特性。例如：记录"哪个用户信息存储在哪个表里面"。
9. 高性能的索引策略：
1). 独立的列：独立的列是指索引列不能是表达式的一部分，也不能是函数的参数。如果查询中的列不是独立的，则MySQL就不会使用索引。
2). 前缀索引和索引选择性：有时候需要索引很长的字符列，这会让索引变得很大且慢。通常可以索引开始部分的字符，这样可以大大节约索引空间，从而提高索引效率。但这样会降低索引的选择性。
a. 索引的选择性是指，不重复的索引值(也称为基数)和数据表的记录总数(#T)的比值，范围从1/#T到1之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。
唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
b. 一般情况下某个列前缀的选择性也是足够高的，足以满足查询性能。对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。
c. 诀窍在于要选择足够长的前缀以保证较高的选择性，同时又不能太长(以便节省空间)。计算合适的前缀长度的一个方法是计算完整列的选择性，并使前缀的选择性接近于完整列的选择性。
下面是如何计算完整列的选择性：SELECT COUNT(DISTINCT city)/COUNT(*) FROM sakila.city_demo; 查询结构值为：0.031
通常来说，这个例子中如果前缀的选择性能够接近于0.031，基本上就可以用了。可以在一个查询中针对不同前缀长度进行计算，这对于大表非常有用。
下面给出了如何在同一个查询中计算不同前缀长度的选择性：
SELECT COUNT(DISTINCT LEFT(city,3)) AS sel3,COUNT(DISTINCT LEFT(city,4)) AS sel4,COUNT(DISTINCT LEFT(city,5)) AS sel5,
COUNT(DISTINCT LEFT(city,6)) AS sel6,COUNT(DISTINCT LEFT(city,7)) AS sel7 FROM sakila.city_demo;
查询结果值，按顺序为：0.0238 , 0.0293, 0.0305,0.0309,0.0310
查询显示当前前缀长度到达7的时候，再增加前缀长度，选择性提升的幅度已经很小了。
d. 只看平均选择性是不够的，也有例外的情况，需要考虑最坏情况下的选择性。
e. 前缀索引是一种能使索引更小、更快的有效办法，但另一方面也有其缺点：MySQL无法使用前缀索引做ORDER BY和GROUP BY , 也无法使用前缀索引做覆盖扫描。
f. 有时候后缀索引页有用途(例如，找到某个域名的所有电子邮件地址)。MySQL原生并不支持反向索引，但是可以把字符串反转后存储，并基于此建立前缀索引。可以通过触发器来维护这种索引。
3). 多列索引：在MySQL或更新的版本中，会使用"索引合并"策略，查询能同时使用两个单列索引进行扫描，并将结果进行合并。这种算法有三个变种：OR条件的联合(union),AND条件的相交，
组合前两种情况的联合及相交。
索引合并策略有时候是一种优化的结果，但实际上更多的时候说明表上的索引建的很糟糕：
a. 当出现服务器对多个索引做相交操作时(通常多个AND条件)，通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。
b. 当服务器需要对多个索引做联合操作时(通常由多个OR条件)，通常需要耗费大量CPU和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描返回的大量数据时。
c. 优化器不会把这些计算到"查询成本"(cost)中，优化器值关系随机页面读取。这样不仅消耗更多的CPU和内存资源，还会影响查询的并发性。
d. 如果在EXPLAIN中看到有索引合并，应该好好检查一下查询和表的结构，看是不是已经最优的。也可以哦太难过参数optimizer_switch来关闭索引合并功能。也可以使用IGNORE INDEX提示让优化器
忽略掉某些索引。
4). 选择合适的索引列顺序：正确的索引顺序依赖于使用该索引的查询，并同时满足需要考虑如何更好地满足排序和分组的需要。
a. 在一个多列的B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，等等。所以，索引可以按照升序或者降序进行全表扫描，以满足符合列顺序的ORDER BY,GROUP BY和
DISTINCT等字句的查询需求。
b. 当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。这时候索引的作用只是用于优化WHERE条件查询。
c. 更具运行频率最高的查询来调整索引的顺序，让这种情况下索引的选择性最高。
d. 如果是从诸如pt-query-digest这样的工具的报告中提起"最差"查询，那么再按上面办法选定索引顺序往往是非常高效的。如果没有类似的具体查询来运行，那么最好还是按经验法则来做，因为全局法则考
滤的是全局基数和选择性，而不是某个具体查询。
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 的选择性更高，所以答案是将其作为索引列的第一列
e. 尽管关于选择性和基数的经验法则值得去研究和分析，但一定要记住别忘了WHERE字句中的排序、分组和范围条件等其他因素，这些因素可能对查询的性能造成非常大的影响。
10. 聚簇索引：InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。它的数据实际上存储在索引的叶子页中。"聚簇"表示把数据行和相邻的键值紧凑地存储在一起。因为无法同时把数据行存放在
两个不同的地方，所以一个表只能有一个聚簇索引。
1). InnoDB将通过主键聚集数据。如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键作为聚簇索引。InnoDB只聚集在同一个页面中的记录。包
含相邻键值的页面可能会相距甚远。
2). 聚簇索引的优点：
a. 可以把相关的数据保存在一起。
b. 数据访问更快。
c. 使用覆盖索引扫描的查询可以直接使用叶节点中的主键值。
3). 聚簇索引的缺点：
a. 更新聚簇索引列的代价很高，因为会强制将每个被更新的行移动到新的位置。
b. 可能会导致页分裂。
c. 导致全表扫描变慢，尤其是行比较稀疏。
e. 二级索引(非聚簇索引)可能比想象的要更大，因为在二级索引的叶子节点包含了引用行的主键列。
f. 二级索引访问需要两次索引查询，而不是一次。这是因为二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值。这意味着通过二级索引查找行，存储引擎需要找到二级索引的叶子
节点获得对应的主键值，然后根据这个值去聚簇索引中查找到对应的行。这里做了重复工作：两次B-Tree查找而不是一次。
4). 最好避免随机的(不连续且值的分布范围非常大)聚簇索引，特别是对于IO密集型的应用。例如：从性能角度考虑，使用UUID作为聚簇索引会很糟糕：它使得聚簇索引的插入变得完全随机，这是最坏的情况，
是的数据没有任何聚集。向UUID主键插入行不仅花费更长的时间，而且索引占用的空间也更大。这一方面是由于主键字段更长，另一方面毫无疑问是由于页分裂和碎片导致的。
5). 使用InnoDB时应该尽可能地按主键顺序插入数据，并且尽可能地使用单调增加的聚簇键的值插入新行。这样可以顺序地写入数据，减少随机IO，减少碎片和减少分页。
但对于高并发工作负载，按主键顺序插入可能造成明显的争用。
mysql中每个表都有一个聚簇索引（clustered index ），除此之外的表上的每个非聚簇索引都是二级索引，又叫辅助索引（secondary indexes）。
索引的优点：快速定位，避免排序
原文：http://www.cnblogs.com/Jtianlin/p/5150104.html