MySQL 开发随笔
如何设置事务隔离级别
事务隔离级别的介绍
事务表示多个数据操作组成一个完整的事务单元,在这个事务内的所有数据操作要么同时成功,要么同时失败。数据库系统必须具有隔离并发运行各个事务的能力,使它们不会相互影响,避免各种并发问题。一个事务与其他事务隔离的程度称为 隔离级别
。SQL 标准中规定了多种事务隔离级别,不同隔离级别对应不同的干扰程度,隔离级别越高,数据一致性就越好,但数据库的并发性能越差。为了解决事务并发问题(脏读、不可重复读、幻读),主流的关系型数据库都会提供以下四种事务隔离级别。
- 读未提交(Read Uncommitted)
- 在该隔离级别,所有事务都可以看到其他未提交事务所做的改变。
- 该隔离级别是最低的隔离级别,虽然拥有超高的并发处理能力及很低的系统开销,但很少用于实际应用。
- 该隔离级别只能解决第一类更新丢失问题,不能解决脏读、不可重复读、幻读的问题。
- 读已提交(Read Committed)
- 这是大多数数据库系统的默认隔离级别(但不是 MySQL 默认的),例如 Oracle 数据库。
- 该隔离级别满足了隔离的简单定义:一个事务只能看见已提交事务所做的改变。
- 该隔离级别可以解决脏读问题,但会出现不可重复读、幻读问题。
- 可重复读(Repeatable Read)
- 这是 MySQL 的默认事务隔离级别,它可以确保在整个事务过程中,对同一条数据的读取结果是相同的,不管其他事务是否在对共享数据进行更新,也不管其他事务更新提交与否。
- 该隔离级别可以解决脏读、不可重复读的问题,但会出现幻读问题。
- 串行化(Serializable)
- 这是最高的事务隔离级别,它通过强制事务排序,使之不可能相互冲突,从而解决脏读、不可重复读、幻读、第一类更新丢失、第二类更新丢失问题。
- 该隔离级别可以解决所有的事务并发问题,但可能导致大量的超时现象和锁竞争,通常数据库不会用这个隔离级别,可用其他的方案来解决这些问题,例如乐观锁和悲观锁。
事务隔离级别的案例
上图中是典型的第二类丢失更新问题,后果非常严重。当数据库隔离级别为读已提交(Read Committed)及以下隔离级别时,会出现不可重复读的现象。从上面的表格可以看出,当事务隔离级别设置为可重复读(Repeatable Read)时,可以避免不可重复读的现象出现。
事务隔离级别的设置
1 | -- 设置下一次事务的隔离级别 |
事务隔离级别的总结
上述四种事务隔离级别会产生的并发问题如下(YES 表示存在对应的问题,NO 表示不存在对应的问题):
各种关系型数据库对事务隔离级别的支持程度如下(YES 表示支持,NO 表示不支持):
Oracle | MySQL | |
---|---|---|
Read Uncommitted | NO | YES |
Read Committed | YES (默认) | YES |
Repeatable Read | NO | YES (默认) |
Serializable | YES | YES |
提示
- 在 Spring 框架中,事务隔离级别可以通过
@Transactional
注解中的isolation
属性定义。 - MySQL 的默认隔离级别是可重复读 (Repeatable Read),而 Oracle 的默认隔离级别是读已提交 (Read Committed)。
数据库表如何删除重复数据
面试题
线上数据库表存在重复数据,如何删除同一字段下的所有重复数据,或者仅仅留下一条数据。
- 创建表,并插入数据
1 | CREATE TABLE `student` ( |
- 查看
name
字段的重复数据
1 | SELECT NAME, COUNT(1) FROM student GROUP BY NAME HAVING COUNT(1) > 1; |
1 | +------+-------------+ |
1 | -- 或者 |
1 | +----+------+------+ |
- 删除
name
字段下的所有重复数据,只要有重复都删除掉
1 | DELETE FROM student WHERE NAME IN ( |
1 | -- 查询删除重复数据后的结果 |
1 | +----+------+------+ |
- 删除
name
字段下的重复数据,仅仅留下一条数据
1 | -- 此方法要求主键(ID)为自增长 |
1 | -- 查询删除重复数据后的结果 |
1 | +----+------+------+ |
优化千万级数据的分页查询
优化的关键
在 SELECT
语句中,可以使用 LIMIT
子句来约束结果集中的行数。LIMIT
子句接受一个或两个参数,两个参数的值都必须为零或者正整数,具体的参数说明如下:
offset
:指定要返回的第一行数据的偏移量。第一行数据的偏移量为 0,而不是 1。count
:指定要返回的最大行数。
1 | SELECT column1, column2, ... FROM table LIMIT offset, count; |
假设 emp
表中有 600 万条的数据,使用以下 SQL 进行分页查询,大概需要花费 18 秒才能查询得到结果。
1 | SELECT SQL_NO_CACHE |
为什么上面的查询会耗费那么多时间呢?其实这是因为 LIMIT
后面的偏移量太大导致的。比如 LIMIT 4950000, 30
,这等同于数据库要扫描出 4950030 条数据,然后再丢弃前面的 49500000 条数据,最后返回剩下的 30 条数据给用户,显然这种做法是不合理的。MySQL 的分页操作通常会使用 LIMIT
加上偏移量的办法来实现,可能还会加上合适的 ORDER BY
子句。当偏移量非常大的时候,它会导致 MySQL 扫描大量不需要的行,然后再抛弃掉,这在生产环境存在一定的性能隐患。在阿里巴巴的开发手册中,提到以下的分页查询优化方案:
优化的方案
方案一,使用覆盖索引 + 子查询
如果数据库表中有 ID 字段,并且在上面建立了索引(主键索引),那么可以先在索引树中找到开始位置的 ID 值,然后再根据找到的 ID 值查询数据。
1 | SELECT |
方案二,起始位置重定义
记住上次分页查询结果的主键位置,避免使用 LIMIT
的偏移量 offset
。比如,记住上次分页查询结果的最后一条数据的 ID 是 4950000,然后就直接跳过 4950000,从 4950001 开始扫描表进行查询。
1 | SELECT |
方案三,服务降级不可用
这是阿里巴巴推荐的解决方案,首先配置
LIMIT
的偏移量和获取行数的最大值,超过这个最大值,就返回空数据。因为按照业务来说,超过这个最大值后,用户已经不是在分页了,而是在刷数据。如果确认要查询数据,应该输入合适的查询条件来缩小范围,而不是一页一页地分页查询。当用户发出请求后,如果
offset
大于某个数值就直接返回一个4xx
的错误,避免黑客攻击或者刷单行为。毕竟,正常人很少翻查 10 页以后的内容。另外,该有的服务降级、限流也应该考虑进去。比如,用户利用多线程工具调用接口,在短时间内执行 5000 次调用,那么就可以简单地使用计数器进行判断,并反馈给用户调用过于频繁,最后直接抛弃用户的请求。
如何往拥有千万级数据量的表添加索引
在千万级数据量的表中添加索引,操作步骤为:创建新表 + 创建索引 + 导入旧数据 + 废弃旧表。简而言之,” 腾笼换鸟”,稳定性压倒一切,保守一点不出错即可。
- (1) 先创建一张跟原表 A 数据结构相同的新表 B。
- (2) 在新表 B 上建立新的索引。
- (3) 将原表 A 的数据迁移到新表 B。
- (4) 将原表 A 改为别的表名,并将新表 B 改为原表的表名 A。
特别注意
在给表添加索引的时候,是会对表加锁的(表锁)。如果不谨慎操作,可能会出现严重的生产事故。比如,在添加索引的过程中,如果发生了数据修改(如 DELETE、UPDATE),则可能会导致客户端读取到不一致或错误的数据。因此,对应大数据量的表,需要使用 "腾笼换鸟" 的方式来添加索引。