MySQL 添加大表索引的缓慢问题

MySQL 添加大表索引耗时过长是许多开发人员和数据库管理员经常遇到的问题。本文将从以下几个方面讨论解决该问题的方法：

索引的原理及其类型
MySQL 添加索引的具体步骤
改进 MySQL 添加大表索引的性能

阅读更多：MySQL 教程

1. 索引的原理及其类型

索引是一种数据结构，用于快速检索表中的记录。它们类似于书籍的目录，使得查找某个特定记录变得更加高效。数据库的索引通常使用 B-Tree 或哈希表等数据结构实现。常见的索引类型包括：

PRIMARY KEY
UNIQUE
INDEX
FULLTEXT
SPATIAL

2. MySQL 添加索引的具体步骤

向 MySQL 数据库添加索引通常需要以下步骤：

使用 CREATE INDEX 语句创建索引
等待索引创建完成

例如，如果要向 users 表的 username 列添加索引，可以使用以下 SQL 语句：

CREATE INDEX username_index ON users (username);

这条语句将在 users 表的 username 列上创建一个名为 username_index 的索引。如果 users 表很大，这个过程可能非常缓慢，而且可能会耗尽服务器的内存或磁盘空间。

3. 改进 MySQL 添加大表索引的性能

MySQL 添加大表索引的速度通常可以通过以下几种方法提高：

3.1 分区

对于非常大的表，可以使用 MySQL 的分区功能将其分成更小的逻辑块。这可以减少 I/O 操作和锁定表的时间。

例如，假设需要在具有 1 亿条记录的 user_logs 表的 username 列上添加索引。可以创建一个基于范围的分区方案，例如按日期范围分区，将每个分区的记录数限制在几百万条以下。这样就可以更快地为单个分区创建索引。

CREATE TABLE user_logs (
  id INT NOT NULL AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL,
  log_date DATETIME NOT NULL,
  ...
) PARTITION BY RANGE (TO_DAYS(log_date)) (
  PARTITION p0 VALUES LESS THAN (TO_DAYS('2022-06-01')),
  PARTITION p1 VALUES LESS THAN (TO_DAYS('2022-07-01')),
  PARTITION p2 VALUES LESS THAN (TO_DAYS('2022-08-01')),
  ...
);

此代码将 user_logs 表分成数百个分区，每个分区都包含小于某个日期的所有记录。在某些情况下，这可以大大加速索引创建的速度。

3.2 大事务改为小事务

默认情况下，InnoDB 存储引擎使用自动提交模式来处理事务。这意味着每个查询都是一个单独的事务，这可能不是很高效。如果需要执行大量查询，则可以将它们组合成一个事务。

然而，如果进行这样的优化，可能会导致锁定时间过长。如果查询涉及的记录太多，那么由于锁定表，其他查询和写入操作可能会被挂起。

3.3 使用并行创建索引

使用并行索引创建可以显著提高添加索引的速度。从 MySQL 8.0 开始，可以通过设置 innodb_dedicated_server 参数启用并行索引创建。该参数使 MySQL 优化器有权选择更佳的索引创建方法以提高性能。

例如，将以下代码添加到 /etc/my.cnf文件中：

[mysqld]
# 启用并行索引创建
innodb_dedicated_server = ON

然后重启 MySQL，以应用新的配置。

3.4 异步创建索引

在 MySQL 5.6 中，引入了异步索引创建，它可以让你将索引的创建操作推迟到后台执行。这意味着可以使您尽可能快地返回 CREATE INDEX 命令，并让它在后台继续执行索引的创建。这大大减少了等待时间。

使用异步索引创建时，由于锁定时间的减少，可以更快地将索引添加到大表中。

添加异步索引可以通过以下步骤完成：

修改 MySQL 配置文件（通常是 /etc/my.cnf），添加以下行：innodb_online_alter_log_max_size
在需要添加索引的表上运行以下命令：

ALTER TABLE mytable ADD INDEX (column) WITH ONLINE=1;

这会异步创建索引。

3.5 较低的 MyISAM 并发

如果您正在使用 MyISAM 存储引擎，您还可以尝试减少 MyISAM 的并发性设置。MyISAM 表通过表锁定来防止多个操作同步进行。

将并发性设置为较低的值可能会使索引创建更快。要设置 MyISAM 并发性，请在 MySQL 配置文件中添加以下行：

[mysqld]
set-variable = concurrent_insert=ALWAYS
set-variable = concurrent_insert_delayed=0
set-variable = thread_concurrency=2
set-variable = myisam_sort_buffer_size=64M

这将同时设置并发插入，延迟等待时间，线程并发性和 MyISAM 排序缓冲区的大小。