索引的创建与设计原则

1. 索引的声明与使用

1.1 索引的分类

MySQL 的索引包括普通索引，唯一性索引，全文索引，单列索引，多列索引和空间索引等...。

从功能逻辑上说，索引主要有4种，分别是普通索引，唯一索引，主键索引，全文索引。
按照物理实现方式，索引分为2种：聚簇索引和非聚簇索引。
按照作用字段个数进行划分：单列索引和联合索引。

1.1.1 普通索引

在创建普通索引时，不附加任何限制条件，只是用于提高查询效率。这类索引可以创建在任何数据类型中，其值是否唯一和非空，要由字段本身的完整性约束条件决定。建立索引之后，可以通过索引进行查询。一张表可以创建多个索普通索引。例如：在表 xld 的字段 name 上建立一个普通索引，查询记录时就可以根据该索引进行查询。

1.1.2 唯一性索引

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

注意：

在创建列时，如果为该列添加了唯一约束时，那么会自动为该列创建唯一索引。
在为列创建唯一索引时，该列也会自动添加唯一约束。
如果想删除唯一约束的话，则直接删除唯一性索引即可。

1.1.3 主键索引（聚簇索引）

主键索引是一种特殊的唯一性索引（非空），在唯一索引的基础上增加了不为空的约束，也就是not null + unique 。一张表最多只有一个主键索引

为什么只能有一个索引呢？

这是由（innoDB 引擎）主键索引的物理实现方式决定的，因为数据存储在文件（文件系统）中只能按照一种顺序进行存储。

1.1.4 单列索引

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

1.1.5 多列（组合，联合）索引

多列索引就是联合表的多个字段而创建的一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询，但是只有查询条件中使用了这些字段中的第一个字段时才会被使用（索引才会生效）。使用联合（组合）索引时必须遵循最左前缀原则。

例如：在表中的字段id，name和gender 上建立一个多列索引 index_id_name_gender ，只有在查询条件中使用了字段id时该索引才会被使用。

注意：

在编写sql时（where）条件的顺序，我们尽量要和创建多列索引时使用联合的字段的顺序一致。
在编写sql时，（where）条件中只要使用了多列索引中的序号为第一个字段即可，在优化器阶段MySQL会自动（优先）使用索引查询。

1.1.6 全文索引

全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术。它能利用【分词技术】等多种算法智能分析出文本文中关键词的频率和重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。全文索引非常适合大型数据集，对于小的数据集，它的用处比较小。

使用参数 FULLTEXT 可以设置索引为全文索引。在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引只能创建在 char，varchar 和 text 类型及其系列类型的字段上，在查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。

例如：xld 表的字段 info 是 text 类型，该字段包含了很多文字信息。在字段 info 上建立全文索引后，可以提高查询字段 info 的速度。

全文索引典型的有两种类型：自然语言的全文索引和布尔全文索引。

自然语言搜索引擎将计算每一个文档对象和查询的相关度。这里，相关度是基于匹配的关键词的个数，以及关键词在文档中出现的次数。在整个索引中出现次数越少的词语，匹配时的相关度就越高。相反，非常常见的单词将不会被搜索，如果一个词语出现在了 50% 的记录中，那么自然语言的搜索将不会搜索这类词语。

MySQL数据库从 3.23.23 版本开始支持全文索引，但MySQL5.6.4以前只有 MyIsam 支持，5.6.4 版本以后 innodb 才支持，但是官方版本不支持 "中文分词"，需要第三方分词插件。在 5.7.6 版本后，MySQL 内置了 ngram 全文解析器，用来支持亚洲语种的分词。测试或使用全文索引时，要先看一下自己的 MySQL 版本，存储引擎和数据类型是否支持全文索引。

随着大数据时代的到来，关系型数据库应对全文索引的需求已力部从心，逐渐被 solr，ElasticSearch 等专门的搜索引擎所替代

1.1.7 补充：空间索引

使用参数 SPATIAL 可以设置索引为空间索引。空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率。MySQL 中的空间数据类型包括 GEOMETRY，POINT，LINESTRINF 和 POLYGON 等。目前只有 MyISAM 存储引擎支持空间检索，而且索引的字段不能为空值。

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

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 等索引；

1.2 创建索引

MySQL 支持多种方法在单个或多个列上创建索引：在创建表的定义语句 create table （创建表时）指定索引列，使用 alter atble （修改表时）创建索引，以及使用 create index 语句在已经存在的表上添加索引。三种方式。

查看表中的索引，语法：

# 查看索引
show index from 表名;
# 或
show create table 表名;
# 或
select * from information_schema.statistics where table_name='表名';

例如：

# 查看索引
SHOW INDEX FROM create_table_index;
# 或
SHOW CREATE TABLE create_table_index;
# 或
select * from information_schema.statistics where table_name='create_table_index';

1.2.1 创建表的时候创建索引

使用 create table 创建表时，除了可以定义列的数据类型外，还可以定义主键约束，外键约束或者唯一性约束，而不论创建那种约束，在定义约束的同时相当于在指定列上创建了一个索引。

主键索引（约束）与唯一性索引（约束）

