MySQL

louie
89 阅读5分钟

MySQL

事务

  1. 事务的基本要素ACID
    1. 原子性 事务要么都做要么都不做
    2. 一致性 事务开始前后数据库的完整性约束不被破坏
    3. 隔离性
    4. 持久性 事务完成后，对数据库的操作被保存到数据库中
  2. 事务的并发问题
    1. 脏读 读到了未提交的数据
    2. 不可重复读 同一个事务读同一个数据前后读取的数据不一致
    3. 幻读 并发事务对数据进行添加和删除，导致其他事务内两次查询的结果不同。
  3. 事务隔离级别
    • 隔离级别越高，越能保证一致性和完整性，但是对并发性能的操作影响越大。
    1. 读未提交 不加锁 会出现脏读
    2. 读已提交 行级锁 读取数据使用快照读 导致其他要修改该行数据的事务阻塞，避免脏读
    3. 可重复读 MVCC 对每一行进行版本号的控制
    4. 串行化 对整张表加锁 相当于变成单线程的操作
  4. mysql默认隔离级别为可重复读
    1. 即Mutil-Version Concurrency Control,多版本并发控制
    2. select不会更新版本号，读的是之前的版本 其他操作会更新版本号
    3. 事务读取的版本号小于等于当前版本号
    4. 如果检索条件有索引（包括主键索引）的时候，默认加锁方式是next-key 锁；如果检索条件没有索引，更新数据时会锁住整张表。一个间隙被事务加了锁，其他事务是不能在这个间隙插入记录的，这样可以防止幻读。

mysql表的设计

  1. 三范式
    1. 每一列的原子性
    2. 不存在非关键字段对任一候选关键字段的部分函数依赖 防止数据的冗余
    3. 保证每一列都和主键直接相关
  2. 优化查询
    1. 添加冗余的字段 减少连表查询
    2. 联合索引 减少回表查询

mysql调优

  1. 索引的使用
  2. 使用explain对执行的sql语句进行分析 查看是否用到了索引
  3. 减少查询的次数

索引失效

  1. 查询条件or
  2. 最左匹配
    1. 字符串通配符在左边 ‘%xxx’
    2. 联合索引 a，b 查询b不走索引
  3. 对索引字段进行函数计算或者其他运算的时候
  4. 需要进行隐式类型转换 1和‘1’
  5. 优化器对使用哪种方案进行筛选
    1. 找出所有可能使用的索引
    2. 计算全表扫描的代价
    3. 计算使用不同索引扫描的代价

索引不适合的场景

  1. 离散度比较低 比如性别
  2. 数据量较小的时候无需索引
  3. 更新频繁的字段不适合加索引（要综合考虑加索引的代价）

索引

  1. 数据结构
    1. b+ 树
      1. 为什么不使用hash？是基于kv的，对于区间查询无法使用hash 不支持多列索引的最左匹配原则
      2. 二叉搜索树-》二叉平衡树avl树-》b树-》b+树
      3. b+树：非叶子节点只存储索引，不存储数据，叶子节点存储所有索引字段 叶子节点由指针相连，提高区间访问速度
  2. 聚簇索引和非聚簇索引 innodb有且只有一个聚簇索引 myisam都是非聚簇
    1. 聚簇：叶子节点记录了该索引对应的行记录的完整数据
    2. innodb一般都是pk若无则使用唯一非空索引 都没有则使用一个隐式索引
    3. 非聚簇索引 节点除了包含键值外，还有一个pointer指向对应的行数据
    4. 需要两次b+树的遍历查找才可以取到数据
    5. 为什么不直接使用地址来存储？ 还要多维护非聚簇索引的值
  3. 为什么有索引但是没有走索引？
    1. 联合索引的最左匹配原则
    2. 优化器对使用哪种方案进行筛选
      1. 找出所有可能使用的索引
      2. 计算全表扫描的代价
      3. 计算使用不同索引扫描的代价
  4. 建立索引的几个原则
    1. 索引列的离散性较好
      1. 如果一列只有01两个值，建立或者不建立索引的消耗其实是不大的，都相当于是全局扫描了
    2. 在设计的时候要保证最左匹配原则
      1. 要注意的是 a and b and c 这种情况 还要like %xxx的情况

explain 的理解

  1. possible_keys
    1. 可能会用到的key 可能有多个
    2. key 真正用到的key
  2. type
    1. null const ref range all 性能依次变差

MySQL优化器选择索引

  1. 需不需要回表操作（覆盖索引） 如果需要可能就不走索引 直接查 避免回表带来开销
  2. 索引基数不准确 需要执行analyze table table_name命令重新统计索引基数

SQL

  1. DDL 数据定义语言 定义修改表结构、索引、关系等
  2. DML 数据操纵语言 update insert delete等
  3. DQL 数据查询语言 select等
  4. DCL 数据控制语言 授权等
  • online ddl
    • 增加字段 直接执行ddl sql 表不需要重构
    • 字段重命名 直接执行ddl sql 表不需要重构
    • 删除索引 直接执行ddl sql 表不需要重构
    • 修改表名 直接执行ddl sql 表不需要重构
    • 调整字段大小 属于耗时操作 需要重构表
    • 删除字段 属于耗时操作 需要重构表
    • 修改字段类型 属于耗时操作 需要重构表
    • 添加索引 属于耗时操作 需要重构表
  • gh-ost online ddl 工具
    • 使用go-mysql 同步数据
avatar
文章
阅读
粉丝