记录一次sql语句优化经历

柔情只为你懂 2023-08-17 17:11 137阅读 0赞

原SQL：

SELECT
    	mp.id AS mp_id,
    	mp.page_id AS mp_page_id,
    	mp.is_published AS mp_is_published,
    	mp.thumbnail AS mp_thumbnail,
    	mp.published_up AS mp_published_up,
    	mp.published_down AS mp_published_down,
    	mp.type AS mp_type,
    	mp.create_org_id AS mp_create_org_id,
    	mp.share_desc AS mp_share_desc,
    	mp.date_added AS mp_date_added,
    	mp.created_by AS mp_created_by,
    	mp.created_by_user AS mp_created_by_user,
    	mp.date_modified AS mp_date_modified,
    	mp.modified_by AS mp_modified_by,
    	mp.modified_by_user AS mp_modified_by_user,
    	mp.title AS mp_title,
    	mps.meta_description AS pageDescription,
    	mc.id AS pageCategoryId,
    	mc.title AS pageCategoryName,
    	mo.NAME AS orgName 
    FROM
    	m_promotion mp
    	LEFT JOIN m_pages mps ON mps.id = mp.page_id
    	LEFT JOIN m_staff_task_xref mstx ON ( mstx.promotion_id = mp.id AND mstx.staff_id = 13 )
    	LEFT JOIN m_categories mc ON mc.id = mps.category_id
    	LEFT JOIN m_organization_task mot ON mp.id = mot.promotion_id
    	LEFT JOIN m_organization mo ON mo.id = mot.org_id 
    WHERE
    	mp.is_published = 1 
    	AND mp.type = 1 
    	AND mot.org_id IN ( 3, 2, 1 ) 
    	AND mstx.date_added IS NULL
    	AND date( '2019-09-30 07:49:57.88' ) > date( mp.published_up ) 
    	AND mp.date_added > '2019-09-19 17:54:22.0' 
    ORDER BY
    	mp.published_down ASC 
    	LIMIT 0,
    	30;

以上SQL执行需要5秒，因为使用了 IS NULL语句。

更换IS NULL 为 IFNULL(mstx.date\_added,'0') = 0 执行只需几毫秒，性能大大提升。

SELECT
    	mp.id AS mp_id,
    	mp.page_id AS mp_page_id,
    	mp.is_published AS mp_is_published,
    	mp.thumbnail AS mp_thumbnail,
    	mp.published_up AS mp_published_up,
    	mp.published_down AS mp_published_down,
    	mp.type AS mp_type,
    	mp.create_org_id AS mp_create_org_id,
    	mp.share_desc AS mp_share_desc,
    	mp.date_added AS mp_date_added,
    	mp.created_by AS mp_created_by,
    	mp.created_by_user AS mp_created_by_user,
    	mp.date_modified AS mp_date_modified,
    	mp.modified_by AS mp_modified_by,
    	mp.modified_by_user AS mp_modified_by_user,
    	mp.title AS mp_title,
    	mps.meta_description AS pageDescription,
    	mc.id AS pageCategoryId,
    	mc.title AS pageCategoryName,
    	mo.NAME AS orgName 
    FROM
    	m_promotion mp
    	LEFT JOIN m_pages mps ON mps.id = mp.page_id
    	LEFT JOIN m_staff_task_xref mstx ON ( mstx.promotion_id = mp.id AND mstx.staff_id = 13 )
    	LEFT JOIN m_categories mc ON mc.id = mps.category_id
    	LEFT JOIN m_organization_task mot ON mp.id = mot.promotion_id
    	LEFT JOIN m_organization mo ON mo.id = mot.org_id 
    WHERE
    	mp.is_published = 1 
    	AND mp.type = 1 
    	AND mot.org_id IN ( 3, 2, 1 ) 
    	AND IFNULL(mstx.date_added,'0') = 0
    	AND date( '2019-09-30 07:49:57.88' ) > date( mp.published_up ) 
    	AND mp.date_added > '2019-09-19 17:54:22.0' 
    ORDER BY
    	mp.published_down ASC 
    	LIMIT 0,
    	30;