在性能测试中遇到性能瓶颈最的多地方就是数据库这块,而数据库出问题很多都是索引使用不当导致,根据以往遇到的索引问题做个简单的总结:
本次测试环境为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 | +----+-------------+--------------+------+---------------+----------+---------+-------+------+-------------+
添加索引前的执行时间为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,所以造成了索引有可能是比数据大。
转载请注明:爱开源 » 性能测试中sql索引引起的性能问题