简单索引优化 [英] Simple Indexing Optimisation

问题描述

我最近参加了一次简单的技能测试，得到了反馈:

I recently took a simple skills test to which I was given the feedback:

有一个小的索引优化可以改善 性能."

"There is one small indexing optimisation which could improve performance."

技能测试涉及创建生日电子贺卡在线应用程序；用户注册，然后在生日那天向他们发送一封电子邮件.我以为这是在运行mysql数据库的Linux服务器上，该数据库大约有400万条记录.

The skills test involved creating a birthday e-card online app; users sign up, then on their birthday an email is sent to them. I was to presume this is on a Linux server running a mysql database with around 4 million records.

我已经尽力研究数据库索引方面的其他问题，但是就我的最佳研究和知识而言，我一直在努力寻找任何改进之处.我真的很感谢这里的任何指示，以便我可以知道我哪里出了问题；

I've tried my best to research further issues with indexing on my database, but to my best research and knowledge, im struggling to find any improvements. I'd really appreciate any pointers here so I can learn where I went wrong;

数据库:

CREATE TABLE `birthdayCard`
(
   `Email` VARCHAR(255), 
   `FirstName` CHAR(30), 
   `LastName` CHAR(30), 
   `Dob` DATE, 
   PRIMARY KEY (Email), 
   INDEX(Dob)
 );

查询:

SELECT * FROM `birthdayCard` 
WHERE MONTH(Dob) = MONTH(NOW()) 
AND DAY(Dob) = DAY(NOW());

推荐答案

如上面的注释所述，未使用INDEX(Dob)，因为这是 year-month-day 上的索引>.您必须在月日上创建索引.

As explained in the comment above, the INDEX(Dob) is not used -- since this is an index on year-month-day. You have to create an index on month-day.

可能不是最优雅的解决方案，但是:

Probably not the most elegant solution, but:

CREATE TABLE `birthdayCard`(`Email` VARCHAR(255), `FirstName` CHAR(30), `LastName` CHAR(30),
                            `Mob` int, `Dob` int, 
                            PRIMARY KEY (Email), INDEX(`Mob`, `Dob`));

请参见 http://sqlfiddle.com/#!2/db82ff/1

一个更好的答案(?):由于MySQL不支持计算列，因此您可能需要触发器来填充月日"列，并为其添加索引:

For a better( ?) answer: as MySQL does not support computed columns, you might need triggers to populate a "month-day" columns, and have an index on it:

CREATE TABLE `birthdayCard`(`Email` VARCHAR(255), `FirstName` CHAR(30), `LastName` CHAR(30),
                            `Dob` DATE,
                            `Birthday` CHAR(5),
                            PRIMARY KEY (Email), INDEX(`Birthday`));

CREATE TRIGGER ins_bithdayCard BEFORE INSERT ON `birthdayCard`
FOR EACH ROW
    SET NEW.`birthday` = DATE_FORMAT(NEW.`Dob`, "%m%d");

CREATE TRIGGER upd_bithdayCard BEFORE UPDATE ON `birthdayCard`
FOR EACH ROW
    SET NEW.`birthday` = DATE_FORMAT(NEW.`Dob`, "%m%d");

这允许简单"插入，并根据需要保留完整的Dob，如您的原始示例:

This allow "simple" inserts, preserving if needed the full Dob as in your original example:

insert into birthdayCard (Email, FirstName, LastNAme, Dob) 
   values ("[e-mail]", "Sylvain", "Leroux", '2013-08-05');

必须修改SELECT查询以使用新的搜索"列:

The SELECT query has to be modified to use the new "search" column:

SELECT * FROM `birthdayCard` WHERE Birthday = DATE_FORMAT(NOW(), "%m%d");

Sett http://sqlfiddle.com/#!2/66111/3

这篇关于简单索引优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！