二、索引使用原则
1、索引可以改善查询,但会减慢更新,索引不是越多越好,特别是在数据增、删、改比较频繁的表中,过多的索引反而会导致系统整体性能的下降,这一点已经在索引的弊端中介绍过,这里就不做太多介绍。
2、离散程度越小,不适合加索引,例如:不要给性别建索引 status这样字段建索引;
测试代码(stats字段是0~4随机生成的):
mysql> SELECT SQL_NO_CACHE count(id) FROM test.test WHERE stats=4; +———–+ | count(id) | +———–+ | 740591 | +———–+ 1 row in set (0.40 sec) mysql> ALTER TABLE `test`.`test` add INDEX `stats` (`stats`); Query OK, 3702592 rows affected (24.08 sec) Records: 3702592 Duplicates: 0 Warnings: 0 mysql> SELECT SQL_NO_CACHE count(id) FROM test.test WHERE stats=4; +———–+ | count(id) | +———–+ | 740591 | +———–+ 1 row in set (1.58 sec) |
3、在数据量较少且访问频率不高的情况下,假如只有一百行记录的表不需要建立索引。因为在数据量少的情况下,使用全表扫描效果比走索引更好,这就好比只有一本只有5页的书,如果我们想找其中一个章节,我们一般不会通过目录去寻找,而是直接去找了。
测试数据:
mysql> SELECT COUNT(*) FROM test.newtest; //该表数据为10000条 +———-+ | COUNT(*) | +———-+ | 10001 | +———-+ |
没有索引时执行时间大概接近于0秒
mysql> SELECT SQL_NO_CACHE count(time1) FROM test.`newtest` WHERE time1 >’20130517100591′; +————–+ | count(time1) | +————–+ | 9992 | +————–+ |
添加索引
mysql> ALTER TABLE `test`.`newtest` ADD INDEX `time1` (`time1`); Query OK, 10001 rows affected (0.04 sec) Records: 10001 Duplicates: 0 Warnings: 0 |
添加索引后执行时间为0.02sec
mysql> SELECT SQL_NO_CACHE count(time1) FROM test.`newtest` WHERE time1 >’20130517100591′; +————–+ | count(time1) | +————–+ | 9992 | +————–+ 1 row in set (0.02 sec) |
4、避免建立两个或以上功能相同索引。例如已经建立字段A、B两个字段的索引,应该避免再建立字段A的单独索引。两个索引之间,对相同的查询都会起到相同的作用。建立两个功能相同的索引,反而会容易引起数据库产生错误的查询计划,降低查询效率。
5、选择正确的组合索引字段顺序,最常用的查询字段和选择性、区分度较高的字段,应该作为索引的前导字段使用。假设存在组合索引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`.`friends` ADD INDEX `fuid_fname` (`fuid`, `fname`);创建组合索引 mysql> EXPLAIN SELECT * FROM test.`friends` WHERE fname=’test5′; +—-+————-+———+——+—————+——+———+——+——–+————-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +—-+————-+———+——+—————+——+———+——+——–+————-+ | 1 | SIMPLE | friends | ALL | NULL | NULL | NULL | NULL | 100000 | Using where | +—-+————-+———+——+—————+——+———+——+——–+————-+ |
如果单独再创建一个fuid索引这样造成了索引的浪费。
mysql> EXPLAIN SELECT * FROM test.`friends` WHERE fuid=’364′; +—-+————-+———+——+—————+————+———+——-+——-+————-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +—-+————-+———+——+—————+————+———+——-+——-+————-+ | 1 | SIMPLE | friends | ref | fuid_fname | fuid_fname | 9 | const | 33712 | Using where | +—-+————-+———+——+—————+————+———+——-+——-+————-+ |