《高性能MySQL》读书笔记

一、存储引擎和事务

InnoDB引擎

• MySQL5.5（包括）默认引擎，行级锁，支持事务

• 事务隔离级别：默认是可重复读（RR）

• MVCC

  • 多版本并发控制

    • 基于undo来实现，每条记录都有隐藏的事务id字段，回滚指针字段，删除flag字段，以及构建聚族索引的rowid。

    • 快照读

      • 基于read view，第一次查询的时候才会生效，相当于框定了查询记录的版本号范围。select是快照读，不加锁

    • 当前读

      • 读取数据的最新版本，插入/更新/删除操作，都是当前读，需要加锁

    • 数据操作

      • update：新增一条记录，事务版本号是当前系统事务版本号。同时把旧记录的删除标识设置为删除
      • delete：删除标识设置为删除状态
      • insert：新增一条记录，用当前系统事务版本号

  • 解决哪些问题：实现读写的高并发，读写不冲突

  • 只在可重复读和读已提交生效，其他两个隔离级别不生效

  • 不能解决幻读

    • 如果在当前事务中使用了当前读，则可能出现幻读

• 支持四个事务隔离级别

• 数据存储

  • 数据和索引保存在一个文件里，表名.ibd文件，空间占用比较大

• 索引类型

  • 聚族索引

• 间隙锁

  • 防止幻读。间隙锁是锁在主键上，如果where条件不是主键，则有可能锁住的是整个表

MyISAN引擎

• MyIsam是5.5之前的默认引擎，表锁，不支持事务，支持全文索引，数据压缩，崩溃后无法安全恢复

• 数据存储

  • 数据和索引分别保存在.MYD和MYI文件里

• 索引类型

Memory引擎

• 保留的数据重启后会丢失，但是表结构还在，用来保存中间数据。跟临时表有点区别，临时表是链接级别，链接断了表就不存在

CSV引擎

• 读写csv文件

表定义文件

• 后缀.frm，是在服务器层，跟引擎无关

如何选择引擎

• 选择InnoDB

  • 优先InnoDB，以及在线热备份

• 选择MyISAM

  • 空间小或者非事务，不在乎崩溃丢失数据选MyIsam(写入是先写在缓冲区，异步回盘的，所以速度快，但崩溃恢复比较麻烦)
  • 日志这种只有插入，考虑写入速度和存储空间的选MyIsam，但如果要同时做数据分析，那么就在备份表做

• MyIsam不一定比innodb快，要考虑数据量，IO，索引等

服务器层的锁机制

• alter table之类的DDL操作会在服务器层加上表锁，表锁支持并发，但是也带来很大的锁开销。一些DDL操作之前会强制之前的事务提交。

事务

• 事务日志的好处

  • 只需要修改内存拷贝和持久化事务日志，不需要把数据持久化到磁盘，事务日志是是在日志文件追加日志，顺序IO，比随机要快的多。事务提交后，内存中的数据会慢慢写会磁盘，这种叫做预写式日志(Write-Ahead Logging)，修改数据需要写两次磁盘

• 事务实现层

  • 事务是引擎实现的，一个事务中使用多种引擎的表不可靠，不支持事务的表可能无法回滚

二、选择优化的数据类型

选择原则

• 更小的更好。简单：比如整形比字符型操作代价更低，因为字符有字符集和检验规则
• 尽量避免null(空值影响索引的查询)

Decimal

• 5.0或者更高版本支持decimal的高精度运算，低版本计算有问题
• Decimal是把数学打包保存到一个二进制字符串，每4个字节存9个数字，只是一种存储格式，占用空间比浮点型大，计算时会转为duouble。如果可以，把浮点型转为bitgint会更快的计算。TINYINT,SMALLINT,MEDIUNINT,INT,BIGINT对应8,16,24,32,64位存储空间

varchar

• 更小的存储空间，如果开启ROW_FORMAT=FIXED则是定长，一个或者两个字节保存长度(最多保存65535个字节)

• 容易产生碎片：由于长度可变，更新时可能超出页或者段，需要额外处理

