MySQL索引原理以及使用

下雨不打伞啊

已于 2022-07-11 21:06:15 修改

阅读量1.2k

点赞数

分类专栏： MySQL 文章标签：算法 mysql 数据库 b树 sql

于 2022-07-10 02:20:58 首次发布

本文链接：https://blog.csdn.net/weixin_39765162/article/details/125687597

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

前言

一、索引是什么？

MySQL中的索引是什么?
索引是为MySQL（当然，不仅仅是MySQL）检索数据而专门设计的一种特殊数据结构，目的旨在通过对表中数据之间的关系顺序进行维护，生成一组特殊的排序结构，从而提高数据查询效率，降低数据库IO成本。引用众多示例，把索引比作字典的目录，通过目录可以准确快速的定位到需要查找的”数据“。

注：以下所有内容主要是针对InnoDB存储引擎作为实例说明，不同的索引在不同存储引擎下可能会有差别。

优点

提高检索效率，降低磁盘IO成本
索引本身有序，可以显著提高order by 和 group by速度
提高表之间的连接速度
通过唯一索引（primary_key）可以保证数据的唯一性

缺点

索引本身也是数据，创建索引会占用额外的磁盘空间，尤其是表数据量比较大的时，索引也会随之变大。
对数据库的写入操作会导致索引的重新维护，故会降低数据的写入性能，并且随着数据量增大，维护成本进一步提高。所以对于一些read操作明显大于write操作的表更适合为其建立合适的索引。

二、索引的数据结构

1.哈希索引

使用哈希索引也能提高数据的查询速度，哈希索引是建立在哈希表的基础上，通过对索引列建立哈希码，保存在索引中，并且会保存一个指向哈希表的指针，这样可以很快的定位到符合要求的数据行上。虽然查询性能很高，但存在很多天生的缺陷，如下：

索引只包含哈希码和行指针，而不是值自身，MySQL不能使用索引中的值来避免读取行
MySQL不能使用哈希索引进行排序，因为他们本身就不会按序保存行。
哈希索引不支持部分键匹配，例如（A,B）两列上有索引，如果where子句中只使用了A，索引就不会起作用
索引只能实现精确查找，不能实现范围查询
处置不当，当发生碰撞的时候，存储引擎不得不访问链表中的每一个行指针，逐行比对数据，以获取正确的数据
在碰撞性很高的列上创建哈希索引，然后从表中删除一列，那么从索引中找到行的代价就会很高，存储引擎不得不检查哈希键链表中的每一行，以找到和移除被删除行的索引。

2.B-Tree索引

B-Tree是一种多路自平衡查找树，与AVL、红黑树不同的是，每个节点可以存储更多的数据，允许有多个子节点，简单示例图如下：
摘自百度 B-Tree具备以下特性：

所有的key以及对应的数据分布在整个树中
一个key只能出现在一个节点中
搜索可能会提前结束，比如查找17，仅需要一次IO
任何一条数据的整体查找速度接近于log(N)

以上图为例，每一个B-Tree除了保存两个Key值以外，还会保存3个指向下一个磁盘块地址的指针，以便通过顺序对不同的磁盘块进行多次IO操作。
比如查询key=3的数据，首先找到root节点，读取该磁盘块，3<17，使用p1指针继续读取磁盘块2，3<8，紧接着继续使用指针读取磁盘块5，然后获取到数据，通过3次IO操作，就可以获取到目标数据3。

二分伪代码：

Data* BTreeSearch(Root *node, Key key)
{
    Data* data;
    if(root == NULL)
        return NULL;
    data = BinarySearch(node);
    if(data->key == key)
    {
        return data;
    }else{
        node = ReadDisk(data->next);
        BTreeSearch(node, key);
    }
}

3.B+Tree

B+Tree是B-Tree树的一种变种，B-Tree支持的它都支持，整体来看，他要比B-Tree更适合作为数据库的索引结构，MySQL索引也是采用B+Tree实现的，以下是B树的简单抽象：
摘自百度

从以上图可以看出与B-Tree主要的区别是：

B+Tree树的非叶子节点不会存储数据，被索引的数据全部存储在叶子节点上
所有叶子节点之间通过双向链表结构进行连接
每次查询都必须到叶子节点上，更稳定

