《高性能MySQL》读书笔记

发布于 2020-03-21  3.29k 次阅读

《高性能MySQL》读书笔记

第一章

MySQL服务器逻辑架构图

image-20200321093004761

  • 顶层服务层并非MySQL独有,大多数基于网络对外提供服务的中间件都有这一层,主要是连接处理、授权认证、安全等
  • 第二层是MySQL的服务器层,大多数核心服务功能都在这一层,包括解析、优化、查询、缓存以及所有内置函数,所有跨存储引擎的功能都在这一层实现:存储过程、触发器、视图
  • 第三层是存储引擎层,不同的存储引擎向上提供相同的api的各自实现,接口屏蔽了存储引擎之间的差异,存储引擎不会解析SQL,只是简单的响应上层的请求

并发控制

  • 共享锁(读锁);

    多个客户可以在同时间访问同一个资源

  • 排他锁(写锁)

    一个排他锁会阻塞其他的读锁和写锁

  • 锁粒度的选择:锁定对象更具选择性的时候即锁粒度越小,系统并发性越高,但是加锁(获得锁、检查锁是否释放、释放锁)也需要消耗资源,如果系统花费了大量的时间来管理锁,而不是存取数据,那么系统的性能可能会受到影响.

    而锁策略就是在锁开销与数据安全性之间寻求平衡.

  • 表锁

    • 是MySQl最基本的锁策略也是开销最小的策略,表锁通常和存储引擎实现相关
    • 某些特定场景,如READ LOCAL表锁支持某些类型的并发操作
    • 服务器会为ALTER TABLE之类的语句加表锁,忽略具体表存储引擎的锁机制

  • 行级锁

    • 行级锁可以最大程度的支持并发处理
    • 行级锁只在存储引擎层实现(InnoDB、XtraDB等)

事务

  • 幻读指的是当事务在读取某个范围内的记录时,另外一个事务又在该范围内插入了新的纪录
  • 可串行化指的是强制事务串行执行

死锁

  • InnoDB目前处理死锁的方法是,将持有最少行级排他锁的事务进行回滚
  • 锁的行为和顺序是和存储引擎相关的

多版本并发控制

  • 大多数事务型的存储引擎都实现了MVCC,同样的,不仅是MySQL,其他数据库系统也实现了MVCC,但是MVCC并没有一个统一的标准

  • 可以认为MVCC是行级锁的一个变种,但是他在很多情况下避免了加锁操作.不同的MVCC实现方式不同,典型的有乐观并发控制和悲观并发控制

  • InnoDB简化MVCC:

    • 每行记录后有两个隐藏列,一个列保存了行的创建时间、一个保存行的过期时间(删除时间),两列存储的都不是实际时间值而是系统版本号.

    每开始一个新的事务,系统版本号都会递增,事务开始时刻的系统版本号会作为事务的版本号,下面看一下在REPEATABLE READ 隔离级别下，MVCC具体是如何操作的。

    • SELECT

      InnoDB会根据以下两个条件检查每行记录：

      • a. InnoDB 只 查 找 版 本 早 于 当 前 事 务 版 本 的 数 据 行（也就是，行的系统版本号小于或等于事务的系统版本号）,这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的。

      • b. 行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除。

      • 只有符合上述两个条件的记录，才能返回作为查询结果。

    • INSERT

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

    • DELETE

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

    • UPDATE

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

    • 保存这两个额外系统版本号，使大多数读操作都可以不用加锁。这样设计使得读数据操作很简单，性能很好，并且也能保证只会读取到符合标准的行。不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作，以及一些额外的维护工作。

    • MVCC只在REPEATABLE READ 和READ COMMITTED 两个隔离级别下工作。其他两个隔离级别都和MVCC不兼容,因为READ UNCOMMITTED 总是读取最新的数据行，而不是符合当前事务版本的数据行。而SERIALIZABLE 则会对所有读取的行都加锁。

InnoDB存储引擎

  • InnoDB使用间隙锁策略防止幻读,间隙锁使得MySQL不仅锁定查询涉及的行,还会对索引的间隙进行锁定

第四章

选择优化的数据类型

几个简单的原则

  • 更小的通常更好

  • 简单的就好

    例如用整形存储ip地址(整形比字符串操作代价更低)

  • 尽量避免NULL

    如果查询中包含可为NULL 的列，对MySQL来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空 间，在MySQL里也需要特殊处理。当可为NULL 的列被索引时，每个索引记录需要 一个额外的字节，在MyISAM里甚至还可能导致固定大小的索引（例如只有一个整数列的索引）变成可变大小的索引。

