手机版 欢迎访问it开发者社区(www.mfbz.cn)网站

当前位置: > 开发

【MYSQL】索引详解

时间:2021/6/5 23:02:25|来源:|点击: 次

索引是一种排好序的数据结构,帮助MySQL提高查询效率

  • 查看索引
  • 创建索引
  • 删除索引
  • 索引类型
  • 索引方法
  • 聚簇索引
  • 组合索引
  • 索引失效
  • 索引覆盖
  • 索引下推(ICP)
  • 强制索引

查看索引

SHOW INDEX FROM `table_name`;

创建索引

-- create index 必须声明索引名,而alter table未声明索引名则会以第一个索引字段命名
-- create index 无法创建主键索引,并且一次只能创建一个索引
CREATE INDEX `index_name` ON `table_name` (`column`);
-- 创建主键索引
ALTER TABLE `table_name` ADD PRIMARY KEY (`column`);
-- 创建唯一索引 KEY|INDEX 没有本质区别,只不过如果是创建主键索引只能用PRIMARY KEY
ALTER TABLE `table_name` ADD UNIQUE KEY (`column`);
ALTER TABLE `table_name` ADD UNIQUE INDEX (`column`);
-- 创建组合索引
ALTER TABLE `table_name` ADD INDEX `index_name` (`column`, `column2`);
-- 一次创建多个索引
ALTER TABLE `table_name` ADD INDEX `index_name` (`column`), ADD INDEX `index_name` (`column2`);

删除索引

-- 根据索引名删除
ALTER TABLE `table_name` DROP INDEX `index_name`;
-- 删除主键
ALTER TABLE `table_name` DROP PRIMARY KEY;

索引类型

主键索引

普通索引(Normal):只包含单列的索引

唯一索引(Unique):索引列值必须唯一,能且仅能存在一个null

组合索引:多列构成的索引

全文索引(Full Text):作用于CHAR、VARCHAR、TEXT类型的字段上,5.7版本以前InnoDB不支持

索引方法

B+TREE 索引

要讲B+Tree首先需要认识下B-Tree(多叉平衡查找树)

B-Tree

在这里插入图片描述

特征:

每页能存储多个索引键

索引键分布在整颗树中,任何一个键值出现且仅出现在一个结点中

每个结点中都存储大量信息(键值、指针、data数据)

搜索有可能在非叶子结点中结束

B+Tree

B+Tree是在B-Tree基础上的一种优化

在这里插入图片描述

于B-Tree主要的区别:

  • 非叶子结点的子树指针与关键字个数相同
  • 非叶子结点不存储data数据,只存储索引和指针(每页能存储更多的键)
  • 叶子结点包含所有索引字段和data数据(每次都需要查询到叶子结点,相对稳定)
  • 叶子结点间用双向指针连接,提高区间访问的性能

假设索引树高为3层,一行记录占512b,大概可以存储多少条记录?

对于InnoDB的页默认大小是16KB,非叶子结点只存储索引键和指针

假设主键索引是bigint类型占8b,一个指针占6b,那么一个结点就是存储 16KB * 1024 / (8b + 6b) = 1170个键值

树高为3,则二层就会有 1170 * 1170 = 136w个键值,也就是说会有136w个叶子结点

前面说了一页是16KB,一行记录占512b,那么叶子结点一页可以存储16KB * 1024 / 512 = 32 条记录

也就是说三层树可以记录的总条数约为 136w * 32 = 4380w条记录

对于三层树来讲,通常需要两次I/O操作,因为顶层页常驻内存

HASH 索引

哈希索引是通过哈希表实现的,通过索引所包含的列计算哈希码,如果遇到哈希冲突则通过链表解决

在这里插入图片描述

Mermory默认使用的Hash索引,InnoDB不支持HASH索引,但是对于频繁访问的表,InnoDB会建立自适应HASH索引

哈希索引存在以下几点问题

  • 由于哈希索引比较的是计算后的哈希值,所以只能作用于等值查找(=、in、<=>),不支持范围查找
  • 无法避免再次的排序操作,hash索引是无序的,无法直接通过索引排序,需要查找出记录后再次排序
  • 对于组合索引,必须使用所有索引列查询,因为在计算哈希时,是通过所有索引列计算的,无法使用最左前缀原则
  • 无法使用覆盖索引,因为索引中只包含哈希值和行指针
  • 无法避免表扫描,因为不同的索引键存在相同的哈希值,还需要表中的实际数据进行比较,当哈希冲突严重时,效率不一定高