为什么MySQL采用B+Tree而不采用B-Tree作为索引的实现？
单个元素越小，量就越大。这就意味着B+树单次磁盘 IO 的信息量大于B-树

非叶子节点不存储数据，只存储索引，则意味着相同的磁盘块B+Tree可以存储更多的索引key，单次IO获取的索引数据就要大于B-Tree，可以有效的降低树的高度，相对B-Tree，B+Tree更矮更胖，这也就会减少磁盘IO的次数。
数据有序存储在叶子节点上，通过双向链表进行连接，根据空间有限性原理，可以提高局部访问性能，更方便进行范围查找，而B树每个节点的key和数据存储在一起，分布在整个树中，对范围查找并不友好。
B-Tree数据分布在整个树中，查找数据有可能一次查询到，也有可能多次，效率不稳定，而B+Tree每次查询数据都会到叶子节点上才有可能获取到数据，查询效率相对B-Tree更加稳定。

2.聚集索引和非聚集索引

1.聚集索引

聚集索引不是一种单独的索引类型，而是一种存储数据的方式。
一般来说，聚集索引在mysql下的体现就是主键索引，以InnoDB聚集索引的主键索引来说，叶子节点中存储的就是行数据，也就是数据即索引，索引即数据。
行数据在物理储器中的真实地址就是按照主键索引树形成的顺序进行排列的，所以查询效率快，只要找到第一个索引值记录，其余就连续性的记录在物理也一样连续存放。聚集索引对应的缺点就是修改慢，因为为了保证表中记录的物理和索引顺序一致，在记录插入的时候，会对数据页重新排序。
摘取《高性能MySQL》中对聚集索引优缺点的说明：
摘自高性能MySQL

聚集索引在一个表中只能有一个，因为在物理内存中的顺序只能有一种。

2.非聚集索引

顾名思义，聚集索引之外的索引就是非聚集索引（也叫二级索引），也就是我们表中除主键之外的其他列索引。非聚集索引制定了表中记录的逻辑顺序，但是记录的物理和索引不一定一致（在逻辑上数据是按顺序排存放的，但是物理上在真实的存储器中并不一定连续）
两种索引都采用B+树结构，非聚集索引的叶子层并不和实际数据页相重叠，而采用叶子层包含了索引数据记录行的主键值。

因为非聚集索引的叶子节点不再存储具体的数据行，而是数据的聚集索引的key，所以通过非聚集索引查找的过程是先找到该索引key对应的聚集索引key，然后再拿着聚集索引的key到主键索引树上查找对应的数据，进行两次索引，这个过程称之为“回表”。

这也就说明了为什么我们使用索引是应尽可能使用覆盖索引，因为使用覆盖索引无需进行第二次索引，不需要访问数据行，直接通过索引树就可以返回我们想要的数据

ps：覆盖索引，查询的数据列恰好能够匹配创建的索引列。

3.使用索引需要注意的问题

以该实例说明：

CREATE TABLE `tbl_user` (
	`id` INT UNSIGNED AUTO_INCREMENT,
	`name` VARCHAR ( 128) NOT NULL,
	`age` TINYINT NOT NULL,
	`phone` VARCHAR ( 32 ) NOT NULL,
	`address` VARCHAR ( 255 ),
PRIMARY KEY ( `id` ) 
) ENGINE = INNODB DEFAULT CHARSET = utf8;
CREATE INDEX name_age_phone_idx ON tbl_user (name, age, phone);

1.首先id自增，InnoDB会自动使用该主键建立聚集索引
2.使用name、age、phone创建名为name_age_phone_idx组合索引，这个二级索引的叶子节点包含了name , age , phone和id的值
3.当使用该索引进行检索时：

首先会按照name进行排序，
如果name值相同，则会按照age进行排序，
如果age也相同，则会按照phone进行排序

全值匹配：

全值匹配是指where子句条件与索引列一致：

SELECT * from tbl_user where name = 'Jerry' and age = 18 and phone = '1234567';

如上这三列索引都可能被用到，这比较简单，地球人都知道。

前缀匹配

SELECT * FROM tbl_user where name like 'Tom%';

对于只匹配字符串前缀也是用到索引，快速定位目标记录的。因为字符串比较本质上也是逐个字符进行比较，字符串已经在索引中已经排好序，是可以使用索引查询的。
但是像这种情况索引就无能为力了：

