注: 支持版本MySQL8.0

第一章 MySQL架构

MySQL的逻辑架构

  • 最上层的客户端锁包含的服务并不是MySQL独有的,大多数基于网络的客户端/服务器工具或服务器都有类似的服务,包括连接处理,身份验证,确保安全性等.
  • 第二层是比较有意思的部分.大多数MySQL的核心功能都在这一层,包括查询解析,分析,优化,以及所有的内置函数(例如,日期,时间,数学和加密函数),所有跨存储引擎的功能也在这一层实现:存储过程,触发器,视图等.
  • 第三层是存储引擎层.存储引擎负责MySQL中数据的存储和提取.服务器通过存储引擎API进行通信.这些API屏蔽了不同存储引擎之间的差异,使得他们对上面的查询层基本上是透明的.存储引擎层还包含几个底层函数,用于执行诸如"开始一个事务"或者"根据主键提取一行记录"等操作.但存储引擎不会去解析SQL,不同存储引擎之间也不会相互通信,而只是简单地响应服务器的请求.
连接管理和安全性
  • 默认情况下,每个客户端连接都会在服务器进程中拥有一个线程,该连接的查询只会在这个单独的线程中执行,该线程驻留在一个内核或者CPU上.
  • 服务器维护了一个缓存区,用于存放已就绪的线程,因此不需要为每个新的连接创建或者销毁线程.
  • 当客户端连接到MySQL服务器是,服务器需要对其进行身份验证.身份验证基于用户名,发起的主机名和密码.如果以跨传输层安全(TLS)的方式连接,还可以使用X.509证书认证.
  • 客户端连接成功后,服务器会继续验证该客户端是否具有其发出的每个查询的权限.
优化与执行
  • MySQL解析查询以创建内部数据结构(解析树),然后对其进行各种优化,包括重写查询,决定表的读取顺序,以及选择合适的索引等.
  • 优化器并不关心表使用的是什么存储引擎,但存储引擎对于查询优化有影响的.优化器会向存储引擎询问它的一些功能,某个具体操作的成本,以及表数据的统计信息.

并发控制

读写锁
  • 并发控制这一经典问题的解决方案相当简单.处理并发读/写访问的系统通常实现一个由两种锁类型组成的锁系统.这两种锁通常被称为共享锁(shared lock)和排他锁(exclusive lock),也叫读锁(read lock)和写锁(write lock)
  • 锁的概念可以如下描述: 资源商的读锁是共享的,或者说是相互不阻塞的.多个客户端可以同时读取同一个资源而不像不干扰.写锁则是排他的,也就是说,一个写锁即会阻塞读锁也会阻塞其他的写锁,这是处于安全策略的考虑,只有这样才能确保在特定的时间点只有一个客户端能执行写入,并防止其他客户端读取正在写入的资源.
锁的粒度
  • 一种提高共享资源并发性的方式就是让锁定的对象更有选择性.
  • 锁是数据库实现一致性保证的方法.
  • 表锁(table lock)是MySQL中最基本也是开销最小的锁策略.
  • 行级锁,使用行级锁(row lock)可以最大程度地支持并发处理(也带来了最大的锁开销),这种策略允许多人同时编辑不同的行,而不会阻塞彼此.这使得服务器可以执行更多的并发写操作,带来的代价则是需要承担更多的开销,以跟踪谁拥有这些行级锁,已经锁定了多长时间,行级锁的类型,以及合适该清理不再需要的行级锁.
    • 行级锁是在存储引擎而不是服务器中实现的.服务器通常不清楚存储引擎的实现方式.

事务

  • 事务就是一组SQL语句,作为一个工作单元以原子的方式进行处理.如果数据库引擎能够成功地对数据库应用整组语句,那么就执行该语句.如果其中任何一条语句因为崩溃或其他原因无法执行,那么整租语句都不执行.即,作为事务的一组语句,要么全部执行,要么全部执行失败.
ACID
隔离级别
死锁
  • 为了解决死锁问题,数据系统实现了各种死锁检测和锁超时机制.更复杂的系统,比如InnoDB存储引擎,检测到循环依赖后会立即返回一个错误信息.这可能是一件好事,否则死锁将表现为非常缓慢的查询.
  • 还有一种方式,当锁等待超过超时的时间限制后直接终止查询,这样做通常不太好.InnoDB目前处理死锁的方式是将持有最少行级排它锁的事务回滚(这是一种最容易回滚的近似算法).
  • 一旦发生死锁,如果不回滚其中一个事务(部分或全部),就无法打破死锁.对于事务型的系统,这是无法避免的,所以应用程序在设计时必须考虑如何处理死锁.大多数情况下只需要重新从头开始执行被回滚的事务即可,除非又遇到另一个死锁.
事务日志
  • 事务日志有助于提高事务的效率.存储引擎只需要更改内存中的数据副本,而不用每次修改磁盘中的表,这会非常快.然后再把更改的记录写入事务日志中,事务日志会被持久化 保存在硬盘上.因为事务日志采用的是追加写操作,是在硬盘中一小块区域内的顺序I/O,而不是需要写多个地方的随机I/O,所以写入事务日志是一种相对较快的操作.最后会有一个后台进程在某个时间去更新硬盘中的表.因此,大多数使用这种技术(write-ahead logging,预写式日志)的存储引擎修改数据最终需要写入磁盘两次.如果修改操作已经写入事务日志,那么即使系统在数据本身写入硬盘之前发生崩溃,存储引擎仍可在重新启动时恢复更改.具体的恢复方法则因存储引擎而异.
