高性能MySQL第五章—索引！

这一章对于开发极其重要，讲了关于索引和索引优化的详细知识点

索引基础

数据库的索引和书本的索引是同样的道理，都是帮助我们快速地找到某个想要的数据。索引有很多类型，可以为不同的场景提供更好的性能。

B-Tree索引（后面会详细介绍）
哈希索引（后面会详细介绍）
空间数据索引（R-Tree）：MyISAM表支持空间索引，可以有效使用任意维度来组合查询。
全文索引：查找的是文本中的关键词，而不是直接笔记索引中的值
其他索引类别

其中对我们最重要得是B-Tree索引，其次是哈希索引。

B-Tree索引

存储引擎以不同得方式使用B-Tree索引，性能也各有不同，各有优劣。

建立在B-Tree树上的B+tree

B-Tree索引能够加快访问数据的速度，而且对索引列是顺序组织存储的，所以很适合查找范围数据。存储引擎不再需要进行全表扫描来获取需要的数据。而是根据根节点向下开始搜索，通过比较节点页的值和要查找的值可以找到合适的子节点。

B+树（二）

CREATE TABLE People (
    last_ name varchar( 50) not null, 
    first_ name varchar( 50) not null, 
    dob date not null, 
    gender enum(' m', 'f') not null, 
    key( last_ name, first_ name, dob) );

可以使用B-Tree索引的查询类型：

全值匹配：和索引中的所有列进行匹配。最普遍的使用，创建什么样的索引就通什么索引去匹配，对于上图来说就是匹配Cuba Allen、出生于1960-01-01的人。
匹配最左前缀：查找所有姓为Allen的人，只用了定义索引的最左边那一列数据
匹配列前缀：也可以只匹配一列的值开头部分。例如用来匹配所有以J开头的人。
匹配范围值：用于查找姓再Allen和Barrymore之间的人。使用了索引的第一列
精确匹配：精确某一列并范围匹配另一列，查找所有姓为ALlen，并且名字以K开头
只访问索引的查询：查询只需要访问索引，而不需要访问数据行。后面再讨论这种覆盖索引。

下面是B-Tree索引的限制：

如果不是按照索引的最左边开始，则无法使用索引
不能跳过索引中的列
如果查询中有某个列的范围查询，则右边所有列都无法使用索引优查找，也就是范围查询最好放在最后面。

哈希索引

哈希索引基于哈希表（散列表）实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希值。如果要了解哈希表，哈希冲突等专业术语的意思。关于散列表的信息在之前的文章《散列表和HashMap源码》中有介绍到。

MySQL中，只有Memory引擎显式支持哈希索引。它支持非唯一哈希索引，在数据库中如果多个列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中。

哈希索引也有它的限制

哈希索引只包括哈希值和指针，而不存储字段值，所以不能使用索引的值来避免读取行。
哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序
哈希索引也不支持部分索引匹配查找
哈希索引只支持等值比较。不支持任何范围查找
访问哈希索引的数据非常快，除非哈希冲突很大。而且如果哈希冲突很多的话，一些索引维护操作的代价也会很高。每删除一行，都需要遍历对应哈希值的链表中每一行，找到并删除对应的引用。

InnoDB引擎有个特殊的功能自适应哈希索引。当InnoDB注意到某些索引值被使用得非常频繁时，就会在内存中基于B—Tree索引之上再创建一个哈希索引，这是一个完全自动、内部的行为，用户无法控制或者配置，不过你可以完全关闭这功能。

实际上我们自己也可以在外面实现自己的哈希索引，无非就是在数据库中多存一列数值—该列的Hash值。然后插入数据的时候根据哈希函数（散列函数）算出哈希值存入数据库，之后查找数据的时候。也用该数据传入到哈希函数中得到哈希值，把这个值跟到where后面第一个，注意哈希值是有重复的。所以不能只根据哈希值去查找。例如完全可以使用如下语句：

1	`SELECT ID FROM url WHERE url_crc=CRC32("http://www.likeben.games") AND url="http://www.likeben.games"`

