假设存在这样顺序的一个联合索引:“col_1, col_2, col_3”。
× SELECT * FROM SomeTable WHERE col_1 = 10 AND col_3 = 500 ; × SELECT * FROM SomeTable WHERE col_2 = 100 AND col_3 = 500 ; × SELECT * FROM SomeTable WHERE col_2 = 100 AND col_1 = 10 ; ○ SELECT * FROM SomeTable WHERE col_1 = 10; ○ SELECT * FROM SomeTable WHERE col_1 = 10 AND col_2 = 100 ; ○ SELECT * FROM SomeTable WHERE col_1 = 10 AND col_2 = 100 AND col_3 = 500; (5)使用 OR在 col_1 和 col_2 上分别建立了不同的索引,或者建立了(col_1, col_2)这样的联合索引时,如果使用 OR 连接条件,那么要么用不到索引,要么用到了但是效率比 AND 要差很多。
WHERE col_1 > 100 OR col_2 = 'abc';如果无论如何都要使用OR,那么有一种办法是位图索引。但是这种索引的话更新数据时的性能开销会增大,所以使用之前需要权衡一下利弊。
(6)使用 LIKE 谓词进行后方一致或中间一致的匹配使用 LIKE 谓词时,只有前方一致的匹配才能用到索引。
× SELECT * FROM SomeTable WHERE col_1 LIKE '%a'; × SELECT * FROM SomeTable WHERE col_1 LIKE '%a%'; ○ SELECT * FROM SomeTable WHERE col_1 LIKE 'a%'; 十三、进阶 - SQL 与 集合论SQL 其中一个数学基础就是构建在集合论上。
我们通过画维恩图,可以很大程度地加深对 SQL 的理解。
1、全称量化和存在量化 (1)判断集合之间的包含关系SQL 并没有提供任何用于检查集合的包含关系或者相等性的谓词。IN 谓词只能用来检查元素是否属于某个集合(∈),而不能检查集合是否是某个集合的子集(∪)。
据说,IBM 过去研制的第一个关系数据库实验系统——System R 曾经实现了用 CONTAINS 这一谓词来检查集合间的包含关系,但是后来因为性能原因被删除掉了,直到现在也没有恢复。
而判断集合之间的包含关系,就是下面要提到的:全称量化。
(2)全称量词和存在量词“所有的 x 都满足条件P”或者“存在(至少一个)满足条件 P 的 x”。
前者称为“全称量词”,后者称为“存在量词”,分别记作 ∀ 、∃。
其实,全称量词的符号其实是将字母 A 上下颠倒而形成的,存在量词则是将字母 E 左右颠倒而形成的。
“对于所有的x,……”的英语是“for All x,…”,而“存在满足……的x”的英语是“there Exists x that…”,这就是这两个符号的由来。
但可惜,SQL 只支持 EXISTS(存在量词),不支持 FORALL(全称量词)。
但全称量词和存在量词只要定义了一个,另一个就可以被推导出来。
(3)全称量化 ⇔ 存在量化通过德·摩根定律,来进行 “肯定⇔双重否定” 之间的转换。
即在 SQL 中,为了表达全称量化,需要将 “所有的行都满足条件 P” 这样的命题转换成 “不存在不满足条件 P 的行“,然后使用存在量词。
例子1:
查询条件为肯定的:“所有科目分数都在50 分以上”,转换成它的双重否定:“没有一个科目分数不满50 分”,然后用 NOT EXISTS 来表示转换后的命题,即:
SELECT DISTINCT student_id FROM TestScores TS1 WHERE NOT EXISTS -- 不存在满足以下条件的行 ( SELECT * FROM TestScores TS2 WHERE TS2.student_id = TS1.student_id AND TS2.score < 50 -- 分数不满 50 分的科目 );例子2:
“所有队员都处于待命状态”转化成“不存在不处于待命状态的队员”