Schema的优化和索引 - 高性能的索引策略

前言

　　创建正确的索引和正确的使用它们，是获得好的查询性能的关键所在。我们已经介绍了不同种类的索引并且探究了它们的优缺点。现在让我们来看看怎样深入的使用强大的索引。

　　有许多高效的选择和使用索引的方法，因为有很多特殊情况的优化以及一些专门的行为。决定什么时候使用并且评估你的选择对性能所造成的潜在影响，这些也是我们将会学的技能。接下来的部门会教你怎样高效的使用索引，但是千万不要忘了进行基准测试。

隔离列

　　如果你在一个查询中不隔离已经索引的列，一般来说MySQL就不会使用这个列上的索引，除非在查询中隔离这个列。“隔离”列的意思就是，这个列不能使一个表达式的一部分或者被放在一个函数的内部。

　　举个例子，在这个查询中，你不能使用在actor_id上的索引。

　　mysql> SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;

　　我们很简单的就能知道上面的actor_id+1=5和actor_id=4是相同的，但是MySQL却不能处理这个actor_id+1=5这个方程式。这取决于你所做的。你应该简化你的WHERE条件。因此应该把索引列独立在比较符号的一端。

　　下面的例子是我们另一个常犯的错误。

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

　　这个查询将会查找最近10天的所有的行，但是这个语句不会使用索引，因为使用了TO_DAYS函数。下面是比较好的方式

　　mysql> SELECT ... WHERE date_col >= DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);

　　这个查询使用索引不会有什么问题，但是你可以用另一种方法提升性能。CURRENT_DATE的引用会导致缓存失效。你可以用具体的日期来替换它来解决这个问题。

　　mysql> SELECT ... WHERE date_col >= DATE_SUB('2008-01-17', INTERVAL 10 DAY);

　　在以后会详细说到查询的缓存。

前缀索引和索引的选择性

　　有的时候你可能需要索引字符数非常多的列。这会让你的索引非常大并且很慢。一个策略就是模拟hash索引。前一部分已经说过了。但是有的时候这种方法也不是很好。将要怎么做。

　　一般可以用索引前一部分的一些字符的方法来节省空间并且可以获得好的性能。这样可以使你的索引使用更少的空间，但是这样会降低选择性。索引的选择性（Index selectivity）是索引值的数量和表中行的数量（#T）的一个比率。范围是1/#T到1.索引的选择性越高越好，因为这样当匹配的时候，MySQL就会过滤掉更多的行。一个唯一索引的选择性1，这是最好的了。

　　一个前缀的索引对于好的性能来说，它的选择性已经足够了。如果你对BLOB和TEXT列进行索引，或者非常长的VARCHAR列，你必须定义前缀索引，因为MySQL不允许索引它们的全部长度。

　　技巧就是选择一个列的前缀，并且这个前缀的长度能有个好的选择性，但是又可能节约更多的空间。索引前缀所带来的索引效果应该尽可能的接近索引全部长度所带来的效果。

　　要知道一个好的前缀的长度，找到最常见的值并且把它们和最常见的前缀进行比较。看下如下例子

CREATE TABLE sakila.city_demo(city VARCHAR(50) NOT NULL);
INSERT INTO sakila.city_demo(city) SELECT city FROM sakila.city;
-- Repeat the next statement five times:
INSERT INTO sakila.city_demo(city) SELECT city FROM sakila.city_demo;
-- Now randomize the distribution (inefficiently but conveniently):
UPDATE sakila.city_demo
　　 SET city = (SELECT city FROM 　sakila.city ORDER BY RAND( ) LIMIT 1);

　　现在我们有一个示例数据集。结果并不是真是分布的，并且我们使用了RAND()，因此结果是变化的，但是对于此练习并不会有什么影响。首先我们找到出现频率最高的cities.

聚簇索引(Clustered Indexes)

　　聚簇索引并不是一个独立的索引类型。确切的说它们是存储数据的一个途径。在不同实现之间，还是有一些细节上的变化，但是InnoDB的聚簇索引实际是在相同的结构中把B-TREE索引和这些行一并的存储。

　　当一个表中有聚簇索引，它的行实际存储在索引的叶子的页（leaf pages）。“clustered”所指的意思是行的相邻键值彼此存储的非常近。你可能每张表只会有一个聚簇索引，因为你不能同时的在两个地方存储这些行。

