16.索引优化(八)

字符串不加单引号索引会失效

mysql> select * from staffs where name=2000;
+----+------+-----+-----+---------------------+
| id | name | age | pos | add_time            |
+----+------+-----+-----+---------------------+
|  3 | 2000 |  23 | dev | 2017-05-08 20:48:58 |
+----+------+-----+-----+---------------------+
1 row in set, 1 warning (0.01 sec)


mysql> select * from staffs where name=july;
ERROR 1054 (42S22): Unknown column 'july' in 'where clause'

当name为数字时,不加单引号,内部进行了类型转换,所以导致索引失效。

mysql> explain select * from staffs where name=2000;
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys         | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | idx_staffs_nameAgePos | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)

少用or, 用它来连接时会索引失效

mysql> explain select * from staffs where name='july' or name='zs';
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys         | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | idx_staffs_nameAgePos | NULL | NULL    | NULL |    3 |    66.67 | Using where |
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

口诀总结

  • 1.带头大哥不能死
  • 2.中间兄弟不能断,永远要符合左前缀法则
  • 3.索引列上无计算
  • 4.like 百分加右边
  • 5.范围后面全失效
  • 6.字符串里有引号

题目总结:

假设index(a,b,c)

where语句 索引是否被使用
where a = 3 Y, 使用到a
where a = 3 and b = 5 Y, 使用到a,b
where a = 3 and b = 5 and c = 4 Y, 使用到 a, b, c
where a = 3 或者 where b = 3 and c = 4 或者 c = 4 N
where a = 3 and c = 5 使用到a, 但是c不可以,b中间断了
where a = 3 and b > 4 and c = 5 使用到a和b,c不能被使用,由于在范围之后
where a = 3 and b like 'kk%' and c = 5 Y, 使用到a,b,c
where a = 3 and b like '%kk' and c = 5 Y,只能用到a
where a = 3 and b like '%kk%' and c = 5 Y,只能用到a
where a = 3 and b like 'K%kk%' and c = 5 Y,使用到a,b,c

[优化总结口诀]

  • 全值匹配我最受,最左前缀要遵守;
  • 带头大哥不能死,中间兄弟不能断;
  • 索引列上少计算,范围之后全失效;
  • LIKE百分写最右,覆盖索引不写星
  • 不等空值还有or,索引失效要少用;
  • VAR引号不可丢,SQL高级也不难!