性能优化

MySQL性能优化

MySQL 性能指标都有哪些？如何得到这些指标？

MySQL 的性能指标如下：

① TPS（Transaction Per Second） 每秒事务数，即数据库每秒执行的事务数。

MySQL 本身没有直接提供 TPS 参数值，如果我们想要获得 TPS 的值，只有我们自己计算了，可以根据 MySQL 数据库提供的状态变量，来计算 TPS。

需要使用的参数：

Com_commit ：表示提交次数，通过命令 show global status like 'Com_commit'; 获取；
Com_rollback：表示回滚次数，通过命令 show global status like 'Com_rollback'; 获取。

我们定义第一次获取的 Comcommit 的值与 Comrollback 值的和为 c_r1，时间为 t1；

第二次获取的 Comcommit 的值与 Comrollback 值的和为 cr2，时间为 t2，t1 与 t2 单位为秒。那么 TPS = ( cr2 - c_r1 ) / ( t2 - t1 ) 算出来的就是该 MySQL 实例在 t1 与 t2 生命周期之间的平均 TPS。

② QPS（Query Per Second） 每秒请求次数，也就是数据库每秒执行的 SQL 数量，包含 INSERT、SELECT、UPDATE、DELETE 等。 QPS = Queries / Seconds Queries 是系统状态值—总查询次数，可以通过 show status like 'queries'; 查询得出，如下所示：

Seconds 是监控的时间区间，单位为秒。比如，采样 10 秒内的查询次数，那么先查询一次 Queries 值（Q1），等待 10 秒，再查询一次 Queries 值（Q2），那么 QPS 就可以通过，如下公式获得：

QPS = (Q2 - Q1) / 10

③ IOPS（Input/Output Operations per Second） 每秒处理的 I/O 请求次数。

IOPS 是判断磁盘 I/O 能力的指标之一，一般来讲 IOPS 指标越高，那么单位时间内能够响应的请求自然也就越多。理论上讲，只要系统实际的请求数低于 IOPS 的能力，就相当于每一个请求都能得到即时响应，那么 I/O 就不会是瓶颈了。

注意：IOPS 与磁盘吞吐量不一样，吞吐量是指单位时间内可以成功传输的数据数量。

可以使用 iostat 命令，查看磁盘的 IOPS，命令如下：

yum install sysstat iostat -dx 1 10

执行效果如下图所示：

IOPS = r/s + w/s 其中：

r/s：代表每秒读了多少次；
w/s：代表每秒写了多少次。

什么是慢查询？

慢查询是 MySQL 中提供的一种慢查询日志，它用来记录在 MySQL 中响应时间超过阀值的语句，具体指运行时间超过 longquerytime 值的 SQL，则会被记录到慢查询日志中。longquerytime 的默认值为 10，意思是运行 10S 以上的语句。默认情况下，MySQL 数据库并不启动慢查询日志，需要我们手动来设置这个参数，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会给 MySQL 服务器带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

使用 mysql> show variables like '%slow_query_log%'; 来查询慢查询日志是否开启，执行效果如下图所示：

slowquerylog 的值为 OFF 时，表示未开启慢查询日志。

如何开启慢查询日志？

开启慢查询日志，可以使用如下 MySQL 命令：

mysql> set global slowquerylog=1

不过这种设置方式，只对当前数据库生效，如果 MySQL 重启也会失效，如果要永久生效，就必须修改 MySQL 的配置文件 my.cnf，配置如下：

slowquerylog =1 slowquerylogfile=/tmp/mysqlslow.log

如何定位慢查询？

使用 MySQL 中的 explain 分析执行语句，比如：

explain select * from t where id=5;

如下图所示：

其中：

id — 选择标识符。id越大优先级越高，越先被执行。
select_type — 表示查询的类型。
table — 输出结果集的表
partitions — 匹配的分区
type — 表示表的连接类型
possible_keys — 表示查询时，可能使用的索引
key — 表示实际使用的索引
key_len — 索引字段的长度
ref— 列与索引的比较
rows — 大概估算的行数
filtered — 按表条件过滤的行百分比
Extra — 执行情况的描述和说明

其中最重要的就是 type 字段，type 值类型如下：

