Skip to content

Latest commit

 

History

History
165 lines (140 loc) · 8.18 KB

高效mysql.md

File metadata and controls

165 lines (140 loc) · 8.18 KB
Raw

事务

事务的特性（ACID）

  • 原子性（atomicity） 最小的工作单元
  • 一致性（consistency） 只能由一种状态到另一种状态，中间没有其他状态
  • 隔离性（isolation） 一个事务的修改，在提交前，对其他事物不可见
  • 持久性 (durability) 事务提交后，修改永久有效

事务隔离级别

  • Read Uncommitted (未提交读)
  • Read Committed（读提交）
  • Repeatable Read (可重复读) mysql 默认隔离级别
  • Seralizable （可串行化）强制事物串行化

死锁 - 常用解决策略

俩个或者多个事务在同一个资源上相互占用，并请求锁定对方占用的资源

  • 超过获取锁的等待时间，自动放弃获取锁
  • 持有最少资源行级排他锁的事务回滚

数据库锁 - 锁其实是在并发下控制多个操作的顺序执行，以此来保证数据安全的变动

  • 悲观锁

    悲观锁认为被它保护的数据是极其不安全的，每时每刻都有可能动，一个事务拿到悲观锁后（可以理解为一个用户），其他任何事务都不能对该数据进行修改，只能等待锁被释放才可以执行。数据库中的行锁，表锁，读锁，写锁，以及syncronized实现的锁均为悲观锁。

    • 实现： select for update
    • 优点：列用数据库的锁机制来实现对数据变化的顺序执行，这是最有效的办法
    • 缺点：如果一个事务对数据加悲观锁，那么其他事务不能对数据做除查询以外的任何操作，如果该事务执行很长，其他事务一直等待，会对系统吞吐造成影响
    • 场景： 适合应用在写为居多的场景

  • 乐观锁

    乐观锁认为他拿到数据不会频繁变动，允许多个事务并发的操作数据

    • 实现：乐观锁通常是通过在表中增加一个版本(version)或时间戳(timestamp)来实现
    • 优点：乐观锁不再数据库加锁，每个事务都可以对数据进行操作，在更新的时候才进行校验，避免了悲观锁造成的系统吞吐下降
    • 缺点：乐观锁为我们人为实现，仅仅适用于我们自己的业务，如果有外来事务插入，会造成错误
    • 场景： 读为居多的场景

  • InnoDB 行锁

Innodb默认使用的是行锁，行锁是基于索引实现的，因此使用行锁的时候必须命中索引，否则将使用表锁

事务日志

数据每次修改，1、 修改内存拷贝； 2、 操作添加到持久在磁盘上的事务日志， 故需要写俩次磁盘

索引

索引的优点

  • 索引大大减少服务器要扫描的数据量
  • 索引可以帮助服务器避免排序和临时表
  • 索引可以将随机 I/O 变为 顺序 I/O

MySQL 索引类型

  • B-Tree 索引 -- 基于 B+ 树索引实现
    • 支持查询
      • 值是按顺序存储的
      • 遵循最左前缀
      • 支持全值匹配，匹配列前缀和范围匹配
      • 只访问索引
    • 限制查询
      • 如果不是按照索引的最左列开始查找，则无法使用索引
      • 不能跳过索引中的列
      • 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查询
  • hash 索引 --- 基于哈希表实现 （memory 引擎）
    • 存储的时候没有排序，所以不能按照范围查询使用
    • 只有精准匹配索引所有列的查询才有效
  • 空间数据索引（R-Tree）（ MyISAM 引擎 ）
  • 全文索引 （只适用于 match against 操作）

B+树索引分类

  • 聚簇索引：按照主键构造B+树，同时叶子节点存放的即为整张表的行记录数据
  • 覆盖索引：即从辅助索引中就可以得到查询的记录，而不需要查询聚簇索引中的记录。

