性能测试中sql索引引起的性能问题-爱开源

在性能测试中遇到性能瓶颈最的多地方就是数据库这块，而数据库出问题很多都是索引使用不当导致，根据以往遇到的索引问题做个简单的总结：

本次测试环境为xen的虚拟机配置如下：

操作系统	CentOS release 5.4
Cpu	E5645 @ 2.40GH（四核）
内存	4G
磁盘大小	128.8 GB

特殊说明：

1.本次测试为了防止系统的缓存影响测试结果的准确性，所以每次测试之前都会执行下面的清除缓存的命令

sync && echo 3 >/proc/sys/vm/drop_caches && sleep 2 && echo 0 >/proc/sys/vm/drop_caches

2.并且个select语句都会添加SQL_NO_CACHE来消除mysql的query cahce对测试结果的影响

3.测试数据库量：150万行

一、索引的利弊

索引的好处：索引能够极大地提高数据检索的效率，让Query 执行得更快，也能够改善排序分组操作的性能，在进行排序分组操作中利用好索引，将会极大地降低CPU资源的消耗。

索引到底是如何帮助我们的sql提高效率的呢，我们举一个比较通俗的例子来说明比如一本字典，我们要查书里“查”这个字，方法有很多，比如从第一页开始一个字一个字去找；或者从后往前找；这样都很慢，所以用到索引，字典有目录，可以很快定位到这个字的页数，也就是数据库中的地址。

下面我们通过几个测试来看一下索引能给我们带来哪些好处。

本文所用测试的表结构如下：

CREATE TABLE `order_search` (
`order_search_id` int(11) NOT NULL AUTO_INCREMENT,
`order_id` int(10) DEFAULT NULL,
`order_num` varchar(90) DEFAULT NULL,
`customer_id` int(11) DEFAULT NULL,
`customer_name` varchar(360) DEFAULT NULL,
`product_type` tinyint(4) DEFAULT NULL,
`deposits` decimal(10,0) DEFAULT NULL,
`service_amount` decimal(10,0) DEFAULT NULL,
`amount` decimal(10,0) DEFAULT NULL,
`discount` decimal(10,0) DEFAULT NULL,
`contract_copy_num` tinyint(4) DEFAULT NULL,
`contract_receive_type` tinyint(4) DEFAULT NULL,
`contract_num` varchar(60) DEFAULT NULL,
`finance_paper_type` tinyint(4) DEFAULT NULL,
`receipt_company_name` varchar(300) DEFAULT NULL,
`receipt_num` varchar(60) DEFAULT NULL,
`paper_num` varchar(60) DEFAULT NULL,
`present_type` tinyint(4) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
`create_user_id` int(11) DEFAULT NULL,
`follow_up_user_id` int(11) DEFAULT NULL,
`order_status` smallint(6) DEFAULT NULL,
`alter_time` datetime DEFAULT NULL,
`order_source` tinyint(4) DEFAULT NULL,
`conference_order` tinyint(4) DEFAULT NULL,
PRIMARY KEY (`order_search_id`),
) ENGINE=InnoDB AUTO_INCREMENT=8167630 DEFAULT CHARSET=gbk

1、索引对查询语句的影响

a)存储引擎为innodb

没有索引的情况下测试结果：

mysql>  SELECT SQL_NO_CACHE order_num FROM test.`order_search` WHERE order_id = 10000;

+----------------------+
| order_num            |
+----------------------+
| tdzxXS20130462852002 |
| yttcXS20130495716374 |
……………………

15 rows in set (1.82 sec)

mysql> explain SELECT SQL_NO_CACHE order_num FROM test.`order_search` WHERE order_id = 10000;

+----+-------------+--------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table        | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+--------------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | order_search | ALL  | NULL          | NULL | NULL    | NULL | 1500143 | Using where |
+----+-------------+------------–+------+---------------+------+---------+------+---------+-------------+

1 row in set (0.00 sec)

添加索引：

mysql> ALTER TABLE `test`.`order_search` ADD INDEX `order_id` (`order_id`);

Query OK, 0 rows affected (4.62 sec)

Records: 0  Duplicates: 0  Warnings: 0

添加索引后的测试情况如下：

mysql>  SELECT SQL_NO_CACHE order_num FROM test.`order_search` WHERE order_id = 10000;

+----------------------+
| order_num            |
+----------------------+
| tdzxXS20130462852002 |
| yttcXS20130495716374 |
| njmhXS20130416197300 |
……………

15 rows in set (0.09 sec)

mysql> explain SELECT SQL_NO_CACHE order_num FROM test.`order_search` WHERE order_id = 10000;

