mysql索引查询优化及原理

一、hash索引和Btree索引

1.Hash索引

hash索引是基于hash表实现的，对于hash索引中的所有列，存储引擎都会为每一行计算一个hash码，hash索引原理是将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中。只有查询条件精确匹配到hash索引中的所有列时，才能使用到hash索引。
hash索引的匹配效率非常高。但是会存在使用限制

（1）Hash索引仅仅能满足"=",“IN"和”<=>"查询，不能使用范围查询。

由于 Hash 索引比较的是进行 Hash 运算之后的 Hash值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样。

（2）Hash 索引无法被用来避免数据的排序操作。

由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash值，而且Hash值的大小关系并不一定和 Hash运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算

（3）Hash索引不能利用部分索引键查询。

对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。

（4）Hash 索引在任何时候都不能避免表扫描。

前面已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。

（5）Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。

对于选择性比较低的索引键，如果创建 Hash 索引，那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下。

hash索引的限制：

（6）mysqlInnoDB存储引擎 是支持hash索引的，但是是自适应的，InnoDB存储引擎会根据表的使用情况自动为表生成hash索引。不能人为干预是否在一张表中是否生成hash索引。

2.BTREE索引

常说的B TREE包含BTREE和B+TREE
（1）B TREE

定义数据记录为一个二元组[key, data]
key为记录的键值，对于不同数据记录，key是互不相同的
data为数据记录除key外的数据。
那么B Tree满足下列条件

d为大于1的一个正整数，称为B-Tree的度
h为一个正整数，称为B-Tree的高度
每个非叶节点由n-1个key和n个指针组成，其中d<=n<=2d
每个叶节点最少包含一个key和两个指针，最多包含2d-1个key和2d个指针，叶节点的指针均为null
所有叶节点具有相同的深度，等于树高h
key和指针互相间隔，节点两端是指针
一个节点中的key从左到右非递减排列
所有节点组成树结构
每个指针要么为null，要么指向另外一个节点
如果某个指针在节点node最左边且不为null，则其指向节点的所有key小于>v(key1),v(key1)为node的第一个key的值
如果某个指针在节点node最右边且不为null，则其指向节点的所有key大于v(keym),v(keym)为node的最后一个key的值。
如果某个指针在节点node的左右相邻key分别是keyi,keyi+1且不为null，则其指向节点的所有key小于v(keyi+1)且大于v(keyi)

按key检索数据的算法

首先从根节点二分查找
如果找到则返回对应节点的data
否则对相应区间的指针指向的节点递归进行查找
直到找到目标节点/null指针，查找成功/失败

bTreeSearch(node, key) {
if(node == null) return null;
foreach(node.key) {
if(node.key[i] == key) return node.data[i];
if(node.key[i] > key) return bTreeSearch(point[i]->node);
}
return bTreeSearch(point[i+1]->node);
}
data = bTreeSearch(root, my_key);

关于B-Tree有一系列有趣的性质，例如一个度为d的B-Tree，设其索引N个key，则其树高h的上限为

检索一个key，其查找节点个数的渐进复杂度为

（2）B+TREE
与B Tree相比，B+Tree有以下不同点

每个节点的指针上限为2d
内节点只存key
叶节点不存指针,叶节点指向被索引的数据而不是其他叶节点,在innodb中,指向的是主键,myshaym中指向的是数据的物理地址

由于并不是所有节点都具有相同的域，因此B+Tree中叶节点和内节点一般大小不同
这点与B Tree不同，虽然B Tree中不同节点存放的key和指针可能数量不一致，但是每个节点的域和上限是一致的，所以在实现中B Tree往往对每个节点申请同等大小的空间
一般来说，B+Tree比B Tree更适合实现外存储索引结构
对B+TREE进行优化，便车带有顺序访问指针的B+Tree
在经典B+Tree的基础上进行了优化，增加了顺序访问指针

在B+Tree的每个叶节点增加一个指向相邻叶节点指针，形成带有顺序访问指针的B+Tree
此优化的目的是提高区间访问的性能，例如图4中如果要查询key为从18到49的所有数据记录，当找到18后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提高了区间查询效率.

二、mysql存储引擎对索引的实现
在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的，本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式

1、MYISAM的索引实现
MyISAM引擎使用B+Tree作为索引结构，叶节点data域存放数据记录的地址

设Col1为主键，则上图是一个MyISAM表的主索引（Primary key）示例。
可以看出MyISAM的索引文件仅仅保存数据记录的地址。
在MyISAM中，主索引和辅索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅索引的key可以重复
如果我们在Col2上建立一个辅索引，则此索引的结构如下图所示：

同样也是一颗B+Tree，data域保存数据记录的地址。
因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。
MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分

2. InnoDB索引实现
   虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同
   第一个重大区别是InnoDB的数据文件本身就是索引文件。
   从上文知
   MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址
   而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引
   
   可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。
   因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形
   第二个与MyISAM索引的不同是InnoDB的辅索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，
   
   这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，
例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅索引都引用主索引，过长的主索引会令辅索引变得过大。
再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择

3.B+Tree中的“最左前缀原理”
（1）联合索引
MySQL中的索引可以以一定顺序引用多个列，这种索引叫做联合索引，一般的，一个联合索引是一个有序元组<a1, a2, …, an>，其中各个元素均为数据表的一列
联合索引的列选取顺序：
经常会被使用的列优先
选择性高的列优先
宽度小的列优先
（2）覆盖索引
如果一个索引包含(或覆盖)所有需要查询的字段的值，称为‘覆盖索引’。即只需扫描索引而无须回表。
覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引不存储索引列的值，所以mysql只能用B-tree索引做覆盖索引。

