高性能MySQL 笔记

第一章 基础

MySQL存储引擎、服务架构

1. 查询与数据存储/提取分离
2. 服务器通过API与存储引擎通信，接口屏蔽了不同存储引擎实现
3. 服务架构分三层
   1. 连接客户端。用户信息认证、安全等处理
   2. SQL解析、分析、优化、缓存层
   3. 存储引擎层
4. 客户端连接。采用SSL（安全套接字）方式连接需要认证证书。一个客户端连接对应于一个服务端线程，服务端有线程池来维护这些线程。

并发控制

1. 使用锁的方式。锁的使用使数据被串行化处理，并不支持并发处理
2. 处理并发读或写的经典方案是使用读写锁，又叫共享锁和排它锁
3. 读锁相互不阻塞，写锁会阻塞其他读锁和写锁
4. 锁粒度。锁的粒度越小，操作锁使用的资源越多，越影响性能。
5. 锁策略：在锁的开销和数据安全性间做平衡。商业数据库常用的锁策略是在表上施加行级锁。
6. 表锁是MySQL中最基本的锁策略，性能优先。行级锁，InnoDB实现了行级锁，行级锁只在存储引擎层实现。

事务

1. 事务是一组原子性的SQL查询，即要么全部执行成功，要么全部执行失败
2. 事务四个特点：ACID，A - Atomicity，C - Consistency，I - Isolation，D - Duration
3. 事务隔离级别
   1. SQL标准定义了四个隔离级别：读取未提交的数据（脏读）、读取提交的数据、可重复读、串行执行。大多数数据库默认的隔离级别是 read-committed，但MySQL不是，InnoDB默认隔离级别是 Repeatable read。
4. 死锁。两个或多个事务在相同的资源上相互占用，并尝试请求锁定对方占用的资源。
   1. 原因是当多个事务试图以不同的顺序锁定资源时会造成死锁
   2. InnoDB处理死锁方法：将持有最少行级排它锁的事务回滚。
   3. 死锁发生时只能回滚事务才能打破死锁。
5. MySQL中的事务
   1. MySQL提供了两种事务型存储引擎，InnoDB和NDBCluster
   2. MySQL服务器层不管理事务，事务由下层存储引擎层实现的。所以在同一个事务中使用多种存储引擎是不可靠的。例如，混合使用了事务型和非事务型表，回滚数据时不能回滚非事务型表数据，使数据库状态不一致。
6. InnoDB采用两阶段锁定协议。存储引擎采用隐式锁定，服务器可显式锁定。（for update）

多版本并发控制

1. MySQL事务型存储引擎实现：行级锁 + MVCC。MVCC只在提交读和可重复读两个隔离级别下工作

存储引擎

1. 采用InnoDB存储引擎，MySQl会将数据库保存到 data 目录下的子目录中，每个表都会有一个对应的 .frm 文件与之对应
2. InnoDB默认隔离级别是可重复读（Repeatable Read）
3. InnoDB 表是基于聚簇索引建立的。聚簇索引对主键查询有很高的性能。InnoDB的二级索引中必须包含主键列，所以如果主键列很大，所有索引都很大，如果表上索引较多，主键应尽可能小。
4. 非事务型引擎：MyISAM
5. 转换表存储引擎
   1. 直接使用 ALTER table 命令。这种方法操作简单，但执行时间很长，MySQL会按行将数据复制到新表，复制时会在原表上加锁
   2. 导出与导入。使用 MySQL dump 工具，注意修改 SQL 文件中语句
   3. 创建与查询
      1. create table innoDB_table like myisam_table;
      2. alter table innodb_table engine = InnoDB;
      3. insert into innodb_table select * from myisam_table;
   4. 分批插入
      1. start transaction;
      2. insert into innodb_table select * from myisam_table where id between x and y;
      3. commit;

第三章 服务器性能剖析

简介

1. 三个性能相关的问题
   1. 如何确认服务器是否达到了最佳性能状态
   2. 找出某条语句执行慢的原因
   3. “停顿”、“堆积”、“卡死”等间歇性疑难问题处理
2. 处理问题要重点测量服务器的时间花费在哪里，无法测量就无法有效地优化，第一步应该测量时间花在什么地方
3. 性能即响应时间
4. 找到合适的测量范围。合适的测量范围指只测量需要优化的活动
5. 完成一项任务所需要的时间分两部分，执行时间和等待时间
6. 性能剖析一般分两步，第一步，测量任务所花费的时间，第二步，对测量结果进行统计和排序，将重要的任务排在前面
7. 测量点，测量工具 Percona Server

剖析MySQL查询