整数类型

  • 整数类型有可选的UNSIGNED 属性，表示不允许负值，这大致可以使正数的上限提高一倍
  • 有符号和无符号类型使用相同的存储空间，并具有相同的性能
  • MySQL可以为整数类型指定宽度，例如INT（11） ，对大多数应用这是没有意义的：它不会限制值的合法范围，只是规定了MySQL的一些交互工具（例如MySQL命令行客戶端）用来显示字符的个数。

实数类型

  • MySQL可以为整数类型指定宽度，例如INT（11） ，对大多数应用这是没有意义 的：它不会限制值的合法范围，只是规定了MySQL的一些交互工具（例如MySQL命令行客戶端）用来显示字符的个数。
  • 浮点类型在存储同样范围的值时，通常比DECIMAL 使用更少的空间。FLOAT 使用4个字节存储。DOUBLE用8个字节，相比FLOAT有更高的精度和更大的范围。和整数类型一样，能选择的只是存储类型
  • 假设要存储财务数据精确到万分之一分，则可以把所有金额乘以一百万，然后将结果存储在BIGINT里，这样可以同时避免浮点存储计算不精确和DECIMAL 精确计算代价高的问题

字符串类型

  • HAR 非常适合存储密码的MD5 值，因为这是一个定⻓的值。
  • 填充和截取空格的行为在不同存储引擎都是一样的，因为这是在MySQL服务器层进行处理的。

BLOB和TEXT

  • 当BLOB 和TEXT 值太大时，InnoDB会使用专⻔的“外部”存储区域来进行存储，此时每个值在行内需要1〜4个字节存储一个指针，然后在外部存储区域存储实际的值。
  • BLOB 和TEXT 家族之间仅有的不同是BLOB 类型存储的是二进制数据，没有排序规则或字符集，而TEXT 类型有字符集和排序规则。
  • MySQL对BLOB 和TEXT 列进行排序与其他类型是不同的：它只对每个列的最前max_sort_length 字节而不是整个字符串做排序。如果只需要排序前面一小部分字符，则可以减小max_sort_length 的配置，或者ORDER BY SUSTRING（column,length ） 。

时间类型

  • 除了特殊行为之外，通常也应该尽量使用TIMESTAMP ，因为它比DATETIME 空间效率更高。有时候人们会将Unix时间截存储为整数值，但这不会带来任何收益。用整数保存时间截的格式通常不方便处理，所以我们不推荐这样做。

位数据类型

  • MySQL把BIT 当作字符串类型，而不是数字类型

选择标识符

  • 标识列选择数据类型时，应该选择跟关联表中的对应列一样的类型
  • MySQL在内部使用整数存储ENUM 和SET 类型，然后在做比较操作时转换为字符串。
  • 整数通常是标识列最好的选择
  • ENUM 和SET 列适合存储固定信息，例如有序的状态、产品类型、人的性别。
  • 在字符串类型下:
    • 如果可能，应该避免使用字符串类型作为标识列，因为它们很消耗空间，并且 通常比数字类型慢。尤其是在MyISAM表里使用字符串作为标识列时要特别小心。 MyISAM默认对字符串使用压缩索引，这会导致查询慢得多。在我们的测试中，我们注意到最多有6倍的性能下降。
    • 对于完全“随机”的字符串也需要多加注意，例如MD5()、SHA1() 或者UUID() 产生的字符串。这些函数生成的新值会任意分布在很大的空间内，这会导致INSERT以及一些SELECT 语句变得很慢
    • 因为插入值会随机地写到索引的不同位置，所以使得INSERT 语句更慢。这会导致⻚分裂、磁盘随机访问，以及对于聚簇存储引擎产生聚簇索引碎片。
    • SELECT 语句会变得更慢，因为逻辑上相邻的行会分布在磁盘和内存的不同地方。 随机值导致缓存对所有类型的查询语句效果都很差，因为会使得缓存赖以工作的访 问局部性原理失效。如果整个数据集都一样的“热”，那么缓存任何一部分特定数据到内存都没有好处；如果工作集比内存大，缓存将会有很多刷新和不命中。
    • 如果存储UUID值，则应该移除“-”符号；或者更好的做法是，用UNHEX() 函数转换UUID值为16字节的数字，并且存储在一个BINARY（16）列中。检索时可以通过 HEX() 函数来格式化为十六进制格式。
    • UUID() 生成的值与加密散列函数例如SHA1() 生成的值有不同的特征：UUID值虽然分布也不均匀，但还是有一定顺序的。尽管如此，但还是不如递增的整数好用。

