Mysql面试

1.什么事内连接，外连接，交叉连接，笛卡尔积

• 内连接（inner join）：取得两张表中满足存在连接匹配关系的记录。
• 外连接（outerjoin）：不只取得两张表中满足存在连接匹配关系的记录，还包括某张表（或两张表）中不满足匹配关系的记录。
• 交叉连接（crossjoin）：显示两张表所有记录一一对应，没有匹配关系进行筛选，它是笛卡尔积在SQL中的实现，如果A表有m行，B表有n行，那么A和B交叉连接的结果就有m*n行。
• 笛卡尔积：是数学中的一个概念，例如集合 A={a,b}，集合 B={1,2,3}，那么 A B={<a,o>,<a,1>,<a,2>,
  <b,0>,<b,1>,<b,2>,}

2.MySQL 中 in和 exists 的区别

MySQL 中的 in 语句是把外表和内表作hash 连接，而exists 语句是对外表作loop 循环，每次loop 循环再对内表进行
查询。我们可能认为exists 比 in 语句的效率要高，这种说法其实是不准确的，要区分情景：

1. 如果查询的两个表大小相当，那么用in 和 exists 差别不大。
2. 如果两个表中一个较小，一个是大表，则子查询表大的用 exists，子查询表小的用 in。
3. not in 和 not exists：如果查询语句使用了not in，那么内外表都进行全表扫描，没有用到索引；而not extsts
   的子查询依然能用到表上的索引。所以无论那个表大，用not exists 都比 not in 要快

3.一条SQL查询语句在MySQL中如何执行的

1. 先检查该语句是否有权限，如果没有权限直接返回错误信息。
2. 如果没有缓存，分析器进行语法分析
3. 语法解析后，Mysql服务器会对查询的语句进行优化，确定执行方案。
4. 完成查询优化后，按照生成的执行计划调用数据库引擎接口，返回执行结果。

4.慢sql的优化

1. 避免不必要的列
2. 分页优化
3. 索引优化：避免列上函数运算，正确使用联合索引
4. Join优化：小表驱动大表，避免Join太多表
5. UNIOIN优化：条件下推最好是将where，limit等子句下推到各个子查询中，以便优化器可以充分利用这些条件进行优化

5.如何了解explain的各个字段值的含义

6. id：每个select的分配的唯一值id
7. select_type：查询的类型，可以分为SIMPLE,PRIMARY
8. table列：表示explain的一行正在访问那个表。
9. type：最重要的列，表示关联类型或者访问类型。性能从最优到最差，system 》const》eq_ref 》ref》fulltext 》ref_or_null >index_merge >unique_subquery >index_subquery >range >index >ALL
10. possible_keys:显示查询可能使用那些索引来查找
11. key：这一列显示mysql实际采用那个索引来优化对该表的访问。
12. key——len：显示了该表的使用
13. ref列：展示的就是索引列等值匹配
14. rows列：扫描器读取的行数，原则越少越好
15. 10.Extra：显示不适合在其他列显示的信息。

6.索引的分类

三个维度分类

7. 为什么使用索引会加快查询

在我们添加完索引后，Mysql一般通过BTREE算法生成一个索引文件，在查询数据库的时候，找到索引文件进行遍历，在较小的索引数据里面查询，然后映射到对应的数据，能大幅度提升查找的效率。

8.索引建立的注意点

1. 索引应该建立在查询应用频繁的字段
2. 索引的个量应该适量
3. 区分度低的字段，如性别就不需要建立索引
4. 更新频繁的值，就不要作为主键或者索引
5. 组合索引把区分度高的值放到前面
6. 创建组合索引，而不是修改单列索引
7. 过长的字段，使用前缀索引。当字段值比较长的时候，建立索引会消耗很多的空间，搜索起来也会很慢。我们可以通过截取字段的前面一部分内容建立索引，这个就叫前缀索引
8. 不建议用无序的值(例如身份证、UUID )作为索引，当主键具有不确定性，会造成叶子节点频繁分裂，出现磁盘存储的碎片化

9.索引那些情况会失效

