优化我的 mysql 查询以使用索引进行排序

Question

我需要您的帮助来优化下面的查询。假设我们有一个用于文章的 Web 应用程序。该软件使用两张表，一张是文章表，一张是用户表。文章表保存文章创建日期、id、正文、标题和文章内容。该部分。假设我们有一个名为“新闻”的版块，并且有 100 万篇文章属于新闻版块。那么在这种情况下，如何优化以下查询：

SELECT username,title FROM article,users 
WHERE article.auther_id=users.id AND section LIKE 'news' 
ORDER BY article.date DESC 
LIMIT 0,40

表结构是：

CREATE TABLE `article` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`title` VARCHAR( 100 ) NOT NULL ,
`body` VARCHAR( 200 ) NOT NULL ,
`date` VARCHAR( 30 ) NOT NULL ,
`auther_id` INT NOT NULL ,
`section` VARCHAR( 30 ) NOT NULL
) ENGINE = MYISAM ;


CREATE TABLE `users` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`username` VARCHAR( 30 ) NOT NULL
) ENGINE = MYISAM ;

我试图创建一个由section & 组成的索引。但它不是最好的，因为如果我们有 200 万条记录，其中 100 万条属于一个部分，那么数据库将扫描 100 万行。

Answer 1

您需要在（部分，日期）上创建索引。

不要将 auther_id 包含为前导列：文章将在联接中处于前导位置，并且不会对此列执行任何搜索。

由于查询中有 LIMIT 0, 40，MySQL 将不必扫描整个索引。它只会选择前 40 记录。

下面是一个要检查的测试脚本：

CREATE TABLE `article` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`title` VARCHAR( 100 ) NOT NULL ,
`body` VARCHAR( 200 ) NOT NULL ,
`date` VARCHAR( 30 ) NOT NULL ,
`auther_id` INT NOT NULL ,
`section` VARCHAR( 30 ) NOT NULL
) ENGINE = MYISAM ;


CREATE TABLE `users` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`username` VARCHAR( 30 ) NOT NULL
) ENGINE = MYISAM ;

INSERT
INTO    article
SELECT  id,
        CONCAT('Title ', id),
        CONCAT('Body ', id),
        DATE_FORMAT('2009-12-18' - INTERVAL id MINUTE, '%Y-%m-%d %H:%i:%S'),
        (id - 1) % 500 + 1,
        'news'
FROM    t_source;

INSERT
INTO    users
SELECT  id, CONCAT('Username ', id)
FROM    t_source
LIMIT 500;

CREATE INDEX ix_article_section_date ON article (section, date);

SELECT  username,title
FROM    article
JOIN    users
ON      users.id = article.auther_id
WHERE   section = 'news'
ORDER BY
        article.date DESC
LIMIT 0, 40;

t_source 是一个虚拟表，其中包含 1,000,000 行。

最终查询在我的机器上在 0.0018 s 内完成（立即）

以下是执行计划：

1, 'SIMPLE', 'article', 'range', 'ix_article_section_date', 'ix_article_section_date', '92', '', 999998, 'Using where'
1, 'SIMPLE', 'users', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'test.article.auther_id', 1, ''

Answer 2

在article.auther_id上添加索引。除了您已经添加的日期和部分的索引之外。

Answer 3

从查询来看，您似乎需要一个关于部分的索引和一个关于日期的索引（两个单独的索引）。尝试添加这些，然后查看解释计划，看看它们是否有效，或者是否有其他一些调整可能有帮助。

Answer 4

好的，首先使用 DATE 数据类型来表示日期，因为这比使用 VARCHAR 快得多。

其次，您只需将以下键添加到文章表的创建表语法中。

KEY auther_id (auther_id),
KEY section (section),

有点无关紧要，但如果您使用 InnoDB，您还可以将 auther_id 作为外键。

另外，如果您不需要使用“LIKE”，则只需检查 section="news" 会快很多。 （如果列表有限，您甚至可以对这些部分使用 ENUM。）

Answer 5

正如其他人所说，索引为日期、部分和 auther_id。据此：

http://www .mysqlperformanceblog.com/2008/08/22/multiple-column-index-vs-multiple-indexes/

拥有一个多列索引而不是三个单独的索引可能会更有利，如下所示

...
key idx_combo (auther_id, section, date)
...

：表定义。

Answer 6

最好的办法是根据日期/部分创建一个分区表。

然后创建本地分区索引，这样性能会高很多。