MySql数据库优化查询工具

一、MySql数据库监控工具 Profile

• Profile工具
  用来统计SQL语句的执行时间。
• 条件
  • Profile工具
  • MySql数据库
• 步骤

  • 打开Profile

        # 打开Profile
        set profiling = 1;
    1
    2

  • 执行sql语句

        # 执行sql语句
    1

  • sql语句分析

        # sql分析
        show profiles;
    1
    2

    如图：
    

  • sql语句执行的每个过程的时间

         # sql语句执行的每个过程的时间
         show profile for query  5;
    1
    2

    如图：
    

  • 分析过程

        # 分析过程
        show profile all for query 5;
    1
    2

  • 关闭监控

         set profiling = 0;
    1

    如图：
    

  • 缺陷

    • 只能在mysql客户端对sql进行性能分析。
• 解决方案
  • 使用索引 sql语句的性能的提升全靠索引
  • 使用缓存
    • mysql本地缓存
    • 使用webapi [IMemoryCache]
    • 使用分布式缓存 [redis]

二、Sql查询技术工具 Explain [推荐使用]

• 目的
  主要用来分析sql语句是怎么优化的。
• 步骤

  • 开启 Explain

        explain 
          [sql语句];
        # 例如
        explain 
          select * from Table;
    1
    2
    3
    4
    5

    如图：
    

    • 表结构说明

      字段名称	备注
      id	sql执行顺序编号
      select_type	查询类型
      table	表名
      partitions	分区
      type	是否使用索引
      possible_keys	可能使用的索引名称
      key	最终决定要使用的索引名称
      key_len	查询索引使用的字节数，通常是越少越好 尽量在INT类型上添加索引
      ref	查询的列或常量
      rows	需要扫描的行数
      filtered	过滤
      extra	额外的信息

      • 表字段属性
        • select_type [查询类型]
          • 单表查询：SIMPLE;
          • 联合查询：PRIMARY[联合查询的第一个sql]；UNION[联合sql];
          • 子查询：PRIMARY[首先执行的sql语句];SUBQUERY[子查询];
          • 关联查询：PRIMARY[首先执行的sql语句];
          • DEPENDENT SUBQUERY[依赖子查询，必须主表查询出结果，副表依赖，不走索引，性能很慢]；
          • DEPENDENT UNION[跟UNION相同，但依赖外层查询结果];DERIVED[临时表；用于FROM 中的子查询]
        • type [是否使用索引]
          • ALL:全表扫描;
          • const:常量[性能最高 1对1 查询,设置成主键或者设置成unqiue索引];
          • ref:某字段使用索引；
          • eq_ref:联合查询的时候使用了索引；
          • range:[索引范围查找];
          • index:[组合索引]
    • 使用查询计划的目的
      • 知道目标数据是从多少行中查出来
      • 查询目标数据是否使用的索引
      • 执行了多少sql语句

三、Sql慢查询日志分析

• 慢查询的概念
  慢查询指的是记录执行时间比较长的sql语句。
• 步骤

  • 查询系统是否开启了慢查询

        show variables like '%slow_query_log%';
    1

    如图：
    

  • 开启慢查询

        #开启   [0:关闭  1：开启] 
        set global slow_query_log = 1;
    1
    2

    如图：
    

  • 可以过修改mysql配置文件设置慢查询的开启、文件的存储路径和监控时间，并重启mysql服务

  • 执行慢SQL语句

• 缺陷
  • 在生产环境中并发量高了，生成很多的sql日志，排查慢sql会很麻烦。