Mysql avanzado (segunda edición)

Esta obra le enseña a crear rápidamente sistemas fiables con MySQL. Escrito por expertos con una amplia experiencia práctica en la creación de grandes sistemas, esta edición analiza el rendimiento de MySQL con detalle y se centra en la solidez, la seguridad y la integridad de los datos.

Aprenderá las técnicas avanzadas para que pueda aprovechar todo el potencial de MySQL, a diseñar esquemas, índices, consultas y opciones de MySQL avanzadas para obtener un máximo rendimiento. Además, tendrá una guía detallada para el ajuste de su servidor MySQL, sus sistemas operativos y su hardware para utilizar su máximo potencial. También se incluyen métodos prácticos, seguros y de alto rendimiento para escalar aplicaciones con el replicado, los balances de carga, la alta disponibilidad y los procedimientos contra fallos.

Prólogo
Introducción
Organización de este libro
Una amplia visión general
Conseguir unos fundamentos sólidos
Ajustar la aplicación
Escalar hacia arriba tras realizar cambios
Conseguir una aplicación fiable
Diversos temas útiles
Versiones y disponibilidad de software
Convenciones
Recursos útiles para el lector

Capítulo 1. Arquitectura de MySQL
Arquitectura lógica de MySQL
Administración y seguridad de la conexión
Optimización y ejecución
Control de concurrencia
Bloqueos de lectura y escritura
Granularidad del bloqueo
Bloqueos de tablas
Bloqueos de fila
Transacciones
Niveles de aislamiento
Interbloqueos
Registro de transacciones
Transacciones en MySQL
AUTOCOMMIT
Combinar motores de almacenamiento en las transacciones
Bloqueos implícitos y explícitos
Control de concurrencia de múltiples versiones
Motores de almacenamiento de MySQL
Motor MyISAM
Almacenamiento
Características de MyISAM
Tablas MyISAM comprimidas
Motor Merge de MyISAM
Motor InnoDB
Motor Memory
Motor Archive
Motor CSV
Motor Federated
Motor Blackhole
Motor NDB Cluster
Motor Falcon
Motor solidDB
Motor PBXT (Primebase XT)
Motor de almacenamiento Maria
Otros motores de almacenamiento
Seleccionar el motor apropiado
Consideraciones
Ejemplos prácticos
Registro
Tablas de sólo lectura o principalmente de sólo lectura
Procesamiento de orden
Cotizaciones
Boletines de anuncios y foros de discusiones anidadas
Aplicaciones CD-ROM
Resumen de los motores de almacenamiento
Conversiones de tablas
ALTER TABLE
Dump e import
CREATE y SELECT

Capítulo 2. Buscar cuellos de botella: medida de rendimiento y perfilado
¿Por qué la medida de rendimiento?
Estrategias de medidas de rendimiento
Qué medir
Tácticas de medidas del rendimiento
Diseño y planificación de una medida de rendimiento
Obtener resultados precisos
Ejecutar las pruebas de comparación y analizar los resultados
Herramientas de medida del rendimiento
Herramientas de pila completa
Herramientas de un solo componente
Ejemplos de medidas de rendimiento
http_load
sysbench
Prueba de comparación sysbench de la CPU
Prueba de comparación de E/S de archivo de sysbench
Prueba de comparación OLTP de sysbench
Otros sistemas sysbench
dbt2 TPC-C en Database Test Suite
MySQL Benchmark Suite
Perfilado
Perfilar una aplicación
Cómo y qué medir
Ejemplo de perfilado PHP
Registro de consultas
Control más detallado del registro
Cómo leer el registro de consultas lentas
Herramientas de análisis de registro
Perfilar un servidor MySQL
Perfilar consultas con SHOW STATUS
SHOW PROFILE
Otros métodos de perfilado de MySQL
Cuando no se puede añadir un código de perfilado
Perfilado del sistema operativo
Solución de problemas en conexiones y procesos MySQL
Perfilado y solución de problemas avanzados

