rebuild index online和create index online及没有online的区别

最新推荐文章于 2025-08-13 21:48:18 发布

转载最新推荐文章于 2025-08-13 21:48:18 发布 · 143 阅读

加 online时：存在DDL锁，但是没有DML锁
不加 online , DDL锁和DML锁都有

---------------------------------------------

普通的create index，如果没有online，加的则是级别为4的S类型TM锁，会阻塞DML语句加在表上面的类型为RX的TM锁。

刚开始要先加TM4锁的，过程中是TM2锁。DDL 3锁一直都需要。

http://www.itpub.net/showthread.php?threadid=776273&pagenumber=

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/35489/viewspace-84663/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/35489/viewspace-84663/

确定要放弃本次机会？

福利倒计时

: :

立减 ¥

普通VIP年卡可用

立即使用

cuixie2370

关注关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
分享

复制链接

分享到 QQ

分享到新浪微博

扫一扫
举报

举报

MySQL大表结构变更利器：pt-online-schema-change原理与实战指南

专注于编程、AI、人工智能等多个领域内容研究和分析

05-30

653

MySQL大表结构变更利器：pt-online-schema-change原理与实战指南

create index online 与rebuild index online

cijiangen0848的博客

08-01

155

加 online时：DDL锁是有的，但是没有DML锁不加 online ,DDL锁和DML锁都有他们加的锁的级别不一样，是一个级别为2的RS锁不阻塞级别为3的RX锁，也就是 dml时候加在表上的TM锁 ...

参与评论您还未登录，请先登录后发表或查看评论

【罗玄】从锁的角度看rebuild index online和rebuild index

ctpq29224的博客

06-14

227

众所周知，rebuild index online不会阻塞DML操作，而rebulid index却会阻塞DML操作。现在我们来看一下在rebuild index online及rebulid index下的锁 os: rhe...

关于rebuild index online 及drop index后重建问题

cuixie2370的博客

12-12

210

rebuildindex .. 当我们对索引进行rebuild时,如果不加online选项,oracle则直接读取原索引的数据;当我们添加online选项时, oracle是直接扫描表中的数据,维护...

Oracle的online index rebuild

bisal的专栏

09-08

1615

看到墨天轮上翔宇老师写的的这篇文章《online index rebuild的操作》，介绍了Oracle中在线创建索引涉及到的相关信息，对了解原理很有帮助，P.S. https://www.modb.pro/db/174945?sjhyonline index build(online create或者rebuild index)是Oracle的一个非常常用的online操作，我们知道当创建索引或...

rebuild index & rebuild index online [final]

cuixie2370的博客

04-28

279

Rebuild index : rebuild时也会使用临时表空间ask tom上关于rebuild index 有这么一段话：If you need to rebuild your indexes, you need ...

索引：Rebuild Online 被坑的知识点-ORA-08106

tworain

01-25

1102

最近一直通过rebuild online 做索引碎片整理，因表均为分区表，大部分为本地索引及分区索引，有的表涉及上千个索引分区，于是我就用脚本放在后台执行了。分区从凌晨4：29开始到中午12点都还没有结果，意识到执行不正常，查看了下索引涉及到的数据有数据正常入表，数据也可查询，目前看不影响此表的dml操作。注：以下结果是被验证N次后，Rebuild Online 和 Insert 执行1小时以上再Kill会话，才会偶尔出现。【注1】：插入表任务影响到了rebuild online操作。

MySQL 5.7.32 Online ddl使用注意事项

sinat_36757755的博客

04-03

2320

MySQL从5.6开始有了Online ddl功能，有效的解决了DDL操作的痛点，特别是针对一些大表的DDL操作，大幅度的减少ddl操作对应用的影响，但 Online ddl不是一个完美的功能，它仍然会对数据库产生影响，所以我们需要了解使用Onlineddl的注意事项。

MySQL系列---Online DDL

lipviolet的博客

10-13

2556

