记录一次sql语句优化经历

原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;