MySQL--索引--0427--0507

最新推荐文章于 2024-07-21 22:23:56 发布
最新推荐文章于 2024-07-21 22:23:56 发布
阅读量390 收藏
点赞数
分类专栏： MySQL 文章标签： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_68741368/article/details/130671003
版权

目录

1. MySQL是如何处理数据的

2. MySQL与磁盘的关系

 3. MySQL与磁盘交互的基本单位

 4.MySQL和磁盘之间联系的总结

 5.索引的理解

5.1 理解单个page

 5.2 理解多个page

5.3 为什么采用B+树

5.4 聚簇索引 和 非聚簇索引

5.5 聚簇索引 和 非聚簇索引下的普通索引

6.索引操作

6.1 创建主键索引

 6.2 创建唯一索引

6.3 创建普通索引

6.4 查询索引

6.5 删除索引

6.6 全文索引

7.复合索引

7.1 最左匹配原则

8.索引覆盖

1. MySQL是如何处理数据的

MySQL 中的数据文件，是以page为单位保存在磁盘当中的。找到了对应的扇区之后呢？

MySQL 的 CURD 操作，都需要通过计算，找到对应的插入位置，或者找到对应要修改或者查询的数据，谁又可以帮助计算呢？

2. MySQL与磁盘的关系

mysqld本质上就是一个在应用层的进程，所以他一定在操作系统之上运行的。当MySQL对内部的数据进行操作(CURD)的时候，本质上就是操作文件内容。所以文件必须先被打开，在哪里打开呢？难道是磁盘吗？肯定不是，文件需要被加载到内存当中，才能被同在内存中的进程获取到。

如果数据不在内存中呢？换入换出。换出来的内容放在哪里呢？所以mysql内部一定管理有一块空间，用来读取存放在内核缓冲区中的数据，不然每次都要开辟空间。所以在MySQL启动的时候，要预先申请一批空间。

 3. MySQL与磁盘交互的基本单位

MySQL本身就是用来处理数据的，所以少不了和磁盘进行接触。磁盘扇区大小为512字节，为了避免大量IO，以及减少耦合，文件系统读取以块为单位读取磁盘数据，单位是4KB。而MySQL进行IO的基本单位是16KB，这个基本数据单元称为page（和系统的page不一样）。

所以MySQL一次读取，就会至少读4个linux kernel，也就是4*8个扇区。

为何MySQL和磁盘进行IO交互的时候，要采用Page的方案进行交互呢?用多少，加载多少可以吗?
如过有5条记录，如果MySQL要查找id=2的记录，第一次加载id=1，第二次加载id=2，一次一条记录，那么就需要2次IO。如果要找id=5，那么就需要5次IO。
但如果这5条都被保存在一个Page中(16KB，能保存很多记录),那么第一次IO查找id=2的时
候，整个Page会被加载到MySQL的Buffer Pool中，这里完成了一次IO。但是往后如果在查找id=1,3,4,5等，完全不需要进行IO了，而是直接在内存中进行了。所以，就在单Page里面，大大减少了IO的次数。

MySQL申请的缓冲区中基本IO单位为page，说明内部一定有很多page，那么也就需要他来管理这些数据，用什么样的数据结构管理这些数据，也就是建立索引之后搜索变快的基础。

 4.MySQL和磁盘之间联系的总结

  • MySQL 中的数据文件，是以page为单位保存在磁盘当中的。
  • MySQL 的 CURD 操作，都需要通过计算，找到对应的插入位置，或者找到对应要修改或者查询的数据。而只要涉及计算，就需要CPU参与，而为了便于CPU参与，一定要能够先将数据移动到内存当中。
  • 所以在特定时间内，数据一定是磁盘中有，内存中也有。后续操作完内存数据之后，以特定的刷新策略，刷新到磁盘。而这时，就涉及到磁盘和内存的数据交互，也就是IO了。而此时IO的基本单位就是Page。
  • 为了更好的进行上面的操作， MySQL 服务器在内存中运行的时候，在服务器内部，就申请了被称为 Buffer Pool 的的大内存空间，来进行各种缓存。其实就是很大的内存空间，来和磁盘数据进行IO交互。
  • 为何更高的效率，一定要尽可能的减少系统和磁盘IO的次数
     

 5.索引的理解

