Mysql简易索引方案讲解

2024-09-28 21:27:46

Mysql简易索引

一、没有索引的时候如何查找

先忽略掉索引这个概念，如果现在直接要查某条记录，要如何查找呢？

在一个页中查找

如果表中的记录很少，一个页就够放，那么这时候有 2 种情况：

用主键为搜索条件：这时就是之前文章提过的方式，页面目录中用二分法快速定位到槽，然后遍历该槽对应分组的记录，最终找到指定记录。
用其他非主键的列为搜索条件：因为数据页中没有为非主键列建立页目录，无法通过二分法快速定位槽，只能从 Infimum 记录开始一次遍历单链表的每条记录，效率低下。

在很多页中查找

当表中的记录非常多，就会用到很多的数据页来存储，这时候需要 2 个步骤：

定位到记录所在页。
重复上述在一个页中查找的过程。

总得来说，当没有索引，我们无法快速定位到记录所在页，只能从第一页沿着双向链表(页有前一页和后一页)一直找下去，然后在每一页中重复上述的过程查询指定的记录，需要遍历所有记录，这种方式非常耗时。

二、一个简易索引

既然是因为页数太多导致定位记录太慢，那如何解决呢？不妨参考一下“页目录”。

页目录就是为了根据主键快速定位一条记录在页中的位置而设置的。那么我们也可以想办法为快速定位记录所在的页，搞一个“别的目录”。

但是这个“别的目录”要想完成还得干好 2 件事。

1. 下一页用户记录的主键值必须大于上一页的

假设，每个数据页最多可以放 3 条记录(实际上可以放很多)，那么现在向表里插入 3 条记录，每条记录有3个列 c1、c2、c3。为了看着方便，存储行格式也简化下，只留关键属性。注意中间3条是用户记录，首尾的2条是虚拟记录 Infimum 和 Supremum。

此时，继续插入 1 条记录。按照假设的情况，现在需要多分配一个新的页，所以 2 个页之间就变成了这样。

注意红色字体显示的2条记录，本来主键 4 的记录是新插入的，按理应该放在新的页。但是，为了满足下一页用户记录的主键值必须大于上一页的用户记录主键值，做了诸如记录移动的操作，这个过程也可以称为“页分裂”。

另外，为什么新页是页 28，而不是 11？因为页在磁盘上可能并不挨着，它们只是通过维护上一页和下一页的编号而建立了链表关系。

2. 给所有的页建立一个目录项

现在继续向表里增加数据，最终多个页的关系是这样：

因为这些页在磁盘上可能不挨着，所有想要快速从这么多页中根据主键快速定位某记录，就要给它们编制一个目录。

页的用户记录中最小的主键值，用 key 来表示
页号，用 page_no 表示

所以，给它们编好目录之后就是这样的关系：

那么，现在我想查找主键值为 20 的记录，具体就分两步走：

先从目录项中根据二分法快速确定出主键值为 20 的记录所在目录项 3 中，且对应的页为 9。知道是在页 9，重复之前的方式，找到最终目标记录。

到此，一个简易的方案完成。而完成的这个简易目录，它有个别名，叫做索引。

三、简易索引暴露出的问题

上述的简易索引是原书作者为了循序渐进的帮助读者理解而设置的内容，这并不是innodb的索引方案。

那么针对上述的建议索引，看下有哪些问题。

问题一：

InnoDB 使用页作为管理存储空间的基本单位，也就是最多只能保存16kb的连续存储。

当表中记录越来越多，此时就需要非常大的连续存储空间才可以把所有的目录项都装下，这对大数据量的表来说不现实。

问题二：

我们经常还要对记录执行增删改操作，会牵一发而动全身。

比如，上图中我如果把页 28 中的记录都删除，那么页 28 就没必要存在，进而目录项 2 也没必要存在。这时候就需要把目录项 2 后的目录项都向前移动一下。

就算不移动，把目录项 2 作为冗余放在目录项列表中，仍然会浪费很多的存储空间。

所以，InnoDB 的作者发现了一种灵活管理所有目录项的方式，详见下一篇。

本文参考书籍：《mysql是怎样运行的》

以上就是Mysql简易索引方案讲解的详细内容，更多关于Mysql简易索引的资料请关注我们其它相关文章！

如何利用MySQL添加联合唯一索引

目录联合唯一索引扩展延伸: 附:mysql中如何用命令创建联合索引总结联合唯一索引项目中需要用到联合唯一索引: 例如:有以下需求:每个人每一天只有可能产生一条记录:处了程序约定之外,数据库本身也可以设定: 例如:t_aa 表中有aa,bb两个字段,如果不希望有2条一模一样的记录(即:aa字段的值可以重复: bb字段的值也可以重复,但是一条记录(aa,bb)组合值不允许重复),需要给 t_aa 表添加多个字段的联合唯一索引: alter table t_aa add unique ind
MySQL使用索引优化性能

