MySQL索引失效分析与优化( 三 ) _MySQL

通配符号写最右

索引字段使用like以通配符开头(%字符串)时，会导致索引失效而转向全表扫描。尽量以(字符串%)方式写语句。如果一定要使用%开头，业务满足的话，可以配合覆盖索引进行一定优化

# 以%开头，索引失效，变成全表扫描mysql> explain select * from tuser where name like '%fdco' G;*************************** 1. row ***************************id: 1select_type: SIMPLEtable: tuserpartitions: NULLtype: ALLpossible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 7filtered: 14.29Extra: Using where# 以%结尾，可以使用到索引mysql> explain select * from tuser where name like 'fdco%' G;*************************** 1. row ***************************id: 1select_type: SIMPLEtable: tuserpartitions: NULLtype: rangepossible_keys: idx_name_age_sexkey: idx_name_age_sexkey_len: 303ref: NULLrows: 1filtered: 100.00Extra: Using index condition# 如果业务上只需要返回索引中的字段，则可以将*改为索引字段，形成覆盖索引，可以提升性能mysql> explain select name,age,sex from tuser where name like '%fdcofd' G;*************************** 1. row ***************************id: 1select_type: SIMPLEtable: tuserpartitions: NULLtype: indexpossible_keys: NULLkey: idx_name_age_sexkey_len: 312ref: NULLrows: 7filtered: 14.29Extra: Using where; Using index

字符要加单引号

【MySQL索引失效分析与优化】索引字段是字符串，但查询时不加单引号，形成隐式类型转换，相当于使用了函数进行计算，会导致索引失效而转向全表扫描

# 字符串类型的字段，传入一个数字类型，导致索引失效全表扫描mysql> explain select * from tuser where name = 7 G;*************************** 1. row ***************************id: 1select_type: SIMPLEtable: tuserpartitions: NULLtype: ALLpossible_keys: idx_name_age_sexkey: NULLkey_len: NULLref: NULLrows: 7filtered: 14.29Extra: Using where# 数字类型的age可以传入字符串的数字，索引有效，但不建议这样去使用mysql> explain select * from tuser where name = 'ccc' and age = '7' and sex = 'M' G;*************************** 1. row ***************************id: 1select_type: SIMPLEtable: tuserpartitions: NULLtype: refpossible_keys: idx_name_age_sexkey: idx_name_age_sexkey_len: 312ref: const,const,constrows: 1filtered: 100.00Extra: NULL

索引字段不用or

索引字段使用or时，会导致索引失效（这个似乎MySQL有做优化，5.7版本测试可以使用索引，后面再详细测试）

# 5.7版本测试下，同一个索引字段用or时可以使用到索引的mysql> explain select * from tuser where loginname = 'fd' or loginname = 'coffee' G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: tuserpartitions: NULLtype: rangepossible_keys: idx_loginnamekey: idx_loginnamekey_len: 303ref: NULLrows: 2filtered: 100.00Extra: Using index condition#5.7版本测试下，不同索引字段用or则变成全表扫描mysql> explain select * from tuser where name='fdcoffee' or age = 7 G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: tuserpartitions: NULLtype: ALLpossible_keys: idx_name_age_sexkey: NULLkey_len: NULLref: NULLrows: 7filtered: 26.53Extra: Using where# 同上mysql> explain select * from tuser where name='fdcoffee' and age = 7 or sex = 'M' G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: tuserpartitions: NULLtype: ALLpossible_keys: idx_name_age_sexkey: NULLkey_len: NULLref: NULLrows: 7filtered: 16.03Extra: Using where

索引优化优化索引，其实就是要遵守索引的使用规则，然后避免出现导致索引失效的情况。通过EXPLAIN可以发现索引失效的情况，然后按照使用规则纠正即可。下面是流传的优化口诀，简单实用易记。
全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
LIKE百分写最右，覆盖索引不写星；
不等空值还有or，索引失效要少用。