概述

MySQL的逻辑架构：

–摘自《高性能MySQL 第3版》1.1 小节

优化与执行

  • MySQL会解析查询，并创建内部数据结构，然后对其进行各种优化，包括重写查询、决定表的读取顺序，以及选择合适的索引。
  • 用户可以通过特殊的关键字提示(hint)优化器，影响它的决策过程。
  • 也可以请求优化器解释(explain)优化过程的各个因素，使用户可以知道服务器是如何进行优化决策的。
  • 对于SELECT语句，在解析查询之前，服务器会先检查查询缓存(Query Cache)，如果能够在其中找到对应的查询，服务器就不必再执行查询解析、优化和执行的整个过程，而是直接返回查询缓存中的结果集。

并发控制

读写锁

共享锁、排他锁。

锁粒度

表锁是最基本的锁策略，并且是开销最小的策略。

行级锁可以最大程度的支持并发处理。行级锁仅在存储引擎中实现，如InnoDB和XtraDB。

事务

原子性、一致性、隔离性、持久性。

事务的隔离级别又分为：未提交读、提交读、可重复读、可串行化。

未提交读（脏读）

提交读（不可重复读）

大多数数据库默认是提交读，MySQL不是。同一个事务中执行两次相同的查询可能得到不一样的结果。

可重复读

解决了脏读问题，同一事务中多次读取相同的记录结果是一致的。但是存在幻读问题，当前某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取范围的记录时，读取不到新事务插入的新值，会产生幻行。

当隔离级别是可重复读，且禁用innodb_locks_unsafe_for_binlog的情况下，在搜索和扫描index的时候使用的next-key locks可以避免幻读。

MySQL的默认数据库隔离级别。

可串行化

最高的隔离级别。通过强制事务串行执行，避免了前面说的幻读问题。

死锁

InnoDB中的死锁：Deadlocks in InnoDB

InnoDB有两种处理死锁的方式：

  • When deadlock detection is enabled (the default), InnoDB automatically detects transaction deadlocks and rolls back a transaction or transactions to break the deadlock. InnoDB tries to pick small transactions to roll back, where the size of a transaction is determined by the number of rows inserted, updated, or deleted.
  • On high concurrency systems, deadlock detection can cause a slowdown when numerous threads wait for the same lock. At times, it may be more efficient to disable deadlock detection and rely on the innodb_lock_wait_timeout setting for transaction rollback when a deadlock occurs. Deadlock detection can be disabled using the innodb_deadlock_detect configuration option.

死锁检测：Deadlock Detection and Rollback

事务日志

事务日志可以帮助提高事务的效率。使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久化到磁盘。这种方式通常称为预写式日志(Write-Ahead Logging)。这种方式通常修改数据需要写两次磁盘。

多版本并发控制

MySQL的大多数事务型存储引擎实现的都不是简单的行级锁。基于提升并发性能的考虑，他们一般都同时实现了多版本并发控制(MVCC)。
MVCC是行级锁的一个变种，但是他在很多情况下避免了加锁操作，因此开销更低。非阻塞读和行锁。

MVCC的实现，是通过保存数据在某个时间点的快照来实现的。不管需要执行多长时间，每个事务看到的数据都是一致的。根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。

InnoDB的MVCC是通过每行记录后面保存两个隐藏的列来实现的。这两个列一个保存了行的创建时间，一个保存行的过期时间(或删除时间)。当然存储的并不是实际的时间值，而是系统版本号（system version number）。没开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录进行比较。例如：

select:

  • InnoDB只查找版本早于当前事务版本的数据行。
  • 行的删除版本要么未定义，要么大于当前事务版本号，这样可以确保事务读取到的行，在事务开始之前未被删除。

insert:

  • InnoDB为新插入的每一行保存当前系统版本号作为行版本号。

delete：

  • InnoDB为删除的每一行保存当前系统版本号作为行删除标识。

update：

  • InnoDB为插入一行新记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。

目前MVCC模式支持提交读和可重复读。未提交读每次读取最新的全表数据，

缺点: MVCC对于并发修改由良好的性能支持，但是由于使用多版本控制行记录，如果进行回滚，则可能会影响其他其他事物的提交。

比如，如果Transaciton1执行理想的MVCC，修改Row1成功，而修改Row2失败，此时需要回滚Row1，但因为Row1没有被锁定，其数据可能又被Transaction2所修改，如果此时回滚Row1的内容，则会破坏Transaction2的修改结果，导致Transaction2违反ACID。

innodb-multi-version

多版本并发控制和二级索引

MVCC对二级索引和聚簇索引的处理方式是不同的。聚簇索引的记录会立马更新，聚簇索引的隐藏系统列指向可以被重构记录的更早版本的undo log条目，不像聚簇索引记录，二级索引记录不包含隐藏系统列，也不会立即更新。

当二级索引列被更新时，老的二级索引记录被标记删除，新纪录被插入，被标记删除记录最终被清除。当二级索引记录被删除标记或者二级索引页被新事务更新，InnoDB在聚簇索引中查找数据库记录。（此时覆盖索引失效）在聚簇索引中，记录的DB_TRX_ID被检查，如果在启动读取事务后修改了记录，则会从undu log中检索记录的正确版本。

但是，如果启用了索引条件下推（ICP）优化，并且仅可以使用索引中的字段来评估WHERE条件的部分，则MySQL服务器仍会将WHERE条件的这部分下推到存储引擎中进行评估使用索引。如果找不到匹配的记录，则避免聚集索引查找。如果找到匹配的记录，即使在删除标记的记录中，InnoDB也会在聚集索引中查找记录。

引擎篇

MyISAM

  • Myisam是Mysql的默认存储引擎，当create创建新表时，未指定新表的存储引擎时，默认使用Myisam。

  • 每个MyISAM在磁盘上存储成三个文件。文件名都和表名相同，扩展名分别是.frm（存储表定义）、.MYD (MYData，存储数据)、.MYI (MYIndex，存储索引)。数据文件和索引文件可以放置在不同的目录，平均分布io，获得更快的速度。

  • 它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一 。

InnoDB