目录 1.索引问题 2.索引的存储分类 3.如何使用索引 3.1使用索引 3.2存在索引但不使用索引 4.查看索引使用情况 5.两个简单实用的优化方法 5.1定期分析表和检查表 5.2定期优化表 1.索引问题索引是数据库优化中最常用也是最重要的手段之一,通过索引通常可以帮助用户解决大多数的SQL性能问题.本章节将对MySQL中的索引的分类.存储.使用方法做详细的介绍. 2.索引的存储分类 MyISAM存储引擎的表数据和索引是自动分开存储的,各自是独立的一个文件:InnoDB存储引擎的表数据和
你知道mysql哪些查询情况不走索引吗

目录前言 mysql哪些查询情况不走索引不走索引的情况: 总结前言在MySQL中,并不是你建立了索引,并且你在SQL中使用到了该列,MySQL就肯定会使用到那些索引的,有一些情况很可能在你不知不觉中,你就“成功的避开了”MySQL的所有索引. mysql哪些查询情况不走索引 1.索引列参与计算,不走索引 SELECT `username` FROM `t_user` WHERE age=20;-- 会使用索引 SELECT `username` FROM `t_user` WHERE a
为什么Mysql 数据库表中有索引还是查询慢

目录前言: 1.字段类型不匹配导致的索引失效 2.被索引字段使用了表达式计算 3.被索引字段使用了内置函数 4.like 使用了 %X 模糊匹配 5.索引字段不是联合索引字段的最左字段 6.or 分割的条件 7.in.not in 可能会导致索引失效总结前言: 问题分析: 在进行数据库查询的时候,我们都知道索引可以加快数据查询的效率.但是在实际的业务场景下,经常会遇到即使在表中增加了索引,但是同样还是会出现数据查询慢的问题.这就需要具体分析数据查询慢的具体原因到底是什么了. 首先需要进行确
MySQL高级篇之索引的数据结构详解

目录 1.为什么使用索引? 2.索引的优缺点 3.InnoDB中的索引 3.1 设计索引 3.2 常见索引概念 3.2.1 聚簇索引 3.2.2 非聚簇索引 3.2.3 联合索引 4.InnoDB与MyISAM的索引对比 5.B-Tree和B+Tree的差异总结 1.为什么使用索引? 假如给数据使用二叉树这样的数据结构进行存储,如下图所示 2.索引的优缺点 MySQL 官方对索引的定义为: 索引(Index )是帮助 MySQL 高效获取数据的数据结构 .索引的本质: 索引是数据结构.你可
为MySQL创建高性能索引

目录 1 索引基础 1.1 索引作用 1.2 MySQL索引常用数据结构 1.2.1 B-Tree 1.2.2 B+Tree索引 1.2.3 Hash索引 2 高性能索引策略 2.1 聚簇索引与非聚簇索引聚簇索引非聚簇索引 2.2 前缀索引 2.3 回表 2.4 覆盖索引 2.5 索引匹配方式 2.5.1 最左匹配 2.5.2 匹配列前缀 2.5.3 匹配范围值 2.5.5 只访问索引的查询 3 索引优化最佳实践 4 索引监控 1 索引基础 1.1 索引作用在MySQL中,查找数据时先在索
Mysql简易索引方案讲解

目录 Mysql简易索引一.没有索引的时候如何查找在一个页中查找在很多页中查找二.一个简易索引 1. 下一页用户记录的主键值必须大于上一页的 2. 给所有的页建立一个目录项三.简易索引暴露出的问题 Mysql简易索引一.没有索引的时候如何查找先忽略掉索引这个概念,如果现在直接要查某条记录,要如何查找呢? 在一个页中查找如果表中的记录很少,一个页就够放,那么这时候有 2 种情况: 用主键为搜索条件:这时就是之前文章提过的方式,页面目录中用二分法快速定位到槽,然后遍历该槽对应分组的记
MySQL 函数索引的优化方案

很多开发人员在使用MySQL时经常会在部分列上进行函数计算等,导致无法走索引,在数据量大的时候,查询效率低下.针对此种情况本文从MySQL5.7 及MySQL8.0中分别进行不同方式的优化. 1. MySQL5.7 MySQL5.7版本中不支持函数索引,因此遇到函数索引的时候需要进行修改,否则即使查询的字段上有索引,执行时也无法使用索引而进行全表扫描,数据量大的表查询时间会比较长.具体案例如下: 1.1 创建测试表及数据 mysql> use testdb; Database changed
mysql IS NULL使用索引案例讲解

