高性能 MySQL 第三版精简版

这本书很详细地介绍了 MySQL 的架构,历史以及各种特性,尤其在性能优化方面有很多好的建议,书籍很厚,内容也很多,这边整理了一个简化版,方便大家阅读.另外需要注意的是,这本书印刷于2013年,那时 MySQL 5.6 版本还没有发布,所以书中有些内容在现在看来是过时的.

第 1 章 MySQL架构与历史

MySQL是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，后被 Sun 公司收购, 最后一起被 Oracle 收购.

1.1 MySQL 逻辑架构

最上层架构负责连接处理,授权认证,安全等
第二层架构包含 MySQL 的主要核心功能,包括查询解析,分析,优化,缓存,以及所有的内置函数(例如日期,时间,数据和加密函数),所有跨存储引擎的功能都在这一层实现: 存储过程,触发器,视图等
第三层包含了存储引擎.存储引擎负责 MySQL 中数据的存储和提取.服务器通过API与存储引擎进行通信.存储引擎 API 包含几十个底层函数,用于执行诸如"开始一个事务"或者"根据主键提取一行记录"等操作.但存储引擎不会去解析SQL,不同存储引擎之间也不会相互通信,而只是简单地响应上层服务器的请求.

1.1.1 连接管理与安全性

每个客户端连接都会在服务器进程中拥有一个线程,这个连接的查询只会在这个单独的线程中执行,该线程只能轮流在某个 CPU 核心或者 CPU 中运行.服务器会负责缓存线程,因此不需要为每一个新建的链接创建或者销毁线程.

1.1.2 优化与执行

MySQL会解析查询,并创建内部数据结构(解析树),然后对其进行各种优化,包括重写查询,决定表的读取顺序,以及选择合适的索引等.

1.2 并发控制

1.2.1 读写锁

读锁(read lock): 也称为共享锁 (shared lock),读锁是共享的,或者说是相互不阻塞的.多个用户在同一时刻可以同时读取同一个资源,而互不干扰.
写锁(write lock): 也称为排他锁 (exclusive lock),写锁是排他的,也就是说一个写锁会阻塞其他的写锁和读锁.

1.2.2 锁粒度

一种提高共享资源并发性的方式就是让锁定对象更有选择性.尽量只锁定需要修改的部分数据,而不是所有的资源.
所谓的锁策略,就是在锁的开销和数据的安全性之间寻求平衡,这种平衡当然也会影响到性能.

表锁 (table lock)
表锁是 MySQL中最基本的锁策略,并且是开销最小的策略.它会锁定整张表,一个用户在对表进行写操作(插入,删除,更新等)前,需要先获得写锁,这会阻塞其他用户对该表的所有读写操作.
另外,写锁也比读锁有更高的优先级,因此一个写锁请求可能会被插入到读锁队列的前面,反之则不行.
尽管存储引擎可以管理自己的锁,MySQL本身还是会使用各种有效的表锁来实现不同的目的.例如,服务器会为诸如 ALTER TABLE 之类的语句使用表锁,而忽略存储引擎的锁机制.

行级锁 (row lock)
行级锁可以最大程度地支持并发处理(同时也带来了最大的锁开销).行级锁只在存储引擎层实现,而MySQL服务器层没有实现.

1.3 事务

事务就是一组原子性的SQL查询,也就是说,事务内的语句,要么全部执行成功,要么全部执行失败.
事务具有 ACID 特征, ACID 指原子性(atomicity),一致性(consistency).隔离性(isolation)和持久性(durability).
原子性: 一个事务必须被视为一个不可分割的最小工作单元,整个事务中的所有操作要么全部提交成功,要么全部失败回滚.
一致性: 数据库总是从一个一致性的状态转换到另外一个一致性的状态.
隔离性: 通常来说,一个事务所做的修改在最终提交之前,对其他事务是不可见的.
持久性: 一旦事务提交,则其所做的修改就会永久保存到数据库中.

1.3.1 隔离级别

隔离性其实比想象的要复杂.
在SQL标准中定义了四种隔离级别,每一种级别都规定了一个事务中所做的修改,哪些在事务内和事务间是可见的,哪些是不可见的.

未提交读 (READ UNCOMMITTED)
事务中的修改,即使没有提交,对其他事务也都是可见的.事务可以读取未提交的数据,这也被称为脏读(Dirty Read).这个级别一般很少使用.

提交读(READ COMMITTED)
大多数数据库系统的默认隔离级别都是 READ COMMITTED(但MySQL不是),一个事务从开始直到提交之前,所做的任何修改对其他事务都是不可见的,这个级别有时也叫作不可重复读(nonerepeatable read),因为两次执行同样的查询,可能会得到不一样的结果.

可重复读(REPEATABLE READ)
REPEATABLE READ 解决了脏读的问题,该级别保证了同一个事务中多次读取同样记录的结果是一致的.理论上存在幻读(Phantom Read) 的问题,所谓幻读,指的是当某个事务在读取某个范围内的记录时,另外一个事务又在该范围内插入了新的记录,当之前的事务再次读取该范围的记录时,会产生幻行(Phantom Row).可重复读是 MySQL 的默认事务隔离级别.

可串行化 (SERIALIZABLE)
SERIALIZABLE 是最高的隔离级别.它通过强制事务串行执行,避免了前面说的幻读的问题.简单来说, SERIALIZABLE 会在读取的每一行数据上都加锁,所以可能导致大量的超时和锁争用的问题.实际应用中很少用到,只有在非常需要确保数据的一致性而且可以接受没有并发的情况下,才考虑采用该级别.

设置隔离级别

1.3.2 死锁

死锁是指两个或者多个事务在同一资源上相互占用,并请求锁定对方占用的资源,从而导致恶性循环的现象.
形成原因:有些是因为真正的数据冲突,这种情况通常很难避免,但有些则是完全是由于存储引擎的实现方式导致的.
解决办法:数据库系统实现了各种死锁检测和死锁超时机制.一种是检测到死锁的循环依赖,立即返回一个错误,另一种就是将持有最少行级排他锁的事务进行回滚.

1.3.3 事务日志

