MySQL 常见索引的简介
索引的基本概念就不介绍了，首先需要熟悉一下 MySQL 的两个比较常见的存储引擎（InnoDB MyISAM）中索引的实现方式的差别，理解了索引的内部实现原理对于后面查询优化的原理也就比较好理解了。
MySQL主要使用B+Tree来存储索引，下图是其数据结构：
（图片来自 Google 侵删）
B+Tree的详细分析此处不做讨论）当访问数据的时候存储引擎不需要进行全表的扫描，只需通过索引数的根结点逐层向下查找直至叶子结点，然后通过叶子结点的指针，不同的存储引擎根据不同的原理去磁盘读取数据。
MySQL 常见引擎 InnoDb 与 MyISAM 索引文件的差异
MyISAM 存储引擎的索引文件和数据问价是分离的，索引文件通过文件指针来定位数据。不管是主键索引还是二级索引都是按照如此的逻辑来存储的。
InnoDB 中叶子结点其实就是文件本身，也就是说一个主键索引的叶子结点就表示一个数据表中的一行的完整信息。这里InnoDB会自动的按照主键对文件进行索引，如果没有定义主键的话InnoDB会选择一个唯一的非空索引来代替，如果没有这样的列话InnoDB会隐式的定义这样一个列。这里数据行和相邻的数据行的物理位置与逻辑位置一致的情况也被称作为 聚簇索引 。（结合前面MyISAM的定义来理解：MyISAM的索引为非聚簇索引）
而我们建立的其他索引称为二级索引，这种二级索引的叶子结点值是：二级索引列值+主键索引值。当通过二级索引取数据时，通过二级索引中的信息获取主键值，然后再去主键索引中去取数据的。
下图概括两种不同存储引擎的索引存储差异：
对于聚簇索引的性能分析
当使用聚簇索引时会对数据查询带来很大的性能优化：
- 因为数据是按照顺序进行聚簇的，索引文件本身亦是数据文件，在读取磁盘数据的时候减少了磁盘的寻道时间。
- 根据局部性原理：
程序运行期间所需要的数据通常比较集中。当计算机从磁盘中一页页的读取数据到内存的时候，每次取的数据被利用就会很高，这样就减少了缺页异常的发生，从而也就减少了磁盘的 I/O 操作频率，而现阶段 I/O 的瓶颈是数据库性能瓶颈的主要因素。
但是聚簇索引也会面临着下面的问题
- 因为文件按照主键进行聚簇，所以当主键的值被更新的时候，会需要对文件重新的排序
- 插入数据时如果不是按照主键值的按序插入的话，也会导致文件重新排序
- 二级索引因为是
二级索引列值+主键索引值的结构，所以会导致二级索引文件过大 - 如果单独使用二级索引查询时除了要访问一次二级索引文件外还需要根据查询到的主键值在主键索引内再一次访问主键索引文件。
根据上述的聚簇索引的更新和插入数据问题，所以在InnoDB中使用AUTO_INCREMENT自增列来当作主键是一个比较好的选择，因为自增保证了主键按序插入，而在业务中也没有无聊到更新自增列数字的需求，同时我们可以理解在聚簇索引中使用UUID（Universally Unique Identifier）这类随机数作为主键是非常不明智的，因为插入的值是随机数，所以每次都要计算该数据行应该的位置，并且如果该位置的数据不在内存中，还需要去磁盘上读取一页数据，无疑又增加了磁盘的 I/O 操作，同时因为插入数据会导致页的频繁的分裂，最终会导致碎片的存在。当然使用自增列作为主键在高并发的情况下并发的插入会导致对主键的竞争，并发性的问题这里暂时不讨论。
由 B+Tree 的索引结构引申出的查询优化之——覆盖索引
由前面的知识我们可以知道，在InnoDB中二级索引上包含了索引列和主键列的值，那么一个优化查询的思路是：如果我们查询的列就是这些二级索引列的值，那么就无需再根据主键值去主键索引里查询了。这种优化思路被称为被索引覆盖的查询。
例如我们在一张学生表中对last_name 和 first_name 字段设置了索引，那么下面的查询就是一个被索引覆盖的查询。
SELECT last_name,first_name FROM STUDENTS
覆盖索引的思路告诉我们尽量查询的列能够覆盖索引列，同时，在较大数据量的时候尽量避免执行 SELECT *这种操作，因为没有哪一个索引能够覆盖所有的列。
PS：当使用覆盖索引查询的时候，执行EXPLAIN查看查询的时候 会看到Extra: Using index信息提示。
同理，在使用排序的时候当ORDER BY 的列和二级索引列的顺序以及排列次序一致的时候，排序就只需访问索引文件，而不用再去主键索引中查找。
MySQL索引的最左侧前缀原则
当我们定一个索引的时候，定义时的位置也是MySQL索引查找匹配索引的顺序。
例如我们定义一个索引如下：
KEY (first_name,last_name,sex)
那么在查询匹配索引的时候也是按照定义时的先后顺序来进行匹配，如果不是按照这个顺序进行条件筛选的话，就无法使用索引来查询数据。例如：
...WHERE last_name='hh' and first_name='xxx'
(有些时候使用EXPLAIN能看到这样也使用了索引查询，但是其实是MySQL查询优化器优化了这个顺序成正确的顺序，最终执行的还是按索引定义的顺序来查询)
另外，MySQL的索引中不能使用LIKE等模糊的查询（准确的说是以通配符开始的模糊查询）当使用模糊查询这类范围查询的时候后面的列就无法使用索引匹配。
同理在范围查询的过程中，当遇到查询条件中出现范围查询的时候，MySQL就无法继续使用索引来进行检索，因此在设计索引的时候一个比较好的方案就是将平时较多使用范围查询的字段例如年龄这样的字段放在索引列的最后一个位置，而如果出现多个范围查询的时候，可以考虑将可能出现的值较少的列用IN这样的关键字来替代，MySQL索引中对IN是可以使用索引检索的，但是也需要考虑不能将In的可能值列举的过长。
延迟关联
在排序分页中我们一个使用的比较经典的SQL如下（假设一个学生表，其中在name和score上建立索引（name，score），主键为id）：
SELECT department（部门） FROM students where name='yang' ORDER BY score LIMIT 100000,10
MySQL 在执行这条SQL的时候会先取前1000000条数据，然后再往后取10条数据并把前面的100000条数据丢弃掉，我们可以发现这个过程中其实前面的100000条数据是没有意义的而且由于这个选择的department是没有索引覆盖的所以这个查询是十分耗时的。所以一个可以选择的优化策略是使用如下这种称之为延迟关联的SQL写法:
SELECT department FROM students INNER JOIN (SELEECT id FROM students where name='yang' ORDER BY score LIMIT 100000,10 ) AS a USING(id)
在这里根据前面二级索引的知识我们可以知道当我们定义（name，score）这样一个二级索引的时候，在索引文件里会追加一个主键跟在这个索引后面也就是说实际上每一个索引结点的真实数据是（name,score,id）那么我们在子查询中对id的查询就是一个索引覆盖查询，这样是比较省时的（虽然还是要查询并丢弃100000条数据，但是这个是扫描索引而不是像前面那样扫描文件，后面关于查询优化我会继续探讨这个问题），然后再根据查询出的主键去查询department这就相当于是在主键索引上查询数据了，也是相较于扫描文件快的多。
如何创建高性能的索引
- 查询时使用索引字段不能用表达式例如：
WHERE id+1=3这种操作 - 索引列的数据不建议太长否则导致索引过大
- 在不考虑排序和分组的情况下，将选择性最高的列放在最前前面是个很好的策略但是要考虑实际的查询情况。