理解AUTOCOMMIT

  • 默认情况下,单个INSERT、UPDATE或DELETE语句会被隐式包装在一个事务中并在执 行成功后立即提交,这称为自动提交(AUTOCOMMIT)模式.通过禁用此模式,可以在 事务中执行一系列语句,并在结束时执行COMMIT提交事务或ROLLBACK回滚事务. 在当前连接中,可以使用SET命令设置AUTOCOMMIT变量来启用或禁用自动提交模式. 启用可以设置为1或者ON,禁用可以设置为0或者OFF.如果设置了AUTOCOMMIT=0,则当前连接总是会处于某个事务中,直到发出COMMIT或者ROLLBACK,然后MySQL会立即启动一个新的事务.此外,当启用``AUTOCOMMIT时,也可以使用关键字BEGIN或者START TRANSACTION来开始一个多语句的事务.修改AUTOCOMMIT`的值对非事务型的表不会有任何影响,这些表没有COMMIT或者ROLLBACK的概念.

  • 还有一些命令,当在活动的事务中发出时,会导致MySQL在事务的所有语句执行完毕前提交当前事务.这些通常是进行重大更改的DDL命令,如ALTER TABLE,但LOCK TABLES和其他一些语句也具有同样的效果.有关会导致自动提交事务的完整命令列表,请查看对应版本的官方文档.

  • MySQL可以通过执行SET TRANSACTION ISOLATION LEVEL命令来设置隔离级别.新的隔离级别会在下一个事务开始的时候生效.可以在配置文件中设置整个服务器的隔离级别,也可以只改变当前会话的隔离级别.

    1
    SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
在事务中混合使用存储引擎
  • MySQL不再服务器层管理事务,事务是由下层的存储引擎实现的.所以在同一个事务中,混合使用多种存储引擎是不可靠的.
隐式锁定和显示锁定

  • InnoDB使用两阶段锁定协议(two-phase locking protocol).在事务执行期间,随时都可以获取锁,但锁只有在提交或回滚后才会释放,并且所有的锁会同时释放.前面描述的锁定机制都是隐式的.InnoDB会根据隔离级别自动处理锁.

  • 另外,InnoDB还支持通过特定的语句进行显式锁定,这些语句不属于SQL规范：

    1
    2
    SELECT ... FOR SHARE
    SELECT ... FOR UPDATE

  • MySQL还支持LOCK TABLES和UNLOCK TABLES命令,这些命令在服务器级别而不在存储引擎中实现.如果需要事务,应该使用支持事务的存储引擎.因为InnoDB支持行级锁,所以没必要使用LOCK TABLES.

  • LOCK TABLES命令和事务之间的交互非常复杂,并且在一些服务器版本中存在意想不到的行为.因此,本书建议,除了在禁用AUTOCOMMIT的事务中可以使用之外,其他任何时候都不要显式地执行LOCK TABLES,不管使用的是什么存储引擎

多版本并发控制

  • MVCC的工作原理是使用数据在某个时间点的快照来实现的.这意味着,无论事务运行多长时间,都可以看到数据的一致视图,也意味着不同的事务可以在同一时间看到同一张表中的不同数据.
  • InnoDB通过为每个事务在启动时分配一个事务ID来实现MVCC.该ID在事务首次读取任何数据时分配.在该事务中修改记录时,将向Undo日志写入一条说明如何恢复该更改的Undo记录,并且事务的回滚指针指向该Undo日志记录.这就是事务如何在需要时执行回滚的方法.
  • 值得注意的是,所有Undo日志写入也都会写入Redo日志,因为Undo日志写入是服务器崩溃恢复过程的一部分,并且是事务性的(Undo Log会产生Redo Log,也就是Undo Log的产生会伴随着Redo Log的产生,这是因为Undo Log也需要持久性的保护.).这些Redo日志和Undo日志的大小也是高并发事务工作机制中的重要影响因素.
  • MVCC仅适用于REPEATABLE READ和READ COMMITTED隔离级别.READ UNCOMMITTED与MVCC不兼容,是因为查询不会读取适合其事务版本的行版本,而是不管怎样都读最新版本.SERIALIZABLE与MVCC也不兼容,是因为读取会锁定它们返回的每一行

InnoDB引擎

  • InnoDB使用MVCC来实现高并发性,并实现了所有4个SQL标准隔离级别.InnoDB默认为REPEATABLE READ隔离级别,并且通过间隙锁(next-key locking)策略来防止在这个隔离级别上的幻读：InnoDB不只锁定在查询中涉及的行,还会对索引结构中的间隙进行锁定,以防止幻行被插入.
  • InnoDB表是基于聚簇索引构建的.InnoDB的索引结构与MySQL其他大部分存储引擎有很大的不同.聚簇索引提供了非常快速的主键查找.但是,因为二级索引(secondary index,非主键索引)需要包含主键列,如果主键较大,则其他索引也会很大.如果表中的索引较多,主键应当尽量小

原子DDL

  • MySQL 8.0引入了原子数据定义更改.这意味着数据定义语句现在要么全部成功完成,要么全部失败回滚.这是通过创建DDL特定的Undo日志和Redo日志来实现的,InnoDB便依赖这两种日志来跟踪变更——这是InnoDB经过验证的设计,已经扩展到MySQL服务器的操作中

第二章 可靠性工程世界中的监控

Availability level Allowed unavailability window
per year per quarter per month per week per day per hour
90% 36.5 days 9 days 3 days 16.8 hours 2.4 hours 6 minutes
95% 18.25 days 4.5 days 1.5 days 8.4 hours 1.2 hours 3 minutes
99% 3.65 days 21.6 hours 7.2 hours 1.68 hours 14.4 minutes 36 seconds
99.5% 1.83 days 10.8 hours 3.6 hours 50.4 minutes 7.20 minutes 18 seconds
99.9% 8.76 hours 2.16 hours 43.2 minutes 10.1 minutes 1.44 minutes 3.6 seconds
99.95% 4.38 hours 1.08 hours 21.6 minutes 5.04 minutes 43.2 seconds 1.8 seconds
99.99% 52.6 minutes 12.96 minutes 4.32 minutes 60.5 seconds 8.64 seconds 0.36 seconds
99.999% 5.26 minutes 1.30 minutes 25.9 seconds 6.05 seconds 0.87 seconds 0.04 seconds

第三章 Performance Schema

  • 程序插桩(instrument).程序插桩在MySQL代码中插入探测代码,以获取我们想了解的信息.

  • 消费者表(consumer),指的是存储关于程序插桩代码信息的表.如果我们为查询模块添加插桩,相应的消费者表将记录诸如执行总数、未使用索引的次数、花费的时间等信息.

插桩元件

  • 在performance_schema中,setup_instruments表包含所有支持的插桩的列表.所有插桩的名称都由用斜杠分隔的部件组成.下面的例子展示了插桩的命名规则：

    • statement/sql/select

    • wait/synch/mutex/innodb/autoinc_mutex

  • 插桩名称的最左边部分表示插桩的类型.因此,statement表示插桩类型是statement,wait表示插桩类型是wait,以此类推.名称字段中的其余部分从左至右依次表示从通用到特定的子系统.在前面的示例中,select是sql子系统的一部分,属于statement类型.或者autoinc_mutex属于innodb,它是更通用的插桩类mutex的一部分,而mutex又是更通用的插桩类型wait的sync插桩的一部分.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
mysql> SELECT * FROM performance_schema.setup_instruments WHERE DOCUMENTATION IS NOT NULL LIMIT 5,5\G
*************************** 1. row ***************************
         NAME: wait/synch/mutex/sql/LOCK_authentication_policy
      ENABLED: NO
        TIMED: NO
   PROPERTIES: singleton
        FLAGS: NULL
   VOLATILITY: 0
DOCUMENTATION: A lock to ensure execution of CREATE USER or ALTER USER sql and SET @@global.authentication_policy variable are serialized
*************************** 2. row ***************************
         NAME: wait/synch/mutex/refcache/refcache_channel_mutex
      ENABLED: NO
        TIMED: NO
   PROPERTIES: 
        FLAGS: NULL
   VOLATILITY: 0
DOCUMENTATION: A mutex to guard access to the channels list
*************************** 3. row ***************************
         NAME: wait/synch/mutex/innodb/ahi_enabled_mutex
      ENABLED: NO
        TIMED: NO
   PROPERTIES: 
        FLAGS: NULL
   VOLATILITY: 0
DOCUMENTATION: Mutex used for AHI disabling and enabling.
*************************** 4. row ***************************
         NAME: wait/synch/rwlock/pfs/LOCK_pfs_tls_channels
      ENABLED: NO
        TIMED: NO
   PROPERTIES: singleton
        FLAGS: NULL
   VOLATILITY: 0
DOCUMENTATION: This lock protects list of instrumented TLS channels.
*************************** 5. row ***************************
         NAME: wait/synch/rwlock/sql/LOCK_thd_store_data
      ENABLED: NO
        TIMED: NO
   PROPERTIES: singleton
        FLAGS: NULL
   VOLATILITY: 0
DOCUMENTATION: RW Lock protecting structure required for THD store service
5 rows in set (0.00 sec)

消费者表的组织

  • 消费者表是插桩发送信息的目的地.测量结果存储在Performance Schema数据库的多个表中；事实上,MySQL 8.0.25社区版的performance_schema中包含110个表.基于它们的用途,可分为以下几个类别
当前和历史数据

  • 存放事件的表名包含如下结尾

    标识 说明
    *_current 当前服务器上进行中的事件
    *_history 每个线程最近完成的10个事件
    *_history_long 从全局来看,每个线程最近完成的10000个事件
    • *_history和*_history_long表的大小是可配置的

  • 当前和历史数据

    标识 说明
    events_waits 底层服务器等待,例如获取互斥对象
    events_statements SQL查询语句
    events_stages 配置文件信息,例如创建临时表或发送数据
    events_transactions 事务
汇总表和摘要

  • 汇总表保存有关该表所建议的内容的聚合信息.例如,memory_summary_by_thread_by_event_name表保存了用户连接或任何后台线程的每个MySQL线程的聚合内存使用情况.

  • 摘要是一种通过删除查询中的变量来聚合查询的方法.例如以下查询

    1
    2
    3
    SELECT user,birthdate FROM users WHERE user_id=19;
    SELECT user,birthdate FROM users WHERE user_id=20;
    SELECT user,birthdate FROM users WHERE user_id=21;

    • 该查询的摘要是:

      1
      SELECT user,birthdate FROM users WHERE user_id=?;

  • 这允许Performance Schema跟踪摘要的延迟等指标,而无需单独保留查询的每个变体

实例表(Instance)
  • 实例是指对象实例,用于MySQL安装程序.例如,file_instances表包含文件名和访问这些文件的线程数.
设置表(Setup)
  • 设置表用于performance_schema的运行时设置.

sys schema

  • sys schema它全部基于performance_schema上的视图和存储例程组成.它的设计目的是让performance_schema体验更加流畅,它本身并不存储任何数据.

理解线程

  • 每个线程至少有两个唯一标识符:一个是操作系统线程ID,另一个是MySQL内部线程ID.

    • 操作系统线程ID可以通过相关工具查看,如在Linux系统中可以使用ps -ef命令查看.

    • MySQL内部线程ID在大多数performance_schema表以THREAD_ID命名.此外,每个前台线程都有一个指定的PROCESSLIST_ID(连接标识符),在SHOW PROCESSLIST命令输出中或者在MySQL命令行客户端连接是"Your MySQL connection id is"字符串中可以看到.

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      Welcome to the MySQL monitor.  Commands end with ; or \g.
      Your MySQL connection id is 26
      Server version: 8.0.33 MySQL Community Server - GPL

      Copyright (c) 2000, 2023, Oracle and/or its affiliates.

      Oracle is a registered trademark of Oracle Corporation and/or its
      affiliates. Other names may be trademarks of their respective
      owners.

      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

      mysql>

    • THREAD_ID不等于PROCESSLIST_ID

  • performance_schema中的threads表包含了服务器中存在的所有线程.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    mysql> SELECT NAME,THREAD_ID,PROCESSLIST_ID,THREAD_OS_ID FROM performance_schema.threads;
    +---------------------------------------------+-----------+----------------+--------------+
    | NAME                                        | THREAD_ID | PROCESSLIST_ID | THREAD_OS_ID |
    +---------------------------------------------+-----------+----------------+--------------+
    | thread/sql/main                             |         1 |           NULL |            1 |
    | thread/innodb/io_ibuf_thread                |         3 |           NULL |           85 |
    | thread/innodb/io_read_thread                |         4 |           NULL |           86 |
    | thread/innodb/io_read_thread                |         5 |           NULL |           87 |
    | thread/innodb/io_read_thread                |         6 |           NULL |             119 |
    | thread/innodb/clone_gtid_thread             |        38 |           NULL |       ...
    | thread/sql/event_scheduler                  |        43 |              5 |          125 |
    | thread/sql/signal_handler                   |        44 |           NULL |          126 |
    | thread/mysqlx/acceptor_network              |        45 |           NULL |          127 |
    | thread/sql/compress_gtid_table              |        47 |              7 |          129 |
    | thread/sql/one_connection                   |        63 |             23 |          138 |
    | thread/sql/one_connection                   |        64 |             24 |          137 |
    | thread/sql/one_connection                   |        66 |             26 |          162 |
    +---------------------------------------------+-----------+----------------+--------------+
    40 rows in set (0.00 sec)
    • performance_schema到处使用THREAD_ID,而PROCESSLIST_ID只在threads表中可用.如果需要获取PROCESSLIST_ID,例如,要杀死持有锁的连接,则需要查询threads表来获取.

配置

启用或者禁用performance_schema
1
2
3
4
5
6
7
mysql> show variables like 'performance_schema';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+
1 row in set (0.01 sec)
启用或禁用插桩

  • 可以通过setup_instruments表查看插桩的状态

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    mysql> SELECT * FROM performance_schema.setup_instruments WHERE NAME='statement/sql/select'\G
    *************************** 1. row ***************************
             NAME: statement/sql/select
          ENABLED: YES
            TIMED: YES
       PROPERTIES: 
            FLAGS: NULL
       VOLATILITY: 0
    DOCUMENTATION: NULL
    1 row in set (0.00 sec)

  • 有三个方法可用于启用或禁用performance_schema插桩

    • 使用setup_instruments表

      1
      UPDATE performance_schema.setup_instruments SET ENABLED='YES' WHERE NAME='statement/sql/select';
      • 数据库重启后会失效

    • 使用sys_schema中的ps_setup_enable_instrument存储过程和ps_setup_disable_instrument

      1
      2
      3
      4
      5
      6
      7
      mysql> CALL sys.ps_setup_enable_instrument('statement/sql/select');
      +-----------------------+
      | summary               |
      +-----------------------+
      | Enabled 0 instruments |
      +-----------------------+
      1 row in set (0.01 sec)
      • 数据库重启后会失效

    • 使用performance-schema-instrument启动参数

      • 如果要在重启之后保留特定插桩的配置,需要使用performance-schema-instrument配置参数.
        这个变量支持performance-schema-instrument='instrument_name=value'这样的语法.其中,
        instrument_name是插桩名称,值为ON、TRUE或1表示启用,值为OFF、FALSE或0表示禁
        用.对于那些计数统计而不是计时统计的插桩,可以多次指定此选项以启用或禁用不同的
        插桩.该选项还支持通配符:performance-schema-instrument='statement/sql/select=ON'
      • 如果指定了多个选项,则无论顺序如何,较长的插桩字符串优先于较短的插桩字符串.
启用或禁用消费者表
  • 消费者表也可以通过一下三种方式启用或禁用
    • 使用setup_consumers表
    • 使用sys_schema中的ps_setup_enable_consumer存储过程和ps_setup_disable_consumer
    • 使用performance-schema-consumer启动参数

使用Performance Schema

检查SQL语句
插桩类 描述
statement/sql SQL语句,如SELECT,或者CREATE TABLE
statement/sp 存储过程控制
statement/scheduler 事件调度器
statement/com 命令,如quit,kill,DROP DATABASE或者Binlog Dump.有些命令是用户不可用的,只能由mysqld进程调用
statement/abstract 包括四类命令: clone,Query,new_packet和relay_log
常规SQL语句
  • Performance Schema将语句指标存储在events_statements_current、events_statements_history和events_statements_history_long表中.这三个表具有相同的结构

第四章 操作系统和硬件优化

平衡内存和磁盘资源

缓存,读取和写入
  • 如果有足够的内存,可以完全避开磁盘读取操作.如果所有数据都能装入内存,那么一旦服务器的缓存预热完成,每次读取都将是一次缓存命中.在这种情况下,仍然会从内存中进行逻辑读取,但不会从磁盘中进行物理读取.然而,写入是另一回事.写入可以像读取一样在内存中执行,但迟早必须被写入磁盘,才能持久保留数据.换句话说,缓存可以延迟写操作,但缓存不能像消除读操作那样消除写操作.
  • 事实上,除了允许写操作延迟之外,缓存还允许它们以两种重要的方式组合在一起
  • 多次写操作,一次刷新
    • 一个数据片段可以在内存中被多次更改,而无须每一次都将新值写入磁盘.当数据被最终刷新到磁盘时,自上次物理写入以来发生的所有修改都将被持久化.例如,许多语句可以更新内存中的计数器.如果计数器被更新了100次,然后写入磁盘,则100次内存修改被合并为一次磁盘写入.
  • I/O合并
    • 许多不同的数据片段可以在内存中被修改,这些修改可以被收集在一起,因此物理写可以作为单个磁盘操作执行.
  • 这就是为什么许多事务系统使用提前写日志(write-ahead logging)策略的原因.提前写日志允许在内存中更改页面,而不用将更改刷新到磁盘,这通常涉及随机I/O,速度非常慢.相反,它们将更改的记录写入顺序日志文件,这样要快得多.后台线程可以稍后将修改过的页面刷新到磁盘,这样做可以优化写操作的性能.写操作从缓冲中获益,因为可以将随机I/O转换为顺序I/O.异步(缓冲)写操作通常由操作系统处理,并且是被成批处理的,因此可以更优地被刷新到磁盘.同步(无缓冲)写入必须等待数据落盘.这就是为什么廉价磁盘冗余阵列(RAID)控制器的电池保护回写缓存能提升写入性能.

第五章 优化服务器设置

MySQL的配置是如何工作的

  • MySQL从何处获取配置信息：命令行参数和配置文件中的设置项.在 类UNIX系统上,配置文件通常位于/etc/my.cnf或/etc/mysql/my.cnf

  • 如果不知道服务器会读取哪些文件,可以通过如下命令查询

    1
    2
    3
    4
    5
    which mysqld

    bash-4.4# mysqld --verbose --help | grep -A 1 'Default options'
    Default options are read from the following files in the given order:
    /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

  • 配置文件采用标准INI格式,被分为多个部分,每个部分都以一行包含在方括号中的该部分名称开头.MySQL程序通常会读取与该程序同名的部分,很多客户端程序也会读取client部分,这为你提供了放置公共设置的位置.服务器通常读取mysqld部分.确保将设置放在文件的正确部分,否则它们将不起作用.

语法,作用域和动态性

  • 配置设置全部用小写字母书写,单词之间以下画线或短横线分隔.

  • 除了在配置文件中进行设置外,很多变量(但不是全部)还可以在服务器运行时进行更改.MySQL将这些称为动态配置变量.

  • 请注意,动态设置的变量在MySQL重启后会失效.如果要保留设置,必须更新配置文件.

  • 如果在服务器运行时设置变量的全局值,则当前会话和其他现有会话的值将不受影响(设置完成后,之后新产生的会话才会应用新的值).如果客户端依赖数据库长连接,请务必记住这一点.这是因为在创建连接时,会话值是从全局值初始化的.在每次更改后应该检查SHOW VARIABLES的输出,以确保其达到预期效果.

  • 了解所有这些变量作用域和配置方法还不够,你还必须知道,如果重新启动MySQL,即 使使用了SET GLOBAL来更改全局变量,它也将恢复到配置文件中的状态.这意味着必须同时管理MySQL的配置文件和运行时配置,并确保它们保持同步.如果要增加服务器的最大连接数max_connections,必须在每个正在运行的实例上执行SET GLOBALmax_connections命令,然后编辑配置文件以反映新配置.

  • MySQL 8.0引入了一个名为持久化系统变量的新功能,这有助于简化这个问题.新的语法SET PERSIST允许在运行时设置一次值,MySQL将把这个设置写入磁盘,以便在下次重启后继续使用该值.

    • 以下是设置持久化参数的步骤：

      • 使用 “SET GLOBAL” 命令来修改配置参数,例如：

        1
        SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

      • 确保参数已经生效.您可以通过执行以下命令来验证参数的值是否已经更改：

        1
        SELECT @@global.transaction_isolation;

      • 使用 “PERSIST” 关键字将参数持久化到配置文件中：

        1
        SET PERSIST TRANSACTION ISOLATION LEVEL REPEATABLE READ;

      • 最终写入mysqld-auto.cnf中,在数据库启动时,会首先读取其它配置文件,最后才读取mysqld-auto.cnf文件.

        1
        2
        bash-4.4# cat mysqld-auto.cnf 
        {"Version": 2, "mysql_dynamic_variables": {"transaction_isolation": {"Value": "READ-COMMITTED", "Metadata": {"Host": "", "User": "root", "Timestamp": 1688696283896258}}}}

什么不该做

  • 在开始服务器配置之前,我们建议你避免一些常见的做法,这些做法有风险,或者实际上不值得这么做.警告：前方有咆哮声！

  • 你可能会被期望(或相信你会被期望)建立一个基准测试套件,并通过迭代修改配置来“调优”服务器,以寻找最佳设置.我们通常不建议大家去做这样的事情.它需要大量的工作和研究,而且大多时候,潜在的回报非常小,因此是巨大的时间浪费.最好把这些时间花在其他事情上,比如检查备份、监控查询计划的变化等.

  • 你不应该“按比率调优”.经典的“调优比率”是一个经验法则,比如,InnoDB缓冲池命中率 应该高于某个百分比,如果命中率过低,应该增加缓存大小.这是非常错误的建议.不管别人怎么说,缓存命中率与缓存是太大还是太小无关.首先,命中率取决于工作负载——不管缓存有多大,有些工作负载根本不能被缓存——其次,缓存命中是没有意义的,原因我们将在后面解释.有时会出现缓存太小,出现命中率很低的情况,增加缓存大小会增加命中率.然而,这是一种偶然的关联,并不表示缓存的性能或大小是适当的.

  • 相关性有时看似正确,但问题在于人们容易相信它们永远是正确的.Oracle DBA在几年 前就放弃了基于比率的调优,我们希望MySQL DBA也能跟随他们的脚步.我们甚至更强烈地希望人们不要编写“调优脚本”来将这些危险的实践编入法典,并将它们传授给成千上万的人.这就引出了我们“什么不该做”的下一个建议：**不要使用调优脚本！**可以在网上找到一些非常流行的脚本,但最好还是忽略它们.

  • 我们还建议你避免使用调优这个词,在前几段文字中大量使用了这个词.我们更倾向于用配置或优化(只要这是你实际在做的).一提到调优,人们就会联想到一名不守纪律的新手,他调整了服务器,然后看看会发生什么.我们在前一节中建议,这个实践最好留给那些研究服务器内部结构的人.“调优”服务器可能是一种惊人的时间浪费.

  • 在互联网上搜索配置建议并不总是一个好主意,你会在博客、论坛等找到很多糟糕的建议.尽管许多专家在网上贡献了他们所知道的,但很难判断谁是真正的专家.当然,对于去哪里找真正的专家,我们无法给出公正的建议.但是我们可以说,可靠的、有信誉的MySQL服务提供商通常比简单的互联网搜索结果更安全,因为那些需要拥有满意的客户的人可能正在做正确的事情.然而,即使是他们的建议,在没有经过测试和理解的情况下进行应用也可能是危险的,因为它可能针对的是一种与你不同的情况,而你却没有理解.

  • 最后,不要相信流行的内存消耗公式——是的,MySQL本身在崩溃时会输出的那个公式.(这里我们就不重复了.)这个公式是很早之前的.这不是一个可靠的,甚至不是一个有用的方法来了解MySQL在最坏的情况下可以使用多少内存.你也可以在网上看到这个公式的一些变体.这些公式都有类似的缺陷,尽管它们添加了更多原公式所没有的因素.事实是,你不能给MySQL的内存消耗设定上限.MySQL并不是一个严格控制内存分配的数据库服务器.

创建MySQL配置文件

  • 不要让socket和.pid文件使用服务器编译的默认值；在不同的MySQL版本中有一些bug,使用默认值可能会导致问题,所以最好显式设置.(我们不是建议选择不同的地点,只是建议确保my.cnf文件明确地设置了这些位置,这样当升级服务器时它们就不会改变或引起问题.)

配置MySQL的I/O行为

InnoDB事务日志
  • InnoDB使用日志来降低提交事务的成本.它不会在每个事务提交时将缓冲池刷新到磁盘,而是将事务记录到日志中.事务对数据和索引所做的更改通常映射到表空间中的随机位置,因此将这些更改刷新到磁盘将需要随机I/O.InnoDB假定它使用的是传统的磁盘,随机I/O比顺序I/O的开销要大很多,因为随机I/O需要在磁盘上寻找正确的位置,并等待将所需的磁盘部分旋转到磁头下.
  • 使用日志,InnoDB可以将随机磁盘I/O转换为顺序I/O.一旦日志被安全地保存在磁盘中,即使更改的数据尚未写入数据文件,事务仍将是持久的.如果发生故障(例如停电), InnoDB可以重放日志并恢复已提交的事务.
  • 当然,InnoDB最终必须将更改的数据写入数据文件,因为日志的大小固定,采取的是循环写入的方式：当到达日志的末尾时,它会环绕到日志的开头.如果日志记录中包含的更改尚未应用于数据文件,则无法覆盖日志记录,因为这将删除已提交事务的唯一永久记录.
  • InnoDB使用后台线程智能地刷新对数据文件的更改.该线程可以将写入分组,并使数据写入顺序化,以提高效率.实际上,事务日志可以将随机数据文件I/O转换为顺序日志文件I/O和顺序数据文件I/O.将刷新移到后台可以更快地完成查询,并有助于缓冲I/O系统的查询负载峰值.
  • 日志文件的总大小由innodb_log_file_size和innodb_log_files_in_group控制,这对写入性能非常重要.如果你采纳了我们之前的建议,使用innodb_dedicated_server,日志文件的大小将根据系统内存量来自动管理.
日志缓冲区

  • InnoDB修改数据时会将修改记录写入日志缓冲区,并将其保存在内存中.当缓冲区满了、事务提交时,或者每秒1次(这三个条件以先满足者为准),InnoDB会将缓冲区刷新到磁盘上的日志文件中.如果有大型事务,增加缓冲区大小(默认为1MB)有助于减少I/O.控制缓冲区大小的变量是innodb_log_buffer_size.

  • 通常不需要将缓冲区设置得太大.建议的范围是1～8MB,一般来说足够了,除非写入很多大的BLOB记录.与InnoDB的普通数据相比,日志条目非常紧凑.它们不是基于页面的,所以不会浪费空间一次存储整个页面.InnoDB也会让日志条目尽量短,有时甚至只用几个整数来表示记录的操作类型和该操作所需的任何参数

InnoDB如何刷新日志缓冲区

  • 当InnoDB将日志缓冲区刷新到磁盘上的日志文件时,会使用互斥锁锁定缓冲区,将其刷新到所需的位置,然后将剩余的条目移动到缓冲区的前面.当释放互斥锁时,可能会有多个事务准备刷新其日志条目.InnoDB使用了一个组提交特性,可以在单次I/O操作中将一组日志全部提交.

  • 日志缓冲区必须被刷新到持久存储中,以确保提交的事务完全持久.如果你更关心性能而不是持久性,可以更改innodb_flush_log_at_trx_commit来控制日志缓冲区的刷新位置和刷新频率.

    值 说明
    0 每秒定时将日志缓冲区写入日志文件,并刷新日志文件,但在事务提交时不做任何操 作
    1 每次事务提交时,将日志缓冲区写入日志文件,并将其刷新到持久存储中.这是默认的(也是最安全的)设置；它保证你不会丢失任何已提交的事务,除非磁盘或操作系统“假装”进行刷新操作(没有将数据真正写入磁盘)
    2 每次事务提交时都将日志缓冲区写入日志文件,但不执行刷新.InnoDB按计划每秒刷新1次.与0设置最重要的区别是,如果只是MySQL进程崩溃,设置为2不会丢失任何事务.但是,如果整个服务器崩溃或断电,仍然可能丢失事务

  • 了解将日志缓冲区写入日志文件和将日志刷新到持久存储之间的区别很重要.在大多数操作系统中,将缓冲区写入日志只是将数据从InnoDB的内存缓冲区移动到操作系统的缓存中,依然还是在内存中.它实际上不会将数据写入持久存储.因此,如果发生崩溃或断电,设置为0和2通常会导致最多1秒的数据丢失,因为数据可能只存在于操作系统的缓存中.我们之所以说“通常”,是因为InnoDB会以每秒1次的速度将日志文件刷新到磁盘上, 但在某些情况下,例如刷新暂停时,可能会丢失超过1秒的事务.

InnoDB表空间
  • InnoDB将数据保存在表空间中,表空间本质上是一个虚拟文件系统,由磁盘上的一个或多个文件组成.InnoDB将表空间用于多种用途,而不仅仅是存储表和索引.表空间中还包含了Undo日志(重新创建旧行版本所需的信息)、修改缓冲区、双写缓冲区和其他内部结构.
  • 管理单个表空间可能很麻烦,特别是当它自动扩展而你又想回收空间时(出于这个原因,我们建议禁用自动扩展特性,或者至少为空间设置一个合理的上限).回收空间的唯一方法是将数据导出,然后关闭MySQL并删除所有文件,再修改配置,重启,让InnoDB创建新的空文件,最后再恢复数据.InnoDB对表空间是完全不宽容的：你不能简单地删除文件或者改变其大小.如果损坏了表空间,InnoDB将无法启动.同样,InnoDB对日志文件也非常严格.如果你习惯像使用MyISAM那样随意地移动文件,请务必注意！
其他I/O配置选项
  • sync_binlog选项控制MySQL如何将二进制日志刷新到磁盘,默认值是1,意味着MySQL将执行刷新并保持二进制日志的持久性和安全性.强烈推荐将其设置为1,不建议设置为任何其他值.
  • 如果不将sync_binlog设置为1,发生崩溃时可能会导致二进制日志与事务数据不同步.这很容易破坏复制且不可恢复,尤其是当数据库使用全局事务ID时(更多信息,请参阅第9章).将其设置为1所提供的安全性远远超过由此产生的I/O性能损失.

第六章 schema设计与管理

第七章 创建高性能的索引

索引基础

高性能的索引策略

前缀索引
索引列顺序
聚簇索引
覆盖索引
使用索引扫描来排序

  • MySQL有两种方式可以生成有序的结果：通过排序操作,或者按索引顺序扫描.如果在 EXPLAIN的输出结果中,type列的值为“index”,则说明MySQL使用了索引扫描来做排序 (注意,不要和Extra列的“Using index”搞混).

  • 扫描索引本身是很快的,因为只需要从一条索引记录移动到紧接着的下一条记录.但如果索引不能覆盖查询所需的全部列,那么就不得不每扫描一条索引记录都回表查询一次对应的记录.这基本上都是随机I/O,因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢,尤其是在I/O密集型的应用负载上.

  • 只有当索引的顺序和ORDER BY子句的顺序完全一致,并且所有列的排序方向(倒序或正序)都一样时,MySQL才能使用索引来对结果做排序.如果查询需要联接多张表,则只有当ORDER BY子句引用的字段全部在第一个表中时,才能使用索引做排序.ORDER BY子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求,否则,MySQL需要执行排序操作,而无法利用索引排序.

  • 有一种特殊情况,如果前导列为常量(索引的第一列被指定为了一个常数)的时候,ORDER BY子句中的列也可以不满足索引的最左前缀的要求.如果在WHERE子句或者JOIN子句中将这些列指定为了常量,就可以“填补”索引字段的间隙了.

冗余和重复索引
  • 不幸的是,MySQL允许在相同列上创建多个相同的索引.虽然MySQL会抛出一个警告,但是并不会阻止你这么做.MySQL需要单独维护重复的索引,优化器在优化查询的时候也需要逐个地进行评估,这会影响性能,同时也浪费磁盘空间.
  • 重复索引是指在相同的列上按照相同顺序创建的相同类型的索引.应该避免创建这样的重复索引,发现以后应该立即移除.

小结

  • 在选择索引和编写利用这些索引的查询时,有如下三个原则始终需要记住
    • 单行访问是很慢的,特别是在机械硬盘中存储(SSD的随机I/O要快很多,不过这一点仍然成立).如果服务器从存储中读取一个数据块只是为了获取其中一行,那么就浪费了很多工作.最好读取的块中能包含尽可能多的所需要的行.
    • 按顺序访问范围数据是很快的,有两个原因.第一,顺序I/O不需要多次磁盘寻道,所以比随机I/O要快很多(特别是对于机械硬盘).第二,如果服务器能够按需顺序读取数据,那么就不再需要额外的排序操作,并且GROUP BY查询也无须再做排序和将行按组进行聚合计算了.
    • 索引覆盖查询是很快的.如果一个索引包含了查询需要的所有列,那么存储引擎就不需要再回表查找行.这避免了大量的单行访问,而上面的第一点已经写明单行访问是很慢的.

第八章 查询性能优化

为什么查询速度会慢

  • 通常来说,查询的生命周期大致可以按照如下顺序来看：从客户端到服务器,然后在服务器上进行语法解析,生成执行计划,执行,并给客户端返回结果.其中,“执行”可以被认为是整个生命周期中最重要的阶段,这其中包括大量为了检索数据对存储引擎的调用以及调用后的数据处理,包括排序、分组等.
  • 在完成这些任务的时候,查询需要在不同的地方花费时间,包括网络、CPU计算、生成统计信息和执行计划、锁等待(互斥等待)等操作,尤其是向底层存储引擎检索数据的调用操作,这些调用需要在内存操作、CPU操作和内存不足时导致的I/O操作上消耗时间.根据存储引擎不同,可能还会产生大量的上下文切换以及系统调用

慢查询基础: 优化数据访问

  • 确认应用程序是否在检索大量且不必要的数据.这通常意味着访问了太多的行,但有时候也可能是访问了太多的列.
  • 确认MySQL服务器层是否在分析大量不需要的数据行.
是否向数据库请求了不需要的数据

  • 查询了不需要的记录

  • 多表联接时返回全部列

    1
    2
    3
    4
    SELECT * FROM sakila.actor
    INNER JOIN sakila.film_actor USING(actor_id)
    INNER JOIN sakila.film USING(film_id)
    WHERE sakila.film.title = 'Academy Dinosaur'
    • 应该只取需要的列

  • 总是取出全部列

    1
    SELECT *

  • 重复查询相同的数据

    • 缓存重复的数据
MySQL是否在扫描额外的记录
  • 在确定查询只返回需要的数据以后,接下来应该看看查询为了返回结果是否扫描了过多的数据.对于MySQL,最简单的衡量查询开销的三个指标如下:
    • 响应时间
    • 扫描的行数
    • 返回的行数
  • 没有哪个指标能够完美地衡量查询的开销,但它们大致反映了MySQL在内部执行查询时需要访问多少数据,并可以大概推算出查询运行的时间.这三个指标都会被记录到MySQL的慢日志中,所以检查慢日志记录是找出扫描行数过多的查询的好办法.
响应时间
  • 响应时间是两部分之和: 服务时间和排队时间.
    • 服务时间是指数据库处理这个查询真正花了多少多长时间.
    • 排队时间是指服务器因为等待某些资源而没有真正执行查询的时间,可能是等I/O操作完成,也可能是等待行锁等等.
  • 可以通过"快速上限估计"法来估算查询的响应时间.
扫描的行数和返回的行数
  • 理想情况下扫描的行数和返回的行数应该是相同的

重构查询的方式

一个复杂查询还是多个简单查询
切分查询

  • 有时候对于一个大查询,我们需要“分而治之”,将大查询切分成小查询,每个查询的功能完全一样,只完成一小部分,每次只返回一小部分查询结果

  • 删除旧的数据就是一个很好的例子.定期清除大量数据时,如果用一个大的语句一次性完成的话,则可能需要一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询.将一个大的DELETE语句切分成多个较小的查询可以尽可能小地影响MySQL的性能,同时还可以降低MySQL复制的延迟.

  • 需要注意的是,如果每次删除数据后,都暂停一会儿再做下一次删除,也可以将服务器上原本一次性的压力分散到一个很长的时间段中,可以大大降低对服务器的影响,还可以大大减少删除时锁的持有时间.

分解联接查询

查询执行的基础

  • 当向MySQL发送一个请求的时候,MySQL到底做了些什么：
    1. 客户端给服务器发送一条SQL查询语句.
    2. 服务器端进行SQL语句解析、预处理,再由优化器生成对应的执行计划.
    3. MySQL根据优化器生成的执行计划,调用存储引擎的API来执行查询.
    4. 将结果返回给客户端
MySQL的客户端/服务器通信协议
  • MySQL的客户端和服务器之间的通信协议是“半双工”的,这意味着,在任 何时刻,要么是由服务器向客户端发送数据,要么是由客户端向服务器发送数据,这两个动作不能同时发生.所以,我们无法也无须将一个消息切成小块来独立发送.
查询状态
  • 对于一个MySQL连接,或者一个线程,任何时刻都有一个状态,该状态表示了MySQL当前正在做什么.有很多种方式能查看当前的状态,最简单的是使用SHOW FULL PROCESSLIST命令(该命令返回结果中的Command列,其就表示当前的状态).
    • Sleep
      • 线程正在等待客户端发送新的请求.
    • Query
      • 线程正在执行查询或者正在将结果发送给客户端.
    • Locked
      • 在MySQL服务器层,该线程正在等待表锁.在存储引擎级别实现的锁,例如InnoDB的行锁,并不会体现在线程状态中.
    • Analyzing and statistics
      • 线程正在检查存储引擎的统计信息,并优化查询.
    • Copying to tmp table [on disk]
      • 线程正在执行查询,并且将其结果集复制到一个临时表中,这种状态一般要么是在做GROUP BY操作,要么是在进行文件排序操作,或者是在进行UNION操作.如果这个状态后面还有“on disk”标记,那表示MySQL正在将一个内存临时表放到磁盘上.
    • Sorting result
      • 线程正在对结果集进行排序
查询优化处理
  • 查询的生命周期的下一步是将一个SQL查询转换成一个执行计划,MySQL再依照这个执行计划和存储引擎进行交互.
  • 这包括多个子阶段：解析SQL、预处理、优化SQL执行计划.这个过程中产生的任何错误(例如,语法错误)都可能终止查询.
语法解析器和预处理
  • 首先,MySQL通过关键字将SQL语句进行解析,并生成一棵对应的“解析树”.MySQL解析器将使用MySQL语法规则验证和解析查询.例如,它将验证是否使用了错误的关键字,使用关键字的顺序是否正确,或者它还会验证引号是否能前后正确匹配.
  • 然后,预处理器检查生成的解析树,以查找解析器无法解析的其他语义,例如,这里将检查数据表和数据列是否存在,还会解析名字和别名,看看它们是否有歧义.
  • 下一步预处理器会验证权限.这通常很快,除非服务器上有非常多的权限配置
查询优化器

  • 现在解析树被认为是合法的了,并且由优化器将其转化成查询执行计划.一条查询可以有很多种执行方式,最后都返回相同的结果.优化器的作用就是找到这其中最好的执行计划.

  • MySQL使用基于成本的优化器,它将尝试预测一个查询使用某种执行计划时的成本,并选择其中成本最小的一个.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    mysql> select * from user;
    +----+------+------+
    | id | name | age  |
    +----+------+------+
    |  3 | z13  |   22 |
    |  4 | z14  |   23 |
    |  5 | z15  |   24 |
    |  6 | z16  |   25 |
    |  7 | z17  |   26 |
    |  8 | z18  |   27 |
    |  9 | z19  |   28 |
    | 10 | z100 |   29 |
    | 11 | z21  |  230 |
    +----+------+------+
    9 rows in set (0.00 sec)

    mysql> show status like 'last_query_cost';
    +-----------------+----------+
    | Variable_name   | Value    |
    +-----------------+----------+
    | Last_query_cost | 1.799000 |
    +-----------------+----------+
    1 row in set (0.00 sec)

    mysql>
    • 这个结果表示,MySQL的优化器认为大概需要做1个数据页的随机查找才能完成上面的查询.这是根据一系列的统计信息计算得来的：每个表或者索引的页面个数、索引的基数(索引中不同值的数量)、索引和数据行的长度、索引分布情况.优化器在评估成本的时候并不考虑任何层面的缓存带来的影响,它假设读取任何数据都需要一次磁盘I/O.

  • 有很多种原因会导致MySQL优化器选择错误的执行计划,如下所示：

    • 统计信息不准确.MySQL服务器依赖存储引擎提供的统计信息来评估成本,但是有的存储引擎提供的信息是准确的,有的偏差可能非常大.例如,InnoDB因为其MVCC的架构,并不能维护一个数据表的行数的精确统计信息.
    • 成本指标并不完全等同于运行查询的实际成本,因此即使统计数据是准确的,查询的成本也可能超过或者低于MySQL估算的近似值.例如,有时候某个执行计划虽然需要读取更多的页面,但是它的成本却更低.因为如果这些页面都是顺序读或者这些页面都已经在内存中的话,那么它的访问成本将很低.MySQL并不知道哪些页面在内存中、哪些在磁盘中,所以查询在实际执行过程中到底需要多少次物理 I/O是无法得知的.
    • MySQL的最优可能和你想的最优不一样.你可能希望执行时间尽可能短,但是MySQL只是基于其成本模型选择最优的执行计划,而有些时候这并不是最快的执行方式.所以,这里我们看到的根据执行成本来选择执行计划并不是完美的模型.
    • MySQL从不考虑其他并发执行的查询,这可能会影响到当前查询的速度.
    • MySQL也并不是任何时候都是基于成本的优化.它有时也会基于一些固定的规则,例如,如果存在全文搜索的MATCH()子句,则在存在FULLTEXT索引的时候就使用全文索引.即使有时候使用其他索引和WHERE条件可以远比这种方式要快,MySQL也仍然会使用对应的全文索引.
    • MySQL不会考虑不受其控制的操作的成本,例如,执行存储函数或者用户自定义函数成本.

  • MySQL的查询优化器是一个非常复杂的软件,它使用了很多优化策略来生成一个最优的执行计划.优化策略可以简单地分为两种,一种是静态优化,一种是动态优化.

    • 静态优化可以直接对解析树进行分析,并完成优化.例如,优化器可以通过一些简单的代数变换将WHERE条件转换成另一种等价形式.静态优化不依赖于特别的数值,如WHERE条件中带入的一些常数等.静态优化在第一次完成后就一直有效,即使使用不同的参数重复执行查询也不会发生变化,可以认为这是一种“编译时优化”.
    • 动态优化则和查询的上下文有关,也可能和很多其他因素有关,例如WHERE条件中的取值、索引中条目对应的数据行数等.这需要在每次查询的时候都重新评估,可以认为这是“运行时优化”.

  • 在执行绑定变量和存储过程的时候,动态优化和静态优化的区别非常重要.MySQL对查询的静态优化只需要做一次,但对查询的动态优化则在每次执行时都需要重新评估.有时候甚至在查询的执行过程中也会重新优化.

  • 下面是一些MySQL能够处理的优化类型:

    • 重新定义联接表的顺序

      • 数据表的联接并不总是按照在查询中指定的顺序进行.决定联接的顺序是优化器很重要的一个功能

    • 将外联接转化成内联接

      • 并不是所有的OUTER JOIN语句都必须以外联接的方式执行.诸多因素,例如WHERE条件、库表结构都可能会让外联接等价于一个内联接.MySQL能够识别这一点并重写查询,让其可以调整联接顺序.

    • 使用代数等价变换规则

      • MySQL可以使用一些代数等价变换规则来简化并规范表达式.它可以合并和减少一 些比较,还可以移除一些恒成立和一些恒不成立的判断.例如,(5=5 AND a>5)将被改写为a>5.类似地,如果有(a<b AND b=c)AND a=5则会改写为b>5 AND b=c AND a=5.

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

      • 索引和列是否可为空通常可以帮助MySQL优化这类表达式.例如,要找到某一列的最小值,只需要查询对应B-tree索引最左端的记录,MySQL可以直接获取索引的第一行记录.在优化器生成执行计划的时候就可以利用这一点,在B-tree索引中,优化器会将这个表达式作为一个常数对待.类似地,如果要查找一个最大值,也只需读取B-tree索引的最后一条记录.如果MySQL使用了这种类型的优化,那么在EXPLAIN中就可以看到“Select tables optimized away”.从字面意思可以看出,它表示优化器已经从执行计划中移除了该表,并以一个常数代替.

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

      • 当MySQL检测到一个表达式可以转化为常数的时候,就会一直把该表达式作为常数进行优化处理.例如,一个用户自定义变量在查询中没有发生变化时就可以将其转换为一个常数.数学表达式则是另一种典型的例子.
      • 让人惊讶的是,在优化阶段,有时候一个查询也能够转化为一个常数.一个例子是在索引列上执行MIN()函数.甚至是主键或者唯一键查找语句也可以被转换为常数表达式.如果WHERE子句中使用了该类索引的常数条件,MySQL可以在查询开始阶段就先查找到这些值,这样优化器就能够知道并将其转换为常数表达式.
      • 另一种会看到常数条件的情况是通过等式将常数值从一个表传到另一个表,这可以通过WHERE、USING或者ON语句来限制某列取值为常数.

    • 覆盖索引扫描

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

    • 子查询优化

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

    • 提前终止查询

      • 在发现已经满足查询需求的时候,MySQL总是能够立刻终止查询.一个典型的例子就是当使用了LIMIT子句的时候.除此之外,MySQL在其他几类情况下也会提前终止查询,例如发现了一个不成立的条件,这时MySQL可以立刻返回一个空结果.

    • 列表IN()的比较

      • 在很多数据库服务器中,IN()完全等同于多个OR条件的子句,因为这两者是完全等价的.在MySQL中这点是不成立的,MySQL将IN()列表中的数据先进行排序,然后通过二分查找的方式来确定列表中的值是否满足条件,这是一个O(logn)复杂度的操作,等价地转换成OR查询的复杂度为O(n),对于IN()列表中有大量取值的时候,MySQL的处理速度将会更快.
MySQL如何执行联接查询
  • 当前MySQL的联接执行策略很简单：MySQL对任何联接都执行嵌套循环联接操作,即MySQL先在一个表中循环取出单条数据,然后再嵌套循环到下一个表中寻找匹配的行,依次下去,直到找到所有表中匹配的行为止.最后根据各个表匹配的行,返回查询中需要的各列.MySQL会尝试在最后一个联接表中找到所有匹配的行,如果最后一个联接表无法找到更多的行,MySQL返回到上一层次的联接表,看是否能够找到更多的匹配记录,依此类推,迭代执行.
  • 在MySQL 8.0.20版本之后,已经不再使用基于块的嵌套循环联接操作,取而代之的是哈希联接(参见链接33).这让联接操作性能变得更好,特别是当数据集可以全部存储在内存时.
联接查询优化器
排序优化

  • 当不能使用索引生成排序结果的时候,MySQL需要自己进行排序,如果数据量小则在内存中进行,如果数据量大则需要使用磁盘,不过MySQL将这个过程统一称为文件排序(filesort),即使完全是在内存中排序不需要任何磁盘文件时也是如此.如果需要排序的数据量小于“排序缓冲区”,MySQL使用内存进行快速排序操作.如果内存不够排序,那么MySQL会先将数据分块,对每个独立的块使用“快速排序”进行排序,并将各个块的排序结果存放在磁盘上,然后将各个排好序的块进行合并(merge),最后返回排序结果.

  • 在联接查询的时候如果需要排序,MySQL会分两种情况来处理这样的文件排序.

    • 如果ORDER BY子句中的所有列都来自联接的第一个表,那么MySQL在联接处理第一个表的时候就进行文件排序.如果是这样,那么在MySQL的EXPLAIN结果中可以看到Extra字段会有**“Using filesort”**字样.
    • 除此之外的所有情况,MySQL都会先将联接的结果存放到一个临时表中,然后在所有的联接都结束后,再进行文件排序.在这种情况下,在MySQL的EXPLAIN结果的Extra字段可以看到**“Using temporary；Using filesort”**字样.
    • 如果查询中有LIMIT的话,LIMIT也会在文件排序之后应用,所以即使需要返回较少的数据,临时表和需要排序的数据量仍然会非常大.
查询执行引擎
  • 在解析和优化阶段,MySQL将生成查询对应的执行计划,MySQL的查询执行引擎会根据这个执行计划来完成整个查询.这里的执行计划是一个数据结构,而不是和很多其他的关系数据库那样生成对应的可执行的字节码.相对于查询优化阶段,查询执行阶段不是那么复杂：MySQL只是简单地根据执行计划给出的指令逐步执行.在根据执行计划逐步执行的过程中,有大量的操作需要通过调用存储引擎实现的接口来完成,这些接口也就是我们称为“handler API”的接口.查询中的每一个表都由一个handler的实例表示.如果一个表在查询中出现了三次,服务器会创建三个handler对象.前面我们有意忽略了这一点,实际上,MySQL在优化阶段就为每个表创建了一个handler实例,优化器根据这些实例的接口可以获取表的相关信息,包括表的所有列名、索引统计信息,等等.
将结果返回客户端
  • 执行查询的最后一个阶段是将结果返回给客户端.即使查询不需要给客户端返回结果集,MySQL仍然会返回这个查询的一些信息,如该查询影响到的行数.MySQL将结果集返回客户端是一个增量且逐步返回的过程.例如,我们回头看看前面的联接操作,一旦服务器处理完最后一个联接表,开始生成第一条结果时,MySQL就可以开始向客户端逐步返回结果集了.这样处理有两个好处：服务器端无须存储太多的结果,也就不会因为要返回太多结果而消耗太多内存.另外,这样的处理也可让MySQL客户端第一时间获得返回的结果.结果集中的每一行都会以一个满足MySQL客户端/服务器通信协议的封包发送,再通过TCP协议进行传输,在TCP传输的过程中,可能对MySQL的封包进行缓存,然后批量传输.

MySQL查询优化器的局限性

UNION的限制

  • 有时,MySQL无法将限制条件从UNION的外层“下推”到内层,这使得原本能够限制部分返回结果的条件无法应用到内层查询的优化上.

  • 如果希望UNION的各个子句能够根据LIMIT只取部分结果集,或者希望能够先排好序再合并结果集的话,就需要在UNION的各个子句中分别使用这些子句.例如,想将两个子查询结果联合起来,然后再取前20条记录,那么MySQL会将两个表存放到同一个临时表中,然后再取出前20行记录:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    (SELECT frist_name,last_name
     FROM sakila.actor
     ORDER BY last_name
    )
    UNION ALL
    (SELECT frist_name,last_name
     FROM sakila.customer
     ORDER BY last_name
    )
    LIMIT 20;

  • 这条查询将会把actor表中的200条记录和customer表中的599条记录存放在一个临时表中,然后再从临时表中取出前20条.可以通过在UNION的两个子查询中分别加上一个LIMIT 20来减少临时表中的数据:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    (SELECT frist_name,last_name
     FROM sakila.actor
     ORDER BY last_name
     LIMIT 20
    )
    UNION ALL
    (SELECT frist_name,last_name
     FROM sakila.customer
     ORDER BY last_name
     LIMIT 20
    )
    ORDER BY last_name
    LIMIT 20;

  • 现在临时表只包含40条记录了,除了考虑性能之外,在这里还需要注意一点：从临时表中取出数据的顺序并不是一定的,所以如果想获得正确的顺序,还需要在最后的LIMIT操作前加上一个全局的ORDER BY操作.

等值传递
并行执行
  • MySQL无法利用多核特性来并行执行查询.很多其他的关系数据库能够提供这个特性,但是MySQL做不到.这里特别指出是想告诉读者不要花时间去尝试寻找并行执行查询的方法
在同一个表中查询和更新

  • MySQL不允许对一张表同时进行查询和更新.这其实并不是优化器的限制,如果你清楚MySQL是如何执行查询的,就可以避免这种情况.下面是一段无法运行的SQL语句,尽管这是一段符合标准的SQL语句.这个查询会将表中每一行的c字段值更新为和该行的type字段值相同的行数量：

    1
    2
    3
    4
    5
    UPDATE tbl AS outer_tbl
    SET c=(SELECT count(*) FROM tbl AS inner_tbl
    	   WHERE inner_tbl.type = outer_tbl.type
    );
    ERROR 1093 (HY000): You can't specify target table 'outer_tbl' for update in FROM clause

  • 可以使用生成表的形式来绕过上面的限制,因为MySQL只会把这个表当作一个临时表来处理.实际上,这执行了两个查询：一个是子查询中的SELECT语句,另一个是多表UPDATE查询,其中包含原表和子查询的联接结果.子查询会在UPDATE语句打开表之前就完成,所以下面的查询将会正常执行

    1
    2
    3
    UPDATE tbl 
    INNER JOIN (SELECT type,count(*) AS c FROM tbl GROUP BY type) AS der USING(type)
    SET tbl.c = der.c;

优化特定类型的查询

优化COUNT()查询
COUNT()的作用
  • COUNT()是一个特殊的函数,有两种非常不同的作用：它可以统计某列的值的数量,也可以统计行数.在统计列值时要求列值是非空的(不统计NULL).如果在COUNT()的括号中指定了列或者列的表达式,则统计的就是这个表达式有值的结果数.因为很多人对NULL理解有问题,所以这里很容易产生误解.如果你想了解更多关于SQL语句中NULL的含义,建议阅读一些关于SQL语句基础的书籍.(关于这个话题,互联网上的一些信息是不够准确的.)
  • COUNT()的另一个作用是统计结果集的行数.当MySQL确认括号内的表达式值不可能为空时,实际上就是在统计行数.最简单的就是当我们使用COUNT(*)的时候,这种情况下通配符*并不会像我们猜想的那样扩展成所有的列,实际上,它会忽略所有的列而直接统计所有的行数.
简单优化

  • 通常会看到这样的问题：如何在一个查询中统计同一列的不同值的数量,以减少查询的语句量.例如,假设可能需要通过一个查询返回各种不同颜色的商品数量,此时不能使用OR语句(比如,SELECT COUNT(color='blue’OR color=‘red’)FROM items；),因为这样做无法区分不同颜色的商品数量；也不能在WHERE条件中指定颜色(比如,SELECT COUNT(*)FROM items WHERE color='blue’AND color=‘RED’；),因为颜色的条件是 互斥的.下面的查询可以在一定程度上解决这个问题:

    1
    2
    SELECT SUM(IF(color = 'blue',1,0)) AS blue,SUM(IF(color = 'red',1,0)) AS red
    FROM items;

  • 也可以使用COUNT()而不是SUM()实现同样的目的,只需要将满足条件设置为真,不满足条件设置为NULL即可：

    1
    2
    SELECT COUNT(color = 'blue',1,0) AS blue,COUNT(color = 'red',1,0) AS red
    FROM items;
使用近似值
  • 有时候,某些业务场景并不要求完全精确的统计值,此时可以用近似值来代替.EXPLAIN出来的优化器估算的行数就是一个不错的近似值,执行EXPLAIN并不需要真正地去执行查询,所以成本很低.
使用WITH ROLLUP优化GROUP BY
  • 分组查询的一个变种就是要求MySQL对返回的分组结果再做一次超级聚合.可以使用WITH ROLLUP子句来实现这种逻辑,但可能优化得不够.可以通过EXPLAIN来观察其执行计划,特别要注意分组是否是通过文件排序或者临时表实现的.然后再去掉WITH ROLLUP子句来看执行计划是否相同.
优化LIMIT和OFFSET子句

  • 在系统中需要进行分页操作的时候,我们通常会使用LIMIT加上偏移量的办法实现,同时加上合适的ORDER BY子句.如果有对应的索引,通常效率会不错,否则,MySQL需要做大量的文件排序操作.

  • 一个非常常见又令人头疼的问题是,在偏移量非常大的时候,例如,可能是LIMIT 1000,20这样的查询,这时MySQL需要查询10020条记录然后只返回最后20条,前面10000条记录都将被抛弃,这样的代价非常高.如果所有的页面被访问的频率都相同,那么这样的查询平均需要访问半个表的数据.要优化这种查询,要么是在页面中限制分页的数 量,要么是优化大偏移量的性能.

  • 优化此类分页查询的一个最简单的办法就是尽可能地使用索引覆盖扫描,而不是查询所有的行.然后根据需要做一次联接操作再返回所需的列.在偏移量很大的时候,这样做的效率会有非常大的提升.考虑下面的查询

    1
    SELECT film_id,description FROM sakila.film ORDER BY title LIMIT 50,5;

  • 如果这个表非常大,那么这个查询最好改写成下面的样子

    1
    2
    3
    4
    5
    6
    SELECT film_id,description FROM sakila.film 
    INNER JOIN(
    SELECT film_id,description 
    FROM sakila.film 
    ORDER BY title LIMIT 50,5;
    ) AS lim USING(film_id);

  • 这种“延迟联接”之所以有效,是因为它允许服务器在不访问行的情况下检查索引中尽可能少的数据,然后,一旦找到所需的行,就将它们与整个表联接,以从该行中检索其他列.类似的技术也适用于带有LIMIT子句的联接.

  • LIMIT和OFFSET的问题,其实是OFFSET的问题,它会导致MySQL扫描大量不需要的行然后再抛弃掉.如果可以使用书签记录上次取数据的位置,那么下次就可以直接从该书签记录的位置开始扫描,这样就可以避免使用OFFSET.例如,若需要按照租借记录做翻页,那么可以根据最新一条租借记录向回追溯,这种做法可行是因为租借记录的主键是单调增长的.首先使用下面的查询获得第一组结果.

    1
    2
    SELECT * FROM sakila.rental
    ORDER BY rental_id DESC LIMIT 20;

  • 假设上面的查询返回的是主键为16,049到16,030的租借记录,那么下一页查询就可以从16,030这个点开始

    1
    2
    3
    SELECT * FROM sakila.rental
    WHERE rental_id < 16030
    ORDER BY rental_id DESC LIMIT 20;

  • 该技术的好处是无论翻页到多么靠后,其性能都会很好.

  • 其他优化办法还包括使用预先计算的汇总表,或者联接到一个冗余表,冗余表只包含主键列和需要做排序的数据列.

优化SQL CALC FOUND ROWS
  • 分页的时候,另一个常用的技巧是在LIMIT语句中加上SQL_CALC_FOUND_ROWS提示 (hint),这样就可以获得去掉LIMIT以后满足条件的行数,因此可以作为分页的总数. 看起来,MySQL做了一些非常“高深”的优化,像是通过某种方法预测了总行数.但实际 上,MySQL只有在扫描了所有满足条件的行以后,才会知道行数,所以加上这个提示以 后,不管是否需要,MySQL都会扫描所有满足条件的行,然后再抛弃掉不需要的行,而不是在满足LIMIT的行数后就终止扫描.所以该提示的代价可能非常高.
  • 一个更好的设计是将具体的页数换成“下一页”按钮,假设每页显示20条记录,那么我们每次查询时都是用LIMIT返回21条记录并只显示20条,如果第21条存在,那么就显示“下一页”按钮,否则就说明没有更多的数据,也就无须显示“下一页”按钮了.
  • 另一种做法是先获取并缓存较多的数据——例如,缓存1000条——然后每次分页都从这个缓存中获取.这样做可以让应用程序根据结果集的大小采取不同的策略,如果结果集小于1000,就可以在页面上显示所有的分页链接,因为数据都在缓存中,所以这样做不会对性能造成影响.如果结果集大于1000,则可以在页面上设计一个额外的“找到的结果多于1000条”之类的按钮.这两种策略都比每次生成全部结果集再抛弃不需要的数据的效率高很多.
  • 有时候也可以考虑使用EXPLAIN的结果中的rows列的值来作为结果集总数的近似值(实际上,Google的搜索结果总数也是一个近似值).当需要精确结果的时候,再单独使用COUNT(*)来满足需求,这时如果能够使用索引覆盖扫描则通常也会比SQL_CALC_FOUND_ROWS快得多.
优化UNION查询
  • MySQL总是通过创建并填充临时表的方式来执行UNION查询,因此很多优化策略在UNION查询中都没法很好地被使用.经常需要手工地将WHERE、LIMIT、ORDER BY等 子句“下推”到UNION的各个子查询中,以便优化器可以充分利用这些条件进行优化(例如,直接将这些子句冗余地写一份到各个子查询).
  • 除非你确实需要服务器消除重复的行,否则一定要使用UNION ALL,这一点很重要.如果没有ALL关键字,MySQL会给临时表加上DISTINCT选项,这会导致对整个临时表的数据做唯一性检查.这样做的代价非常高.即使有ALL关键字,MySQL仍然会使用临时表存储结果.事实上,MySQL总是将结果放入临时表,然后再读出,再返回给客户端,虽然很多时候这样做是没有必要的(例如,MySQL可以直接把这些结果返回给客户端).