性能优化一创建高性能的索引_如何创建性能最优的索引-CSDN博客

本文链接：https://blog.csdn.net/disiwei1012/article/details/63684399

一、索引基础

在MYSQL中，索引是在存储引擎层而不是服务器层实现的。所以，并没有统一的索引标准：

B TREE索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行索引，不断的比较要查找的值和节点值，直到找到对应的值。叶子节点比较特别，他们的指针指向的是被索引的数据。

看书上的一个例子：
有如下数据表：
这里写图片描述

在这个表中建立了一个组合索引（last_name,first_name,dob）。
根据建立组合索引形成的BTREE索引结构图如下：
这里写图片描述

BTREE索引适用于：

全值匹配：查询条件中同时有三个索引。如查找 last_name=’Allen’ and first_name=’Cuba’ and dob=’1960-01-01’。
匹配最左前缀：如下
last_name=’Allen’;
last_name=’Allen’ and first_name=’Cuba’
匹配列前缀：也可以只匹配某一列的开头部分。例如查找所有J开头的姓的人，前提满足【匹配最左原则】
匹配范围值：查找姓在Allen 和 Barrymore之间的人。前提满足【匹配最左原则】
精确匹配某一行并范围匹配另一列：查找所有姓为Allen,并且字母K开头的人。前提满足【匹配最左原则】

因为索引树中的节点是有序的，所以除了按值查找之外，索引还可以用于查询中的order by 操作。一般来说，如果BTREE可以按照某种方式查找到值，那么也可以按照这种方式排序。所以，如果order by子句满足前面列出的几种查询类型，那么这个索引页可以满足对应的排序需求。

下面是关于BTREE索引的限制：

如果不是按照索引最左列开始查找，则无法使用索引。例如上面例子中的索引无法用于查找名字为Bill的人，也无法查找某个特定生日的人，因为这两列都不是最左数据列。
不能跳过索引中的列，也就是说，前面所述的索引无法用于查找姓名为Simth并且再某个特定日期出生的人。
如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查询。例如有查询 where last_name=’Simth’ and first_name like ‘%j’ and dob = ‘1976-01-01’，这个查询只能使用索引的前两列，因为这里的LIKE是一个范围条件。如果范围查询列值的数量有限，那么可以通过使用多个等于条件来代替范围条件。

看下面的例子：
这里写图片描述

表中数据如下：
这里写图片描述

假如hash函数f()，它返回下面的值：
这里写图片描述

来看此查询：

select lname from testhash where fname='Peter';

mysql先计算Peter的哈希值，并使用该值找到对应的记录指针，便能够找到要查找的行。

因为索引自身只需存储对应的哈希值，所以索引结构十分紧凑，速度非常快。然而，哈希索引也有它的限制：

哈希索引只包含哈希值，而不存储字段值，所以不能使用索引中的值来避免读取行。不过，访问内存中的行速度很快，所以大部分情况下这一点对性能的影响并不明显。
哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。
哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。
哈希索引只支持等值比较查询，包括=、in（）。
访问哈希索引的数据非常快，除非有很多哈希冲突（不同行有相同的哈希值）
如果hash冲突很多的话，一些索引的维护代价也很高。例如，如果再某个哈希冲突很多的列上建立哈希索引，那么从表中删除一行时，存储引擎需要遍历对应哈希表中的每一行，找到并删除对应行的引用，冲突越多，代价越大。

因为这些限制，哈希索引只适用与某些特定的场合，而一旦适合哈希索引，则它带来的性能能提升非常显著。在mysql中，只有Memory引擎显示的支持哈希索引。

InnoDB引擎有一个特殊的功能叫做“自适应哈希索引”。当引擎注意到某些值使用的非常频繁时，他会再内存中基于BTREE索引之上再创建一个哈希索引。这是一个完全自动的、内部的行为，用户无法控制或配置，不过如果有必要，完全可以关闭该功能。