（五）【Java精选面试题】MySql（含答案）

矫情吗；* 2023-09-28 08:01 85阅读 0赞

#### 目录 ####

*  1. 垂直与水平拆分设计原理
 *  2. 分表分库之后如何查询的呢？
 *  3. 千万级数据分表分库策略有哪些?
 *  4. 分表分库之后分页如何查询
 *  5. 单表达到多大量开始分表分库
 *  6. hash 分表算法分表存在哪些优缺点缺点：
 *  7. 根据范围的形式分片
 *  8. 根据日期的形式分片
 *  9. mycat 或者Shardingjdbc
 *  10. MyISAM与InnoDb之间区别
 *  11. 为什么InnoDb引擎必须有主键，并且推荐使用整型的自增方式
 *  12. 数据库如何定位慢查询
 *  13. 执行计划EXPLAIN如何使用
 *  14. EXPLAIN Type需要达到什么级别
 *  15. mysql索引为什么需要遵循最佳左前缀法则
 *  16. mysql索引为什么需要避免回表查询
 *  17. 超过多少张表禁止join
 *  18. 为什么阿里巴巴需要禁止join

--------------------

## 1. 垂直与水平拆分设计原理 ##

1.数据库拆分主要指分库分表，其目的主要是分散数据库压力，达到横向扩展，满足均衡访问等。  
2.数据库拆分主要有两种方式：垂直拆分和水平拆分。

A.垂直拆分：  
将不同业务功能相关的表放到不同的数据库中 也就是类似于 微服务架构中会员数据库/订单数据库/支付数据库  
数据整合就需要通过 RPC 接口通讯

B.水平拆分：  
将一张表数据拆分 n 多张不同的子表来进行存放  
考虑扩容的问题  
考虑查询的问题  
考虑主键问题 （序列）  
考虑一张表最多存放多少数据（）  
当一张表的业务量行数如果超过 500 万行（阿里巴巴 java 开发手册官方推荐），分页/排序效率还是非常低，可以对同一张表数据实现拆分放到多个不同的表中存放。

## 2. 分表分库之后如何查询的呢？ ##

1.  整合数据库中间件 mycat 或者 shadingjdbc;
2.  客户端代码中将表的名称替换成虚拟表的名称(虚拟表的名称是被我们数据库中间件改写的)，客户端发送 JDBC 语句 会被数据库中间件进行拦截
3.  获取到 条件后面的 分片字段 如果获取成功 则根据该  
    分片字段 计算 直接定位到该表----只会查询一次。
4.  Sql 后面没有带上条件 分片字段 对每张表都会做操作，在将该结果给数据库中间件整合后返回给客户端。  
    建议最好查询的过程中 带上分片字段，不然对每张都会查询一次。

## 3. 千万级数据分表分库策略有哪些? ##

回答：  
数据的插入：

1.  定义一个全局的 id，根据该全局 id 采用分片算法，计算该条数据应该落地存放到具体那张表。
2.  该全局 id 特点：有序且递增 推荐序列

1.取余/取模  
2.按照范围分片  
3.按照日期进行分片  
4.按照枚举值分片  
5.二进制取模范围分片  
6.一致性 hash 分片  
7.按照目标字段前缀指定的进行分区  
8.按照前缀 ASCII 码和值进行取模范围分片  
一张表的数据达到 500 万条 开始分表分库

## 4. 分表分库之后分页如何查询 ##

核心点 还是看分片字段 如果 sql 语句有带上分片字段的话  
则只会对一张表来进行分页查询。  
如果 sql 语句没有带上 分片字段的话 则会对每张表来做分页查询  
将该结果交给我们的数据库中间件来整合 二次分页之后返回给客户端。

## 5. 单表达到多大量开始分表分库 ##

【推荐】单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。说明：如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。  
摘自：阿里巴巴 java 开发手册

## 6. hash 分表算法分表存在哪些优缺点缺点： ##

以后无法支持新增表的扩容  
优点：  
将数据 可以分摊的形式存放 均匀的形式存放  
前提：需要保证用户的 userid 连续 计算 存放  
序列或者 设置每张表的步长

## 7. 根据范围的形式分片 ##

假设一张表存放的数据 500 万  
以每张 500 万条数据的形式来进行分表  
User0 0-500 万条数据  
User1 500-1000 万条数据  
User2 1000 1500 万条数据  
Kafka 的日志存储

## 8. 根据日期的形式分片 ##

应用场景在哪些？  
例如：订单  
例如 根据每天 或者 每年  
数据存储不均匀 淡季 旺季  
支持扩容  
订单号码 带上日期规则  
20251010  
查询历史数据

## 9. mycat 或者Shardingjdbc ##

Mycat 是基于服务器端实现代理;  
Shardingjdbc 是基于客户端改写 sql 语句代理;  
相对于来说 Shardingjdbc 效率比 Mycat 高。  
Mycat 比 Shardingjdbc 更加安全的  
![在这里插入图片描述][ff709332b0fb4310a4f6614b44b3da52.png]  
如果我们 sql 语句没有带上分片字段 ，建议 sql 语句后面 带上  
Limit 分页查询 避免数据内存溢出

## 10. MyISAM与InnoDb之间区别 ##

1.InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；

2.InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；

3.InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。

MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

