Compartir tecnología

한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina

Tabla de contenido

Aplicación de índice

¿Qué índices tiene MySQL?

¿Cuál es la diferencia entre un índice normal y un índice único?

¿Cómo configurar el índice de clave principal del índice agrupado? Pregunta: ¿Qué pasará si no lo configura?

¿Qué tipo de campos elegimos habitualmente para crear índices?

¿Son mejores más índices?

¿Cómo optimizar el índice (que cubre la optimización del índice, la prevención de fallas del índice, el incremento de la clave principal, la optimización del índice de prefijo)

Si se crea un índice, ¿se utilizará al realizar consultas (falla del índice, el optimizador selecciona un plan de ejecución según el costo)

Si defino un campo de fecha de tipo varchar, y uno de los datos es '20230922', y si hay un índice en este campo de fecha, entonces si la condición donde de mi consulta es donde tiempo=20230922 sin comillas simples, ¿el ¿El índice aún se ve afectado?

¿La última versión de MySQL ha resuelto algún caso de falla del índice (índice de función: el valor calculado por la función también se puede indexar, mecanismo de escaneo de salto de índice (prefijo más a la izquierda))

¿Cuál es el principio de coincidencia más a la izquierda?

¿A qué debo prestar atención al establecer un índice conjunto (coloque los más diferenciados en el extremo izquierdo, el principio de coincidencia más a la izquierda, y no utilice el índice después de la consulta de rango)?

Orden de consulta del principio de coincidencia más a la izquierda

¿Qué es el pushdown de índice? Agregado en MySQL5.6 para optimizar consultas de datos.

¿Cómo crear un índice donde a>1 y b=2 yc <3?

(A,B,C) índice conjunto seleccione * de tbn donde a=? y b en (?,?) y c>?

¿Cómo crear un índice conjunto donde a>100 y b=100 yc=123 ordenan por d?

seleccione id, nombre de XX donde edad > 10 y nombre como 'xx%', hay un índice conjunto (nombre, edad), hablemos del proceso de consulta

---

Aplicación de índice

MySQL¿Qué índices hay?

Aprendí que MySQL tieneClave primariaÍndice, índice único, índice ordinario, índice de prefijo,índice sindicalEste tipo de índices.

El motor Innodb requiere que cada tabla de base de datos tenga unClave primariaíndice，No se permiten valores de columna de índicevalor nulo。Por ejemplo, el campo de identificación en la tabla es el índice de clave principal

Índice único: Garantice la unicidad de cada fila de datos en la columna de datos, pero permita valores nulos.

EntoncesPara los campos que se consultan con frecuencia, podemos crear un índice normal para este campo.，Si hay varios campos, puede considerar crearíndice sindical,usarCobertura del índiceLas funciones mejoran la eficiencia de las consultas.

Para texto largo, cadenas y otros tipos de campos, como títulos de artículos, nombres de productos, etc., solo podemos indexar la parte del prefijo de estos campos, es decirCree un índice de prefijo para reducir el espacio de almacenamiento del índice.

¿Cuál es la diferencia entre un índice normal y un índice único? ¿Cuál tiene mejor rendimiento de actualización? 、

• Un índice único puede ser un poco más rápido al consultar un valor único porque puede finalizar la búsqueda después de encontrar la primera coincidencia.

• Para operaciones de inserción y actualización, un índice normal puede ser un poco más rápido porque no requiere comprobaciones de unicidad.

1. Los valores de las columnas de índice ordinarias se pueden repetir, pero los valores de las columnas de índice únicas deben ser únicos. Cuando insertamos un valor repetido en un índice único, se informará un error debido a la restricción de unicidad.

2. CreoEl rendimiento de actualización del índice ordinario será mejor, porque cuando se actualiza el índice ordinario, si la página de datos actualizada no estáMemoria Si es así, puede almacenar en caché directamente la operación de actualización en el búfer de cambios y se completará la operación de actualización. (no se requiere verificación de unicidad)

3. pero,El índice único debe tener restricciones únicas si la página de datos actualizada no está en el.MemoriaSi es así, debe leer la página de datos correspondiente del disco a la memoria para determinar si hay un conflicto. Esto implicará la aleatorización del disco.IOAcceso.

