本笔记通过看 B站IT老哥获得 @[toc]

MVCC

多版本并发控制multi-version concurrency control 主要是为了提高数据库的并发性能提高读写性能

什么是innodb的当前读和快照读？

当前读：

读取数据库记录都是当前最新的版本会对当前读取的数据进行加锁防止其他事物修改数据是一种悲观锁操作 select lock in share mode（共享锁） select for update update（拍他锁） insert（排他锁） delete（排他锁）串行事务隔离级别

快照读

快照读的实现是基于多版本并发控制的 mvcc 既然是多版本那么快照读读到的数据不一定是当前最新的数据有可能是之前历史版本的数据其中读已提交和可重复读是mvcc实现的不加锁的select 操作事务级别不是串行化

补充：原子性是通过：undo log实现持久性是通过 redo log实现隔离性就是通过加锁来实现 mvcc去实现

四种隔离级别第一种读未提交第二种读已提交第三种可重复读第四种串型化

mvcc概念第一个是undo log

版本链是undo log 和回滚指针

mysql百万级别数据优化

平均查询 4.3ms 不加索引无缓存 select SQL_NO_CHCHE *from test_user where phone='1342414134' and lan_id = 121 and region_id = 53;

优化开始

1、加索引 0.009ms

ALTER TABLE test_user ADD INDEX idx_phone_lan_region(phone,lan_id,region_id);

2、最左前缀法则

如果建立的事复合索引索引的顺序要按照建立时的顺序，即从左到右 a->b->c (和B+树的结构相关) 第一个索引是 phone 第二个索引是 lan_id 第三个索引是 region_id 用explain查看索引的使用情况 EXPLAIN SELECT * from test_user where phone='1412141134' and lan_id=121 and regin_id=53; 级别是 ref 级别去掉中间的索引 lan_id 索引字段变小查询行数变大（一座桥去掉桥中间只能走到桥头） EXPLAIN SELECT * from test_user where phone='1412141134' and regin_id=53;

去掉头部索引（一座桥去掉桥头到不了桥中间和桥尾） EXPLAIN SELECT * from test_user where phone='1412141134 and regin_id=53; type 是全部级别不好 ref 是null 行数是差不多百万级别的行数

3、不要对索引做以下处理

计算 +、 -、 *、 /、 !=、<>、is null 、is not null、or 函数 sum（）、rand（）手动/自动类型转换 id =”1“；本就是数字给转换成字符串

4、索引字段不要放在范围查找的右边

索引字段如果放在范围查找的右边将使用不到索引会失效 EXPLAIN SELECT * from test_user where phone='1412141134' and lan_id>121 and regin_id=53; 像本条语句 regin_id使用不到索引字段长度依旧是原来的数字

5、减少select * 的使用，使用覆盖索引

select 查询语句和where 中使用的索引字段一致就是我查询 a、b两个字段索引字段正好也是 a、b 如果不采用这个会浪费好多性能还有带宽

6、like模糊搜索

失效情况 like “%张三%” “%张三”

解决方案使用复合索引使用复合索引即like 字段是 select的查询字段如 select name from table where name like “%张三%” 使用like “张三%”

eg：EXPLAIN SELECT * from test_user where phone='%1412141134%“; 级别是all 级别用不到索引所以索引失效 sql 性能弱像这个去掉% 就ok了 EXPLAIN SELECT * from test_user where phone='1412141134%“;

建议使用最右边的百分号使用一下覆盖索引试用一下
EXPLAIN SELECT phone from test_user where phone='1412141134“;

这条语句特别影响性能
EXPLAIN SELECT * from test_user order by create_time asc

mysql锁

行锁

首先mysql 使用的是自动提交事物所以验证锁的时候要先把这个事务提交去掉变成手动提交事物 set autocommit = 0;

当我在一个窗口里修改我的一条语句锁的时候手动提交开启我们行锁是对内容进行操作 update 更新语句内容在总数据库不会发生变化，内容但是在本窗口查询已经改变避免了脏读
只有手动提交事务之后才会全局会更改其他数据是无法看到我的操作的因为数据进行了保护所以不会发生幻读的发生如果想全部更新就使用 >commit 语句

如果两个回话同时对一行数据进行更改第一个回话没有修改完数据提交完其他数据是无法进行更改的只能发生阻塞操作

表锁

产生原因索引失效主要用了or update test_innodb_lock set b='a4' where a=1 or a=2; commit 主要是行级锁索引失效就升级成为表级锁只有当表级锁使用完对其他表进行更改的语句才会生效否则进行阻塞

间隙锁

间隙锁一看就是在范围查询里面 eg：比如查询 1-9的数据就会对1-9 进行加锁如果要插入一个回话：update test_innodb_lock set b= '4' where a>1 and a<9; 第二个回话：insert into test_innodb_lock values (4,"b6"); 其中第一个进行更改的时候第二个插入不进去

show status like "innodb_row_lock%"

mysql百万数量级优化及MVCC硬核知识