InnoDB是MySQL的默认事务型引擎，它被设计用来处理大量的短期事务。短期事务大部分情况是正常提交的，很少会回滚。
InnoDB的数据存储在表空间中。

  • InnoDB 存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比Myisam的存储引擎，InnoDB 写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。

  • 用于事务处理应用程序，具有众多特性，包括ACID 事务支持。

  • InnoDB可通过Sphinx实现全文索引。
  • InnoDB表基于聚簇索引建立的，聚簇索引对主键查询有很高的性能。不过它的二级索引必须包含主键列。
  • InnoDB根据主键引用被索引的行。

Memory

将所有数据保存在RAM 中，在需要快速查找引用和其他类似数据的环境下，可提供极快的访问。

特点Myisam BDB MemoryInnoDB Archive
存储限制没有没有有64TB没有
事务安全支持支持
锁机制表锁页锁表锁行锁行锁
B 树索引支持支持支持支持
哈希索引支持支持
全文索引支持
集群索引支持
数据缓存支持支持
索引缓存支持支持支持
数据可压缩支持支持
空间使用低低N/A 高非常低
内存使用低低中等高低
批量插入的速度高高高低非常高
支持外键支持

Schema与数据类型优化

数据类型优化

  • 一般情况下，应该尽量使用可以正确存储数据的最小数据类型。

  • 简单的数据类型通常需要更少的CPU周期。

  • 尽量避免使用NULL，原因是由于NULL可能需要单独的位（bit）去存储或者NULL使得索引、索引统计、值比较都更为复杂。InnoDB使用单独的位(bit)存储NULL之，所以对于稀疏数据有很好的空间效率。

整数类型

有两种类型的数字：整数(whole number)和实数(real number)。如果存储整数，可以使用这几种整数类型：TYNYINT，SMALLINT，MEDIUMINT，INT，BIGINT。分别使用8，16，24，32，64位存储空间。它们可以存储的值的范围从-2^(N-1)到-2^(N-1)-1，其中N是存储空间位数。

整数类型可选UNSIGNED属性，表示不允许为负值。

有符号和无符号类型使用相同的存储空间，并具有相同的性能。
整数计算一般使用64位的BIGINT整数，及时在32位环境也是如此。

实数类型

实数是带有小数部分的数字。 它不只是为了存储小数部分，也可以使用DECIMAL存储比BIGINT还大的整数。及支持精确类型，也支持不精确类型。
Float和double类型支持使用标准的浮点运算进行近似计算。
DECIMAL类型用于存储精确的小数。

范式的优点和缺点

  • 范式化的更新操作会比反范式化的快。

  • 当数据较好范式化的时候，只有很少或者没有重复数据，所以只需要修改更少的数据。

  • 范式化的表通常更小。

  • 很少有冗余数据意味着检索列表数据更少需要distinct或者group by语句。

反范式的优点和缺点

  • 反范式的schema所有数据都在一张表中，可以很好的避免关联，还可以避免随机IO(和存储引擎有关)。

总结：

  • 尽可能避免过度设计，例如会导致复杂查询的schema设计，或者有很多列的表的设计。

  • 使用小而简单的合适数据类型，除非真实数据模型中确切的需要，否则应尽可能的避免NULL值。

  • 尽量使用相同的数据类型存储相似或者相关的值，尤其是在关联条件中要使用的列。

  • 避免使用MySQL已经遗弃的特性，例如浮点数的精度，或者整数的显示宽度。

  • 小心使用ENUM和SET。避免使用BIT。

MySQL查询性能优化

捕获SQL查询日志

慢查询日志

慢查询日志是开销最低，精度最高的测量查询时间的工具。不需要考虑性能消耗，但是需要注意日志量太大导致占用太多磁盘空间。
慢查询日志中V/M列提供了方差均值比的详细数据，方差均值比也就是常说的离差指数。离差指数高的查询对应的执行时间的变化较大。

抓取TCP包

通过tcpdump将网络包数据保存到磁盘，然后使用pt-query-digest的–type=tcpdump解析查询。

分析查询日志

工具：pt-query-digest。

show profile

set profiling = 1
show profiles
show prifle for query 1
输出结果根据执行顺序排序，不使用show profile也可以直接查询infomation_schema中对应的表。

show status

这是一个计数器，可以清楚的知道句柄、临时文件和表的次数。是否是磁盘表。

诊断问题工具

show Global status，可以将这个命令的数据绘制成图像。

show processlist， 观察是否存在大量线程处于不正常的状态或者有其他不正常的特征。

上面两个工具性能消耗很低，可以交叉或者频繁多次调用获取数据分析问题。

捕获诊断数据

监控工具：pt-stalk、pt-collect
监控变量：系统的状态、CPU利用率、磁盘使用率和可用空间、ps的输出采样、内存利用率，show status、show processlist和show innodb status。

高性能索引创建

系统从磁盘读取数据到内存是以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。

InnoDB存储引擎中有页（page）的概念，页是其磁盘管理的最小单位。

索引基础

索引是存储引擎用于快速找到记录的一种数据结构。

B-Tree索引（平衡多路查找树）

b-tree

B-Tree 通常意味着所有的值都是按照顺序存储的，并且每一个叶子页到根的距离相同。B-Tree索引能够加快访问数据的速度，存储引擎不需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索。根节点的槽中存放指向子节点的指针，存储引擎根据这些指针向下层查找。指针中定义了子节点页中值的上限和下限。最终找到该值或者不存在。

叶子节点比较特殊，他们指向的是被索引的数据，而不是其他的节点页。

索引对多个值进行排序的依据是CREATE TABLE语句中定义索引时列的顺序。

B-Tree索引的查询类型适用于全键值、键值范围和键前缀查找，其中键前缀查找只适用于根据最左前缀查找。

树的深度和表的大小直接相关。

b-tree

可以使用B-Tree索引的查询类型：

全值匹配：全值匹配指的是和索引的所有列进行匹配，例如前面提到的索引可用于查找姓名为Cuba Allen 1960-01-01。

匹配最左前缀：可用于查找索引的第一列。

匹配列前缀：也可以匹配某一列的值的开头部分。

匹配范围值：遵循最左前缀的前提下，范围查找。

精确匹配某一列并范围匹配另外一列：可用于查找所有姓为Allen,并且名字是字母K开头的人。

只访问索引的查询：B-Tree通常可以支持“只访问索引的查询”，即查询只需要访问索引，而无需访问数据行。

B-Tree使用限制：

  • 必须遵循最左原则。
  • 不能跳过索引中的列。
  • 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。
  • 如果查询中某个列的范围查询，则其右边所有列都无法使用索引优化查找。

