optimization español - ¿Cómo agrego índices a las tablas de MySQL?




index funciona (6)

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?


Answers

ALTER TABLE TABLE_NAME ADD INDEX (COLUMN_NAME);

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`

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


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

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


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. .


La llamada sqrt no es perfectamente precisa, como se ha mencionado, pero es interesante e instructivo que no desaprueba las otras respuestas en términos de velocidad. Después de todo, la secuencia de instrucciones en lenguaje ensamblador para un sqrt es pequeña. Intel tiene una instrucción de hardware, que no es utilizada por Java, creo, porque no cumple con IEEE.

Entonces, ¿por qué es lento? Porque Java en realidad está llamando a una rutina de C a través de JNI, y en realidad es más lento hacerlo que llamar a una subrutina Java, que en sí misma es más lenta que hacerlo en línea. Esto es muy molesto, y Java debería haber encontrado una mejor solución, es decir, construir llamadas de biblioteca de punto flotante si es necesario. Oh bien.

En C ++, sospecho que todas las alternativas complejas perderían velocidad, pero no las he comprobado todas. Lo que hice, y lo que la gente de Java encontrará útil, es un simple truco, una extensión de las pruebas de casos especiales sugeridas por A. Rex. Utilice un solo valor largo como una matriz de bits, que no está marcada con límites. De esa manera, tienes búsqueda booleana de 64 bits.

typedef unsigned long long UVLONG
UVLONG pp1,pp2;

void init2() {
  for (int i = 0; i < 64; i++) {
    for (int j = 0; j < 64; j++)
      if (isPerfectSquare(i * 64 + j)) {
    pp1 |= (1 << j);
    pp2 |= (1 << i);
    break;
      }
   }
   cout << "pp1=" << pp1 << "," << pp2 << "\n";  
}


inline bool isPerfectSquare5(UVLONG x) {
  return pp1 & (1 << (x & 0x3F)) ? isPerfectSquare(x) : false;
}

La rutina isPerfectSquare5 se ejecuta en aproximadamente 1/3 del tiempo en mi máquina core2 duo. Sospecho que más ajustes en la misma línea podrían reducir el tiempo en promedio, pero cada vez que verifica, está cambiando más pruebas por más eliminatorias, por lo que no puede ir mucho más lejos en esa carretera.

Ciertamente, en lugar de tener una prueba separada para el negativo, puede verificar los 6 bits altos de la misma manera.

Tenga en cuenta que todo lo que estoy haciendo es eliminar los cuadrados posibles, pero cuando tengo un caso potencial, tengo que llamar al original, en línea esPerfectSquare.

La rutina init2 se llama una vez para inicializar los valores estáticos de pp1 y pp2. Tenga en cuenta que en mi implementación en C ++, estoy usando unsigned long long, así que ya que está firmado, tendría que usar el operador >>>.

No hay una necesidad intrínseca de verificar los límites de la matriz, pero el optimizador de Java tiene que resolver esto rápidamente, así que no los culpo por eso.







mysql optimization indexing row