MySQL——索引

学习目标：

• 一个月掌握MySQL入门知识

学习内容与笔记：

1. 创建索引
2. 管理索引

---

1. 创建索引

（1）查看索引

mysql> SHOW INDEX FROM xs FROM cjgl;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| xs    |          0 | PRIMARY  |            1 | 学号        | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.01 sec)

（2）在已有表上创建索引

mysql> USE cjgl;
Database changed
mysql> CREATE INDEX index_xs_xm ON xs(姓名);
Query OK, 0 rows affected (0.04 sec)

使用show create table查看索引是否创建成功：

mysql> SHOW CREATE TABLE xs\g;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table

                                                                             |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| xs    | CREATE TABLE `xs` (
  `学号` char(6) NOT NULL,
  `姓名` char(8) NOT NULL,
  `专业名` char(10) DEFAULT NULL,
  `性别` enum('男','女') NOT NULL DEFAULT '男',
  `出生时间` datetime NOT NULL,
  `总学分` tinyint(1) DEFAULT NULL,
  `备注` tinytext,
  PRIMARY KEY (`学号`),
  KEY `index_xs_xm` (`姓名`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

使用explain查看索引是否正在使用：

mysql> EXPLAIN SELECT * FROM xs WHERE 姓名='小狗'\G;

创建组合索引：

mysql> CREATE INDEX index_cj ON cj(学号,课程号);

（3）在创建表时创建索引

mysql> CREATE TABLE kc(
    -> 课程号 CHAR(3) NOT NULL PRIMARY KEY,
    -> 课程名 CHAR(16) NOT NULL,
    -> TIME TINYINT NOT NULL CHECK (TIME>=1 AND TIME<=6),
    -> 学时 TINYINT NOT NULL,
    -> 学分 TINYINT NOT NULL,
    -> UNIQUE INDEX index_kc(课程名));

查看：

mysql> SHOW CREATE TABLE kc\g;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table

                                                                     |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| kc    | CREATE TABLE `kc` (
  `课程号` char(3) NOT NULL,
  `课程名` char(16) NOT NULL,
  `TIME` tinyint NOT NULL,
  `学时` tinyint NOT NULL,
  `学分` tinyint NOT NULL,
  PRIMARY KEY (`课程号`),
  UNIQUE KEY `index_kc` (`课程名`),
  CONSTRAINT `kc_chk_1` CHECK (((`TIME` >= 1) and (`TIME` <= 6)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

2. 管理索引

索引无法修改，只能删除原索引，再创建新索引（同名）。
删除索引通过drop index实现。

mysql> DROP INDEX index_xs_xm ON xs;