《高性能MySQL(第3版)》读书笔记

BinBin_Bang

已于 2022-07-19 22:27:04 修改

阅读量384

点赞数

分类专栏：读书笔记 sql 数据库文章标签： mysql 数据库

于 2022-07-19 22:21:52 首次发布

本文链接：https://blog.csdn.net/biubiubiubibibi/article/details/125882384

版权

sql 同时被 3 个专栏收录

4 篇文章 0 订阅

订阅专栏

读书笔记

3 篇文章 0 订阅

订阅专栏

数据库

3 篇文章 0 订阅

订阅专栏

高性能MySQL(第3版)

1.1 MySQL逻辑架构

MySQL服务器逻辑架构图
第二层架构是MySQL比较有意思的部分。大多数MySQL的核心服务功能都在这一层，包括查询解析、分析、优化、缓存以及所有的内置函数（例如，日期、时间、数学和加密函数），所有跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等
每个客户端连接都会在服务器进程中拥有一个线程

1.2 并发控制

无论何时，只要有多个查询需要在同一时刻修改数据，都会产生并发控制的问题
本章的目的是讨论MySQL在两个层面的并发控制：服务器层与存储引擎层。

读锁和写锁是数据库引擎中实现事务的原理，
表锁、行锁和其他的如MVCC是引擎的具体实现事务方式。
这里说的实现事务不是说ACID都支持，可能支持的程度没有那么高。ACID是事务的一个最理想的转态。其实我觉得不能把ACID单做是事务的定义，非常容易误导人

共享锁（shared lock）和排他锁（exclusive lock），也叫读锁（read lock）和写锁（writelock）。
表锁（table lock）
ALTER TABLE之类的语句使用表锁，而忽略存储引擎的锁机制
行级锁（row lock）
存储引擎中实现了行级锁

1.3 事务

事务
可以用START TRANSACTION语句开始一个事务，然后要么使用COMMIT提交事务将修改的数据持久保留，要么使用ROLLBACK撤销所有的修改
ACID表示原子性（atomicity）、一致性（consistency）、隔离性（isolation）和持久性（durability）
对于一些不需要事务的查询类应用，选择一个非事务型的存储引擎，可以获得更高的性能
即使存储引擎不支持事务，也可以通过LOCK TABLES语句为应用提供一定程度的保护
隔离级别
下面简单地介绍一下四种隔离级别。
READ UNCOMMITTED（未提交读）
事务可以读取未提交的数据，这也被称为脏读（Dirty Read）
READ COMMITTED（提交读）
换句话说，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的
这个级别有时候也叫做不可重复读（nonrepeatable read），因为两次执行同样的查询，可能会得到不一样的结果。
REPEATABLE READ（可重复读）
无法解决另外一个幻读
所谓幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行（Phantom Row）
InnoDB和XtraDB存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）解决了幻读的问题
可重复读是MySQL的默认事务隔离级别。
SERIALIZABLE（可串行化）
它通过强制事务串行执行，避免了前面说的幻读的问题。
事务日志可以帮助提高事务的效率
使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。事务日志采用的是追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁头，所以采用事务日志的方式相对来说要快得多
事务日志持久以后，内存中被修改的数据在后台可以慢慢地刷回到磁盘。目前大多数存储引擎都是这样实现的，我们通常称之为预写式日志（Write-Ahead Logging），修改数据需要写两次磁盘。
强制执行COMMIT提交当前的活动事务。典型的例子，在数据定义语言（DDL
另外还有LOCK TABLES等其他语句也会导致同样的结果
InnoDB也支持通过特定的语句进行显式锁定，这些语句不属于SQL规范(3)：
• SELECT … LOCK IN SHARE MODE
• SELECT … FOR UPDATE
MySQL也支持LOCK TABLES和UNLOCK TABLES语句，这是在服务器层实现的，和存储引擎无关。

1.4 多版本并发控制