1. 使用慢查询日志先生成一个分析报告，再根据需要查看日志内容
2. 使用 pt-query-digest 分析日志，输出报告
3. show profile
   1. 打开剖析（默认禁用）。set profiling = 1;
   2. show profiles; // 查看执行时间
   3. show profile for query 1; // 查看剖析详情
4. profile的结果会存在 Information_schema.profiling表中
5. show status
   1. show status的结果只是一个计数器，无法提供基于时间的统计
   2. explain执行计划无法得到使用的临时表是磁盘临时表还是内存临时表
   3. 更好用的查询剖析工具：performance_schema

间歇性问题

1. 尽量不要用试错的方式解决问题。这种方式有很大的风险。因为结果可能导致问题更复杂。
2. 间歇性问题分析
   1. 确定是单条查询问题还是服务器问题。如果服务器上每条查询都慢了，那么慢查询可能就不是问题的原因
   2. show global status
      1. 如果Thread_running过大，表示当前正在执行的查询数量过大，一般有两个原因，一是新到的查询在等待查询释放锁，二是服务器短时间内接收到大量查询，比如前端 memcache 突然失效导致查询风暴
   3. 使用 show processlist
      1. 不停地捕获 show processlist 的输出，来观察是否有大量线程处于不正常的状态或者有其他不正常的特征
   4. 使用慢查询日志。需要开启慢查询日志并在全局级别设置 long-query-time 为0
   5. 可视化数据最有说服力，将日志分析结果绘制成图形
   6. 捕获诊断数据
      1. 出现间歇性问题时需要尽可能多地收集所有数据
      2. 在尝试解决其他人提出的问题之前，先要明确两件事且能够记录下来
         1. 首先，问题是什么，一定要清晰地描述出来
         2. 为解决问题已经做了什么操作
      3. 定指标。指标要能和正常时的阈值进行比较的指标。阈值设置要稍高于正常值，但不能高出太多在问题出现频率不高的时候收集日志数据，便于诊断到问题发生的最根本原因。
      4. 达到阈值几秒/一段时间后开始收集诊断数据
   7. 如果有什么地方看起来很眼熟，明智的做法还是需要花一点时间去测量一下其充分必要条件，以证明其是否就是问题所在。遇到熟悉的问题，收集相关的充分必要条件。
   8. 通常来说，MySQL只会写数据，日志，排序文件和临时表到磁盘。

第 4 章 Schema 与数据类型优化

选择数据类型

1. 选择不会超过范围的最小类型
2. 关于列值为NULL
   1. 尽量避免NULL
   2. 把可以为NULL的列改为NOT NULL带来的性能提升较小
   3. 如果计划在列上建索引，尽量避免设计为可NULL值，因为可为NULL的列被索引时，每个索引记录要一个额外的字节
   4. InnoDB使用单独的位(bit)存储NULL值，所以对于稀疏的数据有很好的空间利用率
3. 整数类型
   1. 分 tinyint, smallint, mediumint, int, bigint，分别使用 8, 16, 24, 32, 64位存储
   2. 整数类型有可选的 unsigned 属性
   3. 可为整数类型指定宽度，这个宽度只是指定数字显示时的位数
4. 实数类型
   1. 浮点数。double 和 float，cpu 原生支持浮点运算，结果为近似值，运算效率高
   2. 精确小数。Decimal
      1. Decimal(18, 9) 表示一共 18 位，小数点前 9 位，小数点后 9 位
      2. Decimal 以字符串形式保存，每 4 个字节保存 9 位数字，小数点单独占一个字节，如 Decimal(18,9) ，一共使用 9 个字节。
      3. Decimal 运算效率比浮点数效率低
   3. 可以考虑使用 Bigint 代替 Decimal 存储财务数据，将需要存储的货币单位根据小数位数乘以相应的倍数即可。
5. 字符串
   1. 存储引擎存储 char 或 varchar 值在内存中和在磁盘上存储方式可能不一样
   2. varchar 类型，节省空间，额外使用一到两个字节存储字符串长度。varchar 和 char 指定的长度是字符数，字节长度跟所采用的字符集有关。
   3. InnoDB会把过长的 varchar 存储为 Blob
