mysql什么情况索引会重排_聚集索引更新后会不会马上重新排序

最新推荐文章于 2023-05-12 16:44:02 发布
最新推荐文章于 2023-05-12 16:44:02 发布
阅读量1k 收藏
点赞数
文章标签： mysql什么情况索引会重排
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_42399388/article/details/113907822
版权

SQL SERVER 聚集索引 万圣节问题 更新排序 数据页面
关键词由CSDN通过智能技术生成

聚集索引更新后会不会马上重新排序

今天在QQ群里有人问到下面问题

b9aa74028ef63638e567b7fece1518c8.png

提问人用的是MYSQL，不过这个问题让我想起了SQLSERVER的万圣节问题

万圣节问题就是因为更新了非聚集索引之后，非聚集索引迅速排序导致的更新错误问题

当时我只是测试了非聚集索引下面的情况，但是聚集索引下面有没有这种情况呢？

我们修改一下

使用下面SQL脚本建立测试环境

8f900a89c6347c561fdf2122f13be562.png

961ddebeb323a10fe0623af514929fc1.png

1 USEmaster2 GO

3 CREATE DATABASESpool4 GO

5

6 USE [Spool]

7 GO

8

9

10 --建表

11 CREATE TABLEHalloween12 (13 ID INT IDENTITY(1, 1),14 Name VARCHAR(30) ,15 Salary NUMERIC(18, 2),16 Remark NVARCHAR(3000)17 )18 GO

19

20 --插入数据

21 INSERT INTO [dbo].[Halloween] ( [Name], [Salary], [Remark])22 SELECT '小明',1,replicate('a', 3000) UNION ALL

23 SELECT '小方',2,replicate('a', 3000)24

25

26

27

28 --建立聚集索引

29 CREATE CLUSTERED INDEX ix_Halloween ON Halloween(Salary ASC,[name])30 GO

31

32 --查询

33 SELECT * FROMHalloween34 GO

View Code

f7e958f0685a4a6313bd817641f9cc76.png

我们用下面SQL语句看一下聚集索引页面

8f900a89c6347c561fdf2122f13be562.png

961ddebeb323a10fe0623af514929fc1.png

1 CREATE TABLEDBCCResult (2 PageFID NVARCHAR(200),3 PagePID NVARCHAR(200),4 IAMFID NVARCHAR(200),5 IAMPID NVARCHAR(200),6 ObjectID NVARCHAR(200),7 IndexID NVARCHAR(200),8 PartitionNumber NVARCHAR(200),9 PartitionID NVARCHAR(200),10 iam_chain_type NVARCHAR(200),11 PageType NVARCHAR(200),12 IndexLevel NVARCHAR(200),13 NextPageFID NVARCHAR(200),14 NextPagePID NVARCHAR(200),15 PrevPageFID NVARCHAR(200),16 PrevPagePID NVARCHAR(200)17 )18

19 --TRUNCATE TABLE [dbo].[DBCCResult]

20 INSERT INTO DBCCResult EXEC ('DBCC IND(spool,Halloween,-1)')21

22 SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC

View Code

聚集索引页面

8f900a89c6347c561fdf2122f13be562.png

961ddebeb323a10fe0623af514929fc1.png

1 DBCC TRACEON(3604,-1)2 GO

3 DBCC PAGE(spool,1,174,3)4 GO

View Code

210f25ca4fe176046ea997fd0cf03774.png

b2789bb8671e4ed548fd8e1e6bc94d83.png

我们update一下Salary等于1的那位员工的工资

1 UPDATE Halloween SET Salary = 2.5

2 FROMHalloween3 WHERE Salary =1

再看一下聚集索引页面

0adb0e6d73258fffefa9861d03be2122.png

可以看到SQLSERVER增加了页面89来保存结果，就是说聚集索引也是在更新之后马上排序的

b98c42b031e8ae4a7645dde95d208528.png

那么页面89和页面109里面到底有什么内容呢？

8f900a89c6347c561fdf2122f13be562.png

961ddebeb323a10fe0623af514929fc1.png

1 DBCC TRACEON(3604,-1)2 GO

3 DBCC PAGE(spool,1,109,3)4 GO

View Code

数据页面109

1 PAGE: (1:109)2

3

4

5 UNIQUIFIER = [NULL]

6

7 Slot 0 Column 1 Offset 0x4 Length 9

8

9 Salary = 1.00

10