多版本并发控制（MVCC）。不仅是MySQL，包括Oracle、PostgreSQL等其他数据库系统也都实现了MVCC，但各自的实现机制不尽相同
可以认为MVCC是行级锁的一个变种，但是它在很多情况下避免了加锁操作，因此开销更低。虽然实现机制有所不同，但大都实现了非阻塞的读操作，写操作也只锁定必要的行
InnoDB的MVCC，是通过在每行记录后面保存两个隐藏的列来实现的
一个保存了行的创建时间，一个保存行的过期时间（或删除时间）
当然存储的并不是实际的时间值，而是系统版本号（system version number）
每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。下面看一下在REPEATABLEREAD隔离级别下，MVCC具体是如何操作的。
SELECTInnoDB会根据以下两个条件检查每行记录：InnoDB只查找版本早于当前事务版本的数据行（也就是，行的系统版本号小于或等于事务的系统版本号），这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的。行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除
INSERTInnoDB为新插入的每一行保存当前系统版本号作为行版本号。DELETEInnoDB为删除的每一行保存当前系统版本号作为行删除标识。UPDATEInnoDB为插入一行新记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识
MVCC只在REPEATABLE READ和READ COMMITTED两个隔离级别下工作。其他两个隔离级别都和MVCC不兼容(4)，因为READ UNCOMMITTED总是读取最新的数据行，而不是符合当前事务版本的数据行。而SERIALIZABLE则会对所有读取的行都加锁。

1.5 MySQL的存储引擎

在文件系统中，MySQL将每个数据库（也可以称之为schema）保存为数据目录下的一个子目录
InnoDB采用MVCC来支持高并发，并且实现了四个标准的隔离级别。其默认级别是REPEATABLE READ（可重复读），并且通过间隙锁（next-key locking）策略防止幻读的出现。间隙锁使得InnoDB不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，以防止幻影行的插入
InnoDB表是基于聚簇索引建立的
聚簇索引对主键查询有很高的性能。不过它的二级索引（secondary index，非主键索引）中必须包含主键列，所以如果主键列很大的话，其他的所有索引都会很大
主键应当尽可能的小
MyISAM提供了大量的特性，包括全文索引、压缩、空间函数（GIS）等，但MyISAM不支持事务和行级锁
MyISAM会将表存储在两个文件中：数据文件和索引文件，分别以.MYD和.MYI为扩展名
MyISAM对整张表加锁，而不是针对行。读取时会对需要读到的所有表加共享锁，写入时则对表加排他锁
MyISAM压缩表
可以使用myisampack对MyISAM表进行压缩（也叫打包pack）
压缩表是不能进行修改的（除非先将表解除压缩，修改数据，然后再次压缩）。压缩表可以极大地减少磁盘空间占用，因此也可以减少磁盘I/O，从而提升查询性能。压缩表也支持索引，但索引也是只读的
如果需要在线热备份，那么选择InnoDB就是基本的要求

3.3 剖析MySQL查询

MySQL的当前版本中，慢查询日志是开销最低、精度最高的测量查询时间的工具。如果还在担心开启慢查询日志会带来额外的I/O开销，那大可以放心。我们在I/O密集型场景做过基准测试，慢查询日志带来的开销可以忽略不计
从慢查询日志中生成剖析报告需要有一款好工具，这里我们建议使用pt-query-digest
EXPLAIN
SHOW PROFILE
不使用SHOW PROFILE命令而是直接查询INFORMATION_SCHEMA中对应的表，则可以按照需要格式化输出

4.1 选择优化的数据类型

尽量避免NULL
通常情况下最好指定列为NOT NULL，除非真的需要存储NULL值。
整数类型有可选的UNSIGNED属性，表示不允许负值，这大致可以使正数的上限提高一倍
DECIMAL类型用于存储精确的小数
浮点和DECIMAL类型都可以指定精度。对于DECIMAL列，可以指定小数点前后所允许的最大位数
VARCHAR和CHAR类型
VARCHAR类型用于存储可变长字符串，是最常见的字符串数据类型。它比定长类型更节省空间，因为它仅使用必要的空间
CHAR类型是定长的：MySQL总是根据定义的字符串长度分配足够的空间

4.2 MySQL schema设计中的陷阱

MySQL schema设计中的陷阱
太多的列
太多的关联

4.3 范式和反范式

在范式化的数据库中，每个事实数据会出现并且只出现一次
相反，在反范式化的数据库中，信息是冗余的，可能会存储在多个地方。