适用于mysql的5.7及以上的版本：增加、删除索引不会锁表，且删除索引速度非常快，即使表很大的情况下，无关CREATE INDEX语句还是ALTER TABLE语句，CREATE INDEX的内部执行逻辑也是ALTER TABLE。无限制条件的update语句也不会锁表：UPDATE jml_qrcode SET aaa = ‘22’;它的执行逻辑应该是逐行修改，总之不会锁表，最多锁行。修改字段默认值及修改字段限制也不会锁表，应该都是逐行；

pt-online-schema-change 全解析：MySQL 表结构变更的安全之道

LBJ_2016_1984_12_30

08-13

982

pt-online-schema-change 作为 Percona Toolkit 的核心工具，凭借 “无锁变更”“兼容性强”“风险可控” 等优势，成为 MySQL 表结构变更的首选方案之一。其最佳实践可总结为：小表（100 万行以下）可直接使用（InnoDB 表在 5.6 + 已支持在线 DDL），大表优先用 pt 工具。变更前必须测试：在测试环境模拟生产数据量，验证变更耗时和资源占用。全程监控：关注 CPU、IO、主从延迟及业务响应时间，发现异常及时暂停。

聊聊索引Index Rebuild和Rebuild Online（下）

weixin_30340353的博客

08-21

231

转载地址：http://blog.itpub.net/17203031/viewspace-1473163/ 3、使用10046诊断rebuild动作 10046诊断事件是我们经常用来使用跟踪事件，也是我们分析Oracle内部行为的最常用工具。下面笔者将用这个工具对rebuild动作进行检查。首先获取一下数据表T和索引IDX_T_ID的分区结构。 --数...

rebuild index VS. rebuild index online

Aamir 的专栏

03-19

1181

create index和rebuild index时都会对原表加4号锁，对obj$加3号锁，阻止对原表的DML操作。 create index online 和 rebuild index online 采用新增一张临时表来处理创建或者重建索引时对原表索引列的修改，这样就可以不用锁表，保证原表的dml操作在过程中可以正常执行。 rebuild的过程用户仍然可以走旧的索引来查询，索引重建索引需

rebuild index online阻塞情况分析

cpsv51884的博客

02-06

225

rebuild index online说是不阻塞DML语句的执行，但是在开始和结束阶段需要获得类型为4的TM锁，如果此时有另外事务执行没有及时提交，或者比较大则有可能阻塞rebuild index，下面来重现这...

rebuild index online的锁机制浅析(续)

weixin_33725270的博客

03-24

198

上篇文章介绍了Oracle10.2.0.4中rebuild index online的锁机制，在开始和结束的时候需要对表加一个模式为4的TM锁，导致在这两个时刻会短暂的阻塞DML。到了Oracle11g，这种情况有所变化，还是通过同样的实验来观察一下Oracle11g到底做出了怎样的改进，对于DBA来说又有怎样的好处。实验环境为Oracle11.1.0.6。 session...

index rebuild和rebuild online的区别

关注系统性能调优

09-01

2346

1. rebuild 会阻塞dml语句而rebuild online则不会。 2. rebuild online时系统会产生一个SYS_JOURNAL_xxx的IOT类型的系统临时日志表，所有rebuild online时索引的变化都记录在这个表中，当新的索引创建完成后，把这个表的记录维护到新的索引中去，然后drop掉旧的索引，rebuild online就完成了。

oracle rebuild online,关于rebuild index online和非online的区别？

weixin_42348880的博客

04-03

273

***********************************************alter index t_idx rebuild online***********************************************select name,online$,contents$,undofile#,undoblock#,blocksize,dflmaxext,dfl...

rebuild index online的锁机制浅析

phphot

11-22

1749

一般都说，rebuild index online不阻塞DML操作，这是相对于rebuildindex来说的，加上了online，只是在rebuild的期间不阻塞DML，但是在开始和结束阶段还是可能阻塞其他进程的DML的，要弄清楚到底是阻塞还是不阻塞，何处阻塞，为什么阻塞，还是要从锁的角度来分析。本文实验环境为Oracle 10.2.0.4。Oracle中的锁，一共有6两种模式：0

oracle rebuild online,rebuild online 请慎用

weixin_42517044的博客

04-03

2266

