MySQL 英文原文：MySQL Indexing Best Practices: Webinar Questions Followup

我之前发布的 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 直接的字符串是最好的

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

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

问：使用关联表时，是否应该在 (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 来做其他的查询。

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

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

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

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

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

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

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类型列。类似的情况下它确实会比增加一个索引好。

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

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

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

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

问：索引数量最大值是多少？

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

本文中的所有译文仅用于学习和交流目的，转载请务必注明文章译者、出处、和本文链接。 2KB翻译工作遵照 CC 协议，如果我们的工作有侵犯到您的权益，请及时联系我们。

2KB项目（www.2kb.com，源码交易平台）,提供担保交易、源码交易、虚拟商品、在家创业、在线创业、任务交易、网站设计、软件设计、网络兼职、站长交易、域名交易、链接买卖、网站交易、广告买卖、站长培训、建站美工等服务