【MySQL】一次查询优化，体现类型转换的重要性

Kida 的技术小屋

已于 2024-02-21 09:16:26 修改

阅读量203

点赞数

分类专栏： # MySQL 文章标签： mysql

于 2018-12-14 11:51:30 首次发布

本文为博主原创文章，未经博主允许不得转载。

本文链接：https://blog.csdn.net/kida_yuan/article/details/85000699

版权

MySQL 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

需要优化的sql语句如下：

SELECT
  a.SYS_ID,
  a.CUST_TYPE,
  a.CUST_ID,
  a.CUST_NAME,
  a.area_info,
  a.CUST_RAGE,
  a.INFO_AUTH,
  a.CUST_STATE,
  a.LIST_TOP,
  a.COMMEND,
  DATE_FORMAT(a.auth_date, '%Y-%m-%d') AS auth_date,
  DATE_FORMAT(a.add_date, '%Y-%m-%d') AS add_date,
  a.email,
  DATE_FORMAT(a.oper_time, '%Y-%m-%d') AS oper_time,
  a.COMPANY_ADDRESS,
  s.CELL_PHONE AS CONTACT_PHONE,
  a.ACCESS_STATUS,
  s.CONTACT_NAME AS CONTACT_NAME,
  b.MESSAGE_SWITCH,
  b.USER_NAME,
  b.user_Id AS user_Id,
  b.QQ_NUM,
  b.CELLPHONE,
  b.USER_STATE,
  b.ROLE_AUTH_ID,
  
  (SELECT
      r.role_name
    FROM role r
    WHERE r.ROLE_AUTH_ID = b.ROLE_AUTH_ID) AS role_name,
	
  d.CONTACT_NAME AS customerServiceUserName,
  f.CONTACT_NAME AS devCustomerServiceUser,
  a.MAJOR_TRADE_ID,
  a.AVAILABILITY_TRADE_ID,
  b.PERSONAL_ID,
  a.BUIS_REMARK,
  CASE WHEN d.REGIONAL_MANAGER = 0 THEN d.MANAGE_AREA ELSE d.SALESMAN_MANAGER_AREA END AS SALESMAN_MANAGER_NAME
FROM spot_company a

  LEFT JOIN (SELECT
      rf.field_id_val,
      rf.dept_id,
      rf.ROLE_ID,
      rf.user_Id
    FROM rows_filter rf
    WHERE rf.table_name = UPPER('spot_company')
    AND rf.field_name = UPPER('CUST_ID')) rf
    ON a.CUST_ID = rf.field_id_val

  LEFT JOIN spot_user_info b
    ON a.CUST_ID = b.CUST_ID
	
  LEFT JOIN (
	SELECT *
    FROM spot_frequent_contacts
    WHERE (IS_FREQUENT, CUST_ID) IN (SELECT
        MAX(IS_FREQUENT),
        CUST_ID
      FROM spot_frequent_contacts
      GROUP BY CUST_ID)
    GROUP BY CUST_ID) s
    ON s.CUST_ID = a.CUST_ID
	
  LEFT JOIN spot_user_info d
    ON a.CUSTOMER_SERVICE_USER = d.user_Id
	
  LEFT JOIN spot_user_info f
    ON a.DEV_CUSTOMER_SERVICE_USER = f.user_Id

WHERE a.CUST_ID != 100000000000000
AND b.USER_TYPE = '1'
AND a.CUST_STATE != 0
AND b.USER_ACCOUNT_TYPE = 1
AND b.USER_STATE = 1
AND a.CUST_STATE != 9
AND a.CUST_STATE != 3

ORDER BY a.oper_time DESC, a.CUST_ID DESC;

执行explain分析后得出

   id  select_type         table                   type    possible_keys                                      key               key_len  ref                                  rows  Extra                                               
------  ------------------  ----------------------  ------  -------------------------------------------------  ----------------  -------  ---------------------------------  ------  ----------------------------------------------------
     1  PRIMARY             b                       ALL     CUST_ID,IDX_USER_TYPE,user_state                   (NULL)            (NULL)   (NULL)                               1963  Using where; Using temporary; Using filesort        
     1  PRIMARY             a                       eq_ref  PRIMARY,company_custid,IDX_CUST_STATE_CUST_SUPPLY  PRIMARY           8        paohe.b.CUST_ID                         1  Using where                                         
     1  PRIMARY             <derived4>              ALL     (NULL)                                             (NULL)            (NULL)   (NULL)                                260  Using where; Using join buffer (Block Nested Loop)  
     1  PRIMARY             d                       eq_ref  PRIMARY,user_id                                    PRIMARY           8        paohe.a.CUSTOMER_SERVICE_USER           1  Using where                                         
     1  PRIMARY             f                       eq_ref  PRIMARY,user_id                                    PRIMARY           8        paohe.a.DEV_CUSTOMER_SERVICE_USER       1  Using where                                         
     1  PRIMARY             <derived3>              ALL     (NULL)                                             (NULL)            (NULL)   (NULL)                               1187  Using where; Using join buffer (Block Nested Loop)  
     4  DERIVED             spot_frequent_contacts  index   IDX_FC_CUST_ID                                     IDX_FC_CUST_ID    62       (NULL)                                260  Using where                                         
     5  SUBQUERY            spot_frequent_contacts  index   IDX_FC_CUST_ID                                     IDX_FC_CUST_ID    62       (NULL)                                260  (NULL)                                              
     3  DERIVED             rf                      ref     PRIMARY                                            PRIMARY           304      const,const                          1187  Using where                                         
     2  DEPENDENT SUBQUERY  r                       ref     IDX_ROLE_AUTH_ID                                   IDX_ROLE_AUTH_ID  48       paohe.b.role_auth_id                    1  Using index condition

