# 故事背景
最近更新了一版小程序，发现首页等待时长要好几秒，开始以为我家网速太慢。谁曾想，是sql查询时间太长！！！
# 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
```
发现createTime并没有使用到索引，然后将DATE_FORMAT函数去掉，索引就被使用了。
# 优化sql
## 优化前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
```
## 优化后
```sql
-- 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索引就生效了.
【数据库】DATE_FORMAT导致索引失效
Copyright: 采用 知识共享署名4.0 国际许可协议进行许可