当涉及到我们索引的字符串过于复杂的时候完全可以自己这样做，插入的时候使用触发器就可以了，不过要记得对自定义哈希值的列先添加一个索引。这样的性能会非常高，因为哈希值是一个数字，用数字匹配速度很快。而且就算有哈希冲突，也只是在极少数的行中查找数据。下面是书中讲到触发器的示例代码。

#建表语句
CREATE TABLE pseudohash (
    id int unsigned NOT NULL auto_increment,
    url varchar(255) NOT NULL,
    url_crc int unsigned NOT NULL DEFAULT 0,
    PRIMARY KEY( id) );
    
#触发器语句
DELIMITER // 
CREATE TRIGGER pseudohash_crc_ins BEFORE INSERT ON pseudohash FOR EACH ROW BEGIN 
SET NEW.url_crc= crc32(NEW.url);
END;
//
CREATE TRIGGER pseudohash_ crc_ upd BEFORE UPDATE ON pseudohash FOR EACH ROW BEGIN
SET NEW. url_ crc= crc32( NEW. url);
END;
//
DELIMITER;

记住不要使用SHA1()和MD5()作为哈希函数。因为这两个函数计算出的哈希值是非常长的字符串，会浪费大量空间，比较时也更慢。设计的目标是最大程度消除冲突，这里不需要这么高的要求。

索引的优点和缺点

优点：

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

缺点：

使用索引会影响插入数据和修改数据的性能。
有时坏的索引会诱导优化器进行很差的索引查询，甚至比全表查询还要慢。

所以作者在书中推荐我们去读另一本关于索引的书，其中凭借了一个索引是否适合某个查询的“三星系统”：

索引将相关的记录放到一起则获得一星；如果索引中的数据顺序和查找中的排列顺序一致则获得二星；如果索引中的列包含了查询中需要的全部列则获得三星；

高性能的索引策略

下面几个小节将介绍如何高效使用索引

独立的列

独立的列是指索引列不能是表达式的一部分，例如下面的语句：

1	`SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5`

这样的语句无法触发索引，你完全可以改成WHERE actor_id = 5-1。这完全是习惯问题。

前缀索引和索引选择性

有时候需要索引很长的字符串，这会让索引变得大且慢。一个策略是前面提到过得自定义哈希索引。另一个策略是使用前缀索引。具体是这样操作的：索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性（不重复的索引值占数据表总记录的比重）。

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

书上的数据集用不了，所以我自己找了个数据集来使用。数据集下载

选择里面的最大的city1.sql来执行，执行以后会出现china_area的表。现在不要在意数据集的数据准确和真实性

SELECT COUNT(*) AS cnt,LEFT(`merger_name`,7) AS pref FROM china_area WHERE merger_name IS NOT NULL GROUP BY pref ORDER BY cnt DESC

SELECT COUNT(DISTINCT `merger_name`)/COUNT(*) AS sel,
			COUNT(DISTINCT LEFT(`merger_name`,3))/COUNT(*) AS sel3,
			COUNT(DISTINCT LEFT(`merger_name`,4))/COUNT(*) AS sel4,
			COUNT(DISTINCT LEFT(`merger_name`,5))/COUNT(*) AS sel5,
			COUNT(DISTINCT LEFT(`merger_name`,6))/COUNT(*) AS sel6,
			COUNT(DISTINCT LEFT(`merger_name`,7))/COUNT(*) AS sel7,
			COUNT(DISTINCT LEFT(`merger_name`,8))/COUNT(*) AS sel8
FROM china_area WHERE merger_name IS NOT NULL

ALTER TABLE china_area ADD KEY(merger_name(9))

首先计算你需要索引列的基数，这里假设使用china_area表中的merger_name字段。你甚至可以执行第二个SQL，来查看每个截取数量下的比例。

前缀索引

上面的结果显示，当截取到8个字符的时候，提升很大。所以暂时先选择截取8个字符的吧。

然后你就可以运行上述SQL中第一条。更换数据来测试看看会有多少数据

前缀索引1

为什么要这样做？

