MySQL 索引最佳实践之问题反馈 已翻译 100%

oschina 投递于 2013/01/09 08:40 (共 19 段, 翻译完成于 01-23)
阅读 6145
收藏 116
9
加载中

我之前发布的 PPT —— 《MySQL 索引最佳实践》中,有很多人提了很多问题,我没有时间一一回答,于是我决定把这些问题集中在一起进行回答。

问:我们团队中的一人想要使用 bigint 字段类型来代替 25-30 长度的 varchar 类型来存储 CRC64 数据,然后将索引也改成 bigint 的索引,这会节省索引的空间。请问这否是合理的性能优化方法?(需要注意到是自适应前缀哈希在这里是无效的,因为前 10 个字符重复率很高,因此 UNIQUE 索引不适合用在这里)

答:在很多时候,这是一个很好的优化方法。考虑到哈希碰撞的问题,你可以使用形如 SELECT * FROM TBL WHERE hash=crc32(‘string’) AND string=’string’这样的查询。另外还需要注意的是 MySQL 里的字符串比较默认是不区分大小写的,对于哈希字符串也是如此。另外与 25~30 字节长度的字符串来说,使用 8 位的 bigint 在索引存储上的差异并不大。你所采用的这项技术对超过 100 直接的字符串是最好的

红薯
翻译于 2013/01/09 08:41
8

问:ORDER BY 的优化问题:select * from table where A=xxx and B between 100 and 200 order by B 是很常见的日期范围的查询,并且需要对结果进行排序。问题是这样的查询如何设置最佳的索引?

答:实际上这种情况在 (A,B) 建索引就很好了,但如果你需要使用第三个列(C)作排序,那么复合索引 (A,B,C) 就会导致排序无法使用索引,因此你可以我在PPT里介绍的将排序转成小范围数据的联合来处理。

红薯
翻译于 2013/01/09 08:41
3

问:使用关联表时,是否应该在 (foreignkey1,foreignkey2) 和 (foreignkey2,foreignkey1) 上建索引?

答:是的,这是一个好的实践方法。正常情况下如果我需要使用不同查询做双向遍历时,我会这样创建表:CREATE TABLE LINK (id1 int unsigned not null ,id2 int unsigned not null, PRIMARY KEY(id1,id2), KEY K(id2,id1)) engine=INNODB; 这个将使用更快的主键来做一些查询,然后使用索引 K 来做其他的查询。

红薯
翻译于 2013/01/09 08:42
2

问: 技巧#1“WHERE a IN (2-4)”的效率比“WHERE a IN (2,3,4)”低吗?换句话说,在范围查询中IN比BETWEEN效率高吗?

A: IN(2-4) 并不是你想要的那样。2-4被看做是一个数学公式,结果是IN(-2),而不是你想要的结果.

缪斯的情人
翻译于 2013/01/09 19:56
1

Q: 有一个主索引在int(ID),还有另外一些复合索引在idx1(X,A,B,C) idx2(Y,A,B,C)等等(一共有五个).是不是把A,B,C,ID设置为主索引,把另外的索引设置在一列,像这样idx(X),idx2(Y),这样是不是更好?

A: 相较于设置5个复合索引和仅仅设置第一列为索引,那一个才是最好的配置. 关于改变主键来包含这么多的列前缀取决你准备搜索什么. 通过这些列的数据分组有利于你很多方面的搜索,但是也会减慢插入操作以及使主键显得很碎,很分散. 并且在处理把主键追加到索引的时候MYSQL也有很多的限制。尤其是你说的这种情况.最后我们要注意的是这种独特的设置是不是对系统性能有很大的提升.

-V-
-V-
翻译于 2013/01/23 11:27
1

问:Table1有一主键,Table2 用Table1的当前这个主键来关联Table1,Table2上两个表存在关联的那个字段有必要建立索引吗?

答:这个问题涉及到MySQL执行join的问题。如果首先是用别的索引字段来检索Table2,然后再到Table1通过Table1的主键索引来查找行数据,这样就没有必要在Table2关联的那个字段上建一个索引。



nuowei08
翻译于 2013/01/14 20:31
1

Q: 关于扩展原有的索引是否比新添加一个索引更好:我有张名叫PO的表,拥有一个主键以及两个额外的字段vendor_id 和order_id。如果我有一个索引基于vendor_id和order_id,但是我的查询语句仅选择了vendor,这个索引是否会影响到该查询的速度?

A: 如果你将该索引从 (vendor_id) 扩展至 (vendor_id,order_id),却只用到vendor_id,你将会因为增加了4个字节的长度(假设order_id是int类型)而影响你的查询,但可能并不明显。不过除了单独基于(vendor_id)的索引,比起对(vendor_id,order_id)建立另一个索引,它似乎能极大的减少系统花费。这个例子里你真正需要担心的会因扩展索引而造成影响的是当你大幅度增加他的长度,例如增加long varchar类型列。类似的情况下它确实会比增加一个索引好。


