MySQL не использует индекс с JOIN, WHERE и ORDER

У нас есть две таблицы, напоминающие простую структуру тега-записи следующим образом (на самом деле это гораздо сложнее, но в этом суть проблемы): 

tag (A.a) | recordId (A.b)
1         | 1
2         | 1
2         | 2
3         | 2
....

И 

recordId (B.b) | recordData (B.c)
1              | 123
2              | 666
3              | 1246

Проблема заключается в получении упорядоченных записей с определенным тегом. Очевидный способ сделать это-с помощью простого соединения и индексов на (PK) (A. a, A. b), (A. b), (PK) (B. b), (B. b, B. c) как таковых: 

select A.a, A.b, B.c from A join B on A.b = B.b where a = 44 order by c;

Однако, это дает неприятный результат filesort: 

+----+-------------+-------+------+---------------+---------+---------+-----------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref       | rows | Extra                                        |
+----+-------------+-------+------+---------------+---------+---------+-----------+------+----------------------------------------------+
|  1 | SIMPLE      | A     | ref  | PRIMARY,b     | PRIMARY | 4       | const     |   94 | Using index; Using temporary; Using filesort | 
|  1 | SIMPLE      | B     | ref  | PRIMARY,b     | b       | 4       | booli.A.b |    1 | Using index                                  | 
+----+-------------+-------+------+---------------+---------+---------+-----------+------+----------------------------------------------+

Используя огромный и чрезвычайно избыточное "материализованное представление" мы можем получить довольно приличную производительность, но это за счет усложнения бизнес-логики, чего мы хотели бы избежать, тем более что таблицы A и B уже являются MV:s (и необходимы для других запросов, и воздействуют на те же запросы с помощью объединения).

create temporary table C engine=innodb as (select A.a, A.b, B.c from A join B on A.b = B.b);
explain select a, b, c from C where a = 44 order by c;
Еще больше усложняет ситуацию тот факт, что у нас есть условные обозначения на B-таблице, такие как фильтры диапазона. 
select A.a, A.b, B.c from A join B on A.b = B.b where a = 44 AND B.c > 678 order by c;

Но мы уверены, что сможем справиться с этим, если файловый порт проблема уходит.

Кто-нибудь знает, почему простое соединение в кодовом блоке 3 выше не будет использовать индекс для сортировки, и если мы можем обойти проблему каким-то образом, не создавая новый MV?

Ниже приведен полный список SQL, который мы используем для тестирования.

DROP TABLE IF EXISTS A;
DROP TABLE IF EXISTS B;
DROP TABLE IF EXISTS C;
CREATE TEMPORARY TABLE A (a INT NOT NULL, b INT NOT NULL, PRIMARY KEY(a, b), INDEX idx_A_b (b)) ENGINE=INNODB;
CREATE TEMPORARY TABLE B (b INT NOT NULL, c INT NOT NULL, d VARCHAR(5000) NOT NULL DEFAULT '', PRIMARY KEY(b), INDEX idx_B_c (c), INDEX idx_B_b (b, c)) ENGINE=INNODB;

DELIMITER $$
CREATE PROCEDURE prc_filler(cnt INT)
BEGIN
        DECLARE _cnt INT;
        SET _cnt = 1;
        WHILE _cnt <= cnt DO
                INSERT IGNORE INTO A SELECT RAND()*100, RAND()*10000;
                INSERT IGNORE INTO B SELECT RAND()*10000, RAND()*1000, '';
                SET _cnt = _cnt + 1;
        END WHILE;
END
$$
DELIMITER ;

START TRANSACTION;
CALL prc_filler(100000);
COMMIT;
DROP PROCEDURE prc_filler;

CREATE TEMPORARY TABLE C ENGINE=INNODB AS (SELECT A.a, A.b, B.c FROM A JOIN B ON A.b = B.b);
ALTER TABLE C ADD (PRIMARY KEY(a, b), INDEX idx_C_a_c (a, c));

