09_MySQL数据库_索引的原理

柿子__

已于 2022-11-05 11:44:01 修改

阅读量605

点赞数

分类专栏： MySQL 文章标签：数据库 mysql 服务器索引

于 2022-10-25 10:59:12 首次发布

本文链接：https://blog.csdn.net/yohe1937/article/details/127509217

版权

MySQL 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

此专栏所有章节快速导航

01_MySQL数据库_CentOS7安装MySQL
02_MySQL数据库_数据库基础知识
 03_MySQL数据库_库的操作
 04_MySQL数据库_表的操作
 05_MySQL数据库_数据类型
 06_MySQL数据库_表的约束
 07_MySQL数据库_增删查改
 08_MySQL数据库_复合查询
 09_MySQL数据库_索引
 10_MySQL数据库_事务管理
 11_MySQL数据库_用户管理
 12_MySQL数据库_CentOS7下C-C++链接MySQL

一. 索引的简述

索引是给表的某个字段或者多个字段建立的一种方便检索的结构，字段为主键那就是主键索引，字段为唯一键那就是唯一索引，普通的字段就是普通索引。查询时，用建立起索引的字段查询会显著增加查询的速度。建立索引也会有一点点牺牲，比如说会占用空间去存储这种结构，还有在插入，更新，删除数据的时候会比有一点点性能牺牲，因为在操作的同时也要维护好索引结构。

二. 索引的原理

2.1 mysql的工作过程

如下图所示，我们使用数据库时，数据库会在内存中开辟一段空间buffer_pool（缓冲池），然后从硬盘中读取数据库数据到缓冲池。我们操作数据库时，数据会先保存在buffer_pool中，然后mysql会定期通过系统调用接口将数据写到内核缓冲区，操作系统再将数据刷新到硬盘。可以看出buffer_pool是mysql用来跟硬盘交互的一段空间。

在这里插入图片描述

2.2 MySQL与硬盘交互的基本单位

磁盘驱动器在向磁盘读取和写入数据时，要以扇区为单位，一扇区是512字节，以扇区为单位主要是为了减少IO次数以提高性能。访问硬盘是需要寻找对应的扇区的，如果频繁地寻找扇区效率是非常低的。为了解决这个问题，Linux的文件系统读取硬盘时是以块为单位，一块为4KB。也就是本来需要用到1个扇区内的数据，读硬盘时将后面的7个扇区的数据也读进去，这样如果后面需要用到其他7个扇区的数据就不用重新去读取读取硬盘了。MySQL与硬盘交互的基本单位是page，1page为16KB，也就是32扇区。，这样会减少大量的IO次数，相应的寻找扇区的次数也减少了，就能显著地提高效率。

2.3 索引结构

建立测试表

# 创建表
create table if not exists user (
	id int primary key, 
	age int not null,
	name varchar(16) not null
);

# 插入数据（就按照下面这个顺序插入）
insert into user (id, age, name) values(3, 18, '杨过');
insert into user (id, age, name) values(4, 16, '小龙女');
insert into user (id, age, name) values(2, 26, '黄蓉');
insert into user (id, age, name) values(5, 36, '郭靖');
insert into user (id, age, name) values(1, 56, '欧阳锋');

# 插入后查询user表的所有数据，我们会发现插入时是无序的，查询出来的是有序的

在这里插入图片描述

出现上面这种，插入无序，查询有序的原因是因为我们创建表时如果声明有主键，就会自动建立主键索引。为什么建立索引就是有序的，我们就得好好了解一下索引的底层结构了。

2.3.1 一个page的内部结构

如下图一个page对象就是一个双向链表的一个节点，这个节点分为两大部分，分别是指向前后page对象的指针，和数据部分。数据部分又分为数据记录和目录，数据记录是通过单链表的形式保存起来的。一般情况下一个page对象有成百上千条记录，如果直接遍历数据记录，效率是非常低的。为了提高page内部查询的效率，就加入了目录，目录有指针指向对应索引编号的记录，这样通过遍历目录可以定位到对应的记录。我们发现数据记录建立索引的字段是有序的，这是为了方便引入目录。

在这里插入图片描述

2.3.2 多个page之间

上面我们解决了page内部的检索效率问题，那么我们的page对象之间是双链表连接起来的，如下图所示，这样检索的效率也很低怎么办呢？

在这里插入图片描述

如图所示，这里引进了专门管理的page的page对象，这些page里面的数据部分存放的只有目录，目录指向的地址是对应编号的page的地址，这个编号是指向这个page里面数据记录的最小key值（索引字段值）。

在这里插入图片描述

2.3.3 索引结构的解析

刚刚我们上面说的存储结构其实就是B+树这种数据结构。我们可以将page对象分为目录页和数据页两种。目录页只放各个下级page的最小键值。查找的时候，自定向下找，只需要加载部分目录页到内存，即可完成算法的整个查找过程，大大减少了IO次数。在硬盘上有完整的B+树索引用户数据。在内存的buffer_pool中有被高频访问的数据页page和B+树的核心的目录页page。MySQL的增删查改操作一定会伴随着根据B+树进行的page换人换出。

2.3.4 B+树和其他数据结构对比

平衡二叉树： 虽然是平衡或者近似平衡，但是毕竟是二叉结构，相比较多阶B+，意味着树整体过高。大家都是自顶向下找，层高越低，意味着系统与硬盘更少的IOpage交互。

Hash： 官方的索引实现方式中，MySQL是有一种存储引擎是支持Hash的，根据Hash算法特征，(O(1))的查找速度，不过在面对范围查找就明显不行。

