MySQL面试题——第一篇

1. 一张自增表里面总共有7条数据，删除了最后2条数据，重启数据库后又插入了一条数据，此时ID是几

表类型如果是MyISAM，那么id就是8
如果是InnoDB，那就是6
InnoDB表只会把自增主键的最大id记录在内存中，所以重启之后才会导致最大id丢失。

2. FLOAT和DOUBLE的区别是什么

Float最多可以存储8位的十进制数，并在内存中占4个字节
Double最多可以存储16位的十进制数，并在内存中占8字节。

3. Char和Varchar区别是什么

char(n): 固定长度类型，比如订阅char(10)，当输入“abc”三个字符的时候，他们占的空间还是10个字节，其他7个是空字节。
char的优点：效率高，缺点：占用空间、适用场景：存储密码的md5值，固定长度的，使用char非常合适。
varchar(n): 可变长度，存储的值是每个值占用的字节再加上一个用来记录其长度的字节的长度。
所以，从空间上考虑varchar比较合适，从效率上char比较合适。

4. varchar(50)中的50是什么意思?

字段最多存放50个字符，varchar(50)和varchar(200)存储“jay”字符串所占空间是一样的，后者在排序上会消耗更多的内存。

5. 如果一个表有一列定义为TimeStamp，将发生什么

每当行被更改时，时间戳字段将获取当前时间戳。

6. DateTime和TimeStamp的区别

存储精度都为秒
区别

1. Datetime日期范围是1001-9999；TimeStamp时间范围是1970-2038；
2. DateTime存储时间和时区无关，TimeStamp存储时间和时区有关，显示的值也依赖时区。
3. DateTime存储空间为8字节，TimeStamp存储空间为4字节。
4. DateTime默认值为null，TimeStamp默认不为空。

7. 数据库存储日期格式时，如何考虑时区转换问题

datetime类型适合用来记录数据的原始创建时间，修改记录中其他字段的值，datetime字段的值不会改变，除非手动修改他。
timestamp类型适合用来记录数据最后修改时间，只要修改了记录中其他字段的值，timestamp字段的值都会被自动更新。

8. 如果要存储用户的密码散列，应该使用什么字段进行存储

密码散列，盐，用户的身份证号等固定长度的字符串，应该使用char而不是varchar来存储，这样可以节省空间且提高检索效率。

9. 空值和NULL的区别是什么

空值表示字段的值为空，而NULL表示字段没有值，他们的区别如下：

1. 空值不占用空间，NULL值是未知的占用空间。
2. 空值判断使用=或者<>,NULL值使用IS NULL或者IS NOT NULL。
3. 使用count统计某字段时，如果是NULL表示忽略，空值则会算入统计之内。

10. DROP、DELETE和Truncate区别

	drop	delete	truncate
类型	DDL	DML	DDL
回滚	不可回滚	可以回滚	不可回滚
删除内容	从数据库中删除表，所有的数据行、索引、权限也会被删除	表结构还在，删除表中所有数据	表结构还在，删除表中所有数据
删除速度	删除速度最快	慢、逐行删除	删除速度快

11. MySQL中IN和Exists区别

数据库最费劲的就是和程序的连接，释放。假设连接两次，每次做上百万次的查询，这样连接过程只做了两次；相反建立了上百万次的连接，申请连接释放反复重复，系统会崩溃。即 MySQL优化原则，就是小表驱动大表，小的数据集驱动大的数据集，从而让性能更优。因此，要选择最外层循环小的，即（Select * from A where id exists/IN (select id from B)）当A数据量大于B时，适合用IN，如果A数据量小于B，适合用exists。

12. UNION和UNION ALL区别

Union: 对两个结果集进行并集操作，不包括重复行，同时进行默认规则排序。
Union All：对两个结果集进行并集操作，包括重复行，不进行排序。

13. 什么是内连接、外连接、交叉连接

内连接：取得两张表中存在连接匹配关系的记录。
外连接：取得两张表中存在连接匹配关系的记录，以及某张表中不满足匹配关系的记录。
交叉连接：也称为笛卡尔积。没有匹配关系进行筛选。

14. MySQL的内连接、左连接和右连接有什么区别

Inner Join内连接：在两张表进行连接查询时，只保留两张表中完全匹配的结果集。
left join：在两张表进行连接查询时，会返回左表所有的行，即使在右表中没有匹配记录。
right join：返回右表所有的行，即使在左表中没有匹配的记录。

15. Now()和Current_Date()有什么区别？

Now()用于显示当前年月日-时分秒
Current_Date()仅显示当前年月日。

16. Count(1)、Count(*) 、Count(列名)的区别

• count(1) 包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为null
• count(*) 包括了所有列，相当于行数，在统计结果的时候，不会忽略列值为null
• count(列名) 只包括列名的那一列，在统计结果的时候，会忽略列值为空的记录。

