高性能MySQL : 第三版

foreword

preface

1. mysql architecture and history

mysql's logical architecture

connection management and security

optimization and execution

concurrency control

read/write locks

lock granularity

transactions

isolation levels

deadlocks

transaction logging

transactions in mysql

multiversion concurrency control

mysql's storage engines

the innodb engine

the mylsam engine

other built-in mysql engines

third-party storage engines

selecting the right engine

table conversions

a mysql timeline

mysql's development model

summary

2. benchmarking mysql

why benchmark?

benchmarking strategies

what to measure

benchmarking tactics

designing and planning a benchmark

how long should the benchmark last?

capturing system performance and status

getting accurate results

running the benchmark and analyzing results

the importance of plotting

benchmarking tools

full-stack tools

single-component tools

benchmarking examples

httpload

mysql benchmark suite

sysbench

dbt2 tpc-c on the database test suite

percona's tpcc-mysql tool

summary

3. profiling server performance

introduction to performance optimization

optimization through profiling

interpreting the profile

profiling your application

instrumenting php applications

profiling mysql queries

profiling a server's workload

profiling a single query

using the profile for optimization

diagnosing intermittent problems

single-query versus server-wide problems

capturing diagnostic data

a case study in diagnostics

other profiling tools

using the user_statistics tables

using strace

summary

4. optimizing schema and data types

choosing optimal data types

whole numbers

real numbers

string types

date and time types

bit-packed data types

choosing identifiers

special types of data

schema design gotchas in mysql

normalization and denormalization

pros and cons of a normalized schema

pros and cons of a denormalized schema

a mixture of normalized and denormalized

cache and summary tables

materialized views

counter tables

speeding up alter table

modifying only thefrm file

building mylsam indexes quickly

summary

5. indexing for high performance

indexing basics

types of indexes

benefits of indexes

indexing strategies for high performance

isolating the column

prefix indexes and index selectivity

multicolumn indexes

choosing a good column order

clustered indexes

covering indexes

using index scans for sorts

packed (prefix-compressed) indexes

redundant and duplicate indexes

unused indexes

indexes and locking

an indexing case study

supporting many, kinds of filtering

avoiding multiple range conditions

optimizing sorts

index and table maintenance

finding and repairing table corruption

updating index statistics

reducing index and data fragmentation

summary

6. query performance optimization

why are queries slow?

slow query basics: optimize data access

are you asking the database for data you don't need?

is mysql examining too much data?

ways to restructure queries

complex queries versus many queries

chopping up a query

join decomposition

query execution basics

the mysql client/server protocol

the query cache

the query optimization process

the query execution engine

returning results to the client

limitations of the mysql query optimizer

correlated subqueries

union limitations

index merge optimizations

equality propagation

parallel execution

hash joins

loose index scans

min0 and max()

select and update on the same table

query optimizer hints

optimizing specific types of queries

optimizing count() queries

optimizing join queries

optimizing subqueries

optimizing group by and distinct

optimizing limit and offset

optimizing sql_calc_found_rows

optimizing union

static query analysis

using user-defined variables

case studies

building a queue table in mysql

computing the distance between points

using user-defined functions

summary

7. advanced my$ql features

partitioned tables

how partitioning works

types of partitioning

how to use partitioning

what can go wrong

optimizing queries

merge tables

views

updatable views

performance implications of views

limitations of views

foreign key constraints

storing code inside mysql

stored procedures and functions

triggers

events

preserving comments in stored code

cursors

prepared statements

prepared statement optimization

the sql interface to prepared statements

limitations of prepared statements

user-defined functions

plugins

character sets and collations

how mysql uses character sets

choosing a character set and collation

how character sets and collations affect queries

full-text searching

natural-language full-text searches

boolean full-text searches

full-text changes in mysql 5.1

full-text tradeoffs and workarounds

full-text configuration and optimization

distributed (xa) transactions

internal xa transactions

external xa transactions

the mysql query cache

how mysql checks for a cache hit

how the cache ues memory

when the query cache is helpful

how to configure and maintain the query cache

innodb and the query cache

general query cache optimizations

alternatives to the query cache

summary

8. optimizing server settings

how mysql's configuration works

syntax, scope, and dynamism

side effects of setting variables

getting started

iterative optimization by benchmarking

what not to do

creating a mysql configuration file

inspecting mysql server status variables

configuring memory usage

how much memory can mysql use?

per-connection memory needs

reserving memory for the operating system

allocating memory for caches

the innodb buffer pool

the myisam key caches

the thread cache

the table cache

the innodb data dictionary

configuring mysql's i/o behavior

innodb i/o configuration

myisam i/o configuration

configuring mysql concurrency

innodb concurrency configuration

mylsam concurrency configuration

workload-based configuration

optimizing for blob and text workloads

optimizing for filesorts

completing the basic configuration

safety and sanity settings

advanced innodb settings

summary

9. operating system and hardware optimization

what limits mysql's performance?

how to select cpus for mysql

which is better: fast cpus or many cpus?

cpu architecture

scaling to many cpus and cores

balancing memory and disk resources

random versus sequential i/o

caching, reads, and writes

what's your working set?

finding an effective memory-to-disk ratio

choosing hard disks

solid-state storage

an overview of flash memory

flash technologies

benchmarking flash storage