B+Tree索引

B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。

在B-tree的基础上，为叶子节点增加链表指针，而且所有的关键字都在叶子节点中出现，且数据只存储在叶子节点中。非叶子节点的关键字仅作为叶子节点的索引。

哈希索引

哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

在MySQL中，只有Memory引擎显式支持哈希索引且支持非唯一哈希索引的。

哈希索引每个槽点编号是有序的。

哈希索引使用限制：

哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。

哈希索引并不是按照索引值顺序存储的，所以无法用于排序。

哈希索引也不支持部分索引列的匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。

哈希索引支持等值比较查询，包括=、in()、<=>也不支持任何范围查询，例如WHERE price > 100。

访问哈希索引的数据非常快，除非有很多哈希冲突。

如果哈希冲突很多的话，一些索引维护操作的代价也会很高。

除了Memory引擎外，NDB集群引擎也支持唯一哈希索引，且在NDB集群引擎中作用非常特殊。

InnoDB引擎有一个特殊的功能叫“自适应哈希索引”。当InnoDB引擎注意到某些索引值被使用的非常频繁时，他会在内存中基于B-Tree索引之上再创建一个哈希索引，这样就让B-Tree索引也具有哈希索引的一些优点，比如快速哈希查找。

全文索引

全文索引是一种特殊类型索引，它查找的是文本中的关键词，而不是直接比较索引中的值。适用于MATCH AGINST操作，而不是普通的WHERE条件操作。

索引的优点

  • 索引大大减少了服务器需要扫描的数据量。

  • 索引可以帮助服务器避免排序和临时表。

  • 索引可以将随机IO变为顺序IO。

高性能的索引策略

独立的列

索引不能是表达式的一部分，也不能是函数的参数。

前缀索引和索引选择性

使用前缀作为索引。

前缀的选择性应接近于索引整个列。

完整列的选择性： select count(distinct city)/Count() from sakila.city_demo 。
前缀索引的选择性：select count(city,3)/count() from sakila.city_demo。

多列索引

在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能。

  • 当服务器对多个索引做相交操作时（通常多个and），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。
  • 当服务器对多个索引做联合操作时（通常多个or），通常需要耗费大量CPU和内存资源在算法的缓存、排序、合并操作上。
  • 优化器不会把这些计算到查询成本中，优化器只关心随机页面读取，这会使得查询的成本被低估，到时该执行计划还不如直接走全表扫描。

选择合适的索引顺序

当不考虑排序和分组时，选择性最高的列放到索引最前列。这时候索引的作用只是用于优化where条件的查找。

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。

优点：

  • 可以把相关的数据保存在一起。
  • 数据访问更快。
  • 使用覆盖索引扫描的查询可以直接使用叶节点中的主键值。

缺点：

  • 聚簇索引最大限度的提高了I/O密集型应用的性能，但是如果数据全部放在内存中，则访问的顺序就没那么重要了，聚簇索引也就没有什么优势了。
  • 插入速度依赖于插入顺序。按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。如果不是按照主键顺序加载数据，那么在加载完成后最好使用optimize table重新组织一下表。
  • 更新聚簇索引代价很高，因为会强制InnoDB将每个更新的行移动到新的位上。
  • 聚簇索引的表插入新行，或者主键被更新导致需要移动行的时候，可能面临“页分裂”的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作。
  • 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
  • 二级索引可能比想象的要更大，因为在二级索引的叶子节点包含了引用行的主键列。
  • 二级索引访问需要两次索引查找，而不是一次。

聚簇索引的每一个叶子节点都包含了主键值、事务ID、用于事务和MVCC的回滚指针以及所有的剩余列。如果主键是一个列前缀索引，InnoDB也会包含完整的主键列和剩下的其他列。

写入目标页可能已经刷到磁盘上并从缓存中移除，护着还没有被加载到缓存中，InnoDB在插入之前不得不先找到并从磁盘读取目标页到内存中，这将导致大量的随机I/O。

因为写入是乱序的，InnoDB不得不频繁的做页分裂操作，以便为新的行分配空间。也分类会导致移动大量数据，一次插入最少需要修改三个页而不是一个页。
由于频繁的页分裂，也会变的稀疏并被不规则的填充，最终数据会有分片。

覆盖索引

如果查询只需要扫描索引而无需回表：

  • 索引条目通常远小于数据行大小，所以只需要读取索引，极大减少数据访问量。
  • 因为索引是按照列支顺序存储的，所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少的多。
  • 由于InnoDB是聚簇索引，覆盖索引对InnoDB特别有用。

覆盖索引无法优化的问题：

  • 没有任何索引能够覆盖这个查询。
  • MySQL不能在索引中执行like操作，这是底层API的限制。

解决方式：

延迟关联：

select * from test join (select id from test where id = 1 and title like '%%') as t1 on t1.id = id

这种优化取决于where条件返回的数据条数。

如果返回一个很大的结果集（第一、二个条件过滤后还是很大），则优化效果不太明显。
如果返回一个小结果集（第一个条件过滤后还是很大，第二个过滤后很小），则优化效果明显。
如果返回一个小结果集（第一个条件过滤后还是很小，第二个过滤后也很小），则优化效果不明显。

注意，一定要看下5.6版本中存储引擎API上的重大改进。索引条件推送。

索引下推

索引条件下推（ICP）是针对MySQL使用索引从表中检索行的情况的一种优化。如果不使用ICP，则存储引擎将遍历索引以在基表中定位行，并将其返回给MySQL服务器，后者将评估这些行的WHERE条件。启用ICP后，如果仅可以使用索引中的列来评估WHERE条件的一部分，则MySQL服务器会将WHERE条件的这一部分下推到存储引擎。然后，存储引擎通过使用索引条目来评估推送的索引条件，并且只有在满足此条件的情况下，才从表中读取行。 ICP可以减少存储引擎必须访问基表的次数以及MySQL服务器必须访问存储引擎的次数。

