【高性能MySQL】第5章创建高性能的索引

前言：

索引key是存储引擎用于快速找到记录的一种数据结构；本章将讨论索引一些有用属性；恰当的索引对良好的性能非常关键，特别是数据量越来越大时，索引优化是对查询性能优化最有效的手段；

正文：

5.1索引基础

存储引擎使用索引：1、在索引中找到对应值，2、据匹配的索引记录找到对应数据行

索引可包含一或多个列的值，如果索引包含多个列，列的顺序很重要（最左前缀），索引对多个值排序依据的是create table语句中定义索引时的列顺序；

使用ORM，仍要理解索引，ORM工具能生产符合逻辑、合法的查询，但很难生成适合索引的查询

5.1.1类型

在存储引擎实现，无统一索引标准：不同引擎索引工作方式不一样，不是all引擎都支持所有类型的索引

B-Tree索引：

默认索引，大多数MySQL支持这种索引，archive例外：直到5.1才开始支持单个自增列的索引；

底层存储引擎也可能使用不同的存储结构，如NDB集群内部T-Tree，InnoDB是B+Tree；存储引擎以不同方式使用B-Tree，性能各不相同；

B-Tree通常all值按顺序存储的，且每个叶子页到根的距离相同，适合查找范围数据

图：+查找过程

快速访问数据，存储引擎从索引根节点(未画出)搜索、根节点槽中存放了指向子节点的指针，引擎据此向下层查找，通过比较节点页值和查找的值可找到合适的指针进入下层子节点，这些指针定义了子节点页中值的上下限；

叶子节点的指针指向被索引的数据，上图仅绘制节点及其对应叶子节点，在根节点和叶子节点间可能有很多层节点页，树深度和表大小直接相关；

全值匹配：和索引中all列匹配

匹配最左前缀：只使用索引第一列

匹配列前缀：只匹配某列值的开头

匹配范围值：查找在**间的数据

精确匹配某一列并范围匹配另一列：如查找all姓为Allen、名字以K开头的人

只访问索引的查询：覆盖索引的优化

 

索引树节点有序，除按值查找外，可用于order by（按顺序查找），一般来说，如果B-Tree可按照某种方式查找到值，也可按这种方式排序

限制：

• 不是按照索引最左列开始查找，则无法使用索引,上图 索引无法查找某个特定生日的人，列不是最左数据列
• 不能跳过索引中的列，无法查找姓为*且出生日期*的人，不指定名first_name，MySQL只能使用索引第一列
• 查询中有某个列的范围查询，则其右边all列均无法使用索引优化查找；还是写例子吧：where last_name=‘smith’ and first_name like‘j%’ and dob=‘1976-09-08’

 

哈希索引

哈希索引基于哈希表实现，精确匹配索引all列的查询才有效；对于每一行数据，引擎都会对all索引列计算一个哈希码，不同键值行哈希码不同，all哈希码存在索引中，同时哈希表保存指向各数据行的指针（一行数据一个哈希码）

只有Memory显式支持，也是其默认索引（也支持B-Tree、非唯一哈希索引：多个、链表方式），NDB集群引擎也支持唯一哈希索引

有一个表，数据如下

   

f是假设的哈希函数；三图每个槽顺序编号，但是数据行不是，查询select lname from testhash where fname=‘Peter' ;时，mysql先计算Peter的哈希值，并使用该值寻找对应的记录指针，据二图，在索引中查找8784，找到第3行指针，比较第三行的值是否为Peter，以确保是要查找的行（来、确认下眼神）紧凑、快

限制：因为此so只适用特定场合，一旦适合、性能显著（适合查找表的需求）

• 哈希索引只包含哈希值和行指针，不能使用索引值来避免读取行，不过访问内存中的行的速度快
• 哈希索引数据不是按照索引值顺序存储，无法用于排序
• 不支持部分索引列匹配查找：使用索引列的全部内容计算哈希值，在列A、B上建索引，如只查A，无法使用索引
• 只支持等值比较查询  =    in   <=>  ,不支持范围查询
• （表非常大易）出现哈希冲突（不同的索引列值却有相同的哈希值）时，引擎须遍历链表中all行指针，逐行比较
• 冲突很多时，维护操作代价很高，如在某个选择性很低（冲突很多）的列建立哈希索引，当从表中删除一行，引擎需遍历对应哈希值链表中每一行，找到并删除对应的行引用，冲突多代价大

InnoDB引擎有个特殊的功能“自适应哈希索引adaptive hash index”，当某些索引值频繁使用，会在内存中基于B-Tree索引上再建个哈希索引，使得B-Tree索引也具有哈希索引的一些优点：快速哈希查找，这是自动内部行为，可关闭；

创建自定义哈希索引，如果引擎不支持哈希，则可模拟想innodb一样创建哈希索引：

• b-tree基础上创建伪哈希索引（还是使用B-Tree查找）使用哈希值而不是键进行索引查找，需要在where子句中手动指定使用哈希函数
• 实例：存储大量url，据url搜索，使用b-tree存内容会很大：删除原url列的索引，增被索引的url_crc列，使用CRC32做哈希，查询改为：select id from url where url='http://www.mysql.com' and url_crc=CRC32('http://www.mysql.com');性能高 优化器会使用url_crc列索引完成查找，快
• 避免哈希冲突，须在where中带入哈希值及对应列值，如不查询具体值，可不带入列值，直接使用CRC32哈希值查询，select  word ,crc  from words where crc=CRC32('gun') and word='gun';
• 推荐使用FNV64（）做哈希函数，直接替换

 

空间数据索引

MyISAM支持，可做地理数据存储，无须前缀查询，从all维度来索引数据，查询时、可有效使用任意维度来组合查询，必须使用GIS相关函数如MBRCONTAINS等维护数据

全文索引

查找文本中的关键词，注意细节：停用词、词干、复数、布尔搜索等，类似于搜索引擎做的事情

相同类上同时创建全文索引和基于值的B-Tree索引不冲突；

适用于match against操作

其他索引类别

第三方存储引擎使用不同类型的数据结构做存储索引，tokuDB分形数索引（B-Tree很多优点也避免了btree一些缺点）多数情况下，对InnoDB的讨论也使用于TokuDB

ScaleDB使用Patricia tries

 

5.2索引优点

快速定位到表的指定位置，据创建索引的数据结构不同，索引其他附加作用

B-Tree顺序存储可以用来做order by和group by操作，数据有序会将相关列值一起存储，存储了实际列值，使用索引即可完成全部查询：总结如下

1. 减少服务器需要扫描的数据量
2. 帮服务器避免排序和临时表
3. 将随机I/O变成顺序I/O

评价索引：

1、相关记录放到一起，2、索引中数据顺序和查找中排列顺序一致，3、索引列包含查询中需要的全部列

索引并不总是最好的解决方案，根据表大小、及其他原因 视情况而定

 

5.3高性能索引策略

这个……听着名字的分量就感觉该换一篇写了

 

小结：

主要说了索引的类型和优点，其中穿插着的实例对理解很有帮助