一、什么是索引

1、简单定义

索引通过各种数据结构实现值到行（值=》行）位置的映射，没有索引会全表扫描；

2、索引的作用

（1）提高访问速度；
（2）实现主键、唯一键逻辑；

（1）在计算机世界了，磁盘的IO（读写性能）是常见的性能瓶颈，它与内存IO/CPU的IO根本不在一个量级上。一般机械硬盘的随机读写效率在120/s~150s/s；也就是每秒只    能做120到150次的响应，因此计算机里的IO是宝贵的资源。索引通过各种数据结构的实现可以减少磁盘IO的消耗，提高了访问速度。（2）数据库里有主键或唯一索引时，每次新插入数据都要验证新增记录是否在数据库里存在，这里的验证其实就是一个select操作。对主键建立索引可以大大提高这种验证查询的效率，因此数据库不管是mysql还是oracle都会对主键默认建立索引（聚集索引）。

3、索引类型（数据结构）

索引类型是指实现索引的具体数据结构，mysql的索引类型主要包括：Btree索引、hash索引等,重点是B-tree索引

（1）Btree索引

MYSQL里的Btree索引，实际上是B+tree索引，是在Btree上（平衡二叉树）的改良的。

Btree上每个节点只有一个值，而B+tree的每个节点上可以保护很多个值，且值是按照索引键值顺序来排序的，这是因为innodb每次读取数据节点的数据块是以page（页，16k）为单位读取数据，如果尽可能多的将数据值放入一个page可以极大减少IO次数。

innodb存储类型的表都是以索引组织表形式存储的，也就是整个数据表的存储都是B+tree结构的。B+tree的层高不多，例如图上层高是3，也就是做查询最多会查三次数据页（page）也就是三次IO操作，就能查到数据的rowID，然后根据rowID获取到整个数据行信息。例子：有表t1(id,name)，是innodb存储类型，其中id是主键，name上有索引；【查询-1】：select * from t1 where id=1000;由于查询条件字段是主键id，主键是聚簇索引，innodb存储类型的表都是以索引组织表存储的，也就是将主键作为键值把整个表建立成一个索引，也就意味着，整个表的所有    数据的物理存储都是按照主键的存储顺序来排序的（叶子节点根据主键顺序，依次存储数据行记录）。可以想象整个数据表就是一个B+tree结构，查询键是主键，叶子节点是数据行，假设B+tree层高为3，则select * from t1 id=1000;语句只要三次IO操作（查找三个page）就能找到id=1000的记录。 【查询-2】：select * from t1 where name=‘chenjing‘;由于查询条件字段不是主键但是有建立索引，可以想象：有一个B+tree树，查询键是name字段值，叶子节点是主键值（**如果没有主键，叶子节点就是唯一索引，如果也没有唯一索引，则叶子节点就是innodb引擎内部自建的有序rowID**）。select * from t1 where name=‘chenjing‘;这个语句要先根据name字段查询索引，获取到主键（也可能是唯一索引/rowID)，在根据主键查找表结构的B+tree，根据主键查找到数据记录行。

补充：innodb存储引擎表，没有显示定义主键，也没有非空的唯一索引，那么innodb存储引擎会自动创建一个隐藏的主键，但是这个隐藏的主键是有限制的。innodb存储引擎会自动创建一个6字节大小的指针。

4、聚簇索引和二级索引（非聚簇索引，一般索引）

在《数据库原理》里面，对聚簇索引的解释是:聚簇索引的顺序就是数据的物理存储顺序，而对非聚簇索引的解释是:索引顺序与数据物理排列顺序无关。正式因为如此，所以一个表最多只能有一个聚簇索引。
（1）聚簇索引：主键就是聚簇索引，将主键作为键值把整个表建成一个索引。也就意味着，整个表的所有数据的物理存储都是按照主键的索引顺序来排序的（叶子节点根据主键顺序，依次存储数据行）；
（2）二级索引：除了主键以外，用户创建的其他索引，也是B+tree结构，键值就是建立了索引的那个字段，叶子节点的值（指针指向的）其实就是主键，一般查询会先根据二级索引（建立了二级索引的字段）查找对应的主键，再根据主键查找数据表的B+tree，找到对应的记录行数据。