　　因为存储引擎负责实现索引，因此并不是所有的存储引擎都支持聚簇索引。目前来说，solidDB和InnoDB支持。这部分我们重点放在InnoDB上，但是对于那些已经实现或者将要实现聚簇索引的引擎来说，在概念上至少有部分的正确。

　　下图展示了记录是怎样分布在聚簇索引上的。注意的是叶子的页（leaf pages）包含了整个行，但是节点的页仅仅包含了索引的列。这个例子中，索引列包含了整型值。

　　一些数据库服务器使你可以选择哪个索引可以聚簇，但是MySQL目前的存储引擎是不能那么做的。InnoDB通过主键来集中数据的。也就是说上图的索引列就是主键列。

　　如果你没有定义主键，InnoDB会选择一个唯一非空的索引来替代主键索引。如果没有这么个索引，InnoDB就会定义一个隐藏的主键。InnoDB仅仅在一个页中聚集数据。页所伴随的临近的键值可能彼此相互远离。

　　一个聚簇主键索引对性能有所帮助，但是也可能会导致严重的性能问题。因此，你应该仔细的思考聚簇，尤其是当你改变一个表的存储引擎从InnoDB转为其他的引擎。

覆盖索引(Covering Indexes)

　　索引是高效找到行的一个方法，但是MySQL也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了（或覆盖了）满足查询结果的数据就叫做覆盖索引（covering indexex）

　　覆盖索引是非常强大的工具并且可以大幅度提升性能。考虑下仅仅读取索引的好处：

　　索引的实体往往小于整个行的大小。如果MySQL仅仅读取索引，意味着访问的数据就非常少了。这对于缓存的工作非常有用，这样相应的时间基本都是来自复制数据而已。对于IO限制也非常有用，因为索引要比数据更小并且更容易的写入内存中。（这对于MyISAM尤其有效，它可以对索引进行压缩，这样索引就变得更小了）。

　　索引是根据索引值的来排序的，因此IO限制范围的访问相对比从随机硬盘位置所需的IO是较少的。对于一些存储引擎，比如MyISAM，你甚至可以用OPTIMIZE这个表来获取全部的排序索引。这样可使简单的范围查询使用完全连续的索引的访问。

　　大部分存储引擎缓存索引要好于数据。一些存储引擎，比如MyISAM只缓存索引。因为操作系统缓存了MyISAM的数据，访问数据需要一个系统的调用。这样会导致非常严重的性能问题。尤其对于缓存来说，系统的调用是数据访问消耗最大的一部分。

　　覆盖索引对与InnoDB表有些特殊的效用。因为InnoDB是聚簇索引。InnoDB的次要索引在它们叶子节点保存了行的主键。因此，次要索引的覆盖可以避免在主键上另一个索引的查找。

　　在这些场景中，从索引中满足一个查询消耗要比查询行要低很多。

　　覆盖索引也并不适用于任意的索引类型，索引必须存储列的值。Hash, spatial, 和full-text索引不存储值，因此MySQL只能使用B-TREE。并且不同的存储引擎实现覆盖索引都是不同的。并不是所有的存储引擎都支持它们。

使用索引扫描来进行排序

　　MySQL有两种方法生成有序的结果：使用文件排序或者按顺序的扫描索引。你可以使用EXPLAIN来查看type列是否是index来得知MySQL是否计划的扫描索引。

　　扫描索引本身是非常快速的，因为它只是简单的需要从一个索引实体移动到下一个。然而，如果MySQL没有使用索引覆盖这个查询，它就会查找在索引中发现的每一行。这是个随机IO的过程，因此从索引顺序中读取数据比连续表的扫描要慢很多。

　　MySQL可以使用相同的索引来排序和查找行。如果可能，同时满足这两个任务是再好不过的事情了。

　　当索引的顺序和order by条件顺序相同并且所有的列都在同一个方向（升序或降序）时，通过索引排序才能好用。如果查询关联了多张表，所有order by 条件后的列必须是第一张表的情况下，才能使用索引排序。ORDER BY条件也和查询有相同的限制：它需要形成一个左端前缀的索引。在其他的情况下MySQL使用文件排序。

　　有一种情况就是，ORDER BY后的条件并不是最左端的索引前缀，如果WHERE条件或者一个JOIN条件也赋予了这些缺失索引的值，那么依然可以使用索引来排序。

