05月24, 2013

性能测试中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 | ALL | NULL | NULL | NULL | NULL | 1500000 | Using where |

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

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;

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | order_search | ref | order_id | order_id | 5 | const | 12 | Using where |

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

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`;

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

| 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; Using filesort

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

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`;

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | order_search | ref | NewIndex1 | NewIndex1 | 5 | const | 14 | Using where |

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

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`;

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra

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

| 1 | SIMPLE | order_search | ALL | NULL | NULL | NULL | NULL | 1500000 | Using where; Using filesort |

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

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`;

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | order_search | ref | NewIndex1 | NewIndex1 | 5 | const | 1592 | Using where |

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

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`;

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra

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

| 1 | SIMPLE | order_search | ALL | NULL | NULL | NULL | NULL | 1500000 | Using where; Using filesort

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

我们会发现并没有使用索引,为什么会出现这样的情况呢?这里我们就需要了解一个索引的最左前缀原则,就是因为这个原则,所以选择正确的组合索引字段顺序显得非常重要,最常用的查询字段和选择性、区分度较高的字段,应该作为索引的前导字段使用。假设存在组合索引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;

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+—-+————-+————–+——+———————–+———————–+———+————-+——+————-+

| 1 | SIMPLE | order_search | ref | oerder_create_user_id | oerder_create_user_id | 10 | const,const | 117 | Using where |

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

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

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 ;

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | order_search | ref | oerder_create_user_id | oerder_create_user_id | 10 | const,const | 117 | Using where |

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

这种情况需要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 ;

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | order_search | ALL | NULL | NULL | NULL | NULL | 1500000 | Using where |

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

只有create_user_id字段

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

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | order_search | ALL | NULL | NULL | NULL | NULL | 1500000 | Using where |

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

竟然都没用到索引

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

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

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | order_search | ref | oerder_create_user_id | oerder_create_user_id | 5 | const | 1592 | Using where |

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

虽然用到了索引,但是索引的长度由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 DEFAULT0’,  
`fuid` bigint(20) unsigned DEFAULT0’,  
`fname` varchar(50) DEFAULT ”,  
`fpicture` varchar(150) DEFAULT ”,  
`fsex` tinyint(1) DEFAULT0’,  
`status` tinyint(1) DEFAULT0’,  
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,所以造成了索引有可能是比数据大。

本文链接:http://blogs.360.cn/post/性能测试中sql索引引起的性能问题-2.html

-- EOF --

Comments