MySQL索引

1. 简介

MySQL 支持如下索引：

• index/key：普通索引；
• primary key：（1）值必须唯一，且所有的关键列都必须被定义为 not null，如果用户没有显式地定义，MySQL 会隐私地声明；（2）一张表只能包含一个 primary key；（3）primary key 对应索引的名称为 primary，对于其它类型的索引，如果没有显式地赋予一个名字，则默认为第一个索引列的名称，可能还会添加 _2、_3 等后缀，以保持唯一性；
• unique：值必须唯一，但可以为 NULL；
• fulltext：（1）用于全文搜索，只有 InnoDB 和 MyISAM 存储引擎支持；（2）只能用于 char, varchar, text 数据类型；（3）索引时使用的是该列的全部内容，不支持列前缀，即使用全文，而不是值的前缀；
• spatial：只能用于空间类型，只有 InnoDB 和 MyISAM 存储引擎支持；

2. 列前缀

对于字符串类型，创建索引时可以通过 column_name(length) 来指定只使用该列的 $[0,length)$ 部分。

对于 blob 和 text 数据类型，则是必须指定列前缀。

对于非二进制字符串类型（char，varchar、text）前缀长度以字符为单位；对于二进制字符串类型（binary，varbinary、blob）前缀长度以字节为单位。

3. 添加索引

{index | unique | fulltext | spatial | primary key} [index_type] (key_part,...)

index_type: using {btree| hash}
key_part: column_name[(length)] [ASC | DESC]

注：fulltext 和 spatial 索引不能使用 index_type。

创建表的同时添加索引：

create table `lookup`
(
    `id` int,
    `first_name` varchar(20),
    `second_name` varchar(20),
    index using btree(`first_name`(10), `second_name`(10))
) engine=Memory;

创建表之后添加：create [unique | fulltext | spatial] index ...

# 索引名为 first_name
MariaDB [mydb]> create index `first_name` using btree on `lookup` (`first_name`(10), `second_name`(10));
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

或者：alter table table_name add {index | unique | fulltext | spatial | primary key} ...

MariaDB [mydb]> alter table `lookup` add index using btree(`first_name`(10), `second_name`(10));
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

4. 查看索引

MariaDB [mydb]> show index from `lookup` \G
*************************** 1. row ***************************
        Table: lookup
   Non_unique: 1
     Key_name: first_name
 Seq_in_index: 1
  Column_name: first_name
    Collation: A
  Cardinality: NULL
     Sub_part: 10
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: lookup
   Non_unique: 1
     Key_name: first_name
 Seq_in_index: 2
  Column_name: second_name
    Collation: A
  Cardinality: NULL
     Sub_part: 10
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
2 rows in set (0.00 sec)

5. 删除索引

MariaDB [mydb]> drop index `first_name` on `lookup`;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

或者：

MariaDB [mydb]> alter table `lookup` drop index `first_name`;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

删除主键时使用：

alter table `table_name` drop primary key;