MySQL不权威指南

架构

mysql最重要、最与众不同的特性是它的存储引擎架构, 这种架构的设计将查询处理, 及其他系统任务和数据的存储/提取相分离. 这种处理和存储分离的设计可以在使用时根据性能、特性, 以及其他需求来选择数据存储方式

mysql_structure.png

图中包含的部分

• 连接池组件
• 管理服务和工具组件
• SQL接口组件
• 查询分析器组件
• 优化器组件
• 缓冲(Cache)组件
• 插件式存储引擎
• 物理文件

架构分成了服务器层和存储引擎两层, 两层的执行速度是存在量级的差别的, 比如索引查询都是由存储引擎执行, 而filesort等均在服务器层

mysql的架构是单进程多线程, 每个客户端连接都会在服务器进程中拥有一个线程, 这个连接的查询只会在这个单独的线程中执行

锁

锁是实现事务和并发的重要工具

读写锁

只要有多个查询需要在同一时刻修改数据(即多线程), 都会产生并发控制的问题, 解决并发常见的解决方案是实现读写锁系统
lock(有别于latch闩锁), 主要使用在事务中

• 读锁(read lock也叫 共享锁(share lock) S lock 允许事务读取一行数据, 兼容读锁, 不兼容写锁
• 写锁(write lock也叫 排他锁(exclusive lock) X lock 允许事务删除或更新一行数据, 不兼容读写锁

读锁的优先级低于写锁, 所在在队列中会被插队

锁粒度

锁冲突是影响并发的原因之一, 所以更加精细地对资源进行加锁, 会提供高并发性. 但这也意味着更多计算资源的消耗

• 表级锁(table lock) mysql最基本的锁, 开销最小, 会阻塞对表的所有写操作, 是在服务器层实现
• 行级锁(row lock) 行级锁可以最大程度地支持并发处理, 同时也带来了最大的锁开销. 行级锁只在存储引擎层实现, 而mysql服务器层没有实现. 服务器层完全不了解存储引擎中的锁实现

死锁

两个事务互相等待对方释放所需资源的锁, 导致事务阻塞, 解决办法一般有

• 回滚其中一个事务
• 对资源的添加加锁的顺序
• 悲观锁, 事先对可能用到的资源全部加锁

事务

事务简单来说就是一组查询要么全部执行, 要么全部不执行. 事务系统必须满足ACID原则:

• 原子性 (atomicity)
• 一致性 (consistency)
• 隔离性 (isolation)
• 持久性 (durability)

隔离性四个级别

1. read uncommitted 未提交读 事务中允许读到其他事务未提交的修改(dirty read 脏读), 这其实已经违反了隔离性的原则
2. read committed 提交读 事务中允许读到其他事务已提交的修改, 这符合基本的隔离性. 但存在幻读(phantom read), 即在同一个事务中读取同一资源可能出现不一致(被其他事务提交修改). 所以该级别也叫不可重复读
3. repeatable read 可重复读 该隔离级别保证了同一事务中重复读取同一资源结果是一致的. 但理论上来说, 这并不能解决幻行(phantom row), 即其他事务提交新增, mysql主要通过MVCC(多版本控制)来解决, 该级别是mysql默认隔离级别
4. serializable 可串行化 可重复读还不能避免应用程序级别的丢失更新, 即事务t1和事务t2在ci前都修改资源s, 都提交后(先拿到写锁的提交前, 后一个不能修改), 后提交的事务会覆盖先提交的, 所以从应用层面看, 更新"丢失"了. 这主要是事务并行执行造成的.

mysql提供两种事务型存储引擎: InnoDB、NDB Cluster

mysql默认采用自动提交模式. 也就是说, 如果不是显示的开始一个事务, 则每个查询都被当作一个事务执行提交

# 显示开启事务
start transaction \ begin;
...
rollback \ commit;

存储引擎

myisam

mysql最早的默认存储引擎, 现在默认为innodb

优点

• 数据存储设计简单、紧凑占用空间小, 数据插入速度快(更新由于表锁的原因, 高并发时并不快)
• 底层文件分为数据文件(.MYD)和索引文件(.MYI), 方便维护
• 由于数据与索引的分离, 同时会压缩索引, 可以一次性加载更多的索引到内存中
• 支持全文索引
• 支持地理空间搜索

缺点

• 只支持表级锁, 不支持行级锁
• 不支持事务
• 不支持外键
• 崩溃后不能自动修复, 无热备, 维护成本高
• 索引中的叶子节点保存的是数据物理位置, 所以会产生大量的随机i/o

innodb

目前mysql的默认存储引擎(自5.5.8), 其设计目标主要是面向在线事务处理(OLTP)的应用 (myisam主要面向一些OLAP数据库应用)

innodb的主键
innodb是基于聚簇索引建立的. 聚簇索引对主键查询有很高的性能. 但它的二级索引(非主键索引)中必须包含主键列, 所以如果主键列很大的话, 其他的所有索引都会很大. 因此, innodb的主键应该尽可能小

这也是为什么InnoDB为什么最好选择自增int型作为主键的原因之一; 另一个原因是使用递增型作为主键, 存储引擎在插入数据时, 不会出现裂页插入的现象, 因为是append添加数据

当未显性建立主键时, InnoDB会先按照索引定义顺序寻找非null唯一索引, 未找到则会隐性建立一个6字节的ROWID作为主键

优点

• 支持行锁
• 支持事务
• 支持外键
• 崩溃自动修复
• 热备份
• 表基于主键聚簇索引建立, 主键高并发查询性能好

缺点

• 数据库占用空间大
• 由于使用聚簇索引, 所以插入数据时可能导致裂页而造成性能下降

存储引擎选择

大部分情况下, InnoDB都是正确的选择, 除非需要某些InnoDB不具备的特性, 并且没有其他办法可以代替, 否则都应该使用InnoDB引擎

几个方面考虑

• 事务, 需要事务直接InnoDB
• 备份, 需要热备直接InnoDB
• 崩溃恢复, 直接InnoDB
• 特有特性, 地理空间搜索只有myisam支持, 全文索引myisam支持, InnoDB最新版也支持

事例

• 日志应用 写(add多 update少)多读少, 同时决不能成为瓶颈, 耗费存储, 基本不更新数据, 适合采用myisam
• 订单应用 需要事务支持, InnoDB

mysql主要存储引擎对照表

mysql_engine_compare.png

修改存储引擎

• alter table 最简单的办法, 适用任何存储引擎, 但是需要很长时间, 因为mysql会按行将数据从原表复制到一张新的表中, 在复制期间可能会消耗系统所有的I/O能力
  mysql> ALTER TABLE mytable ENGINE = InnoDB;
• 导入与导出 适用mysqldump工具导出数据到sql文件, 然后对该文件进行一定修改进行导入. 注意create前默认加入的drop table
• 创建与查询 (create和select)结合了第一种的快, 和第二种的安全. 创建一个新的索引表, 然后利用 Insert...select语法导入数据
  mysql> CREATE TABLE innodb_table LIKE myisam_table;
  mysql> ALTER TABLE innodb_table ENGINE=InnoDB;
  mysql> INSERT INTO innodb_table SELECT * FROM myisam_table;
• 数据量不大时这样可以很好工作, 数据量大时可以使用分批处理+事务
  mysql> START TRANSACTION;
  mysql> INSERT INTO innodb_table SELECT * FROM myisam_table WHERE id BETWEEN x AND y;
  mysql> COMMIT;

混合使用存储引擎
在mysql事务中混合使用存储引擎会使问题非常复杂, 比如同时使用了支持和不支持事务的存储引擎, 又需要rollback

数据库schema

类型选取原则

• 更小的通常更好 尽量使用可以正确存储数据的最小数据类型(同时也要兼顾以后的维护和扩展). 更小的数据意味着更少的磁盘、内存、CPU缓存和CPU处理周期
• 简单就好 优先使用mysql内建类型. 如使用date类型存储日期就比varchar类型效率要高, 又如使用整型存储IP地址
• 尽量避免NULL 注意NULL是默认属性, 不需要NULL时要显示指定 not null. 包含null的列会更加难以处理, 耗费更多空间. 但是也不要矫枉过正, 确实需要null时也不要吝啬

整数

类型	字节	bit
tinyint	1	8
smallint	2	16
mediumint	3	24
int	4	32
bigint	8	64

表示的范围
-2^(N-1) ~ 2^(N-1)-1
0 ~ 2^(N)-1 unsigned

注意: mysql可以为整数类型指定宽度, 如int(11), 但这基本无意义, 不会影响存储空间和存储范围, 只是规定了mysql的一些交互工具用来显示字符的个数

字符串

• char 存储定长
• varchar 存储变长字符串

char(8) varchar(8)都表示8个字符, 跟编码无关

varchar实际占用字节计算
实际占用字节为: varchar长度 * 编码字节 + 长度记录
长度记录在varchar小于255字节为1, 大于255字节为2, varchar最大字节为65535, 即当采用latin编码时最多存65533个字符, utf8时为2万多

慷慨是不明智的
varchar(6) varchar(200)用于存储 hello 时的耗费是一致的, 那么使用短列有优势吗？
事实证明是有很大优势的. 更长的列消耗更多内存, 尤其使用内存临时表排序. 最好的策略是只分配真正需要的空间

使用枚举类型(enum)代替字符串
当字符串是固定类型的情况时, 可以使用enum类型代替char/varchar, 存储空间小, 因enum内部使用整型, 计算速度快

但需要添加新的元素时, 除非是append(末尾添加), 否则将更新整个表(由于映射关系变化); 其次排序时时按照enum的顺序, 而非字符串本身, 容易造成误解

日期

mysql中提供了多种时间类型, year date datetime time timestamp等

datetime
表示范围1001年到9999年

timestamp
表示范围 1970年1月1日(格林尼治标准时间)至 2038年
mysql提供了from_unixtime()把时间戳转换日期, 和unix_timestamp()转换日期为时间戳
可以设置插入或更新数据时的行为
默认情况下尽量使用timestamp, 如果需要处理毫秒级数据可以使用bigint进行hack, 详见4.1.4

主键类型选择

优先使用合适大小的自增整数类型

合适大小可以减小空间, 加快计算速度;自增可以使插入更加高效;(当使用随机值作为主键时比如md5, 会导致索引平均分布, 各种裂页)

enum、set、字符串尽量避免作为主键

IP存储

不要使用varchar(15)来存储ip地址, 最好使用unsinged int, 结合inet_aton() inet_ntoa()

加快alter table操作

mysql的alter table操作对于大表来说是很是个问题, 因为mysql的做法是用新的结构创建一个空表, 然后将数据插入新表, 然后删除旧表. 这将耗费大量时间, 同时阻塞服务

并非所有alter table操作都会导致重建表, 部分会导致只修改.frm文件(很快)

其他奇技淫巧详见4.5

索引

索引的类型

索引是由存储引擎实现, 具有很多类型, 而且相同类型的索引不同存储引擎可能实现不同

• BTree索引 最常见的索引, 大部分存储引擎都支持. 使用B+Tree数据结构, 数据有序且平衡存储, 叶子节点距离顶部的距离都一样, 索引真实数据存储在最后一层
• 哈希索引 只有Memory引擎显式支持哈希索引. 不能实现覆盖索引, 不能利用索引排序, 不支持范围查询, hash冲突多的话维护成本高. 但hash索引访问速度快
• 空间数据索引 (R-Tree)myisam支持, 适用于match against操作, 而不是普通的where

myisam与InnoDB实现BTree索引

myisam使用了前缀压缩索引实现BTree索引, 索引更小, 同时索引存储的是数据的物理位置

InnoDB保存了完整索引, 同时使用主键索引数据位置(这就解释了为什么InnoDB始终会创建一个整型的主键索引树;以及为什么InnoDB的主键要小一点;)

不能使用索引的情况

• 不是按照索引的最左列开始查找;
• 不能跳过索引中的列;
• 如果查询中的某个列是范围查询, 则其右边的所有列都无法使用索引;比如like, between
• 索引列上使用函数, 或者算数运算

索引的优点

索引可以让服务器快速定位数据位置, 但这并非索引唯一作用. 根据索引的数据结构, 还有其他优点:

• 索引大大减少服务器需要扫描的数据量
• 索引可以帮助服务器避免排序和临时表
• 索引可以将随机I/O变为顺序I/O

三星索引

1. 索引将相关的记录放到一起则获得一星
2. 索引中的定义顺序和查找中的排序顺序一致则获得二星
3. 索引中列包含了查询中需要的全部列则获得三星

前缀索引和索引选择性

前缀索引, 是为了减少索引的长度, 而指定了索引列的索引长度

mysql> ALTER TABLE table ADD KEY (city(7));

• 优点 减小索引长度, 存储空间减少, 可以放入更多索引至内存
• 缺点 不能使用索引进行order by和group by;无法使用覆盖索引;

另外, 前缀索引的长度的选择需要考虑索引选择性, 因为选择性很差的索引还不如不使用索引试探索引选择性:

    mysql> SELECT
    mysql> count(distinct left(city,3))/count(*) as sel3,
    mysql> count(distinct left(city,4))/count(*) as sel4,
    mysql> count(distinct left(city,5))/count(*) as sel5
    mysql> FROM table;

多列索引

常见错误

为每个列单独创建一个索引, 这样最多只能获得“一星索引”. 而且这样并不能有效提高查询性能. 即便是mysql使用了“索引合并”策略(using union/intersection(col1, col2))

虽然索引合并是一种优化策略, 但一般出现时也说明了表上索引建的很糟糕:
出现利用多个列进行相交操作时, 通常意味着需要一个包含相关列的多列索引, 而不是多个单列;

出现利用多个列进行合并操作时, 通常要耗费大量CPU;可以考虑使用UNION, 或者不使用索引

聚簇索引

聚簇索引的叶子节点保存的就是数据本身. 所以, 聚簇索引就是表本身

优点

• 可以把相关数据保存在一起
• 数据访问更快, 因为数据和索引存在一块
• 索引扫描时无需回表, 因可以直接使用主键值

缺点

• 假如能够将所有数据放入内存中, 则聚簇索引便无意义了, 因其提供的I/O优势不存在
• 插入速度严重依赖插入顺序. 比如InnoDB中最差的, 按照主键逆序插入, 会导致大量的裂页
• 更新聚簇索引代价高, 因为涉及的行会全部更新位置
• 会导致全表扫描变慢, 尤其行比较稀疏
• 由此导致产生的二级索引会比想象的大, 因为其必须包含主键索引
• 二级索引查找需要两次索引查找, 因为二级索引中保存的是主键而不是数据

InnoDB与myisam比较

myisam使用的是堆存储(存疑？), InnoDB使用的是聚簇索引, 所以他们的索引分布是这样的

cluster_index.png

这也就解释了, myisam中的二级索引为什么和主键索引差别不大, 而InnoDB中主键索引和二级索引差别比较大

覆盖索引

由于mysql的索引可以获取到索引列的值(BTree决定的, 叶子节点最后一层有所有索引值), 所以当索引列包含了查找的全部列时, 我们称之为覆盖索引

优点

• 不需要回表, 也不需要二次查询
• 当数据库只缓存索引时, 也同时缓存了数据

不能使用

• 没有任何索引能够覆盖这个查询
• mysql不能在索引中执行like

索引扫描排序

因为索引是有序的, 所以当order的列存在索引时, 可以通过扫描索引来进行排序

explain中的type列为'index', 则说明使用了索引扫描, 因为索引是排序的, 所以也可理解为使用了索引扫描排序

type中的index, 和extra中的 using index区别是
前者为利用index执行了full table scan;后者为使用了覆盖索引(即没有进行全表查询)

导致不能进行索引扫描排序

• order的列升降序不同
• order中存在不在索引中的列
• where和order中列不能组合为最左前缀
• where中存在范围查询(等值不会导致失效, 因为等值的话相当于忽略该索引, 但是范围的话, 则导致范围索引后续索引无法区分顺序而失效)

小技巧

索引为 col1_col2_col3, 而查询 col1, col3, 此时可以通过添加 col2的恒等式引导mysql使用该索引

查询

mysql客户端/服务器通信协议

1. 半双工 意即任何时刻, 要么由服务器向客户端发送数据, 要么由客户端向服务器发送数据
2. “从消防水管喝水” 当客户端从服务器拉取数据时就是这样的感受, 不能停止, 除非粗暴的断开连接, 但这并不是好做法

为什么查询速度会慢

查询速度指的是 响应时间 如果把查询看做一个任务, 那么它是由一些列子任务组成. 查询的优化, 就是优化这些子任务, 要么减少子任务执行次数, 要么让子任务运行的更快

查询的生命周期大致可以按照顺序分为从客户端, 到服务器, 然后在服务器上进行解析, 生成执行计划, 执行, 并返回结果给客户端. 执行 可以认为是整个生命周期中最重要的阶段, 这其中包括了大量为了检索数据到存储引擎的调用以及调用后的数据处理, 包括排序、分组等.

在完成这些任务的时候, 查询需要在不同的地方花费时间, 包括网络, CPU计算, 生成统计信息和执行计划、锁等待(互斥等待)等操作, 尤其是想底层存储引擎检索数据的调用操作

mysql检索了不必要的数据

1. 确认查询中没有多余的行、列, 即select的列要精心挑选, 同时where语句也要反复斟酌
2. 利用explain确保mysql没有分析大量超过需要的数据行; 比如explain中扫描的数据行和返回的行数比

扫描行数和访问类型

explain中type说明了mysql使用的扫描类型, 性能由差到好依次

1. All 全表扫描
2. Index 全索引扫描
3. range 索引范围扫描
4. eq_ref 基于主键或者唯一索引扫描
5. const 基于主键或者非null唯一索引扫描, 结果最多返回一条数据
6. system 查询对象表只有一条数据, 是const的特殊情况

mysql使用where

mysql过滤数据时性能由好到此依次为

1. 直接在做索引中使用where条件来过滤
2. 使用覆盖扫描(Extra列中出现 Using index)来返回数据, 直接在索引中过滤数据, 不需要回表
3. 从数据表中返回数据, 然后过滤不满足条件的记录(Extra列中出现 Using Where), 这种情况就比较惨了, 需要读出所有数据然后再在mysql服务层过滤

谨慎使用复杂查询

当网络环境良好, 机器运算速度足够时, 不必总是强调在一条查询中尽可能多或精准的返回结果, 因为这意味着更长的锁表时间、更复杂的执行计划、更加困难的维护

1. 切分查询 将一个需要长时间执行的查询划分为多次, 更长的时间片中执行
2. 分解查询 将复杂的查询分解为多个简单的查询, 这样会带来很多好处, 诸如可以更加高效利用的缓存, 更高的并发, 这相当于在应用层进行哈希关联, 有时会比mysql跟家高效

查询状态

mysql的每个连接任何时刻都有一个状态, Show full processlist查看

• Sleep 线程正在等待客户端发送新的请求
• Query 线程正在执行查询或者正在将结果发送给客户端
• Locked mysql服务器层, 该线程正在等待表锁(存储引擎实现的锁并不会在此反映如行锁). 对于myisam来说这是一个比较典型的状态, 但在其他没有行锁的引擎中也经常出现
• Analyzing and statistics 线程正在收集存储引擎的统计信息, 并生成查询的执行计划
• Copying to tmp table [on disk] 线程正在执行查询, 并且将结果集都复制到一个临时表中, 这种状态一般要么是在做group by操作, 要么是在file sort, 或者union;on disk标记表示mysql正在将一个内存临时表放到磁盘上
• Sorting result 线程正在对结果集进行排序
• Sending data 多种情况: 可能是线程在多个状态之间传送数据;或者生成结果;或者向客户端返回数据

mysql如何进行filesort

应尽可能避免排序, 或者尽可能使用索引排序, 或者当必须在服务器层进行filesort(内存或磁盘)尽可能减少需要排序的数据量

mysql目前有两种排序算法

1. 两次传输排序 (旧版本使用)首先排序数据引用, 然后再进行数据的读取, 这会产生大量随机I/O, 成本非常高
2. 单次传输排序(新版本使用)直接将数据进行排序, 不需要回表, 适合I/O密集型的应用, 但会占用大量空间

很难说哪个算法效率更高, 两种算法都有各自最好和最糟的场景. 当查询需要所用的列长度未超过参数max_length_for_sort_data, mysql使用单次排序

mysql查询优化的限制

1. 关联子查询 mysql的子查询实现的非常糟糕. 其中最糟糕的一类是where条件中包含in()的子查询, mysql会将相关的外层表压到子查询中, 此时最好使用inner join改写查询或者exist
2. Union限制 mysql不能将限制条件从外层下推到内层, 比如limit语句, 必须分别在子句和查询语句的最后添加, order by同理
3. 其他详见6.5.3 索引合并优化、等值传递、不能进行并行执行、不能哈希关联、松散索引扫描、最大值、最小值、同一个表同时进行查询和更新

count

1. count(col1) 统计某个列值的数量(不统计NULL)
2. count(express) 统计表达式有值的结果数
3. count(*) 统计结果集行数
   *并不会像我们猜想的那样扩展成所有的列, 它会忽略所有的列而直接统计所有行数
   这里的一个常见错误就是, 在括号内指定了一个列却希望统计结果集的行数, 这样意义不明确, 而且性能更差
   另一个误解是myisam的count( )函数总是非常快, 这是有前提条件的, 只有没有任何where条件的count(*)才非常快, 否则和其他引擎一样

小技巧

1. 当count需要扫描大量数据时, 可以尝试求其补集
2. 使用近似值
3. 使用统计表

group by & distinct

很多场景下, mysql都使用同样的办法优化这两种查询, 事实上, mysql优化器会在内部处理的时候相互转换这两类查询. 它们都可以使用索引来优化, 这也是最有效的优化办法, 当无法使用索引时会使用filesort

1. 当分组列是其他表的是外键(比如pluto.phone.product列), 可以使用inner join来排序product_id
2. 如果没有通过order by子句显式地指定排序列, 当查询使用group by子句的时候, 结果集会自动按照分组的字段进行排序. 如果不关心结果集的顺序, 而这种默认排序又导致了需要文件排序, 则可以使用order by null让mysql不再进行文件排序. 也可以在group by子句中直接使用desc asc使分组的结果集按需要的方向来排序

Limit

limit分页主要的问题在与当offset非常大时, mysql需要遍历大量数据, 所以优化limit要从offset着手

1. 直接记录上次limit时的offset
2. 改写查询为子查询, 在子查询中利用索引找到offset, 然后再在主查询中利用where精准定位到offset
   尽量使用更小的索引, 或者更小的列来定位offset

Union

除非确实需要服务器消除重复行, 否则就一定要使用Union All, 否则会做distinct, 这将导致filesort

静态查询分析

pt-query-advisor能够解析查询日志、分析查询模式, 然后给出所有可能存在的潜在问题的查询, 并给出足够详细的建议

视图

虚拟表, 不存放任何数据, 与其他表处在同一命名空间, 基本与其他表无异

CREATE VIEW Ocenian AS
    SELECT * FROM Country WHERE Continent = 'Oceania'
    WITH CHECK OPTION;

with check option
表示任何通过视图更新的行, 都必须符合视图本身定义的where条件定义, 即不能更新后导致视图数据“消失”

视图实现

mysql使用两种两种算法实现视图, 尽量使用前者

1. 合并算法, 即整合查询语句
2. 临时表算法, 即使用临时表, 比如视图中包含聚合函数, 此时视图不可更新

视图的用处

1. 便于查询监控, 简化一些应用逻辑
2. 在底层数据库修改时, 可以利用视图“欺骗”应用程序
3. 更加灵活的权限管理

视图的限制

1. 视图临时表目前不能使用任何索引
2. mysql暂时不支持物化视图, 但可以利用一些技巧实现 7.2.3

外键

InnoDB是目前mysql唯一支持外键的内置存储引擎

外键虽然可以保持数据的一致性, 减少维护操作;但很容易成为性能瓶颈, 使用外键时要充分测试

查询缓存

mysql查询缓存保存查询返回的完整结果. 当查询命中该缓存, mysql会立刻返回结果, 跳过了解析、优化和执行阶段

缓存可能成为整个服务器的资源竞争点, 在多核服务器上还可能导致服务器僵死, 查询缓存默认是关闭的

判断命中

1. 查询缓存系统会跟踪查询中涉及的每个表, 如果这些表发生任何变化, 那么和整个表相关的所有缓存数据都将失效
2. 通过查询语句hash判断命中, 查询语句的任何变化都将导致未命中, 包括注释和空格
3. 查询中存在不确定函数

常用配置

• query_cache_type 是否打开查询缓存, off | on | demand
• query_cache_size 可使用总内存字节
• query_cache_min_res_unit 查询缓存分配内存时最小单位
• query_cache_limit mysql能够缓存的最大查询结果
• query_cache_wlock_invalidate 涉及的表有写锁时是否仍然使用缓存

通用优化

• 用多个小表代替一个大表
• 批量写入可以有效降低缓存失效次数
• 设置较小的缓存大小(query_cache_size)
• 密集写应用, 直接关闭查询缓存
• 查询缓存最高境界不去执行, 再高境界不发送查询到mysql, 利用应用程序或者第三方缓存

配置

(本段只记录我能看懂的或者感觉可能会涉及的, 更详细完整的见 chapter 8)

mysql有大量可以修改的参数, 但不应该随便去修改;

通常只需要把基本的项配置正确, 避免过度配置;不要追求“完美”, 建议在“足够好”时候就可以停下来, 除非有理由相信停下来会导致放弃重大的性能提升机会

应该把更多的精力花在schema的优化、索引以及查询的设计上

不要面向懵逼 “调” 优, 因为不断尝试出来的看似最优值, 也许只是当前状态下起作用;应该通过监控服务状态, 从整体考虑出发, 进行 “配置”

基本

一定要确认配置文件位置

确定mysql启动程序

$ which mysqld
/usr/sbin/mysqld

确定mysql所读取的配置文件

$ /usr/sbin/mysqld --verbose --help | grep -A 1 'Default options'
Default options are read from the following files in the given order:
/etc/mysql/my.cfn ~/.my.cnf /usr/etc/my.cnf

语法

配置项设置都使用小写, 单词之间用下划线或者横线隔开, 比如以下两条等价

/usr/sbin/mysqld --auto-increment-offset=5
/usr/sbin/mysqld --auto_increment_offset=5

作用域

不同的配置项具有不同的作用域, 有的是全局, 有的只是会话级, 作用在线程

注意在线程中修改全局变量也许不会生效, 有些变量是在mysql启动时才读取一次;所以重要的配置要写入配置文件或者使用脚本启动, 避免遗漏

副作用

动态修改完配置后要及时恢复, 比如为每个线程分配的内存

创建配置

mysql可配置性太强也可以说是一个弱点, 其实大多数配置的默认值已经是最佳配置了, 所以最好不要改动太多

以下创建了一个完整的最小示例配置文件, 可以作为一个良好的起点, 不要以自带配置文件作为起点

此配置文件可能比你见过的其他配置文件太少了, 但实际上已经超过了许多人的需要, 请确保基本了解这些配置的意义

[mysqld]
# General
datadir                 = /var/lib/mysql
socket                  = /var/lib/mysql/mysql.sock
pid_file                = /var/lib/mysql/mysql.pid
user                    = mysql
port                    = 3306
default_storage_engine  = InnoDB

# InnoDB
innodb_buffer_pool_size = <value>
innodb_log_file_size    = <value>
innodb_file_per_table   = 1
innodb_flush_method     = 0_DIRECT

# myisam
key_bufffer_size        = <value>

#Logging
log_error               = /var/lib/mysql/mysql-error.log
slow_query_log          = /var/lib/mysql/mysql-slow.log

# Other
tmp_table_size          = 32M
max_heap_table_size     = 32M
query_cache_type        = 0
query_cache_size        = 0
max_connections         = <value>
thread_cache            = <value>
table_cache             = <value>
thread_cache            = <value>
open_files_limit        = 65535

[client]
socket                  = /var/lib/mysql/mysql.sock
port                    = 3306

oepn_file_limit 在典型的Linux系统上我们把它设置得尽可能大. 现代操作系统中打开文件句柄的开销都很小. 如果这个参数不够大, 将会碰到经典的24号错误, “打开的文件太多(too many open files)”

配置mysql内存使用

总体按照以下步骤逐步考虑

1. 给操作系统留够内存, 5%或者2G, 取最大值
2. 然后考虑mysql能够使用的最大值, 注意mysql由于是单进程多线程架构, 所以也受限于进程的最大内存, 甚至也受限于编译器的最大内存限制
3. 然后考虑mysql每个连接线程的内存, 正常情况下每个线程大概消耗255kb的内存, 但当查询使用了内存表等情况时会猛增;所以在分配内存时要考虑到最坏情况下 max_connections * 单个线程最大值, 当然这是极限情况, 可以在其基础上适当减少
4. 其余内存尽量分配给缓存, 依次考虑
   1. InnoDB缓冲池
   2. InnoDB日志文件和myisam数据的操作系统缓存
   3. myisam键缓存
   4. 查询缓存
   5. 无法手工配置的缓存

当配置缓存的时候要谨慎保守, 不要配的过大. 因为配置小了可能影响部分性能, 但如果配置大了, 则可能导致严重的问题: 内存交换、磁盘抖动、甚至耗尽内存和硬件死机

InnoDB缓冲池(innodb_buffer_pool_size)

InnoDB严重依赖缓冲池, 必须为它分配足够内存

缓冲池不仅仅缓存索引, 它还会缓存行的数据、插入缓冲、锁, 以及其他内部数据结构

当然缓存池过大也存在挑战, 预热时间长, 一旦自动恢复数据, 往往需要数小时或者数天

myisam键缓存

缓存索引(不缓存数据, myisam数据缓存依赖操作系统), 所以最大值是可以将所有索引缓存即可

线程缓存(thread_cache_size)

即数据库连接池, 一般不需要修改, 除非服务器会有很多连接请求

建议根据 threads_connected 系统变量来动态设置线程缓存大小
比如 threads_connected 保持在100~120, 则线程缓存设置20左右, 如果500~700, 200则足够大了

设置过大也没有必要, 过小也不会节省多少内存

表缓存

与线程缓存概念类似, 但是缓存对象是表对象, 即表结构进行解析后的数据对象

mysql I/O配置 详见8.5

InnoDB的I/O配置比较重要的是 事务日志 的配置(innodb_log_file_size), 一般日志文件配置为4G即可

事务日志的缓冲区不需要过大(innodb_log_buffer_size), 1 ~ 8Mb即可

并发配置

innodb_thread_concurrency 限制一次性可以有多少线程进入内核, 0为不限制

并发值 = CPU数量 * 磁盘数量 * 2

myisam可以通过 concurrent_insert

• 0 myisam不允许并发插入, 所有插入都会添加表互斥锁
• 1 默认值. 只要表中没有空洞(逻辑删除的行), 就允许并发插入
• 2 强制并发, 并插入表末尾, 会使表更加碎片化

安全及其他基本配置

• expire_logs_days 二进制文件过期时间, 建议7~14天, 前提是开启二进制日志(会很大)
• max_allowed_packet 防止服务器发送太大的包
• max_connect_errors 连接的最大出错次数
• skip_name_resolve 跳过dns检查
• sql_mode sql的模式, 各种方言
• tmp_table_size max_heap_table_size 临时表的大小, 一般32M, 防止过大
• max_connections 默认100, 可以观察 Max_used_connections状态变量来制定
• thread_cache_size 观察Thread_connected
• table_cache_size 观察Opened_tables

备份与恢复

数据库的备份与恢复的目的

• 灾难恢复 不解释
• 需求变更 需要恢复到之前某个时间点重新开发
• 测试 线下环境
• 审计 法律、复查等需求

备份经常在无形中被设为重点, 因为在备份与恢复中“备份”在前, 其次备份经常发生, 开发人员也经常优化备份, 而恢复只是紧急情况下才会需要
但是不要忘了, 恢复才是根本目的, 如果没有经常性的演习恢复和良好的文档交接, 一旦紧急情况发生, 再好的备份恐怕也将失去作用

备份方案制定

1. 离线备份 or 在线备份
这将极大的决定备份方案的难易程度
在线备份的话情况就相当复杂了, 需要考虑数据的一致, 以及在线备份/还原时对服务性能的影响

2. 确定RPO(备份时间点目标)and RTO(备份时间目标)
RPO 需要你确定系统能够忍受的丢失的数据量(一天？一小时？一次事务？还是零容忍？)
RTO 需要你确定系统能够忍受的还原时间成本(无缝还原？停服务还原？数小时的还原时间？)

3. 逻辑备份 or 物理备份

逻辑备份优点

• 逻辑备份是纯文本, 能够方便的查看和编辑
• 恢复简单
• 可以通过网络来备份和恢复
• 不需要底层文件系统权限
• 灵活, 比如mysqldump的 -where 选项
• 跨存储引擎

逻辑备份缺点

• 必须由mysql服务器来完成备份生成, 消耗更多CPU周期
• 逻辑备份可能比数据库本身更大, 因为ASCII形式没有数据库紧凑
• 无法保证导出后再还原出来的一定是同样的数据, 这是因为浮点表示、软件bug等, 并不常见
• 恢复速度难以保证, 也许会很长, 这是逻辑备份比较大的问题, 所以要经常测试逻辑备份的恢复时间

物理备份优点

• 基于文件的物理备份, 只需要将需要的文件复制到其他地方即可完成备份. 不需要其他额外的工作来生成原始文件
• 物理备份的恢复可能就更简单了, myisam只需要简单的复制文件到目的地即可, innodb则需要停止数据库服务, 可能还需要采取其他一些步骤
• 恢复更快, 要比逻辑快一个数量级, 因不需要执行SQL和构建索引

物理备份缺点

• InnoDB的原始文件要比逻辑备份的大很多(所以数据库文件和备份到底谁大要具体分析), 因为InnoDB的表空间往往包含很多未使用的空间. 还有一些数据存储以外的用途, 如插入缓存, 回滚
• 物理备份由于牵扯服务器文件系统, 跨平台时可能遇到问题

建议物理备份与逻辑备份组合使用, 比如使用物理备份作为数据库起点, 然后更加细粒度的逻辑备份进行备份

4. 备份what

• 非显著数据, 指那些容易被忽略的如二进制日志、InnoDB事务日志
• mysql中的代码, 如存储过程、自定义函数
• 配置, 如果有一定的拓扑结构不要忘了其他节点
• 操作系统一类的外部配置, 如crontab任务、用户权限配置、管理脚本

5. 增量备份 or 差异备份
(添加示意图)
建议混合使用全量与增量

6. 二进制日志日志的管理和备份
非常重要, 记录着服务器执行的所有sql, 如果有某个时间点的数据备份和从那时以后的二进制日志, 就可以通过重放来恢复这之间的所有变更

二进制日志相比数据库文件要小很多, 适合频繁备份

二进制日志的有效期是使用expire_log_days来控制的, 这个时间建议结合备份策略来设置, 保证至少能够从最近两次备份中回放, 比如备份周期是一天, 那至少要保存两天的二进制日志(越多越好)

(二进制回放实验)

执行备份

逻辑备份
1. mysqldump
mysqldump比较常用, 常用选项 -q (不进行缓存) -where --table --lock-all-tables,-x(锁表) --skip-lock-table
2. select into outfile 这样产生的CSV比mysqldump要更快更小

        select * into outfile '/tmp/t1.txt'
        fields terminated by ',' optionally enclosed by '"'
        lines terminated by '\n'
        from test.t1

        load data infile '/tmp/t1.txt'
        into table test.t1
        fields terminated by ',' optionally enclosed by '"'
        lines terminated by '\n'

    但是outfile和infile要是mysql服务器上的文件;还要有文件写权限;不能覆盖(安全考虑, 无关权限);

物理备份
文件系统快照 (没看懂, 有实践经验才来总结吧)

执行恢复

逻辑备份恢复

    $ mysql < backup.sql

    \# 最好关闭二进制记录, 这样效率更高
    mysql> set SQL_LOG_BIN = 0;
    mysql> source backup.sql
    mysql> set SQL_LOG_BIN = 1;

    \# 如果压缩过, 则不要将解压、导入分开, 这将会消耗额外的资源, 而且要慢一些
    $ gunzip -c backup.sql.gz | mysql

    \# 如果想用source 或则 load file 进行解压和导入, 要借助命名管道
    $ mkfifo /tmp/backup/default/test.fifo
    $ chmod 555 /tmp/backup/default/test.fifo
    $ gunzip -c backup.sql.gz > /tmp/backup/default/test.fifo

    \# 管道会等待, 直到其他层序打开它并从另外一端读取数据
    mysql> set SQL_LOG_BIN = 0;
    myslq> load data infile '/tmp/backup/default/test.fifo'
    myslq> into table test;

    \# 然后删除命名管道

物理备份恢复 (wait...)

基于时间点的恢复

对mysql进行基于时间点的恢复常见的方法是还原最近一次全备份, 然后从哪个时间点开始重放二进制日志(有时叫做 前滚恢复)

主要缺点是二进制日志重放可能是个很慢的过程

这里来恢复一个有害操作drop table

1. 关闭数据库, 防止更多的操作
2. 恢复最近的时间点数据库
3. 添加如下配置禁止正常的连接
   skip-networking
   socket=/tmp/mysql_recover.sock
4. 启动mysql
5. 利用grep找到问题语句
   # mysqlbinlog --database=test /val/log/mysql/mysql-bin.000215 | grep -B3 -i 'drop table test.tar'
   # at 352
   # 070919 15:11:23 server id 1 end_log_pos 429 ...
   ...
   drop table test.tar;
6. 目标是跳过352位置, 直接从吓一跳429开始执行
   # mysqlbinlog --database=test /var/log/mysql/mysql-bin.000215 --stop-position=352 | mysql -uroot -p
   # mysqlbinlog --database=test /var/log/mysql/mysql-bin.000215 --start-position=429 | mysql -uroot -p
7. 检查数据, 修改配置3, 重启mysql

文件

日志

mysql中常见的日志文件

• 错误日志 error log
• 二进制日志 binlog
• 慢查询日志 slow query log
• 查询日志 log

此外还有事务相关的redo, undo日志

套接字文件

保存mysql的socket, 连接本机mysql时可以使用socket文件, 性能较好

pid文件

保存mysql运行时进程id

表结构文件

.frm文件, 保存数据表结构信息