Mysql 分批加索引的详细方法_Mysql

1. 分批次创建索引怎么创建

在生产环境中，分批次创建索引是一种有效的策略，尤其适用于处理大型数据表（如千万级数据），目的是减少对系统性能的影响，并避免长时间锁表。分批创建索引通常可以分为以下几种方法：

方法 1：使用临时表分批创建索引

这种方法通过创建一个临时表，将原表的数据按批次分批插入到临时表中，并在临时表上添加索引，最后再交换原表和临时表。这种方法不会影响生产表的查询操作。

步骤：

创建临时表：创建一个与原表结构相同的表，并在临时表上添加索引。

create table temp_table like original_table;
create index idx_column_name on temp_table (column_name);

分批插入数据：将原表数据分批插入到临时表中。为了避免一次性插入过多数据导致长时间锁定，可以按批次（比如每 10000 行）分批执行插入操作。

insert into temp_table (columns) select columns from original_table
where condition limit 10000 offset 0;

逐步增加偏移量 (offset)：为了逐步插入数据，可以使用以下方法在每次插入后更新偏移量。
执行一次批量插入后，更新 offset

insert into temp_table (columns)
select columns from original_table where condition limit 10000 offset 10000;

切换临时表和原表：在所有数据都插入到临时表并且索引已创建完成后，你可以通过重命名表的方式将临时表替换为原表：

rename table original_table to backup_table, temp_table to
original_table;

如果操作成功完成，你可以删除原来的备份表 backup_table，但保留数据和索引。

方法 2：使用分区表分批创建索引

按分区处理数据：你可以选择一个分区进行索引创建，确保不会锁定整个表。

create index idx_column_name on original_table partition (p1)
(column_name);

逐个分区创建索引：如果表是基于某个字段分区的，你可以为每个分区单独创建索引，逐个执行每个分区上的索引创建操作。

create index idx_column_name on original_table partition (p1)
(column_name); create index idx_column_name on original_table
partition (p2) (column_name);

方法 3：在后台创建索引 (使用 online 选项)

如果使用的数据库引擎支持 online 索引创建（如 innodb），则可以避免在索引创建过程中锁住整个表。online 索引创建可以在不锁表的情况下进行，从而对生产环境的影响较小。

使用 online 创建索引：在创建索引时加上 algorithm=inplace 和 lock=none，以便在索引创建过程中避免对表的锁定。

create index idx_column_name on original_table (column_name) algorithm=inplace, lock=none;

渐进式创建索引：如果需要创建多个索引，可以分批次逐个创建。例如，先为 column1 创建索引，创建完成后再为 column2 创建索引，等等。

create index idx_column1 on original_table (column1) algorithm=inplace, lock=none;
create index idx_column2 on original_table (column2) algorithm=inplace, lock=none;

这样做可以在索引创建期间保持表的可用性，并且对性能的影响较小。

方法 4：分批次执行 alter table

如果你使用 alter table 来创建索引，也可以分批次执行。在大表上创建索引时，通常会发生锁表操作（尤其是在没有使用 online 选项的情况下），因此在此情况下，要谨慎操作。

你可以通过将大表拆分成多个小表或者通过分批更新来减少锁表的时间。

alter table original_table add index idx_column_name (column_name);

如果表的行数非常大，执行时间可能会较长，可能需要根据实际业务负载选择合适的时间窗口。

方法 5：使用 mysql 的 pt-online-schema-change 工具

pt-online-schema-change 是 percona toolkit 中的一个工具，用于在生产环境中在线修改 mysql 表结构，包括创建索引，而不影响系统的可用性。这个工具会在后台执行表修改操作，并创建一个临时表，确保整个过程不会影响到生产数据库的使用。

pt-online-schema-change --alter "add index idx_column_name (column_name)" --execute d=your_db,t=original_table

总结

分批次创建索引是一个优化操作，在生产环境中尤为重要，尤其是在数据量大的情况下。常用的分批策略包括：

使用临时表：先创建临时表并插入数据，再为临时表创建索引，最后交换表。
分区表处理：如果是分区表，按分区逐步创建索引。
使用 online 索引创建：使用 mysql 支持的 online 选项减少对表的锁定。
分批 alter table：在不同时间点逐步添加索引。
使用 pt-online-schema-change 工具：在生产环境中安全地创建索引而不影响可用性。
通过合理的分批策略和工具，您可以在生产环境中有效地为大表创建索引，同时保持系统的高可用性。

2. 千万级的数据表加索引

1.评估需要索引的字段

首先，要评估和确定哪些字段需要索引。通常，可以考虑以下几个方面：
查询频繁的字段：如果某个字段经常出现在 where 子句中，或作为排序依据，给该字段加索引可能会显著提高查询性能。
join 字段：用于连接的字段通常需要索引，因为索引可以加速连接操作。
范围查询字段：用于 between、>、< 等范围查询的字段加索引会提升性能。
唯一性要求的字段：如主键和唯一键，通常都应该加索引。