因为只看平均选择性不不够的，也有例外的情况，需要考虑最坏的情况。你可以看到里面最大的145和最小的差距好几倍。这说明查询一旦落到最上面的索引，那么数据还是有很多。所以严格意义来说，这截取的还不够。不过嘛，这只是演示，具体怎么选择还是看自己咯。

最后一步就是执行最后一行SQL，假定截取为9是最好的选择。那么就可以直接插入索引了。

前缀索引是一个使索引更小、更快的有效方法，但它也有缺点：MySQL无法使用前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描

有时候也可以用后缀索引，但是MySQL原生并不支持，其实在这个表中使用后缀索引是挺好的。但是我们可以通过字符串反转后存储来实现。

多列索引

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

在多个列上建立独立的单独索引大部分情况下并不能提高MySQL的查询性能。5.0和更新版本引入了一种叫索引合并的策略，一定程度上可以缓解这个问题。

在古老的版本中，假如两个列都使用了单独的索引，然后查询语句where后是列1 OR 列2.那么都会使用全表查询。除非改成用UNION来连接两个查询的结果。5.0以后的版本会同时使用这两个单列索引进行查询，并将结果进行合并。这种算法有三种情况OR条件的联合，AND条件的相交，组合前两种情况的联合及相交。

不过索引合并策略是一种优化的结果，但实际上更多时候说明了表上的索引建得很糟糕。在EXPLAIN中的Extrea可以看到是否引用了其他单独的索引。如果看到有的话，应该好好检查一下查询和表的结构。

当出现服务器对多个索引做相交操作时（通常有多个AND条件），意味着需要一个包含这几个查询列的多列索引
当对多个索引做联合操作时（通常有多个OR条件），当其中有些索引选择性不高的时候，需要耗费大量CPU和内存资源在算法的缓存、排序和合并上。
优化器不会把这些计算到查询成本中，它只关系随机页面读取。这会导致成本被低估。

而且用OR的时候，可能根本不需要使用索引，就算索引了有些数据还是会丢掉，那么该执行计划还不如直接走全表扫描呢

选择合适的索引列顺序

这一节只使用于B-tree索引。B-tree索引是从左向右匹配的，有一个经验法则：将选择性最高的列放到索引的最前列。对于这个原则，如果不考虑排序和分组的时候，通常是很好的。但是当左边的索引值比较大时，就会造成虽然使用了索引但还是很慢的样子。

聚簇索引

聚簇索引并不是一个单独的索引类型，而是一种数据存储的方式。InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

聚簇索引

上图的解释是这样的：叶子页包含了行的全部数据，但是节点页只包含了索引列。也就是下面的名字和日期，上面的数字表示索引列，在这里显示得是整数。InnoDB将通过主键聚合数据，也就是上面的数字。

聚集的数据有一些优点：

可以把相关数据保存在一起。根据用户id聚合数据，只需要从磁盘读取少量的数据页就能获得数据。
数据访问更快。因为保存在一个B-Tree中，因此聚簇索引获取数据比非聚簇索引快一些。
使用覆盖索引扫描的查询可以直接使用页节点中的主键值

缺点：

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

那么聚簇索引和非聚簇索引的存储方式有什么不同呢？

上图！

聚簇索引对比

总的来说，非聚簇索引中的主键其实可以简单当作唯一索引，反正主键索引和二级索引都是直接索引到数据。对于聚簇索引来说主键索引是很特别的，二级索引会保存主键值。使用二级索引的时候往往先索引到对应的主键，然后通过主键去查找数据。

MyISAM的主键分布：

MyISAM的主键分布

MyISAM的二级索引分布

可以看到，主键索引和二级索引好像差距不是很大？都是去索引行号。

没有差距就没有伤害，看看InnoDB的聚簇索引

InnoDB的主键分布

InnoDB的二级索引分布

聚簇索引就是“表”，因为存储了所有数据。聚簇索引的每一个叶子节点都包含了主键值、事务ID、用于事务和MVCC的回滚指针以及所有的剩余列。如果主键是一个列的前缀索引，InnoDB也会包含完整的主键列和其他剩下的其他列。而二级索引是索引到主键列。

在完全理解之后，从性能角度来说，在InnoDB表中应该按主键顺序插入行

