MySQL (开放性问题篇)

MySQL (开放性问题篇)

1、有一个超级大表,如何优化分页查询?

(1)数据库层面优化
# 采用延迟关联的形式进行优化

select * from film orderby title limit 100,10;
# 改为:
select * from film  as a  
inner join 
(select film_id from film  orderby title limit 100,10) as b 
on a.film_id=b.film_id

注:可以把 on a.film_id=b.film_id 换成 using(film_id),效果是等价的。


(2)程序层面优化

可以利用缓存把查询的结果缓存起来,这样再下一次查询的时候性能就非常高了。

 

2、线上修改表结构有哪些风险?如何解决?

风险:

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

解决方案:

  • 尽量在业务量小的时间段进行;

  • 查看官方文档,确认要做的表修改可以和 DML 并发,不会阻塞线上业务;

  • 使用 percona 公司的 pt-online-schema-change 工具,该工具被官方的 online ddl 更为强大,它的基本原理是:通过 insert…select… 语句进行一次全量拷贝,通过触发器记录表结构变更过程中产生的增量,从而达到表结构变更的目的。比如,要对 A 表进行变更,它的主要流程为:
    创建目的表结构的空表 A_new;
    在A表上创建触发器,包括增、删、改触发器;
    通过 insert…select…limit N 语句分片拷贝数据到目的表;
    Copy完成后,将 A_new 表 rename 到 A 表。

 

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

(1)可能原因:

  • 查询字段没有索引或者没有触发索引查询

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

  • 内存不足

  • 网络速度慢

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

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

(2)解决方案:

  • 正确创建和使用索引

  • 把数据、日志、索引放到不同的 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 命令杀掉慢查询的客户端,然后再去项目中找到执行慢的 SQL 语句进行修改和优化。

 

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

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

定期断开空闲的长连接;

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

 

8、如何实现一个高并发的系统?

前端优化:

静态资源缓存:将活动页面上的所有可以静态的元素全部静态化,尽量减少动态元素;通过 CDN、浏览器缓存,来减少客户端向服务器端的数据请求。

禁止重复提交:用户提交之后按钮置灰,禁止重复提交。

用户限流:在某一时间段内只允许用户提交一次请求,比如,采取 IP 限流。

 
负载均衡:

比如 nginx 等工具,可以将并发请求分配到不同的服务器,从而提高了系统处理并发的能力。

轮询(默认值)、权重(weight)、ip 哈希(ip_hash)、响应时间(fair)、url 哈希(hash $request_uri;)

upstream backend { 
    # 在这里填写选择哪种方式进行分发请求,不写此行则默认为轮询。举例:
    ip_hash;
    server 192.168.0.14:88; 
    server 192.168.0.15:80; 
}
# 注:如果是选择权重,则将权重数跟到对应的ip后面

 
控制层(网关层):

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

 
服务层:

  • 业务服务器分离(可以使用微服务架构或将秒杀等并发高的业务单独独立出来)

  • 消息队列缓冲请求

  • 利用 nosql (比如 redis、memcache)提升读性能

 
数据库层:

  • 合理使用数据库引擎

  • 合理设置事务隔离级别,合理使用事务

  • 正确使用 SQL 语句和查询索引

  • 读写分离

  • 合理分库分表

 

 

NEW

1核2G服务器1年99元,还有更多配置等你挑选...

买阿里云服务器买腾讯云服务器