查询性能优化

简单衡量查询开销的指标

  • 响应时间
  • 扫面的行数

  • 返回的行数

    EXPLAIN 计划

    使用 explain

      explain 计划提供了关于查询优化器如何执行查询的信息

    使用 explain json

      以 JSON 格式使用 explain 计划，能提供有关查询执行情况的完整信息

    使用 explain 进行连接

      可以为正在进行的计划执行 explain 计划，需指定 connection ID

    基准查询和服务器

    使用 mysqlslap 工具，模拟MySQL服务器的客户端负载，并报告每个阶段所耗费的时间，就像多个客户端正在访问该服务器一样。

    索引优化

    添加索引

      MySQL可以使用索引来筛选WHERE、ORDER BY、GROUP BY子句中的行，也可以使用索引来连接表，一个列上有多个索引，MySQL会选择给出了最多筛选行的索引。
      执行 ALTER TABLE 命令进行索引的添加和删除。

    主键

       Ⅰ 主键是 UNIQUE（唯一）和 NOT NULL（非空）的
       Ⅱ 选择最小的可能键，因为所有的二级索引都会存储主键，主键太大，整个索引也会占用很多的空间
       Ⅲ 选择一个单调递增的值。物理是实根据主键排序的
       Ⅳ 最好选择一个主键。

    添加索引

    主键索引：ALTER TABLE table_name ADD PRIMARY KEY ( column )
    唯一索引：ALTER TABLE table_name ADD UNIQUE (column )
    普通索引：ALTER TABLE table_name ADD INDEX index_name ( column )
    全文索引：ALTER TABLE table_name ADD FULLTEXT ( column )
    多列索引：ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3 )

    不可见索引

    可以将未使用的索引设置为不可见，需要使用时标记为可见，不使用时可以删除它。
    操作方式：ALTER TABLE table_name ALTER INDEX index_name INVISIBLE

    降序索引

    在 MySQL 8之前，索引的定义中可以包含顺序（升序、降序），但它只是被解析并没有实现，索引值始终以升序存储。MySQL 8引入了对降序索引的支持。索引定义中指定索引的顺序不会被忽略。降序索引实际上按降序存储关键值，对于降序索引，反向扫描升序索引效率不高。
    降序索引对同时具有 ORDER BY 子句的查询很有用。

    pt-query-digest

    pt-query-digest 是Percona 的一部分，用于对查询进行分析，可以通过以下任何方式收集查询：
      慢查询日志：sudo pt-query-digest /var/lib/mysql/mysql-slow.log > query_digest
      通用查询日志：sudo pt-query-digest --type genlog /var/lib/mysql/mysql-slow.log > general_query_digest
      进程列表：
      二进制日志
      TCP转储

    优化表

    优化数据类型

    表的最优设计原则：既能保存所有可能值，同时在磁盘上占用的空间最小
    占用空间最少的情况：

  • 向磁盘写入或读取的数据越少，插入或查询的速度就越快

  • 在处理查询时，磁盘上的内容会被加载到主内存中，所以，表越小，占用的内存空间就越小
  • 被索引占用的内存空间就越小

    分析表

    **analyze table tb_name**
    用于分析和存储表的关键字分布，分析的结果将可以使得系统得到准确的统计信息，使得 SQL 能够生成正确的执行计划。
    在分析期间，使用一个读取锁定对表进行锁定。这对于 MyISAM, BDB 和 InnoDB 表有作用。

    检查表

    **check table tb_name**
    检查表的作用是检查一个或多个表是否有错误。CHECK TABLE对MyISAM 和InnoDB表有作用。
    CHECK TABLE 也可以检查视图是否有错误，如在视图定义中被引用的表已不存在等情况。

    优化表

    **optimize table ta_name**
    该命令可以将表中的空间碎片进行合并，并且可以消除由于删除或者更新造成的空间浪费，但OPTIMIZE TABLE 命令只对 MyISAM、BDB 和 InnoDB 表起作用。

    如果已经删除了表的一大部分，或者如果已经对含有可变长度行的表（含有 VARCHAR、BLOB 或 TEXT 列的表）进行了很多更改，则应使用 OPTIMIZE TABLE 命令来进行表优化。

注意：ANALYZE、CHECK、OPTIMIZE 执行期间将对表进行锁定，因此一定注意要在数据库不繁忙的时候执行相关的操作。

MySQL优化器

作用

找到查询最好的执行计划

优化类型

  • 重新定义关联表的顺序

数据库表的关联顺序并不总是按照在查询中指定的顺序进行

  • 将外连接转为内连接

并不是所有的OUT JOIN语句都必须以外连接的方式执行

  • 使用等价变换规则

MySQL可以使用一些等价变换来简化并规范表达式

  • 优化COUNT()、MIN()、MAX()

索引和列是否可空通常可以帮助MySQL优化这类表达式

  • 预估并转换为常数表达式

当MySQL检测到一个表达式可以转化为常数的时候，就会一直把该表达式作为常数进行优化处理

  • 覆盖索引扫描

当索引中的列包含查询所有查询中需要使用的列时侯，MySQL就可以使用索引返回需要的数据，而无需查询对应的数据行

  • 子查询优化

MySQL在某种情况下可以将子查询转换一种效率更高的形式，从而减少多个子查询多次对数据库进行访问

  • 提前终止查询

在发现已经满足查询需求的时候，MySQL总是能够立刻终止查询

  • 等值传播

如果两个列的值是通过等式关联，那么MySQL能把一个列的where条件传递给另一个列

  • 列表IN()的比较

MySQL先将IN中的数据先进行排序，然后通过二分法查找的方式来确定列中的值是否满足条件，这是一个O(log n) 复杂的操作，等价的转换成OR的查询复杂度为O(n)，对于IN中MySQL有大量取值的时候，MySQL的处理速度会更快。
……

应用性能优化