Mysql数据库优化学习之三 索引优化（二）

转载请标明出处： http://fuliang.iteye.com/blog/1071157

高性能的索引策略
创建正确的索引和恰当的使用它，对查询的性能起到关键的作用。我们已经介绍了各种不同的索引的能力和弱点。下面我们将介绍索引的威力所在。

有很多的创建和选择使用索引的有效方式，因为有很多特殊情况的优化和特殊行为。

孤立列
如果你不将索引列孤立出来，MySQL通常无法用到索引。"孤立"一列意思是它不能是表达式的
一部分或者在函数中。
比如：

SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;
SELECT ... WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) <= 10;

你可以把它改造成：

SELECT actor_id FROM sakila.actor WHERE actor_id = 4;
SELECT ... WHERE date_col >= DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);

你可以对第二个语句进行改造，将CURRENT_DATE作为一个具体的日期值，这样
可以命中query cache：

SELECT ... WHERE date_col >= DATE_SUB('2011-05-29', INTERVAL 10 DAY);

前缀索引和索引的选择性
有时候你需要对一个很长字符串的列见索引，这会导致你的索引非常的大，并且蛮。一种
策略是建立hash索引，我们已经在前面介绍过。
你还可以采用另外的策略，比如对这列的前几个字符建索引，而不是全部。索引的选择性是指索引中不同的值的个数和所有行数的比率。一个高选择性的索引是好的，因为能够过滤掉更多的行。
一个前缀索引如果选择性足够好那么可以获得很高的性能。如果你使用BLOB或者TEXT，或者
非常长的VARCHAR列，那么你必须定义前缀索引，因为MySQL不允许在全部长度上建索引。

一个选择索引的技巧是选择足够长的前缀来获得比较好的选择性，但是又足够短来节省空间。比如对一个城市表前7个字符创建索引：

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

聚集索引：
聚集索引并不是单独的索引的类型，它只是一种数据存储方式。详细的信息依赖于实现。InnoDB的聚集索引实际上是将B-Tree索引和行存储到相同的结构中。一个具有聚集索引的表，它的行实际上存储在索引的叶子节点中的。聚集的意思实际上是相邻的key的值存储在相邻的空间中。一个表只能有一个聚集索引，因为你不能将一行数据同时存储在两个位置。
（然而，覆盖索引可以让你模拟多个聚集索引）
由于存储引擎负责实现索引，并且不是所有的存储引擎都支持聚集索引。现在只有solidDB和InnoDB支持。我们只讨论InnoDB，但是有些原则适用于所有的聚集索引的存储引擎。

有些数据库可以让你选择聚集的索引，但是MySQL至少现在还不允许。InnoDB通过主键来聚集。如果你没有定义主键，InnoDB将试着使用非空的具有唯一性的索引列。如果没有这样的索引，InnoDB将定义一个隐藏的主键，然后使用它来聚集。InnoDB只有在同一个也的记录才会聚集，因此相邻的key可能数据存储距离很远。

聚集索引可以帮助提高性能，但是仍然会导致一些严重的性能问题。你需要对聚集特别小心，特别是你从InnoDB和其他存储引擎切换的时候。

聚集索引优点：
1.将相关的数据保存相近的位置。比如你实现一个邮件系统，你可以按照user_id来聚集，这样你可以所有通过访问很少的磁盘页就可以得到单个用户的所有的message。如果你没有聚集索引，那么每一个message都需要一次磁盘I/O。
2.数据访问访问快。聚集索引在B-Tree上同时持有索引和数据，所以从聚集索引中获得数据要比没有聚集索引要快。
3.使用覆盖索引的查询，可以使用在存储在叶子节点主键的值，不需要根据key再去找对应的行。

