MySQL一遍过

一、MySQL引擎

1.概述

• 引擎就是数据在处理数据时的机制
• 不同的引擎执行策略/机制不同
• 不同的需求选用不同的引擎,到达最优选择

2.Innodb

• Innodb是一个事务型的存储引擎,有行级锁和外键约束
• 适应场景:需要事务,并发大,支持外键约束,主键自增
• 以前版本不支持全文检索

3.myisam

• 不支持事务,外键,不支持行级锁
• 查询快

二、索引

1.概述

• 就是为了方便数据查询所诞生的一种数据结构
  • 类似于书中的目录
• 优点:
  • 方便查询,降低IO成本
• 缺点:
  • 索引也需要占用磁盘空间
  • 修改删除新增时也需要对索引进行维护
    • 删除某条动态时,相应的索引也会做出变化

2.索引分类

• 主键索引
  • 设置主键后数据库会自动建立索引
• 单值索引
  • 一个索引只包含单个列,一个表可以有许多单列索引
• 唯一索引
  • 索引值必须唯一,运行为null
• 复合索引
  • 一个索引包括多个列,在数据库操作期间,复合索引比单值索引开销更小,当表的行数远大于索引数目时可以使用复合索引

3.索引创建原则

• 哪些情况下适合创建索引
  • 主键,自动创建唯一索引
  • 频繁作为查询条件(where后面的语句)
  • 外键
  • 排序字段,可通过索引去访问
• 哪些情况不适合创建索引
  • 表记录太少
  • 经常增删改的表
    • 提高了查询速率
    • 降低了更新表的速率,对表进行更新时,索引也会改动
  • where里面用不到的字段
  • 数据重复且分布平均的表字段,
    • 所以,经常查询和排序才创建索引,
    • 而某个数据若包含重复内容,那么建立索引没有太大的实际效果

4.索引的数据结构

• B+树
  • 一个节点可以存储多个数据
  • 非叶子节点值存储索引,不存数据
  • 数据只在叶子节点上存储,而且数据与数据之间有连接,适合范围查询
  • 树的高度是固定的,查询快

5.聚簇索引和非聚簇索引

• 判断标准:是否有回表查询
• 聚簇索引:
  • 找到了索引就找到了数据,索引和数据一一对应,主键就是聚簇索引
  • 不需要回表查询的索引也是聚簇索引
  • 使用主键查询数据,或者使用非主键查询索引列
• 非聚簇索引
  • 回表查询
  • 先通过非主键查询,查询到主键,在通过主键查询需要的数据
• 可以理解为
  • 聚簇索引的叶子结点是数据,
  • 非聚簇索引的叶子节点是主键索引的值
    • 在通过查询到的索引值在通过查询得到的主键来二叉查询数据,这个过程就叫做回表

三、事务

1.概念

• 事务是数据库管理系执行过程中的一个逻辑单位
• 在MySQL中只有Innodb数据库引擎的数据库或表才支持事务
• 事务处理保证了维护数据库的完整性,保证了SQL要么全部执行,要么全部不执行
• 事务管理的语句 insert,update,delete

2.事务特性

• 原子性,一致性,隔离性,持久性
• 原子性:
  • 一个事务中的所有操作,要么全部完成,要么全部不完成
  • 事务在执行过程中发生错误,会回滚到事务开始前的状态
• 持久性:
  • 事务处理结束后,对数据的更新时永久的,即使系统故障也不会丢失
• 隔离性
  • 数据库允许多个并发事务同时对其数据进行读写和修改的能力
  • 隔离性可以防止由于交叉执行而导致数据不一致
  • 事务隔离被分为不同级别,读未提交,读提交,可重复读,串行化
• 一致性:
  • 多次对数据库操作后,最终的数据与我们的预期值一致

3.事务设置

• 默认情况下,MySQL自动提交事务,
  • 当你执行DML操作时,MySQL会立即隐式提交事务
• MySQL事务处理主要俩种方法
  • 用 BEGIN, ROLLBACK, COMMIT 来实现
    • BEGIN – 开启事务
    • insert into depr(NAME) value(‘财务部’)
    • ROLLBACK – 事务回滚
    • COMMIT – 提交事务
  • 查看事务类型
    • 默认是自动提交事务
    • show GLOBAL VARIABLES like ‘autocommit’;
    • 修改是否为自动提交
    • set GLOBAL autocommit=0
    • 0禁止自动提交
    • 1开启自动提交

4.并发事务处理带来的问题