建立一张有主键的表，然后随便插入几条数据。

mysql> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
mysql> insert into t1 values(3,18,'杨过');
mysql> insert into t1 values(4, 16, '小龙女');
mysql> insert into t1 values(2, 26, '黄蓉');
mysql> insert into t1 values(5, 36, '郭靖');
mysql> insert into t1 values(1, 56, '欧阳锋');
mysql> select * from t1;
+----+------+-----------+
| id | age  | name      |
+----+------+-----------+
|  1 |   56 | 欧阳锋    |
|  2 |   26 | 黄蓉      |
|  3 |   18 | 杨过      |
|  4 |   16 | 小龙女    |
|  5 |   36 | 郭靖      |
+----+------+-----------+

 现象：我并没有按照主键大小的顺序进行插入，但是在表中这些数据已经按照一定顺序帮我排列好了。

谁做的？mysql自己做的。

为什么做？只有page里面的数据是有序的，才能方便的引入页内目录（后面说），才能加快查找速度。

5.1 理解单个page

先描述，在管理

但是当我单个page内的数据非常多，每次查找一个就要遍历一遍链表吗？使用二分？

加一个页内目录即可，就像书籍前面的目录一样，方便读者查找文章内容。

在单个页中存放了多条数据信息，从而避免了频繁的IO操作，大大加快了搜索速度。可我怎么知道我需要的数据就在这个page，而不是前一个或者后一个呢？

 5.2 理解多个page

如果真的采用上述双链表样的结构存储数据，由于链表的遍历是O(n)的，找对应的page需要遍历一遍，那效率也太低了。

所以我们需要将页内目录也扩展到page与page之间。

  • 使用一个目录项来指向某一页，而这个目录项存放的就是将要指向的页中存放的最小数据的键值。
  • 和页内目录不同的地方在于，这种目录管理的级别是页，而页内目录管理的级别是行。
  • 其中，每个目录项的构成是：键值+指针。

存在一个目录页来管理页目录，目录页中的数据存放的就是指向的那一页中最小的数据。有数据，就可通过比较，找到该访问那个Page，进而通过指针，找到下一个Page。

目录页的本质也是页

普通页中存的数据是用户数据，而目录页中存的数据是普通页的地址。

目录页所在结构无需加前后指针，因为

只有相邻的普通页由于连续存储，一个page存不下了，需要创建第二个page，这样两个page使用前后指针方便了查找，而无需再从类似根节点的地方再走一遍。

目录页不存数据，因为

虽然存数据可以方便某一些查找情况下直接返回数据，但是如果页目录没有数据，就意味着16KB的空间全部可以用来保存(id,page_address)，会让这颗树变得矮胖，所以走到叶子节点所经历的中间节点就少了，也就意味着IO的次数少了，所以更快了。

 综上，存储的结构已经显现，就是B+树。

5.3 为什么采用B+树

链表——线性遍历，太慢。

二叉搜索树——退化问题，而且是二叉，树层高。

AVL、红黑树——再平衡也是二叉树，树层高。

Hash——MySQL其实支持Hash,不过 InnoDB 和 MyISAM 并不支持,在面对范围查找就明显不行

B树

 可以发现B树会在近似页目录的机构中添加数据。即B树的节点既有数据、又有page指针，叶子节点有键值和数据。

B+树

 

而B+树只有叶子节点有数据，其他目录页，只有键值和page指针，可以使树更矮，减少了IO次数。而且叶子节点相连，更有利于进行范围查找。