　　比如，rental表有一个索引（rental_date, inventory_id, customer_id）。、

CREATE TABLE rental (
　　 ...
　　 PRIMARY KEY (rental_id),
　　 UNIQUE KEY rental_date (rental_date,inventory_id,customer_id),
　　 KEY idx_fk_inventory_id (inventory_id),
　　 KEY idx_fk_customer_id (customer_id),
　　 KEY idx_fk_staff_id (staff_id),
　　 ...
);

　　MySQL会使用rental_date索引去排序下列查询

mysql> EXPLAIN SELECT rental_id, staff_id FROM sakila.rental
　　 　-> WHERE rental_date = '2005-05-25'
　　 　-> ORDER BY inventory_id, customer_idG
*************************** 1. row ***************************
　　 　 　 　 type: ref
possible_keys: rental_date
　　 　 　 　 　key: rental_date
　　 　 　 　 rows: 1
　　 　 　 　Extra: Using where

　　这是可以使用索引排序的，即使ORDER BY条件并不是最左边的索引前缀。那是因为我们已经在where条件后指定了第一列索引的值。

　　下面也是可以使用索引排序的一些查询。下面的一个可以使用索引，是因为查询已经使用了第一个索引列并且用order by指定了第二个索引列。一起来看，就是一个最左边的索引前缀。

　　... WHERE rental_date = '2005-05-25' ORDER BY inventory_id DESC;

　　下面的语句也可以，因为order by后的条件也是最左边的索引前缀。

　　... WHERE rental_date > '2005-05-25' ORDER BY rental_date, inventory_id;

　　下面都是不能使用索引排序的语句：

　　这个查询使用了排序的不同方向，但是这个索引列都是升序。

　　... WHERE rental_date = '2005-05-25' ORDER BY inventory_id DESC, customer_id ASC;

　　下面的语句，ORDER BY 后面的列并不是索引。

　　... WHERE rental_date = '2005-05-25' ORDER BY inventory_id, staff_id;

　　下面的语句，并不是最左端的索引前缀。

　　... WHERE rental_date = '2005-05-25' ORDER BY customer_id;

　　这个语句对第一个字段使用了范围查询，所以MySQL就不会使用剩下的索引。

　　... WHERE rental_date > '2005-05-25' ORDER BY inventory_id, customer_id;

　　对于inventory_id列有很多相同的条件。对于这种排序，和查询一个范围一样。

　　... WHERE rental_date = '2005-05-25' AND inventory_id IN(1,2) ORDER BY customer_id;

　　下面的语句，从表面上看可以使用索引去排序，但是实际上却不能，因为语句优化器把film_actor表放到了第二的位置。以后再讲这是为什么。

mysql> EXPLAIN SELECT actor_id, title FROM sakila.film_actor
　　 　-> INNER JOIN sakila.film USING(film_id) ORDER BY actor_idG
+------------+----------------------------------------------+
| table 　 　 　| Extra 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　|
+------------+----------------------------------------------+
| film 　 　 　 | Using index; Using temporary; Using filesort |
| film_actor | Using index 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　|
+------------+----------------------------------------------+

　　使用索引来排序其中最重要就是一个查询有ORDER BY和LIMIT。这个以后会详细说到。

压缩索引（Packed Indexes）

　　MyISAM使用前缀压缩来降低索引的大小，这样就可以把更多的索引放到内存中并且在某些情况下可以大幅度提升性能。默认的是压缩字符串，但是你可以指定它来压缩整型的值。

　　MyISAM通过存储块的第一个值来压缩每个索引块，之后通过记录相同前缀的字节数在加上不同后缀实际的数据的方式来存储在块中的每个附加的值。举个例子，如果第一个值是“perform”并且第二个值是“performance”，第二个值就会存储为"7,ance"。MyISAM也会前缀压缩相邻的行指针。

　　压缩块使用的更少的空间，但是它们会使主要操作变慢。因为每个值的压缩前缀都依赖于它之前的值，MyISAM不能在块中使用二进制搜索来找到需要的项并且必须从头来扫描整个块。按照顺序的扫描可能会表现不错，但是反向的扫描-比如ORDER BY DESC-就不会很好。要在块的中间找到一个单独的行的操作，需要扫描，平均的，大概一半块。

