索引的分类


索引是在存储引擎中实现的,不同的存储引擎会使用不同的索引。

  • MyISAM 和 InnoDB 只支持BTree索引
  • MEMORY 和 HEAP 支持hash索引和Btree索引

索引分为四类

  1. 单列索引(普通索引,唯一索引,主键索引)
    普通索引:没什么限制,允许插入空值和重复值
    唯一索引:不允许重复值,允许空值
    主键索引:不允许空值,不允许重复值
  2. 组合索引(联合索引)
    遵循最左前缀原则
    eg:(a,b,c)创建索引,共有(a),(a,b),(a,b,c)三种索引
  3. 全文索引
    InnoDB,MyISAM引擎都能用,只能在CHAR,VARCHAR,TEXT类型的字段上加该索引。主要用来查找文本中的关键字,而不是直接与字段值进行比较,全文索引需要配合match against使用,而不是一般的where like…
  4. 空间索引
    是对空间数据类型的字段建立的索引,空间索引的列必须是not null,只有MyISAM支持该索引

索引在MySQL中的分类

  • B+树索引
  • Hash索引
  • 全文索引

B树

因为内存的易失性,一般情况下,都会将表中的数据和索引存储在磁盘这种外围设备中,但是和内存相比,从磁盘中读取数据的速度会慢上千倍,从磁盘中读取数据都是按照磁盘块来读,并不是一条一条的读。
如果能尽量的把更多的数据放进磁盘里,那么一次读取就能获取更多数据,但是二叉树每个节点就存储一个键值对,如果用这种结构存储,那么每次读取一个键盘只能读到一个键值对,如果存储海量数据,占用磁盘过多,效率过慢。
为了解决这个弊端,创建了B树,B树是一个节点可以存储多个键值对的平衡树。
在这里插入图片描述

图中每个节点称为页,也就是磁盘块,在MySQL中读取的基本单位都是页。
假如要查找id=9 的数据:
1、先找到根节点,发现9小于17,根据指针P1,找到页2
2、将9和页2中的数据进行标胶发现9在8和12之间,根据P2找到页6
3、将9和页6中的键值进行比较,找到(9,ac)

B+树

B+树 和 B树的区别
1、B+树非叶子节点是不存储数据的,仅存储键值,因为数据库中页的大小是固定的,InnoDB默认是16k,如果不存储数据,就会存储更多的键值,另外,B+树的阶数等于键值的数量,如果B+树的一个节点可以存储1000个键值,那么三层的B+树可以存储100010001000 = 10亿个数据。
一般节点是常驻内存的,所以一般查找10亿数据只需要两次IO。
2、B+树的所有数据都存储在叶子节点,而且数据时按照顺序排列的,这就使得B+树的范围查找,排序查找,分组查找,以及去重查找变得简单。
在这里插入图片描述

B+树各个页之间通过双向链表连接的,叶子节点之间数据通过单向链表连接的,通过这些连接可以找到表中所有数据。

聚集索引和非聚集索引

  • 聚集索引:
    以InnoDB作为存储引擎的表,即使你不创建主键,系统也会帮你创建一个隐式的主键。因为InnoDB把数据存放在B+树中,而B+树的键值就是主键。这种以主键作为键值而构建的B+树索引称为聚集索引。

  • 非聚集索引:
    以主键之外的列值作为键值构建的B+树索引。与聚集索引的区别在于,叶子节点不存储表中的数据,而是存储该列对应的主键,想要查询数据还需要根据主键再去聚集索引中查找,称之为回表
    叶子节点存储结构:
    在这里插入图片描述
    非聚集索引不一定进行回表操作,如果查询的字段全部命中了索引,就不必进行回表操作。
    eg:select age from user where age<20
    在索引上已经包含了age信息,不需要回表查询。

Hash索引

利用哈希函数h(k)计算出元素的位置
在这里插入图片描述
Hash索引的检索效率很高,索引的减速偶可以一次定位,不像B树索引需要从根节点到枝节点经过多次IO。
Hash索引缺点:
1、Hash只支持等值比较查询,如 = ,IN,<=>。不支持任何范围查询,因为Hash索引比较的值是经过hash运算之后的值,这些值的大小并不能保证和Hash运算之前完全一样。
2、Hash也无法进行排序操作,原因同上。
3、Hash索引在任何时候都不能避免表扫描,由于不同索引键存在相同hash值,所以即使满足某个hash值,也无法完成直接查询,而是要访问表中的数据进行比较。
4.不支持联合索引最左匹配原则。

MySQL中InnoDB和MyISAM的区别

  • InnoDB支持事务,MyISAM不支持
  • InnoDB支持外检,MyISAM不支持
  • InnoDB是聚集索引,MyISAM是非聚集索引
  • InnoDB不支持全文索引,MyISAM支持

MySQL中存在索引但是不被使用的情况

1、用“or”分隔开的两个条件,如果有一个条件没有索引,则不会使用索引
2、如果like以“%”开头,则不会使用索引
3、如果列类型为字符串,则where条件中该常量值必须加引号,否则索引不起作用
4、对索引列进行数学运算或者函数,索引不起作用
eg:select * from … where id-1=9
select * from … round(id) = 10

