mysql 重置表索引_MySQL如何进行索引重建操作？

最新推荐文章于 2024-05-15 08:42:46 发布
最新推荐文章于 2024-05-15 08:42:46 发布
阅读量1.8k 收藏
点赞数 1
文章标签： mysql 重置表索引
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_39644494/article/details/111531925
版权

在MySQL数据库中,没有类似于SQL Server数据库或Oracle数据库中索引重建的语法(ALTER INDEX ... REBUILD)，那么在MySQL数据库中，是否有什么方式重建索引呢？ 在官方文档中"2.11.10Rebuilding or Repairing Tables or Indexes"中，提到下面三种方式可以Rebuild Index

·Dump and Reload Method

·ALTER TABLE Method

·REPAIR TABLE Method

另外， OPTIMIZE TABLE也会对索引进行重建，下面我们来简单验证、测试一下，如有不对或不足的地方，敬请指正。

第一种方法(mysqldump导出然后重新导入)，相当于重新CREATE INDEXES ， 这里就不讨论了。下面我们来看看其它几种方法，那么要判断索引是否REBUILD了呢？我们来测试验证一下吧，新建测试表如下：

CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,c2 VARCHAR(100),c3 VARCHAR(100) )ENGINE=InnoDB;create index ix_t1_c2 on t1(c2);

DROP INDEX + CREATE INDEX方法

这种方法过于简单，这里不叙说了。其实也没有啥好说的。

ALTER TABLE方法

那么我们能否在MySQL中找到索引的创建或修改时间呢？经过查证，目前而言，MySQL中是没有相关系统表或视图会记录索引的创建时间的，我们可以用间接的方法来间接验证，有些方法不是特别可靠和准确，最准确的方法应该是阅读源码：

1：表的创建时间，可以间接推断索引的创建时间。因为索引的创建时间肯定在表的创建时间之后。

2：对应表的idb文件的修改或创建时间(若文件从创建后不曾修改过则可认为创建时间=修改时间，关于更多详细内容，参考”)，当然这种方法不是非常准确。我们知道，对于InnoDB存储引擎的表而言，对应的索引数据存储在ibd文件中，所以文件的创建时间或修改时间是间接判断索引创建时间。如果存储引擎为MyISAM的话，还有专门的索引文件MYI。

注意：show indexes from tablename不会显示索引创建时间

mysql> SELECT table_name,create_time FROM  information_schema.TABLES WHERE table_name='t1';+------------+---------------------+| TABLE_NAME | CREATE_TIME         |+------------+---------------------+| t1         | 2019-10-20 08:18:33 |+------------+---------------------+1 row in set (0.01 sec)

然后我们对表进行ALTER TABLE t1 ENGINE = InnoDB;进行操作后，然后去验证表的创建时间，如下所示，其实ALTER TABLE xxx ENGINE=InnoDB 其实等价于REBUILD表(REBUILD表就是重建表的意思)，所以索引也等价于重新创建了。

在另外一个窗口，我们对比t1.ibd的创建时间，如下所示，也间接验证了表和索引都REBUILD了。(这里是MySQL 8.0.18 ，如果是之前的版本，还有frm之类的文件。)

[root@db-server MyDB]# ls -lrt t1*-rw-r-----. 1 mysql mysql 131072 Oct 20 08:18 t1.ibd[root@db-server MyDB]# stat t1.ibdFile: ‘t1.ibd’Size: 131072          Blocks: 224        IO Block: 4096   regular fileDevice: fd00h/64768d    Inode: 106665154   Links: 1Access: (0640/-rw-r-----)  Uid: ( 1000/   mysql)   Gid: ( 1000/   mysql)Context: system_u:object_r:mysqld_db_t:s0Access: 2019-10-20 08:18:25.911990445 +0800Modify: 2019-10-20 08:18:33.626989940 +0800Change: 2019-10-20 08:18:33.626989940 +0800Birth: -[root@db-server MyDB]# stat t1.ibdFile: ‘t1.ibd’Size: 131072          Blocks: 224        IO Block: 4096   regular fileDevice: fd00h/64768d    Inode: 106665156   Links: 1Access: (0640/-rw-r-----)  Uid: ( 1000/   mysql)   Gid: ( 1000/   mysql)Context: system_u:object_r:mysqld_db_t:s0Access: 2019-10-20 08:20:50.866980953 +0800Modify: 2019-10-20 08:20:51.744980896 +0800Change: 2019-10-20 08:20:51.744980896 +0800Birth: -

REPAIR TABLE方法

REPAIR TABLE方法用于修复被破坏的表，而且它仅仅能用于MyISAM,ARCHIVE,CSV类型的表。下面的测试环境为MySQL 5.6.41，创建测试表，然后对表进行REPAIR TABLE操作

