创建高性能索引

索引是在存储引擎层而不是服务器层实现的

B-Tree索引

B-Tree通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同。B-Tree索引能够加快访问数据的速度,因为存储引擎不再需要进行全表扫描来获取需要的数据,取而代之的是从索引的根节点开始进行搜索。
可以使用B-Tree索引的查询类型:全键值、键值范围、或键前缀查找。
CREATE TABLE People (
last_name varchar(50) not null,
first_name varchar(50) not null,
dob date not null,
gender enum(‘m’, ‘f’) not null,
key(last_name, first_name, dob)
);
全值匹配:匹配全部列
匹配最左前缀:只匹配索引的第一列,例如找last_name(姓)为Allen的人
匹配列前缀:匹配某一列的值的开头部分，例如匹配姓开头为J的人，这里也只使用了索引的第一列。如果以%开头就不行
匹配范围值:查找姓在Allen和Barraymore之间的人。这里也只使用了索引的第一列
精确匹配第一列并范围匹配另外一列:第一列全匹配，第二列范围匹配
只访问索引的查询:只访问索引的查询，即查询只需要访问索引，而无须访问数据行。又叫覆盖索引。
如果ORDER BY子句满足前面列出的集中查询类型,则这个索引也可以满足对应的排序需求。

B-Tree索引的限制:
1.如果不是按照索引的最左列开始查找，则无法使用索引。例如上面无法查找某个特定生日的人
2.不能跳过索引的列，也就是说无法查找last_name为Smith并且某个特定日期出生的人，因为跳过了first_name，此时只能使用索引的第一列。
3.如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。例如查找WHERE last_name=‘Smith’ AND first_name LIKE ‘J%’ AND job = ‘1976-12-23’,这个查询只能使用索引的前两列，因为LIKE是一个范围条件。

哈希索引

哈希索引的数据结构:

槽	值
232(列的哈希值)	指向第一行的指针
2458	指向第四行的指针
7437	指向第三行的指针

哈希索引的限制:
1.该结构只有哈希值和行指针，所以不能使用覆盖索引
2.哈希索引数据并不是按照索引值顺序存储，所以就无法用于排序
3.哈希索引也不支持部分索引列查找，因为哈希索引是使用索引列的全部内容来计算哈希值的。例如，在数据列(A,B)上建立哈希索引，如果查询只有数据列A，则无法使用该索引列。
4.哈希索引只支持等值比较查询
5.考虑哈希冲突，当列的选择性低时，从表中删除一行时，存储引擎需要遍历对应哈希值的链表中的每一行，找到并删除对应行的引用，冲突越多，代价越大。

InnoDB引擎有一个特殊的功能叫自适应哈希。当InnoDB注意到某些索引值被使用得非常频繁时,它会在内中基于B-Tree索引在创建一个哈希索引，使得B-Tree索引也具有哈希索引的一些优点。

高性能的索引策略

如果查询的列不是独立的，则MYSQL就不会使用索引。

前缀索引和索引选择性:

对于BLOB、TEXT或很长的VARCHAR类型的列，必须使用前缀索引，MySQL不允许索引这些列的完整长度。前缀长度的选择要保证较高的选择性，同时又不能太长。
SELECT COUNT(DISTINCT LEFT(city, 7) ) / COUNT(*) AS sel3 from sakila.city_demo;
这条语句可以查询选择性。选择合适的长度保证较高的选择性。

多列索引

索引合并策略:如果EXPLAIN看到Extra带有union字样，说明有索引合并策略
有索引合并通常说明表上的索引建的不好，索引合并需要耗费CPU及内存
选择合适的索引列顺序:当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。这时候索引的作用只是用于优化Where条件的查找。

聚簇索引