# 创建表时 创建索引 - 主键索引（约束）与 唯一性索引（约束）
CREATE TABLE create_table_index (
    id BIGINT PRIMARY KEY COMMENT '主键',  #自动创建主键索引
    name VARCHAR(50) UNIQUE COMMENT '名称', # 唯一约束，自动创建唯一索引
    age TINYINT DEFAULT 0 COMMENT '年龄'
);

外键索引（约束）

# 通过设置外键约束自动生成外键索引
CREATE TABLE create_table_index_foreign (
id BIGINT PRIMARY KEY COMMENT '主键',
table_id BIGINT COMMENT '外键',
CONSTRAINT foreign_index_table_id_pk FOREIGN KEY(table_id) REFERENCES create_table_index(id) #外键约束(索引)
);

注意：在声明有主键约束，唯一性约束，外键约束的字段上，会自动的添加相关的索引

创建索引 - 语法：

create table 表名 (
	列名 数据类型 约束 默认值 说明-备注,
	列名 数据类型 约束 默认值 说明-备注,
	列名 数据类型 约束 默认值 说明-备注,
	...,
	[unique | fulltext | spatial] [index | key] [index_name] (col_name [length] [asc | desc])  [visible | invisible]
);

unique，fulltext 和 spatial 为可选参数，分别表示唯一索引，全文索引和空间索引；

index 与 key 为同义词，两者的作用相同，用来指定创建索引（创建索引的关键字）；

index_name 指定索引的名称，可选参数，如果不指定，那么MySQL默认 col_name（列名）为索引名；

col_name 为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择；

length 可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；

asc 或 desc 指定升序或者降序的索引值存储（默认升序）；

visible 或 invisible 指定该索引是否为隐藏索引（默认为显示索引）；

1. 创建 - 普通索引

CREATE TABLE create_table_index (
	id BIGINT PRIMARY KEY COMMENT '主键',
	NAME VARCHAR (50) UNIQUE COMMENT '名称',
	age TINYINT DEFAULT 0 COMMENT '年龄',
	# 创建名为 index_age的普通索引，作用在 age 字段上。
	INDEX index_age (age)
);

2. 创建 - 唯一索引（声明有唯一索引的字段，同时也有了唯一性约束）

CREATE TABLE xld_unique_index (
	id BIGINT PRIMARY KEY COMMENT '主键',
	CODE VARCHAR (50) COMMENT 'code 唯一标识',
	NAME VARCHAR (50) COMMENT '名称',
	age TINYINT DEFAULT 0 COMMENT '年龄',
    # 创建唯一索引
	UNIQUE INDEX index_code_unique (CODE)
);

3. 创建 - 主键索引（通过定义主键约束的方式定义主键索引）

CREATE TABLE create_table_primary_index (
	# 创建主键索引（定义主键约束）
	id BIGINT PRIMARY KEY COMMENT '主键',
	NAME VARCHAR (50) COMMENT '名称',
	age TINYINT DEFAULT 0 COMMENT '年龄'
);

4. 创建 - 单列索引（必须保证该索引只能对应一个字段）

CREATE TABLE create_table_index_danlie (
	id BIGINT PRIMARY KEY COMMENT '主键id',
	CODE VARCHAR (50) UNIQUE COMMENT '唯一code',
	NAME VARCHAR (50) NOT NULL COMMENT '名字',
	age TINYINT DEFAULT 0 COMMENT '年龄',
	# 创建单列索引并降序排列
	INDEX index_age (age DESC)
);

5. 创建 - 联合（组合）索引（该索引对应多个字段）

CREATE TABLE create_table_index_duolie (
	id BIGINT PRIMARY KEY COMMENT '主键id',
	CODE VARCHAR (50) UNIQUE COMMENT '唯一code',
	NAME VARCHAR (50) NOT NULL COMMENT '名字',
	age TINYINT DEFAULT 0 COMMENT '年龄',
	# 创建联合（组合）索引
	INDEX index_name_age (NAME, age DESC)
);

6. 创建 - 全文索引（只有是文本数据类型的字段，才能创建该索引。支持局部（前缀）索引）

CREATE TABLE create_table_index_quanwen (
	id BIGINT PRIMARY KEY COMMENT '主键id',
	CODE VARCHAR (50) UNIQUE COMMENT '唯一code',
	NAME VARCHAR (50) NOT NULL COMMENT '名字',
	age TINYINT DEFAULT 0 COMMENT '年龄',
	# 创建全文索引 使用前20个字符构建索引（前缀索引）
	FULLTEXT INDEX index_name (NAME(20))
);
# 普通全文索引
# FULLTEXT INDEX index_name (NAME)

在创建了全文索引的字段，我们可以使用 match+ageinst 的方式查询

注意点：

使用全文索引前，搞清楚版本支持情况；（5.7 之后 InnoDB 开始支持全文索引）

全文索引比 like + %，快 N 倍，但是可能存在精度问题；

如果需要全文索引的是大量数据，建议先添加数据，再创建索引；

什么是前缀索引？

使用字符串的前几个字符作为索引 (即前缀索引)

下面具体主要说第2种方法，主要思路就是选择足够长的前缀以保证较高的选择性，同时又不能太长(造成空间浪费)。所谓选择性，是指不重复的索引数量除以总记录数，范围是(0,1]，唯一索引之所以查询