+----+-------------+--------------+------+---------------+----------+---------+-------+------+-------------+
| id | select_type | table        | type | possible_keys | key      | key_len | ref   | rows | Extra       |
+----+-------------+--------------+------+---------------+----------+---------+-------+------+-------------+
|  1 | SIMPLE      | order_search | ref  | order_id      | order_id | 5       | const |   15 | Using where |
+----+-------------+--------------+------+---------------+----------+---------+-------+------+-------------+

添加索引前的执行时间为1.82 sec，而执行时间后的时间变成了0.09秒，性能有明显提高。为什么性能会有这么大的提升呢，通过explain命令可以看出，在没索引的情况下要查出order_id = 10000的order_num需要扫描1500143行才能查出结果，而添加索引以后只需要扫描15行就可以定位到要查询信息。

b)存储引擎为mysiam

ALTER TABLE `test`.`order_search` ENGINE=MYISAM COMMENT=” ROW_FORMAT=DEFAULT CHARSET=gbk COLLATE=gbk_chinese_ci ;

下面测试的是没索引的情况下性能:

mysql> SELECT SQL_NO_CACHE order_num FROM test.`order_search` WHERE order_id = 10000;

+----------------------+
| order_num            |
+----------------------+
| tdzxXS20130462852002 |
| yttcXS20130495716374 |
| njmhXS20130416197300 |
……………………

15 rows in set (1.29 sec)

mysql> explain SELECT SQL_NO_CACHE order_num FROM test.`order_search` WHERE order_id = 10000;

+----+-------------+--------------+------+---------------+----------+---------+-------+------+-------------+
| id | select_type | table        | type | possible_keys | key      | key_len | ref   | rows | Extra       |
+----+-------------+--------------+------+---------------+----------+---------+-------+------+-------------+
|  1 | SIMPLE      | order_search | ref  | order_id      | order_id | 5       | const |   15 | Using where |
+----+-------------+--------------+------+---------------+----------+---------+-------+------+-------------+

b)存储引擎为mysiam

ALTER TABLE `test`.`order_search` ENGINE=MYISAM COMMENT=” ROW_FORMAT=DEFAULT CHARSET=gbk COLLATE=gbk_chinese_ci ;

下面测试的是没索引的情况下性能:

mysql> SELECT SQL_NO_CACHE order_num FROM test.`order_search` WHERE order_id = 10000;

+———————-+

| order_num |

+———————-+

| tdzxXS20130462852002 |

| yttcXS20130495716374 |

| njmhXS20130416197300 |

……………………..

15 rows in set (1.29 sec)

执行计划：

mysql> explain SELECT SQL_NO_CACHE order_num FROM test.`order_search` WHERE order_id = 10000;

+—-+————-+————–+——+—————+——+———+——+———+————-+

+—-+————-+————–+——+—————+——+———+——+———+————-+

+—-+————-+————–+——+—————+——+———+——+———+————-+

1 row in set (0.00 sec)

添加索引后的情况下性能:

mysql> ALTER TABLE `test`.`order_search` ADD INDEX `order_id` (`order_id`);

Query OK, 1500000 rows affected (15.07 sec)

Records: 1500000 Duplicates: 0 Warnings: 0

mysql> SELECT SQL_NO_CACHE order_num FROM test.`order_search` WHERE order_id = 10000;

+———————-+

| order_num |

+———————-+

| tdzxXS20130462852002 |

| yttcXS20130495716374 |

| njmhXS20130416197300 |

…………………………….

15 rows in set (0.07 sec)

mysql> explain SELECT SQL_NO_CACHE order_num FROM test.`order_search` WHERE order_id = 10000;

+—-+————-+————–+——+—————+———-+———+——-+——+————-+

+—-+————-+————–+——+—————+———-+———+——-+——+————-+

+—-+————-+————–+——+—————+———-+———+——-+——+————-+

1 row in set (0.04 sec)

通过以上数据不难发现无论存储引擎为innodb还是myisam，索引对query的语句的性能都有很大的提高。关于存储引擎之间的比较可以发现myisam可能稍占优势。这可能与myisam的索引缓存有关，因为myisam的索引都是缓存在key_buffer内，如果key_buffer足够大，这就意味着key可以全部在内存中。

2、索引对order by语句的影响

a)存储引擎为innodb

没有索引的情况下测试结果：

mysql> SELECT sql_no_cache order_num FROM test.`order_search` WHERE `order_id` =100000 ORDER BY `create_user_id`;

+———————-+

| order_num |

+———————-+

| yttcXS20130452827058 |

| szmmXS20130419697158 |

…………………………..

14 rows in set (0.96 sec)

mysql> explain SELECT sql_no_cache order_num FROM test.`order_search` WHERE `order_id` =100000 ORDER BY `create_user_id`;

+—-+————-+————–+——+—————+——+———+——+———+—————————–+

