una repitan repetir que para optimizar ocultar lentas excluir ejemplos datos consultas consulta columna campo mysql indexing innodb

repitan - ocultar columna en consulta mysql



¿Es práctico almacenar columnas de cadenas en índices? (1)

  1. ¿Es práctico almacenar en columnas de cadenas de índices, eso solo se necesita como datos? Por ejemplo, una tabla con 20 columnas, y a menudo necesitamos strcolumn, que se busca por intcolumn. ¿Es bueno crear un índice como (intcolumn, strcolumn) o realmente solo necesitamos (intcolumn) aquí?

    Esto se conoce como índice de cobertura ; tiene el beneficio de rendimiento de poder recuperar las columnas seleccionadas del archivo de índice sin tener que buscar valores entre los registros en los datos de la tabla.

    Como con todo, su uso es una compensación que puede ser apropiada en algunas circunstancias, pero no en otras.

  2. ¿Realmente mysql in innodb engine realiza algunas acciones adicionales para recuperar los datos (cuando vemos "Using where; Using index")?

    El sqlfiddle al que su pregunta vincula muestra el Using where; Using index Using where; Using index para las cuatro consultas. Según lo documentado en EXPLAIN Extra Information :

    La columna Extra de la salida EXPLAIN contiene información adicional sobre cómo MySQL resuelve la consulta. La siguiente lista explica los valores que pueden aparecer en esta columna.

    [ deletia ]

    • Using index

      La información de columna se recupera de la tabla utilizando solo información en el árbol de índice sin tener que realizar una búsqueda adicional para leer la fila real. Esta estrategia se puede usar cuando la consulta utiliza solo columnas que son parte de un índice único.

      Si la columna Extra también dice Using where , significa que el índice se está utilizando para realizar búsquedas de valores clave. Sin Using where , el optimizador puede leer el índice para evitar leer las filas de datos pero no usarlas para las búsquedas. Por ejemplo, si el índice es un índice de cobertura para la consulta, el optimizador puede escanear sin usarlo para las búsquedas.

    Por lo tanto, todas sus consultas utilizan índices de cobertura para las búsquedas y la recuperación de datos, independientemente del motor de almacenamiento que se utilice.

    No me queda claro a qué se refiere cuando dice " innodb engine realmente hace algunas acciones adicionales para recuperar los datos ". La única diferencia en el resultado de EXPLAIN que puedo ver es que las consultas de InnoDB muestran un valor menor en la columna Rows ; Sin embargo, según lo documentado :

    La columna de rows indica el número de filas que MySQL cree que debe examinar para ejecutar la consulta.

    Para las tablas de InnoDB , este número es una estimación, y puede no ser siempre exacto.

  3. También sucede lo mismo con los ENUM. Sucede, porque Enum_field`s real_type devuelve MYSQL_TYPE_STRING. ¿Hace lo mismo para las enumeraciones?

    De nuevo, no me queda claro a qué se refiere cuando dice que " sucede lo mismo ". Sin embargo, como se discutió anteriormente, Using where; Using index Using where; Using index simplemente indica que se ha utilizado un índice de cobertura tanto para las búsquedas como para la recuperación de datos.

    Además, los campos ENUM tienen un real_type de MYSQL_TYPE_ENUM , no MYSQL_TYPE_STRING . Ver sql/field.h:1873 :

    enum_field_types real_type() const { return MYSQL_TYPE_ENUM; }

  4. ¿Podemos entonces asumir, que las enumeraciones son súper malvadas y que siempre deberíamos usar simplemente una tabla de referencia simple?

    Hay muchas razones para evitar ENUM , pero no creo que su pregunta haya tocado a ninguna de ellas.

  5. Para MyISAM es undertandable, ya que almacena en el índice no todos los valores. Pero entonces ¿por qué almacena dos valores, no uno?

    Los resultados de egrep te llevan a sacar conclusiones falsas. El hecho de que una búsqueda insensible a mayúsculas y minúsculas del patrón "tobeornottobe" encuentre dos cadenas coincidentes en el archivo .myi no significa que el índice MyISAM tenga dos registros. La estructura de datos es un árbol, de la siguiente manera:

    // / / Tobeornottobe toBeornottobe // / / tobeOrnottobe tobeorNottobe / / tobeornotTobe

    Uno obtiene una pista de esto al ver todas las cadenas .myi index file:

    $ strings mysql_index_reading_myisam.MYI Tobeornottobe toBeornottobe beOrnottobe orNottobe notTobe

    Por lo tanto, si hubiera realizado una búsqueda (sin distinción de mayúsculas y minúsculas) del patrón "nottobe" , habría encontrado cinco coincidencias en lugar de dos.

    Puede leer más sobre el formato de almacenamiento de las estructuras de índice de MyISAM en The .MYI File .

  6. Si esto es todo lo que realmente sucede, ¿son solo las restricciones actuales del kernel mysql, que no dependen de la implementación del controlador concreto?

    Me temo que no tengo idea de qué se está preguntando aquí.

Supongamos que tenemos este ejemplo de estructura / datos:

@see fiddle en http://sqlfiddle.com/#!8/1f85e/1

-- SET GLOBAL innodb_file_per_table=1; DROP TABLE IF EXISTS mysql_index_reading_myisam; CREATE TABLE IF NOT EXISTS mysql_index_reading_myisam ( id INT NOT NULL AUTO_INCREMENT , str VARCHAR(50) NOT NULL , enm ENUM(''thatis'', ''thequestion'') NOT NULL , cnt TINYINT NOT NULL , PRIMARY KEY (id) , INDEX str_cnt (str, cnt) , INDEX enm_cnt (enm, cnt) ) ENGINE=MyISAM CHARSET=Latin1; INSERT INTO mysql_index_reading_myisam (str, enm, cnt) VALUES (''Tobeornottobe'', ''Thatis'', 1) , (''toBeornottobe'', ''thatIs'', 2) , (''tobeOrnottobe'', ''ThatIs'', 3) , (''tobeorNottobe'', ''thatis'', 4) , (''tobeornotTobe'', ''THATIS'', 5) ; DROP TABLE IF EXISTS mysql_index_reading_innodb; CREATE TABLE mysql_index_reading_innodb LIKE mysql_index_reading_myisam; ALTER TABLE mysql_index_reading_innodb ENGINE InnoDB; INSERT INTO mysql_index_reading_innodb SELECT * FROM mysql_index_reading_myisam; EXPLAIN SELECT cnt FROM mysql_index_reading_myisam WHERE str = ''tobeornottobe''; EXPLAIN SELECT cnt FROM mysql_index_reading_innodb WHERE str = ''tobeornottobe''; EXPLAIN SELECT cnt FROM mysql_index_reading_myisam WHERE enm = ''thatis''; EXPLAIN SELECT cnt FROM mysql_index_reading_innodb WHERE enm = ''thatis'';

Veamos cómo se almacena internamente

# egrep --ignore-case --only-matching --text ''(tobeornottobe|thatis)'' * mysql_index_reading_innodb.frm:thatis mysql_index_reading_innodb.ibd:Tobeornottobe mysql_index_reading_innodb.ibd:toBeornottobe mysql_index_reading_innodb.ibd:tobeOrnottobe mysql_index_reading_innodb.ibd:tobeorNottobe mysql_index_reading_innodb.ibd:tobeornotTobe mysql_index_reading_innodb.ibd:Tobeornottobe mysql_index_reading_innodb.ibd:toBeornottobe mysql_index_reading_innodb.ibd:tobeOrnottobe mysql_index_reading_innodb.ibd:tobeorNottobe mysql_index_reading_innodb.ibd:tobeornotTobe mysql_index_reading_myisam.frm:thatis mysql_index_reading_myisam.MYD:Tobeornottobe mysql_index_reading_myisam.MYD:toBeornottobe mysql_index_reading_myisam.MYD:tobeOrnottobe mysql_index_reading_myisam.MYD:tobeorNottobe mysql_index_reading_myisam.MYD:tobeornotTobe mysql_index_reading_myisam.MYI:Tobeornottobe mysql_index_reading_myisam.MYI:toBeornottobe

  • En ambos motores, las enumeraciones se almacenan en * .frm como debería ser. De acuerdo.
  • En ambos motores, los datos están almacenados en datos y archivos de datos / índice. De acuerdo.
  • En el índice MyISAM tiene dos registros.
  • En el índice InnoDB tiene los cinco registros en el caso correcto.

Lo que ya he encontrado

http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html

En algunos casos, una consulta puede optimizarse para recuperar valores sin consultar las filas de datos. Si una consulta solo utiliza columnas de una tabla que son numéricas y que forman un prefijo situado más a la izquierda para alguna clave, los valores seleccionados se pueden recuperar del árbol de índice para una mayor velocidad:

SELECCIONAR key_part3 FROM tbl_name WHERE key_part1 = 1

http://www.mysqlperformanceblog.com/2009/09/12/3-ways-mysql-uses-indexes/

Uso del índice para leer datos Algunos motores de almacenamiento (MyISAM e Innodb incluidos) también pueden usar el índice para leer los datos, evitando así leer los datos de la fila en sí. Esto no es solo un ahorro de tener 2 lecturas por entrada de índice en lugar de una, sino que puede ahorrar órdenes de magnitud de IO en algunos casos. Los índices se ordenan (al menos en el límite de la página). la misma página, pero las filas en sí pueden dispersarse en muchas páginas que requieren potencialmente muchos IO. Además de eso, si solo necesitas acceder a un par de columnas, el índice puede ser simplemente mucho más pequeño que los datos, que es uno de los motivos por los que los índices ayudan a acelerar las consultas, incluso si los datos están en la memoria. Si MySQL solo lee el índice y no accede a las filas, verá "usar índice" en la salida EXPLAIN.

Luego en las fuentes de sql_select.cc: http://bazaar.launchpad.net/~mysql/mysql-server/5.1/view/head:/sql/sql_select.cc#L12834

/* We can remove binary fields and numerical fields except float, as float comparison isn''t 100 % secure We have to keep normal strings to be able to check for end spaces */ if (field->binary() && field->real_type() != MYSQL_TYPE_STRING && field->real_type() != MYSQL_TYPE_VARCHAR && (field->type() != MYSQL_TYPE_FLOAT || field->decimals() == 0)) { return !store_val_in_field(field, right_item, CHECK_FIELD_WARN); }

Entonces mis preguntas son

  1. ¿Es práctico almacenar en columnas de cadenas de índices, eso solo se necesita como datos? Por ejemplo, una tabla con 20 columnas, y a menudo necesitamos strcolumn, que se busca por intcolumn. ¿Es bueno crear un índice como (intcolumn, strcolumn) o realmente solo necesitamos (intcolumn) aquí?

  2. ¿Realmente mysql in innodb engine realiza algunas acciones adicionales para recuperar los datos (cuando vemos "Using where; Using index")?

  3. También sucede lo mismo con los ENUM. Sucede, porque Enum_field`s real_type devuelve MYSQL_TYPE_STRING. ¿Hace lo mismo para las enumeraciones?

  4. ¿Podemos entonces asumir, que las enumeraciones son súper malvadas y que siempre deberíamos usar simplemente una tabla de referencia simple?

  5. Para MyISAM es undertandable, ya que almacena en el índice no todos los valores. Pero entonces ¿por qué almacena dos valores, no uno?

  6. Si esto es todo lo que realmente sucede, ¿son solo las restricciones actuales del kernel mysql, que no dependen de la implementación del controlador concreto?