语法：

SELECT * FROM 表名 WHERE MATCH(字段名称) AGAINST('查询内容');

例如：

SELECT * FROM create_table_index_quanwen WHERE MATCH(name) AGAINST('xld');

7. 创建 - 空间索引（只有是空间数据类型的字段，才能创建索引。要求空间类型的字段必须 "非空"）

CREATE TABLE create_table_index_kongjian (
	id BIGINT PRIMARY KEY COMMENT '主键id',
	geo GEOMETRY NOT NULL COMMENT '空间类型字段',
	# 创建空间索引，作用的空间类型字段必须是非空的
	SPATIAL INDEX index_geo (geo)
);

1.2.2 在已经存在的表上创建索引

建表

CREATE TABLE alter_table_index (
	id BIGINT COMMENT 'id',
	CODE VARCHAR (50) COMMENT 'code',
	NAME VARCHAR (50) NOT NULL COMMENT '名字',
	age TINYINT DEFAULT 0 COMMENT '年龄',
	time datetime COMMENT '时间'
);

CREATE TABLE create_index_on_table (
	id BIGINT COMMENT 'id',
	CODE VARCHAR (50) COMMENT 'code',
	NAME VARCHAR (50) NOT NULL COMMENT '名字',
	age TINYINT DEFAULT 0 COMMENT '年龄',
	time datetime COMMENT '时间'
);

1.2.2.1 使用 alter table 语句创建索引

与创建表时创建索引的语法不同的是，在这里使用了 alter table 和 add关键字，add 表示向表中添加索引。

创建索引 - 语法：

ALTER TABLE 表名 ADD
	[unique | fulltext | spatial] index index_name (col_name [(length)] [asc | desc]) [visible | invisible];

[unique | fulltext | spatial] # 索引类型

index # 创建索引的关键字

index_name # 索引名称

(col_name [asc | desc]) # 创建的索引字段列升序降序

[visible | invisible] # 是否隐藏/显示索引

创建 - 普通索引

ALTER TABLE alter_table_index ADD INDEX index_age (age DESC);

创建 - 唯一性索引

ALTER TABLE alter_table_index ADD UNIQUE INDEX uk_index_code (CODE);

创建 - 主键索引

# 主键索引，主键索引只能通过设置主键约束的方式，系统自动创建。
ALTER TABLE alter_table_index MODIFY id BIGINT PRIMARY KEY;

创建 - 单列索引

# 单列索引必须保证，在创建索引时对应一个字段列（只能是一个）。
ALTER TABLE alter_table_index ADD INDEX index_age(age);

创建 - 联合（组合索引）

# 创建联合（组合）索引，就是在创建索引时对应多个字段列（必须多于一个字段列）。
ALTER TABLE alter_table_index ADD INDEX index_age_name_time(age,name,time desc);

创建 - 全文索引之（前缀索引）

# 创建全文索引，只有是文本数据类型的字段，才能创建该索引。
ALTER TABLE alter_table_index ADD FULLTEXT INDEX index_name(name(20));

1.2.2.2 使用 create index 创建索引

create index 语句可以在已经存在的表上添加索引，在 MySQL中，create index 会被映射成一个 alter table 语句。

创建索引 - 语法：

create [unique | fulltext | spatial] index index_name
	on 表名 (col_name[(length)] [asc | desc]) [visible | invisible];

[unique | fulltext | spatial] # 索引类型

index # 创建索引的关键字

index_name # 索引名称

(col_name [asc | desc]) # 创建的索引字段列升序降序

[visible | invisible] # 是否隐藏/显示索引

普通索引

CREATE INDEX index_age ON create_index_on_table(age DESC);

唯一性索引

CREATE INDEX uk_index_code ON create_index_on_table (code);

主键索引

# 该创建方式无法创建主键索引。

单列索引

# 单列索引必须保证，在创建索引时对应一个字段列（只能是一个）。
CREATE INDEX index_age on create_index_on_table(age);

联合（组合索引）

# 创建联合（组合）索引，就是在创建索引时对应多个字段列（必须多于一个字段列）。
CREATE INDEX index_code_name_age_time ON create_index_on_table(code,name,age,time DESC);

全文索引

# 创建全文索引，只有是文本数据类型的字段，才能创建该索引。
CREATE FULLTEXT INDEX index_full_text_name ON create_index_on_table(NAME);

1.3 删除索引

1.3.1 删除主键索引

语法：

alter table 表名 drop primary key;

例如：

ALTER TABLE create_table_index DROP PRIMARY KEY;

注意：删除主键索引，只能通过删除主键约束的方式删除。

1.3.2 修改表时 - 删除索引

语法：

alter table 表名 drop index 索引名称;

例如：

ALTER TABLE create_table_index DROP INDEX index_name;

注意：添加 auto_increment 约束字段的唯一索引不能被删除

1.3.3 删除索引

语法：

drop index 索引名称 on 表名;

例如：

DROP INDEX index_name ON create_table_index;

1.3.4 删除联合索引

方式1 - （直接删除整个索引）

语法：

drop index 索引名称 on 表名;
# 或者
alter table 表名 drop index 索引名称;

例如：

