UP | HOME

high-performance-mysql-3rd

Table of Contents

Chapter 01: MySQL Architecture and History

  • mysql和其他的database server很不一样,这导致mysql很适合一些场景,又非常不适合 另外一些场景
  • mysql并不完美,但足够flexible,可以应付很多类系统
  • 为了能够从mysql中得到更多,你需要理解它的设计思想.比如,mysql最特别的地方在于 它的storage-engine架构,这个架构的特点是,这个设计区把如下两个部分区分开了:
    1. query processing和其他server task
    2. data storage 和 retrieval

MySQL's Logical Architecture

  • 下图能够让人更容易理解MySQL的架构 
                  Clients

+--------------------------------+
|  +--------------------------+  |
|  |Connection/thread handling|  |
|  +--+------------------+----+  |
|     |                  |       |
|     |                  |       |
|  +--v--+           +---v----+  |
|  |Query|           |Parser  |  |
|  |cache|<----------+        |  |
|  +-----+           +---+----+  |
|                        |       |
|                        |       |
|  +---------------------v----+  |
|  |      Optimizer           |  |
|  +--------------------------+  |
|                                |
+--------------------------------+
          Storage engines
  • 上图中的最上面一层是大部分的数据库系统都有的,就是处理connection,认证,安全等 系统
  • 第二层包含了大部分的MySQL的精华,包括代码的query parse, cache,同时系统提供的 date,time,math和加密等utility都是在这里提供的
  • 第三层就是很存储相关的部分了.这部分会和系统的storage engines相互通信,其为第 二层提供了一套非常高效的storage engine API

Connection Management and Security

  • 每一个client的connection到来,都会导致server为这个connection分配一个thread, connection的query都是在一个单个的thread里面运行的,也就会是仅仅存在于固定的 一个thread里面.而server端则会cache 这个thread,而不必每次新的client来都去创 建一次thread(5.5开始支持使用线程池的设置)
  • 当client连接server的时候,server需要对用户进行认证,认证的方式就是username, password,注意,还有originating host!
  • 认证不仅仅包括是否允许连接server,每个client使用的用户不同,其在server端的权 限也是不同的(比如某些用户就不能SELECT world数据库里面的Country表)

Optimization and Execution

  • mysql分析query语句,并且会建立一个内部的数据结构(叫做parse tree),然后会在parse tree的基础上,进行优化:
    • 重写query
    • 决定读取table的顺序
    • 决定使用哪个index
  • 上述的优化决定都是server做出的,但是你可以通过传入一些hint,来让server按照你 的口味进行更改
  • 你还可以让server把它的优化方案全盘展现给你,看看你是否满意
  • 优化系统还需要考虑storage engine的特点,所以它也会通过API了解storage engine 的特点之后,才进行按需的优化
  • 而上述这些对于query语句的优化(只包括select)也不一定执行:那是因为有可能前面 有其他client已经查询过同样的"问题"了,这种情况下,只需要简单返回cache的值就可 以了

Concurrency Control

  • 如果有两个以上的client同时访问server数据库,为了防止相互影响,我们必须要加锁, 但是如果是加mutex锁的话,会对性能有很大影响,因为对于数据库来说,显然同时读取 是可以的,不会相互影响的.所以在Mysql里面加的是Read/Write Lock
  • 我们原来在多线程编程中学到过,尽可能的减小critical region的可以提高并发的效率, 而在数据库里面的体现,就是减小我们lock的粒度(granularity), 本来是锁整个数据 库的可以改成锁"需要更改的那个表,甚至是表里面的一行,甚至更小"
  • 大部分的商业数据库给出的解决方案也就是"锁到改动的那一行",因为更小的critical region会有更多的lock的系统消耗,综合起来考虑,"行锁"是最经济的解决方案.
  • Mysql当然比大部分的商业数据库灵活,它可以采取使用者想要的各种级别的锁,可以自 己配置:
    • Table locks: mysql里面最常见的锁的方式,它锁住了整个table(同时只能有一个client 更改某个table),这种方式的系统消耗(overhead)最小
    • Row locks: 它锁住了某一行(同时只能有一个cliet改动某一行),这种方式的并发效 果最好.需要注意的是在mysql里面row lock是在storage engine里面实现的,而不是 在server里面实现的(我们上面的图中可以看到storage engine是不在mysql server) 里面的.实现storage engine的两个engine是InnoDB和XtraDB

Transactions

  • 所谓事务(transaction)就是被当做"atomical"的一系列SQL
  • 在MySQL里面事务的样子是如下: 
    START TRANSACTION;
