Java 面试（二）| 详细的MySql面试部分

一、MySql面试-索引篇

1.索引的优点

为什么要创建索引？这是因为，创建索引可以大大提高系统的查询性能。

• 第一、通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
• 第二、可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
• 第三、可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
• 第四、在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的 时间。
• 第五、通过使用索引，可以在查询的过程中，使用查询优化器，提高系统的性能。

2.索引的缺点

• 第一、创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
• 第二、索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物 理空间。如果要建立聚簇索引，那么需要的空间就会更大。
• 第三、当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降 低了数据的维护速度。

捷豹

3.什么样的字段适合创建索引

索引是建立在数据库表中的某些列的上面。因此，在创建索引的时候，应该仔细考虑 在哪些列上可以创建索引，在哪些列上不能创建索引。 一般来说，应该在具备下述特性的 列上创建索引：

• 第一、在经常需要搜索的列上，可以加快搜索的速度；
• 第二、在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
• 第三、在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
• 第四、在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范 围是连续的；
• 第五、在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的 排序，加快排序查询时间；
• 第六、在经常使用在 WHERE 子句中的列上面创建索引，加快条件的判断速度。

建立索引，一般按照 select 的 where 条件来建立，比如： select 的条件是 where f1 and f2，那么如果我们在字段 f1 或字段 f2 上建立索引是没有用的，只有在字段 f1 和 f2 上同时 建立索引才有用等。

4.什么样的字段不需要创建索引

• 第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些 列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反 而降低了系统的维护速度和增大了空间需求。
• 第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值 很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例， 即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
• 第三，对于那些定义为 text, image 和 bit 数据类型的列不应该增加索引。这是因为，这 些列的数据量要么相当大，要么取值很少。
• 第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索 性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，
  会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

5.索引的分类

单列索引

• 普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插⼊重复值和空值，
  纯粹为了查询数据更快⼀点。 add index
• 唯⼀索引：索引列中的值必须是唯⼀的，但是允许为空值. add unique index
• 主键索引：是⼀种特殊的唯⼀索引，不允许有空值。 pk

组合索引

在表中的多个字段组合上创建的索引 add index(col1,col2…)
组合索引的使⽤，需要遵循最左前缀原则。
⼀般情况下，建议使⽤组合索引代替单列索引（主键索引除外，具体原因后⾯知识点讲解）。

上面两种索引属于B-Tree索引大类型

Hash索引

哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。如果多个行的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中。

Memory存储引擎支持Hash索引

全⽂索引

• 只有在MyISAM引擎、InnoDB（5.6以后）上才能使⽤，⽽且只能在CHAR,VARCHAR,TEXT类型字段上
  使⽤全⽂索引。 fulltext
• 优先级最⾼ 先执⾏ 不会执⾏其他索引
• 存储引擎 决定执⾏⼀个索引

空间索引

不做介绍，⼀般使⽤不到。

6.创建索引语句

• 单列索引之普通索引

CREATE INDEX index_name ON table(column(length)) ;
ALTER TABLE table_name ADD INDEX index_name (column(length)) ;

• 单列索引之唯一索引

CREATE UNIQUE INDEX index_name ON table(column(length)) ;
alter table table_name add unique index index_name(column);

• 单列索引之全文索引

CREATE FULLTEXT INDEX index_name ON table(column(length)) ;
alter table table_name add fulltext index_name(column)

• 组合索引

ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10)) ;

7.删除索引

DROP INDEX index_name ON table

8.查看索引

SHOW INDEX FROM table_name \G

9.索引原理分析

索引的存储结构

• 索引是在存储引擎中实现的，所以不同的存储引擎使用不同的索引
• MyISAM和InnoDB存储引擎只支持B+Tree索引
• MEMORY/HEAP存储引擎支持Hash和BTree索引

B树和B+树

详情参考：https://blog.csdn.net/hao65103940/article/details/89032538

• B-Tree图示
  

• B+Tree图示
  

B+Tree和B-Tree的不同

• 所有叶子节点之间都有一个链指针。
• 非叶子节点只存储键值信息。数据记录都存放在叶子节点中。

为什么选用B+Tree来做索引的存储结构

• 索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数
• B-Tree结构的数据可以让系统高效的找到数据所在的磁盘块
• B-Tree相对于AVLTree缩减了节点个数，减少了磁盘IO存取次数
• B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，它和B-Tree最大的区别就是非叶子节点不存储数据，只存储键值，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

