Tabla de
contenidos
SELECT y otras consultas [+/-]
La
optimización es una tarea compleja, porque requiere un conocimiento de todo el
sistema a optimizar. Se podría optimizar sólo algunos aspectos teniendo poco
conocimiento del sistema o aplicación, pero cuanto más óptimo se quiera el
sistema, más se tiene que conocer acerca del mismo.
Este
capítulo intenta explicar y da algunos ejemplos de las diferentes maneras de
optimizar MySQL. Sin embargo se debe recordar que siempre existen vías de hacer
todavía más rápido el sistema, aunque pudieran requerir un notable incremento
de esfuerzos.
Al
utilizar el motor de almacenamiento MyISAM, MySQL usa un bloqueo (lock)
extremadamente rápido de tablas, que permite múltiples lecturas o una sola
escritura. El mayor problema con este motor de almacenamiento ocurre cuando se
tiene un flujo constante de actualizaciones y selecciones lentas de una sola
tabla. Si éste es el problema para algunas tablas, puede usar otro motor de
almacenamiento para ellas. Ver Capítulo 14, Motores de almacenamiento de MySQL y
tipos de tablas.
MySQL
puede trabajar con tablas transaccionales y no transaccionales. Para hacer el
trabajo más facil con tablas no transaccionales (donde no se puede deshacer una
transacción si algo va mal), MySQL tiene las siguientes reglas. Obsérvese que
estas reglas sólo se aplican cuando no se está corriendo en modo SQL
estricto o si se usa el especificador IGNORE para un INSERT o un UPDATE.
- Todas
las columnas tienen valores por defecto. Obsérvese que cuando se ejecuta
en modo SQL estricto (incluyendo modo SQL TRADITIONAL), se debe especificar cualquier
valor para una columna NOT NULL.
- Si se inserta un valor
inapropiado o fuera de rango dentro de una columna, MySQL atribuye a la
columna el “ mejor valor posible ” en vez de reportar un error. Para
valores numéricos, éste es el 0, el valor más pequeño posible o el valor
más grande posible. Para cadenas de texto, puede ser una cadena vacía o el
trozo de la cadena más grande que quepa en la columna. Este comportamiento
no se aplica cuando se ejecuta en modo SQL estricto TRADITIONAL.
- Todas las expresiones
calculadas retornan un valor que puede ser usado en vez de señalar una
condición de error. Por ejemplo, 1/0 devuelve NULL. (Este comportamiento puede
ser cambiado usando el modo SQL ERROR_FOR_DIVISION_BY_ZERO).
Si se usa
una tabla no transaccional, no debería usar MySQL para comprobar el contenido
de la columna. En general, la manera más segura (y generalmente más rápida) es
usar la aplicacion para asegurarse que se están pasando sólo valores legales a
la base de datos.
Los índices se utilizan para buscar las
filas con valores de columna específicos rápidamente. Sin un índice, MySQL debe comenzar con el primer disco y luego leer a
través de toda la tabla para buscar las filas correspondientes. Cuanto mayor sea la tabla, más esto cuesta. Si la tabla tiene un índice para las columnas en cuestión, MySQL puede
determinar rápidamente la posición de buscar en el medio del archivo de datos
sin tener que mirar a todos los datos. Si una tabla tiene
1000 filas, entonces este es al menos 100 veces más rápido que la lectura
secuencial. Tenga en cuenta que si usted necesita
para acceder a la mayoría de las filas, es más rápido para leer de forma
secuencial, ya que esto minimiza el disco busca.
La mayoría de los índices de MySQL ( PRIMARY KEY , UNIQUE , INDICE y FULLTEXT ) se almacenan en
árboles b.Las excepciones son que los índices de tipos de columna espaciales
utilizan R-árboles, y que los MEMORIA tablas también soporta índices hash.
Las cadenas son automáticamente prefijo
y finales espacio comprimido. Consulte Sección 13.1.4, "Sintaxis deCREATE
INDEX " .
En general, los índices se utilizan
como se describe en la siguiente discusión. Características
específicas de índices hash (como los utilizados en MEMORIA tablas) se describen
al final de esta sección.
Los índices se utilizan para estas
operaciones:
·
Para buscar las filas que coincidan con una DONDE cláusula rápidamente.
·
Para eliminar filas de consideración. Si hay una elección
entre varios índices, MySQL normalmente utiliza el índice que encuentra el
menor número de filas.
·
Para recuperar filas de otras tablas al realizar combinaciones.
·
Para encontrar el MIN () o MAX () valor para una columna indexada específico key_col . Esto se optimiza mediante un preprocesador
que comprueba si se está utilizando DONDE key_part_ # = constante en todas las partes fundamentales que se producen antes key_col en el índice. En este caso, MySQL hace una búsqueda
de claves para cada MIN () y MAX () expresión y sustituirla por una constante. Si todas las expresiones se sustituyen con constantes, la consulta
devuelve a la vez. Por ejemplo:
·
SELECT MIN ( key_part2 ), MAX ( key_part2 )
·
DE tbl_name DONDE key_part1 = 10;
·
Para ordenar o agrupar una tabla si la clasificación o agrupación se
realiza en un prefijo a la izquierda de una clave útil (por ejemplo, ORDER BY key_part1 , key_part2 ). Si todas las partes fundamentales son
seguidos por DESC , la clave se leen en orden inverso. Consulte Sección 7.2.10, "Como optimización de MySQL ORDER BY ".
·
En algunos casos, una consulta puede ser optimizado para recuperar
valores sin consultar a las filas de datos. Si una consulta
utiliza sólo las columnas de una tabla que es numérica y que forman un prefijo
a la izquierda por alguna clave, los valores seleccionados se pueden recuperar
desde el árbol de índice para una mayor velocidad:
·
SELECT key_part3 DE tbl_name
DONDE key_part1 = 1
La siguiente lista indica algunas de
las formas en que el mysqld servidor utiliza la
memoria. En su caso, se le da el nombre de la
variable de sistema relevantes para el uso de la memoria:
·
El key buffer (variable key_buffer_size ) es compartida por todos los temas, otros buffers usados por el
servidor se asignan según sea necesario. Consulte Sección 7.5.2, "afinar Lista de parámetros del
Servidor" .
·
Cada conexión utiliza algo de espacio para subprocesos específicos:
§ Una pila (por defecto
64KB, variables thread_stack )
§ Un tampón de conexión
(variable net_buffer_length )
§ Un buffer resultado
(variable net_buffer_length )
El buffer de conexión y búfer resultado se amplían de forma dinámica
hasta max_allowed_packet cuando sea necesario. Mientras se ejecuta una consulta,
también se le asigna una copia de la cadena de consulta actual.
·
Todos los hilos comparten la misma memoria base.
·
Sólo comprimidos MyISAM tablas son la memoria asignada. Esto es debido a que
el espacio de memoria de 32 bits de 4 GB no es lo suficientemente grande para
la mayoría de grandes tablas. En equipos con un espacio de
direcciones de 64 bits se vuelven más comunes, podemos añadir un apoyo general
para la asignación de memoria.
·
Cada solicitud que realiza un análisis secuencial de la tabla asigna un búfer de lectura (variableread_buffer_size ).
·
Cuando la lectura de filas en una secuencia arbitraria (por ejemplo, a
raíz de una especie), una memoria intermedia de
lectura aleatoria (variable read_rnd_buffer_size ) puede ser asignado con el fin de
evitar búsquedas en disco.
·
Todo se une se ejecutan en un solo paso, y la mayoría se une se puede
hacer sin necesidad de utilizar una tabla temporal. La mayoría son tablas temporales basados en memoria ( HEAP tablas). Las tablas temporales con una gran longitud de registro (calculado como
la suma de todas las longitudes de columna) o que contengan BLOBcolumnas se almacenan en el disco.
Si una tabla de montón interna supera el tamaño de tmp_table_size , MySQL 5.0 se
encarga de esto automáticamente al cambiar la tabla de montón en memoria a un
disco basado en MyISAM mesa si es necesario.También puede aumentar el tamaño de la tabla
temporal mediante el establecimiento de la tmp_table_sizeopción de mysqld o estableciendo la
opción SQL SQL_BIG_TABLES en el programa cliente. Consulte Sección 13.5.3, "Sintaxis de SET " .
·
La mayoría de las peticiones que realizan una especie asignar un buffer
de ordenación y de cero a dos archivos temporales, dependiendo del tamaño del
conjunto de resultados. Consulte Sección A.4.4, "Dónde
almacena MySQL los Archivos Temporales" .
·
Casi todos los análisis y cálculo se realiza en una tienda de la memoria
local. No se necesita ninguna sobrecarga de la
memoria para objetos pequeños, por lo que se evita la asignación de memoria
normal y lenta liberación. La memoria se asigna sólo a las grandes
cadenas de forma inesperada, lo que se hace con malloc () y free () .
·
Para cada MyISAM tabla que se abre, el archivo índice se abrió una vez, el archivo de
datos se abre una vez por cada hilo de ejecución simultánea. Para cada hilo concurrente, una estructura de tabla, las estructuras de
columna para cada columna, y un buffer de tamaño 3 * N se asignan (donde N es la longitud máxima de fila, sin
contar BLOB columnas). Un BLOB columna requiere siete y cincuenta y
cinco bytes más la longitud de los BLOB de datos. El MyISAM motor de almacenamiento mantiene un búfer fila adicional para su uso
interno.
·
Para cada tabla que tiene BLOB columnas, un tampón se amplía
dinámicamente a leer en grandes BLOB valores.Si escanea una mesa, un búfer tan grande como el mayor BLOB se asigna valor.
·
Estructuras de controlador de todas las tablas en uso se guardan en la
memoria caché y administran como una FIFO. De forma
predeterminada, la memoria caché tiene 64 entradas. Si una tabla se ha utilizado por dos subprocesos que se ejecutan al
mismo tiempo, la memoria caché contiene dos entradas para la tabla. ConsulteSección 7.4.8, "como abre y cierra tablas
MySQL" .
·
A FLUSH TABLES declaración o mysqladmin flush-tables comando cierra todas
las tablas que no están en uso a la vez y marca todas las tablas en uso a ser
cerradas cuando el hilo se está ejecutando actualmente acabados.Esto libera más
eficaz en el uso de memoria. FLUSH TABLES no vuelve hasta que todas las mesas se han cerrado.
ps y otros programas de estado del sistema
puede informar de que mysqld utiliza una gran
cantidad de memoria.Esto puede ser causado por las pilas de procesos en
diferentes direcciones de memoria. Por ejemplo, la
versión de Solaris ps cuenta la memoria no
utilizada entre las pilas de la memoria utilizada. Usted puede verificar esto comprobando intercambio disponible con swap-s . Probamos mysqld con
varios detectores de fugas de memoria (tanto comerciales y de código abierto),
por lo que no debe haber fugas de memoria.
No hay comentarios:
Publicar un comentario