高性能MySQL笔记

第一章 MySQL架构与历史

MySQL 架构

存储引擎层负责MySQL中数据的存储与提取，各个存储引擎都有其优势与劣势，存在即合理。Server层通过API与存储引擎进行通信，接口屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询过程透明。存储引擎不会去解析SQL，InnoDB 除外，InnoDB会解析外键定义。不同存储引擎之间不会相互通信，只响应Server层的请求。

优化器会解析查询，并创建内部数据结构(解析数)，然后对其进行各种优化，包括重写查询，选择合适的索引，以及在一个语句有多表关联(join) 的时候，决定各个表的连接顺序。用户可以通过关键字 hint(提示) 优化器，影响其决策过程。

优化器不关心表使用的什么存储引擎，但是存储引擎对优化查询是有影响的。优化器会请求存储引擎提供容量或者某个具体操作的开销信息，以及表的统计信息等。某些存储引擎的某种索引可能对一些特定的查询有优化。

并发控制

无论何时，只要有多个查询在同一个时刻修改数据，都会产生并发控制的问题。

“共享锁” 又称 “读锁”，“排它锁” 又称 “写锁”。

读锁是共享的，或者说是相互不阻塞的。多个客户端在同一个时刻可以读取同一个资源，且互不干扰。写锁是排他的，也就是说一个写锁会阻塞其他的写锁和读锁，这是处于安全策略的考虑，只有这样，才能保证数据在给定的时间里，只有一个用户能执行写入，并防止其他用户读取正在写入的资源。

锁粒度

一种提高共享资源并发性的方式是让锁定的对象更有选择性。尽量只锁定需要修改的部分数据，而不是所有的资源。更理想的方式是，只对会修改的数据片进行精确的锁定。任何时候，在给定的资源上，锁定的数据量越少，系统的并发程度越高，只要相互之间不发生冲突即可。
加锁也会消耗资源。锁的各种操作，包括获得锁，检查锁是否已删除，释放锁等，都会造成系统的开销。

各个MySQL的存储引擎都可实现自己的锁策略和锁粒度。

表锁(table lock)

表锁是MySQL中最基本的锁策略，并且是开销最小的策略。表锁会锁定整张表，当一个用户在对表进行写操作（插入、删除、更新等）前，需要获得写锁，这会阻塞其他用户对该表的读写操作。只有在没有写锁时，其他读取的用户才能获得读锁，读锁之间是相互不阻塞的。写锁比读锁有更高的优先级，写锁请求可能会插入到读锁队列的前面，但是读锁不能抢占写锁。
虽然存储引擎可以管理自己的锁，但是MySQL本身还是会用各种有效的表锁来实现不同的目的。比如，服务器会对诸如 alter table ... 之类的语句使用表锁，而忽略存储引擎的锁机制。

行锁(row lock)

InnoDB 以及 XtraDB 等存储引擎实现了行级锁。
行锁最大程度地支持并发处理（同时也带来了最大的锁开销）。
行锁只在存储引擎层实现，Server层完全不了解存储引擎中的锁实现。

事务

事务ACID

1. 原子性
   一个事务必须为被视为一个不可分割的最小单元，整个事务只有全部提交成功与全部失败会滚两种状态，不可能只执行其中一小部分。
2. 一致性
   数据库总是从一个一致性状态转移到另外一个一致性状态。
3. 隔离性。
   通常来说，一个事务所做的修改在最终提交前， 对其他事务是不可见的。
4. 持久性
   一旦事务提交，则其所做的修改就会被永久保存到数据库中，即使系统崩溃，修改的数据也不丢失。

一个实现了ACID的数据库，相比没有实现ACID的数据库，通常会需要更强的CPU处理能力，更大的内存以及更多的存储空间。即使存储引擎不支持事务，也可以通过LOCK TABLES 语句为应用提供一定的保护。

隔离级别

隔离级别	脏读可能性	不可重复读可能性	幻读可能性	加锁读
读未提交 Read UNCOMMITTED	Yes	Yes	Yes	No
读提交 Read COMMITTED	No	Yes	Yes	No
可重复读 REPEATABLE READ	No	No	Yes	No
串行化 SERIALIZABLE	No	No	No	Yes

死锁

死锁是在两个或多个事务在同一个资源上相互占用，并请求锁定对方的资源，从而引起的恶性循环的现象。当多个事务试图以不同的顺序锁定资源时 以及 多个事务同时锁定同一资源，就可能产生死锁。

例子:

事务1

start transaction;
update table_a set price = 10.34 where id = 1;
update table_a set price = 8.26 where id = 2;
commit;

事务2

start transaction;
update table_a set price = 21.26 where id = 2;
update table_a set price = 9.34 where id = 1;
commit;

InnoDB 目前处理死锁的方式是：将持有最少行级排它锁的事务进行回滚。

锁的行为与顺序与存储引擎相关。以同样的顺序执行语句，有些存储引擎会发生死锁，有些则不会。

