MySQL：添加和删除索引


#MySQL 笔记

建表时创建索引

索引可以有1个字段，也可以有多个字段。多个字段时，称为「联合索引」。

方式1：

用 KEY 声明索引。

CREATE TABLE `user_info` (
	`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(45) NOT NULL,
    `mail` varchar(20) NOT NULL DEFAULT '' COMMENT '邮箱',
	PRIMARY KEY (`id`),
    KEY `idx_name_mail` (`name`, `mail`) -- 关键字KEY可声明索引
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE utf8mb4_general_ci;

方式2： 将KEY 换成INDEX

CREATE TABLE `user_info` (
	`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(45) NOT NULL,
    `mail` varchar(20) NOT NULL DEFAULT '' COMMENT '邮箱',
	PRIMARY KEY (`id`),
    INDEX `idx_name_mail` (`name`, `mail`)  -- 换成 INDEX，效果一样
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE utf8mb4_general_ci;

虽然换成了 INDEX，但是show create 的结果依然是 KEY：

mysql> show create table user_info;
+-----------+----------------------------------------------------------+
| Table     | Create Table                                             |
+-----------+----------------------------------------------------------+
| user_info | CREATE TABLE `user_info` (                               |
|           |   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,      |
|           |   `name` varchar(45) NOT NULL,                           |
|           |   `mail` varchar(20) NOT NULL DEFAULT '' COMMENT '邮箱', |
|           |   PRIMARY KEY (`id`),                                    |
|           |   KEY `idx_name_mail` (`name`,`mail`)                    |
|           | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4                  |
+-----------+----------------------------------------------------------+

建表时创建唯一索引

CREATE TABLE `user_info` (
	`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(45) NOT NULL,
    `mail` varchar(20) NOT NULL DEFAULT '' COMMENT '邮箱',
	PRIMARY KEY (`id`),
    UNIQUE KEY `uk_name` (`name`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE utf8mb4_general_ci;

CREATE TABLE `user_info` (
	`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(45) NOT NULL,
    `mail` varchar(20) NOT NULL DEFAULT '' COMMENT '邮箱',
	PRIMARY KEY (`id`),
    UNIQUE `uk_name` (`name`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE utf8mb4_general_ci;

CREATE TABLE `user_info` (
	`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(45) NOT NULL,
    `mail` varchar(20) NOT NULL DEFAULT '' COMMENT '邮箱',
	PRIMARY KEY (`id`),
    UNIQUE INDEX `uk_name` (`name`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE utf8mb4_general_ci;

这三种形式，show create 的结果是一样的：

mysql> show create table user_info;
+-----------+----------------------------------------------------------+
| Table     | Create Table                                             |
+-----------+----------------------------------------------------------+
| user_info | CREATE TABLE `user_info` (                               |
|           |   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,      |
|           |   `name` varchar(45) NOT NULL,                           |
|           |   `mail` varchar(20) NOT NULL DEFAULT '' COMMENT '邮箱', |
|           |   PRIMARY KEY (`id`),                                    |
|           |   UNIQUE KEY `uk_name` (`name`)                          |
|           | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4                  |
+-----------+----------------------------------------------------------+

建表后添加索引

建表：

CREATE TABLE `user_info` (
	`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(45) NOT NULL,
    `mail` varchar(20) NOT NULL DEFAULT '' COMMENT '邮箱',
	PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE utf8mb4_general_ci;

添加索引：

ALTER TABLE `user_info` ADD INDEX idx_name_mail(`name`, `mail`);

执行后，再查看表的创建语句：

mysql> show create table user_info;
+-----------+----------------------------------------------------------+
| Table     | Create Table                                             |
+-----------+----------------------------------------------------------+
| user_info | CREATE TABLE `user_info` (                               |
|           |   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,      |
|           |   `name` varchar(45) NOT NULL,                           |
|           |   `mail` varchar(20) NOT NULL DEFAULT '' COMMENT '邮箱', |
|           |   PRIMARY KEY (`id`),                                    |
|           |   KEY `idx_name_mail` (`name`,`mail`)                    |
|           | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4                  |
+-----------+----------------------------------------------------------+

删除索引

建表时创建索引：

CREATE TABLE `user_info` (
	`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(45) NOT NULL,
    `mail` varchar(20) NOT NULL DEFAULT '' COMMENT '邮箱',
	PRIMARY KEY (`id`),
    INDEX `idx_name_mail` (`name`, `mail`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE utf8mb4_general_ci;

删除索引：

ALTER TABLE `user_info` DROP INDEX `idx_name_mail`;


( 本文完 )