组合索引中,需要按照最左前缀原则使用索引字段才会有效:带头索引不能丢,中间索引不能断
# 正确示例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
推荐阅读
-
浩燃娱塘|为照顾生病母亲,有戏找他都不拍!,他是很接地气的演员
-
暮年|为实用而生!友商这次真拼,性价比定价媲美中端体验?
-
-
什么值得买■升级!,给旧电脑做一次“ SPA ”,清灰加硅脂,DIY
-
-
人民日报客户端|17至19日长江上中游及淮河流域将有强降雨
-
高校|国内最容易被忽视的3所211高校,毕业轻松进名企,录取分数却不高!
-
-
账单|快查查!2020微信年度账单上线,网友:我哪来这么多钱?
-
-
励志图文影视|别以为世界抛弃了你,世界根本没空搭理你,伤感
-
穿搭|小哥露出性感肌肉,穿着古风十足,别有一番风味
-
武神:戈兰高地归叙利亚还是以色列?内塔尼亚胡与普京通电话
-
方媛|方媛连续四年卡点为老公庆生,郭富城打扮帅气低头许愿,满脸幸福
-
虎扑篮球|和最爱的人享受劳动果实,丹尼尔斯晒照:我们创造了历史
-
穿搭|时尚界永远的“蕾丝”,只要掌握好这样的搭配,一周让你美7天
-
-
血魔|名刀司命是射手的保命装?醒醒吧,这件2120才是,生命值轻松过万
-
-
辽篮|今日!CBA5场对决,辽篮+首钢为争第3排名隔空暗战,央视这样直播