MySQL-索引优化(八)

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

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高级也不难！