【MySQL】(事务隔离,索引,锁)一览

---

索引相关问题

为什么要使用索引
什么样的信息能成为索引
索引的数据结构
密集索引和稀疏索引的区别
联合索引的最左匹配原则的成因
索引是建立的越多越好吗

为什么要使用索引?

# 答:
我们进行最基本的查询方式是全表扫描,
也就是说把整张表或者分批次放到内存当中,适用于数据量少的情况;
当数据量大的情况下并不适用,所以要引入一个更高效的方式

什么样的信息能成为索引?

# 答:
主键,唯一键,普通键等,只要具备区分性的字段

索引的数据结构?

# 答:
生成索引:建立二叉查找树进行二分查找
生成索引:建立B-Tree结构进行查找
生成索引:建立B+-Tree结构进行查找[MySQL]
生成索引,建立Hash结构进行查找

密集索引和稀疏索引的区别?

# 答:
密集索引,文件中的每个搜索码值都对应一个索引值
稀疏索引,文件只为索引码的某些值建立索引项

索引是建立的越多越好吗?

# 答:
否,数据量小的表不需要建立索引,建立会增加额外的索引开销
数据变更需要维护索引,因此更多的索引意味着更多的维护成本
更多的索引意味着也需要更多的空间

联合索引的最左匹配原则的成因?

# 复杂联合索引:
create unique index 索引名称 on 表名(列名1,列名2)
select * from 表名 where 列名1 =值 and 列名2=值;
要和建立索引时候的顺序对应

只有列名1出现的时候最左边的,才会走联合索引,只出现列名2的时候不出现

# 答:
最左前缀匹配原则,mysql会一直向右匹配直到遇到范围查询(>,<,between,like)就停止匹配,
比如a=3 and b=4 c>5 and d=6 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,
如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意跳转; = 和in可以乱序,
比如a=1 and b =2 and c=3 建立 (a,b,c) 索引可以任意顺序,
mysql的查询优化器会帮你优化成索引可以识别的形式

---

锁相关问题

MyISAM于InnoDB关于锁方面的区别是什么
数据库事务的四大特性
事务隔离级别以及各级别下的并发访问问题
InnoDB可重复读隔离级别下如何避免幻读
RC,RR级别下InnoDB的非阻塞读如何实现

---

MyISAM于InnoDB关于锁方面的区别是什么

数据库锁的分类有哪些?

# 按锁的颗粒度划分:
表级锁, 行级锁, 页级锁

# 按锁级别划分
共享锁 , 排它锁

# 按加锁方式划分
自动锁, 显式锁

# 按操作划分
DML锁, DDL锁

# 按使用方式划分
乐观锁, 悲观锁

MyISAM于InnoDB关于锁方面的区别是什么?

# 答:
① MyISAM默认用的是表级锁,不支持行级锁
InnoDB默认用的是行级锁,也支持表级锁
② MyISAM不支持事务; InnoDB支持事务

MyISAM是如何进行表锁处理的?

# 答:
① MyISAM 在执行查询的时候会对整张表自动进行加表级的读锁(共享)处理; 
② 对数据进行增删改或者查询for update的时候会对表自动进行加表级的写(排他)锁处理; 
③当读(共享)锁未被释放.
想要加上写锁就会被阻碍,只有释放了读锁才可以进行
④ 也就是说上了共享锁可以再加共享锁,但是不能上排他锁
⑤上了排他锁,不支持上排他锁和共享锁

场景如下1:
select * from person_info_myisam where id between 1 and 200000 
查询myisam引擎的表对1~2000000的数据进行查询
update person_info_myisam set account = account where id = 2000001;
在查询的同时执行修改,模拟并发情况
结果发现: 在执行查询的时候会对表进行 加表锁处理,要等待查询结束才可以修改数据

场景如下2:
lock table person_info_myisam read; 锁表读锁
update person_info_myisam set account = account where id = 2000001; 发现进行了读锁时无法执行
unlock tables; 释放读锁,才可以执行

为什么读锁也叫共享锁?

# 答:
在加了读锁的时候,进行范围查询的同时,
也可以执行其他的读操作,所以叫共享锁

为什么写锁也叫排他锁?

# 答:
在加了写锁的情况,既不支持上共享锁也不支持加写锁

---

为什么InnoDB是使用二段锁的?