索引条件下推优化的适用性取决于这些条件:

  • 当需要访问整个表行时，ICP用于范围，ref，eq_ref和ref_or_null访问方法。
  • ICP适用于InnoDB和MyISAM表
  • 对于InnoDB表，ICP仅用于二级索引。 ICP的目标是减少全行读取的次数，从而减少I / O操作。对于InnoDB聚集索引，完整的记录已被读入InnoDB缓冲区。在这种情况下使用ICP不会减少I / O。
  • 不能将引用子查询的条件下推。
  • 涉及存储功能的条件不能下推。存储引擎无法调用存储的功能。
  • 触发条件不能下推。

不使用ICP索引的扫描

  • 获取下一行，首先读取索引元组，然后使用索引元组查找并读取整个表行。

  • 测试适用于此表的WHERE条件部分。根据测试结果接受或拒绝该行。

    使用索引条件下推

    • 获取下一行的索引元组（而不是整个表行）。
    • 测试适用于此表的WHERE条件部分，仅可使用索引列进行检查。
    • 如果不满足条件，请转到下一行的索引元组。如果满足条件，请使用索引元组来定位和读取整个表行。
    • 测试适用于此表的WHERE条件的其余部分。根据测试结果接受或拒绝该行。

使用索引扫描来做排序

MySQL有两种方式可以生成有序的结果：通过排序操作；或者按索引顺序扫描；如果explain出来的type列的值为“index”，则说明使用了索引扫描来做排序。按索引顺序读取数据的速度通常比顺序的全表扫描慢，尤其是在I/O密集型的工作负载时。

MySQL设计索引时，如果有可能，既满足排序，又用于查找行。

只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向都一样时，MySQL才能够使用索引来对结果做排序。如果查询需要关联多张表，则只有当order by子句引用的字段你全部为第一个表时，才能使用索引做排序。

一般情况下，order by子句可以不满足索引的最左前缀的要求，就是前导列为常量时，如果where子句或者join子句对这些列指定了常量，就可以弥补索引的不足。

以下是不能使用索引做排序的查询：

  • 查询使用了两种不同的排序方向，索引列都是正序排序的
  • order by子句中引用了一个不再索引中的列
  • where和order by中的列无法组合成索引的最左前缀
  • 索引列的第一列不是常量查询，而是范围查询
  • inventory_id列上有多个等于条件，这也是一种范围查询

压缩（前缀压缩）索引 和 未使用的索引

冗余和重复索引

重复索引是指在相同的列上按照相同的顺序创建相同类型的索引。

冗余索引通常发生在为表添加新索引的时候。
information_schema也可以查询哪些是冗余的索引。

可以通过查询information_schema.index_statistics可以查询到每个索引的使用频率。

SCHEMATA表：提供了关于数据库的信息。

TABLES表：给出了关于数据库中的表的信息。

COLUMNS表：给出了表中的列信息。

STATISTICS表：给出了关于表索引的信息。

USER_PRIVILEGES表：给出了关于全程权限的信息。该信息源自mysql.user授权表。

SCHEMA_PRIVILEGES表：给出了关于方案（数据库）权限的信息。该信息来自mysql.db授权表。

TABLE_PRIVILEGES表：给出了关于表权限的信息。该信息源自mysql.tables_priv授权表。

COLUMN_PRIVILEGES表：给出了关于列权限的信息。该信息源自mysql.columns_priv授权表。

CHARACTER_SETS表：提供了关于可用字符集的信息。

COLLATIONS表：提供了关于各字符集的对照信息。

COLLATION_CHARACTER_SET_APPLICABILITY表：指明了可用于校对的字符集。

TABLE_CONSTRAINTS表：描述了存在约束的表。

KEY_COLUMN_USAGE表：描述了具有约束的键列。

ROUTINES表：提供了关于存储子程序（存储程序和函数）的信息。此时，ROUTINES表不包含自定义函数（UDF）。

VIEWS表：给出了关于数据库中的视图的信息。

TRIGGERS表：提供了关于触发程序的信息。

索引和锁

InnoDB在二级索引上使用共享锁，但访问主键索引需要排他锁。这消除了使用覆盖索引的可能性，并且使得select for update比lock in share mode或非锁定查询要慢很多。

索引案例学习

支持多种过滤条件

避免多个范围条件

优化排序

维护索引和表

维护表有3个目的：找到并修复损坏的表，维护准确的索引统计信息，减少碎片。

找到并修复损坏的表

表损坏（corruption）是件很糟糕的事情。对于MyISAM，表损坏通常是系统崩溃导致的。

损坏的索引会导致查询返回错误的结果或者莫须有的主键冲突等问题，严重时甚至还会导致数据库的崩溃。

修复：

  • check table，能找出大多数表和索引错误。有些存储引擎不支持该命令。

  • repair table，和check类似。

  • alter table innodb_tbl engine=innodb，修改表的存储引擎为当前引擎重建表。

  • 离线工具myisamchk

  • 将数据导出在重新导入。

  • 如果损坏的是行数据或者系统区域，以上办法无效。

  • 如果损坏系统区域或者行数据，可以从备份中恢复表，或者尝试从损坏的数据文件中尽可能的恢复数据。

如果InnoDB引擎的表出现损坏，那么一定是发生了严重的错误，需要立刻调查原因。具体细节可以查看MySQL手册。

更新索引统计信息

查询优化器会通过两个API了解存储引擎的索引值的分布信息
record_in_range()通过传入两个边界值获取这个范围大概有多少条纪律
info()返回各种类型的数据，包括索引的基数（每个键值有多少纪律）

减少索引和数据的碎片

  • 行碎片
  • 行间碎片
  • 剩余空间碎片

总结

三个原则：

  • 单行访问时很慢的。最好读取块中能包含尽可能多的所需要的行。使用索引可以创建位置引用以提升效率。

  • 按顺序访问范围数据是很快的，这有两个原因。第一、顺序IO不需要多次磁盘寻道，所以比随机IO快很多。第二、如果服务器能够按需要顺序读取数据，那么就不再需要额外的排序操作，并且group by查询也无需再做排序和将行按组进行聚合计算了。

  • 索引覆盖查询是很快的。

一般来说，我们建议按响应时间来对查询进行分析。

如果一个查询无法从所有可能的索引中获益，则应该看看是否可以创建一个更合适的索引来提升性能。

剖析MySQL查询

对查询进行性能剖析有两种方式，每种方式都有各自的问题。

剖析服务器负载

服务器端可以有效的审计效率低下的查询。

捕获MySQL的查询到日志文件中

