MySQL索引重建?如何操作？

SEian.G

发布于 2021-11-15 16:38:44

3.7K0

发布于 2021-11-15 16:38:44

文章被收录于专栏：SEian.G学习记录SEian.G学习记录

两周没有更新文章了，最近一直在忙”人生大事”，毕竟人这一生，除了工作、上班还有其他几件重要的事情，而且也是每个人都必须要经历的，走完了，也就走完了……

言归正传，在日常数据库管理中，经常会遇到索引重建的需求，针对MySQL数据库，不像Oracle数据库中有支持索引重建的语法（ALTER INDEX … REBUILD），在MySQL数据库中，重建索引具体有哪些方案呢？

在官方文档中提供如下的三种方案：

Dump and Reload Method
ALTER TABLE Method
REPAIR TABLE Method

下面我们针对官方提供的三种方案进行简单的验证

测试环境：MySQL 8.0.19

mysql>CREATE TABLE t1 (
    -> c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> c2 VARCHAR(100),
    -> c3 VARCHAR(100) )
    -> ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)
 
mysql>create index ix_c2 on t1(c2);   
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql>SELECT table_name,create_time FROM information_schema.TABLES WHERE table_name='t1';
+------------+---------------------+
| TABLE_NAME | CREATE_TIME         |
+------------+---------------------+
| t1         | 2021-11-09 13:48:48 |
+------------+---------------------+
1 rows in set (0.00 sec)

一、Dump and Reload Method

mysqldump导出然后重新导入，相当于重新CREATE TABLE、CREATE INDEXES ，这里就不讨论了。相信大家也非常的熟悉了；

二、 ALTER TABLE Method

在验证之前，我们需要先知道如何可以查看MySQL中找到索引的创建或修改时间，可以通过如下的方式间接的进行查看：

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

2、对应表的idb文件的修改或创建时间，当然这种方法不是非常准确。对于InnoDB存储引擎的表而言，对应的索引数据存储在ibd文件中，所以文件的创建时间或修改时间是间接判断索引创建时间。如果存储引擎为MyISAM的话，还有专门的索引文件MYI。

下面开始简单验证：

mysql>SELECT table_name,create_time FROM information_schema.TABLES WHERE table_name='t1';
+------------+---------------------+
| TABLE_NAME | CREATE_TIME         |
+------------+---------------------+
| t1         | 2021-11-09 13:48:48 |
+------------+---------------------+
1 rows in set (0.00 sec)
 
mysql>ALTER TABLE t1 ENGINE = InnoDB;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
 
mysql>SELECT table_name,create_time FROM information_schema.TABLES WHERE table_name='t1';
+------------+---------------------+
| TABLE_NAME | CREATE_TIME         |
+------------+---------------------+
| t1         | 2021-11-09 13:51:40 |
+------------+---------------------+
1 rows in set (0.00 sec)

从上述的测试结果可以发现，CREATE_TIME 时间发生了变化，从2021-11-09 13:48:48变成了2021-11-09 13:51:40；如上所示，其实ALTER TABLE t1 ENGINE=InnoDB 其实等价于REBUILD表（REBUILD表就是重建表的意思），所以索引也等价于重新创建了。

我们对比t1.ibd的创建时间，也可以间接验证了表和索引都REBUILD了。我这里测试环境是MySQL 8.0.19，如果是之前的版本，还有frm之类的文件；

执行ALTER之前：
# ll t1.ibd
-rw-r----- 1 mysql mysql 131072 Nov  9 13:48 t1.ibd
 
# stat t1.ibd
  File: ‘t1.ibd’
  Size: 131072          Blocks: 224        IO Block: 4096   regular file
Device: fd02h/64770d    Inode: 107         Links: 1
Access: (0640/-rw-r-----)  Uid: ( 1001/   mysql)   Gid: ( 1001/   mysql)
Access: 2021-11-09 13:48:37.205682728 +0800
Modify: 2021-11-09 13:48:48.518681932 +0800
Change: 2021-11-09 13:48:48.518681932 +0800
Birth: -
 
执行ALTER之后：
# ll t1.ibd  
-rw-r----- 1 mysql mysql 131072 Nov  9 13:51 t1.ibd
 
# stat t1.ibd
  File: ‘t1.ibd’
  Size: 131072          Blocks: 224        IO Block: 4096   regular file