二级索引并不是说层高只有两层，二级索引也就是非聚簇索引，和聚簇索引相区别。
建立索引是有代价的，在增删改操作时都可能要更新索引的B+tree来保证树的平衡，导致每一个索引行的更新都变成了一个内部事务，索引越多，事务越长。因此索引不是越多越好；

【聚簇索引和非聚簇索引的区分】聚簇索引和非聚簇索引，这是索引的两种类型。在聚簇索引中，索引的叶子节点包含实际的数据，记录的索引顺序和物理顺序相同。主键就是聚簇索引，innodb类型的表就是以B+tree形式存储，叶子节点包含的是完整的记录和行数据。非聚簇索引也即是一般索引（包括唯一索引），又叫二级索引。非聚簇索引中，叶子节点指向的是表中的记录rowID，记录的物理顺序和逻辑顺序没有必然联系。总结：主键是聚簇索引，也是唯一索引；但是唯一索引不是聚簇索引。非聚簇索引也就是二级索引，是除了主键外，用户创建的其他索引，包括唯一索引。唯一索引不是聚簇索引，聚簇索引是唯一索引。

5、索引的优势

（1）减少查询IO;
（2）优化等值查询或者范围查询；也即是范围查询和等值查询都是会走索引的；
（3）利用有序特性（例如：order by/group by/distinct/max/min等函数）；这些操作都是利用排序技术来实现的，而索引天然就是有序的，因此使用到order by等有序操作时，对相关字段建立索引会提高效率。

6、如何用好索引

（1）依据where查询条件建立索引；
（2）使用联合索引，而不是多个单列索引；
例如：select * from tab_a where b=? and c=?这个SQL，对b c字段建立联合索引的效率比单列的索引效率更高。
（3）联合索引中索引的顺序根据区分度排，区分度大的放在前面。区分度是指字段值的种类，字段值种类越多的字段要放在前面，例如：idx_smp(name,gender)的效率要比idx_smp(gender,name)的效率高
（4）联合索引能为前缀单列、复列查询提供帮助；
例如：
有idx_smp(a,b,c)这样的索引，where a=?或者where a=? and b=?都可以使用该索引，但是where c=?就无法使用该索引。
（5）同样的，要合理创建联合索引，避免冗余
例如建立了idx_smp（a,b,c）就不需要建立idx_smp(a)、idx_smp(a,b)索引了。
（6）order by group by distinct等需要排序的操作，在没有索引的大数据量情况下需要排序，对IO和CPU性能消耗很大。如果有类似排序需求，则需要对相关字段建立索引，这样利用索引的有序特性不需要排序，直接按着索引顺序扫描即可。

7、查看是否使用了索引：explain命令查看

（1）explain是确定一个查询如何走索引的最简便有效的方法；
（2）关注的项目：
type：查询access的方式；
key：本次查询最终选择使用哪个索引，NULL表示未使用索引；
key_len：选择的索引使用的前缀长度或者整个长度（判断联合索引的使用情况）；
rows：可以理解为查询逻辑读，需要扫描过的记录行数；
extra：额外信息，主要指的fetch data的具体方式；
（3）explain的一些使用建议：
（3.1）对不确定执行计划的关键语句上线前务必explain；
（3.2）type为all的要格外注意，避免全表扫描；
（3.3）key_len只能用很少一部分前缀的，要注意索引字段顺序等；
（3.4）extra里看到using filesort和using tmporary都要尽量优化，这两种fetch方式不应该出现在任何执行频繁的关键语句中。

（4）强制使用索引hint：
select * from table_1 force index(xxx)…
select * from table_1 ignore index(yyy)….
默认情况下，建议使用mysql优化器，不要强制所用或忽略索引

20160916-2：索引与查询优化