【概述】
自定义哈希索引总结：
索引对于良好的性能非常关键。尤其是当表中的数据量越来越大时，索引对性能的影响很重要。
索引优化应该是对查询性能优化最有效的手段。
总的来说索引（B-Tree）有如下优点：
a. 索引大大减少了服务器需要扫描的数据量。
b. 索引可以帮助服务器避免排序和临时表。
c. 索引可以将随机I/O变为顺序I/O.
1. 索引基础
基础性的东西参照我的MySQL基础篇，这里着重重点。
索引可以包含一个或者多个列的值。如果索引包含多个列，那么列的顺序也是非常重要的，
因为MySQL只能高效地使用最左前缀列。
——关于ORM
我们经常使用ORM产品（如Hibernate等），它们能够生产符合逻辑、合法的查询语句，
除非生成非常基本的查询（例如根据主键查询），否则它很难生成适合索引的查询。无论多么的
牛X的ORM工具，在精妙和复杂的索引面前都是浮云。
1.1 索引的类型
1.1.1 B-Tree 索引
B-Tree通常是所有的值按照顺序存储。下图展示的B-Tree在InnoDB下的结构表示（不同的
存储引擎对B-Tree的实现可能不同，但思想都是类似）。
例如，有如下表：
CREATE TABLE persons(
last_name varchar(30) NOT NULL,
first_name varchar(30) NOT NULL,
birthday date NOT NULL,
KEY last_first_birthday_IDX ( last_name , first_name , birthday )
);
这个表上创建了一个索引，索引包含了last_name ，first_name ，birthday 列的值，下面展示了
这个索引是如何组织数据的。
针对B-Tree索引，重点注意如下：
a. MySQL索引是按照最左列开始查找。即索引列的最左边的列不在没有在语句中，则其他列
无法使用索引。
b. 因为索引树中的节点是有序的，所以除了按值查找之外，索引还可以用于查询中的ORDER BY
操作（按顺序查找）。
c. 不能跳过索引中的列。如，WHERE last_name = 'Z AND birthday = '2000-01-01'不能使用索引
查找last_name为Z且生日为2000-01-01的人。因为没有在WHERE语句中指定
first_name列，所以只能使用索引额第一列last_name，而不能跳过first_name列。明白否？ 呵呵
d. 如果查询中有某个列的范围查询，则其右边的所有列都无法使用索引优化查询。如：
WHERE last_name = 'Z' AND first_name LIKE 'YJ%' AND birthday = '2000-01-01'，这个查询
只能使用索引的前两列，因为这里LIKE是一个范围条件。
针对刚才的persons表，伙计们，来看三个不同的SQL的执行计划。SQL不同部分我用红色标出：
1) . SELECT * FROM persons WHERE last_name LIKE 'Z%' AND first_name = 'YJ'
AND birthday = '2000-01-01' .
2) . SELECT * FROM persons WHERE last_name = 'Z' AND first_name LIKE 'YJ%'
AND birthday = '2000-01-01' .
3) . SELECT * FROM persons WHERE last_name = 'Z' AND first_name = 'YJ'
AND birthday = '2000-01-01' .
有人肯定要问，红线处三个SQL是不同，有何区别呢？ 看完下面的东西：
- - - - - - - - - - - - - - - - - -- - - - - -解释 - - - - -- - - - - -- - - - - - -- - - - -- - - - - - -- - - - -- -
| id | SELECT识别符。这是SELECT的查询序列号 |
| select_type |
SELECT类型,可以为以下任何一种:
|
| table |
输出的行所引用的表 |
| type |
联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型进行排序:
|
| possible_keys |
指出MySQL能使用哪个索引在该表中找到行 |
| key | 显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。 |
| key_len | 显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。 |
| ref | 显示使用哪个列或常数与key一起从表中选择行。 |
| rows | 显示MySQL认为它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数。 |
| filtered | 显示了通过条件过滤出的行数的百分比估计值。 |
| Extra |
该列包含MySQL解决查询的详细信息
|
-- - - - - - -- -- -- -- -- - - - - - - -解释完毕- - - -2013- 10-27 01:28 - - - - - - - - - -- - - - -- -- - -
1.1.2 哈希索引
哈希索引是基于哈希表实现的，只有精确匹配索引所有列的查询才有效。关于哈希表可以
百度相关资料。目前MySQL中只有Memory存储引擎显示支持哈希索引。
看下面的例子：
CREATE TABLE myhash(
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
KEY test_hashIDX USING HASH(firstname)
) ENGINE = MEMORY;
表中有如下数据，如图：
假如索引使用哈希函数f(),那么将有如下值（表示值而已，并非真实数据，仅仅为了便于理解）：
哈希索引总结：
1) 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。不
过，访问内存中的行速度会非常快，故大部分情况下对性能影响不明显。
2) 哈希索引数据并不是按照索引值顺序存的，所以也就无法用于排序。
3) 哈希索引不支持部分索引列匹配查找，因为哈希索引始终都是使用索引列的全部内容来计算哈
希值的。
4) 哈希索引只支持等值比较查询。
5) 哈希值有可能冲突，太多的冲突哈希值的话，维护代价会很高的。
6) 哈希索引非常适合数据仓库中的 “星型” schema，需要关联很多查找表。
7) INNODB 中有种 “自适应哈希索引” 。这完全是innodb内部操作。它的原理是，innoDB注意到某些
索引值使用得很频繁，它会在内存中基于B-Tree索引之上再创建一个Hash索引，这样就让B-Tree索引也
具有了哈希索引的特点。
1.1.2 创建自定义哈希索引
之前，我们讨论过，尽量让表使用同一种存储引擎，这样就会带来某些问题。如一些存储引擎不支
持某些特性，这时，我们的做法是变通——改变一种方式让该存储引擎支持某种特性。比如InnoDB不支
持显示的哈希索引，那么我们应该这么做呢？——自定义哈希索引。
还是用刚才的表，举例。我们在myhash表中添加一个字段，个人主页URL字段。表名称改为
mydefinehash，存储引擎默认为innoDB。
CREATE TABLE mydefinehash(
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
url VARCHAR(500) ,
url_crc VARCHAR(50)
);
由于url会很长，所以要匹配url查找，性能不会太好。我在表中加一个url_crc列，用于存储url的哈希值。
哈希值是一个体积很小的数字，这样查找就很快了。 如下：
CRC32返回的HASH值可能在大量数据情况下有重复，为此，可以使用下面的函数：
CONV( RIGHT( MD5(
'http://zyj.com'),16 ), 16, 10 ) AS hash64 . 如下：
它不是真正的哈希索引，实际上，上述例子中还是通过B-Tree索引来快速定位url_crc列的值，
这个办法，可以解决内容很多的列的查找。同时会有另外一个问题，就是额外索引列（上述例
子中的url_crc列）需要维护，可以通过触发器维护。如：
/* 新增触发器 */
DELIMITER //
CREATE TRIGGER mydefinehash_crc_insert BEFORE INSERT ON mydefinehash
FOR EACH ROW BEGIN
SET NEW.url_crc = CRC32(NEW.url);
END;
//
/* 修改触发器 */
CREATE TRIGGER mydefinehash_crc_upd BEFORE UPDATE ON mydefinehash
FOR EACH ROW BEGIN
SET NEW.url_crc = CRC32(NEW.url);
END;
//
DELIMITER ;
-- - - - - - -- -- -- -- -- - - - - -- - - -2013- 10-27 23:28 - - - - - - - - - -- - - - -- -- - -
提示：【索引并不是通吃，比如在海量数据面前，可以考虑分区、分库、建立元数据信息表等。
特别是元数据表在对于大型系统来说，是经常用到的技巧】
1.2 高性能的索引策略
1.2.1 独立的列
独立的列指的是索引列不能是表达式的一部分，也不能是函数参数。
1.2.2 前缀索引和索引选择性
有时候需要的索引很长的字符串列，这会让索引变得很大且慢，一个策略是前上述的自定义
哈希索引（或者叫模拟哈希索引）。
还有一点很重要，就是一点要高选择性。
1.2.3 多列索引
就是我在Oracle系列里面提到的组合索引。不多说了。
1.2.4 选择合适的索引列顺序
在一个多列B-Tree索引中，索引列顺序按照最左列进行排序，其次是第二列...。索引索引可以
按照升序或者降序进行扫描，以满足符合列顺序的Order BY 、GROUP BY 等。
通常当需不要考虑排序和分组的时候，将选择性最高的列放到索引最前列是最佳实践。
1.2.5 聚簇索引
聚簇索引并不是一种索引类型，而是一种存储方式。参考我的Oracle相关博文。
1.2.6 整理碎片
本来不应该把整理碎片列为索引里面的一个小结，但由于重要且易忽略，故单独提出来。
导致MySQL产生碎片的原因很多，整理碎片有助于提升性能。一般可以通过备份/还原 整理碎片。还
可以通过OPTIMIZE TABLE来重新组织一下表。语法如下：
OPTIMIZE TABLE 表名称 ;
1.3 覆盖索引
这一节应该被列为1.2.7章节，但由于很重要。所以专门给它一个二级章节。够意思吧 哈哈。
覆盖索引——如果一个索引包含（或者说覆盖）所有需要查询的字段的值。目前，MySQL只有
B-Tree索引才支持覆盖索引。
1.4 使用索引扫描来做排序
同样，给它一个二级章节，其重要性，你懂的！
举例说明：
创建如下表：
CREATE TABLE rental(
rental_id VARCHAR(30),
customer_id VARCHAR(20),
staff_id VARCHAR(20),
inventory_id VARCHAR(20),
rental_date date,
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)
)
;
上面的SQL：
EXPLAIN SELECT rental_id ,staff_id FROM rental WHERE rental_date ='2013-11-01'
ORDER BY inventory_id, customer_id ;
并不满足“最左前缀”，但是由于最左的列被指定为了常数，所以讲剩下的两列作为“最左前缀”。
用例子来讲理论，便于理解。下面是一些不能使用索引做排序查询的SQL：
1) 下面这个语句使用了两种不同的排序方向，但索引列都是正序排列的：
... WHERE rental_date ='2013-11-01' ORDER BY inventory_id DESC ,customer_id ASC;
2) 下面这个语句的ORDER BY中引用了一个不再索引中的列：
... WHERE rental_date ='2013-11-01' ORDER BY inventory_id , staff_id;
3) 下面这个语句的WHERE 和ORDER BY中的列无法组合成索引的“最左前缀”：
... WHERE rental_date ='2013-11-01' ORDER BY customer_id ; /* 换成 inventory_id 就OK*/
4) 下面这个语句在索引列的第一列上是范围条件，所以MySQL无法使用索引的其余列：
... WHERE rental_date > '2013-08-01' ORDER BY inventory_id ,customer_id ;
5) 下面的语句在inventory_id 列上有多个等于条件。对于排序来说这也是一种范围查询：
...WHERE rental_date ='2013-11-01' AND inventory_id IN('10','20')
ORDER customer_id ;
看下面的例子，我先删除几个额外的单列索引：
ALERT TABLE rental DROP KEY idx_fk_inventory_id , idx_fk_customer_id , idx_fk_staff_id;
看排序情况：
1.5 冗余和重复索引
应该禁止这类索引。即：在相同的列上按照相同的顺序创建相同类型的索引。
结束语： 这篇博文写了几天才算勉强完成。为什么说勉强呢？因为还有很多需要去挖掘。
重点提醒：一定要学会用执行计划去优化自己的SQL。要熟透执行计划。
下一课题，会探讨 查询性能优化
2013-11-02 0:14
上海
扫一扫
3739
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
