MySQL索引的使用，索引的使用场景

上一篇我们主要是对索引设计体系的一个讲解，本篇主要是对索引使用的一个讲解

一、索引概述

1.索引的分类

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

分类	含义	特点	关键字
主键索引	针对表中主键创建的索引，不为空不能重复	默认自动创建，只能有一个	PRIMARY
普通索引	没有任何限制，可以创建在任何数据类型中	可以有多个
唯一索引	索引值必须唯一，可以加快查询速度	可以有多个	UNIQUE
全文索引	查找的是文本中的关键词，而不是比较索引中的值	可以有多个	FULLTEXT

二、索引的创建

创建索引的方式：

1. 在创建表的时候创建
2. 使用ALTER TABLE语句在存在的表上创建索引
3. 使用CREATE INDEX在已存在的表上添加索引

1.创建表时创建索引

如下，创建两个表：

CREATE TABLE emp(
	emp_id INT PRIMARY KEY AUTO_INCREMENT,
	emp_name VARCHAR(20) UNIQUE,
	dept_id INT,
	CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept(dept_id)
);
CREATE TABLE dept(
	dept_id INT PRIMARY KEY AUTO_INCREMENT,
	dept_name VARCHAR(20)
);

查看emp的索引：

mysql> show index from emp;
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| emp   |          0 | PRIMARY        |            1 | emp_id      | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| emp   |          0 | emp_name       |            1 | emp_name    | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| emp   |          1 | emp_dept_id_fk |            1 | dept_id     | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.00 sec)

一共自动创建了三个索引：

• 主键索引，索引名为PRIMARY
• 唯一索引，索引名就是有唯一性约束的那个字段名
• 外键字段创建的索引(普通索引)，索引名就为外键名

结论：创建表时主键约束、唯一约束、外键约束上会隐式的创建索引

查看dept的索引：

mysql> show index from dept;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| dept  |          0 | PRIMARY  |            1 | dept_id     | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.00 sec)

也可以在创建表时，显式的创建索引，语法如下*：

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指定索引的名称，为可选参数，如果不指定，那么索引名就为字段名；
• col_name为需要创建索引的字段列，可以是单列索引，也可以是联合索引，用逗号分隔；
• length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；
• ASC或DESC指定升序或者降序的索引值存储。

示例：

1. 创建普通索引

在book表中的year_publication字段上建立普通索引，SQL语句如下：

CREATE TABLE book(
    book_id INT ,
    book_name VARCHAR(100),
    authors VARCHAR(100),
    year_publication YEAR,
    INDEX(year_publication)        #索引名为year_publication
);

2. 创建唯一索引

CREATE TABLE test1(
    id INT NOT NULL,
    name varchar(30) NOT NULL,
    UNIQUE INDEX uk_idx_id(id)
);

 创建了唯一索引后，对应的字段会自动加上唯一性约束；另外，给有唯一性约束的字段加上普通索引，该索引会自动变为唯一性索引

3.主键索引

设定为主键后数据库会自动建立索引，innodb为聚簇索引，语法：

• 随表一起建索引：

CREATE TABLE student (
    id INT(10) UNSIGNED AUTO_INCREMENT ,
    student_no VARCHAR(200),
    student_name VARCHAR(200),
    PRIMARY KEY(id)
);

• 删除主键索引(删除主键约束自动删除)：

ALTER TABLE student drop PRIMARY KEY ;

• 修改主键索引：必须先删除掉(drop)原索引，再新建(add)索引

4 创建联合索引

举例：创建表test3，在表中的id、name和age字段上建立组合索引，SQL语句如下：

CREATE TABLE test3(
    id INT(11) NOT NULL,
    name CHAR(30) NOT NULL,
    age INT(11) NOT NULL,
    info VARCHAR(255),
    INDEX multi_idx(id,name,age)
);

 创建联合索引时字段的顺序是有讲究的，不是乱序的

5. 创建全文索引

创建表test4，在表中的info字段上建立全文索引，SQL语句如下：

CREATE TABLE test4(
    id INT NOT NULL,
    name CHAR(30) NOT NULL,
    age INT NOT NULL,
    info VARCHAR(255),
    FULLTEXT INDEX futxt_idx_info(info)
) ENGINE=MyISAM;

全文索引用match+against方式查询，速度更快

2. 在已经存在的表上创建索引

1.使用ALTER TABLE语句创建索引，基本语法如下：

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

