九个常见的sql数据库面试题

1.UNION ALL 与 UNION 的区别

UNION和UNION ALL关键字都是将两个结果集合并为一个。

UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。而UNION ALL只是简单的将两个结果合并后就返回。由于UNION需要排序去重，所以 UNION ALL 的效率比 UNION 好很多。

2.TRUNCATE 与 DELETE 区别

TRUNCATE 是DDL语句，而 DELETE 是DML语句。TRUNCATE 是先把整张表drop调，然后重建该表。而 DELETE 是一行一行的删除，所以 TRUNCATE 的速度肯定比 DELETE 速度快。TRUNCATE 不可以回滚，DELETE 可以。TRUNCATE 执行结果只是返回0 rows affected，可以解释为没有返回结果。TRUNCATE 会重置水平线(自增长列起始位)，DELETE 不会。TRUNCATE 只能清理整张表，DELETE 可以按照条件删除。一般情景下，TRUNCATE性能比DELETE好一点。

3.TIMESTAMP 与 DATETIME 的区别

相同点TIMESTAMP 列的显示格式与 DATETIME 列相同。显示列宽固定在19字符，并且格式为YYYY-MM-DD HH:MM:SS。

不同点TIMESTAMP4个字节存储，时间范围：1970-01-01 08:00:01~2038-01-19 11:14:07。值以UTC格式保存，涉及时区转化，存储时对当前的时区进行转换，检索时再转换回当前的时区。

DATETIME8个字节存储，时间范围：1000-10-01 00:00:00~9999-12-31 23:59:59。实际格式存储，与时区无关。

4.什么是联合索引

两个或更多个列上的索引被称作联合索引，联合索引又叫复合索引。

5.为什么要使用联合索引

减少开销：建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。减少磁盘空间的开销。

覆盖索引：对联合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。覆盖索引是主要的提升性能的优化手段之一。

效率高：索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql select from table where col1=1 and col2=2 and col3=3，假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W * 10%=100w条数据，然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据，然后再排序，再分页;如果是联合索引，通过索引筛选出1000w * 10% * 10% * 10%=1w，效率得到明显提升。

6.MySQL 联合索引最左匹配原则

在 MySQL 建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

MySQL 会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。= 和 in 可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

7.什么是聚集和非聚集索引

聚集索引就是以主键创建的索引。

非聚集索引就是以非主键创建的索引。

8.什么是覆盖索引

覆盖索引(covering index)指一个查询语句的执行只用从索引页中就能够取得(如果不是聚集索引，叶子节点存储的是主键+列值，最终还是要回表，也就是要通过主键再查找一次)，避免了查到索引后，再做回表操作，减少I/O提高效率。

可以结合第10个问题更容易理解。

9.什么是前缀索引

前缀索引就是对文本的前几个字符(具体是几个字符在创建索引时指定)创建索引，这样创建起来的索引更小。但是MySQL不能在ORDER BY或GROUP BY中使用前缀索引，也不能把它们用作覆盖索引。

以上就是“九个常见的sql数据库面试题”，你能回答上来吗?如果想要了解更多的相关内容，可以关注极悦Java官网。