深入理解MySQL
MySQL JDBC装载
- 加载驱动Class.forName(DRIVER);
- 连接数据库Connection connect = DriverManager.getConnection(url,name,password);
- 预编译sql语句PreparedStatement ps = connect.preparedStatement(sql);
- 执行语句 ps.setString(1(/编号/),"");
- 得到返回结果ResultSet rs = ps.execute();
- 关闭连接
MySQL SQL执行顺序
from、on、join、where、group by、having+聚合函数、select、order by、limit
MySQL处理流程
传统sql执行流程:
客户端与服务器建立连接-》发送sql语句-》缓存查询,sql为key存在缓存直接返回(mysql8之后的版本移除)-》分析器-》-》优化器-》执行器
预处理sql执行:
在分析器里面sql解析过后不会执行,由客户端准备数据,服务器再执行数据和预处理sql
MySQL的binlog录入格式
statement,row和mixed。
- statement模式下,每一条会修改数据的sql都会记录在binlog中。不需要记录每一行的变化,减少了binlog日志量,节约了IO,提高性能。由于sql的执行是有上下文的,因此在保存的时候需要保存相关的信息,同时还有一些使用了函数之类的语句无法被记录复制。
- row级别下,不记录sql语句上下文相关信息,仅保存哪条记录被修改。记录单元为每一行的改动,基本是可以全部记下来但是由于很多操作,会导致大量行的改动(比如alter table),因此这种模式的文件保存的信息太多,日志量太大。
- mixed,一种折中的方案,普通操作使用statement记录,当无法使用statement的时候使用row。
MySQL存储引擎MyISAM、InnoDB
- Innodb引擎:Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。
- MyIASM引擎:不提供事务的支持,也不支持行级锁和外键。
MyISAM | Innodb | |
---|---|---|
存储结构 | 每张表被存放在三个文件:frm-表格定义、MYD(MYData)-数据文件、MYI(MYIndex)-索引文件 | 所有的表都保存在同一个数据文件中(也可能是多个文件,或者是独立的表空间文件),InnoDB表的大小只受限于操作系统文件的大小,一般为2GB |
存储空间 | MyISAM可被压缩,存储空间较小 | InnoDB的表需要更多的内存和存储,它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引 |
可移植性、备份及恢复 | 由于MyISAM的数据是以文件的形式存储,所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作 | 免费的方案可以是拷贝数据文件、备份 binlog,或者用 mysqldump,在数据量达到几十G的时候就相对痛苦了 |
文件格式 | 数据和索引是分别存储的,数据.MYD ,索引.MYI | 数据和索引是集中存储的,.ibd |
记录存储顺序 | 按记录插入顺序保存 | 按主键大小有序插入 |
外键 | 不支持 | 支持 |
事务 | 不支持 | 支持 |
锁支持(锁是避免资源争用的一个机制,MySQL锁对用户几乎是透明的) | 表级锁定 | 行级锁定、表级锁定,锁定力度小并发能力高 |
SELECT | MyISAM更优 | |
INSERT、UPDATE、DELETE | InnoDB更优 | |
select count(*) | myisam更快,因为myisam内部维护了一个计数器,可以直接调取。 | |
索引的实现方式 | B+树索引,myisam 是堆表 | B+树索引,Innodb 是索引组织表 |
哈希索引 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
事务的四大特性
- 原子性: 事务是最小的执行单位,不允许分割。事务的原子性确保动作要么全部完成,要么完全不起作用;
- 一致性: 执行事务前后,数据保持一致,多个事务对同一个数据读取的结果是相同的;
- 隔离性: 并发访问数据库时,一个用户的事务不被其他事务所干扰,各并发事务之间数据库是独立的;
- 持久性: 一个事务被提交之后。它对数据库中数据的改变是持久的,即使数据库发生故障也不应该对其有任何影响。
脏读、幻读和不可重复读
- 脏读(Drity Read):某个事务已更新一份数据,另一个事务在此时读取了同一份数据,由于某些原因,前一个RollBack了操作,则后一个事务所读取的数据就会是不正确的。
- 不可重复读(Non-repeatable read):(行级锁)在一个事务的两次查询之中数据不一致,这可能是两次查询过程中间插入了一个事务更新的原有的数据(针对修改)。
- 幻读(Phantom Read):(表级锁)在一个事务的前后多次读取,数据总量不一致(针对插入、删除),例如有一个事务查询了几列(Row)数据,而另一个事务却在此时插入了新的几列数据,先前的事务在接下来的查询中,就会发现有几列数据是它先前所没有的。
事务隔离级别
隔离级别 | 可能脏读 | 可能不可重读 | 可能幻读 |
---|---|---|---|
Read uncommitt | ✔ | ✔ | ✔ |
Read commit | ❌ | ✔ | ✔ |
Repeatable read | ❌ | ❌ | ✔ |
Serializable | ❌ | ❌ | ❌ |
MySQL默认 Repeatable read
- READ-UNCOMMITTED(读取未提交): 最低的隔离级别,允许读取尚未提交的数据变更,可能会导致脏读、幻读或不可重复读。
- READ-COMMITTED(读取已提交): 允许读取并发事务已经提交的数据,可以阻止脏读,但是幻读或不可重复读仍有可能发生。
- REPEATABLE-READ(可重复读): 对同一字段的多次读取结果都是一致的,除非数据是被本身事务自己所修改,可以阻止脏读和不可重复读,但幻读仍有可能发生。
- SERIALIZABLE(可串行化): 最高的隔离级别,完全服从ACID的隔离级别。所有的事务依次逐个执行,这样事务之间就完全不可能产生干扰,该级别可以防止脏读、不可重复读以及幻读。
事务隔离主要依赖于MySQL锁以及MVCC版本控制
MVCC
MVCC全名多版本并发控制,主要目的为提高MySQL Innodb存储引擎的并发性能。
它能够在不加锁的情况下同时进行快照读和写操作,当前读和写操作同时进行会发生并发问题,需要借助锁机制。MySQL借助MVCC完成事务隔离级别读已提交和可重复读
先了解快照读和当前读
快照读
不加锁的select语句
不能读取到大于版本号事务提交的数据
当前读
select…for update 、select …lock in share mode、update、delete、replate into、insert
读取最新版本已提交的数据
版本链
在每一条修改数据行后隐藏三列数据
row_id:图中未标识,如果MySQL中没有索引默认使用该列创建索引
trx_id:事务id与修改该行数据的事务id相同
roll_pointer:指向上一个版本的修改数据行(用于回滚数据,插入语句没有指向)
Read View:一致性视图
trx_ids:表示活跃事务id列表
up_limit_id:活跃事务中的最小事务id
low_limit_id:已创建的最大事务id
creator_trx_id:当前的事务id
实现读已提交
在同一事务中每一次快照读时,都会生成一次一致性视图
判断
当版本链中trx_id<up_limit_id时,说明该数据在事务开启之前已提交可以访问;
当trx_id=creator_trx_id时,说明数据修改为当前事务修改可以访问;
当trx_id>low_limit_id时,说明当前修改在事务开启之后,不可访问;
当trx_id<=low_limit_id时,判断trx_id在不在trx_ids内,如果在说明未提交不可访问,相反可以访问
实现可重复读
可重复读判断方法与读已提交相似,只不过Read View在一个事务中只会生成一次。
遇到START TRANSACTION WITH consistent snapshot或者select语句时开启一致性视图
解决幻读
在可重复读隔离级别下快照读时,其它事务在次期间增加、删除数据,读取结果都不会有变化,但是进行数据修改时就可能产生错误。
解决方法可以是
1.开启串行读隔离级别
2.使用next-key:record lock + gap lock
record lock 为行锁加在索引上面,没有索引变为表锁
gap lock 给索引记录间隙加锁,确保索引间隙不被修改
不用gap lock场景
唯一索引where条件全部命中,除此之外都会使用record lock + gap lock如:
record lock + gap lock
- 只使用唯一索引查询,检索条件是范围检索,或唯一检索而检索结果不存在
- 使用普通索引检索,不管是何种查询,只要加锁,都会产生间隙锁。
- 使用唯一索引和普通索引(数据行优先根据普通索引排序,再根据唯一索引排序)