一、索引基础

select first_name from actor where id = 5;

如果在id上建有索引，则Mysql将使用该索引找到id=5的列，也就是说Mysql先在索引上按值进行查找，然后返回符合条件的所有数据。
索引可以包含一个或者多个列的值。如果索引包含多个列，那么列的顺序也很重要，因为Mysql只能高效的使用索引的最左前缀列。
索引分类：
普通索引
唯一索引
主键
单列索引和多列索引
选择索引列
1、索引的类型
B-tree索引
InnoDB使用的是B-tree。存储引擎以不同的方式使用B-tree索引，性能也各有不同，各有优劣。B-tree通常意味着所有的值都按照顺序存储，所以很适合查找范围数据。例如，在一个基于文本域的索引树上，按照字母顺序传递连续的值进行查找是非常合适的。像找出所有以I到K开头的名字效率非常高。
可以使用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,first_name,dob
适合B-tree索引
全值匹配
和索引中的所有列进行皮牌，例如前面提到的可用于查找姓名为Cuba Allen、出生于1960-01-01的人
匹配最左前缀
前面提到的索引可用于查找所有姓为Allen的人，即只使用索引的第一列
匹配列前缀
例如：查找所有以J开头的姓的人，只用了索引的第一列
匹配范围值
查找在Allen和Barrymore之间的人，只用了索引的第一列
B-tree索引的使用限制：
1、如果不是按照索引的最左列开始查找，则无法使用索引，对于联合索引如果从左往右第一列不是索引则无法使用索引
2、不能跳过索引中的列，也就是无法查找姓为Smith并且在某个特定日期出生的人，如果不指定名（first name）则Mysql只能使用索引的第一列
3、如果查询中有某个列的范围查询，则其右边所有的列都无法使用索引。例如：
where last_name = ‘smith’ and first_name like ‘J%’ and dob = ‘2015-05-12’ ，这个查询只能使用索引的前两列，因为like是范围条件。
2、哈希索引
基于哈希表的实现，只有精确匹配索引的列查询才有效。对于每一行数据存储引擎都会对所有的索引计算一下哈希码。
在Mysql中只有Memory引擎显式的支持哈希索引，Memory同时支持B-tree
假如有以下表：

create table testhash(
 fname varchar(50) not null,
 lname varchar(50) not null,
 key using hash(fname)
)engine = memory

哈希索引的使用限制：
1、哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。
2、哈希索引数据并不是按照索引值顺序存储，所以无法用于排序
3、不支持部分索引列的匹配查找。例如在数据A、B列上创建哈希索引，如果查询只有数据A列，则无法使用索引。
4、哈希索引只支持等值比较查询，包括=，in <=>，不包括任何范围查询，例如，where price > 100
二、索引的优点
1、索引大大减少了服务器需要扫描的数据量
2、索引可以帮助服务器避免排序和临时表
3、索引可以将随机I/O变为顺序I/O
非常小的表，通常全表扫描更高效
中到大型的表，索引非常有效
特大型的表，需要一种技术可以直接区分出需要的一组数据，分区技术
三、高性能索引的策略
1、独立的列
独立的列是指索引不能使表达式的一部分，也不能是函数的参数
例如，下面的查询无法使用索引

select id from user where id + 1 = 5;

2、前缀索引和索引选择性
有时候需要索引很长的字符列，这会让索引变得大且慢。一个策略时前面提到过的模拟哈希索引。但有时不够
通常可以索引开始的部分字符，这样可以大大节约索引空间，提高索引效率。但这样会降低索引的选择性，选择性是指不重复的索引值和数据表的记录总数的比值。索引的选择性越高查询效率越高。选择性高的索引可以让Mysql过滤掉更多的行。唯一索引的选择性是1，这是最高的索引，性能也是最好的。
3、多列索引
在多个列上建立多个单列索引并不能提高Mysql的查询性能。索引合并策略有时候是一种优化的结果，但实际上更多时候说明了表上的索引建立的很糟糕。
4、选择合适的索引列顺序
当不需要考虑和分组时。将选择性最高的列放在索引的最前列，这时候索引列的作用只是用于优化where条件的查找。以下面的查询为例：
select * from payment where staff_id = 2 and customer_id = 584;
是应该创建一个(staff_id,customer_id)索引还是该颠倒下顺序？
答案是后者
5、聚簇索引
不是单独的索引类型而是一种存储方式。聚簇表示数据行和相邻的键值紧凑的存储在一起。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。
聚集的数据优点：
1、可以将相关的数据保存在一起，例如实现电子邮箱时，可以根据用户Id聚集数据，这样只需要从磁盘上读取少量数据页就能获取用户全部邮件
2、数据访问更快，聚簇索引将索引和数据保存在同一个B-Tree中，查找更快
3、使用覆盖索引扫描的查询可以直接使用页节点中的主键值。
6、覆盖索引
大家都会根据查询的where条件来创建合适的索引，不过这只是索引优化的一个方面。设计优秀的索引应该是考虑到整个查询，而不单单是where的条件查询部分。索引确实是一种高效的查询方式，但是Mysql也可以使用索引来直接获取列，这样就不需要读取数据行。如果索引的叶子节点包含了要查询的数据列，就没有必要再查询表了。如果一个索引包含（或者覆盖）所有需要查询的列，我们称之为覆盖索引。
Mysql只能使用B-Tree索引做覆盖索引。
7、使用索引扫描来做排序
Mysql有两种方式可以生成有序结果：通过排序操作；或者按索引顺序扫描。如果explain出来的type为index，说明Mysql使用了索引扫描来排序。
Mysql可以使用同一个索引既满足排序，又用于查找行。如果可能，设计索引时尽可能的同时满足这两种任务，这样是最好的。只有当索引列的顺序和order by子句的顺序完全一样，并且所有列排序方向都一样，Mysql才能够使用索引来对结果进行排序。如果查询需要关联多个表，则只有当order by子句引用的字段全部为第一个表时，才能使用索引做排序。order by子句和查找型查询的限制是一样的：需要满足索引的最左前缀要求，否则，Mysql都要执行排序操作，无法利用索引进行排序。
8、压缩前缀索引
Myisam压缩索引快的方法是，先完全保存索引块中的第一个值，然后将其他值和第一个值进行比较得到相同前缀的字节数和不同后缀部分，把这部分存储起来即可。例如：
第一个值perform，第二个值performance，那么第二个值前缀压缩成7,ance
9、冗余和重复索引
Mysql允许在相同列上创建多个索引，Mysql需要单独维护重复索引，这会影响性能。
一个经验不足的用户可能是想创建一个主键，先加上唯一限制，然后再加上索引供查询。事实上，Mysql的唯一限制和主键限制都是通过索引实现的，上面写法实际上在相同列上创建了三个重复的索引。除非是在同一列创建不同类型的索引来满足不同的查询需求。
冗余索引和重复索引有些不同，如果创建了索引(A,B)，再创建索引(A)，索引A就是冗余索引。
10、索引和锁
如果你的查询不访问那些不需要的行，就会锁定更少的行，锁定行总是需要额外的开销，其次锁定超过需要的行会增加锁竞争并减少并发性。
即使使用了索引，InnoDB也可能锁定一些不需要的数据，如果不能使用索引查找锁定行的话，Mysql可能全表扫描并锁定所有行。
select for update 比lock in share mode或非锁定查询慢很多。