mysql慢sql优化五个原则

发布时间: 2023-11-21 12:20 阅读: 文章来源:1MUMB3598PS

千里之堤,溃于蚁穴。一个在完美的架构,因为一个慢Sql,会导致系统直接崩溃。总结了一些解决慢sql的方法,供参考。

一、慢sql优化

订阅每日慢日志,优先解决调用次数多的慢sql,因慢sql优化的知识点非常多,只列举几个容易忽视的地方。

注意:

1、数据量不同,查询条件不同,sql使用的索引可能是不一样的,要构造多种查询条件去测试。

2、避免所有字段都返回,尽量使用覆盖索引,解决慢sql问题,终归是与库的磁盘IO、CPU做抗争。

3、避免隐式转换造成的索引无法使用问题。

4、控制好事务粒度,大事务不仅会严重影响数据库的吞吐量,CPU(死锁检测),也会造成主从的延迟,危害极大。

5、合理的设置数据库连接池的参数,设置sql语句的timeout,查询量大的地方,需要有降级开关。

6、新增功能,每一条sql语句,都要进行explain

7、所谓的慢sql,有些sql并不慢,而是坏sql,调用量低,数据量少的情况,并不慢,慢日志无法捕获。这个时候,需要对功能进行压测,压测需要注意两个问题:

a) 压测脚本的选择,如果使用固定的查询条件,会造成mysql命中缓存,或使用固定索引,压测效果不明显b) 压测数据库的操作,要逐渐放量,避免将库CPU打满,既要盯UMP的性能曲线,又要关注数据库CPU的使用率。二、读写分离

使用读写分离的方式,降低数据库的压力,读写分离能有效降低库的压力,但是其并不是银弹,使用时需注意以下问题。

注意:

1、主从延迟问题。读写分离后,无可避免的会有延迟问题,所以需要甄别好,哪些业务是对延迟敏感的,这类业务,需要继续查询主库。为尽量避免延迟问题,需注意以下几点:

a) 从库的压力,不能过大,如果资源允许,尽量主从的硬件资源相同。b) 避免使用大事务。c) 尽量避免大批量的删除、更新操作,尤其是无法使用索引的情况。

2、业务隔离,不同业务使用不同从库。识别出业务的黄金流程。重点业务与其他非重点业务使用不同的从库进行隔离。

三、架构调整,服务化改造,应用拆分

对库的操作,统一收口到应用的服务层,收口之后,sql语句集中,优化效果会事半功倍。

注意:

1、脱库改造,增加缓存。

a) 对于数据要求实时性不高的场景,并且为了快速的减少系统问题,可采取缓存read-through的方式,该方式系统改造量低,简单。但是要注意,避免不存在的key缓存穿透(不存在key设置特殊值、bloomfilter)。缓存雪崩问题。b) 数据异构,将依赖的底层数据通过binlake或双写等等方式,异构到jimdbc) 数据异构,将列表类或多条件复杂查询数据,异构到ES。查询需注意深分页及一次查询的数据量过多问题。

2、复杂的统计类功能,使用离线计算的方式,避免实时通过库函数进行计算统计

3、浏览记录、日志类或其他不重要功能,可通过mq,同步写转异步写

四、数据库垂直拆分,业务隔离

底层资源进行拆分,按业务维度,不同业务拆分为不同应用 ,使用不同的资源。

五、数据库水平拆分,分库分表

注意:

1、库水平拆分会出现很多问题,无法join,无法聚合查询,可采用异构数据到ES等方式解决。

2、将无用的历史数据进行归档。

六、不适合使用Mysql场景

Mysql数据库不适用的场景:

1、复杂、多字段、模糊查询

2、超大文本的存储(text类型)。大文本查询,会耗费mysql大量的内存空间,造成热数据被置换出去,查询效率降低

3、日志类大数量的存储

4、超高并发的查询

针对问题1,对于复杂、模糊查询等,更适合使用ES搜索引擎去处理。

a) 如果对数据的实时性要求不高,建议通过binlake或mq的方式,异步构建ES索引。b) 如果对数据实时性要求很高,可通过双写的方式处理,失败可以采用异步补偿的方式。另外ES本身段刷新有1秒的延迟,1s后数据才可搜索。如果不可接受并且数据修改频率低,可通过setRefresh方法强制刷新,立刻即可搜索到。写入量大的时候慎用。

针对问题2、3,建议使用nosql库,hbase、es等存储

针对问题4,简单查询,jimdb是非常好选择。如果有业务需要复杂查询,更建议使用ES多集群方式处理。

如果你对这个技术点有进一步了解和兴趣,或者是有什么更好的意见或者建议,欢迎加入微信群,一起来交流、吐槽。

加群方式:关注 私信 “进群 ” 即可进群并且可获取作者整理的最新相关架构学习资料

•••展开全文