Вторая статья, в которой надо срочно пересмотреть то, что написано в предыдущей.
Как верно подметил товарищ
, использовать в мускле запросы вида SELECT .. WHERE is_published AND rating > 80, нельзя, потому как он сам не догадается привести это выражение к WHERE is_published = 1, а следовательно использовать т.н. композитный индекс.
1
2
|
alter table articles add index articles_is_published_rating(is_published, rating);
|
Тем самым мы создали композитный индекс, в котором в ключе двоичного дерева склеены сразу два значения. Порядок их очень важен, потому как в таком индексе все неопубликованные записи (is_published = 0) будут находиться перед всеми опубликованными записями (is_published = 1), вне зависимости от их рейтинга.
Использовать такой индекс для запроса вида WHERE rating > 80 невозможно, потому как записи с разными рейтингами могут
находится вразнобой, если не учитывать is_published. (На самом деле в постгресе всё хитрее, но в этом я сам плохо разбираюсь).
Повторяем запрос с WHERE is_published AND rating > ..
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
explain select * from articles where is_published and rating > 80 limit 5\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: articles
type: range
possible_keys: articles_rating
key: articles_rating
key_len: 8
ref: NULL
rows: 165117
Extra: Using where
1 row in set (0.00 sec)
|
Мы видим, что комбинированный индекс не помог и мускль переберет все записи с нужным рейтингом в поисках опубликованных статей.
Если переписать как советуют коллеги, то получится:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
explain select * from articles where is_published = true and rating > 80 order by rating desc limit 5\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: articles
type: range
possible_keys: idx1,articles_rating,articles_is_published_rating
key: articles_is_published_rating
key_len: 9
ref: NULL
rows: 25762
Extra: Using where
1 row in set (0.04 sec)
|
Количество перебираемых строк сократилось в 6 раз, а время с 170 мс до 10мс; Постгрес показывает одинаковые результаты вне зависимости от WHERE is_published или WHERE is_published = true
1
2
3
4
5
6
7
8
9
10
|
postgres=# explain analyze select * from articles where is_published = true and rating > 80 limit 5;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..13.70 rows=5 width=53) (actual time=10.316..10.345 rows=5 loops=1)
-> Index Scan using articles_is_published_rating on articles (cost=0.00..39567.38 rows=14445 width=53) (actual time=10.313..10.339 rows=5 loops=1)
Index Cond: ((is_published = true) AND (rating > 83))
Filter: is_published
Total runtime: 10.436 ms
(5 rows)
|
Тут, кстати, опять хорошо видно, как детально постгрес рассказывает о том, чем он будет заниматься в отличие от мускля, который шифруется как партизан. Правда, мусклю можно указывать какими индексами стоит пользоваться, а постгресу практически невозможно.
А теперь приведу крайне неприятный для мускля пример из практики. Добавляем время создания статьи:
1
2
|
postgres=# alter table articles add column created_at time;
|
и индексируем его. Постгрес всё так же демонстрирует неплохие результаты:
postgres=# explain analyze select * from articles where is_published = true and rating > 82 order by created_at desc limit 10;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..661.31 rows=10 width=61) (actual time=0.069..21.611 rows=10 loops=1)
-> Index Scan Backward using articles_created_at on articles (cost=0.00..1020600.08 rows=15433 width=61) (actual time=0.067..21.603 rows=10 loops=1)
Filter: (is_published AND (rating > 82))
Total runtime: 21.706 ms
(4 rows)
Time: 23,339 ms
А вот с мусклем у меня каши сварить не получилось:
mysql> explain select * from articles where is_published = true and rating > 80 order by created_at desc limit 10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: articles
type: range
possible_keys: idx1,articles_rating,articles_is_published_rating
key: articles_is_published_rating
key_len: 9
ref: NULL
rows: 25762
Extra: Using where; Using filesort
1 row in set (0.01 sec)
И это занимает какое-то сумашедшее время, потому что мускль пол-таблицы на жестком диске пересортировывает. Для того, что бы хоть как-то получить результаты, запрос пришлось переписать, что бы бедняжка не сортировал TEXT-ы и VARCHAR-ы:
mysql> select id from articles where is_published = true and rating > 95 order by created_at desc limit 10;
+--------+
| id |
+--------+
| 383154 |
| 384572 |
| 24783 |
| 269848 |
| 193926 |
| 70084 |
| 135605 |
| 392382 |
| 52602 |
| 225367 |
+--------+
10 rows in set (1 min 1.59 sec)
Короче, в таком виде этим запросом пользоваться в мускле нельзя в принципе. Проблема в том, что у него очень примитивный планировщик запроса в части сортировки. Насколько говорят мануалы, пользоваться индексом при сортировке он может только в том случае, если все атрибуты композитного индекса включаются в запрос в константных условиях: is_published = true, а последнее поле индекса участвует в сортировке. Но, к сожалению, этого не так то просто добиться:
mysql> explain select id from articles where is_published = true order by rating limit 10;
+----+-------------+----------+------+-----------------------------------+------------------------------+---------+-------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+-----------------------------------+------------------------------+---------+-------+-------+-------------+
| 1 | SIMPLE | articles | ref | idx1,articles_is_published_rating | articles_is_published_rating | 1 | const | 88097 | Using where |
+----+-------------+----------+------+-----------------------------------+------------------------------+---------+-------+-------+-------------+
1 row in set (0.00 sec)
Как видно из этого explain-а, от ключа используется только 1 байт (is_published), а пользоваться оставшимся индексом для сортировки мускль отказывается. Постгрес в такой же ситуации поступает интереснее:
postgres=# explain analyze select id from articles where is_published = true order by rating limit 10;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..167.81 rows=10 width=16) (actual time=0.124..0.375 rows=10 loops=1)
-> Index Scan using article_on_rating on articles (cost=0.00..1364830.89 rows=81333 width=16) (actual time=0.122..0.366 rows=10 loops=1)
Filter: is_published
Total runtime: 0.448 ms
(4 rows)
Дело в том, что пробежаться по индексу рейтинга с хвоста в начало, вытащить записи, которые отфильтровываются по is_published будет очень эффективно, возможно получится обойтись одни page read. Видимо, анализатор постгреса считает это более эффективным, чем найти записи в индексе по (is_published, rating), которые is_published = 1 и максимальный рейтинг.
Вот как-то так. На повестке дня следующие вопросы: как гарантированно научиться превращать USING WHERE в мускле в USING INDEX и как сделать так, что бы гарантированно не случалось USING FILESORT. Иногда способ — сменить мускль на постгрес (я даже написал конвертер), но иногда это не канает.
Плюс дальше надо будет обсудить пересечение индексов и всё таки JOIN-ы