4. Debido a que los índices ordinarios pueden utilizar la función de búfer de cambio, la actualización de los índices ordinarios es más rápida que la de los índices únicos.Acceso aleatorio reducido al disco, por lo que el rendimiento de las actualizaciones es mejor

índice agrupadodeClave primaria¿Cómo configurar el índice? Pregunta: ¿Qué pasará si no lo configura?

Cuando InnoDB crea un índice agrupado, seleccionará diferentes columnas como índices según diferentes escenarios:

1. Si hay una clave principal, la clave principal se utilizará como clave de índice del índice agrupado de forma predeterminada.

2. Si no hay una clave principal, seleccioneEl primero no contiene Valor nuloLa única columna de es comoíndice agrupadoclave de índice

3. En ausencia de cualquiera de los anteriores, InnoDB generará automáticamente una columna de ID de fila de incremento automático implícita como clave de índice del índice agrupado.

¿Qué tipo de campos elegimos habitualmente para crear índices?

Escenarios donde es aplicable la indexación:

1. Los campos tienen restricciones de unicidad, como el código de producto

2. Campos utilizados frecuentemente en condiciones de consulta WHERE, que puede mejorar la velocidad de consulta de toda la tabla. Si la condición de consulta no es un campo, se puede establecer un índice conjunto.

3. Campos de uso frecuente en GROUPBY y ORDER BY, Por lo que no es necesario volver a ordenar al realizar la búsqueda, porque todos los registros del árbol B+ se ordenan una vez establecido el índice.

Escenarios no aptos para indexación

1. Campos no utilizados en condiciones WHERE, GROUP BY, ORDER BY, el valor del índice es un posicionamiento rápido. Si el campo no se puede posicionar, generalmente no es necesario crear un índice, porque el índice ocupará espacio físico.

2. Campos poco distintivos , no es necesario crear un índice, por ejemplo, el campo de género solo tiene hombres y mujeres. Si los registros de hombres y mujeres se distribuyen uniformemente en la tabla de la base de datos, no importa qué valor se busque, la mitad de los datos pueden. Ser obtenido.En estos casos, es mejor no indexar porque MySQLtodavía hay unooptimizador de consultas, cuando el optimizador de consultas encuentra que un determinado valor aparece en un alto porcentaje de filas de datos en la tabla, generalmente ignorará el índice y realizaráEscaneo completo de la tabla。

3. Campos actualizados frecuentemente, por ejemplo, no indexa el saldo de usuarios de proyectos de comercio electrónico porque los campos de índice se modifican con frecuencia.mantener Árbol B+orden, entonces se requiere una reconstrucción frecuente del índice, y este proceso afectará el rendimiento de la base de datos.

4. No se recomienda utilizar valores desordenados.(como una tarjeta de identificación, UUID) como índice, cuando la clave principal es incierta, provocará la división frecuente de los nodos hoja y la fragmentación del almacenamiento en disco.

• La tabla de datos es más pequeña: Cuando la cantidad de datos en una tabla es pequeña, o cuando una consulta requiere escanear una gran parte de los datos en la tabla, el optimizador de la base de datos puede elegir un escaneo completo de la tabla en lugar de usar un índice. En este caso, el costo de mantener el índice puede ser mayor que la ganancia de desempeño.

¿Son mejores más índices?

No, aunque los índices pueden mejorar la eficiencia de las consultas, crear un índice más significa que se generará un nuevo índice de árbol B+, que ocupará espacio de almacenamiento, especialmente cuando la cantidad de datos de la tabla es muy grande, el índice ocupará más espacio.

Cuantos más índices haya, el rendimiento de escritura de la base de datos disminuirá, porque cada vez que agrega, elimina o modifica la tabla, debe mantener el orden de cada índice del árbol B+.

¿Cómo optimizar el índice (?índice de coberturaOptimice y evite fallas en el índice,Clave primariaOptimización incremental del índice de prefijo)

He utilizado estos métodos de optimización.

1. Para SQL que necesita consultar datos en varios campos, podemos crearíndice sindical, por lo que el método de consulta se convierteíndice de cobertura, evitando el respaldo de tablas y reduciendo una gran cantidad de operaciones de E/S.