死锁的产生有双重原因：有些是因为真正的数据冲突；有些是由于存储引擎的实现方式导致。

死锁发生之后，只有部分或者完全回滚其中一个事务，才能打破死锁。

事务日志 （WAL / Redo log）

事务日志可以帮助提高事务的效率。使用事务日志，存储引擎在修改表数据时只需要修改其内存拷贝，再把修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。事务日志采用追加的方式，因此写日志的操作是磁盘上一小块区域的顺序IO。事务日志吃酒后，内存中被修改的数据在后台可以慢慢刷回磁盘。该方式称为 (Write-Ahead Logging)，修改数据需要写两次磁盘。

InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么这块“粉板”总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写。有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。

MySQL中的事务

MySQL 默认采用自动提交(AutoCommit)模式，如果不是显示的启动一个事务，则每次查询都被当作一个事务执行提交操作。

修改AutoCommit参数，对于非事务型的表不会有任何影响。

有一些命令，在执行之前会强制执行COMMIT提交当前的活动事务，比如 alter table、lock tables等。

可以通过 set transaction isolation level 命令来设置隔离级别，新的隔离级别在下一个事务开始时生效。

mysql > set transaction isolation level read committed;

MySQL Server不管理事务，事务由存储引擎层实现，所以在用一个事务中，使用多个存储引擎是不可靠的。
如果在事务中混合了事务型与非事务型的表，在正常提交的情况下不会有什么问题。但是如果该事务回滚，非事务型的表上的变更就无法撤销，这会导致数据库处于不一致的状态，这种情况很难修复，事务的最终结果将无法确定。

两阶段锁协议

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，锁只有在执行 COMMIT 或 ROLLBACK 的时候才会释放。这个就是两阶段锁协议。

MySQL也支持 LOCK TABLES 和 UNLOCK TABLES，这是才服务器层实现的，和存储引擎无关。他们有自己的用途，并不能代替事务处理。

多版本并发控制 MVCC

todo P12

MVCC只在 REPEATABLE READ 和 READ COMMITTED 两个隔离级别下工作。其他两个隔离界别不兼容 MVCC，因为 READ UNCOMMITTED 总是读取最新的数据行，而不是符合当前事务版本的数据行，而 SERIALIZABLE 则会对所有读取的行都加锁。

InnoDB 概览

1. InnoDB 的数据存储在表空间中。独享表空间存储方式使用.ibd文件，并且每个表一个.ibd文件 共享表空间存储方式使用.ibdata文件，所有表共同使用一个.ibdata文件（或多个，可配置）。
2. InnoDB采用MVCC来支持高并发，并实现了4个隔离界别，默认级别是REPEATABLE READ（可重复读），并且通过间隙锁策略防止幻读的出现。间隙锁是的InnoDB不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，以防止幻行的插入。
3. InnoDB 基于聚簇索引建立，该索引对查询有很高的性能。不过其二级索引中必须包含主键列，如果主键列很大，其他的索引都会很大。
4. InnoDB 内部做了很多优化，包括从磁盘读取数据时采用的可预测性预读，能够自动在内存中创建hash索引以加速读操作的自适应哈希索引，以及能够加速插入操作的插入缓冲区。

MyISAM 特性

在MySQL5.1版本之前，MyISAM是默认的存储引擎，其提供了大量的特性，包括全文索引、压缩、空间函数（GIS）等，但是MyISAM 不支持事务和行锁，且不支持外键，而且有一个毫无疑问的缺陷就是崩溃后无法安全恢复。对于只读的数据，或者表比较小，可以忍受修复操作，则依然可以使用MyISAM。

1. 加锁和并发
   MyISAM对整张表加锁，不是针对行。读取是会对需要读到的所有表加共享锁，写入时对表加排它锁。但是在表有读取查询的同时，也可以往表中插入新的记录(并发插入)
2. 修复
3. 索引特性
   对于MyISAM表，即使是BLOB和TEXT等长字段，也可以基于其前500个字符创建索引。MyISAM也支持全文索引，这是一种基于分词创建的索引，可以支持复杂的查询。
4. 延迟更新索引键(Delayed Key Write)
   创建MyISAM索引的时候，如果指定了Delayed Key Write选项，在每次修改执行完成时，不会立刻将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区（in-memory key buffer），只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入到磁盘。这种方式可以极大地提升写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作。Delayed Key Write可以全局设置，也可以对单表设置。

MyISAM压缩表

如果表在创建并导入数据之后，不会再进行修改操作，那么这样的表或许适合采用MyISAM压缩表。

可以使用 myisampack 对MyISAM表进行压缩。压缩表不能被修改，（除非先将表解压缩，修改数据，然后再次压缩）。压缩表可以极大地减少磁盘空间占用，因此也可以减少磁盘I/O，以提升查询性能。压缩表也支持索引，但索引也是只读的。

Memory 引擎

