面试系列：MySQL

以下是我总结一些关于MySQL的面试题，有误请指出。

• 1.delete和truncate的区别？
• 2.B树与B+数的区别
• 3.聚集索引和非聚集索引
• 4.主键索引和辅助索引（次要索引、二级索引）的区别
• 5.覆盖索引
• 6.innodb为什么不建议使用过长的字段作为主键？
• 7.什么情况下需要创建索引？
• 8.哪些情况不需要创建索引？
• 9.索引失效
• 10.explain查看执行计划
• 11.两阶段锁
• 12.主从复制原理
• 13.B+树的分裂
• 14.innodb什么时候用行锁
• 15.一条sql的执行流程
• 16.jdbc(更新)

1.delete和truncate的区别？

相同点：
都可以删除表中所有数据，
使用方法：delete from tableName和truncate table tableName
不同点:
1）删除表中部分数据只能使用delete, delete加上where语句（带上where语句的delete比不带where的语句开销大得多，因为带where的语句需要扫描每一行记录），truncate不可以删除部分数据
2）若要删除所有数据，建议使用truncate,因为truncate是将表结构重建一次，速度比一行一行删除的delete要快得多
3）delete可以返回被删除的记录数，truncate只能返回0

2.B树与B+数的区别

最大区别是非叶子节点是否存储数据，B树非叶子节点存储了数据，B+树非叶子节点不存储数据
B+树索引叶子节点是一个链表，适用于范围查询，
B树查询只要匹配到即可，B+树查询必须找到叶子节点，更稳定

3.聚集索引和非聚集索引

innodb 聚集索引，数据和索引存储在同一个文件
myisam 非聚集索引，数据和索引不存储在同一个文件

4.主键索引和辅助索引（次要索引、二级索引）的区别

1）非聚集索引中 myisam
相同点：两者在结构上没有区别，主键索引和辅助索引中存储的都是数据记录的地址
不同点: 主键索引要求key是唯一的，辅助索引的key可以重复
2）聚集索引中 innodb
主键索引的叶子节点会存储所有数据行
辅助索引只会存储主键值
innodb要是没有主键的话，会寻找值唯一的一列作为主键，要是找不到的话，会隐式创建主键（自动增加一个6字节（48位）的整数列作为主键）

5.覆盖索引

在innodb（聚集索引）中，创建了辅助索引，在查询数据时需要先从辅助索引找到主键，再到主键索引中去查询数据，这是回表操作，要是不需要进行回表操作的索引就是覆盖索引，即再辅助索引上就查找到数据。可以多使用组合索引。
举例说明什么是覆盖索引：
一个user表（id, name，age），使用id作为主键索引。
select * from user where name="xx" 不是覆盖索引，需要进行回表操作
select id, name from user where name="xx" 是覆盖索引，不需要进行回表操作

6.innodb为什么不建议使用过长的字段作为主键？

因为innodb中所有的辅助索引都存储主键，过长的主键会使辅助索引变得过大。

7.什么情况下需要创建索引？

1）主键会自动创建唯一索引
2）频繁查询的字段应该创建索引 (覆盖索引)
3）多表关联查询中，关联字段应该创建索引
4）查询中排序的字段，应该创建索引
5）查询中统计或者分组字段，应该创建索引

8.哪些情况不需要创建索引？

1）表记录太少
2）经常增删改的表
3）频繁更新的表
4)where条件里使用频率不高的字段

9.索引失效

1）组合索引 遇到范围查询失效
2）组合索引 模糊匹配（like）%在前会失效
3) 条件语句中用or 每个字段都得单独有索引才生效，否则失效

10.explain查看执行计划

