索引的创建和使用原则：索引的声明和使用

1. 索引的分类

MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。
● 按照功能逻辑分类：普通索引，唯一索引，主键索引，全文索引
● 按照物理实现方式分类：聚簇索引，非聚簇索引
● 按照作用字段个数分类：单列索引，联合索引

1.1 普通索引

不添加任何限制条件，只用于提高查询效率。可以创建在任意数据类型中。

1.2 唯一索引

使用unique参数可以设置索引为唯一性索引，在创建唯一索引时，必须限制该索引的值是唯一的，但允许有空值。在一张表里可以有多个唯一索引。

1.3 主键索引

主键索引是特殊的唯一索引，在唯一索引上增加了不为空的约束，也就是not null + unique，一张表里只能有一个主键索引。

• 为什么一张表只能有一个主键索引？
  因为，这是由主键索引的物理实现方式决定的，索引结构只能按照亦能排序方式存储。

1.4 单列索引

在表中的单个字段上创建索引。单列索引只根据该字段进行索引。单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只要保证该索引只对应一个字段即可。一个表可以有多个单列索引。

1.5 多列（组合、联合索引）

多列索引是在表的多个字段组合上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询，但是只有查询条件中使用了这些字段中的第一个字段时才会被使用。例如，在表中的字段id、name和gender.上建立一个多列索引idx_1d_name_gender`,只有在查询条件中使用了字段id时该索引才会被使用。使用组合索引时遵循最左前缀集合。

1.6 全文索引

全文索引也称全文检索，是目前搜索引擎使用的一种关键技术。他能够利用【分词技术】等多种算法智能分析出文本文字中的关键词的频率和重要性，然后按照一定的算法规则智能的筛选出我们想要的结果。全文索引非常适合大型数据集。
使用【fulltext】设置全文索引。只能穿件在char，varchar，text字段上。
全文索引典型的有两种类型：自然语言的全文索引和布尔全文索引
● 自然语言搜索引擎将计算每一个文档对象和查询的相关度。这里，相关度是基于匹配的关键词的个数，以及关键词在文档中出现的次数。在整个索引中出现次数越少的词语，匹配时的相关度就越高。相反，非常常见的单词将不会被搜索，如果一个词语的在超过50%的记录中都出现了，那么自然语言的搜索将不会搜索这类语。

1.7 小结：不同的存储引擎支持的索引类型也不一样

• InnoDB:支持B-tree、Full-text等索引，不支持Hash索引；
• MyISAM:支持B-tree、Full-text等索引，不支持Hash索引；
• Memory:支持B-tree、Hash等索引，不支持Full-text索；
• NDB:支持Hash索引，不支持B-tree、Full-text等索引；
• Archive:不支持B-tree、Hash、Full-text等索引；

---

2. 创建索引

MySQL支持多种方法在单个或多个列上创建索引：在创建表的定义语句CREATE TABLE中指定索引列，使用ALTER TABLE语句在存在的表上创建索引，或者使用CREATE INDEX语句在已存在的表上添加索引。

2.1 创建表的时候创建索引：CREATE TABLE

CREATE TABLE table_name [col_name data_type]
[UNIQUE FULLTEXT SPATIAL][INDEX KEY][index_name](co1_name [length])[ASC DESC]

● UNIQUE、FULLTEXT和SPATIAL为可选参数，分别表示唯一索引、全文索引和空间索引
● INDEX与KEY为同义词，两者的作用相同，用来指定创建索引；
● index_name指定索引的名称，为可选参数，如果不指定，那么MySQL默认col_name为索引名；
● co1_name为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择；
● length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；
● ASC或DESC指定升序或者降序的索引值存储。

• 例子：

  CREATE TABLE test_table (
    id INT PRIMARY KEY AUTO_INCREMENT, -- 主键索引，自动递增
    name VARCHAR(20) UNIQUE, -- 唯一索引
    passwd VARCHAR(20), -- 普通字段
    INDEX idx_passwd (passwd), -- passwd列的普通索引
    c1 VARCHAR(2),
    c2 VARCHAR(2),
    INDEX idx_c1_c2 (c1, c2), -- c1，c2的联合索引
    context text,
    FULLTEXT idx_context (context) --全文索引
  );

  ①创建普通索引

  create table test_table (
  id int,
  index idx_id (id)
  );

  ②创建唯一索引

  create table test_table (
  id int,
  unique index un_idx_id (id)
  );

  ③创建主键索引

  CREATE TABLE test_table (
    id INT PRIMARY KEY, -- 主键索引
  );
  
  #通过删除主键约束的方式删除主键索引
  alter table test_table drop primary key;

  ④创建全文索引

  CREATE TABLE test_table (
    id INT,
    context text,
    fulltext fu_idx_con (context)
  );

  ⑤创建联合索引

  create table test_table (
  id int,
  c1 varchar(2),
  c2 varchar(2),
  index idx_c1_c2 (c1,c2)
  );

---

2.2 在存在的表创建索引：ALTER TABLE，CREATE INDEX

在已经存在的表中创建索引可以使用ALTER TABLE语句或者CREATE INDEX语句。

①使用ALTER TABLE 表名 add 索引类型 索引名称 (列 )

alter table test_table add index idx_c1 (c1)
alter table test_table add unique index un_idx_c1 (c1)
alter table test_table add index idex_c1_c2 (c1,c2)

②使用CREATE INDEX 索引类型 索引名称 ON 表名(列)

create index idx_c1 on test_table(c1) 
create unique index un_idx_c1 on test_table(c1)
create index idx_c1_c2 on test_table(c1,c2)

---

3. 删除索引

3.1 使用ALTER TABLE 删除索引

ALTER TABLE删除索引的基本语法格式：ALERT TABLE 表名 DROP INDEX 索引名

alter table test_table drop idx_c1

• 提示：注意有auto_incerment自增约束字段的唯一索引不能删除。

3.2 使用DROP INDEX 删除索引

DROP INDEX删除索的基本语法格式：DROP INDEX 索引名 ON 表名
drop index idx_c1 on test_table

drop index idx_c1 on test_table

• 提示
  删除表中的列时，如果要删除的列为索的组成部分，则该列也会从索引中删除。如果组成索引的所有列都
  被删除，则整个索引将被删除。