B树： B树的节点既有数据又有page指针，而B+树只有叶子节点有数据，其他目录页，只有键值和page指针。B+树叶子节点全部相连，而B树没有，所以B树在面对范围查找明显也是不行的。

B+树对比B树的优势：目录节点不存储数据记录，这样一个节点就可以存储更多的key。可以使得树更矮，所以IO操作次数更少。叶子节点相连，更便于进行范围查找。

三. 聚簇索引和非聚簇索引

3.1 概念

非聚[cù]簇索引： 用户数据与索引数据分离的索引方案，也就是说叶子节点的page里面存放的不是用户存放进表中的数据记录，而是这些数据记录的地址。也就是说在硬盘中索引数据是一个文件，用户数据又被存放进另外一个文件，代表的存储引擎就是MyISAM。

聚簇索引： 用户数据与索引数据在一起的存储方案，刚刚我们理解索引结构用的就是聚簇索引，即用户数据记录直接存放进叶子节点page里面，代表的存储引擎就是InnoDB。

3.2 InnoDB和MyISAM

MySQL除了默认会建立主键索引外，用户也可以按照其字段信息建立的索引，一般这种索引可以叫做辅助（普通）索引。

MyISAM： 对于 MyISAM，辅助索引和主键索引的结构没有差别，无非就是主键不能重复，而非主键可重复。

InnoDB： InnoDB 的辅助键索引中叶子节点并没有存放用户的数据记录，而是对应记录的主键值。所以通过辅助索引，找到目标记录，需要两遍索引，首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录，这种过程，就叫做回表查询。

四. 索引的操作

4.1 建立索引

4.1.1 主键和唯一键索引的建立

主键和唯一键索引对比

主键索引： 一个表中最多有一个主键索引，当然可以使用符合主键
唯一键索引： 一个表中可以有多个唯一键索引，如果一个唯一索引上指定not null，等价于主键索引

# 建立索引时唯一键和主键索引是一样的，
# 也就是下面的三种方式将primary key换成unique就是唯一键索引的建立了
# 第一种方式：建表时直接在字段后面指定primary key
create table tb1
(
    id int primary key, 
    name varchar(32)
);

# 第二种方式：在创建表的最后，指定某列或某几列为主键索引
create table tb2
(
    id int, 
    name varchar(32),
    primary key(id)
);

# 第三种方式：创建表以后再添加主键
# 先创建没有主键的表
create table tb3
(
    id int, 
    name varchar(32)
);
# 后面需要的时候再添加主键
alter table tb3 add primary key(id);

4.1.2 一般索引的创建

一个表中可以有多个普通索引，普通索引在实际开发中用的比较多
如果某列需要创建索引，但是该列有重复的值，那么我们就应该使用普通索引

# 第一种方式：在创建表时，指定某列为索引
create table tb4
(
    id int primary key,
	name varchar(32),
	index(name)
);

# 第二种方式：创建表之后再添加索引
create table tb5
(
    id int primary key,
	name varchar(32),
);

# 下面两种添加索引都行
alter table tb5 add index(name);  # 推荐这种
create index idx_name on tb5(name);  # idx_name为索引名，可以自定义

4.2 查询索引

# 方法一（推荐）：show index from table_name;
show index from tb1;  # 也可以将';'换成'/G',显示的格式友好一点

# 方法二：show keys from table_name;
show keys from tb1;

在这里插入图片描述

上图的解析

mysql> show index from tb1\G
*************************** 1. row ***************************
        Table: tb1  # --> 表名
   Non_unique: 0  # --> 0表示唯一索引，即主键索引和唯一键索引其中一种
     Key_name: PRIMARY  # --> 索引的名字，主键索引的名字为PRIMARY，其他的为字段名字或者自命名
 Seq_in_index: 1
  Column_name: id  # --> 索引为哪个字段
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE  # --> 索引的结构，一般是BTREE（B+树）
      Comment:
Index_comment:
1 row in set (0.00 sec)

4.3 索引的删除

4.3.1 主键索引的删除

# alter table 表名 drop primary key;
alter table tb1 drop primary key;  # 也就是直接去掉主键就行了

4.3.2 唯一键和普通键索引的删除

# 方法一：alter table 表名 drop index 索引名
alter table tb4 drop index name;  # 推荐这种

# 方法二： drop index 索引名 on 表名
drop index name on tb4;

4.4 全文索引

4.4.1 简述

当对文章字段或有大量文字的字段进行检索时，会使用到全文索引。MySQL提供全文索引机制，但是有要求，要求表的存储引擎必须是MyISAM，而且默认的全文索引支持英文，不支持中文。

4.4.2 创建测试表

# 创建全文索引
create table articles 
(
	id int primary key auto_increment,
	title varchar(32),
	body text,
	fulltext (title,body)  # 这里指明全文索引的字段
)engine=MyISAM;

# 插入数据
insert into articles (title,body) values
('MySQL Tutorial','DBMS stands for DataBase ...'),
('How To Use MySQL Well','After you went through a ...'),
('Optimizing MySQL','In this tutorial we will show ...'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
('MySQL vs. YourSQL','In the following database comparison ...'),
('MySQL Security','When configured properly, MySQL ...');

4.4.3 查询中用全文索引

# 查询数据
# 1. 没有用到索引去查找
select * from articles where body like '%database%';

# 2. 查询中用到全文索引
select * from articles where match (title,body) against ('database');  # 匹配时可以忽略大小写的

# against --> /əˈɡenst/ 与...相比