简介 mysql的sql查询语句中使用is null.is not null.!=对索引并没有任何影响,并不会因为where条件中使用了is null.is not null.!=这些判断条件导致索引失效而全表扫描. mysql官方文档也已经明确说明is null并不会影响索引的使用. MySQL can perform the same optimization on col_name IS NULL that it can use for col_name = constant_value.
深入讲解MySQL Innodb索引的原理

引言回想四年前,我在学习mysql的索引这块的时候,老师在讲索引的时候,是像下面这么说的索引就像一本书的目录.而当用户通过索引查找数据时,就好比用户通过目录查询某章节的某个知识点.这样就帮助用户有效地提高了查找速度.所以,使用索引可以有效地提高数据库系统的整体性能. 嗯,这么说其实也对.但是呢,大家看完这种说法,其实可能还是觉得太抽象了!因此呢,我还想再深入的细说一下,所以就有了此文! 需要说明的是,我说的内容只在Mysql的Innodb引擎中是成立的.在Sql Server.oracle.
MySQL 数据库 ALTER命令讲解

MySQL 为关系型数据库(Relational Database Management System), 这种所谓的"关系型"可以理解为"表格"的概念, 一个关系型数据库由一个或数个表格组成. 当我们需要修改数据表名或者修改数据表字段时,就需要使用到MySQL ALTER命令. 开始本章教程前让我们先创建一张表,表名为:testalter_tbl. root@host# mysql -u root -p password; Enter password:*****
Mysql性能优化方案分享

网上有不少mysql 性能优化方案,不过,mysql的优化同sql server相比,更为麻烦,同样的设置,在不同的环境下 ,由于内存,访问量,读写频率,数据差异等等情况,可能会出现不同的结果,因此简单地根据某个给出方案来配置mysql是行不通的,最好能使用status信息对mysql进行具体的优化. mysql> show global status; 可以列出MySQL服务器运行各种状态值,另外,查询MySQL服务器配置信息语句: mysql> show variables; 一.慢查询
MySQL中索引失效的常见场景与规避方法

前言之前有看过许多类似的文章内容,提到过一些sql语句的使用不当会导致MySQL的索引失效.还有一些MySQL"军规"或者规范写明了某些sql不能这么写,否则索引失效. 绝大部分的内容笔者是认可的,不过部分举例中笔者认为用词太绝对了,并没有说明其中的原由,很多人不知道为什么.所以笔者绝对再整理一遍MySQL中索引失效的常见场景,并分析其中的原由供大家参考. 当然请记住,explain是一个好习惯! MySQL索引失效的常见场景在验证下面的场景时,请准备足够多的数据量,因为数据量少时
PHP之mysql位运算案例讲解

位运算,赋值状态时异或对应位数1的整形,判断状态则与运算对应位数1的整形.最大用处就是同时判断32位状态,节省存储空间,便于扩展, 如果你不知道什么是位运算的话, 那么请你先去看看基础的C语言教程吧. 与运算 a & b , 或运算 a | b , 异或运算 a ^ b , 或者你也可以将与运算理解为 + 法例如 1|2 = 3 (1+2 = 3) 1|2|4 = 7 (1+2+4 = 7) 将异或运算理解为 - 法例如 3^2 = 1 (3-2 = 1) 3^1 = 2
MySQL常见优化方案汇总

目录思考sql优化的几个地方,我把他做了个分类,方便理解 key_len计算方式简单介绍一.优化点1:字段优化覆盖索引尽量用二.优化点2:where优化 1.尽量全值匹配 2.最佳左前缀法则 3.范围条件放最后 4.不在索引列上做任何操作 5.不等于要甚用 6.Null/Not null有影响 7.Like 查询要当心 like 8.字符类型加引号三.优化3 1.OR 改 UNION 效率高 mysql优化是我们日常工作经常遇到的问题,今天给大家说下MySQL常见的几种优化方案. 注:
MySQL导致索引失效的几种情况

目录一.准备工作二.索引失效规则 1.优先使用联合索引 2.最左匹配原则 3.范围条件右边的列索引失效 4.计算.函数导致索引失效 5.类型转换导致索引失效 6.不等于(!= 或者<>)索引失效 7.is null可以使用索引,is not null无法使用索引 8.like以%开头,索引失效 9.OR前后存在非索引的列,索引失效 10.字符集不统一三.建议一.准备工作首先准备两张表用于演示: CREATE TABLE `student_info` ( `id` int NOT NU