Mysql调优:表连接优化

作者: 魔镜的技术心经 | 来源:发表于2018-09-10 21:43 被阅读3次

    Mysql存储引擎

    • MyISAM,不支持事务、也不支持外键,但其访问速度快,对事务完整性没有要求。
    • InnoDB,提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MyISAM存储引擎,InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。
    • MEMORY, 使用存在内存中的内容来创建表。 每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常得快,因为它的数据是放在内存中的,并且默认使用HASH索引。
      image.png

    表连接的类型

    • Left Join
    • Right Join
    • Inner Join
    • Full Join(Do not support)
    image.png

    临时表的使用

    一旦涉及排序和分组,就很大可能会使用临时表,而临时表分两类:

    • 内存临时表 - MEMORY Engine
    • 磁盘临时表 - MyISAM Engine

    根据临时表的大小(tmp_table_size),mysql会决定使用哪种临时表;众所周知,内存的IO速度远远超过了磁盘的IO速度,所以在做优化的时候,尽量避免使用磁盘临时表的情况。

    常见优化方式

    • 先定位慢查询 show status like ‘slow_queries’;
    • 使用explain查看SQL的执行计划,如果出现Using temporary的字段,就意味着Query使用了临时表,如下图:
    image.png
    • Order by或者Group by的字段建立索引
    • 拆分SQL语句,将查询与排序进行分离,为了减少临时表的大小,可以先找到符合条件的查询结果集,然后通过IN或者NOT IN的方式连接在一起。
    • 业务变通,了解业务的真正需求,比如查询是否一定要实时,数据是否需要强一致性,是否可以以空间换时间的做法解决问题。
      • 物化视图,预先将需要关联或者汇总的数据,通过实体表存放起来,避免Join查询
      • 增加冗余字段,减少连表查询
      • 增加冗余表,比如统计汇总表
      • 缓存, 数据库缓存或者程序的缓存
      • 增加索引
    • 应用层的优化
      • 业务代码进行结果集的组装(排序或者分组),减少数据库的Join。
      • 将不变的数据,从数据库读取出来后,就保存到内存中,直接调用相应的静态方法或者Map,减少和数据库的交互次数。
    • 架构层面优化
      • 读写分离,通过slave节点,分担读的压力,提高性能(与硬件升级类似)
      • 分库分表,会使Join更加复杂和麻烦(特别是如何保证高效的分布式事务一致性),不到万不得已,千万别使用。
      • Join太多,是否意味着这种多表联合查询,放在大数据分析里面会更合适?

    参考

    相关文章

      网友评论

        本文标题:Mysql调优:表连接优化

        本文链接:https://www.haomeiwen.com/subject/geuegftx.html