Mysql索引

索引

索引是一种高效获取数据的存储结构，例：hash、 二叉树、 红黑树。

1、索引类型

索引的类型由建立索引的字段内容特性来决定，通常normal最常见。

Normal：普通索引
Unique：唯一索引，不允许重复的索引，该字段信息保证不会重复（例如：身份证号用作索引时，可设置为unique）
Full Text: 全文搜索的索引。 Full Text用于搜索很长一篇文章的时候，效果最好。用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。

创建索引与索引类型的原则

在字段上创建索引和创建什么类型的索引的原则：

选择唯一性索引
为经常需要排序、分组和联合操作的字段建立索引
为常作为查询条件的字段建立索引
限制索引的数目
尽量使用数据量少的索引
尽量使用前缀来索引
删除不再使用或者很少使用的索引

2、索引方法（数据结构）

Mysql索引方法有：B+Tree和Hash
InnoDB存储引擎默认索引方法为B+Tree。

B+Tree

1、B+Tree是一种多路平衡查询树，它的节点是天然有序的（左子节点小于父节点、父节点小于右子节点），故对于范围查询的时候效率高，不需要做全表扫描。

Hash

1、哈希索引适合等值查询，但是无法进行范围查询 ；

原因：Hash索引底层是哈希表，哈希表是一种以key-value存储数据的结构，所以多个数据在存储关系上是完全没有任何顺序关系的。对于区间查询是无法直接通过索引查询的，需要全表扫描。所以，哈希索引只适用于等值查询的场景。）

2、哈希索引没办法利用索引完成排序 ；
3、哈希索引不支持多列联合索引的最左匹配规则；
4、如果有大量重复键值的情况下，哈希索引的效率会很低（因为存在哈希碰撞问题）；

3、聚簇索引、非聚簇索引

在 InnoDB 里，索引B+ Tree的叶子节点存储了整行数据的是主键索引，也称为聚簇索引。而索引B+ Tree的叶子节点存储了主键的值的是非主键索引，也称为非聚簇索引。

聚簇索引

1、聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的即为整张表的记录数据；
2、聚簇索引的叶子节点称为数据页，聚簇索引的这个特性决定了索引组织表中的数据也是索引的一部分；

非聚簇索引

叶子节点=键值+书签。Innodb存储引擎的书签就是相应行数据的主键索引值。

比较

1、聚簇索引查询更快。（原因：主键索引树的叶子节点直接就是我们要查询的整行数据。而非主键索引的叶子节点是主键的值，查到主键的值以后，还需要再通过主键的值再进行一次查询（这个过程称为：回表））

4、覆盖索引

非主键索引查询时都需要进行回表的说法是不准确的，因为存在一种情况，即使是非主键索引，也不需要回表，这种情况就是覆盖索引。

解释

查询的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。

例：表covering_table中有一个普通索引 idx_key1_key2(key1,key2)
当通过SQL语句：select key2 from covering_table where key1 = 'keytest'; 查询时，就可以通过覆盖索引查询，无需回表

如何实现

常见的方法是：将被查询的字段，建立到联合索引里去。

create table user (

id bigint primary key auto_increment,	# 主键自增、不为空、Long类型

name varchar(20),

age tinyint(4),  # tinyint 表示的范围是0-255之间的数字

index(name, age )	# 为name字段创建索引，InnoDB存储引擎默认索引方法为B+Tree

)engine=innodb	# 存储引擎为InnoDB

SELECT age FROM user WHERE name = 'name_test';

此时SELECT语句，不需要回表

5、查询优化器

在一条单表查询语句真正执行之前，MySQL的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案。

这种情况下，可能会出现，创建了索引，但查询时，未走索引的情况

优化过程（大致）

1、计算全表扫描的代价;
2、根据搜索条件，找出所有可能使用的索引;
3、计算使用不同索引执行查询的代价;
4、对比各种执行方案，找出代价最低的那一个;

补充：

Mysql查询使用like时，即使创建了索引，但也可能会出现不走索引的情况，具体走不走索引，需要看%的位置。
表：

索引：

%在前：不走索引

EXPLAIN
SELECT sex FROM user WHERE name LIKE '%四';

%在两端：不走索引

EXPLAIN
SELECT sex FROM user WHERE name LIKE '%二%';

%在后：走索引

EXPLAIN
SELECT sex FROM user WHERE name LIKE '李%';

联合索引（最左匹配原则）

MySQL索引查询会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

解释：

当创建一个联合索引的时候，如(a, b, c)，相当于创建了（a）、(a, b)和(a, b, c)三个索引，这就是最左匹配原则。

注意：

where子句中使用最频繁的一列放在最左边；
带头大哥（a）不可少；
中间的（b）不可少；

索引下推

MySQL 5.6引入了索引下推优化，默认开启，使用SET optimizer_switch = 'index_condition_pushdown=off';可以将其关闭。

例：

user表中（age, name）构成一个索引

SELECT * FROM user WHERE age=2 AND name LIKE '%二%' AND address LIKE '%地址%';

未使用索引下推：

如果没有使用索引下推技术，MySQL会通过age=2从存储引擎中查询对应的数据，返回到MySQL服务端，然后MySQL服务端基于name LIKE ‘%二%’ 和 address LIKE ‘%地址%’ 来判断数据是否符合条件。

使用索引下推：

如果使用了索引下推技术，则MYSQL会首先返回符合age=2的索引，然后根据name LIKE ‘%二%’ 和 address LIKE ‘%地址%’ 来判断索引是否符合条件。如果符合条件，则根据该索引来定位对应的数据，如果不符合，则拒收。

作用：

索引下推优化，可以在有like条件查询的情况下，减少回表次数。