当前位置：首页 > news >正文

Mysql的索引

news 文章来源：https://blog.csdn.net/m0_61916154/article/details/129262478 2024/11/23 15:30:32

为什么写这篇文章呢~最近在梳理公司的数据库，在查看表结构的时候发现了这个

CREATE TABLE `esp_5_N` (`ID` int(11) NOT NULL AUTO_INCREMENT,`pId` int(11) DEFAULT NULL,`EsFileId` varchar(32) DEFAULT NULL,`obligate1` varchar(45) DEFAULT NULL,`obligate2` varchar(45) DEFAULT NULL,`EssType` varchar(45) DEFAULT NULL,`Dept` varchar(20) DEFAULT NULL,PRIMARY KEY (`ID`),KEY `index_pid` (`pId`),KEY `index` (`EsFileId`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=472272 DEFAULT CHARSET=utf8;

然后一脸懵逼，心想是不是多主键，百度后发现是多索引~

1、索引的概念：

索引是一种用于快速查询和检索数据的数据结构。常见的索引结构有: B 树， B+树和 Hash。

2、为什么用索引

索引的作用就相当于书的目录。打个比方: 我们在查字典的时候，如果没有目录，那我们就只能一页一页的去找我们需要查的那个字，速度很慢。如果有目录了，我们只需要先去目录里查找字的位置，然后直接翻到那一页就行了。

3.索引类型

按数据结构分类：B+tree索引、Hash索引、Full-text索引。
按物理存储分类：聚集索引、非聚集索引（也叫二级索引、辅助索引）。
按字段特性分类：主键索引(PRIMARY KEY)、唯一索引(UNIQUE)、普通索引(INDEX)、全文索引(FULLTEXT)。
按字段个数分类：单列索引、联合索引（也叫复合索引、组合索引）。

4.主键索引：

数据表的主键列使用的就是主键索引。

一张数据表有只能有一个主键，并且主键不能为 null，不能重复。

在 MySQL 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引且不允许存在null值的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。

在Innodb下主键索引是聚集索引，在Myisam下主键索引是非聚集索引

后面的文章写存储引擎的介绍和区别

5.非聚集索引（也叫二级索引、辅助索引）：

非聚集索引的结构和聚集索引基本相同（非叶子结点存储的都是索引指针），区别在于叶子节点存放的不是行数据而是数据主键。因此在使用非聚集索引进行查找时，需要先查找到主键值，然后再到聚集索引中进行查找。

最左前缀匹配原则：

在使用联合索引时，MySQL 会根据联合索引中的字段顺序，从左到右依次到查询条件中去匹配，如果查询条件中存在与联合索引中最左侧字段相匹配的字段，则就会使用该字段过滤一批数据，直至联合索引中全部字段匹配完成，或者在执行过程中遇到范围查询。如 >、<、between 和 以%开头的like查询 等条件，才会停止匹配。

所以，我们在使用联合索引时，可以将区分度高的字段或场用于查询的字段放在索引的最左边，这也可以过滤更多数据。

6.唯一索引(UNIQUE)

建立在UNIQUE字段上的索引被称为唯一索引，一张表可以有多个唯一索引，索引列值允许为空，列值中出现多个空值不会发生重复冲突。

7.普通索引(INDEX)

建立在普通字段上的索引被称为普通索引。

8.全文索引(FULLTEXT)

MyISAM 存储引擎支持Full-text索引，用于查找文本中的关键词，而不是直接比较是否相等。Full-text索引一般使用倒排索引实现，它记录着关键词到其所在文档的映射。

9.单列索引

建立在单个列上的索引被称为单列索引。

10.B+tree索引

B+tree 是在B树基础上的一种优化，其更适合做存储索引结构。在 B+tree 中，非叶子节点上仅存储键值，不存储数据；而所有数据记录均存储在叶子节点上，并且数据是按照顺序排列的。此外在 B+tree 中各个数据页之间是通过双向链表连接的，叶子节点中的数据是通过单向链表连接的。

B+tree 结构实现数据索引具有如下优点：

a. 非叶子节点上可以存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树也就会变得更矮更胖。这样一来我们查找数据进行磁盘I/O的次数就会大大减少，数据查询的效率也会更快。

b. 所有数据记录都有序存储在叶子节点上，就会使得范围查找，排序查找，分组查找以及去重查找变得异常简单。

c. 数据页之间、数据记录之间都是通过链表链接的，有了这个结构的支持就可以方便的在数据查询后进行升序或者降序

11.Hash索引

Memory引擎默认支持哈希索引，如果多个Hash值相同，出现哈希冲突，那么索引就以链表方式存储。InnoDB或MyISAM存储引擎页支持Hash索引，但是需要通过伪Hash索引来实现，叫自适应Hash索引。

Hash索引是基于Hash算法实现的，我们将一系列的最终的键值通过哈希函数转化为存储实际数据桶的地址数值。值本身存储的地址就是基于哈希函数的计算结果，而搜索的过程就是利用哈希函数从元数据中推导出桶的地址。所以基于Hash索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B+Tree索引需要从根节点到枝节点，最后才能访问到页节点这样多次的I/O访问，所以Hash索引在精确查询时的效率要远高于B+Tree索引。虽然Hash索引效率高，

Hash索引本身由于其特殊性也带来了很多限制和弊端：

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

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

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

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

e. Hash索引遇到大量Hash值相等的情况后性能并不一定就会比B+Tree索引高

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

12.Full-text索引

Full-text索引一般使用倒排索引实现。倒排索引同B+tree索引一样，也是一种索引结构。

MySQL中InnoDB存储引擎在之前版本中是不支持全文检索的，要使用全文检索的话只能使用MySIAM存储引擎。在 MySQL 5.6.4 版本中InnoDB存储引擎才开始支持Full-text索引。

对于文本类型的大对象，或者较大的CHAR类型的数据，如果使用普通索引，那么匹配文本前几个字符还是可行的，但是想要匹配文本中间的几个单词，那么就要使用LIKE %word%来匹配，这样需要很长的时间来处理，响应时间会大大增加，这种情况，就可使用时FULLTEXT索引了，在生成FULLTEXT索引时，会为文本生成一份单词的清单，在索引时及根据这个单词的清单来索引。

Full-text索引的查询有自己特殊的语法，而不能使用 LIKE 模糊查询的语法，语法如下：

SELECT * FROM s MATCH(ft_index) AGAINST('word');