Capítulo 3. Optimización del esquema e indizado
Elegir los tipos de datos óptimos
Números enteros
Números reales
Tipos de cadenas
Tipos VARCHAR y CHAR
Tipos BLOB y TEXT
Utilizar ENUM en lugar de un tipo de cadena
Tipos de fecha y hora
Tipos de datos empaquetados en bits
Elegir identificadores
Tipos de datos especiales
Elementos básicos del indizado
Tipos de índices
Índices de árbol B
Índices hash
Índices espaciales (árbol R)
Índices de texto completo
Estrategias de indizado para un alto rendimiento
Aislar la columna
Índices de prefijos y selectividad del índice
Índices agrupados
Comparación de diseños de datos InnoDB y MyISAM
Insertar filas en un orden de clave primaria con InnoDB
Índices de cobertura
Utilizar escaneados de índice para ordenar
Índices empaquetados (comprimidos por prefijo)
Índices redundantes y duplicados
Índices y bloqueos
Un caso de estudio de índices
Admitir muchos tipos de filtrado
Evitar condiciones de múltiples rangos
Optimizar las ordenaciones
Mantenimiento de índices y de tablas
Buscar y reparar daños en la tabla
Actualizar estadísticas de índices
Reducir el índice y la fragmentación de datos
Normalización y desnormalización
Ventajas e inconvenientes del esquema normalizado
Ventajas e inconvenientes del esquema sin normalizar
Una combinación de esquemas normalizados y sin normalizar
Tablas de caché y de resumen
Tablas de contadores
Incrementar la velocidad de ALTER TABLE
Modificar sólo el archivo .frm
Crear índices MyISAM rápidamente
Notas sobre los motores de almacenamiento
Motor de almacenamiento MyISAM
Motor de almacenamiento Memory
Motor de almacenamiento InnoDB

Capítulo 4. Optimización del rendimiento de las consultas
Elementos básicos de una consulta lenta: optimización del acceso a datos
¿Estamos pidiendo a la base de datos más datos de los necesarios?
¿Está MySQL examinando demasiados datos?
Tiempo de ejecución
Filas examinadas y filas devueltas
Filas examinadas y tipos de acceso
Métodos de restructuración de consultas
Consultas complejas frente a muchas consultas
Fragmentar una consulta
Descomposición de combinaciones
Elementos básicos de la ejecución de consultas
Protocolo cliente/servidor de MySQL
Estados de consulta
La caché de consultas
Proceso de optimización de consultas
El analizador sintáctico y el preprocesador
Optimizador de consultas
Estadísticas de tablas e índices
Estrategia de ejecución de combinaciones en MySQL
Plan de ejecución
Optimizador de combinaciones
Optimizaciones de ordenación
Motor de ejecución de consultas
Devolver resultados al cliente
Limitaciones del optimizador de consultas de MySQL
Subconsultas correlacionadas
Cuándo saber que una subconsulta es buena
Limitaciones de UNION
Optimizaciones de combinación de índices
Propagación de la igualdad
Ejecución en paralelo
Combinaciones hash
Escaneos de índice sueltos
MIN( ) y MAX( )
SELECT y UPDATE en la misma tabla
Optimizar tipos específicos de consultas
Optimizar consultas COUNT( )
Qué hace COUNT( )
Mitos sobre MyISAM
Optimizaciones simples
Optimizaciones más complejas
Optimizar consultas JOIN
Optimización de subconsultas
Optimizar GROUP BY y DISTINCT
Optimizar GROUP BY WITH ROLLUP
Optimizar LIMIT y OFFSET
Optimizar SQL_CALC_FOUND_ROWS
Optimizar UNION
Sugerencias del optimizador de consultas
Variables definidas por el usuario
Cuidado con las actualizaciones de MySQL