4.4 缓存表和汇总表

有时提升性能最好的方法是在同一张表中保存衍生的冗余数据。然而，有时也需要创建一张完全独立的汇总表或缓存表

4.5 加快ALTER TABLE操作的速度

MySQL的ALTER TABLE操作的性能对大表来说是个大问题
MySQL执行大部分修改表结构操作的方法是用新的结构创建一个空表，从旧表中查出所有数据插入新表，然后删除旧表

4.6 总结

ALTER TABLE是让人痛苦的操作，因为在大部分情况下，它都会锁表并且重建整张表

第5章创建高性能的索引

索引（在MySQL中也叫做“键（key）”）是存储引擎用于快速找到记录的一种数据结构
索引优化应该是对查询性能优化最有效的手段了

5.1 索引基础

B-Tree索引
InnoDB则使用的是B+Tree
InnoDB则按照原数据格式进行存储
B-Tree通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同
哈希索引
哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。
哈希索引也有它的限制
哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。
哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的
哈希索引只支持等值比较查询，包括=、IN()、<=>（注意<>和<=>是不同的操作）。也不支持任何范围查询，例如WHERE price>100
访问哈希索引的数据非常快，除非有很多哈希冲突
InnoDB引擎有一个特殊的功能叫做“自适应哈希索引（adaptive hashindex）
当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于B-Tree索引之上再创建一个哈希索引，这样就让B-Tree索引也具有哈希索引的一些优点，比如快速的哈希查找。这是一个完全自动的、内部的行为，用户无法控制或者配置，不过如果有必要，完全可以关闭该功能
在B-Tree基础上创建一个伪哈希索引。这和真正的哈希索引不是一回事，因为还是使用B-Tree进行查找，但是它使用哈希值而不是键本身进行索引查找。你需要做的就是在查询的WHERE子句中手动指定使用哈希函数。
如果采用这种方式，记住不要使用SHA1()和MD5()作为哈希函数。因为这两个函数计算出来的哈希值是非常长的字符串，会浪费大量空间，比较时也会更慢
处理哈希冲突。当使用哈希索引进行查询的时候，必须在WHERE子句中包含常量值：
全文索引
全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值
全文索引更类似于搜索引擎做的事情，而不是简单的WHERE条件匹配

5.2 索引的优点

总结下来索引有如下三个优点：索引大大减少了服务器需要扫描的数据量。索引可以帮助服务器避免排序和临时表。索引可以将随机I/O变为顺序I/O。

5.3 高性能的索引策略

ALTER TABLE sakila.city_demo ADD KEY (city(7));

创建前缀索引：
创建前缀索引：
前缀索引是一种能使索引更小、更快的有效办法，但另一方面也有其缺点：MySQL无法使用前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描
聚簇索引(7)并不是一种单独的索引类型，而是一种数据存储方式
这意味着通过二级索引查找行，存储引擎需要找到二级索引的叶子节点获得对应的主键值，然后根据这个值去聚簇索引中查找到对应的行。这里做了重复的工作：两次B-Tree查找而不是一次(9)。对于InnoDB，自适应哈希索引能够减少这样的重复工作
MyISAM按照数据插入的顺序存储在磁盘上
索引中的每个叶子节点包含“行号
MyISAM中主键索引和其他索引在结构上没有什么不同。主键索引就是一个名为PRIMARY的唯一非空索引
因为在InnoDB中，聚簇索引“就是”表，所以不像MyISAM那样需要独立的行存储
使用主键值当作指针会让二级索引占用更多的空间，换来的好处是，InnoDB在移动行时无须更新二级索引中的这个“指针”
聚簇和非聚簇表对比图
从这个案例可以看出，使用InnoDB时应该尽可能地按主键顺序插入数据，并且尽可能地使用单调增加的聚簇键的值来插入新行
如果索引的叶子节点中已经包含要查询的数据，那么还有什么必要再回表查询呢?如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引
只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，MySQL才能够使用索引来对结果做排序(14)。如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表时，才能使用索引做排序。ORDER BY子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求；否则，MySQL都需要执行排序操作，而无法利用索引排序。
InnoDB只有在访问行的时候才会对其加锁，而索引能够减少InnoDB访问的行数，从而减少锁的数量。
但这只有当InnoDB在存储引擎层能够过滤掉所有不需要的行时才有效