all — 扫描全表数据
index — 遍历索引
range — 索引范围查找
index_subquery — 在子查询中使用 ref
uniquesubquery — 在子查询中使用 eqref
refornull — 对 null 进行索引的优化的 ref
fulltext — 使用全文索引
ref — 使用非唯一索引查找数据
eq_ref — 在 join 查询中使用主键或唯一索引关联
const — 将一个主键放置到 where 后面作为条件查询， MySQL 优化器就能把这次查询优化转化为一个常量，如何转化以及何时转化，这个取决于优化器，这个比 eq_ref 效率高一点

MySQL 的优化手段都有哪些？

MySQL 的常见的优化手段有以下五种：

① 查询优化

避免 SELECT *，只查询需要的字段。
小表驱动大表，即小的数据集驱动大的数据集，比如，当 B 表的数据集小于 A 表时，用 in 优化 exist，两表执行顺序是先查 B 表，再查 A 表，查询语句：select * from A where id in (select id from B) 。
一些情况下，可以使用连接代替子查询，因为使用 join 时，MySQL 不会在内存中创建临时表。

② 优化索引的使用

- 尽量使用主键查询，而非其他索引，因为主键查询不会触发回表查询。
- 不做列运算，把计算都放入各个业务系统实现
- 查询语句尽可能简单，大语句拆小语句，减少锁时间
- 不使用 select * 查询
- or 查询改写成 in 查询
- 不用函数和触发器
- 避免 %xx 查询
- 少用 join 查询
- 使用同类型比较，比如 '123' 和 '123'、123 和 123
- 尽量避免在 where 子句中使用 != 或者 <> 操作符，查询引用会放弃索引而进行全表扫描
- 列表数据使用分页查询，每页数据量不要太大
- 用 exists 替代 in 查询
- 避免在索引列上使用 is null 和 is not null
- 尽量使用主键查询
- 避免在 where 子句中对字段进行表达式操作
- 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型

③ 表结构设计优化

使用可以存下数据最小的数据类型。
使用简单的数据类型，int 要比 varchar 类型在 MySQL 处理简单。
尽量使用 tinyint、smallint、mediumint 作为整数类型而非 int。
尽可能使用 not null 定义字段，因为 null 占用 4 字节空间。
尽量少用 text 类型，非用不可时最好考虑分表。
尽量使用 timestamp，而非 datetime。
单表不要有太多字段，建议在 20 个字段以内。

④ 表拆分

当数据库中的数据非常大时，查询优化方案也不能解决查询速度慢的问题时，我们可以考虑拆分表，让每张表的数据量变小，从而提高查询效率。 a）垂直拆分：是指数据表列的拆分，把一张列比较多的表拆分为多张表，比如，用户表中一些字段经常被访问，将这些字段放在一张表中，另外一些不常用的字段放在另一张表中，插入数据时，使用事务确保两张表的数据一致性。垂直拆分的原则：

把不常用的字段单独放在一张表；
把 text，blob 等大字段拆分出来放在附表中；
经常组合查询的列放在一张表中。

b）水平拆分：指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。

通常情况下，我们使用取模的方式来进行表的拆分，比如，一张有 400W 的用户表 users，为提高其查询效率我们把其分成 4 张表 users1，users2，users3，users4，然后通过用户 ID 取模的方法，同时查询、更新、删除也是通过取模的方法来操作。

⑤ 读写分离

一般情况下对数据库而言都是“读多写少”，换言之，数据库的压力多数是因为大量的读取数据的操作造成的，我们可以采用数据库集群的方案，使用一个库作为主库，负责写入数据；其他库为从库，负责读取数据。这样可以缓解对数据库的访问压力。

MySQL 常见读写分离方案有哪些？

MySQL 常见的读写分离方案，如下列表：

1）应用层解决方案 可以通过应用层对数据源做路由来实现读写分离，比如，使用 SpringMVC + MyBatis，可以将 SQL 路由交给 Spring，通过 AOP 或者 Annotation 由代码显示的控制数据源。优点：路由策略的扩展性和可控性较强。缺点：需要在 Spring 中添加耦合控制代码。

2）中间件解决方案 通过 MySQL 的中间件做主从集群，比如：Mysql Proxy、Amoeba、Atlas 等中间件都能符合需求。优点：与应用层解耦。缺点：增加一个服务维护的风险点，性能及稳定性待测试，需要支持代码强制主从和事务。