solid-state drives (ssds)

pcie storage devices

other types of solid-state storage

when should you use flash?

using flashcache

optimizing mysql for solid-state storage

choosing hardware for a replica

raid performance optimization

raid failure, recovery, and monitoring

balancing hardware raid and software raid

raid configuration and caching

storage area networks and network-attached storage

san benchmarks

using a san over nfs or smb

mysql performance on a san

should you use a san?

using multiple disk volumes

network configuration

choosing an operating system

choosing a filesystem

choosing a disk queue scheduler

threading

swapping

operating system status

how to read vmstat output

how to read iostat output

other helpful tools

a cpu-bound machine

an i/o-bound machine

a swapping machine

an idle machine

summary

10. replication.

replication overview

problems solved by replication

how replication works

setting up replication

creating replication accounts

configuring the master and replica

starting the replica

initializing a replica from another server

recommended replication configuration

replicationunder the hood

statement-based replication

row-based replication

statement-based or row-based: which is better?

replication files

sending replication events to other replicas

replication filters

replication topologies

master and multiple replicas

master-master in active-active mode

master-master in active-passive mode

master-master with replicas

ring replication

master, distribution master, and replicas

tree or pyramid

custom replication solutions

replication and capacity planning

why replication doesn't help scale writes

when will replicas begin to lag?

plan to underutilize

replication administration and maintenance

monitoring replication

measuring replication lag

determining whether replicas are consistent with the master

resyncing a replica from the master

changing masters

switching roles in a master-master configuration

replication problems and solutions

errors caused by data corruption or loss

using nontransactional tables

mixing transactional and nontransactional tables

nondeterministic statements

different storage engines on the master and replica

data changes on the replica

nonunique server ids

undefined server ids

dependencies on nonreplicated data

missing temporary tables

not replicating all updates

lock contention caused by innodb locking selects

writing to both masters in master-master replication

excessive replication lag

oversized packets from the master

limited replication bandwidth

no disk space

replication limitations

how fast is replication?

advanced features in mysql replication

other replication technologies

summary

11. scaling mysql

what is scalability?

a formal definition'

scaling mysql

planning for scalability

buying time before scaling

scaling up

scaling out

scaling by consolidation

scaling by clustering

scaling back

load balancing

connecting directly

introducing a middleman

load balancing with a master and multiple replicas

summary

12. high availability

what is high availability?

what causes downtime?

achieving high availability

improving mean time between failures

improving mean time to recovery

avoiding single points of failure

shared storage orreplicated disk

synchronous mysql replication

replication-based redundancy

failover and failback

promoting a replica or switching roles

virtual ip addresses or ip takeover

middleman solutions

handling failover in the application

summary

13. mysql in the cl0ud

benefits, drawbacks, and myths of the cloud

the economics of mysql in the cloud

mysql scaling and ha in the cloud

the four fundamental resources

mysql performance in cloud hosting

benchmarks for mysql in the cloud

mysql database as a service (dbaas)

amazon rds

other dbaas solutions

summary

14. application-level optimization

common problems

web server issues

finding the optimal concurrency

caching

caching below the application

application-level caching

cache control policies

cache object hierarchies

pregenerating content

the cache as an infrastructure component

using handlersocket and memcached access

extending mysql

alternatives to mysql

summary

15. backup and recovery

why backups?

defining recovery requirements

designing a mysql backup solution

online or offiine backups?

logical or raw backups?

what to back up

storage engines and consistency

replication

managing and backing up binary logs

the binary log format

purging old binary logs safely

backing up data

making a logical backup

filesystem snapshots

recovering from a backup

restoring raw files

restoring logical backups

point-in-time recovery

more advanced recovery techniques

innodb crash recovery

backup and recovery tools

mysql enterprise backup

percona xtrabackup

mylvmbackup

zmanda recovery manager

mydumper

mysqldump

scripting backups

summary

16. tools for mysql users

interface tools

command-line utilities

sql utilities

monitoring tools

open source monitoring tools

commercial monitoring systems

command-line monitoring with innotop

summary

a. forks and variants of mysql

b. mysql server status

c. transferring large files

d. using explain

e. debugging locks

f. using sphinx with mysql

index

如何充分发挥mysql数据库的所有能力？在《高性能MySQL（第3版）（影印版）》一书中，你将学到与mysql数据库模式、索引和查询设计相关的所有高级技巧，通过使用这些技巧，你将能够对mysql数据库服务器，操作系统和硬件进行润优以发挥它们的最大潜力。这本指南同时也提供了通过复制、负载均衡，高可用性及故障转移等技术对应用进行扩展的安全实用方法。
第三版中更新了mysql数据库和innodb存储引擎在性能、特性和工具等相关领域取得的最新进展，不仅通过大量的特定示例讲解了mysql数据库如何工作，同时也围绕mysql数据库的设计原则、采用生动的故事和案例研究的形式解释了其对应的工作原理。本书教给读者如何采用mysql的方式进行思考。
学习mysql 5．5版提供的新特性，包括存储过程。数据库分区、触发器和视图。
实现在复制、高可用性和集群上的改进。
实现mysql在云环境中运行的高性能。
优化高级查询特性，如全文检索。
充分利用多核处理器和固态硬盘等硬件带来的优势。
探索备份和恢复策略——包括新的在线热备份工具。