聚簇表示数据行和相邻的键值紧凑地存储在一起，因为无法同时把数据行存放在两个不同的地方，所以一个表只能由一个聚簇索引。InnoDB通过主键聚集数据。
优点:
1.将相关的数据保存在一起
2.数据访问更快，将索引和数据保存在同一个B-Tree中，因此从聚簇索引中获取数据通常比在非局促索引中查找要更快。
3.使用覆盖索引扫描的查询可以直接使用页节点中的主键值
缺点:
1.插入速度严重依赖于插入顺序，如果按照主键的顺序插入式加载数据到InnoDB表中速度最快的方式。如果不是按照主键顺序加载数据，那么在加载完成后最好使用OPTIMIZE TABLE重新组织一下表。
2.更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置(行和索引要存在一起)
3.插入新行可能会引起页分裂，如果某个页已满，插入一行到该页，会将该页分裂。
4.二级索引(非聚簇索引)可能比想象的要更大，因为在二级索引的叶子节点包含了引用行的主键列。

MYISAM没有聚簇索引，InnoDB用主键作为聚簇索引，如果正在使用INNODB表并且没有什么数据需要聚集，那么可以定义一个代理键作为主键，最简单的方法是使用自增列，这样可以保证数据行是按顺序写入，从性能的角度来说，使用UUID来作为聚簇索引会糟糕，因为UUID是完全随机的，没有顺序，使用INNODB时应该尽可能地按主键顺序插入数据。

覆盖索引

如果一个索引包含所有需要查询的字段的值,我们就称之为“覆盖索引”。
覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以MySQL只能使用B-Tree索引做覆盖索引。
如果在EXPLAIN的Extra列可以看到"Using index"的信息，说明发起了一个索引覆盖查询
要利用覆盖索引，可以使用一种延迟关联的方法:先根据其他列，找出主键id，再根据id查出全部列。

使用索引扫描来做排序

判断是否利用了索引来排序:MySQL有两种方式可以生成有序的结果:通过排序操作;或者按索引顺序扫描；如果EXPLAIN出来的type列的值为"index"，则说明MySQL使用了索引扫描来做排序。

限制条件:
只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向(倒序或正序)都一样时，MySQL才能够使用索引来对结果做排序。

如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表时，才能使用索引做排序。
ORDER BY子句和查找型查询的限制是一样的:需要满足索引的最左前缀的要求，否则，MYSQL都需要执行排序操作，而无法利用索引排序。但是如果前导列为常量的时候，如果WHERE子句或者JOIN子句中对这些列指定了常量，就可以弥补索引的不足。

压缩(前缀压缩)索引

使用压缩索引来减少索引的大小，使得更多索引能放在内存里。压缩索引使得索引查找要慢好几倍。

冗余和重复索引

MySQL的唯一限制和主键限制都是通过索引实现的，此处新手容易创建重复索引。
冗余索引:比如有索引(A,B)，再创建索引(A)就是冗余索引了。

索引和锁

索引可以让查询锁定更少的行，InnoDB只有在访问行的时候才会对其加锁，而索引能够减少InnoDB访问的行数，从而减少锁的数量。但这只有当InnoDB在存储引擎层能够过滤掉不需要的行时才有效，如果索引无法过滤掉无效的行，那么InnoDB检索到数据并返回给服务器层厚，MySQL服务器才能应用WHERE子句。这时已经无法避免锁定行了:InnoDB已经锁住了这些行。在MySQL5.1和更新版本中，InnoDB可以在服务器端过滤掉行后就释放锁，但是在早期的MySQL版本中，InnoDB只有在事务提交后才能释放锁。(这意味着，where语句是在服务器层执行的)

EXPLAIN分析时，type=range，说明是范围扫描。
高性能Mysql第182页的例子:第一个事务在InnoDB引擎返回结果后，迟迟不提交事务，导致InnoDB锁住了过滤出来的行。如果不能用索引，导致全表扫描则更糟糕，锁住了全部行。

细节:InnoDB在二级索引上使用共享锁，但访问主键索引需要排他锁。

索引案例

IN()相当于多值等条件查询，可以用来处理前导列缺失问题即把前导列全部写到In里面，但是IN列表不宜过多。