【MySQL 索引类型--比较全】

奇怪的逻辑

已于 2023-03-04 22:07:52 修改

阅读量641

点赞数

文章标签： mysql 数据结构 java

于 2023-03-04 13:59:42 首次发布

本文链接：https://blog.csdn.net/weixin_47628154/article/details/129172702

版权

MySQL 索引学习

MySQL索引类型总结:

按照物理存储区分：聚簇索引，非聚簇索引（二级索引）；

按照存储结构区分：BTree索引（B树索引），Hash索引（哈希索引），R-Tree索引（R树索引，空间索引），Full-Text（全文索引）;

按照字段数量区分：单列索引，联合索引（覆盖索引，复合索引）；

按照字段属性区分：唯一索引，主键索引，普通索引，全文索引，外键索引，空间索引；

MySQL索引类型详细说明：

物理存储

聚簇索引：InnoDB中默认主键使用聚簇类型，如果表中没有定义主键，InnoDB 会选择一个**唯一的非空索引**代替。如果没有这样的索引，InnoDB 会**隐式定义一个主键**来作为聚簇索引。

非聚簇索引：MyISAM中默认主键使用的非聚簇类型；

聚簇索引和非聚簇索引的主要区别在于它们对数据行物理存储顺序的影响。聚簇索引的物理存储顺序和索引顺序一致，因此查询效率高，但插入和更新数据会导致数据行的移动，因此需要更多的时间和空间成本；而非聚簇索引的物理存储顺序和索引顺序不一致，查询效率相对较低，但插入和更新数据不会影响数据行的物理存储顺序。在实际应用中，根据具体的业务需求和查询场景，需要选择合适的索引类型。

存储结构

BTree索引（B树索引分两种介绍）：

B-Tree索引是一种自平衡的树型数据结构，用于快速查找和排序大量的数据。在B-Tree索引中，每个节点包含多个数据项和指向子节点的指针，通过在不同层次的节点之间移动，可以快速查找和访问数据。

B+Tree索引是B-Tree索引的一种变体，它通过将所有数据存储在叶子节点中，从而提高了索引的性能。在B+Tree索引中，非叶子节点仅包含键值和指向子节点的指针，而叶子节点包含指向数据的指针。这种设计使得B+Tree索引更适合于支持范围查询和排序，同时可以更有效地利用缓存。

总的来说，B+Tree索引相对于B-Tree索引来说，更适合于支持范围查询和排序，能够更有效地利用缓存和减少磁盘I/O操作，因此在实际应用中更加常用。

Hash索引：

Hash索引是一种高速的索引类型，它通过Hash算法将索引键映射为索引值，然后通过索引值快速查找数据。Hash索引适用于等值查询（即“=”查询），不适用于范围查询（如“<”、“>”、“BETWEEN”等）。Hash索引的缺点是无法排序，也无法支持模糊查询。

Full-Text索引（全文索引）：

在MySQL中，Full-Text索引在InnoDB和MyISAM存储引擎中有一些不同。

在MyISAM中，Full-Text索引是一种自然语言全文搜索索引，支持基于自然语言的搜索、布尔搜索和短语搜索，可以在全表扫描的情况下快速定位符合条件的行。此外，MyISAM支持实时更新Full-Text索引。

而在InnoDB中，Full-Text索引从MySQL 5.6.4版本开始支持，不过相比于MyISAM，InnoDB的Full-Text索引有以下不同：

InnoDB的Full-Text索引只支持英文和中文的全文搜索，不支持其他语言的全文搜索。

InnoDB的Full-Text索引默认不启用，需要通过设置参数innodb_ft_enable_stopword来启用，同时还需要使用ALTER TABLE语句来创建Full-Text索引。

InnoDB的Full-Text索引不支持实时更新，需要使用FTS_DOC_ID语句来批量更新Full-Text索引。

InnoDB的Full-Text索引性能相对MyISAM较差，在查询较大数据量的表时，查询性能可能会变得较慢。

因此，在选择使用Full-Text索引时，需要根据具体的业务场景和数据特点进行选择。如果需要支持多语言全文搜索，并且不需要实时更新索引，可以考虑使用MyISAM存储引擎；如果需要支持英文和中文的全文搜索，并且需要使用InnoDB存储引擎，则可以考虑使用InnoDB的Full-Text索引。

R-Tree索引：

R-Tree索引(空间索引)是一种特殊的索引类型，用于存储地理数据，包括点、线、多边形等，可以进行空间查询和空间关系运算。在MySQL中，空间索引在InnoDB和MyISAM存储引擎中有一些不同。

在MyISAM中，空间索引使用RTree数据结构进行存储，可以使用MySQL提供的空间函数来创建和查询空间索引。同时，MyISAM也支持空间索引的实时更新。

而在InnoDB中，空间索引从MySQL 5.7版本开始支持，使用的数据结构也是RTree，可以使用MySQL提供的GIS函数来创建和查询空间索引。不过，InnoDB的空间索引需要手动开启，需要在创建表时指定ROW_FORMAT=DYNAMIC，同时还需要使用ALTER TABLE语句来创建空间索引。此外，InnoDB的空间索引不支持实时更新，需要使用REORGANIZE PARTITION语句来重构空间索引。

另外，需要注意的是，MyISAM和InnoDB对于空间数据的存储方式也有所不同。在MyISAM中，空间数据可以存储在表的任意列中，而在InnoDB中，空间数据必须存储在一个名为“geometry”的列中。

因此，在选择使用空间索引时，需要根据具体的业务场景和数据特点进行选择。如果需要使用空间索引，可以考虑使用MyISAM存储引擎；如果需要使用InnoDB存储引擎，则需要注意开启动态行格式，使用ALTER TABLE语句手动创建空间索引，同时注意空间数据存储在“geometry”列中。

