Java面试题mysql

MySQL 

1、据库三大范式是什么 

⚫  第一范式：每个列都不可以再拆分。 

⚫  第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部 分。 

⚫  第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。  在设计数据库结构的时候，要尽量遵守三范式，如果不遵守，必须有足够的理由。比如性能。事实上我们经常会为了性能而妥协数据库的设计。 

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

MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库里，由mysql_install_db脚本初始化。这些权限表分别user，db，table_priv，columns_priv和host。下面分别介绍一下这些表的结构和内容： 

⚫  user权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。 

⚫  db权限表：记录各个帐号在各个数据库上的操作权限。 

⚫  table_priv权限表：记录数据表级的操作权限。 

⚫  columns_priv权限表：记录数据列级的操作权限。 

⚫  host权限表：配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限 表不受GRANT和REVOKE语句的影响。 

3、MySQL 的 Binlog 有有几种录入格式？分别有什么区别？ 

有三种格式，statement，row和mixed。 

⚫  statement模式下，每一条会修改数据的sql都会记录在binlog中。不需要记录每一行 的变化，减少了binlog日志量，节约了IO，提高性能。由于sql的执行是有上下文的， 因此在保存的时候需要保存相关的信息，同时还有一些使用了函数之类的语句无法被记录 复制。 

⚫  row级别下，不记录sql语句上下文相关信息，仅保存哪条记录被修改。记录单元为每一 行的改动，基本是可以全部记下来但是由于很多操作，会导致大量行的改动(比如alter table)，因此这种模式的文件保存的信息太多，日志量太大。 

⚫  mixed，一种折中的方案，普通操作使用statement记录，当无法使用statement的时 候使用row。 

4、MySQL 存储引擎 MyISAM 与 InnoDB 区别 

⚫  锁粒度方面：由于锁粒度不同，InnoDB比MyISAM支持更高的并发;InnoDB 的锁粒度 为行锁、MyISAM的锁粒度为表锁、行锁需要对每一行进行加锁，所以锁的开销更大，但 是能解决脏读和不可重复读的问题，相对来说也更容易发生死锁 

⚫  可恢复性上：由于InnoDB是有事务日志的，所以在产生由于数据库崩溃等条件后，可以 根据日志文件进行恢复。而MyISAM 则没有事务日志。 

⚫  查询性能上:MylSAM要优于InnoDB因为InnoDB在查询过程中，是需要维护数据缓 存，而且查询过程是先定位到行所在的数据块，然后在从数据块中定位到要查找的行;而  MyISAM 可以直接定位到数据所在的内存地址，可以直接找到数据。 

⚫  表结构文件上:MyISAM的表结构文件包括:frm(表结构定义),.MYI(索引),.MYD(数据);而  InnoDB的表数据文件为:ibd和frm(表结构定义)。 

5、MyISAM 索引与 InnoDB 索引的区别？ 

⚫  InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。 

⚫  InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效。 

⚫  MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。 

⚫  InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆 盖索引会非常高效。 

6、什么是索引？ 

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。 

索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。 

更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。索引是一个文件，它是要占据物理空间的。 

7、索引有哪些优缺点？ 

索引的优点 

⚫  可以大大加快数据的检索速度，这也是创建索引的最主要的原因。 

⚫  通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。 

索引的缺点 

⚫  时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和 修改的时候，索引也要动态的维护，会降低增/改/删的执行效率； 

⚫  空间方面：索引需要占物理空间。 

8、索引有哪几种类型？ 

⚫  主键索引: 数据列不允许重复，不允许为NULL，一个表只能有一个主键。 

⚫  唯一索引: 数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。 

¡  可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引。 ¡  可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一 组合索引。 

⚫  普通索引: 基本的索引类型，没有唯一性的限制，允许为NULL值。 

¡  可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通 索引 
¡  可以通过ALTER TABLE table_name ADD INDEX index_name(column1,  column2, column3);创建组合索引。 

⚫  全文索引： 是目前搜索引擎使用的一种关键技术。 

¡  可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引。 

9、MySQL 中有哪几种锁？ 

⚫  表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度 最低。 

⚫	行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度

也最高。 

⚫  页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之 间，并发度一般。 

10、MySQL 中 InnoDB 支持的四种事务隔离级别名称，以及逐级之间的区别？ 

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

⚫  read uncommited ：读到未提交数据 

⚫  read committed：脏读，不可重复读 

⚫  repeatable read：可重读 