Capítulo 5. Opciones avanzadas de MySQL
La caché de consultas de MySQL
Cómo comprueba MySQL si existe un acierto en caché
Cómo utiliza la caché la memoria
Cuándo es útil la caché de consultas
Cómo ajustar y mantener la caché de consultas
Reducir la fragmentación
Mejorar el uso de la caché de consultas
InnoDB y la caché de consultas
Optimizaciones generales de la caché de consultas
Alternativas a la caché de consultas
Guardar código dentro de MySQL
Procedimientos y funciones almacenadas
Activadores
Eventos
Preservar comentarios en un código almacenado
Cursores
Instrucciones preparadas
Optimización de una instrucción preparada
Interfaz SQL para instrucciones preparadas
Limitaciones de las instrucciones preparadas
Funciones definidas por el usuario
Vistas
Vistas actualizables
Implicaciones de las vistas en el rendimiento
Limitaciones de las vistas
Conjuntos de caracteres y compaginaciones
Cómo utiliza MySQL los conjuntos de caracteres
Valores predeterminados para la creación de objetos
Valores de configuración para la comunicación entre el cliente y el servidor
Cómo compara MySQL los valores
Comportamientos especiales
Elegir un conjunto de caracteres y una compaginación
Cómo afectan a las consultas los conjuntos de caracteres y las compaginaciones
Búsqueda de texto completo
Búsquedas de texto completo en lenguaje natural
Búsquedas booleanas de texto completo
Cambios de texto completo en MySQL 5.1 y algo más
Problemas y soluciones del texto completo
Ajuste y optimización de texto completo
Limitaciones de las claves externas
Tablas combinadas y particionadas
Tablas combinadas
Impactos en el rendimiento de la tabla combinada
Ventajas de las tablas combinadas
Tablas particionadas
Por qué funcionan las particiones
Ejemplos de particiones
Limitaciones de las tablas particionadas
Optimización de consultas frente a tablas particionadas
Transacciones (XA) distribuidas
Transacciones XA internas
Transacciones XA externas

Capítulo 6. Optimizar las configuraciones del servidor
Elementos básicos de configuración
Sintaxis, ámbito y dinamismo
Consecuencias secundarias del establecimiento de variables
Inicio
Ajuste general
Ajustar el uso de memoria
¿Cuánta memoria puede utilizar MySQL?
Necesidades de memoria por conexión
Reservar memoria para el sistema operativo
Asignación de memoria para cachés
La caché de claves MyISAM
Tamaño del bloque de claves MyISAM
El conjunto de búferes de InnoDB
La caché de subprocesos
La caché de tablas
Diccionario de datos InnoDB
Ajustar el comportamiento E/S de MySQL
Ajuste E/S de MyISAM
Ajuste E/S de InnoDB
Registro de transacción de InnoDB
Cómo abre y vuelca InnoDB los archivos de registro y de datos
Espacio de tablas de InnoDB
Búfer de doble escritura
Otras opciones E/S
Ajustar la concurrencia de MySQL
Ajuste de la concurrencia MyISAM
Ajuste de la concurrencia de InnoDB
Ajuste basado en la carga de trabajo
Optimizar para cargas de trabajo BLOB y TEXT
Inspeccionar las variables de estado del servidor MySQL
Ajustar las configuraciones por conexión

Capítulo 7. Optimización del sistema operativo y del hardware
Limitaciones de rendimiento de MySQL
Cómo seleccionar una CPU para MySQL
¿Qué es mejor: CPU rápidas o muchas CPU?
Arquitectura de la CPU
Escalar a muchas CPU y núcleos
Equilibrar los recursos de la memoria y del disco
E/S aleatoria frente a E/S secuencial
Memoria caché, lecturas y escrituras
¿Cuál es su conjunto de trabajo?
El conjunto de trabajo y la unidad de caché
Encontrar una proporción efectiva memoria-disco
Seleccionar discos duros
Elegir el hardware para un esclavo
Optimización del rendimiento de RAID
Fallo, recuperación y supervisión de RAID
Equilibrar el hardware y el software RAID
Configuración RAID y memoria caché
El tamaño de bloques RAID divididos
La caché RAID
Redes de almacenamiento y almacenamiento conectado a la red
Redes de almacenamiento
Almacenamiento conectado a la red
Utilizar volúmenes de disco múltiples
Configuración de red
Seleccionar un sistema operativo
Elegir un sistema de archivos
Hilos
Intercambio
Estatus del sistema operativo
Cómo leer el resultado de vmstat
Cómo leer el resultado de iostat
Una máquina limitada por la CPU
Una máquina limitada por E/S
Un ordenador realizando intercambios
Un ordenador inactivo

