1. 创建索引

参考：https://www.w3resource.com/mysql/creating-table-advance/create-index.php

语法

CREATE INDEX [index name] ON [table name]([column name]);

参数

• Name - Description
• index name - Name of the index.
• table name - Name of the table.
• column name - Name of the column.

Example:

• 手动创建索引

CREATE  INDEX index_name  ON  table_name(column_name);

• 创建表的时候定义索引

CREATE TABLE `dog` (
  `id`      int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '名字',
  `count` int DEFAULT 0 COMMENT '次数',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE, -- 主键
  UNIQUE `unq_dog_key` (`name`) USING BTREE -- 唯一约束
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='dog表';

2. 创建唯一约束

参考： - https://blog.csdn.net/u800820/article/details/120392574 - https://www.w3schools.com/sql/sql_unique.asp - https://www.javatpoint.com/mysql-unique-key

语法

ALTER TABLE table_name ADD UNIQUE `unique_name`(`person_id`, `area_id`) USING BTREE COMMENT "一个唯一约束";

3. 查看索引状态

语法

SHOW INDEX FROM <表名> [ FROM <数据库名>]

Example

1. 例一

show index from dog_table;

1. 例二

show index from user from mysql ;

查看系统数据库 mysql 的表 user 的索引状态。