范式与反范式

  • 范式化针对写密集场景能够带来以下好处:

    • 范式化的更新操作通常比反范式化要快。

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

    据

    • 范式化的表通常更小，可以更好地放在内存里，所以执行操作会更快。

    • 很少有多余的数据意味着检索列表数据时更少需要DISTINCT 或者GROUP BY 语

    句。还是前面的例子：在非范式化的结构中必须使用DISTINCT 或者GROUP BY 才

    能获得一份唯一的部⻔列表，但是如果部⻔（DEPARTMENT ）是一张单独的表，

    则只需要简单的查询这张表就行了。

汇总表和缓存表

总结

  • 良好的schema设计原则是普遍适用的，但MySQL有它自己的实现细节要注意。概
  • 括来说，尽可能保持任何东西小而简单总是好的。MySQL喜欢简单，需要使用数据库的人应该也同样会喜欢简单的原则：
  • 尽量避免过度设计，例如会导致极其复杂查询的schema设计，或者有很多列的表 设计（很多的意思是介于有点多和非常多之间）。
  • 使用小而简单的合适数据类型，除非真实数据模型中有确切的需要，否则应该尽可能地避免使用NULL 值。
  • 尽量使用相同的数据类型存储相似或相关的值，尤其是要在关联条件中使用的列。
  • 注意可变⻓字符串，其在临时表和排序时可能导致悲观的按最大⻓度分配内存。
  • 尽量使用整型定义标识列。
  • 避免使用MySQL已经遗弃的特性，例如指定浮点数的精度，或者整数的显示宽度。
  • 小心使用ENUM 和SET 。虽然它们用起来很方便，但是不要滥用，否则有时候会变成陷阱。最好避免使用BIT
  • 范式是好的，但是反范式（大多数情况下意味着重复数据）有时也是必需的，并且 能带来好处。预先计算、缓存或生成汇总表也可能获得很 大的好处。Justin Swanhart的Flexviews工具可以帮助维护汇总表。

第五章

B-Tree索引

我们使用术语“B-Tree”，是因为MySQL在CREATE TABLE 和其他语句中也使用该关键字。不过，底层的存储引擎也可能使用不同的存储结构，例如，NDB集 群 存 储 引 擎 内 部 实 际 上 使 用 了 T-Tree 结 构 存 储 这 种 索 引 ， 即 使 其 名 字 是BTREE；InnoDB则使用的是B+Tree，各种数据结构和算法的变种不在本书的讨论范围之内。

存储引擎以不同的方式使用B-Tree索引，性能也各有不同，各有优劣。例如，MyISAM使用前缀压缩技术使得索引更小，但InnoDB则按照原数据格式进行存储。再如MyISAM索引通过数据的物理位置引用被索引的行，而InnoDB则根据主键引用被索引的行。

image-20200321110819890

  • B-Tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描 来获取需要的数据，取而代之的是从索引的根节点（图示并未画出）开始进行搜 索。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点⻚的值和要查找的值可以找到合适的指针进入下层子节点，这 些指针实际上定义了子节点⻚中值的上限和下限。最终存储引擎要么是找到对应 的值，要么该记录不存在。

  • 叶子节点比较特别，它们的指针指向的是被索引的数据，而不是其他的节点⻚（不同引擎的“指针”类型不同）。图5-1中仅绘制了一个节点和其对应的叶子节点，其实在根节点和叶子节点之间可能有很多层节点⻚。树的深度和表的大小直接相关。

  • B-Tree对索引列是顺序组织存储的，所以很适合查找范围数据。例如，在一个基于文本域的索引树上，按字母顺序传递连续的值进行查找是非常合适的，所以像“找出所有以I到K开头的名字”这样的查找效率会非常高。

    ![image-20200321112033714](/Users/oysterqaq/Library/Application Support/typora-user-images/image-20200321112033714.png)

image-20200321112001010

  • B-Tree索引适用于全键值、键值范围或键前缀查找。其中键前缀查找只适用于根据最左前缀的查找

