MySql 5.7 总结

felix_1104
249 阅读12分钟

事务

image.png

查看事务隔离级别： select @@tx_isolation

修改会话级别的事务隔离级别：set tx_isolation='READ-UNCOMMITTED'

脏读：一个事务读取到另一个事务未提交的数据

不可重复读：同一个事物中两次读取得到不同的结果

幻读：某一次的 select 操作得到的结果所表征的数据状态无法支撑后续的业务操作。更为具体一些：select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，此时就发生了幻读。

读未提交：这种隔离级别存在脏读的可能性。

修改事务隔离级别：set tx_isolation='READ-UNCOMMITTED'

脏读现象：两个会话同时开启事务，事务1查询，事务2修改，此时事务1和事务2都未提交事务，事务1再次查询能够查询到事务2修改后的数据。

读已提交：解决了脏读，但存在不可重复读

修改事务隔离级别：set tx_isolation='READ-COMMITTED'

不可重复读现象：两个会话同时开启事务，事务1查询，事务2修改，此时事务2提交事务，事务1再次查询能够查询到事务2修改后的数据

可重复读：解决了脏读和不可重复读，但存在幻读

幻读现象：两个会话同时开启事务，事务1查询，事务2插入id为3的记录并提交（其实相当于此操作没有加事务），事务1再次查询和之前一样，没有id为3的记录，但此时插入事务为3的记录却报错

串行化： 解决幻读，两个会话中，会话1开启了事务，此时会话2可以查询，但是插入修改必须等待会话1中的事务提交后才能执行

存储引擎

INNODB 逻辑存储结构

表空间-》段-》区-》页-》行

默认一页大小是16KB

默认一区大小是1M

一区有64页

INNODB和MYISAM 区别

INNODB支持事务，行锁 外键

索引

5阶的B树

image.png 5阶的B+树

image.png

image.png

为什么InnoDB采用B+树作为数据的索引结构？

  1. 相比较二叉树，层级更少，搜索效率更高

  2. 相比较B树，一页的大小是固定的，一页就是一个节点，B树的每个结点都存储了key和data，B+树的data存储在叶子节点上。节点不存储data，这样一个节点就可以存储更多的key。可以使得树更矮，所以IO操作次数更少。树的所有叶结点构成一个有序链表，可以按照关键码排序的次序遍历全部记录 由于数据顺序排列并且相连，所以便于区间查找和搜索。而B树则需要进行每一层的递归遍历。相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好。

  3. 相比较哈希索引 B+树支持范围查询和排序

实际能够创建的索引分类

  1. 主键索引 建立主键，会自动创建该索引，只能有一个

  2. 唯一索引 避免表中的列重复和非空所创建的索引，可以有多个，创建唯一约束时会自动创建该索引

  3. 常规索引 快速定位数据，可以有多个

在InnoDB中根据索引的存储形式分类

  1. 聚簇索引 索引结构的叶子节点存放行数据 必须有，而且只有一个

如果有主键，主键索引即是聚簇索引

如果没有主键，第一个唯一索引即是聚簇索引

如果没有唯一索引，会自动生成rowID列作为聚簇索引

  1. 非聚簇索引 索引的叶子节点存放的是对应的主键 可以有多个

一张表中的索引图如下：

image.png

InnoDB中树的高度怎么计算

image.png

联合索引

expain Mysql中explain各字段的解释_mameng1988的博客-CSDN博客

举例：创建一个（a,b）的联合索引，那么它的索引树就是下图的样子。

\

\

可以看到a的值是有顺序的，1，1，2，2，3，3，而b的值是没有顺序的1，2，1，4，1，2。但是我们又可发现a在等值的情况下，b值又是按顺序排列的，但是这种顺序是相对的。

