mysql索引提升查询速度的实例测试

来源：互联网发布：thinder 交友软件编辑：程序博客网时间：2024/06/05 19:17

我们都知道，很多时候，要对数据库增加索引，可以提升查询速度，有兴趣的可以看看数据库索引原理，本文来实际测试一下。
测试数据库记录大概10000条，先看看没有索引的情况。

表结构为：

mysql> show create table tb_test;                            +---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+  | Table   | Create Table                                                                                                                                                                                                                                                                                                                 |  +---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+  | tb_test | CREATE TABLE `tb_test` (    `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'test',    `name` varchar(32) NOT NULL COMMENT 'test',    `score` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'test',    PRIMARY KEY (`id`)  ) ENGINE=InnoDB AUTO_INCREMENT=10004 DEFAULT CHARSET=utf8 COMMENT='测试表， 无实际作用'          |  +---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+  1 row in set (0.00 sec)

开启profiling, 进行两次 select操作，分析结果如下：

mysql> show profiles;                            +----------+------------+----------------------------------------------------+  | Query_ID | Duration   | Query                                              |  +----------+------------+----------------------------------------------------+  |    10021 | 0.00306534 | select * from tb_test where name = 'n1'            |  |    10022 | 0.00298983 | select * from tb_test where score = 1              |  +----------+------------+----------------------------------------------------+

将name字段设置为索引，表结构为：

mysql> show create table tb_test;                            +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+  | Table   | Create Table                                                                                                                                                                                                                                                                                                                                            |  +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+  | tb_test | CREATE TABLE `tb_test` (    `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'test',    `name` varchar(32) NOT NULL COMMENT 'test',    `score` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'test',    PRIMARY KEY (`id`),    KEY `idx_name` (`name`)  ) ENGINE=InnoDB AUTO_INCREMENT=10004 DEFAULT CHARSET=utf8 COMMENT='测试表， 无实际作用'          |  +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+  1 row in set (0.00 sec)

再次进行两次相同的select操作，分析结果如下：

mysql> show profiles;                            +----------+------------+----------------------------------------------------+  | Query_ID | Duration   | Query                                              |  +----------+------------+----------------------------------------------------+  |    10021 | 0.00306534 | select * from tb_test where name = 'n1'            |  |    10022 | 0.00298983 | select * from tb_test where score = 1              |  |    10023 | 0.00074623 | select * from tb_test where name = 'n1'            |  |    10024 | 0.00298101 | select * from tb_test where score = 1              |  +----------+------------+----------------------------------------------------+

可见， Query_ID为10023的那条查询快了很多。

继续将score字段设置为索引，表结构为：

mysql> show create table tb_test;  +---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+  | Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                         |  +---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+  | tb_test | CREATE TABLE `tb_test` (    `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'test',    `name` varchar(32) NOT NULL COMMENT 'test',    `score` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'test',    PRIMARY KEY (`id`),    KEY `idx_name` (`name`),    KEY `idx_score` (`score`)  ) ENGINE=InnoDB AUTO_INCREMENT=10004 DEFAULT CHARSET=utf8 COMMENT='测试表， 无实际作用'          |  +---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+  1 row in set (0.00 sec)

进行两次相同的select操作，分析结果如下：

mysql> show profiles;                            +----------+------------+----------------------------------------------------+  | Query_ID | Duration   | Query                                              |  +----------+------------+----------------------------------------------------+  |    10021 | 0.00306534 | select * from tb_test where name = 'n1'            |  |    10022 | 0.00298983 | select * from tb_test where score = 1              |  |    10023 | 0.00074623 | select * from tb_test where name = 'n1'            |  |    10024 | 0.00298101 | select * from tb_test where score = 1              |  |    10027 | 0.00073865 | select * from tb_test where name = 'n1'            |  |    10028 | 0.00072649 | select * from tb_test where score = 1              |  +----------+------------+----------------------------------------------------+

可见， Query_ID为10027和10028d的那两条快了很多。

综上所述：
1. 索引能提升查询速度。当然，索引也有弊端，不能任意妄为。
2. 如上记录仅仅是10000条，如果记录条数为100万，那么索引的效果就更能很好体现了。

阅读全文

0 0