• 【教3妹学mysql】一条慢sql如何排查优化


    插: 前些天发现了一个巨牛的人工智能学习网站,通俗易懂,风趣幽默,忍不住分享一下给大家。点击跳转到网站。 
    坚持不懈,越努力越幸运,大家一起学习鸭~~~

    3妹

    2哥:3妹,干嘛呢,你有没有看到新闻。成都的核酸系统“崩了”。
    3妹:看到了,程序员又要被锅了。遇到系统突然“崩了”,该如何排查呢?
    2哥:哈哈,3妹真是好学,emm,不过这个问题有点难,涉及的内容也比较多,我们后面再说。
    3妹:切,2哥也有不懂的时候啊。
    2哥:先说说你在干嘛呢。
    3妹:还在学mysql呢,我们公司**统计之前还好好的, 现在数据量上来了,查询总是很慢,在想如何排查呢。
    2哥:虽然我不太懂网络的原理,但是Mysql慢查询这块我知道啊。让我给你分析分析。

    讲课

    我认为,一个 SQL 执行的很慢,我们要分两种情况讨论:

    1、大多数情况下很正常,偶尔很慢,则有如下原因

    (1)、数据库在刷新脏页,例如 redo log 写满了需要同步到磁盘。

    (2)、执行的时候,遇到锁,如表锁、行锁。

    (3)、sql写的太烂

    2、这条 SQL 语句一直执行的很慢,则有如下原因:

    (1)、没有用上索引或则索引失效:例如该字段没有索引;或则由于对字段进行运算、函数操作导致无法用索引。

    (2)、有索引可能会走全表扫描

    怎样判断是否走全表扫描:

    索引区分度(索引的值不同越多,区分度越高),称为基数,而数据量大时不可能全部扫描一遍得到基数,而是采样部分数据进行预测,那有可能预测错了,导致走全表扫描。

    慢sql优化

    01数据库中设置SQL慢查询

    方式一:修改配置文件 在 my.ini 增加几行: 主要是慢查询的定义时间(超过2秒就是慢查询),以及慢查询log日志记录( slow_query_log)
    image.png

    方式二:通过MySQL数据库开启慢查询:
    image.png

    02分析慢查询日志

    可以通过如下命令定位低效率执行sql
    show processlist;# sql 可以用 **explain **分析执行计划。

    对于执行计划的分析,也是面试官喜欢考察的一个点。面试官:你是怎样使用explain的?对于执行计划你是怎样分析的?

    这里简单带过,之后会详细解答。

    主要关注这几个字段即可:

    type:表示MySQL在表中找到所需行的方式,或者叫访问类型

    • type=ALL,全表扫描,MySQL遍历全表来找到匹配行

    • type=index,索引全扫描

    • type=range,索引范围扫描

    • type=eq_ref,唯一索引

    • type=NULL,MySQL不用访问表或者索引,直接就能够得到结果(性能最好)

    possible_keys: 表示查询可能使用的索引

    key: 实际使用的索引

    key_len: 使用索引字段的长度

    rows: 扫描行的数量

    Extra

    • using index:覆盖索引,不回表

    • using where:回表查询

    • using filesort:需要额外的排序,不能通过索引得到排序结果

    03慢sql如何优化

    对于MYSQL慢sql语句的优化,我们也可以分几个方面来进行分析(基本覆盖全面啦):

    面试从这几方面考虑:索引+sql语句+数据库结构优化+优化器优化+架构优化。

    索引
    • 尽量覆盖索引,5.6支持索引下推

    • 组合索引符合最左匹配原则

    • 避免索引失效

    • 在写多读少的场景下,可以选择普通索引而不要唯一索引。更新时,普通索引可以使用change buffer进行优化,减少磁盘IO,将更新操作记录到change bufer,等查询来了将数据读到内存再进行修改.

    • 索引建立原则(一般建在where和order by,基数要大,区分度要高,不要过度索引,外键建索引)

    sql语句

    1、分页查询优化

    该方案适用于主键自增的表,可以把Limit查询转换成某个位置的查询。

    select * from tb_sku where id>20000 limit 10;

    2、优化insert语句

    • 多条插入语句写成一条

    • 在事务中插数据

    • 数据有序插入(主键索引)

    数据库结构优化

    1、将字段多的表分解成多个表有些字段使用频率高,有些低,数据量大时,会由于使用频率低的存在而变慢,可以考虑分开。

    2、对于经常联合查询的表,可以考虑建立中间表

    优化器优化

    1、优化器使用MRR

    原理:MRR 【Multi-Range Read】将ID或键值读到buffer排序,通过把「随机磁盘读」,转化为「顺序磁盘读」,减少磁盘IO,从而提高了索引查询的性能。

    开启mrr:

    image.png

    对于 Myisam,在去磁盘获取完整数据之前,会先按照 rowid 排好序,再去顺序的读取磁盘。

    对于 Innodb,则会按照聚簇索引键值排好序,再顺序的读取聚簇索引。

    **磁盘预读:**请求一页的数据时,可以把后面几页的数据也一起返回,放到数据缓冲池中,这样如果下次刚好需要下一页的数据,就不再需要到磁盘读取(局部性原理)

    索引本身就是为了减少磁盘 IO,加快查询,而 MRR,则是把索引减少磁盘 IO 的作用,进一步放大

    https://zhuanlan.zhihu.com/p/148680235

    架构优化

    读/写分离(主库写,从库读)

    总结:

    1、先设置慢查询(my.ini或数据库命令)

    2、分析慢查询日志

    3、定位低效率sql(show processlist)

    4、explain分析执行计划(是否索引失效,用到索引没,用了哪些)

    5、优化(索引+sql语句+数据库结构优化+优化器优化+架构优化)

  • 相关阅读:
    CMOS反相器的工作原理和电路结构
    产品如何通过网络营销推广提升销售量?
    微信小程序 rpx 转 px
    Java 中经常被提到的 SPI 到底是什么?
    SpringBootWeb案例——Tlias智能学习辅助系统(2)
    C++的struct和class的区别
    代码随想录刷题| 01背包理论基础 LeetCode 416. 分割等和子集
    Maven进阶-属性与资源文件
    Servlet的注册和生命周期
    计算机毕业设计 SSM在线心理评测系统 心理咨询系统 在线心理测试系统Java Vue MySQL数据库 远程调试 代码讲解
  • 原文地址:https://blog.csdn.net/kangbin825/article/details/126679911