10.聚集索引和非聚集索引

聚集索引和非聚集索引是运用B-Tree作为数据结构索引的两种不同实现形式，实现在MyISAN和InnoDB两个存储引擎上

详细参考：http://blog.codinglabs.org/articles/theory-of-mysql-index.html

非聚集索引（MyISAM）

• B+树叶⼦节点只会存储数据⾏（数据⽂件）的指针，简单来说数据和索引不在⼀起，就是⾮聚集
  索引。
• ⾮聚集索引包含主键索引和辅助索引都会存储指针的值

主键索引

这里设表一共有三列，假设我们以Col1为主键，则上图是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。

辅助索引

在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主键索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

聚集索引（InnoDB）

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如下图在Col3上建立一个辅助索引：

聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。例如：

select * from user where name='Alice' # 回表查询 检索两次 ⾮主键索引 --- pk---索引--->数据
select id,name from user where name='Alice' # 不需要回表 在辅助索引树上就可以查询到了 覆盖索引

知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

聚簇索引的优点：

• 可以把相关数据保存在一起，最大限度地提高了I/O密集型应用的性能。
• 聚簇索引将索引和数据保存在同一个B-Tree中，因此数据访问更快。
• 使用覆盖索引扫描的查询可以直接使用叶节点中的主键值。

聚簇索引的缺点：

• 插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。
• 更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置。
• 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临“页分裂”的问题。
• 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。

11.为什么要使用组合索引

最左前缀原则

ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3'）

以上创建的组合索引相当于建立了3个索引：

• col1
• col1、col2
• col1、col2、col3

即只从最左边的字段开始组合

比如当查询条件为col2、col3时，就不用到上面创建的索引

原因

为了节省mysql索引存储空间以及提升搜索性能，可建⽴组合索引（能使⽤组合索引就不使⽤单列索引）

⼀颗索引树上创建3个索引比三颗索引树上分别创建1个索引更省空间

注意

从左向右匹配直到遇到范围查询 > < between like也会使索引匹配终止

比如：建⽴组合索引 (a,b,c,d)，然后查询
where a=1 and b=1 and c>3 and d=1
此时到c>3停⽌了 所以d⽤不到索引了

解决：建立（a,b,d,c）的组合索引

12.覆盖索引

• 如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”，索引不会覆盖所有的列。
• 覆盖索引必须要存储索引列的值，因此MySQL只能使用B-Tree索引做覆盖索引，而且MySQL只能在索引中执行最左前缀匹配的LIKE比较，而不能执行以通配符开头的LIKE查询。

13.查看执行计划

MySQL 提供了⼀个 EXPLAIN 命令, 它可以对 SELECT 语句的执⾏计划进⾏分析, 并输出 SELECT 执⾏的
详细信息, 以供开发⼈员针对性优化.

EXPLAIN 命令⽤法⼗分简单, 在 SELECT 语句前加上 explain 就可以了, 例如:

字段的详细信息解释这里略

14.索引失效的情况

• 隐式类型转换，常见情况是在 SQL 的 WHERE 条件中字段类型为字符串，其值为数值，如果没有加引号那么 MySQL 不会使用索引。
• 如果条件中 OR 只有部分列使用了索引，索引会失效。
• 执行 LIKE 操作时，最左匹配会被转换为比较操作，但如果以通配符开头，存储引擎就无法做比较，。索引失效
• 如果查询中的列不是独立的，则 MySQL 不会使用索引。独立的列是指索引列不能是表达式的一部分，也不能是函数的参数。
• 对于多个范围条件查询，MySQL 无法使用第一个范围列后面的其他索引列，对于多个等值查询则没有这种限制。
• 如果 MySQL 判断全表扫描比使用索引查询更快，则不会使用索引。

二、MySql面试-存储引擎篇

1.MyISAM和InnoDB引擎的区别

• InnoDB支持事务，MyISAM不支持事务
• InnoDB支持外键，MyISAM不支持外键
• InnoDB是聚集索引，对于利用主键查询效率高；MyISAM是非聚集索引，索引保存的是数据文件的指针
• InnoDB不保存整个表的具体行数，执行select count(*)会全表扫描；MyISAM用一个变量保存了整个表的行数，执行上述语句只需要读出该变量的值即可，速度很快
• InnoDB拥有崩溃恢复能力；MyISAM崩溃后无法恢复
• InnoDB支持行锁，提高了高并发访问的性能；MyISAM只支持表锁，插入和更新时会锁住整个表，效率低

