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