　　我们的基准测试显示对于CPU的限制（CPU-BOUND），在MyISAM表中压缩的键能使索引查找变慢，因为扫描需要随机查找。反向扫描压缩的键甚至更慢。要在CPU资源，内存资源的其中之一和硬盘资源之间有个权衡。

　　压缩的索引是原来的十分之一，并且如果你有IO限制(IO-BOUND)的工作量，它们可以降低主要操作的消耗。

冗余和重复的索引

　　MySQL可以在一个列上创建多个索引；这么做并不会提醒和防止发生错误。MySQL必须单独维护每一个重复的索引，当语句优化器优化语句的时候，它会参考这些索引。这样会对性能造成影响。

　　重复的索引是那些具有相同类型，在同顺序下的相同一组索引。你应该避免这样来创建它们并且如果发现它们要尽早的删除。

　　有的时候你可能在不知道的情况下就创建了重复索引。比如，如下的代码

CREATE TABLE test (
　　 ID INT NOT NULL PRIMARY KEY,
　　 UNIQUE(ID),
　　 INDEX(ID)
);

　　一个没有经验的用户可能想到要使列的作为主键，要加上一个UNIQUE约束，并且添加一个索引。事实上，MySQL实现了UNIQUE约束和PRIMARY KEY索引。因此实际上，在一个列上已经创建了三个索引了。没有必要这样做，除非你要在同样的列上创建不同类型的索引来满足不同类型的查询。

　　冗余的索引和重复的索引有所不同。如果在（A,B）上有个索引，另一个在（A）上的索引就是冗余的。因为它是第一个索引的前缀。(A,B)上的索引也可以当作（A）上的索引来单独使用。然而，一个（B,A）上的索引就不冗余，（B）上的索引也是不冗余的，因为它并不是（A,B）索引的前缀。进一步的说，不同的索引类型对于B-Tree索引都不是冗余的，不管它们所覆盖的列是怎样。

　　当人们添加索引的时候，经常会出现冗余索引。比如，有的人在（A,B）添加了索引，用来取代扩展现有（A）的索引。

　　大部分的情况，你都不会想要冗余索引，并且要避免它们，你应该扩展现有索引而不是添加一个新的。也有的时候可能因为性能的原因你需要使用冗余索引。使用冗余索引的主要原因就是当扩展现有的索引，这个冗余索引会使它变得更大一些。

　　举个例子，如果你有一个在整型列上的索引并且你扩展为这个整型列和一个VARCHAR列上的索引，它会变慢。如果你的查询覆盖了这个索引，或者如果表是MyISAM的以及需要范围扫描（因为MyISAM使用前缀压缩），变慢的情况就会发生。

　　考虑下userinfo表。在前面的教程中所提到过。这个表有1，000，000行并且每个state_id有20，000记录。有个索引在state_id上。这对下列的查询很有用。Q1语句如下：

　　mysql> SELECT count(*) FROM userinfo WHERE state_id=5;

　　一个简单的基准测试显示执行率是每秒115条（QPS）。我们在看看相关查询Q2。

　　mysql> SELECT state_id, city, address FROM userinfo WHERE state_id=5;

　　对于这个查询，结果少于10QPS.简单的性能提高方法就是扩展索引（state_id, city, address）。因此索引覆盖了查询。

mysql> ALTER TABLE userinfo DROP KEY state_id,
　　 　-> ADD KEY state_id_2 (state_id, city, address);

　　在修改索引之后，Q2变快了，Q1却慢了。如果我们想让两个查询都快的话，我们应该留下这两个索引，即使那个单独的索引是冗余的。下面的表给出了在MyISAM和INNODB中这两个索引的表现。注意在使用state_id2索引的时候，InnoDB的Q1并没有降低多少性能，因为InnoDB没有键压缩。

　　 　 　 　 　 　 　 　 　 　 　 　 　 　state_id only 　 　 　 　 　state_id_2 only 　 　 　 　 　 　Both state_id and　state_id_2
MyISAM, Q1 　 　 　 　 114.96 　 　 　 　 　 　 　 　 　 　 　 　 　25.40 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　112.19
MyISAM, Q2 　 　 　 　 　9.97 　 　 　 　 　 　 　 　 　 　 　 　 　 　 16.34 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 16.37
InnoDB, Q1 　 　 　 　 　 108.55 　 　 　 　 　 　 　 　 　 　 　 　 100.33 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　107.97
InnoDB, Q2 　 　 　 　 　12.12 　 　 　 　 　 　 　 　 　 　 　 　 　 　 28.04 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 28.06