优化

  • 库表结构优化
    • 数据类型的选取
      • 更小的通常更好
      • 简单就好

      整数优于字符，日期使用内置日期类型，ip存储为整数

      • 尽量避免 NULL
  • 索引优化
    • 高效索引策略
      • 独立的列，索引不能是表达式的一部分、函数的参数
      • 前缀索引和索引选择性
      • 多列索引
      • 选择合适的索引列顺序，当不考虑排序和分组的时候，将选择性最高的列放在前面

      select count(distinct staff_id)/count() as staff_selective,
      count(distict customer_id)/count(      ) as customer_selective,
      count(*)
      from payment \G
      ########### 1 row #############
      staff_selective: 0.0001
      customer_selective: 0.0373
      count(*): 16049

      customer_id 选择性更大，所以选择 （customer_id， staff_id） 而是 （staff_id， customer_id）

      • 聚簇索引，InnoDB聚簇索引实际上是在同一结构中保存了B-Tree 索引和数据行
      • 覆盖索引，一个索引包含全部需要查询的列
      • 冗余和重复索引
      • 未使用的索引，应该删除掉
  • 查询优化

查询的生命周期：从客户端，到服务器，服务器（解析器，预处理器，查询优化器），生成执行计划，查询执行引擎，并返回客户端

  • 慢查询
    • 是否向数据库请求了不需要的数据
    • mysql 是否在扫描额外的记录

    慢日志记录了每个查询的 1，响应时间 2，扫描行数 3， 返回行数

  • 重构查询方式
    • 复杂查询和简单查询取舍
    • 切分查询
    • 分解关联查询

InnoDB 和 MyISAM 区别

  • InnoDB 特性

    • 支持事务，主要是为了在线事务处理（OLTP）设计的
    • 行级锁，支持外键，非锁定读
    • 支持多版本的并发控制 （Mvcc 版本控制）获得高并发特性
    • 提供了插入缓冲、二次写、自适应哈希索引、预读等高性能和高可用的功能
    • 适用场景：需要事务的场景，更新数据需要使用行级锁，大数据量读写，大型互联网应用

  • MyISAM 特性

    • 不支持事务，主要为在线分析（OLAP）的应用而设计
    • 表级锁
    • 支持全文索引
    • 它的缓冲只缓冲索引文件，不缓冲数据文件
    • 适用场景：不需要事务的场景，插入、更新少，读取频繁，频繁的统计计算

数据库的锁

lock latch
对象 事务 线程
保护 数据库内容 内存数据结构
持续时间 整个事务过程 临界资源
模式 表锁、行锁、意向锁 读写锁、互斥量
死锁 通过 waits-for、graph、time out等机制进行死锁检测 无死锁检测与处理机制
存在于 lock manager的hash表中 每个数据结构的对象中
  • 锁的粒度
    • 表级锁
    • 页级锁
    • 行级锁

锁的类型

  • 行级锁
    • 共享锁 S Lock, select lock in share mode
    • 排他锁 X Lock, select for update

为了允许行锁和表锁共存，实现多粒度锁机制,InnoDB存储引擎支持一种额外的锁方式，就称为意向锁，即为表级别锁

  • 表级锁
    1. 意向共享锁：事务想要获取一张表中某几行的共享锁
    2. 意向排它锁：事务想要获取一张表中某几行的拍他锁

一致性非锁定读和一致性锁定读

  • 一致性非锁定读：InnoDB 通过多版本控制（MVCC）读取当前时间数据库中行的数据。读的是快照，不需要等 X 锁的结束。极大提高并发性。默认读取方式

  • 一致性锁定读：用户需要显示的对数据库读写加锁，以保证数据逻辑的一致性。下面是俩中一致性锁定读操作

    • select for update
    • select lock in share mode

  • 锁的算法

    • Record Lock： 单个行记录上的锁
    • Gap Lock： 间隙锁，锁定一个范围，不包括记录本身
    • Next-Key Lock: Gap Lock + Record Lock,

  • 锁带来的问题

    • 脏读：在不同的事务中，当前事务可以读到另一个事务未提交的数据。一般发生在（read uncommitted）
    • 不可重复读：也称幻读。发生在（read committed） 级别下。 一个事务内多次对一块数据集读，在这个事务还没结束前，另外一个事务对该数据集做了 DML 操作，导致俩次读的数据不一致。mysql 一般通过 Next-key lock 算法避免不可重复读
    • 丢失更新