三范式定义(范式和反范式)
1NF:每个数据项都是最小单元,不可分割,确定行列之后只能对应一个数据。
2NF:每一个非主属性完全依赖于候选码(属性组的值能唯一的标识一个元组,但是其子集不可以)。
3NF:每一个非主属性既不传递依赖于码,也不部分依赖于码(主码=候选码为多个市,从中选出一个作为主码)。
BCNF:主属性(候选码中的某一个属性)内部也不能部分或传递依赖于码。
4NF :没有多值依赖。
基本原理流程,3个线程以及之间的关联;
整数: int(m)里的m是表示数据显示宽度,浮点数,定点数。
字符串:char(n)4.0 n 代表字节,5.0 n 代表字符 (UTF-8=3zj,GBK=2zj)
char 固定的字符数,空格补上;检索速度快。
varchar 字符数+1个字节(n<=255)或2个字节(n>255)
text 字符数+2个字节;不能有默认值;索引要指定前多少个字符;文本方式存储
blob 二进制方式存储
各种存储引擎的区别与联系 (存储数据技术和策略,存储机制、索引技巧、锁定水平等)
数据库存储引擎 show table status 显示表的相关信息
InnoDB与MyISAM的比较(从5.7开始innodb存储引擎成为默认的存储引擎。)
锁机制:行级锁,表级锁
事务操作:事务安全,不支持
InnoDB (1)可靠性要求比较高,要求事务;(2)表更新和查询都相当的频繁,并且行锁定的机会比较大的情况。
MySQL4.1之后每个表的数据和索引存储在一个文件里。
InnoDB 采用了MVCC来支持高并发,并且实现了四个标准的隔离级别。其默认级别是REPEATABLE READ(可重复读) ,行级锁。
自动灾难恢复。与其它存储引擎不同,InnoDB表能够自动从灾难中恢复。
外键约束。MySQL支持外键的存储引擎只有InnoDB。
支持自动增加列AUTO_INCREMENT属性。
MyIsam (1)做很多count 的计算;(2)插入不频繁,查询非常频繁;(3)没有事务。
表存储在两个文件中,数据文件(MYD)和索引文件(MYI)
表级锁,读=共享锁,写=排它锁。
适合选择密集型的表,插入密集型的表。
InnoDB支持事务,MyISAM不支持,对于InnoDB每一条SQL语言都默认封装成事务,自动提交,这样会影响速度,所以最好把多条SQL语言放在begin和commit之间,组成一个事务;
InnoDB支持外键,而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败;
InnoDB是聚集索引,数据文件是和索引绑在一起的,必须要有主键,通过主键索引效率很高。但是辅助索引需要两次查询,先查询到主键,然后再通过主键查询到数据。因此,主键不应该过大,因为主键太大,其他索引也都会很大。而MyISAM是非聚集索引,数据文件是分离的,索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
InnoDB支持MVCC, 而MyISAM不支持
InnoDB不保存表的具体行数,执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数,执行上述语句时只需要读出该变量即可,速度很快; 但是MyISAM只要简单的读出保存好的行数即可。注意的是,当count(*)语句包含 where条件时,两种表的操作是一样的。
Innodb不支持全文索引,而MyISAM支持全文索引,查询效率上MyISAM要高;
对于AUTO_INCREMENT类型的字段,InnoDB中必须包含只有该字段的索引,但是在MyISAM表中,可以和其他字段一起建立联合索引。
DELETE FROM table时,InnoDB不会重新建立表,而是一行一行的删除
原子性(Atomicity)一个事务必须被视为一个不可分割的最小工作单元,整个事务中的所有操作要么全部提交成功,要么全部失败回滚,对于一个事务来说,不可能只执行其中的一部分操作。
一致性(Consistency)数据库总是从一个一致性的状态转换到另一个一致性的状态。
隔离性(Isolation)一个事务所做的修改在最终提交以前,对其他事务是不可见的。
持久性(Durability)一旦事务提交,则其所做的修改不会永久保存到数据库。
4 种隔离级别
READ UNCOMMITTED(未提交读)脏读:事务中的修改,即使没有提交,对其他事务也都是可见的。
READ COMMITTED(提交读)不可重复读:事务从开始直到提交之前,所做的任何修改对其他事务都是不可见的。
REPEATABLE READ(可重复读):幻读:一个事务按相同的查询条件读取以前检索过的数据,其他事务插入了满足其查询条件的新数据。产生幻行。
SERIALIZABLE(可串行化) 强制事务串行执行
MVVC是个行级锁的变种,它在普通读情况下避免了加锁操作,自特定情况下加锁。
插入缓冲(insert buffer)
插入主键聚集索引,是顺序的,不需要磁盘的随机读取;但是这也导致同一个表中的非聚集索引不是顺序的,因为B+树的特性决定了非聚集索引插入的离散型。
插入缓存就是为提高非聚集索引的插入和更新操作的性能而做的优化设计,其原理将插入数据先放到内存就直接返回上层,上层看来已经插入成功,其实插入数据还在内存中,内部会触发内存的索引数据与物理的索引数据进行合并操作,合并时将多个插入合并到一个操作(正好一个索引页),这样大大提高了对非聚集索引插入的性能。
二次写(double write)
为了提升数据页的可靠性。
写数据页的时候宕机怎么办?
重做日志,但是如果物理页已经损坏了怎么版?
用doubleWrite:发现物理页损坏了,则找到其前面的一个副本,用副本来还原当前页,再重做日志。
自适应哈希索引(ahi)
原先的索引是B+树结构,当查询频繁,建立哈希可以提高效率,则自动构建哈希索引,提高速度。
异步IO(Async IO)
同时发起多个IO请求(索引页的扫描),可以将多个IO请求合并为一个IO操作,同时将每个IO请求的结果进行Merge。
刷新邻接页
刷新一个脏页的同事检查所在区的其他页是否需要一起刷新。
SELECT ... LOCK IN SHARE MODE SELECT ... FOR UPDATE:(LOCK IN SHARE MODE 在有一方事务要Update 同一个表单时很容易造成死锁)
乐观锁:取锁失败,产生回溯时影响效率。
取数据时认为其他线程不会对数据进行修改。
更新时判断是否对数据进行修改,版本号机制或CAS操作。
悲观锁:每次取数据都会加锁。
innodb_lock_wait_timeout 等待锁超时回滚事务: 【超时法】
直观方法是在两个事务相互等待时,当一个等待时间超过设置的某一阀值时,对其中一个事务进行回滚,另一个事务就能继续执行。在innodb中,参数innodb_lock_wait_timeout用来设置超时时间。
wait-for graph算法来主动进行死锁检测: 【等待图法】
innodb还提供了wait-for graph算法来主动进行死锁检测,每当加锁请求无法立即满足需要并进入等待时,wait-for graph算法都会被触发。
myisam更快,因为myisam内部维护了一个计数器,可以直接调取。
索引(存储引擎 快速找到记录的一种数据结构,索引的基本功能)
(1)、varchar与char的区别
char是一种固定长度的类型,varchar则是一种可变长度的类型
(2)、varchar(50)中50的涵义
最多存放50个字符,varchar(50)和(200)存储hello所占空间一样,但后者在排序时会消耗更多内存,因为order
by col采用fixed_length计算col长度(memory引擎也一样)
(3)、int(20)中20的涵义
是指显示字符的长度
但要加参数的,最大为255,比如它是记录行数的id,插入10笔资料,它就显示00000000001
~~~00000000010,当字符的位数超过11,它也只显示11位,如果你没有加那个让它未满11位就前面加0的参数,它不会在前面加0
20表示最大显示宽度为20,但仍占4字节存储,存储范围不变;
(4)、mysql为什么这么设计
对大多数应用没有意义,只是规定一些工具用来显示字符的个数;int(1)和int(20)存储和计算均一样;
B-Tree索引 索引列的顺序影响者是否使用索引。
哈希索引
无法用于排序。
只支持全部匹配。
只支持等值比较。
有很多哈希冲突时,效率不太高。
空间数据索引(R-Tree)无需前缀查询,从所有维度查询数据。
全文检索 查找文本中的关键词,类似于搜索引擎做的事情。
(1)、有多少种日志;
(2)、事物的4种隔离级别
(3)、事务是如何通过日志来实现的,说得越深入越好。
事务日志是通过redo和innodb的存储引擎日志缓冲(Innodb log
buffer)来实现的,当开始一个事务的时候,会记录该事务的lsn(log sequence
number)号; 当事务执行时,会往InnoDB存储引擎的日志
的日志缓存里面插入事务日志;当事务提交时,必须将存储引擎的日志缓冲写入磁盘(通过innodb_flush_log_at_trx_commit来控制),也就是写数据前,需要先写日志。这种方式称为“预写日志方式”
单列索引:不允许为空
普通索引 不允许有空值
唯一索引
主键索引 在 InnoDB 引擎中很重要
组合引擎:多个字段上创建的索引,复合索引时遵循最左前缀原则。
查询中某个列有范围查询,则其右边的所有列都无法使用查询
全文索引:
空间索引:
参考:细说mysql索引、我的MYSQL学习心得(九) 索引
(1)、binlog的日志格式的种类和分别
(2)、适用场景;
(3)、结合第一个问题,每一种日志格式在复制中的优劣。
磁盘存取原理
局部性原理与磁盘预读
M 阶 B-Tree
根节点至少有2个子树。
每个非叶子节点由n-1个key和n个指针组成。
分支节点至少拥有m/2颗子树,最多拥有m个子树。(除根节点和叶子结点外)
所有叶节点具有相同的深度,等于树高 h。
每个叶子节点最少包含一个key和两个指针,最多包含2d-1个key和2d个指针。
B+ Tree
内节点不存储data,只存储key。
叶子节点不存储指针。
MySQL 索引实现
MyISAM 索引文件和数据文件是分离,非聚集索引。
InnoDB 叶节点包含了完整的数据记录,聚集索引。根据主键聚集。
(1)、没有经验的,可以不问;
(2)、有经验的,问他们的处理思路。
本文由美高梅官方网站发布于数据统计,转载请注明出处:面试-MySQL总结