高性能MySQL笔记之索引

一、索引基础

索引就象是书的目录一样，如果人们想要查看书的指定内容，首先就要去看书的目录，找到该内容在书的多少页，然后再看相关内容。同理，在有索引的情况下，MySQL会先在索引上按值进行查找，然后返包含该值的数据行。需要注意的是，索引是在存储引擎中实现的，所以没有统一的索引标准。

使用索引的优点如下：

索引大大减少了服务器需要扫描的数据量 

索引可以帮助服务器避免排序和临时表

索引可以将随机I/O变为顺序I/O

二、索引类型

B-Tree索引

B-Tree索引是当前用得最多的索引，当人们讨论索引时，如果没有特别地指明类型，那多半说的就是B-Tree索引，这种索引使用的是B-Tree数据结构来存储数据（也叫平衡多路查找树），因为B-Tree结构是顺序组织存储的，在查找数据的时候，存储引擎便不再需要进行全表扫描来获取需要的数据。在不同的存储引擎中，使用的B-Tree数据结构也不同，NDB集群存储引擎使用的是T-Tree，InnoDB则使用的是B+Tree。

B-Tree索引适用于全键值、键值范围或键前缀查找，举一个例子对其适用范围进行详细说明：

create table `person`(

last_name varchar(20) not null,

first_name varchar(20) not null,

age tinyint unsigned not null,

key (last_name,first_name,age)

);

对于person表，B-Tree索引适用的场景如下：

全值匹配：全值匹配指的是和索引中的所有列进行匹配，例如查找姓名为Cube Allen且年龄为20的人。

匹配最左前缀：即匹配左边第一列的人。

匹配列前缀：即匹配左边第一列的开头的某一部分的值。

匹配范围值：可以用来查询姓在Allen和Barrymore之间的人

精确匹配某一列并范围匹配另一列：即第一列用匹配最左前缀，第二列用匹配范围值

只访问索引的查询：要查询的列都在索引内，即在查询的时候只访问索引就可以了，不用再去访问数据行。

使用B-Tree索引的限制：

索引必须要从最左边开始，否则无法索引

不能跳过索引中的列

如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。

哈希索引

哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。哈希索引是Memory的默认索引类型，同时也只有Memory存储引擎支持哈希索引，因为索引自身只需要存储对应的哈希值，所以索引结构十分紧凑，这也让哈希索引查找的速度非常快。

使用例子：

create table `testhash`(

fname varchar(20) not null,

lname varchar(20) not null,

key using hash(fname)

)engine=memory;

使用哈希索引的限制：

哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。

哈希索引数据并不是按照顺序存储的，所以也就无法用来排序。

哈希索引只支持等值查询

如果哈希冲突很多的话，索引维护的代价会很高

其他：

虽然InnoDB中没有显示的哈希索引，但有一个特殊的功能叫做“自适应哈希索引”。当存储引擎注意到时某些索引被使用得非常频繁时，它会在内在中基于B-Tree索引之上再创建一个哈希索引。

全文索引

全文索引是一种特殊的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。因为全文索引有许多需要注意的细节，这种索引更类似于搜索引擎干的事，而不是简单的where查询。需要注意的是在同一个列上同时创建一个B-Tree索引和一个全文索引是不会起冲突的。

Ps：这一部分在索引章节里面并没有详细描述，我也懒得找资料了，后面看到相关的地方再补全吧。

三、高性能的索引策略

1.索引必须是独立的列，即索引列不能是表达式的一部分，也不能是函数的参数

2.当有些索引字段过长时应选择前缀索引，选择前缀长度的时候要注意不能过于短，可以通过计算完整列的选择性来确定长度，假定city列上面存在一个索引，计算完整列选择性的方法如下:

首先select count(distinct city)/count(*) from city_demo;这时会得到一个小数值，假定是0.0031

然后再截取city字段的一部分数据做同样的操作

select count(distinct left(city,4))/count(*) from city_demo; 0.0022

select count(distinct left(city,5))/count(*) from city_demo; 0.0026

select count(distinct left(city,6))/count(*) from city_demo; 0.0030

当测试到6的时候数值与0.0031很接近了，这时我们可以采用字段左边的6位来做索引

即alter table city_demo add key (city(6));

3.应该避免索引合并，5.0版本引入，即在表A和B列上各有一个单列索引，当执行where A=xxx or B=xxx时，MySQL会进行索引合并操作，我们可以通过设置optimizer_switch来关闭这个功能，或者使用union all来连接两个查询的结果。