四、引起索引失效的一些因素

　　1、like语句操作

　　一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%”不会使用索引而like “aaa%”可以使用索引。

　　例如：

mysql> EXPLAIN SELECT * FROM test.`test` WHERE time1 LIKE ‘%550′ LIMIT 0，10;
+—-+————-+——-+——+—————+——+———+——+———+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+——+———+——+———+————-+
| 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 3922757 | Using where |
+—-+————-+——-+——+—————+——+———+——+———+————-+
mysql> EXPLAIN SELECT * FROM test.`test` WHERE time1 LIKE ’2013%’ LIMIT 0，10;
+—-+————-+——-+——-+—————+——-+———+——+———+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——-+—————+——-+———+——+———+————-+
| 1 | SIMPLE | test | range | time1 | time1 | 85 | NULL | 3922626 | Using where |
+—-+————-+——-+——-+—————+——-+———+——+———+————-+

　　2、在索引列上使用函数，或者对索引列进行运算，运算包括(+，-，*，/，! 等)会导致索引失效

　　例如：

mysql> EXPLAIN SELECT * FROM test.test WHERE id-1=153743;
+—-+————-+——-+——+—————+——+———+——+———+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+——+———+——+———+————-+
| 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 3922757 | Using where |
+—-+————-+——-+——+—————+——+———+——+———+————-+
mysql> EXPLAIN SELECT * FROM test.test WHERE id=153744;
+—-+————-+——-+——-+—————+———+———+——-+——+——-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——-+—————+———+———+——-+——+——-+
| 1 | SIMPLE | test | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+—-+————-+——-+——-+—————+———+———+——-+——+——-+

　　3、查询的数量是表的大部分，比如30％以上，这只是估算值看。

　　例如：

mysql> select count(*) from test.test4; //查看一下数据量
+———-+
| count(*) |
+———-+
| 200000 |
+———-+
mysql> ALTER TABLE `test`.`test4` ADD INDEX `id_num` (`id_num`); //添加索引
Query OK， 0 rows affected (0.46 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from test.test4 where id_num<60000; //满足条件的数据为6万条的情况
+—-+————-+——-+——+—————+——+———+——+——–+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+——+———+——+——–+————-+
| 1 | SIMPLE | test4 | ALL | id_num | NULL | NULL | NULL | 200307 | Using where |
+—-+————-+——-+——+—————+——+———+——+——–+————-+
mysql> explain select * from test.test4 where id_num<10000;//满足条件为1万条的情况
+—-+————-+——-+——-+—————+——–+———+——+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——-+—————+——–+———+——+——+————-+
| 1 | SIMPLE | test4 | range | id_num | id_num | 5 | NULL | 9998 | Using where |
+—-+————-+——-+——-+—————+——–+———+——+——+————-+
1 row in set (0.00 sec)

　　4、字符型字段为数字时在where条件里不添加引号

　　例如：

　　被测试数据库的表结构如下：

mysql>desc test;
+——-+————+——+—–+——————-+—————————–+
| Field | Type | Null | Key | Default | Extra |
+——-+————+——+—–+——————-+—————————–+
| id | int(11) | NO | PRI | NULL | auto_increment |
| time1 | char(42) | YES | MUL | NULL | |
| time2 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| time3 | int(11) | YES | | NULL | |
| stats | tinyint(1) | YES | MUL | NULL | |
+——-+————+——+—–+——————-+—————————–+

　　这是添加了引号的sql语句的执行计划：

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM test.test WHERE time1 =’20130517160342′;
+—-+————-+——-+——+—————+——-+———+——-+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+——-+———+——-+——+————-+
| 1 | SIMPLE | test | ref | time1 | time1 | 85 | const | 4281 | Using where |
+—-+————-+——-+——+—————+——-+———+——-+——+————-+

　　这是没有添加引号的sql语句的执行计划：

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM test.test WHERE time1 =20130517160342;
+—-+————-+——-+——+—————+——+———+——+———+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+——+———+——+———+————-+
| 1 | SIMPLE | test | ALL | time1 | NULL | NULL | NULL | 3922757 | Using where |
+—-+————-+——-+——+—————+——+———+——+———+————-+