11 Slot 0 Column 2 Offset 0x1c Length 4

12

13 Name =小明14

15 Slot 0 Column 3 Offset 0xd Length 4

16

17 ID = 1

18

19 Slot 0 Column 4 Offset 0x20 Length 6000

20

21 Remark = aaaa

数据页面89

8f900a89c6347c561fdf2122f13be562.png

961ddebeb323a10fe0623af514929fc1.png

1 DBCC TRACEON(3604,-1)2 GO

3 DBCC PAGE(spool,1,89,3)4 GO

View Code

1 PAGE: (1:89)2

3 Slot 0 Column 1 Offset 0x4 Length 9

4

5 Salary = 2.50

6

7 Slot 0 Column 2 Offset 0x1c Length 4

8

9 Name =小明10

11 Slot 0 Column 3 Offset 0xd Length 4

12

13 ID = 1

14

15 Slot 0 Column 4 Offset 0x20 Length 6000

16

17 Remark = aaa

大家马上会问：为什麽109这个页面不删除他，还存放着旧的记录，留在那里占用空间？？？

除非你重组聚集索引或者重建聚集索引，不然的话SQLSERVER不会马上删除页面的

当你又插入一条记录，并且这条记录的Salary小于2，那么SQLSERVER就会把这条记录保存到109这个页面

1 INSERT INTO [dbo].[Halloween] ( [Name], [Salary], [Remark])2 SELECT '小新',1,replicate('a', 3000)

看一下Halloween表的页面分配情况

5b4cfb342eb51ac9e2cb484978d76af5.png

还是那几个页面，没有变化

现在看一下109这个页面

8f900a89c6347c561fdf2122f13be562.png

961ddebeb323a10fe0623af514929fc1.png

1 DBCC TRACEON(3604,-1)2 GO

3 DBCC PAGE(spool,1,109,3)4 GO

View Code

1 PAGE: (1:109)2

3

4 UNIQUIFIER = [NULL]

5

6 Slot 0 Column 1 Offset 0x4 Length 9

7

8 Salary = 1.00

9

10 Slot 0 Column 2 Offset 0x1c Length 4

11

12 Name =小新13

14 Slot 0 Column 3 Offset 0xd Length 4

15

16 ID = 3

17

18 Slot 0 Column 4 Offset 0x20 Length 6000

19

20 Remark = aaa

关于第一张图片的结果

堆表脚本

8f900a89c6347c561fdf2122f13be562.png

961ddebeb323a10fe0623af514929fc1.png

1 CREATE TABLEHalloween22 (3 ID INT,4 Name VARCHAR(30) ,5 Salary NUMERIC(18, 2),6 Remark NVARCHAR(3000)7 )8 GO

9

10 --插入数据

11 INSERT INTO [dbo].[Halloween2] ([id] ,[Name], [Salary], [Remark])12 SELECT 1, '小明',2,replicate('a', 3000) UNION ALL

13 SELECT 2, '小方',3,replicate('a', 3000)14

15

16

17

18

19 --查询

20 SELECT * FROMHalloween221 GO

22

23 UPDATEHalloween224 SET Salary = id ,[id] =salary25

26 --查询

27 SELECT * FROMHalloween228 GO

View Code

1eb7f8b0a4a2e29e175611fe5035bb91.png

更新时候的执行计划

235ff24436d1367b9fb61e2efc5acba5.png

聚集索引表脚本

8f900a89c6347c561fdf2122f13be562.png

961ddebeb323a10fe0623af514929fc1.png

1 CREATE TABLEHalloween32 (3 ID INT,4 Name VARCHAR(30) ,5 Salary NUMERIC(18, 2),6 Remark NVARCHAR(3000)7 )8 GO

9

10 --插入数据

11 INSERT INTO [dbo].[Halloween3] ([id] ,[Name], [Salary], [Remark])12 SELECT 1, '小明',2,replicate('a', 3000) UNION ALL

13 SELECT 2, '小方',3,replicate('a', 3000)14

15 --建立聚集索引

16 CREATE CLUSTERED INDEX ix_Halloween ON Halloween3(Salary ASC,[name])17 GO

18

19

20

21 --查询

22 SELECT * FROMHalloween323 GO

24

25 UPDATEHalloween326 SET Salary = id ,[id] =salary27

28 --查询

29 SELECT * FROMHalloween330 GO

View Code

9f7b6e4df9d166e1d70312627e9a2786.png

