Mysql索引原理-简书深入理解mysql索引( 二 ) _生活百科

全文索引使用注意事项：

全文索引必须在字符串、文本字段上建立。
全文索引字段值必须在最小字符和最大字符之间的才会有效。（innodb：3-84；myisam：4-84）
全文索引字段值要进行切词处理，按syntax字符进行切割，例如b+aaa，切分成b和aaa
全文索引匹配查询，默认使用的是等值匹配，例如a匹配a，不会匹配ab,ac 。如果想匹配可以在布尔模式下搜索a*

select * from user where match(name) against('a*' in boolean mode);
2. 索引原理
MySQL官方对索引定义：是存储引擎用于快速查找记录的一种数据结构。需要额外开辟空间和数据维护工作。

索引是物理数据页存储，在数据文件中（InnoDB，ibd文件），利用数据页(page)存储。
索引可以加快检索速度，但是同时也会降低增删改操作速度，索引维护需要代价。

索引涉及的理论知识：二分查找法、Hash和B+Tree 。
2.1 二分查找法
二分查找法也叫作折半查找法，它是在有序数组中查找指定数据的搜索算法。它的优点是等值查询、范围查询性能优秀，缺点是更新数据、新增数据、删除数据维护成本高。

首先定位left和right两个指针
计算(left+right)/2
判断除2后索引位置值与目标值的大小比对
索引位置值大于目标值就-1，right移动；如果小于目标值就+1，left移动

举个例子，下面的有序数组有17 个值，查找的目标值是7，过程如下：第一次查找：

文章插图
第二次查找：

文章插图
第三次查找：
第四次查找：
2.2 Hash结构
Hash底层实现是由Hash表来实现的，是根据键值 <key,value> 存储数据的结构。非常适合根据key查找value值，也就是单个key查询，或者说等值查询。其结构如下所示：
从上面结构可以看出，Hash索引可以方便的提供等值查询，但是对于范围查询就需要全表扫描了。Hash索引在MySQL 中Hash结构主要应用在Memory原生的Hash索引、InnoDB 自适应哈希索引。InnoDB自适应哈希索引是为了提升查询效率，InnoDB存储引擎会监控表上各个索引页的查询，当InnoDB注意到某些索引值访问非常频繁时，会在内存中基于B+Tree索引再创建一个哈希索引，使得内存中的 B+Tree 索引具备哈希索引的功能，即能够快速定值访问频繁访问的索引页。InnoDB自适应哈希索引：在使用Hash索引访问时，一次性查找就能定位数据，等值查询效率要优于B+Tree 。自适应哈希索引的建立使得InnoDB存储引擎能自动根据索引页访问的频率和模式自动地为某些热点页建立哈希索引来加速访问。另外InnoDB自适应哈希索引的功能，用户只能选择开启或关闭功能，无法进行人工干涉。
show engine innodb status \G; show variables like '%innodb_adaptive%';
2.3 B+Tree结构
MySQL数据库索引采用的是B+Tree结构，在B-Tree结构上做了优化改造。B-Tree结构：