Mysql-索引

索引

我的理解索引其实就是一种可以加快数据访问速度的一种技术，它的本质一般就是空间换时间。
何时使用索引？
这个需要根据业务情况和实际测试来进行决断，如果你的数据量非常非常小完全没必要用索引，因为插入数据都需要去维护索引，在数据量小的情况下索引查询有可能还不如全表扫描快。

索引的类型

1 单列索引

• 主键索引：索引列中的值必须是唯一的不允许有空值。

ALTER TABLE table_name ADD PRIMARY KEY (column_name);

• 普通索引:MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值。

ALTER TABLE table_name ADD INDEX index_name (column_name);

• 唯一索引：索引列中的值必须是唯一的，但是允许为空值。

CREATE UNIQUE INDEX index_name ON table(column_name);

• 全文索引：只能在文本类型CHAR，VARCHAR，TEXT类型字段上创建全文索引。字段长度比较大
  时，如果创建普通索引，在进行like模糊查询时效率比较低，这时可以创建全文索引。MyISAM和
  InnoDB中都可以使用全文索引。

#创建表时，创建全文索引
CREATE TABLE `t_fulltext` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`content` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#创建全文索引
ALTER TABLE `t_fulltext` ADD FULLTEXT INDEX `idx_content`(`content`);

• 空间索引：MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型。MySQL在
  空间索引这方面遵循OpenGIS几何数据模型规则。
  空间模型
• 前缀索引：在文本类型如CHAR，VARCHAR，TEXT类列上创建索引时，可以指定索引列的长度，
  但是数值类型不能指定。

ALTER TABLE table_name ADD INDEX index_name (column1(length));

2 组合索引

ALTER TABLE table_name ADD INDEX index_name (column1,column2);

索引的数据结构

1 hash表
Hash 是K-V存储，在查询非常的有优势，时间复杂度为O(1)。
但是存在一些瓶颈：

• 但是不支持范围快速查找，范围查找时还是只能通过扫描全表方式。
• 数据结构比较稀疏，不适合做聚合，不适合做范围等查找。

2 二叉树
二叉树特点：每个节点最多有2个分叉，左子树和右子树数据顺序左小右大。时间复杂度最好的情况下为O(logN)
但是对数据的分布要求严格，在某些情况下二叉树可能会退化为单链表，那其实也就是相当于全表扫描。
3 红黑树
其实在红黑树之前应该还有一个平衡二叉树，红黑树的本质也是平衡二叉树，只不过通过红黑染色的方法极大增强了性能。红黑树能够以O(log2 n)的时间复杂度进行搜索、插入、删除操作。
它也存在一定瓶颈：

• 时间复杂度和树的高度有关，每个节点的读取都会有一次IO（IO往往是非常慢的特别是如果是随机读写）。
• 不支持范围快速查找，范围查询时候需要从根节点多次遍历，查询效率比较差。

4 B 树
多叉树降低了树的高度，减少了磁盘IO
1） B树的节点中存储着多个元素，每个节点内有多个分叉。
2） 节点中的元素包含键值和数据，节点中的键值从大到小排列。也就是说，在所有的节点都储存数据。
3） 父节点当中的元素不会出现在子节点中。
4） 所有的叶子结点都位于同一层，叶节点具有相同的深度，叶节点之间没有指针连接。

优点：
磁盘IO次数会大大减少。
比较是在内存中进行的，比较的耗时可以忽略不计。
B树的高度相比于平衡二叉树会大幅缩小，所以使用B树构建索引可以很好的提升查询的效率。
缺点：
B树不支持范围查询的快速查找：如果我们想要查找15和26之间的数据，查找到15之后，需要回到根节点重新遍历查找，需要从根节点进行多次遍历，查询效率有待提高。
空间占用较大：如果data存储的是行记录，行的大小随着列数的增多，所占空间会变大。一个页中可存储的数据量就会变少，树相应就会变高，磁盘IO次数就会变大。

5 B+树
主角终于登场了，Mysql就是选用的这种结构。
B+树：只有叶子节点才会存储数据，非叶子节点只存储键值。叶子节点之间使用双向指针连接，最
底层的叶子节点形成了一个双向有序链表（从而支持了范围查询）。

等值查询：假如我们查询值等于15的数据。查询路径磁盘块1->磁盘块2->磁盘块5。
范围查询：假如我们想要查找15和26之间的数据。

• 查找路径是磁盘块1->磁盘块2->磁盘块5。
• 首先查找值等于15的数据，将值等于15的数据缓存到结果集。
• 查找到15之后，底层的叶子节点是一个有序列表，我们从磁盘块5，键值15开始向后遍历筛选所有符合筛选条件的数据。
• 根据磁盘5后继指针到磁盘中寻址定位到磁盘块6，将磁盘6加载到内存中，在内存中从头遍历比较，15<17<26，15<26<=26，将data缓存到结果集。

