本文探讨了MySQL中日期格式数据列未使用索引的问题，提供了解决方案。当查询`WHERE YEAR(Birthday) = 1980`时不使用索引，可以改为`WHERE Birthday BETWEEN 1980-01-01 AND 1980-12-31`或创建计算列并为其建立索引。EXPLAIN输出的表顺序显示了数据读取的顺序。
背景：date格式的数据列有时使用不到索引，
创建people表：
CREATE TABLE 'people' (
'id' int (10) unsigned NOT NULL AUTO_INCREMENT,
'FirstName' varchar (40) NOT NULL,
'Surname' Varchar (40) NOT NULL,
'Birthday' date NOT NULL,
PRIMARY KEY ('id'),
KEY 'Surname' ('Surname', 'FirstName'),
KEY 'FirstName' ('FirstName'),
KEY 'Birthday' ('Birthday')
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
====================标准分割线========================
干货：
SELECT * FROM people WHERE YEAR(Birthday) = 1980;
The query is not using an index.
解决方案：2选1.
1.Change the WHERE clause to Birthday BETWEEN 1980-01-01 AND 1980-12-31.
2.Add a generated column calculating YEAR(Birthday) and index that column.
======================毫无底线的分割线=============================
拓展：What is the order of tables shown in an EXPLAIN output?
It lists tables in the order in which their data will be read.
==========================End,====================================
扫一扫
1132
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