SELECT * FROM tbl_user where name like '%Tom%';
SELECT * FROM tbl_user where name like '%Tom';

因为前边的字符是不确定的，对于前缀不确定的字符存储引擎没有办法使用已经排好序的索引进行检索，如果真的遇到这种情况，可以考虑将字符串倒序存储

范围匹配

因为索引本身已经按照name, age , phone完成排序，因此一般的范围查找也可以使用索引

SELECT * FROM tbl_user where age >;

精确匹配某一列后，再进行范围匹配同样会使用索引，例如

SELECT * FROM tbl_user where name = 'Jerry' and age > 18;

但是如果先匹配的是范围，再精确查找，这会导致查询范围条件之后的其他列索引失效，如下

SELECT * FROM tbl_user where name = 'Tom' and age > 18 and phone = '138xxxxxxxxxx';

这很好理解，因为索引是按照name,age,phone查找的，name相同会使用age排序，age相同再使用phone。而上边的例子中，当出现age > 18后，这里的条件是一个范围，查询到的记录可能不同，就会导致后边的phone索引没办法使用。

用于排序

在实际业务场景中，经常需要对查询出来的结果进行排序。一般情况下，只能将记录全部加载到内存中（结果集太大可能使用磁盘存放中间结果），再使用排序算法排序。这种在内存中或者磁盘上的排序方式统称为文件排序filesort，性能较差。但是如果order by子句使用到了索引列，就可能避免filesort。比如下面这个查询语句：

SELECT * FROM tbl_user order by name , age, phone;

查询结果依次按照name ,age , phone排序，刚好索引也是按照该顺序建立，因此，就可以直接从索引上提取数据，然后进行回表即可，相反，如果order by顺序与索引创建规则不一致，也没有办法使用索引。

还有一种情况，如下：

SELECT * FROM tbl_user where name = 'zhangsan' order by name,age,phone; (1)
SELECT * FROM tbl_user where name > 'zhangsan' order by name,age,phone; (2)
SELECT * FROM tbl_user where name > 'zhangsan' order by age,phone; (3)

(1)(2)where子句是已经确定为常量或者已经确认范围，而且在该范围内按照索引的顺序排序，也就是useing index。(3)是不可以的，因为name > 'zhangsan’是一个范围，且order by子句不符合索引建立规则，mysql无法确定这个范围是否是已经按照name完成了排序，所以直接使用age phone排序将没有意义，这种情况就会使用filesort进行排序。

用于分组

分组其实和排序类似，如下边语句：

SELECT name,age,phone,count(1) from tbl_user group by name,age,phone;

只要分组字段与索引建立规则保持一致，也会使用索引结构进行分组，当然，使用group by name也是可以的，但是像group by age,name这明显是不可以的，因为它不符合索引结构的“规则”。

覆盖索引

上面提到到，所谓回表就是在二级索引中获取到主键id集合之后，再分别到聚簇索引查询出完整记录，简单来说就是一次二级索引查询，多次聚簇索引回表。这意味着二级索引命中的主键记录越多，需要回表的记录也会也多，整体的性能就会越低。因此某些查询，宁可使用全表扫描也不使用二级索引。

为了更好的使用二级索引+回表的方式进行查询，一般推荐使用limit限制要查询的记录，这样回表的次数也能得到控制。为了彻底告别回表操作带来的性能损耗，建议：在查询列表里只包含索引列，比如这样：

SELECT name,age,phone FROM tbl_user where name = 'Steve';

因为这种查询，返回的列就是索引列，根本不需要进行回表操作，直接在二级索引树中就可以获取到我们需要的数据。

表达式计算

如果索引列在比较表达式中不是以单独列的形式出现，而是以某个表达式，或者函数调用形式出现的话，也是用不到索引的

SELECT * FROM tbl_user where name = 'Andy' and 1 + age = 18;

这也会导致索引无法生效， MySQL没有那么聪明，会去帮忙计算表达式，
一些特定的工具函数，类型不一致问题，编码不一致问题都会导致无法使用索引
如果理解了索引的结构和原理，以上这些情况是很容易理解的。

4.如何挑选索引

为用于搜索，排序和分组的列添加索引