　　同时使用两个索引的缺点是维护消耗大。下面的数据是插入1百万行性能。

　　 　 　 　 　 　 　 　 　 　 　 　 　state_id only 　 　 　 　 Both state_id and state_id_2
InnoDB, enough memory for both　indexes 　 　 　 　　80 seconds 　 　 　 　 　 　 　 　 　 　136 seconds
MyISAM, enough memory for only　one index 　 　 　72 seconds 　 　 　 　 　 　 　 　 　 　470 seconds

　　你也看到了，插入数据的性能很差。一般来说如下说法是正确的：添加一个索引会对INSERT,UPDATE,DELETE操作性能影响很大，尤其是新的索引达到了内存的限制。

索引和锁

　　InnoDB中，索引所扮演的角色是非常重要的。因为它们可以能让语句锁定更少的行。这是个要考虑的事情，因为在MySQL5.0 InnoDB中一个事物提前之前，是不会释放锁的。

　　如果查询语句不会检索它们不需要的行。它们将锁定更少的行。并且对于性能有提高，原因有二：首先，即使InnoDB行锁是非常有效率的并且使用更少的内存，但是行锁也会消耗一定的资源。其次，锁定很多的行就提高了锁的竞争并且降低了并发。

　　仅当InnoDB访问行的时候，才对它们加锁，并且一个索引可以降低InnoDB所要访问的行，因此也会降低锁。然而，这种情况只适用于在存储引擎级别中，InnoDB过滤了不期望的行。如果索引不允许InnoDB那么做，MySQL服务器就会在InnoDB取到这些值并且返回服务层之后，应用WHERE条件了。这种情况下，避免行的锁定就太晚了：InnoDB已经锁定了它们，并且服务器是不可能解锁的。

　　为了更好的理解我们看个例子，我们还是用以前的数据库Sakila

mysql> SET AUTOCOMMIT=0;
mysql> BEGIN;
mysql> SELECT actor_id FROM sakila.actor WHERE actor_id < 5
　　 　-> AND actor_id <> 1 FOR UPDATE;
+----------+
| actor_id |
+----------+
| 　 　 　 　2 |
| 　 　 　 　3 |
| 　 　 　 　4 |
+----------+

　　这个查询返回了2到4行，但是实际上它已经获得了1到4行的独占锁。InnoDB锁定了第一行，因为这个语句是个索引范围读取：

mysql> EXPLAIN SELECT actor_id FROM sakila.actor
　　 　-> WHERE actor_id < 5 AND actor_id <> 1 FOR UPDATE;
+----+-------------+-------+-------+---------+--------------------------+
| id | select_type | table | type 　| key 　 　 | Extra 　 　 　 　 　 　 　 　 　 　|
+----+-------------+-------+-------+---------+--------------------------+
| 　1 | SIMPLE 　 　 　| actor | range | PRIMARY | Using where; Using index |
+----+-------------+-------+-------+---------+--------------------------+

　　换句话说，这个低级别的存储引擎操作是“从索引开始并且获取所有的行直到actor_id<5为false”。服务器是不会告诉InnoDB,WHERE　actor_id　<>1的条件。来看下EXTRA列的Using where。这就说明了在存储引擎返回行之后，MySQL服务器用WHERE进行了过滤。

　　下面的语句证明了第一行已经被锁了，即使它不会出现在第一个查询结果之中。丢掉第一个连接，开始第二个连接执行下列语句。

mysql> SET AUTOCOMMIT=0;
mysql> BEGIN;
mysql> SELECT actor_id FROM sakila.actor WHERE actor_id = 1 FOR UPDATE;

　　这个查询会被挂起。等待第一个事物是否第一行的锁。这个行为是为了保证基于语句级的复制是正确的。（在复制的一节会说道。）

　　就像这个例子所显示的，即使使用了索引，InnoDB还回会锁定它并不是真正需要的行。当不使用索引去查找和锁定行，这样的问题会更严重：如果这个语句没有索引，不管需不需要，MySQL都会扫描整张表并且锁定每个行。