高性能的索引策略

  • 始终将索引列单独放在比较符号的一侧

  • 前缀索引减少索引体积,足够长的前缀保证一定的选择性

  • 在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能。MySQL 5.0和更新版本引入了一种叫“索引合并”（index merge）的策略,一定程度上可以使用表上的多个单列索引来定位指定的行。

  • 将选择性最高的列放到索引最前列。这个建议有用吗?在某些场景可能有帮助，但通常不如避免随机IO和排序那么重要，考虑问题需要更全面

  • 聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。当表有聚簇索引时，它的数据行实际上存放在索引的叶子⻚

    image-20200321194544967

    InnoDB将通过主键聚集数据，这也就是说图5-3中的“被索引的列”就是主键列

    如果没有定义主键， InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引

  • 聚簇索引的一些缺点

    • 聚簇数据最大限度地提高了I/O密集型应用的性能，但如果数据全部都放在内存中，则访问的顺序就没那么重要了，聚簇索引也就没什么优势了。
    • 插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到InnoDB表中速 度最快的方式 。但如 果不是按照主键顺序加载数据， 那么在加载完成后最好使用OPTIMIZE TABLE命令重新组织一下表。
    • 更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新 的位置。
    • 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候， 可能面临“⻚分裂（page split）”的问题。当行的主键值要求必须将这一行插入到某个已满的⻚中时 ，存储引擎会将该⻚分裂 成 两 个⻚面来容纳该行， 这就是一次⻚分裂操作。⻚分裂会导致表占用更多的磁盘空间。
    • 二级索引（ 非聚簇索引）可能比想象的要更大，因为在二级索引的叶子节点包含了引用行的主键列。
    • 二级索引访问需要两次索引查找，而不是一次。

  • 通过二级索引查找行，存储引擎需要找到二级索引的叶子节点获得对应的主键值，然后根据这个值去聚簇索引中查找到对应的行。

InnoDB和MyISAM的数据分布对比

image-20200321195341625

image-20200321195452992

image-20200321195411026

image-20200321195511020

避免非主键顺序插入

最好避免随机的（不连续且值的分布范围非常大）聚簇索引，特别是对于I/O密集型的应用。例如，从性能的⻆度考虑，使用UUID来作为聚簇索引则会很糟糕：它使得聚簇索引的插入变得完全随机，这是最坏的情况，使得数据没有任何聚集特性。

非主键顺序插入的缺点

  • 写入的目标⻚可能已经刷到磁盘上并从缓存中移除，或者是还没有被加载到缓存中，InnoDB在插入之前不得不先找到并从磁盘读取目标⻚到内存中。这将导致大量的随机I/O。
  • 因为写入是乱序的，InnoDB不得不频繁地做⻚分裂操作，以便为新的行分配空间。⻚分裂会导致移动大量数据，一次插入最少需要修改三个⻚而不是一个⻚。
  • 由于频繁的⻚分裂，⻚会变得稀疏并被不规则地填充，所以最终数据会有碎片。

覆盖索引

  • 如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引
  • 优点:
    • 索引条目通常远小于数据行大小，所以如果只需要读取索引，那MySQL就会极大地减少数据访问量。这对缓存的负载非常重要，因为这种情况下响应时间大部分花费在数据拷⻉上。
    • 覆盖索引对于I/O密集型的应用也有帮助，因为索引比数据更小，更容易全部放入内存中（这对于MyISAM尤其正确，因为MyISAM能压缩索引以变得更小）。
    • 因为索引是按照列值顺序存储的（至少在单个⻚内是如此），所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少得多。对于某些存储引擎，例如MyISAM和PerconaXtraDB，甚至可以通过OPTIMIZE命令使得索引完全顺序排列，这让简单的范围查询能使用完全顺序的索引访问。
    • 一些存储引擎如MyISAM在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用。这可能会导致严重的性能问题，尤其是那些系统调用占了数据访问中的最大开销的场景。
    • 由于InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询。
  • 不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以MySQL只能使用B-Tree索引做覆盖索引。
  • 当发起一个被索引覆盖的查询（也叫做索引覆盖查询）时，在EXPLAIN的Extra列可以看到“Usingindex”的信息

使用索引扫描来做排序

  • 扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列，那就不得不每扫描一条索引记录就都回表查询一次对应的行。这基本上都是随机I/O，因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢，尤其是在I/O密集型的工作负载时。
  • 只有当索引的列顺序和ORDERBY子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，MySQL才能够使用索引来对结果做排序(14)。如果查询需要关联多张表，则只有当ORDERBY子句引用的字段全部为第一个表时，才能使用索引做排序。
  • 有一种情况下ORDERBY子句可以不满足索引的最左前缀的要求，就是前导列为常量的时候。如果WHERE子句或者JOIN子句中对这些列指定了常量，就可以“弥补”索引的不足。

未使用的索引

另外，还可以使用PerconaToolkit中的pt-index-usage，该工具可以读取查询日志，并对日志中的每条查询进行EXPLAIN操作，然后打印出关于索引和查询的报告。这个工具不仅可以找出哪些索引是未使用的，还可以了解查询的执行计划⸺例如在某些情况有些类似的查询的执行方式不一样，这可以帮助你定位到那些偶尔服务质量差的查询，优化它们以得到一致的性能表现。该工具也可以将结果写入到MySQL的表中，方便查询结果。

索引和锁

索引可以让查询锁定更少的行。如果你的查询从不访问那些不需要的行，那么就会锁定更少的行，从两个方面来看这对性能都有好处。首先，虽然InnoDB的行锁效率很高，内存使用也很少，但是锁定行的时候仍然会带来额外开销；其次，锁定超过需要的行会增加锁争用并减少并发性。