• 分配真正需要的空间

  • varchar(5)与varchar(200)存储hello的区别：存储空间一致，但是varchar(200)内存开销更大，因为mysql是按固定大小的内存块来保存内部值，在临时表排序操作时更糟。所以最好只分配真正需要的空间。

char

• 末尾会用空格填充，方便索引匹配，所以末尾存了空格是读取不到的

blob与text区别

• 一个是二进制没有字符集或者排序规则，一个是字符有检验规则和排序规则，只对前max_sort_length做排序

datetime

• 精确到秒，YYYYMMDDHHMMSS格式存储到整数中，占用8字节，年范围1001-9999，与时区无关

timestamp

• 保存从1970年1月1日开始的秒数，4个字节，占用空间更少，带有时区，1970到2038年

标识值的类型选择

• 最好用整数型，因为快还可以自增

• 小心用字符型

  • 因为它会占用更多的空间，MyIsam会对字符做压缩，做索引查询时性能慢很多

• 谨慎用随机字符

  • 像md5,uuid函数因为值会任意分布在很多的空间里。影响insert和select的性能

    • 对于insert查询会变慢，会随机写索引到不同的位置，导致页分裂，随机访问磁盘，如果是聚族索引引擎产生聚族索引碎片
    • 对于select，会变慢，逻辑上相邻的行会分布在磁盘和内存的不同地方，随机值会让缓存赖以工作的访问局部性原理失效

schema设计中的陷阱

• 不要有太多的列：数据库缓存行，转换为列会消耗很大
• 不要太多关联：限制是61个，最好控制在12个。表关联会读磁盘，随机IO

三范式

• 第一范式。列是不可分割的，列具有原子性，消除重复
• 第二范式。满足第一范式的基础上，每一行都与主键关联，消除部分依赖。
• 第三范式。满足第二范式的基础上，每一列都与主键关联，消除传递依赖

三范式的原理和例子总结

• 范式的好处

  • 1.更新比反范式快，因为更新更少的表，没有部分依赖
  • 2.修改更少的数据。因为没有或者很少重复
  • 3.内存操作更快，因为表更小
  • 4.更少使用group by，distinct。因为很少重复

• 反范式的好处

  • 更少的表关联，做搜索和排序可能更快

• 混合范式

  • 现实中常用，纯范式和反范式是实验室中的，排序或者查询列可以通过复制列(同样列在多个表)就是反范式来避免表关联

优化的表

• 巧妙使用缓存表，汇总表，影子表（新表准备好数据再改为旧表名字）

慎用ALTER TABIE

• ALTER TABIE一般会引起锁表并重建表，旧表的数据搬到新表，因此大表要谨慎操作。修改字段默认值可以修改.frm文件

三、高性能索引

索引在存储引擎层实现

索引类型

• B-Tree索引

  • 聚族索引

    • 特性

      • 是一种数据的存储方式，索引树和数据行保存在一个结构里。每个数据单元包括了索引键值，行的列值，事务id，用于事务和mvcc的回滚指针
      • 索引保存在父节点，数据行保存在叶子页。所谓聚族是数据行和相邻的键值紧紧存储在一起。
      • InnoDB的默认主键是聚族索引，如果没有主键就选择唯一非空索引，没有符合的，inodb隐试定义一个主键作为个聚族索引。
      • 只聚集在一个页面的数据，包含相邻键的页面可能相隔很远
      • 二级索引也是聚族的，保存的是索引值+主键的值，需要回表查询。可以避免行移动时修改二级索引

    • 好处

      • 1，相关数据保存在一起
      • 2，数据访问更快，尤其是范围查询

    • 缺点

      • 1，数据放在内存中的话，聚族没优势
      • 2，插入速度严重依赖插入顺序(无序可能页分裂)
      • 3，更新聚族索引列代价更高，因为每个被更新的行移动到新的位置。也有可能导致页分裂，占用更多磁盘空间。
      • 4，全表扫描更慢，尤其行比较稀疏，或者页分裂导致数据不连续的时候
      • 5，二级索引可能比较大，因为包含了主键索引

  • 非聚族索引

    • MyISAM上的索引都是非聚族的

    • 特性

      • MyIsam主键索引和二级索引在存储分布上没什么不同，独立的页保存索引，主要是两个数据，索引键值和行指针。
      • 行指针是按插入顺序编号的，不是聚族，存储分布是无序的。
      • 二级索引不用回表

