MySQL-mysql建立最佳索引

WordPress 开发 WordPress 开发 主题:1098 回复:2322

MySQL-mysql建立最佳索引

晚风撩人 发布于 2017-05-19 字数 244 浏览 1215 回复 8

表现在有其中3个字段,a int(10),b int(10) c datatime
有这么几个常用的查询,该怎么建立索引最为合适
where a = 11 and b = 22
where b = 22
where b > 33 order by c desc
顺便问一下联合索引的内部实际存储结构是怎样的?

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

支持 Markdown 语法,需要帮助?

评论(8

甜柠檬 2017-10-15 8 楼

我之前写过一篇很详细关于mysql索引的文章,请见http://www.codinglabs.org/html/theory-of-mysql-index.html

晚风撩人 2017-10-01 7 楼

“如果条件中包含A=?时必定会同时有B=?的条件,则建立联合索引是比较好的。如果条件A=?会单独查询且机会较多,则单独对A列建一个索引会更好。”

是的,针对 “where b > 33 order by c desc”,
首先你要先了解下你的业务增长量,并发量,实际生产过程中还要考虑存储容量问题,这个排序我觉得
建立索引时有排序语法则要使用,如果表的数量级较大而且返回查询结果较多的话,针对排序资源消耗是不能接受的,容易造成阻塞。

泛泛之交 2017-08-28 6 楼

个人觉得,从索引频率分析,在b字段上创建索引比较好。
如果没有索引数量限制,则a字段上也建立一个
第三种情况:where b > 33 order by c desc,刨去b字段索引,仅仅只是一个结果集排序。
关键在于你的搜索条件,与索引频率以及采用数据库类型,你不妨做一个测试表测试索引查询效率。实际效率才是硬道理
仅个人建议

想挽留 2017-07-29 5 楼

如果条件中包含A=?时必定会同时有B=?的条件,则建立联合索引是比较好的。如果条件A=?会单独查询且机会较多,则单独对A列建一个索引会更好。

归属感 2017-07-27 4 楼

针对这种情况:where a = 11 and b = 22
当然是需要建复合索引比较好,建a,b复合索引,这样,可以直接通过这个复合索引命中目标

针对这种情况:where b = 22
需要单独对b列建立索引即可

第三种情况:where b > 33 order by c desc
因为已经有了b列单独索引,则不再需要现建索引,因为c列只需在结果中按c列值排序,即使创建索引,也没有真正使用索引,所以两种索引足矣!

最后总结如下:
需要建立这两种索引
ALTER TABLE table_name ADD INDEX (a,b);
ALTER TABLE table_name ADD INDEX (b);

复合索引,可以想像成存在一起的结构体,不可拆开分别建索引!

其实到这里,已经是一个不错的索引方案了,不过,我们还可以根据“最左前缀(Leftmost Prefixing)”方针,对索引再次优化,即:我们只需建立b,a复合索引,则相当于建立了b,a索引与b索引,所以:
ALTER TABLE table_name ADD INDEX (b,a);
就可以了,至于查询时:where a = 11 and b = 22
是否需要把b提到前面,感觉不是很必要,因为mysql会自己优化,所以,你可以写成where b = 22
and a = 11
不写也是可以的!

瑾兮 2017-07-23 3 楼

建议你把第一个条件改为 b = 22 and a = 11,你可以建两个索引ALTER TABLE table_name ADD INDEX (b,a)和ALTER TABLE table_name ADD INDEX (b,c)。

多列索引有一个优点,它通过称为 最左前缀(Leftmost Prefixing)的概念体现出来。现在我们有一个(a,b,c)的多列索引,索引名为abc.当搜索条件是以下各种列的组合时,MySQL将使用abc索引:它相当于我们创建了(a,b,c)、(a,b)以及(a)这些列组合上的索引。

甜柠檬 2017-07-14 2 楼

建复合索引的时候,要注意和你的 sql 里 where 中的顺序保持一致,否则会大打折扣,甚至不能起到索引到作用。
还要注意复合索引实际是根据最左前缀 起了多个索引的作用
所以 修改第一个查询的顺序 b = 22 and a = 11
然后建立 (b,a) 索引和(b,c) 索引

清晨说ぺ晚安 2017-06-10 1 楼

索引建立不是越多越好,我觉得只在 b 列上建立索引即可。

那么
where a = 11 and b = 22
where b = 22
where b > 33 order by c desc

以上三种查询都会用到索引,分别是:ref , ref, range

我觉得没必要在 bc列 建立联合索引,因为即使建立bc索引 where b > 33 order by c desc,排序方式也是 Using filesort ,也就说没有利用索引排序,同样是文件排序。

还有 我觉得也没有必要在ba列建联合索引,如果建立该索引只对where a = 11 and b = 22 有点作用,type 都是 ref, 不建立的话只是需要 使用条件再过滤结果。当然这个根据情况来定,如果 b 字段重复的值很多,那么在ba列建立索引有很大作用。

还有我认为不用修改查询条件的顺序为 b = 22 and a = 11

如果在 a 和 b 字段建立建立索引:

ALTER TABLE table_name ADD INDEX ab(a,b);

那么 b = 22 and a = 11 与 a = 22 and b = 11 执行计划是一样的,但是经过优化器优化过的sql,是与条件顺序相反的。结果如下:

mysql> alter table test add index ab(a,b);

mysql> explain extended select * from test where a=54 and b=12G
1. row
id: 1
select_type: SIMPLE
table: test
type: ref
possible_keys: ab
key: ab
key_len: 8
ref: const,const
rows: 1
Extra:

select test.test.id AS id,test.test.a AS a,tes t.test.b AS b,test.test.c AS c from test.test where ((test.
test.b = 12) and (test.test.a = 54))

============================================================

mysql> explain extended select * from test where b=54 and a=12G
1. row
id: 1
select_type: SIMPLE
table: test
type: ref
possible_keys: ab
key: ab
key_len: 8
ref: const,const
rows: 1
Extra:

select test.test.id AS id,test.test.a AS a,tes t.test.b AS b,test.test.c AS c from test.test where ((test.
test.a = 12) and (test.test.b = 54))