InnoDB只有在访问行的时候才会对其加锁，而索引能够减少InnoDB访问的行数，从而减少锁的数量。但这只有当InnoDB在存储引擎层能够过滤掉所有不需要的行时才有效。如果索引无法过滤掉无效的行，那么在InnoDB检索到数据并返回给服务器层以后，MySQL服务器才能应用WHERE子句(19)。这时已经无法避免锁定行了：InnoDB已经锁住了这些行，到适当的时候才释放。在MySQL5.1和更新的版本中，InnoDB可以在服务器端过滤掉行后就释放锁，但是在早期的MySQL版本中，InnoDB只有在事务提交后才能释放锁。

关于InnoDB、索引和锁有一些很少有人知道的细节：InnoDB在二级索引上使用共享（读）锁，但访问主键索引需要排他（写）锁。这消除了使用覆盖索引的可能性，并且使得SELECT FOR UPDATE比LOCK IN SHARE MODE或非锁定查询要慢很多。

支持多种过滤条件

  • 让多个查询都会用到的列放在索引的最前列

    例如性别

    如果某个查询不限制性别，那么可以通过在查询条件中新增ANDSEXIN（m,f）来让MySQL选择该索引。这样写并不会过滤任何行，和没有这个条件时返回的结果相同。但是必须加上这个列的条件，MySQL才能够匹配索引的最左前缀。这个“诀窍”在这类场景中非常有效，但如果列有太多不同的值，就会让IN()列表太⻓，这样做就不行了。

  • 尽可能的把范围查询的列放在索引的最后面

避免多个范围查询

  • 对于范围条件查询，MySQL无法再使用范围列后面的其他索引列了，但是对于“多个等值条件查询”则没有这个限制。
  • 可以讲范围条件转化为等值,例如今天内有登陆的用户是活跃用户,则可以定时维护这个列将时间范围转化成是否活跃用户的0或1

第六章

查询执行的过程

image-20200321204856087

优化数据访问

查询性能低下最基本的原因是访问的数据太多。

  • 确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行，但有时候也可能是访问了太多的列。

  • 确认MySQL服务器层是否在分析大量超过需要的数据行。

对于MySQL来说最简单的衡量开销的三个指标如下

  • 响应时间

    响应时间是两个部分之和：服务时间和排队时间。服务时间是指数据库处理这个查询真正花了多⻓时间。排队时间是指服务器因为等待某些资源而没有真正执行查询的时间⸺可能是等I/O操作完成，也可能是等待行锁，等等。

  • 扫描的行数

  • 返回的行数

MySQL访问类型从慢到快

  • 全表扫描(扫表)
  • 索引扫描(索引)
  • 范围扫描(索引)
  • 唯一索引查询(索引)
  • 常数引用(单值)

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

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

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

  • 从数据表中返回数据，然后过滤不满足条件的记录（在Extra 列中出现Using Where）。这在MySQL服务器层完成，MySQL需要先从数据表读出记录然后过滤。

重构查询方式

分解关联查询的好处

  • 让缓存的效率更高。
  • 将查询分解后，执行单个查询可以减少锁的竞争。
  • 在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。
  • 查询本身效率也可能会有所提升。这个例子中，使用IN() 代替关联查询，可以让MySQL按照ID顺序进行查询，这可能比随机的关联要更高效
  • 可以减少冗余记录的查询。在应用层做关联查询，意味着对于某条记录应用只需要查询一次，而在数据库中做关联查询，则可能需要重复地访问一部分数据。
  • 更进一步，这样做相当于在应用中实现了哈希关联，而不是使用MySQL的嵌套循环关联。某些场景哈希关联的效率要高很多

查询执行的基础

MySQL通讯协议为半双工,即任意时刻每个连接只能由一方发送数据

查询状态

  • Sleep

    线程正在等待客戶端发送新的请求。

  • Query

    线程正在执行查询或者正在将结果发送给客戶端。

  • Locked

    在MySQL服务器层，该线程正在等待表锁。在存储引擎级别实现的锁，例如InnoDB的行锁，并不会体现在线程状态中。对于MyISAM来说这是一个比较典型的状态，但在其他没有行锁的引擎中也经常会出现。

  • Analyzing and statistics

    线程正在收集存储引擎的统计信息，并生成查询的执行计划。

  • Copying to tmp table [on disk]

    线程正在执行查询，并且将其结果集都复制到一个临时表中，这种状态一般要么是在做GROUP BY 操作，要么是文件排序操作，或者是UNION操作。如果这个状态后面还有“on disk”标记，那表示MySQL正在将一个内存临时表放到磁盘上。

  • The thread is

    线程正在对结果集进行排序。

  • Sending data

    这表示多种情况：线程可能在多个状态之间传送数据，或者在生成结果集，或者在向客戶端返回数据。

