需要优化的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%。