使用事务日志,存储引擎在修改表的数据时只需要修改其内存拷贝,再把该修改行为记录到持久在硬盘上的事务日志中,而不用每次都将修改的数据本身持久到磁盘.事务日志持久以后,内存中被修改的数据在后台可以慢慢地刷回磁盘.目前大多数存储引擎都是这样实现的,我们通常称之为预写式日志 (Write-Ahead Logging),修改数据需要写两次磁盘.

1.3.4 MySQL中的事务

MySQL提供了两种事务型的存储引擎: InnoDB 和 NDB Cluster.

自动提交 (AUTOCOMMIT)
MySQL 默认采用自动提交模式.也就是说,如果不是显式地开始一个事务,则每个查询都被当做一个事务执行提交操作.

隐式锁定和显式锁定
InnoDB采用的是两阶段锁定协议 (two-phase locking protocol).
隐式锁定: 在事务执行过程中,InnoDB 会根据隔离级别在需要的时候执行锁定,锁只有在执行 COMMIT 或者 ROLLBACK 的时候才会释放,并且所有的锁是在同一时刻被释放.
显式锁定: InnoDB也支持通过特定的语句进行显式锁定,这些语句不属于 SQL 规范:

• SELECT … LOCK IN SHARE MODE
• SELECT … FOR UPDATE

注: 显式锁定提示经常被滥用,实际上应当尽量避免使用

1.4 多版本并发控制

可以认为 MVCC 是行级锁的一个变种,但是它在很多情况下避免了加锁操作,因此开销更低.
MVCC 的实现,是通过保存数据在某个时间点的快照来实现的.
InnoDB 的 MVCC 是通过在每行记录后面保存两个隐藏的列来实现的.
这两个列,一个保存了行的创建时间,一个保存行的过期时间(或删除时间).
当然存储的并不是实际的时间值,而是系统版本号(system version number).
每开始一个新的事务,系统版本号都会自动递增.
事务开始时刻的系统版本号作为事务的版本号,用来和查询到的每行记录的版本号进行比较.
下面看一下在 REPEATABLE READ 隔离级别下, MVCC 具体是如何操作的.

SELECT 操作
InnoDB 会根据以下两个条件检查每行记录:
a. InnoDB 只查找版本早于当前事务版本的数据行(也就是,行的系统版本号小于或等于事务的系统版本号),这样可以确保事务读取的行,要么是在事务开始前已经存在的,要么是事务自身插入或者修改过的.
b. 行的删除版本要么未定义,要么大于当前事务版本号.这可以确保事务读取到的行,在事务开始之前未被删除.
只有符合上述两个条件的记录,才能返回作为查询结果.

INSERT 操作
InnoDB 为新插入的每一行保存当前事务版本号作为行版本号.

DELETE 操作
InnoDB 为删除的每一行保存当前事务版本号作为行删除标识.

UPDATE 操作
InnoDB 首先会插入一行新纪录,并将当前事务版本号作为它的行版本号,然后将当前事务版本号保存到原来的行作为删除标识.

保存着两个额外系统版本号,使大多数读操作都可以不用加锁.
不足之处是每行记录都需要额外的存储空间,需要做更多的行检查工作,以及一些额外的维护工作.

1.5 MySQL 的存储引擎

可以使用 SHOW TABLE STATUS LIKE ‘表名’ 命令显示表的相关信息.
在文件系统中, MySQL 将每个数据库(也可以称之为 schema) 保存为数据目录下的一个子目录.
创建表时, MySQL 会在数据库子目录下创建一个和表同名的 .frm 文件保存表的定义.
因为 MySQL 使用文件系统的目录和文件来保存数据库和表的定义,大小写敏感性和具体的平台密切相关.
在 Windows 中,大小写是不敏感的;而在类 Unix 中则是敏感的.

1.5.1 InnoDB 存储引擎

InnoDB 是 MySQL 的默认事务型引擎,也是最重要,使用最广泛的存储引擎.
InnoDB 的数据存储在表空间 (tablespace) 中,表空间是由 InnoDB 管理的一个黑盒子,由一系列的数据文件组成.
InnoDB 采用 MVCC 来支持高并发,并且实现了四个标准的隔离级别.其默认级别是 REPEATABLE READ(可重复读),并且通过间隙锁(next-key locking) 策略防止幻读的出现.

InnoDB 表是基于聚簇索引建立的,聚簇索引对主键查询有很高的性能.不过它的二级索引(secondary index, 非主键索引) 中必须包含主键列,所以如果主键列很大的话,其他的所有索引都会很大.

1.5.2 MyISAM 存储引擎

在 MySQL 5.1 及之前的版本, MyISAM 是默认的存储引擎.
MyISAM 不支持事务和行级锁,而且还有一个缺陷就是崩溃后无法安全恢复.
MyISAM 会将表存储在两个文件中: 数据文件和索引文件,分别以 .MYD 和.MYI 为扩展名.

1.6 MySQL 时间线

版本 5.1 (2008) 这是 Sun 收购 MySQL AB 以后发布的第一个版本.
版本 5.5 (2010) 这是 Oracle 收购 Sun 以后发布的第一个版本.

1.8 总结

MySQL 拥有分层的架构.上层是服务器层的服务和查询执行引擎,下层则是存储引擎.
虽然有很多不同作用的插件 API, 但存储引擎 API 还是最重要的.
如果能理解 MySQL 在存储引擎和服务层之间处理查询时如何通过 API 来回交互,就能抓住 MySQL 的核心基础架构的精髓.

第 2 章 MySQL 基准测试

这一章主要讲的是测试方面的内容,这边就略过了.
摘录一些基本的 MySQL 信息查询语句.

查看 InnoDB 的状态

show engine INNODB status

查看建表语句

show create table upload_direct_log

SQL语句分析

explain select * from USER where LOGIN_NAME = 'datou';

查询 MySQL 当前的状态

show full processlist

查看表信息

show table status like 'USER'

查看存储引擎信息

show engine INNODB status

查看具体表的索引信息

show index from SYS_STATION_YYTIME

查看缓存是否开启

show variables like "%query_cache_type%"

查看 MySQL 版本信息

select VERSION();

第 4 章 Schema 与数据类型优化

4.1 选择优化的数据类型

4.1.1 整数类型

TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT
分别使用8, 16, 24, 32, 64 位存储空间.
整数类型有可选的 UNSIGNED(无符号) 属性,表示不允许负值,这大致可以使整数的上限提高一倍.
例如 TINYINT UNSIGNED 可以存储的范围是 0~ 255, 而 TINYINT 的存储范围是 -128~ 127

4.1.2 实数类型

实数是带有小数部分的数字.
DECIMAL 类用于存储精确的小数. DECIMAL 最多允 65 个数字,但需要额外的空间和计算开销.
MySQL 使用 DOUBLE 作为内部浮点计算的类型.
当数据量比较大时,可以考虑使用 BIGINT 代替 DECIMAL,这样可以同时避免浮点存储计算不精确和 DECIMAL 精确计算代价高的问题.

4.1.3 字符串类型

VARCHAR
VARCHAR 类型用于存储可变长字符串,是最常见的字符串数据类型.
VARCHAR 需要使用 1 或 2 个额外字节记录字符串的长度: 如果列的最大长度小于或等于 255 字节,则只使用 1 个字节表示,否则使用 2 个字节. 假设采用 latin1 字符集,一个 VARCHAR(10) 的列需要 11 个字节的存储空间. VARCHAR(1000) 的列则需要 1002 个字节,因为需要 2 个字节存储长度信息.
VARCHAR 节省了存储空间,所以对性能也有帮助.但是,由于行是变长的,在 UPDATE 时可能使行变得比原来更长,这就导致需要做额外的工作.

CHAR
CHAR 类型是定长的: MySQL 总是根据定义的字符串长度分配足够的空间.
当存储 CHAR 值时, MySQL 会删除所有的末位空格.(Oracle 会保留)
CHAR 适合存储很短的字符串, 或者所有值都接近同一个长度.
例如, CHAR(1) 只需要一个字节, 但是, VARCHAR(1) 却需要两个字节,因为还有一个记录长度的额外字节.

BLOB 和 TEXT 类型
BOLO 和 TEXT 都是为存储很大的数据而设计的字符串数据类型,分别采用二进制和字符方式存储.
实际上,它们分别属于两组不同的数据类型家族:
字符类型是 TINYTEXT, SMALLTEXT, TEXT, MEDIUMTEXT, LONGTEXT;
对应的二进制类型是 TINYBLOB, SMALLBLOB, BLOB, MEDIUMBLOB, LONGBLOB.
BLOB 和 TEXT 家族之间仅有的不同是 BLOB 类型存储的是二进制数据,没有排序规则或字符集,而 TEXT 类型有字符集和排序规则.

4.1.4 日期和时间类型

MySQL 提供两种相似的日期类型: DATETIME 和 TIMESTAMP
DATETIME
这个类型能保存大范围的值,从 1001 年到 9999 年, 精度为秒.
它把日期和时间封装到格式为 YYYYMMDDHHMMSS 的整数中,与时区无关.使用 8 个字节的存储空间.
TIMESTAMP
TIMESTAMP 类型保存了从 1970 年 1 月 1 日午夜(格林尼治标准时间)以来的秒数, 它和 UNIX 时间戳相同.
TIMESTAMP 只使用 4 个字节的存储空间,因此它的范围比 DATETIME 小得多: 只能表示从 1970 年到 2038 年.
TIMESTAMP 显示的值依赖于时区,例如,存储值为 0 的 TIMESTAMP 在美国东部时区显示为 “1969-12-31 19:00:00”, 与格林尼治时间差 5 个小时.

4.1.5 位数据类型

所有的位数据类型,不管底层存储格式和处理方式如何,从技术上来说都是字符串类型.
BIT
可以使用 BIT 列在一列中存储一个或多个 true/false 值.
BIT(1) 定义一个包含单个位的字段, BIT(2) 存储 2 个位,依次类推.BIT 列的最大长度是 64 个位.
比如存储值 ‘00111001’ , 尽量避免使用.

SET

4.1.6 选择标识符

为标识列(identifier column) 选择合适的数据类型非常重要.
标识列一般用于与其他表中的值进行比较,或者通过标识列查找数据.
标识列也可能在另外的表中作为外键使用.
整数通常是标识列最好的选择,因为它们很快并且可以使用 AUTO_IINCREMENT.
如果可能,应该避免使用字符串类型作为标识列,因为它们很消耗空间,并且通常比数字类型慢.

4.3 范式和反范式

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

4.3.1 范式的优点和缺点

优点:
范式化的更新操作通常比反范式化要快
重复数据很少,所以只需要修改更少的数据
重复数据少,表的占用空间也小,执行速度更快
更少需要使用 DISTINCT 或者 GROUP BY 语句
缺点:
稍微复杂一些的查询语句通常需要关联多张表,这不但代价昂贵,也可能使一些索引策略失效.

4.3.2 反范式化的优点和缺点

反范式化的 schema 因为所有数据都在一张表中,可以很好地避免关联.

影子表实现
影子表指的是一张在真实表背后创建的表,当完成建表操作后,就可以通过一个原子的重命名操作切换影子表和原表.
例如,如果需要重建 my_summary,则可以先创建 my_summary_new, 然后填充好数据,最后和真实表做切换.

4.5 加快 ALTER TABLE 操作的速度

MySQL 执行大部分修改表结构操作的方法是用新的结构创建一个空表,从旧表中查出所有数据插入新表,然后删除旧表.这样操作对于大表可能需要花费很长时间.

4.6 总结

尽可能保持任何东西小而简单总是好的.

• 避免过度设计,表的列很多(1000列以上),一个查询语句需要关联很多表(100张以上)
• 使用小而简单的合适数据类型.

第 5 章 创建高性能的索引

索引(在 MySQL 中也叫作 “键 (key)”) 是存储引擎用于快速找到记录的一种数据结构.这是索引的基本功能.

5.1 索引基础

在 MySQL 中, 存储引擎先在索引中找到对应值,然后根据匹配的所有记录找到对应的数据.

5.1.1 索引的类型

在 MySQL 中,索引是在存储引擎层而不是服务器层实现的,所以,并没有统一的索引表中: 不同存储引擎的索引的工作方式并不一样.