索引失效的场景：

  1. 索引列上加函数操作 需要注意的是，优化器并不是要放弃使用这个索引。 有可能只是降低type级别

  2. 字符串不加单引号，相当于做了函数运算

  3. 联合索引 最左匹配原则，查询从索引最左侧开始，并且跳过中间列，后面的索引将失效，范围查询> < 后面的列失效

  4. 模糊查询，前半部分中精确的话不会失效，后半部分精确的话会失效

  5. 用or链接的子句 必选都是索引列 索引才会生效

  6. 数据分布情况 如果优化器认为走索引还不如全表扫描来的快，将不走索引，因为走索引会有回表操作 覆盖索引

select 所需要查询的列尽量在一个索引树上，这样能够避免回表查询

select name,password from user where name='a';

这条sql 最优应该建立 name,password联合索引，比建立name单列索引要高效。

where order by group by 后的条件建立索引

批量插入sql

insert into user values(1,'mxx'),(2,'wqq'),(3,'ass');

建立索引时候可以指定索引排序是升序还是降序

create idx_name_age_ad on user (age asc ,name desc);

order by 条件使用索引生效的前提是使用了覆盖索引，如果select * 将不会走索引

limit 分页查询优化

explain select * from user_test ut limit 100,100 全表扫描

explain select s.* from user_test s right join (select id from user_test ut limit 100,100) a on s.id=a.id 利用覆盖索引优化查询

select count(name) from user 只会累加name 不会null的记录

image.png

innodb 行锁是针对索引加的锁，并不是针对记录加的锁，所以update 尽量where条件加上索引列，否则会升级到表锁

锁

乐观锁和悲观锁是两种思想，根据两种思想有不同的实现，两种锁都能达到锁的效果，但是不同场景下带来的效率不一样。乐观锁不会每次都会上锁，只有当更新操作时候判断当前数据是否有其他线程占用，适用于读多写少场景，带来更高的吞吐量，如果读少写多，会不停的重试，悲观锁是每次操作数据都会上锁，适用于读少写多，如果读多写少的话会浪费资源。

Mysql乐观锁通常需要程序实现，增加版本号字段，当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一。当我们提交更新的时候，判断数据库表对应记录 的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数 据。实现参考：mysql乐观锁是什么？-mysql教程-PHP中文网

表级锁

表读锁，表写锁

加了读锁 所有的客户端对表只能读，当前客户端写入报错，其他客户端写入阻塞

加了写锁 当前客户端能读能写，其他客户端读阻塞 写阻塞

什么时候使用表锁

    对于InnoDB表，在绝大部分情况下都应该使用行级锁，因为事务和行锁往往是我们之所以选择InnoDB表的理由。但在个别特殊事务中，也可以考虑使用表级锁。

  • 第一种情况是：事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。
  • 第二种情况是：事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销。

    当然，应用中这两种事务不能太多，否则，就应该考虑使用ＭyISAＭ表。

    在InnoDB下 ，使用表锁要注意以下两点。\

    （１）使用LOCK TALBES虽然可以给InnoDB加表级锁，但必须说明的是，表锁不是由InnoDB存储引擎层管理的，而是由其上一层ＭySQL Server负责的，仅当autocommit=0、innodb_table_lock=1（默认设置）时，InnoDB层才能知道MySQL加的表锁，ＭySQL Server才能感知InnoDB加的行锁，这种情况下，InnoDB才能自动识别涉及表级锁的死锁；否则，InnoDB将无法自动检测并处理这种死锁。\

    （２）在用LOCAK TABLES对InnoDB锁时要注意，要将AUTOCOMMIT设为0，否则ＭySQL不会给表加锁；事务结束前，不要用UNLOCAK TABLES释放表锁，因为UNLOCK TABLES会隐含地提交事务；COMMIT或ROLLBACK不能释放用LOCAK TABLES加的表级锁，必须用UNLOCK TABLES释放表锁，正确的方式见如下语句。

    例如，如果需要写表t1并从表t读，可以按如下做：

