高性能MySQL - 架构和基准测试

MySQL架构

逻辑架构

每个虚线框为一层,总共三层。
第一层,服务层(为客户端服务):为请求做连接处理,授权认证,安全等。

第二层,核心层:查询解析,分析,优化,缓存,提供内建函数;存储过程,触发器,视图。

第三层,存储引擎层,不光做存储和提取数据,而且针对特殊数据引擎还要做事务处理。

并发控制

共享锁(shared lock, 读锁)和排它锁(exclusive lock, 写锁)。

每种MySQL数据库都有自己的锁策略和锁粒度。(商用数据库多为行级锁, row level lock)

表锁(开销少)和行级锁(并发度高)。

事务

事务就是一组原子性的SQL查询。(ACID, 原子性、一致性、隔离性、持久型)

ps. START TRANSACTION / COMMIT / ROLLBACK

隔离级别

  1. READ UNCOMMITTED(未提交读)
    未提交的事务中的修改对其他事务是可见的,即事务可以读取未提交的数据,也叫作脏读(dirty read)
    例如:事务T1将值修改,然后事务T2读取该值,此后T1因为某种原因撤销对该值的修改,这就导致T2所读取到的数据是无效的。

  2. READ COMMITTED(提交读)
    大多数数据库默认隔离级别都是提交读(MySQL不是)。
    一个事务从开始到提交前,所做的任何修改对于其他事务都不可见,也叫不可重复读。

  3. REPEATABLE READ(可重复读,MySQL默认)
    在同一事务中,同一查询多次进行时候,由于其他插入操作(insert)的事务提交,导致每次返回不同的结果集。这也叫做幻读,可重复读解决了脏读的问题,但还是可能出现幻读。

不可重复读的重点是修改 : 同一事务,两次读取到的数据不一样。
幻读的重点在于新增或者删除 : 同样的条件, 第1次和第2次读出来的记录数不一样
对于前者, 只需要锁住满足条件的记录;对于后者, 要锁住满足条件及其相近的记录

  1. SEAIALIZABLE(可串行化)
    串行化是最高的隔离级别,强制事务串行执行(在读取的每行数据上加锁)。
隔离级别 脏读 不可重复读 幻读 读锁
READ UNCOMMITTED YES YES YES NO
READ COMMITTED NO YES YES NO
REPEATABLE READ NO NO YES NO
SEAIALIZABLE NO NO NO YES

死锁

InnoDB回滚持有最少行级排它锁。

事务日志

MySQL存储引擎

基准测试

整体测试整个系统(集成式)和单独测试MySQL(单组件式)。

测试指标:吞吐量,响应时间或延迟,并发性,可扩展性

测试方法

常见错误

  1. 使用真实数据的子集而不是全集。例如应用需处理200G,只测试20G。
  2. 错误的数据分布。均匀分布的数据,未考虑热点数据情况。
  3. 使用不真实的分布参数。如假定所有用户个人信息被平均读取。
  4. 多用户场景中只做单用户测试。
  5. 单服务器上测试分布式应用。
  6. 与真实用户行为不匹配。如页面阅读和思考时间,而不是连续点击。
  7. 反复执行同一个查询。(缓存命中率)
  8. 没有检查错误。没有检查错误日志或者探究测试结果异常的原因。
  9. 忽略系统预热。(数据缓存不同)
  10. 默认数据库配置,测试时间太短等。

基准测试工具

集成式: ab、http_load、JMeter
单组件: mysqlslap、sql-bench、super smack、Database Test Suits、sysbench

MySQL BENCHMARK()函数—SELECT BENCHMARK(100000,MD5/SHA1(‘HELLO’));