• 查询条件包含or，可能导致索引失效
• 如果字段类型是字符串，where一定要用引号括起来，否则可能会因为饮食类型转换，索引失效
• like通配符可能会导致失效
• 联合索引，查询的条件不是索引的一个列，可能会失效
• 在索引上使用内置函数，可能会导致失效
• 在索引列上进行运算，可能会导致失效
• 索引字段上使用（！=或者<>，notin）时，可能会导致索引失效。
• 索引字段使用is null，is not null 等可能会导致索引失效
• 左连接和右连接查询关联的字段的编码格式不同，可能会导致索引失效。
• Mysql的优化器估计使用全表扫描比使用索引快，则不使用索引。

10 索引不适合那些场景

• 数据量比较小的表不适合加索引
• 更新比较频繁的字段
• 散列低（区分度低）的不适合，如性别

11.MySql索引用的是什么数据结构

MySql的默认引擎是innoDb，它采用的是B+树结构的索引

• B+树，只有叶子节点才会存储数据，非叶子节点只存储健值，叶子节点之间采用双向指针连接，最底层的叶子节点形成了一个双向有序链表。
  

12.为什么用B+树，而不用普通的二叉树

可以从几个维度去看，查询是否足够快，效率是否稳定，存储数据多少，以及查找磁盘次数

• 为啥不用普通的二叉树
  普通二叉树存在退化的情况，如果他退化成链表，那么相当于全表扫描，平衡二叉树相比来二叉树来说查找效率更文档，总体的查找速度也更快
• 为啥不用平衡二叉树呢
  读取数据的时候，是从磁盘读到内存，如果树这种数据结构作为索引，那么每一次查找数据就需要从磁盘里面读取一个节点，也就是一个磁盘块，但是平衡二叉树可是每个节点都只存储一个键值和数据， 如果是B+树，可以存储更多的节点数据，树的高度也会降低，因此读取磁盘的次数就降下来了，查询效率就快。

13.为什么用B+树，而不用B树

B+树相比较B树，有这些优势

• 它是B树的变种，B Tree能解决的问题，它都能解决
• BTree解决的两大问题：每个节点存储更多关键字，路径更多，扫库扫表能力更强，如果我们要对表进行全表扫描，只需要遍历叶子节点就可以了，不需要变量整颗树，B+Tree能拿到所有的数据。
• B+Tree的磁盘读写能力相对于B Tree来说更强，Io次数更少
• 排序能力更强，因为叶子节点上有下 一个数据区的指针，数据形成了链表
• 效率更加稳定，B+tree永远是在叶子节点拿到的数据，所以io次数是稳定的。

14.Hash索引和B+ 树索引的区别是什么

• B+树索引可以进行范围查询，hash索引不行
• B+树索引可以支持联合索引的最左侧原则。
• B+树支持order by排序
• Hash索引在等值查询上比B+树效率更高
• B+树使用like进行模糊查询的 话，like后面的话可以起到优化作用，Hash索引根本无法进行模糊查询

15 回表了解吗

在InnoDB引擎里面，利用辅助索引查询，先通过辅助索引找到主键索引的键值，在通过主键值查询出主键索引里面有没有符合要求的数据，他比基于主键索引的查询多扫描了一颗索引树，这个过程就叫回表

16.覆盖索引了解吗

在辅助索引里面，不管是单列索引还是联合索引，如果select的数据列只用辅助索引中就能够取得，不用去查主键索
引，这时候使用的索引就叫做覆盖索引，避免了回表

17.什么是最左前缀原则/最左匹配原则

注意：最左前缀原则、最左匹配原则、最左前缀匹配原则这三个都是一个概念
最左匹配原则：在 InnoDB 的联合索引中，查询的时候只有匹配了前一个/左边的值之后，才能匹配下一个。

18.什么是索引下推优化

索引下推优化是MySql5.6后添加的，用于优化数据查询

• 不使用索引下推优化时候，存储引擎通过索引检索到数据，然后返回给MysqlServer，Mysql Server进行过滤条件的判断
• 当使用索引下推优化的时候，如果存在某些索引的列的判断条件时，Mysql Server将这一部分判断条件下推给存储引擎，然后由存储引擎通过判断索引是否符合，MysqlServer传递的条件，只有当索引符号条件时才会将数据传递给Mysql服务器。

索引下推优化可以减少存储引擎查询基础表的次数，也可以减少Mysql服务器从存储引擎接收数据的次数。

19.MySql的事务4大特性

• 原子性 要么一起被执行，要么不执行
• 一致性 ：指在事务开始和结束后，数据不会被破坏。假如A账户转给B账户10块钱，无论是否成功，总和不变
• 隔离性： 多个事务并发访问时候i，事务之间是相互隔离的，即一个事务不影响其他事务运行效果，
• 持久性： 事务完成以后，该事务对数据库的操作更改，都是永久的保存在数据库之中