DROP INDEX index_name ON create_table_index;
# 或者
DROP INDEX index_name ON create_table_index;

方式2 - （通过删除表中逐个字段（列）的方式，删除联合索引）

语法：

alter table 表名 drop column 字段（列）;

例如：

ALTER TABLE create_index_on_table drop COLUMN NAME;

注意：如果我们删除表中字段（列），随之也会删除对应的索引。

单列索引：如果该字段（列）的对应是单列索引，那么会直接删除该字段对应的索引。
联合索引：如果该字段（列）作用在联合索引中，则会在联合索引的字段列表中将该字段删除。

宋老师批注：提示（联合索引）：

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

1.4 修改索引

1.4.1 修改索引名称

语法：

alter table 表名 rename index old_index_name to new_index_name;
# -- 
alter table 表名 rename index 旧的索引名称 to 新的索引名称;

例如：

ALTER TABLE create_index_on_table RENAME INDEX alter_index_code TO new_alter_index_time;

1.4.2 修改索引是可见性（显示 - 隐藏）

语法：

alter table 表名 alter index 索引名称 (col_name [length] [asc | desc]) [visible | invisible];
# visible = 可见（默认）
# invisible = 不可见-隐藏

例如：

# 修改为可见
ALTER TABLE create_index_invisible ALTER INDEX index_name_invisible visible;
# 修改为不可见（隐藏）
ALTER TABLE create_index_invisible ALTER INDEX index_name_invisible invisible;

1.4.3 修改索引

语法：

# --先删除索引
drop index 索引名称 on 表名;
# --再创建索引
create index 索引名称 on 表名(对应的字段);

例如：

# 先删索引
DROP INDEX new_alter_index_time ON create_index_on_table;
# 再创建索引
CREATE UNIQUE INDEX uk_index_code ON create_index_on_table(code DESC);

1.5 强制使用索引

select * from force index(idx_name) xld where name = 'xld';

2. MySQL 8.0 索引新特性

2.1 支持降序索引

降序索引，是以降序存储键值的索引。虽然在语法上，从 MySQL 4 版本开始就已经支持降序索引的语法了，但实际上该 desc 定义是被忽略的，直到 MySQL 8.x 版本才开始真正支持降序索引（但仅限于 InnoDB 存储引擎）。

MySQL在 8.0 版本之前创建的仍然是升序索引，使用时进行反向扫描，这大大降低了数据库的效率。在某些场景下，降序索引意义重大。

例如：如果一个查询，需要对多个列进行排序，且顺序要求不一致，那么使用降序索引将会避免数据库使用额外的文件排序操作，从而提高性能。

创建降序索引

语法：

alter table 表名 add index 索引名称(字段 desc);
# 或者
create index 索引名称 on 表名(字段 desc);

例如：

ALTER TABLE alter_table_index ADD INDEX index_name_desc(name DESC);
# 或者
CREATE INDEX index_code_desc ON alter_table_index(code DESC);

注意：降序索引只对查询中特定的排序顺序有效，如果使用不当，反而查询效率更低。当查询中的字段排序顺序和索引中的字段排序顺序不一致时，那么该查询的效率是很低的。

例如：如果查询中的排序为 order by 字段x asc，字段y desc，创建索引的时候字段x asc，字段y asc。那么该查询效率是很低的。

扩展

使用 EXPLAIN 关键字，分析 sql 语句的执行效率（计划）。

语法：
explain select ... from ....;
提示

在对 sql 语句的分析结果中：如果 Extra 中有 Using filesort 的话。就表示是一种速度比较慢的外部排序，能避免还是避免。多数情况下，DBA 可以通过优化索引来尽量避免出现 Using filesort，从而提高数据库执行速度。

2.2 隐藏索引

隐藏索引就是让该索引失效，使查询优化器不再使用这个索引。

在MySQL 5.7版本及之前，只能通过显示的方式删除索引。此时，如果发现删除索引后出现错误，又只能通过显式创建索引的方式将删除的索引创建回来。如果数据表中的数据量非常大，或者数据表本身比较大，这种操作就会消耗系统过多的资源，操作成本非常高。

从 MySQL 8.x 开始支持隐藏索引（invisible indexes），只需要将待删除的索引设置为隐藏索引，使查询优化器不再使用这个索引（force index（强制使用索引），优化器也不会使用该索引），确认将索引设置为隐藏索引后系统不受任何影响，就可以彻底删除索引。这种通过先将索引设置为隐藏索引，再删除索引的方式就是软删除。

同时，如果你想验证某个索引删除之后的查询性能影响，就可以暂时先隐藏该索引。

注意：主键索引（聚簇索引）不能被设置为隐藏索引。当表中没有显式主键索引时，表中第一个唯一非空的索引会成为隐式主键索引，同样也不能设置为隐藏索引。

在创建索引时默认是可见的，在使用 create table，create index 和 alter table 等语句时可以通过 visible 或者 invisible 关键词设置索引的可见性。

2.2.1 创建表时 - 隐藏索引

语法：

create table 表名 (
	列名 数据类型 约束 默认值 说明-备注,
	列名 数据类型 约束 默认值 说明-备注,
	列名 数据类型 约束 默认值 说明-备注,
	...,
	[unique | fulltext | spatial] [index | key] [index_name] (col_name [length] [asc | desc]) invisible
);