⚫  serializable ：串行事物 

11、CHAR 和 VARCHAR 的区别？ 

⚫  CHAR和VARCHAR类型在存储和检索方面有所不同 

⚫  CHAR列长度固定为创建表时声明的长度，长度值范围是1到255 

⚫  当CHAR值被存储时，它们被用空格填充到特定长度，检索CHAR值时需删除尾随空 格。 

12、主键和候选键有什么区别？ 

表格的每一行都由主键唯一标识,一个表只有一个主键。 

主键也是候选键。按照惯例，候选键可以被指定为主键，并且可以用于任何外键引用。 

13、如何在 Unix 和 MySQL 时间戳之间进行转换？ 

UNIX_TIMESTAMP是从Mysql时间戳转换为Unix时间戳的命令 FROM_UNIXTIME是从Unix时间戳转换为Mysql时间戳的命令。 

14、MyISAM 表类型将在哪里存储，并且还提供其存储格式？ 

每个MyISAM表格以三种格式存储在磁盘上： 

⚫  “.frm”文件 存储表定义 

⚫  数据文件具有“.MYD”（MYData）扩展名 

⚫  索引文件具有“.MYI”（MYIndex）扩展名 

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

NUMERIC和DECIMAL类型被Mysql实现为同样的类型，这在SQL92标准允许。他们被用于保存值，该值的准确精度是极其重要的值，例如与金钱有关的数据。当声明一个类是这些类型之一时，精度和规模的能被(并且通常是)指定。 

例如： 

salary DECIMAL(9,2) 
在这个例子中，9(precision)代表将被用于存储值的总的小数位数，而2(scale)代表将被用于存储小数点后的位数。 

因此，在这种情况下，能被存储在salary列中的值的范围是从-9999999.99到9999999.99。 

16、创建索引时需要注意什么？ 

⚫  非空字段：应该指定列为NOT NULL，除非你想存储NULL。在mysql中，含有空值的 列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。应该 用0、一个特殊的值或者一个空串代替空值； 

⚫  取值离散大的字段：（变量各个取值之间的差异程度）的列放到联合索引的前面，可以通 过count()函数查看字段的差异值，返回值越大说明字段的唯一值越多字段的离散程度 高； 

⚫  索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取 的数据越大效率越高。 

17、使用索引查询一定能提高查询的性能吗？为什么 

通常，通过索引查询数据比全表扫描要快。但是我们也必须注意到它的代价。 

索引需要空间来存储，也需要定期维护， 每当有记录在表中增减或索引列被修改时，索引本身也会被修改。 这意味着每条记录的INSERT，DELETE，UPDATE将为此多付出4，5 次的磁盘I/O。 因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。使用索引查询不一定能提高查询性能，索引范围查询(INDEX RANGE SCAN)适用于两种情况: 

⚫  基于一个范围的检索，一般查询返回结果集小于表中记录数的30% 

⚫  基于非唯一性索引的检索 

18、百万级别或以上的数据如何删除 

关于索引：由于索引需要额外的维护成本，因为索引文件是单独存在的文件,所以当我们对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效率。所以，在我们删除数据库百万级别数据的时候，查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。 

⚫ ⚫ ⚫ ⚫

所以我们想要删除百万数据的时候可以先删除索引（此时大概耗时三分多钟）  然后删除其中无用数据（此过程需要不到两分钟）  删除完成后重新创建索引(此时数据较少了)创建索引也非常快，约十分钟左右。 与之前的直接删除绝对是要快速很多，更别说万一删除中断,一切删除会回滚。那更是坑

了。 

19、什么是最左前缀原则？什么是最左匹配原则 

顾名思义，就是最左优先，在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。 

最左前缀匹配原则，非常重要的原则，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的查询优化器会帮你优化成索引可以识别的形式。 

20、什么是聚簇索引？何时使用聚簇索引与非聚簇索引 

⚫  聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据 

⚫  非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行， myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数 据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不 在key buffer命中时，速度慢的原因。 

21、MySQL 连接器 

首先需要在MySQL客户端登陆才能使用，所以需要 个连接器 来连接用户和MySQL数据库，我们 一般是使用 

mysql-u用户名-p密码 
来进行MySQL登陆，和服务端建立连接。在完成TCP握手后，连接器会根据你输入的用户名和密码验证你的登录身份。如果用户名或者密码错误，MySQL就会提示 Access denied for user，来结束执行。如果登录成功后，MySQL会根据权限表中的记录来判定你的权限。 