mysql> CREATE TABLE t (->     c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,->     c2 VARCHAR(100),->     c3 VARCHAR(100) )-> ENGINE=MyISAM;Query OK, 0 rows affected (0.01 sec)mysql> SELECT table_name,create_time FROM  information_schema.TABLES WHERE table_name='t';+------------+---------------------+| table_name | create_time         |+------------+---------------------+| t          | 2019-10-20 08:35:43 |+------------+---------------------+1 row in set (0.00 sec)

然后对表t进行修复操作，发现表的create_time没有变化，如下所示：

mysql> REPAIR TABLE t;+--------+--------+----------+----------+| Table  | Op     | Msg_type | Msg_text |+--------+--------+----------+----------+| MyDB.t | repair | status   | OK       |+--------+--------+----------+----------+1 row in set (0.01 sec)mysql> SELECT table_name,create_time FROM  information_schema.TABLES WHERE table_name='t';+------------+---------------------+| table_name | create_time         |+------------+---------------------+| t          | 2019-10-20 08:35:43 |+------------+---------------------+1 row in set (0.00 sec)

在另外一个窗口，我们发现索引文件t.MYI的修改时间和状态更改时间都变化了，所以判断索引重建(Index Rebuild)了。

[root@testlnx02 MyDB]# ls -lrt t.*-rw-rw----. 1 mysql mysql 8608 Oct 20 08:35 t.frm-rw-rw----. 1 mysql mysql 1024 Oct 20 08:35 t.MYI-rw-rw----. 1 mysql mysql    0 Oct 20 08:35 t.MYD[root@testlnx02 MyDB]# stat t.MYIFile: `t.MYI'Size: 1024            Blocks: 8          IO Block: 4096   regular fileDevice: fd00h/64768d    Inode: 1836747     Links: 1Access: (0660/-rw-rw----)  Uid: (   27/   mysql)   Gid: (   27/   mysql)Access: 2019-10-20 08:36:02.395428301 +0800Modify: 2019-10-20 08:35:43.112562600 +0800Change: 2019-10-20 08:35:43.112562600 +0800[root@testlnx02 MyDB]# stat t.MYIFile: `t.MYI'Size: 1024            Blocks: 8          IO Block: 4096   regular fileDevice: fd00h/64768d    Inode: 1836747     Links: 1Access: (0660/-rw-rw----)  Uid: (   27/   mysql)   Gid: (   27/   mysql)Access: 2019-10-20 08:37:19.686899429 +0800Modify: 2019-10-20 08:37:10.271475420 +0800Change: 2019-10-20 08:37:10.271475420 +0800

OPTIMIZE TABLE方法

OPTIMIZE TABLE也可以对索引进行重建，官方文档的介绍如下：

OPTIMIZE TABLEreorganizes the physical storage of table data and associated index data, to reduce storage space and improve I/O efficiency when accessing the table. The exact changes made to each table depend on thestorage engineused by that table.

OPTIMIZE TABLEusesonline DDLfor regular and partitioned InnoDB tables, which reduces downtime for concurrent DML operations. The table rebuild triggered byOPTIMIZE TABLEand performed under the cover byALTER TABLE ... FORCEis completed in place. An exclusive table lock is only taken briefly during the prepare phase and the commit phase of the operation. During the prepare phase, metadata is updated and an intermediate table is created. During the commit phase, table metadata changes are committed.

OPTIMIZE TABLErebuilds the table using the table copy method under the following conditions:

·

·When theold_alter_tablesystem variable is enabled.

·

·When the server is started with the--skip-newoption.

OPTIMIZE TABLEusingonline DDLis not supported for InnoDB tables that contain FULLTEXT indexes. The table copy method is used instead.

简单来说，OPTIMIZE TABLE操作使用Online DDL模式修改Innodb普通表和分区表，

该方式会在prepare阶段和commit阶段持有表级锁：在prepare阶段修改表的元数据并且创建一个中间表，在commit阶段提交元数据的修改。

由于prepare阶段和commit阶段在整个事务中的时间比例非常小，可以认为该OPTIMIZE TABLE的过程中不影响表的其他并发操作。

测试验证如下，对表t1做了OPTIMIZE TABLE后， 表的创建时间变成了2019-10-20 08:41:57

mysql> OPTIMIZE TABLE t1;+---------+----------+----------+-------------------------------------------------------------------+| Table   | Op       | Msg_type | Msg_text                                                          |+---------+----------+----------+-------------------------------------------------------------------+| MyDB.t1 | optimize | note     | Table does not support optimize, doing recreate + analyze instead || MyDB.t1 | optimize | status   | OK                                                                |+---------+----------+----------+-------------------------------------------------------------------+2 rows in set (0.67 sec)mysql> SELECT table_name,create_time FROM  information_schema.TABLES WHERE table_name='t1';+------------+---------------------+| TABLE_NAME | CREATE_TIME         |+------------+---------------------+| t1         | 2019-10-20 08:41:57 |+------------+---------------------+1 row in set (0.00 sec)

