storage

「高性能MYSQL」学习笔记[DOING]

Posted by 小石匠 on 2019-07-05

书籍豆瓣链接：《高性能MySQL》

开始学习时间：

预计完成时间：

实际完成时间：

MySQL架构

MySQL三层架构

架构

服务层

负责链接管理(客户端与服务器半双工)、授权认证、安全。维护一个连接池，使用用户名和密码或SSL进行认证。

查询优化

负责解析查询（编译SQL），并进行优化。对于SELECT语句，先查询缓存。存储过程、触发器、视图都在这一层。

存储层

负责存储数据、提取数据、开启一个事务等等。存储引擎通过api与上层进行通信，api屏蔽了不同存储引擎之间的差异。

隔离性

针对隔离性遇到的问题如下：

  1. 脏读：读到了别的事务回滚前的脏数据

  2. 不可重复读：事务A前后两次读取数据不一致(事务B改变了数据，update和delete)

  3. 幻读：事务A首先根据索引得到N条数据，再次搜索发现有N+M条数据了(事务B进行了insert

  4. 丢失更新：同时更新数据，一方数据被覆盖

隔离级别

  1. read uncommitted(未提交读)

解决了丢失更新问题

  1. read committed(提交读)

解决了脏读的问题

  1. repeatable read(可重复读)

解决了不可重复读问题，解决了幻读问题

  1. serializable(序列化)

解决丢失更新问题

乐观锁和悲观锁

乐观锁

获取数据不加锁

更新数据判断数据是否被修改，如果修改不更新

适合于多读的机制，可以提高吞吐量

一般使用：version方式和CAS方式

version方式

提交版本必须大于记录当前版本才能执行更新，

CAS方式

compare and swap或者compare and set，是一种有名无锁算法，不使用锁的情况下（线程不会被阻塞）实现多线程之间的变量同步。

涉及到三个操作数：内存值、预期值和新值

  • 需要读写的内存值 V
  • 进行比较的值 A
  • 你写入的新值 B

CAS有3个操作数，内存值V，旧的预期值A，要修改的新值B。当且仅当预期值A和内存值V相同时，将内存值V修改为B，否则什么都不做。

1
2
3
4
5
6
7
8
9
int compare_and_swap (int* reg, int oldval, int newval) 
{
  ATOMIC(); 
  int old_reg_val = *reg;
  if (old_reg_val == oldval) 
     *reg = newval;
  END_ATOMIC();
  return old_reg_val;
}

悲观锁

每次获取数据都加锁

依赖数据库的锁机制，无法保证外部系统不会修改数据

存储引擎

InnoDB

MVCC支持高并发，默认可重复读，通过间隙锁策略防止幻读

MyISAM

MyISAM会将表存储在两个文件：数据文件和索引文件，不支持事务和行级锁

崩溃后无法安全回复，对于只读，容忍修复操作，小表可以用MYISAM

索引特性，即使是BLOB和TEXT等长字段，也可以基于前500字符创建索引，支持全文索引

myisam的数据分布，myisam的数据根据插入的顺序存储在磁盘上，行号从0开始递增

myisam的索引的叶子节点存的是行指针

Schema与数据类型优化

数据类型

整数类型

整数存储，分为TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT，分别使用8,16,24,32,64位存储空间

整数计算使用64位的BIGINT

实数类型

FLOAT 4字节

DOUBLE 8字节，使用DOUBLE作为浮点计算类型

DECIMAL(M,N) 精确计算代价高，转为浮点不精确，宜转成BIGINT

字符串类型

VARCHAR 可变长字符串，比定长节省空间，额外存字符串长度

CHAR 定长字符串

BLOB 二进制存储 二进制数据

TEXT 字符存储 有字符集和排序规则

BLOB和TEXT不能索引全部长度字符串，应该避免

  • ENUM

优点 代替常用字符串，MYSQL会保存一个映射表，每个值的位置-字符串

缺点 字符串列表固定，添加或删除字符串必须使用ALTER TABLE

日期和时间类型

  • DATETIME

8字节，与时区无关，保存大范围值

  • TIMESTAMP

保存1970.1.1(格林尼治标准时间)以来的秒数，4字节，与时区有关

位数据类型

BIT MYSQL把bit当字符串，b’00111001’存0x57

SET 需要ALTER TABLE

选择标识符

整数性能好，可以使用AURO INCREMENT

不宜使用ENUM SET

不宜使用字符串，占空间，随机字符串造成页分裂

类型选择策略

  1. 尽量选择可以正确存储数据的最小数据类型

  2. 使用操作简单的数据类型。字符串有字符集和校对规则，操作代价比整型大。使用MYSQL内建类型，而不是字符串存储日期和时间，使用整型存储IP地址

  3. 尽量避免NULL，给默认值

schema设计陷阱

很宽的表，只使用一小部分列，性能差

避免太多的关联

ENUM添加枚举需要ALTER TABLE，不如新建一个枚举表

范式和反范式

数据库三范式

  • 第一范式 字段原子不可分

数据库的每一列都是不可分割的原子数据项，不能是数组或JSON串

  • 第二范式 有主键，非主键完全依赖主键

主键是关注人ID+被关注人ID，关注人信息部分依赖主键

  • 第三范式 非主键直接依赖主键，不存在传递依赖

员工表有个字段是部门ID，部门信息不直接依赖员工ID，不应该存在于员工表

范式优缺点

优点 表更小，没有重复冗余数据，更新更快

缺点 需要关联

反范式优缺点

优点 数据在一张表里，很好地避免关联，更好地使用索引

缓存和汇总表

高性能的索引策略

索引基础

b树索引

  • 数据结构

  1. B树

    树中每个节点最多包含m个孩子，根节点至少有2个子节点，非根非叶节点至少有m/2个子节点

    有n个关键字的父节点，拥有n+1个子节点，每个节点的关键字记录数据在磁盘中的位置

  2. B+树

    叶子节点包含了全部的关键字信息，所有的数据仅存在叶子节点

  3. 为什么B+树更适合做文件或者数据库索引

    B+树非叶子节点不存数据的地址信息，B+树叶子节点有链表结构，方便范围查询

  4. B*树

    非叶子节点加链表，非叶子节点关键字个数至少2/3M

  • 支持的查询

创建一个包含多列的索引，支持以下查询

  1. 全值匹配，和索引中所有列匹配
  2. 匹配索引第一列
  3. 匹配索引第一列的前缀
  4. 匹配索引第一列的范围
  5. 匹配索引第一列，匹配索引第二列的范围
  6. 只访问索引列的查询(索引覆盖)
  • 索引的限制
  1. 必须从最左列开始查找
  2. 不能跳过索引的列
  3. 某一列范围查询，右边所有列都无法使用索引查找
  • 优点
  1. 减少了服务器需要扫描的数据量
  2. 帮助服务器避免排序和临时表
  3. 随机io变顺序io

哈希索引

计算所有列的哈希值，为key，指针为为v

特性

  1. innodb内置自适应哈希索引，给频繁使用的b树索引加一个哈希索引
  2. 不支持部分列查询，无法用于排序
  3. 只包含哈希值和行指针，不存储字段值，无可避免的要读取行

空间数据索引

TODO

全文索引

TODO

索引优化

单列索引

索引的选择性 不重复的索引值和数据表记录总数的比值，比值越高查询效率越高

计算 SELECT COUNT(DISTINCT column)/COUNT (*) FROM

前缀索引 很长字符串列建立索引索引，索引大且慢，在保证索引选择性的情况下，使用字符串前缀作为索引

多列索引

  • 索引合并策略

对多个索引做相交操作(AND)，需要一个包含所有相关列的多列索引

对多个索引做联合操作(OR)，需要耗费大量的CPU和内存资源在算法的缓存，排序和合并操作上，可能还不如全表扫描

  • 选择合适的索引列顺序

选择性最高的列放在最前列效率最好

聚簇索引

当表有聚簇索引，数据行实际上存放在叶子页

  • 优点

可以键值相邻的数据保存在一起

数据访问更快

覆盖索引扫描的查询可以直接使用主键值

  • 缺点

更新聚簇索引列的代价很高，强制innodb将每个被更新的行移动到新的位置，插入造成页分裂

  • 按主键顺序插入行

最简单的方法是使用auto increment自增列

使用UUID作为聚簇索引，索引插入完全随机，写入是乱序的，不得不频繁做页分裂操作

覆盖索引

索引包含所有需要查询的字段值，称为覆盖索引

  • 优点

索引条目远小于数据行大小

索引按列值顺序存储的，支持范围查询，比访问磁盘的io少得多

二级索引叶子节点保存主键值，如二级索引能覆盖查询，避免主键索引的二次查询

  • 延迟关联

如索引无法覆盖，可使用二级索引查出id集，然后使用聚簇索引获取数据

索引排序

  1. 只有索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向一致或相反

  2. 关联多张表，ORDER BY子句字段都是第一张表

  3. ORDER BY子句满足最左前缀

  4. WHERE子句使用常量过滤，和ORDER BY子句一起，满足最左前缀

冗余和重复索引

  • 重复索引 创建多个相同列，索引类型相同的索引

  • 冗余索引 有了index(a,b)，再创建index(a)，或者包含主键的耳机索引

  • 需要冗余索引场景

为保证单查a的效率，已有index(a,b,c)，仍新建index(a)

索引与锁

Innodb只有访问行的时候才会加锁，而索引能减少innodb访问的行数

在主键索引上使用排他锁，在二级索引上使用共享锁

Explain

type 访问类型

all 全表扫描

index 另外一种形式的全表扫描，扫描顺序按照索引的顺序，然后根据索引回表查数据

ref 查找列使用索引而且不为主键和unique

req_eq 查找结果唯一

const 将主键放到where后面作为条件查询，将查询转化为常量

extra

using filesort 无法利用索引完成排序操作

using index 表示使用覆盖索引，不用回表

using where 表示优化器需要通过索引回表查询数据

索引案例

支持多种过滤条件

选择性低，但是几乎所有查询都能用到的索引，比如sex，用作索引前缀

如果没用到sex，可以加上sex in [‘male’, ‘female’]，匹配最左前缀，复用索引而不是建立大量的组合索引

由于索引遇到范围查询，后续字段无法使用索引，可以使用IN代替范围查询

但是IN查询优化器会转化成组合，应避免嵌套IN

优化排序

  • 索引排序

文件排序对于小数据集很快，对于选择性低的列，使用索引做排序

  • 深度分页

一个策略是延迟关联，使用覆盖索引返回需要的主键，根据主键关联原表获取行

查询性能优化

  • 查询性能优化方向
  1. 好的库表结构设计
  2. 好的索引优化策略
  3. 好的查询语句

排查思路

是否向数据库请求了不需要的数据

  • 查询不需要的记录

查询后面加上LIMIT

  • 多表关联时返回全部列

修改为select tb.*返回某个表全部列

  • 总是取出全部列，无法完成索引覆盖

不使用select *

  • 重复查询相同的数据

使用缓存

MYSQL是否扫描了额外的记录

  • 响应时间

响应时间 = 服务时间 + 排队时间

服务时间 数据库处理查询的时间

排队时间 等待io等待锁的时间

  • 扫描的行数

使用explain type返回where条件的三个等级

  1. type=ref extra=，主键索引中使用where过滤不匹配记录，存储引擎完成

  2. type=ref extra=using index，使用覆盖索引扫描和过滤，服务器层完成

  3. type=all extra=using where，数据表返回数据，服务器层完成

  • 返回的行数

重构查询

切分查询

MYSQL扫描内存中数据快，响应数据给客户端慢，将一条大的语句一次性完成，可能会造成

  1. 一次锁住很多数据

  2. 占满整个事务日志

  3. 耗尽系统资源

  4. 阻塞很多小但重要的查询

分解关联查询

在应用程序进行关联，每一个表进行一次单表查询，优势

  1. 让缓存效率更高，应用方便缓存单表查询结果

  2. 单个查询减少锁的竞争

  3. 应用层做关联，更容易对数据库进行拆分，高性能和扩展

  4. 使用单表查询获得的ids进行IN查另一个表，比随机关联查询性能更高

  5. 减少冗余记录的查询

  6. 相当于用应用层的哈希关联，替代mysql的嵌套循环关联

查询执行的基础

查询执行流程

  1. 客户端发送一条查询给服务器

  2. 先检查查询缓存，未命中进入下一步

  3. 服务器端进行SQL解析(解析器)，预处理器(预处理器)，查询优化器生成对应执行计划

  4. 查询执行引擎调用存储引擎API执行计划

  5. 结果返回客户端

查询优化处理

  • 等价变换简化规范表达式

如where子句顺序

  • MIN,MAX,COUNT

转化为常量

  • 等值传播

等值关联的字段，WHERE条件会传递到另一个列上，无需写两个

  • 列表IN

先排序列表，在二分搜索，IN是logn，OR是n**？？为何是logn，不是都是nlogn**

  • 执行关联查询

执行嵌套循环关联操作(全外连接无法通过嵌套循环和回溯的方式完成，MYSQL没有全外连接)

优化特定类型查询

  • COUNT()
  1. COUNT(*) 不会扩展所有的列，直接忽略所有的列统计行数

MYISAM是表级锁，不会有并发操作，将表的总行数记录下来

INNODB选择最小的非聚簇索引来扫表，前提是没有where和groupby

  1. COUNT(col) 统计某个列值数量，要求列值非NULL

  2. COUNT(1)和COUNT(*)没有区别

  • 优化关联查询

确保ON或USING子句中的列上有索引

确保groupby orderby只涉及一个表中的列

  • 优化LIMIT分页
  1. 使用索引覆盖扫描，而不是查询所有列

不能解决深度分页

  1. 使用延迟关联

  2. 查询转换为已知位置查询，offset转换为<>

  • 优化UNION查询

通过创建并填充临时表的方式来执行UNION

UNION会给临时表加DISTINCT选项，导致整个临时表做唯一性检查

除非需要去重，否则一定要使用UNION ALL

脑图

链接




FEATURED TAGS
storage
FRIENDS