高性能MySQL
一 MySQL架构与历史
- 最上层:服务器层服务。非MySQL独有,基于网络客户端\服务器工具都有类似架构。如连接处理、授权认证、安全等。
- 第二层:MySQL核心服务功能层。包括查询解析、分析、优化、缓存以及所有内置函数(如日期、时间、数学和加密函数)。所有跨存储引擎功能在这层实现:存储过程、触发器、视图等。
- 第三层:存储引擎。负责数据存储和提取。

锁
- 处理并发读写:
- 共享锁(shared lock):读锁(read lock)
读锁是共享的,相互不阻塞的,多个用户同一时刻可以读取同一个资源,且互不干扰。 - 排他锁(exclusive lock):写锁(write lock)
写锁则是排他的,一个写锁会阻塞其他的写锁和读锁,确保给定时间内,只有一个用户能执行写入,防止其他用户读取正在写入的同一资源。 - 锁粒度:锁的各种操作都会增加系统开销。每种MySQL存储引擎都可以实现自己的锁策略和锁粒度。
- 锁策略:
- 表锁(table lock):开销最小。会锁定整张表
- 行级锁(row lock):开销最大。在InnoDB和XtraDB存储引擎层实现了行级锁。
- 共享锁(shared lock):读锁(read lock)
- 处理并发读写:
事务:
事务的四大特性(ACID)
原子性: 事务是最小的执行单位,不允许分割。事务的原子性确保动作要么全部完成,要么完全不起作用;
一致性: 执行事务前后,数据库从一个一致性状态转换到另一个一致性状态。
隔离性: 并发访问数据库时,一个用户的事物不被其他事务所干扰,各并发事务之间数据库是独立的;
持久性: 一个事务被提交之后。它对数据库中数据的改变是持久的,即使数据库 发生故障也不应该对其有任何影响。
隔离级别
READ_UNCOMMITTED(未提交读): 最低的隔离级别,允许读取尚未提交的数据变更,可能会导致脏读、幻读或不可重复读
READ_COMMITTED(提交读): 允许读取并发事务已经提交的数据,可以阻止脏读,但是幻读或不可重复读仍有可能发生
REPEATABLE_READ(可重复读): 对同一字段的多次读取结果都是一致的,除非数据是被本身事务自己所修改,可以阻止脏读和不可重复读,但幻读仍有可能发生。
SERIALIZABLE(串行): 最高的隔离级别,完全服从ACID的隔离级别。所有的事务依次逐个执行,这样事务之间就完全不可能产生干扰,也就是说,该级别可以防止脏读、不可重复读以及幻读。但是这将严重影响程序的性能。通常情况下也不会用到该级别。
- 注意:Mysql 默认采用的 REPEATABLE_READ隔离级别 Oracle 默认采用的 READ_COMMITTED隔离级别.
InnoDB:MySQL的默认事务型引擎,是最重要、使用最广泛的存储引擎。
- 是一个事务型数据库引擎。处理大量短期事务很少回滚。
- 性能优越,自动崩溃回复特性。
- Oracle在MySQL5.5版本时将InnoDB作为默认存储引擎
- InnoDB采用MVCC支持高并发,实现了四个标准隔离级别,其默认隔离级别是REPEATABLE READ(可重复读),并且通过锁间隙(next-key locking)策略防止幻读。
- InnoDB表时基于聚簇索引建立的,对主键查询有很高的性能。它的二级索引(secondary index)中必须包含主键列。
- InnoDB从磁盘读取时采用可预测性预读,自动在内存中创建hash索引加速读操作的自适应哈希索引(adaptive hash index),以及能加速插入操作的插入缓冲区(insert buffer)。
MyISAM存储引擎:MySQL5.1及之前版本MySQL的默认存储引擎。
- 全文索引、压缩、空间函数(GIS)
- 不支持事务和行级锁
- 崩溃后无法安全恢复
- 对于只读或较小数据,可以使用MyISAM
ALTER TABLE
ALTER TABLE mytable ENGINE = InnoDB;
- 服务器层面不同并发下的每秒事务数测试结果:

- 折线图:

二 MySQL基准测试
定义:针对系统设计的一种压力测试。
目的:掌握系统的行为。
吞吐量(throughput):单位时间内的事务处理数。测试单位:
TPS(Transaction Per Second):每秒事务处理数
TPM(Transaction Per Minute):每分钟事务处理数响应时间或延迟(latency):测试任务所需的整体时间。测试单位:
平均、最小、最大响应时间
百分比响应时间(percentile response time)- 并发性:同时工作中的线程数或连接数。当并发性增加时,测量吞吐量是否下降,响应时间是否延长
目的:为了测试应用在不同并发下的性能。 - 可扩展性:给系统增加一倍工作,理想情况获得两倍效果(吞吐量增加一倍)。实际上无法做到线性扩展。
目的:提供其他测试无法提供的信息,帮助发现应用的瓶颈。
设计规划基准测试
确定基准测试时间:
- 达到系统稳定状态
获取系统性能和状态
运行基准测试并分析结果
通常要运行多次取均值。
编写shell脚本收集分析数据
将性能指标按照时间顺序绘图,可以更快发现问题,如下图性能尖刺在压力大的系统较常见,由于使用InnoDB,脏块刷新算法性能差,一开始的低谷是系统缓存产生的,后面其他下跌则是系统刷新脏块到磁盘导致

- 集成式测试工具:ab, http_load, JMeter
- 单组件式测试工具:mysqlslap, MySQL Benchmark Suite(sql-bench), Super Smack, sysbench
- 尽量早和多地使用绘图的方式,来发现基准测试和系统中的问题和错误。你的眼睛是比任何脚本和自动化工具都更有效的发现问题的工具。