最简单的方法是使用AUTO_INCREMENT自增的整数主键，这样可以保证数据行是按顺序写入，对于根据主键做关联操作的性能也会更好。

聚簇索引顺序插入

当进行顺序插入的时候，InnoDB把每一条记录都存储在上一条的后面。当达到页的最大填充因子时（InnoDB默认的最大填充因子是页的15/16，留出部分空间用于以后修改），下一条记录就会写入新的页中。这样的插入时很和谐，很顺畅的。

当使用UUID作为主键插入时，随机字符串可能造成很大的问题。

聚簇索引字符串插入

因为不确定下一个UUID是否一定把上一个更大，所以没办法简单地插入到最后面。而是要为这条数据寻找合适的位置。而且万一那一页已经满了，还要分裂页。总的来说缺点有以下几个：

写入的目标页可能已经插入到磁盘上了，并已经从缓存中移除了，这就导致存储引擎必须要先进行查找磁盘上的内容。这将导致大量的随机磁盘I/O
因为写入是乱序的，InnoDB不得不频繁地做页分裂操作，页分裂会导致移动大量数据，一次插入最少需要修改三个页而不是一个页。
由于频繁的页分裂，页会变得稀疏并被不规则地填充，会有很多碎片，影响查询性能。

所以把这些随机值填充以后，最好做一次OPTIMIZE TABLE来重建表并优化页的填充。

不过虽然在性能上不推荐使用这样的方式，但是在业务层面使用自增的主键会有一些问题：

导入旧数据时，可能会ID重复或ID变化，导致导入失败。
分布式架构，分库分表，无法实现全局ID唯一。
增长从1开始，无法统一为统一的格式。
超高并发工作负载下，按顺序插入可能会造成明显的争用。主键的上一条会成为热点。
AUTO_INCREMENT锁机制会变成热点

覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们称之为“覆盖索引”。简单来说，就是要查询的字段刚好就是你的索引。

覆盖索引的好处：

索引条目通常远小于数据行大小，只需要读取索引就会极大减少数据访问量。
索引是按照列值顺序储存的，所以随机I/O会少一些，都是顺序读取。
一些存储引擎如MyISAM在内存中只缓存索引，数据则依赖操作系统来缓存。
InnoDB的聚簇索引，由于只需要索引值，那么就不需要再次根据主键去查询数据了。

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

1	`SELECT * FROM products WHERE actor = 'SEAN CARREY' AND title LIKE '%APO%';`

这个查询是不可能用索引覆盖的，SELECT * 不可以被索引覆盖，使用LIKE会导致索引失效。应该改为以下形式：

SELECT
	* 
FROM
	products
	JOIN ( SELECT prod_id FROM products WHERE actor = 'SEAN CARREY' AND title LIKE '%APO%' ) AS t1 ON (
	t1.prod_id = products.prod_id)

我们把这种方式叫做延迟关联，延迟了对列的访问。在查询的第一阶段可以使用覆盖索引，然后根据这个id去匹配外面的值，虽然无法使用索引覆盖整个查询，但总比什么都不用来的好。不过这样的优化仍然来自于WHERE匹配的行数。假如匹配actor = 'SEAN CARREY'的数据已经很少的时候，子查询带来的成本反而比从表中直接提取完整行更高。

使用索引扫描来做排序

如果EXPLAIN出来的type列的值为“index”，则说明MySQL使用了索引扫描来做排序。只有当索引的列顺序和ORDER BY字句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，才能使用索引来对结果做排序。有一种情况下ORDERY BY子句可以不满足这个要求，就是当前面的索引列指定为常量的时候，就是WHERE 索引列1 = 固定值 ORDER BY 索引列2,索引列3.这样的情况下就可以。

压缩（前缀压缩）索引

MyISAM使用前缀压缩来减少索引的大小，从而让更多的索引可以放入内存中。默认只压缩字符串，但通过参数设置也可以对整数做压缩。例如索引第一个值时perform，第二个时performance，那么第二个存储就是类似于7,ance这样的形式。

