【数据库】DATE_FORMAT导致索引失效

故事背景

最近更新了一版小程序,发现首页等待时长要好几秒,开始以为我家网速太慢。谁曾想,是sql查询时间太长!!!

sql排查

EXPLAIN
SELECT
	bdr.bookId,
	bdr.createTime
FROM
	book_download_record AS bdr
WHERE 
 	DATE_FORMAT( bdr.createTime, '%Y%m' ) = DATE_FORMAT( CURDATE( ), '%Y%m' )
LIMIT 0, 20

image.png

发现createTime并没有使用到索引,然后将DATE_FORMAT函数去掉,索引就被使用了。

优化sql

优化前sql

-- EXPLAIN
SELECT
	b.id,
	b.`name`,
	(
	SELECT
		bt.`name` 
	FROM
		book_type AS bt 
	WHERE
		bt.id = b.typeId 
	) AS typeName,
	b.content,
	b.typeId,
	b.size,
	(
	SELECT
		a.`name` 
	FROM
		author AS a 
	WHERE
		a.id = b.authorId 
	) AS author,
	b.isfree,
	b.fileType,
	(
	SELECT
		bac.downloadCount 
	FROM
		book_about_count AS bac 
	WHERE
		bac.bookId = b.id 
	) AS downloadNum,
	(
	SELECT
		bac.clickCount 
	FROM
		book_about_count AS bac 
	WHERE
		bac.bookId = b.id 
	) AS clickNum,
	b.create_time createTime,
	b.update_time updateTime,
	b.`order`,
	(
	SELECT
		COUNT( * ) 
	FROM
		book_download_record AS bdr 
	WHERE
		bdr.bookId = b.id 
		AND YEARWEEK(
			date_format( bdr.createTime, '%Y-%m-%d' ),
			7 
		) = YEARWEEK( now( ), 7 ) 
	) 
FROM
	book b 
WHERE
	b.id != 1 
ORDER BY
	(
	SELECT
		COUNT( * ) 
	FROM
		book_download_record AS bdr 
	WHERE
		bdr.bookId = b.id 
		AND YEARWEEK(
			date_format( bdr.createTime, '%Y-%m-%d' ),
			7 
		) = YEARWEEK( now( ), 7 ) 
	) DESC,
	(
	SELECT
		bac.downloadCount 
	FROM
		book_about_count AS bac 
	WHERE
		bac.bookId = b.id 
	) DESC,
	(
	SELECT
		bac.clickCount 
	FROM
		book_about_count AS bac 
	WHERE
		bac.bookId = b.id 
	) DESC 
	LIMIT 0,
	15

优化后

-- EXPLAIN
SELECT
	bdr.bookId,
	b.`name`,
	(
	SELECT
		bt.`name` 
	FROM
		book_type AS bt 
	WHERE
		bt.id = b.typeId 
	) AS typeName,
	b.content,
	b.fileType,
	COUNT( bdr.id ) AS downloadCount,
	b.isfree,
	(
	SELECT
		a.`name` 
	FROM
		author AS a 
	WHERE
		a.id = b.authorId 
	) AS author 
FROM
	book_download_record AS bdr
	LEFT JOIN book AS b ON bdr.bookId = b.id 
WHERE
	bdr.bookId != 1 
	AND bdr.createTime >= '2020-03-02 00:00:00' 
GROUP BY
	bdr.bookId 
ORDER BY
	downloadCount DESC 
	LIMIT 0,
	15

优化后, 禁止使用sql函数, 增加一个参数来传递时间范围,这样createTime索引就生效了.
image.png

Copyright: 采用 知识共享署名4.0 国际许可协议进行许可

Links: https://demongao.com/2020/03/数据库索引的重要性

Buy me a cup of coffee ☕.