基础
- UNION与UNION ALL的区别?
如果使用UNION ALL,不会合并重复的记录行
效率 UNION 低于 UNION ALL - 窗口函数
<窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名>)
专用窗口函数,包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。 - DELETE 、TRUNCATE、DROP的区别?
① DELETE 在 InnoDB 中,DELETE其实并不会真的把数据删除,mysql 实际上只是给删除的数据打了个标记为已删除,因此 delete 删除表中的数据时,表文件在磁盘上所占空间不会变小,存储空间不会被释放,只是把删除的数据行设置为不可见。
delete 操作是一行一行执行删除的,并且同时将该行的的删除操作日志记录在redo和undo表空间中以便进行回滚(rollback)和重做操作,生成的大量日志也会占用磁盘空间。
② TRUNCATE 执行后立即生效,无法找回;truncate table table_name 立刻释放磁盘空间 。只是删除表中的数据,不会删除表结构(触发器,索引,约束)
③ DROP 执行后立即生效,无法找回;truncate table table_name 立刻释放磁盘空间 。
可以这么理解,一本书,delete是把目录撕了,truncate是把书的内容撕下来烧了,drop是把书烧了。
一般而言,速度:drop > truncate > delete - 数据库三大范式
一范式:原子性(每一个属性都不能再分)
二范式就是要有主键,要求其他字段都依赖于主键。
为什么要有主键?没有主键就没有唯一性,没有唯一性在集合中就定位不到这行记录,所以要主键。
其他字段为什么要依赖于主键?因为不依赖于主键,就找不到他们。更重要的是,其他字段组成的这行记录和主键表示的是同一个东西,而主键是唯一的,它们只需要依赖于主键,也就成了唯一的。
三范式:非主键字段不能相互依赖。 每列都与主键有直接关系,不存在传递的依赖(学号–>学生–>课程就是传递相关性)
三范式就是要消除传递依赖,方便理解,可以看做是“消除冗余”。
消除冗余应该比较好理解一些,就是各种信息只在一个地方存储,不出现在多张表中。
索引
- 主键索引:唯一且不为空。一个表只能有一个主键索引,但是可以有多个唯一索引。
联合索引:遵循前缀原则 - 只要列涉及到运算,MySQL就不会使用索引。比如 SELECT FROM users WHERE YEAR(adddate) < 2007;
- B+tree 索引树高度影响因素:索引列值过长 , 数据量级,数据类型
- 聚簇索引:按照每张表的主键构造一颗B+树,同时叶子节点中存放的就是整张表的行记录数据
- 索引的数据结构?
索引数据结构有哈希表、完全平衡二叉树、B树、B+树。
Hash不支持范围查询,二叉树树高很高,B+树是B树的升级版,会有指针指向下一个节点的叶子节点。
B+Tree相对于B-Tree的不同:B+Tree非叶子节点只存储键值信息, 数据记录都存放在叶子节点中。所以B+Tree的高度可以被压缩到特别的低。 - 回表 覆盖索引
如果在我们建立的索引上就已经有我们需要的字段,就不需要回表了,在电商里面也是很常见的,我们需要去商品表通过各种信息查询到商品id,id一般都是主键,可能sql类似这样:
select itemId from itemCenter where size between 1 and 6
- 最左匹配原则是什么?
联合索引会一直向右匹配直到遇到范围查询就停止匹配。 - explain你记得哪些字段,分别有什么含义?
table(查询操作的表,可精确到问题表)、type(索引类型)、possible_keys(可能会走的索引)、key(真正走的索引)、key_len(联合索引覆盖长度)、rows、Extra
① type :索引类型
all:全表扫描,不用任何索引
index:全索引扫描 —> 把整个索引树扫描一遍
range:索引范围扫描
—> 辅助索引> < >= <= LIKE IN OR
—> 特殊情况:主键 <> NOT IN
ref:非唯一性索引,辅助索引等值查询
eq_ref:针对多表连接中,非驱动表连接条件是主键或唯一键
const( system):聚簇索引等值查询 - change buffer 是什么?使用场景是?
① 当更新一个数据页时, 如果这个数据页还没有在内存中的话,在不影响数据一致性的前提下,InooDB会将这些更新操作缓存在change buffer中,在下次查询需要访问这个数据页的时候,将数据页读入内存,然后执行change buffer中与这个页有关的操作,通过这种方式就能保证这个数据逻辑的正确性。
② 唯一索引的更新就不能使用change buffer,实际上也只有普通索引可以使用。
③ 对于写多读少的业务来说,页面在写完以后马上被访问到的概率比较小,此时change buffer的使用效果最好,这种业务模型常见的就是账单类、日志类的系统。
引擎
- MyISAM索引与InnoDB索引的区别?
InnoDB索引是聚簇索引,MyISAM索引是非聚簇索引。
InnoDB的主键索引的叶子节点存储着行数据, 因此主键索引非常高效;MyISAM索引的叶子节点存储的是行数据地址,需要再寻址一次才能得到数据。 - InnnoDB的特性
AHI:帮助InnoDB快速读取索引页。加快索引读取的所读。相当与索引的索引。
change buffer:临时缓冲辅助索引需要的数据更新。当我们需要查询新insert 的数据,会在内存中进行merge(合并)操作,此时辅助索引就是最新的。
MVCC:多版本并发控制。MVCC就是用同一份数据临时保留多版本的方式的方式,实现并发控制
事务
行锁
自动故障恢复 - InnoDB引擎体系结构
myt.frm:存放数据字典信息(列的定义和属性)
myt.ibd:独立表空间文件,存放数据行和索引信息
ibdata1:共享表空间文件,存放数字字典信息,undo logs、double write、change buffer磁盘区域 - InnoDB 和 MyISAM 的区别
InnoDB支持事务,MyISAM不支持;
InnoDB数据存储在共享表空间,MyISAM数据存储在文件中;
InnoDB支持行级锁,MyISAM只支持表锁;
InnoDB支持崩溃后的恢复,MyISAM不支持;
InnoDB支持外键,MyISAM不支持;
InnoDB不支持全文索引,MyISAM支持全文索引; - 锁机制
共享锁:不堵塞,多个用户可以同时读一个资源,互不干扰。
排他锁:一个写锁会阻塞其他的读锁和写锁 ,这样可以只允许一个用户进行写入,防止其他用户读取正在写入的资源。
表锁、页锁、行锁、间隙锁
事务
- 事务的ACID特性:A(原子性)、C(一致性)、I(隔离性)、D(持久性)
- redo logs:在MySQL出现Crash异常宕机时,提供前滚功能(CSR)
undo logs:在rollback时,会将数据恢复到修改之前的状态。回滚 - 数据库并发会引起的问题:脏读 、不可重复读、幻读
脏读:A事务读取B事务尚未提交的更改数据,并在这个数据基础上操作。如果B事务回滚,那么A事务读到的数据根本不是合法的,称为脏读
不可重复读:A事务读取了B事务已经提交的更改(或删除)数据。比如A事务第一次读取数据,然后B事务更改该数据并提交,A事务再次读取数据,两次读取的数据不一样 update
幻读:A事务读取了B事务已经提交的新增数据。注意和不可重复读的区别,这里是新增,不可重复读是更改(或删除) insert - 事务的隔离级别:
① read-uncommitted
读未提交,一个事务还没提交时,它做的变更就能被别的事务看到,即允许读取未提交的数据,会导致脏读、不可重复读、幻读的问题
② read-committed
读已提交,一个事务提交之后,它做的变更才会被其他事务看到,这里的读指的是一致性非锁定读 ,即每次都读最新的快照数据,不加共享锁,解决了脏读问题,但仍会导致不可重复读、幻读问题。
读操作事务要等待这个更新操作事务提交后才能读取数据,可以解决脏读问题。但在这个事例中,出现了一个事务范围内两个相同的查询却返回了不同数据,这就是不可重复读。
③ repeatable-read
可重复读,MySQL的默认隔离级别,总是会在事务开启的时候读取最新提交的行版本,并将该行版本一直持有到事务结束,未提交变更对其他事务也是不可见的,解决了不可重复读问题,但仍有可能发生幻读问题
④ serializable
可串行化,这种隔离级别不会造成任何并发问题,但并发性能极低 - binglog:记录的是变更类型的SQL语句,不记录查询语句
记录格式:
statement: 可读性较高(直接存储语句),日志量少,但是不够严谨
row:可读性低(需要借助工具分析),日志量大,足够严谨
网址:
https://mp.weixin.qq.com/mp/appmsgalbum?__biz=MzAwNDA2OTM1Ng==&action=getalbum&album_id=1343708196397187073&scene=173&from_msgid=2453141466&from_itemidx=1&count=10#wechat_redirect&scene=178&subscene=&sessionid=svr_62988d1ae81&enterid=1606829461