数据库面试题

数据库的三范式

• 第一范式（1NF）:原子性，每个字段的数据都不可再分

• 第二范式（2NF）:唯一性，在满足第一范式的前提下，表中的非主键字段都完全依赖于主键，而不是依赖于主键的一部分（联合主键）

• 第三范式（3NF）:消除传递依赖，在满足第二范式的基础上，表中的每个非主键字段都直接依赖于主键，而不是通过其他非主键字段间接依赖于主键。

索引的优缺点

优点

• 大大加速了数据的检索速度（大大减少了数据的检索量）

• 使用唯一性索引，保证每一行数据的唯一性

缺点

• 创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低SQL执行效率

• 索引需要使用物理文件存储，需要耗费一定空间

常见索引类型

• B-Tree索引（平衡树索引）：最常见和默认的索引类型，支持等值查询、范围查询和排序

• 唯一索引：被索引的列的值是唯一的，插入和更新数据的时候会校验索引列数据的唯一性

• 主键索引：特殊的唯一索引，每个表只能有一个主键索引，可以提高数据的访问速度和数据完整性

• 全文索引：用于在文本数据中进行全文搜索，支持关键词搜索，不仅仅是简单的等值或范围搜索。

MySQL字段类型

• 数值类型：整型（TINYINT、SMALLINT、MEDIUMINT、INT和BIGINT）、浮点型（FLOAT 和 DOUBLE）、定点型（DECIMAL）

• 字符串类型：CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB等，最常用的是CHAR（定长字符串）和VARCHAR（变长字符串）。

• 日期时间类型：YEAR、TIME、DATE、DATETIME和TIMESTAMP等

unsigned整数类型属性的作用

• 不允许为负值的无符号整数

• 可以将正整数的上线提高一倍，例如普通TINYINT的正常取值范围为-128~127，TINYINT UNSIGNED取值范围为0~255

• 适用于从0开始递增的ID列字段，提供了更多的可用ID值

CHAR和VARCHAR的区别

• CHAR是定长字符串，VARCHAR是边长字符串

• CHAR在存储时会在右边填充空格以达到指定长度，在检索时会去掉空格。VARCHAR在存储时会占用1或2个额外字节记录字符串长度，检索时无需处理

• CHAR适用于长度较多或长度差不多的字符串，如MD5加密后的密码、身份证号码、Bcrypt算法等。VARCHAR适用于长度不确定或者差异较大的字符串，如用户昵称，文章标题等。

DATETIME和TIMESTAMP的区别

• DATETIME类型没有时区信息，TIMESTAMP和时区有关

• DATETIME占用8个字节存储空间（5~8），TIMESTAMP占用4个字节存储空间（4~7）

• TIMESTAMP表示的时间范围更小，只能从1970年到2038年

NULL和’’的区别（不建议使用NULL作为默认值）

• NULL是一个不确定值，两个null不一定相等，如select NULL=NULL 结果为false,但使用DISTINCT,GOURP BY,ORDER BY时，又被认为是相等的

• ‘’的长度为0，不占用空间，NULL需要占用空间

• NULL会影响聚合函数的结果，如SUM、AVG、MIN、MAX等会忽略NULL值，COUNT(*)会统计所有记录数包括NULL,而COUNT(列名)会忽略NULL值

• NULL值必须使用IS NULL和IS NOT NULL来做判断，不能使用比较运算符

MySQL存储架构

MyISAM和InnoDB的区别

• MyISAM只支持表级锁，InnoDB支持行级锁和表级锁，默认是行级锁，且支持MVCC(行级锁的升级，有效减少加锁操作，提升性能)

• MyISAM不支持事务，InnoDB支持事务，实现了SQL标准定义的四种隔离级别，默认使用REPEATABLE-READ（可重读）隔离级别，可以解决幻读问题的发生

• MyISAM不支持外键，InnoDB支持外键，外键对于维护数据一致性非常有帮助，但是对性能有一定的损耗，一般不建议在数据库层面使用外键，而是在应用层进行约束

• MyISAM不支持数据库异常崩溃后的安全恢复，InnoDB支持，在数据库异常崩溃后重启数据库能保证数据恢复到崩溃前的状态，主要依赖于redo log日志

• InnoDB的性能比MyISAM更强大,随着CPU核数的增加，InnoDB的读写能力呈线性增长

• MyISAM引擎和InnoDB引擎都是使用B+Tree作为索引结构，但是两者的实现方式不太一样

聚簇索引和非聚簇索引

聚簇索引（聚集索引）

非聚簇索引（非聚集索引）

二级索引

按应用维度划分索引

• 主键索引：加速查询，列值唯一（不可以有NULL），表中只有一个，如果没有显式指定，InnoDB会自动检查是否有唯一索引且不允许为NULL值的字段，选择其作为主键，否则创建一个6Byte的自增主键。

