where 子句中易混淆的条件

数字格式的字符串

SELECT …
   FROM …
  WHERE numeric_string = 42

如果将一个字符串和一个数字作比较，数据库会隐式把字符串转换成数字。此时，在 NUMERIC_STRING 字段上建立的索引不能起作用。

合并多个字段

SELECT …
   FROM …
  WHERE ADDTIME(date_column, time_column)
      > DATE_ADD(now(), INTERVAL -1 DAY)

这个查询不能直接使用联合索引 (DATE_COLUMN, TIME_COLUMN)，因为搜索使用的字段是由这两个字段派生而成的。

可以使用基于函数的索引，把派生字段加到索引中。

或者可以在 where 中增加一个对 DATE_COLUMN 的筛选：

WHERE ADDTIME(date_column, time_column)
      > DATE_ADD(now(), INTERVAL -1 DAY)
    AND date_column
     >= DATE(DATE_ADD(now(), INTERVAL -1 DAY))

虽然这个条件看起来多余，但是可以使用索引帮助减少要检索的数据范围。

有时候我们也会希望不使用索引，就像之前提到的 like 表达式中通配符%在首位的时候。

SELECT last_name, first_name, employee_id
   FROM employees
  WHERE subsidiary_id = ?
    AND last_name  '' LIKE ?

动态 SQL

SELECT first_name, last_name, subsidiary_id, employee_id
   FROM employees
  WHERE ( subsidiary_id    = @sub_id OR @sub_id IS NULL )
    AND ( employee_id      = @emp_id OR @emp_id IS NULL )
    AND ( UPPER(last_name) = @name   OR @name   IS NULL )

这个 query 看似很合理，实际上运行效率很低。

数据库无法为某个过滤条件优化执行计划，因为任何一个过滤条件都可能在运行时被取消。数据库需要为最坏的情况做准备：如果所有的过滤条件都被禁用。这样的后果就是，即使每个字段都有索引，数据库依然会选择使用全表扫描。

如果我们不使用动态参数，而是在 SQL 语句中写入实际值，优化器就会为激活的过滤条件选择适当的索引。

可以使用动态 SQL 的方式来动态拼接 where 子句。

参数嗅探

当 SQL Server 第一次执行查询语句或存储过程（或者查询语句与存储过程被强制重新编译）的时候，SQL Server 会有一个进程来评估传入的参数，并根据传入的参数生成对应的执行计划缓存，然后参数的值会伴随查询语句或存储过程执行计划一并保存在执行计划缓存里。这个评估的过程就叫做参数嗅探。

SQL Server 对查询语句编译和缓存机制是 SQL 语句执行过程中非常重要的环节，也是 SQLOS 内存管理非常重要的一环。理由是 SQL Server 对查询语句编译过程是非常消耗系统性能，代价昂贵的。因为它需要从成百上千条执行路径中选择一条最优的执行计划方案。所以，查询语句可以重用执行计划的缓存，避免重复编译，以此来节约系统开销。这种编译查询语句，选择最优执行方案，缓存执行计划的机制就是参数嗅探问题产生的理论基础。

数学运算

SELECT numeric_number
   FROM table_name
  WHERE numeric_number + 0 = ?

数学运算都不走索引（因为数据库不自带方程计算器）。

可以建相应的基于函数的索引。