　　下面谈一下InnoDB，索引和锁的一些鲜为人知的细节：InnoDB会把共享锁放置在次要索引上，但是独占锁需要访问主键。这就降低了使用覆盖索引的可能性并且会导致SELECT FOR UPDATE 慢于LOCK IN SHARE MODE 或者没有锁的查询。

学习一个索引示例

　　用例子来理解索引的概念是再简单不过的方法了。因此我们研究一个索引示例。

　　假使我们要做一个在线约会的网站。用户的资料就会有很多列，比如country, state/region, city, sex, age, eye color等等。这个站点必须支持这些属性的组合来查询用户资料。也必须支持让用户通过用户最近的在线时间，其他会员的打分等等来排序和限制结果。对于这么个复杂的需求我们怎样来设计索引？

　　奇怪的是，第一个要决定的就是我们是否必须使用索引排序或者文件排序是否是可接受的。基于索引的排序限定了索引和语句创建的方式。比如，我们不能使用索引的情况是WHERE条件的age在18-25之间，不管语句是否使用了一个索引去排序。

　　如果MySQL使用了索引的范围查询，它也就不能使用另一个索引来排序了。假如这是个比较常见的查询，我们就得知有很多查询都要用到文件排序。

　　支持多种类的过滤

　　现在我们需要知道列具有唯一值的数量以及那些经常出现在WHERE条件后的列。在具有许多唯一值的列创建索引，这样做选择性很高。因为MySQL会高效的过滤掉不需要的值。

　　country不确定是否具有选择性，但是它可能会在大部分查询语句中出现。SEX列不具有选择性，但是它基本会在每个查询语句中出现。根据以上的想法，我们可以使用前缀为（sex,country）来创建一系列不同组合的索引。

　　按照传统的思路来说，在选择性低的字段加索引没有什么效果。但是我们为什么把一个选择性低的列放在每个索引的开始部分呢？思路错误了？

　　我们有两个理由要这么使用。第一个理由就是每个查询都会用到sex。我们甚至可以设计让用户仅能通过一个sex来进行搜索。重要的是，添加这一列也不会有什么弊端，因为我们还有一些技巧在里面。

索引和表的维护

　　当你已经创建了一张表，有合适的数据类型，并添加了索引之后，其实你的工作还并没有结束：你还需要维护你的表和索引使它们工作的更好。表的维护有三个主要的目标：发现和解决表的损坏，维护准确的索引统计，并且要降低存储碎片。

　　找到和修复损坏的表

　　最差的事情莫过于表已经损坏了。对于MyISAM，大部分是由于当机所造成的。然而，所有的存储引擎都会由于硬件问题或者MySQL内部BUG再或者操作系统的原因导致索引的损坏。

　　损坏的索引能导致查询返回不正确的结果，当没有重复值出现却抛出重复键值的错误，或者导致查询死锁和当机。如果你碰到了奇怪的行为-比如一个你意想不到的错误，就CHECK TABLE来查看表是否损坏。CHECK TABLE一般可以检查大部分表和索引的损坏。

　　你可以使用REPAIR TABLE来修复。但是并不是所有的引擎都支持这个命令。这样你可以使用ALTER命令，比如修改和表相同的存储引擎。

　　mysql> ALTER TABLE innodb_tbl ENGINE=INNODB;

　　你也可以使用离线的针对存储引擎的修复工具。比如myisamchk或者删除数据再重新加载。然而，如果换坏是发生在系统中，或者在表中的“行数据”取代了索引，你就无能为力了。这种情况下，你只能从备份中恢复表或者从损坏的文件中恢复数据。以后会详细说到。

　　更新索引的统计

　　MySQL的查询优化器使用两个API从存储引擎中得知当决定怎样使用索引的时候，索引是怎样分布的。第一个是records_in_range调用。它传入终结点范围并且返回了范围的记录的值。第二个就是info(),它返回了不同类型的数据，包括了索引的基数（对于每个键值有多少数据）。

　　当存储引擎并没有提供给优化器关于查询行数的准确信息，这个优化器就会使用索引的统计信息。这个信息你可以使用ANALYZE TABLE来估计下行数。MySQL的优化器是基于成本的，并且最主要的消耗因素就是这个查询要访问多少数据。如果这个统计信息没有生成，或者如果它们过期了，优化器可能就会有个比较差的决定。方案就是使用ANALYZE TABLE来生成统计数据。

范式和非范式