埼玉老师
翻译于 2013/01/15 17:18
1

Q: 我们有个数据库大概有400G的索引,这么多索引不可能全部读取到内存了.这样会影响系统性能吗?

A: 通常你不必把所有的索引都放入内存,只需要让一部分频繁进出内存. 这部分工作区的大小取决于应用程序,范围大小是总内存的5%至100%. 合适的工作区大小和不合适的工作区大小会让系统性能相差10倍以上.

-V-
-V-
翻译于 2013/01/14 11:05
2

问:在什么情况下使用自增字段为主键?

答:自增字段是一个很好的默认主键。在以下几种情况下你应该选择别的——如果一个字段有助于你在各个方面的数据分组或者这个字段被频繁的在查询中使用.

-V-
-V-
翻译于 2013/01/10 16:00
1

问:索引数量最大值是多少?

答:在最新的MYSQL版本里,每个表最多可以有64个索引.这已经太多了。相比于考虑索引数量的最大值,我更倾向于考虑添加索引是否有利于提高系统性能;添加索引增加的收益是否小于系统性能的降低

-V-
-V-
翻译于 2013/01/11 10:12
1
本文中的所有译文仅用于学习和交流目的,转载请务必注明文章译者、出处、和本文链接。
我们的翻译工作遵照 CC 协议,如果我们的工作有侵犯到您的权益,请及时联系我们。
加载中

评论(11)

g
grey256
“另外还需要注意的是 MySQL 里的字符串比较默认是不区分大小写的,对于哈希字符串也是如此”
我看mysql手册(5.7)里面的例子 crc32('MYSQL') 得到3259397556,而crc32('mysql')得到的是2501908538,自己测了下大小写是有影响的(5.5)。
Pader
Pader

引用来自“苏翰”的评论

第二个问题为什么“复合索引 (A,B,C) 就会导致排序无法使用索引”?

因为B的 BETWEEN..IN 得到的是一系列的值,而不是单一的值,而按照BTREE的原理,C是B的子树,那么查到B的时候就得到的是一堆C的子树,这时候C就失去作用了。大家可以去多看看BTREE相关的知识。

这里讲到的“将排序转成小范围数据的联合来处理”不知道是如何做的。MySQL 在实现这种 BETWEEN..IN 和 IN() 的范围查询后再对结果集进行排序是一个软肋,但这也并不是 MySQL 的缺陷,而是所有数据库都必备的BTREE索引的一个软肋。
pyruby
pyruby
同楼上问
苏生不惑
苏生不惑
第二个问题为什么“复合索引 (A,B,C) 就会导致排序无法使用索引”?
pyruby
pyruby
文章中最好再举几个更实际点例子,就更好理解了
今天天气不错
今天天气不错
使用BETWEEN的写法转换成IN来查询日期段,以天为单位那好点,如果存储的时间戳精确到秒,同时范围在一周,是罗列出in(所有的秒)快还是between快?前者构造的sql不知道有多大。。。数据不会堵塞?
恺哥
恺哥

引用来自“Khiyuan”的评论

引用来自“恺哥”的评论

感觉老外说话真啰嗦,那些啰嗦的话我个人觉得不翻译为好,翻译出来反倒不通顺且迷惑

我也有同感!
但是翻译的时候,却又不能随便的省略掉,因为可能会造成前后译文的脱节,也生怕遗漏掉作者的本意。
翻译,本来就不是纯粹的写作,其旨为传递作者的思考结果。
特别是技术类翻译,又与文学类不同,文学类达意即可,技术类,还是兢兢业业为好。

赞成"翻译,本来就不是纯粹的写作,其旨为传递作者的思考结果"这句话
K6F
K6F

引用来自“恺哥”的评论

感觉老外说话真啰嗦,那些啰嗦的话我个人觉得不翻译为好,翻译出来反倒不通顺且迷惑

我也有同感!
但是翻译的时候,却又不能随便的省略掉,因为可能会造成前后译文的脱节,也生怕遗漏掉作者的本意。
翻译,本来就不是纯粹的写作,其旨为传递作者的思考结果。
特别是技术类翻译,又与文学类不同,文学类达意即可,技术类,还是兢兢业业为好。
华兹格
华兹格

引用来自“sunyh”的评论

翻译的文章 为啥不 列出 原文 好对照啊。

你可以点右上角的:中英文对照 啊,有的!
sunyh
sunyh
翻译的文章 为啥不 列出 原文 好对照啊。
返回顶部
顶部