高性能mysql笔记 - worldgreen/freamwork-test GitHub Wiki

mysql构架与历史

逻辑构架
- 存储和计算分离，可以使用不同存储引擎
- 分3层 1、客户端通信 2、语句解析，优化 3、存储引擎
- 每个客户端都会在服务器中有一个线程，连接的查询在线程中单独进行
并发控制
- 服务器层和存储引擎层都需要并发控制
- 读写锁：读锁：读锁不会阻塞，写锁：写锁会阻塞其他读锁和写锁
- 表锁：锁整张表，行锁：只在存储引擎实现
事务
- 事务是在存储引擎层实现的，也就是说有的存储引擎支持事务，有的不支持
- 事务特性：原子，隔离（一个事务所做的修改在未提交前，对其他事务不可见），持久，一致
- 脏读（读了另一个事务未提交数据），不可重复读（update），幻读（insert）
- 读未提交（读了另一个事务未提交数据），读已提交（保证事务隔离性），可重复读，幻读
- 死锁：多个事务占有自己的资源并请求占用其他事务资源
- 存储引擎使用死锁检测避免死锁
- 事务日志：先修改内存拷贝，把事务持久到硬盘事务日志中（顺序IO），在后台慢慢刷回到磁盘。
- autocomit每条查询会当作一个事务
- 不要在事务中混合使用存储引擎，有的存储引擎不支持事务，没法回滚。
- 服务器层lock table和存储引擎层事务不可混用，除非禁用autocomit，否则不要使用lock table
- innodb 加锁方式为两级锁定协议，事务分为加锁阶段：只能加锁和操作数据，解锁阶段：只能解锁和操作数据，并发度高，但可能发生死锁。一次性锁协议：事务开始时一次性申请全部锁，事务结束后释放锁，并发度不高，不会发生死锁
- mvcc 多版本并发控制，每个数据有多个副本，副本有时间戳，当更新数据时，将数据拷贝一份，对数据进行修改，并记录当前版本号，修改完，检测版本号和刚才记录的是否一致，如果不一致，数据被其他事务修改，当前事务取消，否则提及修改，增加版本号。
- myisam只支持表级锁

schema和数据类型优化

选择优化的数据类型
- 小的比较好占用磁盘内存少，处理需要时间少
- 简单的比较好整形比字符串操作代价低
- 避免使用null
- 选数据类型时：
  - 合适的大类型：数字，字符串，时间
  - 选具体类型
- 数字
  - 整形：tinyint smallint mediumint int bigint 8， 16，24， 32， 64， unsigned
  - 实数：float double 浮点小数 decimal 精确小数，只在对小数精确计算时使用
- 字符串
  - blob 二进制 text 字符串存储比较大的类型
  - 枚举代替常用字符串类型内部是数字，varchar 和 enum关联降低速度
  - varchar 在临时表和排序时会悲观按最大长度分配内存
- 时间
  - datetime YYYYMMDDHHMMSS的整数中，与时区无关，8个字节空间
  - timestamp 转换为时间戳，和时区有关，插入和更新用 4字
- 选择标识符
  - 相关联的列数据类型一定相同，包括 unsigned
- 特殊类型数据 IP地址 inet_aton()和inet_ntoa()
错误表的设计
- 太多的列，在服务器层将编码内容解码成行数据结构代价高
- 每个查询太多的关联
- 依赖枚举在枚举中加内容要用alter table，会锁表
- 表示未知时可以用null，不用null会让代码复杂化
范式和反范式
- 范式
  - 第一范式：每个列都是不可分隔基本数据项
  - 第二范式：每行必须完全的被区分，如主键，实体属性完全依赖于主关键字
  - 第三范式：表中不包含其他表中的非主关键字，如学生表中有班级名称
  - 优点：更新操作快，没有重复数据，修改数据少，表小，放内存中执行速度快
  - 缺点：查询可能需要关联表，如果信息在同一张表中可以建索引。
