关系型数据库通用的坑-自定义函数的优化

自定义函数，和标量子查询一样，在主查询返回结果集（行数N）巨大时，自定义函数里面的查询会被访问N多次……

自定义函数唯一的适用场景就是 分页查询，其他场景基本上都具有潜在风险，需要改写。

SELECT rrs.o_id,
       (SELECT min(IFNULL(get_product_minOrderPkgQty(rrs.o_id, rct.typeId) *
                          get_product_sale_quantity(rrs.o_id, rct.typeId),
                          999999))
          FROM ruigu_customer_type AS rct
         WHERE rct.typeId > 40
           AND rct.current_using = 1) AS min_num
  FROM ruigu_replace_sku AS rrs

上面是一条mysql 语句，执行时间是20s  SQL中涉及两张表和两个自定义函数

根据以往ORACLE的优化经验，自定义函数 可能是SQL语句的瓶颈，函数的定义如下：

--1
CREATE DEFINER=`dev_team3`@`%` FUNCTION `get_product_minOrderPkgQty`(product_id int, customer_type int) RETURNS int(11)
begin
  declare minOrderPkgQty_result int(11);
    select minOrderPkgQty into minOrderPkgQty_result 
    from `ruigu_product_sale_configuration`
    where productId=product_id and customerTypeId=customer_type limit 1;
  return minOrderPkgQty_result;
--2
CREATE DEFINER=`dev_team3`@`%` FUNCTION `get_product_sale_quantity`(product_id int, customer_type int) RETURNS int(11)
begin
  declare quantity_result int(11);
    select saleQuantity into quantity_result 
    from `ruigu_product_sale_configuration`
    where productId=product_id and customerTypeId=customer_type limit 1;
  return quantity_result;

自定义函数是否会产生性能问题，取决于主查询返回的行数，即

SELECT count(*)
    FROM ruigu_customer_type AS rct
   WHERE rct.typeId > 40
     AND rct.current_using = 1

因为对于mysql没有一个常识衡量值，ORACLE是10000行（IO性能好的磁盘20w行 性能的衰减幅度也能接受）

所以只能使用别的方法去"试"：在遇到 自定义函数、标量子查询这种SQL，判断他们是否产生了性能问题，可以将

自定义函数、标量子查询注释掉去跑原SQL，如果效率提升，则改写之~

原SQL去掉自定义函数之后，性能提升明显，故作如下改写：
select w.o_id,
       min(IFNULL(w.quantity_result * w.minOrderPkgQty_result, 999999))
  from (select c.saleQuantity   as quantity_result,
               c.minOrderPkgQty as minOrderPkgQty_result,
               a.o_id
          from ruigu_replace_sku a
         inner join (select typeId
                      from ruigu_customer_type rct
                     WHERE rct.typeId > 40
                       AND rct.current_using = 1) b
          left join ruigu_product_sale_configuration c
            on a.o_id = c.productId
           and b.typeId = c.customerTypeId) w
 group by w.o_id;

改写之后,SQL运行300ms