【MySQL】常见问题

1. 有一个超级大表，如何优化分页查询？

超级大表的分页优化分有以下两种方式：

• 数据库层面优化：
  • 利用子查询优化超多分页场景，比如：SELECT a.* FROM 表 1 a, (select id from 表 1 where 条件 LIMIT 100000,20 ) b where a.id=b.id ，先快速定位需要获取的 id 段，然后再关联查询。
  • MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N 行，那当 offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL 改写，利用子查询先快速定位需要获取的 id 段，然后再关联查询，就是对分页进行 SQL 改写的具体实现；
• 程序层面优化：
  • 可以利用缓存把查询的结果缓存起来，这样再下一次查询的时候性能就非常高了。

2. 线上修改表结构有哪些风险？

线上修改表结构有可能 MySQL 服务器阻塞，因为在执行 DML（select、update、delete、insert）操作时，会给表增加一个元数据锁，这个元数据锁是为了保证在查询期间表结构不会被修改，而执行修改表结构时，必须要等待元数据锁完成之后才能执行，这就可能造成数据库服务器的阻塞。

在 MySQL 5.6 开始提供了 online ddl 功能，允许一些 DDL（create table/view/index/syn/cluster）语句和 DML 语句并发，在 5.7 版本对 online ddl 又有了增强，这使得大部分 DDL 操作可以在线进行，详见：https://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html，这使得在线上修改表结构的风险变的更大，如果在业务开发过程中必须在线修改表结构，可以参考以下方案：

• 尽量在业务量小的时间段进行；
• 查看官方文档，确认要做的表修改可以和 DML 并发，不会阻塞线上业务；
• 推荐使用 percona 公司的 pt-online-schema-change 工具，该工具被官方的 online ddl 更为强大，它的基本原理是：通过 insert…select… 语句进行一次全量拷贝，通过触发器记录表结构变更过程中产生的增量，从而达到表结构变更的目的。比如，要对 A 表进行变更，它的主要流程为：
  • 创建目的表结构的空表 A_new；
  • 在A表上创建触发器，包括增、删、改触发器；
  • 通过 insert…select…limit N 语句分片拷贝数据到目的表；
  • Copy完成后，将 A_new 表 rename 到 A 表。

3. 查询长时间不返回可能是什么原因？应该如何处理？

查询速度慢的原因很多，常见如下几种：

• 查询字段没有索引或者没有触发索引查询，没有触发索引查询的情况如下：

  • 不会使用索引的情况如下：
    • 以 % 开头的 like 查询不会使用 b-tree 索引；
    • 数据类型出现隐式转换时不会使用索引，比如，某列是 varchar 类型，却使用了columnname=1 的查询语句，这是不会使用索引，正确触发索引的查询语句为：columnname=‘1’ ；
  • 不符合最左前缀原则；
    • 如果查询条件有 or 分割，or 前面的使用索引，or 后面的未使用索引，则不会使用索引，因为即使 or 之前的使用了索引，但是 or 之后的也需要全表查询，索引就忽略索引，直接全表查询；
    • 如果 MySQL 认为使用索引会比全表查询更慢，则不会使用索引。

• I/O 压力大，读取磁盘速度变慢。

• 内存不足

• 网络速度慢

• 查询出的数据量过大，可以采用多次查询或其他的方法降低数据量

• 死锁，一般碰到这种情况的话，大概率是表被锁住了，可以使用 show processlist; 命令，看看 SQL 语句的状态，再针对不同的状态做相应的处理:

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SxadiI7Q-1571191619145)(D:\desktop\markdown\image\1.jpg)]

  其中，当 State 列值为 Locked 时，表示被锁定。 其它关于查看死锁的命令：

  • 查看当前的事务：

  select * from informationschema.innodbtrx;
  

  • 查看当前锁定的事务：

  select * from informationschema.innodblocks;
  

  • 查看当前等锁的事务:

  select * from informationschema.innodblock_waits;
  

以上问题的解决方案如下：

• 正确创建和使用索引。
• 把数据、日志、索引放到不同的 IO 设备上，减少主数据库的 IO 操作。更换 MySQL 的磁盘为固态硬盘，以提高磁盘的 IO 性能。
• 升级内存，更换更大的内存。
• 提升网速，升级带宽。
• 用 Profiler 来跟踪查询，得到查询所需的时间，找出有问题的 SQL 语句，优化 SQL。
• 查询时值返回需要的字段。
• 设置死锁的超时时间，限制和避免死锁消耗过多服务器的资源。
• 尽量少用视图，它的效率低，对视图操作比直接对表操作慢,可以用存储过程来代替视图。
• 不要用视图嵌套，嵌套视图增加了寻找原始数据的难度

4. MySQL 主从延迟的原因有哪些？

主从延迟可以根据 MySQL 提供的命令判断，比如，在从服务器使用命令： show slave status;，其中 SecondsBehindMaster 如果为 0 表示主从复制状态正常。 导致主从延迟的原因有以下几个：