也就是说：InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。  
![在这里插入图片描述][4ea3e39e87e541e18f224d681432c9eb.png]  
![在这里插入图片描述][11dc6b5684dd437eb89363495cfcb9ef.png]  
4. InnoDB不保存表的具体行数，执行select count(\*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快（注意不能加有任何WHERE条件）；

> 那么为什么InnoDB没有了这个变量呢？
> 
> 因为InnoDB的事务特性，在同一时刻表中的行数对于不同的事务而言是不一样的，因此count统计会计算对于当前事务而言可以统计到的行数，而不是将总行数储存起来方便快速查询。InnoDB会尝试遍历一个尽可能小的索引除非优化器提示使用别的索引。如果二级索引不存在，InnoDB还会尝试去遍历其他聚簇索引。
> 
> 如果索引并没有完全处于InnoDB维护的缓冲区（Buffer  
> Pool）中，count操作会比较费时。可以建立一个记录总行数的表并让你的程序在INSERT/DELETE时更新对应的数据。和上面提到的问题一样，如果此时存在多个事务的话这种方案也不太好用。如果得到大致的行数值已经足够满足需求可以尝试SHOW  
> TABLE STATUS

5.Innodb不支持全文索引，而MyISAM支持全文索引，在涉及全文索引领域的查询效率上MyISAM速度更快高；PS：5.7以后的InnoDB支持全文索引了

6.MyISAM表格可以被压缩后进行查询操作

7.InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁

InnoDB的行锁是实现在索引上的，而不是锁在物理行记录上。潜台词是，如果访问没有命中索引，也无法使用行锁，将要退化为表锁。

例如：
    
       t_user(uid, uname, age, sex) innodb;
    
       uid PK
       无其他索引
       update t_user set age=10 where uid=1;             命中索引，行锁。
    
       update t_user set age=10 where uid != 1;           未命中索引，表锁。
    
       update t_user set age=10 where name='chackca';    无索引，表锁。

8、InnoDB表必须有唯一索引（如主键）（用户没有指定的话会自己找/生产一个隐藏列Row\_id来充当默认主键），而Myisam可以没有

9、Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI

Innodb：frm是表定义文件，ibd是数据文件
    
        Myisam：frm是表定义文件，myd是数据文件，myi是索引文件

**如何选择：**  
1. 是否要支持事务，如果要请选择innodb，如果不需要可以考虑MyISAM；  
2. 如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读也有写，请使用InnoDB。  
3. 系统奔溃后，MyISAM恢复起来更困难，能否接受；  
4. MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的，如果你不知道用什么，那就用InnoDB，至少不会差。

## 11. 为什么InnoDb引擎必须有主键，并且推荐使用整型的自增方式 ##

自增ID可以保证每次插入时B+索引是从右边扩展的，可以避免B+树和频繁合并和分裂（对比使用UUID）。如果使用字符串主键和随机主键，会使得数据随机插入，效率比较差。

## 12. 数据库如何定位慢查询 ##

慢SQL定位（慢查询日志）  
定位慢SQL相对来说很简单，因为Mysql中已经提供了对应的工能，我们只需要开启对应的“慢查询日志”功能，然后稍作配置即可，开启功能有Mysql会把查询时间大于你设置时间的SQL记录下来，并且保存到一个专门的文件中，你只需要查看这个文件内容就可以找到对应查询慢的SQL了，配置了慢查询日志后，它会记录在设定时间范围内的数据查询和数据修改语句。

慢查询日志的配置  
在mysql 配置文件中 （不出意外应该在/etc/my.conf)，进行下面配置，修改配置后重启mysql生效。

slow_query_log = ON
    long_query_time = 5
    slow_query_log_file = /opt/soft/mysql/log/slow.log
    log_queries_not_using_indexes=on

## 13. 执行计划EXPLAIN如何使用 ##

最直接评判一条SQL语句的性能，就是使用explain来查看SQL语句的执行计划。可以说，explain是SQL语句优化的基础。explain的语法很简单，就是在一条SQL语句之前添加explain即可。

## 14. EXPLAIN Type需要达到什么级别 ##

至少要达到range级别，要求是ref级别，如果可以是const最好。

说明：  
1.consts单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读到数据。  
2.ref指的是使用普通的索引（normal index）。  
3.range对索引进行范围检索  
`type：表示连接类型，性能由好到差为null（不查表）>system（查系统表）>const（主键/唯一索引访问时）>eq_ref>ref（非唯一索引查询）>range>index（使用索引，但全表扫描了）>all（全文检索）`

## 15. mysql索引为什么需要遵循最佳左前缀法则 ##

最左前缀法则：如果使用联合索引不是按照索引顺序从左到右使用，联合索引就会失效（注：sql中可以将顺序打乱，MySQL会进行优化，但不能出现缺少该索引字段的前驱索引字段的情况）。

## 16. mysql索引为什么需要避免回表查询 ##

一般情况下，只要使用普通索引，并且select 字段不属于索引，单次普通索引Tree查找，无法获取满足的数据，会进行第二次Tree查找  
这就是回表查询，先定位主键值，再定位行记录，它的性能较扫一遍索引树更低，因为两次查找Tree，磁盘IO较多。

## 17. 超过多少张表禁止join ##

《阿里巴巴JAVA开发手册》里面写超过三张表禁止join

## 18. 为什么阿里巴巴需要禁止join ##

超过三张表禁止join ,由于数据量太大的时候，mysql根本查询不出来，导致阿里出了这样一个规定。

[ff709332b0fb4310a4f6614b44b3da52.png]: https://img-blog.csdnimg.cn/ff709332b0fb4310a4f6614b44b3da52.png
[4ea3e39e87e541e18f224d681432c9eb.png]: https://img-blog.csdnimg.cn/4ea3e39e87e541e18f224d681432c9eb.png
[11dc6b5684dd437eb89363495cfcb9ef.png]: https://img-blog.csdnimg.cn/11dc6b5684dd437eb89363495cfcb9ef.png