2.MyISAM是否一无是处

• 从查询的角度来说，InnoDB没有MyISAM的单条查询速度高，MyISAM采用Hash存储回行得到数据的查询过程，单MyISAM对于范围查询不是很友好。
• MyISAM对于表内的总数查询，维护了单独的数据
• 对于只读或表比较小，可以忍受系统崩溃导致数据无法恢复的问题，可以使用MyISAM

三、MySql面试-基础和优化篇

1.查询优化方法

• 避免全表扫描：考虑在 WHERE 和 ORDER BY 涉及的列上建立索引，IN 和 NOT IN 也要慎用，尽量用 BETWEEN 取代。
• 优化 COUNT：某些业务不要求完全精确的 COUNT 值，此时可以使用近似值来代替，EXPLAIN 估算的行数就是一个不错的近似值。
• 避免子查询：在 MySQL5.5 及以下版本避免子查询，因为执行器会先执行外部的 SQL 再执行内部的 SQL，可以用关联查询代替。
• 禁止排序：当查询使用 GROUP BY 时，结果集默认会按照分组字段排序，如果不关心顺序，可以使用 ORDER BY NULL 禁止排序。
• 优化分页：从上一次取数据的位置开始扫描，避免使用 OFFSET。
• 优化 UNION：MySQL 通过创建并填充临时表的方式来执行 UNION 查询，除非确实需要消除重复的行，否则使用 UNION ALL，如果没有 ALL 关键字，MySQL 会给临时表加上 DISTINCT 选项，对整个临时表的数据做唯一性检查，代价非常高。
• 使用用户自定义变量：用户自定义变量是一个用来存储内容的临时容器，在连接 MySQL 的整个过程中都存在，可以在任何可以使用表达式的地方使用自定义变量，避免重复查询刚刚更新过的数据。

2.delete、drop、truncate的区别？

• delete 可以删除部分数据也可以删除全部数据，和truncate 一样只删除数据而不删除表的结构；drop会删除表的结构。
• delete 是 DML 操作，可以进行回滚；drop 和 truncate 是 DDL，不能进行回滚。
• 速度来说，一般 drop > truncate > delete。

3.MySQL 有哪些聚合函数？

① max 求最大值。② min 求最小值。③ count 统计数量。④ avg 求平均值。⑤ sum 求和。

4.varchar(100)和varchar(200)的区别

占用内存空间大小肯定是不一致的，但是占用我们磁盘的大小是一致的，我们存储字符串"abc"，完全是一样的磁盘空间，但是对于varchar(100)来说，接收到的字符串长度太长了就会报错的。后面的数字代表可存储的字节数。

四、MySql面试-事务和锁篇

1.介绍下MySql中的锁

按照锁的粒度

• 全局锁:锁的是整个database。由MySQL的SQL layer层实现的
• 表级锁:锁的是某个table。由MySQL的SQL layer层实现的
• 行级锁:锁的是某⾏数据，也可能锁定⾏之间的间隙。由某些存储引擎实现，⽐如InnoDB。

表级锁和行级锁的区别:

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

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

按照锁的功能

• 共享读锁
• 排他写锁

按照锁的实现方式

• 悲观锁
• 乐观锁

2.元数据锁

• MDL不需要显式使⽤，在访问⼀个表的时候会被⾃动加上。MDL的作⽤是，保证读写的正确性。你可
  以想象⼀下，如果⼀个查询正在遍历⼀个表中的数据，⽽执⾏期间另⼀个线程对这个表结构做变更，删
  了⼀列，那么查询线程拿到的结果跟表结构对不上，肯定是不⾏的
• 在 MySQL 5.5 版本中引⼊了 MDL，当对⼀个表做增删改查操作的时候，加 MDL 读锁；当要对
  表做结构变更操作的时候，加 MDL 写锁。

读锁之间不互斥，因此你可以有多个线程同时对⼀张表增删改查。

读写锁之间、写锁之间是互斥的，⽤来保证变更表结构操作的安全性。因此，如果有两个线程要同
时给⼀个表加字段，其中⼀个要等另⼀个执⾏完才能开始执⾏。

