Почему MySQL выбирает неправильный индекс в запросе JOIN?

Я использую простой запрос JOIN, однако MySQL продолжает показывать неверный индекс в плане EXPLAIN.

Он выбирает индекс для столбца, который не участвует в запросе.

Запрос основан на первичном ключе. Я попытался удалить индекс, но затем оптимизатор выбрал другой нерелевантный индекс.

В моем случае таблица a содержит ~ 2,5 миллиона записей, а таблица b ~ 5 миллионов записей. Каждая запись в a имеет ~2 записи в b.

Я использую MySql 5.6.

Я сделал ANALYZE и CHECK на столах.

Запрос занимает около 70 секунд, он использует неправильный индекс и выполняет вложенный цикл, почему?

SELECT 
IFNULL(SUM(a.val),0) as total
FROM a , b
where a.id = b.a_id;

1   SIMPLE  a   index   PRIMARY idx_a_c_id  5       2406691 Using index
1   SIMPLE  tv  ref idx_a_id idx_a_id   4   capb_1.a.id 1

# id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
'1', 'SIMPLE', 'spd_transaction', 'index', 'PRIMARY', 'idx_a_c_id', '5', NULL, '2406691', 'Using index'
'1', 'SIMPLE', 'tv', 'ref', 'idx_a_id', 'idx_a_id', '4', 'a.id', '1', NULL

mysql performance join explain
person Gidi Kern    schedule 20.11.2013    source источник
comment
привет, a это spd_transaction? b для телевизора?   -  person Jason Heo    schedule 20.11.2013
comment
Можете ли вы поместить оператор CREATE для таблиц, которые вы хотите СОЕДИНИТЬ? То, как вы пишете соединения, также не рекомендуется ИМХО.   -  person Mad Dog Tannen    schedule 20.11.2013
comment
CREATE TABLE A ( id int(11) NOT NULL, p_id int(11) DEFAULT NULL, date1 datetime NOT NULL, c_id int(11) NOT NULL, acc_id int(11) DEFAULT NULL, co_id int(11) DEFAULT NULL, PRIMARY КЛЮЧ (id), КЛЮЧ idx_c_id (c_id), КЛЮЧ idx_acc_id (acc_id), КЛЮЧ fk_co_id (co_id), ) ДВИГАТЕЛЬ = InnoDB ПО УМОЛЧАНИЮ CHARSET = utf8;   -  person Gidi Kern    schedule 20.11.2013
comment
CREATE TABLE b ( id int(11) NOT NULL, a_id int(11) NOT NULL, another_id int(11) NOT NULL, val decimal(32,6) DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY uk_a_id_another_id (a_id, another_id), ОГРАНИЧЕНИЕ fk_a_id ВНЕШНИЙ КЛЮЧ (a_id) ССЫЛКИ a (id) ПРИ УДАЛЕНИИ НЕТ ДЕЙСТВИЙ ПРИ ОБНОВЛЕНИИ НЕТ ДЕЙСТВИЙ ) ENGINE=InnoDB DEFAULT CHARSET=utf8;   -  person Gidi Kern    schedule 20.11.2013
comment
Что такое «idx_a_c_id»? Я не смог найти в таблице определения. Можете ли вы опубликовать EXPLAIN, когда вы принудительно используете первичный ключ?   -  person Stoleg    schedule 20.11.2013

Ответы (1)


arrow_upward
1
arrow_downward

1) Глядя на природу SQL (без предикатов), я бы предложил использовать подсказку (которую я не часто рекомендую), чтобы игнорировать индекс (ы) и заставить механизм БД использовать FTS (полное сканирование таблицы).

FTS будет лучше, чем использование любого индекса в этом сценарии.

SELECT 
IFNULL(SUM(a.val),0) as total
FROM a , b USE INDEX /* Specify no index here to ignore index and force FTS*/
where a.id = b.a_id;

Я не рекомендовал удалять индексы для таблиц, так как не знаю, используются ли они где-либо еще в вашем приложении.

2) Я бы также предложил удалить JOIN для таблицы «B», поскольку SQL не использует это дорогостоящее JOIN для большой таблицы в выходных данных.

SELECT 
IFNULL(SUM(a.val),0) as total
FROM a USE INDEX /* Specify no index here to ignore index and force FTS*/
;
person pahariayogi    schedule 29.01.2014