Java面试——MySQL

一、索引相关

1、什么是索引?
索引是一种数据结构,可以提高数据查询的效率。
1）优点：它类似于书籍的目录，提高数据检索的效率，提高数据排序的效率。
2）缺点：虽然索引大大提高了查询的速度，但会降低DML（数据库操作语言）的速度，对表进行update、insert、delete时，需要对索引文件进行更新

2、索引是个什么样的数据结构呢?
索引的数据结构和具体存储引擎的实现有关, 在MySQL中使用较多的索引有Hash索引,B+树索引等,而我们经常使用的InnoDB存储引擎的默认索引实现为:B+树索引。

3、Hash索引和B+树索引的底层实现原理
hash索引底层就是hash表,进行查找时,调用一次hash函数就可以获取到相应的键值,之后进行回表查询获得实际数据。
B+树底层实现是多路平衡查找树，对于每一次的查询都是从根节点出发,查找到叶子节点方可以获得所查键值,然后根据查询判断是否需要回表查询数据。

3.1、Hash索引和B+树索引有什么区别?
区别:
1）hash索引不支持范围查询、不支持排序；而B+树支持范围查询、支持排序。
因为在hash索引底层实现是哈希函数，索引的顺序与表中顺序不一致，不能支持范围查询和排序。
而B+树底层实现是多路平衡查找树，树的所有节点皆遵循左节点小于父节点,右节点大于父节点，天然支持范围；结果按索引排序。
2）hash索引不支持排序；而B+树支持排序；原因同上。
3）hash索引不支持模糊查询、以及最左前缀匹配，原理也是因为hash函数的不可预测，AAAA和AAAAB的索引没有相关性。
B+树也不支持模糊查询但支持最左前缀匹配。
4）hash索引任何时候都避免不了回表查询数据；而B+树有些情况（比如满足聚簇索引和覆盖索引的时候），可以只通过索引完成查询。

3.2、Hash索引和B+树索引各自的优劣势
hash索引在等值查询时较快，但是性能不稳定；原因是如果字段值存在大量重复、又用了哈希索引的时候，会发生hash碰撞，此时效率可能极差。
B+树的查询效率比较稳定，对于所有的查询都是从根节点到叶子节点，且树的高度较低。
因此，在大多数情况下，直接选择B+树索引可以获得稳定且较好的查询速度，而不需要使用hash索引。

4、上面提到了B+树在满足聚簇索引和覆盖索引的时候不需要回表查询数据，什么是聚簇索引?
聚簇索引就是在B+树的索引中，叶子节点既存储了当前的key值、又存储对应的了整行数据；因此用聚簇索引查询时，不用再回表查询。
在InnoDB中，只有主键索引是聚簇索引；如果没有主键，则数据库自动挑选一个唯一键建立聚簇索引。如果没有唯一键，则隐式的生成一个键来建立聚簇索引。

5、非聚簇索引一定会回表查询吗?
不一定，如果查询的字段全部包含在当前查询命中的索引中，那么就不必再进行回表查询.
举个简单的例子,假设我们在员工表的年龄上建立了索引,那么当进行select age from employee where age < 20的查询时,在索引的叶子节点上,已经包含了age信息,就不会再次进行回表查询。

6、在建立索引的时候,都有哪些需要考虑的因素呢?
1）建立索引的时候一般要考虑到字段的使用频率,经常作为条件进行查询的字段比较适合建立索引；
2）如果需要建立联合索引的话,还需要考虑联合索引中的顺序。
3）此外也要防止过多的索引对表造成太大的压力。

8、创建的索引有没有被使用到?或者说怎么才可以知道这条语句运行很慢的原因?
查看执行计划；
可以用explain plan for语句查询执行计划；也可以通过客户端的“解释”按钮查看执行计划。

10、那么在哪些情况下会发生针对该列创建了索引但是在查询的时候并没有使用呢?
1）使用不等于查询
2）where条件中的字段参与了数学运算或者函数
3）在字符串like时左边是通配符，类似于’%aaa’
4）当mysql分析全表扫描比使用索引快的时候不使用索引.
5）查询结果占整张表数据行比重过大时，不使用索引；比如21万行数据，查询了20万行。

二、事务相关

1、什么是事务?
理解什么是事务最经典的就是转账的例子,相信大家也都了解,这里就不再说一边了；
事务是一系列的操作,他们要符合ACID特性。最常见的理解就是:事务中的操作要么全部成功,要么全部失败，但是只是这样还不够的。

2、ACID是什么?可以详细说一下吗?
ACID是事务的几个特性，它们具体为：
原子性（A=Atomicity）：就是各项操作要么全做，要么全不做，任何一项的失败都会导致整个事务的失败；
一致性（C=Consistency）：事务执行前后，系统（数据库）的状态是一致的；
隔离性（I=Isolation）: 通常来说，并发执行的事务、彼此无法看见对方的中间状态；
持久性（D=Durability）：事务提交完成、做的改动都会被持久化,哪怕系统崩溃也不会影响到这个事务的执行结果。