　　有很多方法来展现给定的数据。从完全范式到完全的非范式以及介于两者之间。在符合范式的数据库中，每个事实展现一次并且仅仅展现一次而已。相反的，在非范式的数据库，信息重复或者存储在很多的地方。

　　如果你不熟悉范式，你应该加强学习了。关于范式，你可以通过一些书和网上资源来学习。在这里，我们主要介绍这一章中你应该明白的知识。让我们看看经典的例子，那就是employee,departments,和department heads.

EMPLOYEE 　 　 　 　 　 　 DEPARTMENT 　 　 　 　 　 　 　 　 　HEAD
Jones 　 　 　 　 　 　 　 　 　 　 　Accounting 　 　 　 　 　 　 　 　 　 　 　 Jones
Smith 　 　 　 　 　 　 　 　 　 　 　Engineering 　 　 　 　 　 　 　 　 　 　 Smith
Brown 　 　 　 　 　 　 　 　 　 　 Accounting 　 　 　 　 　 　 　 　 　 　 　 Jones
Green 　 　 　 　 　 　 　 　 　 　 Engineering 　 　 　 　 　 　 　 　 　 　 　Smith

　　这种设计问题在于当数据更改的时候，这数据模型就会变得不正常了。如果Brown接管了Accounting部门，我们必须更新多条语句来反映出这个改变，并且这些更新可能还使数据的状态不一致。如果Jones行的HEAD和Brown行的HEAD不同的话，没有方法知道到底那个HEAD是正确的。就像那句老话一样：一个人有两块表，就不会知道准确的时间。更进一步的说，没有员工的时候就不能展现DEPARTMENT.如果我们删除了全部员工，DEPARTMENT的信息也同样的被删除了。为了避免这个问题，我们把这个表分为EMPLOYEE和DEPARTMENT两个实体。结果为两张表：

EMPLOYEE_NAME 　 　 　 　DEPARTMENT
Jones 　 　 　 　 　 　 　 　 　 　 　 　 　 Accounting
Smith 　 　 　 　 　 　 　 　 　 　 　 　 　 Engineering
Brown 　 　 　 　 　 　 　 　 　 　 　 　 　Accounting
Green 　 　 　 　 　 　 　 　 　 　 　 　 　 Engineering



DEPARTMENT 　 　 　 　 　 　 　 　 HEAD
Accounting 　 　 　 　 　 　 　 　 　 　 Jones
Engineering 　 　 　 　 　 　 　 　 　 Smith




加速ALTER TABLE

　　当对于一个大表进行ALTER TABLE的时候，性能问题就产生了。MySQL大部分改动的步骤如下：根据新的表结构创建一个空表，从旧表中把数据取出来插入到新表中，在删除旧表。这是个非常漫长的过程。许多人ALTER TABLE之后，都有等待1小时或者1天的痛苦经历。

　　MySQL AB已经开始提升这方面的性能了。一些即将到来的特性是支持"在线"的操作，而不会去锁定表。InnoDB的开发者也在积极努力开发以排序来创建索引。MyISAM已经支持这一特性了，结果就是索引更快并且压缩了索引布局。

　　并不是所有的ALTER TABLE都会使表重建。举个例子，你可以用两个方法更改或者删除列默认值（一个快，一个慢）。如果你想更改一个film的租赁期限为原来的3天到现在的5天。方法如下：

mysql> ALTER TABLE sakila.film
　　 　-> MODIFY COLUMN rental_duration TINYINT(3) NOT NULL DEFAULT 5;

　　用SHOW 　STATUS来监控这个语句，它做了1000次读和1000次插入。换句话说就是复制一个表到新表中。即使这个列的类型，大小，是否为NULL都没有改变。

　　原理上来说，MySQL可以跳过创建新表。这个默认的值实际存储在.frm文件中。因此你可以更改它而没有必要接触表。MySQL也不会做优化，然而任何的MODIFY COLUMN都会导致表的重建。

　　你可以使用ALTER COLUMN来修改：

mysql> ALTER TABLE sakila.film
　　 　-> ALTER COLUMN rental_duration SET DEFAULT 5;

　　这个语句修改了.frm文件而没有去操作表。结果就是速度非常之快。

　　仅仅修改.frm文件

　　我们发现修改标的.frm文件速度非常快并且当它不能那么做的时候，MySQL有的时候会重建表。如果你愿意承担一部分风险，你可以告诉MySQL做一些类型的修改而不去重建表。