6. Blob 和 Text 类型
   1. Text 分 tinytext, smalltext, mediumtext, text longtext，Blob 分类跟 Text 类似
   2. MySQL 将 Blob 和 Text 类型值当作特殊对象处理，当值太大时会使用专门的存储区域存储，此时每个值在行内存储一个 1 到 4 字节的指针。
   3. Blob 和 Text 排序，只对前 max_sort_length 字节排序
   4. 查询使用 Blob 或者 Text 列会使用隐式临时表，这会造成严重的性能开销，特殊技巧是使用 substring(column, length) 将列值转换为字符串
   5. order by varchar 类型字段会使用临时表。假如有一个 1000 万行的表，其中有一个 UTF-8 字符集 varchar(1000) 的列。每个字符最多使用 3 个字节，最坏的情况下要用 3000 字节空间，如果在 order by 时用到这个列，并且查询扫描了整个表，那为了排序就需要 30GB 的临时表，非常耗资源。
   6. 如果 explain 执行计划的 extra 列包含 using temporary 则说明这个查询使用了隐式临时表。
7. 枚举类型
   1. 枚举类型可以把一些不重复的字符串存储成一个预定义的集合，MySQL 在内部会将每个值在列表中的位置保存为整数。
   2. 枚举字段排序是按照内部存储的整数排序
   3. 枚举列添加或删除数据必须使用 ALTER TABLE 命令
8. 日期时间类型
   1. Datetime类型，使用 8 字节存储，存储时间范围大，与时区无关，精确到秒
   2. Timestamp类型，使用 4 字节存储，范围在 1970年到2038年，与时区有关，精确到秒
   3. 如果要存储比秒更小粒度时间单位，可以使用 Bigint 存储微秒级别时间戮，或使用 double 存储秒之后的小数部分，或使用 MariaDB 替代 MySQL
9. 标识符列
   1. 当选择标识类型时，不仅仅要考虑存储类型，还要考虑 MySQL 对这种类型怎么执行计算和比较
   2. 整数类型是标识列最好的选择，因为它们很快并且可以使用 auto_increment
   3. 字符串类型。
      1. 不建议使用字符串类型作为标识类型
      2. 使用随机字符串，MD5(), SHA1(), UUID() 作为主键，因为数据不能顺序插入和查找，会导致 insert, select 很慢；insert 慢是因为要写索引到不同的位置；select 慢，因为逻辑上相邻的行分布在磁盘和内存的不同地方。
10. 特殊类型数据
    1. IPV4实际上是无符号整数，所以应该用无符号整数存储 IP 地址，MySQl 提供 INET_ATON() 和 INET_NTOA() 函数在这两种表示方法间转换。

MySQL schema 设计中的陷阱

1. 太多的列，且很多列值为空
2. 太多的关联。如果希望查询执行速度快且并发性好，单个查询最好在12个表以内做关联。
3. 配置MySQL 的 SQL_MODE 来禁止不可能的日期

范式和反范式

1. 三范式
   1. 列不可分
   2. 在第一范式基础上，表中所有列必须依赖于主键
   3. 第二范式基础上，每一列只与主键直接相关而不是间接相关
2. 五大约束
   1. primary key
   2. unique
   3. default
   4. not null
   5. foreign key
3. 范式化优点，很少有重复数据，占用空间小，更多需要表关联
4. 反范式化的特点，所有数据都在一个表，可以很好的避免关联，这时，查询最差的情况是全表扫描

缓存表和汇总表

1. 实时计算统计值是很昂贵的操作，因为需要扫描表中大部分数据，要么查询语句只能在某些特定的索引上才能有效，而这种索引一般对 update 操作有影响，所以一般不建议创建这样的索引
2. 在使用缓存表和汇总表时，必须决定是实时维护数据还是定期重建表，定期重建不只是节省资源，也可以保持表不会有很多碎片。
3. 使用影子表
   1. Drop table if exists my_summary_new, my_summary_old;
   2. create table my_summary_new like my_summary;
   3. 填充数据到 my_summary_new
   4. Rename table my_summary to my_summary_old, my_summary_new to my_summary;
4. Oracle数据库中视图跟汇总表、缓存表作用类似。
5. 计数器表。如果应用在表中保存计数器，则在更新计数器时可能碰到并发问题。折中办法是使用下表
   1. create table hit_counter(slot tinyint unsigned not noull primary key, cnt int unsigned not null) engin=innodb
   2. 预先在表中增加100行数据，随机选择一个槽更新
   3. update hit_counter set cnt = cnt + 1 where slot = rand() * 100;
   4. 统计结果：select sum(cnt) from hit_counter

第 5 章 创建高性能索引

索引基础

1. 使用索引查询，先在索引中找到对应的值，然后根据匹配的索引记录找到对应的行
2. 索引最左前缀原则
3. 一个包含两个列的索引和分别在两个列上建索引大不相同
4. MySQL中索引是存储引擎层实现的
5. 索引类型
   1. B-Tree索引。InnoDB使用 B+Tree 索引，InnoDB根据主键引用被索引的行
   2. B-Tree索引意味着所有值是按顺序存储的，且每一个叶子页到根的距离相同。使用索引查到数据是有序的。
   3. 因为索引树中的节点是有序的，所以除了按值查找外，索引还可用于排序操作。
   4. 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。这种情况跟创建索引时列的顺序也有关系
   5. 哈希索引。用数据行计算出哈希值来作为索引。哈希索引不支持在多个列上建索引，只支持等值查询，不支持范围查询，优点是速度快。
