Java MySQL数据库面试题（二）

---

前言

最新的 Java 面试题，技术栈涉及 Java 基础、集合、多线程、Mysql、分布式、Spring全家桶、MyBatis、Dubbo、缓存、消息队列、Linux…等等，会持续更新。

如果对老铁有帮助，帮忙免费点个赞，谢谢你的发财手！

1、说下Innodb和 myisam的区别吗？

Innodb引擎：

Innodb是基于聚簇索引建立的，支持事务、外键，并且通过MVCC来支持高并发，索引和数据存储在一起。
InnoDB将一张表的结构和内容分为两个文件：
1、表结构文件，后缀名为 .frm
2、数据、索引在一个文件，后缀名为 .ibd

myisam引擎：

1）支持全文检索、压缩、空间函数等，但是不支持事务和行级锁，所以一般用于有大量查询少量插入的场景来使用；
2）不支持外键，并且索引和数据是分开存储的。
MyISAM将一张表的结构和内容分为三个文件：

• 1、表结构文件，后缀名为 .frm
• 2、索引文件，后缀名为 .MYI（MYIndex）
• 3、数据文件，后缀名为 .MYD（MYData）

2、MySQL 如何优化 DISTINCT？

MysqL把distinct优化为group by,在需要去重的列上创建索引,然后扫描索引, 可以快速定位数据。

3、如何查询表第1000到1020条记录？

• 1）SELECT * FROM sys_user LIMIT 1000,20;
• 2）SELECT * FROM sys_user WHERE id > 999 LIMIT 20;

4、可以使用多少列创建索引？

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

5、NOW（）和 CURRENT_DATE（）有什么区别？

• NOW（）命令用于显示当前年份，月份，日期，小时，分钟和秒。
• CURRENT_DATE（）仅显示当前年份，月份和日期。

6、什么是非标准字符串类型，以及长度？

• 1、TINYTEXT： 2^8；
• 2、TEXT: 2^16；
• 3、MEDIUMTEXT: 2^24；
• 4、LONGTEXT: 2^32。

7、什么是通用 SQL 函数？

• 1、CONCAT(A, B)：通常用于将两个或多个字段拼接在一起；
• 2、CONCAT_WS(‘-’, A, B)：通常用于将两个或多个字段拼接在一起，但可以指定一个分隔符；
• 3、FORMAT(N, D)：可以将数据格式化为整数或者带几位小数（四舍五入）；
• 4、CURRDATE()/CURRTIME()：返回当前日期/时间；NOW()：返回当前日期+时间；
• 5、YEAR/MONTH/DAY/WEEK/WEEKDAY（date）：从日期值中提取给定数据；
• 6、HOUR/MINUTE/SECOND（date）：从时间值中提取给定数据;
• 7、DATEDIFF（date1，date2）：确定计算两个日期之间的天数；
• 8、SUBTIMES（date1，date2）：用于计算两个时间的差值。

8、MySQL 里记录货币用什么字段类型好？

NUMERIC(a，b)和 DECIMAL(a，b)
都有两个参数，前面一个参数为总的位数，后面一个参数是小数点后的位数。

9、MySQL 有关权限的表都有哪几个？

MySQL通过权限表来控制用户对数据库的访问，权限表存放在 MySQL数据库里，由 MySQL_install_db 脚本初始化。
这些权限表分别 user，db，table_priv， columns_priv 和 host。

10、MySQL 数据库一天五万条以上的增量，预计运维三年,怎么优化？

• 1、设计良好的数据库结构，允许部分数据冗余，尽量避免 join 查询，提高效率。
• 2、选择合适的表字段数据类型和存储引擎，适当的添加索引。
• 3、MySQL 库主从读写分离。
• 4、添加缓存机制，比如 memcached，apc 等。
• 5、不经常改动的页面，生成静态页面。

11、索引的目的是什么？

• 1、可以提高查询速度；
• 2、创建唯一性索引，保证数据库表中每一行数据的唯一性；
• 3、可以减少查询中分组和排序的时间。

12、索引对数据库系统的负面影响是什么？

• 1、创建索引和维护索引需要耗费性能，随着数据量的增加而增加；
• 2、索引需要占用物理空间；
• 3、对表数据进行增/删/改操作时，索引也要动态维护，降低了表数据的维护速度。

13、为数据表建立索引的原则有哪些？

• 1、在频繁使用的、可以缩小查询范围的字段上建立索引；
• 2、在频繁使用的、需要排序的字段上建立索引。

14、什么情况下不宜建立索引？

• 1、对于查询中很少涉及的列或者重复值比较多的列，不宜建立索引；
• 2、对于一些特殊的数据类型，不宜建立索引，比如文本字段（text）等。

15、Myql 中的事务回滚机制概述：

• 事务是用户定义的一个数据库操作序列，这些操作要么全做要么全不做，是一个不可分割的工作单位，事务回滚是指将该事务已经完成的对数据库的更新操作撤销。
• 要同时修改数据库中两个不同表时，如果它们不是一个事务的话，当第一个表修改完，可能第二个表修改过程中出现了异常而没能修改，此时就只有第二个表依旧是未修改之前的状态，而第一个表已经被修改完毕。而当你把它们设定为一个事务的时候，当第一个表修改完，第二表修改出现异常而没能修改，第一个表和第二个表都要回到未修改的状态，这就是所谓的事务回滚