查询缓存

查询缓存是通过一个对查询语句大小写敏感的哈希查找实现的。如果当前的查询恰好命中了查询缓存，那么在返回查询结果之前MySQL会检查一次用戶权限。

查询优化

MySQL使用基于成本的优化器，它将尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。最初，成本的最小单位是随机读取一个4K数据⻚的成本，后来（成本计算公式）变得更加复杂，并且引入了一些“因子”来估算某些操作的代价，如当执行一次WHERE条件比较的成本。可以通过查询当前会话的Last_query_cost的值来得知MySQL计算的当前查询的成本。

错误执行计划的原因

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

一些自动优化规则

  • 重新定义关联表的顺序,即选择驱动表

  • 将外连接转化成内连接

  • 使用等价变换规则

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

    通过B-Tree的排序特性实现,最左端或者最右端

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

  • 覆盖扫描

  • 子查询优化

  • 提前终止查询

  • 等值传播

  • 列表IN

    先对列表排序,后执行

MySQL查询的执行实质

MySQL认为任何一个查询都是一次“关联”⸺并不仅仅是一个查询需要到两个表匹配才叫关联，所以在MySQL中，每一个查询，每一个片段（包括子查询，甚至基于单表的SELECT）都可能是关联

有点类似递归,当前MySQL关联执行的策略很简单：MySQL对任何关联都执行嵌套循环关联操作，即MySQL先在一个表中循环取出单条数据，然后再嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为止。然后根据各个表匹配的行，返回查询中需要的各个列。MySQL会尝试在最后一个关联表中找到所有匹配的行，如果最后一个联表无法找到更多的行以后，MySQL返回到上一层次关联表，看是否能够找到更多的匹配记录，依此类推迭代执行。

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

执行计划

MySQL生成查询的一棵指令树，然后通过存储引擎执行完成这棵指令树并返回结果。最终的执行计划包含了重构查询的全部信息。如果对某个查询执行EXPLAIN EXTENDED后，再执行SHOW WARNINGS，就可以看到重构出的查询

image-20200322101109506

关联查询优化器

  • 可以使用TRAIGHT_JOIN指定驱动表
  • MySQL通常会计算出所有可倒转的关联可能,选择只扫描很少的行数一个关联表作为驱动表
  • 倒转的关联顺序会让查询进行更少的嵌套循环和回溯操作。

排序优化

当不能使用索引生成排序结果的时候，MySQL需要自己进行排序，如果数据量小则在内存中进行，如果数据量大则需要使用磁盘，不过MySQL将这个过程统一称为文件排序（filesort），即使完全是内存排序不需要任何磁盘文件时也是如此。

如果需要排序的数据量小于“排序缓冲区”，MySQL使用内存进行“快速排序”操作。如果内存不够排序，那么MySQL会先将数据分块，对每个独立的块使用“快速排序”进行排序，并将各个块的排序结果存放在磁盘上，然后将各个排好序的块进行合并（merge），最后返回排序结果。

