事务简介
含义:操作的集合
事务操作:
- 开启事务(start transaction),
- 提交事务(commit),
- 回滚事务(rollback);
特性:
- 原子性:(要么全部成功,要么全部失败),
- 一致性:(若存在事务异常,需要回滚事务)设置提交方式:autocommit = 0 手动提交(需要配合commit指令提交操作集合(事务))
- 隔离性:不同事务间不会相互影响
- 持久性:一旦事务提交或回滚,数据库里的数据将永久保存。
并发事务问题:
- 脏读:一个事务A读到,另一个事务B未提交(uncommit)的数据。
- 可重复读:一个事务读同一条记录,两次读取数据不同(其他事务(已提交)对记录进行更改)
- 幻读:一个事务两次重复读数据,但数据行数不同(其他事务(已提交)对记录进行更改)
事务隔离级别:
隔离级别越高:并发性能越差,数据安全性最高。
查看事务隔离级别:select @@transaction_isolation
注:mysql默认隔离级别:可重复读
存储引擎
含义:存储数据、建立索引、更新/查询数据的实现方式。
INNODB:特点:
- 事务:dml(增删改查)遵循ACID原则 (myisam不支持)
- 行级锁:提高并发访问性能 (myisam不支持,支持表锁)
- 外键(foreign key):保证数据完整性与正确性
逻辑存储结构:
表(段(区(页(行))))一个区固定1m;一个页固定16k;故:一个区 = 64页;
索引
含义: 目录作用的有序数据结构(B+树)。无索引的获取数据的方式 ----> 全表扫描(效率低)
优缺点:
结构问题:
二叉树:层级多,i/o多搜索效率低
B树:所有节点都会保存数据,导致一层可以查找的数据量少,同量数据B数的层级比B+树多
Hash:无法支持范围匹配与排序操作。
索引分类:
聚集索引选取规则:
- 辅助索引 叶子结点 ---> 聚焦索引(主键索引)
- 主键索引 叶子结点 ---> 数据
回表查询:先走二级索引获得主键值,在通过主键值获得行数据信息
索引使用规则:最左前缀原则
索引失效:
模型树空运最快;
- 模:代表模糊查询的意思,我们在使用like关键字的时候,如果是%开头,那么索引就会失效。
- 型:代表数据类型,如果数据类型错误了,索引也会失效。
- 数:代表函数的意思,对索引的字段使用内部函数,索引也会失效,这种情况应该建立基于函数的索引。
- 空:代表英文的 null 的意思,索引不存储空值,如果不限制索引列是 Not Null,数据库会认为索引列有可能存在空值,所以也不会按照索引进行计算。
- 运:代表运算的意思,对索引列进行加、减、乘、除等运算,会导致索引失效。
- 最:代表最左原则的意思,在复合索引中,索引列的顺序非常重要,如果不是按照索引列最左列开始进行查找,则无法使用索引。
- 快:代表全表扫描更快的意思,若数据库预计使用全表扫描比使用索引更快就不会使用索引。
覆盖索引:
通过建立普通索引或联合索引,避免回表查询的查询策略,直接通过二级索引拿到行数据。
- 通过二级索引获取想要所有内容,无需回表进行聚焦索引搜索,覆盖索引。
- 通过二级索引无法获取全部内容,需要回表查询。
索引设计原则:
- 数据量大于10w级,查询较频繁
- 经常使用查询排序分组的字段索引
- 尽量使用唯一索引,区分度越高索引效率越高
- 长字段数据,根据字段特点建立前缀索引
- 尽量多使用联合索引,实现索引覆盖减少回表查询
- 控制索引数量,多索引维护代价大,内存占用多,影响增删改效率
- 不能存储NULL至,可通过NOT NULL进行约束。
SQL优化:
- insert优化(一条一条插入效率低)
批量插入:
手动事务提交:
!!巨量数据插入:load (符合一定规则的字符串(不符合sql语法))
- 主键优化
页分裂:
插入数据:
数据页分裂(50%页移植到新页,再插入)
链表顺序重拍(顺序排并)
页合并
前页存在多删,满足50%空闲,判断可否页合并
页合并,空闲新页
设计原则:
- 降低主键长度
- 尽量顺序操作,否则页分裂
- 尽量不用无序UUID作为主键(身份证号
- limit 优化
L:在访问巨值的限制查询,需要将0-巨量数据尾进行排列,取对应的限制区间,耗时长效率低。
解决思路:覆盖索引,
- update 优化
若通过索引进行事务update,不同行间相互隔离,可以提交(上行级锁)
若通过无索引字段(如name)进行事务update,不同行间无法同时提交(上表级锁)
锁
-
粒度分类:
全局锁 (实现一致性数据备份):锁住数据库全部的表,[ 实例变成只读状态,无法进行DML与DDL语句,更新操作全阻塞
表级锁:每次操作锁住整张表
- 读锁:共享锁 (lock tables) (unlock tables)[可读不可写]
- 写锁:独占锁 [本客户端可读写,其他客户端不可读写]
- 意向锁:解决行锁表锁冲突(表锁在加锁的时候逐行检查行锁), 不用逐行检查行锁情况。
- 意向共享锁:与读锁兼容,排他所互斥。
- 意向排他所:与读锁写锁都互斥。
行级锁:每次操作锁住对应行数据,针对索引项加锁
- 行锁
- 间隙锁(解决幻读现象)
- 临键锁
InnoDB引擎
存储结构
内存结构:
脏页:未被刷入磁盘的数据
change buffer:二级索引修改后,等刷盘时放入bufferPool中,一起更改
redolog(持久性)
:脏页刷盘出现错误,保证事务的持久性(先保存至redobuffer 刷盘到 redolog文件(WAL模式:先存日志后刷数据(顺序磁盘IO效率高))每读一段数据redolog会变更。
注意:BufferPool中是将多操作随机操作数据页,随机刷新进入磁盘,随机IO的性能效率低!!!
undolog(原子性)
:电脑宕机回滚事务、事务更新后系统不稳定需要回滚版本、MVCC
MVCC;
隐式字段
readview
MYSQL语句执行原理