Consider the table people with the definition:
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
The application uses a query such as:
SELECT * FROM people WHERE YEAR(Birthday) = 1980;
The query is not using an index.
Which two methods can be used to allow the query to use an index?
A. Change the WHERE clause to Birthday BETWEEN 1980-01-01 AND 1980-12-31.
B. Add a functional index for YEAR(Birthday).
C. Execute ANALYZE TABLE to update the index statistics.
D. Add a generated column calculating YEAR(Birthday) and index that column.
E. Add FORCE INDEX (Birthday) to the query.
Answer: AD

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;
该语句没有使用索引，下面哪两种方法可以使上述SQL使用到索引？
A．将WHERE条件修改为Birthday BETWEEN 1980-01-01 AND 1980-12-31
B．增加函数索引YEAR(Birthday)
C．执行ANALYZE TABLE
D．增加YEAR(Birthday)索引
E．增加hint：FORCE INDEX (Birthday)http://www.cndba.cn/hbhe0316/article/22781http://www.cndba.cn/hbhe0316/article/22781http://www.cndba.cn/hbhe0316/article/22781

版权声明：本文为博主原创文章，未经博主允许不得转载。

MYSQL