Mysql Indices - Historial de revisiones

Wiki en 11:39 8 nov 2020

2020-11-08T11:39:34Z

Wiki: /* Crear índices */

2020-05-04T17:31:33Z

Crear índices

Wiki en 17:11 4 may 2020

2020-05-04T17:11:05Z

Wiki en 17:00 4 may 2020

2020-05-04T17:00:15Z

Wiki: /* Optimizando los índices */

2019-02-25T16:01:43Z

Optimizando los índices

Wiki: /* Optimizando los índices */

2019-02-25T16:01:07Z

Optimizando los índices

Wiki: /* Multicolumna */

2019-02-21T00:13:20Z

Multicolumna

Wiki: /* Ejercicios propuestos */

2019-02-18T19:18:32Z

Ejercicios propuestos

Wiki: /* Optimizando los índices */

2019-02-18T19:17:25Z

Optimizando los índices

Wiki: /* Optimizando los índices */

2019-02-18T19:14:25Z

Optimizando los índices

← Revisión anterior		Revisión del 11:39 8 nov 2020
Línea 367:		Línea 367:


		+	<br />
		+	'''[https://wiki.cifprodolfoucha.es/index.php?title=Mysql#UNIDAD_4:_Optimizaci.C3.B3n_del_rendimiento_y_monotorizaci.C3.B3n Enlace a la página principal de la UD4]'''
		+	<br />
		+	<br />
		+	'''[https://wiki.cifprodolfoucha.es/index.php?title=Mysql Enlace a la página principal del curso]'''
		+	<br />


	<br> -- [[Usuario:angelfg\|Ángel D. Fernández González]] -- (2017).		<br> -- [[Usuario:angelfg\|Ángel D. Fernández González]] -- (2017).

@@ Línea 39: / Línea 39: @@
 =Crear índices=