5.4 索引案例学习

那么可以通过在查询条件中新增AND SEX IN（‘m’,‘f’）来让MySQL选择该索引。这样写并不会过滤任何行，和没有这个条件时返回的结果相同。但是必须加上这个列的条件，MySQL才能够匹配索引的最左前缀

5.5 维护索引和表

OPTIMIZE TABLE

5.6 总结

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

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

一般最常见和重要的等待是I/O和锁等待
这里的“Using Where”表示MySQL将通过WHERE条件来筛选存储引擎返回的记录。

6.3 重构查询的方式

do {
rows_affected = do_query(
“DELETE FROM messages WHERE created < DATE_SUB(NOW(),INTERVAL 3 MONTH)
LIMIT 10000”)
} while rows_affected > 0

6.4 查询执行的基础

统计信息，包括：每个表或者索引有多少个页面、每个表的每个索引的基数是多少、数据行和索引长度、索引的分布信息等。优化器根据这些信息来选择一个最优的执行计划
对于UNION查询，MySQL先将一系列的单个查询结果放到一个临时表中，然后再重新读出临时表数据来完成UNION查询

6.5 MySQL查询优化器的局限性

如果希望UNION的各个子句能够根据LIMIT只取部分结果集，或者希望能够先排好序再合并结果集的话，就需要在UNION的各个子句中分别使用这些子句。
SELECT first_name, last_name
FROM sakila.actor
ORDER BY last_name)
UNION ALL
(SELECT first_name, last_name
FROM sakila.customer
ORDER BY last_name)
LIMIT 20;
这条查询将会把actor中的200条记录和customer表中的599条记录存放在一个临时表中，然后再从临时表中取出前20条。可以通过在UNION的两个子查询中分别加上一个LIMIT 20来减少临时表中的数据：
(SELECT first_name, last_name
FROM sakila.actor
ORDER BY last_name
LIMIT 20)
UNION ALL
(SELECT first_name, last_name
FROM sakila.customer
ORDER BY last_name
LIMIT 20)
LIMIT 20;
MySQL不允许对同一张表同时进行查询和更新。
可以通过使用生成表的形式来绕过上面的限制

6.6 查询优化器的提示（hint）

STRAIGHT_JOIN
这个提示可以放置在SELECT语句的SELECT关键字之后，也可以放置在任何两个关联表的名字之间。第一个用法是让查询中所有的表按照在语句中出现的顺序进行关联。第二个用法则是固定其前后两个表的关联顺序。
SQL_CACHE和SQL_NO_CACHE
这个提示告诉MySQL这个结果集是否应该缓存在查询缓存中
FOR UPDATE和LOCK IN SHARE MODE
两个提示主要控制SELECT语句的锁机制，但只对实现了行级锁的存储引擎有效
这两个提示经常被滥用，很容易造成服务器的锁争用问题
USE INDEX、IGNORE INDEX和FORCE INDEX

6.7 优化特定类型的查询

如果希望知道的是结果集的行数，最好使用COUNT（*），这样写意义清晰，性能也会很好
当表A和表B用列c关联的时候，如果优化器的关联顺序是B、A，那么就不需要在B表的对应列上建上索引
一般来说，除非有其他理由，否则只需要在关联顺序中的第二个表的相应列上创建索引
确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列，这样MySQL才有可能使用索引来优化这个过程。
关于子查询优化我们给出的最重要的优化建议就是尽可能使用关联查询代替
例如可能是LIMIT 1000,20这样的查询，这时MySQL需要查询10 020条记录然后只返回最后20条，前面10000条记录都将被抛弃，这样的代价非常高
优化此类分页查询的一个最简单的办法就是尽可能地使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列
对于偏移量很大的时候，这样做的效率会提升非常大。考虑下面的查询：
UNION查询中
经常需要手工地将WHERE、LIMIT、ORDER BY等子句“下推”到UNION的各个子查询中，以便优化器可以充分利用这些条件进行优化
用户自定义变量
可以使用下面的SET
然后可以在任何可以使用表达式的地方使用这些自定义变量

