1. 参数传递
禁止用字符串拼接 SQL 中的参数值，必须通过带参数的 PreparedStatement 执行 SQL。 例如读取产品信息的 SQL:
只是传入的 id 参数值不同，但 SQL 文本一模一样。这样做有 2 个好处:
(1) 对于相同的 SQL 文本，查询分析器可以直接重用缓存中的 SQL 文本分析结果与执行计划，提高了 SQL 执行效率;
(2) 避免黑客在参数值中注入危险代码。假如黑客给 password 参数赋值"'
or 1=1 or '1'='"，那么下列 SQL 就可以跳过登录检查:
"select 1 from User where name='" + name + "' and password='" + password + "'";
2. SQL 函数
禁止在 where 或是 order by 的字段上使用 SQL 函数，这样无法利用任何索引。 例如按照创建时间过滤产品的 SQL:
select item_no from Product where date_add(create_time, 1 interval day)>now()
上述 SQL 在 create_time 字段上执行了函数，导致 MySQL 必须对所有记录的 create_time 值进行函数运算后才知道它是否满足>now()的条件，无法通过索引 结构快速查找。
如果函数不在条件字段上还是可以使用的，例如上述 SQL 可以改成:
select item_no from Product where create_time>date_sub(now(), 1 interval day)
因为不等式的右值在查询表数据之前就可以计算完成。
3. 反向条件
<>，NOT IN, NOT EXISTS, NOT LIKE
等反向条件无法使用索引，应尽可能将其转成正向条件。
4. 全模糊匹配
禁止在过滤条件中使用全模糊 Like 匹配。
(1) like '%abc%' 这样的全模糊匹配无法用到任何索引;
(2) like 'abc%' 这样的前缀匹配还是可以用到索引的;
5. 分页实现
当总记录数巨大、页数较多时，写出一个高性能的分页 SQL 不是一件容易的事情。
以下 SQL:
select id from Product order by create_time limit 200000, 100;
select id, name, price, description from Product where id in (?, ...) order by create_time;
的执行效率是非常低的，可以建一个索引(create_time, id)，然后用以下 2 个 SQL 取代上述一个 SQL，执行效率有几个数量级的提升:
注:如果第二个 SQL 不加 order by create_time，就要在应用代码中按照前一 个 SQL 返回的 id 顺序对结果集排序，切记!
第一个 SQL 满足“索引全覆盖”的条件，执行效率很高;第二个 SQL 的查询 时间稳定，不会随页码的增加而增加。
如果 id 是一个按产品创建时间自增的序列，那么有一种更加优化的方法:
select id, name, price, description from Product where id>? order by id limit 100;
每次记住上次查询到的最大一个 id 即可，执行引擎只需扫描 100 行记录，且 因为扫描的是聚簇索引，可以直接查询最终数据，不用先取 ID 再二次查询。
如果 Product 表的 id 字段无序，或者业务上不能按照 id 排序，我们只能采用 limit 200000, 100 这样的语法分页，但数据量特别大的时候这种语法也会遇到严 重的性能问题，此时我们需要更精巧的设计。例如分页查询指定类别下的产品列 表，按创建时间排序返回:
create index idx_cat_crt on Product(category_id, create_time, id);
select create_time, id from Product where category_id=? and (create_time>? or (create_time=? and id>?)) order by create_time, id limit 100;
select id, name, price, description from Product where id in (?, ...) order by create_time, id;
注意第一个 select 语句的 order by 字段列表，不是 (category_id, create_time,id)，而是(create_time,id)，否则有时会导致 filesort(未查清)。
- [ HXW ]