另外，网上有种说法ANALYZE TABLE方法也可以重建索引，其实ANALYZE TABLE是不会对索引进行重建的。测试验证的话，你会发现ibd文件没有变化，表的修改时间/状态更改时间也没有变化。

总结：

测试完后，还是感觉MySQL索引重建的方式怪怪的，可能是有先入为主的观念。总结一下MySQL索引重建的方法：

1： DROP INDEX + RECREATE INDEX.

2： ALTER TABLE方法

3： REPAIR TABLE方法,这种方法对于InnoDB存储引擎的表无效。

4： OPTIMIZE TABLE方法

参考资料：

https://dev.mysql.com/doc/refman/8.0/en/rebuilding-tables.html

https://docs.oracle.com/cd/E17952_01/mysql-5.6-en/rebuilding-tables.html

weixin_39644494
关注
MySQL索引重建和在线重建详解
DfsnVue的博客
09-25 766
在线重建则通过辅助的数据结构和算法，在线生成新的索引并逐步替换旧的索引，可以在不阻塞其他查询的情况下完成操作。它的原理是删除现有的索引并重新创建一个新的索引，以消除索引的碎片化问题。通过在线重建，可以实现索引的连续性和碎片化的解决，同时不会对正常的数据库操作产生明显的影响。需要注意的是，索引重建操作会锁定相关表，因此在执行期间可能会对数据库的正常操作产生一定的影响。需要注意的是，使用在线重建工具时，仍然会对数据库产生一定的负载，但相比于索引重建，它可以在不阻塞其他查询的情况下完成操作。
mysql rebuild index_SQLServer重建索引RebuildIndex
weixin_29482557的博客
02-07 538
重建索引是为了减少数据碎片。数据碎片会导致 SQL Server 进行不必要的数据读，降低 SQL Server 的性能。重建索引也会同时更新列统计，而如果查询所使用的列缺少或遗漏统计信息，这可能导致 SQL Server 内部的优化器选择比预期效率低的查询计划。 如果您重建重建索引是为了减少数据碎片。数据碎片会导致SQL Server进行不必要的数据读，降低SQL Server的性能。重建索引也...
Mysql 重建索引
毅呀毅呀哟
05-15 3056
这将执行与上述方法相同的任务，但是仅对存储引擎支持优化表的情况下才有效。该命令会锁定表，并在执行期间阻止对表进行更改。这将强制MySQL删除并重新构建表和索引。注意，这可能会导致数据丢失，因此请务必在执行此操作之前进行备份。无论哪种方法，都要谨慎考虑并确认备份数据，避免因误操作造成数据丢失。
MySQL如何重建索引？
最新发布
长风破浪会有时的博客
05-15 906
索引就像是我们书本后面的目录，它可以帮助我们快速地找到想要的内容，而不需要一页一页地翻书。在MySQL数据库中，索引也有类似的作用，它可以帮助我们快速地找到存储在数据库中的数据。当然，在实际操作中，我们可能还需要考虑一些其他因素，比如什么时候重建索引最合适（比如在数据库负载较低的时候），以及重建索引可能会对数据库性能产生什么影响（比如重建过程中可能会占用一些系统资源）。总的来说，重建MySQL中的索引就像是我们重新整理书本的目录一样，目的是为了让我们能够更快速、更高效地找到想要的数据。
Mysql在线添加索引
xiaoyi52的专栏
01-13 3856
在实际工作中，经常遇到需要给数据库表添加索引的情况。虽然操作是由dba来执行，但开发还是应该了解在线添加索引会引起的性能问题。比如博主最近就遇到了线上添加索引导致业务报警的问题。 问题描述 出于业务需要，给一个表添加普通索引，但这个表有100个分表，因此需要给100个分表都加上。平均每张表大概有500万行的数据量。线上业务流量也比较高。mysql版本为5.7版本，在线添加索引问题不大。跟dba商量好之后，提交sql脚本由dba实施。经实践发现，平均一张表添加索引大概需要3~5分钟。在添加完20多张表之后，
mysql sqlalchemy 索引_SQLAlchemy中指定想要使用的索引
weixin_35738834的博客
02-26 721
我们知道在MySQL中可以使用FORCE INDEX(index_name,...)来强制使用索引，那在SQLAlchemy如何指定呢？经过多方查找和实践，我发现了一个叫with_hint的函数。我们看下源码def with_hint(self, selectable, text, dialect_name="*"):r"""Add an indexing or other executional...
mysql什么情况索引会重排_MySQL 面试索引全篇，索引面试掌握这些就够了
weixin_39662462的博客
02-18 1213
问：可以说说聚集索引和非聚集索引区别吗？在 Mysql 中，有两大常用的存储引擎 MyisAm 和 Innodb ， Myisam 使用的是非聚集索引也叫非聚簇索引， InnoDb 使用的是聚集索引，聚集索引就是以主键创建的索引，非聚集索引就是除了主键以外的索引。非聚集索引在建立的时候也未必是单列的，可以多个列来创建索引，本质区别是表记录的排列顺序和索引的排列顺序是否一致。聚集索引的叶节点就是数据...
mysql 大表ddl_大型表的DDL操作（转）
weixin_30555169的博客
01-19 980
add by zhj: 关系型数据库有Schema，修改Schema的时间比较长，一般都要秒级和分钟级。主备延迟有相同的数量级，对于不支持Inplace方式的DDL，只能用Copy方式，Copy方式在执行DDL时，整个表不可读写，影响非常大。不过，对于MySQL5.6(尤其是MySQL5.7)，绝大部分常用的DDL都支持Inplace，DDL加MDL写锁的时间很短，所以主要评估主备延迟的影响就可以...
mysql重建索引
wocjj的专栏
03-01 6987
资料出处：http://www.desteps.com/program/database/1723.html MySQL 重建索引数据库操作命令，MySQL 除创建、删除和查询索引之外，还可以进行重建索引的操作。 1、重建索引命令 以下为引用内容：  mysql> REPAIR TABLE tbl_name QUICK; 2、查询数据表索引 以下为引用内容：  mysql> SHOW
MySQL修改表结构操作命令总结
09-10
在MySQL中，对数据库表结构进行修改是一项常见的任务，它涉及到数据库的设计优化或者为了满足新的业务需求。以下是对标题和描述中提及的各种修改表结构操作命令的详细解释： 1. **删除列**： 使用`ALTER TABLE`...
Mysql 索引重置
qq_25600055的专栏
02-16 1213
方法1 alter table table_name AUTO_INCREMENT=1 命令可以重置起始值 方法2 truncate table table_name 方法3 delete from table_name checkident(table_name,reseed,0)
MySQL重建或修复索引
为梦想奋斗
02-21 5826
在索引丢失或者恢复只有数据的备份时很有用。 mysql> repair table XXX USE_FRM;
MySQL重置索引列
热门推荐
wanglb465的博客
04-27 1万+
目前暂未发现可以直接重置索引列的方式，不过可以使用迂回战术来达到目的 1、删除原有的索引列 ALTER TABLE 表名 DROP COLUMN 索引列名, DROP PRIMARY KEY; 2、新增索引列 ALTER TABLE 表名 ADD COLUMN 索引列名 int(11) NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (`index`); 3、示例 -- 删除索引列 ALTER TABLE table_test DROP COL
Mysql 重置自动索引
天明破晓
01-03 1776
          由于删除了某些记录行，所以自增字段不连 续了 1，3，4，5，7，10这样子 若要重新恢复1，2，3，4，5，6，7连续排序只需要做： 方法1：truncate table 你的表名 方法2：delete from 你的表名 dbcc checkident(你的表名,reseed,0) //重新定位自增的字段，让它从1                 开始   ...
常用的sql优化方案
Rabir-YellowDavid的博客
09-16 141
1、使用连接（join）代替子查询，因为子查询会创建一个临时表。 2、不适用通配符（*），建议使用具体的字段名称，使用（**）,会查询数据库字段多了一次无意义的查询 3、合理使用索引
mysql rebuild index_MSSQL Rebuild(重建)索引
weixin_34593328的博客
01-19 881
前的项目是做数据库的归档，在每次archive后都需要对原数据库的索引进行rebuild，以减少索引碎片，于是乎就自己写了一段sql：DECLARE @tablename VARCHAR(50)DECLARE @indexname VARCHAR(50)DECLARE @cmdsql NVARCHAR(MAX)DECLARE index_cursor CURSORFORSELECT OBJECT...
【数据库】MySQL数据库索引重新排序（重置索引）
KingdomYuen的博客
07-18 1554
解决数据库因某些原因导致主索引不连贯的问题，让主索引重新排序 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 重新建立索引_mysql 重新建立索引的方法
weixin_39718460的博客
01-19 1333
本节内容：mysql索引重建方法操作方法：./myisamchk -r -q 路径/数据库/表名例如： 代码示例:./myisamchk -r -q ./../crawler/feedalter table feed Auto_increment=34909090alter table feed modify raw_url varchar(255) BINARY NOT NULL;首先，...
mysql rebuild index_批量rebuild索引
weixin_29455479的博客
01-19 574
自己写了一个批量rebuild索引的存储过程： create or replace procedure sp_rebuild_indexas v_count number; cursor empCur is select owner,index_name from dba_indexes where status='UNUSABLE'; v_sql varchar2(100); v_owner v...
重置mysql自增索引
weixin_42947972的博客
11-17 444
重置mysql自增索引。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值