Cómo funcionan los índices de clave primaria y los índices ordinarios
Cada índice corresponde a un árbol B en InnoDB.
Supongamos que tenemos una tabla cuya columna de clave principal es ID. Hay un campo K en la tabla y hay un índice en K.
La declaración de creación de la tabla para el. la tabla es:
Los valores (ID, k) de R1~R5 en la tabla son (100, 1), (200, 2), (300, 3), (500, 5) , (600, 6) respectivamente. A continuación se muestra un diagrama de dos árboles.
Es fácil ver en la figura que el tipo de índice se divide en índice de clave primaria e índice de clave no primaria según el contenido del nodo hoja.
El nodo hoja del índice de clave principal almacena la fila completa de datos. En InnoDB, el índice de clave principal también se denomina índice agrupado.
El contenido de los nodos hoja en índices de clave no primaria es el valor de la clave primaria. En InnoDB, los índices de clave no primaria también se denominan índices secundarios o índices ordinarios.
Basándonos en la descripción anterior de la estructura del índice, analicemos una pregunta: ¿Cuál es la diferencia entre consultas basadas en índices de clave principal y consultas basadas en índices ordinarios?
En otras palabras, las consultas basadas en índices de clave no primaria necesitan escanear un árbol de índice más. Es por eso que deberíamos intentar utilizar consultas de clave principal.
Para mantener el orden del índice, es necesario mantener un árbol B al insertar nuevos valores. Tome la imagen de arriba como ejemplo. Si el valor de ID de la nueva fila es 700, solo necesita insertar un nuevo registro después del registro de R5. Si el valor de ID recién insertado es 400, será más problemático. Los datos deberán moverse lógicamente hacia atrás para dejar espacio.
Lo peor es que si la página de datos donde se encuentra R5 está llena, según el algoritmo del árbol B, debes solicitar una nueva página de datos y luego mover algunos datos. Este proceso se llama división de páginas. En este caso, el rendimiento naturalmente se verá afectado.
Además del rendimiento, las operaciones de división de páginas también afectan la utilización de la página de datos. Los datos que originalmente se colocaban en una página ahora se dividen en dos páginas y la utilización general del espacio se ha reducido en aproximadamente un 50%.
Por supuesto que hay divisiones y fusiones. Las páginas de datos se fusionan cuando dos páginas adyacentes tienen poca utilización debido a la eliminación de datos. El proceso de fusión puede verse como el proceso inverso al proceso de segmentación.
Basándonos en la descripción anterior del proceso de mantenimiento del índice, analicemos un caso:
Es posible que haya visto descripciones similares en algunas especificaciones de creación de tablas, que requieren que la declaración de creación de tablas Hay una clave primaria de incremento automático. Por supuesto, nada es absoluto. Analicemos qué escenarios deberían utilizar claves primarias de incremento automático y cuáles no.
La clave principal autoagregada se refiere a la clave principal definida en la columna autoagregada, que generalmente se define en la declaración de creación de la tabla:
Al insertar un nuevo registro, hay No es necesario especificar el valor del ID, el sistema obtendrá el ID máximo actual más 1 como valor de ID del siguiente registro.
En otras palabras, el modo de inserción de datos de las claves primarias autoincrementales está exactamente en línea con el escenario de inserción incremental que mencionamos anteriormente. Cada inserción de un nuevo registro es una operación de suma, que no implica mover otros registros ni desencadena la división de nodos hoja.
Cuando se utiliza un campo con lógica empresarial como clave principal, a menudo es difícil garantizar una inserción ordenada, por lo que el costo de escribir datos es relativamente alto.
Además de considerar el rendimiento, también puedes verlo desde la perspectiva del espacio de almacenamiento. Suponiendo que su tabla tiene un campo único, como un número de identificación de tipo cadena, ¿debería usar el número de identificación como clave principal o usar un campo de incremento automático como clave principal?
Porque el nodo hoja de cada índice de clave no primaria es el valor de la clave primaria. Si se utiliza el número de identificación como clave principal, cada nodo hoja del índice secundario ocupa aproximadamente 20 bytes. Si se utiliza un número entero como clave principal, solo se necesitan 4 bytes. Si es un bigint, son 8 bytes.
Obviamente, cuanto menor es la longitud de la clave primaria, más pequeños son los nodos hoja del índice ordinario y menor es el espacio ocupado por el índice ordinario.
Por lo tanto, teniendo en cuenta el rendimiento y el espacio de almacenamiento, las claves primarias de incremento automático suelen ser una opción más razonable.
¿Existen escenarios en los que sea adecuado utilizar directamente campos comerciales como claves principales? Todavía quedan algunos. Por ejemplo, los requisitos del escenario de algunas empresas son los siguientes:
Debes haber visto que este es un escenario típico de KV.
Dado que no hay otros índices, no es necesario considerar el tamaño del nodo hoja de otros índices.
En este momento, debemos dar prioridad al principio de "usar consultas de clave primaria tanto como sea posible" y establecer directamente este índice como clave primaria, evitando así tener que buscar en dos árboles cada vez.
-Aprende del tiempo geek