Device: fd02h/64770d    Inode: 7478793     Links: 1
Access: (0640/-rw-r-----)  Uid: ( 1001/   mysql)   Gid: ( 1001/   mysql)
Access: 2021-11-09 13:51:40.115669853 +0800
Modify: 2021-11-09 13:51:40.535669823 +0800
Change: 2021-11-09 13:51:40.535669823 +0800
Birth: -

三、REPAIR TABLE Method

REPAIR TABLE方法用于修复被破坏的表，而且它仅仅能用于MyISAM, ARCHIVE,CSV类型的表。如下是官方文档描述：

The REPAIR TABLE method is only applicable to MyISAM, ARCHIVE, and CSV tables.

如果表是innodb表的话，就会出现如下提示该存储引擎不支持repair操作

mysql>REPAIR TABLE t1;
+------------+--------+----------+---------------------------------------------------------+
| Table      | Op     | Msg_type | Msg_text                                                |
+------------+--------+----------+---------------------------------------------------------+
| wjqtest.t1 | repair | note     | The storage engine for the table doesn't support repair |
+------------+--------+----------+---------------------------------------------------------+
1 row in set (0.00 sec)

由于我们现在基本都是innodb引擎表，所以repair操作不在验证，感兴趣的话，大家可以自行进行验证；

上面三种就是官方文档提供的方法，但是在在实际运维管理中，OPTIMIZE TABLE操作也是支持重建索引的，

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

OPTIMIZE TABLE uses online DDL for regular and partitioned InnoDB tables, which reduces downtime for concurrent DML operations. The table rebuild triggered by OPTIMIZE TABLE is 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 TABLE操作使用Online DDL模式修改Innodb普通表和分区表，这样减少了并发DML操作的停机时间；该方式会在prepare阶段和commit阶段持有表级锁：在prepare阶段修改表的元数据并且创建一个中间表，在commit阶段提交元数据的修改。由于prepare阶段和commit阶段在整个事务中的时间比例非常小，可以认为该OPTIMIZE TABLE的过程中不影响表的其他并发操作。

下面简单进行验证：

mysql>OPTIMIZE TABLE t1;
+------------+----------+----------+-------------------------------------------------------------------+
| Table      | Op       | Msg_type | Msg_text                                                          |
+------------+----------+----------+-------------------------------------------------------------------+
| wjqtest.t1 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| wjqtest.t1 | optimize | status   | OK                                                                |
+------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.04 sec)
 
 
mysql>SELECT table_name,create_time FROM information_schema.TABLES WHERE table_name='t1';
+------------+---------------------+
| TABLE_NAME | CREATE_TIME         |
+------------+---------------------+
| t1         | 2021-11-09 13:53:53 |
+------------+---------------------+
1 rows in set (0.00 sec)

从上述结果可以看到，表的创建时间变成了2021-11-09 13:53:53；

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

mysql>ANALYZE TABLE t1;
+------------+---------+----------+----------+
| Table      | Op      | Msg_type | Msg_text |
+------------+---------+----------+----------+
| wjqtest.t1 | analyze | status   | OK       |
+------------+---------+----------+----------+
1 row in set (0.01 sec)
 
 
mysql>SELECT table_name,create_time FROM information_schema.TABLES WHERE table_name='t1';
+------------+---------------------+
| TABLE_NAME | CREATE_TIME         |
+------------+---------------------+
| t1         | 2021-04-13 11:30:49 |
| t1         | 2021-11-09 13:53:53 |
+------------+---------------------+
2 rows in set (0.00 sec)
 
# ll t1.ibd  
-rw-r----- 1 mysql mysql 131072 Nov  9 13:53 t1.ibd
 
# stat t1.ibd
  File: ‘t1.ibd’
  Size: 131072          Blocks: 224        IO Block: 4096   regular file
Device: fd02h/64770d    Inode: 107         Links: 1
Access: (0640/-rw-r-----)  Uid: ( 1001/   mysql)   Gid: ( 1001/   mysql)
Access: 2021-11-09 13:53:53.468660465 +0800
Modify: 2021-11-09 13:53:53.548660460 +0800
Change: 2021-11-09 13:53:53.548660460 +0800
Birth: -

在实际的工作中，我们经常会delete数据，我们都知道，delete操作不会立即回收空间，反而频繁的delete操作会产生大量的磁盘碎片，这时候可能会影响到执行计划等，所以我们就需要整理磁盘碎片，通过就会用到上述几种方式的操作；

好了今天就先介绍这些吧；

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2021-11-10，如有侵权请联系 cloudcommunity@tencent.com 删除

官方文档