例如：

CREATE TABLE create_index_invisible (
	id BIGINT PRIMARY KEY COMMENT '主键id',
	CODE VARCHAR (50) COMMENT 'code',
	NAME VARCHAR (50) NOT NULL COMMENT '名称',
	age TINYINT DEFAULT 0 COMMENT '年龄',
	time datetime COMMENT '时间',
	# 创建 隐式索引
	INDEX index_age_invisible (age) invisible
);

2.2.2 修改表时 - 隐藏索引

语法：

ALTER TABLE 表名 ADD
	[unique | fulltext | spatial] index index_name (col_name [(length)] [asc | desc]) invisible;

例如：

ALTER TABLE create_index_invisible ADD INDEX index_name_invisible(name) invisible;

2.2.3 创建索引时 - 隐藏索引

语法：

create [unique | fulltext | spatial] index index_name on 表名 (col_name[(length)] [asc | desc]) invisible;

例如：

CREATE UNIQUE INDEX uk_index_code_invisible ON create_index_invisible(code) invisible;

2.2.4 切换索引可见状态（和修改索引中的一样）

# 切换成可见索引
alter table 表名 alter index index_name visible;
# 切换成隐藏索引
alter table 表名 alter index index_name invisible;

注意

当索引被隐藏时，它的内容仍然是和正常索引一样实时更新的。如果一个索引需要长期被隐藏，那么可以将其删除，因为索引的存在会影响插入，更新和删除的性能。

2.2.5 使隐藏索引对查询优化器可见

在 MySQL 8.x 版本中，为索引提供了一种新的测试方式，可以通过查询优化器的一个开关（use_invisible_indexes）来打开某个设置，使隐藏索引对查询优化器可见。如果 use_invisible_indexes 为 off（默认），优化器会忽略隐藏索引。反之如果为 on，即使隐藏索引不可见，优化器在生成执行计划时仍会考虑使用隐藏索引。

查看优化器的开关设置的：系统变量（全局 / session）：optimizer_switch

SHOW VARIABLES LIKE '%optimizer_switch%';
# 或
SELECT @@session.optimizer_switch;
SELECT @@global.optimizer_switch;

在输出的结果信息中找到如下属性

 use_invisible_indexes = off

此属性配置值为 off，说明隐藏索引默认对查询优化器不可见。

修改隐藏索引对查询优化器可见。

SET session optimizer_switch = "use_invisible_indexes=on";
# 注意：这里必须使用冒号"";

3. 索引的设计原则

为了使索引的使用效率更高，在创建索引时，必须考虑在那些字段上创建索引和创建什么类型的索引、索引设计不合理或者缺少索引都会对数据库和应用程序的性能造成障碍。高效的索引对于获得良好的性能非常重要。设计索引时，应该考虑相应准则。

3.1 数据准备

哈哈哈...自己想办法吧！

直接去自己的项目上搞一个数据量大的表。

SELECT COUNT(OBJ_CODE) FROM data_minute_history_xld
# 10958453 条数据

3.2 那些情况适合创建索引

......大哥需不需要说点什么吧！

3.2.1 有唯一性约束的数值字段

索引本身可以起到约束的作用，比如唯一索引，主键索引都是可以起到唯一性约束的，因此在我们的数据表中，如果某个字段是唯一性的，就可以直接创建唯一性索引，或者主键索引。这样可以更快速地通过该索引来确定某条记录。

业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。（来源：Alibaba）

说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找的速度是明显的。

3.2.2 频繁作为 where 查询条件的字段

在 select 语句的 where 条件中经常被使用到的字段，那么就需要给这个字段创建索引了。尤其是在数据量大的情况下，创建普通索引就可以大幅提升数据查询的效率。

该字段没有索引

SELECT * FROM data_minute_history_xld WHERE OBJ_CODE = 'ZZWS_GSYW';
# 没有索引的查询时间 9.400 秒

该字段有索引

SELECT * FROM data_minute_history_xld WHERE OBJ_CODE = 'ZZWS_GSYW'; 
# 有索引的查询时间 3.540 秒

3.2.3 经常 group by 和 order by 的字段

索引就是让数据按照某种顺序进行存储或检索，因此当我们使用 group by 对数据进行分组查询，或者使用 order by 对数据进行排序的时候，就需要对分组或者排序的字段创建对应的索引。如果待排序的列有多个，那么可以在这些字段（列）上建立联合（组合）索引。

经常 group by 的字段（列）- 该字段没有索引

SELECT OBJ_CODE,count(*) FROM data_minute_history_xld GROUP BY OBJ_CODE; 
# 没有索引的查询时间 18.890 秒

经常 group by 的字段（列）- 该字段有索引

SELECT OBJ_CODE,count(*) FROM data_minute_history_xld GROUP BY OBJ_CODE; 
# 有索引的查询时间 8.583 秒

经常 order by 的字段（列）- 该字段没有索引

