数据库索引的优化及SQL处理过程

如果列CITY有2000个不同的值，列BD有2700个不同的值，那么组合谓词的过滤因子就是： 1/2000*1/2700 。那么列组合[CITY,BD]总共有5400000个不同的值。

而对于有相关性的列，值会比这小很多。

我们在设计索引结构的时候，需要将SQL语句中的组合谓词看做一个整体来评估过滤因子。

过滤因子对索引设计的影响

很显然，需要扫描的索引片的大小对访问路径的性能影响至关重要。过滤因子越小，筛选出来的索引片的就越小，那就意味着访问表的次数越少。

假设表有联合索引 (MAKE, MODEL, YEAR)

对于sql语句:

SELECT PRICE, COLOR, DEALERNO 
FROM CAR 
WHERE MAKE = :MAKE 
 AND 
 MODEL = :MODEL 
ORDER BY PRICE 

MAKE 和 MODEL都是匹配列。如果组合谓词的过滤因子是0.1%，那么所需要访问的索引片大小将为整个索引的0.1%。

而对于下面这个sql语句，这个索引就不大好了：

SELECT PRICE, COLOR, DEALERNO 
FROM AUTO 
WHERE MAKE = :MAKE 
 AND 
 YEAR = :YEAR 

由于联合索引的最左匹配原则，匹配列只有MAKE。过滤因子为1%，索引片比较大。

sql语句：

SELECT LNAME, FNAME, CNO 
FROM CUST 
WHERE SEX='M' 
 AND 
 (WEIGHT > 90 
 OR 
 HEIGHT > 190) 
ORDER BY LNAME, FNAME 

这个SQL语句查找身材高大有一定要求的男性，此时匹配谓词只有一个SEX，过滤因子正常情况下为50%，如果表有100万行记录，那么索引片就有50万行，这就是相当厚的索引片了。

练习

思考一下为以下两个SQL语句设计最佳的索引

SELECT LNAME, FNAME, CNO 
FROM CUST 
WHERE SEX = 'M' 
 AND 
 HEIGHT > 190 
ORDER BY LNAME, FNAME 
SELECT LNAME, FNAME, CNO 
FROM CUST 
WHERE SEX = 'M' 
 AND 
 (WHIGHT > 90 
 OR 
 HEIGHT > 190) 
ORDER BY LNAME, FNAME 

【编辑推荐】

1. 程序员必备！关系型数据库架构的超强总结
2. 一文告诉你全世界头部的开发者都在使用什么数据库
3. SQLite AUTOINCREMENT你需要懂得知识
4. 分库分表 or NewSQL数据库？终于看懂应该怎么选！
5. 记一次生产数据库优化--定期归档大表

（编辑：晋中站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!