只需要读取索引而不用读取数据,大大提高查询性能。有以下优点：
(1)索引项通常比记录要小，使得MySQL访问更少的数据
(2)索引都按值排序存储，相对于随机访问记录，需要更少的I/O
(3)大多数据引擎能更好的缓存索引。比如MyISAM只缓存索引。
(4)覆盖索引对于InnoDB表尤其有用，因为InnoDB使用聚集索引组织数据，如果二级索引中包含查询所需的数据，就不再需要在聚集索引中查找了。

三、索引的匹配
假设titles表的主索引为<emp_no, title, from_date>
1.全值匹配

当按照索引中所有列进行精确匹配（这里精确匹配指“=”或“IN”匹配）时，索引可以被用到。
这里有一点需要注意，理论上索引对顺序是敏感的，但是由于MySQL的查询优化器会自动调整where子句的条件顺序以使用适合的索引，例如我们将where中的条件顺序颠倒：结果是一样的

2.最左前缀匹配

当查询条件精确匹配索引的左边连续一个或几个列时，如<emp_no>或<emp_no, title>，索引可以被用到，但是只能用到一部分，即条件所组成的最左前缀。上面的查询从分析结果看用到了PRIMARY索引，但是key_len为4，说明只用到了索引的第一列前缀。
3.查询条件用到了索引中列的精确匹配，但是中间某个条件未提供

此时索引使用情况和情况二相同，因为title未提供，所以查询只用到了索引的第一列，而后面的from_date虽然也在索引中，但是由于title不存在而无法和左前缀连接，因此需要对结果进行扫描过滤from_date（这里由于emp_no唯一，所以不存在扫描）。
如果想让from_date也使用索引而不是where过滤，可以增加一个辅助索引<emp_no, from_date>，此时上面的查询会使用这个索引。
除此之外，还可以使用一种称之为“隔离列”的优化方法，将emp_no与from_date之间的“坑”填上。
首先我们看下title一共有几种不同的值：

只有7种。在这种成为“坑”的列值比较少的情况下，可以考虑用“IN”来填补这个“坑”从而形成最左前缀：

4.查询条件没有指定索引第一列。

由于不是最左前缀，索引这样的查询显然用不到索引。

5.匹配某列的前缀字符串

此时可以用到索引，但是如果通配符%不出现在开头，则可以用到索引，但根据具体情况不同可能只会用其中一个前缀）
6.范围查询(由于B+树的顺序特点,尤其适合此类查询)

范围列可以用到索引（必须是最左前缀），但是范围列后面的列无法用到索引。同时，索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引。

可以看到索引对第二个范围索引无能为力。这里特别要说明MySQL一个有意思的地方，那就是仅用explain可能无法区分范围索引和多值匹配，因为在type中这两者都显示为range。同时，用了“between”并不意味着就是范围查询，例如下面的查询：

7.查询条件中含有函数或表达式
很不幸，如果查询条件中含有函数或表达式，则MySQL不会为这列使用索引（虽然某些在数学意义上可以使用）。例如：

虽然这个查询和情况五中功能相同，但是由于使用了函数left，则无法为title列应用索引，而情况五中用LIKE则可以。再如：

显然这个查询等价于查询emp_no为10001的函数，但是由于查询条件是一个表达式，MySQL无法为其使用索引。看来MySQL还没有智能到自动优化常量表达式的程度，因此在写查询语句时尽量避免表达式出现在查询中，而是先手工私下代数运算，转换为无表达式的查询语句。

四、BTree索引的使用限制

不会使用索引的情况
① 以“%”开头的LIKE语句，模糊匹配
② OR语句前后没有同时使用索引
③ 数据类型出现隐式转化（如varchar不加单引号的话可能会自动转换为int型）

五、InnoDB的主键选择与插入优化
在使用InnoDB存储引擎时，如果没有特别的需要，请永远使用一个与业务无关的自增字段作为主键。

经常看到有帖子或博客讨论主键选择问题，有人建议使用业务无关的自增主键，有人觉得没有必要，完全可以使用如学号或身份证号这种唯一字段作为主键。不论支持哪种论点，大多数论据都是业务层面的。如果从数据库索引优化角度看，使用InnoDB引擎而不使用自增主键绝对是一个糟糕的主意。

上文讨论过InnoDB的索引实现，InnoDB使用聚集索引，数据记录本身被存于主索引（一颗B+Tree）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）。

如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。如下图所示：

这样就会形成一个紧凑的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上。

如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置：

此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。
因此，只要可以，请尽量在InnoDB上采用自增字段做主键。

六、全文索引

文本字段上(text)如果建立的是普通索引,那么只有对文本的字段内容前面的字符进行索引,其字符大小根据索引建立索引时申明的大小来规定.

如果文本中出现多个一样的字符,而且需要查找的话,那么其条件只能是 where column like ‘%xxxx%’ 这样做会让索引失效

.这个时候全文索引就起作用了
全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多

ALTER TABLE tablename ADD FULLTEXT(column1, column2)
有了全文索引，就可以用SELECT查询命令去检索那些包含着一个或多个给定单词的数据记录了。

ELECT * FROM tablename
WHERE MATCH(column1, column2) AGAINST(‘xxx′, ‘sss′, ‘ddd′)
这条命令将把column1和column2字段里有xxx、sss和ddd的数据记录全部查询出来。