Re: 一个关于数据库索引的思考题--如何建立索引

我是码农，数据库肯定会用到。 如果索引区分度不高，这业务一定有问题，哪有一次取100w条数据的，该分页分页，该分表分表。 设计数据库的时候一定有一个使用场景，数据规模和各个字段的分布肯定是预先可以估计的。 设置索引的时候，需要考虑是否有效，依据更重要的是使用场景。 比如有的库插入/修改远大于查询，那么索引不能太多，会影响插入效率。比如有需要一次性导入大量的数据，这时甚至可以先删除索引，导入后再重建。。所以业务是很重要的。 而且实际使用中，对于索引的使用是随着业务随时变化的，随时保证索引满足所有的业务需求是很难的。一般给主要逻辑和区分度（比如主键）有索引就够用了。保证每次affected rows在1000以下就可以接受。 对于面向用户展示型应用，我们都限制只允许主键查询。当发生上述情况，并且affected rows在100w这样的量级，业务绝对有问题，扔后台做去吧。 【 在 Robberking 的大作中提到: 】 : 我不信你不懂数据库，phoneNumber 那句也许没有什么区分度，列名称可能是随便写的，没有实际意义。 不过你的回答真的很棒，很有启发性。 : 如果3句都没有什么过滤性结果会怎么样？如何验证你说的区分度大小呢？假设表有 1000 万数据，过滤完了 还有 100 万数据，会怎么样？ : : ...................

如果真有那么多 phoneNumber, locationID, Locationtype, order 应该会好一点，explain一下就知道 【 在 Robberking 的大作中提到: 】 : 我们可以改一下SQL ： : select destNumber, order : from m4001_routerArea : ...................

对于1： 不了解优化器在处理的时候是怎么走的，但哪条语句先走PhoneNumber的索引再进行LocationId效率比较高。（从业务的角度出发） 对于2： INDEX2(phoneNumber, locationID) 可以查询到需要的结果，但是需要额外的排序。可以进一步优化。 NDEX5(phoneNumber, locationID, Locationtype, order) 这个在叶结点上面会有一定的排序（先根据PhoneNumber排，在同一个区段再根据order或者其他属性排。）。检索的时候直接从叶节点拿到数据，而且根据order by order 排序只需要简单的正向或者逆向拿数据。不知道为什么这里需要LocationType。觉得这个效率最高吧 INDEX6(phoneNumber, locationID, Locationtype, order, destNumber) 这个觉得没必要，联合索引建得太大本身在优化上面难度就大，而且插入的开销也大。 （水水，答错了帮我指明错在哪，谢谢版上大虾）

【 在 Robberking 的大作中提到: 】 : 是的，如果 SQL 继续变化，这个怎么办呢？ :) 越来越刁钻了 : select destNumber, order : from m4001_routerArea : ................... 弱水三千，我只取一瓢，呵呵，对于这种情况，最好是在索引上就排好数据，拿的时候直接去符合条件的区段拿数据，不要把百万级的数据全部来一次排序再去前几，这样的时空开销就太大了

explain一下 【 在 Robberking 的大作中提到: 】 : 是的，如果 SQL 继续变化，这个怎么办呢？ :) 越来越刁钻了 : select destNumber, order : from m4001_routerArea : ...................

你的思路完全正确 对于 select destNumber, order from m4001_routerArea where phoneNumber > $mCalledMSISDN and locationID = $provinceID1 and Locationtype = '1' order by order asc fetch first 10 row only 我们需要一个新的index(locationID, Locationtype, order) 这时，我们把 phoneNumber>$mCalledMSISDN 做为 screening predicate , 这样我们只需要取到前10条记录而避免排序。 【 在 zhihao 的大作中提到: 】 : 弱水三千，我只取一瓢，呵呵，对于这种情况，最好是在索引上就排好数据，拿的时候直接去符合条件的区段拿数据，不要把百万级的数据全部来一次排序再去前几，这样的时空开销就太大了

如果我知道是它怎么执行的，我就知道当前的索引是否有效，然后就可以调整索引了啊。。 【 在 Robberking 的大作中提到: 】 : 首先非常感谢你的回复，但是作为一个优化器的developer, 优化器不会保证选到最优的查询计划，explain 只能知道优化器选到哪个plan 并不一定是最好的。而且 explain 并不能帮你优化当前 SQL，只能告诉你当前是如何执行的。 :

学习了 【 在 Robberking 的大作中提到: 】 : 你的思路完全正确 : 对于 : select destNumber, order : ...................

一般来说，我们都是在某个场景做测试，选取的数据和查询都是非常一般化的。 如果数据不平坦（我编的词。。就是在某个索引下的数据量差异非常大），这在一开始就会被注意到，测试的时候也会特别测试。。 可能作为数据库一般使用者，也不是DBA，我更关注实际的效果，通过调试来获得最佳结果。 【 在 Robberking 的大作中提到: 】 : 如果没有选用到你写的索引，不代表你的索引没有效。优化器会计算各个索引访问的 cost ，然后选择一个优化器认为 cost 最小的。 :