mysql重置表索引_MySQL如何进⾏索引重建操作？
在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_n
ame='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)
mysql操作官方文档在另外⼀个窗⼝，我们发现索引⽂件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 My
DB]# 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 ro
ws 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⽅法
参考资料：
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论