江苏龙网

  1. 主页 > 生活百科 > >

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

mysql索引失效

  • 最佳左前缀法则
组合索引中，需要按照最左前缀原则使用索引字段才会有效：带头索引不能丢，中间索引不能断
# 正确示例mysql> explain select * from tuser where name = 'fdcoffee' and age = 36 and sex = 'M' G;mysql> explain select * from tuser where name = 'fdcoffee' and age = 36 G;mysql> explain select * from tuser where name = 'fdcoffee' G;# 错误示例# 缺少带头索引name，剩下的age和sex字段都无法使用索引mysql> explain select * from tuser where age = 36 and sex = 'M' G;# 同上，没有前面的name和age字段一起，sex字段无法使用到索引mysql> explain select * from tuser where sex = 'M' G;# 缺少中间索引age,只能使用到部分索引：name字段有效，但sex字段无法用到索引mysql> explain select * from tuser where name = 'fdcoffee' and sex = 'M' G;
  • 索引列上不计算
不要在索引列上进行计算操作：计算、函数、自动/手动类型转换，不然会导致索引失效而转向全表扫描
# 使用left函数对loginname长度截取，索引失效mysql> explain select * from tuser where left(loginname,4) = 'fdco' G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: tuserpartitions: NULLtype: ALLpossible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 7filtered: 100.00Extra: Using where# 上面的示例可以这样优化：使用 like% 或在程序上先对loginname做处理在传入MySQL数据库查询mysql> explain select * from tuser where loginname like 'fdco%' G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: tuserpartitions: NULLtype: rangepossible_keys: idx_loginnamekey: idx_loginnamekey_len: 303ref: NULLrows: 2filtered: 100.00Extra: Using index condition
  • 范围右边列失效
不能继续使用索引中范围条件(between、<、>、in等)右边的列
# 由于age使用范围操作符，后面的sex字段索引失效mysql> explain select * from tuser where name = 'fdcoffee' and age > 20 and sex = 'M' G;*************************** 1. row ***************************id: 1select_type: SIMPLEtable: tuserpartitions: NULLtype: rangepossible_keys: idx_name_age_sexkey: idx_name_age_sexkey_len: 308ref: NULLrows: 1filtered: 14.29Extra: Using index condition
  • 尽量用覆盖索引
尽量使用覆盖索引(select 索引列)，也就是查询列和索引列一致，减少使用select *
# 使用select *全表扫描mysql> explain select * from tuser G;*************************** 1. row ***************************id: 1select_type: SIMPLEtable: tuserpartitions: NULLtype: ALLpossible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 7filtered: 100.00Extra: NULL# 使用select 索引列时，扫描索引，不需要回表mysql> explain select name,age,sex from tuser G;*************************** 1. row ***************************id: 1select_type: SIMPLEtable: tuserpartitions: NULLtype: indexpossible_keys: NULLkey: idx_name_age_sexkey_len: 312ref: NULLrows: 7filtered: 100.00Extra: Using index
  • 不等判断不要用
索引字段上使用不等(!=或者<>)判断时，会导致索引失效而转向全表扫描
mysql> explain select * from tuser where name != 'fdcoffee' G;*************************** 1. row ***************************id: 1select_type: SIMPLEtable: tuserpartitions: NULLtype: ALL# 全表扫描possible_keys: idx_name_age_sexkey: NULLkey_len: NULLref: NULLrows: 7filtered: 100.00Extra: Using where
  • 索引不做空判断
索引字段上使用is null/is not null判断会走全表扫描 。允许为null的索引字段呢？看下面分析 。
# 主键idmysql> explain select * from tuser where id is not null G;*************************** 1. row ***************************id: 1select_type: SIMPLEtable: tuserpartitions: NULLtype: ALLpossible_keys: PRIMARYkey: NULLkey_len: NULLref: NULLrows: 7filtered: 85.71Extra: Using where# 由于主键不允许为空，使用is null，执行计划信息Extra列将提示Impossible WHERE信息mysql> explain select * from tuser where id is null G;*************************** 1. row ***************************id: 1select_type: SIMPLEtable: NULLpartitions: NULLtype: NULLpossible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: NULLfiltered: NULLExtra: Impossible WHERE# 允许为空的索引# 使用is not null将走全表扫描mysql> explain select * from tuser where loginname is not null G;*************************** 1. row ***************************id: 1select_type: SIMPLEtable: tuserpartitions: NULLtype: ALLpossible_keys: idx_loginnamekey: NULLkey_len: NULLref: NULLrows: 7filtered: 100.00Extra: Using where# 而is null则用到了索引mysql> explain select * from tuser where loginname is null G;*************************** 1. row ***************************id: 1select_type: SIMPLEtable: tuserpartitions: NULLtype: refpossible_keys: idx_loginnamekey: idx_loginnamekey_len: 303ref: constrows: 1filtered: 100.00Extra: Using index condition


推荐阅读


上一篇：库存管理系统有哪些功能 淘宝卖家仓库管理软件哪个好

下一篇：淘宝店铺拉新 淘宝拉新怎么做