Mysql的基础概念

发表于 | 阅读次数:
字数统计: | 阅读时长 ≈

存储引擎

  1. 关于count(*)

    知识点：MyISAM会直接存储总行数，InnoDB则不会，需要按行扫描。

    实践：数据量大的表，InnoDB不要轻易select count(*)，性能消耗极大。

    常见坑：只有查询全表的总行数，MyISAM才会直接返回结果，当加了where条件后，两种存储引擎的处理方式类似。

    例如：
    t_user(uid, uname, age, sex);

    • uid PK
    • age index

    select count(*) where age<18 and sex=’F’;
    查询未成年少女个数，两种存储引擎的处理方式类似，都需要进行索引扫描。

    启示：不管哪种存储引擎，都要建立好索引。

  2. 关于事务

    知识点：MyISAM不支持事务，InnoDB支持事务。

    实践：事务是选择InnoDB非常诱人的原因之一，它提供了commit，rollback，崩溃修复等能力。在系统异常崩溃时，MyISAM有一定几率造成文件损坏，这是非常烦的。但是，事务也非常耗性能，会影响吞吐量，建议只对一致性要求较高的业务使用复杂事务。
    画外音：Can’t open file ‘XXX.MYI’. 碰到过么？

    小技巧：MyISAM可以通过lock table表锁，来实现类似于事务的东西，但对数据库性能影响较大，强烈不推荐使用。

  3. 关于外键

    知识点：MyISAM不支持外键，InnoDB支持外键。

    实践：不管哪种存储引擎，在数据量大并发量大的情况下，都不应该使用外键，而建议由应用程序保证完整性。外键的一致性是数据库服务器去维护，而交给应用服务器维护要更加快一些。

  4. 关于行锁和表锁

    知识点：MyISAM只支持表锁，InnoDB可以支持行锁。

    分析：
    MyISAM：执行读写SQL语句时，会对表加锁，所以数据量大，并发量高时，性能会急剧下降。
    InnoDB：细粒度行锁，在数据量大，并发量高时，性能比较优异。

    实践：网上常常说，select+insert的业务用MyISAM，因为MyISAM在文件尾部顺序增加记录速度极快。楼主的建议是，绝大部分业务是混合读写，只要数据量和并发量较大，一律使用InnoDB。

    常见坑：
    InnoDB的行锁是实现在索引上的，而不是锁在物理行记录上。潜台词是，如果访问没有命中索引，也无法使用行锁，将要退化为表锁。
    画外音：Oracle的行锁实现机制不同。

    例如：
    t_user(uid, uname, age, sex) innodb;

    • uid PK
    • 无其他索引

    update t_user set age=10 where uid=1;
    命中索引，行锁。

    update t_user set age=10 where uid != 1;
    未命中索引，表锁。

    update t_user set age=10 where name=’shenjian’;
    无索引，表锁。

    启示：InnoDB务必建好索引，否则锁粒度较大，会影响并发。

  5. 对比结论
    在大数据量，高并发量的互联网业务场景下，请使用InnoDB:

    • 行锁，对提高并发帮助很大
    • 事务，对数据一致性帮助很大

    这两个点，是InnoDB最吸引人的地方。

InnoDB的高并发模型

  1. 并发控制

    • 同其他并发模型的控制原理，如果对临界资源进行操作，不采取措施会导致不一致的情况
    • 如何进行并发控制？
      • 锁
      • 数据多版本（mv）

  2. 锁

    • 普通锁
    • 共享锁（读）与排他锁（写）

  3. 数据多版本（进一步的提高并发的性能）

    • 写任务发生时，将数据克隆一份，以版本号区分
    • 写任务操作新克隆的数据，直至提交
    • 并发读任务可以继续读取旧版本的数据，不至于阻塞

  4. redo、undo和回滚段

    1. redo
      • 数据库事务提交后，必须将更新后的数据刷到磁盘上，以保证ACID特性。磁盘随机写性能较低，如果每次都刷盘，会极大影响数据库的吞吐量。优化方式是，将修改行为先写到redo日志里（此时变成了顺序写），再定期将数据刷到磁盘上，这样能极大提高性能。
      • redo日志用于保障，已提交事务的ACID特性
    2. undo
      • 数据库事务未提交时，会将事务修改数据的镜像（即修改前的旧版本）存放到undo日志里，当事务回滚时，或者数据库奔溃时，可以利用undo日志，即旧版本数据，撤销未提交事务对数据库产生的影响。
      • undo日志用于保障，未提交事务不会对数据库的ACID特性产生影响。
    3. 回滚段
      • 存储undo日志的地方，是回滚段。

  5. InnoDB的多版本控制的引擎

    • 多版本并发控制（Multi Version Concurrency Control, MVCC）

    • MVCC就是通过“读取旧版本数据”来降低并发事务的锁冲突，提高任务的并发度

    • 旧版本数据存储在回滚段里

    • InnoDB的内核，会对所有row数据增加三个内部属性：

      (1)DB_TRX_ID，6字节，记录每一行最近一次修改它的事务ID；

      (2)DB_ROLL_PTR，7字节，记录指向回滚段undo日志的指针；

      (3)DB_ROW_ID，6字节，单调递增的行ID；

    • 并发高的核心原因就是读快照，即读回滚段的内容不被写事务阻塞

    • 除非显示的加锁，普通的select语句都是快照读，例如：

      • select * from t where id>2;

    • 这里的显示加锁，非快照读是指：

      select * from t where id>2 lock in share mode;

      select * from t where id>2 for update;