2.选择合适的索引类型

单列索引：针对查询条件只涉及单一列的情况。
复合索引：当查询涉及多个列（特别是多个 and 条件的情况下），复合索引可以提高性能，但要注意索引列的顺序非常重要。
全文索引：适用于文本搜索（如 match … against）的场景。
空间索引：如果是地理数据，可以考虑使用空间索引（如 mysql 的 spatial 索引）。

3. 评估影响并准备备份

创建索引会占用系统资源，可能会锁定表或造成性能瓶颈，尤其是在大表上。为了确保安全，备份当前数据是非常重要的。如果索引创建失败或操作过程中出现问题，可以通过恢复备份来恢复数据。

4. 索引创建策略

对于千万级数据表，直接在生产环境中创建索引可能会导致性能下降，甚至出现长时间的锁表情况。为了减少对生产环境的影响，可以考虑以下策略：

4.1 使用 online 选项（如果支持）

mysql 5.6 及以上版本支持使用 online 选项创建索引，可以在不锁定表的情况下创建索引。这有助于避免对查询和插入的干扰。

create index idx_column_name on table_name (column_name) online;

4.2 分批次创建索引

如果表非常大，可以考虑分批次创建索引。假设表的数据分布有一定规律，可以将数据分成多个部分，逐步进行索引创建。
可以通过分区表（partitioning）或根据某些条件（如日期、范围）选择性地创建索引。创建索引时使用 algorithm=inplace，可以减少表的锁定时间。

4.3 低流量时段执行索引创建

在生产环境中，通常有低流量时段，可以通过定时任务在低峰期执行索引创建。

4.4 逐步添加索引

对于已经有索引的大表，逐步添加索引可以避免一次性创建多个索引造成的负载过高。每次添加一个索引后，评估系统性能，确保没有重大性能瓶颈后再添加下一个。

4.5 使用 pt-online-schema-change 工具

percona toolkit 提供的 pt-online-schema-change 工具可以在不中断服务的情况下安全地对表结构进行更改（如创建索引）。该工具通过创建一个新的表来实现更改，并通过触发器同步数据，这样对生产环境的影响较小。

pt-online-schema-change --alter “add index idx_column_name
(column_name)” d=your_db,t=your_table --execute

5. 监控性能影响

创建索引的过程中，监控数据库的性能非常重要。特别是在生产环境中，创建索引可能会导致：

cpu、内存使用率：大量索引创建可能会占用 cpu 和内存资源。
磁盘 i/o：索引创建需要读取和写入大量数据，可能会导致磁盘 i/o 高峰。
锁竞争：在不支持 online 索引创建的情况下，表会被锁定，可能会影响到其他查询的执行。
使用监控工具（如 mysql enterprise monitor、prometheus + grafana、percona monitoring and management）来监控数据库的性能指标，确保索引创建过程中的负载不会过高。

6. 创建索引时注意事项

选择合适的字段：确保你创建的索引能满足实际查询的需求。如果一个索引不会被查询使用，创建它将浪费存储空间并影响写入性能。
避免过多的索引：虽然索引可以提高查询性能，但它们也会增加数据插入、更新和删除的成本。过多的索引会影响写入性能，所以应尽量避免冗余索引。
定期检查索引使用情况：使用 show index 查看当前索引的使用情况，定期清理不再使用的索引。

7. 测试

在生产环境部署前，建议在开发或测试环境中进行充分的测试，模拟生产环境的负载和查询模式，确保新添加的索引不会对系统性能产生负面影响。

测试内容包括：

- 测试创建索引所需的时间和资源消耗。

- 测试新索引对查询性能的提升（或者可能带来的性能问题）。

- 测试索引创建过程中的系统资源消耗和响应时间。

8. 优化索引策略

在数据量不断增长的情况下，索引的设计也需要随之调整。考虑以下方面来持续优化：

定期删除不再使用的索引。
对查询模式进行分析，调整索引的设计，使用复合索引来提升查询性能。
考虑使用分区表来更好地管理大表数据。

总结

在生产环境对千万级数据表加索引时，需要避免直接在高峰期操作，尽量使用低流量时段进行操作。
使用 online 选项或工具如 pt-online-schema-change 来减少对生产环境的影响。
定期监控数据库的性能，确保索引创建过程中不会对生产系统造成过大的负担。
测试和优化索引设计，避免创建冗余的索引。

到此这篇关于mysql 分批加索引的文章就介绍到这了,更多相关mysql 分批加索引内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！

Mysql 分批加索引的详细方法

2024年12月28日 • Mysql •我要评论