关于MySQL索引的好处，如果正确合理设计并且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。对于没有索引的表，单表查询可能几十万数据就是瓶颈，而通常大型网站单日就可能会产生几十万甚至几百万的数据，没有索引查询会变的非常缓慢。以WordPress来说，其多个数据表都会对经常被查询的字段添加索引，比如wp_comments表中针对5个字段设计了BTREE索引。

索引简介

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

一般情况下，在没建立索引的时候，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多。如果表中查询的列有索引，MySQL能够快速到达一个位置去搜索数据文件，而不必查看所有数据，这将会节省很大一部分时间。

优点

提高MySQL的检索速度。

缺点

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
索引本身也是表，建立索引会占用磁盘空间的索引文件，一般来说，索引表占用的空间是数据表的1.5倍。

使用原则

对经常更新的表就避免对其进行过多的索引，对经常用于查询的字段应该创建索引，
数据量小的表最好不要使用索引，因为由于数据较少，可能查询全部数据花费的时间比遍历索引的时间还要短，索引就可能不会产生优化效果。
在一同值少的列上(字段上)不要建立索引，比如在学生表的”性别”字段上只有男，女两个不同值。相反的，在一个字段上不同值较多可是建立索引。

索引类型

1、B-Tree索引

最常见的索引类型，使用 B 树（Balanced Tree）数据结构。

节点存储索引键值及指向子节点的指针，叶子节点存储索引键值和对应的数据行指针。

支持范围查询、精确匹配、排序操作。

可以根据索引列的前缀进行查找，如对字符串列的左前缀查找。

示例代码

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    INDEX idx_name (name)
);

示例解释

在上述 CREATE TABLE 语句中，INDEX idx_name (name) 为 name 列创建了一个 B-Tree 索引。

当执行 SELECT FROM users WHERE name = ‘John’ 或 SELECT FROM users WHERE name LIKE ‘Jo%’ 这样的查询时，MySQL 可以利用这个索引快速定位符合条件的数据行，而不是进行全表扫描。

对于 SELECT * FROM users WHERE name BETWEEN ‘A’ AND ‘M’ 这样的范围查询，B-Tree 索引也能很好地发挥作用。

2、Hash索引

1，仅支持 =、IN、<=> 精确查询，不能使用范围查询：

由于Hash索引比较的是进行Hash运算之后的Hash值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的Hash算法处理之后的Hash。

2，不支持排序：

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

3，在任何时候都不能避免表扫描。查找速度快，但哈希冲突会影响性能：

由于Hash索引比较的是进行Hash运算之后的Hash值，所以即使取满足某个Hash键值的数据的记录条数，也无法从Hash索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。

4，检索效率高，索引的检索可以一次定位，不像B-Tree索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以Hash索引的查询效率要远高于B-Tree索引。

5，通常存储引擎会自动管理哈希索引的创建和维护。

示例代码

-- MySQL 中的 Memory 存储引擎默认使用 Hash 索引，InnoDB 存储引擎从 MySQL 8.0 开始支持显式创建 Hash 索引
CREATE TABLE hash_table (
    id INT PRIMARY KEY,
    data VARCHAR(100),
    INDEX idx_data USING HASH (data)
) ENGINE = MEMORY;

示例解释

在 CREATE TABLE 语句中，INDEX idx_data USING HASH (data) 为 data 列创建了一个 Hash 索引。

对于 SELECT FROM hash_table WHERE data = ‘value’ 这样的等值查询，Hash 索引可以快速定位数据行，但对于 SELECT FROM hash_table WHERE data > ‘value’ 这样的范围查询，无法使用 Hash 索引，MySQL 会进行全表扫描。

3、全文索引 (FULLTEXT)

针对文本数据，支持全文搜索功能，允许在大量文本中查找包含特定关键词的记录。

可以对文本列中的多个单词进行匹配和查找，支持自然语言搜索、布尔搜索等模式。

只能用于 MyISAM 和 InnoDB 表中的 CHAR, VARCHAR, 或 TEXT 列，并且必须是这些列的全部内容，不能是部分。

可通过 MATCH AGAINST 关键字进行全文搜索操作。

示例代码

CREATE TABLE articles (
    id INT PRIMARY KEY,
    content TEXT,
    FULLTEXT INDEX ft_content (content)
);

-- 使用全文索引进行查询
SELECT * FROM articles WHERE MATCH(content) AGAINST('keyword' IN NATURAL LANGUAGE MODE);

示例解释

FULLTEXT INDEX ft_content (content) 为 content 列创建了全文索引。

在 SELECT 语句中，使用 MATCH(content) AGAINST(‘keyword’ IN NATURAL LANGUAGE MODE) 进行全文搜索，MySQL 会查找 content 列中包含 keyword 的记录，并根据相关性排序。

全文索引在处理长文本搜索时比 LIKE 操作更高效，尤其当搜索条件较为模糊时。

4、空间索引 (SPATIAL)

空间索引是专门为地理空间数据设计的索引类型，比如存储地理位置信息的 GIS 数据。

对于包含几何对象（例如点、线、多边形）的数据列，可以创建空间索引以加速空间查询。

用于存储和查询空间数据类型，如 POINT、LINESTRING、POLYGON 等。

支持地理信息系统（GIS）中的空间数据查询和操作，如查找距离、范围、重叠等。

可以使用空间函数进行查询，如 ST_Distance、ST_Contains 等。

示例代码

CREATE TABLE locations (
    id INT PRIMARY KEY,
    location POINT,
    SPATIAL INDEX sp_location (location)
);

-- 使用空间索引进行查询
SELECT * FROM locations WHERE ST_Distance(location, POINT(0,0)) < 10;

示例解释

SPATIAL INDEX sp_location (location) 为 location 列创建了空间索引。

在 SELECT 语句中，使用 ST_Distance(location, POINT(0,0)) < 10 查找距离点 (0,0) 小于 10 的位置信息。

空间索引有助于提高空间数据查询的性能，避免全表扫描。

5、唯一索引 (UNIQUE)

唯一索引确保索引列中的所有值都是唯一的，即不允许出现重复值。

这个索引可以是单列的，也可以是多列组合而成的联合唯一索引。

代码示例

# 使用 CREATE INDEX 语句创建唯一索引
CREATE TABLE students (
    id INT PRIMARY KEY,
    student_id INT,
    name VARCHAR(100)
);
CREATE UNIQUE INDEX idx_student_id ON students(student_id);

# 在多列上创建唯一索引
CREATE TABLE students (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    -- 在 first_name 和 last_name 列的组合上创建唯一索引
    UNIQUE INDEX idx_name ON students(first_name, last_name)
);

6、主键索引 (PRIMARY KEY)

主键是一种特殊的唯一索引，它不仅保证了数据行的唯一性，还提供了表的物理排序。

每个表只能有一个主键，并且主键不能为空（NOT NULL）。

7、复合索引 (Composite Indexes)

当一个索引覆盖多个列时，我们称之为复合索引。复合索引允许你在一个索引中同时对多个列进行索引。

在查询条件中如果涉及到了复合索引的前导列，则该索引会被有效地使用。