更新时候的执行计划

5f1633c72f3b19cc2e9f3ed4f4fab3dd.png

非聚集索引表脚本

8f900a89c6347c561fdf2122f13be562.png

961ddebeb323a10fe0623af514929fc1.png

1 CREATE TABLEHalloween42 (3 ID INT,4 Name VARCHAR(30) ,5 Salary NUMERIC(18, 2),6 Remark NVARCHAR(3000)7 )8 GO

9

10 --插入数据

11 INSERT INTO [dbo].[Halloween4] ([id] ,[Name], [Salary], [Remark])12 SELECT 1, '小明',2,replicate('a', 3000) UNION ALL

13 SELECT 2, '小方',3,replicate('a', 3000)14

15 --建立非聚集索引

16 CREATE NONCLUSTERED INDEX ix_Halloween ON Halloween4(Salary ASC,[name])17 GO

18

19

20

21 --查询

22 SELECT * FROMHalloween423 GO

24

25 UPDATEHalloween426 SET Salary = id ,[id] =salary27

28 --查询

29 SELECT * FROMHalloween430 GO

View Code

77fd1793b0c090261701c29a05a2fbdb.png

更新时候的执行计划

8a1f318265424a4cc0ce82c90f632c91.png

总结

聚集索引也会引起万圣节问题

61b008a6cbc63fb4218c8c4085cacd53.png

万圣节问题：重新排序导致的更新记录错误

聚集索引更新了之后也会重新排序，所以你看到上面聚集索引表的结果

5f1633c72f3b19cc2e9f3ed4f4fab3dd.png

中间也会有一个表假脱机的运算符

而非聚集索引的执行计划为什麽没有呢？

因为非聚集索引那个扫描的是堆里的数据页面，而不是扫描非聚集索引页面，

只有扫描非聚集索引页面才会出现万圣节问题，更新了非聚集索引之后马上进行排序

而堆里的数据页面无论怎麽更新都不会重新排序的

这里归纳一下重新排序的情况

重新排序：扫描聚集索引(聚集索引表)、扫描非聚集索引(非聚集索引表)

不重新排序：扫描堆里的数据页面(非聚集索引表)、堆表

非聚集索引比较特殊，因为索引页面和数据页面是分开的，所以要分两种情况

如有不对的地方，欢迎大家拍砖o(∩_∩)o