• hash索引

  • 特性

    • hash值可以不唯一，冲突用链表保存，memory引擎的默认索引类型
    • 索引包含哈希值和指针

  • 优点

    • 查询快

  • 缺点

    • 1，索引包含哈希值和指针，不能直接做值匹配，可能二次查表
    • 2，存储无序，无法做排序
    • 3，不支持字段部分匹配，组合索引必须同时出现
    • 4，哈希冲突的话，操作比较低效

• 空间数据索引R-Tree

• 全文索引

  • 类似搜索引擎，搜关键词

索引的限制

• 小表和特大表不适合索引

索引的好处

• 1，减少服务器需要扫描的数据量
• 2，帮助服务器避免排序和临时表
• 3，将随机IO变为顺序IO

索引最佳实践

• 三星索引

  • 一星将相关数据放在一起。单行访问很慢，随机I/O
  • 两星索引中的数据顺序和查找的排列顺序一致。顺序I/O，有排序
  • 三星索引中的列包含了查询中需要的所有列。覆盖索引很快

• 前缀索引

  • 选取前面几个字符做索引
  • MyIsam索引有前缀压缩，默认是字符串，也可以整数。每个索引块开头放置前缀，所以在索引块做倒序扫描会比较慢。另外因为做了压缩，查询性能会有影响，如果是IO密集型的是比较好的选择

• 多列索引

  • 选择性高的列放在前面

• 不要用UUID做聚族主键

  • 第一，插入时会向已经存满数据的页中间插入数据，导致页分裂，页分裂导致最少需要修改三个页而不是一个页
  • 第二，如果页数据写入磁盘的话，可能还要读取到内存作比较，随机IO会很慢
  • 第三，频繁的页分裂导致页稀疏不规则填充，产生页碎片，最后可能需要OPTIMIZE TABLE来重建表，并优化页填充

• 自增插入的缺点

  • 并发高时，所有插入都在一个地方引起间隙锁的竞争，自增的方法AUTO_INCRU也会有锁竞争

• 覆盖索引

  • 索引列包含了查询中的所有列，解析器中Extra列中显示using index。二级索引里有主键，如果查询中需要用到主键，则二级索引也可以走覆盖索引

• 组合索引

  • 组合索引走最左前缀匹配，但是范围查询就不会满足最早匹配。最常使用的搜索列应该放在最前面，比如约会网站，的sex，country，但是age列放在后面，因为age会经常做范围查询。巧妙使用in让它符合最左前缀，方便走索引，比如sex in(‘f’, ‘m’)

• 清除碎片

  • 1，ALTER TABLE ENGIN= 不会做任何操作来重建表
  • 2，OPTIMIZE TABLE

四、分区表

可以理解每个分区是一个独立的底层表，每个底层表有自己索引

分区表的作用

• 1.表非常大无法放内存
• 2.分区表更容易维护，比如删除大量数据时可以清理整个分区，还可以对独立分区做优化和检查修复，备份
• 3.分区表可以在不用的物理设备上，可以有效利用硬件资源

分区表的限制

• 1. 最多只能有1024个分区
     2.分区表无法使用外键约束
     3.如果分区字段中有主键或者唯一索引，那么所有主键或者唯一索引比较包含进来。
     4.选择分区的成本可能很高
     5.打开并锁住所有的底层表的成本可能很高
     6.维护分区的成本很高
     7.所有分区必须使用一种存储引擎

分区表保存大数量的数据策略

• 1.全量扫描数据，不要任何索引
• 2.索引热点数据。把热点数据放到一个分区中，索引起来也快

五、缓存

理解缓存

• mysql基于完整SELECT，是缓存结果，如果缓存中涉及的表有变化，则缓存失效
• 同样的语句直接返回缓存结果，不用走解析，优化和执行
• 缓存放到一个引用表中，是一个哈希引用。哈希值包括查询本身，当前要查询的数据库，客户端协议版本等。

不会缓存结果情况

• sql中包含不确定数据时，例如包含函数now()，current_user等自定义函数，存储函数，用户变量。即包含了不确定数据时，查缓存是查不到的。

