MySql教程

MySQL性能优化总结(更新)

本文主要是介绍MySQL性能优化总结(更新),对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

MySQL性能优化总结(更新)

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

只是性能优化方法的简单总结,每个内容都可以拓展开来讲,如果有什么问题,希望您能及时联系我。只有在讨论的过程中,知识才能进脑子,hhhhh!

常规优化

长连接导致的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 的结果返回的话,可能会导致后面的业务逻辑一起失败。
这篇关于MySQL性能优化总结(更新)的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!