2. nuestroClave primariaLos índices son preferiblemente valores crecientes.Debido a que nuestro índice almacena datos en orden, si el valor de la clave principal es un valor aleatorio, puede provocar la división de la página. La división de la página provocará una gran cantidad de fragmentos de memoria, por lo que la estructura del índice no será compacta, lo que provocará una gran cantidad de fragmentos de memoria. afectar la eficiencia de la consulta.

3. queremosEvite escribir errores en el índice SQL Declaraciones como no realizar coincidencias difusas hacia la izquierda o hacia la izquierda en columnas de índice, no realizar cálculos, funciones ni operaciones de conversión de tipos en índices para utilizar índices conjuntos correctamente, debe seguir el principio de coincidencia más a la izquierda, etc.En la cláusula WHERE, si la columna de condición antes de OR es una columna de índice y la columna de condición después de OR no es una columna de índice, el índice fallará.

• Utilice no igual a (<>) o operador NOT: estos operadores generalmente invalidan el índice porque escanean toda la tabla.

• Operador O: si se usa OR en la condición de consulta y las condiciones en ambos lados del OR involucran índices diferentes, entonces estos índices no se pueden usar.

  • usar OR operador, siOR Las condiciones en ambos lados involucran índices diferentes y el motor de la base de datos no puede usar múltiples índices al mismo tiempo para optimizar la consulta en la mayoría de los casos.Esto esporque OR El operador solo necesita cumplir las condiciones de ambos lados, lo que aumenta la complejidad de la optimización de la consulta.

1. Índice de una cadena grande, podemos considerar usaríndice de prefijoSolo se indexa la parte del prefijo de la columna de índice para ahorrar espacio de almacenamiento del índice y mejorar el rendimiento de las consultas.

2. Es mejor configurar el índice en NO NULO : Para utilizar mejor el índice, la columna de índice debe establecerse en la restricción NOT NULL. Hay dos razones:

   1. La presencia de NULL en las columnas de índice hará que la selección del índice del optimizador sea más complicada, lo que dificultará la optimización de operaciones como el recuento.

   2. El valor NULL no tiene sentido, pero ocupará espacio físico. Hay una columna de valor nulo.Se utilizará al menos 1 byte de espacio para almacenar NULL lista de valores

Si se crea un índice, ¿se utilizará al realizar consultas (fallo del índice,optimizadorSeleccione el plan de ejecución según el costo)

No.

1. he aprendidoIncluso si la consulta usa un índice, es posible que no use el índice.

   1. Por ejemplo: cuando nuestra declaración de consulta realiza operaciones de coincidencia aproximada izquierda, cálculo de expresiones, funciones y conversión de tipos implícitos en el campo de índice, la declaración de consulta no puede pasar por el índice y el método de consulta se convierte en un escaneo completo de la tabla.

   2. y usamosíndice sindicalAl realizar consultas, si no se sigue el principio de coincidencia más a la izquierda, también se producirá una falla en el índice.。

2. El optimizador esElija un método de consulta basado en consideraciones de costos, cuando se utiliza el índice secundario para la consulta, el optimizador calculará el costo de la devolución de la tabla y el costo del escaneo completo de la tabla. Si el costo de la devolución de la tabla es demasiado alto, el optimizador elegirá no usar el índice, sino usar el. escaneo completo de la tabla.

Si defino un campo de fecha de tipo varchar, y uno de los datos es '20230922', y si hay un índice en este campo de fecha, entonces si la condición donde de mi consulta es donde tiempo=20230922 sin comillas simples, ¿el ¿El índice aún se ve afectado?

No alcanzará el índice.

Porque mysql se encuentraComparación de cadenas y númerossucederá cuandoconversión de tipo implícita, voluntadConvertir objeto de cadena a número, este proceso de conversión en realidad implicafunción . En la consulta que mencionó, el campo de fecha es una cadena, por lo que cuando se produce una conversión de tipo implícita, se aplicará al campo de índice de fecha. Si se realiza el cálculo de la función en el índice, el índice dejará de ser válido.

