MySQL如何進行索引重建操作？-有解無憂

在MySQL資料庫中,沒有類似于SQL Server資料庫或Oracle資料庫中索引重建的語法（ALTER INDEX ... REBUILD），那么在MySQL資料庫中，是否有什么方式重建索引呢？在官方檔案中"2.11.10 Rebuilding 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檔案的修改或創建時間（若檔案從創建后不曾修改過則可認為創建時間=修改時間，關于更多詳細內容，參考“Linux如何查找檔案的創建時間”），當然這種方法不是非常準確，我們知道，對于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表就是重建表的意思），所以索引也等價于重新創建了，

clip_image001

在另外一個視窗，我們對比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.ibd

  File: ‘t1.ibd’

  Size: 131072          Blocks: 224        IO Block: 4096   regular file

Device: fd00h/64768d    Inode: 106665154   Links: 1

Access: (0640/-rw-r-----)  Uid: ( 1000/   mysql)   Gid: ( 1000/   mysql)

Context: system_u:object_r:mysqld_db_t:s0

Access: 2019-10-20 08:18:25.911990445 +0800

Modify: 2019-10-20 08:18:33.626989940 +0800

Change: 2019-10-20 08:18:33.626989940 +0800

 Birth: -

[root@db-server MyDB]# stat t1.ibd

  File: ‘t1.ibd’

  Size: 131072          Blocks: 224        IO Block: 4096   regular file

Device: fd00h/64768d    Inode: 106665156   Links: 1

Access: (0640/-rw-r-----)  Uid: ( 1000/   mysql)   Gid: ( 1000/   mysql)

Context: system_u:object_r:mysqld_db_t:s0

Access: 2019-10-20 08:20:50.866980953 +0800

Modify: 2019-10-20 08:20:51.744980896 +0800

Change: 2019-10-20 08:20:51.744980896 +0800

 Birth: -

clip_image002

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)

clip_image003

在另外一個視窗，我們發現索引檔案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.MYI

  File: `t.MYI'

  Size: 1024            Blocks: 8          IO Block: 4096   regular file

Device: fd00h/64768d    Inode: 1836747     Links: 1

Access: (0660/-rw-rw----)  Uid: (   27/   mysql)   Gid: (   27/   mysql)

Access: 2019-10-20 08:36:02.395428301 +0800

Modify: 2019-10-20 08:35:43.112562600 +0800

Change: 2019-10-20 08:35:43.112562600 +0800

[root@testlnx02 MyDB]# stat t.MYI

  File: `t.MYI'

  Size: 1024            Blocks: 8          IO Block: 4096   regular file

Device: fd00h/64768d    Inode: 1836747     Links: 1

Access: (0660/-rw-rw----)  Uid: (   27/   mysql)   Gid: (   27/   mysql)

Access: 2019-10-20 08:37:19.686899429 +0800

Modify: 2019-10-20 08:37:10.271475420 +0800

Change: 2019-10-20 08:37:10.271475420 +0800

OPTIMIZE TABLE方法

OPTIMIZE TABLE也可以對索引進行重建，官方檔案的介紹如下：

OPTIMIZE TABLE reorganizes 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 the storage engine used by that 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 and performed under the cover by ALTER TABLE ... FORCE 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 rebuilds the table using the table copy method under the following conditions:

· When the old_alter_table system variable is enabled.

· When the server is started with the --skip-new option.

OPTIMIZE TABLE using online DDL is 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

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/116977.html

標籤：MySQL

上一篇：“GIS DICTIONARY A-Z” 查詢頁面開發（2）——INSERT INTO資料庫

下一篇：MySQL truncate含有外鍵約束的條目報錯