在性能测试中遇到性能瓶颈最的多地方就是数据库这块，而数据库出问题很多都是索引使用不当导致，根据以往遇到的索引问题做个简单的总结：
一、索引的利弊
索引的好处：索引能够极大地提高数据检索的效率，让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，所以造成了索引有可能是比数据大。
