Come aggiungo indici alle tabelle MySQL?

Ho una tabella MySQL molto grande con circa 150.000 righe di dati. Attualmente, quando provo a correre

SELECT * FROM table WHERE id = '1'; 

il codice funziona bene in quanto il campo ID è l’indice primario. Tuttavia, recentemente per uno sviluppo nel progetto, devo cercare nel database da un altro campo. Per esempio

 SELECT * FROM table WHERE product_id = '1'; 

Questo campo non è stato precedentemente indicizzato, tuttavia, l’ho aggiunto come indice, ma quando provo a eseguire la query precedente, i risultati sono molto lenti. Una query EXPLAIN rivela che non esiste un indice per il campo id_prodotto quando ne ho già aggiunto uno e, di conseguenza, la query prende qualsiasi dove da 20 minuti a 30 minuti per restituire una singola riga.

I miei risultati completi di EXPLAIN sono:

 | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+----------------------+------+---------+------+------+------------------+ | 1 | SIMPLE | table | ALL | NULL | NULL | NULL | NULL | 157211 | Using where | +----+-------------+-------+------+----------------------+------+---------+------+------+------------------+ 

Potrebbe essere utile notare che ho appena dato un’occhiata e il campo ID è memorizzato come INT mentre il campo PRODUCT_ID è memorizzato come VARCHAR. Questa potrebbe essere la fonte del problema?

     ALTER TABLE `table` ADD INDEX `product_id` (`product_id`) 

    Non confrontare mai integer con strings in MySQL. Se id è int , rimuovi le virgolette.

     ALTER TABLE TABLE_NAME ADD INDEX (COLUMN_NAME); 

    È ansible utilizzare questa syntax per aggiungere un indice e controllare il tipo di indice (HASH o BTREE).

     create index your_index_name on your_table_name(your_column_name) using HASH; or create index your_index_name on your_table_name(your_column_name) using BTREE; 

    Puoi conoscere le differenze tra gli indici BTREE e HASH qui: http://dev.mysql.com/doc/refman/5.5/en/index-btree-hash.html

    Vale la pena notare che più indici di campo possono migliorare drasticamente le prestazioni delle query. Quindi, nell’esempio sopra, assumiamo che ProductID sia l’unico campo da cercare ma che la query dicesse ProductID = 1 AND Category = 7, quindi un indice di colonne multiple aiuta. Questo è ottenuto con il seguente:

     ALTER TABLE `table` ADD INDEX `index_name` (`col1`,`col2`) 

    Inoltre, l’indice deve corrispondere all’ordine dei campi della query. Nel mio esempio esteso l’indice dovrebbe essere (ProductID, Category) non il contrario.

    È ansible aggiungere indici di due tipi: quando si definisce una chiave primaria, MySQL la prenderà come indice per impostazione predefinita.

    Spiegazione

    Chiave primaria come indice

    Considera che hai una tabella tbl_student e vuoi student_id come chiave primaria:

     ALTER TABLE `tbl_student` ADD PRIMARY KEY (`student_id`) 

    L’istruzione sopra aggiunge una chiave primaria, il che significa che i valori indicizzati devono essere univoci e non possono essere NULL.

    Specifica il nome dell’indice

     ALTER TABLE `tbl_student` ADD INDEX student_index (`student_id`) 

    L’affermazione precedente creerà un indice ordinario con nome student_index .

    Crea un indice univoco

     ALTER TABLE `tbl_student` ADD UNIQUE student_unique_index (`student_id`) 

    Qui, student_unique_index è il nome dell’indice assegnato a student_id e crea un indice per il quale i valori devono essere univoci (qui null può essere accettato).

    Opzione fulltext

     ALTER TABLE `tbl_student` ADD FULLTEXT student_fulltext_index (`student_id`) 

    L’istruzione sopra riportata creerà il nome dell’indice Fulltext con student_fulltext_index , per il quale è necessario MyISAM Mysql Engine.

    Come rimuovere gli indici?

     DROP INDEX `student_index` ON `tbl_student` 

    Come controllare gli indici disponibili?

     SHOW INDEX FROM `tbl_student` 

    Dici di avere un indice, la spiegazione dice il contrario. Tuttavia, se lo fai davvero, questo è come continuare:

    Se hai un indice sulla colonna, e MySQL decide di non usarlo, potrebbe perché:

    1. C’è un altro indice nella query che MySQL ritiene più appropriato da usare e può usarne solo uno. La soluzione è solitamente un indice che si estende su più colonne se il loro normale metodo di recupero è in base al valore di più di una colonna.
    2. MySQL decide che ci sono molte righe corrispondenti e pensa che un tablescan sia probabilmente più veloce. Se questo non è il caso, a volte una ANALYZE TABLE aiuta.
    3. Nelle query più complesse, decide di non utilizzarlo in base a un voodoo estremamente intelligente concepito nel piano di query che, per qualche motivo, non soddisfa le tue attuali esigenze.

    Nel caso di (2) o (3), potresti persuadere MySQL a usare l’indice per la sillaba dell’indice dell’indice , ma se lo fai, assicurati di eseguire alcuni test per determinare se migliora effettivamente le prestazioni per usare l’indice mentre lo induci .