2.使用CREATE INDEX创建索引，在MySQL中， CREATE INDEX被映射到一个ALTER TABLE语句上，基本语法结构为：

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
ON table_name (col_name[length],...) [ASC | DESC]

三、删除索引

 1.使用ALTER TABLE删除索引，基本语法格式如下：

ALTER TABLE table_name DROP INDEX index_name;

2.使用DROP INDEX语句删除索引，基本语法格式如下：

DROP INDEX index_name ON table_name;

添加了自增约束的字段唯一索引不能删除；

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

四、隐藏索引

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

1.隐藏索引的创建只需要在索引的后面加上INVISIBLE关键字即可。如：

CREATE TABLE tablename(
    propname1 type1[CONSTRAINT1],
    propname2 type2[CONSTRAINT2],
    ……
    INDEX [indexname](propname1 [(length)]) INVISIBLE
);

CREATE INDEX indexname
ON tablename(propname[(length)]) INVISIBLE;

2.切换索引可见状态，已存在的索引可通过如下语句切换可见状态：

ALTER TABLE tablename ALTER INDEX index_name INVISIBLE; #切换成隐藏索引
ALTER TABLE tablename ALTER INDEX index_name VISIBLE; #切换成非隐藏索引

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

五、哪些情况下适合创建索引

首先准备好两张表：

