MySQL 5.7 優化不能只看執行計劃

深圳gg發表於2018-06-08
MySql優化
 就這麼一條SQL要40s，看執行計劃，發現rows很小，神奇了。
SELECT T1.* FROM
(SELECT 
  T.*,
  R.LOCATION_DEPART_ID AS locationDepartId,
  CASE
    WHEN T.INVENTORY_TYPE = 1 
    THEN f_fm_getFullLocationName(R.LOCATION_DEPART_ID, '50000050')
    WHEN T.INVENTORY_TYPE = 2 
    THEN 
    (SELECT f_gg_org_getorgnamepath(OT.ORG_ID,'50000050',ot.org_name)
       FROM gg_ORGANIZATION OT 
      WHERE OT.ORG_ID = R.LOCATION_DEPART_ID) 
    ELSE '' 
  END AS locationOrOrgName,
  CASE
    WHEN T.INVENTORY_TYPE = 1 
    THEN f_fm_getFullClassifyName (R.CLASSIFY_ID) 
    ELSE '' 
  END AS classifyName, 
  (SELECT 
    COUNT(
      CASE
        WHEN ii.INVENTORY_RESULT = 1 THEN 1 
      END
    ) 
  FROM
    FM_INVENTORY I,
    FM_INVENTORY_ITEM II 
  WHERE I.ID = II.INVENTORY_ID 
    AND I.TASK_ID = T.ID) AS waitInventoryNum
FROM
  FM_INVENTORY_TASK T,
  FM_INVENTORY_TASK_RANGE R 
WHERE T.ID = R.TASK_ID) T1
WHERE 1 = 1
  AND T1.FLOW_STATE = 2;
  
id  select_type         table       type   key                   key_len  ref                   rows  Extra        
--  ------------------  ----------  ------ --------------------  -------  ------------------- ------ -----------
 1  PRIMARY             <derived2>  ref    <auto_key0>           2        const                   8   (NULL)       
 2  DERIVED             T           ALL    (NULL)                (NULL)   (NULL)                  80  (NULL)       
 2  DERIVED             R           ref    IDX_RANGE_TASK_ID     111      T.ID                    1   (NULL)       
 4  DEPENDENT SUBQUERY  I           ref    IDX_INV_TASK_ID       110      T.ID                    1   Using index  
 4  DEPENDENT SUBQUERY  II          ref    IDX_INV_INVENTORY_ID  110      I.ID                    6   (NULL)       
 3  DEPENDENT SUBQUERY  OT          eq_ref PRIMARY               98       R.LOCATION_DEPART_ID    1   Using where  
 
 第一個感覺是資料庫快取的問題，檢查了一下，很多配置都是預設的，於是加了一下。
[mysqld]下面新增
innodb_read_io_threads = 16
innodb_write_io_threads = 16
max_heap_table_size = 159M
tmp_table_size = 159M
sort_buffer_size = 8M
join_buffer_size = 8M
read_buffer_size = 4M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
innodb_buffer_pool_instances = 8
innodb_file_per_table = 1
innodb_thread_concurrency = 0
innodb_log_buffer_size = 6M
innodb_log_file_size = 256M 
重啟資料庫之後，發現效能有所提升，還是30s。只能用最傳統的辦法，把子查詢一個個去掉，執行，發現第三個子查詢去掉之後秒出。
  (SELECT 
    COUNT(
      CASE
        WHEN ii.INVENTORY_RESULT = 1 THEN 1 
      END
    ) 
  FROM
    FM_INVENTORY I,
    FM_INVENTORY_ITEM II 
  WHERE I.ID = II.INVENTORY_ID 
    AND I.TASK_ID = T.ID) AS waitInventoryNum
 用show profiles診斷髮現時間耗在send data上面。
 
    檢查了一下FM_INVENTORY_ITEM是明細的資料，接近800萬的資料，且大部分資料集中在一個單上。找了下業務，發現是異常問題，把垃圾資料遷移出去，然後執行SQL，秒出。
    總結：MySQL裡面的執行計劃比較弱，有時候不能完全依賴，還是得查每張表實際的大小。

相關文章