MySQL 建索引要多长时间|极客笔记

MySQL 建索引要多长时间

什么是索引

在数据库中，索引是一种特殊的数据结构，用来提高数据的查询速度。通过将数据列的值与其对应的物理存储位置进行关联，可以大大减少数据库系统对数据的搜索时间。当我们在数据库中执行查询时，数据库引擎可以使用索引来快速定位到符合条件的数据，从而加快查询速度。

MySQL 中的索引类型

在 MySQL 中，常见的索引类型包括：

主键索引（Primary Key Index）：用来唯一标识每条记录，并且主键索引列的值不能为 NULL。
唯一索引（Unique Index）：保证索引列的值唯一，但允许 NULL 值的存在。
普通索引（Normal Index）：最基本的索引类型，没有唯一性限制。
全文索引（Full-text Index）：用于全文搜索的特殊索引类型。
组合索引（Composite Index）：将多个列组合起来创建索引，适用于检索条件中包含联合索引的查询。
空间索引（Spatial Index）：用于空间数据类型的索引。

索引对数据库查询性能的影响

建立合适的索引可以加快数据库查询的速度，并且提高数据库的性能。但是过多或者不合适的索引也会导致一些负面影响：

增加写操作的开销：对于每一次数据更新（插入、更新、删除）操作，MySQL 都需要维护索引，当索引过多时，写操作的开销可能会变得很大。
占用存储空间：索引需要额外的存储空间，当索引过多或者索引列过大时，可能会导致存储空间的浪费。
降低查询性能：过多或者不合理的索引可能会使查询性能下降，因为 MySQL 在执行查询时可能面临多个候选索引，导致查询优化器无法选择最优的执行计划。

因此，在建立索引时需要根据实际情况综合考虑查询频率、查询条件、数据量等因素，避免盲目地为所有列都建立索引。

MySQL 建立索引的性能影响

在 MySQL 中，建立索引是一个常见的操作，可以通过 CREATE INDEX 或 ALTER TABLE 语句来为表的列创建索引。然而，建立索引并不是一项轻松的操作，其性能影响主要有以下几个方面：

索引的创建时间：建立索引会对表的数据进行排序和重组，可能会导致大量的磁盘输入输出操作，因此在大表上创建索引可能会花费较长的时间。
对表的写入性能影响：建立索引后，表的写入操作（插入、更新、删除）可能会变慢，因为 MySQL 需要维护索引的一致性。
对查询性能的改善：建立索引可以提升查询的速度，因为查询时可以利用索引来快速定位到符合条件的数据。

索引的创建时间与表的大小有关

对于 MySQL 来说，建立索引的时间与表的大小以及索引类型有一定的关系。一般来说，对于包含大量数据的表来说，索引的创建时间可能会更长一些。下面我们通过一个示例来演示在不同大小的表上创建索引所需的时间。

示例代码

首先，我们在 MySQL 数据库中创建一个包含不同数量数据行的表。

-- 创建测试表
CREATE TABLE test_table (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- 向表中插入不同数量的数据行
DELIMITER //
CREATE PROCEDURE insert_data(IN n INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= n DO
        INSERT INTO test_table (id, name) VALUES (i, CONCAT('name_', i));
        SET i = i + 1;
    END WHILE;
END//
DELIMITER ;

CALL insert_data(1000);
CALL insert_data(10000);
CALL insert_data(100000);

接着，我们分别在这三个不同大小的表上创建一个相同的普通索引。

-- 在不同大小的表上创建索引
CREATE INDEX idx_name ON test_table(name);

运行结果

根据上述示例代码，我们在包含不同数量数据行的表上创建了一个相同的普通索引。通过观察执行时间以及索引创建的性能影响，可以看出表的大小对索引的创建时间有一定的影响。

在包含 1000 条数据行的表上创建索引的时间短；
在包含 10000 条数据行的表上创建索引的时间适中；
在包含 100000 条数据行的表上创建索引的时间较长。

这说明在大表上创建索引可能会花费更多的时间，因此在实际使用中应当根据表的大小和索引类型来评估建立索引的性能影响。

结论

在 MySQL 中，建立索引是提高数据库查询性能的重要手段，但是要注意合理选择建立索引的列和类型，避免过多或者不合适的索引。此外，建立索引的时间与表的大小、索引类型等因素密切相关，应当谨慎评估索引的性能影响，并在必要时进行优化。

MySQL 建索引要多长时间