explain可以用来看sql语句有没有使用索引，有没有做全表扫描等
参数有10列：id、select_type、table、type、possible_keys、key_len、ref、rows、Extra
1）id
select 的id
id相同：执行顺序由上到下
id不同：id越大，先执行
id等于null,表示是一个结果集，不需要使用它来进行查询
2）select_type
查询类型，包括普通查询(simple)、联合查询（union）、子查询等
3）table
查询的表名，使用了别名，就显示别名
尖括号表示的是临时表
4）type
包括all(全表扫描)、index、index_merge、范围查找（range）等
除了all以外，其他type都可以使用索引。除了index_merge之外，其他的type都只可以用到一个索引
5）possible_keys
此次查询中可能选用的索引，一个或多个
6)key
查询真正使用的索引
7）key_len
用到的索引长度。
单列索引，那就计算整个索引长度，组合索引，那就是使用到的索引长度。
8）ref
9）rows
扫描行数
10）extra
额外信息
常用的信息：
using temporary:表示使用了临时表存储中间结果
using index:查询中使用了覆盖索引
using where:表示存储引擎返回的记录并不是所有的都满足查询条件，需要在server层进行过滤

11.两阶段锁

分为加锁和解锁两个过程
加锁过程，只加锁，不释放锁
解锁过程，只释放锁，不加锁

12.主从复制原理

三个线程：binlog线程、I/O线程和SQL线程
binlog线程：负责将主服务器上的数据更改写进二进制日志中
I/O线程：负责将主服务器上读取二进制日志文件，并写入从服务器的中继日志
SQL线程：负责读取中继日志，在从服务器中replay

13.B+树的分裂

每页大小默认为16k

按照原页面中50%的数据量进行分裂，针对当前这个分裂操作，3，4记录保留子啊原有页面，5，6记录移动到新的页面，最后将新记录7插入都新的页面中。
50%分裂策略的优势：
分裂之后两个页面的空间利用率是一样的，如果新的插入时随机在两个页面中挑选进行，那么下一次分裂操作就会更晚触发
50%分裂策略的劣势：
空间利用率不高：按照传统50%的页面分裂策略，索引页面的空间利用率在50%左右
分裂频率较大：没新插入一半的记录，就会导致最右的叶页面再次发生分裂

14.innodb什么时候用行锁

innodb支持行锁，但是是有限制的。
只有在CRUD时匹配的条件字段带有索引，innodb才会使用行锁，否则innodb将使用表锁，因为当匹配的条件不带有索引时，会进行全表扫描，需要将整张表加锁，才能保证查询的正确性。

15.一条sql的执行流程

MySQL的组成包括两个部分： Server端和存储引擎
1）Server
包括连接器、查询缓存、分析器、优化器和执行器等，存储过程、视图、sql优化都是在这完成的
连接器
用来使客户端连接上mysql服务器,连接时需要ip、端口、用户名和密码等信息
这是一个长连接，直到连接关闭，若是长连接过多，可能导致占用太多内存，所以最好定期断开长连接
查询缓存：查询数据先从缓存去取，建议不要使用，因为任意一个表的记录更新都会使缓存失效，缓存命中率低。mysql8.0彻底抛弃了查询缓存。
缓存可以不使用，query_cache_type参数可以来设置，值为0,1,2。
0（off）表示不使用缓存，1（on）开启缓存，但当select语句中使用sql_no_cache时，不使用缓存，2（Demand）开启缓存，但只有select使用sql_cache时才能使用缓存
分析器
分析sql的操作，是查询呐还是更新，或判断sql是否有语法错误
优化器
可以优化查询方式等，
执行器
调用存储引擎去读取表中数据
2）存储引擎
负责数据的存取，包括有innodb、myisam等，innodb从mysql5.5.5之后就是默认存储引擎了，可以通过在create table语句中使用engine=myisam指定存储引擎

16.jdbc（更新）

jdbc与dbms的比较：

dbms是数据库管理系统，比如MySQL、SQL Server等，主要是用来管理SQL（增删改查），其主要的一些组件参考15 MySQL的组成。

jdbc是一个标准接口，只要用户实现这个接口就可以 不借助dbms就可以与数据库交互；也就是使用java等语言进行与数据库交互的话，需要借助jdbc实现。