那么代价是什么呢？某些操作可能很慢，无法在索引块中使用二分查找而只能从头开始扫描。正序速度很好，但是倒序速度就会很慢！可以在 CREATE TABLE 语句中指定 PACK_ KEYS 参数来控制索引压缩的方式。

冗余和重复索引

MySQL允许在相同列上创建多个索引。多列索引可以从左向右匹配。

如果创建了索引（A，B），再创建索引（A）就是冗余。

如果创建了索引（A，B），再创建索引（B）就不是冗余。

如果创建了索引（A，B），再创建索引（B，A）也不是冗余。

不过如果扩展已有的索引会导致其变得太大，从而影响其他使用该索引得查询的性能，那么就应该冗余。比如整数列上的索引要加一个很长VARCHAR列的索引，那性能可能会急剧下降。

还有种情况，当创建了索引（A）的时候，如果像WHERE A=5 ORDER BY ID 这样的查询就很有用，但是假如改成索引(A,B)以后那么就会变成（A，B，ID），上面的查询就无法使用索引做排序了。

不过表中索引越多，插入速度会越慢。所以当有很多索引冗余或者使用率不高，就要考虑是否清楚它们。

索引和锁

索引可以让查询锁定更少的行。如果你的查询从不访问那些不需要的行，那么就会锁定更少的行。效率就会很高，InnoDB只有再访问行的时候才对其加锁。

1	`SELECT actor_ id FROM sakila. actor WHERE actor_ id < 5 AND actor_ id <> 1 FOR UPDATE;`

这个查询会返回2-4行之间的数据，实际上获得了1-4行的排他锁。但假如使用索引不访问第一行，那么第1行就不会被加锁。

索引案例学习

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

支持多种过滤条件

首先考虑哪些列在WHERE中出现的多？

国家列和性别列选择性通常不高，但是很多查询中都会用到。所以考虑创建不同组合索引的时候将（sex,country)列作为前缀。那么当作为前缀以后，我们查询没有这个sex列怎么办呢？

一个诀窍是：如果某个查询不限制性别，那么通过在查询条件中新增ADD SEX IN ('m','f')来让MySQL选择该索引。不过，如果列有太多不同的值，就会让IN()列表太长，这样就不行了。

但是也不能滥用这个技巧，因为每增加一个IN()条件，优化器需要做的组合都将以指数形式上升

1	`WHERE eye_color IN(' brown',' blue',' hazel') AND hair_color IN(' black',' red',' blonde',' brown') AND sex IN(' M',' F')`

优化器会转换成4*3*2=24种组合。如果组合很大的时候就会有很大的问题。

避免多个范围条件

什么是范围条件：EXPLAIN的输出种通过使用range来描述这是范围查询，但是当你使用><,>=<=和使用IN()的时候都会显示range。但实际上使用IN()是不会中断索引的。

假设我们有一个last_online并希望通过下面的查询显示过去几周上线过的用户：

WHERE eye_color IN(' brown',' blue','hazel')
AND hair_color IN(' black',' red',' blonde',' brown')
AND sex IN(' M',' F')
AND last_online > DATE_SUB(NOW(), INTERVAL 7 DAY)
AND age BETWEEN 18 AND 25

那么我们要如何使用索引优化这个查询？

答案是：没有一个直接的办法能够解决这个问题。不过我们能把其中一个范围查询转换为一个简单的等值比较。我们可以用定时任务来维护一个字段active列，将过去连续7天未登录的用户的值设置为0.

然后使用索引(active,sex,country,age)来索引。active并不是完全精确，但也足够了。因为使用多个范围查询就无法使用索引了。当然假如以后优化器支持><,>=<=的话就没必要这么麻烦了。

优化排序

看以下的SQL

1	`SELECT<cols> FROM profiles WHERE sex='M' ORDER BY rating LIMIT 10;`

可以创建(sex,rating)索引用于上面的查询：

1	`SELECT<cols> FROM profiles WHERE sex='M' ORDER BY rating LIMIT 1000000 10;`

但是无论如何创建索引，面对很大的LIMIT都是个问题。

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

SELECT
	<cols>