粗糙阿琼
关注
mysql什么情况索引会重排_MySQL 面试索引全篇，索引面试掌握这些就够了
weixin_39662462的博客
02-18 1204
问：可以说说聚集索引和非聚集索引区别吗？在 Mysql 中，有两大常用的存储引擎 MyisAm 和 Innodb ， Myisam 使用的是非聚集索引也叫非聚簇索引， InnoDb 使用的是聚集索引，聚集索引就是以主键创建的索引，非聚集索引就是除了主键以外的索引。非聚集索引在建立的时候也未必是单列的，可以多个列来创建索引，本质区别是表记录的排列顺序和索引的排列顺序是否一致。聚集索引的叶节点就是数据...
php 数组索引重新排序,PHP索引数组排序方法整理
weixin_29420299的博客
03-10 1959
这篇文章主要介绍了关于PHP索引数组排序方法整理，有着一定的参考价值，现在分享给大家，有需要的朋友可以参考一下一、冒泡排序1. 原理每次在无序队列里将相邻两个数依次进行比较，将小数调换到前面，逐次比较，直至将最大的数移到最后。最将剩下的N-1个数继续比较，将次大数移至倒数第二位。 依此规律，直至比较结束。2. 代码/*** 冒泡排序* @param Array 排序数组* @return Arr...
【数据库】MySQL数据库索引重新排序（重置索引）
KingdomYuen的博客
07-18 1534
解决数据库因某些原因导致主索引不连贯的问题，让主索引重新排序 alter table 表名 add old_id int(10) not null; update 表名 set old_id=主索引名; alter table 表名 drop 主索引名; alter table 表名 add 主索引名 int(10) not null; alter table 表名 modify column 主索引名 int(10) not null auto_increment, add primary key (主
mysql自增长主键，删除数据后，将主键顺序重新排序
qq_39770301的博客
03-23 9966
用数据库的时候，难免会删除数据，会发现设置的主键增长不是按照正常顺序排列，中间有断隔比如这样。以我这个情况举例处理方法的原理：删除原有的自增ID，重新建立新的自增ID。ALTER TABLE `news` DROP `NewsID`; ALTER TABLE `news` ADD `NewsID` int NOT NULL FIRST; ALTER TABLE `news` MODIFY COLU...
mysql什么情况索引会重排_什么情况下mysql索引会失效？
weixin_39699163的博客
02-18 796
mysql索引失效的几种情况：1、条件中有or，即使其中有条件带索引也不会使用；2、对于多列索引，不使用的第一部分，则不会使用索引；3、like查询以%开头，索引无效；4、当字段类型为字符串时，条件中数据没有使用引号引用。(推荐教程：mysql视频教程)索引并不是时时都会生效的，比如以下几种情况，将导致索引失效：1.如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)...
MySQL使用索引排序
程序员劝退师的博客
09-03 6097
前言 过去我们只知道使用ORDER BY来做排序，那么本章将来介绍MySQL使用索引进行排序，MySQL实现排序共有两种手段，第一种就是通过排序操作，第二种就是通过索引来排序。(关于查询语句中有没有使用带索引排序我们可以通过EXPLAIN执行计划得到type列的值为index，则说明使用到了索引来排序) 支持 MySQL可以使用同一个索引满足排序(ORDER BY)和查询(WHERE)操作,因此如果可以，设计索引的时候尽可能考虑同时满足两种任务 可能更慢 扫描索引本身就很快，因为只需要从一条索引记录移动到紧
mysql 重复率高字段 索引_MySQL的高性能索引策略
weixin_42508785的博客
02-02 2951
前言首先，如果不是很理解索引的底层结构原理的话，建议先看一下我的另一篇介绍索引的底层原理。(点击跳转)本篇文章是大部分摘入高性能MySQL的笔记，也有参考网上的各种博客整理。仅供大家学习和复习！独立的列独立的列是指索引不能参与运算，要将索引索引单独的放在表达式的一列。像下面的语句就不会用到索引。mysql> SELECT actor_id FROM sakila.actor WHERE ac...
mysql按升序创建索引_MySQL数据库ORDER BY优化总结(为排序使用索引)
weixin_35952427的博客
12-30 1910
在使用order by时，经常出现Using filesort，所以对于此类sql语句我们需要去尽力优化，使其尽量使用Using index。那么，我们对于这类型的语句我们怎么去做优化呢?因为这一块还是比较容易混淆的，所以我弄了个实验，相信大家跟我一起做下实验就都能理解了~1. 环境准备droptableifexiststest;createtabletest(idintprima...
mysql 分区表 分区索引_MySQL转换分区表&索引重用
weixin_35108433的博客
01-18 668
背景：生产环境下，大表数据量剧增，影响到了SQL的执行效率；业务越来越多，陆陆续续增加的索引并不是很合理，为了提高索引的使用率，需要把不必要的索引合并起来，减少索引的数量，提高索引的使用率方法：大表水平切分-->分区表转换；综合利用联合索引的特点，去掉一些多余的单列索引和一些重复的联合索引这篇博文的主要内容：转换分区表的方法：直接alter即可(http://blog.itpub.net/2...
mysql 数据不重复 索引_mysql字段类型 唯一索引 记录不重复设置
weixin_39598954的博客
01-19 2107
用phpmyadmin查看你的数据库，设置为主键、选择唯一索引，是否允许重复与数据类型无关。关键字UNIQUE把它定义为一个唯一索引.唯一性索引 和“普通索引”基本相同,但有一个区别:索引列的所有值都只能出现一次,即必须唯一。MySQL 普通索引、唯一索引和主索引1、普通索引普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件(W...
mysql 组合索引 排序_mysql一般组合索引分析及验证最左原则和索引重排序
weixin_32672471的博客
02-18 355
1、建表语句CREATE TABLE `temp` (`id` int(11) NOT NULL AUTO_INCREMENT,`aaa` varchar(255) DEFAULT NULL,`bbb` varchar(20) DEFAULT NULL,`ccc` varchar(255) DEFAULT NULL,PRIMARY KEY (`id`),KEY `INDX_ABC` (`aaa`,...
mysql索引重建和索引碎片问题_[笔记整理]SQL Server 索引碎片 和 重建索引
weixin_33969103的博客
02-02 1034
铺垫知识点：数据库存储本身是无序的，建立了聚集索引，会按照聚集索引物理顺序存入硬盘。既键值的逻辑顺序决定了表中相应行的物理顺序多数情况下，数据库读取频率远高于写入频率，索引的存在 为了读取速度牺牲写入速度页 为最小单位 8kb区物理连续的页(8页)的集合内部碎片 数据库页内部产生的碎片，外部反之碎片的产生：有一个表里有8条数据，已经将一页填满，这个时候要插入第九条数据，页也就分裂了。这就产生了内...
关于mysql索引排序的那些事
huangpixu的博客
09-08 166
mysql在8.0版本真正支持降序索引，详情请看：MySQL官方文档 测试一下5.7和8.0两个版本下索引排序的情况： 1.mysql8.0 CREATE TABLE `t1` ( `c1` int(11) DEFAULT NULL, `c2` int(11) DEFAULT NULL, KEY `idx_c1_c2` (`c1`, `c2` DESC) ); explain select * from t1 order by c1 , c2 desc limit
索引重建(重组)的常见问题
baicongli9053的博客
09-30 1477
1. 索引重建和重组有什么用？ 当修改表(UPDATE、INSERT、DELETE等)中数据，数据库引擎自动维护索引的数据和结构。但是随着修改次数的累积，可能会现： 索引中记录的数据顺序(逻辑顺序)和数据的实际顺序不一致(物理顺序)，这也称之为外部碎片。 索引页的数据填充度变小(页密度)，也称之为内部碎片。 有索引碎片是正常的，但是有大量的碎片，会降低查询性能，可以通过重建和重组索引来...
Update操作对索引的影响
06-08 3926
对于update操作，索引将对原先的数据进行删除标记，然后增加一条新的记录，因此很多人，包括我在内，就有一个错误观念：大批量的update更新操作，就可能造成索引存储空间浪费，我们需要定期对索...
mysql自增主键问题（顺序和重排）
冲冲冲的博客
04-02 1320
mysql自增主键问题（顺序和重排）顺序问题重排问题 顺序问题 故事开始是一道MySQL 面试题 一张自增表中有三条数据，删除最后两条数据之后重启数据库，再新增一条数据，此时这条数据的 ID 是几？ 如果表的引擎类型是 MyISAM，那么这条记录的 ID 就是 4。因为 MyISAM表会把自增主键的最大ID记录到数据文件里面，重启 MySQL 后，自增主键的最大 ID 也不会丢失。 如果表的引擎类型是 InnoDB，那么这条记录的 ID 就是 2。因为 InnoDB表把自增主键的最大ID记录到内存中
MySQL-基础
云上道人的博客
10-29 779
数据库事务 数据库事务具有ACID特点 1.Atomic（原子性） 事务是一个原子操作单元，不可分割，即事务中包含的所有操作要么都做，要么都不做，不会出现一半成功，一半失败的情况。 2.Consistency（一致性） 事务操作前和操作后都必须满足业务规则约束，使得数据库从一个一致性状态变到另一个一致性状态。比如A向B转账，...
关于在 MySQL 排序中使用索引这件事！
最新发布
江南一点雨的专栏
05-12 2156
我们来梳理下：只有当索引的顺序和 order by 子句的顺序完全一致，并且所有列的排序方向也都一致的情况下，MySQL 才能通过索引来对结果进行排序，同时，如果是联合索引，order by 子句也需要满足最左匹配原则。可以看到，在这个查询中，优化器将 ta 表作为了第一张表，tc 表作为了第二张表，那么根据前面的结论，如果使用第一个表中的索引排序，就会用到索引排序，第二张表的则用不了，我们来验证一下。如果是第二张表的索引，就没有用到索引排序，如果两张表的索引都用了，也不会使用索引排序。
【mysql表ID重新排序】
A_guilei的博客
12-19 2669
无特殊情况 最好不用---truncatetruncate无法通过binlog回滚。truncate会清空所有数据且执行速度很快。truncate不能对有外键约束引用的表使用。执行truncate需要drop权限，不建议给账号drop权限。执行truncate前一定要再三检查确认，最好提前备份下表数据。
MySQL性能优化：覆盖索引与SQL_NO_CACHE实战
"Mysql性能优化案例研究-覆盖索引和SQL_NO_CACHE" 在数据库管理中，性能优化是一项至关重要的任务，特别是在处理大量数据时。本文将深入探讨MySQL性能优化中的两个关键概念：覆盖索引和SQL_NO_CACHE指令，并通过一...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值