\
1，为了与时俱进，文中数据库环境为MySQL5.6版本
2，为了通用，更为了避免造数据的痛苦，文中所涉及表、数据，均来自于MySQL官网提供的示例库employees，可通过 https://launchpad.net/test-db/employees-db-1/1.0.6 自行下载。
基本概念
Binary search(二分查找法，折半查找法)：是一种在有序数组中查找某一特定元素的搜索算法。搜素过程从数组的中间元素开始，如果中间元素正好是要查找的元素，则搜素过程结束；如果某一特定元素大于或者小于中间元素，则在数组大于或小于中间元素的那一半中查找，而且跟开始一样从中间元素开始比较。如果在某一步骤数组为空，则代表找不到。这种搜索算法每一次比较都使搜索范围缩小一半。
(以上摘自 http://en.wikipedia.org/wiki/Binary_search_algorithm )
如下图：
B-tree(Btree、B树、B-树)：指的是多路查找树，有别于二叉查找树(Binary Search Tree)、平衡二叉查找树(Balanced Binary Search Tree)。此处的B可理解成Balanced(注1)。
对于一颗M阶的树：
1，定义任意非叶子节点最多只有M个儿子；且M>2；
2，根节点的儿子数为[2, M]；
3，除根节点以外的非叶子节点的儿子数为[M/2, M]；
4，每个节点存放至少M/2-1(取上整)和至多M-1个关键字；(至少2个关键字)
5，非叶子节点的关键字个数=指向儿子的指针个数-1；
6，非叶子节点的关键字：K[1], K[2], …, K[M-1]；且K[i]
7，非叶子节点的指针：P[1], P[2], …, P[M]；其中P[1]指向关键字小于K[1]的子树，P[M]指向关键字大于K[M-1]的子树，其它P[i]指向关键字属于(K[i-1], K[i])的子树；
8，所有叶子节点位于同一层；
如下图：
B+-tree(B+tree、B+树)：也是多路查找树，为B-tree的变形，在B-tree的基础上将叶节点打通，加上指向兄弟节点的指针，形成双向链表。叶节点的打通，使得只需遍历叶节点就可以实现整棵树的遍历，而无需像B-tree一样必须回到根枝节点再访问叶节点，对应数据库中范围查询，大大提高了IO性能。此为MySQL的B-tree索引结构。如下图：
B*-tree(B*tree、B*树)：仍是多路查找树，为B+-tree的变形，在B+-tree的基础上将枝节点(非根非叶)打通，加上指向兄弟节点的指针，形成双向链表。此为Oracle的B-tree索引结构。如下图：
索引结构
聚簇索引表、非聚簇索引表的索引结构，如下图：
由图可以看出，InnoDB表本身就是个索引组织表，即插入数据会按照主键的顺序。InnoDB二级索引叶节点存储的是索引key和表主键，MyISAM索引叶节点存储的则是索引key和指向表的rowid。
直接反应到SQL的执行计划则是：
select id from table where column = '***';
上面的SQL想要走快速索引扫描，InnoDB表需创建索引为index(column)，而MyISAM则需要创建索引index(column,id)。因为在InnoDB物理存储层中，会自动补全主键字典，index(column)实际内部存储为index(column,id)。
InnoDB自增主键的拓展阅读：
http://imysql.com/2014/09/14/mysql-faq-why-innodb-table-using-autoinc-int-as-pk.shtml
索引创建语法
CREATE [ONLINE|OFFLINE] [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[index_type]
ON tbl_name (index_col_name,...)
[index_option]
[algorithm_option | lock_option] ...
index_col_name:
col_name [(length)] [ASC | DESC]
index_type:
USING {BTREE | HASH}
index_option:
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
| COMMENT 'string'
algorithm_option:
ALGORITHM [=] {DEFAULT|INPLACE|COPY}
lock_option:
LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}索引类型：普通索引(什么都不加)、唯一索引(UNIQUE)、全文索引(FULLTEXT)、空间索引(SPATIAL)
索引范围：单列索引、组合索引、部分索引(前缀索引、最左索引)
其中ASC|DESC目前只有语法支持，并无实际效果，默认是升序排列。即可以创建索引index(c1 asc, c2 desc)，但查询select ... order by c1 asc, c2 desc并不会用到索引。换个说法，index(c1 asc, c2 desc)与index(c1 asc, c2 asc)一个效果。
另外：MySQL目前不支持函数索引，不支持反向索引，不支持位图索引(Infobright中KN模式相类似)。
索引的使用原则
一，基数、选择性
基数(Cardinality)：列唯一键(Distinct_keys)的数量，比如性别，该列只有男女之分，所以这一列基数是2，而主键列的基数等于行数。
选择性(Selectivity)：列唯一键(Distinct_Keys)与行数(Num_Rows)的比值。
select
count(distinct column_name) as cardinality,
count(*) as total_rows,
count(distinct column_name) / count(*) * 100 as selectivity
from table_name;择性的过低可能导致执行计划不选择索引。那么如何理解呢？
理解这个问题之前，我们先得知道，为什么通过索引能快速的获取信息，有个很好理解却不太正规的解释：
假设有个表有100w条记录，大小为1G，要找出其中一条数据，只能遍历整个表，运气不好的话，就得读取整个100w记录1G内容，而索引一般是表中的一个或几个列，从空间上来说肯定远小于1G，可能只有几十M，扫描几十M跟扫描1G的效率是显而易见的(此处不考虑单块读多块读情况)，而且索引是B-tree结构，采用二分法进行查找数据，几十M的内容可能只需要扫描到其中几M甚至几十K，然后再通过索引直接找到对应表记录(这个过程在不同的数据库、不同的引擎实现不一样，这里可以简单理解为索引中有指向表记录的指针，此处不考虑集群因子的影响)，效果很明显。
理解了这个，那么第一个问题就很好了明白了，依然举个例子：
有本汉语词典，假设有10W个词，现在需要找其中10个词的解释，你会怎么做，肯定是通过拼音或偏旁检索到具体的页，然后找到解释。那要是要找其中5W个词的解释，你还会去检索拼音或偏旁吗，肯定没人这么傻，直接翻完整个词典找出需要的词一定要省事省时很多。在找不同数量词的时候，肯定会有一个阀值，即到底是多少词的时候，通过拼音或偏旁检索总是优于直接翻找所有页。反应到数据库，就是索引扫描跟全表扫描的场景，而在MySQL中这个阀值大概在20%-30%。也可以由此得出，在某些情况下，全表扫描可能优于索引扫描。(所以经常听到有人会说，为什么我在这个列上创建了索引，执行计划却还是全表扫描，其实此时CBO选择全表扫描是更优的)
下面是一个索引扫描与全表扫描在不同扫描行数时的不同开销的关系图(本图并不精确，只是为了方便理解而表达的一个简单关系)
二，最左前缀原则
employees表中创建组合索引如下：
假设不存在选择性问题，对于以上SQL对索引的使用情况：
1：使用到idx_first_last索引的部分
2：使用到idx_first_last索引的全部
3：不能使用到idx_first_last索引
4：使用到idx_first_last索引的全部
此处我们可以通过如何执行计划的key_len来判断使用部分还是全部索引，如下：
1
：key_len = (14 + 16) * 3 + 2*2 = 94
2：key_len = 14 * 3 + 2 = 44(关于ken_len的计算，可以参考： MySQL执行计划之key_len的计算 )
至于4为什么也会走全部索引，估计有些人不理解，因为很多网站上说，where条件的执行顺序是固定的，从右往左，所以过滤性越好的条件放在最右边，依次到左，其实这条规则现在已经不适用了，这已经不是遥远RBO时代了，现在CBO会根据成本估算，选择一个成本更低的执行计划。(成本从统计信息中计算得出，如果统计信息不正确，那执行计划也可能会选错，SQL就可能达不到预期效果。)
由最左前缀原则引申出的索引冗余问题：
根据最左索引原则，我们不难得出，index(a,b)相当于创建了二个索引index(a)、index(a,b)。也可以类推，index(a,b,c)相当于创建了三个索引index(a)、index(a,b)、index(a,b,c)。
但是，如果where条件中只需要过滤a列的数据，分别使用index(a)、index(a,b)在性能上会有什么不一样吗？我们测试一下就知道了。
先在employees表中创建如下两个索引(每次创建索引前先清空原有索引)：
通过强制指定cbo选择索引，分析上述执行计划，key_len一致，rows一致，基本可以看出两个索引的执行计划性能是一样的，所以idx_first_name这个索引就显得很冗余。
上面sql是指定了cbo走某个索引，要是不指定的话，cbo默认会选什么呢？答案很简单，哪个先创建就选哪个。(这个可以自己试下)
三，覆盖索引原则(引申：利用索引排序)
employees表中创建组合索引如下：
对于以上SQL对索引的使用情况：
1：所有信息都可以从idx_first_last索引中获取(即索引已覆盖SQL所需所有信息)
2：所有信息都可以从idx_first_last索引中获取
3：所有信息都可以从idx_first_last索引中获取，并且也能通过索引直接获取排序
4：所有信息都可以从idx_first_last索引中获取，但无法通过索引直接获取排序，需有额外的排序过程，但索引中依然包含排序字段。
5：where条件能通过idx_first_last索引过滤出结果集(此时仍需回表查出select部分所需字段返回给用户)
6：先通过first_name字段所在idx_first_last索引快速过滤出结果集1，在回表获取表中其它字段信息，并通过hire_date字段过滤出结果集1中的符合条件的数据，最终反馈给用户。
前两个很好理解，主要是后面四个，看下执行计划：
看执行计划的Extra，分别为：
3，Using where; Using index
表示可通过覆盖索引获取全部信息
4，Using where; Using index; Using filesort
表示可通过覆盖索引获取全部信息，但有排序
5，Using index condition
表示可通过索引过滤数据，但要取出全部数据仍需回表(这里有个Index Condition Pushdown问题，参见 http://dev.mysql.com/doc/refman/5.6/en/index-condition-pushdown-optimization.html )
6，Using index condition; Using where
表示可通过索引过滤部分数据，但要过滤出全部数据仍需回表
需要注意的是，当Using where和Using index同时出现的时候，此时Using where只是用来从索引中查找数据，此数据如果不是用来过滤，那么就是用来读取，以避免回表读取数据行。
具体的Extra解释，可参见官方文档：
explain-extra-information
http://dev.mysql.com/doc/refman/5.6/en/explain-output.html#explain-extra-information
四，一些无法使用索引的情况
假设不存在选择性问题，对于以上SQL对索引的使用情况：
1：不能使用索引
2：能使用到索引idx_first
3：不能使用索引
4：不能使用索引
5：不能使用索引
6：能使用到索引idx_birth
7：能使用到索引idx_birth
8：不能使用索引
下面是执行计划：
解析
1：表达式右侧出现运算，无法使用索引。
2，3，4：解释起来很简单，就像查字典一样，比如要查拼音首字母是ch的字，按顺序翻完以ch开头的页就能有结果，但要是查拼音包含an的字，那就只能翻完整个字典才能知道哪些是需要的，此时就相当于无法走索引，只能走全表扫描。
5：跟1一样(在oracle中可以创建函数索引达到一些优化目的)
6，7：birth_date为date类型，'1954-06-19'为varchar，发生隐式转换，'1954-06-19'由varchar类型被转换成date类型，此时的SQL相当于birth_date = date('1954-06-19')，所以能使用到索引。
8：like用于处理字符串的模糊匹配，此时'1954-06%'只能当做varchar处理，跟birth_date的date类型不匹配，无法使用索引。
五，多个索引同时使用的情况：
index merge(Using intersect、Using union、Using sort_union)，即先仅通过索引进行交集/并集过滤数据，再进行后续的操作。
(实际中，这种情况出现概率比较小，了解下即可。)
