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自动化办公系统。