也许你经常用MySQL
,也会经常用索引,但是对索引的原理和高级功能却并不知道,我们在这里一起学习下。
<span style="font-size: 20px;">InnoDB</span>
存储索引
在数据库中,如果索引太多,应用程序的性能可能会受到影响;如果索引太少,又会对查询性能产生影响。所以,我们要追求两者的一个平衡点,足够多的索引带来查询性能提高,又不因为索引过多导致修改数据等操作时负载过高。
InnoDB
支持3
种常见索引:
● 哈希索引
● B+
树索引
● 全文索引
我们接下来要详细讲解的就是B+
树索引和全文索引。
哈希索引
学习哈希索引之前,我们先了解一些基础的知识:哈希算法。哈希算法是一种常用的算法,时间复杂度为O(1)
。它不仅应用在索引上,各个数据库应用中也都会使用。
哈希表
哈希表(Hash Table)
也称散列表,由直接寻址表改进而来。
在该表中U
表示关键字全集,K
表示实际存在的关键字,右边的数组(哈希表)表示在内存中可以直接寻址的连续空间,哈希表中每个插槽关联的单向链表中存储实际数据的真实地址。
如果右边的数组直接使用直接寻址表,那么对于每一个关键字K都会存在一个h[K]
且不重复,这样存在一些问题,如果U数据量过大,那么对于计算机的可用容量来说有点不实际。而如果集合K
占比U
的比例过小,则分配的大部分空间都要浪费。
因此我们使用哈希表,我们通过一些函数h(k)
来确定映射关系,这样让离散的数据尽可能均匀分布的利用数组中的插槽,但会有一个问题,多个关键字映射到同一个插槽中,这种情况称为碰撞(collision)
,数据库中采用最简单的解决方案:链接法(chaining)
。也就是每个插槽存储一个单项链表,所有碰撞的元素会依次形成链表中的一个结点,如果不存在,则链表指向为NULL
。
而使用的函数h(k)
成为哈希函数,它必须能够很好的进行散列。最好能够避免碰撞或者达到最小碰撞。一般为了更好的处理哈希的关键字,我们会将其转换为自然数,然后通过除法散列、乘法散列或者全域散列来实现。数据库一般使用除法散列,即当有m个插槽时,我们对每个关键字k进行对m的取模:h(k) = k % m
。
<span style="font-size: 18px;">InnoDB</span>
存储引擎中的哈希算法
InnoDB
存储引擎使用哈希算法来查找字典,冲突机制采用链表,哈希函数采用除法散列。对于缓冲池的哈希表,在缓存池中的每页都有一个chain
指针,指向相同哈希值的页。对于除法散列,m
的值为略大于2
倍缓冲池页数量的质数。如当前innodb_buffer_pool_size
大小为10M
,则共有640个16KB
的页,需要分配1280
个插槽,而略大于的质数为1399
,因此会分配1399
个槽的哈希表,用来哈希查询缓冲池中的页。
而对于将每个页转换为自然数,每个表空间都有一个space_id
,用户要查询的是空间中某个连续的16KB
的页,即偏移量(offset)
,InnoDB
将space_id
左移20
位,再加上space_id
和offset
,即K=space_id<<20+space_id+offset
,然后使用除法散列到各个槽中。
自适应哈希索引
自适应哈希索引采用上面的哈希表实现,属于数据库内部机制,DBA
不能干预。它只对字典类型的查找非常快速,而对范围查找等却无能为力,如:
select * from t where f='100';
我们可以查看自适应哈希索引的使用情况:
mysql> show engine innodb status\G; *************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 2019-05-13 23:32:21 7f4875947700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 32 seconds ... ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 1226, seg size 1228, 0 merges merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 276671, node heap has 1288 buffer(s) 0.16 hash searches/s, 16.97 non-hash searches/s<p>我们可以看到自适应哈希的使用情况,可以通过最后一行的<code>hash searches/non-hash searches</code>来判断使用哈希索引的效率。</p> <p>我们可以使用<code>innodb_adaptive_hash_index</code>参数来禁用或启用此特性,默认开启。</p> <p><strong><code><span style="font-size: 20px;">B+ </span></code><span style="font-size: 20px;">树索引</span></strong></p> <p><code>B+ </code>树索引是目前关系型数据库系统中查找最为常用和有效的索引,其构造类似于二叉树,根据键值对快速找到数据。<code>B+ </code>树<code>(balance+ tree)</code>由<code>B</code>树<code>(banlance tree 平衡二叉树)</code>和索引顺序访问方法<code>(ISAM: Index Sequence Access Method)</code>演化而来,这几个都是经典的数据结构。而<code>MyISAM</code>引擎最初也是参考<code>ISAM</code>数据结构设计的。</p> <p><span style="font-size: 18px;"><strong>基础数据结构</strong></span></p> <p>想要了解<code>B+</code> 树数据结构,我们先了解一些基础的知识。</p> <p><strong>二分查找法</strong></p> <p>又称为折半查找法,指的是将数据顺序排列,通过每次和中间值比较,跳跃式查找,每次缩减一半的范围,快速找到目标的算法。其算法复杂度为<code>log2(n)</code>,比顺序查找要快上一些。</p> <p>如图所示,从有序列表中查找<code>48</code>,只需要<code>3</code>步:<br></p> <p><img src="https://img.php.cn/upload/image/871/940/820/1574846701708829.jpg" title="1574846701708829.jpg" alt="2.jpg"></p> <p>详细的算法可以参考<a href="https://juejin.im/post/5c90edee5188252d805c6b61" target="_blank">二分查找算法</a>。</p> <p><strong>二叉查找树</strong></p> <p>二叉查找树的定义是在一个二叉树中,左子树的值总是小于根键值,根键值总是小于右子树的值。在我们查找时,每次都从根开始查找,根据比较的结果来判断继续查找左子树还是右子树。其查找的方法非常类似于二分查找法。<br></p> <p><img src="https://img.php.cn/upload/image/203/317/932/1574846755181275.jpg" title="1574846755181275.jpg" alt="3.jpg"></p> <p><strong>平衡二叉树</strong></p> <p>二叉查找树的定义非常宽泛,可以任意构造,但是在极端情况下查询的效率和顺序查找一样,如只有左子树的二叉查找树。<br></p> <p><img src="https://img.php.cn/upload/image/944/170/208/1574846773452591.jpg" title="1574846773452591.jpg" alt="4.jpg"></p> <p>若想构造一个性能最大的二叉查找树,就需要该树是平衡的,即平衡二叉树(由于其发明者为<code>G. M. Adelson-Velsky </code>和 <code>Evgenii Landis</code>,又被称为<code>AVL</code>树)。其定义为必须满足任何节点的两个子树的高度最大差为<code>1</code>的二叉查找树。平衡二叉树相对结构较优,而最好的性能需要建立一个最优二叉树,但由于维护该树代价高,因此一般平衡二叉树即可。</p> <p>平衡二叉树查询速度很快,但在树发生变更时,需要通过一次或多次左旋和右旋来达到树新的平衡。这里不发散讲。</p> <p><strong><code><span style="font-size: 18px;">B+</span></code><span style="font-size: 18px;"> 树</span></strong></p> <p>了解了基础的数据结构后,我们来看下<code>B+</code> 树的实现,其定义十分复杂,简单来说就是在<code>B</code>树上增加规定:</p> <p>1、叶子结点存数据,非叶子结点存指针</p> <p>2、所有叶子结点从左到右用双向链表记录</p> <p>目标是为磁盘或其他直接存取辅助设备设计的一种平衡查找树。在该树中,所有的记录都按键值的大小放在同一层的叶子节点上,各叶子节点之间有指针进行连接(非连续存储),形成一个双向链表。索引节点按照平衡树的方式构造,并存在指针指向具体的叶子节点,进行快速查找。</p> <p>下面的<code>B+</code> 树为数据较少时,此时高度为<code>2</code>,每页固定存放<code>4</code>条记录,扇出固定为<code>5</code>(图上灰色部分)。叶子节点存放多条数据,是为了降低树的高度,进行快速查找。<br></p> <p><img src="https://img.php.cn/upload/image/342/762/205/1574846813384266.jpg" title="1574846813384266.jpg" alt="5.jpg"></p> <p>当我们插入<code>28、70、95</code> <code>3</code>条数据后,<code>B+</code> 树由于数据满了,需要进行页的拆分。此时高度变为<code>3</code>,每页依然是<code>4</code>条记录,双向链表未画出但是依然是存在的,现在可以看出来是一个平衡二叉树的雏形了。<br></p> <p><img src="https://img.php.cn/upload/image/873/277/529/1574846832346132.jpg" title="1574846832346132.jpg" alt="6.jpg"></p> <p><strong><code><span style="font-size: 18px;">InnoDB</span></code><span style="font-size: 18px;">的</span><code><span style="font-size: 18px;">B+</span></code><span style="font-size: 18px;"> 树索引</span></strong></p> <p><code>InnoDB</code>的<code>B+</code> 树索引的特点是高扇出性,因此一般树的高度为<code>2~4</code>层,这样我们在查找一条记录时只用<code>I/O</code> <code>2~4</code>次。当前机械硬盘每秒至少<code>100</code>次<code>I/O/s</code>,因此查询时间只需<code>0.02~0.04s</code>。</p> <p>数据库中的<code>B+</code> 树索引分为聚集索引<code>(clustered index)</code>和辅助索引<code>(secondary index)</code>。它们的区别是叶子节点存放的是否为一整行的完整数据。</p> <p><strong>聚集索引</strong></p> <p>聚集索引就是按照每张表的主键(唯一)构造一棵<code>B+</code> 树,同时叶子节点存放整行的完整数据,因此将叶子节点称为数据页。由于定义了数据的逻辑顺序,聚集索引也能快速的进行范围类型的查询。</p> <p>聚集索引的叶子节点按照逻辑顺序连续存储,叶子节点内部物理上连续存储,作为最小单元,叶子节点间通过双向指针连接,物理存储上不连续,逻辑存储上连续。</p> <p>聚集索引能够针对主键进行快速的排序查找和范围查找,由于是双向链表,因此在逆序查找时也非常快。</p> <p>我们可以通过<code>explain</code>命令来分析<code>MySQL</code>数据库的执行计划:</p> <pre class="brush:php;toolbar:false"># 查看表的定义,可以看到id为主键,name为普通列 mysql> show create table dimensionsConf; | Table | Create Table | dimensionsConf | CREATE TABLE `dimensionsConf` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, `remark` varchar(1024) NOT NULL, PRIMARY KEY (`id`), FULLTEXT KEY `fullindex_remark` (`remark`) ) ENGINE=InnoDB AUTO_INCREMENT=178 DEFAULT CHARSET=utf8 | 1 row in set (0.00 sec) # 先测试一个非主键的name属性排序并查找,可以看到没有使用到任何索引,且需要filesort(文件排序),这里的rows为输出行数的预估值 mysql> explain select * from dimensionsConf order by name limit 10\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: dimensionsConf type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 57 Extra: Using filesort 1 row in set (0.00 sec) # 再测试主键id的排序并查找,此时使用主键索引,在执行计划中没有了filesort操作,这就是聚集索引带来的优化 mysql> explain select * from dimensionsConf order by id limit 10\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: dimensionsConf type: index possible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 10 Extra: NULL 1 row in set (0.00 sec) # 再查找根据主键id的范围查找,此时直接根据叶子节点的上层节点就可以快速得到范围,然后读取数据 mysql> explain select * from dimensionsConf where id>10 and id<10000\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: dimensionsConf type: range possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 56 Extra: Using where 1 row in set (0.00 sec)
辅助索引
辅助索引又称非聚集索引,其叶子节点不包含行记录的全部数据,而是包含一个书签(bookmark)
,该书签指向对应行数据的聚集索引,告诉InnoDB
存储引擎去哪里查找具体的行数据。辅助索引与聚集索引的关系就是结构相似、独立存在,但辅助索引查找非索引数据需要依赖于聚集索引来查找。
全文索引
我们通过B+
树索引可以进行前缀查找,如:
select * from blog where content like 'xxx%';
只要为content
列添加了B+
树索引(聚集索引或辅助索引),就可快速查询。但在更多情况下,我们在博客或搜索引擎中需要查询的是某个单词,而不是某个单词开头,如:
select * from blog where content like '%xxx%';
此时如果使用B+
树索引依然是全表扫描,而全文检索(Full-Text Search)
就是将整本书或文章内任意内容检索出来的技术。
倒排索引
全文索引通常使用倒排索引(inverted index)
来实现,倒排索引和B+
树索引都是一种索引结构,它需要将分词(word)
存储在一个辅助表(Auxiliary Table)
中,为了提高全文检索的并行性能,共有6
张辅助表。辅助表中存储了单词和单词在各行记录中位置的映射关系。它分为两种:
inverted file index
(倒排文件索引),表现为{单词,单词所在文档ID
}full inverted index
(详细倒排索引),表现为{单词,(单词所在文档ID
, 文档中的位置)}对于这样的一个数据表:
倒排文件索引类型的辅助表存储为:
详细倒排索引类型的辅助表存储为,占用更多空间,也更好的定位数据,比提供更多的搜索特性:
全文检索索引缓存
辅助表是存在与磁盘上的持久化的表,由于磁盘I/O
比较慢,因此提供FTS Index Cache
(全文检索索引缓存)来提高性能。FTS Index Cache
是一个红黑树结构,根据(word, list)
排序,在有数据插入时,索引先更新到缓存中,而后InnoDB
存储引擎会批量进行更新到辅助表中。
当数据库宕机时,尚未落盘的索引缓存数据会自动读取并存储,配置参数innodb_ft_cache_size
控制缓存的大小,默认为32M
,提高该值,可以提高全文检索的性能,但在故障时,需要更久的时间恢复。
在删除数据时,InnoDB
不会删除索引数据,而是保存在DELETED
辅助表中,因此一段时间后,索引会变得非常大,可以通过optimize table
命令手动删除无效索引记录。如果需要删除的内容非常多,会影响应用程序的可用性,参数innodb_ft_num_word_optimize
控制每次删除的分词数量,默认为2000
,用户可以调整该参数来控制删除幅度。
全文检索限制
全文检索存在一个黑名单列表(stopword list)
,该列表中的词不需要进行索引分词,默认共有36
个,如the
单词。你可以自行调整:
mysql> select * from information_schema.INNODB_FT_DEFAULT_STOPWORD; +-------+ | value | +-------+ | a | | about | | an | | are | | as | | at | | be | | by | | com | | de | | en | | for | | from | | how | | i | | in | | is | | it | | la | | of | | on | | or | | that | | the | | this | | to | | was | | what | | when | | where | | who | | will | | with | | und | | the | | www | +-------+ 36 rows in set (0.00 sec)
其他限制还有:
● 每张表只能有一个全文检索索引
● 多列组合的全文检索索引必须使用相同的字符集和字符序,不了解的可以参考MySQL乱码的原因和设置UTF8数据格式
● 不支持没有单词界定符(delimiter)
的语言,如中文、日语、韩语等
全文检索
我们创建一个全文索引:
mysql> create fulltext index fullindex_remark on dimensionsConf(remark); Query OK, 0 rows affected, 1 warning (0.39 sec) Records: 0 Duplicates: 0 Warnings: 1 mysql> show warnings; +---------+------+--------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------+ | Warning | 124 | InnoDB rebuilding table to add column FTS_DOC_ID | +---------+------+--------------------------------------------------+ 1 row in set (0.00 sec)
全文检索有两种方法:
● 自然语言(Natural Language)
,默认方法,可省略:(IN NATURAL LANGUAE MODE)
● 布尔模式(Boolean Mode)
:(IN BOOLEAN MODE)
自然语言还支持一种扩展模式,后面加上:(WITH QUERY EXPANSION)
。
其语法为MATCH()...AGAINST()
,MATCH
指定被查询的列,AGAINST
指定何种方法查询。
自然语言检索
mysql> select remark from dimensionsConf where remark like '%baby%'; +-------------------+ | remark | +-------------------+ | a baby like panda | | a baby like panda | +-------------------+ 2 rows in set (0.00 sec) mysql> select remark from dimensionsConf where match(remark) against('baby' IN NATURAL LANGUAGE MODE); +-------------------+ | remark | +-------------------+ | a baby like panda | | a baby like panda | +-------------------+ 2 rows in set (0.00 sec) # 查看下执行计划,使用了全文索引排序 mysql> explain select * from dimensionsConf where match(remark) against('baby'); +----+-------------+----------------+----------+------------------+------------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------------+----------+------------------+------------------+---------+------+------+-------------+ | 1 | SIMPLE | dimensionsConf | fulltext | fullindex_remark | fullindex_remark | 0 | NULL | 1 | Using where | +----+-------------+----------------+----------+------------------+------------------+---------+------+------+-------------+ 1 row in set (0.00 sec)
我们也可以查看各行数据的相关性,是一个非负的浮点数,0
代表没有相关性:
mysql> select id,remark,match(remark) against('baby') as relevance from dimensionsConf; +-----+-----------------------+--------------------+ | id | remark | relevance | +-----+-----------------------+--------------------+ | 106 | c | 0 | | 111 | 运营商 | 0 | | 115 | a baby like panda | 2.1165735721588135 | | 116 | a baby like panda | 2.1165735721588135 | +-----+-----------------------+--------------------+ 4 rows in set (0.01 sec)
布尔模式检索
MySQL
也允许用修饰符来进行全文检索,其中特殊字符会有特殊含义:
+:
该word
必须存在-:
该word
必须排除(no operator):
该word
可选,如果出现,相关性更高@distance:
查询的多个单词必须在指定范围之内>:
出现该单词时增加相关性<:
出现该单词时降低相关性~:
出现该单词时相关性为负*:
以该单词开头的单词":
表示短语# 代表必须有a baby短语,不能有man,可以有lik开头的单词,可以有panda, select remark from dimensionsConf where match(remark) against('+"a baby" -man lik* panda' IN BOOLEAN MODE);
扩展查询
当查询的关键字太短或不够清晰时,需要用隐含知识来进行检索,如database
关联的MySQL/DB2
等。但这个我并没太明白怎么使用,后续补充吧。
类似的使用是:
select * from articles where match(title,body) against('database' with query expansion);
推荐学习:MySQL教程
以上是MySQL InnoDB索引原理和算法的详细内容。更多信息请关注PHP中文网其他相关文章!