高性能MySQL笔记

1. MySQL架构与历史

MySQL最重要、最与众不同的特性是它的存储引擎架构，这种架构的设计将查询处理（Query Processing）及其他系统任务（Server Task）和数据的存储/提取相分离。这种处理和存储分离的设计可以在使用时根据性能、特性，以及其他需求来选择数据存储的方式。

1.1 MySQL逻辑架构

• 最上层的服务基于网络的客户端/服务器，比如连接处理、授权认证、安全等

• 第二层包含了大多数MySQL的核心服务功能，包括查询解析、分析、优化、缓存以及所有内置函数。

• 第三层包含了存储引擎，负责数据的存储和提取。

1.1.1 连接管理与安全性

• 每个客户端连接都会在服务器进程中拥有一个线程，这个连接的查询只会在这个单独的线程中执行

• 该线程只能轮流在某个CPU核心中运行，服务器会负责缓存线程，因此无需为每一个连接创建或销毁线程

• 当客户端连接时，服务器需要进行认证。如果使用了安全套接字（SSL），还可以使用X.509证书认证。

1.1.2 优化与执行

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

• 用户可以通过特殊的关键字提示（hint）优化器，影响它的决策过程。也可以请求优化器解释（explain）优化过程的各个因素。

• 对于SELECT语句，在解析查询之前，服务器会先检查查询缓存（Query Cache），如果能够在其中找到对应的查询，服务器就不必再执行查询解析、优化和执行的整个过程，而是直接返回查询缓存中的结果集。

1.2 并发控制

无论何时，只要有多个查询需要在同一时刻修改数据，都会产生并发控制的问题。

1.2.1 读写锁

• 在处理并发读或者写时，可以通过实现一个由两种类型的锁组成的锁系统来解决问题。

• 这两种类型的锁通常被称为共享锁（shared lock）和排他锁（exclusive lock），也叫读锁（read lock）和写锁（write lock）。

• 读锁是共享的，相互不阻塞。写锁则是排他的，也就是说一个写锁会阻塞其他的写锁和读锁

1.2.2 锁粒度

• 任何时候，在给定的资源上，锁定的数据量越少，则系统的并发程度越高，只要相互之间不发生冲突即可。

• 所谓的锁策略，就是在锁的开销和数据的安全性之间寻求平衡。

• 每种MySQL存储引擎都可以实现自己的锁策略和锁粒度，最重要的两种锁策略就是表锁和行级锁

表锁（table lock）

• 表锁是MySQL中最基本的锁策略，并且开销最小，即锁定整张表。

• 写锁的优先级更高，一个写锁请求可能会被插入到读锁队列前。

行级锁（row lock)

• 行级锁可以最大程度支持并发处理（同时开销最大）。

• 行级锁只在存储引擎层实现，MySQL服务器层没有实现。

1.3 事务

事务内的语句，要么全部执行成功，要么全部执行失败。事务的ACID特性：

原子性（Atomicity）

• 一个事务必须被视为一个不可分割的最小工作单元

一致性（Consistency）

• 数据库总是从一个一致性的状态转换到另外一个一致性的状态。

• 只要事务最终没有提交，事务中所做的修改也不会保存到数据库中。

隔离性（Isolation）

• 一个事务所做的修改在最终提交前，对其他事务是不可见的。

持久性（Durability）

• 一旦事务提交，其所做的修改就会永久保存到数据库中。即使系统崩溃，修改的数据也不会丢失。

1.3.1 隔离级别

在SQL标准中定义了四种隔离级别：

READ UNCOMMITTED（未提交读）

• 事务中的修改即使没有提交，对其他事务也是可见的。

• 事务可以读取未提交的数据，称为脏读（Dirty Read）。

READ COMMITTED（提交读）

• 大多数数据库的默认隔离级别都是READ COMMITTED（但MySQL不是）

• 一个事务开始时，只能看见已经提交事务的修改，即事务从开始到提交前，任何修改都是不可见的。

• 也叫做不可重复读，因为两次同样的查询可能会得到不一样的结果。

REPEATABLE READ（可重复读）

• REPEATABLE READ解决了脏读的问题。该级别保证了在同一个事务中多次读取同样记录的结果是一致的。

• 但是可重复读隔离级别还是无法解决幻读（Phantom Read）的问题。

• 幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行（Phantom Row）。

• InnoDB通过多版本并发控制（MVCC,Multiversion Concurrency Control）解决了幻读的问题。

• 可重复读是MySQL的默认事务隔离级别。

SERIALIZABLE（可串行化）

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

• SERIALIZABLE会在读取的每一行数据上都加锁，可能导致大量的超时和锁争用的问题。

