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

  一、索引的利弊

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

  索引的弊端:

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

  测试代码:

CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`time1` varchar(11) DEFAULT NULL,
`time2` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURREN T_TIMESTAMP,
`time3` int(11) DEFAULT NULL,
`stats` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `NewIndex1` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0  DEFAULT CHARSET=gbk

  a、为了保证测试的公平性,所以必须保证添加索引前后的数据是完全一致的,现在中保留表中id小于200万的数据。

mysql> DELETE FROM test.test WHERE id >2000000;
Query OK, 231412 rows affected (0.92 sec)

  b、没有添加索引时添加200万数据用时1.81秒

mysql> INSERT INTO test.`test` (time1,time2,time3,stats) SELECT time1,time2,time3,stats FROM test.`test`;
Query OK, 231412 rows affected (1.32 sec)
Records: 231412 Duplicates: 0 Warnings: 0

  c、清除刚添加数据

mysql> DELETE FROM test.test WHERE id >2000000;
Query OK, 231412 rows affected (1.00 sec)

  d、添加索引

mysql> ALTER TABLE `test`.`test` ADD INDEX `time1_2_3_stats` (`time1`, `time2`, `time3`, `stats`);
Query OK, 0 rows affected (0.97 sec)
Records: 0 Duplicates: 0 Warnings: 0

  e、添加索引后增加200万数据用时4.88秒

mysql> INSERT INTO test.`test` (time1,time2,time3,stats) SELECT time1,time2,time3,stats FROM test.`test`;
Query OK, 231412 rows affected (2.27 sec)
Records: 231412 Duplicates: 0 Warnings: 0

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