InnoDB中不同的锁结构

  1. 自增锁

    • 特殊的表级锁，专门针对事务插入auto_increment的列
    • 假如不是自增的列 t(id unique PK, name)
      • 这里不会使用自增锁

  2. 共享锁、排他锁

    • 标准的行锁，当行索引成功时，锁对应的行，当索引不成功时，锁退化为表锁

  3. 意向锁

    • InnoDB支持多粒度锁(multiple granularity locking)，它允许行级锁与表级锁共存，实际应用中，InnoDB使用的是意向锁。

    • 意向锁是一个表级别的锁

      • 意向共享锁，事务有意向表中某些行加共享锁

      • 意向排他锁，事务有意向对表中的某些行加排他锁

      • 举个例子：

        select … lock in share mode，要设置IS锁；

        select … for update，要设置IX锁；

    • 它会与共享锁/排它锁互斥，其兼容互斥表如下：

      ​ S X

      IS 兼容 互斥

      IX 互斥 互斥

  4. 插入意向锁

    • 多个事务，在同一个索引，同一个范围区间插入记录时，如果插入的位置不冲突，不会阻塞彼此。

  5. 记录锁

    • 记录锁，封锁索引记录，select * from t where id=1 for update; 会在id=1的索引记录上加锁
      • select * from t where id=1; 这个只快照读，并不加锁 ，单纯读不需要加锁，但是事务紧接着会更新数据话，应该要加锁，防止出问题

  6. 间隙锁

    • 封锁索引记录中的间隙

      select * from t

      ​ where id between 8 and 15

      ​ for update;

      会封锁区间，防止其他事务id=10的记录插入，防止幻影数据，导致不可重复读，如果将隔离级别换成读提交，则间隙锁会消失

  7. 临键锁

    • 临键锁会封锁索引记录本身，以及索引记录之前的区间。

数据库索引

  1. 为什么要设计索引？

    • 用于提升数据库的查找速度

  2. 索引结构为什么要设计成树形

    • 对比hash索引和b+树形索引
    • hash树单个查找能力很快，但是大多数的查找情况基于范围的查找，hash查找不适合

  3. 为什么数据库的索引都使用b+树？

    1. 二叉搜索树
      • 数据量大的时候树的高度会比较高，普通的索引
      • 每个节点只存一个数据，磁盘io次数会太多了，聚合在一起，一起传输数据比较多
    2. B树
      • m叉搜索树—树高比较矮
      • 叶子结点和非叶子结点都存储数据
      • 中序遍历访问所有的节点
      • 局部性原理 – 充分提高磁盘io的效率
        • 内存的读写快，磁盘读写慢
        • 磁盘是按页预读
        • 数据读取集中
    3. b+树
      • 非叶子结点不存储数据
      • 叶子结点之间增加了链表
    4. B+树相对B树的更优的特性
      • 范围查找更加快
      • 适合大数据量的磁盘存储
      • B+树可以存储更多的索引

  4. InnoDB的索引

    • InnoDB的索引有两类索引，聚集索引(Clustered Index)与普通索引(Secondary Index)

    • InnoDB的每一个表都会有聚集索引：

      (1)如果表定义了PK，则PK就是聚集索引；

      (2)如果表没有定义PK，则第一个非空unique列是聚集索引；

      (3)否则，InnoDB会创建一个隐藏的row-id作为聚集索引；

    • (1)在索引结构中，非叶子节点存储key，叶子节点存储value；

      (2)聚集索引，叶子节点存储行记录(row)；所以，InnoDB索引和记录是存储在一起的，而MyISAM的索引和记录是分开存储的

      (3)普通索引，叶子节点存储了PK的值；

事务的隔离级别

  1. ACID特性

    • atomic 原子性
    • consistent 一致性
    • isolation 隔离性 即事务之间不发生干扰
    • durability 持久性

  2. 脏读，例如读未提交的状态，读到了被人未提交的数据

  3. 不可重读读， 例如另外一个事务在两次查询之间修改了数据， 就是重复读的数据不一样

  4. 幻读，读到了别人插入的数据，或者重读提交的时候，发生了重读，

  5. 四个隔离级别

    • 读未提交
    • 读提交
    • 可重复读
    • 串行化
    • innoDB通过不同的锁策略来实现这些隔离的级别

  6. 可重复读的实现

    1. 普通的select，即快照读，并发性能最高
    2. 加锁的select(in share mode / select … for update), update, delete等语句, 它们的锁，依赖于它们是否在唯一索引(unique index)上使用了唯一的查询条件(unique search condition)，或者范围查询条件(range-type search condition)：
      • 在唯一索引上使用唯一的查询条件，会使用记录锁(record lock)，而不会封锁记录之间的间隔，即不会使用间隙锁(gap lock)与临键锁(next-key lock)
      • 范围查询条件，会使用间隙锁与临键锁，锁住索引记录之间的范围，避免范围间插入记录，以避免产生幻影行记录，以及避免不可重复的读

  7. 读提交

    这是互联网最常用的隔离级别，在RC下：

    1. 普通的读是快照读
    2. 加锁select、update、insect都只会使用记录锁，除了在外键约束和重复性检查时会封锁区间

  8. 事务的开始时间不一样，会不会影响“快照读”的结果呢？

    快照产生的时间是在select 语句时

  9. 在可重复读的隔离级别下，当第一个Read操作发生的时候，Read view就会建立。 在Read Committed隔离级别下，每次发出Read操作，都会建立新的Read view。