mysql sql调优案例（记一次生产数据库sql优化案例--Waiting）

概述

分享下最近因一条慢sql导致的数据库卡顿，因为是新项目，所以坑比较多。下面一起来看看吧！

服务器层面

项目经理反映说下午的时候系统很卡，观察了一下服务器，发现mysqld进程占了很大一部分资源，像这种情况80%以上都是sql引起的。

mysql sql调优案例（记一次生产数据库sql优化案例--Waiting）(1)

数据库层面

通过 show processlist 一看，发现很多是 Waiting for table metadata lock 状态的连接。

MySQL在进行一些alter table等DDL操作时，如果该表上有未提交的事务则会出现 Waiting for table metadata lock ，而一旦出现metadata lock，该表上的后续操作都会被阻塞。

mysql sql调优案例（记一次生产数据库sql优化案例--Waiting）(2)

慢查询

观察了一下当前数据库慢查询sql的情况，发现有一条sql执行需要到600秒

mysql sql调优案例（记一次生产数据库sql优化案例--Waiting）(3)

--200多行 SELECT ( CASE WHEN ( category.equipment_type = 'T' OR product.is_pallet = TRUE ) THEN 1 ELSE 0 END ) AS is_pallet, ( CASE WHEN ( ob.project_code = 'FS' ) THEN 0 ELSE 1 END ) AS is_create_pallet, ( pallet.capa city_length * pallet.capacity_width * pallet.capacity_height ) AS max_pallet_volume, ... pallet.weight AS pallet__weight, pallet.capacity_length AS pallet__capacity_length, pallet.capacity_width AS pallet__capacity_width, pallet.capacity_height AS pallet__capacity_height FROM fsl_order_base_line AS pack_boxes_order_base_lines LEFT JOIN fsl_order_base AS ob ON pack_boxes_order_base_lines.order_base = ob.id LEFT JOIN fsl_thing_type AS product ON pack_boxes_order_base_lines.product = product.id LEFT JOIN fsl_thing_type AS category ON product.category = category.id LEFT JOIN fsl_thing_type AS pallet ON product.pallet = pallet.id WHERE ( ( ( ( ob.auto_releasing = 'Y' ) AND ( ob.project_code = 'DD' ) ) AND ( ob.order_status = '0' ) ) AND ( ob.model1 = 'pack' ) ) AND pack_boxes_order_base_lines.domain_name IN ( 'FSL' ) ORDER BY pack_boxes_order_base_lines.insert_date DESC, pack_boxes_order_base_lines.id DESC;

改写sql

set profiling=1; --改写如下： select ..... pallet.capacity_length AS pallet__capacity_length, pallet.capacity_width AS pallet__capacity_width, pallet.capacity_height AS pallet__capacity_height FROM (select * from fsl_order_base_line where domain_name IN ( 'FSL' ) ) AS pack_boxes_order_base_lines LEFT JOIN (select * from fsl_order_base where auto_releasing = 'Y' and project_code = 'FS' and order_status = '0' and model1 = 'pack') AS ob ON pack_boxes_order_base_lines.order_base = ob.id LEFT JOIN fsl_thing_type AS product ON pack_boxes_order_base_lines.product = product.id LEFT JOIN fsl_thing_type AS category ON product.category = category.id LEFT JOIN fsl_thing_type AS pallet ON product.pallet = pallet.id ORDER BY pack_boxes_order_base_lines.insert_date DESC, pack_boxes_order_base_lines.id DESC;

其实原理就是缩小下结果集，调增后就剩100秒了。

mysql sql调优案例（记一次生产数据库sql优化案例--Waiting）(4)