SELECT * FROM data_minute_history_xld ORDER BY COLLECT_TIME DESC LIMIT 100000; 
# 没有索引的查询时间 15.213秒
-----------------------------------------------------------------------------------------------------------------------------
SELECT * FROM data_minute_history_xld ORDER BY COLLECT_TIME DESC LIMIT 30000; 
# 没有索引的查询时间 15.047 秒

经常 order by 的字段（列）- 该字段有索引

SELECT * FROM data_minute_history_xld ORDER BY COLLECT_TIME DESC LIMIT 100000; 
# 有索引的查询时间 14.695秒
EXPLAIN SELECT * FROM data_minute_history_xld ORDER BY COLLECT_TIME DESC LIMIT 100000; 
# 分析工具 - 结果为没有使用到索引
-----------------------------------------------------------------------------------------------------------------------------
SELECT * FROM data_minute_history_xld ORDER BY COLLECT_TIME DESC LIMIT 30000; 
# 有索引的查询时间 0.105 秒。 此时的分析工具 - 使用到了索引。
# 个人认为是该字段的索引的重复度或者索引文件太大了导致该索引在数据量大的情况下，索引失效

注意：单单为 order by 的字段创建索引，而你查询的是所有的字段，（在查询数据量大的情况下，索引为失效）此时索引失效。所以建议 order by 的字段索引和其他索引联合使用（联合索引）。

当我们同时有 group by 和 order by 的情况时：这里就必须使用联合（组合）索引，且 group by 的字段必须是联合索引中的第一个。

group by 和 order by 没有联合索引

SELECT OBJ_CODE,COUNT(*) FROM data_minute_history_xld GROUP BY OBJ_CODE ORDER BY COLLECT_TIME DESC;
# 没有索引的查询时间 20.947 秒

group by 和 order by 联合索引

SELECT OBJ_CODE,COUNT(*) FROM data_minute_history_xld GROUP BY OBJ_CODE ORDER BY COLLECT_TIME DESC;
# 有索引的查询时间 9.174 秒

在进行 select 查询的时候，是先进行 group by，再对数据进行 order by 的，所以在建立索引（联合索引）时，要按照 sql 语句执行顺序来建立索引（联合索引）的顺序。

3.2.4 作为 update，delete 的 where 条件的字段

update 时 where 条件的字段没有索引

UPDATE data_minute_history_xld SET target_type = '002'  WHERE obj_code = 'ZZWS_GSYW'; 
# 没有索引时使用的时间 14.773秒

update 时 where 条件的字段有索引

UPDATE data_minute_history_xld SET target_type = '001'  WHERE obj_code = 'ZZWS_GSYW'; 
# 有索引时使用的时间 3.587秒

delete 时 where 条件的字段没有索引

DELETE FROM data_minute_history_xld WHERE OBJ_CODE = 'xld_yyds'; 
# 没有索引使用的时间 17.949s

delete 时 where 条件的字段有索引

DELETE FROM data_minute_history_xld WHERE OBJ_CODE = 'xld_yyds_delte'; 
# 有索引使用的时间 9.908秒

对数据按照某个条件进行查询后再进行 update 或 delete 的操作，如果对 where 字段创建了索引，就能大幅度提升效率。原理是因为我们需要先根据 where 条件字段（列）检索（查询）出来这些记录，然后再对这些记录进行更新或删除。如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对索引进行维护。

3.2.5 distinct （去重）的字段需要创建索引

有时候我们需要对某个字段进行去重，使用 distinct，那么对这个字段创建索引，不但会提升查询的效率，同时在展示时也会按照某种顺序进行排序。

该字段没有索引

SELECT DISTINCT(obj_code) FROM data_minute_history_xld; 
# 没有索引的查询时间 11.897 秒

该字段有索引

SELECT DISTINCT(obj_code) FROM data_minute_history_xld; 
# 有索引的查询时间 0.785 秒

3.2.6 多表 join 连接操作时，创建索引注意事项

首先，连接表的数量尽量不要超过 3 张，因为每增加一张表就相当于增加了一次嵌套循环，数据量级增长会非常快，严重影响查询的效率。

其次，对 where 条件创建索引，因为 where 才是对数据条件的过滤。如果在数据量非常大的情况下，没有 where 条件过滤是非常可怕的。

最后，对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致。

多表查询时 where 条件的字段没有索引

SELECT dm.* FROM data_minute_history_xld dm LEFT JOIN base_obj bo 
	ON dm.OBJ_CODE = bo.OBJ_CODE
	WHERE dm.OBJ_CODE = 'NPQJS';
# 没有索引的查询时间 9.377 秒 104920 条

多表查询时 where 条件的字段有索引

SELECT dm.* FROM data_minute_history_xld dm LEFT JOIN base_obj bo 
	ON dm.OBJ_CODE = bo.OBJ_CODE
	WHERE dm.OBJ_CODE = 'NPQJS'; 
# 没有索引的查询时间 4.220 秒 104920 条

3.2.7 使用列的类型小的创建索引

我们这里所说的类型大小指的是该类型表示的数据范围（数据类型占用存储空间）的大小。

我们在定义表结构的时候要显式的指定字段（列）的类型，以整数类型为例，有 tinyint，mediumint，int，bigint 等，它们占用的存储空间依次递增，能表示的整数范围当然也是依次递增，如果我们想要对某个整数字段（列）建立索引的话，在表示的整数范围允许的情况下，尽量让索引列使用较小的类型，比如我们能使用 int 就不要使用 bigint，能使用 mediumint 就不要使用 int。这是因为：

