MySQL non utilizza gli indici con la clausola WHERE IN?

Sto cercando di ottimizzare alcune delle query del database nella mia app Rails e ne ho diverse che mi hanno bloccato. Stanno tutti utilizzando un IN nella clausola WHERE e stanno facendo tutte le scansioni complete della tabella anche se sembra che sia presente un indice appropriato.

Per esempio:

SELECT `user_metrics`.* FROM `user_metrics` WHERE (`user_metrics`.user_id IN (N,N,N,N,N,N,N,N,N,N,N,N)) 

esegue una scansione completa della tabella e EXPLAIN dice:

 select_type: simple type: all extra: using where possible_keys: index_user_metrics_on_user_id (which is an index on the user_id column) key: (none) key_length: (none) ref: (none) rows: 208 

Gli indici non vengono utilizzati quando viene utilizzata un’istruzione IN o devo fare qualcosa in modo diverso? Le query qui sono state generate da Rails, così ho potuto rivisitare come sono definite le mie relazioni, ma ho pensato di iniziare con potenziali correzioni a livello di DB.

Guarda come MySQL utilizza gli indici .

Convalidare inoltre se MySQL esegue ancora una scansione completa della tabella dopo aver aggiunto altre 2000 righe alla tabella user_metrics . Nelle tabelle piccole, l’accesso per indice è in realtà più costoso (I / O-saggio) di una scansione della tabella e l’ottimizzatore di MySQL potrebbe tenerne conto.

Contrariamente al mio post precedente , risulta che MySQL utilizza anche un ottimizzatore basato sui costi , che è una buona notizia – ovvero, se si esegue ANALYZE almeno una volta quando si ritiene che il volume di dati nel database sia rappresentativo del futuro utilizzo quotidiano.

Quando si gestiscono gli ottimizzatori basati sui costi (Oracle, Postgres, ecc.), È necessario assicurarsi di eseguire periodicamente ANALYZE sui vari tavoli in quanto le loro dimensioni aumentano di oltre il 10-15%. (Postgres lo farà automaticamente per te, di default, mentre altri RDBMS lasceranno questa responsabilità a un DBA, cioè tu.) Attraverso l’analisi statistica, ANALYZE aiuterà l’ottimizzatore a farsi un’idea migliore di quanto I / O (e altri associati risorse, come la CPU, necessarie ad esempio per l’ordinamento) saranno coinvolte nella scelta tra i vari piani di esecuzione dei candidati. La mancata esecuzione di ANALYZE può portare a decisioni di pianificazione molto scarse, a volte disastrose (ad esempio, query millisecondo che richiedono, a volte, ore a causa di loop nidificati errati su JOIN s.)

Se le prestazioni sono ancora insoddisfacenti dopo l’esecuzione di ANALYZE , in genere sarà ansible risolvere il problema utilizzando suggerimenti, ad esempio FORCE INDEX , mentre in altri casi potresti essere incappato in un bug MySQL (ad esempio questo più vecchio , che avrebbe potuto morso eri tu a usare l’ nested_set Rails).

Ora, dato che ci si trova in un’app Rails , sarà ingombrante (e vanificherebbe lo scopo di ActiveRecord ) di emettere le query personalizzate con suggerimenti invece di continuare a utilizzare quelli generati da ActiveRecord .

Avevo menzionato che nella nostra applicazione Rails tutte le query SELECT scendevano sotto i 100ms dopo il passaggio a Postgres, mentre alcuni dei complessi join generati da ActiveRecord volte richiedevano fino a 15s o più con MySQL 5.1 a causa di cicli annidati con scansioni interne della tabella, anche quando gli indici erano disponibili. Nessun ottimizzatore è perfetto e devi essere consapevole delle opzioni. Altri potenziali problemi di prestazioni da tenere in considerazione, oltre all’ottimizzazione del piano di query, sono bloccanti. Questo è al di fuori della portata del tuo problema però.

Prova a forzare questo indice:

 SELECT `user_metrics`.* FROM `user_metrics` FORCE INDEX (index_user_metrics_on_user_id) WHERE (`user_metrics`.user_id IN (N,N,N,N,N,N,N,N,N,N,N,N)) 

Ho appena controllato, utilizza un indice sulla stessa query:

 EXPLAIN EXTENDED SELECT * FROM tests WHERE (test IN ('test 1', 'test 2', 'test 3', 'test 4', 'test 5', 'test 6', 'test 7', 'test 8', 'test 9')) 1, 'SIMPLE', 'tests', 'range', 'ix_test', 'ix_test', '602', '', 9, 100.00, 'Using where' 

A volte MySQL non usa un indice, anche se uno è disponibile. Una circostanza in cui ciò si verifica è quando l’ottimizzatore stima che l’utilizzo dell’indice richiederebbe a MySQL di accedere a una percentuale molto ampia delle righe nella tabella. (In questo caso, una scansione della tabella è probabile che sia molto più veloce perché richiede meno ricerche).

Quale percentuale di righe corrisponde alla tua clausola IN?

So che sono in ritardo per la festa. Ma spero di poter aiutare qualcun altro con problemi simili.

Ultimamente, sto avendo lo stesso problema. Poi decido di usare self-join-thing per risolvere il mio problema. Il problema non è MySQL. Il problema siamo noi Il tipo di ritorno dalla sottoquery è la differenza dalla nostra tabella. Quindi dobbiamo eseguire il cast del tipo di subquery sul tipo di colonna select. Di seguito è riportato un codice di esempio:

 select `user_metrics`.* from `user_metrics` um join (select `user_metrics`.`user_id` in (N, N, N, N) ) as temp on um.`user_id` = temp.`user_id` 

O il mio codice:

Vecchio: (Non usare indice: ~ 4s)

 SELECT `jxm_character`.* FROM jxm_character WHERE information_date IN (SELECT DISTINCT (information_date) FROM jxm_character WHERE information_date >= DATE_SUB('2016-12-2', INTERVAL 7 DAY)) AND `jxm_character`.`ranking_type` = 1 AND `jxm_character`.`character_id` = 3146089; 

Novità: (Usa indice: ~ 0.02s)

 SELECT * FROM jxm_character jc JOIN (SELECT DISTINCT (information_date) FROM jxm_character WHERE information_date >= DATE_SUB('2016-12-2', INTERVAL 7 DAY)) AS temp ON jc.information_date = STR_TO_DATE(temp.information_date, '%Y-%m-%d') AND jc.ranking_type = 1 AND jc.character_id = 3146089; 

jxm_character:

  • Record: ~ 3,5M
  • PK: jxm_character (information_date, ranking_type, character_id)

SHOW VARIABLES LIKE '%version%';

 'protocol_version', '10' 'version', '5.1.69-log' 'version_comment', 'Source distribution' 

Ultima nota: assicurati di aver compreso la regola più a sinistra dell’indice MySQL.

P / s: Scusa per il mio pessimo inglese. Inserisco il mio codice (produzione, ovviamente) per cancellare la mia soluzione: D.

Funziona meglio se rimuovi le parentesi ridondanti attorno alla clausola where?

Anche se potrebbe essere solo perché hai solo 200 o più righe, ha deciso che una scansione della tabella sarebbe stata più veloce. Prova con una tabella con più record al suo interno.