20.ACID靠什么进行的保证

• 事务的隔离性通过数据库锁的机制实现的
• 事务的一致性由undo log来保证，undo log 是逻辑日志，记录了事务的insert、update、delete、操作。回滚的时候做相反的delete、update、insert操作。
• 事务的原子性和持久性由redo log来保证，redolog被称作重做日志，物理日志，事务提交的时候，必须先将事务的日志写道redo log持久化，到事务提交操作才算完成。

21.什么是幻读，脏读，不可重复读呢？

• 事务 A、B 交替执行，事务 A 读取到事务 B 未提交的数据，这就是脏读。
• 在一个事务范围内，两个相同的查询，读取同一条记录，却返回了不同的数据，这就是不可重复读。
• 事务 A 查询一个范围的结果集，另一个并发事务 B 往这个范围中插入 / 删除了数据，并静悄悄地提交，然后事务 A 再次查询相同的范围，两次读取得到的结果集不一样了，这就是幻读。

22.MVCC了解吗，怎么实现

MVCC多版本并发控制，简单来说就是通过维护数据历史版本，从而解决并发访问情况下的读一致性问题，MVCC 在 MySQL InnoDB 中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读。
是为了解决并发情况下数据库读写一致的问题 ，主要实现关键点**隐式字段、undo日志、版本链、快照读写、当前读，read View

23.数据库读写分离了解吗

读写分离的基本原理是将数据库读写操作分散到不同的节点上。
读写分离的基本实现:

• 数据库服务器搭建主从服务器，一主一从，一主多从
• 数据库主机复制写操作，从数据库读操作。
• 数据库主机通过复制将数据同步到从机，每台数据库服务?都存储了所有的业务数据。
• 业务服务器将写操作发给数据库主机，将读操作发给数据库从机。

24.读写分离怎么实现