介绍一下 Sharding-JDBC 的功能和执行流程？

Sharding-JDBC 在客户端对数据库进行水平分区的常用解决方案，也就是保持表结构不变，根据策略存储数据分片，这样每一片数据被分散到不同的表或者库中，Sharding-JDBC 提供以下功能：

分库分表
读写分离
分布式主键生成

Sharding-JDBC 的执行流程：当业务代码调用数据库执行的时候，先触发 Sharding-JDBC 的分配规则对 SQL 语句进行解析、改写之后，才会对改写的 SQL 进行执行和结果归并，然后返回给调用层。

什么是 MySQL 多实例？如何配置 MySQL 多实例？

MySQL 多实例就是在同一台服务器上启用多个 MySQL 服务，它们监听不同的端口，运行多个服务进程，它们相互独立，互不影响的对外提供服务，便于节约服务器资源与后期架构扩展。多实例的配置方法有两种：

一个实例一个配置文件，不同端口；
同一配置文件(my.cnf)下配置不同实例，基于 MySQL 的 d_multi 工具。

怎样保证确保备库无延迟？

通常保证主备无延迟有以下三种方法：

每次从库执行查询请求前，先判断 secondsbehindmaster 是否已经等于 0。如果还不等于 0 ，那就必须等到这个参数变为 0 才能执行查询请求，secondsbehindmaster 参数是用来衡量主备延迟时间的长短；
对比位点确保主备无延迟。MasterLogFile 和 ReadMasterLogPos，表示的是读到的主库的最新位点，RelayMasterLogFile 和 ExecMasterLog_Pos，表示的是备库执行的最新位点；
对比 GTID 集合确保主备无延迟。AutoPosition=1 ，表示这对主备关系使用了 GTID 协议；RetrievedGtidSet，是备库收到的所有日志的 GTID 集合；ExecutedGtid_Set，是备库所有已经执行完成的 GTID 集合。

MySQL开放问题

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

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

数据库层面优化：利用子查询优化超多分页场景，比如：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 改写的具体实现；
程序层面优化：可以利用缓存把查询的结果缓存起来，这样再下一次查询的时候性能就非常高了。

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

线上修改表结构有可能 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 表进行变更，它的主要流程为：

1）创建目的表结构的空表 A_new；

2）在A表上创建触发器，包括增、删、改触发器；

3）通过 insert…select…limit N 语句分片拷贝数据到目的表；

4）Copy完成后，将 A_new 表 rename 到 A 表。

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

查询速度慢的原因很多，常见如下几种： 1）查询字段没有索引或者没有触发索引查询，没有触发索引查询的情况如下：不会使用索引的情况如下：

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

2）I/O 压力大，读取磁盘速度变慢。 3）内存不足 4）网络速度慢 5）查询出的数据量过大，可以采用多次查询或其他的方法降低数据量 6）死锁，一般碰到这种情况的话，大概率是表被锁住了，可以使用 show processlist; 命令，看看 SQL 语句的状态，再针对不同的状态做相应的处理。

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

select * from informationschema.innodbtrx;

b）查看当前锁定的事务：

select * from informationschema.innodblocks;

c）查看当前等锁的事务

select * from informationschema.innodblock_waits;

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

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

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

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

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

如何保证数据不被误删？

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

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

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

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

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

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

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

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

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

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

1）前端优化

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

2）中间层负载分发

可利用负载均衡，比如 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;   
}

3）控制层（网关层）

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

4）服务层

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

5）数据库层

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

MySQL性能优化​

MySQL 性能指标都有哪些？如何得到这些指标？​

什么是慢查询？​

如何开启慢查询日志？​

如何定位慢查询？​

MySQL 的优化手段都有哪些？​

① 查询优化​

② 优化索引的使用​

③ 表结构设计优化​

④ 表拆分​

⑤ 读写分离​

MySQL 常见读写分离方案有哪些？​

介绍一下 Sharding-JDBC 的功能和执行流程？​

什么是 MySQL 多实例？如何配置 MySQL 多实例？​

怎样保证确保备库无延迟？​

MySQL开放问题​

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

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

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

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

如何保证数据不被误删？​

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

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

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

1）前端优化​

2）中间层负载分发​

3）控制层（网关层）​

4）服务层​

5）数据库层​