1.3.2 死锁

• 死锁是指多个事务在同一资源上相互占用，并请求锁定对方占用的资源

• 当多个事务试图以不同的顺序锁定资源时，就可能会产生死锁。多个事务同时锁定同一个资源时，也会产生死锁。

• InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚

1.3.3 事务日志

• 修改表的数据时只需要修改其内存拷贝，再把修改行为记录到持久在硬盘上的事务日志中。

• 因此不用每次都将修改的数据本身持久到磁盘。事务日志持久以后，内存中被修改的数据在后台可以慢慢地刷回到磁盘。

1.3.4 MySQL中的事务

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

• MySQL默认采用自动提交（AUTOCOMIMIT）模式。如果不是显式地开始一个事务，则每个查询都被当作一个事务执行提交操作。

• MySQL服务器层不管理事务，事务是由下层的存储引擎实现的。所以在同一个事务中，使用多种存储引擎是不可靠的。

1.4 多版本并发控制

MySQL的大多数事务型存储引擎实现的都不是简单的行级锁。它们一般都同时实现了多版本并发控制（MVCC）。

• MVCC的实现，是通过保存数据在某个时间点的快照来实现的。

• InnoDB的MVCC，是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间（或删除时间）。

• 存储的并不是实际的时间值，而是系统版本号）。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比

1.5 MySQL的存储引擎

1.5.1 InnoDB存储引擎

• InnoDB是MySQL的默认事务型引擎，也是最重要、使用最广泛的存储引擎。

• 它被设计用来处理大量的短期（short-lived）事务，即大部分是正常提交的，很少会被回滚。

• InnoDB的性能和自动崩溃恢复特性，使得它在非事务型存储的需求中也很流行。

InnoDB概览

• InnoDB的数据存储在表空间（tablespace）中，表空间是由InnoDB管理的一个黑盒子，由一系列的数据文件组成。

• InnoDB采用MVCC来支持高并发，并且实现了四个标准的隔离级别。

• 默认级别是REPEATABLE READ（可重复读），并且通过间隙锁（next-keylocking）策略防止幻读的出现。

• 间隙锁使得InnoDB不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，以防止幻影行的插入。

• InnoDB表是基于聚簇索引建立的，二级索引必须包含主键列，主键应尽可能小。

1.5.2 MyISAM存储引擎

• 在MySQL 5.1及之前的版本，MyISAM是默认的存储引擎。

• 但MyISAM不支持事务和行级锁，而且有一个毫无疑问的缺陷就是崩溃后无法安全恢复。

• 对于只读的数据，或者表比较小、可以忍受修复（repair）操作，则依然可以继续使用MyISAM

4. Schema与数据类型优化

4.1 选择优化的数据类型

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

• 简单就好。简单数据类型的操作通常需要更少的CPU周期。例如，整型比字符操作代价更低。

• 尽量避免NULL，对于MySQL更难优化，使得索引和值比较都更复杂

4.1.1 整数类型

• 有两种类型的数字：整数（whole number）和实数（real number）。

• 几种整数类型：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT。分别使用8，16，24，32，64位存储空间。

4.1.2 实数类型

• MySQL既支持精确类型，也支持不精确类型。可以使用DECIMAL存储比BIGINT还大的整数。

• DECIMAL类型用于存储精确的小数。在MySQL 5.0和更高版本，DECIMAL类型支持精确计算。

• 尽量只在对小数进行精确计算时才使用DECIMAL，例如存储财务数据。但在数据量比较大的时候，可以考虑使用BIGINT代替

4.1.3 字符串类型

VARCHAR和CHAR是两种最主要的字符串类型。

VARCHAR

• 用于存储可变长字符串，比定长类型更节省空间。VARCHAR需要使用1或2个额外字节记录字符串长度。

• 下面这些情况下使用VARCHAR是合适的：

  • 字符串列的最大长度比平均长度大很多；

  • 列的更新很少，所以碎片不是问题；

  • 使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储。

CHAR

• CHAR类型是定长的：MySQL总是根据定义的字符串长度分配足够的空间。

• CHAR适合存储很短的字符串，或者所有值都接近同一个长度。例如CHAR非常适合存储密码的MD5值，因为这是一个定长的值。

• 对于经常变更的数据，CHAR也比VARCHAR更好，因为定长的CHAR类型不容易产生碎片。

BLOB和TEXT类型

• BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。

• 与其他类型不同，MySQL把每个BLOB和TEXT值当作一个独立的对象处理。

4.1.4 日期和时间类型