聚簇索引

聚簇索引:将数据的存储和索引放在一起,索引结构的叶子节点保存了行数据

通常来讲主键索引都是聚簇索引,叶子节点存储了所有行数据

非聚簇索引:将数据与索引分开存储,索引结构的叶子结点指向了数据对应的位置

通常来讲除了主键索引外的其他索引都是非聚簇索引,非聚簇索引存储的是主键值,非聚簇索引访问数据通常需要二次查找

先通过非聚簇索引查找到对应的主键,然后回到聚簇索引通过主键去查询记录(回表)

为什么InnoDB建议必须要创建主键?

如果我们定义了主键那么InnoDB会选择主键进行聚簇索引,如果没有会选择第一个不包含空值的唯一索引作为聚簇索引,如果还是没有那么MySQL就会内置一个ROWID作为聚簇索引,代价比较高

为什么推荐整型的自增主键?

如果主键是自增的,那么每次插入记录都会以顺序添加到当前索引结点后续的位置,如果不够存储则开辟新页。

如果主键不是自增的,那么每次插入大概率是中间的位置,从而导致移动数据,甚至引起B+数结构的变化发生自平衡。影响数据写入的性能

整型的数据所占空间比较小,能使每一页存放更多的Key,并且在做比较的时候也比较快。

MyISAM的B+Tree聚簇索引叶子结点存储的是主键,非聚簇索引存储的是辅助键,表数据是单独存储的,两颗树通过一个地址指向真正的表数据,所以对于非聚簇索引来讲无需回表即可访问数据

组合索引

DDL

