Mysql的索引总结及索引ABC问题

最新推荐文章于 2023-05-24 17:35:32 发布

message丶小和尚

最新推荐文章于 2023-05-24 17:35:32 发布

阅读量3.7k

点赞数 3

分类专栏：工作总结文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/qq_38254635/article/details/105793428

版权

工作总结专栏收录该内容

50 篇文章 7 订阅

订阅专栏

一 : 索引种类

1 : 单列索引

即以表的单列字段创建的索引。

2 : 聚合索引

即以表的多列字段创建的索引，该索引在使用时，从左字段顺序才会生效，遵循最左匹配原则。

3 : 普通索引

即不是主键，又不是唯一的索引。

4 : 主键索引

表主键自动生成的索引，即在定义表结构是设置的PRIMARY KEY所生成的索引。
如果表未定义主键，则查找该表中是否存在非空、整形、唯一索引作为其主键。
主键可通过`select _rowid from table名称` 查看。
若以上都不满足，mysql会生成一个rowid作为主键，无法直接查到。

5 : 唯一索引

表的唯一列生成的索引，可为空值。

6 : 全文索引

将存储于数据库的字符串中任意内容查找出来，如大量级的文字中如like %关键字%
普通索引的效率与全文索引相比是非常低的。

二 : 索引方法

在这里插入图片描述

1 : BTREE索引

mysql数据库最常用的索引算法。
因为它不仅可以被用在=，>，>=，<，<=和between这些比较操作符上，而且还可以用于like操作符。
只要它的查询条件不是一个以通配符开头的常量。

2 : HASH索引

Hash索引仅仅能满足“=”，“IN”，“<=>”查询，不能使用范围查询。
联合索引中，Hash索引不能利用部分索引键查询。
Hash索引遇到大量Hash值相等的情况后性能并不一定会比BTree高。

三 : 引擎类型

在这里插入图片描述

1 : Innodb

以上图为例，test表使用InnoDB引擎，生成两个文件。
test.ibd 存储表索引及表数据。
test.frm 存储表结构信息。

2 : MyISAM

以上图为例，test表使用MyISAM引擎，生成三个文件。
testa.MYI 存储表索引，文件名后缀的 I 即为index索引。
testa.MYD 存储表数据，文件名后缀的 D 即为data数据。
testa.frm 存储表结构信息。

四 : 实现原理图

1 : 索引图结构

Mysql索引B+ tree底层实现原理如图：
在这里插入图片描述

2 : 索引图说明

Mysql主键会自动添加索引，innoDB表必须有主键，并且推荐使用整型的自增主键。
B+树，整型是因为B+树需要比较大小，整型比字符串快，占用字节小，节约磁盘空间。自增是因为UUID这种会导致树的分裂，平衡，自增的会往后顺序增
如图所示，18 -> 24 中间有一个箭头，类似于C语言中的指针，当需要取10至40的数据的时候，就可以根据指针框定范围。

五 : 聚合索引 ABC 问题

1 : 聚合索引原则

组合索引的生效原则是
从前往后依次使用生效，如果中间某个索引没有使用，那么断点前面的索引部分起作用，断点后面的索引没有起作用。

在MySQL中，如果创建了复合索引(A, B, C)，就相当于创建了(A, B, C)、(A, B)和(A)三个索引，这被称为复合索弓|前导列特性，因此在创建复合索索引时应该将最常用作查询条件的列放在最左边，依次递减。

2 : 测试表结构

CREATE TABLE `TEST` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `A` varchar(30) COLLATE utf8_bin DEFAULT NULL,
  `B` varchar(30) COLLATE utf8_bin DEFAULT NULL,
  `C` varchar(30) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`ID`) USING BTREE,
  KEY `TEST_INDEX` (`A`,`B`,`C`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

3 : 聚合索引组合顺序

 A B C 组合顺序如下
 
 A
 A B
 A C
 A B C
 A C B
 
 B
 B A
 B C
 B A C
 B C A
 
 C
 C A
 C B
 C A B
 C B A

4 : 聚合索引测试结果

测试结果如下所示 Y：是，N：否

EXPLAIN select * from test t where A = 1; -- Y
EXPLAIN select * from test t where A = 1 and B = 1; -- Y
EXPLAIN select * from test t where A = 1 and C = 1; -- Y
EXPLAIN select * from test t where A = 1 and B = 1 and C = 1; -- Y
EXPLAIN select * from test t where A = 1 and C = 1 and B = 1; -- Y

EXPLAIN select * from test t where B = 1; -- N
EXPLAIN select * from test t where B = 1 and A = 1; -- Y
EXPLAIN select * from test t where B = 1 and C = 1; -- N
EXPLAIN select * from test t where B = 1 and A = 1 and C = 1; -- Y
EXPLAIN select * from test t where B = 1 and C = 1 and A = 1; -- Y

EXPLAIN select * from test t where C = 1; -- N
EXPLAIN select * from test t where C = 1 and A = 1; -- Y
EXPLAIN select * from test t where C = 1 and B = 1; -- N
EXPLAIN select * from test t where C = 1 and A = 1 and B = 1; -- Y
EXPLAIN select * from test t where C = 1 and B = 1 and A = 1; -- Y

-- 其余查询方式
EXPLAIN select * from test where A > 3; -- Y
EXPLAIN select * from test where A = 3 order by B; -- Y
EXPLAIN select * from test where A = 3 order by C; -- Y
EXPLAIN select * from test where C = 3 order by A; -- N

是否走索引的标志

5 : 聚合索引总结

Mysql表结构以test表为例，通过测试，得出结论：
1.查询的条件，Mysql会根据索引排序。
2.由于聚合索引(A, B, C)，就相当于创建了(A, B, C)、(A, B)和(A)三个索引，所以只要条件中有(A, B, C)、(A, B)或(A)，就会走索引。
3.建议建表测试，更加直观。