FROM
	PROFILES INNER JOIN ( SELECT < PRIMARY KEY cols > FROM PROFILES WHERE x.sex = ' M' ORDER BY rating LIMIT 100000, 10 ) AS x USING (< PRIMARY KEY cols >);

维护索引和表

即使使用了正确的类型创建了表加上了合适的索引，工作也没有结束，还需要维护表和索引

找到并修复损坏的表

如果遇到了古怪的问题，可以尝试运行CHECK TABLE

然后使用REPAIR TABLE命令来修复。如果存储引擎不支持，也可通过一个不做任何操作的ALTER操作来重建表，例如修改表的存储引擎为当前的引擎。

1	`ALTER TABLE innodb_tal ENGINE=INNODB;`

如果InnoDB引擎的表出现了损坏，那么一定是发生了严重的错误，需要立刻调查一下原因。一般要么是数据库硬件问题，要么是由于数据库管理员的错误，例如在MySQL外部操作了数据文件。常见的错误通常是由于尝试使用rsync备份InnoDB导致的。不存在什么查询能让InnoDB表损坏。

更新索引统计信息

MySQL的查询优化器会通过两个API来了解存储引擎的索引值的分布信息

records_in_range()：通过向存储引擎传入两个边界值获取这个范围大概有多少条
info():该接口返回各种类型的数据，包括索引的基数（每个键值有多少记录）

如果返回的信息是不准确的数据，或者执行计划太复杂无法准确地获取各个阶段匹配的行数，优化器就会使用索引统计信息来估算扫码行数。如果表没有统计信息，或者统计信息不准确，优化器可能做出错误的决定。可以通过ANALYZE TABLE来重新生成统计信息解决这个问题。

ANALYZE TABLE的运行成本：

Memory引擎根本不存储索引统计信息
MyISAM将索引统计信息存储在磁盘中，需要进行一次全索引扫描来计算索引基数。整个过程需要锁表。
直到MySQL5.5，InnoDB也不在磁盘存储索引统计信息，而是通过随机的索引访问进行评估并将其存储在内存中。

可以使用SHOW INDEX FROM 表来查看索引的基数

减少索引和数据的碎片

B-Tree索引可能会碎片化，这会降低查询的效率。表的数据存储也可能碎片化。数据存储的碎片化比索引更加复杂。

行碎片：数据行被存储为多个地方的多个片段中。即使查询只从索引中访问一行记录，行碎片也会导致性能下降。
行间碎片：逻辑上顺序的页，或者行在磁盘上不是顺序存储的。行间碎片对全表扫描和聚簇索引扫描之类的操作有很大的影响，因为这些操作本来是顺序读取的
剩余空间碎片：数据页中有大量的空余空间。这会导致服务器读取大量不需要的数据，从而造成浪费。

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

可以通过执行`OPTIMIZE TABLE 或导出再导入的方式来重新整理数据。对于那些不支持的，可以通过一个不做任何操作的ALTER TABLE来重建表。

总结

索引是一个非常复杂的话题。选择索引和编写利用这些索引的查询时，有如下三个原则：

单行访问是很慢的。特别是在机械硬盘中，如果服务器从存储中读取一个数据块只是为了获取其中一行，那么就浪费了很多工作。最好读取的块能包含尽可能多所需要的行。使用索引可以创建位置用以提高效率
按顺序访问范围数据是很快的，第一顺序I/O不需要多次磁盘寻道。第二服务器能够按需要顺序读取顺序，那么就不再需要额外的排序操作了，并且GROUP BY查询也无须做排序就能进行聚合计算了。
索引覆盖查询时很快的。如果一个索引包含了查询需要的所有列，那么存储引擎就不需要再回表查找了。

如何判断一个系统创建的索引时合理的呢？

一般来说，建议按照响应实际来对查询进行分析。找出那些消耗最长时间的查询或者那些给服务器带来最大压力的查询。

读书笔记

数据库 MySQL

本博客所有文章除特别声明外，均采用 CC BY-SA 4.0 协议，转载请注明出处！

高性能MySQL第六章上一篇

全心爱你-手机游戏下一篇