5.4 聚簇索引 和 非聚簇索引

二者底层存储结构都是B+树，但是存储的数据内容不一样。

MyISAM存储引擎，叶节点的数据域存放的是数据记录的地址。

 MyISAM最大的特点就是将索引page和数据page分离，叶节点只有对应数据的地址。

而InnoDB是将索引和数据放在一起的。

MyISAM 这种用户数据与索引数据分离的索引方案，叫做非聚簇索引

mysql> create table mtest(id int primary key,
        name varchar(11) not null)engine=MyISAM;

mtest.frm  表的数据结构
mtest.MYD 该表对应的数据，现在没插入数据 所以是0
mtest.MYI  该表对应的主键索引

InnoDB 这种用户数据与索引数据在一起索引方案，叫做聚簇索引

mysql> create table itest(id int primary key,
        name varchar(11) not null)engine=InnoDB;

itest.frm 表结构数据
itest.ibd  该表对应的主键索引和用户数据，虽然没有数据 但是有主键索引，所以不是0

5.5 聚簇索引 和 非聚簇索引下的普通索引

MyISAM ,建立普通索引和主键索引没有差别，无非就是主键不能重复，而非主键可重复。以第二列建立索引。
 

 InnoDB 除了主键索引，用户也会建立普通索引

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

6.索引操作

6.1 创建主键索引

在建表的时候，创建主键，自动建立主键索引。

create table user1(id int primary key, name varchar(30));


create table user3(id int, name varchar(30));
alter table user3 add primary key(id)

主键索引名字为PRIMARY

 6.2 创建唯一索引

创建唯一键时自动建立唯一索引

create table user4(id int primary key, name varchar(30) unique);

