MySQL联合索引和索引不生效的情况研究

联合索引

测试前准备

mysql版本5.7.22

创建表结构:

CREATE TABLE tmp_table (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    age INT,
    msg VARCHAR(255)
    );

插入临时数据:

INSERT INTO tmp_table (name,age,msg) values ('andy',15,'a'),('jerry',20,'j'),('kelly',25,'k');

表内容:

id name age msg
1 andy 15 a
2 jerry 20 j
3 kelly 25 k

创建联合索引:

ALTER TABLE tmp_table add INDEX name_age_msg(name,age,msg);

测试联合索引的生效情况

语句 type key
explain select * from tmp_table where name = 'andy'\G; ref name_age_msg
explain select * from tmp_table where age = 20\G; index name_age_msg
explain select * from tmp_table where msg = 'a'\G; index name_age_msg
explain select * from tmp_table where name='andy' and age=15\G; ref name_age_msg
explain select * from tmp_table where name='andy' and age=15 and msg='a'\G; ref name_age_msg
explain select * from tmp_table where name='andy' and msg='a'\G; ref name_age_msg
explain select * from tmp_table where age=15 and msg='a'\G; index name_age_msg
explain select * from tmp_table where age=15 and msg='a' and name='andy'\G; ref name_age_msg
explain select * from tmp_table where age=15 or msg='a' or name='andy'\G; index name_age_msg
explain select * from tmp_table where age=15 or msg='a'\G; index name_age_msg
explain select * from tmp_table where name='andy' or age=15\G; index name_age_msg

接下来创建新的一列,并且此列没有索引

ALTER TABLE tmp_table ADD column location VARCHAR(255);

更新location的数据,不让其为NULL(避免值为NULL的列影响索引的使用,实际上值是否为NULL并不会对索引是否生效造成影响。这个下文会解释)

UPDATE tmp_table set location = 'text';

现在表中的内容如下:

id name age msg location
1 andy 15 a text
2 jerry 20 j text
3 kelly 25 k text

重复上面的语句,查看联合索引使用情况:

序号 语句 type key
1 explain select * from tmp_table where name = 'andy'\G; ref name_age_msg
2 explain select * from tmp_table where age = 20\G; ALL NULL
3 explain select * from tmp_table where msg = 'a'\G; ALL NULL
4 explain select * from tmp_table where name='andy' and age=15\G; ref name_age_msg
5 explain select * from tmp_table where name='andy' and age=15 and msg='a'\G; ref name_age_msg
6 explain select * from tmp_table where name='andy' and msg='a'\G; ref name_age_msg
7 explain select * from tmp_table where age=15 and msg='a'\G; ALL NULL
8 explain select * from tmp_table where age=15 and msg='a' and name='andy'\G; ref name_age_msg
9 explain select * from tmp_table where age=15 or msg='a' or name='andy'\G; ALL NULL
10 explain select * from tmp_table where age=15 or msg='a'\G; ALL NULL
11 explain select * from tmp_table where name='andy' or age=15\G; ALL NULL
12 explain select * from tmp_table where name='andy' or location='text'\G; ALL NULL
13 explain select * from tmp_table where name='andy' and msg='a' and location = 'text'\G; ref name_age_msg
14 explain select * from tmp_table where name='andy' and msg='a' or location = 'text'\G; ALL NULL
15 explain select * from tmp_table where name=NULL\G NULL NULL
16 explain select * from tmp_table where name=''\G ref name_age_msg
17 explain select * from tmp_table where name is null\G ref name_age_msg
18 explain select * from tmp_table where name is null and msg is null\G ref name_age_msg

结论和解释

如上例子,联合索引会创建(name),(name,age),(name,age,msg)这三个索引。

虽然看上去没有创建(name, msg)索引,但是通过explain select * from tmp_table where name='andy' and msg='a'\G;(表格中的第6条)语句可以发现还是使用到了联合索引。

联合索引遵循最左匹配原则,在where...and语句中必须包含name这个“最左字段”,不然的话联合索引不生效。

但是where...and语句中的联合索引这三个字段的顺序可以不一致,只要包含最左的字段就可以使索引生效,因为mysql的sql优化器会优化这些代码。

联合索引对or关系不起作用,必须要使用and作为条件

上面的实验结果存不存在location字段对explain出来的结果不一致的情况,原因是在没有加上location字段的时候全表都是有索引的,id是主键,剩余三个字段是联合索引,所以语句explain出来都是使用了index。而后面的实验添加了无索引的额外字段之后就使的测试结果恢复正常的预期了。

使用and作为条件查询,即使存在无索引的条件字段,只要存在有索引的列,explain的结果也会使用到索引,但是如果使用or作为条件查询,那么只要其中一个字段没有索引,就不会使用索引,而是全表扫描(例如表格中的第14条)

col=nullcol is null查询的区别

  1. col=null无法使用索引,并且也查询不出数据(对比上面15,和17条查询语句)
  2. 是否使用索引只与查询条件是否使用col=null有关,而与列的数据是否存在null值的行无关。

比如上面的name列,不管这一列是否存在null值的行,只要是使用col=null就不会使用索引,并且查询不到值,而使用col is null即会使用索引,也会查询出具体的行。

  1. 空字符串与null并不相等,且不可替换,针对这两个值的查询语句和结果都不一样

如果name列存在一行值为空字符串,则需要使用name=''条件来查询。并且可以使用索引,参考上面的第16条语句的explain结果。而不能使用name=null这样的查询。

至于有些文章里面提到的“联合索引中如果列的值包含NULL则此列对于联合索引就是无效的”,这一结论并不准确。

还是使用上面的表,我将age列中的其中一个数据改为NULL之后,使用语句explain select * from tmp_table where name='jerry' and age=20\G依然是使用到了索引。并且将name字段中其中一个值改为null,执行语句explain select * from tmp_table where name='jerry'\G同样使用到了索引,证明此结论并不准确。只有当查询条件中使用到了where name=null时此列的索引才不会生效,即使name字段中没有值为null。

索引不生效的情况

  1. 查询条件中包含“!=”或”<>”。

这种的情况针对普通索引,主键的情况下type为range,使用了主键作为key。

  1. 条件中有or,但是有其中一个or没有建立index

此时要使用index,需要把所有or查询字段都加上索引

  1. like查询以“%”开头

例如explain select * from tmp_table where name like '%sdf'\G;就没有使用索引,而like 'sdf%'就使用了

  1. 如果列类型是字符串,那要在条件中将数据用引号引用起来,即使查询的数据是一个字符串整型

例如explain select * from tmp_table where name='123'\G;使用到了索引,而where name=123则是全表扫描。

  1. 如果查询条件里面包含col=null则此列的索引不生效。并且也查询不出数据,必须使用col is null语句才能使索引生效。
  2. 如果mysql查询优化器估计使用全表扫描要比使用索引快,则不使用索引

参考

mysql 联合索引匹配原则
mysql联合索引

最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 213,186评论 6 492
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 90,858评论 3 387
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 158,620评论 0 348
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 56,888评论 1 285
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 66,009评论 6 385
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 50,149评论 1 291
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 39,204评论 3 412
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 37,956评论 0 268
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 44,385评论 1 303
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,698评论 2 327
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,863评论 1 341
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 34,544评论 4 335
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 40,185评论 3 317
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,899评论 0 21
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,141评论 1 267
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 46,684评论 2 362
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 43,750评论 2 351