MySQL可以使用许多类型来保存日期和时间值，例如YEAR和DATE。MySQL能存储的最小时间粒度为秒，但是MySQL也可以使用微秒级的粒度进行临时运算。

DATETIME

• 这个类型能保存大范围的值，从1001年到9999年，精度为秒。

• 它把日期和时间封装到格式为YYYYMIMDDHHMMSS的整数中，与时区无关。使用8个字节的存储空间。

TIMESTAMP

• TIMETAMP类型保存了从1970年1月1日午夜（格林尼治标准时间）以来的秒数，它和UNIX时间戳相同。

• TIMESTAMP只使用4个字节的存储空间，因此它的范围比DATETIME小得多：只能表示从1970年到2038年。

• 通常应尽量使用TIMESTAMP，因为它比DATETIME空间效率更高。

4.1.5 位数据类型

所有这些位类型，不管底层存储格式和处理方式如何，从技术上来说都是字符串类型。

BIT

• 在MySQL 5.0之前，BIT是TINYINT的同义词。但是在MySQL 5.0以及更新版本，这是一个特性完全不同的数据类型。

• 可以使用BIT列在一列中存储一个或多个true/false值。BIT（1）定义一个包含单个位的字段，BIT（2）存储2个位，依此类推。

4.2 范式和反范式

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

4.3.1 范式的优点和缺点

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

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

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

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

范式化设计的schema的缺点是通常需要关联。稍微复杂一些的查询语句在符合范式的schema上都可能需要至少一次关联，也许更多。这不但代价昂贵，也可能使一些索引策略无效。

4.3.2 反范式的优点和缺点

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

• 如果不需要关联表，则对大部分查询最差的情况—即使表没有使用索引—是全表扫描。当数据比内存大时这可能比关联要快得多，因为这样避免了随机I/O

4.3.3 混用范式化和反范式化

事实上，在实际应用中经常需要混用，可能使用部分范式化的schema、缓存表，以及其他技巧。

• 最常见的反范式化数据的方法是复制或者缓存，在不同的表中存储相同的特定列。

• 在MySQL 5.0和更新版本中，可以使用触发器更新缓存值，这使得实现这样的方案变得更简单。

4.4 缓存表和汇总表

有时提升性能最好的方法是在同一张表中保存衍生的冗余数据。然而，有时也需要创建一张完全独立的汇总表或缓存表（特别是满足检索的需求时）。如果能容许少量的脏数据，这是非常好的方法。

4.4.1 物化视图

许多数据库管理系统（例如Oracle或者微软SQL Server）都提供了一个被称作物化视图的功能。物化视图实际上是预先计算并且存储在磁盘上的表，可以通过各种各样的策略刷新和更新。MySQL并不原生支持物化视图。

4.4.2 计数器表

如果应用在表中保存计数器，则在更新计数器时可能碰到并发问题。计数器表在Web应用中很常见。可以用这种表缓存一个用户的朋友数、文件下载次数等。创建一张独立的表存储计数器通常是个好主意，这样可使计数器表小且快。使用独立的表可以帮助避免查询缓存失效。

4.5 加快ALTER TABLE操作的难度

MySQL的ALTER TABLE操作的性能对大表来说是个大问题。

• MySQL执行大部分修改表结构操作的方法是用新的结构创建一个空表，从I表中查出所有数据插入新表，然后删除旧日表。

• 这样操作可能需要花费很长时间，且大部分ALTER TABLE操作将导致MySQL服务中断。

• 另外一种方法是通过ALTER COLUMN操作来改变列的默认值，这个语句会直接修改.frm文件而不涉及表数据。所以，这个操作是非常快的。

4.5.1 只修改.frm文件

从上面的例子我们看到修改表的.frm文件是很快的，但MySQL有时候会在没有必要的时候也重建表。如果愿意冒一些风险，可以让MySQL做一些其他类型的修改而不用重建表。

4.6 总结

MySQL喜欢简单的原则：

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

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

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

• 注意可变长字符串，其在临时表和排序时可能导致悲观的按最大长度分配内存。

• 尽量使用整型定义标识列。

• 小心使用ENUM和SET。虽然它们用起来很方便，但是不要滥用。最好避免使用BIT。

• 范式是好的，但是反范式（大多数情况下意味着重复数据）有时也是必需的，并且能带来好处。

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

  • 对大部分场景，必须使用其他更常规的方法，例如在备机执行ALTER并在完成后把它切换为主库。

5. 创建高性能的索引

索引（在MySQL中也叫做“键（key）”）是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键。尤其是当表中的数据量越来越大时，索引对性能的影响愈发重要。

5.1 索引基础