17. 在MySQL中有对Count(*)做优化吗？做了哪些优化？

count(*) 在不同的MySQL引擎中的实现方式是不相同的，在没有where条件的情况下

• MyISAM引擎会把表的总行数存储在磁盘上，因此，在执行count(*)的时候会直接返回这个行数。执行效率很高。
• InnoDB引擎中count(*) 需要把数据一行行读出来，然后累加计数。

InnoDB中，MySQL还是做了优化的，对于count()这样的操作，遍历任意索引树得到的结果，在逻辑上是一样的，因此，MySQL优化器会找到最小的那颗索引树来遍历，这样就能保证在逻辑正确的前提下，尽量少扫描数据量，从而优化了count()的执行效率。

18. 什么是视图以及为何要使用视图

视图是一个虚拟的表，是一个表中的数据经过某种筛选后的显示方式，视图由一个预定义的查询select语句组成。
使用视图可以提高复杂SQL语句的复用性和表操作的安全性。

19. 可以使用多少列创建索引

任何标准表最多可以创建16个索引。

20. 什么是最左前缀原则？什么是最左匹配原则？

最左前缀原则：创建多列索引时，根据业务需求，where子句中使用最频繁的一列放在最左边。
最左匹配原则：创建一个组合索引（k1、k2、k3）相当于创建了(k1)、(k1、k2)、(k1、k2、k3)三个索引。

21. 聚集索引和非聚集索引区别

1. 聚集索引：物理存储按照索引排序，非聚集索引，物理存储不按照索引排序。
2. 聚簇索引：索引的叶节点中就是数据节点，而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。
3. 一个表中只能有一个聚集索引，而非聚集索引一个表可以有多个。

22. MYSQL索引为什么用B+树，而不是二叉树

1. 为什么不是一般二叉树？
   如果二叉树特殊化为一个链表，相当于全表扫描。平衡二叉树相比于二叉查找树来说，查找效率更稳定，总体查找速度更快。
2. 为什么不是平衡二叉树
   在内存比在磁盘的数据，查询效率快得多。如果是平衡二叉树，那么每个节点只能存储一个键值和数据，每次查找都要从磁盘读取节点，如果是B树，可以存储更多的节点数据，树的高度也会降低，因此，读取磁盘的次数就降下来了，查询效率更快。
3. 为什么不是B树而是B+树呢

• B+树非叶子结点上是不存储数据的，仅存储键值，而B树节点中不仅存储键值，也会存储数据。InnoDB中页的默认大小是16Kb，如果不存储数据，那么就会存储更多的键值，相应的树的阶数就会更大，树就会更矮更胖，如此一来查找数据进行磁盘IO操作就会进一步降低，数据查询效率也会更高。
• B+树索引的所有数据都存储在叶子结点上，而且按照顺序排列的，那么B+树进行范围查找，排序查找、分组查找就会更快。

23. MYSQL常用引擎，InnoDB和MyISAM的区别

InnoDB引擎：
InnoDB引擎提供了对数据库的acid事务的支持，并且还提供了行级锁和外键的约束。他的设计目标就是处理大数据容量的数据库系统。MySQL运行的时候，InnoDB会在内存中建立缓冲池，用于缓冲数据和索引，但是该引擎是不支持全文搜索，同时启动也比较慢。不保存表的行数，所、以当进行select count(*)的时候，需要进行全表扫描。由于锁的粒度小，写操作不会锁定全表，所以比较适合并发较高的场景。

MyISAM引擎：
MySQL的默认引擎，不提供事务支持，也不支持行级锁和外键。因此，当执行插入、更新时，需要锁定整个表，执行效率降低。不过和InnoDB不同的是，MyISAM引擎保存了表的行数，于是进行select count(*) from table语句时，可以直接读取已经保存的值而不需要全表扫描。 所以，如果读的操作远远大于写操作时，并且不需要事务支持，可以将MyISAM作为数据库引擎的首选。

24. MySQL可以针对表级别设置数据库引擎吗？怎么设置？

可以针对不同的表设置不同的引擎，在create table语句中使用engine=引擎名，如
create table student( id int primary key auto_increment, username varchar(20), age int )engine=Memory

25. InnoDB引擎的索引策略

1. 覆盖索引
2. 最左前缀原则
3. 索引下推（索引下推优化是MySQL5.6引入的，可以在索引遍历过程中，对索引包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。）

26. InnoDB引擎的四大特性

1. 插入缓冲
2. 二次写
3. 自适应hash索引
4. 预读

27. InnoDB的事务与日志的实现方式

InnoDB的两种日志redo 和undo

日志的存放方式

• redo：在页修改的时候，先写到redo log buffer中，然后写到redo log的文件系统缓存，最后同步到磁盘。
• undo：在MySQL5.5之前，放在共享表空间中，5.6之后，可以通过设innodb_undo_tablespaces参数把undo log存放在ibdata外。
  事务是如何通过日志来实现的
