数据表索引性能优化测试：5000万条数据的实测分析

引言

在之前的文章中，详细介绍了如何选择和优化数据表索引，同时也探讨了覆盖索引和前缀索引的相关知识。

本文将通过实际的大数据量（约为5000万条）测试索引的实际效果，以验证其是否真的具有显著的性能优势。得出的结论为：索引确实能够显著提升查询性能。

如需查阅之前的文章，可以访问以下链接：

MySQL数据表索引选择与优化方法

数据表索引应用之覆盖索引

测试数据说明

数据表包含4个字段：id、userid、orderid和title。该表使用InnoDB类型，字符集设定为utf8_general_ci，当前记录总数为51,927,525条，存储占用硬盘空间约为18.9 GB。

数据表的结构及其包含的内容均为随机生成，大致如下所示。

id：int型，记录表的ID，主键自增加 userid：int型，用户ID，测试数据全局唯一，未设置索引 orderid：varchar型，订单号，测试数据全局唯一，未设置索引 title：varchar型，订单商品标题，随机内容，测试数据全局唯一，未设置索引

实际测试

1、根据 id 查找数据

运行SELECT查询语句，条件是根据id进行查找。

SELECT * FROM `orders` WHERE `id`='51927331'
正在显示第 0 - 0 行 (共 1 行, 查询花费 0.0004 秒。)

SELECT `userid`,`orderid`,`title` FROM `orders` WHERE `id`='51927331'
正在显示第 0 - 0 行 (共 1 行, 查询花费 0.0002 秒。)

根据执行结果可以看出，通过id查找数据的方式极为高效，耗时几乎无差异。进一步通过 explain 语句深入分析了两条查询语句均依赖的主键索引。

EXPLAIN SELECT * FROM `orders` WHERE `id`='51927331'

EXPLAIN SELECT `userid`,`orderid`,`title` FROM `orders` WHERE `id`='51927331'

type：const表示使用唯一索引或主键 key：PRIMARY表示实际使用的索引名为PRIMARY，即默认的主键索引

小结：经过测试验证，查询语句使用了主键索引，查询效率极高。

2、根据 userid 查找数据

2.1、未建立索引前

默认userid未建立索引，运行SELECT查询语句，条件是根据 userid 进行查找。

SELECT * FROM `orders` WHERE `userid`='569356991'
正在显示第 0 - 0 行 (共 1 行, 查询花费 15.5170 秒。)

可见处理过程耗时较长，总计约15秒左右。通过explain功能查看详细情况。

EXPLAIN SELECT * FROM `orders` WHERE `userid`='569356991'

type： ALL 表示全表扫描，效率较低。 key：实际使用的索引 NULL，表示没有索引。

2.2、建立索引后

此时为字段 userid 建立 b-tree 索引。

数据量庞大，创建索引过程耗时较长，建议在命令行环境中执行。

ALTER TABLE `orders` ADD INDEX(`userid`);
Query OK, 0 rows affected (1 min 6.01 sec)

重新执行先前的查询操作，结果如下：

SELECT * FROM `orders` WHERE `userid`='569356991'
正在显示第 0 - 0 行 (共 1 行, 查询花费 0.0003 秒。)

SELECT `id`,`orderid`,`title` FROM `orders` WHERE `userid`='569356991'
正在显示第 0 - 0 行 (共 1 行, 查询花费 0.0002 秒。)

通过explain功能查看详细情况。

type： ref 表示非唯一索引或非主键索引扫描。 key：实际使用索引 userid。

小结：索引的建立与否对性能影响显著，未建立索引时耗时15.5170秒，而建立索引后仅为0.0002秒。

3、根据 orderid 查找数据

3.1、未建立索引前

默认orderid未建立索引，运行SELECT查询语句，条件为根据 orderid 进行查找。

SELECT * FROM `orders` WHERE `orderid`='20240718200726305235'
正在显示第 0 - 0 行 (共 1 行, 查询花费 14.6137 秒。)

处理过程耗时较长，总计大约14秒。通过explain功能查看详细情况。

EXPLAIN SELECT * FROM `orders` WHERE `orderid`='20240718200726305235'

type： ALL 表示全表扫描，效率较低。 key：实际使用的索引为 NULL，表示没有索引。

3.2、建立索引后

此时为字段 orderid 建立 b-tree 索引。

数据量庞大，创建索引过程耗时较长，建议在命令行环境中执行。

ALTER TABLE `orders` ADD INDEX(`orderid`);
Query OK, 0 rows affected (2 min 8.12 sec)