7.1 分区表

MySQL在创建表时使用PARTITION BY子句定义每个分区存放的数据。在执行查询的时候，优化器会根据分区定义过滤那些没有我们需要数据的分区，这样查询就无须扫描所有分区——只需要查找包含需要数据的分区就可以了。
一个表最多只能有1024个分区
分区表中无法使用外键约束
MySQL支持多种分区表。我们看到最多的是根据范围进行分区，每个分区存储落在某个范围的记录，分区表达式可以是列，也可以是包含列的表达式。例如，下表就可以将每一年的销售额存放在不同的分区里：
CREATE TABLE sales (
order_date DATETIME NOT NULL,
– Other columns omitted
) ENGINE=InnoDB PARTITION BY RANGE(YEAR(order_date)) (
PARTITION p_2010 VALUES LESS THAN (2010),
PARTITION p_2011 VALUES LESS THAN (2011),
PARTITION p_2012 VALUES LESS THAN (2012),
PARTITION p_catchall VALUES LESS THAN MAXVALUE );
PARTITION分区子句中可以使用各种函数。但有一个要求，表达式返回的值要是一个确定的整数，且不能是一个常数。
NULL值会使分区过滤无效
应该避免建立和分区列不匹配的索引
所以，对于访问分区表来说，很重要的一点是要在WHERE条件中带入分区列，有时候即使看似多余的也要带上，这样就可以让优化器能够过滤掉无须访问的分区
如果没有这些条件，MySQL就需要让对应存储引擎访问这个表的所有分区，如果表非常大的话，就可能会非常慢。
使用EXPLAIN PARTITION可以观察优化器是否执行了分区过滤，下面是一个示例：
下面查询的WHERE条件理论上可以过滤分区，但实际上却不行：
EXPLAIN PARTITIONS SELECT * FROM sales_by_day WHERE YEAR(day) = 2010\G
MySQL只能在使用分区函数的列本身进行比较时才能过滤分区，而不能根据表达式的值去过滤分区，即使这个表达式就是分区函数也不行。
这就和查询中使用独立的列才能使用索引的道理是一样的
所以只需要把上面的查询等价地改写为如下形式即可：
EXPLAIN PARTITIONS SELECT * FROM sales_by_day -> WHERE day BETWEEN ‘2010-01-01’ AND ‘2010-12-31’\G
一个很重要的原则是：即便在创建分区时可以使用表达式，但在查询时却只能根据列来过滤分区。
例如，若分区表是关联操作中的第二张表，且关联条件是分区键，MySQL就只会在对应的分区里匹配行。（EXPLAIN无法显示这种情况下的分区过滤，因为这是运行时的分区过滤，而不是查询优化阶段的。）
用户无法访问底层的各个分区，对用户来说分区是透明的

7.4 在MySQL内部存储代码

存储过程和存储函数都可以接收参数然后返回值，但是触发器和事件却不行。
MySQL并没有什么选项可以控制存储程序的资源消耗，所以在存储过程中的一个小错误，可能直接把服务器拖死。
触发器可以让你在执行INSERT、UPDATE或者DELETE的时候，执行一些特定的操作
可以在MySQL中指定是在SQL语句执行前触发还是在执行后触发。
对每一个表的每一个事件，最多只能定义一个触发器（换句话说，不能在AFTER INSERT上定义两个触发器）。
触发器可能导致死锁和锁等待。如果触发器失败，那么原来的SQL语句也会失败。如果没有意识到这其中是触发器在搞鬼，那么很难理解服务器抛出的错误代码是什么意思
事件是MySQL 5.1引入的一种新的存储代码的方式。它类似于Linux的定时任务，不过是完全在MySQL内部实现的。你可以创建事件，指定MySQL在某个时候执行一段SQL代码，或者每隔一个时间间隔执行一段SQL代码。通常，我们会把复杂的SQL都封装到一个存储过程中，这样事件在执行的时候只需要做一个简单的CALL调用。
它不接收任何参数，也没有任何的返回值
CREATE EVENT optimize_somedb ON SCHEDULE EVERY 1 WEEK
DO
CALL optimize_tables(‘somedb’);