SET AUTOCOMMIT=0;
LOCAK TABLES t1 WRITE, t2 READ, ...;
[do something ``with` `tables t1 ``and` `here];
COMMIT;
UNLOCK TABLES;

元数据锁

mysql 当执行增删改查语句时候会自动增加元数据读锁，alter table会加元数据写锁

意向锁

执行增删改，select * for update 会加上表的意向写锁，执行select * lock in share model 会加意向读锁，这样当这时加表锁的时候不用每行数据去遍历查询是否兼容，直接判断加表锁的类型是否和意向锁兼容，从而判断是否能加表读锁或者表写锁

行级锁

行级锁的单位是next-key lock(临键锁),在特定条件下（等值查询）会优化成行锁或者间隙锁

存在以下表，当然一下语句设置了不自动提交或在事务中执行 image.png select * from user_test 不加锁

select * from user_test lock in share mode 不加where条件 升级到表锁

select * from where id=1 user_test lock in share mode 条件是唯一索引且条件值存在 加的是行读锁

假设每行数据有两个属性，共享锁和排他锁，如果两个线程进来，共享锁和共享锁能够兼容，共享锁和排他锁不能兼容，排他锁和排他锁不能兼容，insert update delete 加的都是排他锁，select不加锁，select .. lock in share mode 加的是共享锁，select 。。for update 加的是排他锁,注意，所有的加锁都是针对索引加的锁，要增删改查操作是否用到了索引，没用到会升级到表锁，insert语句是insert完后对索引加锁（应该是），不会升级到表锁

select * from where id=4 user_test lock in share mode 条件是唯一索引且条件值不存在 加的是间隙锁，将（5，6）间隙锁住

间隙锁（Gap Lock）是Innodb在可重复读提交下为了解决幻读问题时引入的锁机制（上了间隙锁，只有再插入的时候会等待），锁的是叶子节点之间的指针，间隙锁不分什么共享锁与排它锁，

select * from user_test where id=4 for update 条件是唯一索引且条件值不存在 加的是间隙锁，将（5，6）间隙锁住

select * from user_test where name ='d' for UPDATE 条件是非唯一索引且条件存在，加的是临键锁，锁的是（c,d）的间隙，d的行锁，（d,e）的间隙,此时另一会话插入insert into user_test values (8,"cc","aaaa")是插不进去的，会等待事务释放

select * from user_test where name ='cc' for UPDATE 锁的是(c,d)的间隙

注意一点 间隙锁是可以兼容的，但是会出现死锁 实践此点执行以下几步：

  1. A客户端开始事务 B客户端开始事务

  2. A客户端select * from user_test where id=4 for update

  3. B客户端select * from user_test where id=4 for update 执行成功可以兼容

  4. B insert into user_test values (4,"ccaccc","aaaa") 等待

  5. A insert into user_test values (4,"ccaccc","aaaa") 等待

范围查询比较复杂 参考Next-key Lock的规则 - 知乎 (zhihu.com)

避免死锁

设置事务等待锁的超时时间。当一个事务的等待时间超过该值后，就对这个事务进行回滚，于是锁就释放了，另一个事务就可以继续执行了。在 InnoDB 中，参数 innodb_lock_wait_timeout 是用来设置超时时间的，默认值时 50 秒。

当发生超时后，就出现下面这个提示：

image.png

可以设置重试

更好的是从业务的角度来预防死锁

mvcc 看一遍就理解：MVCC原理详解 - 掘金 (juejin.cn)

binlog Binlog是记录所有数据库表结构变更以及表数据修改的二进制日志，不会记录SELECT和SHOW这类操作。逻辑日志 作用是主从复制 数据恢复 记录默认为ROW级别

undolog 逻辑日志，执行一条delete,记录一条insert 在事务开始时产生，事务提交时不会立即删除，还可能用于mvcc，作用是事务的原子性，mvcc

avatar
文章
阅读
粉丝