二、MySQL深入

一、MySQL事务与锁

1、事务可靠模型 ACID

• Atomicity原子性：一次事务中所有操作要么都成功，要么都不成功。
• Consistency一致性：跨表、跨行、跨事务、跨数据库始终保持一致。
• Isolation隔离性、可见性：保护事务不相互干扰，包括四种隔离级别。
• Durability持久性：事务提交成功后，不会丢数据，比如：断电、系统奔溃。

 

2、锁

2.1、表级锁

意向锁，表明事务稍后要进行那种类型的锁定，上锁前先上意向锁分类：

• 共享意向锁IS
• 排他意向锁IX
• Insert意向锁

 

确定锁：

• 共享锁S
• 排他锁X

锁类型兼容性
	X	IX	S	IS
X	不兼容	不兼容	不兼容	不兼容
IX	不兼容	兼容	不兼容	兼容
S	不兼容	不兼容	兼容	兼容
IS	不兼容	兼容	兼容	兼容

 

 

其他：

• 自增锁
• Lock Tables/DDL

 

2.2、行级锁(InnoDB)

• 记录锁：始终锁住索引记录，注意隐藏聚簇索引。
• 间隙锁：锁住一个范围。
• 临建锁：记录锁+间隙锁。
• 谓词锁：空间索引。

 

3、四种隔离级别以及对应问题

1. 读未提交：脏读、幻读、不可重复读。
2. 读已提交：幻读、不可重复读。
3. 可重复读：幻读。（MySQL默认）

  问题描述：

• 脏读：使用到了其他事务未提交的数据。
• 不可重复读：不加锁的情况下，其他事务update会对结果集产生影响。
• 幻读：相同的查询语句，在事务内的不同时间段查询，有不同结果。

 

4、事务相关日志

4.1、撤销日志undo log

用处：保证事务原子性，用于事务回滚，记录着事务回滚时所需的撤销操作。

 

4.2、重做日志redo log

用处：保证事务的持久性，防止事务提交后数据未刷新到磁盘就断掉或奔溃，记录着事务对数据页做了哪些操作。

• 提升性能：WAL(write-ahead-logging)技术，先写日志，再写磁盘。
• 日志文件：ib_logfile0、ib_logfile1。
• 日志缓冲：innodb_log_buffer_size。
• 强刷：fsync()。

 

4.3、如何保证刷盘

Mysql提供了参数innodb_flus_log_at_trx_commit，有三个配置选项：

• 1：保证CAID，当commit事务是，必须将redo log buffer中的数据刷新到磁盘中，确保只要commit成功，磁盘上就有对应的redo log日志。
• 0：每秒写一次日志，并将其刷到磁盘。
• 2：当commit时，将redolog buffer中的数据刷到系统缓存page cache中，然后依托操作系统每秒刷新一次的机制同步到磁盘中。

 

4.4、脏页是什么

当内存数据页跟磁盘数据页不一致是，就把内存页称为“脏页”。

 

5、MVCC多版本并发控制

通过读取历史版本数据，类似于快照，来降低并发事务冲突、挺高并发性能的一种机制，通过以下几种功能结合实现：

• 事务版本号
• 表隐藏列
• undo logo
• read view（事务进行快照读操作时产生的读视图）

举例：

事务A	事务B
开启事务	开启事务
快照读数据为500	快照读数据为500
修改数据为400
提交事务
	select 快照读为500
	select 当前读为400

事务A	事务B
开启事务	开启事务
快照读数据为500
修改数据为400
提交事务
	select 快照读为400
	select 当前读为400

 

总结：核心在于事务首次出现快照的地方

 

二、DB优化总结

1、建表的时候，注意数据类型和存储引擎的选择。

2、简单SQL可能带来大问题，where条件中注意数据类型，避免类型转换的问题。

3、数据量变大，查询变慢，定位问题可以用慢日志。

4、主键强调递增，避免出现页分裂情况。

5、索引字段选择：最左原则，字段重复值越少越好。

6、修改表会出现索引重建、锁表、抢占资源、主从延时等问题。

7、主键索引会比非主键索引快，因为非主键索引对应的二级索引文件末级存的不是数据页，而是主键ID，查询时先要拿到主键ID，再到对应的聚簇索引文件找对应的数据。

 

三、SQL优化总结

 1、大批量写入的优化

• preparedstatement减少SQL解析。
• Multiple vuales / Add Batch 减少交互次数。
• Load Data直接导入。
• 索引和约束问题。

 

2、数据更新

• 注意更新时锁的范围，避免与其他事务发生冲突。最好直接用ID来更新，减少锁范围。

 

3、模糊查询

• 前缀匹配走索引（xx%）。
• 数据量大可以使用solr/ES。

 

4、连接查询

• 选择驱动表问题，驱动表越小数据越明确。
• 避免笛卡尔积，连接导致n*m*l的结果集。

 

5、索引失效

• 后缀like、NULL、not、not in、函数等都不走索引。
• 减少使用or，可以用union代替。
• 隐式转换

 

6、查询SQL怎么设计

•  查询数据量和查询次数的平衡。
• 避免不必须的大量重复数据传输。
• 避免使用临时表或临时文件排序。
• 分析类需求，可以用汇总表，宽表。

 

四、常见场景使用

 1、怎么实现主键ID

• 自增
• 模拟seq
• UUID
• 时间戳+随机数
• snowflake雪花算法（机器+时间+随机数）

 

2、高效分页

• 使用分页插件。
• 改写count。
• 反序。
• 带ID。
• 非精准分页。

 

3、乐观锁与悲观锁

• 悲观锁：select * from xxx for update；排他锁x。
• 乐观锁：select * from xxx；update xxx where value = old value；先查旧数据，再把旧数据作为条件去update。存在aba问题，中途可能被其他事务改了其他值后有改成旧值。