高性能MySQL

一 MySQL架构与历史

1. MySQL的逻辑架构：

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

2. 锁

• 处理并发读写：
  • 共享锁(shared lock)：读锁(read lock)
    读锁是共享的，相互不阻塞的，多个用户同一时刻可以读取同一个资源，且互不干扰。
  • 排他锁(exclusive lock)：写锁(write lock)
    写锁则是排他的，一个写锁会阻塞其他的写锁和读锁，确保给定时间内，只有一个用户能执行写入，防止其他用户读取正在写入的同一资源。
  • 锁粒度：锁的各种操作都会增加系统开销。每种MySQL存储引擎都可以实现自己的锁策略和锁粒度。
  • 锁策略：
    • 表锁(table lock)：开销最小。会锁定整张表
    • 行级锁(row lock)：开销最大。在InnoDB和XtraDB存储引擎层实现了行级锁。

3. 事务：

• 事务的四大特性

  • 原子性： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；

  • 一致性： 执行事务前后，数据库从一个一致性状态转换到另一个一致性状态。

  • 隔离性： 并发访问数据库时，一个用户的事物不被其他事务所干扰，各并发事务之间数据库是独立的；

  • 持久性： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库 发生故障也不应该对其有任何影响。

• 隔离级别

  • READ_UNCOMMITTED（未提交读）: 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读

  • READ_COMMITTED（提交读）: 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生

  • REPEATABLE_READ（可重复读）: 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。

  • SERIALIZABLE（串行）: 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。但是这将严重影响程序的性能。通常情况下也不会用到该级别。

• 注意：Mysql 默认采用的 REPEATABLE_READ隔离级别 Oracle 默认采用的 READ_COMMITTED隔离级别.

4. MySQL的存储引擎

• 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

5. 转换存储引擎

• ALTER TABLE

  ALTER TABLE mytable ENGINE = InnoDB;

6. MySQL时间线(Timeline)

• 服务器层面不同并发下的每秒事务数测试结果：

  

• 折线图：

  

二 MySQL基准测试

1. 基准测试(benchmark)

• 定义：针对系统设计的一种压力测试。

• 目的：掌握系统的行为。

2. 测试指标

• 吞吐量(throughput)：单位时间内的事务处理数。测试单位：
  TPS(Transaction Per Second)：每秒事务处理数
  TPM(Transaction Per Minute)：每分钟事务处理数

• 响应时间或延迟(latency)：测试任务所需的整体时间。测试单位：
  平均、最小、最大响应时间
  百分比响应时间(percentile response time)

• 并发性：同时工作中的线程数或连接数。当并发性增加时，测量吞吐量是否下降，响应时间是否延长
  目的：为了测试应用在不同并发下的性能。

• 可扩展性：给系统增加一倍工作，理想情况获得两倍效果(吞吐量增加一倍)。实际上无法做到线性扩展。
  目的：提供其他测试无法提供的信息，帮助发现应用的瓶颈。

3. 测试方法

• 设计规划基准测试

• 确定基准测试时间：

  • 达到系统稳定状态

• 获取系统性能和状态

• 运行基准测试并分析结果

  • 通常要运行多次取均值。

  • 编写shell脚本收集分析数据

  • 将性能指标按照时间顺序绘图，可以更快发现问题，如下图性能尖刺在压力大的系统较常见，由于使用InnoDB，脏块刷新算法性能差，一开始的低谷是系统缓存产生的，后面其他下跌则是系统刷新脏块到磁盘导致

  

4. 测试工具

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