left join、right join和inner join区别?
Mysql数据库
事务的特性
原子性(支持回滚,底层根据undo log,事务如果中途出现错误,会进行回滚,回滚到开始前的状态)
一致性 (事务开始前和结束后,数据库的完整性约束没有破坏)
隔离性(只允许一个事务请求同一数据,不同事务之间彼此**互不干扰**,底层MVCC多版本并发控制间隙锁--读写锁)
持久性 (不支持回滚,底层根据 redo log)
事务的并发问题(脏读、幻读、不可重复读)
脏读:事务A已经更新了一份数据,在这个过程中,事务B去执行了同一份数据,但是由于某些原因,被修改的数据rollback了,然后一个事务所读取的数据就不一样了(没有提交,进行了回滚)
不可重复读(一个事务中不允许多次读取数据):事务a多次读取同一个数据,事务B在事务A多次读取的过程中对数据做了更改,导致最终事务A读的数据不一致(提交成功了)
幻读:管理员A已经把学生的信息全部统计完毕了,在统计过程中,管理员B添加了一条数据,但是管理员A不知道,等管理员A执行完之后,发现有一条数据没有被统计进来,这个时候就发生了幻读(提交成功了)
不可重复读和幻读的区别:不可重复读是侧重于修改,幻读侧重于新增或删除,解决不可重复读就锁住满足条件的,解决幻读是需要锁全表
MySQL事务隔离级别
读未提交(脏读、不可重复读、幻读)事务A读取到事务B未提交的数据
读已提交(不可重复读、幻读)事务A去修改数据但是不提交,事务B查询数据查询的还是原来的数据,事务A提交事务,事务B再次读取数据,读到的数据和第一次读取 的数据是不一致的
可重复读(幻读 默认的)事务A在执行的过程中不会读取到其他提交的事务,只有当前事务结束之后才可以读取到
串行化 排队依次去执行
binlog、redo log和undo log
bin log:读写分离,每次操作都会记录到binlog中去,可以直接在binlog中去查询丢失的数据
redo log:保证持久性
undo log:保证原子性
MySQL事务实现原理
主要是用undo log和redo log来实现的。
undo log用来恢复数据,保证了原子性
redo log用来回滚数据,保证了持久化
事务的隔离性是通过(读写锁(间歇锁)+MVCC)来实现的
MVCC的原理:将历史信息在快照内存中,其他事务发生删除修改操作,都是对他不可见的
间隙锁的原理:读取数据的该行与上一行和下一行有一个间隙的锁定,保证在此范围内读取到的数据是一致的
left join、right join和inner join区别?
left join(左连接) 返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右连接) 返回包括右表中的所有记录和左表中联结字段相等的记录
inner join(内连接) 只返回两个表中联结字段相等的行
说一下 mysql 的行锁和表锁
mysiam支持表锁,innodb支持行锁
表级锁:开销小,加锁快,不会出现死锁。锁定力度大,并发量最低,发生锁冲突的概率最高
行级锁:开销大,加锁慢,会出现死锁。锁定力度小,并发度最高,发生锁冲突的概率小
索引有哪些
主键索引,唯一索引,普通索引,组合索引
数据结构
数组:查询效率比较高,添加,删除效率比较低
链表:删除添加操作效率比较高,查询效率比较低
hash:做的是数据的比较,不能那个做区间查询 等值查
二叉树:
二叉树是每个节点最多有两个子节点的树。
二叉树的叶子节点有0个字节点,二叉树的根节点或者内部节点有一个或者两个字节点。
二叉树在极端的情况下会出现单链表的情况,所以说查询的速度还是慢
红黑树:根节点永远是黑色的
红黑树与AVL树的比较:
AVL是严格的平衡树,因此在增加或者删除节点的时候,根据不同情况,旋转的次数比红黑树要多;
红黑树是用非严格的平衡来换取增删节点时候旋转次数的降低开销;
所以简单说,如果你的应用中,搜索的次数远远大于插入和删除,那么选择AVL树,
如果搜索,插入删除次数几乎差不多,应选择红黑树
B树(b-)一个节点可以存多个数据块,它的高度降低了很多,顶多达到3层高度,高度变小,IO次数变少,性能有提升
B+树(在B树上做了改造)
Innodb和Myisam存储引擎区别
1.索引区别,Innodb聚集索引(数据文件和索引文件放在一起的)
Innodb二级索引(非主键的索引)(建立普通索引和主键的关系,先通过普通索引找主键,然后回表找数据)
Myisam非聚集索引(数据文件和索引文件分开的)
Myisam二级索引(跟主键没有关系,都是维护独立索引树,然后叶子节点存地址,然后通过地址找数据)
2.Myisam不支持事务的,Innodb支持事务
3.Myisam表级锁,Innodb是行级锁(必须使用到索引列才能去使用)
4.Myisam崩溃后无法安全恢复 ,Innodb具有自动崩溃恢复功能
为什么索引底层实现选择B+
1.新增了叶子节点,叶子节点存的是类似链表有序的数据,所以可以排序或区间查询
2.充分利用操作系统空间局部性原理(磁盘在读取数据的时候,不是按需读取的,是按页读取的,它会把需要的数据的,周边也读取到,比如说,1.2.3.4.5,我们读取4,会把它周边的1.2.3.4.5都读出来,减少了IO的次数),mysqB+树非叶子节点的数据都是按页存储,默认页存储是16KB
3.B+树在在其他树上改造的,数的高度控制了了,整个IO查找次数减少了,性能有提升
uuid为什么不适合做主键?
32位,空间占用率大,无序,Innodb索引文件会很大
因为我们数据库通常Innodb用存储引擎,它的索引结构是根据主键去组织的,那么占用空间会很大,并且我们建了二级索引, 二级索引它会和主键索引建立关系,先去二级索引查询主键,然后通过主键再去查询数据块(回表 )
因为无序,没法充分利用B+树特性,非叶子节点没法充分使用操作系统系统空间局部性原理,导致性能低
1万数据未支付,已支付,支付失败状态3种,适合建索引么?
不适合,建索引规则必须要保证离散型最强的一列,不然重复比较多
sql优化
-- 全表扫描 EXPLAIN select * from tb_test;
EXPLAIN select * FROM tb_test where mobile='15921484451';
EXPLAIN select * FROM tb_test where mobile=15921484451;
数据库有隐式类型转换,索引类型不一样,不走索引
EXPLAIN select * FROM tb_test where mobile like '%21%';
EXPLAIN select * FROM tb_test where mobile like '134%';
like查询前模糊匹配不走索引,like后模糊匹配可能会走索引
组合索引user_name与mobile
EXPLAIN select * FROM tb_test where user_name='大宝' and mobile='15921484451';
EXPLAIN select * FROM tb_test where user_name='大宝' ;
EXPLAIN select * FROM tb_test where mobile='15921484451';
Sql优化不走索引的情况?
1.数据库有隐式类型转换,索引类型不一样,不走索引
2.like查询前模糊匹配不走索引,后模糊匹配可能会走索引
3.NULL列不走索引
4.not,not in ,!=,or不走索引
5.在建的索列上有函数操作,都不走索引,比如EXPLAIN select * FROM tb_test where substr(mobile,1,3)='159'
6.组合索引必须满足最左匹配原则,比如:abc,a,ab,abc,ac
索引覆盖
建了user_name和mobile对应的组合索引
select * from tb_user_test where user_name='大宝' and mobile='15921484451';
select user_name,mobile from tb_user_test where user_name='大宝' and mobile='15921484451';
如果查询的列只包含我们建的索引列,此时不需要回表操作,叫索引覆盖
回表
Innodb是聚集索引,它的索引结构是根据主键去组织的,二级索引它会和主键索引建立关系,先去二级索引查询主键,然后通过主键再去查询数据块的这个过程
mysql深度分页问题
在查询的时候,如果查询的数据量较大的情况下,用limit查询的时候,查询的效率会慢很多,如:
select * from tb_user limit 3000000,10;
优化方案:
1.如果表有自增id,最好加上一个条件,id>,取上一页的id加上id>上一页id,可以提高效率
2.如果没有自增,可以用延迟关联处理
因为会有一个回表的操作,所以说可以先查询出他们的id,查询出来之后然后在去查询数据,通过inner join去连接
标签:事务,log,mysql,查询,索引,干货,Mysql,数据,主键 From: https://blog.csdn.net/2403_86981468/article/details/141665487