聚集索引缺点：
1.聚集索引使得I/O负载最大程度的改善。但如果数据可以放在内存中，那么访问的顺序就没有那么大的关系，所以聚集索引没有提供更多的好处。
2.插入的速度依赖于插入的次序。按照主键的顺序插入到InnoDB表中，是最快的方式。如果你没有按照主键的顺序加载数据，那么最好在加载完之后使用optimize table来重新组织表。
3.更新聚集索引列非常耗时，因为这迫使InnoDB跟新行到新的位置。
4.按照聚集索引创建的表，当有新的记录插入时，如果不是按照主键顺序，那么可能导致页的分裂。当一行的key需要在一个满了的页插入数据时，会导致页的分裂。页面的分裂导致表用更多的空间。
5.二级索引将会变得非常大，因为叶子节点包含了key所引用的行。
6.二次索引需要两次索引查找而不是一次。

覆盖索引
索引是用来高效的查找行的，但是MySQL可以使用索引来检索一列的数据，所以不再需要去读行。索引的叶子节点存有想找的数据，所以不需要在去读取行再去找要检索的数据了。这种索引中包含了query要检索的数据被称为覆盖索引。

覆盖索引是一个非常强大的能够很大程度提高性能的工具。只读取索引而不再读取行中的数据的优点：
1.索引的大小要比表中所有行的大小小的多，所以Mysql只需要访问很少的数据就能得到需要的数据。这对Cache非常有好处，因为索引要比数据小的多，放在内存中更好。这个对MyISAM更是如此，因为他的索引是压缩的，这让它更小。
2.索引是按照索引的值排序的，所以这比从磁盘中访问每一行需要更少I/O。对于一些存储引擎，比如MyISAM，你可以使用OPTIMIZE来得到完全排序的索引，这让简单的去区间的query，完全使用顺序的索引访问。
3.很多的存储引擎cache索引药比数据更好.(Falcon是一个例外）。一些存储引擎比如
MyISAM，仅仅在MySQL内存中缓存索引，因为操作系统为MyISAM缓存数据，访问它需要系统调用，所以可能引起很大的性能开销，尤其在缓存，系统调用是一个非常昂贵的数据访问部分。
4.覆盖索引对于InnoDB表非常有帮助，由于InnoDB的聚集索引。InnoDB的二级索引的叶子几点保存主键的值，所以二级索引的如果覆盖了查询的数据，就可以避免主键的二次查询。

覆盖索引不是任何索引类型都可以的。索引必须能够存储索引列的值才可以，所以Hash,空间，全文索引都不能存储这些值，只有B-Tree索引才可以。并且不同的存储引擎支持的不同（比如内存和Falcon引擎现在还不可以）。

当一个查询被一个索引覆盖，可以使用Explain的Extra列看到使用"Using index"。


使用索引扫描来排序
MySQL有两种方式来生成有序的结果集：1.filesort 2.按照索引的顺序扫描。
可以使用Explain来查看查询计划中的type列是否有”index“来判断。

扫描索引是很快的，因为它只需要从index的一个entry到另一个。但是如果MySQL不能使用index去覆盖查询，那么需要根据索引去查找每一行，这基本上是一个随机的I/O操作，所以按照索引的顺序读取数据通常要比顺序扫描表要慢的多。

MySQL可以同时使用相同的索引来排序和查找。使用索引来将结果集合排序，只有索引的顺序和ORDER BY的顺序一致，并且所有排序的列都是相同的方向排序（降序或者升序）的时候才可以。如果多个表进行Join的话，那么只有第一个表的列在order by中出现，并且需要order by满足最左匹配。其他的情况MySQL都是使用filesort.

压缩（前缀压缩）索引
MyISAM使用前缀压缩来减小index的大小，这样可以让更多的索引放到内存中，在某些情况能很大的优化性能。MyISAM压缩字符类型的值，你也可以告诉它压缩整数的值。

MyISAM将每一个索引块进行压缩，它完全的存储索引块的第一个值，然后通过记录和前面值的最长公共前缀的大小，加上不同的后缀值的方式来存储其他的值。比如第一个是perform，第二个是performance，那么第二个值将被存储为7,ance。MySQL同事对相邻行的指针进行前缀压缩的方式存储。

压缩的块使用较少的空间，但是让某些操作变慢。因为每一个前缀压缩的值都依赖于前面的值，所以MyISAM不能使用二分查找来找到索引块中的值，必须通过从开始顺序扫描的方式。
顺序的向前扫描很高效，但是相反的扫描方向，比如order by desc，不能很好的工作。
任何在块中央的值都需要顺序扫描，平均需要扫描半个块。我们做性能测试发现，压缩的索引要慢好几倍，因为扫描需要随机查找，逆向的扫描会更糟。这是cpu和I/O操作的一个权衡，压缩的索引可能会在磁盘空间上是原来的1/10左右。

你可以通过在创建表的时候使用PACK_KEYS选项来控制一个table的索引被压缩。

多余和重复的索引
MySQL允许对一个列建多次索引，MySQL需要独立的维护这些重复的索引，query的优化也要考虑他们每一个。这会导致严重的性能问题。

你可能不经意间创建重复的索引，比如：

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

MySQL会对UNIQUE和PRIMARY KEY约束的列自动建索引，所以会导致重复对一个列ID创建三个索引。

多余的索引和重复索引有点不同。如果你对（A，B）建立了联合索引，另外对A建立了索引，那么索引A是重复的，因为他是第一个索引的前缀。

索引和锁
索引在InnoDB中起到了重要的作用，因为它让查询锁住更少的行，这是一个重要的考虑，因为MySQL 5.0的InnoDB知道事务结束才解锁行。
如果查询不触及不需要的行，那么就会锁住更少的行，这样会得到更好的性能：
1.虽然InnoDB的所非常高效、使用比较少的内存，但是仍然可能导致一些行过早的锁住。
2.锁住更多的行增加了锁的竞争，减少并发性。

尽可能的去扩展索引，而不是添加一个新的索引，因为通常维护多列的索引要比几个单列的索引要搞笑，如果你不知道你query的分布，那么尽量在有区分度的列建索引。

支持多种过滤条件
在有区分度的列建索引通常会更高效，因为它能够过滤掉更多的结果。但是有时候为了支持多种过滤条件，我们可能会做出一些其他的选择。
比如对country列建索引可能不具有可选择性，但是经常出现在查询中，sex列更没有区分度，但是它基本出现在每一个query中。所以我们为了支持不同的组合我们可以建以(sex,country)为前缀的索引。传统的观点是对具有很低选择性的列建索引是无用的，但是我们将这两个不具有选择性的列放在每一个索引的前面，是因为：
每一个查询都使用了sex，我们可以使用一些小技巧，通过添加sex in ('m','f')到where条件，使其具有前缀索引的特性。但是如果in（）列表如果太大也会导致一些问题。
这个例子说明一个一般的原则：在表上保持所有的选项。当你设计索引的时候，你不要值考虑现在已有的query需要哪些索引，而是要同时考虑如果优化这些query。你可以通过优化query和索引来做一个最好的妥协，你不可能设计出完美的索引。

避免多个区间条件：
什么是区间条件？ > < 以及like都是区间索引，但是in不是的，所以如果区间范围不是很大可以使用in来优化。
多个区间索引，只能使用一个区间的索引，并且第一个区间之后的索引都不能被使用。

优化排序：
排序很小的结果集使用filesort很快。但是如果是有百万以上的行匹配，那么将会导致性能问题。
比如如果只有sex在where条件中，那么会有很多匹配的记录，我们可以通过添加对低选择性的列添加为排序使用的索引。比如下面的查询，(sex,rating)索引将被使用：

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

即使有了索引，如果用户的接口具有分页，如果用户请求了不再开始的记录。比如：

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

这种查询不管怎么建索引都会导致性能问题，因为很大的offset导致花费了大量的时间扫描大量的数据，并且这些大量的数据会被扔到。反范式、预先计算、缓存是解决这个问题的策略。
另一中优化这类查询的方式是覆盖索引。你可以先只查主键，然后在join这个表来得到需要的列。
比如前面的例子可以使用如下sql进行优化：

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>);

学习参考书《高性能MySQL》