mysql5.7 执行计划_MySQL 5.7 优化不能只看执行计划

就这么一条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              ref               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里面的执行计划比较弱，有时候不能完全依赖，还是得查每张表实际的大小。