- 反范式
- 数据在一张表中避免关联
汇总表
- 汇总表：保存使用sum, group by 等语句聚合结果的表
- 计数器表：如计算网站的点击次数，并发的情况下，建多个列，并发插入
加快alter的执行速度
- 常见操作：创建一个新表，把数据插入新表中
- 修改列默认值优化， alter table a modify column b tinyint not null default 5 要做读和写操作 -> alter table a alter column b set default 5 只修改 .frm文件
- 不需要重建表：移除列auto_increment属性，增加，移除，更改 enum和set常量
总结
- 避免过度设计，简单合适数据类型，相同的数据类型存形似值（表关联），可变长字符串（临时表，排序），小心使用set enum

创建高新能索引

索引类型
- b+ 树索引
  - 全值匹配。最左前缀。列前缀。范围值。order by操作
- 哈希索引
  - 只包含哈希值和字段指针，不包含字段值，不是覆盖索引
  - 无法用于排序，部分列匹配，范围，
- 自定义哈希索引 url = ? and url_crc = crc32(url)
索引优点
- 减少服务器需要扫描的数据量
- 避免排序和临时表
- 将随机io变为顺序io
- 三星系统（索引是否符合某个查询）：索引顺序和where顺序相同索引顺序和order by顺序是否相同，索引包含查找全部列
高性能索引策略
- 独立的列，不是表达式的一部分，不是函数参数
- 前缀索引和索引选择性，前缀选择性接近列的选择性，无法做order 和 group by
- 多列索引 and 用多列索引 or 转换为 union all
- 选择合适的索引列顺序把选择性高的列放前面
- 聚簇索引：尽量使用自增主键，否则会导致叶分列
- 覆盖索引：延迟访问，用join（select ）子查询用覆盖索引， extra using index
- 使用索引排序： type 为 index， order by和索引顺序一直，排序方向一直，如果有关联表，order by字段一定在第一个表中，并有索引。
- 重复索引和冗余索引，有时候在整数列后加长的字符串，会使只查整数的查询变慢

查询性能优化

优化数据访问
- 是否请求了不需要的数据
  - 返回过多的行加limit，和列，重复查询的加缓存
- 是否扫描列过多的行
重构查询方式
- 切分查询：删除旧表时，分多次删除，锁定更少的行数
- 分解关联查询：缓存效率高。
查询执行基础
- 查询的过程
  - 客户端发一条查询语句给服务器
  - 服务器查询缓存，如果命中缓存，发送结果
  - 没有命中，服务器进行sql解析，预处理，优化器生成执行计划
  - 调用执行引擎API执行查询
  - 返回结果集
- 服务器和客户端，线程，半双工，客户端缓存数据
- 关联子查询
  - where 条件中包含 in（select）的自查询，会全部扫描in外部的表，用 join，或exists 改写
  - 用exist 去重，代替 join 的distinct select distinct film.id from film inner join film_actor using(film.id) select film.id from film where exist (select * from film_actor where film.id = film_actor.film_id)
- union 把条件从外层下推到内层
- 优化 min select min(id) from student where name = 'we', 如果name上没有索引会全表扫描， select id from student ue index(primary) where name = 'whe' limit 1
优化特定类型查询
- 关联查询只在第二个表的列上建索引
- 尽量用关联查询代替子查询
- 简历索引优化group by和 order by，否则会出现临时表和文件排序
- 使用延迟加载和确定ID来优化limit 分页
- union 将where，limit， group by下推，否则会出现临时表

事务隔离级别的实现方式

锁一个线程操作的时候，其他线程读写不能操作，并发度不高。
读写锁：读锁间不相互阻塞，读写锁，写锁相互阻塞，度多写少
读的时候不用锁，写的时候锁，读的时候读快照就行。
binlog 主要用于到其他数据源的复制
redulog主要实现事务的原子性，持久性
undolog 主要实现事务的回滚，记录数据修改之前的值