数据库二轮复习
跟着牛客走,offer天天有
1、请你说说MySQL索引,以及它们的好处和坏处
- 索引是什么:MySQL索引是一种帮助快速查找数据的数据结构,类似书的目录
- 索引的数据结构:B+树
- 是一种多路平衡查找树,更宽更矮(一个节点存放多个信息),利于存储在磁盘中
- 查询的速度也比较稳定
- 叶子节点相互连接,范围查找和遍历更加方便
- 索引的优点:方便查找
- 索引的缺点:
- 会占用一些磁盘空间用来存索引结构
- 在进行增、删、改时效率会降低
- 索引的种类
- 主键索引
- 一般主键索引都是聚集索引,一个表只能有一个,不能为空,数据在物理上是连续的
- 二级索引/辅助索引
- 非主键索引
- 唯一索引
- 单列索引
- 联合索引:最左前缀原则
- > < between like的%会停止匹配
- 需要包含左边的
- group by需要包含where的字段
- 前缀索引
- 全文索引
- 覆盖索引:一种查询方式,如果查询的字段就是索引字段,就不需要回表,叫覆盖索引
- 主键索引
- 查看是不是用了索引:explain
2、乐观锁和悲观锁
- 乐观锁
- 乐观的认为每次操作数据的时候都不会有人修改,所以不用加锁
- CAS保证,ABA问题,版本号机制和时间戳
- 悲观锁:悲观锁认为每次进行操作的时候都有会有其他线程来改,所以每次都加锁,数据库中的悲观锁分为很多种,宏观上分为
- 表锁
- 读写锁(手动)(server层):lock table T read/write;
- 元数据锁(自动)(server层):DML(数据库操作语言)自动加R锁,DDL(数据库定义语言)自动加W锁
- 意向锁(自动)(引擎层):解决行锁和表锁之间的冲突,当行锁加了共享锁(S)会自动的获取意向共享锁(IS),互斥锁一样。当要锁表时需要监测是否有意向锁,不用一行一行的查有没有行锁了
- 行锁:InnoDB
- 共享锁(S)
- 读(当前读):select * from tab lock in share mode;
- 排他锁/互斥锁(X)
- 增删改:自动加锁
- 读(当前读):select * from tab for update;
- 提交事务自动释放锁
- 共享锁(S)
- 表锁
3、请你说说MySQL的事务隔离级别
- 事务的特性:ACID
- 原子性:undolog
- 一致性:binlog
- 隔离性:隔离级别
- 持久性:redolog
- 隔离级别
- 读取未提交:脏读、幻读、不可重读
- 读取已提交:幻读、不可重读
- 可重复读(innodb默认):幻读——使用mvcc或者当前读(加锁)解决
- 串行化
4、请你说说聚簇索引和非聚簇索引
- 聚集索引是指数据和索引存在一起,可以通过索引直接查找到数据,一般主键索引都为聚集索引;非聚集索引数据和索引没有存在一起,一般二级索引是非聚集索引,叶子节点存的是主键值
- 一张表只能有一个聚集索引,可以有多个非聚集索引
- MyISAM用的是非聚集索引(主键和非主键),每个叶子节点存的是数据地址;InnoDB的主键聚集索引,存的数据,二级索引存的是主键
- 使用聚集索引+非聚集索引对比只用非聚集索引的优势
- (大规模访问时)叶子节点存数据,访问同一页数据的时候已经将同一页数据加载到内存了,不用再次从磁盘加载,减少磁盘io
- 聚集索引存数据是连续的,MyISAM用的非聚集是乱序的(物理地址),所以MyISAM在大规模读取时不占优势会增加磁盘io,但加上mvcc等维护,还是MyISAM读取更快一些
- (维护)在聚集索引+非聚集索引中,当页表分裂或者行变换时,不用维护非聚集索引的叶子节点
- (大规模访问时)叶子节点存数据,访问同一页数据的时候已经将同一页数据加载到内存了,不用再次从磁盘加载,减少磁盘io
5、数据库为什么不用红黑树而用B+树?
- 红黑树是近似平衡的二叉树,虽然增删改查的效果很高,但是由于索引是存储在磁盘中的,在查询的时候会产生大量的磁盘IO降低查找的速率,并且随着数量增多,树也会变的越来越高,占用空间
- B+树作为一种多叉树,每个节点存了多个索引,降低了树的高度,同时也减少了磁盘的IO次数;并且由于叶子节点是连在一起的,所以在范围查找和遍历的时候也会更快
6、请你说说innodb和myisam的区别?
- 相同点:都是MySQL的引擎
- 不同点
- 是否支持redolog(重做日志)
- 是否支持行级锁
- 是否支持事务
- 是否支持外键
- MyISAM查询速率比InnoDB更快(innodb需要在select时候维护缓存、MVCC、文件索引)
7、请你说说索引怎么实现的B+树,为什么选这个数据结构?
- 结构:非叶子节点存多个索引信息,按照键值排列,叶子节点存数据,并且相邻叶子节点是连接的
- 为什么
- (树深度)多路的查找树,可以存储更多的信息,树深度比较矮
- (磁盘io)多路查找,叶子节点存放数据,磁盘IO次数少
- (访问速度)查找访问速度较快
8、请你说说数据库引擎有哪些,各自有什么区别
- InnoDB、MyISAM、Memory
- InnoDB引擎: - 支持事务,支持外键,支持行级锁,增删改性能更优,不支持全文索引( 但可以通过插件等方式支持 ),需要更多的内存和存储空间,支持崩溃恢复机制,只支持B+树
- MyISAM引擎: - 不支持事务,不支持外键,只支持表级锁,查询性能更优,默认支持全文索引,占用空间较小,没有崩溃恢复机制,支持B+树(默认),也支持B树
- Memory引擎: - 不支持事务,不支持外键,存储在内存中,可快速查找数据,不支持范围查找,支持Hash(默认),也支持B+树,也支持B树
9、请你说说数据库索引的底层数据结构
- 红黑树、B树、hash、B+树
10、请你讲讲B树和B+树
- 叶子节点不存数据,一次性可以从内存中读取更多的键查找的更快(一次磁盘io加载一页多条数据,b树每次节点都要进行磁盘访问)
- 查找速度稳定
- 叶子节点相互连接,可以进行范围查找或者是遍历
11、MySQL主从同步是如何实现的?
- 1、主服务器(master)把数据更改记录到二进制日志(binlog)中
- 2、从服务器(slave)把主服务器的二进制日志复制到自己的中继日志(relay log)中
- 3、从服务器重做中继日志中的日志,把更改应用到自己的数据库上,以达到数据的最终一致性
- 异步复制:异步复制中,主库将数据更新传播给从库后立即提交事务,而不论从库是否成功读取或重放数据变化。这种情况下,在主库事务提交后的短时间内,主从库数据并不一致。
12、数据库事务
- ACID
13、请你说说数据库的索引是什么结构,为什么不用哈希表
- hash进行了散列化,不支持模糊查询
- hash可能会出现哈希碰撞,查询效率不稳定
- hash需要一次性加载到内存,b+树可以分段加载
- hash无法做到范围查找
14、请你说说InnoDB的MVCC
- 名称:多版本的并发控制,又叫快照读,通过无锁的方式解决RR下的幻读,在高并发下有着更高的效率
- 实现
- 行隐藏字段:DB_TRX_ID(事务id)、DB_ROLL_PTR(回滚指针)、DB_ROW_ID(如果没有主键用这个做主键)
- ReadView
- m_ids:还未提交的事务id
- m_low_limit_id:ids(未提交)中最小的id
- m_up_limit_id:当前出现过最大的id+1
- m_create_trx_id:创建readview的事务id
- undolog
- 数据可见性算法
- 小于m_low_limit_id的都可见
- 小于m_up_limit_id都可见
- m_low_limit_id和m_up_limit_id之间的,ids中没有就可见
- 否则就通过DB_ROLL_PTR去undolog中找上一个版本的数据
15、索引失效
(1)对列进行计算或者是使用函数,则该列的索引会失效
(2)不匹配数据类型,会造成索引失效
(3)where语句中使用了IS NULL或者IS NOT NULL,会造成索引失效
(4)使用了like操作,以%开头,索引就将不起作用
(5)在WHERE中使用OR时,有一个列没有索引,那么其它列的索引将不起作用
(6)在索引字段上使用not,< >,!=
16、索引为什么查找速度比较快
- 多路查找树,使用了二分的原理,加快查找的速度
- 每个节点存多个值,叶子节点存数据,减少了磁盘io的次数