16、什么是存储过程？用什么来调用？

• 存储过程是一个预编译的 SQL 语句，就是说只需要创建一次，可以调用多次。
• 如果某次操作需要执行多次 SQL，使用存储过程比单纯 SQL 语句执行要快很多。
• 可以用一个命令对象来调用存储过程。

CREATE PROCEDURE <过程名> BEGIN <SELECT语句> END;

17、什么是基本表？什么是视图？

• 基本表：是指mysql中一个个独立的表。
• 视图：是指一个或几个基本表导出的表，视图本身不独立存储在数据库中，是一个虚表。
  视图能够简化用户的操作，保证数据库的安全。

CREATE VIEW <视图名> AS <SELECT语句>;

18、MYSQL二阶段提交？

执行流程：

• 1、mysql执行器想要更新记录A，此时开启事务，然后InnoDB引擎会把记录A加载到缓冲池buffer poll；
• 2、把记录A的旧值写入undo log，便于回滚；
• 3、执行器更新内存中的数据，同时把数据写入redo log中，此时redo log处于prepare准备状态，到这里是一阶段提交；
• 4、然后执行器把所有操作记录都写入到bin log中，然后提交事务，同时把redo log状态改成commit提交状态，到这里，mysql二阶段提交就算完成了。
  （binlog：主从复制、数据恢复、增量备份；undo log：数据回滚；redo log：事务持久化）

19、什么是索引覆盖、回表、索引下推？

• 索引覆盖：指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取；
  回表：主键索引树的叶子节点直接就是我们要查询的整行数据，而非主键索引的叶子节点是主键的值，查到主键的值以后，还需要再通过主键的值再进行一次查询。
• 索引下推(MySQL 5.6支持)：
  应用点：针对非主键索引上的优化。
  如果没有索引下推优化（或称ICP优化），当进行索引查询时，首先根据索引来查找记录，然后再根据where条件来过滤记录；在支持ICP优化后，MySQL会在取出索引的同时，判断是否可以进行where条件过滤再进行索引查询，这样可以减少回表次数，从而提升整体性能。

20、MySQL 中 InnoDB 支持的四种事务隔离级别名称？

SQL 标准定义的四个隔离级别为：

• 1、read uncommited ：读未提交；
• 2、read committed：读已提交；
• 3、repeatable read：可重复读；
• 4、serializable ：串行事物。

21、mysql语句执行顺序是什么样的？

from -> join -> on -> where -> group by -> avg,sum,max,min,count -> having -> select -> distinct -> order by -> limit

22、mysql语句底层执行过程？

Mysql整体分为三部分，客户端Client、服务端Server层和存储引擎层：

• 1.连接器：权限校验用户名与密码；
• 2.查询缓存：命中则直接返回结果；
• 3.分析器：进行词法和语法分析，比如select 表示查询语句；
• 4.优化器：比如在表里面有多个索引时，选择效率最高的索引，或者多表关联时，优化各个表的连接顺序；
• 5.执行器：操作存储引擎层，去寻找符合条件的数据，并且返回给客户端。

23、你们mysql用了集群吗？

没有，我们用的主从同步，master/slave，主库开启binlog，采用row行同步方式，主库一个线程去写入binlog里；从库一个线程从主库的binlog同步到本地的中继日志（relay log），然后再用一个线程从中继日志同步数据到本地数据库。

24、联合索引abc怎么生效，ac 或者ab生效吗？

• 生效：where/order 单独使用和顺序无关（mysql会自动优化），一起使用要保证abc顺序；
• 不生效：不满足最左原则或用使用了大于小于，后面的索引列不生效。

25、mysql里的索引操作语句？

• 查看索引：

SHOW INDEX FROM 表名;

1、添加索引：

ALTER TABLE 表名 ADD INDEX 索引名 (列名);**或者** CREATE INDEX 索引名 ON 表名 (列名);

1、删除索引：

ALTER TABLE 表名 DROP INDEX 索引名; **或者** DROP INDEX 索引名 ON 表名 (列名);

26、char(30)和varchar(30)的区别？

• varchar(30)：保存实际字符长度，最大为30；
• char(30）：不管你保存的字符数是多少，它都固定占用30个字节的存储空间；
• char(30）占用空间更多，但是因为长度固定，运算时速度更快。
  使用modify column改为varchar会产生内存碎片。

27、索引的建议？

• 1、在数据量大的情况下能够极大地提高查询效率，但创建索引是有代价的，它会增加磁盘空间的使用，并且在插入、更新和删除数据时，可能会降低性能，因为索引也需要维护的。
• 2、创建索引的建议：
  1）不要在数据变化很频繁的列上创建索引。
  2）对于查询中经常出现的列或条件，应考虑创建索引。
  3）复合索引应根据查询的WHERE子句和JOIN的顺序来确定列的顺序。
  4）索引的选择性（不重复值的数量与总行数的比例）越高，索引的效果越好。
  5）尽量使用覆盖索引（查询列直接在索引中），这样可以避免回表查询提高效率。

总结

都已经看到这里啦，赶紧收藏起来，祝您工作顺心，生活愉快！