跳转至

高性能MySQL（第4版）-Silvia Botros Jeremy Tinley

  • 高性能MySQL（第4版）|200
  • 书名： 高性能MySQL（第4版）
  • 作者： Silvia Botros Jeremy Tinley
  • 简介： 《高性能 MySQL》一直是 MySQL 领域的经典之作，影响了一代又一代的 DBA 和技术人员，从第3 版出版到第 4 版出版过去了近十年，MySQL 也从 5.5 版本更新到了 8.0 版本。第 4 版中增加了大量对 MySQL 5.7 和 8.0 版本新特性的介绍，删除了一些在新版本中已经废弃或者不再常用的功能，还增加了对云数据库的介绍，减少了在官方文档中已有的基础使用和配置相关的内容。这些年，MySQL 经过在大量大规模互联网场景中的应用验证，使得本书在继续关注高性能之外，还用了较多的篇幅来介绍如何实现 MySQL 的大规模可扩展应用和合规性问题，这是相比第 3 版最大的不同，也是本书封面上所写的“经过大规模运维验证的策略”的体现。本书适合数据库管理员（DBA）阅读，也适合系统运维和开发人员参考学习。不管你是数据库新手还是专家，相信都能从本书中有所收获。
  • 出版时间 2022-09-01 00:00:00
  • ISBN： 9787121442575
  • 分类： 计算机-数据库
  • 出版社： 电子工业出版社

高亮划线

封面

版权信息

内容简介

O'Reilly Media, Inc.介绍

本书赢得的赞誉

推荐序

译者序

序

前言

本书第4版的致谢

第1章 MySQL架构

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

第3章 Performance Schema

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

第5章 优化服务器设置