Para columnas de índice de tipo entero, por ejemploid Columna cuyo valor se almacena directamente en el índice sin que se produzca el cálculo de la función.Esto significa usar en la consulta.idAl emparejar, no es necesarioidRealice cualquier cálculo funcional o conversión y simplemente compare valores enteros.

MySQL¿La última versión ha resuelto algún caso de falla del índice (Índice de función:cálculo de funcionesEl valor posterior también se puede indexar y omitir el mecanismo de escaneo de índice (prefijo más a la izquierda))

Aprendí que MySQL8.0 puede agregar campos.índice de función, esta nueva característica puede resolver el problema de falla del índice cuando se usan funciones en el índice.

Otra característica nueva esescaneo de salto de índiceAntes de la versión 5.7, cuando se usaba un índice conjunto, si no se cumplía el principio de coincidencia más a la izquierda, se produciría una falla en el índice. Sin embargo, después de que se introdujo la función de escaneo de salto de índice en 8.0, los índices conjuntos aún se pueden usar incluso si se aplica el principio de coincidencia más a la izquierda. no se sigue.

¿Cuál es el principio de coincidencia más a la izquierda?

Supongamos que hay un índice conjunto (a, b, c). Su orden de almacenamiento es ordenar por a primero, luego ordenar por b cuando a es igual y luego ordenar por c cuando b es igual. Debido a esta característica, cuando se utilizan índices conjuntos, existe un principio de coincidencia más a la izquierda. Las reglas específicas son:

1. El índice federado de MySQL comenzará desdeLa columna de índice más a la izquierda comienza a coincidir con las condiciones de la consulta y luego coincide en secuencia de izquierda a derecha. Si las condiciones de la consulta no utilizan una columna, todas las columnas a la derecha de la columna no se pueden indexar.

2. Cuando se utiliza una columna en la condición de consulta,Sin embargo, el valor de esta columna contiene una consulta de rango y los campos de la consulta de rango se pueden usaríndice sindical, pero el índice conjunto no se puede utilizar en los campos detrás del campo de consulta de rango.

Por lo tanto, cuando utilizamos índices conjuntos, debemos cumplir con el principio de coincidencia más a la izquierda; de lo contrario, es posible que algunos campos de índice no se indexen.

Estableceríndice sindical¿Hay algo a lo que debamos prestar atención? (Los más diferenciados se colocan en el extremo izquierdo, el principio de coincidencia más a la izquierda, y el índice no se utiliza después de la consulta de rango)

1. mayoríaPoner los campos con mayor distinción eníndice sindicalextremo izquierdo, útilMejorar el efecto de filtrado de índice, campos como UUID son más adecuados para indexar o clasificar en la parte superior de la columna de índice conjunto.

2. Si un campo con baja discriminación se coloca en el lado izquierdo del índice conjunto, puede hacer que el optimizador de consultas elija un escaneo completo de la tabla en lugar de usar el índice.

3. El principio de coincidencia más a la izquierda del índice conjunto, enCuando encuentre una consulta de rango (como >, <), la coincidencia se detendrá, es decir, los campos de la consulta de rango pueden usar el índice conjunto, pero los campos detrás del campo de consulta de rango no pueden usar el índice conjunto.Sin embargo, para las cuatro consultas de rango de >=, <=, BETWEEN y coincidencia de prefijos similares, la coincidencia no se detendrá.

   1. En MySQL, BETWEEN contiene valores límite de valor1 y valor2, similares a >= y =<.

   2. Enlace de referencia https://zhuanlan.zhihu.com/p/573138586

Orden de consulta del principio de coincidencia más a la izquierda

select * from T where c=1 and a=2 and b=3;

abc se puede indexar porque El orden de los campos de condición de consulta no afecta, el optimizador MySQL nos ayudará a ajustar el orden de consulta de los campos, para que también cumpla con el principio de coincidencia más a la izquierda.

bajo índiceempujar ¿Qué es? Agregado en MySQL5.6 para optimizar consultas de datos.

La reducción del índice puede reduciríndice secundarioLa operación de retorno de tabla durante la consulta mejora la eficiencia de la consulta porque La capa del servidor es responsable de algunas de las cosas que maneja la capa del motor de almacenamiento.Fui a solucionarlo.

