一：索引的类型
二：索引的优点
三：高性能索引策略
四：索引案例
1.1类型介绍
索引有很多类型，可以为不同场景提供更好的性能。在MySQL中，索引是在存储引擎层而不是服务器层实现的。所以，并没有统一的索引标准：不同存储引擎的索引的工作方式并不一样，也不是所有的存储引擎都支持所有类型的索引，即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同
1.2 B+Tree索引
存储引擎以不同的方式使用B+Tree索引，性能也各有不同，各有优劣。
例如MyISAM使用前缀压缩技术使得索引更小，但InnoDB则按照原数据格式进行存储。
再如MyISAM索引通过数据的物理位置引用被索引行，而InooDB则根据主键引用被索引的行
1.2.1 B+Tree索引数据结构
1.2.2理解数据结构
假如有如下数据表：
CREATE TABLE people(
last_name varchar(50) not null,
first_name varchar(50) not null,
dob date not null,
gender eum('m','f') not null,
key(last_name, first_name, dob)
)对于每一行的数据，索引中包含了，last_name，first_name和dob列的值
注意：索引对多个值进行排序的依据是CREATE TABLE语句中定义索引时列的顺序。看最后两个条目，两个人的姓和名都一样，则根据他们的出生日期来排列顺序
1.2.3 B+Tree索引对如下类型的查询有效
全值匹配：和索引中的所有列进行匹配，例如前面创建的索引可用于查找姓名为Allen Cuba，出生于1960-01-01的人
匹配最左前缀：前面创建的索引可用于查找所有姓为Allen的人，即只使用索引的第一列
匹配列前缀：前面创建的索引也可以只匹配某一列的值的开头部分，例如可以用于查找所有以J开头的姓的人
匹配范围值：前面创建的索引可用于查找姓在Allen和Barrymore之间的人。（这里只使用了索引的第一列）
精确匹配某一列并范围匹配另外一列：前面创建的索引可用于查找所有姓为Allen，并且名字是字母K开头（比如Kim）的人。即第一列last_name全匹配，第二列first_name范围匹配
只访问索引的查询：B+Tree通常可以支持“只访问索引的查询”，即查询只需要访问索引，而无须访问数据行。
因为索引树中的节点是有序的，所以除了按值查找之外，索引还可以用于查询中的ORDER BY操作。一般来说，如果B+Tree可以按照某种方式查找到值，那么也可以按照这种方式用于排序。所以，如果ORDER BY 子句满足前面列出的几种查询类型，则这个索引也可以满足对应的排序需求
1.2.4 B+Tree索引的限制
1.2.4.1如果不是按照索引的最左列开始查找，则无法使用索引。例如上面例子中的索引无法用于查找名字为Kim的人，也无法查找某个特定生日的人，因为这两列都不是最左数据列。类似的，也无法查找姓氏以某个字母结尾的人
1.2.4.2不能跳过索引中的列。也就是说，前面所述的索引无法用于查找姓为Allen并且在某个特定日期出生的人。如果不指定名（first_name），则MySQL只能使用索引的第一列
1.2.4.3如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。例如有查询WHERE last_name='Allen' ANDfirst_name LIKE 'K%' AND dob='1930-07-12'，这个查询只能使用索引的前两列，因为这里LIKE是一个范围条件（但是服务器可以把其余列用于其它目的）。如果范围查询列值数量有限，那么可以通过使用多个等于条件来代替范围条件。尽可能将范围查询放到最后,因为范围查询后的列不能使用索引
2.1索引的优点
索引可以让服务器快速的定位到表的指定位置。但是这并不是索引的唯一作用，到目前为止
可以看到，根据创建索引的数据结构不同，索引也有一些其它的附加作用
最常见的B+Tree索引，按照顺序存储数据，所以MySQL可以用来做ORDER BY 和GROUPBY操作。因为数据是有序的，所以B+Tree也就会将相关的列值都存储在一起。最后，因为索引中存储了实际的列值，所以某些查询只使用索引就能够完成全部查询。据此特性，总结下来索引有如下三个优点：
1.索引大大减少了服务器需要扫描的数据量
2.索引可以帮助服务器避免排序和临时表
3.索引可以将随机I/O变为顺序I/O
Lahdenmaki和Leach提出，如何评价一个索引是否适合某个查询的“三星系统”：
A.索引将相关的记录放到一起则获得一星；
B.如果索引中的数据顺序和查找中的排序顺序一致则获得两星；
C.如果索引中的列包含了查询中需要的全部列则获得三星；
3.高性能的索引策略
正确的创建索引和使用索引是实现高性能查询的基础。现在讨论一下如何真正的发挥这些索引的优势
3.1独立的索引
通常看到一些查询不当的使用索引，或者使MySQL无法使用已有的索引。如果查询中的列不是独立的，则MySQL就不会使用索引。“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数
例1：下面这个查询就无法使用user_id列的索引
mysql> SELECT user_id FROM tb1 WHERE user_id+1 = 5;
很容易看出WHERE中的表达式起始等价于user_id=4，但是MySQL无法自动解析这个方程式，这完全是用户行为。我们应该养成简化WHERE条件的习惯，始终将索引列单独放在比较符号的一侧
例2：下面这个查询就无法使用date_col列的索引
mysql> SELECT ... WHERE TO_DAYS(CUURRENT_DATE) - TO_DAYS(date_col) <= 10
解决方法：在程序中获取当前时间，减去10天，加入最后的值存储在last_date_val中
mysql> SELECT ... WHERE date_col <= last_date_val
3.2前缀索引和索引选择性
有时候需要索引很长的字符列，这会让索引变得很慢。通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。
索引选择性是指：不重复的索引值（也称为基数）和数据表的记录总数（#T）的比值，范围从1/#T到1之间。索引的选择性越高，则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也最好
理解索引选择：每行记录都有一个索引值，如果前缀过少，那么索引值重复的可能性就越大，这样在查询的时候查询到的数据就越多，过滤掉的数据就越少，所以要找一个合理的前缀。
合理前缀的诀窍在于：选择足够长的前缀以保证较高的选择性，同时又不能太长（以便节约空间）。前缀应该足够长，以使得前缀索引的选择性接近于索引整个列。换句话说，前缀的“基数”应该接近于完整列的“基数”
3.2.1如何选择合理前缀的长度
mysql> CREATE TABLE tb( -> city VARCHAR(50) NOT NULL -> ); Query OK, 0 rows affected (0.26 sec) mysql> SELECT COUNT(*) AS cnt, city_name FROM city GROUP BY city_name ORDER BY cnt DESC LIMIT 10;
可以看到，上面每个值都出现了5-7次，现在查找到最频繁出现的城市，先从3个前缀字母开始
mysql> SELECT COUNT(*) AS cnt, LEFT(city_name,3) AS pref FROM city GROUP BY pref ORDER BY cnt DESC LIMIT 10;
每个前缀都比原来的城市出现的次数更多，因此唯一前缀比唯一城市要少的多。然后增加前缀的长度，知道这个前缀的选择性接近完整列的选择性。
mysql> SELECT COUNT(*) AS cnt, LEFT(city_name,7) AS pref FROM city GROUP BY pref ORDER BY cnt DESC LIMIT 10;
计算合适前缀的长度的方法就是计算完整列的选择性，并使前缀的选择性接近于完整列的选择性。下面是如何计算完整列的选择性：
mysql> SELECT COUNT(DISTINCT city_name)/COUNT(*) FROM city;
通常来说（尽管也有例外情况），这个例子中如果前缀的选择性能够接近于0.4317，基本上就可用了，可以在一个查询中针对不同前缀长度计算。
显示长度为7的时候，再增加前缀长度，选择性已经没有提升了（或者某些时候提升幅度很小了）
3.2.2创建前缀索引
mysql> ALTER TABLE city ADD [KEY|INDEX] city_name_index(city_name(7));
前缀索引是一种能使索引更小、更快的有效方法，但另一方面也有其缺点：MySQL无法使用前缀索引做ORDER BY 和 GROUP BY ，也就无法使用前缀索引做覆盖扫描
3.3多列索引
很多人对多列索引的理解还不够。一个常见的错误就是，为每个列创建独立的索引，或者按照错误的顺序创建多列索引
CREATE TABLE t( c1 INT, c2 INT, c3 INT, KEY(c1), KEY(c2), KEY(c3) );
这种索引策略，一般是由于人们听到一些说，“把WHERE条件里面的列都建上索引”导致的。实际上是非常错误的，这样一来最好的情况下也只能是“一星”索引，其性能比起真正最有的索引可能差几个数量级。有时候如果无法设计一个“三星”索引，那么不如忽略掉WHERE子句，集中精力优化索引列的顺序，或者创建一个全覆盖索引
在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能。MySQL5.0和更新版本引入了一种叫“索引合并”的策略，一定程度上可以使用表上的多个单列索引来定位指定的行。
更早版本的MySQL只能使用其中某一个单列索引，然后这种情况下没有哪一个独立的单列索引是非常有效的。例如：表film_actor在字段film_id和actor_id上各有一个单列索引，但对于下面这个查询WHERE条件，这两个单列索引都不是好的选择
mysql> SELECT film_id,actor_id FROM film_actor WHERE actor_id=1OR film_id=1;
在老的MySQL版本中，MySQL对这个查询会使用全表扫描。除非改写成如下的两个查询。
mysql> SELECT film_id,actor_id FROM film_actor WHERE actor_id=1 -> UNION ALL -> SELECT film_id,actor_id FROM film_actor WHERE film_id=1 AND actor_id<>1;
3.4选择合适的索引列顺序
3.4.1例子分析
mysql> SELECT * FROM payment WHERE staff_id = 2 AND customer_id = 584;
是创建一个(staff_id, customer_id)索引还是应该颠倒顺序呢？可以做一些查询来确定这个表中值的分布情况，并确定哪个列的选择性更高。
mysql> SELECT SUM(staff_id=2),SUM(customer_id=584) FROM payment;
mysql> SELECT COUNT(DISTINCT(staff_id))/COUNT(*) AS staff_id , COUNT(DISTINCT(customer_id))/COUNT(*) AS customer_id , COUNT(*) FROM payment;