# 该ddl用于测试组合索引/索引失效/索引覆盖用例
CREATE TABLE `t_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` varchar(32) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  `e` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_ABC` (`a`,`b`,`c`),
  KEY `IDX_AE` (`a`,`e`)
) ENGINE=InnoDB;

最左前缀原则和查询优化

# 走索引 a_b_c
EXPLAIN SELECT * FROM t_test WHERE a = 'test' AND b = 123 AND c = 0;
# mysql查询优化器会纠正这条查询语句以什么样的顺序执行效率最高,然后才生成真正的执行计划
# 走索引 a_b_c
EXPLAIN SELECT * FROM t_test WHERE a = 'test' AND c = 0 AND b = 123;
# 走索引,a_b_c 
EXPLAIN SELECT * FROM t_test WHERE c = 0 AND b = 123 AND a = 'test';
# 走索引,a_b
EXPLAIN SELECT * FROM t_test WHERE a = 'test' AND b = 123;
# 走索引,a
EXPLAIN SELECT * FROM t_test WHERE a = 'test' AND c = 0;
# 不走索引,违背了最左前缀原则
EXPLAIN SELECT * FROM t_test WHERE b = 123 AND c = 0; 
# 走索引, a_b 遇到范围查询,后面的索引会失效, 这里也就是c失效
EXPLAIN SELECT * FROM t_test WHERE a = 'test' AND b > 123 AND c = 0;
# 走索引, a_b_c 可以看成先排序在判断范围查询失效的情况
EXPLAIN SELECT * FROM t_test WHERE c > 0 AND b = 123 AND a = 'test';
# 走索引, a_b
EXPLAIN SELECT * FROM t_test WHERE c = 0 AND b > 123 AND a = 'test';

联合索引abc

最左前缀原则,从左到右,如果断了,则后面的索引字段不生效

如果where后面只有aa走索引

如果where后面是abab走索引

如果where后面是ac则只有a走索引,b断了所以c不生效

如果where后面是bc则不走索引,a断了所以bc不生效

联合索引在where后的顺序可以打乱

例如:abc acb cab cba bac bca ,mysql都会优化成abc

遇到范围判断,如< > 会导致后面的索引失效

这个优先级较低,mysql会先优化顺序再判断,例如:c > 0 and b = 123 and a = 'cc' ,索引abc还是生效

经测试组合索引中如果不符合最左前缀原则,但是符合覆盖索引,还是能走到index索引(原理待考究)

索引失效

通过or连接会导致索引失效(只要or左右两边存在一个非索引字段)

EXPLAIN SELECT * FROM t_test WHERE a = 'test' OR b = 123 or c = 0;

前百分号%会导致索引失效

EXPLAIN SELECT * FROM t_test WHERE a LIKE '%test';

类型转换会导致索引失效

当同时存在数值类型和字符类型时,两边都会转换为浮点数进行比较,而字符串(左边字段)转为浮点数是不确定的

详见:MySQL官方文档-type-conversion

EXPLAIN SELECT * FROM t_test WHERE a = 1; // a (varchar)   索引失效
EXPLAIN SELECT * FROM t_test WHERE a = '1'; // a (int)   索引生效
EXPLAIN SELECT * FROM t_test WHERE a IN (1, '2'); // a (varchar)   索引失效
EXPLAIN SELECT * FROM t_test WHERE a IN (1, '2'); // a (int)   索引生效

例:

a为varchar类型,两边类型不匹配发生隐式类型转换,字符串100a、100b都转为浮点数都是100

在这里插入图片描述

where条件中的索引列有参与运算,b+1导致b失效

EXPLAIN SELECT * FROM t_test WHERE a = 'test' AND b+1 > 0; 

where条件中的索引列使用了函数

EXPLAIN SELECT * FROM t_test WHERE LOWER(a) = 'test'

如果MySQL觉得用全表扫描更快,会导致不走索引

in 、not in、!=、<> 、is null、is not null 是走索引的,之所以有不走索引的说法,是因为回表的数据太多,MySQL认为索引扫描的成本大于全表扫描,才导致的索引失效。

使用覆盖索引,把*改成a则不会造成回表,必走索引

EXPLAIN SELECT * FROM t_test WHERE a IS NULL;

测试证明 in 、not in、!=、<> 、is null、is not null都是会走索引的,索引类型为range

索引覆盖

只通过查询辅助索引树就能得到所要查询的列数据,即select列的数据只用从索引中就能够取得

# count非索引列会全表扫描,可以count索引使用索引覆盖,count(0)如果该表存在索引mysql会自动找一列索引
EXPLAIN SELECT COUNT(d) FROM t_test
# 查询列做组合索引,避免回表
EXPLAIN SELECT a,e FROM t_test WHERE a = 'test'

索引下推(ICP)

这里先同步一个概念,对于索引的范围查询,MySQL是一次性将所有符合条件的非聚簇索引都取出来后再进行统一的回表操作,还是匹配到一条就进行一次回表?

  1. 其实是匹配到一条记录后就立即回表,然后将完整的记录返回给service层
  2. server层接收到引擎层返回的记录后,接着判断其余WHER条件是否成立,成立的话就发送给客户端
  3. 接着server层向存储引擎请求读取下一条记录
  4. 周而复始,直到引擎层遇到了不符合的记录,向server层返回读取完毕的信息,server查询完毕
  5. 客户端收到所有数据后进行展示

了解了引擎层和服务层是怎么交互了之后,再来看下索引下推

# ALTER TABLE t_user ADD INDEX IDX_NAME_AGE (name, age);
EXPLAIN SELECT * FROM t_user WHERE name LIKE '陈%' AND age = 18;

where条件中的索引使用分为了三类:

index key 使用范围索引来确定扫描的范围(range)

index filter 使用index key确定范围之后,如果还有不符合的条件,如果这些条件可以通过索引进行过滤

table filter where中的条件不能通过索引过滤,只能访问table

MySQL5.6之前并不区分index filtertable filter,统一将范围索引回表查找完整记录然后返回给MySQL Server层过滤,MySQL5.6之后,index filtertable filter分离,index filter 下降到索引层进行过滤,减少了回表与返回MySQL Server层的记录交互开销,这也就是所谓的索引下推(ICP)

索引下推只发生在非聚簇索引中,并且使用索引字段的个数不影响索引下推的优化

详见:MySQL官方文档-index-condition-pushdown-optimization

未使用索引下推:

在这里插入图片描述

使用索引下推:

在这里插入图片描述

强制索引

可以使用FORCE INDEXUSE INDEX 强制指定索引或者使用IGNORE INDEX忽略指定索引

EXPLAIN SELECT * FROM t_test FORCE INDEX (IDX_ABC) WHERE a = 'test';
EXPLAIN SELECT * FROM t_test USE INDEX (IDX_ABC) WHERE a = 'test';
EXPLAIN SELECT * FROM t_test IGNORE INDEX (IDX_ABC) WHERE a = 'test';

Copyright © 2002-2019 某某自媒体运营 版权所有