-* Podemos crear un índice de dos formas:
+* Podemos crear un índice de tres formas:
 :* Con la orden SQL [https://dev.mysql.com/doc/refman/5.5/en/create-index.html CREATE INDEX]
 :* Con la orden SQL [https://dev.mysql.com/doc/refman/8.0/en/alter-table.html ALTER TABLE] y la opción ADD INDEX.

@@ Línea 46: / Línea 46: @@
 * Veamos un ejemplo sobre una supuesta tabla Alumnos sobre la que queramos indexar su nombre:
-::<syntaxhighlight lang="java" line enclose="div" highlight="" >
+::<syntaxhighlight lang="SQL" enclose="div" highlight="" >
 CREATE INDEX idx_Alumnos_nombre ON Alumnos (nombre)
 </syntaxhighlight>
 : Siendo Alumnos el nombre de la tabla y nombre la columna a indexar.
-::<syntaxhighlight lang="java" line enclose="div" highlight="" >
+::<syntaxhighlight lang="SQL" enclose="div" highlight="" >
 ALTER TABLE Alumnos ADD INDEX idx_Alumnos_nombre (nombre)
 </syntaxhighlight>
@@ Línea 77: / Línea 77: @@
 * Un ejemplo:
-::<syntaxhighlight lang="java" line enclose="div" highlight="" >
+::<syntaxhighlight lang="SQL" enclose="div" highlight="" >
 ALTER TABLE Alumnos ALTER INDEX idx_Alumnos_nombre INVISBLE
 </syntaxhighlight>
@@ Línea 89: / Línea 89: @@
 * Podemos usar la orden SQL [https://dev.mysql.com/doc/refman/8.0/en/drop-index.html DROP INDEX].
 * Un ejemplo:
-::<syntaxhighlight lang="java" line enclose="div" highlight="" >
+::<syntaxhighlight lang="SQL" enclose="div" highlight="" >
 DROP INDEX idx_Alumnos_nombre ON Alumnos
 </syntaxhighlight>
@@ Línea 106: / Línea 106: @@
 * Para evitarlo podemos crear un índice parcial, es decir, un índice sobre una parte del tamaño de la columna.
-::<syntaxhighlight lang="java" line enclose="div" highlight="" >
+::<syntaxhighlight lang="SQL" enclose="div" highlight="" >
 CREATE INDEX idx_Alumnos_direccion ON Alumnos (direccion(100))
 </syntaxhighlight>
@@ Línea 125: / Línea 125: @@
 : Si quiero crear un índice por apellidos y dentro de apellidos que ordene por nombre, crearía un único índice con las dos columnas, de la forma:
-::<syntaxhighlight lang="java" line enclose="div" highlight="" >
+::<syntaxhighlight lang="SQL" enclose="div" highlight="" >
 CREATE INDEX idx_Alumnos_nombre_apellidos ON Alumnos (apellidos(100),nombre)
 </syntaxhighlight>
@@ Línea 142: / Línea 142: @@
 : Al crearlo, Mysql ordena físicamente los registros para que coincidan con el índice, de tal forma que índice y registro están 'unidos'. El registro se guarda junto a su clave primaria.
 : Así, si tengo creado como índice de clave primaria una columna id, físicamente los registros estarán:
-::<syntaxhighlight lang="java" enclose="div" highlight="" >
+::<syntaxhighlight lang="SQL" enclose="div" highlight="" >
 ID-------Nombre
 --------Angel
@@ Línea 155: / Línea 155: @@
 * En una tabla, como vimos, podemos crear otros índices. Cuando usamos INNODB como motor de almacenamiento, éstos guardan el valor de la clave primaria para buscar el registro, a parte del valor del índice creado. Este tipo de índices se denominan '''índices secundarios''' o '''no clusterizados'''.
 : En el ejemplo anterior, si creamos un índice sobre la columna Nombre tendremos:
-::<syntaxhighlight lang="java" enclose="div" highlight="" >
+::<syntaxhighlight lang="SQL" enclose="div" highlight="" >
 Nombre-------ID
 Ana----------4
@@ Línea 225: / Línea 225: @@
 * Veamos un ejemplo:
-::<syntaxhighlight lang="java" line enclose="div" highlight="" >
+::<syntaxhighlight lang="SQL" enclose="div" highlight="" >
 SHOW INDEX FROM employees.departments;
 </syntaxhighlight>
@@ Línea 285: / Línea 285: @@
 * Veamos un ejemplo:
-::<syntaxhighlight lang="java" line enclose="div" highlight="" >
+::<syntaxhighlight lang="SQL" enclose="div" highlight="" >
 OPTIMIZE TABLE ALUMNOS;
 </syntaxhighlight>
-::<syntaxhighlight lang="java" line enclose="div" highlight="" >
+::<syntaxhighlight lang="SQL" enclose="div" highlight="" >
 ANALYZE TABLE ALUMNOS;
 </syntaxhighlight>
@@ Línea 302: / Línea 302: @@
 * Dada la siguiente consulta SQL:
-::<syntaxhighlight lang="java" line enclose="div" highlight="" >
+::<syntaxhighlight lang="SQL" enclose="div" highlight="" >
 select *
 from employees
@@ Línea 312: / Línea 312: @@
 * Dada la siguiente consulta SQL:
-::<syntaxhighlight lang="java" line enclose="div" highlight="" >
+::<syntaxhighlight lang="SQL" enclose="div" highlight="" >
 select *
 from salaries
@@ Línea 325: / Línea 325: @@
 : Para borrar puedes utilizar esta orden SQL: DELETE FROM employees.salaries WHERE YEAR(from_date)<1995
 : Comprueba el tamaño de la tabla:
-::<syntaxhighlight lang="java" line enclose="div" highlight="" >
+::<syntaxhighlight lang="SQL" enclose="div" highlight="" >
-     SELECT table_name AS `Table`,
+    SELECT table_name AS `Table`,
-    round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
+           round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
      FROM information_schema.TABLES
      WHERE table_schema = "$DB_NAME" AND table_name = "$TABLE_NAME";
@@ Línea 343: / Línea 343: @@
 * Crea en SQL una tabla en memoria que sea una copia de la tabla employees (botón derecho sobre la tabla, obtener la orden create y modificar el motor de almacenamiento):
-::<syntaxhighlight lang="java" line enclose="div" highlight="11" >
+::<syntaxhighlight lang="SQL" enclose="div" highlight="11" >
 CREATE TABLE `employees_m` (
    `emp_no` int(11) NOT NULL,

@@ Línea 15: / Línea 15: @@
 * Como en todo, el uso de registros lleva aparejado ciertos problemas (sino fuera así podríamos crear índices para todas las columnas de una tabla):
 :* Es una estructura de datos que ocupa espacio a mayores del espacio de la propia tabla.
-:* En caso de operaciones de insercción, borrado o modificación el índice se debe rehacer con la consiguiente carga de trabajo para el gestor.
+:* En caso de operaciones de inserción, borrado o modificación el índice se debe rehacer con la consiguiente carga de trabajo para el gestor.

@@ Línea 257: / Línea 257: @@
 : Además, los índices generan ciertas estadísticas que son utilizadas por el optimizador de consultas para determinar cual es la mejor forma de obtener los resultados de una consulta.
-* Por estas dos razones, <u>'''debemos de utilizar uno de estos dos comandos:
+* Por estas dos razones, '''debemos de utilizar uno de estos dos comandos:
-:*  El comando [https://dev.mysql.com/doc/refman/8.0/en/optimize-table.html OPTIMIZE TABLE]'''</u> cuando trabajamos con tablas que han sufrido muchas operaciones modificando las columnas asociadas a sus índices (borrados, actualizaciones, inserciones).
+:*  El comando <u>[https://dev.mysql.com/doc/refman/8.0/en/optimize-table.html OPTIMIZE TABLE]'''</u> cuando trabajamos con tablas que han sufrido muchas operaciones modificando las columnas asociadas a sus índices (borrados, actualizaciones, inserciones).
 :: Este comando va a desfragmentar la tabla y a actualizar y reordenar sus índices. Sólo se puede aplicar sobre motores INNODB, MYISAM y Archive.
 :: Tendrá el efecto de recuperar el espacio de disco que una tabla ya no usa (después de hacer un borrado de muchas filas).
 :: Recrear los índices y volver a generar la información necesaria para que el optimizador de consultas determine cuando hacer uso del mismo.
-:* El comando [https://dev.mysql.com/doc/refman/5.7/en/analyze-table.html ANALYZE TABLE] '''<u>cuando trabajamos con tablas que han sufrido muchas operaciones  modificando las columnas asociadas a sus índices (borrados, actualizaciones, inserciones).
+:* El comando <u>[https://dev.mysql.com/doc/refman/5.7/en/analyze-table.html ANALYZE TABLE] '''</u>cuando trabajamos con tablas que han sufrido muchas operaciones  modificando las columnas asociadas a sus índices (borrados, actualizaciones, inserciones).
 :: A diferencia del anterior, este comando sólo recrea los índices de la tabla pero no recupera el espacio de disco que ya no utilice.

@@ Línea 132: / Línea 132: @@
-* Si se realizan consultas en los que se busca por varios campos, se debe de crear un [http://wiki.cifprodolfoucha.es/index.php?title=Mysql_Indices#Multicolumna índice multicolumna] mejor que varios [http://wiki.cifprodolfoucha.es/index.php?title=Mysql_Indices#Parciales índices parciales] La primera parte del índice debería ser siempre la columna más usada. En el caso de usar siempre varias columnas es mejor que la primera columna sea la que tenga más duplicados.
+* Si se realizan consultas en los que se busca por varios campos, se debe de crear un [http://wiki.cifprodolfoucha.es/index.php?title=Mysql_Indices#Multicolumna índice multicolumna] mejor que varios [http://wiki.cifprodolfoucha.es/index.php?title=Mysql_Indices#Parciales índices parciales]
+: Veremos en el [https://wiki.cifprodolfoucha.es/index.php?title=Mysql_Optimizaci%C3%B3n_en_el_dise%C3%B1o_de_BD#Caso_2:_Parte_Where_con_una_varias_condiciones_de_tipo_AND siguiente punto de la Wiki cual es el mejor orden]
 <br />

@@ Línea 314: / Línea 314: @@
 : Crea el índice adecuado y comprueba cual es el tiempo ahora.
 : Muestra los índices creados en la tabla.
-: Borra 300.000 filas (haz una [https://dev.mysql.com/doc/refman/8.0/en/select-into.html copia antes de los datos de la tabla]. Vas a tener que poner la variable secure-file-priv="" dentro del archivo de configuración y reiniciar el servidor) y muestra la información sobre los índices asociados a la tabla. ¿ Qué diferencias encuentras?
+: Borra 1.000.000 filas (haz una [https://dev.mysql.com/doc/refman/8.0/en/select-into.html copia antes de los datos de la tabla]. Vas a tener que poner la variable secure-file-priv="" dentro del archivo de configuración y reiniciar el servidor) y muestra la información sobre los índices asociados a la tabla. ¿ Qué diferencias encuentras?
-: Para borrar puedes utilizar esta orden SQL: DELETE FROM employees.salaries WHERE YEAR(from_date)<1990
+: Para borrar puedes utilizar esta orden SQL: DELETE FROM employees.salaries WHERE YEAR(from_date)<1995
 : Comprueba el tamaño de la tabla:
 ::<syntaxhighlight lang="java" line enclose="div" highlight="" >

@@ Línea 265: / Línea 265: @@
 : La reducción de espacio sólo funcionará si la [https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_file_per_table variable del sistema innodb_file_per_table] está a ON (valor por defecto).
-:<u>Nota:</u> Cuando se ejecuta este comando con el motor InnoDB saldrá un aviso con el siguiente texto: Table does not support optimize, doing recreate + analyze instead.
+:<u>Nota:</u>
 :: No hacer caso al mismo ya que lo que indica es que el motor InnoDB no va a hacer lo que se hacía con el motor MyISAM para optimizar la tabla.