什么是索引？

索引是数据库中用来提高数据读取性能的常用工具，所有mysql列类型都可以被索引，对相关列使用索引；

可以是提高select操作性能的最佳途径，可以尽可能快的锁定要查询数据的范围，从而达到加速查询的目的（减少IO消耗）；

一般索引设置都是应用在比较大的数据表上，比如百万级别、千万级别或亿级别的数据表中，从而完成一些针对性优化；

可以简单理解：数据库索引相当于书的目录，可以借助索引有针对的查看相应数据的信息，避免了全盘检索带来的工作量；

主要利用MySQL中的索引，可以快速锁定查询范围，mysql索引比较适合范围查找数据;

索引类型介绍

在MySQL数据库服务中，是有很多种索引类型的，但是比较常用的索引类型主要有：

类型说明
B+Tree默认类型索引
Hash算法类型索引
R+Tree空间类型索引
Fulltext全文类型索引

数据库数据查找算法演变：（B+Tree索引的由来）

这个举个简单的例子，假设现在一个教室中有100来号人，这时可以派发礼品，通过礼品引诱这100人来报名学习xiaoQ老师课程；

比如礼品是学习课程的1000元代金券，现在把这1000元的代金券随机放到了1到100号盒子其中的一个里面，只有我知道放置的号码；

下面要求这100个人尽量快的猜到1~100号盒子里面，哪个有放置代金券的盒子，当然，我会给予一些合适的提示信息；

这时在场的100号人就需要想一些办法，在我合适的配合下，定位有代金券的盒子，想的办法就等价于是查找算法：

方法一：根据定位的盒子编号顺序，询问是与否，这种方式就可以理解为是遍历算法（全扫描），也可以理解为随机性算法；

方法二：根据定位的盒子编号比较，询问大于小，这种方式就可以理解为是二分算法（定范围），也可以理解为二叉树算法；

看似这种二分算法比遍历算法，更加科学，但是如果代金券放在了第01号或第100号盒子里呢，或者二分节点两侧时呢？

所以采用二分法依然会存在数据查询不平衡的问题。

通过以上两种算法的介绍，了解到都存在一些缺陷或问题，因此数据库在检索数据信息时，最终采用的算法是B+Tree，其中的B表示平衡

并且BTREE还可以细分为B-tree或B+tree，以及B++tree，其中的加号就是表示增强版或优化版的BTree；

在讲解B+树之前先了解一下树的整体结构，无非就是二叉树、二叉搜索树、平衡二叉树，更高级一点的有红黑树、Btree、B+tree等；

而树的查找性能取决于树的高度，让树尽可能平衡是为了降低树的高度。

为什么MySQL会选用B+树的结构，可以先来看看其他的树形结构：

二叉树

二叉树的每一个节点都只有两个子节点，当需要向其插入更多的数据的时候，就必须要增加树的高度，而增加树的高度会导致IO消耗大；

对于二叉树而言，它的查找操作的时间复杂度就是树的高度，树的高度越高查询性能就会随着数据的增多越来越低。

二叉树节点中，还存在非正常的倾斜（比如ID自增的情况）的二叉树，查询一次数据就相当于全表搜索，因此二叉树的查询性能特别差

红黑树

红黑树一种平衡二叉树，它复杂的定义和规则都是为了保证树的平衡性；

img

对于B++tree算法的底层算法逻辑理解：

利用Btree算法还是快速锁定100个盒子中，有代金券的盒子编号，如下图所示：

  • 将需要存储的数据信息，均匀分配保存到对应页当中，最终数据信息的均匀存储（落盘）

  • 根据页节点存储的数据信息，取出页节节点最小数据信息，并将每个叶节点最小数据信息进行汇总整合，生成相应内部节点数据；

    实质上存储的是下层页节点的区间范围，以及与之对应的指针信息，最后构建出内部节点信息；

  • 根据内部节点存储的数据信息，取出内部节点最小数据信息，并将每个内部节点最小值信息进行汇总整合，生成相应根节点数据；

    根节点只能有占用一个页区域，如果一个页区域空间不够，需要进行内部节点层次扩展，但是尽量要保证层次越少越好；

    实质上存储的是下层内部节点的区域范围，以及与之对应的指针信息，最后构建出独立且唯一的根节点信息；

  • 整个树形结构，越向上节点存储数据的范围越大，然后依次再分发数据到下面的小范围，最终形成多叉树；

    由于出现了多叉树，就表示全部数据分布在多个链表上，避免了单条链表存储数据，同时可以实现并发的访问数据

  • 对于加号表示增强，其中增强表示在整个链表上，增加了同级相邻节点之间的双向指针，从而实现相邻节点相互跳转