索引可以包含一个或多个列的值。如果索引包含多个列，那么列的顺序也十分重要，因为MySQL只能高效地使用索引的最左前缀列。创建一个包含两个列的索引，和创建两个只包含一列的索引是大不相同的。

5.1.1 索引的类型

B-Tree索引

• InnoDB则使用的是B+Tree，存储引擎以不同的方式使用B-Tree索引，性能也各有不同，各有优劣。

• B-Tree通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同。图5-1展示了B-Tree索引的抽象表示。

• B-Tree索引能够加快访问数据的速度，因为不再需要进行全表扫描来获取数据，而是从索引的根节点开始进行搜索。

• 叶子节点比较特别，它们的指针指向的是被索引的数据，而不是其他的节点页（不同引擎的“指针”类型不同）。

下面是一些关于B-Tree索引的限制：

• 如果不是按照索引的最左列开始查找，则无法使用索引。

• 不能跳过索引中的列。

• 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。

哈希索引

• 哈希索引 （hash index）基于哈希表实现，只有精确匹配索引所有列的查询才有效。

• 对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

哈希索引的查找速度很快，但也有它的限制：

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

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

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

• 哈希索引只支持等值比较查询，包括=、IN（）、＜=>（注意<>和<=>是不同的操作）。也不支持任何范围查询，例如WHERE price>100。

• 当出现哈希冲突的时候，存储引擎必须遍历链表中所有的行指针。如果哈希冲突很多的话，一些索引维护操作的代价也会很高。

5.2 索引的优点

最常见的B-Tree索引，按照顺序存储数据，所以MySQL可以用来做ORDER BY和GROUP BY操作。因为数据是有序的，所以B-Tree也就会将相关的列值都存储在一起。最后，因为索引中存储了实际的列值，所以某些查询只使用索引就能够完成全部查询。据此特性，总结下来索引有如下三个优点：

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

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

3. 索引可以将随机I/O变为顺序I/O。

三星系统

• 索引将相关的记录放到一起则获得一星；

• 如果索引中的数据顺序和查找中的排列顺序一致则获得二星；

• 如果索引中的列包含了查询中需要的全部列则获得“三星”。

索引并不总是最好的工具。只有当索引帮助存储引擎快速查找到记录带来的好处大于其带来的额外工作时，索引才是有效的。

5.3 高性能的索引策略

5.3.1 独立的列

如果查询中的列不是独立的，则MySQL就不会使用索引。“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数。

例如，这个查询无法使用actor_id列的索引：SELECT actor_id FROM sakila actor WHERE actor_id + 1 = 5;

凭肉眼很容易看出WHERE中的表达式其实等价于actor_id=4，但是MySQL无法自动解析这个方程式。下面是另一个常见的错误：

SELECT ... WHERE TO_DAYS (CURRENT_DATE) - TO_DAYS (date_col) <= 10;

5.3.2 前缀索引和索引选择性

• 索引的选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性。

• 要选择足够长的前缀以保证较高的选择性，同时又不能太长（以便节约空间）。

• 前缀索引是一种能使索引更小、更快的有效办法，但另一方面也有其缺点：

  • MySQL无法使用前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描。

5.3.3 聚簇索引

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

• InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。InnoDB通过主键聚集数据。

• 当表有聚簇索引时，它的数据行实际上存放在索引的叶子页（leaf page）中。术语“聚簇”表示数据行和相邻的键值紧凑地存储在一起,使用覆盖索引扫描的查询可以直接使用页节点中的主键值。一个表只能有一聚簇索引。

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

5.3.4 覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，无需回表查询，我们就称之为“覆盖索引”。

• 不是所有类型的索引都可以成为覆盖索引。MySQL只能使用B-Tree索引做覆盖索引。

• 当发起一个被索引覆盖的查询（也叫做索引覆盖查询）时，在EXPLAIN的Extra列可以看到“Using index”的信息

5.3.5 使用索引扫描来做排序

• MySQL有两种方式可以生成有序的结果：通过排序操作；或者按索引顺序扫描；

• 如果EXPLAIN出来的type列的值为“index”，则说明MySQL使用了索引扫描来做排序（不要和Extra列的“Using index”搞混淆了）。

• 只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向都一样时，MySQL才能够使用索引来对结果做排序。

• 如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表时，才能使用索引做排序。

• ORDER BY子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求。

5.3.6 冗余和重复索引

MySQL允许在相同列上创建多个索引，重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。应该避免这样创建重复索引，发现以后也应该立即移除。