将读写操作区分开来，然后访问不同的数据库服务器，一般两种方式：程序代码封装和中间件封装
1.程序代码封装
程序代码封装指在代码中抽象一个数据访问层，实现读写操作分离和数据库服务器连接的管理。类如基于Hibernate进行简单封装就可以实现，目前开源的实现方案中，淘宝的 TDDL ( Taobao Distributed Data Layer, 外号：头都大了）是比较有名的

2.中间件封装
中间件封装指的是独立一套系统出来，实现读写操作分离和数据库服务器连接的管理。中间件对业务服务器提供SQL，兼容的协议，业务服务器无需自己实现读写分离。

25.主从复制原理了解吗

• master数据写入，更新binlog
• master创建一个dump线程向slave推送binlog
• salve连接到master的时候，会创建IO线程接受binlog，并记录relay log中继日志中
• slave在开启sql线程读取relay log事件并在slave执行完成同步
• slave记录自己的binglog
  

26.主从延迟同步怎么处理

主从同步延迟原因：一个服务?开放Ｎ个链接给客户端来连接的，这样有会有大并发的更新操作, 但是从服务?的里面读取 binlog 的线程仅有一个，当某个 SQL 在从服务?上执行的时间稍长 或者由于某个 SQL 要进行锁表就会导致，主服务?的 SQL大量积
压，未被同步到从服务?里。这就导致了主从不一致， 也就是主从延迟。
加粗样式
解决方法：
1.写操作后的读操作发送给数据库主服务器
2.读从机失败后，在读一次主机
这就是通常所说的 “二次读取” ，二次读取和业务无绑定，只需要对底层数据库访问的 API 进行封装即可，实现代价
较小，不足之处在于如果有很多二次读取，将大大增加主机的读操作压力。例如，黑客暴力破解账号，会导致大量的二
次读取操作，主机可能顶不住读操作的压力从而崩溃。
3.关键业务全部指向主机，非关键业务指向从机
例如，对于一个用户管理系统来说，注册 + 登录的业务读写操作全部访问主机，用户的介绍、爰好、等级等业务，可
以采用读写分离，因为即使用户改了自己的自我介绍，在查询时却看到了自我介绍还是旧的，业务影响与不能登录相
比就小很多，还可以忍受。

27.如何分库的

1. 垂直分库：以表为依据，按照业务归属不同，将不同的表拆分到不同的库中。
2. 水平分库：以字段为依据，按照一定策略（hash、range 等），将一个库中的数据拆分到多个库中

28.那你们是怎么分表的？

• 水平分表：以字段为依据，按照一定策略（hash、range 等），将一个表中的数据拆分到多个表中。
• 垂直分表：以字段为依据，按照字段的活跃性，将表中字段拆到不同的表（主表和扩展表）中。

水平分表主要有三种路由方式

• 范围路由：选取有序的数据列 （例如，整形、时间戳等） 作为路由的条件，不同分段分散到不同的数据库表
  中。
• Hash 路由：选取某个列 （或者某几个列组合也可以） 的值进行 Hash 运算，然后根据 Hash 结果分散到不同
  的数据库表中。
• 配置路由：配置路由就是路由表，用一张独立的表来记录路由信息。同样以订单 id 为例，我们新增一张
  order_router 表，这个表包含 orderjd 和 tablejd 两列 , 根据 orderjd 就可以查询对应的 table_id。

29.不停机扩容怎么实现

实际上不停机扩容实操起来是个麻烦而且很有风险的操作

• 第一阶段：在线双写，查询走老库

1. 建立好新的库表结构，数据写入久库的同时，也写入拆分的新库
2. 数据迁移，使用数据迁移程序，将旧库中的历史数据迁移到新库
3. 使用定时任务，新旧库的数据对比，把差异补齐

• 第二阶段，在线双写，查询走新库
  1.完成了对历史的同步和校验
  2.把对数据库的读切换到新库

• 第三阶段：旧库下线
  1.旧库不在写入新的数据
  2.经过一段时间后确定旧库没有请求后，下线旧库

30.常用的分库分表中间件有哪些？

1.sharding-jdbc
2.MyCat

31.那你觉得分库分表会带来什么问题呢？

从分库的角度来讲：
1.事务的问题
使用关系型数据库，有很大一点在于它保证事务完整性。
而分库之后单机事务就用不上了，必须使用分布式事务来解决。
2.跨库 JOIN问题
在一个库中的时候我们还可以利用 JOIN 来连表查询，而跨库了之后就无法使用 JOIN 了。
此时的解决方案就是在业务代码中进行关联，也就是先把一个表的数据查出来，然后通过得到的结果再去查另一张
表，然后利用代码来关联得到最终的结果。
这种方式实现起来稍微比较复杂，不过也是可以接受的。
还有可以适当的冗余一些字段。比如以前的表就存储一个关联 ID，但是业务时常要求返回对应的 Name 或者其他字
段。这时候就可以把这些字段冗余到当前表中，来去除需要关联的操作。
还有一种方式就是数据异构，通过 binlog 同步等方式，把需要跨库 join 的数据异构到 ES 等存储结构中，通过 ES
进行查询。
从分表的角度来看：

• - 跨节点的 count,order by,group by以及聚合函数问题
  只能由业务代码来实现或者用中间件将各表中的数据汇总、排序、分页然后返回。
• 数据迁移，容量规划，扩容等问题
  数据的迁移，容量如何规划，未来是否可能再次需要扩容，等等，都是需要考虑的问题。
  - ID 问题
  数据库表被切分后，不能再依赖数据库自身的主键生成机制，所以需要一些手段来保证全局主键唯一。

32.如果百万级别的数据删除

1. 先删除索引
2. 删除无用数据
3. 重新建立索引

33.百万千万数据表如何新建一个字段

当线上的数据库数据量到达几百万、上千万的时候，加一个字段就没那么简单，因为可能会长时间锁表。

• 通过中间表转换过去
  创建一个临时的新表，把旧表的结构完全复制过去，添加字段，再把旧表数据复制过去，删除旧表，新表命名为旧
  表的名称，这种方式可能回丢掉一些数据
• 先在从库添加，在进行主从切换
  如果一张表数据量大且是热表（读写特别频繁），则可以考虑先在从库添加，再进行主从切换，切换后再将其他几个
  节点上添加字段

34… MySQL 数据库 cpu飙升的话，要怎么处理呢？

1. 使用top命令观察，确定是mysqlid导致还是其他原因
2. 如果是mysqlId导致的，show processlist，查看session情况，确定是不是由消耗资源的sql执行
3. 找出消耗高的sql，explain看看执行计划是否准确，索引是否缺少，数据量大小

处理

1. kill线程
2. 进行相应的sql调整
3. 重新跑sql