根据以上B+Tree的结构说明，假设现在需要查找54这个数据值信息所在的数据页：等值查询

  • 根据定义查找的数值信息，首先在根节点中获取数值所在的区间范围和相应指针信息，从而找到下层对应的内部节点信息；
  • 根据定义查找的数据信息，其次在枝节点中获取数值所在的区域范围和相应指针信息，从而找到下层对应的叶子节点信息；
  • 根据定义查找的数据信息，最后在叶子节点中获取最终的数据信息，结果结合上图经历三步完成了数据查找（3*16=48kB）；

在利用BTree查找数据信息时，会结合树形层次结构，来决定查询数据的步骤过程，并且理论上每个数据查找过程步骤相同；

总结：B代表的平衡含义就是，每次查找数据消耗的IO数量是一致的，并且读取的页数量也是一致的，查找时间复杂度是一致的；

根据以上B+Tree的结构说明，假设现在需要查找大于90这个数据值信息所在的数据页：不等值查询

  • 根据定义查找的数值信息，首先在根节点中获取首个大于指定数值的区间范围和相应指针信息，从而找到下层对应的内部节点信息；
  • 根据定义查找的数据信息，其次在枝节点中获取数值所在的区域范围和相应指针信息，并且结合双向指针进行预读；
  • 根据定义查找的数据信息，最后在叶子节点中获取最终的数据信息，并且结合双向指针进行预读，查询其余大于90的数值；

在利用BTree查找数据信息时，由于存在双向指针概念，可以避免重复从根查找问题，减少IO消耗，结合预读快速调取数据到内存中

总结：在BTree中的双向链接增强特性和预读功能，可以根据簇（64page）读取数据，可以使数据信息的范围查找变得更加方便高效

索引构建过程

数据库服务在进行BTree索引构建时，是比较重要的知识点，因为最终还是会应用BTree算法知识进行索引的构建，常用方法有：

索引方式一：聚簇索引（集群索引/聚集索引）

聚簇索引主要是：将多个簇（区-64个数据页-1M）聚集在一起就构成了所谓聚簇索引，也可以称之为主键索引；

聚簇索引作用是：用来组织存储表的数据行信息的，也可以理解为数据行信息都是按照聚簇索引结构进行存储的，即按区分配空间的；

聚簇索引的存储：聚簇是多个簇，簇是多个连续数据页(64个)，页是多个连续数据块(4个)，块是多个连续扇区(8个)；

总结：利用聚簇索引可以实现从物理上或逻辑上，都能满足数据存储的连续性关系，方便进行数据查找的有序性IO；（IOT组织表）

聚簇索引的构建方式：

  • 数据表创建时，显示的构建了主键信息（pk），主键（pk）就是聚簇索引；
  • 数据表创建时，没有显示的构建主键信息时，会将第一个不为空的UK的列做为聚簇索引；
  • 数据表创建时，以上条件都不符合时，生成一个6字节的隐藏列作为聚簇索引；

结合下图信息，可以看出聚簇索引组织存储数据过程与加速查询过程原理：

以上图信息为例，若显示创建ID列为pk自增列：

① 按照ID逻辑顺序，在同一个区中连续的数据页上，有序存储数据行；

② 数据行所在的数据页，作为聚簇索引的叶子节点（叶子节点就是所有数据行）；

③ 叶子节点构建完后，可以构建no-left（支节点），用于保存的是leaf节点中的ID范围和指针信息；

④ 支节点构建完后，可以构建root（根节点），用于保存的是no-leaf节点中的ID范围和指针信息；

⑤ 并且leaf节点和no-leaf相邻数据页之间都具有双向指针，从而加速数据的范围查找；

索引方式二：辅助索引

辅助索引主要是：主要用于辅助聚簇索引查询的索引，一般按照业务查找条件，建立合理的索引信息，也可以称之为一般索引；

辅助索引作用是：主要是将需要查询的列信息可以和聚合索引信息建立有效的关联，从而使数据查询过程更高效，节省IO和CPU消耗

辅助索引的存储：调取需要建立的辅助索引列信息，并加上相应主键列的所有信息，存储在特定的数据页中；

总结：利用辅助索引与聚合索引建立的关联，先经过辅助索引的查询获取对应聚簇索引，在经过聚簇索引回表查询获取详细数据；

辅助索引的构建方式：

  • 数据表创建时，显示的构建了一般索引信息（mul），一般索引信息（mul）就是辅助索引；
  • 数据表创建时，没有显示的构建一般索引信息时，在查询检索指定数据信息，会进行全表扫描查找数据；

结合下图信息，可以看出辅助索引组织存储数据过程与加速查询过程原理：

以上图信息为例，若显示创建name列为mul查询列：

① 调取需要建立的辅助索引列信息，并加上相应主键列的所有信息，存储在特定的内存区域中；

② 根据调取的辅助索引列信息，进行字符的顺序排序，便于形成范围查询的区间，并将排序后的数据信息存储在特定数据页中；

③ 叶子节点构建完后，可以构建no-left（支节点），用于保存的是leaf节点中的字符范围和指针信息；

④ 支节点构建完后，可以构建root（根节点），用于保存的是no-leaf节点中的字符范围和指针信息；

