医疗 网站前置审批,介绍小说的网站模板下载,wordpress options,央视十大广告代理公司一.前言
正确的顺序依赖于使用索引的查询,并且同时需要考虑如何更好地满足排序和分组的需要。因为哈希或者其他类型的索引并不会像 B-Tree索引一样顺序存储数据,所以这里只针对B-Tree展开讨论。
二.合适的索引顺序
1. 概念
对于如何选择索引顺序有一个经验法则: 将选择性最…一.前言
正确的顺序依赖于使用索引的查询,并且同时需要考虑如何更好地满足排序和分组的需要。因为哈希或者其他类型的索引并不会像 B-Tree索引一样顺序存储数据,所以这里只针对B-Tree展开讨论。
二.合适的索引顺序
1. 概念
对于如何选择索引顺序有一个经验法则: 将选择性最高的列放在索引最前列。当不需要考虑排序和分组时,将选择性最高的列放在前面通常是很好的这时索引的作用只是用于优化 Where 条件的查找。在这种情况下,这样设计的索引确实能够最快地过滤出需要的行,对于在Where子句中只使用了索引前缀列的查询来说性能也更高。然而,性能不只是依赖于索引索引列的选择性(整体基数),也和查询条件的具体值有关,也就是和值分布有关。
2. 案例
下面有个查询:
SELECT * FROM payment WHERE staff_id 2 AND customer_id 584;那按照平时的最左前缀原则,我们是不是建一个(staff_id,customer_id)的索引合适呢 其实不然,我们可以跑一些查询来确定在这个表中值的分布情况,并确定哪个列的选择性更高。 2.1. 查看Where条件分支对应的基数有多大
SELECT SUM(staff_id2),SUM(customer_id584) FROM payment;根据前面的经验法则,应该将所有列customer_id 放在前面,因为对应条件值的 customer_id数量更小。我们在来看看对于这个customer_id 的条件值,对应的staff_id 列的选择性如何:
SELECT SUM(staff_id2) FROM payment WHERE customer_id584这样做有一个地方需要注意,查询的结果非常依赖于选定的具体值。如果按上述方法优化,可能对其他一些条件值的查询不公平,服务器的整体性可能变得更糟,或者其他某些查询的运行变得不如预期。
2.2. 按照全局基数和选择性
如果是从诸如 pt-query-digest这样的工具的报告中提取最差查询,那么再按上述方法选定的索引顺序往往是非常高效的。如果没有类似具体的查询类运行,那么最好还是按经验法则来做,因为经验法则考虑的是全局基数和选择性。
SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity,
COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,COUNT(*) FROM payment;从上面的结果也可以看出 customer_id 的选择性更高,所以答案是将其作为索引列的第一列:
ALTER TABLE payment ADD KEY(customer_id, staff_id);3. 选择性不高的索引列
当使用前缀索引的时候,在某些条件值的基数比正常值高的时候,问题就来了。例如,在某些应用程序中,对于没有登录的用户,都将其用户名记录为guest,在记录用户行为的会话(session)表和其他记录用户活动的表中guset就成为一个特殊的ID。一旦涉及到这个查询,可能性能就会大不相同。
下面给出一个真实案例,在一个用户分享购买商品和购买经验的论坛上,这个特殊表上的查询运行得非常慢:
SELECT COUNT(DISTINCT threadId) AS COUNT_VALUE FROM Message
WHERE (groupId10137) AND (userId1288826) AND (anoymous0)
ORDER BY priority DESC, modifiedDate DESC执行计划的结果:
看上去已经建立了索引(groupId,userId),如果不考虑列的基数这看起来是一个非常合理的选择,但如果考虑一下userId 和groupId条件匹配的行数,可能会有不同的想法了:
SELECT COUNT(*), SUM(groupId10137),SUM(userId1288826),
SUM(anoymous0) FROM Message从上面的结果可以看出符合组(groupId)条件几乎满足表中的所有行,符合用户(userId)条件有130万条记录——也就是说索引基本上没什么作用。这里从索引层面上已经解决不了问题,我们可以从业务上区分这类特殊用户和组,禁止针对这类用户和组执行这个查询。
三.总结
从上面的案例可以看到经验法则和推论在多数情况下是有用的,但注意不要假设平均情况下的性能也能代表特殊情况下的性能,特殊情况可能会摧毁整个应用的性能。最后,尽管关于选择性和技术的经验法则值得去研究和分析,但Where子句中的排序,分组和范围条件等其他因素对查询的性能也会造成非常大的影响。
