MySQL性能优化总结（更新）

• 常规优化
• • 长连接导致的OOM问题
  • 尽量不要使用查询缓存
  • 事务启动方式
  • 控制刷脏页速度否则会变慢
  • 如何正确的删除表
  • 避免使用orderBy语句
  • 避免使用函数计算就用不上索引
  • redolog与binlog双1操作
• 索引优化
• • 合理使用覆盖索引
  • 普通索引与唯一索引如何选择
  • 索引选错怎么办
  • 如何正确字符串加索引
• 锁
• • 如何减少热点行锁的影响
• 业务高峰期伤人伤己的优化方法
• • 短连接风暴如何处理
  • 慢查询性能问题
  • QPS 突增问题

常规优化

长连接导致的OOM问题

1. 定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
2. 如果你用的是 MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

尽量不要使用查询缓存

查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。

事务启动方式

有些客户端连接框架会默认连接成功后先执行一个 set autocommit=0 的命令。这就导致接下来的查询都在事务中，如果是长连接，就导致了意外的长事务。因此，我会建议你总是使用 set autocommit=1, 通过显式语句的方式来启动事务：

1. 显式启动事务语句， begin 或 start transaction。配套的提交语句是 commit，回滚语句是 rollback。
2. set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接。

控制刷脏页速度否则会变慢

按照 innodb_io_capacity 定义的能力乘以 R% 来控制刷脏页的速度。R脏页比例以及 redo log 写盘速度的最大值，一般不超过75%。

如何正确的删除表

表中的数据被删除了，但是表空间却没有被回收。delete 命令其实只是把记录的位置，或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的。此外插入数据也会造成这种空洞现象。

解决措施重建表：

alter table A engine=InnoDB

此时表A不能更新，否则就会有数据丢失。MySQL5.6后引入ONLINE DDL,对操作流程进行了优化，允许增删改操作，使用gh-ost:一个开源的缩小表空间工具

避免使用orderBy语句

1. orderby时MySQL会为每个线程分配一个内存（sort_buffer）用于排序该内存大小为sort_buffer_size
   1>如果排序的数据量小于sort_buffer_size，排序将会在内存中完成
   2>如果排序数据量很大，内存中无法存下这么多数据，则会使用磁盘临时文件来辅助排序，也称外部排序
   3>在使用外部排序时，MySQL会分成好几份单独的临时文件用来存放排序后的数据，然后在将这些文件合并成一个大文件
2. 排序方式全字段排序与rowid排序，认为内存足够大时会优先选择全字段排序，因为这种方式比rowid 排序避免了一次回表操作

改进:创建联合索引或者覆盖所有字段的联合索引，这里并不是 说要为了每个查询能用上覆盖索引，就要把语句中涉及的字段都建上联合索引，毕竟索引还是有维护代价的。这是一个需要权衡的决定。

避免使用函数计算就用不上索引

select count(*) from tradelog where month(t_modified)=7;

为什么条件是 where t_modified='2018-7-1’的时候可以用上索引，而改成 where month(t_modified)=7用不上索引?
对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。需要注意的是，优化器并不是要放弃使用这个索引。而是放弃了树搜索功能，优化器可以选择遍历主键索引，也可以选择遍历索引 t_modified，优化器对比索引大小后发现，索引 t_modified 更小，遍历这个索引比遍历主键索引来得更快。因此最终还是会选择索引 t_modified。

解决方法：由于在 t_modified 字段加了 month() 函数操作，导致了全索引扫描。为了能够用上索引的快速定位能力，我们就要把 SQL 语句改成基于字段本身的范围查询。

mysql> select count(*) from tradelog where
    -> (t_modified >= '2016-7-1' and t_modified<'2016-8-1') or
    -> (t_modified >= '2017-7-1' and t_modified<'2017-8-1') or 
    -> (t_modified >= '2018-7-1' and t_modified<'2018-8-1');

避免使用隐式类型转换，就用不上索引

mysql> select * from tradelog where tradeid=110717;

交易编号 tradeid 这个字段上，本来就有索引，但是 explain 的结果却显示，这条语句需要走全表扫描。你可能也发现了，tradeid 的字段类型是 varchar(32)，而输入的参数却是整型，所以需要做类型转换。字符串和数字做比较的话，是将字符串转换成数字。

就知道对于优化器来说，这个语句相当于：

mysql> select * from tradelog where  CAST(tradid AS signed int) = 110717;

这条语句触发了我们上面说到的规则：对索引字段做函数操作，优化器会放弃走树搜索功能。

避免两个表的字符集不同，一个是 utf8，一个是 utf8mb4，所以做表连接查询的时候用不上关联字段的索引。同样也是使用到了函数转换用不上树搜索功能。

redolog与binlog双1操作

通常我们说 MySQL 的“双 1”配置，指的就是 sync_binlog 和 innodb_flush_log_at_trx_commit 都设置成 1。也就是说，一个事务完整提交前，需要等待两次刷盘，一次是 redo log（prepare 阶段），一次是 binlog。这样才能保证数据的完整性。

索引优化

合理使用覆盖索引

