Cómo crear correctamente un índice de base de datos MYSQL
En las tablas de bases de datos, los campos de índice pueden mejorar enormemente la velocidad de consulta. Supongamos que creamos una tabla mytable:
Crear tabla mytable (ID INT NOT NULL, nombre de usuario VARCHAR(16)NOT NULL); insertamos aleatoriamente 10,000 registros, incluido uno: 5555, admin.
Encuentre el registro con el nombre de usuario "admin", seleccione * en mi tabla, donde el nombre de usuario es "admin"; si se ha establecido un índice en el nombre de usuario, MySQL puede encontrar el registro con precisión sin ningún escaneo. En cambio, MySQL escaneará todos los registros, es decir, consultará 10.000 registros.
Los índices se dividen en índices de una sola columna e índices combinados. Un índice de una sola columna significa que un índice contiene solo una columna. Una tabla puede tener varios índices de una sola columna, pero este no es un índice compuesto. Índice compuesto, es decir, un índice contiene varias columnas.
Los tipos de índice MySQL incluyen:
(1) Índice total
Este es el indicador más básico y no tiene restricciones. Se puede crear de las siguientes maneras:
◆Crear índice
Crear un índice en mytable(username(length)) indexName Si es de tipo CHAR y VARCHAR, la longitud puede ser menor que la longitud real del campo; si se trata de tipos BLOB y TEXT, se debe especificar la longitud, lo mismo a continuación.
◆Modificar la estructura de la tabla
ALTERAR mi tabla AGREGAR ÍNDICE[INDEX nombre]ON (nombre de usuario (longitud))
◆Especificar directamente al crear la tabla .
CREAR TABLA mi TABLA(ID INT NOT NULL, nombre de usuario VARCHAR(16)NOT NULL, ÍNDICE[nombre de ÍNDICE](nombre de usuario(longitud));
Sintaxis para eliminar índice:
Eliminar el índice [indexName] en 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. Si es un índice compuesto, la combinación de valores de la columna debe ser única. Puede crearse mediante:
◆Crear índice
Crear único en mi tabla. Índice nombreíndice (nombre de usuario (longitud))
◆Modificar la estructura de la tabla
ALTERAR mi tabla AGREGAR ÚNICO[nombre de índice]ON (nombre de usuario ( length))
◆Especifique directamente al crear una tabla.
Crear tabla mytable(ID INT NOT NULL, nombre de usuario VARCHAR(16)NOT NULL, UNIQUE[nombre de índice](nombre de usuario( length)));
(3) Índice de clave principal
Este es un índice único especial que no permite valores nulos. Por lo general, el índice de clave principal se crea al mismo tiempo. mientras se construye la tabla:
Cree la tabla mytable (ID INT NOT NULL, nombre de usuario VARCHAR(16) NOT NULL, clave principal (ID)); la tabla solo puede tener una clave principal.
(4) Índice completo
Para comparar visualmente índices de una sola columna e índices compuestos, agregue varios campos a la tabla:
Crear tabla mytable(ID INT no está vacío, nombre de usuario VARCHAR(16) no está vacío, ciudad VARCHAR(50) no está vacía, age INT no está vacío para extraer aún más la eficiencia de MySQL; necesario considerar el establecimiento de un índice combinado.
Simplemente cree el nombre, la ciudad y la edad en un índice:
ALTER TABLE mytable agrega el índice nombre_ciudad_edad (nombre(10), ciudad, edad al crear la tabla, la longitud del nombre de usuario es 16, que es); usado aquí Son 10. Esto se debe a que, en circunstancias normales, la longitud del nombre no excederá 10, lo que acelerará la consulta de índice, reducirá el tamaño del archivo de índice y aumentará la velocidad de actualización de la inserción.
Si crea índices de una sola columna para el nombre de usuario, la ciudad y la edad respectivamente, de modo que la tabla tenga tres índices de una sola columna, entonces la eficiencia de la consulta será muy diferente del índice combinado anterior, y mucho inferior a nuestro índice combinado. Aunque hay tres índices en este momento, MySQL solo puede usar el índice de una sola columna que cree 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:
¿Por qué nombre de usuario, ciudad, edad, nombre de usuario y ciudad nombre de usuario no tienen índices combinados como ciudad? y edad? ¿Esto se debe al índice compuesto MySQL? ¿El prefijo más a la izquierda? resultado. El entendimiento simple es combinar solo desde el lado izquierdo. No todas las consultas que contienen estas tres columnas usarán este índice compuesto, pero el siguiente SQL usará este índice compuesto:
seleccione * de mi tabla por qué nombre de usuario = "admin" y ciudad = "Zhengzhou"
seleccione * de mi tabla por qué nombre de usuario = "admin" y ciudad = "Zhengzhou"
p>SELECCIONE * DE mi tabla DONDE nombre de usuario = " admin "
y lo haré No use lo siguiente:
SELECCIONE * DE mi tabla POR QUÉ edad = 20, ciudad = "Zhengzhou"
Seleccione * de mi tabla por qué ciudad = "Zhengzhou"
Por ejemplo, existe tal declaración: seleccione * de los usuarios donde área=? ¿Pekín? Y edad = 22;
Si creamos un único índice en área y edad respectivamente, la consulta mysql solo puede usar un índice a la vez, por lo que aunque esto es mucho más eficiente que un escaneo completo de la tabla sin un índice, si crea un índice compuesto en las columnas de área y edad, generará una mayor eficiencia.
Si creamos un índice compuesto de (área, edad, salario), en realidad equivale a crear tres índices (área, edad, salario), (área, edad) y (área). ? ¿El mejor prefijo izquierdo? Características. Por lo tanto, cuando creamos un índice compuesto, debemos colocar las columnas más utilizadas como restricciones a la izquierda y luego disminuirlas en orden.
(5) Momento para establecer un índice
Aquí hemos aprendido cómo establecer un índice, pero ¿bajo qué circunstancias necesitamos establecer un índice? En términos generales, las columnas que aparecen en WHERE y JOIN deben indexarse, pero esto no es del todo cierto, porque MySQL solo se usa para seleccionar t nombre de mytable left JOIN my table m on t nombre de usuario donde m.age. = 20, m.city = 'Zhengzhou' En este momento, debe indexar la ciudad y la edad, y también debe indexar el nombre de usuario de mi tabla, porque también aparece en la cláusula JOIN.
Acabo de mencionar que solo los Me gusta deben indexarse en un momento específico. Debido a que MySQL no utiliza índices al realizar consultas que comienzan con los caracteres comodín % y _. Por ejemplo, la siguiente oración usa un índice:
SELECT * FROM mytable, donde el nombre de usuario es "admin%"
La siguiente oración no lo usa:
seleccione * de mi tabla cuando nombre LIKE ' % admin ' Por lo tanto, debemos prestar atención a las diferencias anteriores al usar LIKE.
Los índices son especialmente importantes para aplicaciones donde dominan las consultas. Muchas veces, los problemas de rendimiento se deben simplemente a que se olvida agregar un índice o no se agrega un índice más eficiente.
Si no se agrega ningún índice
entonces se realizará un escaneo completo de la tabla si se encuentra algún dato o incluso un dato específico. Si una tabla tiene una gran cantidad de datos y pocos resultados calificados, entonces. no indexar conducirá a un rendimiento fatal.
Déjalo ir. Sin embargo, no es necesario indexar en todos los casos. Por ejemplo, el género puede tener solo dos valores. La indexación no sólo no tiene ventajas, sino que también afecta a la velocidad de actualización. Esto se llama índice de transición.
(6) Desventajas de los índices
Lo anterior ha hablado de los beneficios de usar índices, pero el uso excesivo de índices puede conducir a abusos. Por lo tanto, el índice también tendrá sus desventajas:
Aunque el índice mejora en gran medida la velocidad de consulta, también ralentizará la velocidad de actualización de la tabla, como insertar, actualizar y eliminar la tabla. Porque al actualizar la tabla, MySQL no solo necesita guardar los datos, sino también el archivo de índice.
◆Se ocupará espacio en disco al indexar archivos. En términos generales, este problema no es grave, pero si crea varios índices combinados en una tabla grande, el archivo de índice se expandirá rápidamente.
La indexación es sólo un factor que mejora la eficiencia. Si su MySQL tiene una gran cantidad de tablas, debe dedicar tiempo a investigar y crear los mejores índices u optimizar las declaraciones de consulta.
(7) Precauciones al usar indicadores
Hay algunos consejos y precauciones al usar índices:
◆El índice no contendrá columnas con valores nulos.
Mientras una columna contenga valores nulos, no se incluirá en el índice. Siempre que una columna de un índice compuesto contenga un valor nulo, la columna no será válida para el índice compuesto. Entonces, cuando diseñamos la base de datos, no debemos permitir que el valor predeterminado del campo sea NULL.
◆Utiliza índices cortos.
Si es posible, indexe las columnas de cadena y especifique una longitud de prefijo. Por ejemplo, si tiene una columna CHAR(255), no indexe la columna completa si los valores múltiples son únicos dentro de los primeros 10 o 20 caracteres. Los índices cortos no solo aumentan la velocidad de consulta, 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 ya se usa en la cláusula donde, las columnas ordenadas por no usarán el índice. Por lo tanto, cuando la clasificación predeterminada de la base de datos pueda cumplir con los requisitos, no utilice operaciones de clasificación; intente no incluir la clasificación de varias columnas. Si es necesario, es mejor crear un índice compuesto para estas columnas.
◆Operación de declaración similar
En términos generales, se desaconseja el uso de operaciones similares. Si es necesario, cómo utilizarlo también es una cuestión. ¿Por ejemplo? %aaa%? No sé usar índices, ¿te gusta? ¿aaa%? Puedes usar índices.
◆No realizar operaciones en columnas.
seleccione * de los usuarios donde AÑO(agregar fecha)<2007 Esta operación se realizará en cada fila, lo que hará que el índice no sea válido y escanee toda la tabla, por lo que podemos cambiarlo a :
seleccione * de los usuarios donde agregar fecha <? 2007-01-01?;
◆No usar not en y
Lo anterior presenta los tipos de índice de MySQL.
########################################## # ############################################### ### #####################
La clave principal de la tabla
Crear automáticamente un índice único
Por ejemplo, hbs_bh (número de identificación de usuario) en zl_yhjbqk (información básica del usuario)
Restricciones únicas de campo de la tabla
ORACLE utiliza índices para garantizar la integridad de los datos.
Por ejemplo, lc_bh+hj_sx (número de proceso + secuencia de enlace) en lc_hj (enlace de proceso).
Campos para consulta condicional directa
Campos utilizados para restricciones en SQL
Por ejemplo, qc_bh (número de libro regional) en zl_yhjbqk (información básica del usuario)
seleccione * de zl_yhjbqk donde qc_bh=<2000 y QC_bh>=5000;
Campos asociados con otras tablas en la consulta
Los campos generalmente son Establecer relaciones de clave externa.
Por ejemplo, jldb_bh (número de punto de medición) en zl_ydcf (componente de consumo de energía).
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
Si se accede a los campos ordenados a través del índice, la velocidad de clasificación mejorará enormemente.
seleccione * de ZL_yhjbqk ordenar por qc_bh (crear índice QC_BH)
seleccione * de zl_yhjbqk donde qc_bh=? 7001? Ordenar por cb_sx (crear un índice compuesto de qc_bh+cb_sx, nota: es solo un índice, incluidos los campos qc_bh y cb_sx).
Campos utilizados para estadísticas o estadísticas de grupo en la consulta
Seleccione max(hbs_bh) de zl_yhjbqk
seleccione qc_bh, count(*) de ZL_yhjbqk grupo por control de calidad _ BH
¿En qué circunstancias no se deben crear índices o se deben crear menos índices?
Muy pocos registros de tabla
Si una tabla tiene solo cinco registros y se accede a los registros a través de un índice, primero debe acceder a la tabla de índice y luego a los datos. tabla a través de la tabla de índice. Normalmente, la tabla de índice y la tabla de datos no están en el mismo bloque de datos. En este caso, ORACLE debe leer el bloque de datos al menos dos veces. Sin un índice, ORACLE leerá todos los datos a la vez y la velocidad de procesamiento será obviamente más rápida que con un índice.
Por ejemplo, la tabla zl_sybm (que usa departamento) generalmente solo tiene unos pocos registros, y la indexación de cualquier campo que no sea la clave principal no resultará en una optimización del rendimiento. De hecho, si realiza un análisis estadístico de la tabla, ORACLE no utilizará su índice, pero realizará automáticamente un acceso completo a la tabla. Por ejemplo:
seleccione * de zl_sybm donde sydw_bh=? 5401? (El índice sydw_bh no optimizará el rendimiento)
Tablas que se insertan, eliminan y modifican con frecuencia.
Para algunas tablas comerciales procesadas con frecuencia, reduzca los índices tanto como sea posible si la consulta lo permite, como zl_yhbm, gc_dfss, gc_dfys, gc_fpdy y otras tablas comerciales.
Campos de tabla con datos repetidos y distribuidos uniformemente (como campos de género)
Si una tabla tiene 654,38 millones de filas de registros, un campo A tiene solo dos valores, T y F. Cada valor La probabilidad de distribución es aproximadamente del 50%, por lo que indexar el campo A de esta tabla generalmente no aumentará la velocidad de consulta de la base de datos.
Un campo de tabla que generalmente se consulta junto con el campo principal, pero tiene más valores de índice del campo principal.
Por ejemplo, la tabla gc_dfss (factura de electricidad real) a menudo consulta una determinada cantidad de dinero en función del número de serie de carga, el número de identificación del usuario, la fecha de lectura del medidor, el año y mes en que se produjo la factura de electricidad y el funcionamiento. bandera. Si todos los campos están integrados en el índice, se incrementará el tiempo para la modificación, inserción y eliminación de datos. De hecho, si indexa un pago por su número de secuencia de cargo, reducirá la cantidad de registros a solo unos pocos, y si lo indexa por los campos a continuación, no generará mucha ganancia de rendimiento.
Problemas con la indexación en decenas de millones de bases de datos MySQL y métodos para mejorar el rendimiento
1. Notas:
En primer lugar, hay que considerar el espacio de tabla. y espacio en disco ¿Es suficiente? Sabemos que los índices también son un tipo de datos e inevitablemente ocuparán una gran cantidad de espacio de tabla al establecer índices. Por lo tanto, al indexar tablas grandes, primero se debe considerar la capacidad del espacio.
En segundo lugar, la tabla debe estar bloqueada durante la indexación. Cabe señalar que la operación debe realizarse cuando el negocio está inactivo.
2. Ajuste de rendimiento:
Lo primero a considerar es la E/S del disco. De manera realista, debería intentar distribuir el índice y los datos en diferentes discos (independientemente de la matriz). Lógicamente, el espacio de la tabla de datos y el espacio de la tabla de índice están separados. Este es el principio básico que se debe seguir al crear un índice.
En segundo lugar, sabemos que se debe escanear toda la tabla al crear un índice, por lo que debemos considerar aumentar el valor del parámetro de inicialización db_file_multiblock_read_count. Generalmente se establece en 32 o más.
Nuevamente, además de escanear toda la tabla, se requiere una gran cantidad de operaciones de clasificación para construir el índice, por lo que es necesario ajustar el tamaño del área de clasificación.
Antes de 9i, se podía aumentar el tamaño de sort_area_size a nivel de sesión, por ejemplo configurándolo en más de 100 m.
Después de 9i, si el valor del parámetro de inicialización workarea_size_policy es VERDADERO, el área de clasificación se asigna automáticamente desde pga_aggregate_target.
Finalmente, al crear el índice, puedes agregar la opción nologging. Para reducir la gran cantidad de rehacer generado durante el proceso de indexación, mejorando así la velocidad de ejecución.
########################################## # ############################################### ### #####################
En términos generales, se deben indexar los siguientes campos:
1. La clave externa Los campos de consultas relacionadas deben estar indexados.
2. El campo a ordenar (colocado en el método de pedido)
3. El campo a consultar (colocado en el método donde)
4. Campos agrupados (colocados en el método de agrupación)
Escriba menos, lea más, cree más índices, escriba más, lea menos, cree menos índices y no cree índices si hay menos datos.
Diseñar índices 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: