【概述】
自定义哈希索引总结:
索引对于良好的性能非常关键。尤其是当表中的数据量越来越大时,索引对性能的影响很重要。
索引优化应该是对查询性能优化最有效的手段。
总的来说索引(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的实现可能不同,但思想都是类似)。
![(三)创建高性能的索引 - John.Zhou - zhouyou.jun的博客](http://img0.ph.126.net/5DvSX4gIO3rLv_YYGV6aWQ==/1756685329751306511.jpg)
例如,有如下表:
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 列的值,下面展示了
这个索引是如何组织数据的。
![(三)创建高性能的索引 - John.Zhou - zhouyou.jun的博客](http://img0.ph.126.net/oI5IdiLQEL9I-D26Foj-4Q==/3336322899051829506.jpg)
针对B-Tree索引,重点注意如下:
a.
M
ySQL索引是按照最左列开始查找。即索引列的最左边的列不在没有在语句中,则其他列
无法使用索引。
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' .
![(三)创建高性能的索引 - John.Zhou - zhouyou.jun的博客](http://img0.ph.126.net/2Y39uf7JO4hDI3EzkanlMA==/3047811047923227885.jpg)
2) . SELECT * FROM persons WHERE last_name
= 'Z' AND
first_name LIKE 'YJ
%'
AND birthday
= '2000-01-01' .
![(三)创建高性能的索引 - John.Zhou - zhouyou.jun的博客](http://img2.ph.126.net/fjefOWyF3TOiOCuO7qcifQ==/291326600995738367.jpg)
3) . SELECT * FROM persons WHERE
last_name =
'Z' AND first_name
=
'YJ'
AND birthday
= '2000-01-01' .
![(三)创建高性能的索引 - John.Zhou - zhouyou.jun的博客](http://img1.ph.126.net/oRPrmpWbTS7bdMHmTRxlkQ==/2291769260478262918.jpg)
有人肯定要问,红线处三个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;
![(三)创建高性能的索引 - John.Zhou - zhouyou.jun的博客](http://img0.ph.126.net/gx8jMTNTkeZ1sE8NQcH2MQ==/1759218604541704443.jpg)
![(三)创建高性能的索引 - John.Zhou - zhouyou.jun的博客](http://img2.ph.126.net/0fjzIqr5kZkanDTlYU992A==/1464514303925636690.jpg)
表中有如下数据,如图:
![(三)创建高性能的索引 - John.Zhou - zhouyou.jun的博客](http://img0.ph.126.net/ZwmuM_D_I8yQvg5EN-bDYw==/2265592087644174564.jpg)
假如索引使用哈希函数f(),那么将有如下值(表示值而已,并非真实数据,仅仅为了便于理解):
![(三)创建高性能的索引 - John.Zhou - zhouyou.jun的博客](http://img1.ph.126.net/b13wV0LiruFy3qbq__URzA==/3222044058507080467.jpg)
哈希索引总结:
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)
);
![(三)创建高性能的索引 - John.Zhou - zhouyou.jun的博客](http://img1.ph.126.net/UXnX0fs0kcf0WoqlXlqavg==/702843016946612017.jpg)
由于url会很长,所以要匹配url查找,性能不会太好。我在表中加一个url_crc列,用于存储url的哈希值。
哈希值是一个体积很小的数字,这样查找就很快了。 如下:
![(三)创建高性能的索引 - John.Zhou - zhouyou.jun的博客](http://img1.ph.126.net/02lPkBQaZ3JbQ_Rv8DZyZQ==/1753307630030781172.jpg)
CRC32返回的HASH值可能在大量数据情况下有重复,为此,可以使用下面的函数:
CONV
(
RIGHT
(
MD5( 'http://zyj.com'),16
), 16, 10
) AS hash64 . 如下:
![(三)创建高性能的索引 - John.Zhou - zhouyou.jun的博客](http://img2.ph.126.net/2ovVUlWA4EUsIxYNHsER3Q==/2019582957999073796.jpg)
它不是真正的哈希索引,实际上,上述例子中还是通过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 ;
![(三)创建高性能的索引 - John.Zhou - zhouyou.jun的博客](http://img1.ph.126.net/OMWK41u-WGZx7HvptHQzew==/1485906402155656288.jpg)
-- - - - - - -- -- -- -- -- - - - - -- - - -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)
)
;
![(三)创建高性能的索引 - John.Zhou - zhouyou.jun的博客](http://img1.ph.126.net/JRMJlxH6B9nty5-e72BnVg==/2853874788969646558.jpg)
上面的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;
看排序情况:
![(三)创建高性能的索引 - John.Zhou - zhouyou.jun的博客](http://img0.ph.126.net/YeBa19gzj1VnPel9Y4uubA==/6599292979167755766.jpg)
![(三)创建高性能的索引 - John.Zhou - zhouyou.jun的博客](http://img0.ph.126.net/dXrSKKTI5xbW5P9tMUgycA==/1475773302994102833.jpg)
1.5 冗余和重复索引
应该禁止这类索引。即:在相同的列上按照相同的顺序创建相同类型的索引。
结束语: 这篇博文写了几天才算勉强完成。为什么说勉强呢?因为还有很多需要去挖掘。
重点提醒:一定要学会用执行计划去优化自己的SQL。要熟透执行计划。
下一课题,会探讨 查询性能优化
2013-11-02 0:14
上海