作为一名工程狮, 不只要会写SQL, 还得会写性能优良的SQL. 以下是总结在开发过程中常用的方法.
- 规则1 遵循单SQL最少功能原则
- 不建议使用复杂的SQL查询数据, 如超过3个表的关联查询;
- 建议使用简单的SQL查询数, 在业务代码中进行处理以达到最终想要的数据.
如查询某订单详情的例子:
#方法一:分多次简单查询, 然后在代码聚合数据(推荐)
select ... from om_order where order_id = 1;
select ... from om_order_item where order_id = 1;
select ... from om_order_item_ext where order_id = 1;
#方法二:通过关联查询,一次查出数据(亦可)
select ... from om_order t1,om_order_item t2,om_order_item_ext t2
where t1.order_id = t2.order_id and t1.order_id = t3.order_id and t1.order_id = 1;
- 规则2 建议按需查询字段
- 不建议查询全部字段(SELECT
*
) - 尤其在查询一些字段很多的表或者多表关联查询的时候, 更应该避免查询全部字段
- 不建议查询全部字段(SELECT
如下例子:
#就算查询全部字段,也不建议使用select *
select * from om_order where order_id = 1; //不合适
select field1, field2... where order_id = 2; //推荐
-
规则3 避免嵌套子查询
-
规则4 注意MySQL隐式转换规则, 否则SQL语句不会匹配到索引
-
规则5 降低数据库交互次数
- 在
规则1
推荐用多次简单查询获得数据, 权衡有如下好处:- 表分散在不同物理库不受影响
- 业务实现逻辑条理清晰
- 虽然数据库访问次数变多, 在访问次数控制3次左右, 能达到一个好的平衡. 但是如果单个业务逻辑数据库访问次数过多, 比如超过10次, 那么性能会急剧下降. 这个时候应当寻找更好的方式, 避免多次交互.
以下为常见例子:
- 用批量插入代替单条插入,比如创建订单的时候需要写入20条订单项目,一定要避免循坏20次来进行插入操作
- 比如业务需要查询10个订单极其订单项目详情。那么可以用一次查询出10个订单主体信息,再用一次查询查询出这10个订单的订单项,最后在业务代码中将订单和对应的订单项目组合起来。一定不要在查询出订单主体信息以后,再循坏10次调用数据库连接查询对应的订单项信息
- 在
-
规则6 在影响行数确定的时候使用limit
- 在查找数据、更新数据、删除数据时,如影响行数确定需带上
limit + 行数
- 其一加快速度,其二在出错的情况下降低错误影响范围;
- 有大量数据修改是,可使用limit来限制单次操作行数,减少操作响应时间
###以下为常见例子: select ... from um_user_info where uid = 1 limit 1; //推荐 update um_user_info set age = 18 where uid = 1 limit 1; //推荐
- 在查找数据、更新数据、删除数据时,如影响行数确定需带上
-
规则7 查询时
禁止
对索引字段使用函数或计算- SQL的查询条件,直接对某字段使用了函数或者进行运算,索引无效
### 以下为示例 select ... from om_order where order_id - 1 = 99;//不会使用索引 select ... from om_order where order_id = 99 + 1;//会使用索引,但是不推荐 select ... from om_order where order_id = 100;//会使用索引, 推荐。(变量运算尽量在业务代码中完成)
-
规则8 谨慎使用
NOT IN
和NOT LIKE
NOT IN
和NOT LIKE
会造成全表扫描,避免使用,可用JOIN代替
-
规则9
IN 语句
一次查询集合不宜太大- 在in语句中, 附带的集合不宜超过500个, 如果查询的集合太大就算有索引, 效率也会受损, 此种情况可以考虑业务上优化.
-
规则10 字符串模糊查询注意事项
- 单向模糊匹配, 被查询的字段需要建立索引
- 建立索引后, 需注意查询条件避免造成大范围检索, 比如搜索订单商品, 虽然barcode字段有索引, 但是如果查询条件是 LIKE “DI%”, 那么索引其实不会起到具体作用.
禁止
使用双向模糊匹配, 此类优化可以从需求层面解决或者通过全文搜索应用来解决. 如果一定要使用双向模糊匹配, 也一定要将搜索范围控制到非常窄的范围内进行模糊查询.