mysql 索引

（1）直接创建索引

CREATE INDEX index_name ON table(column(length))

CREATE UNIQUE INDEX zipindex on ec_address(FIRST_LETTER(3))

mysql> create index index_name on t1(id);
Query OK, 0 rows affected (2.80 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  KEY `index_name` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

（2）修改表结构的方式添加索引

普通索引

ALTER TABLE table_name ADD INDEX index_name (column(length))

唯一索引

ALTER TABLE ec_address add UNIQUE INDEX suoyin  (FIRST_LETTER(3))

主键

alter table ec_address add primary key(ID)

（3）删除索引

DROP INDEX index_name ON table

mysql> drop index index_name on t1;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t1;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                      |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------+