Capítulo 8. Replicación
Visión general de la replicación
Problemas resueltos por la replicación
Cómo funciona la replicación
Configuración de la replicación
Crear cuentas de replicación
Configuración del maestro y el esclavo
Iniciar el esclavo
Inicializar un esclavo desde otro servidor
Configuración de replicación recomendada
Puesta en práctica de la replicación
Replicación basada en las sentencias
Replicación basada en filas
Archivos de replicación
Enviar eventos de replicación a otros esclavos
Filtros de replicación
Topologías de la replicación
Un maestro y múltiples esclavos
Maestro-maestro en el modo activo-activo
Maestro-maestro en el modo activo-pasivo
Maestro-maestro con esclavos
Anillo
Maestro, distribución de maestros y esclavos
Árbol o pirámide
Soluciones de replicación personalizadas
Replicación selectiva
Funciones de separación
Archivo de datos
Utilizar esclavos para búsquedas de texto completo
Esclavos de sólo lectura
Emular la replicación multimaestro
Crear un registro del servidor
Replicación y capacidad de planificación
Por qué la replicación no ayuda a escalar escrituras
Planificar la infrautilización
Administración y mantenimiento de la aplicación
Supervisar la replicación
Medir el desfase de los esclavos
Determinar si los esclavos son consistentes con el maestro
Resincronización de un esclavo desde el maestro
Cambiar maestros
Conversiones planeadas
Conversiones no planificadas
Localizar las posiciones de registro deseadas
Cambiar los papeles en una configuración maestro-maestro
Problemas y soluciones de replicación
Errores causados por la corrupción o pérdida de datos
Utilizar tablas no transaccionales
Mezclar tablas transaccionales y no transaccionales
Instrucciones no deterministas
Distintos motores de almacenamiento en maestro y esclavo
Cambios de datos en el esclavo
ID de los servidores no únicas
ID del servidor sin definir
Dependencia de datos no replicados
Tablas temporales perdidas
No replicar todas las actualizaciones
Bloquear la contención ocasionada por las selecciones de bloque InnoDB
Escribir a ambos maestros en una replicación maestro-maestro
Lapso de replicación excesivo
No duplicar las partes caras de la escritura
Hacer escrituras en paralelo fuera de la aplicación
Primar la caché sobre el hilo del esclavo
Paquetes demasiado grandes desde el maestro
Ancho de banda de replicación limitado
No hay espacio de disco
Limitaciones de la replicación
¿Qué tan rápida es la replicación?
El futuro de la replicación de MySQL

Capítulo 9. Escalabilidad y alta disponibilidad
Terminología
Escalar MySQL
Planificar la escalabilidad
Comprar tiempo antes de escalar
Escalabilidad vertical
Escalabilidad horizontal
Partición funcional
Fragmentación de datos
Elegir una clave de partición
Peticiones en varios fragmentos
Asignación de datos, fragmentos y nodos
Asignación fija
Asignación dinámica
Asignación explícita
Reequilibrar los fragmentos
Generar ID únicas de forma global
Herramientas para fragmentar
Reducción
Mantener los datos activos separados
Escalar mediante clustering
Clustering
Federación
Equilibrio de la carga
Conectar directamente
Dividir lecturas y escrituras en la replicación
Cambiar la configuración de la aplicación
Cambiar los nombres DNS
Mover direcciones IP
Introducción a los intermediarios
Balanceadores de carga
Algoritmos de equilibrio de carga
Añadir y eliminar servidores en el grupo
Equilibrio de carga con un maestro y varios esclavos
Alta disponibilidad
Planificación de la alta disponibilidad
Añadir redundancia
Arquitecturas de almacenamiento compartido
Arquitecturas de discos replicados
Replicación MySQL síncrona
Failover y failback
Convertir un esclavo en maestro o cambiar los papeles
Direcciones IP virtuales o IP takeover
MySQL Master-Master Replication Manager
Soluciones intermedias
Manejar failover en la aplicación

