Оптимизиране на моята 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 милиона записа и един милион от тях принадлежат към една секция, DB ще сканира един милион ред.


mysql sql optimization query-optimization
person usef_ksa    schedule 28.12.2009    source източник
comment
Предполагам, че има причина да не използвате тип данни DATE за дати?   -  person John Parker    schedule 29.12.2009
comment
Не, няма причина. когато проектирах DB, не знам за типа данни 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 „новини“ и раздел = „новини“. Вероятно не, но бих проверил това. Обикновено избягвам да използвам LIKE, освен ако не ми трябват заместващи знаци. - person Arthur Thomas; 29.12.2009
comment
@Arthur: Мисля, че това вероятно ще направи голяма разлика. Добра точка. - person Scott Saunders; 29.12.2009
comment
Отпадането на LIKE ще направи това значително по-бързо. - 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.

Второ, просто трябва да добавите следните ключове към синтаксиса на вашата таблица за създаване на таблицата на статиите.

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