EXPLAIN EXTENDED SELECT A.a, A.b, B.c FROM A JOIN B ON A.b = B.b WHERE A.a = 44;
EXPLAIN EXTENDED SELECT A.a, A.b, B.c FROM A JOIN B ON A.b = B.b WHERE 1 ORDER BY B.c;
EXPLAIN EXTENDED SELECT A.a, A.b, B.c FROM A JOIN B ON A.b = B.b where A.a = 44 ORDER BY B.c;
EXPLAIN EXTENDED SELECT a, b, c FROM C WHERE a = 44 ORDER BY c;
-- Added after Quassnois comments
EXPLAIN EXTENDED SELECT A.a, A.b, B.c FROM  B FORCE INDEX (idx_B_c) JOIN A ON A.b = B.b WHERE A.a = 44 ORDER BY B.c;
EXPLAIN EXTENDED SELECT A.a, A.b, B.c FROM A JOIN B ON A.b = B.b WHERE A.a = 44 ORDER BY B.c LIMIT 10;
EXPLAIN EXTENDED SELECT A.a, A.b, B.c FROM  B FORCE INDEX (idx_B_c) JOIN A ON A.b = B.b WHERE A.a = 44 ORDER BY B.c LIMIT 10;
2 7
mysql indexing sql-order-by materialized-views

2 ответа:

Когда я пытаюсь воспроизвести этот запрос с помощью ваших скриптов: 

SELECT  A.a, A.b, B.c
FROM    A
JOIN    B
ON      A.b = B.b
WHERE   a = 44
ORDER BY
        c

, он завершается в 0.0043 seconds (мгновенно), возвращает 930 строки и дает следующий план: 

1, 'SIMPLE', 'A', 'ref', 'PRIMARY', 'PRIMARY', '4', 'const', 1610, 'Using index; Using temporary; Using filesort'
1, 'SIMPLE', 'B', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'test.A.b', 1, ''

Это довольно эффективно для такого запроса.

Для такого запроса нельзя использовать один индекс как для фильтрации, так и для сортировки.

Смотрите эту статью в моем блоге для более подробных объяснений:

Если вы ожидаете, что ваш запрос вернет несколько записи, вы должны использовать индекс на A для фильтрации, а затем сортировать с помощью filesort (как это делает запрос выше).

Если вы ожидаете, что он вернет много записей (и LIMIT их), вам нужно использовать индекс для сортировки, а затем фильтровать: 

CREATE INDEX ix_a_b ON a (b);
CREATE INDEX ix_b_c ON b (c)

SELECT  *
FROM    B FORCE INDEX (ix_b_c)
JOIN    A
ON      A.b = B.b
ORDER BY
        b.c
LIMIT 10;

1, 'SIMPLE', 'B', 'index', '', 'ix_b_c', '4', '', 2, 'Using index'
1, 'SIMPLE', 'A', 'ref', 'ix_a_b', 'ix_a_b', '4', 'test.B.b', 4, 'Using index'
9

select A.a, A.b, B.c from A join B on A.b = B.b where a = 44 order by c;

Если вы псевдонимы столбцов, это поможет? Пример: 

 SELECT 
 T1.a AS colA, 
 T2.b AS colB, 
 T2.c AS colC 
 FROM A AS T1 
 JOIN B AS T2 
 ON (T1.b = T2.b) 
 WHERE 
 T1.a = 44 
 ORDER BY colC;

Единственными изменениями, которые я сделал, были:

  • я ставлю условия соединения в скобки
  • условия соединения и где условия основаны на столбцах таблицы
  • порядок по условию основан на результирующем столбце таблицы
  • я изменил псевдонимы столбцов таблицы результатов и запрашиваемых таблиц, чтобы (надеюсь) сделать их более понятными, когда я использую один или другой (и более понятными для сервер. Вы забываете ссылаться на свои столбцы в двух местах в исходном запросе).
Я знаю, что ваши реальные данные более сложны, но я предполагаю, что вы предоставили простую версию запроса, потому что проблема находится на этом простом уровне.
1