22、MySQL 查询缓存 

连接完成后，你就可以执行SQL语句了，这行逻辑就会来到第二步:查询缓存。 

MySQL在得到一个执行请求后，会首先去查询缓存 中查找，是否执行过这条SQL语句，之前执行过的语句以及结果会以key-value对的形式，被直接放在内存中。key是查询语句，value是查询的结果。 

如果通过key能够查找到这条SQL语句，就直接妾返回SQL的执行结果。 

如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果就会被放入查询缓存中。 

可以看到，如果查询命中缓存，MySQL不需要执行后面的复杂操作，就可以直接返回结果，效率会很高。 

23、MySQL 分析器 

如果没有命中查询，就开始执行真正的SQL语句。 

⚫  首先，MySQL会根据你写的SQL语句进行解析，分析器会先做词法分析，你写的SQL就是由多 个字符串和空格组成的一条SQL语句，MySQL需要识别出里面的字符串是什么，代表什么。 

⚫  然后进行语法分析，根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足MySQL语法。如果SQL语句不正确，就会提示 You have an error in your SQL syntax。 

24、MySQL 优化器 

经过分析器的词法分析和语法分析后，你这条SQL就合法了，MySQL就知道你要做什么了。但是在执行前，还需要进行优化器的处理，优化器会判断你使用了哪种索引，使用了何种连接，优化器的作用就是确定效率最高的执行方案。 

25、MySQL执行器 

MySQL通过分析器知道了你的SQL语句是否合法，你想要做什么操作，通过优化器知道了该怎么做效率最高，然后就进入了执行阶段，开始执行这条SQL语句在执行阶段，MySQL首先会判断你有没有执行这条语句的权限，没有权限的话，就会返回没有权限的错误。如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。对于有索引的表，执行的逻辑也差不多。 

26、什么是临时表，何时删除临时表？ 

什么是临时表?MySQL在执行SQL语句的过程中 通常会临时创建一些存储中间结果集的表，临时 表只对当前连接可见，在连接关闭时，临时表会被删除并释放所有表空间。 

临时表分为两种:一种是内存临时表，一种是磁盘临时表，什么区别呢?内存临时表使用的是 MEMORY存储引擎，而临时表采用的是MylSAM 存储引擎。 

MySQL会在下面这几种情况产生临时表。 

⚫	使用UNION查询:UNION有两种，一种是UNION，一种是 UNION ALL，它们都用于联合查询;

区别是使用UNION会去掉两个表中的重复数据，相当于对结果集做了一下 去重(distinct)。使用UNIONALL，则不会排重，返回所有的行。使用UNION查询会产生临时表。 

⚫  使用TEMPTABLE算法或者是UNION查询中的视图。TEMPTABLE算法是一种创建临时表的算 法，它是将结果放置到临时表中，意味这要MySQL要先创建好一个临时表，然后将结果放到临 时表中去，然后再使用这个临时表进行相应的查询。 

⚫  ORDER BY和 GROUPBY的子句不一样时也会产生临时表。 

⚫  DISTINCT 查询并且加上ORDER BY时; 

⚫  SQL中用到SQL_SMALL_RESULT选项时;如果查询结果比较小的时候，可以加上 SQL SMALL  RESULT来优化，产生临时表 

⚫  FROM中的子查询; 

⚫  EXPLAIN 查看执行计划结果的Extra列中，如果使用Using Temporary 就表示会用到临时表。 27、谈谈SQL 优化的经验 

⚫  查询语句无论是使用哪种判断条件等于、小于、大于，WHERE左侧的条件查询字段不要使用函数 或者表达式 

⚫  使用EXPLAIN命令优化你的SELECT查询，对于复杂、效率低的sql语句，我们通常是使用  explainsql来分析这条sql语句，这样方便我们分析，进行优化。 

⚫  当你的SELECT查询语句只需要使用一条记录时，要使用 LIMIT 1。不要直接使用SELECT*，而应 该使用具体需要查询的表字段，因为使用EXPLAIN进行分析时，SELECT"使用的是全表扫描，也 就是type =all 。 

⚫  为每一张表设置一个ID属性。 

⚫  避免在MHERE 字句中对字段进行 NULL

⚫  判断避免在WHERE中使用!或>操作符 

⚫  使用BETWEEN AND 替代 IN 

⚫  为搜索字段创建索引 

⚫  选择正确的存储引擎，InnoDB、MyISAM、MEMORY等 