看到执行结果里面除了外层的sql 类型（type）为ALL之外，其他内容都执行了主键或索引，在索引优化方面是没有什么可以再优化了。

之后想看一下profile信息看看sql的执行时间，先执行 SET profiling=1 让mysql做一下记录，用完之后记得将其重新设置为0。

再执行一次上面的sql语句看看记录时间。

Query_ID    Duration  Query                                                                                                                                                                                                        
--------  ----------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      11  0.00007050  SET profiling_history_size = 15                                                                                                                                               
      12  0.00118750  SHOW STATUS                                                                                                                                                                  
      13  1.77348200  SELECT                                                                                                                                                                        
						a.SYS_ID,
						a.CUST_TYPE,  
                        a.CUST_ID,
                        a.CUST_NAME,
                        a.area_info, 
                        a.CUST_RAGE,
                        a.INFO_AUTH,
                        a.CUST_STATE, 
                        a.LIST_TOP,
                        a.COMMEND,
                        DATE_FORMAT(a.auth_date, '%Y-%m-%d') AS auth_date,
                        DATE_FORMAT(a.add_date, '%Y-%m-%d') AS add_date, 
                        a.email,   
                        DATE_FORMAT(a.oper_time, '%Y-%m-%  	
      14  0.00112575  SHOW STATUS   
      15  0.00070225  select state, round(sum(duration),5) as `duration (summed) in sec` from information_schema.profiling where query_id = 13 group by state order by `duration (summed) in sec` desc

这里主要是看13，整个sql执行用了1.7734秒，只是执行一条sql语句居然就用了差不多2秒，因此会觉得网页很慢。

但是看了索引已经没有优化的情况下还是执行这么慢应该如何入手？

那就有可能是join的时候出现问题了，最有可能的就是连接的时候字段类型不一致导致，连接的时候先做类型转换，之后再做连接遍历。

排查了一下，如果将

LEFT JOIN (SELECT
      rf.field_id_val,
      rf.dept_id,
      rf.ROLE_ID,
      rf.user_Id
    FROM rows_filter rf
    WHERE rf.table_name = UPPER('spot_company')
    AND rf.field_name = UPPER('CUST_ID')) rf
    ON a.CUST_ID = rf.field_id_val

这一段sql语句屏蔽了之后性能就上来了，这段是行过滤权限查询的sql。

从语句可以看到最后是用CUST_ID和field_id_val来进行关联的，而CUST_ID是bigint类型，而field_id_val是varchar类型。由于在这个连接里面field_id_val字段是属于外连接部分，因此可以判断优化的步骤是将field_id_val字段转换成int类型后再进行关联。

因此将上面的sql改成：

 LEFT JOIN (SELECT
      CAST(rf.field_id_val AS UNSIGNED integer) AS field_id_val,
      rf.dept_id,
      rf.ROLE_ID,
      rf.user_Id
    FROM rows_filter rf
    WHERE rf.table_name = UPPER('spot_company')
    AND rf.field_name = UPPER('CUST_ID')) rf
    ON a.CUST_ID = rf.field_id_val

使用CAST方法在查询出来的时候先将field_id_val字段转成Integer类型再做关联，之后我们再看看执行时间。

Query_ID    Duration  Query                                                                                                                                                                           
--------  ----------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      17  0.00007125  SET profiling_history_size = 15                                                                                                                                                 
	  18  0.00117550  SHOW STATUS                                                                                                                                                                     
	  19  0.31157250  SELECT
                        a.SYS_ID,
                        a.CUST_TYPE,
                        a.CUST_ID, 
                        a.CUST_NAME,
                        a.area_info,  
                        a.CUST_RAGE, 	
                        a.INFO_AUTH, 
                        a.CUST_STATE, 
                        a.LIST_TOP, 
                        a.COMMEND, 
                        DATE_FORMAT(a.auth_date, '%Y-%m-%d') AS auth_date,  
                        DATE_FORMAT(a.add_date, '%Y-%m-%d') AS add_date, 	
                        a.email,         	
                        DATE_FORMAT(a.oper_time, '%Y-%m-%
      20  0.00116000  SHOW STATUS  
      21  0.00071575  select state, round(sum(duration),5) as `duration (summed) in sec` from information_schema.profiling where query_id = 19 group by state order by `duration (summed) in sec` desc

看得出在修改之后19，执行时间缩短到了0.3115秒，性能提高了80%。