3、同时有多个事务在进行会怎么样呢?
多事务的并发进行一般会造成以下几个问题：
脏读: A事务读取到了B事务已修改、但未提交的数据，而B事务后面进行了回滚；
不可重复读: A事务同一数据进行了两次读取，获得的结果却不相同，因为B事务在此期间进行了更新；
幻读: A事务对一个范围的数据进行了两次读取，获得的结果却不相同，因为B事务在此期间插入了一条数据。

4、怎么解决这些问题呢?MySQL的事务隔离级别了解吗?
MySQL的四种隔离级别如下:
1）读未提交(READ UNCOMMITTED)
这个级别的性能没有足够大的优势,还有很多的问题,因此很少使用.
2）读已提交(READ COMMITTED)
其他事务只能读取到本事务已经提交的部分.这个隔离级别有不可重复读的问题。
3）可重复读(REPEATABLE READ)
可重复读隔离级别解决了上面不可重复读的问题(看名字也知道),但是仍然有一个新问题,就是幻读
4）可串行(SERIALIZABLE)
这是最高的隔离级别,可以解决上面提到的所有问题,因为他强制将所以的操作串行执行,这会导致并发性能极速下降,因此也不是很常用。

5、Innodb使用的是哪种隔离级别呢?
InnoDB默认使用的是可重复读隔离级别.

6、对MySQL的锁了解吗?
当数据库有并发事务的时候,可能会产生数据的不一致,这时候需要一些机制来保证访问的次序,锁机制就是这样的一个机制.
就像酒店的房间,如果大家随意进出,就会出现多人抢夺同一个房间的情况,而在房间上装上锁,申请到钥匙的人才可以入住并且将房间锁起来,其他人只有等他使用完毕才可以再次使用.

7、MySQL都有哪些锁呢?像上面那样子进行锁定岂不是有点阻碍并发效率了?
从锁的类别上来讲,有共享锁和排他锁.
共享锁: 又叫做读锁. 当用户要进行数据的读取时,对数据加上共享锁.共享锁可以同时加上多个.
排他锁: 又叫做写锁. 当用户要进行数据的写入时,对数据加上排他锁.排他锁只可以加一个,他和其他的排他锁,共享锁都相斥.
用上面的例子来说就是用户的行为有两种,一种是来看房,多个用户一起看房是可以接受的. 一种是真正的入住一晚,在这期间,无论是想入住的还是想看房的都不可以.
锁的粒度取决于具体的存储引擎,InnoDB实现了行级锁,页级锁,表级锁.
他们的加锁开销从大大小,并发能力也是从大到小。

三、表结构设计

1、为什么要尽量设定一个主键?
有了主键，表中的每一行数据就都有了一个唯一标识，方便我们以后对数据进行增删改查；即使业务上本张表没有主键，也建议添加一个自增id作为主键。

2、主键使用自增ID还是UUID?
推荐使用自增ID，不要使用UUID。
首先，mysql的innodb引擎中，表的主键是聚簇索引，其B+树的叶子节点既存储了键值、又存储着相应的数据行数据。这时如果主键是自增ID，那么只需要不断向后排列即可；但如果是UUID，由于新来的ID与原来ID的大小不确定，为了按B+树节点的排列规则（左节点小于父节点，右节点大于父节点）将它们排好，不得不进行非常多的数据插入、数据移动，进而导致产生很多内存碎片，使数据库性能的下降。
所以，在数据量大一些的情况下，使用自增主键性能会更好。

3、字段为什么要求定义为not null?
MySQL官网这样介绍说：null值会占用更多的字节，且会在程序中造成很多与预期不符的情况。

4、如果要存储用户的密码散列,应该使用什么字段进行存储?
密码散列，用户身份证号等固定长度的字符串应该使用char来存储，这样可以节省空间且提高检索效率。

四、存储引擎相关

1、MySQL支持哪些存储引擎?
MySQL支持多种存储引擎,比如InnoDB,MyISAM,Memory,Archive等等.
在大多数的情况下,直接选择使用InnoDB引擎都是最合适的,InnoDB也是MySQL的默认存储引擎.

2、InnoDB和MyISAM有什么区别?

InnoDB支持事物，而MyISAM不支持事物

InnoDB支持行级锁，而MyISAM支持表级锁

InnoDB支持外键，而MyISAM不支持

InnoDB不支持全文索引，而MyISAM支持。

五、零散问题

1.、MySQL中的varchar和char有什么区别.
char是一个定长字段,而varchar是可变长的；从检索效率上来讲,char > varchar。

2、varchar(10)和int(10)代表什么含义?
varchar的10代表了可以存储的数据的最大长度,而int的10只是代表了展示的长度,不足10位以0填充.
也就是说,int(1)和int(10)所能存储的数字大小以及占用的空间都是相同的,只是在展示时按照长度展示.

3、MySQL的binlog有有几种录入格式?分别有什么区别?
有三种格式,statement,row和mixed.
statement模式下,记录单元为语句.即每一个sql造成的影响会记录.由于sql的执行是有上下文的,因此在保存的时候需要保存相关的信息,同时还有一些使用了函数之类的语句无法被记录复制.