• 恢复操作：在系统发生故障时，数据库系统可以通过读取事务日志中的记录，将数据库恢复到最近的检查点，并通过重放日志文件中的操作来恢复未完成的事务。
• 回滚操作：如果事务在执行过程中发生错误，数据库可以通过undolog将事务回滚到开始前的状态。

28. InnoDB中count(*)、count(1)、count(主键)、count(字段)哪个性能最高

• count(*)： 并不会把全部字段取出来，而是专门做了优化，不取值，直接按照行累加。
• count(1): InnoDB遍历整张表，但是不取值。server层对于返回的每一行，放一个数字【1】进去，判断是不可能为空的，按行累加。
• count(主键)：InnoDB会遍历整张表，把每一行的id值都取出来，返回给Server层，server层拿到id后，判断不为空，就按行累加。
• count(字段)：如果这个字段定义为not null，一行行从记录里面读出这个字段，判断不能为null，按行累加。如果允许null，则判断不为null的行进行累加。
  count(字段)<count(主键id)<count(1) = count(*)

29. MySQL事务ACID特性的实现思想

原子性：使用undo log来实现，如果事务执行过程中出错或者用户执行了rollback，系统通过undo log日志返回事务开始状态。
持久性：使用redo log实现
隔离性：MVCC以及锁
一致性：通过redo log、以及undo log来做回滚、恢复。

30. MySQL锁分类

1. 按照锁粒度分

• 行锁
• 页锁
• 表锁

2. 加锁机制

• 乐观锁
• 悲观锁

3. 锁模式

• 记录锁（record lock）
• 间隙锁（gap lock）
• next-key lock（record lock+gap lock）

4. 兼容性

• 共享锁
• 排他锁

31. MySQL事务实现原理是什么

InnoDB是通过多版本并发控制（MVCC）解决不可重复读问题，加上间隙锁解决幻读问题，因此InnoDB的RR隔离级别实现了串行化级别的效果，而且保留了比较好的并发性能。事务的隔离性是通过锁实现的，而事务的原子性、一致性和持久性则通过事务日志实现的。

32. Repeatable Read隔离级别下MVCC如何工作

select: 第一，InnoDB只查找版本早于当前事务版本的数据行，第二，行的删除版本号要么未定义，要么大于当前事务版本号，这样可以确保事务读取到的行在事务开始之前未被删除。
insert： InnoDB为新插入的每一行保存当前系统版本号作为行版本号。
delete： InnoDB为删除的每一行保存当前系统版本号作为行删除标识。
update： InnoDB为插入的一行新纪录保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为删除标识。使得大多数操作都不用加锁，但是他的不足之处是没行记录都需要额外的存储空间。

33. 什么是全局锁？他的应用场景有哪些？

全局锁就是对整个数据库实例加锁，他的典型应用场景就是全库逻辑备份。这个命令可以使整个库处于只读状态。使用该命令后，数据更新语句、数据定义语句、等操作都会阻塞。

34. 如何设置数据库为全局只读锁

使用命令 flush tables with read lock。

35. InnoDB引擎的行锁是怎么实现的

select * from t where id=666 for update;
for update可以根据条件来完成行锁锁定，并且id是有索引的列，如果id不是索引键那么InnoDB将实行表锁。

36. select for update有什么含义？会锁表还是锁行

select查询语句是不会加锁的，但是select for update 除了有查询的作用外，还会加锁，而且是悲观锁。至于加了是行锁还是表锁，这就要看是不是用了索引/主键。没用索引/主键的话就是表锁，否则就是行锁。

37. 在高并发情况下，如何做到安全的修改同一行数据

要安全的修改同一行数据，就要保证一个线程在修改时其他线程无法更新这条记录。一般有悲观锁和乐观锁两种方案。
使用悲观锁
悲观锁思想是，当前线程要进来修改数据时，别的线程都得拒之门外，比如可以使用select…for update
select * from user where name=“jay” for update
以上这条SQL语句会锁定了User表中所有符合检索条件（name=“jay”）的记录，本次事务提交之前，别的线程都无法修改这些记录。
使用乐观锁
乐观锁思想是，有线程过来，先放过去修改，如果看到别的线程没修改，就可以修改成功，如果别的线程修改过，就修改失败。

38. 如何优化锁

1. 尽量使用较低的隔离级别
2. 精心设计索引，并尽量使用索引访问数据，使加锁更精确。
3. 选择合理的事务大小，小事务发生锁冲突的几率更小。
4. 给记录集加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再申请排他锁，这样容易产生死锁。
5. 除非必须，查询时不要显示加锁。MySQL下的MVCC可以实现事务中的查询不用加锁，优化事务性能。