• 脏读,不可重复读,幻读
• 脏读:(读取到其他事务为提交的数据)
  • 事务B更新年龄为18岁
  • 事务A读取数据库信息,年龄为18岁
  • 事务B回滚
  • 此时事务A回去到错误的信息
• 不可重复读:
  • 事务A读取年龄为18岁
  • 事务B更新年龄为20岁
  • 事务A在次读取年龄为18岁
  • 导致事务A先后读取俩吃数据,俩次结果不一致
• 幻读:
  • 事务A读取年龄大于15的数据,发现只有一条数据
  • 事务B插入一条年龄大于15的数据,并提交
  • 事务A在次读取数据时,发现有2跳记录

5.事务隔离级别

• 只有InnoDB支持事务,所以事务隔离也就是InnoDB下的事务隔离
• 查看当前会话隔离级别
  • mysql5
    • show VARIABLES like ‘tx_isolation’;
  • mysql8
    • select @@global.transaction_isolation,@@transaction_isolation
• 设置当前会话隔离级别
  • set session TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
• 读未提交
  • 一个事务可以读取另一事务未提交的修改
  • 会带来脏读等问题
• 读以提交
  • 一个事务只能读取另一事务以提交的修改
  • 可以避免脏读
• 可重复读
  • 同一事物中多次读取相同的数据返回的结果是一样的
  • 避免了脏读和不可重复读问题
• 串行化
  • 事务串行执行,避免了幻读等所有问题

6.锁

• 概述:
  • 事务在修改数据之前,先获得相应的锁,
  • 获得锁后,事务修改数据
  • 改事务操作时,这部分数据锁定,其他事物若想修改数据,需要等待事务提交或回滚后释放锁
• 表锁:
  • 操作时数据会锁定整张表,并发性较差
  • 被大部分mysql引擎支持,常见的右MYISAM和INNODB
  • 特点:
    • 开销小,加锁快
    • 不会出现死锁
    • 锁的粒度大,发出锁冲突的概率最高,并发度最低
• 行锁:
  • 操作时锁定需要操作的行,并发性好点儿
  • 特点:
    • 开销大,加锁慢;
    • 会出现死锁
    • 锁定粒度最小,发生锁冲突最低,并发度最高
  • 共享锁:读锁(S)
    • 可以有多个事务进行查询
  • 排他锁(X):写锁
    • 修改删除添加排他锁,只有一个事务对此进行操作
    • 查询也可以添加排它锁select …for update

7.MVCC（多版本并发控制 Multi-Version Concurrent Control）

• 是MySQL提高性能的一种方式
• 使用Undo log和版本链,替代锁,让不同的事务,读写,写读可以并发操作,从而提升系统性能.
• 使用InnoDB存储引擎时,它的聚簇索引都会有俩个必要的隐藏列
  • trx_id:
  • 每次对某条聚簇索引记录进行改动时,都会把对应的事务id赋给trx_id
  • roll_pointer
    • 每次对某条聚簇索引记录进行改动时,都会把旧的版本写入Undo日志中,然后这个隐藏列就相当于一个指针,可以通过他来找到该记录修改前的信息
• 基本特征
  • 每行数据都存在一个版本,每次数据更新时都更新该版本
  • 修改时Copy出当前版本随意修改,各个事务之间无干扰
  • 保存时比较版本号,如果成功,则覆盖原纪录,失败则放弃copy
  • 每次对记录进行改动,都会记录一条undo日志,每条日志也都有一个roll_pointer属性,可以将这些undo日志连接起来,形成一个链表
  • 每次记录更新后,都会将旧日志放在一条undo日志中,就算记录一个旧版本,随着更新次数的增多,所有版本都会被roll_pointer属性连城一个链表,我们把这个链表称为版本链,链表的头节点就算当前记录最新的值.且每个版本中还生成对应的事务id,这个信息就很重要

8. ReadView

• 使用读未提交:从版本链读取,读取最新的,事务还没提交,可以撤销
• 串行:加锁了,直接读取表中的数据
• 读已提交:每次读数据时,都会生成readview(将所有的事务操作记录在版本链中拍了个照)造成了不可重复复
• 可重复读:第一次读取时生成readview,解决了不可重复度

9.视图

• 将查询语句封装到视图中
• 查询就可以以视图为虚拟表
• 定义视图
  • CREATE VIEW 视图名 AS SELECT 列 1，列 2… FROM 表(查询语句);
• 使用视图
  • SELECT * FROM 视图名
• 删除视图
  • drop view 视图名