PD: Veo que esta pregunta es algo enorme. Si alguien ayudará a reformularlo / romperlo, será agradable.

Actualización1: agregar otro SQL sobre "Usar índice" frente a "Usar índice; usar dónde"

@see fiddle en http://sqlfiddle.com/#!8/3f287/2

DROP TABLE IF EXISTS tab; CREATE TABLE IF NOT EXISTS tab ( id INT NOT NULL AUTO_INCREMENT , num1 TINYINT NOT NULL , num2 TINYINT , str3 CHAR(1) NOT NULL , PRIMARY KEY (id) , INDEX num1_num2 (num1, num2) , INDEX num1_str3 (num1, str3) , INDEX num2_num1 (num2, num1) , INDEX str3_num1 (str3, num1) ) ENGINE=InnoDB; INSERT INTO tab (num1, num2, str3) VALUES (1, 1, ''1'') , (2, 2, ''2'') , (3, 3, ''3'') , (4, 4, ''4'') , (5, 5, ''5'') , (6, 6, ''6'') , (7, 7, ''7'') , (8, 8, ''8'') , (9, 9, ''9'') , (0, 0, ''0'') ; INSERT INTO tab (num1, num2, str3) SELECT num1, num2, str3 FROM tab; -- Using index EXPLAIN SELECT num2 FROM tab WHERE num1 = 5; EXPLAIN SELECT str3 FROM tab WHERE num1 = 5; -- Using where; Using index EXPLAIN SELECT num1 FROM tab WHERE num2 = 5; EXPLAIN SELECT num1 FROM tab WHERE str3 = ''5'';

Preguntas n. ° 2

  1. ¿Por qué en caso de búsqueda por null int vemos solo "Using index"?

  2. Pero en el caso de una cadena OR anulable, también vemos "Using where"?

  3. ¿Qué acciones adicionales hace allí?