第6章 schema设计与管理

  • 📌 简单数据类型的操作通常需要更少的CPU周期。例如，整型数据比字符型数据的比较操作代价更低，因为字符集和排序规则(collation)使字符型数据的比较更复杂。这里有两个例子：一个是应该将日期和时间存储为MySQL的内置类型而不是字符串类型，另外一个是应该用整型数据存储IP地址。稍后我们将专门讨论这个话题。 ^16-1786-1939

    • ⏱ 2024-03-09 15:52:40

  • 📌 尽量避免存储NULL ^16-1968-1978

    • ⏱ 2024-03-09 15:52:44

  • 📌 例如，DATETIME和TIMESAMP列可以存储相同类型的数据：时间和日期，精确到秒。然而TIMESTAMP只使用DATETIME一半的存储空间，还会根据时区变化，而且具有特殊的自动更新能力。另一方面，TIMESTAMP允许的时间范围要小得多，有时候它的特殊能力会成为障碍。 ^16-2518-2656

    • ⏱ 2024-03-09 15:54:06

  • 📌 本章只讨论基本的数据类型。MySQL为了兼容性支持很多别名，例如，INTEGER（映射到INT）、BOOL（映射到TINYINT）和NUMERIC（映射到DECIMAL）。它们都只是别名。这些别名可能令人不解，但不会影响性能。如果建表时采用数据类型的别名，然后用SHOW CREATE TABLE检查，会发现MySQL报告的是基本类型，而不是别名。 ^16-2685-2859

    • ⏱ 2024-03-09 15:54:40

  • 📌 MySQL可以为整数类型指定宽度，例如，INT(11)，这对大多数应用毫无意义：它不会限制值的合法范围，只是规定了MySQL的一些交互工具（例如，MySQL命令行客户端）用来显示字符的个数。对于存储和计算来说，INT(1)和INT(20)是相同的。 ^16-3741-3865

    • ⏱ 2024-03-09 16:39:50

  • 📌 由于额外的空间需求和计算成本，应该尽量只在对小数进行精确计算时才使用DECIMAL——例如，存储财务数据。但在一些大容量的场景，可以考虑使用BIGINT代替DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。假设要存储财务数据并精确到万分之一分，则可以把所有金额乘以一百万，然后将结果存储在BIGINT里，这样可以同时避免浮点存储计算不精确和DECIMAL精确计算代价高的问题。 ^16-4457-4655

    • ⏱ 2024-03-09 16:43:00

  • 📌 VARCHAR需要额外使用1或2字节记录字符串的长度：如果列的最大长度小于或等于255字节，则只使用1字节表示，否则使用2字节。假设采用latin1字符集，一个VARCHAR(10)的列需要11字节的存储空间。VARCHAR(1000)的列则需要1002个字节，因为需要2字节存储长度信息。 ^16-5337-5482

    • ⏱ 2024-03-09 17:02:43

  • 📌 CHAR适合存储非常短的字符串，或者适用于所有值的长度都几乎相同的情况。例如，对于用户密码的MD5值，CHAR是一个很好的选择，它们的长度总是相同的。对于经常修改的数据，CHAR也比VARCHAR更好，因为固定长度的行不容易出现碎片。对于非常短的列，CHAR也比VARCHAR更高效；设计为只保存Y和N的值的CHAR(1)在单字节字符集[插图]中只使用1字节，但VARCHAR(1)需要2字节，因为还有一个记录长度的额外字节。 ^16-6006-6325

    • ⏱ 2024-03-09 17:06:33

  • 📌 慷慨是不明智的使用VARCHAR(5)和VARCHAR(200)存储'hello'的空间开销是一样的。那么使用更短的列有什么优势吗？事实证明有很大的优势。较大的列会使用更多的内存，因为MySQL通常会在内部分配固定大小的内存块来保存值。这对于使用内存临时表的排序或操作来说尤其糟糕。在利用磁盘临时表进行文件排序时也同样糟糕。最好的策略是只分配真正需要的空间。 ^16-7637-7909

    • ⏱ 2024-03-09 17:09:42

  • 📌 实际上，它们分别属于两组不同的数据类型家族：字符类型是TINYTEXT、SMALLTEXT、TEXT、MEDIUMTEXT和LONGTEXT；二进制类型是TINYBLOB、SMALLBLOB、BLOB、MEDIUMBLOB、LONGBLOB。BLOB是SMALLBLOB的同义词，TEXT是SMALLTEXT的同义词。 ^16-8055-8214

    • ⏱ 2024-03-09 17:10:52

  • 📌 MySQL不能将BLOB和TEXT数据类型的完整字符串放入索引，也不能使用索引进行排序。 ^16-8643-8687

    • ⏱ 2024-03-09 17:14:45

  • 📌 DATETIME这种类型可以保存大范围的数值，从1000年到9999年，精度为1微秒。它以YYYYMMDDHHMMSS格式存储压缩成整数的日期和时间，且与时区无关。这需要8字节的存储空间。 ^16-12930-13053

    • ⏱ 2024-03-09 20:19:39

  • 📌 TIMESTAMP只使用4字节的存储空间，所以它的范围比DATETIME小得多：只能表示从1970年到2038年1月19日。MySQL提供FROM_UNIXTIME()函数来将UNIX时间戳转换为日期，并提供了UNIX_TIMESTAMP()函数将日期转换为UNIX时间戳。 ^16-13288-13425

    • ⏱ 2024-03-09 20:20:56