字段数量

单列索引（Single Column Index）：对单个列创建索引，可以加快该列的查询速度。常用于对经常被查询的列进行索引。

联合索引（Composite Index）：也叫组合索引，对多个列创建索引，可以提高多个列的查询速度。常用于多个列同时作为查询条件的场景。

不同类型的索引适用于不同的场景，需要根据实际需求进行选择。比如单列索引适合经常被查询的单个列，联合索引适合多个列同时作为查询条件的场景。

字段属性

普通索引（Normal Index）：最基本的索引类型，没有任何限制。它可以对表中的任何一列或多列创建索引。
唯一索引（Unique Index）：创建索引时，要求该索引列的每个值都必须唯一，可以用于保证数据的完整性和唯一性。
主键索引（Primary Key Index）：主键是一种特殊的唯一索引，它用于唯一标识表中的每一行数据，并且每张表只能有一个主键索引。
外键索引（Foreign Key Index）：用于建立表与表之间的关联，可以保证数据的完整性和一致性。外键索引的建立必须依赖于相应的主键索引。
全文索引（Fulltext Index）：主要用于对文本类型的数据进行全文搜索，可以支持模糊查询、关键词搜索等操作。
空间索引（Spatial Index）：主要用于地理信息系统（GIS）等领域，支持对空间数据进行索引和查询，如点、线、面等。

MySQL索引应用场景：

主键索引：主键索引是一种唯一性索引，适用于对表的主键列进行查询的场景。
唯一索引：唯一索引适用于需要保证列值唯一的场景，比如电子邮件地址、用户名等。
普通索引：普通索引适用于基本的查询场景，可以提高查询效率。
全文索引：全文索引适用于文本内容的搜索和匹配，比如对博客文章进行关键词搜索。
组合索引：组合索引适用于多列查询场景，可以减少查询的数据行数，提高查询效率。
空间索引：空间索引适用于地理位置、二维坐标等空间数据的查询。
前缀索引：前缀索引适用于对较长的字符串进行查询的场景，可以减少索引的存储空间，提高查询效率。
外键索引：用于建立表与表之间的关联，可以保证数据的完整性和一致性。外键索引的建立必须依赖于相应的主键索引
函数索引：它基于一个表达式或函数而不是列上的值来建立索引。函数索引允许您在查询中使用函数，而不需要在查询中使用计算列或者临时表来保存函数结果，从而简化了查询和应用程序代码，例如为了方便查询每个订单下单时间的小时数，可以创建一个函数索引

mysql索引创建：

CREATE [UNIQUE] INDEX index_name ON table_name (column_name1 [, column_name2, …]);

其中，index_name 为索引名称，
table_name 为需要创建索引的表名，
column_name 为需要创建索引的列名。

该语句用于在表 table_name 中创建一个名为 index_name 的索引，该索引包含列 column_name1，column_name2，等等。如果在创建索引时使用了 UNIQUE 选项，则该索引不允许重复值。

案例：

假设有一个用户表 user，包含以下列：

id: 用户ID，整数类型，主键自增
name: 用户名，字符串类型，长度不超过50
email: 邮箱，字符串类型，长度不超过100
age: 年龄，整数类型
mobile: 手机号，字符串类型固定，长度11
create_time: 创建时间，时间类型

CREATE TABLE user (
  id INT(11) NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL,
  age INT(11),
  mobile VARCHAR(11),
  create_time DATETIME NOT NULL,
  PRIMARY KEY (id)
);

测试数据：

INSERT INTO user (name, email, age, mobile, create_time)
VALUES ('Alice', 'alice@example.com', 28, '13812345678', NOW()),
       ('Bob', 'bob@example.com', 30, '13987654321', NOW()),
       ('Charlie', 'charlie@example.com', 25, '13698765432', NOW()),
       ('David', 'david@example.com', 35, '13712345678', NOW()),
       ('Eva', 'eva@example.com', 26, '13587654321', NOW());

多列索引

如果我们希望通过用户名和邮箱查询用户信息，可以创建一个多列索引：

CREATE INDEX idx_user_name_email ON user (name, email);

唯一索引

给年龄创建普通索引：

CREATE  INDEX idx_user_age ON user (age);

普通索引

给手机号创建唯一索引，必须确保数据没有重复的有重复的会报错：

CREATE UNIQUE INDEX idx_mobile ON user (mobile);

函数索引

可以使用以下方式针对create_time列添加一个函数索引来加一天(我MySQL版本8.0.28 建立函数索引报错没查到原因 )

CREATE INDEX idx_user_create_time_plus_one_day ON user (DATE_ADD(create_time, INTERVAL 1 DAY));

执行这个sql也没问题
select DATE_ADD(u.create_time, INTERVAL 1 DAY),u.create_time from user  as u

为了方便查询一小时新增用户量使用 create_time创建一个函数索引：

CREATE INDEX idx_order_order_time_hour ON user (HOUR(create_time));

查看表有哪些索引：

SHOW INDEX FROM user;

查看mysql版本号：

SELECT VERSION();

删除user表索引：

要删除表的所有索引，可以使用ALTER TABLE语句和DROP INDEX子句来删除每个索引。语法如下：

ALTER TABLE table_name DROP INDEX index_name1, DROP INDEX index_name2, ...;

其中，table_name是要删除索引的表的名称，index_name1、index_name2等是要删除的索引的名称。

ALTER TABLE user DROP INDEX idx_mobile, DROP INDEX idx_user_name_email;

索引类型参考文章（文章中有提到B+Tree数据结构—给出的图叶子节点是单向指针是错的，B+Tree的叶子节点是双向指针）

BTree结构图详解（B+Tree数据结构文中有提到）

覆盖索引参考文章

MySQL日期函数