MySQL 索引

1、索引简介

字典大家都看过吧，我们可以按照拼音、笔画、偏旁首部等快速查询到我们想要的汉字。MySQL 也可以看成一部大字典，里面的记录就是一个个汉字，如果没有适当的索引，MySQL 会从第一条记录开始一条条查询，那么找记录就会变的非常耗时，所以，在 MySQL 中，同样存在索引的应用

定义：索引（index）是帮助 MySQL 高效获取数据的数据结构，所以，索引是一种数据结构（本文不介绍如何存储，这是数据结构该干的活）

我们简单介绍一下为什么使用索引可以大大加快查询，首先我们假定有 100000 条记录，查询每一条记录的时间是 1 ms（随便假设的，别当真），假设待查询的记录在第 100000 条，那么不使用索引，数据库就会从第一条记录一直查询到目标为止，即花费的时间是 100000 * 1ms = 100000 ms = 100 s

现在我们将记录的 id 设置为索引（假设记录有 id 这个字段，而且不重复），那么这时候 MySQL 在存储数据的时候，并不是按照顺序存储，而是利用 BTREE 或者 HASH 的数据结构，按照 id 进行存储，具体需要我们理解什么是树，什么是哈希，但是没关系，我们现在只要知道这玩意不是顺序存储就完事了！那么既然都不是顺序存储了，查找记录的时候自然不会按照顺序查找了，所花费的时间一定 < 100 s

索引分类：

• 主键索引（PRIMARY KEY）：一张表只能有一个主键，主键是区分记录的唯一标识，不允许有空值
• 唯一索引（UNIQUE）：索引列的值必须唯一，但允许有空值，如果存在多列，则多列的组合必须唯一
• 全文索引（FULLTEXT）：在整个数据库中，利用关键字进行快速定位，只有特定的数据库引擎（MyISAM）才有
• 常规索引（INDEX）：也称为普通索引，最基本的索引方式，通过创建 INDEX 实现数据索引

注意：有时候能听到单列索引和组合索引的说法，其实区别就是索引列的个数，单列索引顾名思义就只有一列，组合索引有多列，但是它们都是通过 INDEX 关键字创建

索引优点：

• 加快查询速度呗
• 灵活性较高，可以有多种方式设置索引

索引缺点：

• 创建索引和维护索引需要耗费时间，而且维护还是很耗 money 的
• 存储索引需要额外空间，使用大量索引可能会造成空间浪费
• 在数据增、删、改，或者修改表结构的时候，需要额外注意索引

一句话总结，小表尽量不要用，大表合理使用，切勿乱用！

2、创建索引

下面我们介绍一下每一种索引的创建方式，还是比较容易记忆的

-- 三种创建索引的方式（一般情况下）：直接创建索引、使用 ALTER 修改表结构添加索引、建表的时候同时创建索引

-- ※！！！看代码须知！！！※ 
-- 下面的代码中使用 `` 的都是表名（tableName）或者列名（id、name），这是为了和 MySQL 关键字区别开来

/* --------------------------- 1、主键索引 --------------------------- */
-- 方式一：建表的时候创建主键索引
CREATE TABLE `tableName` (
    `ID` int(10) NOT NULL,
    `name` VARCHAR(20) NOT NULL,
    PRIMARY KEY(`ID`)
);

-- 方式二：使用 ALTER 添加主键索引
ALTER TABLE `tableName` ADD PRIMARY KEY(`id`);


/* --------------------------- 2、唯一索引 --------------------------- */
-- 方式一：直接创建
CREATE UNIQUE INDEX `unique_index` ON `tableName`(`id`);

-- 方式二：建表的时候创建
CREATE TABLE `tableName` (
    `ID` int(10) NOT NULL,
    `name` VARCHAR(20) NOT NULL,
    UNIQUE `unique_index` (`id`)
);

-- 方式三：使用 ALTER 添加唯一索引
ALTER TABLE `tableName` ADD UNIQUE `unique_index`(`id`);


/* --------------------------- 3、全文索引 --------------------------- */
-- 方式一：直接创建
CREATE FULLTEXT INDEX `fulltext_index` ON `tableName`(`id`);

-- 方式二：建表的时候创建
CREATE TABLE `tableName` (
    `ID` int(10) NOT NULL,
    `name` VARCHAR(20) NOT NULL,
    FULLTEXT `fulltext_index`(`id`)
);

-- 方式三：使用 ALTER 添加全文索引
ALTER TABLE `tableName` ADD FULLTEXT `fulltext_index`(`id`);


/* --------------------------- 4、常规索引 --------------------------- */
-- 方式一：直接创建
CREATE INDEX `normal_index` ON `tableName`(`id`);

-- 方式二：建表的时候创建
CREATE TABLE `tableName` (
    `ID` int(10) NOT NULL,
    `name` VARCHAR(20) NOT NULL,
    INDEX `normal_index`(`id`)
);

-- 方式三：使用 ALTER 添加常规索引
ALTER TABLE `tableName` ADD INDEX `normal_index`(`id`);

其实，我们为了方便记忆，可以选择 ALTER 的方式，不难看出就是关键字变了一下，其他格式都是一模一样的！或者你直接可视化操作，那上面的代码当我没写 [doge]

3、删除 & 显示索引

删除索引很简单，除了主键索引有点特殊外，其他的都是一样的格式

-- 1、删除主键索引
ALTER TABLE `tableName` DROP PRIMARY KEY;

-- 2、删除唯一索引、全文索引、常规索引（两种方式都可）
DROP INDEX `indexName` ON `tableName`;
ALTER TABLE `tableName` DROP INDEX `indexName`;

-- 3、显示索引（都一样的）
SHOW INDEX FROM `tableName`;

这里使用索引的例子就不展示了，因为就是 SELECT 语句，写法不变，什么都不用新加，毕竟存储的过程又不用我们操心，我们只需要创建合理的索引就行！