如果需要快速访问数据，且这些数据不会被修改，重启以后丢失也没有关系，那么使用Memory表是非常有用的。Memory表比MyISAM至少快一个数量级，因为所有的数据都在内存中，不会产生磁盘I/O。数据库重启后，Memory表的数据会丢失，但表结构会保留。

如果MySQL在执行查询的过程中需要使用临时表来保存中间结果，内部会使用Memory表作为临时表。如果中间结果太大超出了Memory表的限制，或者含有BLOB或TEXT字段，则临时表会转换为MyISAM表。

第四章 Schema与数据类型优化

整数类型

整数类型包括：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT。分别使用8、16、24、32、64位存储空间。其存储值的范围从 -2^(N-1)到2^(N-1)-1，其中N是存储空间的位数。
整数类型有可选的 unsigned 属性，表示不允许负值，这大致可以使正数的上限提高一倍。
有符号与无符号类型使用相同的存储空间，并具有相同的性能，因此可以根据实际情况选择合适的类型。

MySQL会为整数类型指定宽度，例如INT(11)，对大多数应用这是没意义的：它不会限制值的合法范围，只是规定了MySQL的一些交互工具用来显示字符的个数。对于存储和计算来说，INT(1)和INT(20)是相同的。

实数类型

实数是带有小数部分的数字。也可使用DECIMAL存储比BIGINT还大的整数。MySQL既支持精确类型，也支持不精确类型。

Float和Double类型支持使用标准的浮点运算进行近似计算。Decimal类型用于存储精确的小数。在MySQL5.0以及更高版本中，MySQL自身实现了Decimal高精度计算。由于CPU直接原生支持浮点运算，所以浮点运算速度更快。
浮点和Decimal都可以指定精度。对于decimal列，可以指定小数点前后允许的最大位数。这会影响列的空间消耗。MySQL5.0以及更高的版本将数字打包保存到一个二进制字符串中（每4字节存9个数字）。例如Decimal(18,9)小数点两边将各存储9个数字，一共使用9个字节：小数点前后的数字各用4字节，小数点占用1字节。
浮点类型在存储同样范围的值时，通常比Decimal使用更少的空间。float使用4字节存储，double使用8字节存储，double相比float有更高的精度和更大的范围。
在数据量比较大的时候，可以考虑使用BIGINT代替DECIMAL，这样可以同时避免浮点存储计算不精确和DECIMAL精确计算代价高的问题。

VARCHAR 和 CHAR 类型

VARCHAR 和 CHAR如何存储在磁盘与内存中，与存储引擎的具体实现相关。
存储引擎存储 CHAR 或者 VARCHAR值的方式在内存和磁盘上可能不一样，所以MYSQL服务器从存储引擎读出的值可能需要转换另一种存储格式。

VARCHAR

VARCHAR 类型用于存储可变长字符串，比定长类型更节省空间，因为他仅使用必要的空间。但是，如果MYSQL表使用ROW_FORMAT= FIXED 创建的话，每一行都会使用定长存储，这会浪费空间。
VARCHAR 需要1至2个额外的字节记录字符串的长度；如果列的最大长度小于或等于255字节，则只用1个字节表示，否则使用2个字节。
由于VARCHAR行是变长的，在UPDATE时可能使行变得比原来更长，这就导致需要做额外的工作。如果一个行占用的空间增长，并且在页内没有更多的空间可以存储，在这种情况下，不同存储引擎的处理方式不一样。MyISAM会将行拆成不同的片段存储，InnoDB 则需要页分裂来使行可以放进页内。

VARCHAR 适用范围：字符串列的最大长度比平均长度大很多；列的更新很少，所以碎片不是问题；使用了像UTF8这样复杂的字符集，每个字符都适用不同的字节数进行存储。

InnoDB可以把过长的VARCHAR存储为BLOB。

在5.0或更高版本中，MySQL在存储和检索 VARCHAR时会保留末尾空格。但在4.1或更老的版本，MySQL会剔除末尾空格。

CHAR

CHAR是定长的，存储CHAR值时，MySQL会删除所有的末尾空格。CHAR值会根据需要采用空格进行填充以方便比较。

CHAR适用范围：CHAR适合存储很短的字符串，或者所有值都接近同一个长度。

数据如何存储取决于存储引擎，并非所有的存储引擎都会按照相同的的方式处理定长和变长的字符串。Memory引擎只支持定长的行，即使有变长字段也会根据最大长度分配最大空间。但是填充和截取空格的行为在不同存储引擎都是一样的，这是在Server层实现的。

适用VARCHAR(5)和VARCHAR(200)存储‘hello’的空间开销是一样的，那么使用短的列的优势是什么？
更长的列会消耗更多的内存，因为MySQL通常会分配固定大小的内存快来保存内部值。尤其是适用内存临时表进行排序或操作时会特别糟糕。在利用磁盘临时表进行排序时也同样糟糕。所以最好的策略是只分配真正需要的空间。