缓存带来的消耗

• 1.要判断是否命中缓存
• 2.读取操作时，要缓存结果
• 3.写入操作时，要设置缓存失效。如果缓存的内存设置过大或者碎片过多，设置缓存失效时可以会有一段时间的僵死，因为缓存操作有一个全局锁，判断是否命中缓存，缓存失效都要等这个全局锁

缓存碎片

• 内存单元是query_cache_min_res_unit，小于这个数量的内存将不能分配和回收，所以就产生很多空闲的内存碎片

缓存禁用

• 自从MySQL 5.6(2013)以来，查询缓存已被禁。因为它已知在多核机器上不能与高吞吐量工作负载的规模相比较
• mysql8不再支持查询缓存

六、查询优化

慢查询的根本原因

• 查询了太多的数据

• 查询了太多的行

• 查询了太多不需要的列

  • 带来 IO，CPU，内存的消耗，还可能让优化器无法使用覆盖索引

慢查询日志

• 记录扫描行多，响应时间多的查询

应用where条件从好到次

• 第一，在索引中使用where过滤不匹配的记录，在引擎层完成
• 第二，使用索引覆盖扫描，在服务器层，无须回表
• 第三，从数据表中读出记录在使用where过滤，在服务器层完成，需要回表

扫描大量数据只返回少量行的优化方法

• 第一，使用覆盖索引
• 第二，改变表结构，使用汇总表
• 第三，重写复杂查询

切分查询

• 大查询的缺点

  • 锁住大量数据，阻塞很多小但重要的查询
  • 占满整个事务日志，消耗系统资源

• 拆分为小查询的好处

  • 1，缓存高效，更容易命中缓存
  • 2，减少锁竞争
  • 3，应用层做关联，更容易对数据库做拆分，更容易做到高性能和可扩展

优化器的执行计划

• 基于成本的，最小单位是随机读取一个4k数据页成本，后来计算公式变复杂了，可能是一个因子。

• 导致错误的执行计划原因

  • 1，成本计算是不知道是否读内存，是否顺序读，所以优化器给出的执行计划可能不是最优的。
  • 2，另外统计信息也影响执行计划的准确性。
  • 3，不会考虑并发查询

• mysql优化器能处理的类型

  • 1，重新定义关联表的顺序
    2，使用等价变换规则
    3，优化Count，max，min函数，比如MyIsam记录了表的行数，Count(*)就是一个常量，max和min可以从B树读取最大最小
    4，预估并转为常数表达式
    5，覆盖索引扫描
    6，提前终止查询
    7，等值传播
    8，in的比较，先排序，再二分查找，时间复杂度是log n，而不是or的O(n)

• 表关联

  • 关联表数量小于optmizer_seaerch_depth，则穷尽所有的表关联执行计划，取最优的。即变换表的关联顺序，找到最优的
  • 多表就是嵌套循环关联和回溯。先把第一个表的结果放到临时表，然后以这个临时表作为主表做关联，右外连接会转换为等值左链接。
  • mysql不支持全外连接

• mysql不能并发执行

优化特定类型的查询

• 优化UNION

  • 尽可能使用UNION ALL，因为只用UNION会做全部字段的distinct去重
  • 取limit时，每个关联的结果先做limit，最后UNION后再做一次。

• 优化min和max

  • 有时性能比较差。最大最小可能不会做主键扫描，返回第一个满足的值。而是全表扫描
  • 如果查最小主键值，应该指定其走主键扫描，
    用select min(id) from emp USING INDEX(PRIMARY) WHERE first_name=‘Lily’ Limit 1

• 优化count

  • 统计列会忽略null值，如果要统计行最好用count(*)，因为他会忽略列值，直接统计行数
  • MyIsam的count在没有where的时候会很快，是直接拿到行数，如果知道某列的值不会为null，会转为Count(*)

• 优化group by和order by

  • 最好使用一个表的列，这样有可能走索引

• 优化limit分页

  • 常规的分页会查询很多记录，比如Limit 10020, 20会查出前10020，然后丢弃前10000。可以使用内查询的覆盖索引先排序出主键字段，然后关联出其他字段