# 答:
也就是加锁和解锁是分成两个步骤进行的,
在执行的时候加锁,在commit的时候统一解锁,
MySQL的事务是自动提交的

show VARIABLES LIKE 'autocommit'; --查看是否事务自动提交
set autocommit = 0 ;--关闭自动提交

InnoDB对Select做了改进(非阻塞Select)

# 答:
InnoDB 的 Select语句不会对表进行上锁处理
也就是非阻塞Select

select * from person where id =3 lock in share mode; 
--显示加上InnoDB 的 Select语句的读锁
commit; --提交解锁

行级锁是否和索引有关

# 答:
① InnoDB 当不走索引的时候,整张表就会被锁住,用的是表级锁;
② InnoDB 当走索引的时候,用的是行级锁和gap锁(走普通非唯一索引);


select * from person where id =3 lock in share mode; 
--显示加上InnoDB 的 Select语句的读锁
commit; --提交解锁

---

行级锁一定比表级锁好吗?

# 答:
未必,锁的粒度越小,开销越大,
相比表级锁在表的头部直接加锁,行级锁还要进行扫描到某行对其上锁,
这样代价是比较大的,InnoDB支持事务的同时带来了更大的开销

MyISAM适合的场景有哪些?

# 答:
频繁执行全表的count语句
对数据进行增删改的频率不高,查询非常频繁
没有事务

InnoDB适合的场景有哪些?

# 答:
频繁执行增删改的数据
可靠性要求比较高,要求支持事务

---

事务隔离相关问题

数据库事务的四大特性

数据库事务的四大特性

# 答:
ACID
原子性 Atomic
要么全部执行要么全部失败回滚

一致性 Consistency
保证数据库从一个一致的状态到另外一个一致的状态
保证完整性约束,用户转账之间的总金额不变,但是用户间的金额可以变化

隔离性 Isolation
多个事务执行时,不应该影响其他事务

持久性 Durability
一个事务提交应当永久保存在数据库中,已提交事务在宕机时不能丢失,能恢复,指的是BMS的恢复性能,

---

事务隔离级别以及各级别下的并发访问问题

事务并发访问引起的问题有哪些?

# 更新丢失-lostupdate:
场景:一个事务的更新覆盖了另外一个事务的更新
mysql所有事务隔离级别在数据库层面上均可避免
目前现在所有数据库软件基本都避免了这个情况

# 脏读:
场景:一个事务读到一个未提交事务的更新数据
READ-COMMITTED(已提交读)事务隔离级别以上可避免

# 不可重复读:
场景:A多次读取同一数据,别的事务B对其更新并提交,A发现结果不一致
REPETABLE-READ事务隔离级别以上可避免

# 幻读:
场景:事务A执行当前读操作,事务B在事务A的影响区间内插入/删除了数据,事务A执行当前读操作出现了幻行(多了或少了行)
SERIALIZABLE事务隔离级别可避免

---

事务隔离级别有哪些?

# 答:
未提交读 READ-UNCOMMITTED
已提交读(RC) READ-COMMITTED
可重读读(RR) REPETABLE-READ
串行化 SERIALIZABLE

# 查看当前事务隔离级别
select @@tx_isolation;

# 设置隔离级别
set session transaction isolation level read uncommitted;

# 开启事务
start transaction

事务隔离是越高越好吗?

# 答:
事务隔离级别越高,并发能力越差
要根据业务情况设置隔离级别
Orcale默认为已提交读 READ-COMMITTED;
MySQL为可重复读REPETABLE-READ

---

InnoDB可重复读隔离级别下如何避免幻读

什么是当前读和快照读(InnoDB下)?

# 答:
# 当前读(读取记录的最新版本)
select ……lock in share mode;select for update;
update ; delete ;insert;

# 快照读(不加锁的非阻塞读,不是最新版本,历史版本)
select

---

RC,RR级别下InnoDB的非阻塞读如何实现

# 答:
RR级别下使用next-key锁,RC级别下根据情况设立快照读位置

什么是next-key锁?

# 答:
行锁+Gap锁;RR级别以上才会出现Gap锁

对主键索引或者唯一索引会用Gap锁码?

# 答:
如果where条件全部命中,则不会用Gap锁,只会加记录锁
如果where条件部分命中或者全不命中,则会加Gap锁
结论:Gap锁会用在非唯一索引或者不走索引的当前读中

— end