6. 索引的优点
   1. 索引可以减少服务器需要扫描的数据量
   2. 索引可以帮助服务器避免排序和临时表
   3. 索引可以将随机IO变为顺序IO
   4. 对于数据量很小的表，全表扫描更高效，对于中到大型表，索引非常有效，对于超大表，建立和使用索引代价随之增长，这种情况需要一种技术可以直接区分出查询需要的一组数据而不是一条记录一条记录的匹配。例如可以使用分区技术。

高性能索引策略

1. 索引为独立的列。指索引列不能是表达式或者函数的一部分，如 select actor_id from sakila.actor where actor_id + 1 = 5; 这种情况不会使用索引。应该养成简化 where 条件的习惯，始终将索引列单独放在比较符号的一侧
2. 前缀索引
   1. Alter table sakila.city_demo add key(city(7));
   2. 前缀索引使索引更小、更快，缺点，无法使用前缀索引做 order by 和 group by 也无法使用前缀索引做覆盖扫描。
3. 多列索引
   1. 执行计划 Explain 结果中 Extra 列有索引合并策略（using union primary idx_fk_film_id）时说明表上建的索引很糟糕
   2. 当 where 语句中有多个相交（AND）操作时，表示需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。
   3. 创建过多的单列索引有时会导致性能严重下降。多列索引字段顺序应根据字段选择性从高到低使用字段，也叫基数法则。
   4. where 子句中的排序、分组和范围查找条件等其他因素会对查询性能造成非常大的影响。
4. 聚簇索引
   1. 聚簇索引并不是一种单独的索引类型，而是一种数据存储的方式。具体细节依赖于实现方式，InnoDB聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。
   2. 一个表只能有一个聚簇索引。InnoDB聚簇索引的列是主键列。如果没有定义主键，InnoDB会选择唯一非空索引代替，如果没有这样的索引，InnoDB会隐式定一个主键。
   3. 聚簇索引将数据聚集在一起，提高数据访问速度。聚簇索引最大限度提高了IO密集型应用的性能，但如果全部数据都放在内存上，那聚簇索引的优势没了。
   4. 页分裂。当行的主键值要求必须将这一行插入某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作。
   5. 使用InnoDB应该尽量按主键顺序插入数据，并尽可能使用单调增加的聚簇索引的值来插入新行。
5. 覆盖索引
   1. 如果一个索引包含了所有要查询的字段就称为覆盖索引
   2. MySQL不能在索引中执行 like 操作，可以做最左前缀的like 比较，不能做 '%APOLLO%' 这种形式的 like 比较
6. 扫描索引是很快的，因为只要从一条记录移动到下一条记录，但如果索引不能覆盖查询所需要的全部列，那就不得不每扫描一条索引就回表查询一次对应的行。这基本上是随机IO，因此按索引顺序读取数据的速度通常比顺序的全表扫描慢，尤其是IO密集性工作负载时。
7. 冗余和重复索引
   1. MySQL允许在相同的列上按相同的顺序创建相同类型的索引，应避免这样创建索引，发现后应立即移除
   2. 冗余索引，如果创建了索引 （A，B），再创建索引（A）就是冗余索引
8. 索引和锁
   1. 索引可以让查询锁定更少的行
   2. Explain 的 extra 列出现 using where，表示MySQL服务器将在存储引擎返回行以后再应用 where 过滤条件。
   3. InnoDB在二级索引上使用读锁，但访问主键索引需要写锁
   4. 每额外增加一个in()条件，优化器需要做的组合都将以指数形式增加，最终可能会极大地降低查询性能
   5. in 查询不属于范围查询，属于等值比较。很多数据库中 in 操作完全等同于多个 or 条件子句，但在 MySQL 中不成立，MySQL 中 in 操作是一个 O(logn) 复杂度操作比or查询是 O(n) 效率高
   6. 执行 union 查询，先将单个查询结果放到一个临时表，然后再重新读出临时表数据完成 union 查询。使用 union all 代替 union，因为只用 union 时，MySQL会给临时表加上 distinct 选项，这会导致对整个临时表的数据做唯一性检查。
9. 不建议使用MySQL做太复杂的空间信息存储，PostgreSQL在这方面是一个不错的选择。