重新执行先前的查询操作，结果如下：

SELECT * FROM `orders` WHERE `orderid`='20240718200726305235'
正在显示第 0 - 0 行 (共 1 行, 查询花费 0.0003 秒。)

SELECT `id`,`userid`,`title` FROM `orders` WHERE `orderid`='20240718200726305235'
正在显示第 0 - 0 行 (共 1 行, 查询花费 0.0002 秒。)

通过explain功能查看详细情况。

EXPLAIN SELECT `id`,`userid`,`title` FROM `orders` WHERE `orderid`='20240718200726305235'

type： ref 表示非唯一索引或非主键索引扫描。 key：实际使用的索引为 orderid。

小结：建立索引与未建立索引的性能对比显著，未建立索引所需时间为14.6137秒，而建立索引后所需时间仅为0.0002秒。

4、根据覆盖索引查找数据

针对频繁查询和展示的 userid 和 orderid 字段，构建覆盖索引。

覆盖索引的构建遵循字段顺序的要求，因此 userid 置于索引的第一列。

为测试覆盖索引，需将先前建立userid、orderid的b-tree索引删除。

4.1、建立 userid orderid 的覆盖索引

数据量庞大，创建索引过程耗时较长，建议在命令行环境中执行。

CREATE INDEX userid_orderid ON `orders` (`userid`, `orderid`);
Query OK, 0 rows affected (1 min 16.45 sec)

4.2、通过 userid 查找 orderid

执行查询语句

SELECT `orderid` FROM `orders` WHERE `userid`='569356991'
正在显示第 0 - 0 行 (共 1 行, 查询花费 0.0003 秒。)

通过explain功能查看详细情况。

EXPLAIN SELECT `orderid` FROM `orders` WHERE `userid`='569356991'

type： ref 表示非唯一索引或非主键索引扫描。 key：实际使用覆盖索引 userid_orderid。

4.3、通过 orderid 查找 userid

执行查询语句

SELECT `userid` FROM `orders` WHERE `orderid`='20240718200726305235'
正在显示第 0 - 0 行 (共 1 行, 查询花费 14.4817 秒。)

耗时14秒左右，通过explain功能查看详细情况。

EXPLAIN SELECT `userid` FROM `orders` WHERE `orderid`='20240718200726305235'

type： index 表示遍历了整个索引树，与ALL类似，但遍历的是索引而非表。 key：实际使用覆盖索引 userid_orderid。 rows：查找所需的行数为 51927515 条，几乎遍历了所有数据。

小结

1、尽管耗时大约14秒，与未建立索引的情况相比较，时间上颇为接近，但两者的底层机制迥异。未建立索引时，系统执行的是对数据表的全表扫描操作。而当覆盖索引被创建后，数据库实际上是针对索引本身执行扫描，基于理论推断，扫描索引的效率理应高于全表扫描。

2、索引列的顺序对数据库查询性能有显著影响。由于 userid 列被置于索引的前列，所以基于 userid 的查询能够实现快速检索；相对地，若使用 orderid 进行查找，其速度则会受到一定影响。

5、b-tree索引与覆盖索引优先级

为了更有效地进行测试，对userid和orderid字段创建b-tree索引。

ALTER TABLE `orders` ADD INDEX(`userid`);

ALTER TABLE `orders` ADD INDEX(`orderid`);

分别执行以下语句，得到运行结果。

SELECT `orderid` FROM `orders` WHERE `userid`='569356991'
正在显示第 0 - 0 行 (共 1 行, 查询花费 0.0002 秒。)

SELECT `userid` FROM `orders` WHERE `orderid`='20240718200726305235'
正在显示第 0 - 0 行 (共 1 行, 查询花费 0.0003 秒。)

可见处理速度均非常迅速，通过explain功能来查看具体详情。

EXPLAIN SELECT `orderid` FROM `orders` WHERE `userid`='569356991'

key：实际运行的索引为 userid_orderid，即覆盖索引

EXPLAIN SELECT `userid` FROM `orders` WHERE `orderid`='20240718200726305235'

key：实际运行的索引为 orderid，即字段本身的b-tree索引

总结：在存在多个索引的情况下，数据库系统会自动选择最适宜的索引，以实现性能的最优化。

总结

索引在数据表中的应用能显著提升系统性能，尤其在处理大量数据时效果尤为明显。然而，需要关注到正确构建查询语句并设置索引的重要性，同时，应充分利用 explain 工具对语句进行分析，以确保索引得到恰当的应用。