⑤ 找到相应辅助索引的数据信息后，在根据辅助索引与聚簇索引的对应关系，获取到相应的主键信息，从而获取相应其他数据信息

在利用聚簇索引获取其他数据信息的过程，也可以称之为回表查询过程；

辅助索引检索数据产生回表问题分析：（回表次数越少越高）

产生问题：

① 在回表过程中，有可能会出现多次的回表，从而造成磁盘IOPS的升高；（因为是随机IO操作过程）

② 在回表过程中，有可能会出现多次的回表，从而造成磁盘IO量的增加；

解决方法：

① 可以建立联合索引，调整查询条件，使辅助索引过滤出更精细主键ID信息，从而减少回表查询的次数；

② 可以控制查询信息，实现覆盖索引，辅助索引完全覆盖查询结果；

③ 优化器算法做调整？？？(MRR-多路读功能 ICP-索引下推功能 )

构建索引树高度问题分析：（索引树高度越低越好）

影响索引树高度因素：

① 数据行数量会对高度产生影响；（3层BTREE -- 可以实现一般2000万行数据索引的存储-20~30列表）

解决方法：可以拆分表 拆分库 或者实现分布式存储；

② 索引字段长度过大会对高度产生影响；

解决方法：利用前缀索引解决问题

③ 数据类型设定会对高度产生影响；

解决方法：列定义时，选择简短合适的数据类型；

索引应用

模拟测试环境

mysql> CREATE DATABASE `school`;

CREATE TABLE `student` (
  `sno` int NOT NULL COMMENT '学号',
  `sname` varchar(20) NOT NULL COMMENT '姓名',
  `sage` tinyint unsigned NOT NULL COMMENT '年龄',
  `ssex` enum('f','m') NOT NULL DEFAULT 'm' COMMENT '性别'
  `sid` varchar(20) null comment '身份证号'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

mysql> desc student;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| sno   | int              | NO   |     | NULL    |       |
| sname | varchar(20)      | NO   |     | NULL    |       |
| sage  | tinyint unsigned | NO   |     | NULL    |       |
| ssex  | enum('f','m')    | NO   |     | m       |       |
+-------+------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

查询索引

mysql> use world;
mysql> desc city;
-- 查询表结构信息，获取索引配置
索引标识解释说明
PK（PRI）表示为聚簇索引，也可以理解为主键索引
K（MUL）表示为辅助索引，也可以理解为一般索引
UK表示唯一键索引

创建索引

创建单列索引

# 创建主键索引语法格式
mysql> ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

image-20240701223257972

# 创建辅助索引语法格式
mysql> alter table city add index idx_name(name); # 注：辅助索引名字可以随便写，这里只是个人习惯

image-20240701234133383

# 创建唯一键索引
mysql> ALTER TABLE `table_name` ADD UNIQUE (`column`)

image-20240701235423824

创建联合索引

# 语法格式
mysql> alter table 表名 add index idx_na_id(字段,字段,...)

mysql> alter table student add index idx_na_id(sname,sid);

image-20240702093411390

创建前缀索引

mysql> alter table 表名 add index ix_n(字段名(3)); # 该字段必须是varchar类型

image-20240702094837716

删除索引

删除一般索引

-- 语法格式
alter table 表名 drop index 索引名;

删除辅助索引

-- 语法格式
alter table 表名 drop index 索引名;

image-20240702110416123

删除联合索引

-- 语法格式
alter table 表名 drop index 索引名;

image-20240702110600567

删除唯一索引

-- 语法格式
alter table 表名 drop index 列名;

image-20240702111647501

删除聚簇索引

-- 语法格式
alter table 表名 drop primary key;

image-20240702105549901

删除前缀索引

-- 语法格式
alter table 表名 drop index 列名;

image-20240702111126021

索引压测

上传测试环境

[root@db ~]# wget -qO- https://halo.tongao.top/upload/t100w.sql | mysql -u root -p
  1. 没有设置索引的情况下压测
mysqlslap --defaults-file=/data/mysql/my.cnf --concurrency=100 --iterations=1 --create-schema='t100w' --query="select * from t100w.t100w where k2='VWlm'" engine=innodb --number-of-queries=2000 -uroot -p -h192.168.126.7 -verbose

concurrency=100 模拟同时100会话连接；
iterations=1 测试执行的迭代次数，代表要在不同并发环境下，各自运行测试多少次
create-schema='t100w' 指定操作的数据库信息；
query="select * from test.100w where k2='780P'" 指定压测过程具体执行了什么语句操作
number-of-queries=2000 指定一共做了多少次查询，总的测试查询次数(并发客户数×每客户查询次数)

image-20240702150036463

  1. 设置索引的情况下压测
alter table t100w.t100w add primary key (id);
alter table t100w.t100w add index idx_k2(k2);

image-20240702150618736

可以用show processlist查看当前会话连接数