慢查询日志是一种轻量而且功能全面的性能剖析工具，是优化服务器查询的利器。可以通过修改针对每个链接的long_query_time的阈值来捕获所以的查询。

通用日志在查询请求到服务器时进行记录，所以不包含响应时间和执行计划等重要信息。日志信息记录到数据库表中。

分析查询日志

工具：pt-query-digest

剖析报告：
查询日志

详细报告：
详细查询日志

剖析单条查询

show profile

show profile命令是在5.1版本之后引入的，默认是禁用的，可以通过set profiling=1动态的修改。这个功能有一定的作用，将来能会被Performance Schema所取代。

下面是对一个视图的剖析结果：

详细查询日志

详细查询日志

详细查询日志

剖析报告给出查询执行每个步骤花费的时间，看结果无法快速确定哪个步骤花费时间最多，因为输出是按照执行顺序排序，而不是花费的时间排序的。如果不使用show profile命令。还可以直接查询information_schema中对应的表，按照格式化输出。

详细查询日志

效果比看show profile输出的结果好很多，可以很直观的看到哪些步骤花费时长较长。

查询性能优化

查询慢的原因

查询最重要的是响应时间，如果要优化查询，实际上要优化其子任务，要么消除其中一些子任务，要么减少子任务的执行次数，要么让子任务运行更快。

MySQL执行查询的时候，查询需要在不同的地方花费时间，如网络、cpu、生成统计信息和执行计划、锁等待等。在每一个消耗大量时间的查询案例中，我们都能看到一些不必要的额外操作、某些操作被额外的执行了很多次、某些操作执行的太慢等。

优化查询的目的就是减少和消除这些操作所花费的时间。

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

查询性能低下最基本的原因是访问的数据太多。某些查询不可避免的需要筛选大量数据，大部分查询都可以通过减少访问的数据量的方式进行优化。对于低效查询可以分为两个步骤分析：

    1. 确认应用程序是否检索大量超过需要的数据。如访问过多的行或者列。
    1. 确定MySQL服务器层是否在分析大量超过需要的数据行。

是否向数据库请求了不需要的数据

  • 查询不需要的记录

  • 多表关联返回全部的列

  • 总是取出全部的列

  • 重复查询相同的数据

MySQL是否在扫描额外的记录

在确定查询只返回需要的数据以后，最简单的衡量查询开销的三个指标如下：

  • 响应时间

  • 扫描的行数

  • 返回的行数

响应时间：响应时间只是一个表面上的值。响应时间是服务时间和排队时间。服务时间指数据库处理这个时间花费的时间。排队时间指服务器因为等待某些资源而没有真正执行查询的时间。

快速上限估计：了解查询需要哪些索引以及它的执行计划是什么，然后计算大概需要多少个顺序和随机IO，再用其乘以在具体硬件条件下一次IO的消耗时间。

扫描的行数和返回的行数： 分析查询时，查看扫描的行数一定程度上可以反映出该查询找到需要的数据的效率高不高。

扫描的行数和访问类型：在explain语句中的type列反映了访问类型。访问类型有很多种，从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等。从慢到快，扫描的行数从小到大。

如果查询没有找到合适的访问类型，那么解决的最好办法通常是增加一个合适的索引。

例如：

select * from sakila.film_actor where film_id = 1;

这个查询返回10行数据，从explain的结果可以看到，MySQL在索引idx_fk_film_id上使用了ref访问类型来执行查询：

详细查询日志

explain的结果也显示MySQL预估需要访问10行数据。查询优化器认为这种访问类型可以高效的完成查询。如果我们删除索引再来运行：

详细查询日志

正如我们预料的，查询变成全表扫描，MySQL预估会扫描5073条记录来完成查询。

一般MySQL使用如下三种方式应用WHERE条件，从好到坏依次是：

  • 在索引中使用WHERE条件来过滤不匹配的记录。这是在存储引擎层完成的。

  • 使用索引覆盖扫描（Extra:using index）来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在MySQL服务器层完成的，但无需再回表查询记录。

  • 从数据表中返回数据（Extra:using where），然后过滤不满足条件的记录。这是在服务器层完成的，MySQL需要从数据表中读出来然后再进行过滤。

虽然例子说明了好的索引多么重要，但是也不是说增加了索引就能让扫描的行数等于返回的行数。例如使用聚合函数的查询：

select actor_id,count(*) from sakila.film_actor group by actor_id

这个例子没有什么索引能够让这样的查询减少需要扫描的行数。

通常如果发现查询需要扫描大量的数据但只返回少数的行，那么通常可以尝试使用下面的技巧去优化：

  • 使用覆盖索引扫描，把所有需要的列放到索引中，这样存储引擎无需回表获取对应行就可以返回结果了。

  • 改变库表结构。例如使用单独的汇总表。

  • 重写复杂的查询，让MySQL优化器能够以更优化的方式执行这个查询。

重构查询的方式非锁

一个复杂的查询还是多个简单的查询

衡量一下一个复杂查询和多个简单查询的优劣。

切分查询

将大查询分为小查询，每个查询功能完全一样，只完成一小部分，每次返回一小部分查询结果。删除旧数据就是一个很好的例子

分解关联查询

对每一个表进行一次单表查询，然后将结果在应用程序中关联。

分解关联查询的优点：

  • 让缓存效率更高。

  • 执行单个查询可以减少锁的竞争。

  • 做应用层关联，可以更好的对数据库拆分，更容易做到，高性能可扩展。

  • 查询效率本身效率也可能会有所提升。

  • 可以减少冗余记录的查询。

  • 这样做相当于在应用中实现了哈希关联而不是MySQL的嵌套循环关联。

查询执行的基础

查询执行路径：

查询执行路径

MySQL客户端/服务器通信协议

MySQL客户端与服务器之间的通信协议是“半双工”的，这意味着，要么服务器项客户端发送数据，要么是由客户端向服务器发送数据，这两个动作不能同时发生。

查询状态

show full processlist命令返回结果中的Command列就表示当前的状态。

Sleep: 线程正等待客户端发送新的数据。
Query: 线程正在执行查询或者正在将结果发送给客户端。
Locked: 该线程正在 等待表锁。
Analyzing and statistics: 线程正在收集存储引擎的统计信息，并生成查询的执行计划。
Copying to tmp table [on disk]: 线程正在执行查询，并且将其结果集都复制到一个临时表中，这种状态一般要么是group by操作，要么是文件排序操作，或者union操作。如果这个状态后面还有“on disk”标记，那表示MySQL正在将一个内存临时存放在磁盘上。
Sorting result: 线程正在对结果集进行排序。
Sending data: 线程可能在多个状态之间传送数据，或者在生成结果集，或者在向客户端返回数据。

