Оптимизация моего запроса mysql для использования индекса для сортировки

Мне нужна ваша помощь, чтобы оптимизировать запрос ниже. Предположим, у нас есть веб-приложение для статей. Программное обеспечение использует две таблицы: одна — это таблица статей, а вторая — таблица пользователей. Таблица статей содержит дату создания статьи, идентификатор, тело, заголовок и раздел. Предположим, что у нас есть один раздел под названием «новости», и к разделу новостей относится миллион статей. Итак, в этом случае, как оптимизировать следующий запрос:

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 ;

Я попытался создать один индекс, состоящий из раздела и даты, но он не самый лучший, потому что если у нас есть 2 миллиона записей, и один миллион из них принадлежит одному разделу, БД будет сканировать один миллион строк.


mysql sql optimization query-optimization
person usef_ksa    schedule 28.12.2009    source источник
comment
Я предполагаю, что есть причина, по которой вы не используете тип данных DATE для дат?   -  person John Parker    schedule 29.12.2009
comment
Нет, нет причины. когда я проектировал БД, я не знал о типе данных DATE.   -  person usef_ksa    schedule 29.12.2009

Ответы (6)


arrow_upward
0
arrow_downward

Вам нужно создать индекс на (section, date).

Не включайте 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, ''
person Quassnoi    schedule 29.12.2009
comment
Большое спасибо. очень информативный ответ, особенно эта часть: поскольку в вашем запросе есть LIMIT 0, 40, MySQL не придется сканировать весь индекс. Он просто выберет первые 40 записей. Большое спасибо всем вам, участники. Я ценю ваши ответы. - person usef_ksa; 29.12.2009

arrow_upward
0
arrow_downward

Добавьте индекс для article.auther_id. в дополнение к указателю, который вы уже добавили по дате и разделу.

person Scott Saunders    schedule 28.12.2009

arrow_upward
0
arrow_downward

Судя по запросу, вам понадобится индекс по разделу и индекс по дате (два отдельных индекса). Попробуйте добавить их, а затем посмотрите на план объяснения, чтобы увидеть, работают ли они или есть какие-то другие настройки, которые могут помочь.

person jvilalta    schedule 28.12.2009
comment
Мне может быть любопытно посмотреть, есть ли разница в разделе LIKE 'news' и section = 'news'. Наверное нет, но я бы проверил. Обычно я избегаю использования LIKE, если мне не нужны подстановочные знаки. - person Arthur Thomas; 29.12.2009
comment
@Arthur: Я думаю, это, вероятно, имело бы большое значение. Хорошая точка зрения. - person Scott Saunders; 29.12.2009
comment
Удаление НРАВИТСЯ сделает это значительно быстрее. - person John Parker; 29.12.2009
comment
В MySQL нет разницы между LIKE и = по отношению к строковой константе без подстановочных знаков: dev.mysql.com/doc/refman/5.0/en/range-access-single-part.html - person Quassnoi; 29.12.2009

arrow_upward
0
arrow_downward

Хорошо, сначала используйте тип данных DATE для дат, так как это будет намного быстрее, чем использование VARCHAR.

Во-вторых, вам просто нужно добавить следующие ключи в синтаксис создания таблицы для таблицы article.

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

Несколько неуместно, но если бы вы использовали InnoDB, вы могли бы дополнительно сделать auther_id в качестве внешнего ключа.

Кроме того, если вам не нужно использовать «LIKE», не делайте этого — простая проверка на section="news" будет намного быстрее. (Вы даже можете использовать ENUM для разделов, если есть конечный список.)

person John Parker    schedule 28.12.2009
comment
Я заменил «НРАВИТСЯ» на «=». Изменение даты на данные Mysql требует некоторых изменений в программном обеспечении. текущие данные содержат значение метки времени, сгенерированное функцией php time(). Я планирую измениться в будущем. Большое спасибо. - person usef_ksa; 29.12.2009

arrow_upward
0
arrow_downward

Как уже говорили другие, индексы по дате, разделу и auther_id. Согласно этому:

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

вполне может быть полезно иметь один индекс с несколькими столбцами, а не три отдельных, например:

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

в вашем определении таблицы.

person davek    schedule 28.12.2009

arrow_upward
0
arrow_downward

Лучше всего создать секционированную таблицу на основе даты/разделов.

затем создайте локально секционированные индексы, чтобы производительность была намного выше.

person Venkataramesh Kommoju    schedule 29.12.2009