B-Tree 索引
当人们谈论索引的时候,如果没有特别指明类型,那多半说的是 B-Tree 索引,它使用 B-Tree 数据结构来存储数据.大多数 MySQL 引擎都支持这种索引.
B-Tree 通常意味着所有的值都是按顺序存储的,并且每一个叶子页到根的距离相同.

B-Tree 索引能够加快访问数据的速度.根节点的槽中存放了指向子节点的指针,存储引擎根据这些指针向下层查找.通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点,这些指针实际上定义了子节点页中值的上限和下限.
叶子节点比较特别,它们的指针指向的是被索引的数据,而不是其他的节点页.
图中仅绘制了一个节点和其对应的叶子节点,其实在根节点和叶子节点之间可能有很多层节点页.
树的深度和表的大小直接相关.
因为索引树中的节点是有序的,所以除了按值查找之外,所以还可以用于排序.
B-Tree 索引适用于全键值,键值范围或键前缀查找.

全值匹配
全值匹配指的是和索引中的所有列进行匹配.

匹配最左前缀
key(last_name, first_name, birth) 该索引可用于查找所有 last_name 为 Allen 的人,即只使用索引的第一列.

匹配列前缀
也可以匹配某一列的值的开头部分.例如前面索引可以用于查找所有以 J 开头的 last_name 的记录.这里也只使用了索引的第一列.

匹配范围值
例如前面提到的索引可用于查找 last_name 在 Allen 和 Barrymore 之间的人.这里也只使用了索引的第一列.

精确匹配某一列并范围匹配另外一列
前面提到的索引也可用于查找所有 last_name 为 Allen,并且 first_name 是字母 K 开头的记录.即第一列 last_name 全匹配,第二列 first_name 范围匹配.

只访问索引的查询
B-Tree 通常可以支持"只访问索引的查询",即查询只需要访问索引,而无须访问数据行.

B-Tree 索引的限制:
如果不是按照索引的最左列开始查找,则无法使用索引.
不能跳过索引中的列.也就是说,前面所述的索引无法用于查找 last_name 为 Smith 并且在某个特定日期出生的人. 如果不指定 first_name, 则 MySQL 只能使用索引的第一列.
如果查询中有某个列的范围查询,则其右边所有列都无法使用索引优化查找.
例如有查询 WHERE last_name=‘Smith’ AND first_name LIKE ‘J%’ AND birth = ‘1976-12-23’,这个查询只能使用索引的前两列,因为这里 LIKE 是一个范围条件.如果范围查询列值的数量有限,那么可以通过使用多个等于条件来代替范围条件.

到这里读者应该可以明白,前面提到的索引列的顺序是多么的重要: 这些限制都和索引列的顺序有关.
在优化性能的时候,可能需要使用相同的列但顺序不同的多个索引来满足不同类型的查询需求.

哈希索引
哈希索引(hash index) 基于哈希表实现,只有精确匹配索引所有列的查询才有效.对于每一行数据,存储引擎都会对所有的索引列计算一个哈希码(hash code).
在 MySQL 中,只有 Memory 引擎显式支持哈希索引.

哈希索引的限制:

• 哈希索引只包含哈希值和行指针,而不存储字段值,所以不能使用索引中的值来避免读取行.不过,由于内存中访问行的速度很快,所以大部分情况下对性能的影响并不明显.
• 哈希索引数据并不是按照索引值顺序存储的,所以也就无法用于排序
• 哈希索引也不支持部分索引列匹配查找,因为哈希索引始终是使用索引列的全部内容来计算哈希值的.
• 哈希索引只支持等值的比较查询,包括 =, IN(), <=> (注意 <> 和<=> 是不同的操作).也不支持任何范围查询,例如 WHERE price > 100.
• 访问哈希索引的数据非常快,除非有很多哈希冲突.
• 如果哈希冲突很多的话,一些索引维护操作的代价也会很高.

InnoDB 引擎有一个特殊的功能叫做"自适应哈希索引(daptive hash index)".
当 InnoDB 注意到某些索引值被使用得非常频繁时,他会在内存中基于 B-Tree 索引之上在创建一个哈希索引.这是一个完全自动的,内部的行为,用户无法控制或者配置.

创建自定义哈希索引.如果存储引擎不支持哈希索引,则可以模拟像 InnoDB 一样创建哈希索引.
思路很简单,新增一列,值为目标列的哈希值,查询时使用新列对应的哈希值进行匹配.
例: 如果需要存储大量的 URL, 并根据 URL 进行搜索查找,如果使用 B-Tree 来存储 URL,存储的内容就会很大,因为 URL 本身都很长.
若删除原来 URL 列上的索引,而新增一个被索引的 url_crc 列,使用 CRC32 做哈希,就可以使用下面的方式查询:

这样实现的缺陷是需要额外维护哈希值,可以创建几个触发器,用它们来实现.

空间数据索引 (R-Tree)
MyISAM 表支持空间索引,可以用作地理数据存储.使用比较少.

全文索引
全文索引是一种特殊类型的索引,它查找的是文本中的关键词,而不是直接比较索引中的值.
在相同的列上同时创建全文索引和基于值的 B-Tree 索引不会有冲突,全文索引适用于 MATCH AGAINST 操作,而不是普通的 WHERE 条件操作.
我们将在第 7 章讨论更多的全文索引的细节.

其他索引类别
还有很多第三方的存储引擎使用不同类型的数据结构来存储索引.例如 TokuDB 使用分形树索引(fractal tree index).
InnoDB 也有很多相关主题,包括聚簇索引,覆盖索引等.

5.2 索引的优点

索引可以让服务器快速地定位到表的指定位置.但这不是索引的唯一作用,根据创建索引的数据结构不同,索引也有一些其他的附加作用.
最常见的 B-Tree 索引,安装顺序存储数据,所以可以用来做 ORDER BY 和 GROUP BY 操作.
因为 B-Tree 也会将相关的列值存储在索引中,所以某些查询只使用索引就能够完成全部查询.

索引优点:

1. 索引大大减少了服务器需要扫描的数据量
2. 索引可以帮助服务器避免排序和临时表
3. 索引可以将随机 I/O 变为顺序 I/O

索引是最好的解决方案吗?