好多初级DBA很喜欢用reuildonline重建索引，他们认为这样做不会影响业务。曾经发生的几次故障，在业务用的时候，rebuild online索引，导致大部分业务无法使用，数据库hang.其实rebulid 索引很简单drop indexindex_nameAlter indexindex_namerebuild;Alter indexindex_namerebuild online;...

Rebuild Index 加online与不加online时的plan区别

congku8444的博客

03-21

130

This logic is a fast full index scan, FFS, and can be seen by running an explain plan on the alter index rebuild c...

rebuild index

最新发布

08-20

<think>我们正在讨论如何重建数据库索引。根据用户提供的引用和之前的问题，用户已经了解索引失效（UNUSABLE）的处理方法，现在想了解重建索引的具体方法。注意，用户的问题中提到了多个引用，包括Oracle和SQL Server的重建方法，但用户没有指定数据库类型。因此，我们需要综合回答，并区分不同数据库。根据引用[1]和[2]，重建索引是提高数据库性能的重要手段。引用[2]给出了一个具体的重建索引的SQL语句示例（针对主键索引）。引用[3]则介绍了SQL Server通过维护计划向导自动重建索引的方法。引用[4]提到了Oracle索引创建前的检查规范。由于用户之前的问题是关于Oracle索引失效（UNUSABLE）的，因此我们重点回答Oracle，同时兼顾SQL Server，因为用户引用了两种数据库的内容。重建索引的步骤： 1. 确定需要重建的索引：通过查询数据字典视图（如DBA_INDEXES, DBA_IND_PARTITIONS）来找到状态为UNUSABLE的索引（Oracle）或碎片化严重的索引（SQL Server）。 2. 选择重建方法：手动重建或自动重建（通过脚本或维护计划）。 3. 执行重建：使用ALTER INDEX ... REBUILD（Oracle）或ALTER INDEX ... REORGANIZE/REBUILD（SQL Server）。 4. 验证重建结果：再次检查索引状态或碎片情况。下面分别针对Oracle和SQL Server进行说明： ### Oracle数据库重建索引方法 1. **手动重建单个索引**： ```sql ALTER INDEX [schema.]index_name REBUILD [ONLINE] [TABLESPACE tablespace_name] [PARALLEL n]; ``` 选项说明： - `ONLINE`：在线重建，重建过程中允许对基表进行DML操作。 - `TABLESPACE`：指定重建索引存放的表空间，如果不指定则使用原来的表空间。 - `PARALLEL`：并行度，可以加快重建速度。 2. **重建分区索引的特定分区**： ```sql ALTER INDEX [schema.]index_name REBUILD PARTITION partition_name; ``` 3. **批量重建所有失效索引**：可以使用PL/SQL块来重建所有状态为UNUSABLE的索引： ```sql BEGIN FOR idx IN (SELECT owner, index_name FROM dba_indexes WHERE status = 'UNUSABLE') LOOP EXECUTE IMMEDIATE 'ALTER INDEX ' || idx.owner || '.' || idx.index_name || ' REBUILD ONLINE'; END LOOP; END; / ``` 4. **重建后的验证**：重建后，再次查询`DBA_INDEXES`和`DBA_IND_PARTITIONS`视图，确保索引状态变为VALID。 ### SQL Server数据库重建索引方法 1. **手动重建单个索引**： ```sql -- 重建索引 ALTER INDEX index_name ON table_name REBUILD; -- 重新组织索引（碎片较少时使用） ALTER INDEX index_name ON table_name REORGANIZE; ``` 2. **重建表中所有索引**： ```sql ALTER INDEX ALL ON table_name REBUILD; ``` 3. **使用维护计划向导自动重建索引（引用[3]）**：在SQL Server Management Studio中： - 右键点击“管理”->“维护计划”->“维护计划向导”。 - 输入计划名称，设置计划周期。 - 选择“重新生成索引”任务，选择数据库，然后完成向导。这样就会定期自动重建索引。 4. **使用脚本重建所有数据库的索引**： ```sql -- 生成重建所有索引的脚本 DECLARE @TableName VARCHAR(255) DECLARE @IndexName VARCHAR(255) DECLARE @SQL NVARCHAR(MAX) DECLARE TableCursor CURSOR FOR SELECT '[' + IST.TABLE_SCHEMA + '].[' + IST.TABLE_NAME + ']' AS TableName, '[' + SI.name + ']' AS IndexName FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS PS JOIN sys.indexes AS SI ON PS.object_id = SI.object_id AND PS.index_id = SI.index_id JOIN INFORMATION_SCHEMA.TABLES AS IST ON PS.object_id = OBJECT_ID(IST.TABLE_SCHEMA + '.' + IST.TABLE_NAME) WHERE PS.avg_fragmentation_in_percent > 30 -- 碎片率大于30%则重建 AND SI.index_id > 0 -- 排除堆 AND IST.TABLE_TYPE = 'BASE TABLE' OPEN TableCursor FETCH NEXT FROM TableCursor INTO @TableName, @IndexName WHILE @@FETCH_STATUS = 0 BEGIN SET @SQL = 'ALTER INDEX ' + @IndexName + ' ON ' + @TableName + ' REBUILD' PRINT @SQL -- EXEC sp_executesql @SQL -- 实际执行时去掉注释 FETCH NEXT FROM TableCursor INTO @TableName, @IndexName END CLOSE TableCursor DEALLOCATE TableCursor ``` 5. **验证重建结果**：可以通过以下查询查看索引碎片情况： ```sql SELECT DB_NAME() AS DatabaseName, OBJECT_NAME(IPS.object_id) AS TableName, SI.name AS IndexName, IPS.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS IPS JOIN sys.indexes AS SI ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id WHERE IPS.avg_fragmentation_in_percent > 0 ``` ### 注意事项 1. **重建时机**：在业务低峰期进行，避免影响正常业务。 2. **资源消耗**：重建索引会消耗大量I/O和CPU资源，特别是大表索引。使用并行（Oracle）或分步执行（SQL Server）可以减轻影响。 3. **空间要求**：重建索引需要额外的临时空间，确保表空间（Oracle）或文件组（SQL Server）有足够空间。 4. **锁机制**：在Oracle中，使用`ONLINE`选项可以最小化锁的影响；在SQL Server中，重建索引（REBUILD）会锁表，而重新组织（REORGANIZE）是联机操作，但速度较慢。 5. **监控**：重建后，应监控数据库性能，确认索引重建的效果。通过以上步骤，您可以有效地重建数据库索引，恢复数据库性能[^1][^2][^3][^4]。 ### 相关问题 1. 如何监控数据库索引的碎片情况？ 2. 重建索引和重组索引有什么区别？ 3. 在Oracle中，重建索引时有哪些可用的高级选项？ 4. 在SQL Server中，如何设置自动维护索引的任务？</think>### 数据库索引重建方法索引重建是解决索引失效、碎片化或性能下降的核心手段。以下是不同数据库的重建方法，结合最佳实践和注意事项： --- ### **一、通用重建方法（手动执行）** #### **1. 单索引重建** ```sql -- Oracle ALTER INDEX 索引所有者.索引名 REBUILD [ONLINE] [PARALLEL 4]; -- SQL Server ALTER INDEX 索引名 ON 表名 REBUILD; -- MySQL ALTER TABLE 表名 ENGINE=InnoDB; -- 重建表（含索引）或 OPTIMIZE TABLE 表名; -- 优化表（重建索引） ``` #### **2. 批量重建失效索引** - **Oracle**（重建所有UNUSABLE索引）： ```sql BEGIN FOR idx IN (SELECT owner, index_name FROM dba_indexes WHERE status='UNUSABLE') LOOP EXECUTE IMMEDIATE 'ALTER INDEX ' || idx.owner || '.' || idx.index_name || ' REBUILD ONLINE'; END LOOP; END; / ``` - **SQL Server**（重建所有碎片化索引）： ```sql DECLARE @SQL NVARCHAR(MAX) = ''; SELECT @SQL += 'ALTER INDEX ' + name + ' ON ' + OBJECT_NAME(object_id) + ' REBUILD; ' FROM sys.indexes WHERE avg_fragmentation_in_percent > 30; -- 碎片率>30%时重建 EXEC sp_executesql @SQL; ``` #### **3. 分区索引重建** ```sql -- Oracle（重建特定分区） ALTER INDEX 索引名 REBUILD PARTITION 分区名; -- SQL Server ALTER INDEX 索引名 ON 表名 REBUILD PARTITION = 分区号; ``` --- ### **二、自动化重建（维护计划）** #### **1. SQL Server 维护计划向导** 1. 打开 SSMS → 右键 **`管理`** → **`维护计划`** → **`维护计划向导`** 2. 命名计划 → 设置执行周期（如每周日凌晨） 3. 选择 **`重新生成索引`** 任务 → 配置数据库范围 4. 完成向导（自动生成作业）[^3] #### **2. Oracle 定时任务** ```sql BEGIN DBMS_SCHEDULER.CREATE_JOB( job_name => 'idx_rebuild_job', job_type => 'PLSQL_BLOCK', job_action => 'BEGIN FOR idx IN (SELECT owner, index_name FROM dba_indexes WHERE status=''UNUSABLE'') LOOP EXECUTE IMMEDIATE ''ALTER INDEX '' || idx.owner || ''.'' || idx.index_name || '' REBUILD ONLINE''; END LOOP; END;', start_date => SYSTIMESTAMP, repeat_interval => 'FREQ=WEEKLY; BYDAY=SUN; BYHOUR=2', enabled => TRUE ); END; / ``` --- ### **三、关键注意事项** 1. **在线重建**： - 添加 **`ONLINE`** 选项（Oracle/SQL Server）可避免锁表，减少业务中断。 - 示例：`ALTER INDEX idx_name REBUILD ONLINE;` 2. **并行加速**： - 大表索引使用 **`PARALLEL`**（Oracle）或 **`MAXDOP`**（SQL Server）： ```sql -- Oracle ALTER INDEX idx_name REBUILD PARALLEL 8; -- SQL Server ALTER INDEX idx_name ON 表名 REBUILD WITH (ONLINE=ON, MAXDOP=4); ``` 3. **空间与资源**： - 确保表空间有足够空间（Oracle 检查 `DBA_FREE_SPACE`）。 - 避免高峰时段操作，监控 CPU/I/O 使用率。 4. **重建前检查**： - 确认索引状态（`UNUSABLE` 或碎片率）： ```sql -- Oracle 碎片检查 SELECT index_name, status FROM dba_indexes WHERE table_name='表名'; -- SQL Server 碎片检查 SELECT name, avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL); ``` --- ### **四、重建后验证** 1. **状态检查**： ```sql -- Oracle SELECT index_name, status FROM dba_indexes WHERE status='UNUSABLE'; -- 应返回空 -- SQL Server SELECT name, avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) WHERE avg_fragmentation_in_percent > 10; -- 碎片率应<10% ``` 2. **性能测试**： - 执行高频查询，对比重建前后的执行计划（如 `EXPLAIN PLAN`）。 --- ### **五、预防索引失效的建议** 1. **监控机制**： - 定期查询索引状态（如每日 Job）。 2. **避免高风险操作**： - `ALTER TABLE MOVE`（Oracle）、`TRUNCATE PARTITION` 后立即重建索引。 3. **统计信息维护**： - Oracle: `DBMS_STATS.GATHER_TABLE_STATS` - SQL Server: 开启 `AUTO_UPDATE_STATISTICS`[^1][^4] > **提示**：对于关键业务表，优先使用 **`ONLINE`** 重建。超大表建议分批操作（如按分区重建）。 --- ### 相关问题 1. 如何检测数据库索引的碎片化程度？ 2. 在线重建索引(ONLINE)和离线重建的性能差异是什么？ 3. 哪些操作会导致索引失效？如何避免？ 4. 如何为海量表设计高效的索引维护策略？ [^1]: 重建索引是提高数据库性能的重要手段，需结合监控和资源管理[^1]。 [^2]: 手动重建索引的语法示例[^2]。 [^3]: SQL Server 维护计划向导实现自动化重建[^3]。 [^4]: 索引创建前需检查表大小和列分布[^4]。