row级别下,记录单元为每一行的改动,基本是可以全部记下来但是由于很多操作,会导致大量行的改动(比如alter table),因此这种模式的文件保存的信息太多,日志量太大.

mixed. 一种折中的方案,普通操作使用statement记录,当无法使用statement的时候使用row.
此外,新版的MySQL中对row级别也做了一些优化,当表结构发生变化的时候,会记录语句而不是逐行记录.

4、超大分页怎么处理?
超大的分页一般从两个方向上来解决.
1）数据库层面(虽然收效没那么大，但却是开发人员主要关注的地方)

类似于select * from table where age > 20 limit 1000000,10这种查询其实也是有可以优化的余地的.

这条语句需要load1000000数据然后基本上全部丢弃,只取10条当然比较慢.

我们可以修改为select * from table where id in (select id from table where age > 20 limit 1000000,10)

这样虽然也load了一百万的数据,但是由于索引覆盖,要查询的所有字段都在索引中,所以速度会很快.

同时如果ID连续的话,我们还可以select * from table where id > 1000000 limit 10,效率也是不错的（这样不好，因为即使设置的是自增主键，id也有可能不是连续的，这里只是说道理）。

2）从需求的角度减少这种请求，主要是不做类似的需求——直接跳转到几十万、几百万页之后的具体某一页；只允许逐页查看或者按照给定的路线查，这样可预测、可缓存，以及防止被人恶意攻击。

从效果来看，解决超大分页，其实主要是靠缓存，可预测性的提前查到内容，缓存至redis等k-v数据库中，直接返回结果。

5、关心过业务系统里面的sql耗时吗?统计过慢查询吗?对慢查询都怎么优化过?
在业务系统中,除了使用主键进行的查询,其他的我都会在测试库上测试其耗时,慢查询的统计主要由运维在做,会定期将业务中的慢查询反馈给我们.
慢查询的优化首先要搞明白慢的原因是什么：是load了不需要的数据列? 是查询条件没有命中索引?还是数据量太大?
所以优化也是针对这三个方向来的,
首先分析语句,看看是否load了额外的数据,可能是查询了多余的行并且抛弃掉了,也可能是加载了许多结果中并不需要的列,对语句进行分析以及重写.

分析语句的执行计划,然后获得其使用索引的情况,之后修改语句或者修改索引,使得语句可以尽可能的命中索引.

如果对语句的优化已经无法进行,可以考虑表中的数据量是否太大,如果是的话可以进行横向或者纵向的分表.

6、上面提到横向分表和纵向分表,可以分别举一个适合他们的例子吗?
横向分表是按行分表.假设我们有一张用户表,主键是自增ID且同时是用户的ID.数据量较大,有1亿多条,那么此时放在一张表里的查询效果就不太理想.
我们可以根据主键ID进行分表,无论是按尾号分,或者按ID的区间分都是可以的. 假设按照尾号0-99分为100个表,那么每张表中的数据就仅有100w.这时的查询效率无疑是可以满足要求的.
纵向分表是按列分表.假设我们现在有一张文章表.包含字段id-标题-摘要-内容.而系统中的展示形式是刷新出一个列表,列表中仅包含标题和摘要
当用户点击某篇文章进入详情时才需要正文内容.此时,如果数据量大,将内容这个很大且不经常使用的列放在一起会拖慢原表的查询速度.
我们可以将上面的表分为两张.id-标题-摘要,id-内容.当用户点击详情,那再用主键来取一次内容即可.而增加的存储量只是很小的主键字段.代价很小.
当然,分表和业务的关联度很高,在分表之前一定要做好调研、备份，还有和同事领导沟通，不能按照自己的猜想盲目操作.

7、什么是存储过程？有哪些优缺点？
存储过程是一些预编译的SQL语句。
1）更加直白的理解：存储过程可以说是一个记录集，它是由一些T-SQL语句组成的代码块
这些T-SQL语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。
2）存储过程是一个预编译的代码块，执行效率比较高,一个存储过程替代大量T_SQL语句，可以降低网络通信量，提高通信速率,可以一定程度上确保数据安全
但是,在互联网项目中,其实是不太推荐存储过程的,比较出名的就是阿里的《Java开发手册》中禁止使用存储过程
我个人的理解是,在互联网项目中,迭代太快,项目的生命周期也比较短,人员流动相比于传统的项目也更加频繁
在这样的情况下,存储过程的管理确实是没有那么方便,同时,复用性也没有写在服务层那么好.

8、说一说三个范式
第一范式: 每个列都不可以再拆分.
第二范式: 非主键列完全依赖于主键,而不能是依赖于主键的一部分.
第三范式: 非主键列只依赖于主键,不依赖于其他非主键.
在设计数据库结构的时候,要尽量遵守三范式,如果不遵守,必须有足够的理由.比如性能. 事实上我们经常会为了性能而妥协数据库的设计.

9、MyBatis 中的 # 和 $
#会将传入的内容当做字符串,而$会直接将传入值拼接在sql语句中.
所以#可以在一定程度上预防sql注入攻击.