SELECT balance FROM checking WHERE customer_id = 10233276;
UPDATE checking SET balance = balance - 200.00 WHERE customer_id = 10233276;
UPDATE savings  SET balance = balance - 200.00 WHERE customer_id = 10233276;
COMMIT;
  • transaction需要通过ACID test,才能被称之为一个事务:
    • Atomicity: 一个事务必须要么全部被执行,要么全部不执行. all or nothing
    • Consistency: 数据库的完整性体现在服务器crash的时候,只要没有commit的事务都当做没发生过
    • Isolation: 事务的结果,只有在完成以后,才能对其他事务可见
    • Durability: 事务的结果必须被record,而且要保持不变
  • 前面ACID里面的I就是isolation,isolation并不像看起来那么的简单,SQL的规范里面 甚至定义了不同的isolatoin级别,越松的级别对应着越高的并发性能:
    • READ UNCOMMITED: 在这个级别下面,事务之间可以看到彼此还没有commited的结果.这个级别在实践中 没有使用的,因为它虽然有一些性能优势,但是带来更多的问题
    • READ COMMITTED: 大多数数据库的isolation级别(但是却并不是MySQL的isolation级别!!),也就是说 只要一个transaction一旦begin,其他transaction就看不到这个transaction任何的 结果啦.这个级别依然允许nonrepeateable read.也就是说,你可以运行相同的statement 两次,得到不同的结果
    • REPEATABLE READ: 这是MySQL默认的isolation level:解决了NonRepeatable read,当然了,它还是会遇 到phantom read问题
    • SERIALIZABLE: 解决了phantom read问题,其原理是read的时候,也lock自己读取的每一个row,所以 不会出现phantom read问题,但是代价太大
  • 上述四则总结起来就是
    Isolation Level Dirty read Nonrepeatable Phanom read Locking
      possible read possible possible Reads
    READ UNCOMMITTED Yes Yes Yes No
    Read COMMITTED No Yes Yes No
    REPEATABLE READ No No Yes No
    SERIALIZABLE No No No YES
  • Deadlock也是数据库可能发生的同步问题之一,deadlock产生的原因在于"获取lock资源 的时候,使用了不同的order"
  • 为了解决这个问题,database system会有很多的deadlock detect以及timeout的设置 而现今的InnoDB storage engine会在开始运行的时候就能detect circular dependency 从而更快的发现问题
  • Transaction Logging是数据库里面非常重要的技术,它中和了如下两者的特点:
    • 存储在memory:速度快,但是掉电就没了
    • 存储在硬盘:可以random-access,但访问速度慢
  • 而transaction log的特点就是:是线性存储,所以没那么慢.而且是durable的.数据库并 不是把每次改动都直接写到硬盘里面,那样太慢.一般来说是保存在内存里面,过一点时 间存放到transaction log,再过更长的间隙才会放到硬盘里面.这种技术叫做write-ahead logging.
  • Mysql提供了两种transaction storage engine: InnoDB和NDB Cluster
  • Mysql默认是AUTOCOMMIT的,这句话放到这里有点奇怪,其实不然.所谓AUTOCOMMIT可以 理解为每条语句前后各加上TRANSACTION BEGIN和TRANSACTION END 
    mysql> SHOW VARIABLES LIKE 'AUTOCOMMIT';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> SET AUTOCOMMIT = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'AUTOCOMMIT';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.01 sec)
  • 如果你把AUTOCOMMIT关了的话,那么你的任意一条语句,一定要跟着一个COMMIT(或者 ROLLBACK)才行.否则根本不会执行

Multiversion Convurrency Control

  • 大部分的Mysql transactioinal storage engine都不会简单的使用row-locking的方 法,而会使用更加高大上的MVCC(multiversion concurrency control), Oracle, PostgreSQL 也都会用这个
  • MVCC非常的高效,它可以做到read的时候不需要lock!write的时候,也只lock相关的几行
  • MVCC是通过snapshot来做到上面的情况尽量少用锁的,所以可能会出现不同的事务看到 不同的结果的情况

MySQL's Storge Engines

  • mysql一般把所有的database存储在/var/lib/mysql这个文件夹下面
  • 每创建一个database就会在/var/lib/mysql下面创建一个相同名字的文件夹.
  • 每创建一个table就会在其数据库文件夹下面创建一个同名的fm文件 
    mysql> use hfeng;
Database changed
mysql> create table Persions(pID int);
Query OK, 0 rows affected (0.06 sec)

mysql> show tables;
+-----------------+
| Tables_in_hfeng |
+-----------------+
| Persions        |
+-----------------+
1 row in set (0.00 sec)

root@c426e46f706b:/var/lib/mysql/hfeng# pwd
/var/lib/mysql/hfeng
root@c426e46f706b:/var/lib/mysql/hfeng# ls
Persions.frm  db.opt
  • Mysql的默认storage engine就是InnoDB, InnoDB最早是作为Mysql的插件出现的,但由 于其卓越的性能,再加上Oracle的改进,如今它已经是默认配置了,而且没有特殊理由, 你也不需要使用其他的engine
  • InnoDB使用了MVCC来达到高并发,并且实现默认的四种SQL standard isolation,其默认 的isolation lavel是REPEATABLE READ
  • 在5.1已经之前的版本中,mysql是使用MyISAM作为默认的storge engine的

Chapter 02: Benchmarking Mysql

Why Benchmark?

  • 因为benchmark是最方便有效的了解当你给系统事情做的时候,发生了什么.它可以对你 在如下方面有所提高:
    • 验证你的想法
    • 改变你试图'猜测系统'的坏习惯
    • 模拟一个更高的压力测试
    • 为未来做打算
    • 测试硬件
  • benchmark也有自己的问题,因为你用来测试系统的workload不一定是真实的,因为真实 的workload很难创建.