只为经常用于where、order by和group by子句的列添加索引，而查询列表中没必要设计索引，即使会进行回表操作，但不能为了避免回表而过度增加索引，这会导致索引文件及其庞大，物极必反，合理的添加索引才是需要认真考虑的问题。

考虑列的基数

列的基数指的是某一列中不重复数据的个数。，在记录行数一定的情况下，列的基数越大，该列中的值越分散，列的基数越小，该列中的值越集中。因此推荐的方式是为那些列的基数大的列建立索引，为基数太小的列建立索引不会完全发挥索引的真正作用，就像拳头打在棉花上一样。

索引列的类型尽量小

在表示数据范围允许的情况下，尽量让索引使用较小的类型，原因如下：

数据类型越小，在查询时进行的比较操作越快
数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘I/O带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。

使用前缀索引

当字段值比较长的时候，建立索引会消耗很多的空间，搜索起来也会很慢。我们可以通过截取字段的前面一部分内容建立索引，这个就叫前缀索引。例如在tbl_user的address字段上建立前缀索引：

alter table tbl_user add key(address(7));

以上截取了用户地址信息的前7个字符作为索引，但是7个未必是最合适的，这需要根据实际的数据分布来估算，估算方法如下：
首先计算全列的选择性，并使前缀的选择性接近他

SELECT count(distinct address) / count(*) from tbl_user;

比如以上语句计算得到的结果为0.34，在通过不断测试不同长度的前缀来计算选择率，如下

SELECT count(distinct LEFT(address,3)) / count(*) as t1;
SELECT count(distinct LEFT(address,4)) / count(*) as t2;
SELECT count(distinct LEFT(address,5)) / count(*) as t3;
SELECT count(distinct LEFT(address,6)) / count(*) as t4;
SELECT count(distinct LEFT(address,7)) / count(*) as t5;
....

通过上述方式不断对不同前缀进行测试，找到最接近0.34的结果，此时的前缀就是相对比较合适的前缀，当然，与此同时也要综合考虑索引列的长度问题。

面对长字符或者长文本索引时，还可以通过建立伪哈希索引，即使很长的的键也只有很小的索引，比如为tbl_user表增加address_hash字段，插入数据时会出现类似如下情况：

insert into tbl_user (id,name,age,phone,address,address_hash) values (65535,'村口王大爷',99,'四川成都市成华区成华大道二仙桥187号',CRC32('四川成都市成华区成华大道二仙桥187号'));

当然也可以使用触发器或者其他方式完成该操作，也不仅限于使用CRC，也可以实现自己的算法，来获取一个较短的hash值。
那么如此，就可以通过在address_hash上建立索引，完成对addres字段的检索，查询语句就会变成这个样子：

SELECT * FROM tbl_user where address_hash = CRC32('四川成都市成华区成华大道二仙桥187号');

使用这种方式需要注意碰撞的问题，如果出现碰撞，可以如此：

SELECT * FROM tbl_user where address_hash = CRC32('四川成都市成华区成华大道二仙桥187号') and address = '四川成都市成华区成华大道二仙桥187号';

先到这里吧，以上就是MySQL索引相关的一些知识，如果有什么缺漏，还会及时补充上去。

总结

没事干想写写博客，平时阅读实践中逐渐累积出的知识拿出来简单做下总结。能力尚浅，不足之处请及时指出。
部分内容摘自：
https://baijiahao.baidu.com/s?id=1674598698091841337&wfr=spider&for=pc

下雨不打伞啊

关注

0
点赞
踩
10

收藏

觉得还不错? 一键收藏
1
评论
MySQL索引原理以及使用

MySQL中的索引是什么?索引是为MySQL（当然，不仅仅是MySQL）检索数据而专门设计的一种特殊数据结构，目的旨在通过对表中数据之间的关系顺序进行维护，生成一组特殊的排序结构，从而提高数据查询效率，降低数据库IO成本。引用众多示例，把索引比作字典的目录，通过目录可以准确快速的定位到需要查找的”数据“。注：以下所有内容主要是针对InnoDB存储引擎作为实例说明，不同的索引在不同存储引擎下可能会有差别。使用哈希索引也能提高数据的查询速度，哈希索引是建立在哈希表的基础上，通过对索引列建立哈希码，保存在索引中
复制链接

扫一扫