数据类型越小，在查询时进行的比较操作越快
数据类型越小，索引占用的存储空间就越少，在一个数据页就可以放下更多的记录（节点就更扁平），从而减少磁盘 I/O 带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。

这个建议对于表的主键来说更加适用，因为不仅是聚簇索引中会存储主键值，其他所有的二级索引的节点处都会存储一份记录的主键值，如果主键使用更小的数据类型，也就意味着节省更多的存储空间和更高效的 I/O。

3.2.8 使用字符串前缀创建索引

假设我们的字符串很长，那存储一个字符串就需要占用很大的存储空间。在我们需要为这个字符串列建立索引时，那就意味着在对应的 B+ 树中有这么两个问题：

B+ 树索引中的记录需要把该列的完整字符串存储起来，更费时。而且字符串越长，在索引中占用的存储空间越大。
如果 B+ 树索引中索引列存储的字符串很长，那在做字符串比较时会占用更多的时间。

我们可以通过截取字段的前面一部分内容建立索引，这个就叫前缀索引。这样在查找记录时虽然不能精确的定位到记录的位置，但是能定位到相应前缀所在的位置，然后根据前缀相同的主键值 "回表" 查询完整的字符串值。即节约空间，又减少了字符串的比较时间，还大体能解决排序的问题。

例如：text 和 blog 类型的字段，进行全文检索会很浪费时间，如果只检索字段前面的若干字符，这样可以提高检索速度。

在较长的文本类型字段（列）上，建立前缀索引

# 创建前缀索引。注意：只有是文本类型的字段（列）才能使用前缀索引
ALTER TABLE data_minute_history_xld ADD INDEX index_obj_code(obj_code(20));

非常重要的问题，截取多少字符串呢？截取得多了，达不到节省索引存储空间的目的；截取得少了，重复内容太多了，字段的散列度（选择性）会降低。

那么怎么计算不同的长度的选择性呢？

先看一下字段在全部数据中的选择度（区分度：越接近 1 越好）：

公式：

select count(distinct 字段) / count(*) from 表名;

例如：

SELECT COUNT(DISTINCT obj_code) / COUNT(*) FROM data_minute_history_xld;
# 结果为：0.0001 区别度可以去死了

通过不同长度去计算，与全表的选项性对比（区分度：越接近 1 越好）：

公式：

select count(distinct left(字段名,索引长度)) / count(*),... from 表名;

例如：

SELECT 
    COUNT(DISTINCT LEFT(obj_code,40))/COUNT(*) * 100, # -- 0.0147
    COUNT(DISTINCT LEFT(obj_code,35))/COUNT(*) * 100, # -- 0.0136
    COUNT(DISTINCT LEFT(obj_code,30))/COUNT(*) * 100, # -- 0.0135
    COUNT(DISTINCT LEFT(obj_code,25))/COUNT(*) * 100, # -- 0.0135
    COUNT(DISTINCT LEFT(obj_code,20))/COUNT(*) * 100, # -- 0.0131
    COUNT(DISTINCT LEFT(obj_code,15))/COUNT(*) * 100, # -- 0.0096
    COUNT(DISTINCT LEFT(obj_code,10))/COUNT(*) * 100  # -- 0.0079
FROM data_minute_history_xld;

字符串越长"区分度"越高；
在"区分度"相差不大的情况下，优先选择短得；

拓展：Alibaba 开发手册

【强制】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。

说明：索引的长度与"区分度"是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，"区分度" 高达 90% 以上，可以使用 count(disinct left(字段名,索引长度)) / count(*) 的"区分度"来确定。

引申另一个问题：索引字段（列）前缀对排序的影响

如果使用了索引字段（列）前缀，比方说只把 A列的前12个字符放到了二级索引中，在查询中对该字段进行排序。就可能会出现问题。

SELECT * FROM xld ORDER BY A DESC;

因为二级索引中不包含完整的 A 列信息，所以无法对前 12 个字符相同，后边的字符不同的记录进行排序，也就是使用前缀索引的方式无法支持使用该索引排序，只能使用文件排序。

3.2.9 区分度高（散列性高）的列适合作为索引

字段（列）的基数，指的是某一列中不重复数据的个数

例如：某个列包含值 2，5，8，2，5，8，2，5，8，虽然有 9 条记录，但该列的基数却是 3。

也就是说，在记录行数一定的情况下，列的基数越大，该列中的值越分散；列的基数越小，该列中的值越集中。这个列的基数指标非常重要，直接影响我们是否能有效的利用索引。最好是在基数大的列建立索引，基数太小列的建立索引效果可能不好。

可以使用公式 select count(distinct a)/count(*) from dual 计算"区分度"，越接近 1 越好，一般超过 33% 就算是比较高效的索引了。

**拓展：联合索引优先把区分度高(散列性高)的列放前面。 **

3.2.10 使用最频繁的列放到联合索引的左侧

这样也可以较少的建立一些索引。同时，由于 "最左前缀原则"，可以增加联合索引的使用率。

