性能测试中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 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,所以造成了索引有可能是比数据大。