create table user6(id int primary key, name varchar(30)）；
alter table user6 add unique(name);

 唯一索引名为列名

6.3 创建普通索引

在表的定义最后，指定某列为索引

create table user8(id int primary key,
        name varchar(20),
        email varchar(30),
        index(name));

创建完表以后指定某列为普通索引

create table user9(id int primary key, name varchar(20), 
                email varchar(30));
alter table user9 add index(name);
mysql> desc user9;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  | MUL | NULL    |       |
| email | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

创建一个索引名为 idx_name 的索引

create table user10(id int primary key, name varchar(20), 
        email varchar(30));
create index idx_name on user10(name);

注意：

  • 建立普通索引默认，索引名字和列名字相同。
  • 如果给普通索引起别名，删除时需要指定该别名。

6.4 查询索引

show index from 表名；

mysql> show index from user10;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user10 |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| user10 |          0 | email    |            1 | email       | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| user10 |          1 | name     |            1 | name        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

6.5 删除索引

删除主键索引

alter table 表名 drop primary key;

其他索引的删除

alter table 表名 drop index 索引名;

 其他写法

drop index 索引名 on 表名;

6.6 全文索引

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

查询有没有database数据

如果采用以下查询方式，虽然有结果但是没有用到全文索引。

mysql> select * from articles where body like '%database%';

使用全文索引

mysql> SELECT * FROM articles
    -> WHERE MATCH (title,body) AGAINST ('database');
+----+-------------------+------------------------------------------+
| id | title | body |
+----+-------------------+------------------------------------------+
| 5 | MySQL vs. YourSQL | In the following database comparison ... |
| 1 | MySQL Tutorial | DBMS stands for DataBase ... |
+----+-------------------+------------------------------------------+

7.复合索引

相当于在键值中比较的是(col1,col2)，名字和col1的列名相同。先比较col1，在比较col2。

7.1 最左匹配原则

CREATE INDEX IDX_XXX ON TABLE(COL3, COL2);

 

详见

复合索引的底层数据结构——最左原则_复合索引在底层的数据结构是什么_长不大的大灰狼的博客-CSDN博客

8.索引覆盖

通俗来说，就是select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。

比如

id为聚集索引，name为非聚集索引：

select name, age from t where name = 'chy';

由于select后面有name 和 age，此时就需要回表查询。

但是如果在SQL中只查询name字段。这样name的索引就覆盖到了所有的查询列。

select name from t where name = 'chy';

或者将name的索引修改为联合索引(name, age )

select name, age from t where name = 'chy';

这样也覆盖到了所有的查询列。

Gosolo！
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
MySQL-索引
zhangxuelong461的博客
01-07 123
【索引】 定义： 对数据库中表的一列或者多列的值进行排序的一种结构 （MySQL中索引用BTREE方式）# 类似二分法的方法 索引的优缺点: 优点： 可以加快数据的检索速度 缺点： 当对表中的数据进行增加、删除和修改的时候，索引需要动态维护，降低了数据的维护速度 索引需要占用物理空间 索引示例(检测加索引后的运行时间)： 开启运行时间检测 set profiling=1; ...
MySQL-Federated引擎
热门推荐
commasx的博客
02-15 1万+
       FEDERATED存储引擎访问在远程数据库的表中的数据，而不是本地的表。这个特性给某些开发应用带来了便利，你可以直接在本地构建一个federated表来连接远程数据表，配置好了之后本地表的数据可以直接跟远程数据表同步。实际上这个引擎里面是不真实存放数据的，所需要的数据都是连接到其他MySQL服务器上获取。 开启federated引擎  进入mysql命令行，没有看到Federat...
MyBatis-Plus--解决逻辑删除与唯一索引的问题--方法/实例
IT利刃出鞘的博客
03-20 1万+
本文用示例介绍MyBatis-Plus如何解决逻辑删除与唯一索引的问题。
MySQL数据库---索引
白雪滑落树梢
10-25 2242
文章目录前言总结 前言 总结
mysql-installer-community-5.7.25
07-31
MySQL是世界上最受欢迎的开源数据库系统之一，而MySQL Installer Community则是MySQL官方提供的安装工具，用于简化在Windows平台上安装和管理MySQL产品的过程。在这个特定的版本"mysql-installer-community-5.7.25...
jeesite-mysql-v5.0.1
08-27
4. **查询优化**：在MySQL V5.0.1中，Jeesite充分利用索引、JOIN操作和存储过程等特性，优化查询性能，确保在大数据量下的高效运行。 5. **权限控制**：Jeesite内置了完善的权限控制机制，如Shiro或Spring Security...
mysql-5.7mysql-5.7mysql-5.7
11-20
MySQL 5.7是MySQL数据库管理系统的一个重要版本，它在MySQL的版本历史中引入了许多显著的改进和新特性。MySQL是一种流行的开源关系型数据库系统，广泛应用于Web应用程序、企业级解决方案以及各种数据存储需求。 ...
MySQL-Front_Setup 6.1
05-06
MySQL-Front是一款基于Windows操作系统的图形用户界面（GUI）工具，专为管理和操作MySQL数据库而设计。它提供了直观且用户友好的界面，使得数据库管理变得简单易行，尤其适合那些不熟悉MySQL命令行界面的用户。MySQL...
mysql-server-mysql-5.7.36.tar.gz
09-16
5. **柱状图索引（CTE）**：引入了Common Table Expressions（CTE），这使得复杂的递归查询和临时结果集的处理变得更加简单。 6. **分区表优化**：对分区表的处理有了显著的提升，包括更灵活的分区策略和更高效的...
OAuth2.0 or Spring Session or 单点登录流程
qq_53374893的博客
07-20 288
一句话精辟解释： 在过滤器放行的时候，偷偷的把原生的 request 和 response 对象换成了它的实现，也就是 调用getSession 的时候拿到的 其实是对redis 操作的Session。但其他的操作还是使用原生的，只不过重写的方法，调用的是子类的，也就是往 redis 里放入 Session，把原生的操作给替换了！分布式Session原理，使用子域名，的时候放入父域名的 JsessionId 然后将这个ID 的所存的内容放入Redis ，这样实现不同域名共享同一sessionID.
redis+spring面试题
最新发布
github_36510643的博客
07-21 268
redis+spring面试题
MySQL（事务、索引）&&MyBatis
hycccccch的博客
07-19 669
事务指的是一组操作的集合，是一个不可分割的工作单位。事务会将所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败默认MySQL的事务是自动提交，即当执行一条DML语句时，MySQL会立即隐式的提交事务开启事务后运行事务不会对数据改变，commit后才会改变数据如果有命令发生错误，需要进行rollback进行回滚、
Spring框架之DI依赖注入
G81948577的博客
07-18 980
我们在下面构建spring的过程中体会依赖注入；从上面的图中我们知道，在ssm框架中服务层（server）无法直接操作数据库，持久层（dao）是直接操作数据库进行数据处理的，但是我们如果在服务层有一个持久层的对象，然后我们在服务层通过对象去调用持久层的方法就可以操作数据库了。@OverrideSystem.out.println("持久层，你好");UserServiceImpl类中的代码如下@Override。
深入 Dify 源码，洞察 Dify RAG 核心机制
易迟的专栏
07-17 1152
之前深入源码对 Dify 的完整流程进行了解读，基本上梳理了 Dify 的实现流程与主要组件。但是在实际部署之后，发现 Dify 现有的 RAG 检索效果没有那么理想。因此个人结合前端页面，配置信息与实现流程，深入查看了私有化部署的 Dify 的技术细节。将核心内容整理在这边，方便大家根据实际的业务场景调整 Dify 知识库的配置，或者根据需要进行二次开发调优。
Apache AGE的WITH子句
oscube的博客
07-17 312
使用 WITH，您可以在将输出传递给以下查询部分之前对其进行操作。这些操作可以是结果集中条目的形状和/或数量。WITH 也可以像 RETURN 一样，使用别名对引入结果的表达式进行别名，使用别名作为绑定名称。WITH 还用于将图的读取与图的更新分开。查询的每个部分必须是只读的或只写的。从写入子句转到读取子句时，可以使用可选的 WITH 进行此操作。
怎样在 PostgreSQL 中实现数据的异地备份？
技术笔记
07-19 1076
总而言之，在 PostgreSQL 中实现数据的异地备份不是一件难事，但也需要我们认真对待，精心规划。选择合适的备份方法和异地存储方式，制定合理的备份策略，定期进行测试和恢复演练，才能确保我们的数据在任何情况下都能“安然无恙”。🎉相关推荐🍅关注博主🎗️带你畅游技术世界，不错过每一次成长机会！📚领书：PostgreSQL 入门到精通.pdf📙PostgreSQL 中文手册📘PostgreSQL 技术专栏🍅CSDN社区-墨松科技。
Java并发编程与高并发解决方案笔记
tuku0801的专栏
07-17 1050
将结合大量图示及代码演示，带你掌握多线程并发编程（线程安全，线程调度，线程封闭，同步容器等）与高并发处理思路与手段（扩容，缓存，队列，拆分等），构建完整的并发与高并发知识体系
04-用户画像+sqoop使用
weixin_46567476的博客
07-21 286
sqoop的作用是实现数据的导入和导出，主要是对数据库和数据仓库之间的操作。qoop脚本就是将sqoop指令写入shell文件 后缀是 .sh。只要是支持jdbc连接的数据库都可以使用sqoop操作。
MySQL-MMM架构部署与性能优化实践指南
2. 优化MySQL查询语句：例如使用索引、优化SQL语句、使用查询优化器等。 3. 优化MySQL存储引擎：例如使用InnoDB存储引擎、优化表结构等。 **总结** MySQL-MMM架构部署使用与性能调优是指使用MySQL-MMM架构来实现高...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值