如果执行的语句是 select ID from T where k between 3 and 5，需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引。由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

普通索引与唯一索引如何选择

1. 普通索引与唯一索引，普通索引在更新时速度更快，尽量选普通索引
2. 更新之后马上就是查询时，不使用change buffer ，因为会立即触发merge操作
3. change buffer更适合普通索引

索引选错怎么办

场景：不断的删除历史数据和新增数据可能导致MySQL选错索引

原因：选择索引是优化器的工作，扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。

根据统计信息判断，统计信息就是索引的区分度，索引上不同的值越多，区分度越好。而一个索引上不同的值的个数，我们称之为“基数”（cardinality）。也就是说，这个基数越大，索引的区分度越好。MySQL 是通过采样统计得到索引的基数

explain +语句 产看统计信息
analyze table 索引信息统计不准确解决方法

1. 索引统计信息不准确导致的问题，你可以用 analyze table 来解决。
2. 采用 force index 强行选择一个索引
3. 考虑修改语句，引导 MySQL 使用我们期望的索引。比如，在这个例子里，显然把“order by b limit 1” 改成 “order by b,a limit 1” ，语义的逻辑是相同的。
4. 第三种方法是，在有些场景下，我们可以新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。

如何正确字符串加索引

1. 直接创建完整索引，这样可能比较占用空间；

2. 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；

3. 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；

4. 创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。

锁

如何减少热点行锁的影响

如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。热点行更新的解决策略: 降低并发度 1. 拆行，一行拆多行 2. Server 层限流，即同一时间进入更新的线程数 3. 关闭死锁监测（关闭的弊端是可能超时较多）

业务高峰期伤人伤己的优化方法

短连接风暴如何处理

正常的短连接模式就是连接到数据库后，执行很少的 SQL 语句就断开，下次需要的时候再重连。如果使用的是短连接，在业务高峰期的时候，就可能出现连接数突然暴涨的情况。

错误方法：加大连接数会导致系统的负载可能会进一步加大，大量的资源耗费在权限验证等逻辑上，结果可能是适得其反，已经连接的线程拿不到 CPU 资源去执行业务的 SQL 请求。

第一种方法：先处理掉那些占着连接但是不工作的线程。在 show processlist 的结果里，踢掉显示为 sleep 的线程，可能有损。
第二种方法：减少连接过程的消耗，是让数据库跳过权限验证阶段。跳过权限验证的方法是：重启数据库，并使用–skip-grant-tables 参数启动。这样，整个 MySQL 会跳过所有的权限验证阶段，包括连接过程和语句执行过程在内 ，风险极高。

慢查询性能问题

原因有三：索引没有设计好；SQL 语句没写好；MySQL 选错了索引。

导致慢查询的第一种可能是，索引没有设计好。这种场景一般就是通过紧急创建索引来解决。MySQL 5.6 版本以后，创建索引都支持 Online DDL 了，对于那种高峰期数据库已经被这个语句打挂了的情况，最高效的做法就是直接执行 alter table 语句。

导致慢查询的第二种可能是，语句没写好。可以通过改写 SQL 语句来处理。MySQL 5.7 提供了 query_rewrite 功能，可以把输入的一种语句改写成另外一种模式。比如，语句被错误地写成了 select * from t where id + 1 = 10000，你可以通过下面的方式，增加一个语句改写规则。

mysql> insert into query_rewrite.rewrite_rules(pattern, replacement, pattern_database) values ("select * from t where id + 1 = ?", "select * from t where id = ? - 1", "db1");

call query_rewrite.flush_rewrite_rules();

这里，call query_rewrite.flush_rewrite_rules() 这个存储过程，是让插入的新规则生效，也就是我们说的“查询重写”。

导致慢查询的第三种可能是，MySQL 选错了索引，已经在前文提及。

QPS 突增问题

有时候由于业务突然出现高峰，或者应用程序 bug，导致某个语句的 QPS 突然暴涨，也可能导致 MySQL 压力过大，影响服务。可能是由一个新功能的 bug 导致的。当然，最理想的情况是让业务把这个功能下掉，服务自然就会恢复。

1. 一种是由全新业务的 bug 导致的。假设你的 DB 运维是比较规范的，也就是说白名单是一个个加的。这种情况下，如果你能够确定业务方会下掉这个功能，只是时间上没那么快，那么就可以从数据库端直接把白名单去掉。
2. 如果这个新功能使用的是单独的数据库用户，可以用管理员账号把这个用户删掉，然后断开现有连接。这样，这个新功能的连接不成功，由它引发的 QPS 就会变成 0。
3. 如果这个新增的功能跟主体功能是部署在一起的，那么我们只能通过处理语句来限制。这时，我们可以使用上面提到的查询重写功能，把压力最大的 SQL 语句直接重写成"select 1"返回。

当然，这个操作的风险很高，需要你特别细致。它可能存在两个副作用：

1. 如果别的功能里面也用到了这个 SQL 语句模板，会有误伤；
2. 很多业务并不是靠这一个语句就能完成逻辑的，所以如果单独把这一个语句以 select 1 的结果返回的话，可能会导致后面的业务逻辑一起失败。