• 冗余索引和重复索引有一些不同：

  • 如果创建了索引（A，B），再创建索引（A）就是冗余索引，因为这只是前一个索引的前缀索引。因此索引（A，B）也可以当作索引（A）来使用（这种冗余只是对B-Tree索引来说的）。

  • 如果再创建索引（B，A），则不是冗余索引，索引（B）也不是，因为B不是索引（A,B）的最左前缀列。

• 增加新索引将会导致INSERT、UPDATE、DELETE等操作的速度变慢，特别是达到了内存瓶颈的时候。

5.4 索引案例学习

假设要设计一个在线约会网站，用户信息表有很多列，包括国家、地区、城市、性别、眼睛颜色，等等。网站必须支持上面这些特征的各种组合来搜索用户，还必须允许根据用户的最后在线时间、其他会员对用户的评分等对用户进行排序并对结果进行限制。如何设计索引满足上面的复杂需求呢？

第一件需要考虑的事情是需要使用索引来排序，还是先检索数据再排序。使用索引排序会严格限制索引和查询的设计。例如，如果希望使用索引做根据其他会员对用户的评分的排序，则WHERE条件中的ageBETWEEN 18 AND 25就无法使用索引。如果MySQL使用某个索引进行范围查询，也就无法再使用另一个索引（或者是该索引的后续字段）进行排序了。

• 尽可能将需要做范围查询的列放到索引的后面，以便优化器能使用尽可能多的索引列。

• 即使有索引，如果用户界面上需要翻页，并且翻页翻到比较靠后时查询也可能非常慢。下面这个查询就通过ORDER BY和LIMIT偏移量的组合翻页到很后面的时候：SELECT cols FROM profiles WHERE sex='M' ORDER BY rating LIMIT 100000; 10;

  • 更好的办法是限制用户能够翻页的数量，实际上这对用户体验的影响不大，因为用户很少会真正在乎搜索结果的第10000页。

  • 另一个比较好的策略是使用延迟关联，通过使用覆盖索引查询返回需要的主键，再根据这些主键关联原表获得需要的行。

5.5 维护索引和表

即使用正确的类型创建了表并加上了合适的索引，还需要维护表和索引来确保它们都正常工作。维护表有三个主要的目的：找到并修复损坏的表，维护准确的索引统计信息，减少碎片。

5.5.1 找到并修复损坏的表

• 可以尝试运行CHECK TABLE来检查是否发生了表损坏。CHECK TABLE通常能够找出大多数的表和索引的错误。

• 可以使用REPAIR TABLE命令来修复损坏的表，但不是所有的存储引擎都支持，可以使用ALTER TABLE ... ENGINE=INNODB

5.5.2 更新索引统计信息

MySQL的查询优化器会通过两个API来了解存储引擎的索引值的分布信息，以决定如何使用索引。

• 第一个API是records_in_range（），通过向存储引擎传入两个边界值获取在这个范围大概有多少条记录。

• 第二个APl是info（），该接口返回各种类型的数据，包括索引的基数（每个键值有多少条记录）。

5.5.3 减少索引和数据的碎片

B-Tree索引可能会碎片化，这会降低查询的效率。碎片化的索引可能会以很差或者无序的方式存储在磁盘上。根据设计，B-Tree需要随机磁盘访问才能定位到叶子页，所以随机访问是不可避免的。然而，如果叶子页在物理分布上是顺序且紧密的，那么查询的性能就会更好。

行碎片（Row fragmentation）

• 这种碎片指的是数据行被存储为多个地方的多个片段中。即使查询只从索引中访问一行记录，行碎片也会导致性能下降。

行间碎片 （Intra-row fragmentation）

• 行间碎片是指逻辑上顺序的页，或者行在磁盘上不是顺序存储的。

剩余空间碎片（Free space fragmentation）

• 剩余空间碎片是指数据页中有大量的空余空间。这会导致服务器读取大量不需要的数据，从而造成浪费。

  • 对于MyISAM表，这三类碎片化都可能发生。但InnoDB不会出现短小的行碎片；InnoDB会移动短小的行并重写到一个片段中。

  • 可以通过执行OPTIMIZE TABLE或者导出再导入的方式来重新整理数据。

5.6 总结

在选择索引和编写利用这些索引的查询时，有如下三个原则始终需要记住：

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

2. 按顺序访问范围数据是很快的，这有两个原因。

   • 第一，顺序I/O不需要多次磁盘寻道，所以比随机I/O要快很多。

   • 第二，如果服务器能够按需要顺序读取数据，那么就不再需要额外的排序操作，并且GROUP BY查询也无须再做排序和将行按组进行聚合计算了。

3. 索引覆盖查询是很快的。如果一个索引包含了查询需要的所有列，那么存储引擎就不需要再回表查找行。