《高性能MySQL》学习笔记

高性能MySQL

一 MySQL架构与历史

  1. MySQL的逻辑架构:

  • 最上层:服务器层服务。非MySQL独有,基于网络客户端\服务器工具都有类似架构。如连接处理、授权认证、安全等。
  • 第二层:MySQL核心服务功能层。包括查询解析、分析、优化、缓存以及所有内置函数(如日期、时间、数学和加密函数)。所有跨存储引擎功能在这层实现:存储过程、触发器、视图等。
  • 第三层:存储引擎。负责数据存储和提取。
    • 处理并发读写:
      • 共享锁(shared lock):读锁(read lock)
        读锁是共享的,相互不阻塞的,多个用户同一时刻可以读取同一个资源,且互不干扰。
      • 排他锁(exclusive lock):写锁(write lock)
        写锁则是排他的,一个写锁会阻塞其他的写锁和读锁,确保给定时间内,只有一个用户能执行写入,防止其他用户读取正在写入的同一资源。
      • 锁粒度:锁的各种操作都会增加系统开销。每种MySQL存储引擎都可以实现自己的锁策略和锁粒度。
      • 锁策略:
        • 表锁(table lock):开销最小。会锁定整张表
        • 行级锁(row lock):开销最大。在InnoDB和XtraDB存储引擎层实现了行级锁。
  1. 事务:

  • 事务的四大特性(ACID)

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

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

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

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

  • 隔离级别

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

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

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

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

  • 注意:Mysql 默认采用的 REPEATABLE_READ隔离级别 Oracle 默认采用的 READ_COMMITTED隔离级别.
  1. 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
  1. 转换存储引擎

  • ALTER TABLE

    ALTER TABLE mytable ENGINE = InnoDB;

  1. MySQL时间线(Timeline)

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

二 MySQL基准测试

  1. 基准测试(benchmark)

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

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

  1. 测试指标

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

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

  • 并发性:同时工作中的线程数或连接数。当并发性增加时,测量吞吐量是否下降,响应时间是否延长
    目的:为了测试应用在不同并发下的性能。
  • 可扩展性:给系统增加一倍工作,理想情况获得两倍效果(吞吐量增加一倍)。实际上无法做到线性扩展。
    目的:提供其他测试无法提供的信息,帮助发现应用的瓶颈。
  1. 测试方法

  • 设计规划基准测试

  • 确定基准测试时间:

    • 达到系统稳定状态
  • 获取系统性能和状态

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

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

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

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

  1. 测试工具

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