Cómo crear correctamente un índice de base de datos MYSQL
En las tablas de bases de datos, la indexación de campos puede mejorar enormemente la velocidad de consulta. Supongamos que creamos una tabla mytable:
CREATE TABLE mytable(ID INT NOT NULL, nombre de usuario VARCHAR(16) NOT NULL); insertamos aleatoriamente 10,000 registros, uno de los cuales es: 5555, admin.
SELECT * FROM mytable WHERE nombre de usuario='admin' al buscar el registro con el nombre de usuario "admin" si el nombre de usuario ha sido indexado, MySQL no necesita realizar ningún escaneo para encontrar el registro con precisión. En cambio, MySQL escanea todos los registros y consulta 10.000 registros.
Los índices se dividen en índices de una sola columna e índices combinados. Un índice de una sola columna significa que el índice solo contiene una sola columna. Una tabla puede tener varios índices de una sola columna, pero este no es un índice combinado. Índice combinado, es decir, un índice contiene varias columnas.
Los tipos de índices MySQL incluyen:
(1) Índice ordinario
Este es el índice más básico sin restricciones. Su método de creación es el siguiente:
◆CREATE INDEX
CREATE INDEX indexName ON mytable(username(length)); si es de tipo CHAR o VARCHAR, la longitud puede ser menor que. la longitud real del campo. En el caso de los tipos BLOB y TEXT, la longitud debe especificarse de la siguiente manera.
◆ Modificar la estructura de la tabla
ALTER mytable ADD INDEX [indexName] ON (username(length))
◆ Especifíquelo directamente al crear la tabla
CREATE TABLE mytable( ID INT NOT NULL, nombre de usuario VARCHAR(16) NOT NULL, INDEX [indexName] (nombre de usuario(longitud)) );
Sintaxis para eliminar el índice:
DROP INDEX [indexName] ON mytable;
(2) Índice único
Es similar al índice ordinario anterior, excepto que el valor del índice La columna debe ser única, pero se permiten valores nulos. En el caso de un índice compuesto, la combinación de valores de columna debe ser única. Se puede crear mediante:
◆CREAR ÍNDICE
CREAR ÍNDICE ÚNICO nombre de índice EN mytable(nombre de usuario(longitud))
◆MODIFICAR ESTRUCTURA DE LA TABLA
ALTER mytable ADD UNIQUE [ indexName] ON (nombre de usuario (longitud))
◆Especifique directamente al crear la tabla
CREATE TABLE mytable( ID INT NOT NULL, nombre de usuario VARCHAR ( 16) NOT NULL, UNIQUE [indexName] (username(length)));
(3) Índice de clave principal
Es un índice único especial que no permite valores nulos.
Por lo general, el índice de clave principal se crea al mismo tiempo que se crea la tabla:
Crear TABLE mytable(ID INT NOT NULL, nombre de usuario VARCHAR(16) NOT NULL, PRIMARY KEY(ID)); , también puede utilizar el comando ALTER . Recuerde: una tabla sólo puede tener una clave principal.
(4) Índice compuesto
Para comparar gráficamente índices de una sola columna e índices compuestos, agregue varios campos a la tabla:
Crear TABLE mytable( ID INT NOT NULL, nombre de usuario VARCHAR(16) NOT NULL, ciudad VARCHAR(50) NOT NULL, ciudad VARCHAR(50) NOT NULL, edad INT NOT NULL); Para mejorar aún más la eficiencia de MySQL, deberíamos considerar establecer un índice combinado; . En otras palabras, cree un índice sobre nombre, ciudad y edad:
ALTER TABLE mytable ADD INDEX nombre_ciudad_edad (nombre(10),ciudad,edad); es 16, aquí usamos 10. Esto se debe a que la longitud del nombre de usuario generalmente no excede 10, lo que acelerará la consulta de índice, reducirá el tamaño del archivo de índice y aumentará la velocidad de actualización de INSERT.
Si crea índices de una sola columna para el nombre de usuario, la ciudad y la edad, habrá tres índices de una sola columna en la tabla. La eficiencia de la consulta será muy diferente de la combinación de índices anterior, que es. mejor que nuestra combinación de índices. Aunque hay tres índices en este momento, MySQL sólo puede usar el índice de una sola columna que crea que parece ser el más eficiente.
Establecer un índice combinado de este tipo equivale en realidad a establecer los siguientes tres conjuntos de índices combinados:
Nombre de usuario, ciudad, edad Nombre de usuario, ciudad ¿Por qué no existen las ciudades y la edad? en el nombre de usuario? Este es el resultado del prefijo más a la izquierda del índice compuesto MySQL. El entendimiento simple es que la combinación solo comienza desde el extremo izquierdo. El índice combinado no se utilizará siempre que la consulta contenga estas tres columnas. El siguiente SQL utilizará este índice combinado:
SELECT * FROM mytable WHREE nombre de usuario="admin" AND city="Zhengzhou"<. /p >
SELECT * FROM mytable DONDE nombre de usuario="admin"
No se utilizará lo siguiente:
SELECT * FROM mytable DONDE edad=20 AND city="Zhengzhou "
SELECCIONAR * DE mytable DONDE ciudad="Zhengzhou"
Ejemplo. Existe una declaración de este tipo: seleccione * de los usuarios donde área=?beijing? y edad=22;
Si creamos un índice único en área y edad, entonces la consulta MySQL solo puede usar un índice a la vez. tiempo, por lo que, aunque esto es mucho más eficiente que escanear toda la tabla sin un índice, si creamos un único índice por región y edad, solo se puede usar un índice por consulta. Sin embargo, obtendrá una mayor eficiencia si crea un índice compuesto en las columnas de área y edad.
Si creamos un índice compuesto sobre (área, edad, salario), en realidad equivale a crear tres índices sobre (área, edad, salario), (área, edad) y (área), esto es la propiedad denominada "mejor prefijo izquierdo". Por lo tanto, al crear un índice compuesto, debemos colocar las columnas más utilizadas como restricciones a la izquierda en orden descendente.
(5) Momento para crear un índice
Hasta ahora, hemos aprendido a crear un índice, entonces, ¿bajo qué circunstancias necesitamos crear un índice? En términos generales, las columnas que aparecen en WHERE y JOIN deben indexarse, pero este no es siempre el caso, porque MySQL solo usa índices en <, <=, =, >, >=, BETWEEN, IN y, a veces, LIKE. Por ejemplo:
SELECT t.Name FROM mytable t LEFT JOIN mytable m ON t.Name=m.username WHERE m.age=20 AND m.city='Zhengzhou' En este momento, debe proporcione la ciudad y cree un índice de edad. Dado que el nombre de usuario en la tabla mytable también aparece en la cláusula JOIN, también debe indexarse.
Como se mencionó anteriormente, solo es necesario indexar ciertos períodos de tiempo de LIKE. Esto se debe a que MySQL no utiliza índices para consultas que comienzan con los caracteres comodín % y _. Por ejemplo, la siguiente oración usa un índice:
SELECT * FROM mytable WHERE nombre de usuario como 'admin%'
Y la siguiente oración no usa un índice:
SELECT * FROM mytable WHEREt Nombre como'%admin' Por lo tanto, debe prestar atención a la distinción anterior al usar LIKE.
Los índices son particularmente importantes para aplicaciones donde dominan las consultas. Los problemas de rendimiento a menudo se deben a que se olvida agregar un índice o no se agrega un índice más eficiente. Si no hay ningún índice, debe realizar un escaneo completo de la tabla para encontrar datos específicos. Si hay una gran cantidad de datos en la tabla pero pocos resultados calificados, no se agregará un índice. en
Degradación fatal del rendimiento. Pero no todas las situaciones deben indexarse. Por ejemplo, el género puede tener solo dos valores. La indexación no solo no tiene ninguna ventaja, sino que también afectará la velocidad de actualización. Este es el llamado índice de transición.
(6) Desventajas de los índices
Los anteriores son los beneficios de usar índices, pero el uso excesivo de índices puede causar abuso. Por lo tanto, los índices también tienen sus desventajas:
◆Aunque los índices aumentan en gran medida la velocidad de las consultas, también reducen la velocidad de actualización de las tablas (como INSERT, UPDATE y DELETE) porque al actualizar las tablas, MySQL no Solo tiene que guardar los datos y también guardar el archivo de índice.
◆ La creación de un índice ocupa espacio en disco en el archivo de índice. Normalmente esto no es un problema grave, pero si crea múltiples combinaciones de índices en una tabla grande, el archivo de índice puede hincharse rápidamente.
La indexación es sólo un factor que mejora la eficiencia. Si su tabla MySQL contiene una gran cantidad de datos, deberá dedicar tiempo a investigar la mejor manera de indexar u optimizar sus declaraciones de consulta.
(7) Notas sobre el uso de índices
Hay algunos consejos y precauciones al usar índices:
◆ El índice no incluirá columnas que contengan valores NULL <. /p>
Siempre que una columna contenga un valor NULL, no se incluirá en el índice, y siempre que una columna en el índice compuesto contenga un valor NULL, la columna no será válida para el índice compuesto. Por lo tanto, al diseñar una base de datos, no debemos permitir que los campos tengan valores NULL por defecto.
◆Utilice índices cortos
Para indexar columnas de cadena, especifique la longitud del prefijo siempre que sea posible. Por ejemplo, si tiene una columna CHAR(255), no indexe la columna completa si varios valores son únicos dentro de los primeros 10 o 20 caracteres. Los índices cortos no sólo aceleran las consultas sino que también ahorran espacio en disco y operaciones de E/S.
◆Clasificación de columnas de índice
La consulta MySQL solo puede usar un índice a la vez, por lo que si el índice se ha usado en la cláusula donde, las columnas ordenadas por no se indexarán .
Por lo tanto, si la clasificación predeterminada de la base de datos puede cumplir con los requisitos, no utilice la operación de clasificación; intente no incluir varias columnas en la clasificación y, si es necesario, es mejor crear índices compuestos para estas columnas.
◆Operación de declaración similar
Generalmente se desaconseja usar la operación similar. Si es necesario usarla, cómo usarla también es un problema. %aaa%? no utilizará un índice, pero al igual que ?aaa%?
◆No hagas aritmética en las columnas
selecciona * de los usuarios donde YEAR(adddate)<2007 se realizarán operaciones aritméticas en cada fila, lo que provocará que el índice falle. y realice un escaneo completo de la tabla, para que podamos cambiarla a:
seleccione * de los usuarios donde agregar fecha
◆NOT IN y <>operaciones no utilizado
Lo anterior es una introducción a uno de los tipos de índice MySQL.
########################################## # ############################################### ### ######################
La clave principal de la tabla
Índice único automático
Por ejemplo , hbs_bh (número de identificación familiar) en zl_yhjbqk (perfil de usuario básico)
Restricciones únicas en los campos de la tabla
ORACLE utiliza índices para garantizar la integridad de los datos
Como lc_bh (enlace de proceso) + hj_sx (número de proceso + secuencia de sesión) en lc_hj
Consulta de campo para consulta condicional directa
Campos utilizados para restricciones condicionales en SQL
Para ejemplo, qc_bh (número de bloque) en zl_yhjbqk (perfil de usuario básico)
seleccione * de zl_yhjbqk donde qc_bh=<2000 andqc_bh>=5000;
Campos de consulta asociados con otras tablas
Los campos suelen establecer relaciones de clave externa
Por ejemplo, jldb_bh (número de tabla de puntos de medición) en zl_ydcf (composición de electricidad)
seleccione * de zl_ydcf a,zl_yhdb b donde a.jldb_bh=b.jldb_bh y b.jldb_bh=?540100214511?
Campos ordenados en la consulta
Acceso a través del índice La velocidad de clasificación del campo de clasificación será más rápida
seleccione * de zl_yhjbqk orden por qc_bh (construya el índice qc_bh)
seleccione * de zl_yhjbqk donde qc_bh=?7001?ordene por cb_sx (construya el índice compuesto qc_bh +cb_sx, nota: solo un índice que contenga los campos qc_bh y cb_sx)
Contar o agrupar campos en la consulta
seleccione max(hbs_bh) de zl_yhjbqk
seleccione qc_bh,count(*) de zl_yhjbqk grupo por qc_bh
Qué hacer cuando no tienes índice o utilizas un pequeño número de índices
En general, debes recordar indexar los siguientes campos:
1. Campos de clave externa utilizados para consultas relacionadas
2. Campos a ordenar (colocados en el método de pedido)
3.
4. Campos a agrupar (colocados en el método donde)
5. Palabras a indexar (colocados en el método donde)
6. las columnas (póngalas en el método de grupo)
Cuantos más índices, menos escrituras y cuantas más lecturas, menos índices, más escrituras, menos lecturas, menos índices, mayor será la cantidad de; datos.
Diseñar un buen índice MySql puede hacer que su base de datos vuele y mejorar en gran medida la eficiencia de la base de datos.
Cómo crear correctamente un índice de base de datos MYSQL
etiqueta.