查询缓存

如果SQL命中缓存，则直接从缓存中获取数据，返回客户端。

查询优化处理

解析SQL、预处理、优化SQL执行计划。

语法解析器和预处理

MySQL通过关键字将SQL语句进行解析，并生成一个对应的解析树。MySQL解析器将使用MySQL语法规则验证和解析查询。

预处理则根据一些MySQL规则进一步检查解析树是否合法，验证权限。

查询优化器

一条查询可以有很多执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。

show status like 'last_query_host'

每个表或者索引的页面个数，索引的基数、索引和数据行的长度、索引分布情况。

有很多原因会导致MySQL优化器选择错误的执行计划：

  • 统计信息不准确。MySQL依赖存储引擎提供的统计信息来评估成本，但是有的存储引擎提供的信息是准确的，有的偏差可能非常大。例如，InnoDB因为其MVCC的架构，并不能维护一个数据表的行数的精确统计信息。
  • 执行计划中的成本估算不等同与实际执行的成本。所以即使统计信息精准，优化器给出的执行计划也可能不是最优的。例如有时候某个执行计划虽然需要读取更多的页面，但是它的成本却更小。因为如果这些页面都是顺序读或者这些页面都已经在内存中的话，那么它的访问成本将很小。MySQL层面并不知道哪些页面在内存中、哪些在磁盘上，所以查询实际执行过程中到底需要多少次物理I/O是无法得知的。
  • MySQL只是基于成本模型选择最优的执行计划，而有些时候这并不是最快的执行方式。
  • MySQL从不考虑其他并发执行的查询，这可能会影响到当前查询的速度。
  • MySQL也并不是任何时候都是基于成本优化。如果存在全文搜索的match 子句，则在存在全文索引的时候就是用全文索引。即使有时候使用别的索引和where条件可以远比这种方式要快。
  • MySQL不会考虑不受其控制的操作的成本，例如执行存储过程或者用户自定义函数的成本。
  • 优化器有时候无法去估算所有可能的执行计划，所以他可能错过实际上最优的执行计划。

优化策略可以分为两类：

  • 静态优化：可以直接对解析树进行分析，并完成优化。
  • 动态优化： 动态优化则和查询的上下文有关，也可能和很多其他因素有关，例如where条件中的取值、索引中条目对应数据行数等。

MySQL对查询静态优化只做一次，但对查询的动态优化则在每次执行时都需要重新评估。有时候甚至在查询的执行过程中也会重新优化。

优化类型：

  • 重新定义关联表的顺序：数据表的关联并不总是按照在查询中指定的顺序进行。决定关联的顺序是优化器 很重要的一部分功能。
  • 将外连接转化为内连接：并不是所有的outer join语句都必须以外连接的方式执行。例如where条件、库表结构都可以能会让外连接等价于一个内连接。
  • 使用等价变换规则：MySQL可以使用一些等价变换来简化并规范表达式。
  • 优化count()、min()、max()：索引和列是否可为空通常可以帮助MySQL优化这类表达式。例如，需要找到某一列的最小值，则需要查询对应B-Tree索引最左端的记录，MySQL可以直接获取索引的第一行记录。
  • 预估并转化为常数表达式：当MySQL检测到一个表达式可以转化为常数的时候，就会一直把该表达式作为常数进行优化处理。例如，一个用户自定义变量在查询中没有发生变化时就可以转为一个常数。
1
2
explain select film.film_id, film_actor.actor_id from sakila.film
inner join sakila.film_actor usring(film_id) where film.film_id = 1;

MySQL分两步来执行这个查询，也就是上面执行计划的两行输出。第一步先从film表找到需要的行。因为在film_id字段上有主键索引，所以MySQL优化器知道这会返回一行数据，优化器在生成执行计划的时候，就已经通过索引信息知道将返回多少行数据。

在执行计划的第二部，MySQL将第一步中返回的film_id列当做一个已知取值的列来处理。

覆盖索引扫描

之前说过

子查询优化

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

提前终止查询

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

等值传播

如果两个列的值通过等式关联，那么MySQL能够把其中一个列的where条件传递到另一列上。

列表in()的比较

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

数据和索引的统计信息

因为服务器层没有任何统计信息，所以MySQL查询优化器在生成查询计划时，需要向存储引擎获取相应的统计信息。存储引擎则提供给优化器对应的统计信息：每个表或者索引有多少个页面、每个表的每个索引的基数是多少、数据行和索引长度、索引的分布信息等。

MySQL如何执行关联查询

在MySQL中并不仅仅是查询需要到两个表匹配才叫关联，所以在MySQL中，每一个查询，每一个片段（子查询、甚至于单表的select）都可能是关联。

对于union查询，MySQL先将一系列的单个查询结果放到一个临时表中，然后再重新读取初临时表数据来完成union查询。读取临时表也是一种关联。

MySQL关联执行的策略：MySQL对任何关联都执行嵌套循环关联操作，即MySQL先在一个表中循环取出单条数据，然后再嵌套循环到下一个表中寻找匹配的行，知道找到素有表中匹配的行为止。

从本质上说，MySQL对所有的类型的查询都以同样的方式运行。例如，MySQL在from子句中遇到子查询时，限制性子查询并将其结果放到一个临时表中，然后将这个临时表当做一个普通表对待。简而言之，当前版本的MySQL都会讲所有的查询类型都转换成类似的执行计划。

全外连接就无法通过嵌套循环和回溯的方式完成，当发现关联表中没有找到任何匹配行的时候，则可能是因为关联是恰好从一个没有任何匹配的表开始。

内连接：select tbl1.col1,tbl2,col2 from tbl1 inner join tbl2 using(col3) where tbl1.col1 in(5,6)

外连接：select tbl1.clo1, tbl2.col2 from tbl1 left outer join tbl2 using(col3) where tbl1.col1 in(5,6)

执行计划

MySQL并不会生成查询字节码来执行查询。MySQL生成的查询的一颗指令树，然后通过存储引擎执行完成这棵树并返回结果。