+—-+————-+————–+——+—————+——+———+——+———+—————————–+

+—-+————-+————–+——+—————+——+———+——+———+—————————–+

1 row in set (0.00 sec)

添加索引：

ALTER TABLE `test`.`order_search` ADD INDEX `NewIndex1` (`order_id`, `create_user_id`);

执行用时:

mysql> SELECT sql_no_cache order_num FROM test.`order_search` WHERE `order_id` =100000 ORDER BY `create_user_id`;

+———————-+

| order_num |

+———————-+

| yttcXS20130452827058 |

| szmmXS20130419697158 |

……………………..

+———————-+

14 rows in set (0.06 sec)

执行计划：

mysql> explain SELECT sql_no_cache order_num FROM test.`order_search` WHERE `order_id` =100000 ORDER BY `create_user_id`;

+—-+————-+————–+——+—————+———–+———+——-+——+————-+

+—-+————-+————–+——+—————+———–+———+——-+——+————-+

+—-+————-+————–+——+—————+———–+———+——-+——+————-+

1 row in set (0.02 sec)

b)存储引擎为myisam

没有索引的情况下测试结果：

测试用时：

mysql> SELECT sql_no_cache order_num FROM test.`order_search` WHERE `order_id` =100000 ORDER BY `create_user_id`;

+———————-+

| order_num |

+———————-+

| yttcXS20130452827058 |

| szmmXS20130419697158 |

……………….

14 rows in set (0.87 sec)

执行计划：

mysql> explain SELECT sql_no_cache order_num FROM test.`order_search` WHERE `order_id` =100000 ORDER BY `create_user_id`;

+—-+————-+————–+——+—————+——+———+——+———+—————————–+

+—-+————-+————–+——+—————+——+———+——+———+—————————–+

+—-+————-+————–+——+—————+——+———+——+———+—————————–+

1 row in set (0.00 sec)

添加索引后的结果：

执行用时：

mysql> SELECT sql_no_cache order_num FROM test.`order_search` WHERE `order_id` =100000 ORDER BY `create_user_id`;

+———————-+

| order_num |

+———————-+

| yttcXS20130452827058 |

| szmmXS20130419697158 |

………………………….

14 rows in set (0.07 sec)

执行计划：

mysql> explain SELECT sql_no_cache order_num FROM test.`order_search` WHERE `order_id` =100000 ORDER BY `create_user_id`;

+—-+————-+————–+——+—————+———–+———+——-+——+————-+

+—-+————-+————–+——+—————+———–+———+——-+——+————-+

+—-+————-+————–+——+—————+———–+———+——-+——+————-+

1 row in set (0.01 sec)

从上面的数据可以看出索引对order by的性能也是有提升的，通过explain可以发现没有索引的时候，Extra项给出了filesort提示，而添加索引以后就只有use where了。

有人可能会提出这样的问题，为什么要添加order_id和create_user_id的联合索引，如果只添加create_user_id这个单个索引我们用explain分析一下索引的使用情况:

mysql> explain SELECT sql_no_cache order_num FROM test.`order_search` WHERE `order_id` =100000 ORDER BY `create_user_id`;

+—-+————-+————–+——+—————+——+———+——+———+—————————–+

+—-+————-+————–+——+—————+——+———+——+———+—————————–+

+—-+————-+————–+——+—————+——+———+——+———+—————————–+

我们会发现并没有使用索引，为什么会出现这样的情况呢？这里我们就需要了解一个索引的最左前缀原则，就是因为这个原则，所以选择正确的组合索引字段顺序显得非常重要，最常用的查询字段和选择性、区分度较高的字段，应该作为索引的前导字段使用。假设存在组合索引it1c1c2(c1,c2)，查询语句select * from t1 where c1=1 and c2=2能够使用该索引。查询语句select * from t1 where c1=1也能够使用该索引。但是，查询语句select * from t1 where c2=2不能够使用该索引，因为没有组合索引的引导列，即，要想使用c2列进行查找，必需出现c1等于某值，所以在在添加联合索引的时候尽量将常用的字段放到最前面。
例如我们为测试表新建一索引：

ALTER TABLE `test`.`order_search` ADD INDEX `oerder_create_user_id` (`order_id`, `create_user_id`);

mysql> explain SELECT sql_no_cache order_num FROM test.`order_search` WHERE `order_id` =100000 and create_user_id=2395;

+—-+————-+————–+——+———————–+———————–+———+————-+——+————-+

+—-+————-+————–+——+———————–+———————–+———+————-+——+————-+

这样式最符合左前缀原则的

mysql> explain SELECT sql_no_cache order_num FROM test.`order_search` WHERE `order_id` =100000 and order_num=’yttcXS20130452827058′ and create_user_id=2395 ;

