0
381
ViewsMysql: índices y orden de filas en una tabla
Paso 1:
Estoy creando una tabla simple.
CREATE TABLE `indexs`.`table_one` ( `id` INT NOT NULL AUTO_INCREMENT, `name` VARCHAR(45) NULL, PRIMARY KEY (`id`));Paso 2:
Hago dos inserciones en esta tabla.
insert into table_one (name) values ("B"); insert into table_one (name) values ("A");Paso 3:
Hago una selección, obtengo una tabla, cuyos registros están ordenados por id.
SELECT * FROM table_one; 
Este es el resultado esperado, ya que en mysql la clave primaria es un índice agrupado, por lo tanto los datos estarán ordenados físicamente por ella.
Ahora la parte que no entiendo.
Paso 4:
Estoy creando un índice en la columna de nombre.
CREATE INDEX index_name ON table_one(name)Repito el paso 3 nuevamente, pero obtengo un resultado diferente. Las líneas ahora están ordenadas de acuerdo con la columna de nombre.
¿Por qué está pasando esto? por qué el orden de las filas en la tabla cambia de acuerdo con el nuevo índice en la columna de nombre, porque según tengo entendido, en mysql, la clave principal es el único índice agrupado, y todos los índices creados adicionalmente son secundarios.
2 answers
Answer question0
Hago una selección, obtengo una tabla, cuyos registros están ordenados por id. [...] Este es el resultado esperado, porque en mysql la clave principal es un índice agrupado, por lo tanto, los datos estarán ordenados físicamente por él.
Hay un malentendido de un concepto aquí.
Las filas de la tabla no tienen un orden inherente: representan un conjunto desordenado de filas. Si bien el índice agrupado impone un orden físico de los datos almacenados, no garantiza el orden en que una consulta de select devuelve las filas.
Si desea que se ordenen los resultados de la consulta, utilice una cláusula order by . Sin dicha cláusula, el orden o las filas no están definidos : la base de datos es libre de devolver los resultados en el orden que desee, y no se garantiza que los resultados sean consistentes en ejecuciones consecutivas de la misma consulta.
select * from table_one order by id; select * from table_one order by name;0
(GMB explica la mayoría)
¿Por qué está pasando esto? por qué el orden de las filas en la tabla cambia de acuerdo con el nuevo índice en la columna de nombre
Use EXPLAIN SELECT ... -- podría dar una pista de lo que estoy a punto de sugerir.
Agregó INDEX(name) . En InnoDB, la(s) columna(s) PRIMARY KEY se agregan al final de cada índice secundario. Por lo tanto, es efectivamente un BTree ordenado por (name,id) y que contiene solo esas columnas.
Ahora, el Optimizer es libre de obtener los datos del índice, ya que tiene todo lo que solicitó (identificación y nombre). (Este índice se llama "cobertura".)
Dado que no especificó ORDER BY , el orden del conjunto de resultados es válido (consulte la discusión de GMB).
Moraleja de la historia: si desea un pedido, especifique ORDER BY . (El Optimizador es lo suficientemente inteligente como para "no hacer trabajo extra" si puede ver cómo proporcionar los datos sin hacer una ordenación.
Experimento adicional: agregue otra columna a la tabla pero no cambie los índices. Ahora encontrará que SELECT * FROM t está ordenado de manera diferente a SELECT id, name FROM t . Creo que te he dado suficientes pistas para predecir esta diferencia, si no, pregunta.