迹忆客 专注技术分享

本篇文章介绍如何在 MySQL 中重建索引。

MySQL重建索引

MySQL 处理数据类型和字符，以便 MySQL 的索引或表可以发生变化。 这就是为什么有时我们需要重建 MySQL 中的索引。

重建索引的方法有几种，其中少数会修复或重建整个数据库或表，其中只有REINDEX是MySQL中的一种数据结构，只能用于重建索引。

我们可以使用 REINDEX 为一个或多个列重建索引，使表性能更好，访问数据更快。

每当数据库损坏或需要修复时，就需要使用 REINDEX 重建索引或修复数据库或表。

MySQL 的大部分索引，包括 PRIMARY KEY、UNIQUE、INDEX、FULLTEXT 和 REINDEX，都存储在 B 树中，B 树是一种自平衡数据结构树，以特定方式存储数据。

在使用 REINDEX 重建索引或重建数据库或表之前，我们首先需要创建一个包含表和索引的数据库。 本教程演示了用于在 MySQL 中重建索引的不同方法。

创建表和索引

现在让我们尝试一个使用 REINDEX 重建索引的例子，但在重建索引之前，我们首先必须创建索引，所以让我们从它开始。

1. 首先，创建索引的语法是：

   CREATE INDEX [IndexName] ON [TableName] ([ColumnName]);
   

2. 现在，让我们创建一个表

   create table Employee(id integer, name varchar(100), position varchar(100));
   insert into Employee(id, name, position) values(1, "Sheeraz", "SeniorDeveloper");
   insert into Employee(id, name, position) values(2, "John", "SeniorDeveloper");
   insert into Employee(id, name, position) values(3, "Jack", "JuniorDeveloper");
   select * from Employee;
   

   上面的代码将创建一个三行三列的表格。 查看输出：

   id    name    position
   1    Sheeraz    SeniorDeveloper
   2    John    SeniorDeveloper
   3    Jack    JuniorDeveloper
   

3. 对于 position，有不止一位 Senior Developer。 我们可以根据位置选择数据，也可以解释一下。看例子：

   SELECT id, name FROM Employee WHERE Position= "SeniorDeveloper";
   EXPLAIN SELECT id, name FROM Employee WHERE Position= "SeniorDeveloper";
   

   第一个查询将选择位置为 SeniorDeveloper 的数据，第二个查询将解释 MySQL 如何执行该操作。 查看输出：

   id    name
   1    Sheeraz
   2    John
   id    select_type    table    partitions    type    possible_keys    key    key_len    ref    rows    filtered    Extra
   1    SIMPLE    Employee    NULL    ALL    NULL    NULL    NULL    NULL    3    33.33    Using where
   

4. 现在，正如我们所见，表中有不止一位高级开发人员，因此我们为该列创建一个索引，然后解释上面的查询以查看不同之处。 看例子：

   CREATE INDEX position ON Employee(Position);
   EXPLAIN SELECT id, name FROM Employee WHERE Position= "SeniorDeveloper";
   

   现在创建索引后，解释查询的输出为：

   id    select_type    table    partitions    type    possible_keys    key    key_len    ref    rows    filtered    Extra
   1    SIMPLE    Employee    NULL    ref    position    position    403    const    1    100.00    NULL
   

   我们可以看到，创建索引位置后，类型 possible_keys 被更改为位置； 还为其分配了一个键。 我们还可以通过以下查询显示索引：

   SHOW INDEXES FROM Employee;
   

   此查询的输出是：

   Table    Non_unique    Key_name    Seq_in_index    Column_name    Collation    Cardinality    Sub_part    Packed    Null    Index_type    Comment    Index_comment    Visible    Expression
   Employee    1    position    1    position    A    NULL    NULL    NULL    YES    BTREE            YES    NULL
   

使用 REINDEX 重建索引

现在，无论如何，如果在创建索引后，数据库或表损坏了，我们可以使用 REINDEX 重建索引，这将有助于我们减少内存并提高 IO 效率。

REINDEX 的语法是：

# To REINDEX the whole database
REINDEX DATABASE [DatabaseName];
# To REINDEX the Table
REINDEX TABLE [TableName];
# To REINDEX the Particular column
REINDEX TABLE [TableName]([ColumnName);
# To REINDEX the INDEX
REINDEX INDEX [Index_Name];

在这里，我们可以使用 REINDEX 或 OPTIMIZE 来重建损坏的索引。 看例子：

REINDEX TABLE Employee(position);

SHOW INDEXES FROM Employee;

或者

OPTIMIZE TABLE Employee;

SHOW INDEXES FROM Employee;

以上两个查询都将重建表的索引。

输出：

Table    Op    Msg_type    Msg_text
test.Employee    optimize    status    OK
Table    Non_unique    Key_name    Seq_in_index    Column_name    Collation    Cardinality    Sub_part    Packed    Null    Index_type    Comment    Index_comment    Visible    Expression
Employee    1    position    1    position    A    1    NULL    NULL    YES    BTREE            YES    NULL

使用 REPAIR TABLE 方法重建索引

除了 REINDEX，我们还可以修复整个表，这也会重建表中的索引。 有一个简单的查询来执行它。

REPAIR TABLE Employee;

SHOW INDEXES FROM Employee;

上面的代码会修复表，重建索引，然后显示索引的信息。 查看输出：

Table    Op    Msg_type    Msg_text
test.Employee    repair    status    OK
Table    Non_unique    Key_name    Seq_in_index    Column_name    Collation    Cardinality    Sub_part    Packed    Null    Index_type    Comment    Index_comment    Visible    Expression
Employee    1    position    1    position    A    1    NULL    NULL    YES    BTREE            YES    NULL

使用 InnoDB 的 ALTER TABLE 方法重建索引

我们还可以使用 ALTER TABLE 查询使用 InnoDB 引擎重建损坏的表和索引。 这是执行该操作的简单查询。

ALTER TABLE Employee ENGINE = InnoDB;

SHOW INDEXES FROM Employee;

该代码将使用 ALTER 使用 InnoDB 引擎重建表和索引。

输出：

Table    Non_unique    Key_name    Seq_in_index    Column_name    Collation    Cardinality    Sub_part    Packed    Null    Index_type    Comment    Index_comment    Visible    Expression
Employee    1    position    1    position    A    2    NULL    NULL    YES    BTREE            YES    NULL