1 索引的好处
- 大大减少存储引擎需要扫描的数据量
- 排序以避免使用临时表
- 把随机I/O变为顺序I/O
2 实例
执行 select * from T where k between 3 and 5，需要几次树的搜索，扫描多少行？
- 创建表
- 插入数据
- InnoDB索引组织结构
- SQL查询语句的执行流程：
在k索引树找到k=3，取得 ID 300
再到ID树查到ID 300对应的R3
在k树取下个值5，取得ID 500
再回到ID树查到ID 500对应R4
在k树取下个值6，不满足条件，循环结束
回到主键索引树搜索的过程，称为回表。
查询过程读了k索引树的3条记录（步骤135），回表两次（24）
由于查询结果所需数据只在主键索引有，不得不回表。那么，有无可能经过索引优化，避免回表？
3 覆盖索引
执行语句
select ID from T where k between 3 and 5
只需查ID值，而ID值已在k索引树，因此可直接提供结果，不需回表。即在该查询，索引k已“覆盖”我们的查询需求，称为覆盖索引。
覆盖索引可减少树的搜索次数，显著提升查询性能，使用覆盖索引是个常用性能优化手段。
使用覆盖索引在索引k上其实读了三个记录，R3~R5（对应的索引k上的记录项）
但对于Server层，就是找引擎拿到两条记录，因此MySQL认为扫描行数是2。
问题
在一个市民信息表，有必要将身份证号和名字建立联合索引？
假设这个市民表的定义：
CREATE TABLE `tuser` ( `id` int(11) NOT NULL, `id_card` varchar(32) DEFAULT NULL, `name` varchar(32) DEFAULT NULL, `age` int(11) DEFAULT NULL, `ismale` tinyint(1) DEFAULT NULL, PRIMARY KEY (`id`), KEY `id_card` (`id_card`), KEY `name_age` (`name`,`age`) ) ENGINE=InnoDB
身份证号是市民唯一标识。有根据身份证号查询市民信息的，只要在身份证号字段建立索引即可。再建立一个（身份证号、姓名）联合索引，是不是浪费空间？
如果现在有一个高频请求，根据身份证号查询姓名，联合索引就有意义了。可在这个高频请求上用到覆盖索引，不再回表查整行记录，减少了执行时间。
当然索引字段的维护总是有代价。建立冗余索引支持覆盖索引就需权衡考虑。
2 何时用索引
(1) 定义有主键的列一定要建立索引 : 主键可以加速定位到表中的某行
(2) 定义有外键的列一定要建立索引 : 外键列通常用于表与表之间的连接，在其上创建索引可以加快表间的连接
(3) 对于经常查询的数据列最好建立索引
① 对于需要在指定范围内快速或频繁查询的数据列，因为索引已经排序，其指定的范围是连续的，查询可以利用索引的排序，加快查询的时间
② 经常用在 where子句中的数据列，将索引建立在where子句的集合过程中，对于需要加速或频繁检索的数据列，可以让这些经常参与查询的数据列按照索引的排序进行查询，加快查询的时间
如果为每一种查询都设计个索引，索引是不是太多？
如果我现在要按身份证号去查家庭地址？虽然该需求概率不高，但总不能让它全表扫描？
但单独为一个不频繁请求创建（身份证号，地址）索引又有点浪费。怎么做？
B+树这种索引，可利用索引的“最左前缀”，来定位记录。
为了直观地说明这个概念，用（name，age）联合索引分析。
索引项按照索引定义出现的字段顺序排序。
当逻辑需求是查到所有名字“张三”的，可快速定位到ID4，然后向后遍历得到所有结果。
要查所有名字第一个字“张”的，条件"where name like ‘张%’"。也能够用上索引，查找到第一个符合条件的记录是ID3，然后向后遍历，直到不满足。
不只是索引的全部定义，只要满足最左前缀，就可利用索引加速。
最左前缀可以是
联合索引的最左N个字段
字符串索引的最左M个字符
联合索引内的字段顺序
- 标准
索引的复用能力。因为可以支持最左前缀，所以当已经有了(a,b)这个联合索引后，一般就不需要单独在a上建立索引了。 - 原则
如果调整顺序，可少维护一个索引，那么这顺序优先考虑。 - 为高频请求创建(身份证号，姓名）联合索引，并用这索引支持“身份证号查地址”需求。
如果既有联合查询，又有基于a、b各自的查询？
查询条件里只有b的，无法使用(a,b)联合索引，这时不得不维护另外一个索引，即需同时维护(a,b)、(b) 两个索引。
这时要考虑原则就是空间
比如市民表，name字段比age字段大 ，建议创建一个（name,age)的联合索引和一个(age)的单字段索引
3 索引优化
MySQL的优化主要分为
- 结构优化（Scheme optimization）
- 查询优化（Query optimization）
- 讨论的高性能索引策略主要属于结构优化。
为了讨论索引策略，需要一个数据量不算小的数据库作为示例
选用MySQL官方文档中提供的示例数据库之一：employees
这个数据库关系复杂度适中，且数据量较大。下图是这个数据库的E-R关系图（引用自MySQL官方手册）：
3.1 最左前缀原理与相关优化
要知道什么样的查询会用到索引，和B+Tree中的“最左前缀原理”有关。
联合索引（又名复合索引）
MySQL中的索引可以以一定顺序引用多列，这种索引叫做联合索引，是个有序元组<a1, a2, …, an>。
如何选择索引列的顺序
- 经常会被使用到的列优先
- 选择性高的列优先
- 宽度小的列优先
覆盖索引(Covering Indexes)
包含满足查询的所有列。只访问索引的查询，只需读索引而不用读数据，大大提高查询性能。
优点
索引项通常比记录要小，使得MySQL访问更少数据
索引都按值排序存储，相对于随机访问记录，需要更少I/O
大多数据引擎能更好的缓存索引。比如MyISAM只缓存索引
因为InnoDB使用聚集索引组织数据，若二级索引中包含查询所需的数据，就无需回表
可以优化缓存，减少磁盘IO操作
可以减少随机IO，变随机IO操作变为顺序IO操作
可以避免MyISAM表进行系统调用
覆盖索引只有B-TREE索引存储相应的值，并非所有存储引擎都支持覆盖索引(Memory/Falcon就不支持)。
对于索引覆盖查询(index-covered query)，使用EXPLAIN时，可以在Extra列中看到Using index。
在大多数引擎中，只有当查询语句所访问的列是索引的一部分时，索引才会覆盖
但是，InnoDB不限于此，InnoDB的二级索引在叶节点中存储了primary key的值
覆盖索引失效场景
存储引擎不支持覆盖索引
查询中使用了太多的列
使用了双%号的like查询
使用覆盖索引查询数据
select *不能用覆盖索引
以employees.titles表为例，下面先查看其上都有哪些索引：
从结果中可以看到titles表的主索引为<emp_no, title, from_date>，还有一个辅助索引<emp_no>
为了避免多个索引使事情变复杂（MySQL的SQL优化器在多索引时行为比较复杂），我们将辅助索引drop掉
ALTER TABLE employees.titles DROP INDEX emp_no;
这样就可以专心分析索引PRIMARY