关于存储引擎的简单记录

　　这一章的结束，我们来说一下关于设计模型的存储引擎的选择，这些你应该牢记。我们不会全面的介绍存储引擎，目标就是列出一些影响数据模型设计的一些关键性因素。

　　MyISAM存储引擎

　　表锁（Table locks）

　　MyISAM是表一级的锁。小心地是这个不会成为一个瓶颈。

　　没有自动数据恢复（No automated data recovery）

　　如果MySQL服务器挂了或者电源关闭。你应该在使用表之前来修复MyISAM表。如果有个大表，这个过程可能会持续几个小时。

　　不支持事物（No transactions）

　　MyISAM不支持事物。事实上，MyISAM表不能保证一个单独的语句会执行完成。如果一个错误发生在多个UPDATE之中，一些行被更新而另外的行并不会更新。

　　只有索引会被缓存（Only indexes are cached in memory）

　　在MySQL进程内部，关键的缓冲区中，MyISAM只会缓存索引。操作系统缓存了表的数据，因此在MySQL5.0中，要想获取数据，就需要操作系统的调用。这个过程消耗是较大的。

　　压缩存储（Compact storage）

　　每一行都是紧挨着的，因此需要的硬盘就会小并且全表扫描就会很快。

　　Memory存储引擎

　　表锁（Table locks）

　　像MyISAM表一样，Memory表支持表锁。这不是个问题，因为语句都是在内存中执行的。非常快。

　　没有动态的行（No dynamic rows）

　　Memory表不支持动态（比如，变量的长度）行，因此它们不支持BLOB,TEXT字段。一个VARCHAR(5000)转为CHAR(5000)-如果大部分数据都是小的话，就会浪费大量内存。

　　Hash索引是默认的索引类型（Hash indexes are the default index type）

　　不像其他的存储引擎，它的默认索引类型是hash。

　　没有索引统计（No index statistics）

　　Memory表不支持索引统计，因此可能对于复杂的查询，执行很不理想。

　　重启动后内容丢失（Content is lost on restart）

　　Memory不会持久化任何数据。因此服务器重启后，数据都会丢失，但表还是存在的。

　　InnoDB存储引擎

　　事物（Transactional）

　　InnoDB支持事物，并且支持4个级别的事物隔离级别。

　　外键（Foreign keys）

　　MySQL5.0，InnoDB是唯一支持外键的存储引擎。其他存储引擎在CREATE TABLE时可以创建外键，但是不会约束。一些第三方引擎，比如solidDB以及PBXT，在存储引擎级别支持它们。MySQL AB准备在服务器级别支持外键。

　　行锁（Row-level locks）

　　锁在行的一级。没有扩大和非阻塞的select-标准的select不会设置任意的锁。这样并发性比较好。

　　多版本（Multiversioning）

　　InnoDB使用多版本并发控制，因此默认的你的select会读取健壮的数据。事实上，MVCC架构添加了很多复杂性和可能的意外现象。你应该仔细阅读InnoDB手册。

　　通过主键来聚簇（Clustering by primary key）

　　所有的InnoDB表都是通过主键来聚簇的。这个可以用来更高级别的数据模型设计。

　　所有的索引都包含主键列（All indexes contain the primary key columns）

　　索引用主键来引用行。如果你的主键很长，那么索引的增长会特别快速。

　　缓存的优化（Optimized caching）

　　在缓冲池，InnoDB缓存了数据和内存。它也自动创建Hash索引来加速行的获取。

　　无压缩的索引（Unpacked indexes）

　　索引并没有前缀压缩。因此它们要大于MyISAM表的索引。

　　数据读取缓慢（Slow data load）

　　MySQL5.0，InnoDB没有优化读取数据的操作。它一次给一行创建了索引。而并不是排序的创建它们。这就导致读取数据很慢。

　　阻塞的AUTO_INCREMENT（Blocking AUTO_INCREMENT）

　　在MySQL5.1较早的版本，InnoDB使用表级别的锁来生成AUTO_INCREMENT值。

　　不会缓存COUNT(*)的值

　　不像MyISAM表或者Memory表，InnoDB表并不存储表的行数。也就是说没有WHERE条件的COUNT(*)并不会优化，并且需要整个表的扫描。下一章会说优化COUNT()语句。