Capítulo 10. Optimización de la aplicación
Vista general del rendimiento de la aplicación
Encontrar el origen del problema
Búsqueda de problemas comunes
Problemas de los servidores Web
Encontrar la concurrencia óptima
Caché
La caché debajo de la aplicación
Caché de la aplicación
Políticas de control de caché
Jerarquías de los objetos caché
Pregenerar el contenido
Ampliar MySQL
Alternativas a MySQL

Capítulo 11. Copia de seguridad y recuperación
Visión general
Terminología
Todo sobre la recuperación
Temas que no tratamos
Visión global
¿Por qué copias de seguridad?
Consideraciones y ventajas y desventajas
¿Qué puede permitirse perder?
¿Copias de seguridad con o sin conexión?
¿Copias de seguridad lógicas o sin procesar?
Copias de seguridad lógicas
Copias de seguridad sin procesar
Qué copiar en la copia de seguridad
Copias de seguridad incrementales
Motores de almacenamiento y consistencia
Consistencia de datos
Consistencia de archivos
Replicación
Gestionar y realizar copias de seguridad de los registros binarios
El formato del registro binario
Limpiar registros binarios antiguos de forma segura
Copia de seguridad de los datos
Realizar una copia de seguridad lógica
El volcado SQL
Copias de seguridad de archivos delimitados
Volcado paralelo y restauración
Instantáneas de los sistemas de archivo
Cómo funcionan las instantáneas de LVM
Requisitos previos y configuración
Crear, montar y eliminar una instantánea LVM
Instantáneas para copias de seguridad conectadas
Copias de seguridad de InnoDB sin bloqueos con instantáneas LVM
Planificar las copias de seguridad LVM
Otros usos y alternativas
Recuperación desde una copia de seguridad
Limitar el acceso a MySQL
Restaurar archivos sin procesar
Iniciar MySQL después de restaurar los archivos sin procesar
Restaurar copias de seguridad lógicas
Cargar archivos SQL
Cargar archivos delimitados
Recuperación en un punto de tiempo determinado
Técnicas de recuperación más avanzadas
Replicación retrasada para una recuperación rápida
Recuperación con un servidor de registro
Recuperación InnoDB
Causas de la corrupción de InnoDB
Cómo recuperar datos corruptos de InnoDB
Velocidad de la copia de seguridad y de la recuperación
Herramientas para realizar copias de seguridad
mysqldump
mysqlhotcopy
InnoDB Hot Backup
mk-parallel-dump
mylvmbackup
Zmanda Recovery Manager
Instalar y probar ZRM
R1Soft
Copia de seguridad de MySQL con conexión
Comparación de herramientas de copias de seguridad
Escribir copias de seguridad

Capítulo 12. Seguridad
Terminología
Nociones básicas sobre las cuentas
Privilegios
Las tablas de permisos
Cómo comprueba MySQL los privilegios
Añadir, eliminar y ver permisos
Configurar privilegios en MySQL
Cambios de privilegios en MySQL 4.1
Cambios de privilegios en MySQL 5.0
Rutinas almacenadas
Activadores
Vistas
Privilegios sobre las tablas INFORMATION_SCHEMA
Privilegios y rendimiento
Problemas comunes y soluciones
Conectarse a través de localhost en contraste con 127.0.0.1
Utilizar tablas temporales de forma segura
No permitir el acceso sin contraseña
Desactivar usuarios anónimos
Recuerde citar los hostname de forma separada
No reutilizar nombres de usuario
Conceder SELECT permite SHOW CREATE TABLE
No conceder privilegios en la base de datos mysql
No conceder el privilegio SUPER libremente
Conceder privilegios en bases de datos comodines
Revocar privilegios específicos
Los usuarios pueden conectarse después de REVOKE
Cuándo no se pueden conceder o revocar privilegios
Privilegios invisibles
Privilegios obsoletos
Seguridad del sistema operativo
Directrices
Seguridad de la red
Conexiones sólo a localhost
Firewall
No hay ruta predeterminada
MySQL en DMZ
Codificación de la conexión y túneles
Redes privadas virtuales
SSL en MySQL
Túneles SSH
Envoltorios TCP
Bloqueo del host automático
Codificación de datos
Utilizar algoritmos hash en las contraseñas
Sistemas de archivos codificados
Codificación de la aplicación
Problemas de diseño
Codificar y descodificar dentro de MySQL
Modificación del código fuente
MySQL en un entorno chroot