• 主库有大事务处理；
• 主库做大量的增、删、改操作；
• 主库对大表进行字段新增、修改或添加索引等操作；
• 主库的从库太多，导致复制延迟。从库数量一般 3-5 个为宜，要复制的节点过多，导致复制延迟；
• 从库硬件配置比主库差，导致延迟。查看 Master 和 Slave 的配置，可能因为从库的配置过低，执行时间长，由此导致的复制延迟时间长；
• 主库读写压力大，导致复制延迟；
• 从库之间的网络延迟。主从库网卡、网线、连接的交换机等网络设备都可能成为复制的瓶颈，导致复制延迟，另外跨公网主从复制很容易导致主从复制延迟。

5. 如何保证数据不被误删？

保证数据不被误删的方法如下：

• 权限控制与分配（数据库和服务器权限）
• 避免数据库账号信息泄露，在生产环境中，业务代码不要使用明文保存数据库连接信息；
• 重要的数据库操作，通过平台型工具自动实施，减少人工操作；
• 部署延迟复制从库，万一误删除时用于数据回档，且从库设置为 read-only；
• 确认备份制度及时有效；
• 启用 SQL 审计功能，养成良好 SQL 习惯；
• 启用 sqlsafeupdates 选项，不允许没 where 条件的更新/删除；
• 将系统层的 rm 改为 mv；
• 线上不进行物理删除，改为逻辑删除（将 row data 标记为不可用）；
• 启用堡垒机，屏蔽高危 SQL；
• 降低数据库中普通账号的权限级别；
• 开启 binlog，方便追溯数据。

6. MySQL 服务器 CPU 飙升应该如何处理？

使用 show full processlist; 查出慢查询，为了缓解数据库服务器压力，先使用 kill 命令杀掉慢查询的客户端，效果如下：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vfGO3RJs-1571191619147)(D:\desktop\markdown\image\2.jpg)]

然后再去项目中找到执行慢的 SQL 语句进行修改和优化。

7. MySQL 毫无规律的异常重启，可能产生的原因是什么？该如何解决？

可能是积累的长连接导致内存占用太多，被系统强行杀掉导致的异常重启，因为在 MySQL 中长连接在执行过程中使用的临时内存对象，只有在连接断开的时候才会释放，这就会导致内存不断飙升，解决方案如下：

• 定期断开空闲的长连接；
• 如果是用的是 MySQL 5.7 以上的版本，可以定期执行 mysqlresetconnection 重新初始化连接资源，这个过程会释放之前使用的内存资源，恢复到连接刚初始化的状态。

8. 如何实现一个高并发的系统？

这道面试题涉及的知识点比较多，主要考察的是面试者的综合技术能力。高并发系统的设计手段有很多，主要体现在以下五个方面：

• 前端优化：

  • 静态资源缓存：将活动页面上的所有可以静态的元素全部静态化，尽量减少动态元素；通过 CDN、浏览器缓存，来减少客户端向服务器端的数据请求。
  • 禁止重复提交：用户提交之后按钮置灰，禁止重复提交。
  • 用户限流：在某一时间段内只允许用户提交一次请求，比如，采取 IP 限流。

• 中间层负载分发

  可利用负载均衡，比如 nginx 等工具，可以将并发请求分配到不同的服务器，从而提高了系统处理并发的能力。 nginx 负载分发的五种方式：

  • 轮询（默认） 每个请求按时间顺序逐一分配到不同的后端服务器，如果后端服务器不能正常响应，nginx 能自动剔除故障服务器。

  • 按权重（weight） 使用 weight 参数，指定轮询几率，weight 和访问比率成正比，用于后端服务器性能不均的情况，配置如下：

    upstream backend { 
        server 192.168.0.14 weight=10; 
        server 192.168.0.15 weight=10; 
    }
    

  • IP 哈希值（ip_hash） 每个请求按访问 IP 的哈希值分配，这样每个访客固定访问一个后端服务器，可以解决 session 共享的问题，配置如下：

    upstream backend { 
        ip_hash; 
        server 192.168.0.14:88; 
        server 192.168.0.15:80; 
    }
    

  • 响应时间（fair） 按后端服务器的响应时间来分配请求，响应时间短的优先分配，配置如下：

    upstream backend { 
        fair; 
        server server1.com; 
        server server2.com; 
    }
    

  • URL 哈希值（url_hash） 按访问 url 的 hash 结果来分配请求，和 IP 哈希值类似。

  upstream backend {
      hash $request_uri;
      server server1.com; 
      server server2.com;   
  }
  

• 控制层（网关层）

  • 限制同一个用户的访问频率，限制访问次数，防止多次恶意请求。

• 服务层

  • 业务服务器分离：比如，将秒杀业务系统和其他业务分离，单独放在高配服务器上，可以集中资源对访问请求抗压。
  • 采用 MQ（消息队列）缓存请求：MQ 具有削峰填谷的作用，可以把客户端的请求先导流到 MQ，程序在从 MQ 中进行消费（执行请求），这样可以避免短时间内大量请求，导致服务器程序无法响应的问题。
  • 利用缓存应对读请求，比如，使用 Redis 等缓存，利用 Redis 可以分担数据库很大一部分压力

• 数据库层

  • 合理使用数据库引擎
  • 合理设置事务隔离级别，合理使用事务
  • 正确使用 SQL 语句和查询索引
  • 合理分库分表
  • 使用数据库中间件实现数据库读写分离
  • 设置数据库主从读写分离