MySq中的索引类型

MyISAM索引

1 主键索引（非必须，但是在InnoDB中是必须的）
表t_user_myisam的索引存储在索引文件t_user_myisam.MYI中，数据文件存储在数据文件t_user_myisam.MYD中。
2 辅助索引
在 MyISAM 中，辅助索引和主键索引的结构是一样的，没有任何区别，叶子节点的数据存储的都是行记录的磁盘地址。只是主键索引的键值是唯一的，而辅助索引的键值可以重复。

InnoDB索引

后面的举例都是基于这个这个表

CREATE TABLE `t_user_innodb` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(20) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
KEY `idx_age` (`age`) USING BTREE
) ENGINE=InnoDB;
insert into t_user_innodb values(15,'Nick',5);
insert into t_user_innodb values(18,'Zero',22);
insert into t_user_innodb values(20,'Tom',34);
insert into t_user_innodb values(30,'Nick',55);
insert into t_user_innodb values(49,'Mary',22);
insert into t_user_innodb values(50,'James',77);
insert into t_user_innodb values(56,'John',89);
insert into t_user_innodb values(77,'Lily',100);

1 主键索引（InnoDB中为又为聚簇索引，因为索引和数据放到一起）
主键索引的叶子节点会存储数据行，辅助索引只会存储主键值。
nnoDB创建索引的具体规则如下：

• 在表上定义主键PRIMARY KEY，InnoDB将主键索引用作聚簇索引。
• 如果表没有定义主键，InnoDB会选择第一个不为NULL的唯一索引列用作聚簇索引。
• 如果以上两个都没有，InnoDB 会使用一个6 字节长整型的隐式字段 ROWID字段构建聚簇索引。该ROWID字段会在插入新行时自动递增。
  
  1）等值查询
• 先在主键树中从根节点开始检索，将根节点加载到内存，比较30<56，走左路。（1次磁盘IO）

select * from t_user_innodb where id=30;

• 将左子树节点加载到内存中，比较20<30<49，向下检索。（1次磁IO）
• 检索到叶节点，将节点加载到内存中遍历，比较20<30，30=30。查找到值等于30的索引项，直接可以获取整行数据。将改记录返回给客户端。（1次磁盘IO）
  IO 总次数为3次
  

2）范围查询

select * from t_user_innodb where id between 30 and 49;

• 先在主键树中从根节点开始检索，将根节点加载到内存，比较30<56，左路。（1次磁盘IO）
  将左子树节点加载到内存中，比较20<30<49，向下检索。（1次磁盘IO）
• 检索到叶节点，将节点加载到内存中遍历比较20<30，30<=30<49。查找到值等于30的索引项。获取行数据缓存到结果集中。（1次磁盘IO）
• 向后遍历底层叶子链表，将下一个节点加载到内存中，遍历比较30<49<=49，获取行数据缓存到结果集中。（1次磁盘IO）
• 最后得到2条符合筛选条件，将查询结果集返给客户端。

可以看到，因为在主键索引中直接存储了行数据，所以InnoDB在使用主键查询时可以快速获取行数据。
当表很大时，与在索引树中存储磁盘地址的方式相比，因为不用再去磁盘中获取数据，所以聚簇索引通常可以节省磁盘IO操作。

2 辅助索引
除聚簇索引之外的所有索引都称为辅助索引，InnoDB的辅助索引只会存储主键值而非磁盘地址。
使用辅助索引需要检索两遍索引：

• 首先检索辅助索引获得主键
• 然后使用主键到主索引中检索获得记录。
  我们在Age上建立辅助索引，我们可以看到叶子节点存放的是主键ID和Age的数据。
  
  1）等值查询

select * from t_user_innodb where age=22;

• 先在索引树中从根节点开始检索，将根节点加载到内存，比较22<77，走左路。（1次磁盘IO）
• 将左子树节点加载到内存中，比较22<34，向下检索。（1次磁盘IO）
• 检索到叶节点，将节点加载到内存中从前往后遍历比较。（1次磁盘IO）
  第一项5：5<22不符合要求，丢弃。
  第二项22：等于22，符合要求，获取主键id=18，去主键索引树中检索id=18的数据放入结果集中。（回表查：3次磁盘IO）。
  第三项22：等于22，符合要求，获取主键id=49，去主键索引树中检id=49的数据放入结果集中。（回表查：3次磁盘IO）
• 向后遍历底层叶子链表，将下一个节点加载到内存中，遍历比较。（1次磁盘IO）
  第一项34：34>22不符合要求，丢弃。查询结束。