第7章 创建高性能的索引

  • 📌 请注意，索引对多个值进行排序的依据是CREATE TABLE语句中定义索引时列的顺序 ^17-4592-4634

    • ⏱ 2024-03-08 19:20:10

  • 📌 自适应哈希索引。InnoDB存储引擎有一个被称为自适应哈希索引的特性。当InnoDB发现某些索引值被非常频繁地被访问时，它会在原有的B-tree索引之上，在内存中再构建一个哈希索引。这就让B-tree索引也具备了一些哈希索引的优势，例如，可以实现非常快速的哈希查找。这个过程是完全自动化的，用户无法进行控制或者配置。不过，可以通过参数彻底关闭自适应哈希索引这个特性。 ^17-4702-4885

    • ⏱ 2024-03-08 19:21:41

  • 📌 B-tree索引通常可以支持“只访问索引的查询”，即查询只需要访问索引，而无须访问数据行。后面我们将单独讨论这种“覆盖索引”的优化。 ^17-5844-5910

    • ⏱ 2024-03-08 22:41:46

  • 📌 如果范围查询列值的数量有限，那么可以通过使用多个等于条件来代替范围条件。 ^17-6561-6597

    • ⏱ 2024-03-08 22:46:25

  • 📌 在优化性能的时候，可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求。 ^17-6664-6704

    • ⏱ 2024-03-08 22:46:48

  • 📌 最常见的B-tree索引，按照顺序存储数据，所以MySQL可以用来做ORDER BY和GROUP BY操作。因为数据是有序的，所以B-tree也就会将相关的列值都存储在一起。最后，因为索引中存储了实际的列值，所以某些查询只使用索引就能够完成全部查询。据此特性，总结下来索引有如下三个优点：●索引大大减少了服务器需要扫描的数据量。●索引可以帮助服务器避免排序和临时表。●索引可以将随机I/O变为顺序I/O。 ^17-7313-7602

    • ⏱ 2024-03-08 22:59:03

  • 📌 索引”这个主题完全值得单独写一本书，如果想深入理解这部分内容，强烈建议阅读由Tapio Lahdenmaki和Mike Leach编写的Relational Database Index Design and the Optimizers（Wiley出版社出版）一书，该书详细介绍了如何计算索引的成本和作用、如何评估查询速度、如何分析维护索引的代价和其带来的好处等。 ^17-7632-7843

    • ⏱ 2024-03-08 22:59:46

  • 📌 最容易让人感到困惑的问题之一就是索引列的顺序。正确的顺序依赖于使用该索引的查询语句，同时，还需要考虑如何更好地满足排序和分组操作的需要。 ^17-14369-14437

    • ⏱ 2024-03-09 00:06:19

  • 📌 当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。这时索引的作用只是优化查询语句中的WHERE条件。 ^17-14912-14969

    • ⏱ 2024-03-09 00:08:44

  • 📌 聚簇索引[插图]并不是一种单独的索引类型，而是一种数据存储方式 ^17-18727-18876

    • ⏱ 2024-03-13 19:24:32

  • 📌 当表有聚簇索引时，它的数据行实际上存放在索引的叶子页(leaf page)中。术语“聚簇”表示数据行和相邻的键值紧凑地存储在一起。[插图]因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引（不过，覆盖索引可以模拟多个聚簇索引的情况，本章后面将详细介绍）。 ^17-18958-19171

    • ⏱ 2024-03-13 19:24:59

  • 📌 图7-3展示了聚簇索引中的记录是如何存放的。注意，叶子页包含了一条记录的全部数据，但是节点页只包含了索引列。在这个案例中，索引列包含的是整数值。 ^17-19308-19380

    • ⏱ 2024-03-13 19:25:51

  • 📌 有些数据库服务器允许你选择用于聚簇的索引，但是MySQL内置的存储引擎都不支持这个特性。InnoDB根据主键聚簇数据。这意味着图7-3中所示的“索引列”就是主键列。 ^17-19670-19752

    • ⏱ 2024-03-09 09:52:48

  • 📌 如果你没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。这样做的缺点在于，所有需要使用这种隐藏主键的表都依赖一个单点的“自增值”，这可能会导致非常高的锁竞争，从而出现性能问题。 ^17-19781-19905

    • ⏱ 2024-03-09 09:53:18

  • 📌 聚簇索引将索引和数据保存在同一个B-tree中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快。 ^17-20119-20170

    • ⏱ 2024-03-13 19:27:36

  • 📌 插入速度严重依赖于插入顺序。按照主键的顺序插入行是将数据加载到InnoDB表中最快的方式。但如果不是按照主键的顺序加载数据，那么在加载完成后最好使用OPTIMIZE TABLE命令重新组织一下表。 ^17-20427-20525

    • ⏱ 2024-03-13 19:28:57

  • 📌 二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值。 ^17-21036-21069

    • ⏱ 2024-03-13 19:35:07

  • 📌 这意味着通过二级索引查找行，存储引擎需要找到二级索引的叶子节点，以获得对应的主键值，然后根据这个值去聚簇索引中查找对应的行。这里做了双倍工作：两次B-tree查找而不是一次。[插图]对于InnoDB，自适应哈希索引（参考本章前面的“B-tree索引”一节）能够减少这样的重复工作。 ^17-21098-21409

    • ⏱ 2024-03-09 09:43:25

  • 📌 InnoDB的二级索引的叶子节点中存储的是主键值，并以此作为指向行的“指针”。这样的策略减少了当出现行移动或者数据页分裂时二级索引的维护工作。使用主键值作为指针会让二级索引占用更多的空间，换来的好处是，InnoDB在移动行时无须更新二级索引中的这个“指针”。 ^17-22545-22674

    • ⏱ 2024-03-09 09:47:24

  • 📌 如果你正在使用InnoDB表并且没有什么数据需要聚集，那么可以定义一个代理键(surrogate key)作为主键，这种主键的值和应用无关，最简单的方法是使用AUTO_INCREMENT自增列。这样可以保证数据行是按顺序写入的，对于根据主键做联接操作的性能也会更好。 ^17-23253-23386

    • ⏱ 2024-03-09 09:49:42

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

    • ⏱ 2024-03-09 09:50:38

  • 📌 因为新写入的记录的主键值不一定比之前插入的大，所以InnoDB无法简单地总是把新记录插到索引的最后，而是需要为新记录寻找合适的位置——通常是已有数据的中间位置——并且分配空间。这会增加很多额外工作，并导致数据分布不够优化。下面是总结的一些缺点： ^17-25876-25998

    • ⏱ 2024-03-09 10:16:06

  • 📌 从这个案例可以看出，使用InnoDB时应该尽可能地按主键顺序插入数据，并且尽可能地按照单调增加的聚簇键的值顺序插入新记录。 ^17-26384-26445

    • ⏱ 2024-03-09 10:18:08

  • 📌 索引的确是一种高效找到数据的方式，但是如果MySQL还可以使用索引直接获取列的数据，这样就不再需要读取数据行了。如果索引的叶子节点中已经包含要查询的数据，那么还有什么必要再回表查询呢？如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为覆盖索引。需要注意的是，只有B-tree索引可以用于覆盖索引 ^17-26944-27099

    • ⏱ 2024-03-09 10:20:27

  • 📌 ，InnoDB的二级索引的叶子节点都包含了主键的值，这意味着InnoDB的二级索引可以有效地利用这些“额外”的主键列来覆盖查询。 ^17-28305-28369

    • ⏱ 2024-03-09 10:27:07

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

    • ⏱ 2024-03-09 10:28:42

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

    • ⏱ 2024-03-09 10:31:06

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

    • ⏱ 2024-03-09 10:32:46

  • 📌 即使ORDER BY子句不满足索引的最左前缀的要求，也可以用于查询排序，这正是因为索引的第一列被指定为了一个常数。 ^17-30807-30864

    • ⏱ 2024-03-09 10:49:54

  • 📌 冗余索引和重复索引有一些不同。如果创建了索引(A，B)，再创建索引(A)就是冗余索引，因为这只是前一个索引的前缀索引，因此，索引(A，B)也可以当作索引(A)来使用（这种冗余只是对B-tree索引来说的）。但是如果再创建索引(B，A)，则不是冗余索引，索引(B)也不是，因为B不是索引(A，B)的最左前缀列。另外，如果新建的是其他不同类型的索引（例如，哈希索引或者全文索引），那么无论覆盖了哪些索引列，也不会是B-tree索引的冗余索引。 ^17-34324-34543

    • ⏱ 2024-03-09 10:58:52

  • 📌 冗余索引通常发生在为表添加新索引的时候。例如，有人可能会增加一个新的索引(A，B)而不是扩展已有的索引(A)。还有一种情况是，将一个索引扩展为(A，ID)，其中ID是主键，因为主键列已经包含在二级索引中了，所以这也是冗余的。 ^17-34572-34684

    • ⏱ 2024-03-09 10:59:22

  • 📌 大多数情况下都不需要冗余索引，应该尽量扩展已有的索引而不是创建新的索引 ^17-34713-34748

    • ⏱ 2024-03-09 11:00:16

  • 📌 但有时候出于性能方面的考虑也需要冗余索引，因为扩展已有的索引会导致其变得太大，从而影响其他使用该索引的查询的性能。 ^17-34749-34806

    • ⏱ 2024-03-09 11:00:25

第8章 查询性能优化

  • 📌 每次看到SELECT的时候都需要用怀疑的眼光审视，是不是真的需要返回全部的列，很可能不是必需的。取出全部列，会让优化器无法完成索引覆盖扫描这类优化，还会为服务器带来额外的I/O、内存和CPU的消耗。因此，一些DBA严格禁止SELECT的写法，这样做有时候还能避免某些列被修改而带来的问题。 ^18-3600-3746
    • ⏱ 2024-03-09 12:11:19

第9章 复制

第10章 备份与恢复

第11章 扩展MySQL

第12章 云端的MySQL

第13章 MySQL的合规性

附录A 升级MySQL

附录B Kubernetes上的MySQL

关于作者

封面动物

读书笔记

本书评论