SQL笔记 -- 索引

1. 索引的相关概念

1.1 索引概述

索引的本质：索引是数据结构，可以简单理解为“排好序的快速查找数据结构”，满足特定查找算法。这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现"高级查找算法" 。

个人理解：索引就是在存储引擎中，将数据库数据以某种数据结构（B+树）进行保存，而此数据结构是利于查询操作的，可以加快数据库数据查询的速度。创建索引的过程就是在存储层面构建数据结构的过程。索引创建后，相关列的查询操作可以直接利用这个数据结构完成。

1.2 优缺点

1.2.1 优点

（1）提高数据查询的效率，降低数据库的IO成本，这也是创建索引最主要的原因。

（2）在实现数据的参考完整性方面，可以加速表和表之间的连接。换句话说，对于有依赖关系的子表和父表联合查询时，可以提高查询速度。

（3）在使用分组和排序子句进行数据查询时，可以显著 减少查询中分组和排序的时间，降低了CPU的消耗。

1.2.2 缺点

（1）创建索引和维护索引要耗费时间，并且随着数据量的增加，所耗费的时间也会增加。

（2）索引需要占磁盘空间，除了数据表占数据空间之 外，每一个索引还要占一定的物理空间， 存储在磁盘上 ，如果有大量的索引，索引文件就可能比数据文件更快达到最大文件尺寸。

（3）虽然索引大大提高了查询速度，同时却会降低更新表的速度。当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

1.3 索引的使用原则

1.3.1 适合创建索引的情况

（1）字段的数值有唯一性的限制

（2）频繁作为 WHERE 查询条件的字段

（3）经常 GROUP BY 和 ORDER BY 的字段

（4）UPDATE、DELETE 的 WHERE 条件字段

（5）DISTINCT 字段需要创建索引

（6）多表 JOIN 连接操作时，对 WHERE 条件字段和用于连接的字段创建索引

（7）使用列的类型小的创建索引

（8）使用字符串前缀创建索引，参考count(distinct left(列名, 索引长度))/count(*)来确定索引长度，越接近于1越好，说明越有区分度

（9）区分度高(散列性高)的列适合作为索引，参考公式select count(distinct a) / count(*) from t1 计算区分度，越接近1越好

（10）使用最频繁的列放到联合索引的左侧

（11）在多个字段都要创建索引的情况下，联合索引优于单值索引

1.3.2 不适合创建索引的情况

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

（2）数据量小的表最好不要使用索引

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

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

（5）不建议用无序的值作为索引

（6）删除不再使用或者很少使用的索引

（7）不要定义夯余或重复的索引

2. 创建索引

2.1 创建表的时候创建索引

-- 语法格式
CREATE TABLE table_name [col_name data_type]
[UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC | DESC]

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

举例：

--为字段dept_id 创建普通索引
CREATE TABLE emp(
emp_id INT,
emp_name VARCHAR(20),
dept_id INT,
INDEX(dept_id)
);

--为字段emp_name 创建唯一索引
CREATE TABLE emp(
emp_id INT ,
emp_name VARCHAR(20) ,
dept_id INT,
UNIQUE INDEX idx_emp_name(emp_name)
);

--为字段emp_id 创建主键索引
CREATE TABLE emp(
emp_id INT ,
emp_name VARCHAR(20) ,
dept_id INT,
PRIMARY KEY(emp_id)
);

--为字段emp_id, emp_name 创建组合索引
CREATE TABLE emp(
emp_id INT ,
emp_name VARCHAR(20) ,
dept_id INT,
INDEX multi_idx(emp_id, emp_name)
);

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

2.2.1 使用ALTER TABLE语句创建索引

-- 语法格式
ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY]
[index_name] (col_name[length],...) [ASC | DESC]

举例：

--为字段dept_id 创建普通索引
ALTER TABLE emp ADD INDEX(dept_id)

--为字段emp_name 创建唯一索引
ALTER TABLE emp ADD UNIQUE INDEX idx_emp_name(emp_name)

--为字段emp_id 创建主键索引
ALTER TABLE emp ADD PRIMARY KEY(emp_id)

--为字段emp_id, emp_name 创建组合索引
ALTER TABLE emp ADD INDEX multi_idx(emp_id, emp_name)

2.2.2 使用CREATE INDEX创建索引

-- 语法格式
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
ON table_name (col_name[length],...) [ASC | DESC]

举例：

--为字段dept_id 创建普通索引
CREATE INDEX ON emp(dept_id)

--为字段emp_name 创建唯一索引
CREATE UNIQUE INDEX idx_emp_name ON emp(emp_name)

--为字段emp_id, emp_name 创建组合索引
CREATE INDEX multi_idx ON emp(emp_id, emp_name)

3. 删除索引

3.1 使用ALTER TABLE删除索引

-- 语法格式
ALTER TABLE table_name DROP INDEX index_name;

举例：

--为字段dept_id 删除普通索引
ALTER TABLE emp DROP INDEX dept_id

--为字段emp_name 删除唯一索引
ALTER TABLE emp DROP INDEX idx_emp_name

--为字段emp_id, emp_name 删除组合索引
ALTER TABLE emp DROP INDEX multi_idx

3.2 使用DROP INDEX语句删除索引

-- 语法格式
DROP INDEX index_name ON table_name;

举例：

--为字段dept_id 删除普通索引
DROP INDEX dept_id ON emp 

--为字段emp_name 删除唯一索引
DROP INDEX idx_emp_name ON emp 

--为字段emp_id, emp_name 删除组合索引
DROP INDEX multi_idx ON emp