<think>嗯,用户想了解MySQL索引的不同类型及其用途,特别是分类和每种类型的详细说明。首先,我需要回忆一下MySQL支持的索引类型,根据之前的引用,主要有FULLTEXT、HASH、BTREE和RTREE四种,不过可能还有其他分类方式,比如主键索引、唯一索引等。 首先,我需要明确索引分类方式。引用2提到FULLTEXT、HASH、BTREE、RTREE,但引用3中的CREATE INDEX语句包括UNIQUE、FULLTEXT、SPATIAL,这可能指的是索引的类型或属性。可能用户需要的是按存储结构分类和按功能分类两种维度。 按存储结构分类的话,应该是HASH、BTREE、RTREE这些,而按功能或约束条件分类的话,可能是主键索引、唯一索引、普通索引、全文索引、空间索引等。需要结合引用中的信息来组织。 比如,FULLTEXT对应全文索引,HASH和BTREE是存储方式,而UNIQUE和SPATIAL可能属于约束类型。可能需要分开说明,先按功能分类,再说明存储结构的不同。 另外,每种索引的用途需要详细说明,比如HASH索引适用于等值查询,但不支持范围查询;BTREE是默认的,支持范围查询和排序。全文索引用于文本搜索,空间索引用于地理数据。 需要注意引用中的内容,引用2提到四种索引类型,而引用3中的CREATE INDEX语句中的UNIQUE、FULLTEXT、SPATIAL可能是指索引的类型,而USING后面接的是index_type,如BTREE或HASH。因此,可能需要将索引分为功能类型(如唯一索引、主键索引、全文索引、空间索引)和存储结构类型(BTREE、HASH、RTREE)。 需要确保信息准确,结合MySQL官方文档的内容,比如InnoDB的默认索引结构是BTREE,而MEMORY引擎支持HASH索引。同时,主键索引是一种特殊的唯一索引,不允许NULL值,而唯一索引允许NULL值。 可能还需要提到组合索引,以及索引的最佳实践,比如最左前缀原则。不过用户的问题主要是分类和每种类型的说明,所以可能需要分点回答,先按功能分类,再按存储结构分类,并说明每种类型的特点、适用场景和示例。 需要检查是否有遗漏的类型,比如SPATIAL索引,即空间索引,用于地理数据类型,必须使用RTREE结构,并且只能在MyISAM引擎中使用(不过MySQL 5.7之后的InnoDB也支持空间索引?可能需要确认)。 总结下来,回答的结构应该是: 1. 按功能分类: - 主键索引 - 唯一索引 - 普通索引 - 全文索引 - 空间索引 - 组合索引 2. 按存储结构分类: - BTREE索引 - HASH索引 - RTREE索引(空间索引) 然后详细说明每种类型的特点、创建方式、适用场景,并引用提供的参考文献中的信息,如引用2提到的四种类型,以及引用3中的CREATE INDEX语法。 需要确保LaTeX格式正确,比如变量使用$...$,如索引字段为$col1$,并在适当的位置添加引用标记,如[^2][^3]。</think>### MySQL索引分类及详细说明 #### 一、按功能分类 1. **主键索引(PRIMARY KEY)** - **特点**:唯一且非空,每张表只能有一个主键索引。 - **存储结构**:默认使用$B^+$树(BTREE)结构。 - **用途**:确保数据唯一性并加速查询。 - **示例**: ```sql CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(20)); ``` 2. **唯一索引(UNIQUE INDEX)** - **特点**:字段值唯一,允许有空值。 - **存储结构**:支持BTREE或HASH(取决于存储引擎)[^3]。 - **用途**:防止数据重复,优化等值查询。 - **示例**: ```sql CREATE UNIQUE INDEX uni_email ON users(email); ``` 3. **普通索引(INDEX)** - **特点**:无唯一性约束,可重复创建。 - **存储结构**:默认使用BTREE。 - **用途**:加速条件查询和排序。 - **示例**: ```sql CREATE INDEX idx_age ON users(age); ``` 4. **全文索引(FULLTEXT INDEX)** - **特点**:针对文本内容分词检索,仅支持`CHAR`/`VARCHAR`/`TEXT`类型。 - **存储结构**:使用倒排索引结构。 - **用途**:模糊匹配和关键词搜索,如`MATCH(col) AGAINST("keyword")`[^1]。 - **限制**:仅适用于MyISAM和InnoDB(MySQL 5.6+)。 - **示例**: ```sql CREATE FULLTEXT INDEX ft_content ON articles(content); ``` 5. **空间索引(SPATIAL INDEX)** - **特点**:专用于地理空间数据(如`GEOMETRY`类型)。 - **存储结构**:使用RTREE结构[^2]。 - **用途**:地理范围查询(如地图应用)。 - **限制**:仅MyISAM引擎支持(InnoDB从MySQL 5.7.5开始实验性支持)。 - **示例**: ```sql CREATE SPATIAL INDEX sp_loc ON maps(coordinates); ``` 6. **组合索引(复合索引)** - **特点**:基于多个字段联合创建,遵循最左前缀原则。 - **用途**:优化多条件查询。 - **示例**: ```sql CREATE INDEX idx_name_age ON users(name, age); ``` --- #### 二、按存储结构分类 1. **BTREE索引** - **原理**:基于多路平衡搜索树,支持范围查询(如$>$, $<$)和排序操作。 - **适用场景**:大多数场景(InnoDB默认索引类型)。 - **优势**:时间复杂度稳定为$O(\log n)$[^1]。 2. **HASH索引** - **原理**:通过哈希函数计算键值位置,仅支持等值查询($=$)。 - **适用场景**:内存表(MEMORY引擎)或精确匹配查询。 - **缺点**:不支持范围查询和排序。 3. **RTREE索引** - **原理**:用R树结构存储多维数据(如地理坐标)。 - **适用场景**:空间数据检索(与SPATIAL索引绑定)。 --- #### 三、索引选择建议 1. **高频查询字段**优先建索引。 2. **区分度高**的字段(如用户ID)更适合索引。 3. 避免对**频繁更新**的字段建过多索引。 4. 组合索引字段顺序需按**查询频率**和**区分度**排列。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值