Custom functions, like scalar subqueries, when the result set (row number N) returned by the main query is huge, the query in the custom function will be accessed N times

The only applicable scenario of user-defined function is paging query. Other scenarios basically have potential risks and need to be rewritten.

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

The above is a mysql statement. The execution time is 20s. SQL involves two tables and two custom functions

According to the optimization experience of ORACLE in the past, user-defined functions may be the bottleneck of SQL statements. The definition of functions is as follows:

--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;

Whether or not a custom function causes performance problems depends on the number of rows returned by the main query, that is

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

Because there is no common sense measurement value for mysql, ORACLE is 10000 rows (the attenuation range of 20w rows of disks with good IO performance can also be accepted)

Therefore, you can only use other methods to "try": when you encounter SQL such as custom functions and scalar subqueries, you can determine whether they have performance problems

If the user-defined function and scalar subquery comments are dropped, the original SQL will be run. If the efficiency is improved, it will be rewritten~

primary SQL After the user-defined function is removed, the performance is significantly improved, so it is rewritten as follows:
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;

//After rewriting, SQL runs for 300ms