MySQL总是从一个表开始一直嵌套循环、回溯完成所有表关联。所以MySQL的执行计划是一棵左侧深度优先的树。

关联查询优化器

MySQL优化器最重要的一部分就是关联查询优化，它决定了多个表关联查询时的顺序。

1
select film.film_id,film.title,film.release_year,actor.actor_id,actor.first_name,actor.last_name from sakila.film inner join sakila.film_actor using(film_id) inner join sakila.actor using(actor_id);

可以使用Straight_join关键字重写查询（不会重新排序）。

当需要关联的表超过optimizer_search_depth的限制的时候，就会选择“贪婪”搜索模式了。

排序优化

排序是一个成本很高的操作，所以尽可能避免对大量数据进行排序。

MySQL有如下两种排序算法：

  • 两次传输排序：读取行指针和需要排序的字段，对其进行排序，然后再根据排序结果读取所需要的数据行。这需要两次数据传输，第二次读取数据的时候，因为是读取排序列进行排序后的所有记录，这会产生大量的随机I/O，所以这两次数据传输成本非常高。
  • 单次传输排序：先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果。缺点是如果需要返回的列非常多、非常大、会额外占用大量的空间，这些列堆排序操作本身来说是没有任何作用的。因为单条排序记录很大，所以可能会有更多的排序块需要合并。

5.6版本以后，对排序进行了重大改进，只对最后结果进行排序。

查询执行引擎

MySQL只是简单的根据执行计划给出的指令逐步执行。MySQL在优化阶段为每个表创建了一个handler实例，优化器根据这些实例的接口可以获取表的相关信息，包括标的所有列名、索引统计信息。

返回结果给客户端

查询执行的最后一个阶段是将结果返回给客户端。
MySQL将结果集返回客户端是一个增量、逐步返回的过程。
结果集中每一行数据都会以一个满足MySQL客户端/服务器通信协议的封包发送，再通过TCP协议进行传输，传输过程中，可能对MySQL的封包进行缓存然后批量传输。

MySQL查询优化器的局限性

关联子查询

MySQL的子查询实现非常糟糕，最糟糕的一类查询是where条件中包含in()的子查询语句。
一般建议使用左外连接重写该查询。

union 的限制

MySQL无法将限制条件从外层下推到内层，这使得原本能够限制部分返回结果的条件无法应用到内层查询的优化上。如果希望union能根据limit只取部分结果集，则需要在每个union中使用limit。

索引合并优化

MySQL能够访问单个表的多个索引以合并和交叉过滤的方式定位需要查找的行。

等值传递

如果MySQL优化器发现存在Where、ON或者using子句，将这个列表的值和另一个表的某个列相关联。
优化器会将in()列表都复制应用到关联的各个表中。通常因为各个表增加了过滤条件，优化器可以更高效的从存储引擎过滤记录。但是如果这个列表非常大，则导致优化和执行变得非常慢。

并行执行

MySQL无法利用多核特性来并行执行查询。

哈希关联

MySQL的所有关联都是嵌套循环关联。

松散索引扫描

MySQL不支持松散索引扫描，无法按照不连续的方式扫描索引。

最大值和最小值优化

对于min()和max()查询支持并不好。

select min(actor_id) from sakila.actor where first_name == 'PENELOPE';

因为first_name上没有索引，因此MySQL会进行一次全表扫描。

select actor_id from sakila.actor use index(primary) where first_name = 'Penelope limit 1'

在同一个表上查询和更新

MySQL不允许对同一张表同时进行查询和更新。

update tb1 as outer_tb1 set cnt = (select count(*) from tb1 as innner_tb1 where innner_tb1.type = outer_tb1.type )

解决方式:update tb1 inner join(select type,count(*) as cnt from tb1 group by type) as der using (type) set tb1.cnt = der.cnt

查询优化器的提示(hint)

HIGH_PRIORITY和LOWPRIORITY

HIGH_PRIORITY用于SELECT语句的时候，MySQL会将此SELECT语句重新 调度到所有正在等待表锁以便修改数据的语句之前。 HIGH_PRIORITY还可以用于INSERT语句，其效果只是简单的抵消了全局LOW_PRIORITY设置对该语句的影响。

LOW_PRIORITY则会让该语句一致处于等待状态，只要队列中海油需要访问同一个表的语句。LOW_PRIORITY提示在SELECT、INSERT、UPDATE和DELETE语句中都可以使用。

这两个提示针对表锁的存储引擎有效，千万不要在InnoDB或者其他有细粒度锁机制的并发控制引擎中使用。

DELAYED

这个提示对INSERT和REPLACE有效。MySQL会将使用该提示的语句立即返回给客户端，并插入的行数据放入到缓冲区，然后在表空闲的时候批量将数据写入。

这个用法有一些限制: 并不是所有的存储引擎都支持这样的做法，并且该提示会导致函数LAST_INSERT_ID()无法正常工作。

STRAIGHT_JOIN

这个提示可以放置在SELECT语句的SELECT关键字之后，也可以放置在任何两个关联表的名字之间。第一个用法是让查询中所有表按照在语句中出现的顺序进行关联。第二个用法则是固定其前后两个表的关联顺序。

SQL_SMALL_RESULT和SQL_BIG_RESULT

这两个提示对SELECT语句有效。它告诉优化器对GROUP BY或者DISTINCT查询如何使用临时表及排序。SQL_SMALL_RESULT告诉优化器结果集会很小，可以将结果集放在内存中的索引临时表，以避免排序操作。SQL_BIG_RESULT，则告诉优化器结果集可能非常大，建议使用磁盘临时表做排序操作。

SQL_BUFFER_RESULT

这个提示告诉优化器将查询结果放入到一个临时表，尽可能快的释放表。缺点就是服务器端会占用很多内存。

SQL_CACHE和SQL_NO_CACHE

这个提示告诉MySQL这个结果集是否应该缓存在查询缓存中。

SQL_CALC_FOUND_ROWS

查询中加上该提示MySQL会计算出去LIMIT子句后这个查询要返回的结果集的总数，而实际上只返回limit要求的结果集，可以通过found_row()获得这个值。

FOR UPDATE和LOCK IN SHARE MODE

这两个提示控制SELECT语句的锁机制，只针对实现行级锁的存储引擎有效。唯一内置支持这两个提示的引擎就是InnoDB。通常避免使用这两个提示。