现代MySQL排序算法:单次传输排序(先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果。

在关联查询的时候如果需要排序，MySQL会分两种情况来处理这样的文件排序。如果ORDERBY子句中的所有列都来自关联的第一个表，那么MySQL在关联处理第一个表的时候就进行文件排序。如果是这样，那么在MySQL的EXPLAIN结果中可以看到Extra字段会有“Usingfilesort”。除此之外的所有情况，MySQL都会先将关联的结果存放到一个临时表中，然后在所有的关联都结束后，再进行文件排序。这种情况下，在MySQL的EXPLAIN结果的Extra字段可以看到“Usingtemporary;Usingfilesort”。如果查询中有LIMIT的话，LIMIT也会在排序之后应用，所以即使需要返回较少的数据，临时表和需要排序的数据量仍然会非常大。

MySQL是如何返回结果的

MySQL将结果集返回客戶端是一个增量、逐步返回的过程。例如，我们回头看看前面的关联操作，一旦服务器处理完最后一个关联表，开始生成第一条结果时，MySQL就可以开始向客戶端逐步返回结果集了。

MySQL优化器的局限

关联子查询(5.6或MariaDB修复)

  • MySQL会将相关的外层表压到子查询中，它认为这样可以更高效率地查找到数据行。
  • 为使用IN() 加子查询，性能经常会非常糟，所以通常建议使用EXISTS() 等效的改写查询来获取更好的效率。

优化特定类型的查询

COUNT

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

关联查询

  • 确保ON或者USING子句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。当表A和表B用列c关联的时候，如果优化器的关联顺序是B、A，那么就不需要在B表的对应列上建上索引。没有用到的索引只会带来额外的负担。一般来说，除非有其他理由，否则只需要在关联顺序中的第二个表的相应列上创建索引。
  • 确保任何的GROUPBY和ORDERBY中的表达式只涉及到一个表中的列，这样MySQL才有可能使用索引来优化这个过程。
  • 当升级MySQL的时候需要注意：关联语法、运算符优先级等其他可能会发生变化的地方。因为以前是普通关联的地方可能会变成笛卡儿积，不同类型的关联可能会生成不同的结果等。

第七章

查询缓存

  • 查询缓存系统会跟踪查询中涉及的每个表，如果这些表发生变化，那么和这个表相关的所有的缓存数据都将失效。

  • 当判断缓存是否命中时，MySQL不会解析、“正规化”或者参数化查询语句，而是直接使用SQL语句和客戶端发送过来的其他原始信息。任何字符上的不同，例如空格、注释⸺任何的不同⸺都会导致缓存的不命中。所以在编写SQL语句的时候，需要特别注意这点。通常使用统一的编码规则是一个好的习惯，在这里这个好习惯会让你的系统运行得更快。

  • 我们常听到：“如果查询中包含一个不确定的函数，MySQL则不会检查查询缓存”。这个说法是不正确的。因为在检查查询缓存的时候，还没有解析SQL语句，所以MySQL并不知道查询语句中是否包含这类函数。在检查查询缓存之前，MySQL只做一件事情，就是通过一个大小写不敏感的检查看看SQL语句是不是以SEL开头。

  • 准确的说法应该是：“如果查询语句中包含任何的不确定函数，那么在查询缓存中是不可能找到缓存结果的”。因为即使之前刚刚执行了这样的查询，结果也不会放在查询缓存中。MySQL在任何时候只要发现不能被缓存的部分，就会禁止这个查询被缓存。

  • 打开查询缓存对读和写操作都会带来额外的消耗

    • 读查询在开始之前必须先检查是否命中缓存。

    • 如果这个读查询可以被缓存，那么当完成执行后，MySQL若发现查询缓存中没有这个查询，会将其结果存入查询缓存，这会带来额外的系统消耗。

    • 这对写操作也会有影响，因为当向某个表写入数据的时候，MySQL必须将对应表的所有缓存都设置

    失效。如果查询缓存非常大或者碎片很多，这个操作就可能会带来很大系统消耗（设置了很多的内存

    给查询缓存用的时候）。

  • 对InnoDB用戶来说，事务的一些特性会限制查询缓存的使用。当一个语句在事务中修改了某个表，MySQL会将这个表的对应的查询缓存都设置失效，而事实上，InnoDB的多版本特性会暂时将这个修改对其他事务屏蔽。在这个事务提交之前，这个表的相关查询是无法被缓存的，所以所有在这个表上面的查询⸺内部或外部的事务⸺都只能在该事务提交后才被缓存。因此，⻓时间运行的事务，会大大降低查询缓存的命中率。

  • 如果查询缓存使用了很大量的内存，缓存失效操作就可能成为一个非常严重的问题瓶颈。如果缓存中存放了大量的查询结果，那么缓存失效操作时整个系统都可能会僵死一会儿。因为这个操作是靠一个全局锁操作保护的，所有需要做该操作的查询都要等待这个锁，而且无论是检测是否命中缓存、还是缓存失效检测都需要等待这个全局锁。

什么情况下缓存能发挥作用

  • 理论上，可以通过观察打开或者关闭查询缓存时候的系统效率来决定是否需要开启查询缓存。
  • 对于那些需要消耗大量资源的查询通常都是非常适合缓存的。

缓存未命中的几个原因

任何SELECT 语句没有从查询缓存中返回都称为“缓存未命中”。缓存未命中可能有如下几种原因：

  • 查询语句无法被缓存，可能是因为查询中包含一个不确定的函数（如CURRENT_DATA ），或者查询结果太大而无法缓存。这都会导致状态值Qcache_not_cached 增加。
  • MySQL从未处理这个查询，所以结果也从不曾被缓存过。
  • 还有一种情况是虽然之前缓存了查询结果，但是由于查询缓存的内存用完了，MySQL需要将某些缓 存“逐出”，或者由于数据表被修改导致缓存失效。（后续会详细介绍缓存失效。）

如果你的服务器上有大量缓存未命中，但是实际上绝大数查询都被缓存了，那么一定是有如下情况发生：

  • 查询缓存还没有完成预热。也就是说，MySQL还没有机会将查询结果都缓存起来。

  • 查询语句之前从未执行过。如果你的应用程序不会重复执行一条查询语句，那么即使完成预热仍然会有很多缓存未命中。

  • 缓存失效操作太多了。

总结

本章详细介绍了前面各个章节中提到的一些MySQL特性。这里我们将再来回顾一下其中的一些重点内容。

  • 分区表

    分区表是一种粗粒度的、简易的索引策略，适用于大数据量的过滤场景。最适合的场景是，在没有合适的索引时，对其中几个分区进行全表扫描，或者是只有一个分区和索引是热点，而且这个分区和索引能够都在内存中；限制单表分区数不要超过150个，并且注意某些导致无法做分区过滤的细节，分区表对于单条记录的查询并没有什么优势，需要注意这类查询的性能。

  • 视图

    对好几个表的复杂查询，使用视图有时候会大大简化问题。当视图使用临时表时，无法将WHERE条件下推到各个具体的表，也不能使用任何索引，需要特别注意这类查询的性能。如果为了便利，使用视图是很合适的。

  • 外键

    外键限制会将约束放到MySQL中，这对于必须维护外键的场景，性能会更高。不过这也会带来额外的复杂性和额外的索引消耗，还会增加多表之间的交互，会导致系统中更多的锁和竞争。外键可以被看作是一个确保系统完整性的额外的特性，但是如果设计的是一个高性能的系统，那么外键就显得很臃肿了。很多人在更在意系统的性能的时候都不会使用外键，而是通过应用程序来维护。

  • 存储过程

    MySQL本身实现了存储过程、触发器、存储函数和事件，老实说，这些特性并没什么特别的。而且对于基于语句的复制还有很多问题。通常，使用这些特性可以帮你节省很多的网络开销⸺很多情况下，减少网络开销可以大大提升系统的性能。在某些经典的场景下你可以使用这些特性（例如中心化业务逻辑、绕过权限系统，等等），但需要注意在MySQL中，这些特性并没有别的数据库系统那么成熟和全面。

  • 绑定变量
    当查询语句的解析和执行计划生成消耗了主要的时间，那么绑定变量可以在一定程度上解决问题。因为只需要解析一次，对于大量重复类型的查询语句，性能会有很大的提高。另外，执行计划的缓存和传输使用的二进制协议，这都使得绑定变量的方式比普通SQL语句执行的方式要更快。

  • 插件

    使用C或者C++编写的插件可以让你最大程度地扩展MySQL功能。插件功能非常强大，我们已经编写了很多UDF和插件，在MySQL中解决了很多问题。

  • 字符集

    字符集是一种字节到字符之间的映射，而校对规则是指一个字符集的排序方法。很多人都使用Latin1（默认字符集，对英语和某些欧洲语言有效）或者UTF-8。如果使用的是UTF-8，那么在使用临时表和缓冲区的时候需要注意：MySQL会按照每个字符三个字节的最大占用空间来分配存储空间，这可能消耗更多的内存或者磁盘空间。注意让字符集和MySQL字符集配置相符，否则可能会由于字符集转换让某些索引无法正常使用。

  • 全文索引

    在本书编写的时候只有MyISAM支持全文索引，不过据说从MySQL5.6开始，InnoDB也将支持全文索引。MyISAM因为在锁粒度和崩溃恢复上的缺点，使得在大型全文索引场景中基本无法使用。这时，我们通常帮助客戶构建和使用Sphinx来解决全文索引的问题。

  • XA事务

    很少有人用MySQL的XA事务特性。除非你真正明白参数innodb_support_xa的意义，否则不要修改这个参数的值，并不是只有显式使用XA事务时才需要设置这个参数。InnoDB和二进制日志也是需要使用XA事务来做协调的，从而确保在系统崩溃的时候，数据能够一致地恢复。

  • 查询缓存

    完全相同的查询在重复执行的时候，查询缓存可以立即返回结果，而无须在数据库中重新执行一次。根据我们的经验，在高并发压力环境中查询缓存会导致系统性能的下降，甚至僵死。如果你一定要使用查询缓存，那么不要设置太大内存，而且只有在明确收益的时候才使用。那该如何判断是否应该使用查询缓存呢？建议使用PerconaServer，观察更细致的日志，并做一些简单的计算。还可以查看缓存命中率（并不总是有用）、“INSERTS和SELECT比率”（这个参数也并不直观）、或者“命中和写入比率”（这个参考意义较大）。查询缓存是一个非常方便的缓存，对应用程序完全透明，无须任何额外的编码，但是，如果希望有更高的缓存效率，我们建议使用memcached或者其他类似的解决方案。第14章介绍了更多的细节供大家参考。

面向ACG编程