#1.创建学生表和课程表
CREATE TABLE `student_info` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `student_id` INT NOT NULL ,
    `name` VARCHAR(20) DEFAULT NULL,
    `course_id` INT NOT NULL ,
    `class_id` INT(11) DEFAULT NULL,
    `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `course` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `course_id` INT NOT NULL ,
    `course_name` VARCHAR(40) DEFAULT NULL,
     PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

并且在表中增加一定的数据

select count(*) from student_info; #1000000条数据
select count(*) from course;   #100条数据

这两张表会隐式地创建主键索引，其他字段没有索引。

1.频繁作为 WHERE 查询条件的字段

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

示例：

假如经常需要通过student_id去查找学生信息，这是未加上索引时的一个平均花费

select * from student_info where student_id = '1'; #总花费287毫秒

给该字段加上索引后如下

#创建索引
create index idx_student_id on student_info(student_id);  #创建索引花费12s
#通过索引去查找
select * from student_info where student_id = '1'; #平均花费23毫秒

可以看到通过索引去查找加快了查找速度。

但是创建索引还是需要花费一些时间的，通过提前创建好索引去提升整体的查询效率。

可以通过加上explain关键字列出sql语句的执行计划，可以查看sql语句用到的索引：

mysql> explain select * from student_info where student_id = '1';
+----+-------------+--------------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
| id | select_type | table        | partitions | type | possible_keys  | key            | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | student_info | NULL       | ref  | idx_student_id | idx_student_id | 4       | const |    5 |   100.00 | NULL  |
+----+-------------+--------------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

如上查看执行计划的结果中，possible_keys列出该条语句可能使用的索引，key字段表示实际用到的索引。

2. 有唯一性限制的字段

字段的值是唯一值的时候优先考虑加上索引，索引本身也可以起到约束作用，且唯一索引查询的速度会比普通索引更快速。

3.经常GROUP BY和ORDER BY的列

索引已经将数据排好序了，因此当我们使用GROUP BY对数据进行分组查询，或者使用ORDER BY对数据进行排序的时候，通过索引就可以十分快速(避免了排序操作) 。如果待排序的列有多个，那么可以在这些列上建立联合索引 。

示例：

当我们需要执行如下的sql语句：

#没有索引时，平均需要954毫秒
select student_id,count(*)
from student_info
group by student_id 
order by create_time    

我们可以给这两个字段分别做一个索引，测试如下：

#创建两个普通索引
alter table student_info add index idx_student_id(student_id); #12秒
alter table student_info add index idx_create_time(create_time);

 #2.1秒,根据student_id的索引进行查找
select student_id,count(*),create_time
from student_info
group by student_id 
order by create_time;         

此时，创建索引后查找的平均时间竟然变长了。我们来查看它的执行计划：

mysql> explain select student_id,count(*),create_time
    -> from student_info
    -> group by student_id
    -> order by create_time;
+----+-------------+--------------+------------+-------+----------------+----------------+---------+------+--------+----------+---------------------------------+
| id | select_type | table        | partitions | type  | possible_keys  | key            | key_len | ref  | rows   | filtered | Extra                           |
+----+-------------+--------------+------------+-------+----------------+----------------+---------+------+--------+----------+---------------------------------+
|  1 | SIMPLE      | student_info | NULL       | index | idx_student_id | idx_student_id | 4       | NULL | 918082 |   100.00 | Using temporary; Using filesort |
+----+-------------+--------------+------------+-------+----------------+----------------+---------+------+--------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec)

可以看到，使用的是student_id的索引，没有用到create_time的索引，因为group by是先于order by执行的。在底层，它是先从student_id的索引中取出数据，并且分好组，然后再根据每个组的create_time去做一个排序。

在这种情况下应该使用联合索引，如下：

#创建student_id和create_time组成的联合索引
alter table student_info add index idx_student_id_create_time(student_id,create_time);
#平均需要492毫秒
select student_id,count(*),create_time
from student_info
group by student_id 
order by create_time;   

查看它的执行计划：

+----+-------------+--------------+------------+-------+-------------------------------------------+----------------------------+---------+------+--------+----------+----------------------------------------------+
| id | select_type | table        | partitions | type  | possible_keys                             | key                        | key_len | ref  | rows   | filtered | Extra                                        |
+----+-------------+--------------+------------+-------+-------------------------------------------+----------------------------+---------+------+--------+----------+----------------------------------------------+
|  1 | SIMPLE      | student_info | NULL       | index | idx_student_id,idx_student_id_create_time | idx_student_id_create_time | 10      | NULL | 918082 |   100.00 | Using index; Using temporary; Using filesort |
+----+-------------+--------------+------------+-------+-------------------------------------------+----------------------------+---------+------+--------+----------+----------------------------------------------+
1 row in set, 1 warning (0.00 sec)

实际是用到的是联合索引搜索的，所以速度会变快。

需要注意的是创建联合索引时group by的字段必须先于order by，否则联合索引会失效。

4.UPDATE、DELETE的WHERE条件列

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

例子：

#平均676毫秒
update student_info set student_id=10001 where name='Mpnwrw';
#创建索引
alter table student_info add index idx_name(name);
#平均21毫秒
update student_info set student_id=10001 where name='Mpnwrw';

5.DISTINCT字段需要创建索引

有时候我们需要对某个字段进行去重，使用DISTINCT，那么对这个字段创建索引，也会提升查询效率。

6.多表JOIN连接操作时

对WHERE条件创建索引 ，因为WHERE才是对数据条件的过滤。

其次，对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致 。比如 course_id 在 student_info 表和 course 表中都为 int(11) 类型，而不能一个为int另一个为varchar类型。

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

类型表示的范围越小：

• 查询时进行的比较操作越快
• 索引占用的空间越小，在一个数据页能存放更多的记录，树的高低相对偏低

8.使用字符串前缀创建索引

理由和前一点相同。

截取字段的前一部分内容建立索引，叫做前缀索引。这样在查找时虽然不能精确定位，但是能定位到相应前缀所在位置，然后根据前缀相同记录的主键值回表查询完整的字符串值。节约空间，又减少了字符串的比较时间。

create table shop(address varchar(120) not null);
alter table shop add index(address(12));

问题是，截取多少呢？截取得多了，达不到节省索引存储空间的目的；截取得少了，重复内容太多，字 段的散列度(选择性)会降低。怎么计算不同的长度的选择性呢？

公式：

count(distinct left(列名, 索引长度))/count(*)

通过计算它的区分度来选择，越接近于1就越精确，在其中做一个平衡，一方面区分度要足够高，其次前缀长度尽可能短。

前缀索引列无法支持使用索引排序，因为有些时候无法区分出来。只能使用文件排序

9.区分度高(散列性高)的列适合作为索引

比如性别字段，区分度很低，不建议使用索引。一般区分比超过33%即可。

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

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

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

这种情况下，尽可能建立联合索引。

六、限制索引的数量

建议单张表的索引不超过6个。原因：

1. 占用磁盘空间
2. 影响insert、delete、update的性能
3. 优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，会增加MysQL优化器生成执行计划时间，降低查询性能。

七、哪些情况不适合创建索引

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

WHERE条件(包括GROUP BY、ORDER BY）里用不到的字段不需要创建索引，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的。

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

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

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

字段中如果有大量重复数据，不用创建索引。比如在学生表的性别字段上只有男与女两个不同值，因此无须建立索引。如果建立索引，不但不会提高查询效率，反而会严重降低数据更新速度。

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

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

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

如：身份证号、UUID、MD5……，避免页分裂的场景

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

联合索引最左字段就可以当成一个单列索引使用。