Performance - script
From Ibbddunq
| Line 1: | Line 1: | ||
= Indices = | = Indices = | ||
| - | Un | + | Un índice (o KEY, o INDEX) es un grupo de datos que MySQL asocia con una o varias columnas de la tabla. En este grupo de datos aparece la relación entre el contenido y el número de fila donde está ubicado. |
| - | Los | + | Los índices -como los índices de los libros- sirven para agilizar las consultas a las tablas, evitando que mysql tenga que revisar todos los datos disponibles para devolver el resultado. |
| - | Podemos crear el | + | Podemos crear el índice a la vez que creamos la tabla, usando la palabra INDEX seguida del nombre del índice a crear y columnas a indexar (que pueden ser varias): |
INDEX nombre_indice (columna_indexada, columna_indexada2...) | INDEX nombre_indice (columna_indexada, columna_indexada2...) | ||
| - | La sintaxis es ligeramente distinta segun la clase de | + | La sintaxis es ligeramente distinta segun la clase de índice: |
PRIMARY KEY (nombre_columna_1 [,nombre_columna2...]) | PRIMARY KEY (nombre_columna_1 [,nombre_columna2...]) | ||
| Line 14: | Line 14: | ||
INDEX nombre_index (columna_indexada1 [,columna_indexada2...]) | INDEX nombre_index (columna_indexada1 [,columna_indexada2...]) | ||
| - | Podemos | + | Podemos también añadirlos a una tabla después de creada: |
ALTER TABLE nombre_tabla ADD INDEX nombre_indice (columna_indexada); | ALTER TABLE nombre_tabla ADD INDEX nombre_indice (columna_indexada); | ||
| - | Los | + | Los índices permiten mayor rápidez en la ejecución de las consultas a la base de datos tipo SELECT ... WHERE |
| - | La regla | + | La regla básica es crear índices sobre aquellas columnas que se usen en una cláusula WHERE. Pero existen varias reglas donde el uso de un índice podría afectar directamente a la performance de una consulta (Group BY, Order BY) |
| - | Otra regla | + | Otra regla básica es que son mejores candidatas a indexar aquellas columnas que presentan muchos valores distintos, mientras que no son buenas candidatas las que tienen muchos valores idénticos, como por ejemplo sexo (masculino y femenino) porque cada consulta implicará siempre recorrer practicamente la mitad del indice. |
== Diferentes tipos de indices == | == Diferentes tipos de indices == | ||
| - | En algunas bases de datos existen diferencias entre KEY e INDEX ( no | + | En algunas bases de datos existen diferencias entre KEY e INDEX ( no así en MySQL donde son sinónimos) |
| - | * Un | + | * Un índice que sí es especial es el llamado PRIMARY KEY. Se trata de un índice diseñado para consultas especialmente rápidas. Todos sus campos deben ser UNICOS y no admite NULL |
| - | * Un | + | * Un indice UNIQUE es aquel que no permite almacenar dos valores iguales. |
| - | * Los indices FULL TEXT permiten realizar | + | * Los indices FULL TEXT permiten realizar búsquedas de palabras sobre tablas MYSAM. Puedes crear indices FULLTEXT sobre columnas tipo CHAR, VARCHAR o TEXT. Una vez creado puedes hacer búsquedas del tipo: |
SELECT * FROM nombre_tabla WHERE MATCH(nombre_indice_fulltext) AGAINST('palabra_a_buscar'); | SELECT * FROM nombre_tabla WHERE MATCH(nombre_indice_fulltext) AGAINST('palabra_a_buscar'); | ||
| - | Los | + | Los índices ordinarios no tienen restricciones en cuanto a la existencia de valores idénticos o nulos. Una posibilidad interesante, si pensamos crear un índice sobre columnas CHAR y VARCHAR es la de limitar el campo a indexar. Por ejemplo, cada entrada en la columna puede ser de hasta 40 caracteres y nosotros indexar unicamente los primeros 10 de cada una. Para crear estos índices basta con indicar entre paréntesis el numero de caracteres a indexar despues del nombre de la columna: |
ALTER TABLE libros ADD INDEX idx_autor(nombre(10), apellidos(10)); | ALTER TABLE libros ADD INDEX idx_autor(nombre(10), apellidos(10)); | ||
| Line 43: | Line 43: | ||
== Tips de indices == | == Tips de indices == | ||
| - | Si necesitamos un select del tipo SELECT ... WHERE columna_1 = X AND columna_2 = Y y ya tenemos un INDEX con la columna_1, podemos crear un segundo indice con la columna 2, o mejor | + | Si necesitamos un select del tipo SELECT ... WHERE columna_1 = X AND columna_2 = Y y ya tenemos un INDEX con la columna_1, podemos crear un segundo indice con la columna 2, o mejor todavía, crear un único indice combinado con las columnas 1 y 2. Estos son los índices multicolumna, o compuestos. |
| - | No obstante si | + | No obstante si tienes índices multicolumna y los utilizas en las clausulas WHERE, incluir siempre de izquierda a derecha las columnas indexadas; o el indice NO se usará: |
| - | = | + | = Bibliografía == |
*http://dev.mysql.com/doc/refman/6.0/en/mysql-indexes.html | *http://dev.mysql.com/doc/refman/6.0/en/mysql-indexes.html | ||
*http://dev.mysql.com/doc/refman/6.0/en/group-by-optimization.html | *http://dev.mysql.com/doc/refman/6.0/en/group-by-optimization.html | ||
*http://dev.mysql.com/doc/refman/6.0/en/order-by-optimization.html | *http://dev.mysql.com/doc/refman/6.0/en/order-by-optimization.html | ||
Revision as of 13:34, 8 June 2009
Contents |
Indices
Un índice (o KEY, o INDEX) es un grupo de datos que MySQL asocia con una o varias columnas de la tabla. En este grupo de datos aparece la relación entre el contenido y el número de fila donde está ubicado.
Los índices -como los índices de los libros- sirven para agilizar las consultas a las tablas, evitando que mysql tenga que revisar todos los datos disponibles para devolver el resultado.
Podemos crear el índice a la vez que creamos la tabla, usando la palabra INDEX seguida del nombre del índice a crear y columnas a indexar (que pueden ser varias): INDEX nombre_indice (columna_indexada, columna_indexada2...)
La sintaxis es ligeramente distinta segun la clase de índice:
PRIMARY KEY (nombre_columna_1 [,nombre_columna2...]) UNIQUE INDEX nombre_indice (columna_indexada1 [,columna_indexada2 ...]) INDEX nombre_index (columna_indexada1 [,columna_indexada2...])
Podemos también añadirlos a una tabla después de creada:
ALTER TABLE nombre_tabla ADD INDEX nombre_indice (columna_indexada);
Los índices permiten mayor rápidez en la ejecución de las consultas a la base de datos tipo SELECT ... WHERE
La regla básica es crear índices sobre aquellas columnas que se usen en una cláusula WHERE. Pero existen varias reglas donde el uso de un índice podría afectar directamente a la performance de una consulta (Group BY, Order BY)
Otra regla básica es que son mejores candidatas a indexar aquellas columnas que presentan muchos valores distintos, mientras que no son buenas candidatas las que tienen muchos valores idénticos, como por ejemplo sexo (masculino y femenino) porque cada consulta implicará siempre recorrer practicamente la mitad del indice.
Diferentes tipos de indices
En algunas bases de datos existen diferencias entre KEY e INDEX ( no así en MySQL donde son sinónimos)
- Un índice que sí es especial es el llamado PRIMARY KEY. Se trata de un índice diseñado para consultas especialmente rápidas. Todos sus campos deben ser UNICOS y no admite NULL
- Un indice UNIQUE es aquel que no permite almacenar dos valores iguales.
- Los indices FULL TEXT permiten realizar búsquedas de palabras sobre tablas MYSAM. Puedes crear indices FULLTEXT sobre columnas tipo CHAR, VARCHAR o TEXT. Una vez creado puedes hacer búsquedas del tipo:
SELECT * FROM nombre_tabla WHERE MATCH(nombre_indice_fulltext) AGAINST('palabra_a_buscar');
Los índices ordinarios no tienen restricciones en cuanto a la existencia de valores idénticos o nulos. Una posibilidad interesante, si pensamos crear un índice sobre columnas CHAR y VARCHAR es la de limitar el campo a indexar. Por ejemplo, cada entrada en la columna puede ser de hasta 40 caracteres y nosotros indexar unicamente los primeros 10 de cada una. Para crear estos índices basta con indicar entre paréntesis el numero de caracteres a indexar despues del nombre de la columna:
ALTER TABLE libros ADD INDEX idx_autor(nombre(10), apellidos(10));
Tips de indices
Si necesitamos un select del tipo SELECT ... WHERE columna_1 = X AND columna_2 = Y y ya tenemos un INDEX con la columna_1, podemos crear un segundo indice con la columna 2, o mejor todavía, crear un único indice combinado con las columnas 1 y 2. Estos son los índices multicolumna, o compuestos.
No obstante si tienes índices multicolumna y los utilizas en las clausulas WHERE, incluir siempre de izquierda a derecha las columnas indexadas; o el indice NO se usará:
