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

本笔记通过看 B站IT老哥获得

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%”