声明:本文内容是在B站学习了黑马程序员的mysql课程,结合黑马程序员所提供的资料以及我个人在网上整理所汇总的内容(部分基础内容没有进行整理,主要是包括mysql高级部分),侵权删。
课程链接如下:
黑马程序员MySQL数据库入门到精通,从mysql安装到mysql高级、mysql优化全囊括
目录
优化
定位慢查询
1.使用mysql自带的慢查询日志
慢查询日志默认不开启,建议手动开启,方便我们定位线上问题。
执行时间超过阈值的SQL会被写入到慢查询日志当中,这样可以帮助我们记录执行时间过长的SQL语句,定位线上慢SQL问题,方便我们进行SQL性能调优。
查看是否开启了慢查询日志
show variables like 'slow_query_log'
默认是OFF,不开启,可以手动开启。
开启慢查询日志
一种方法是可以使用MySQL命令开启:
set global slow_query_log=1;
另一种方法是修改MySQL配置文件,重新MySQL服务后,开启。
修改配置文件/etc/my.cnf,加入下面一行命令 slow_query_log = ON
设置慢查询日志的阈值
慢查询日志的阈值默认是10,单位是秒。对于线上服务来说,10秒太长了,我们可以手动修改。
一种是通过MySQL命令修改,比如修改为1秒:
set long_query_time=1;
另一种方法是修改MySQL配置文件,重新MySQL服务后开启。
修改配置文件/etc/my.cnf,加入下面一行命令 long_query_time = 1
修改慢查询日志位置
使用MySQL命令查看慢查询日志位置:
show variables like '%slow_query_log_file%';
想要修改慢查询日志位置,可以修改MySQL配置文件,重新MySQL服务后,开启。
修改配置文件/etc/my.cnf,加入下面一行命令 slow_query_log_file = /usr/local/mysql/data/localhost_slow.log
记录更多慢查询SQL
默认情况下管理语句是不会被记录到慢查询日志中,管理语句包括ALTER TABLE、 ANALYZE TABLE、 CHECK TABLE、 CREATE INDEX、 DROP INDEX、 OPTIMIZE TABLE和 REPAIR TABLE等。
如果想要被记录,可以通过MySQL命令修改:
set global log_slow_admin_statements=ON;
默认情况下,不使用索引的语句,是不会被记录的。
够坑人吧!一不留神就掉坑里了,不记录不使用索引的语句,还要慢查询日志干嘛?
想要记录不使用索引的语句,可以通过命令修改:
set global log_queries_not_using_indexes=ON;
分析慢查询日志
有时候慢查询日志较多,手动查看起来并不是很方便,好在MySQL提供了分析慢查询日志的工具mysqldumpslow。
常用参数有: -s: 表示按何种方式排序 c: 访问次数 l: 锁定时间 r: 返回记录 t: 查询时间 al: 平均锁定时间 ar: 平均返回记录数 at: 平均查询时间 -t: 返回前面多少条的数据
例如:
查询返回结果最多的10条SQL:
mysqldumpslow -s r -t 10 /usr/local/mysql/data/localhost_slow.log
查询耗时最长的10条SQL:
mysqldumpslow -s t -t 10 /usr/local/mysql/data/localhost_slow.log
2.使用其他开源工具
比如调试工具:Arthas
运维工具:Prometheus、Skywalking
SQL性能分析
SQL执行频率
MySQL 客户端连接成功后,通过 show [session|global] status 命令可以提供服务器状态信 息。通过如下指令,可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次:
-- session 是查看当前会话 ; -- global 是查询全局数据 ; SHOW GLOBAL STATUS LIKE 'Com_______';
Com_insert: 插入次数
Com_select: 查询次数
Com_update: 更新次数
Com_delete:删除次数
通过上述指令,我们可以查看到当前数据库到底是以查询为主,还是以增删改为主,从而为数据库优化提供参考依据。 如果是以增删改为主,我们可以考虑不对其进行索引的优化。 如果是以查询为主,那么就要考虑对数据库的索引进行优化了。
explain
使用 EXPLAIN 或者 DESC 命令获取 MySQL 如何执行 SELECT 语句的信息,包括在 SELECT 语句执行过程中表如何连接和连接的顺序。
-- 直接在select语句之前加上关键字 explain 或 desc EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;
Explain 执行计划中各个字段的含义:
字段 | 含义 |
---|---|
id | select查询的序列号,表示查询中执行select子句或者是操作表的顺序 (id相同,执行顺序从上到下;id不同,值越大,越先执行) |
select_type | 表示 SELECT 的类型,常见的取值有 SIMPLE(简单表,即不使用表连接 或者子查询)、PRIMARY(主查询,即外层的查询)、 UNION(UNION 中的第二个或者后面的查询语句)、 SUBQUERY(SELECT/WHERE之后包含了子查询)等 |
type | 表示连接类型,性能由好到差的连接类型为NULL、system(查询系统中的表)、const(根据主键查询)、 eq_ref(主键索引查询或唯一索引查询)、ref(索引查询)、range(范围查询)、 index(索引树扫描)、all(全盘扫描)。性能最好在range及之前比较好。 |
possible_key | 显示可能应用在这张表上的索引,一个或多个 |
key | 实际使用的索引,如果为NULL,则没有使用索引。 |
key_len | 表示索引中使用的字节数, 该值为索引字段最大可能长度,并非实际使用长度,在不损失精确性的前提下, 长度越短越好 。 |
filtered | 表示返回结果的行数占需读取行数的百分比, filtered 的值越大越好。 |
rows | MySQL认为必须要执行查询的行数,在innodb引擎的表中,是一个估计值, 可能并不总是准确的 |
Extra | Using where;Using Index :查找使用了索引,需要的数据都在索引中能找到,不需要回表查询数据。 Using index condition:查找使用了索引,但是需要回表查询数据 null:没有使用索引 |
show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling 参数,能够看到当前MySQL是否支持profile操作:
show profiles
show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling 参数,能够看到当前MySQL是否支持profile操作:
SELECT @@have_profiling ;
可以看到,当前MySQL是支持 profile操作的,但是开关是关闭的。可以通过set语句在 session/global级别开启profiling。
SET profiling = 1;
开关已经打开了,接下来,我们所执行的SQL语句,都会被MySQL记录,并记录执行时间消耗到哪儿去 了。
我们直接执行如下的SQL语句:
select * from tb_user; select * from tb_user where id = 1; select * from tb_user where name = '白起'; select count(*) from tb_sku;
执行一系列的业务SQL的操作,然后通过如下指令查看指令的执行耗时:
-- 查看每一条SQL的耗时基本情况 show profiles; -- 查看指定query_id的SQL语句各个阶段的耗时情况 show profile for query query_id; -- 查看指定query_id的SQL语句CPU的使用情况 show profile cpu for query query_id;
查看每一条SQL的耗时情况:
查看指定SQL各个阶段的耗时情况 :
索引
索引(index)是帮助MySQL高效获取数据的数据结构(有序)。在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据, 这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引。
MySQL的索引是在存储引擎层实现的,不同的存储引擎有不同的索引结构,主要包含以下几种:
索引 | InnoDB | MyISAM | Memory |
---|---|---|---|
B+tree索引 | 支持 | 支持 | 支持 |
Hash 索引 | 不支持 | 不支持 | 支持 |
R-tree 索引 | 不支持 | 支持 | 不支持 |
Full-text | 5.6版本之后支持 | 支持 | 不支持 |
索引结构 | 描述 |
---|---|
B+Tree索引 | 最常见的索引类型,大部分引擎都支持 B+ 树索引 |
Hash索引 | 底层数据结构是用哈希表实现的, 只有精确匹配索引列的查询才有效, 不 支持范围查询 |
R-tree(空间索 引) | 空间索引是MyISAM引擎的一个特殊索引类型,主要用于地理空间数据类 型,通常使用较少 |
Full-text(全文 索引) | 是一种通过建立倒排索引,快速匹配文档的方式。类似于 Lucene,Solr,ES |
B+Tree
-
绿色框框起来的部分,是索引部分,仅仅起到索引数据的作用,不存储数据。
-
红色框框起来的部分,是数据存储部分,在其叶子节点中要存储具体的数据。
B+Tree 与 B-Tree相比,主要有以下三点区别:
-
所有的数据都会出现在叶子节点。
-
叶子节点形成一个单向链表。
-
非叶子节点仅仅起到索引数据作用,具体的数据都是在叶子节点存放的。
MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上,增加一个指向相邻叶子节点 的链表指针,就形成了带有顺序指针的B+Tree,提高区间访问的性能,利于排序。
索引分类
在InnoDB存储引擎中,根据索引的存储形式,可以分为以下两种:
分类 | 含义 | 特点 |
---|---|---|
聚集索引(Clustered Index) | 将数据存储与索引放到了一块,索引结构的叶子节点保存了行数据 | 必须有,而且只 有一个 |
二级索引(Secondary Index) | 将数据与索引分开存储,索引结构的叶子节点关联的是对应的主键 | 可以存在多个 |
聚集索引选取规则:
-
如果存在主键,主键索引就是聚集索引。
-
如果不存在主键,将使用第一个唯一(UNIQUE)索引作为聚集索引。
-
如果表没有主键,或没有合适的唯一索引,则InnoDB会自动生成一个rowid作为隐藏的聚集索引。
聚集索引和二级索引的具体结构如下:
-
聚集索引的叶子节点下挂的是这一行的数据 。
-
二级索引的叶子节点下挂的是该字段值对应的主键值。
们执行如下的SQL语句时,具体的执行流程
①. 由于是根据name字段进行查询,所以先根据name='Arm'到name字段的二级索引中进行匹配查找。但是在二级索引中只能查找到 Arm 对应的主键值 10。
②. 由于查询返回的数据是 *,所以此时,还需要根据主键值10,到聚集索引中查找10对应的记录,最终找到10对应的行数据。
③. 最终拿到这一行的数据,直接返回即可。
回表查询: 这种先到二级索引中查找数据,找到主键值,然后再到聚集索引中根据主键值,获取数据的方式,就称之为回表查询。
思考题: 以下两条SQL语句,那个执行效率高? 为什么?
A. select * from user where id = 10 ;
B. select * from user where name = 'Arm' ;
备注: id为主键,name字段创建的有索引;
解答: A 语句的执行性能要高于B 语句。 因为A语句直接走聚集索引,直接返回数据。 而B语句需要先查询name字段的二级索引,然 后再查询聚集索引,也就是需要进行回表查询。
索引失效情况
(1)在索引列上进行运算操作,索引将失效。
-- 索引将失效 explain select * from tb_user where substring(phone,10,2) = '15';
(2)字符串类型字段使用时,不加引号,索引将失效。
如果字符串不加单引号,对于查询结果,没什么影响,但是数据库存在隐式类型转换(相当于还是发生了运算操作),索引将失效。
(3)模糊查询
如果仅仅是尾部模糊匹配,索引不会失效。如果是头部模糊匹配,索引失效。
-- 索引不失效 explain select * from tb_user where profession like '软件%'; -- 索引失效 explain select * from tb_user where profession like '%工程'; -- 索引失效 explain select * from tb_user where profession like '%工%';
(4)or 连接条件
用or分割开的条件, 如果or前的条件中的列有索引,而后面的列中没有索引,那么涉及的索引都不会被用到。
当or连接的条件的左右两侧字段都有索引时,索引才会生效。
(5)数据分布影响
如果MySQL评估使用索引比全表更慢,则不使用索引。
是因为MySQL在查询时,会评估使用索引的效率与走全表扫描的效率,如果走全表扫描更快,则放弃索引,走全表扫描。 因为索引是用来索引少量数据的,如果通过索引查询返回大批量的数据,则还不如走全表扫描来的快,此时索引就会失效。
(6)违反了最左前缀法则
如果索引了多列(联合索引),要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始, 并且不跳过索引中的列。如果跳跃某一列,索引将会部分失效(后面的字段索引失效)。
例如:在tb_user 表中,有一个联合索引,这个联合索引涉及到三个字段,顺序分别为:profession, age,status。 对于最左前缀法则指的是,查询时,最左边的列,也就是profession必须存在,否则索引全部失效。 而且中间不能跳过某一列,否则该列后面的字段索引将失效。
(7)使用范围查询(>,<),范围查询右侧的列索引失效
联合索引中,出现范围查询(>,<),范围查询右侧的列索引失效。
使用 >= 或 <= 进行范围查询 ,所有的字段都是走索引 的。
所以在业务允许的情况下,尽可能的使用类似于,而避免使用 > 或 < 。
SQL提示
SQL提示,是优化数据库的一个重要手段,简单来说,就是在SQL语句中加入一些人为的提示来达到优 化操作的目的。
1). use index : 建议MySQL使用哪一个索引完成此次查询(仅仅是建议,mysql内部还会再次进 行评估)。
explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';
2). ignore index : 忽略指定的索引。
explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';
3). force index : 强制使用索引。
explain select * from tb_user force index(idx_user_pro) where profession = '软件工程';
覆盖索引
在mysql中,使用索引时,尽量使用覆盖索引,减少使用 select *(除非是根据主键查询,主键索引也是覆盖索引)。
那么什么是覆盖索引呢?
覆盖索引是指查询使用了索引,并且需要返回的列,在该索引中已经全部能够找到 (不需要回表查询)
思考题: 一张表, 有四个字段(id, username, password, status), 由于数据量大, 需要对 以下SQL语句进行优化, 该如何进行才是最优方案:
select id,username,password from tb_user where username = 'itcast';
答案: 针对于 username, password建立联合索引, sql为: create index idx_user_name_pass on tb_user(username,password);
这样可以避免上述的SQL语句,在查询的过程中,出现回表查询
前缀索引
当字段类型为字符串(varchar,text,longtext等)时,有时候需要索引很长的字符串,这会让索引变得很大,查询时,浪费大量的磁盘IO, 影响查询效率。此时可以只将字符串的一部分前缀,建立索引,这样可以大大节约索引空间,从而提高索引效率。
语法:
create index idx_xxxx on table_name(column(n)) ;
示例: 为tb_user表的email字段,建立长度为5的前缀索引。
create index idx_email_5 on tb_user(email(5));
前缀长度
可以根据索引的选择性来决定,而选择性是指不重复的索引值(基数)和数据表的记录总数的比值, 索引选择性越高则查询效率越高, 唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的。
前缀索引的查询流程:
单列索引与联合索引
单列索引:即一个索引只包含单个列。
联合索引:即一个索引包含了多个列。
索引设计原则
1). 针对于数据量较大,且查询比较频繁的表建立索引。
2). 针对于常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引。
3). 尽量选择区分度高的列作为索引,尽量建立唯一索引,区分度越高,使用索引的效率越高。
4). 如果是字符串类型的字段,字段的长度较长,可以针对于字段的特点,建立前缀索引。
5). 尽量使用联合索引,减少单列索引,查询时,联合索引很多时候可以覆盖索引,节省存储空间, 避免回表,提高查询效率。
6). 要控制索引的数量,索引并不是多多益善,索引越多,维护索引结构的代价也就越大,会影响增 删改的效率。
7). 如果索引列不能存储NULL值,请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含 NULL值时,它可以更好地确定哪个索引最有效地用于查询。
满足业务需求的情况下,尽量降低主键的长度。
插入数据时,尽量选择顺序插入,选择使用AUTO_INCREMENT自增主键。
尽量不要使用UUID做主键或者是其他自然主键,如身份证号。 业务操作时,避免对主键的修改。
sql优化
插入数据
如果我们需要一次性往数据库表中插入多条记录,可以从以下三个方面进行优化。
1.批量插入数据
Insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
2.手动控制事务
start transaction; insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry'); insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry'); insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry'); commit;
3.主键顺序插入,性能要高于乱序插入(与页分裂有关)。
大批量插入数据
如果一次性需要插入大批量数据(比如: 几百万的记录),使用insert语句插入性能较低,此时可以使用MySQL数据库提供的load指令进行插入。操作如下:
-- 客户端连接服务端时,加上参数 -–local-infile mysql –-local-infile -u root -p -- 设置全局参数local_infile为1,开启从本地加载文件导入数据的开关 set global local_infile = 1; -- 执行load指令将准备好的数据,加载到表结构中 load data local infile '/root/load_user_100w_sort.sql' into table tb_user fields terminated by ',' lines terminated by '\n' ;
主键优化
在上一小节,我们提到,主键顺序插入的性能是要高于乱序插入的。
这一小节,就来介绍一下具体的原因,然后再分析一下主键又该如何设计。
1). 数据组织方式
在InnoDB存储引擎中,表数据都是根据主键顺序组织存放的,这种存储方式的表称为索引组织表 (index organized table IOT)。
行数据,都是存储在聚集索引的叶子节点上的,InnoDB的逻辑结构图:
在InnoDB引擎中,数据行是记录在逻辑结构 page 页中的,而每一个页的大小是固定的,默认16K。 那也就意味着, 一个页中所存储的行也是有限的,如果插入的数据行row在该页存储不小,将会存储 到下一个页中,页与页之间会通过指针连接。
页分裂
页可以为空,也可以填充一半,也可以填充100%。每个页包含了2~N行数据(如果一行数据过大,会行溢出),根据主键排列。
主键顺序插入效果
①. 从磁盘中申请页, 主键顺序插入
②. 第一个页没有满,继续往第一页插入
③. 当第一个也写满之后,再写入第二个页,页与页之间会通过指针连接
④. 当第二页写满了,再往第三页写入
主键乱序插入效果
①. 假如1#,2#页都已经写满了,存放了如图所示的数据
②. 此时再插入id为50的记录,我们来看看会发生什么现象
会再次开启一个页,写入新的页中吗?
不会。因为,索引结构的叶子节点是有顺序的。按照顺序,应该存储在47之后。
但是47所在的1#页,已经写满了,存储不了50对应的数据了。 那么此时会开辟一个新的页 3#。
但是并不会直接将50存入3#页,而是会将1#页后一半的数据,移动到3#页,然后在3#页,插入50。
移动数据,并插入id为50的数据之后,那么此时,这三个页之间的数据顺序是有问题的。 1#的下一个 页,应该是3#, 3#的下一个页是2#。 所以,此时,需要重新设置链表指针。
上述的这种现象,称之为 "页分裂",是比较耗费性能的操作。
页合并
目前表中已有数据的索引结构(叶子节点)如下
当我们对已有数据进行删除时,具体的效果如下:
当删除一行记录时,实际上记录并没有被物理删除,只是记录被标记(flaged)为删除并且它的空间变得允许被其他记录声明使用。
当我们继续删除2#的数据记录
当页中删除的记录达到 MERGE_THRESHOLD(默认为页的50%),InnoDB会开始寻找最靠近的页(前或后)看看是否可以将两个页合并以优化空间使用。
删除数据,并将页合并之后,再次插入新的数据21,则直接插入3#页
这个里面所发生的合并页的这个现象,就称之为 "页合并"。
order by优化
MySQL的排序,有两种方式:
Using filesort : 通过表的索引或全表扫描,读取满足条件的数据行,然后在排序缓冲区sort buffer中完成排序操作,所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
Using index : 通过有序索引顺序扫描直接返回有序数据,这种情况即为 using index,不需要额外排序,操作效率高。
对于以上的两种排序方式,Using index的性能高,而Using filesort的性能低,我们在优化排序操作时,尽量要优化为 Using index。
order by优化原则:
A. 根据排序字段建立合适的索引,多字段排序时,也遵循最左前缀法则。
B. 尽量使用覆盖索引。
C. 多字段排序, 一个升序一个降序,此时需要注意联合索引在创建时的规则(ASC/DESC)。
D. 如果不可避免的出现filesort,大数据量排序时,可以适当增大排序缓冲区大小 sort_buffer_size(默认256k)。
group by优化
在分组操作中,我们需要通过以下两点进行优化,以提升性能:
A. 在分组操作时,可以通过索引来提高效率。
B. 分组操作时,索引的使用也是满足最左前缀法则的
limit优化
在数据量比较大时,如果进行limit分页查询,在查询时,越往后,分页查询效率越低。
当在进行分页查询时,如果执行 limit 2000000,10 ,此时需要MySQL排序前2000010 记 录,仅仅返回 2000000 - 2000010 的记录,其他记录丢弃,查询排序的代价非常大。
优化思路: 一般分页查询时,通过创建覆盖索引 能够比较好地提高性能,可以通过覆盖索引加子查询形式进行优化。
explain select * from tb_sku t , (select id from tb_sku order by id limit 2000000,10) a where t.id = a.id;
count优化
如果数据量很大,在执行count操作时,是非常耗时的。
MyISAM 引擎把一个表的总行数存在了磁盘上,因此执行 count(*) 的时候会直接返回这个数,效率很高; 但是如果是带条件的count,MyISAM也慢。
InnoDB 引擎就麻烦了,它执行 count() 的时候,需要把数据一行一行地从引擎里面读出 来,然后累积计数。 如果说要大幅度提升InnoDB表的count效率,主要的优化思路:自己计数(可以借助于redis这样的数据库进行,但是如果是带条件的count又比较麻烦了)
count() 是一个聚合函数,对于返回的结果集,一行行地判断,如果 count 函数的参数不是 NULL,累计值就加 1,否则不加,最后返回累计值。 用法:count(*)、count(主键)、count(字段)、count(数字)
count用法 | 含义 |
---|---|
count(主 键) | InnoDB 引擎会遍历整张表,把每一行的 主键id 值都取出来,返回给服务层。 服务层拿到主键后,直接按行进行累加(主键不可能为null) |
count(字 段) | 没有not null 约束 : InnoDB 引擎会遍历整张表把每一行的字段值都取出来,返回给服务层,服务层判断是否为null,不为null,计数累加。 有not null 约束:InnoDB 引擎会遍历整张表把每一行的字段值都取出来,返回给服务层,直接按行进行累加。 |
count(数 字) | InnoDB 引擎遍历整张表,但不取值。服务层对于返回的每一行,放一个数字“1” 进去,直接按行进行累加。 |
count(*) | InnoDB引擎并不会把全部字段取出来,而是专门做了优化,不取值,服务层直接按行进行累加。 |
按照效率排序的话,count(字段) < count(主键 id) < count(1) ≈ count(),所以尽量使用 count(*)。
update优化
我们主要需要注意一下update语句执行时的注意事项。
update course set name = 'javaEE' where id = 1 ;
当我们在执行修改的SQL语句时,会锁定id为1这一行的数据,然后事务提交之后,行锁释放。
但是当我们在执行如下SQL时。
update course set name = 'SpringBoot' where name = 'PHP' ;
当我们开启多个事务,在执行上述的SQL时,我们发现行锁升级为了表锁。 导致该update语句的性能大大降低。
InnoDB的行锁是针对索引加的锁,不是针对记录加的锁 ,并且该索引不能失效,否则会从行锁升级为表锁 。
事务
事务是一组操作的集合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败。
事务的特性
• 原子性(Atomicity):事务是不可分割的最小操作单元,要么全部成功,要么全部失败。
• 一致性(Consistency):事务完成时,必须使所有的数据都保持一致状态。
• 隔离性(Isolation):数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行。
• 持久性(Durability):事务一旦提交或回滚,它对数据库中的数据的改变就是永久的。
原子性,一致性,持久性这三大特性由 redo log 和 undo log 日志来保证的。 隔离性 是由锁机制和MVCC保证的。
并发事务问题
问题 | 描述 |
---|---|
脏读 | 一个事务读到另一个事务还没提交的数据 |
不可重复读 | 一个事务先后读取同一条记录,但两次读取的数据不同 |
幻读 | 一个事务按照条件查询数据时,没有对应的数据行,但是再插入数据时,又发现这行数据已经存在 |
隔离级别
并发事务隔离级别:
隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
Read uncommitted(读取未提交) | √ | √ | √ |
Read committed (读取已提交) | × | √ | √ |
Repeatable Read(默认) (可重复读) | × | × | √ |
Serializable (串行化) | × | × | × |
-
√表示在当前隔离级别下该问题会出现
-
Serializable 性能最低;Read uncommitted 性能最高,数据安全性最差
查看事务隔离级别: SELECT @@TRANSACTION_ISOLATION;
设置事务隔离级别: SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE };
SESSION 是会话级别,表示只针对当前会话有效,GLOBAL 表示对所有会话有效
redo log
重做日志,记录的是事务提交时数据页的物理修改,是用来实现事务的持久性。 该日志文件由两部分组成:重做日志缓冲(redo log buffer)以及重做日志文件(redo log file),前者是在内存中,后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中,用于在刷新脏页到磁盘,发生错误时,进行数据恢复使用。
个人理解: 事物每次提交的时候都会将数据刷到redo log中而不是直接将buffer pool中的数据直接刷到磁盘中(ibd文件中),是因为redo log 是顺序写,性能处理的够快,直接刷到ibd中,是随机写,性能慢。所以脏页是在下一次读的时候,或者后台线程采用一定的机制进行刷盘到ibd中。
undo log
回滚日志,用于记录数据被修改前的信息,作用包含两个:提供回滚(实现事务的一致性和原子性)和MVCC(多版本并发控制)。 undo log和redo log记录物理日志不一样,它是逻辑日志。可以认为当delete一条记录,undo log中会记录一条对应的insert记录,反之亦然,当update一条记录时,它记录一条对应相反的update记录。当执行rollback时,就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。
Undo log销毁:undo log在事务执行时产生,事务提交时,并不会立即删除undo log,因为这些日志可能还用于MVCC。
Undo log存储:undo log采用段的方式进行管理和记录,存放在前面介绍的rollback segment回滚段中,内部包含1024个undo log segment。
锁
MySQL中的锁,按照锁的粒度分,分为以下三类:
全局锁:锁定数据库中的所有表。
表级锁:每次操作锁住整张表。
行级锁:每次操作锁住对应的行数据。
全局锁
全局锁就是对整个数据库实例加锁,加锁后整个实例就处于只读状态,后续的DML的写语句,DDL语句,已经更新操作的事务提交语句都将被阻塞。
其典型的使用场景是做全库的逻辑备份,对所有的表进行锁定,从而获取一致性视图,保证数据的完整 性。
对数据库进行进行逻辑备份之前,先对整个数据库加上全局锁,一旦加了全局锁之后,其他的DDL、 DML全部都处于阻塞状态,但是可以执行DQL语句,也就是处于只读状态,而数据备份就是查询操作。 那么数据在进行逻辑备份的过程中,数据库中的数据就是不会发生变化的,这样就保证了数据的一致性和完整性。
语法
1). 加全局锁
flush tables with read lock ;
2). 数据备份
mysqldump -uroot –proot itcast > itcast.sql
3).释放锁
unlock tables ;
数据库中加全局锁,是一个比较重的操作,存在以下问题:
如果在主库上备份,那么在备份期间都不能执行更新,业务基本上就得停摆。
如果在从库上备份,那么在备份期间从库不能执行主库同步过来的二进制日志(binlog),会导致主从延迟。
在InnoDB引擎中,我们可以在备份时加上参数 --single-transaction 参数来完成不加锁的一致 性数据备份。
mysqldump --single-transaction -uroot –p123456 itcast > itcast.sql
表级锁
表级锁,每次操作锁住整张表。锁定粒度大,发生锁冲突的概率最高,并发度最低。应用在MyISAM、 InnoDB、BDB等存储引擎中。
对于表级锁,主要分为以下三类:
-
表锁
-
元数据锁(meta data lock,MDL)
-
意向锁
表锁
对于表锁,分为两类:
-
表共享读锁(read lock)
-
表独占写锁(write lock)
语法:
-
加锁:lock tables 表名... read/write。
-
释放锁:unlock tables / 客户端断开连接 。
读锁
写锁
元数据锁
meta data lock , 元数据锁,简写MDL。
MDL加锁过程是系统自动控制,无需显式使用,在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性,在表上有活动事务的时候,不可以对元数据进行写入操作。为了避免DML与 DDL冲突,保证读写的正确性。
这里的元数据,大家可以简单理解为就是一张表的表结构。 也就是说,某一张表涉及到未提交的事务 时,是不能够修改这张表的表结构的。 在MySQL5.5中引入了MDL,当对一张表进行增删改查的时候,加MDL共享锁(SHARED_READ、SHARED_WRITE);当对表结构进行变更操作的时候,加MDL排它锁(EXCLUSIVE )。
意向锁
为了避免DML在执行时,加的行锁与表锁的冲突,在InnoDB中引入了意向锁,使得表锁不用检查每行数据是否加锁,使用意向锁来减少表锁的检查。
假如没有意向锁,客户端一对表加了行锁后,客户端二如何给表加表锁呢,来通过示意图简单分析一 下:
首先客户端一,开启一个事务,然后执行DML操作,在执行DML语句时,会对涉及到的行加行锁。
当客户端二,想对这张表加表锁时,会检查当前表是否有对应的行锁,如果没有,则添加表锁,此时就会从第一行数据,检查到最后一行数据,效率较低。
有了意向锁之后 :
客户端一,在执行DML操作时,会对涉及的行加行锁,同时也会对该表加上意向锁。
而其他客户端,在对这张表加表锁的时候,会根据该表上所加的意向锁来判定是否可以成功加表锁,而不用逐行判断行锁情况了。
意向锁分为:
意向共享锁(IS): 由语句select ... lock in share mode添加 。 与表锁共享锁 (read)兼容,与表锁排他锁(write)互斥。
意向排他锁(IX): 由insert、update、delete、select...for update添加 。与表锁共享锁(read)及排他锁(write)都互斥,意向锁之间不会互斥。
行级锁
行级锁,每次操作锁住对应的行数据。锁定粒度最小,发生锁冲突的概率最低,并发度最高。应用在 InnoDB存储引擎中。
InnoDB的数据是基于索引组织的,行锁是通过对索引上的索引项加锁来实现的,而不是对记录加的锁。对于行级锁,主要分为以下三类:
-
行锁(Record Lock):锁定单个行记录的锁,防止其他事务对此行进行update和delete。在 RC、RR隔离级别下都支持。
-
间隙锁(Gap Lock):锁定索引记录间隙(不含该记录),确保索引记录间隙不变,防止其他事务在这个间隙进行insert,产生幻读。在RR隔离级别下都支持。
-
临键锁(Next-Key Lock):行锁和间隙锁组合,同时锁住数据,并锁住数据前面的间隙Gap。 在RR隔离级别下支持。
行锁
InnoDB实现了以下两种类型的行锁:
-
共享锁(S):允许一个事务去读一行,阻止其他事务获得相同数据集的排它锁。
-
排他锁(X):允许获取排他锁的事务更新数据,阻止其他事务获得相同数据集的共享锁和排他锁。
两种行锁的兼容情况如下
常见的SQL语句,在执行时,所加的行锁如下:
SQL | 行锁类型 | 说明 |
---|---|---|
INSERT ... | 排他锁 | 自动加锁 |
UPDATE ... | 排他锁 | 自动加锁 |
DELETE ... | 排他锁 | 自动加锁 |
SELECT(正常) | 不加任何锁 | |
SELECT ... LOCK IN SHARE MODE | 共享锁 | 需要手动在SELECT之后加LOCK IN SHARE MODE |
SELECT ... FOR UPDATE | 排他锁 | 需要手动在SELECT之后加FOR UPDATE |
默认情况下,InnoDB在 REPEATABLE READ事务隔离级别运行,InnoDB使用 next-key 锁进行搜索和索引扫描,以防止幻读。
针对唯一索引进行检索时,对已存在的记录进行等值匹配时,将会自动优化为行锁。
InnoDB的行锁是针对于索引加的锁,不通过索引条件检索数据,那么InnoDB将对表中的所有记录加锁,此时就会升级为表锁。
间隙锁&临键锁
默认情况下,InnoDB在 REPEATABLE READ事务隔离级别运行,InnoDB使用 next-key 锁进行搜索和索引扫描,以防止幻读。
索引上的等值查询(唯一索引),给不存在的记录加锁时, 优化为间隙锁 。
索引上的等值查询(非唯一普通索引),向右遍历时最后一个值不满足查询需求时,next-key lock 退化为间隙锁。 索引上的范围查询(唯一索引)--会访问到不满足条件的第一个值为止。
注意:间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存,一个事务采用的间隙锁不会 阻止另一个事务在同一间隙上采用间隙锁。
MVCC
基本概念
当前读
读取的是记录的最新版本,读取时还要保证其他并发事务不能修改当前记录,会对读取的记录进行加锁。对于我们日常的操作,如:select ... lock in share mode(共享锁),select ... for update、update、insert、delete(排他锁)都是一种当前读。
快照读
简单的select(不加锁)就是快照读,快照读,读取的是记录数据的可见版本,有可能是历史数据, 不加锁,是非阻塞读。
• Read Committed:每次select,都生成一个快照读。
• Repeatable Read:开启事务后第一个select语句才是快照读的地方。
• Serializable:快照读会退化为当前读。
MVCC
全称 Multi-Version Concurrency Control,多版本并发控制。指维护一个数据的多个版本, 使得读写操作没有冲突,快照读为MySQL实现MVCC提供了一个非阻塞读功能。
MVCC的具体实现,还需要依赖于数据库记录中的三个隐式字段、undo log日志、readView。
隐藏字段
有如下一张数据库表
当我们创建了上面的这张表,我们在查看表结构的时候,就可以显式的看到这三个字段。 实际上除了这三个字段以外,InnoDB还会自动的给我们添加三个隐藏字段及,其含义分别是:
隐藏字段 | 含义 |
---|---|
DB_TRX_ID | 最近修改事务ID,记录插入这条记录或最后一次修改该记录的事务ID。 |
DB_ROLL_PTR | 回滚指针,指向这条记录的上一个版本,用于配合undo log,指向上一个版本。 |
DB_ROW_ID | 隐藏主键,如果表结构没有指定主键,将会生成该隐藏字段。 |
undo log
介绍
回滚日志,在insert、update、delete的时候产生的便于数据回滚的日志。 当insert的时候,产生的undo log日志只在回滚时需要,在事务提交后,可被立即删除。 而update、delete的时候,产生的undo log日志不仅在回滚时需要,在快照读时也需要,不会立即被删除。
版本链
有一张表原始数据为:
DB_TRX_ID : 代表最近修改事务ID,记录插入这条记录或最后一次修改该记录的事务ID,是自增的。
DB_ROLL_PTR : 由于这条数据是才插入的,没有被更新过,所以该字段值为null。
然后,有四个并发事务同时在访问这张表。
A.第一步
当事务2执行第一条修改语句时,会记录undo log日志,记录数据变更之前的样子; 然后更新记录, 并且记录本次操作的事务ID,回滚指针,回滚指针用来指定如果发生回滚,回滚到哪一个版本。
B.第二步
当事务3执行第一条修改语句时,也会记录undo log日志,记录数据变更之前的样子; 然后更新记录,并且记录本次操作的事务ID,回滚指针,回滚指针用来指定如果发生回滚,回滚到哪一个版本
C.第三步
当事务4执行第一条修改语句时,也会记录undo log日志,记录数据变更之前的样子; 然后更新记录,并且记录本次操作的事务ID,回滚指针,回滚指针用来指定如果发生回滚,回滚到哪一个版本。
最终我们发现,不同事务或相同事务对同一条记录进行修改,会导致该记录的undolog生成一条记录版本链表,链表的头部是最新的旧记录,链表尾部是最早的旧记录。
readview
ReadView(读视图)是 快照读 SQL执行时MVCC提取数据的依据,记录并维护系统当前活跃的事务 (未提交的)id。
ReadView中包含了四个核心字段:
字段 | 含义 |
---|---|
m_ids | 当前活跃的事务ID集合 |
min_trx_id | 最小活跃事务ID |
max_trx_id | 预分配事务ID,当前最大事务ID+1(因为事务ID是自增的) |
creator_trx_id | ReadView创建者的事务ID |
而在readview中就规定了版本链数据的访问规则:
trx_id 代表当前undolog版本链对应事务ID。
条件 | 是否可以访问 | 说明 |
---|---|---|
trx_id == creator_trx_id | 可以访问该版本 | 成立,说明数据是当前这事务更改的。 |
trx_id < min_trx_id | 可以访问该版本 | 成立,说明数据已经提交了。 |
trx_id > max_trx_id | 不可以访问该版本 | 成立,说明该事务是在 ReadView生成后才开启。 |
min_trx_id <= trx_id <= max_trx_id | 如果trx_id不在m_ids中, 是可以访问该版本的 | 成立,说明数据已经提交。 |
不同的隔离级别,生成ReadView的时机不同:
READ COMMITTED :在事务中每一次执行快照读时生成ReadView。
REPEATABLE READ:仅在事务中第一次执行快照读时生成ReadView,后续复用该ReadView。
原理分析
RC隔离级别下
RC隔离级别下,在事务中每一次执行快照读时生成ReadView。
我们就来分析事务5中,两次快照读读取数据,是如何获取数据的?
在事务5中,查询了两次id为30的记录,由于隔离级别为Read Committed,所以每一次进行快照读 都会生成一个ReadView,那么两次生成的ReadView如下。
那么这两次快照读在获取数据时,就需要根据所生成的ReadView以及ReadView的版本链访问规则, 到undolog版本链中匹配数据,最终决定此次快照读返回的数据。
A.先来看第一次快照读具体的读取过程:
在进行匹配时,会从undo log的版本链,从上到下进行挨个匹配:
(1)先匹配
这条记录,这条记录对应的 trx_id为4,也就是将4带入右侧的匹配规则中。 ①不满足 ②不满足 ③不满足 ④也不满足 , 都不满足,则继续匹配undo log版本链的下一条。
(2)再匹配第二条
,这条记录对应的trx_id为3,也就是将3带入右侧的匹配规则中。①不满足 ②不满足 ③不满足 ④也 不满足 ,都不满足,则继续匹配undo log版本链的下一条。
(3)再匹配第三条
,这条记录对应的trx_id为2,也就是将2带入右侧的匹配规则中。①不满足 ②满足 终止匹配,此次快照读返回的数据就是版本链中记录的这条数据。
B. 再来看第二次快照读具体的读取过程:
在进行匹配时,会从undo log的版本链,从上到下进行挨个匹配:
先匹配
这条记录,这条记录对应的 trx_id为4,也就是将4带入右侧的匹配规则中。 ①不满足 ②不满足 ③不满足 ④也不满足 , 都不满足,则继续匹配undo log版本链的下一条。
再匹配第二条
,这条记录对应的trx_id为3,也就是将3带入右侧的匹配规则中。①不满足 ②满足 。终止匹配,此次 快照读,返回的数据就是版本链中记录的这条数据。
RR隔离级别下
RR隔离级别下,仅在事务中第一次执行快照读时生成ReadView,后续复用该ReadView。 而RR 是可 重复读,在一个事务中,执行两次相同的select语句,查询到的结果是一样的。
那MySQL是如何做到可重复读的呢? 我们简单分析一下就知道了
我们看到,在RR隔离级别下,只是在事务中第一次快照读时生成ReadView,后续都是复用该 ReadView,那么既然ReadView都一样, ReadView的版本链匹配规则也一样, 那么最终快照读返回的结果也是一样的。
主从复制原理
Mysql主从复制的核心是二进制日志(BINLOG)
二进制日志记录了所有的DDL(数据定义语言)语句和DML(数据操作语言)语句,但不包括数据查询(SELECT、SHOW)语句。
主从复制分为3步:
1.master主库在事务提交时,会把数据变更记录在二进制日志文件Binlog中。
2.从库读取主库的二进制日志文件Binlog,写入从库的中继日志 Relay Log中。
3.从库重做中继日志中的事件,将主库的改变同步到从库。