Capítulo 13. Estado del servidor MySQL
Variables del sistema
SHOW STATUS
Estadísticas de hilos y de conexiones
Estado del registro binario
Contadores de instrucciones
Filas y tablas temporales
Operaciones del manejador
El búfer clave MyISAM
Descriptores de archivos
Caché de consultas
Tipos SELECT
Clasificaciones
Bloqueo de tablas
Secure Sockets Layer (SLL)
Específicas de InnoDB
Específicos de los plug-in
Miscelánea
SHOW INNODB STATUS
Cabecera
SEMAPHORES
LATEST FOREIGN KEY ERROR
LATEST DETECTED DEADLOCK
TRANSACTIONS
FILE I/O
INSERT BUFFER AND ADAPTIVE HASH INDEX
LOG
BUFFER POOL AND MEMORY
ROW OPERATIONS
SHOW PROCESSLIST
SHOW MUTEX STATUS
Estado de la replicación
INFORMATION_SCHEMA

Capítulo 14. Herramientas para un alto rendimiento
Herramientas de la interfaz
Herramientas visuales MySQL
SQLyog
phpMyAdmin
Herramientas de monitorización
Sistemas de monitorización no interactiva
Sistemas de cosecha propia
Nagios
Alternativas a Nagios
Servicio de monitorización y asesoramiento de MySQL
MONyog
RDDTool-based systems
Herramientas interactivas
innotop
Herramientas de análisis
Herramientas HackMySQL
Herramientas de análisis de Maatkit
Utilidades de MySQL
Proxy MySQL
Proxy de Dormando para MySQL
Utilidades de Maatkit
Fuentes de más información

Apéndice A. Transferir archivos de gran tamaño
Copiar archivos
Un ejemplo sencillo
Un método de un paso
Evitar la sobrecarga de la codificación
Otras opciones
Comparativa de copia de archivos

Apéndice B. Utilizar EXPLAIN
Invocar EXPLAIN
Reescribir peticiones no-SELECT
Las columnas en EXPLAIN
La columna id
La columna select_type
La columna table
Tablas y uniones derivadas
Un ejemplo de tipos complejos SELECT
La columna type
La columna possible_keys
La columna key
La columna key_len
La columna ref
La columna rows
La columna filtered
La columna Extra
EXPLAIN Visual

Apéndice C. Utilizar Sphinx con MySQL
Revisión: una búsqueda típica con Sphinx
¿Por qué utilizar Sphinx?
Búsqueda de texto completo escalable y eficiente
Aplicar cláusulas WHERE de forma eficiente
Encontrar los resultados principales en orden
Optimizar consultas GROUP BY
Generar conjuntos de resultados paralelos
Escalabilidad
Agregar datos fragmentados
Vista general de la arquitectura
Vista general de la instalación
Utilización de partición típica
Características especiales
Clasificación por proximidad a la frase
Admisión de atributos
Filtros
El motor de almacenamiento conectable SphinxSE
Control de rendimiento avanzado
Ejemplos prácticos de implementación
Búsqueda de texto completo en Mininova.org
Búsqueda de texto completo en BoardReader.com
Optimizar selecciones en Sahibinden.com
Optimizar GROUP BY en BoardReader.com
Optimizar peticiones JOIN fragmentadas en Grouply.com
Conclusión

Apéndice D. Bloqueos de depuración
Esperas de bloqueo en el servidor
Bloqueo de tablas
Descubrir quién contiene un bloqueo
El bloqueo de lectura global
Bloqueos de nombres
Bloqueos de usuario
Espera de bloqueos en motores de almacenamiento
Bloqueos de espera de InnoDB
Hacia un resultado de bloqueo más utilizable
Esperas de bloqueo Falcon

Índice alfabético