3.2.11 在多个字段都要创建索引的情况下，联合索引优于单列索引

在使用单列查询时，如果联合索引中有该列，则也会使用到索引。所以联合索引使用率是很高。本着尽量少建立索引的原则，所以联合索引优于单列索引。

3.3 限制建立索引的数目 - （嗯...）

在实际工作中，我们也需要注意平衡，索引的数目不是越多越好。我们必须要限制每张表上的索引数量单张表索引数量不超过6个，建议。原因：

每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
索引会影响 insert，delete，update 等语句的性能，因为表中数更改的同时，索引也会进行调整和更新，会造成负担。
优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，会增加 MySQL 优化器生成执行计划时间，降低了查询的性能。

3.4 那些情况不合适创建索引

3.4.1 在 where 中使用不到的字段，不要设置索引

where 条件（包括 group by，order by）里用不到的字段不需要创建索引，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的。

SELECT
	obj_code,
	rule_id,
	collect_time
FROM data_minute_history_xld
WHERE obj_code = 'ZZWS_GSYW';

我们是按照 obj_code 字段进行检索的，所以不需要对其他字段创建索引，即使这些字段出现在 select 字段中。

3.4.2 数据量小的表最好不要使用索引

当表中的记录很少时，比如少于 1000 条，那么是不需要创建索引的。表记录太少，是否创建索引对查询效率的影响并不大。甚至说，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。

结论：在数据表中的数据行比较少的情况下，比如不到 1000 行，是不需要创建索引的

3.4.3 有大量重复数据的列上不要建立索引

在条件表达式中经常用到的不同值较多的列上建立索引，但字段中如果有大量重复数据，也不用创建索引。

比如：在学生表的 "性别" 字段上只有 "男" 与 "女" 两个不同值（区分度极低），因此无需建立索引。如果建立索引，不但不会提高查询效率，反而会严重降低数据更新速度。

举例1：要在 100 万行数据中查找其中的 50 万行（比如性别为 "男" 的数据），一旦创建了索引，你需要先访问 50 万次索引，然后再访问 50 万次数据表，这样加起来的开销比不使用索引可能还要大。

# 不建议给该字段创建索引，因为该字段重复数据太多了。

举例2：假设有一个学生表，学生总数为 100 万人，男性只有 10 个人，也就是占总人口的 10 万分之一。此时这种情况在查询 “男性” 时建立是可以提升查询效率的。

# 可以建立索引，但是只有在查询"男性"的时候，可以提高查询效率。那如果是要查询"女性"呢？结果可想而知。

总结：

通过这两个实验可以看出，索引的价值是帮你快速定位。如果想要定位的数据有很多，那么索引就失去了它的使用价值，就比如通常情况下的性别字段。

结论：当数据重复度大，比如重复度高于 10% 的时候，也不需要对这个字段使用索引。

3.4.4 避免对经常更新（update）的表创建过多的索引

第一层含义：频繁更新的字段不一定要创建索引。因为更新数据的时候，也需要更新索引，如果索引太多，在更新索引的时候也会造成负担，从而影响效率。
第二层含义：避免对经常更新的表创建过多的索引，并且索引中的列尽可能少。此时，虽然提高了查询速度，同时却会降低更新表的速度。

3.4.5 不建议用无序的值作为索引

例如：身份证，uuid（在索引比较时需要转为 ASCII，并且插入时可能造成页分裂），md5，Hash，无序长字符串等。

如果无序的列作为索引的话，会经常出现页分裂，B + 树中的数据重新排序。

3.4.6 删除不再使用或者很少使用的索引

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。DBA 应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

3.4.7 不要定义冗余或重复的索引

冗余索引

有时候有意或无意的就对同一个列创建了多个索引。比如：index(a,b,c) 相当于 index(a)，index(a,b)，index(a,b,c)

举例：

CREATE TABLE xld (
	id BIGINT PRIMARY KEY COMMENT '主键id',
	CODE VARCHAR (50) UNIQUE COMMENT '唯一code',
	NAME VARCHAR (50) NOT NULL COMMENT '名字',
	age TINYINT DEFAULT 0 COMMENT '年龄',
	time datetime COMMENT '时间',
	INDEX index_name_age (NAME, age DESC),
	INDEX index_name (NAME)
);

可以看到，通过 index_name_age 索引就可以对 name 列进行快速搜索，再创建一个专门针对 name 列的索引就算是一个冗余索引，维护这个索引只会增加维护的成本，并不会对搜索有什么好处。

重复索引

另一种情况，我们可能会对某个列重复建立索引。

举例：

CREATE TABLE xld_xld (
	id BIGINT PRIMARY KEY COMMENT '主键id',
	CODE VARCHAR (50) UNIQUE COMMENT '唯一code',
	NAME VARCHAR (50) NOT NULL COMMENT '名字',
	age TINYINT DEFAULT 0 COMMENT '年龄',
	INDEX index_code (CODE),
	INDEX index_id (id)
);

可以看到 id 即是主键，又给它定义了一个普通索引，还有 code 本身就已经有了唯一性索引，还定义了一个普通索引。id 和 code 本身就会生成索引（聚簇索引，唯一性索引），所以再定义的普通索引是重复的，这种情况必须避免。