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

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

索引基础知识
索引是帮助MySQL有效检索数据的一种数据结构，它是获得高性能的关键，但是人们常常忘记或者错误的理解了它，所以索引通常是现实中最常出现的性能问题。
当你的数据变得很大时，索引变得非常重要，即使很轻负载的数据库没有恰当的索引，随着数据的增加，性能也会很快的下降。
MySQL使用索引都是一种类似的方式，他首先对给定的值搜索索引结构，如果在索引中找到，再去找包含匹配的行。
当你对多于一行的数据建索引的时候，索引的次序很重要，因为MySQL只能使用索引的最左前缀来有效进行搜索。对两列进行建索引和分别对单个列检索是不同的。

索引类型
有很多类型的索引，每种索引的设计在不同的目的下达到高效，索引是在存储引擎下实现的，并不是在服务层，因此他并没有被标准化：索引在不同的存储引擎不同，不是所有的引擎都支持所有的索引类型。即使多个索引支持同一种索引类型，也可能有不同的实现。

B-Tree索引
当人们谈论索引但没有提及类型时，他们通常指的是B-Tree索引，使用B-Tree的数据结构来存储索引。大多数的存储引擎支持这种索引类型。Archive引擎是个例外，直到5.1还没有支持索引，刚开始支持单索引的AUTO_INCREMENT列。
我们使用"B-Tree"来描述索引，因为CREATE TABLE和其他的语句都使用这个术语，但是不同的存储引擎结构。比如NDB聚类存储引擎使用T-Tree,但是标记BTREE。
不同的存储引擎存储B-Tree索引的方式不同，这会影响性能。比如MyISAM使用前缀压缩技术来让索引变得更小，然后InnoDB并没有压缩索引，因为他不能使用压缩的索引来优化。MyISAM索引直接指向行存储的物理地址，但是InnoDB通过主键的值来引用行，每一种方式都有优缺点。
B-Tree的一般观点是所有的值都有序的存储，每一个节点到根节点都有相同的距离。MyISAM使用不同的结构，但是基本上都是相似的。
由于B-Tree的索引列是有序的，他们对于搜索区间非常有用，比如查找“所有名字以I到K开头的”人，这是很有效的。
可以使用B-Tree索引的类型：B-Tree索引对于查找全部key的值，键的区间或者键的前缀。对
比如有以下表：

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

于一下集中查询非常有用：
1、匹配全部key的值 匹配所有在索引中的列 where last_name='Allen' and first_name='Cuba' and dob='1960-01-01'
2、最左前缀匹配 where last_name='Cuba' and first_name='Allen'
3.匹配一列的前缀 比如 where last_name like 'J%'
4.匹配一个区间 where last_name between 'Allen' and 'Barrymore'
5.匹配条件中前面的一个条件和区间部分 比如last_name='A' and first_na me like 'J%'
6.仅涉及到index的查询 这种查询只会访问索引，不会访问存储的行。这就是使用覆盖索引优化。select last_name, first_name, dob from People where last_name='Allen'
B-Tree索引的缺陷：
1.如果index的列不是从最左开始，那么索引没有用。
2.不能跳过索引中的一列。比如where last_name='Allen' and dob='1960-01-01'
3.存储引擎不能够优化区间右边的索引，比如：
where last_name='Smith' and first_name like 'J%' and dob='1976-12-23'
索引只能够用到last_name和first_name，因为like是一个区间条件。

所以索引的顺序非常重要，所有这些限制都是和索引的顺序相关。

Hash索引：
hash索引是基于hash表构建的，仅仅对精确查找索引中的列有用。对于每一行，存储引擎
对索引列计算hash code，它在索引中存储hash code和指向行的指针。

在MySQL中，只有Memory存储引擎支持显式的hash索引，是其默认的索引类型，但是Memory表可以使用B-Tree索引。
比如下面的表：

CREATE TABLE testhash (
fname VARCHAR(50) NOT NULL,
lname VARCHAR(50) NOT NULL,
KEY USING HASH(fname)
) ENGINE=MEMORY;

hash索引查询非常的快，然而hash索引有如下缺点：
1.因为索引中只有hash code和指向行的指针，所以MySQL不能直接使用索引的值来避免对行的访问，然而访问内存的行是很快的，所以并不会降低多少性能。
2.MySQL不能使用Hash索引来进行排序，因为他们并不是有序存储的。
3.Hash索引不支持部分key的匹配。因为计算hash code需要整个索引的值。
4.Hash索引仅仅支持等号操作=,in <=> ，所以并不能加快区间查询比如where price > 100
5.使用Hash索引访问数据很快，但是如果有很多冲突也是问题。
6.如果有很多hash冲突，那么维护索引会很慢。比如你一个区分度不是很够的列建立hash索引，那么删除一行，查找对应的一行会很耗时。
这些限制导致hash索引只在一部分特殊情况下比较有用。然而如果符合应用的需求，能够很大的增强访问的速度。
NDB聚类引擎支持唯一hash索引。。
InnoDB具有一个特别的特性被称为适应性hash索引。InnoDB发现比较频繁访问的索引值，会为其在B-Tree索引之上建立Hash索引，这使得B-Tree索引具有一定的hash特性，这个特性是自动的，你无法控制和配置。

构建自己的hash索引：
如果存储引擎不支持hash索引，你可以想InnoDB那样模拟一个。这样你可以获得hash索引很好的特性，比如很长的key具有很小的索引大小。

这个办法很简单：创建一个假的hash索引在标准的B树索引之上。这个和使用真的hash索引不是完全相同的事，它仍能够使用B-Tree索引来查询，但是使用的hash值而不是可以的本身来查找。你只需要在where条件中指定hash函数。
比如对于查找url的例子，url通常导致B-Tree索引变得很大，因为他们很长,我们可以使用

select id from url where url='http://www.mysql.com';

我们可以删除掉对url的索引，添加url_crc列，然后建立索引：

select id from url where url='http://www.mysql.com' and url_crc=CRC32('http://www.mysql.com');

一个缺点是需要维护hash这一列，在MySQL5.0以上版本已经支持触发器，我们可以使用它来维护：

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这样的hash函数，他们返回很长的字符产，浪费了很大的空间，导致慢的比较操作。简单的函数能够提供可接受的冲突率，是个比较好的选择。如果有很多的行，CRC32可能导致很多的冲突，实现自己的CRC64函数，确保其返回一个整数，而不是字符串。比如可以简单的这么实现：

SELECT CONV(RIGHT(MD5('http://www.mysql.com/'), 16), 16, 10) AS HASH64;

空间（R-Tree）索引
MyISAM支持空间索引,你可以使用地理空间类型比如geometry。不想B-Tree索引，空间索引不需要是最左前缀的。它同时索引所有维度的数据。这样，查询可以有效使用任何维度组合。
但是你需要使用GIS的函数，比如mbrcontains()。

全文索引
全文索引是MyISAM的一种特殊的索引类型。他可以在文本中查找关键字，而不是直接比较在
索引中的值。全文索引和其他类型的匹配完全不同。他有很多微妙之处，比如停顿词、词根化、和复数归一化，以及bool类型的搜索。和搜索引擎类似。
在一个列建全文索引并不会影响在这列建立B-tree索引。全文索引只对MATCH AGAINST操作有效，对普通的WHERE是无效的。

参考《高性能MySQL》