Этот запрос очень прост, все, что я хочу сделать, – получить все статьи в данной категории, упорядоченные по last_updated полю:
SELECT `articles`.* FROM `articles`, `articles_to_categories` WHERE `articles`.`id` = `articles_to_categories`.`article_id` AND `articles_to_categories`.`category_id` = 1 ORDER BY `articles`.`last_updated` DESC LIMIT 0, 20;
Но он работает очень медленно. Вот что сказал EXPLAIN:
select_type table type possible_keys key key_len ref rows Extra -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SIMPLE articles_to_categories ref article_id,category_id article_id 5 const 5016 Using where; Using temporary; Using filesort SIMPLE articles eq_ref PRIMARY PRIMARY 4 articles_to_categories.article_id 1
Есть ли способ переписать этот запрос или добавить дополнительную логику в мои PHP-скрипты, чтобы избежать Using temporary; Using filesort Using temporary; Using filesort и ускорить работу?
Структура таблицы:
*articles* id | title | content | last_updated *articles_to_categories* article_id | category_id
ОБНОВИТЬ
Я индексирован last_updated . Думаю, моя ситуация объясняется в следующих случаях :
В некоторых случаях MySQL не может использовать индексы для решения ORDER BY, хотя он по-прежнему использует индексы для поиска строк, которые соответствуют предложению WHERE. Эти случаи включают следующее:
Ключ, используемый для извлечения строк, не совпадает с ключом, используемым в ORDER BY: SELECT * FROM t1 WHERE key2 = constant ORDER BY key1;
Вы присоединяетесь ко многим таблицам, а столбцы в ORDER BY не все из первой непостоянной таблицы, которая используется для извлечения строк. (Это первая таблица на выходе EXPLAIN, у которой нет типа соединения const).
но я до сих пор не знаю, как это исправить.
Вот упрощенный пример, который я сделал для аналогичного вопроса, связанного с производительностью, когда-то ранее, который использует преимущества индексированных первичных ключевых индексов innodb (очевидно, только доступные с innodb !!)
У вас есть 3 таблицы: категория, продукт и product_category следующим образом:
drop table if exists product; create table product ( prod_id int unsigned not null auto_increment primary key, name varchar(255) not null unique ) engine = innodb; drop table if exists category; create table category ( cat_id mediumint unsigned not null auto_increment primary key, name varchar(255) not null unique ) engine = innodb; drop table if exists product_category; create table product_category ( cat_id mediumint unsigned not null, prod_id int unsigned not null, primary key (cat_id, prod_id) -- **note the clustered composite index** !! ) engine = innodb;
Самая важная вещь – это порядок составного первичного ключа с кластеризацией product_catgeory, поскольку типичные запросы для этого сценария всегда приводятся cat_id = x или cat_id в (x, y, z …).
У нас есть 500K категорий, 1 миллион продуктов и 125 миллионов категорий товаров.
select count(*) from category; +----------+ | count(*) | +----------+ | 500000 | +----------+ select count(*) from product; +----------+ | count(*) | +----------+ | 1000000 | +----------+ select count(*) from product_category; +-----------+ | count(*) | +-----------+ | 125611877 | +-----------+
Итак, давайте посмотрим, как эта схема работает для запроса, подобного вашему. Все запросы запускаются холодно (после перезапуска mysql) с пустыми буферами и без кэширования запросов.
select p.* from product p inner join product_category pc on pc.cat_id = 4104 and pc.prod_id = p.prod_id order by p.prod_id desc -- sry dont a date field in this sample table - wont make any difference though limit 20; +---------+----------------+ | prod_id | name | +---------+----------------+ | 993561 | Product 993561 | | 991215 | Product 991215 | | 989222 | Product 989222 | | 986589 | Product 986589 | | 983593 | Product 983593 | | 982507 | Product 982507 | | 981505 | Product 981505 | | 981320 | Product 981320 | | 978576 | Product 978576 | | 973428 | Product 973428 | | 959384 | Product 959384 | | 954829 | Product 954829 | | 953369 | Product 953369 | | 951891 | Product 951891 | | 949413 | Product 949413 | | 947855 | Product 947855 | | 947080 | Product 947080 | | 945115 | Product 945115 | | 943833 | Product 943833 | | 942309 | Product 942309 | +---------+----------------+ 20 rows in set (0.70 sec) explain select p.* from product p inner join product_category pc on pc.cat_id = 4104 and pc.prod_id = p.prod_id order by p.prod_id desc -- sry dont a date field in this sample table - wont make any diference though limit 20; +----+-------------+-------+--------+---------------+---------+---------+------------------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+------------------+------+----------------------------------------------+ | 1 | SIMPLE | pc | ref | PRIMARY | PRIMARY | 3 | const | 499 | Using index; Using temporary; Using filesort | | 1 | SIMPLE | p | eq_ref | PRIMARY | PRIMARY | 4 | vl_db.pc.prod_id | 1 | | +----+-------------+-------+--------+---------------+---------+---------+------------------+------+----------------------------------------------+ 2 rows in set (0.00 sec)
Так что это 0.70 секунд холодно – ой.
Надеюсь это поможет 🙂
РЕДАКТИРОВАТЬ
Прочитав ваш ответ на мой комментарий выше, кажется, у вас есть один из двух вариантов:
create table articles_to_categories ( article_id int unsigned not null, category_id mediumint unsigned not null, primary key(article_id, category_id), -- good for queries that lead with article_id = x key (category_id) ) engine=innodb;
или.
create table categories_to_articles ( article_id int unsigned not null, category_id mediumint unsigned not null, primary key(category_id, article_id), -- good for queries that lead with category_id = x key (article_id) ) engine=innodb;
зависит от ваших типичных запросов относительно того, как вы определяете свой кластерный ПК.
Вы должны быть в состоянии избежать fileort, добавив ключ на articles.last_updated . MySQL нуждается в файловом контейнере для операции ORDER BY, но может делать это без filesort, пока вы заказываете индексированный столбец (с некоторыми ограничениями).
Для получения дополнительной информации см. Здесь: http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html
Я предполагаю, что вы сделали следующее в своем db:
1) статьи -> id – первичный ключ
2) articles_to_categories -> article_id – это внешний ключ статей -> id
3) вы можете создать индекс на category_id
ALTER TABLE articles ADD INDEX (last_updated); ALTER TABLE articles_to_categories ADD INDEX (article_id);
должен это сделать. Правильный план состоит в том, чтобы найти первые несколько записей, используя первый индекс, и сделать JOIN, используя второй. Если это не сработает, попробуйте STRAIGHT_JOIN или что-то, чтобы обеспечить надлежащее использование индекса.