USE INDEX、IGNORE INDEX和FORCE INDEX

这个提示会告诉优化器使用或者不使用哪些索引来查询记录。

新增一些参数来控制优化器的行为：

optimizer_search_depth 这个参数控制优化器在穷举执行计划时的限度。如果查询时间处于“Statistics”状态，那么可以考虑调低此参数。
optimizer_prune_level 该参数默认是打开的，这让优化器会根据需要扫描的行数来决定是否跳过某些执行计划。
optimizer_switch 这个变量包含了开启、关闭优化器特性的标志位。

开启和关闭索引下推
SET optimizer_switch = ‘index_condition_pushdown=off’;
SET optimizer_switch = ‘index_condition_pushdown=on’;

优化特定类型的查询

优化count()查询

count()是一个特殊函数。
它可以统计某个列值的数量，也可以统计行数。在统计列值时要求列值是非空的。如果在count()的括号中指定了列或者列的表达式，则统计的就是这个表达式有值的结果数。
count()另一个作用是统计结果集的行数。当MySQL确认括号内的表达式值不可能为空时，实际上就是在统计行数。最简单的就是当我们使用count(*)的时候，这种情况下通配符并不会扩展成所有的列，它会忽略所有的列而直接统计所有的行数。

优化关联查询
  • 确保ON或者using子句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。一般来说说，除非有其他理由，否则只需要在关联顺序中的第二个表的响应列上创建索引。
  • 确保任何的group by和order by中的表达式只涉及到一个表中的列，这样MySQL才有可能使用索引来优化这个过程。
  • 当升级MySQL的时候需要注意: 关联语法、运算符优先级等其他可能会发生变化的地方。
优化子查询

尽量使用关联查询代替。5.6版本后，可以忽略关于优化子查询的建议。

优化GROUP BY 和DISTINCT

MySQL优化器会在内部处理的时候相互转化这两类查询。他们都可以使用索引来优化，这也是最有效的优化办法。

当无法使用索引的时候，group by使用两种策略来完成: 使用临时表或者文件排序来做分组。

如果需要对关联查询做分组(group by)，并且是按照查找表中的某个列进行分组，那么通常采用查找表的标识列分组的效率会比其他列更高。

select actor.first_name, actor.last_name, count(*) from sakila.film_actor inner join sakila.actor using(actor_id) group by actor.first_name, actor.last_name

优化后：
select actor.first_name,actor.last_name,count(*) from sakila.film_actor inner join sakila.actor using(actor_id) group by film_actor.actor_id

事实上，我们建议将MySQL的SQL_MODE设置为包含ONLY_FULL_GROUP_BY，这时MySQL会对这个类查询直接返回一个错误，提醒你重写这个查询。

如果没有通过ORDER BY子句显式的指定排序列，当查询使用GROUP BY子句的时候 ，结果集会自动按照分组的字段进行排序。不需要排序可以使用ORDER BY NULL不再排序。

分组查询的一个变种就是要求MySQL对返回的数组结果再做一次超级聚合。可以使用WITH ROLLUP子句来实现这种逻辑，但可能会不够优化。建议尽可能将WITH ROLLUP功能转移到应用程序中处理。

优化limit分页

优化偏移量大的查询分页最简单的办法就是尽可能的使用索引覆盖扫描，而不是查询所有的列。

select film_id, description from sakila.film order by title limit 50,5

优化后：
select film.film_id,film.description from sakila.film inner join(select film_id from sakila.film order by title limit 50, 5) as lim using(film_id)
limit和offset的问题，其实是offset的问题，我们可以使用相关列的排序规避offset。
例如：select * from sakila.rental order by rental_id sesc limit 20

优化SQL_CALC_FOUND_ROWS

分页的时候，另一个常用的技巧是在limit语句中加上SQL_CALC_FOUND_ROWS提示，可以获得去掉limit以后满足条件的行数，可以用作为分页的总数。该提示代价可能会很高。

优化union查询

MySQL总是通过创建并填充临时表的方式来执行union查询。一次很多优化策略在union查询中都没法很好的使用，经常需要手动的将where、limit、order by等子句下推到union各个子查询中，以便优化器可以充分利用这些条件进行优化。

除非确实需要服务器消除重复的行，否则就一定要使用Union all。如果没有all关键字，MySQL会给临时表加上distinct选项，这会导致对整个临时表的数据做唯一性检查。

静态查询分析

Percona Toolkit中的pt-query-advisor能够解析查询日志、分析查询模式，然后给出所有可能存在潜在问题的查询。

使用用户自定义变量

以下情况不能使用自定义变量：

  • 使用自定义变量的查询，无法使用查询缓存。
  • 不能在使用常量或者标识符的地方使用自定义变量，例如表名，limit子句。
  • 生命周期在一个连接中有效，所以不能用他们来做连接间的通信。
  • MySQL在某些场景下可能会将这些变量优化掉，这可能导致代码不按照预想的方式运行。
  • 赋值的顺序和赋值的时间并不总是固定的。
  • 赋值符号:=的优先级非常低，赋值表达式应该使用明确的括号。
  • 使用未定义变量不会产生任何语法错误，如果没有意思到这一点，非常容易犯错。

使用用户自定义变量的一个重要特性是你可以再给一个变量复制的同时使用这个变量。

  • 优化排名语句
  • 避免重复查询刚刚更新的数据
  • 统计更新和插入的数量
  • 确定取值的顺序
  • 编写偷懒的union
  • 查询运行时计算总数和平均值
  • 模拟group语句中的first和last
  • 对大量数据做一些数据计算
  • 计算一个大表的md5散列值
  • 编写一个样本处理函数，当样本中的数值超过某个边界值的时候将其变成0
  • 模拟读/写游标
  • 在show语句的where子句中加入变量值

查询优化器的提示

优化特定类型的查询

案例学习

MySQL的高级特性

  • 分区表

  • 视图

  • 外键约束

  • MySQL内部存储代码

  • 游标

  • 绑定变量

  • 用户自定义函数

  • 插件

  • 字符集和校对

  • 全文索引

  • 分布式（XA）事务

  • 查询缓存

参考资料

高性能MySQL 第3版 中文 宁海元，周振兴，彭立勋，翟卫祥，刘辉译.
MySQL 8.0 Reference Manual