3.行级锁

MySQL的⾏级锁，是由存储引擎来实现的，利⽤存储引擎锁住索引项来实现的。这⾥我们主要讲解
InnoDB的⾏级锁。

InnoDB的⾏级锁，按照锁定范围来说，分为三种：

• 记录锁（Record Locks）:锁定索引中⼀条记录。 id=1
• 间隙锁（Gap Locks）:要么锁住索引记录中间的值，要么锁住第⼀个索引记录前⾯的值或者最后⼀个索
  引记录后⾯的值。
• Next-Key Locks:是索引记录上的记录锁和在索引记录之前的间隙锁的组合。

InnoDB的⾏级锁，按照功能来说，分为两种：

• 共享锁（S）：允许⼀个事务去读⼀⾏，阻⽌其他事务获得相同数据集的排他锁。
• 排他锁（X）：允许获得排他锁的事务更新数据，阻⽌其他事务取得相同数据集的共享读锁（不是读）
  和排他写锁。

对于UPDATE、DELETE和INSERT语句，InnoDB会⾃动给涉及数据集加排他锁（X)；

对于普通SELECT语句，InnoDB不会加任何锁，事务可以通过以下语句显示给记录集加共享锁或排他
锁。

• 手动添加共享锁

SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE

• 手动添加排他锁

SELECT * FROM table_name WHERE ... FOR UPDATE

InnoDB⾏锁是通过给索引上的索引项加锁来实现的，因此InnoDB这种⾏锁实现特点意味着：只
有通过索引条件检索的数据，InnoDB才使⽤⾏级锁，否则，InnoDB将使⽤表锁！

间隙锁

锁加在不存在的空闲空间，可以是两个索引记录之间，也可能是第一个索引记录之前或最后一个索引之后的空间。

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

间隙锁在InnoDB的唯一作用就是防止其它事务的插入操作，以此来达到防止幻读的发生，所以间隙锁不分什么共享锁与排它锁。如果InnoDB扫描的是一个主键、或是一个唯一索引的话，那InnoDB只会采用行锁方式来加锁，而不会使用Next-Key Lock的方式，也就是说不会对索引之间的间隙加锁，

很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件；当然，对一条不存在的记录加锁，也会有间隙锁的问题

4.MySql中的事务ACID

事务是一组原子性的 SQL 语句，当有任何一条语句因崩溃或其他原因无法执行时，所有语句都不会执行。事务内的语句要么全部执行成功，要么全部执行失败。

• 原子性：一个事务在逻辑上是必须不可分割的最小单元，整个事务中的所有操作要么全部成功，要么全部失败。
• 一致性：数据库总是从一个一致性的状态转换到另一个一致性的状态。
• 隔离性：针对并发事务而言，要隔离并发运行的多个事务之间的影响，数据库提供了多种隔离级别。
• 持久性：一旦事务提交成功，其修改就会永久保存到数据库中，此时即使系统崩溃，修改的数据也不会丢失。

5.事务的隔离级别

• 未提交读：事务中的修改即使没有提交，对其他事务也是可见的。事务可以读取其他事务修改完但未提交的数据，这种问题称为脏读。这个级别还存在不可重复读和幻读，很少使用。
• 读已提交：多数数据库的默认隔离级别，事务只能看见已提交事务的修改。存在不可重复读，两次执行同样的查询可能会得到不同结果。
• 可重复读（MySQL默认的隔离级别）：解决了不可重复读，保证同一个事务中多次读取同样的记录结果一致，InnoDB 通过 MVCC 解决。但无法解决幻读，幻读指当某个事务在读取某个范围内的记录时，会产生幻行。
• 可串行化：最高隔离级别，通过强制事务串行执行避免幻读。在读取的每一行数据上都加锁，可能导致大量的超时和锁争用的问题。实际很少使用，只有非常需要确保数据一致性时考虑。

不可重复读和幻读的区别是：前者是指读到了已经提交的事务的更改数据(修改或删除)，后者是指读到了其他已经提交事务的新增数据。

对于这两种问题解决采用不同的办法，防止读到更改数据，只需对操作的数据添加行级锁，防止操作中的数据发生变化;二防止读到新增数据，往往需要添加表级锁，将整张表锁定，防止新增数据(oracle采用多版本数据的方式实现，mysql可采用间隙锁防止幻读)。