MySQL面试题总结

@[toc]

MySQL

• 数据库三范式？

第一范式：确保每列保持原子性，数据表中的每一列都是不可分割的
第二范式：要求实体的属性完全依赖于主关键字，也就是确保表中的每一列都和主键有关
第三范式：任何非主属性，不依赖其他非主属性，确保表中的每一列都和主键直接关联，不存在传递依赖

• SQL语句在数据库如何执行的？

1. 应用程序把查询sql发给MySQL服务器执行
2. 如果查询缓存是打开的，MySQL并不会直接去查询数据库，而是先从缓存中进行寻找，缓存中有直接返回
3. 解析SQL，校验SQL是否符合语法规则，关键字是否正确
4. 预处理，进一步校验，检查，表名和字段名是否正确
5. 根据3、4步生成的解析树，生成执行计划
6. 查询执行引擎，通过执行引擎的接口查询数据
7. 返回结果

• MySQL查询时，指令的执行顺序？

SELECT DISTINCT
	<select list>
FROM
	<left_table> <join_type>
JOIN
	<right_table> ON <join_condition>
WHERE
	<where_condition>
GROUP BY
	<group_by_list>
HAVING
	<having_condition>
ORDER BY
	<order_by_condition>
LIMIT
	<limit_params>

• 数据库类型有哪些？

整数：tinyint、smallint、mediumint、int、bigint
浮点型：float、double、decimal
字符串：char、varchar
日期：year、time、datetime、date

• char和varchar的区别？

char，固定长度，当存储数据小于指定长度时，则用空格代替填充
varchar：非固定长度，小于定义长度时，则按实际长度存储，存储的值是，每个值占用的字节+用来记录其实际长度的字节
char，效率高，但浪费空间，适用于如电话号码，等长度固定的场景，varchar，效率低，适用于长度不固定的数据存储

• 有哪些存储引擎，以及区别？

InnoDB和MyIsam
两者的区别？
事务：INNODB支持事务，而MyIsam不支持
锁类型：INNODB支持行锁和表锁，但MYISam仅支持表锁
MVCC：INNODB支持MVCC，而MYISAM不支持
外键：INNODB支持外键，MYISAM不支持外键
索引：INNODB，支持聚簇索引，5.7以后，支持全文索引，MYISAM，支持非聚簇索引，支持全文索引
安全性：INNODB，支持异常后的安全恢复，以及在线热备份，MYISAM不支持
缓存：INNODB既缓存索引又缓存真实数据，MYISAM，仅缓存索引
使用场景，INNODB是MySQL默认存储引擎，如果对事务要求比较高，并发条件下，要求数据一致性，数据操作除了插入查询，还包含大量的更新和删除，推荐INNODB（计费系统、财务系统）
如果应用以查询和插入为主，只有很少的修改和删除，对事务完整性、并发性，要求不高，可以使用MYISAM

• Buffer Pool？是什么？

它是INNODB维护的一个缓存区域，在内存中，用来缓存数据和索引，用来加快数据的读写，如果此，缓冲区域越大，MySQL就会像一个内存数据库，默认大小为128M
INNODB，会将一些热点数据或是即将要访问到的数据，存在此区域，用来提升读取性能
INNODB,如果要修改的数据在缓冲区，则会直接修改缓冲区的数据，此时称这个为脏页，INNODB会以一定的频率将脏页写回到磁盘，尽量减少IO操作

• 聚簇索引和非聚簇索引

• 什么是索引？索引的优缺点？

索引是一种用于查询和检索数据的数据结构，常见的索引结构有：B树、B+树和HASH
简单来说索引就是一个目录，就像我们查字典一样，先去目录找到这个字的位置，然后直接翻到对应页就行了
优点的化，提高查询速度，同时通过创建唯一索引，可以保持数据的唯一性
缺点：
时间上，创建索引和维护索引，都需要耗费很多时间，当对表中有索引的数据，进行增删改时，索引也需要动态修改，会降低SQL的执行效率
空间上，索引需要物理文件进行存储，会耗费一定的磁盘空间

• 哪些情况需要索引，哪些情况不需要索引？

适合建立索引的情况？
对于查询中频繁使用、用于缩小查询范围的字段上；对于需要排序的字段上
不适合建立索引的情况？
查询时，使用频率较小或是，重复值比较多；对于特殊数据（比如大文本字段），不宜建立索引

• 索引有哪些？

主键索引：数据表的主键列，使用的就是主键索引，一张表只能有一个主键索引，并且值不能重复不能为空，一般在创建表的时候指定
ALTER TABLE table_name ADD PRIMARY KEY (column_name);。
唯一索引：创建唯一索引的目的，一般是为了保证，当前列数据不能重复，但可以为空，一张表可以有多个唯一索引。
CREATE UNIQUE INDEX 索引名 ON 表名(字段名); 或 ALTER table 表名 ADD UNIQUE INDEX 索引名(字段名)
普通索引：普通索引的目的，就是为了提高查询速度，可以有多个普通索引，允许值为重复和为空
CREATE INDEX 索引名 ON 表名(字段名); （这种方式pgsql通用） 或 ALTER table 表名 ADD INDEX 索引名(字段名)
组合索引：是指在多个字段上，创建索引，只有在查询时，使用了创建索引的第一个字段，才能命中（最左前缀匹配）
create index 索引名 on 表名（字段名）
前缀索引：适用于字符串数据，只取文本的前几个字符创建索引，相比普通索引占用空间更小
CREATE INDEX index_name ON table_name (column_name(length));
全文索引：全文索引是为在检测大文本数据中的关键信息
create fulltext index 索引名字 on 表名（字段名）
修改索引：ALTER TABLE 表名 RENAME INDEX 旧索引名 TO 新索引名
删除索引：DROP INDEX 索引名 ON 表名;

• 什么情况下索引会失效？
• 事务的特性？
• 事务隔离级别？
• SQL优化？
• 乐观锁和悲观锁？
• 表级锁和行级锁？
• 共享锁和排他锁