• 索引并不总是最好的工具.总的来说,只有当索引帮助存储引擎快速查找到记录带来的好处大于其带来的额外工作时,索引才是有效的.
• 对于非常小的表,大部分情况下简单的全表扫描更高效.
• 对于中到大型的表,索引就非常有效
• 但对于特大型的表,建立和使用索引的代价将随之增长.这时可以使用分区技术(请参考第 7 章)
• 如果表的数量特别多,可以建立一个元数据信息表,用来查询需要用到的某些特性.

5.3 高性能的索引策略

正确地创建和使用索引是实现高性能查询的基础.

5.3.1 独立的列

独立的列指索引列不能是表达式的一部分,也不能是函数的参数.
例如, 下面的查询无法使用索引


我们应该养成简化 WHERE 条件的习惯,始终将索引列单独放在比较符号的一侧.

5.3.2 前缀索引和索引选择性

前缀索引指在列的值太长的情况下,我们只索引值开始的部分字符,这样可以节约索引空间,从而提高索引效率.
索引的选择性是指,不重复的索引值(也称为基数,cardinality) 和数据表的记录总数(#T) 的比值,范围从 1/#T 到 1 之间.索引的选择性越高则查询效率越高.唯一索引的选择性是 1, 这是最好的索引选择性,性能也是最好的.
所以我们需要做的就是确定前缀索引的长度是多少.
对于具体数据的表, 操作如下:
首先,我们找到最常见的城市列表:

然后查找最频繁出现的城市前缀,直到前缀的选择性接近完整列的选择性.
经过实验后发现前缀长度为 7 时比较合适.

不想一次次试的话,可以这样:
先计算出完整列的选择性

再计算不同前缀长度的选择性

从中找选择性最接近的.
最后创建一个前缀索引:

前缀索引使索引更新,更快,但缺点是 MySQL 无法使用前缀索引做 ORDER BY 和 GROUP BY,也无法使用前缀索引做覆盖扫描.

5.3.3 多列索引

很多人对多列索引的理解都不够.一个常见的错误就是,为每个列创建独立的索引,或者按照错误的顺序创建多列索引.

5.3.4 选择合适的索引列顺序

正确的顺序依赖于使用该索引的查询,并且同时需要考虑如何更好地满足排序和分组的需要.

5.3.5 聚簇索引

聚簇索引并不是一种单独的索引类型,而是一种数据存储方式.
InnoDB 的聚簇索引实际上在同一个结构中保存了 B-Tree 索引和数据行.当表有聚簇索引时,它的数据实际上存放在索引的叶子页(leaf page) 中.术语"聚簇" 表示数据行和相邻的键值紧凑地存储在一起.因为无法同时把数据行存放在两个不同的地方,所以一个表只能有一个聚簇索引.

5.3.6 覆盖索引

如果一个索引包含(或者说覆盖) 所有需要查询的字段的值,我们就称之为"覆盖索引".

5.3.7 使用索引扫描来做排序

MySQL 有两种方式可以生成有序的结果: 通过排序操作;或者按索引顺序扫描.
MySQL 可以使用同一个索引既满足排序,有用于查找行.因此,如果可能,设计索引时应该尽可能地同时满足这两种任务,这样是最好的.
只有当索引的列顺序和 ORDER BY 子句的顺序完全一致,并且所有列的排序方向 (倒序或正序) 都一样时, MySQL 才能够使用索引来对结果做排序.
如果查询需要关联多张表,则只有当 ORDER BY 子句引用的字段全部为第一个表时,才能使用索引做排序.
ORDER BY 需要满足索引的最左前缀的要求,否则,MySQL 都需要执行排序操作,而无法利用索引排序.
有一种情况可以不满足最左前缀的要求,就是前导列为常量的时候.
例如:

5.3.9 冗余和重复索引

重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引.应该避免这样创建重复索引.

MySQL 的唯一限制和主键限制都是通过索引实现的.
冗余索引和重复索引有一些不同.如果创建了索引(A, B),再创建索引(A) 就是冗余索引,因为这只是前一个索引的前缀索引.但是如果再创建索引(B,A), 则不是冗余索引,索引 (B) 也不是.
对于 InnoDB 来说主键列已经包含在二级索引中了,所以在列 (A) 上的索引就相当于在 (A, ID) 上的索引.如果有像 WHERE A = 5 ORDER BY ID 这样的查询,这个索引会很有作用.

5.6 总结

MySQL 和存储引擎访问数据的方式,加上索引的特性,使得索引成为一个影响数据访问的有力而灵活的工具.
在 MySQL 中,大多数情况下都会使用 B-Tree 索引.其他类型的索引大多只适用于特殊的目的.
选择索引和编写利用索引查询时的三个原则:

1. 单行访问时很慢的.如果服务器从存储中读取一个数据块只是为了获取其中一行,那么就浪费了很多工作.最好读取的块中能包含尽可能多所需要的行.使用索引可以创建位置引用以提升效率.
2. 按顺序访问范围数据是很快的,原因是顺序 I/O 不需要多次磁盘寻道,所以比随机 I/O 要快很多,并且如果服务器能够按需要顺序读取数据,那么就不再需要额外的排序操作.
3. 索引覆盖查询是很快的.如果一个索引包含了查询需要的所有列,那么存储引擎就不需要再回表查找行.这避免了大量的单行访问.

如何判断一个系统创建的索引是否合理
建议按响应时间来对查询进行分析,找出那些耗时最长的查询或者给服务器带来最大压力的查询,然后检查这些查询的 schema, SQL 和索引结构,判断是否有查询扫描了太多的行,是否做了很多额外的排序或者使用了临时表,是否使用随机 I/O 访问数据,或者是有太多回表查询那些不在索引中的列的操作.
可以使用 pt-query-digest 的查询审查 “review” 功能,分析其 EXPLAIN 出来的执行计划.

第 6 章 查询性能优化

查询优化,索引优化,库表结构优化需要齐头并进,一个不落.

6.1 为什么查询速度会慢

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

查询性能低下最基本的原因是访问的数据太多.
对于低效查询的分析步骤:

1. 确认应用程序是否访问了太多的行,或者太多的列
2. 确认 MySQL 服务器是否在分析大量超过需要的数据行.

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

典型案例:

查询不需要的记录
前端需要10条,后台查询了100条.解决办法是在查询后面加上 LIMIT

多表关联时返回全部列
应该根据需要指定返回列

总是取出全部列
每次看到 SELECT * 的时候都需要用怀疑的眼光审视.取出全部列,会让优化器无法完成所有覆盖扫描这类优化,还会为服务器带来额外的 I/O, 内存 和 CPU 的消耗.如果不是为了提高相同代码片段的复用性,尽量指明需要的列.

重复查询相同的数据
不断地重复执行相同的查询,然后每次都返回完全相同的数据.解决办法: 使用缓存,提高性能.

6.2.2 MySQL 是否在扫描额外的记录

在确定查询只返回需要的数据后,接下来看看查询为了返回结果是否扫描了过多的数据.对于 MySQL, 最简单的衡量查询开销的三个指标如下:

• 响应时间
• 扫描的行数
• 返回的行数

它们大致反映了 MySQL 在内部执行查询时需要访问多少数据,并可以大概推算出查询运行的时间.
这三个指标都会记录到 MySQL 的慢日志中,所以检查慢日志记录是找出扫描行数过多的查询的好办法.

-- 查询慢日志所在目录
SHOW VARIABLES LIKE "%slow_query_log_file%"

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

扫描的行数和返回的行数
理想情况下扫描的行数和返回的行数应该是相同的.
一般情况下,扫描的行数对返回的行数的比例在 1:1 和 10:1 之间,不过有时候这个值也可能非常大.

扫描的行数和访问类型
在 EXPLAIN 语句中的 type 列反映了访问类型.访问类型有很多种,从全表扫描到索引扫描,范围扫描,唯一索引查询,常数引用等.

6.3 重构查询的方式

在优化有问题的查询时,目标应该是找到一个更优的方法获得实际需要的结果–而不一定总是需要从 MySQL 获取一模一样的结果集.

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

以前认为网络通信.查询解析和优化是一件代价很高的事情,所以总是强调需要数据库层完成尽可能多的工作.
但对 MySQL 并不适用, MySQL 从设计上让连接和断开连接都很轻量级.现代的网络速度比以前要快很多,无论是带宽还是延迟.所以运行多个小查询现在已经不是大问题了.

6.3.2 切分查询

有时对于一个大范围查询,我们需要"分而治之",将之切分成多个小范围查询.
例如删除旧数据,如果用一个大的语句一次性完成的话,可能需要锁住很多数据,占满整个事务日志,耗尽系统资源.我们可以将一个大的 DELETE 语句切分成多个较小的查询.

6.3.3 分解关联查询

很多高性能的应用都会对关联查询进行分解.

好处是可以利用已有的缓存,提高查询速度.

6.4 查询执行的基础

MySQL 是如何优化和执行查询的.

1. 客户端发送一条查询给服务器
2. 服务器先检查查询缓存,如果命中了缓存,则立刻返回存储在缓存中的结果.否则进入下一阶段.
3. 服务器端进行 SQL 解析,预处理,再由优化器生成对应的执行计划.
4. MySQL 根据优化器生成的执行计划,调用存储引擎的 API 来执行查询
5. 将结果返回给客户端

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

一般来说,不需要去理解 MySQL 通信协议的内部实现细节,只需要大致理解通信协议是如何工作的.
MySQL 客户端和服务器之间的通信协议是"半双工"的,这意味着,在任何一个时刻,要么是由服务器向客户端发送数据,要么是由客户端向服务器发送数据,这两个动作不能同时发生.

查询状态
对于一个 MySQL 连接,或者说一个线程,任何时刻都有一个状态,该状态表示了 MySQL 当前正在做什么.
最简单的查看状态是使用 SHOW FULL PROCESSLIST 命令.
各种状态值

Sleep
线程正在等待客户端发送新的请求

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

Locked
在 MySQL 服务器层,该线程正在等待表锁.在存储引擎级别实现的锁,例如 InnoDB 的行锁,并不会体现在线程状态中.

Analyzing and statistics
线程正在收集存储引擎的统计信息,并生成查询的执行计划.

Coping to tmp table [on disk]
线程正在执行查询,并且将其结果集都复制到一个临时表中,这种状态一般要么是在做 GROUP BY 操作,要么是文件排序操作,或者是 UNION 操作. on dist 表示 MySQL 正在将一个内存临时表放到磁盘上.

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

Sending Data
这表示多种情况: 线程可能在多个状态之间传送数据,或者在生成结果集,或者在向客户端返回数据.

6.4.2 查询缓存(MySQL 8.0版本没有缓存)

• 在解析一个查询语句之前,如果查询缓存是打开的,那么 MySQL 会优先检查这个查询是否命中查询缓存中的数据.
• 这个检查是通过一个对大小写敏感的哈希查找实现的.
• 查询和缓存中的查询即使只有一个字节不同,那也不会匹配缓存结果,这种情况下查询就会进入下一阶段的处理.
• 如果当前的查询正好命中了查询缓存,并且拥有权限,那么会直接从缓存中拿到结果并返回给客户端.

6.4.3 查询优化处理

查询的生命周期的下一步是将一个 SQL 转换成一个执行计划, MySQL 再依照这个执行计划和存储引擎进行交互.
这包括多个子阶段: 解析 SQL, 预处理, 优化 SQL 执行计划.

语法解析器和预处理
MySQL 通过关键字将 SQL 语句进行解析,并生成一棵对应的"解析树".
MySQL 解析器将使用 MySQL 语法规则验证和解析查询.例如,是否使用了错误的关键字.
预处理器则根据一些 MySQL 规则进一步检查解析树是否合法.
例如,数据表和数据列是否存在,下一步预处理器会验证权限.

查询优化器
现在语法树被认为是合法的了,并且有优化器将其转化成执行计划.
一条查询可以有很多种执行方式,最后都返回相同的结果.
优化器的作用就是找到这其中最好的执行计划.
MySQL 使用基于成本的优化器,它尝试预测一个查询使用某种执行计划时的成本,并选择其中成本最小的一个.
可以通过查询当前会话的 Last_query_cost 的值来得知 MySQL 计算的当前查询的成本.


MySQL 能够处理的优化类型

• 重新定义关联表的顺序
• 将外连接转化成内连接
• 使用等价变化规则
• 优化 COUNT(), MIN() 和 MAX()
• 覆盖索引扫描
• 子查询优化
• 提前终止查询
• 等值传播
• 列表 IN() 的比较

执行计划
MySQL 生成查询的一棵指令树(左侧深度优先的树),然后通过存储引擎执行完成这棵指令树并返回结果.

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

排序优化
无论如何排序都是一个成本很高的操作,所以应尽可能避免排序或者避免对大量数据进行排序.
当 MySQL 不能使用索引生成排序结果的时候, MySQL 需要自己进行排序,如果数据量小则在内存中进行,如果数据量大则需要使用磁盘,MySQL 将这个过程同一称为文件排序(filesort).

6.4.4 查询执行引擎

在解析和优化阶段, MySQL 将生成查询对应的执行计划, MySQL 的查询执行引擎则根据这个执行计划来完成整个查询.这里的执行计划是一个数据结构,而不是字节码

6.4.5 返回结果给客户端

查询执行的最后一个阶段是将结果返回给客户端.
即使查询不需要返回结果, MySQL 仍会返回这个查询的一些信息,如果该查询影响到的行数.
如果查询可以被缓存,那么 MySQL 在这个阶段也会将结果存放到查询缓存中.
MySQL 将结果返回客户端是一个逐步返回的过程,一旦服务器处理完最后一个关联表,开始生成第一条结果时, MySQL 就可以开始向客户端逐步返回结果集了.
结果集中的每一行都会以一个满足 MySQL 客户端/服务器通信协议的封包发送,再通过 TCP 协议进行传输,在 TCP 传输的过程中,可能对 MySQL 的封包进行缓存然后批量传输.
这样处理有两个好处: 服务器无须存储太多的结果,也让客户端第一时间获得返回结果.

优化 LIMIT 分页
在偏移量非常大的时候, MySQL 需要查询大量数据,并丢弃大量数据,这样的代价非常高.
优化此类分页查询的一个办法就是尽可能地使用索引覆盖扫描,而不是查询所有的列,然后根据需要做一次关联操作再返回所需的列.
例如:

改写为下面的样子:

另一种办法就是避免偏移量

6.7.7 优化 UNION 查询

MySQL 总是通过创建并填充临时表的方式来执行 UNION 查询.
除非确实需要服务器消除重复的行,否则就一定要使用 UNION ALL.如果没有 ALL 关键字, MySQL 会给临时表加上 DISTINCT 选项,这会导致对整个临时表的数据做唯一性检查.

6.9 总结

除了表结构,索引,查询等基础的优化手段, MySQL 还有一些高级的特性,例如分区,分区和索引有些类似但是原理不同.MySQL 还支持查询缓存.

第 7 章 MySQL 高级特性

7.1 分区表

对用户来说,分区表是一个独立的逻辑表,但是底层由多个物理子表组成.
实现分区的代码实际上是对一组底层表的句柄对象(Handler Object) 的封装.
对分区表的请求,都会通过句柄对象转化成对存储引擎的接口调用.
MySQL 实现分区表的方式----对底层表的封装意味着索引也是按照分区的子表定义的,而没有全局索引.这和 Oracle 不同,在 Oracle 中可以更加灵活地定义索引和表是否进行分区.
MySQL 在创建表是使用 PARTITION BY 子句定义每个分区存放的数据.
在执行查询的时候,优化器会根据分区定义过滤那些没有我们需要的数据的分区.
分区的一个主要目的是将数据按照一个较粗的粒度分布在不同的表中.
分区表的使用场景:

分区表的一些限制:

7.1.1 分区表的原理

如前所述,分区表由多个相关的底层表实现,这些底层表也是由句柄对象(Handler object) 表示,所以我们也可以直接访问各个分区.
存储引擎管理分区的各个底层表和管理普通表一样(所有的底层表都必须使用相同的存储引擎),分区表的索引只是在各个底层表上各自加上一个完全相同的索引.
从存储引擎的角度来看,底层表和普通表没有任何不同.

分区表上的操作按照下面的操作逻辑进行:

SELECT 查询:
当查询一个分区表的时候,分区层先打开并锁住所有底层表,优化器先判断是否可以过滤部分分区,然后再调用对应的存储引擎接口访问各个分区的数据.

INSERT 操作
当写入一条记录时,分区层先打开并锁住所有的底层表,然后确定哪个分区接收这条记录,再将记录写入对应底层表.

DELETE 操作
当删除一条记录是,分区层先打开并锁住所有的底层表,然后确定数据对应的分区,最后对响应底层表进行删除操作.

UPDATE 操作
当更新一条记录时,分区层先打开并锁住所有的底层表, MySQL 先确定需要更新的记录在哪个分区,然后取出数据并更新,再判断更新后的数据应该放在哪个分区,最后对底层表进行写入操作,并对原数据所在的底层表进行删除操作.

7.1.2 分区表的类型

MySQL 支持多种分区表.我们看到最多的是根据范围进行分区,每个分区存储落在某个范围的记录,分区表达式可以是列,也可以是包含列的表达式.
例如, 下表就可以将每一年的销售额存放在不同的分区里:

我们还看到的一些其他的分区技术包括:

7.1.3 如何使用分区表

理解分区时还可以将其当做索引的最初形态,以代价非常小的方式定位到需要的数据在哪一片"区域".在这片"区域"中,你可以做顺序扫描,可以建索引,还可以将数据都缓存到内存,等等.
为了保证大数据量的可扩展性,一般有下面两个策略:

7.1.4 什么情况下会出问题

上面我们介绍的两个分区策略都基于两个非常重要的假设: 查询都能够过滤(prunning)掉很多额外的分区,分区本身并不会带来很多额外的代价.

NULL 值会使分区过滤无效
如果分区表达式的值为 NULL或者是一个非法值的时候,记录都会被存放到第一个分区,所以第一个分区可能会非常大.解决办法就是使用 PARTITION p_nulls VALUES LESS THAN (0) 来创建存放 NULL 值和非法值的分区.

分区列和索引列不匹配
如果定义的索引列和分区列不匹配,会导致查询无法进行分区过滤.
假设在列 a 上定义了索引,而在列 b 上进行分区.因为每个分区都有其独立的索引,所以扫描列 b 上的索引就需要扫描每一个分区内对应的索引.

选择分区的成本可能很高
对于范围分区,像回答"这一行属于哪个分区","这些复合查询条件的行在哪些分区"这样的问题,服务器需要扫描所有的分区定义的列表来找到正确的答案.类似这样的线性搜索的效率不高,所以随着分区数的增长,成本会越来越高.
可以通过限制分区的数量来缓解此问题.(分区数量100以内没有问题)
其他的分区类型,比如键分区和哈希分区,则没有这样的问题.

打开并锁住所有底层表的成本可能很高
可以用批量操作的方式来降低单个操作的此类开销.例如使用批量插入或者 LOAD DATA INFILE,依次删除多行数据,等等.

维护分区的成本可能很高
新增或删除分区的维护操作会非常快,但重组分区或者类似 ALTER 语句的操作,这类操作需要复制数据,所以成本就比较高.

7.2 视图

MySQL 5.0 版本之后开始引入视图.视图本身是一个虚拟表,不存放任何数据.

7.2.1 可更新视图

可更新视图(updatable view) 是指可以通过更新这个视图来更新视图涉及的相关表.
例:

如果视图定义中包含了 GROUP BY, UNION, 聚合函数,以及其他一些特殊情况,就不能被更新了.
更新视图的查询也可以是一个关联语句,但是有一个限制,被更新的列必须来自同一个表中.另外,所有使用临时表算法实现的视图都无法被更新.

7.2.2 视图对性能的影响

多数人认为视图不能提升性能,实际上,在 MySQL 中某些情况下视图也可以帮助提升性能.
例如,在重构 schema 的时候可以使用视图,使得在修改视图底层表结构的时候,应用代码还可能继续不报错的运行.

7.2.3 视图的限制

MySQL 并不会保存视图定义的原始 SQL 语句.

7.3 外键约束

使用外键是有成本的.比如外键通常都要求每次在修改数据时都要在另外一张表中多执行一次查找操作.
外键会带来很大的额外消耗.

7.4 在 MySQL 内部存储代码

MySQL 允许通过触发器,存储过程,函数,定时任务的形式来存储代码.

优点:

• 在服务器内部执行,节省带宽和网络延迟
• 可以在应用开发和数据库开发人员之间更好地分工.

缺点:

• 存储代码的效率稍微差些,可使用的函数非常有限
• 存储代码一旦出现错误,很难调试
• 存储代码可能会给应用程序代码的部署带来额外的复杂性.

7.4.1 存储过程和函数

当一个存储过程调用可以代替很多小查询的时候,如果查询很小,相比这个查询执行的成本,解析和网络开销就变得非常明显.

7.4.2 触发器

触发器可以让你在执行 INSERT, UPDATE 或者 DELETE 的时候,执行一些特定的操作.
可以指定是在 SQL 语句执行前触发还是在执行后触发.

注意点:

• 对每一个表的每一个事件,最多只能定义一个触发器
• MySQL 只支持"基于行的触发"–也就是说,触发器始终是针对一条记录的,而不是针对整个 SQL 语句的.如果变更的数据集非常大的话,效率会很低.

触发器缺点:

7.4.3 事件

通常我们会把复杂的 SQL 都封装到一个存储过程中,这样事件在定时执行的时候只需要做一个简单的 CALL 调用.
事件在一个独立事件调度线程中被初始化,这个线程和处理连接的线程没有任何关系.它不接收任何参数,也没有任何的返回值.
可以在 MySQL 的日志中看到命令的执行日志,还可以在表 INFORMATION_SCHEMA.EVENTS 中看到各个事件状态,例如这个事件最后一次执行的时间等.

7.9 字符集和校对

字符集是指一种从二进制编码到某类字符符号的映射,可以参考如何使用一个字节来表示英文字母.
"校对"是指一组用于某个字符集的排序规则.
只有基于字符的值才真正的"有"字符集的概念.
MySQL 的设置可以分为两类: 创建时的默认值,在服务器和客户端通信时的设置.

创建对象时的默认设置
MySQL 服务器有默认的字符集和校对规则,每个数据库也有自己的默认值,每张表也有自己的默认值.
创建列的时候,可以指定列的字符集.
优先级最高的是列的字符集.

服务器和客户端通信时的设置
当服务器和客户端通信的时候,它们可能使用不同的字符集.这时,服务器将进行必要的翻译转换工作.

7.10 全文索引

如果你希望通过关键字的匹配来进行查询过滤,那么就需要基于相似度的查询,而不是原来的精确数值比较.全文索引就是为这种场景设计的.
查看索引类型

FULLTEXT 表示全文索引, 如果是 BTREE 表示是一个 BTree 索引
使用全文索引

和普通查询不同,这类查询自动按照相识度进行排序

7.11 分布式 (XA) 事务

存储引擎的事务特性能够保证在存储引擎级别实现 ACID, 而分布式事务则让存储引擎级别的 ACID 可以扩展到数据库层面,甚至可以扩展到多个数据库之间.

7.12 查询缓存

MySQL 查询缓存保存查询返回的完整结果.
当查询命中该缓存, MySQL 会立刻返回结果,跳过了解析,优化和执行阶段.
查询缓存应该默认关闭,

7.12.1 MySQL 如何判断缓存命中

缓存存放在一个引用表中,通过一个哈希值引用,这个哈希值包括了如下因素,即查询本身,当前要查询的数据库,客户端协议的版本等一些其他可能会影响返回结果的信息.
当判断缓存是否命中时, MySQL 不会解析查询语句,而是直接使用 SQL 语句和客户端发送过来的其他原始信息.任何字符上的不同,例如空格,注释都会导致缓存的不命中.
当查询语句中有一些不确定的数据时,则不会被缓存.例如包含函数 NOW() 或者 CURRENT_DATE() 的查询不会被缓存.包含用户自定义函数,用户变量,临时表都不会被缓存.

7.12.2 查询缓存如何使用内存.

查询缓存是完全存储在内存中的.

7.13 总结

本章详细介绍了 MySQL 一些特性.