• 最后得到2条符合筛选条件，将查询结果集返给客户端。

2）什么是回表查询？
根据在辅助索引树中获取的主键id，到主键索引树检索数据的过程称为回表查询。

3）范围查询

select * from t_user_innodb where age between 30 and 49;

• 辅助索引的范围查询流程和等值查询基本一致，先使用辅助索引到叶子节点检索到第一个符合条件的索引项，然后向后遍历，直到遇到第一个不符合条件的索引项，终止。
• 检索过程中需要将符合筛选条件的id值，依次到主键索引检索将检索的数据放入结果集中。
• 最后将查询结果返回客户端。

3 组合索引
表t_multiple_index，id为主键列，创建了一个联合索引idx_abc(a,b,c)，构建的B+树索引结构如图所示。索引树中节点中的索引项按照（a，b，c）的顺序从大到小排列，先按照a列排序，a列相同时按照b列排序，b列相同按照c列排序。在最底层的叶子节点中，如果两个索引项的a，b，c三列都相同，索引项按照主键id排序类似于基数排序。

CREATE TABLE `t_multiple_index` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` varchar(10) DEFAULT NULL,
`d` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
KEY `idx_abc` (`a`,`b`,`c`)
) ENGINE=InnoDB;
insert into t_multiple_index (a,b,c,id,d) values(1 ,1 ,4,5,'dll');
insert into t_multiple_index (a,b,c,id,d) values(1 ,5 ,4,2,'doc');
insert into t_multiple_index (a,b,c,id,d) values(5 ,3 ,6,7,'img');
insert into t_multiple_index (a,b,c,id,d) values(13,14,3,4,'xml');
insert into t_multiple_index (a,b,c,id,d) values(13,16,4,1,'txt');
insert into t_multiple_index (a,b,c,id,d) values(13,16,5,3,'pdf');
insert into t_multiple_index (a,b,c,id,d) values(13,16,5,6,'exe');
insert into t_multiple_index (a,b,c,id,d) values(14,14,14,8,'ddd');

1. 查询方式

select * from t_multiple_index where a=13 and b=16 and c=4;

• 先在索引树中从根节点开始检索，将根节点加载到内存，先比较a列，a=14，14>13，走左路。（1次磁盘IO）
• 将左子树节点加载到内存中，先比较a列，a=13，比较b列b=16，14<16，走右路，向下检索。（1次磁盘IO）
• 达到叶节点，将节点加载到内存中从前往后遍历比较。（1次磁盘IO）
  第一项（13,14,3,id=4）：先比较a列，a=13，比较b列b=14，b!=16不符合要求，丢弃。
  第二项（13,14,4,id=1）：一样的比较方式，a=13，b=16，c=4 满足筛选条件。取出索引
  data值即主键id=1，再去主键索引树中检索id=1的数据放入结果集中。（回表：3次磁盘IO）
  第三项（13,14,5,id=3）：a=13，b=16，c!=4 不符合要求，丢弃。查询结束。
  最后得到1条符合筛选条件，将查询结果集返给客户端。
  
  如果我们有一些字段频繁的要放在一起进行查询组合索引是一个非常好的选择，比起单个索引来说降低了存储空间，时间上也比分别查询多个辅助索引要快。同时频繁出现在order by和group by语句中的列，建议按照顺序去创建组合索引（因为存在最左前缀匹配）。
  2 ) 最左前缀匹配原则
  组合索引的最左前缀匹配原则：使用组合索引查询时，mysql会一直向右匹配直至遇到范围查询(>、<、between、like)就停止匹配。
• 最左前缀匹配原则和联合索引的索引存储结构和检索方式是有关系的。
• 在组合索引树中，最底层的叶子节点按照第一列a列从左到右递增排列，但是b列和c列是无序的，b列只有在a列值相等的情况下小范围内递增有序，而c列只能在a，b两列相等的情况下小范围内递增有序。
• 所以当我们使用 where a=13 and b=16 and c=4去查询数据的时候，B+树会先比较a列来确定下一步应该搜索的方向，往左还是往右。如果a列相同再比较b列。但是如果查询条件没有a列，B+树就不知道第一步应该从哪个节点查起。
  所以联合索引只能从第一列开始查找，比如以下三个查询都可以使用idx_abc索引树，检索数据。

select * from t_multiple_index where a=13;
select * from t_multiple_index where a=13 and b=16;
select * from t_multiple_index where a=13 and b=16 and c=4;
select * from t_multiple_index where a=13 and b>13;
select * from t_multiple_index where a>11 and b=14;
select * from t_multiple_index where a=16 and c=4;

而如果查询条件不包含a列，比如筛选条件只有(b，c)或者c列是无法使用组合索引的。下面的查询没有用到索引。

select * from t_multiple_index where b=16 and c=4;
select * from t_multiple_index where c=4;

4 覆盖索引
覆盖索引主要是为了解决回表的问题，如果我们查的辅助措意或者组合索引已经包含了全部我们要投影的列数据，这个时候其实是没有必要进行回表查询索引的，这个是不需要我们建立的，MySql会做相应的工作。

#这条SQL语句就会走覆盖索引不用回表查询

select a,b from t_multiple_index where a=13 and b=16;
select a,b,c,id from t_multiple_index where a=13 and b=16;

索引条件下推ICP

这个其实是一种优化，Mysql会充分利用Where中的条件，减少减少存储引擎必须访问基表的次数以及MySQL服务器必须访问存储引擎的次数。
可用于 InnoDB 和 MyISAM 表，对于InnoDB表ICP仅用于辅助索引

#关闭ICP
SET optimizer_switch = 'index_condition_pushdown=off';
#开启ICP
SET optimizer_switch = 'index_condition_pushdown=on';

我们来看这样一个例子：

select * from t_multiple_index where a=13 and b>15 and c='5' and d='pdf';

insert into t_multiple_index (a,b,c,id,d) values(1 ,1 ,4,5,'dll');
insert into t_multiple_index (a,b,c,id,d) values(1 ,5 ,4,2,'doc');
insert into t_multiple_index (a,b,c,id,d) values(5 ,3 ,6,7,'img');
insert into t_multiple_index (a,b,c,id,d) values(13,14,3,4,'xml');
insert into t_multiple_index (a,b,c,id,d) values(13,16,4,1,'txt');
insert into t_multiple_index (a,b,c,id,d) values(13,16,5,3,'pdf');
insert into t_multiple_index (a,b,c,id,d) values(13,16,5,6,'exe');
insert into t_multiple_index (a,b,c,id,d) values(14,14,14,8,'ddd');
insert into t_multiple_index (a,b,c,id,d) values(14,14,14,9,'ddd');
insert into t_multiple_index (a,b,c,id,d) values(14,14,14,10,'ddd');

如果不使用索引下推：

1. 执行器使用索引(a,b,c)，筛选条件a=13 and b>=15，调用存储引擎"下一行"接口。根据最左前缀原
   则联合索引检索定位到索引项（13,16,4,id=1），然后使用id=1回表查询，获得id=1的行记录。返
   回给MySQL服务层，MySQL服务层使用剩余条件c=5 and d='pdf’过滤，不符合要求，直接丢弃。
2. 执行器调用"下一行"接口，存储引擎遍历向后找到索引项（13,16,5,id=3）,使用id=3回表获得id=3
   的行记录。返回给MySQL服务层，MySQL服务层使用剩余条件c=5 and d='pdf’过滤，符合要求，
   缓存到结果集。
3. 执行器调用"下一行"接口，存储引擎遍历向后找到索引项（13,16,5,id=6）,使用id=6回表获得id=6
   的行记录。返回给MySQL服务层，MySQL服务层使用剩余条件c=5 and d='pdf’过滤，不符合要
   求，直接丢弃。
4. 执行器调用"下一行"接口，存储引擎遍历向后找到索引项（14,14,14,id=8）不满足筛选条件，执行
   器终止查询。
5. 最终获取一条记录，返回给客户端。
   可以看到，在不使用ICP时，回表查询了3次，然后在服务层筛选后（筛选3次），最后返回客户端。

使用ICP时，具体步骤如下：

1. 执行器使用索引(a,b,c)，筛选条件a=13 and b>=15 and c=5，调用存储引擎"下一行"接口。根据最
   左前缀原则联合索引检索定位到索引项（13,16,4,id=1），然后使用ICP下推条件c=5判断，不满足
   条件，直接丢弃。
2. 向后遍历判断索引项（13,16,5,id=3），满足筛选条件a=13 and b>=15 and c=5，使用id=3回表获
   得id=3的行记录。返回给MySQL服务层，MySQL服务层使用剩余条件d='pdf’过滤，符合要求，缓
   存到结果集。
3. 执行器调用"下一行"接口，存储引擎遍历向后找到索引项（13,16,5,id=6）,满足筛选条件a=13 and
   b>=15 and c=5，使用id=6回表获得id=6的行记录。返回给MySQL服务层，MySQL服务层使用剩
   余条件d='pdf’过滤，不符合要求，直接丢弃。
4. 执行器调用"下一行"接口，存储引擎遍历向后找到索引项（14,14,14,id=8）不满足筛选条件，执行
   器终止查询。
5. 最终获取一条记录，返回给客户端。
   可以看到，在使用ICP时，回表查询了2次，然后在服务层筛选后（筛选2次），最后返回客户端。