⚫  使用LIKE%abc%不会走索引，而使用LIKE abc%会走索引。 

⚫  对于枚举类型的字段(即有固定罗列值的字段)，建议使用ENUM 而不是VARCHAR，如性别、星 期、类型、类别等。 

⚫  拆分大的DELETE或INSERT 语句 

⚫  选择合适的字段类型，选择标准是尽可能小、尽可能定长、尽可能使用整数。 

⚫  字段设计尽可能使用NOT NULL 

⚫  进行水平切割或者垂直分割 

28、什么叫外链接？ 

外连接分为三种，分别是是左外连接(LEFT OUTER J0IN 或LEFT JOIN 右外连接(RIGHT OUTER JOIN 或 RIC GHT JOIN、全外连接(FULL OUTER JOIN 或  FULLJOIN)。 

左外连接:又称为左连接，这种连接方式会显示左表不符合条件的数据行，右边不符合条件的数据行直接显示NULL。 

右外连接:也被称为右连接，他与左连接相对，这种连接方式会显示右表不符合条件的数据行，左表不符合条件的数据行直接显示NULL。 

29、什么叫内链接？ 

结合两个表中相同的字段，返回关联字段相符的记录就是内链接。 

30、使用 union 和 union all 时需要注意些什么？ 

通过union连接的SQL分别单独取出的列数必须相同。 

使用union时，多个相等的行将会被合并，由于合升比较耗时，一般不直接使用union 进行合并，而是通常采用union all 进行合并。 

31、MyISAM 存储引擎的特点 

在5.1版本之前，MyISAM是MySQL的默认存储引擎，MylSAM并发性比较差，使用的场景比较少主要特点是: 

⚫  不支持事务操作，ACID的特性也就不存在了，这一设计是为了性能和效率考虑的， 

⚫  不支持外键操作，如果强行增加外键，MySQL不会报错，只不过外键不起作用。 

⚫  MyISAM 默认的锁粒度是表级锁，所以并发性能比较差，加锁比较快，锁冲突比较少，不太容易 发生死锁的情况。 

⚫  MyISAM会在磁盘上存储三个文件，文件名和表名相同，扩展名分别是frm(存储表定义)、 MYD(MYData，存储数据)、MYI(MyIndex，存储索引)。这里需要特别注意的是MyISAM只缓 存 索引文件，并不缓存数据文件。 

⚫  MyISAM支持的索引类型有全局索引(Full-Text)、B-Tree 索引、R-Tree 索引  ¡  Full-Text索引:它的出现是为了解决针对文本的模糊查询效率较低的问题。 

¡  B-Tree索引:所有的索引节点都按照平衡树的数据结构来存储，所有的索引数据节点都在叶节 点 

¡  R-Tree索引:它的存储方式和B-Tree索引有一些区别，主要设计用于存储空间和多维数据的字段做索引目前的MySQL版本仅支持geometry类型的字段作索引，相对于 BTREE,RTREE的优势在于范围查找。 

⚫  数据库所在主机如果宕机，MyISAM的数据文件容易损坏，而且难以恢复。 

⚫  增删改查性能方面:SELECT性能较高，适用于查询较多的情况 

32、InnoDB 存储引擎的特点 

自从MySQL5.1之后，默认的存储引擎变成了InnoDB存储引擎，相对于MylSAM，InnoDB 存储引擎有了较大的改变，它的主要特点是 

⚫  支持事务操作，具有事务ACID隔离特性，默认的隔离级别是可重复读(repetable-read)、通过 MVCC(并发版本控制)来实现的。能够解决 脏读 和 不可重复读 的问题。 InnoDB 支持外键操 作。 

⚫  InnoDB 默认的锁粒度行级锁，并发性能比较好，会发生死锁的情况。 

⚫  和MyISAM一样的是，InnoDB存储引擎也有frm文件存储表结构定义，但是不同的是， InnoDB的表数据与索引数据是存储在一起的，都位于B+数的叶子节点上，而MylSAM的表数 据和索引数据是分开的。 

⚫  InnoDB有安全的日志文件，这个日志文件用于恢复因数据库崩溃或其他情况导致的数据丢失问 题，保证数据的一致性。 

⚫  InnoDB和MylSAM支持的索引类型相同，但具体实现因为文件结构的不同有很大差异。 

⚫  增删改查性能方面，果执行大量的增删改操作，推荐使用InnoDB存储引擎，它在删除操作时是 对行删除，不会重建表。