MySQL索引分类及其使用实例详解

一、MySQL索引分类

MySQL索引主要分为B-tree索引和哈希索引两种，其中B-tree索引又包括主键索引、唯一索引、普通索引、全文索引、空间索引等。

1. B-tree索引

B-tree索引是MySQL中最常用的索引类型，其优点是检索速度快，缺点是对于索引列有前缀的字段，需要存储较多的内容。

B-tree索引又包括以下几种：

主键索引：用于唯一标识每一行数据，具有唯一性和非空性，每个表只能有一个主键索引，且不能为NULL。
唯一索引：是指列中的值必须唯一，可以为空。
普通索引：对于普通查询的列可以建立。
全文索引：用于全文检索，只能在MyISAM存储引擎下使用。
空间索引：用于空间数据类型的检索。

2. 哈希索引

哈希索引通过将索引值作为哈希函数的输入，然后将计算得到的哈希值与哈希表进行匹配，如果存在则返回地址，否则返回空。哈希索引适用于等值查询场景，如WHERE column = value。

二、MySQL索引使用实例

1. 普通索引示例

假设我们有一个用户表，包含以下字段：id、name、age、gender和email：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `gender` enum('male','female') DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

我们为name字段建立普通索引：

ALTER TABLE `user` ADD INDEX `idx_name` (`name`);

之后可以使用以下SQL语句对name进行模糊查询，其效率将大大提升：

SELECT * FROM `user` WHERE `name` LIKE 'Tom%';

2. 唯一索引示例

假设我们有一个学生表，包含以下字段：id、name、age和email：

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

我们为email字段建立唯一索引：

ALTER TABLE `student` ADD UNIQUE INDEX `idx_email` (`email`);

之后插入以下重复记录将会失败：

INSERT INTO `student` (`name`, `age`, `email`) VALUES ('Tom', 18, 'test@test.com');
INSERT INTO `student` (`name`, `age`, `email`) VALUES ('Jerry', 19, 'test@test.com');