• 普通索引：仅加速查询。

• 唯一索引：加速查询 + 列值唯一（可以有 NULL）。

• 覆盖索引：一个索引包含（或者说覆盖）所有需要查询的字段的值，从而避免了回表操作。

• 联合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并。

• 全文索引：对文本的内容进行分词，进行搜索。目前只有 CHAR、VARCHAR、TEXT列上可以创建全文索引。一般不会使用，效率较低，通常使用搜索引擎如ElasticSearch代替

最左匹配原则

索引下推

正确使用索引

• 不为NULL的字段：索引字段的数据尽量不为NULL

• 被频繁查询的字段

• 被作为查询条件的字段

• 频繁需要排序的字段：可以利用索引的排序增加查询效率

• 被频繁用于连接的字段

• 被频繁更新的字段慎用索引

• 限制每张表的索引个数：每张表索引个数最好不超过5个，如果同时有很多个索引都可以用于查询，就会增加MySQL优化器生成执行计划的时间，降低查询性能

• 尽可能的考虑建立联合索引而不是单列索引：较多的单列索引占用磁盘空间大，且修改数据会比较耗时

• 注意避免冗余索引：索引的功能相同，尽量扩展已有索引而不是新建索引

• 字符串类型的字段使用前缀索引代替普通索引：前缀索引仅限于字符串类型，较普通索引会占用更小的空间

• 删除长期不使用的索引：不用的索引的存在会造成不必要的性能损耗（查询sys库的schema_unused_indexes 视图）

索引失效

• 组合索引不满足最左匹配规则

• 在索引列上使用函数、计算、类型转换等操作

• 以%开头的LIKE查询

• 查询条件中使用了OR，且OR的左右两边条件中有一个没有索引

• IN或NOT IN取值范围较大，会走全表扫描

• 使用!=或<>操作符通常会导致索引失效

• MySql认为走全表扫描的速度比走索引速度快，如数据量较少

MySQL执行计划

type:查询时访问数据的方式

• const:表中最多只有一行匹配记录，常用于使用主键或唯一索引的所有字段作为查询条件

• eq_ref:当连表查询时，前一张表的行在当前这张表中只有一行与之对应，使用主键或唯一索引的所有字段作为连表条件

• ref:使用普通索引作为查询条件，查询结果可能找到多个符合条件的行

• range:对索引列进行范围查询，执行计划中的key列表示哪个索引被使用了

• index:遍历索引树查询，通常发生在查询结果只包含索引字段时

• ALL:全表扫描

key:实际使用到的索引，如果为空则表示没有用到索引

Extra:解析查询的额外信息

• Using filesort：在排序时无法通过索引排序，甚至可能通过文件排序

• Using temporary：使用临时表保存查询结果，常见于ORDER BY 和 GROUP BY

• Using index：表明查询使用了覆盖索引，不用回表

• Using index condition：表示查询使用了索引下推，在存储引擎层过滤数据

• Using where：表示查询使用了WHERE子句进行过滤，一般在没有使用索引的时候会出现

• Using join buffer：连表查询时，被驱动表的没有使用索引的时候，MySQL会先将驱动表读出来放到join buffer中，再遍历被驱动表与驱动表进行查询

事务的特性

• 原子性（Atomicity）：事务中的所有操作要么全部成功，要么全部失败回滚。通过undo log（回滚日志）来保证

• 一致性（Consistency）：事务执行前后，数据满足完整性约束，数据库保持一致性状态。通过持久性+原子性+隔离性来保证

• 隔离性（Isoloation）：多个事务并发执行时，一个事务的操作对其他事务是不可见的。通过MVCC(多版本并发控制)来保证

• 持久性（Durability）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。通过redo log（重做日志）来保证

并行事务常见问题（严重性排序：幻读>不可重复读>脏读）

四种隔离级别

• 读未提交（read uncommitted）:指一个事务还未提交时，它做的变更就可以被其他事务看到。可能发生脏读、不可重复读、幻读

• 读提交（read committed）:指一个事务提交后，它做的变更才能被其他事务看到。可能发生不可重复读、幻读

• 可重复度（repeatable read）:指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，MySQL InnoDB引擎的默认隔离级别。可能发生幻读

• 串行化（serializable）:会对记录加上读写锁，通过强制事务排序，必须等前一个事务执行完成，才能继续执行后一个事务。脏读、不可重复读和幻读都不可能会发生

MVCC(多版本并发控制)

分析加锁命令

update没加索引导致锁全表

如何避免

• 使用where条件必须有索引列

• 使用limit

• 同时使用limit和where，where条件中可以没有索引列