+—-+————-+————–+——+———————–+———————–+———+————-+——+————-+

+—-+————-+————–+——+———————–+———————–+———+————-+——+————-+

+—-+————-+————–+——+———————–+———————–+———+————-+——+————-+

这种情况需要query优化器需要对我们的sql进行优化，这样会对性能有稍微影响，最佳写法：

SELECT sql_no_cache order_num FROM test.`order_search` WHERE `order_id` =100000 and create_user_id=2395 and order_num=’yttcXS20130452827058′ ;

如果没有order_id的索引会是什么情况呢：

mysql> explain SELECT sql_no_cache order_num FROM test.`order_search` WHERE order_num=’yttcXS20130452827058′ and create_user_id=2395 ;

+—-+————-+————–+——+—————+——+———+——+———+————-+

+—-+————-+————–+——+—————+——+———+——+———+————-+

+—-+————-+————–+——+—————+——+———+——+———+————-+

只有create_user_id字段

mysql> explain SELECT sql_no_cache order_num FROM test.`order_search` WHERE create_user_id=2395 ;

+—-+————-+————–+——+—————+——+———+——+———+————-+

+—-+————-+————–+——+—————+——+———+——+———+————-+

+—-+————-+————–+——+—————+——+———+——+———+————-+

竟然都没用到索引

如果只有order_id和其他组合条件呢

mysql> explain SELECT sql_no_cache order_num FROM test.`order_search` WHERE `order_id` =100000 and order_num=’yttcXS20130452827058′;

+—-+————-+————–+——+———————–+———————–+———+——-+——+————-+

+—-+————-+————–+——+———————–+———————–+———+——-+——+————-+

+—-+————-+————–+——+———————–+———————–+———+——-+——+————-+

虽然用到了索引，但是索引的长度由10变成了5说明只是用了索引的一部分；

通过以上例子说明，索引的顺序很重要，如果create_user_id这个字段经常作为条件来查询或者排序而order_id`字段只有很少次使用，那么这样的联合索引设置就很糟糕。

索引的弊端：

1、更新数据库时会更新索引，这样，最明显的资源消耗就是增加了更新所带来的 IO 量和调整索引所致的计算量。

mysql> DELETE FROM test.`order_search` WHERE order_search_id >500000

a.没有添加索引时添加20万数据一共用时1.512s

[root@qa05v /home/guozhenhua]# time mysql -uroot -pmypasswd test <test.sql

real 1m7.251s

user 0m1.512s

sys 0m1.304sc.

清除刚添加数据

mysql> DELETE FROM test.`order_search` WHERE order_search_id >500000

b．添加索引

mysql> ALTER TABLE `test`.`order_search` ADD INDEX `NewIndex1` (`order_id`, `customer_id`, `customer_name`);

c．添加索引后增加20万数据用时1.668s秒

[root@qa05v /home/guozhenhua]# time mysql -uroot -pmypasswd test <test.sql

real 1m14.787s

user 0m1.668s

sys 0m1.620s

2、索引也会占用一定的存储空间，有些时候索引所占的空间有可能超过数据所占的空间；

例如：下面举一个比较特殊的例子（如果字段大小设置不合理或者索引建的过多可能会导致一些问题），表结构和索引情况如下：

CREATE TABLE `friends` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`uid` bigint(20) unsigned DEFAULT ’0′,
`fuid` bigint(20) unsigned DEFAULT ’0′,
`fname` varchar(50) DEFAULT ”,
`fpicture` varchar(150) DEFAULT ”,
`fsex` tinyint(1) DEFAULT ’0′,
`status` tinyint(1) DEFAULT ’0′,
PRIMARY KEY (`id`),
KEY `fuid` (`fuid`),
KEY `fuid_fname` (`fuid`,`fname`),
KEY `uid_stats` (`uid`,`status`)
) ENGINE=MyISAM AUTO_INCREMENT=262145 DEFAULT CHARSET=gbk

新建10万条数据后，这个表的索引文件为4.4M而数据文件仅有3.9M：

[root@qa05v /usr/local/mysql/data/test]# du -sh friends.*

12K friends.frm

3.9M friends.MYD

4.4M friends.MYI

这里有点需要注意的是对于varchar字段，索引的长度是其定义的长度。比如一行中`fname` varchar(50) DEFAULT ” 实际只存了3个byte数据，但是其索引长度是50，所以造成了索引有可能是比数据大。

转载请注明：爱开源 » 性能测试中sql索引引起的性能问题

性能测试中sql索引引起的性能问题

与本文相关的文章

您必须登录才能发表评论！

与本文相关的文章

您必须 登录 才能发表评论！

您必须登录才能发表评论！