【Mysql篇】了解Mysql（二）

索引

索引是一种数据结构，帮助我们快速的检索数据库中的数据。大概分为以下几类：

普通索引normal：仅仅加快查询；
唯一索引unique：加索查询，列值唯一，可以有NULL。
主键索引primary：加速查询，列值唯一，不可以为NULL，表中只有一个。
组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并。
全文索引full text：对文本的内容进行分词，进行搜索。

索引具体底层（数据结构）

索引的数据结构和具体存储引擎的实现有关
MySQL主要有两种结构：Hash索引和B+Tree索引，我们使用的是InnoDB引擎，默认的是B+树。

B+树索引和Hash索引比较

Hash索引底层是哈希表(不了解点击这里等待开通)，是以Key-Value存储数据的结构，所以数据在存储关系上是没有顺序的，没办法进行范围查询，必须要全表扫描。Hash索引只适用于等值查询的场景。
B+树索引更适合外部存储。由于内结点不存放真正的数据（只存放其子树的最大或最小的关键字，作为索引），一个结点可以存储更多的关键字，每个结点能索引的范围更大更精确，也意味着B+树单次磁盘IO的信息量大于B树，I/O的次数相对减少。B+树更容易进行区间访问，因为叶子节点维护了一个链表。

B+树的叶子节点，可以存放哪些东西

在B+树索引中，叶子节点可能存储当前的key值，也可能存储当前的key值以及整行数据，这就是聚簇索引和非聚簇索引。
在InnoDB引擎中，只有主键是聚簇索引，如果没有主键则挑选一个唯一键作为聚簇索引，如果没有唯一键，则隐式生成一个键来建立聚簇索引。

聚簇索引和非聚簇索引特点

聚簇索引更快，因为主键索引树的叶子节点直接就是要查询的整行数据。
非主键索引的叶子节点是主键的值，查到主键的值以后，还需再通过主键的值再进行一次查询。
当查询使用聚簇索引的时候，在对应的叶子结点上可以获取到整行的数据，不需要回表查询，而非聚簇索引需要回表查询。

非聚簇索引一定要回表搜索吗

不一定，这涉及到查询语句所要求的字段是否全部命中索引，如果全部命中,就不必再进行回表查询，即覆盖索引。

覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖。
当一条查询语句符合覆盖索引条件时，MySQL只需要通过索引就可以返回查询所需要的数据，这样避免查到索引后再返回表操作，减少I/O提高效率。

//表test中有一个普通索引 idx_key1_key2(key1,key2)。
select key2 from test where key1 = ‘keytest’;
//当通过上列SQL语句，就可以通过覆盖索引查询，无需回表。

建立索引时，考虑哪些因素

考虑字段的使用频率，经常作为查询条件的字段比较适合索引。索引占据内存，且修改表会导致索引更新，因此建立索引时要考虑表结构。
在一些场合使用联合索引是比较好的，如我们建立一个（学校-班级-ID）的联合索引，这样会比建立三个索引效果好，但如果只使用其中一个索引ID不会走联合索引，会导致全表扫描。使用联合索引要注意顺序，尽量把区分度大的索引放在前面。

为什么建立联合索引（区分度大的索引放在前面）

在联合索引使用中，想要命中索引需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。联合索引中有个最左匹配原则，当我们建立联合索引（A，B，C），实际上已经建立了（A）、（A，B）、（A，B，C）三个联合索引。
比如上面说的（学校-班级-ID）联合索引，b+树是按照从左到右的顺序来建立搜索树的，b+树优先比较学校来确定下一步的搜索方向，如果还未达到条件则继续执行搜索。如果只有学校字段，班级字段缺失，只能找到这个学校的所有字段，然后再匹配相应ID的学生，此种情况无法用到联合索引。

如何判断创建的索引是否使用到（如何分析Sql语句）

一般通过explain命令查看语句的执行计划，EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理SQL语句。分析查询语句或是表结构的性能瓶颈。

Explain的字段

id：select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
select_type：查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。
table：当前执行的表。
type：查询使用了哪种类型。查询性能从最好到最差依次是：system > const > eq_ref > ref > range > index > all，一般来说，得保证查询至少达到range级别，最好达到ref。
possible_keys 和 key：（1）possible_keys 显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。（2）key表示实际使用的索引，如果为NULL，则没有使用索引。（可能原因包括没有建立索引或索引失效）。
key_len：索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。
ref：显示索引的那一列被使用，如果可能的话，最好是一个常数。哪些列或常量被用于查找索引列上的值。
rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，也就是说，用的越少越好
Extra：包含不适合在其他列中显式但十分重要的额外信息。

什么情况下针对列创建了索引，查询的时候却没有使用

参与了数学运算或者函数。
字符串like时的左边是通配符，"%a"。
当mysql分析全表扫描比使用索引快时。

MySQL 5.6，对索引做了哪些优化

索引下推，默认是开启的。

官方文档中给的例子和解释如下： people表中（zipcode，lastname，firstname）构成一个索引， SELECT * FROM people WHERE zipcode=‘95054’ AND lastname LIKE ‘%etrunia%’ AND address LIKE ‘%MainStreet%’;如果没有使用索引下推技术，则MySQL会通过zipcode='95054’从存储引擎中查询对应的数据，返回到MySQL服务端，然后MySQL服务端基于lastname LIKE '%etrunia%'和address LIKE '%Main Street%'来判断数据是否符合条件。如果使用了索引下推技术，则MYSQL首先会返回符合zipcode='95054’的索引，然后根据lastname LIKE '%etrunia%'和address LIKE '%MainStreet%'来判断索引是否符合条件。如果符合条件，则根据该索引来定位对应的数据，如果不符合，则直接reject掉。有了索引下推优化，可以在有like条件查询的情况下，减少回表次数。