mysql时间范围查询效率记录

测试表中有1100w条数据，时间字段collection_date为datetime类型加有btree索引。
测试查询某一日的数据，记录一下哪种方法更快。

1. BETWEEN AND

耗时0.15s

SELECT
	id
FROM
	labour_trace_info
WHERE
	collection_date BETWEEN '2023-12-15 00:00:00' AND '2023-12-15 23:59:59'

2. < >

耗时0.15s

SELECT
	id
FROM
	labour_trace_info
WHERE
	collection_date >= '2023-12-15 00:00:00' AND collection_date <= '2023-12-15 23:59:59'

3. TO_DAYS()

耗时5.8s

SELECT
	id
FROM
	labour_trace_info
WHERE
	TO_DAYS(NOW()) - TO_DAYS(collection_date) = 5

4. DAYOFYEAR() + YEAR()

耗时5.8s

SELECT
	id
FROM
	labour_trace_info
WHERE
	DAYOFYEAR(collection_date) = DAYOFYEAR('2023-12-15') AND YEAR(collection_date) = '2023'

5. LIKE

耗时5.9s

SELECT
	id
FROM
	labour_trace_info
WHERE
	collection_date LIKE '2023-12-15%'

6. DATE()

耗时5.9s

SELECT
	id
FROM
	labour_trace_info
WHERE
	DATE(collection_date) = '2023-12-15'

7. YEAR() + MONTH() + DAY()

耗时5.9s

SELECT
	id
FROM
	labour_trace_info
WHERE
	YEAR(collection_date) = '2023' AND MONTH(collection_date) = '12' AND DAY(collection_date) = '15'

8. DATE_FORMAT()

耗时7.3s

SELECT
	id
FROM
	labour_trace_info
WHERE
	DATE_FORMAT(collection_date,'%Y-%m-%d') = '2023-12-15'

9. TIMESTAMP()

耗时10.1s

SELECT
	id
FROM
	labour_trace_info
WHERE
	TIMESTAMP(DATE_FORMAT(collection_date,'%Y-%m-%d')) = TIMESTAMP('2023-12-15')