reorganizar - ¿Cómo agrego índices a las tablas de MySQL?

reorganizar indices en mysql

Question

Tengo una tabla MySQL muy grande con aproximadamente 150,000 filas de datos. Actualmente, cuando intento correr

SELECT * FROM table WHERE id = ''1'';

el código funciona bien ya que el campo ID es el índice principal. Sin embargo, recientemente para un desarrollo en el proyecto, tengo que buscar en la base de datos por otro campo. Por ejemplo

SELECT * FROM table WHERE product_id = ''1'';

Sin embargo, este campo no estaba indexado anteriormente, lo he agregado como un índice, pero cuando intento ejecutar la consulta anterior, los resultados son muy lentos. Una consulta EXPLAIN revela que no hay un índice para el campo product_id cuando ya he agregado uno y, como resultado, la consulta lleva de 20 minutos a 30 minutos para devolver una sola fila.

Mis resultados completos de EXPLAIN son:

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

Podría ser útil tener en cuenta que acabo de echar un vistazo y el campo ID se almacena como INT, mientras que el campo PRODUCT_ID se almacena como VARCHAR. ¿Podría ser esta la fuente del problema?

Answer 1

Puede utilizar esta sintaxis para agregar un índice y controlar el tipo de índice (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;

Puede obtener información sobre las diferencias entre los índices BTREE y HASH aquí: http://dev.mysql.com/doc/refman/5.5/en/index-btree-hash.html

Answer 2

Se pueden agregar índices de dos tipos: cuando define una clave principal, MySQL la tomará como índice por defecto.

Explicación

Clave primaria como índice

Considere que tiene una tabla tbl_student y desea student_id como clave principal:

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

La declaración anterior agrega una clave principal, lo que significa que los valores indexados deben ser únicos y no pueden ser NULL.

Especifique el nombre del índice

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

La declaración anterior creará un índice ordinario con el nombre de student_index .

Crear un índice único

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

Aquí, student_unique_index es el nombre del índice asignado a student_id y crea un índice para el cual los valores deben ser únicos (aquí se puede aceptar null).

Opción de texto completo

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

La declaración anterior creará el nombre del índice de texto completo con student_fulltext_index , para lo que necesita MyISAM Mysql Engine.

¿Cómo eliminar índices?

DROP INDEX `student_index` ON `tbl_student`

¿Cómo comprobar los índices disponibles?

SHOW INDEX FROM `tbl_student`

Answer 3

Usted dice que tiene un índice, la explicación dice lo contrario. Sin embargo, si realmente lo haces, esta es la forma de continuar:

Si tiene un índice en la columna, y MySQL decide no usarlo, puede ser porque:

1. Hay otro índice en la consulta que MySQL considera más apropiado de usar, y puede usar solo uno. La solución suele ser un índice que abarca varias columnas si su método normal de recuperación es por valor de más de una columna.
2. MySQL decide que hay muchas filas coincidentes, y piensa que un escaneo de tablas es probablemente más rápido. Si ese no es el caso, a veces una ANALYZE TABLE ayuda.
3. En consultas más complejas, decide no usarlo en base al vudú pensado extremadamente inteligente en el plan de consulta que, por alguna razón, simplemente no se ajusta a sus requisitos actuales.

En el caso de (2) o (3), podría convencer a MySQL para que use el índice por el sistema de sugerencias de índices , pero si lo hace, asegúrese de realizar algunas pruebas para determinar si realmente mejora el rendimiento al usar el índice como lo sugiere. .

Answer 4

Vale la pena señalar que varios índices de campo pueden mejorar drásticamente el rendimiento de su consulta. Por lo tanto, en el ejemplo anterior, asumimos que ProductID es el único campo para buscar, pero si la consulta dice que ProductID = 1 AND Category = 7, entonces un índice de múltiples columnas ayuda. Esto se logra con lo siguiente:

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

Además, el índice debe coincidir con el orden de los campos de consulta. En mi ejemplo extendido, el índice debe ser (ProductID, Category) no al revés.

Answer 5

ALTER TABLE TABLE_NAME ADD INDEX (COLUMN_NAME);

Answer 6

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

Nunca compares integer con strings en MySQL. Si id es int , elimine las comillas.