vlambda博客
学习文章列表

Mysql2020面试大典 -- 数据库基础知识篇

#知己知彼,方能百战不殆


数据库的三大范式,平时建表时应满足第几范式

第一范式:每个列都不可以再拆分。第二范式:在第一范式的基础上,非主键列完全依赖于主键, 而不能是依赖于主键的一部分。第三范式:在第二范式的基础上,非主。键列只依赖于主键, 不依赖于其他非主键。
在设计数据库结构的时候,要尽量遵守三范式,# 如果不遵守,必须有足够的理由。比如性能。事实上我们经常会为了性能而妥协数据库的设计。

mysql有关权限的表都有哪几个

MySQL服务器通过权限表来控制用户对数据库的访问,权限表存放在mysql数据库里,由mysql_install_db脚本初始化。这些权限表分别user,db,table_priv,columns_priv和host。
# user权限表:记录允许连接到服务器的用户帐号信息,里面的权限是全局级的。# db权限表:记录各个帐号在各个数据库上的操作权限。# table_priv权限表:记录数据表级的操作权限。# columns_priv权限表:记录数据列级的操作权限。# host权限表:配合db权限表对给定主机上数据库级操作权限作更细致的控制。# 这个权限表不受GRANT和REVOKE语句的影响

mysql binlog有几种录入格式

有三种格式,statement,row和mixed。# statement模式下,每一条会修改数据的sql都会记录在binlog中。 不需要记录每一行的变化,减少了binlog日志量,节约了IO,提高性能。 由于sql的执行是有上下文的,因此在保存的时候需要保存相关的信息, 同时还有一些使用了函数之类的语句无法被记录复制。# row级别下,不记录sql语句上下文相关信息,仅保存哪条记录被修改。 记录单元为每一行的改动,基本是可以全部记下来但是由于很多操作, 会导致大量行的改动(比如alter table), 因此这种模式的文件保存的信息太多,日志量太大。# mixed,一种折中的方案,普通操作使用statement记录,  当无法使用statement的时候使用row

mysql 有哪些数据类型


整数类型
tinyint

8位二进制

(-128~127/ 0 ~255)

smallint

16位二进制

(-32768~32767/ 0 ~ 65535)

mediumint 24位二进制
int
32位二进制
小数类型
float
单精度浮点数
double 双精度浮点数
decimal(m,d)
严格的定点数
日期类型
year
YYYY 1901~2155
time
HH:MM:SS -838:59:59~838:59:59
date
YYYY-MM-DD 1000-01-01~9999-12-3
datetime YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00~ 9999-12-31 23:59:59    
timestamp YYYY-MM-DD HH:MM:SS 1970-01-01 00:00:01 UTC~2038-01-19 03:14:07UTC
文本二进制类型
CHAR(M) M为0~255之间的整数
VARCHAR(M) M为0~65535之间的整数
TINYBLOB 允许长度0~255字节
BLOB 允许长度0~65535字节
MEDIUMBLOB 允许长度0~167772150字节
LONGBLOB 允许长度0~4294967295字节    
TINYTEXT 允许长度0~255字节
TEXT 允许长度0~65535字节
MEDIUMTEXT 允许长度0~167772150字节
LONGTEXT 允许长度0~4294967295字节
VARBINARY(M) 允许长度0~M个字节的变长字节字符串
BINARY(M) 允许长度0~M个字节的定长字节字符串

MySQL存储引擎MyISAM与InnoDB区别

# Innodb引擎:Innodb引擎提供了对数据库ACID事务的支持。 并且还提供了行级锁和外键的约束。 它的设计的目标就是处理大数据容量的数据库系统。# MyIASM引擎:不提供事务的支持,也不支持行级锁和外键。# MEMORY引擎:所有的数据都在内存中,数据的处理速度快,但是安全性不高

MyISAM Innodb
存储结构 每张表被存放在三个文件:frm-表格定义、MYD(MYData)-数据文件、MYI(MYIndex)-索引文件 所有的表都保存在同一个数据文件中(也可能是多个文件,或者是独立的表空间文件),InnoDB表的大小只受限于操作系统文件的大小,一般为2GB
存储空间 MyISAM可被压缩,存储空间较小 InnoDB的表需要更多的内存和存储,它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引
可移植性、备份及恢复 由于MyISAM的数据是以文件的形式存储,所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作 免费的方案可以是拷贝数据文件、备份 binlog,或者用 mysqldump,在数据量达到几十G的时候就相对痛苦了
文件格式 数据和索引是分别存储的,数据.MYD,索引.MYI 数据和索引是集中存储的,.ibd
记录存储顺序 按记录插入顺序保存 按主键大小有序插入
锁支持(锁是避免资源争用的一个机制,MySQL锁对用户几乎是透明的) 表级锁定 行级锁定、表级锁定,锁定力度小并发能力高
SELECT MyISAM更优
INSERT、UPDATE、DELETE
InnoDB更优
select count(*) myisam更快,因为myisam内部维护了一个计数器,可以直接调取。
索引的实现方式 B+树索引,myisam 是堆表 B+树索引,Innodb 是索引组织表
哈希索引 不支持 支持
全文索引 支持 不支持

MyISAM索引与InnoDB索引的区别?

# InnoDB索引是聚簇索引,MyISAM索引是非聚簇索引。# InnoDB的主键索引的叶子节点存储着行数据,因此主键索引非常高效。# MyISAM索引的叶子节点存储的是行数据地址,需要再寻址一次才能得到数据。# InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据, 因此查询时做到覆盖索引会非常高效。

InnoDB引擎的4大特性

插入缓冲(insert buffer)二次写(double write)自适应哈希索引(ahi)预读(read ahead)

存储引擎选择

如果没有特别的需求,使用默认的Innodb即可。# MyISAM:以读写插入为主的应用程序,比如博客系统、新闻门户网站。
# Innodb:更新(删除)操作频率也高,或者要保证数据的完整性; 并发量高,支持事务和外键。比如OA自动化办公系统。