• Cuando se utiliza la optimización push-down sin condiciones de índice, el motor de almacenamiento recupera los datos a través del índice y luego los devuelve al servidor MySQL.Servidor MySQL Hacer juicios sobre las condiciones del filtro.

• Cuando se utiliza la optimización push-down de condiciones de índice, si existen ciertas condiciones de juicio para las columnas indexadas, MySQL Server enviará esta parte de las condiciones de juicio al motor de almacenamiento, y luego el motor de almacenamiento juzgará si el índice cumple con las condiciones pasadas. Servidor MySQL Solo cuando el índice cumpla las condiciones, los datos se recuperarán y se devolverán al servidor MySQL.

La optimización de la condición de índice puede reducir la cantidad de veces que el motor de almacenamiento consulta la tabla subyacente y también puede reducir MySQL La cantidad de veces que el servidor recibió datos del motor de almacenamiento.

select * from t_user where age > 20 and reward = 100000;

¿Cómo crear un índice donde a>1 y b=2 yc <3?

1. Cree un índice conjunto (abc), (acb), (ab), (ac), solo un índice puede

2. Cree índices conjuntos (cab), (cba), (ca), (cb), solo c puede indexar

3. Cree un índice conjunto (ba), tanto b como a pueden indexarse

4. Cree un índice conjunto (bc), tanto byc se pueden indexar

5. crear (bac) índice sindical, b y a pueden indexarse, pero son más lentos que (licenciado en Letras) el índice conjunto tiene un beneficio más, el campo c puedeempuje hacia abajo del índice, reducirá el número de devoluciones de la tabla;

6. crear(bca) índice sindical, tanto b como c pueden indexarse, pero tiene una ventaja más que el índice conjunto (bc), el campo a puedeempuje hacia abajo del índice, reducirá el número de devoluciones de la tabla;

(A,B,C) índice conjunto select * from tbn where a=? and b in (?,?) and c>? ¿Estará indexado?

Esta consulta utilizará el índice conjunto. (A,B,C), porque la condición se basa en la columna de índice A、B、C Llega el pedido, que es el escenario de uso ideal.

1. para A=?: Esta condición es una coincidencia exacta. MySQL utilizará el índice para localizar la condición que satisface la condición. A=? registro de.

2. para B IN (?, ?): Esta condición especifica B La columna puede tomar dos valores posibles. MySQL utilizará el índice para encontrar todas las coincidencias.A=? yB La columna es un registro con cualquiera de estos dos valores.

3. para C>? : Esta condición es una consulta de rango.ya basado enA yB Según el filtro, MySQL continuará usando el índice para encontrarC Registros con valores de columna mayores que el valor especificado.

donde a>100 y b=100 y c=123 ordenar por d cómo crearíndice sindical?

CreoEstablecer ACBD en ordeníndice sindicalMejor, en este momento se pueden indexar tanto los campos b como c, yd puede usar el orden de índice para evitar la clasificación de archivos (clasificación adicional), aunque el último campo a no se puede indexar (a está desordenado), se puede presionar hacia abajo usando el índice para reducir la cantidad de retornos de la tabla.

Seleccione id, nombre de XX donde edad > 10 y nombre como 'xx%', etc.índice sindical(nombre, edad), habla sobre el proceso de consulta.

El orden del índice conjunto es primero el nombre, luego la edad. Estructuralmente, se ordena primero por nombre y luego por edad si los nombres son iguales.Por lo tanto, el optimizador debe hacer coincidir el nombre primero. El nombre es una consulta difusa correcta en este momento y no se producirá una falla en el índice, por lo que este SQL puede usar la indexación conjunta.

Específicamente, solo se puede indexar el nombre. Esto se debe a que.Después de la consulta difusa del derecho del nombre, los valores del campo de edad no están en orden, por lo que la edad no se puede indexar, pero la edad sí se puede indexar.empuje hacia abajo del índice。

Los últimos campos consultados son id y nombre. Estos dos campos se pueden encontrar en el índice conjunto, por lo que no es necesario devolver la tabla.

La consulta difusa del derecho de nombre es una consulta de rango y los siguientes campos no se pueden indexar