Optimizar las consultas de MySQL

Optimizar las consultas de MySQL

Optimizar las consultas de MySQL : Multi-millones de consejos para hacer su vida mas facil.

Si la consulta esta tomando mucho tiempo, y conexiones de base de datos y los retrasos estan creciendo, entonces es el momento para optimizar su base de datos. En MySQL, puede utilizar mysqlreport comando para generar un informe sobre utilizado suficientemente base de datos y establecer una linea de base para futuras acciones. Tambien puede ser necesario modificar el archivo de configuracion (mi.cnf) para el registro de consultas lentas. Identificar la lenta ejecucion de consultas y de las posibles causas. Generalmente, las causas estan relacionadas con los indices como ningun indice o inadecuada indice. En este articulo se ofrece una descripcion detallada de los comandos y conceptos que podemos utilizar para optimizar MySQL.

Paso 1

el Uso de indices

el Uso de indices que hace la consulta rapida de la misma manera como un indice en un libro de ayuda en la busqueda de una palabra en particular.

Para saber como y que indices se utilizan en una determinada consulta, uso EXPLICAR comando como un prefijo para consulta de seleccion.

explain SELECT emp_id DE ORGN_DATA DONDE dept_code = 'ES'

Este comando devuelve la siguiente informacion:

Tabla - tabla(s) que se utilizan en la consulta
Tipo - tipo de combinacion. Los valores pueden ser el sistema, const, eq_ref, ref, rango, indice, todos los
las Claves Posibles - Todas las teclas que se pueden utilizar para los indices
Clave - la clave de La realidad utilizada para el indice
Longitud de la Clave - claves mas Cortas son mejores
Ref - Otras Columnas que se usan con la clave para obtener los resultados
Filas - Numero de filas de donde vendran los datos
Extra - Extra de informacion. Algunos de los posibles valores pueden ser el uso de indice, usando donde, utilizando temporal, el uso de filesort

Usted debe reconsiderar la estructura de tus tablas, si es Posible Teclas de contener un valor NULL.
UN valor NULL en el campo de la Clave indica que no hay ningun indice que se utiliza. El no uso de los indices de disuadir el rendimiento si hay demasiados registros en la tabla, porque, para cualquier consulta, todos los registros se buscara la condicion dada.

el Uso de ALTERAR el comando agregar indice como se indica a continuacion:

ALTER TABLE nombre_tabla ADD INDEX (column_name)

Usted puede hacer indexacion en varias columnas con:

ALTER TABLE nombre_tabla ADD INDEX (column_name1,..,column_nameN)

MySQL utiliza el de la izquierda prefijo, por lo que automaticamente los indices agregados para column_name1, (column_name1,column_name2),...(column_name1,..,column_NameN-1). Esto es muy util en situaciones como la busqueda basada en el apellido para un indice de (apellido, nombre, middlename), se agrega automaticamente varios indices utilizados en el mas rapido de la consulta.

Puntos para recordar:

• Seleccione la columna(s) para la indexacion muy cuidadosamente.

• los campos Indexados debe ser utilizado para la busqueda y consulta debe ser re-enmarcado si cualquier calculo que se realiza en los campos indexados.

• Indice toma el espacio de almacenamiento.

• Cada operacion en la base de datos se requiere actualizar el indice.

• Una menor Longitud de la Clave reportado por EXPLICAR comando es mejor. Menor longitud de la clave significa menor espacio de almacenamiento requerido por el archivo de indice y tambien que el tiempo para buscar en el indice es menor. Para ello, los indices se puede configurar en la parte de las columnas. Por ejemplo, el INDICE(col1(10),col2(20)). De nuevo, usted tiene que decidir con prudencia lo que la longitud de las columnas a ser utilizado para la indizacion.

Paso 2

Uso del comando ANALIZAR

ANALIZAR el Uso de comandos para generar la distribucion de la clave de la tabla a ser usado por MySQL optimizador de consultas para decidir que los indices son mejor para usar en una consulta.

ANALYZE TABLE nombre_tabla

Paso 3

Uso de OPTIMIZAR el comando

Si hay demasiados inserciones/elimina en una tabla, OPTIMIZAR el comando debe ser usada con frecuencia para optimizar el disco el movimiento de la cabeza durante la recuperacion. Esto es particularmente util si la variable de tamaño de las columnas se utilizan como varchar, text, blob, etc. OPTIMIZAR el comando de la desfragmentacion de la fragmentacion causada por campos de longitud variable como VARCHAR.

OPTIMIZE TABLE nombre_tabla

Paso 4

Uso de funciones especiales para la carga de datos

Para la carga de datos desde un archivo en una tabla de base de datos, utilizando LOAD DATA INFILE es mucho mas rapido que la lectura de datos desde un archivo de forma iterativa y utilizando el comando INSERTAR. La sintaxis de este comando es la siguiente:

LOAD DATA INFILE 'filedata.dat ' EN la TABLA nombre_tabla (col1,col2,...,colM) CAMPOS TERMINADO POR '|'')

Paso 5

Configuracion de PRIORIDAD de comandos

Si no hay mas consultas de las inserciones de datos, usted puede bajar la prioridad de INSERTAR un comando usando:

INSERTAR LOW_PRIORITY o SELECCIONE HIGH_PRIORITY

Si el cliente no esta interesado en los resultados de INSERCION, entonces puede ser inmediatamente liberado usando el siguiente comando:

INSERTAR RETRASADO.

Esto hace que el sistema mas rapido por lotes inserciones.

Paso 6

ELIMINAR vs TRUNCAR

ELIMINAR elimina la fila por fila, mientras que TRUNCAR elimina todas las filas a la vez. Por lo tanto, si usted no esta interesado en el numero de filas eliminadas de una tabla como resultado de comando ELIMINAR, a continuacion, utilizar TRUNCATE con la siguiente sintaxis:

TRUNCATE TABLE nombre_tabla

Paso 7

la Reduccion de comprobacion de permisos sobrecarga

mas Simple de los permisos de reducir la comprobacion del permiso de sobrecarga. Uso del comando GRANT para establecer los permisos. La sintaxis General para la CONCESION es:

BECA Oper1,...,abre una carpeta EN db_name.tb_name A nombre_usuario@computer_name IDENTIFICADOS POR contraseña

Por ejemplo:

GRANT SELECT,UPDATE EN EMP_DET.SALARY_DET PARA feroz@localhost identified BY 'abc123'

Si la cuenta de usuario 'feroz' no existe, entonces se creara en localhost (aunque el usuario puede acceder a los datos desde cualquier ordenador) y la contraseña 'abc123' sera asignado a el.

Paso 8

Uso BENCHMARK() la funcion

Para saber cuanto de un determinado MySQL funcion o expresion que esta tomando tiempo, el uso interno de MySQL en funcion de:

REFERENCIA(loop_count, expresion)

siempre devuelve 0, pero tambien se imprime el tiempo tomado por la expresion.

Paso 9

Synchrozing tipos de datos

Si las columnas en las tablas contienen informacion identica, a continuacion, hacer sus tipos de datos del mismo modo que se pueden realizar mas rapidamente.

Paso 10

Varios

• Insertar varias filas con una sola instruccion SQL.

• Eliminar innecesarios paranthesis en la clausula where.

• Si SQL_SMALL_RESULT se utiliza la opcion, a continuacion, utiliza MySQL temporal en la memoria las tablas que proporcionan un acceso mas rapido.

optimizacion de la Consulta no es un trabajo a tiempo. Si su base de datos es muy utilizado, a continuacion, puede que tenga que repetir el proceso de optimizacion de cada 5-6 meses.