数据库
2021-07-22 19:37:39 36 举报AI智能生成
数据库基本语法，索引以及锁的汇总
作者其他创作
大纲/内容
分库分表
垂直拆分
水平拆分
数据库相关的锁
锁粒度
Innodb的行锁
共享锁
lock in share mode
如果没有索引会锁表
排他锁
for update
delete update都会自动上排他锁
行锁可以避免锁冲突，但是会出现死锁
Myism的表锁
Innodb也支持页锁
锁思想<br>
乐观锁
利用版本号来控制，写之前读取版本号，写之后读取版本号并进行判断<br>
悲观锁
事务A对数据进行写操作时，会加上排他锁，如果数据正在被其他事务update，那么事务A等待或者抛出异常，这个根据业务情景而定
Innodb行锁的类型
Recod Lock
Gap Lock
间隙锁是在索引两端的间隙加锁，比如update num = 3的索引记录，num = 3的记录前后分别是num = 1, num = 4, 那么间隙锁是在（1， 3], 和 [3, 4)之间加锁， 所以加入要insert一个num = 2的数据就不行，如果要inset num = 5的数据就可以
由于主键(聚集)索引或者唯一索引都有唯一性，所以对当前查询语句的查询条件做等值判断，在加排他锁的时候，加的不是间隙锁
如果普通索引，或者主键/唯一索引做范围查询，加的排他锁是间隙锁
主键/唯一索引在做等值查询或者范围查询，查不到内容的时候，会加上间隙锁
Next-key lock
锁住记录本身，还锁住索引之间的间隙
会什么会发生死锁
事务A获得主键，等待其他索引
事务B获得其他索引，等待主键
如何避免死锁
顺序访问表
加大表的锁粒度
怎么优化sql
慢查询
确保数据库慢查询状态要开启，设定慢查询的时间，以及慢查询日志地址<br>
查询日志，定位sql
使用explain分析优化
优化手段
索引覆盖
最左前缀法则
主键索引查询
索引下推<br>
select * from tablename where name like xx% and size = 22 and age = 30
假设联合索引是(name, size)，mysql5以前是会拿到主键Id进行回表， 现在是会在联合索引表中，就进行判断，对size进行判断
唯一索引和普通索引的区别
change buffer
当通过唯一索引对数据页进行更新时，如果数据页在内存中，就直接更新<br>
如果数据页不在内存中，在不影响数据一致性的前提下，会将更新操作放到change buffer缓冲区中<br>
在下一次遇到读数据页，需要将数据页加载到内存上时，会先执行缓冲区里的更新操作，在进行读，这个步骤叫做merge
change buffer是可持久化的
唯一索引要确保更新的数据的唯一性，所以要先查索引值是否存在，这是读操作，然后再进行更新，这就会将数据页的数据加载到内存中，加载到内存中后，可以直接进行更新操作，所以不需要缓冲区
而普通索引，不需要读数据页，可以直接更新，所以等待数据页进入内存后再更新缓冲区的操作就可<br>
sql
操作数据库的create， drop， alter， use， show create
操作表的：create table, delete, drop, alter, update
查询语句
where
distinct<br>
gropu by<br>
having<br>
order by
limit
事务
ACID
原子性
一致性
隔离性
持久化
脏读<br>
事务A读取并改共享数据，还没有写回磁盘，事务B就读取数据，造成脏读
不可重复读
一个事务对数据多次读取，两次读取之间有其他事务对该数据做了改动，每次都取的数据不一致
幻读<br>
事务B对数据进行多次读取，事务A在事务B执行过程中
隔离级别
读已提交
解决脏写，脏读
读未提交
解决脏写
可重复读<br>
解决脏写，脏读，不可重复读
可串行化
解决脏写，脏读，不可重复读，幻读<br>
索引
约束和索引
创建唯一约束时会创建唯一索引
约束是确保数据的完整性，索引是优化查询效率
主键和索引的区别
子主题<br>
存储引擎
Innodb
支持事务
支持行锁
共享锁
lock in share mode
如果没有索引会锁表
排他锁
for update
delete update都会自动上排他锁
索引结构
myism
不支持事务
支持表锁
什么情况下执行器走错索引
需要避免回表
可以强制走索引，或者删除索引
回表<br>
什么是回表<br>
主键索引中的叶子节点存放了某一行的整个记录信息
非主键索引，联合索引或者是其他唯一索引叶子节点存放的是主键索引信息<br>
如果根据非主键索引联合索引或者唯一索引，去查询其他不在索引内的字段的值，就会回表
因为查到的是主键索引，再根据主键索引去表中查记录
如何避免回表
使用主键查询
或者索引覆盖
索引类别
聚集索引
主键索引
主键是加了唯一性约束的聚集索引
如果不创建聚集索引，也就是主键索引，数据库会自动创建一个隐藏的聚集索引用来物理排序存储
非聚集索引
唯一索引
创建唯一约束的列，会将该列自动创建成一个唯一索引，但是如果创建唯一索引，不会创建唯一约束
联合索引
尽量索引覆盖
0 条评论
下一页
