MySQL中的索引——适合创建索引的情况

1.适合创建索引的情况

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

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

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

比如 student_info 数据表（含 100 万条数据），假设我们想要查询 student_id=123110 的用户信息。

①查看student_info表中的索引

可以看出，我们没有对student_id字段创建索引。

②进行如下查询，耗时220ms

③添加索引

alter table student_info add index idx_sid(student_id);

④再查询。耗时0ms。性能提升很大！

3、经常 GROUP BY 和 ORDER BY 的列

        索引其实就是让数据按照某种顺序进行存储或检索。当我们使用 GROUP BY 对数据进行分组查询，或者使用 ORDER BY 对数据进行排序的时候，如果 对分组或者排序的字段建立索引，本身索引的数据就已经排好序了，进行分组查询和排序操作性能不好吗？另外，如果待排序的列有多个，那么可以在这些列上建立 组合索引 。

使用GROUP BY和ORDER BY，先看看不加索引的情况：

没有添加索引，大概执行时间900ms

加上索引

SELECT student_id,COUNT(*) AS num FROM student_info
GROUP BY student_id
LIMIT 100;

执行时间只要40ms，大概相差20多倍！

---

再考虑复杂一点的情况，group by和order by一起使用

        这是只给student_id和create_time两列单独添加单列索引的情况。可以看到查询速度很慢，要5s多。

ALTER TABLE student_info ADD INDEX idx_sid_cre_time(student_id,create_time DESC);

添加联合索引

使用联合索引只需要耗时0.25ms！

注意：

        如果我们仅仅使用GROUP BY 或者 ORDER BY，且后面只有一个字段，则单独建立索引；如果后面跟多个字段，则建立联合索引。如果既有GROUP BY 又有 ORDER BY，那就建立联合索引，且GROUP BY的字段写在(联合索引的)前面，ORDER BY的字段写在(联合索引的)后面。8.0后的版本也可以考虑使用降序索引。

---

4、UPDATE、DELETE 的 WHERE 条件列

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

 UPDATE student_info SET student_id = 10002
 WHERE NAME = '462eed7ac6e791292a79';

---

5、DISTINCT 字段需要创建索引

        有时候我们需要对某个字段进行去重，使用 DISTINCT，那么对这个字段创建索引，也会提升查询效率。创建索引以后，在B+树种相同的字段就会在一起，这样我们DISTINCT去重的时候，速度就会快很多。

比如，我们想要查询课程表中不同的 student_id 都有哪些，如果我们没有对 student_id 创建索引，执行 SQL 语句：

SELECT DISTINCT(student_id) FROM `student_info`; 

运行结果（600637 条记录，运行时间 0.683s ）

如果我们对 student_id 创建索引，再执行 SQL 语句:

SELECT DISTINCT(student_id) FROM `student_info`;

运行结果（600637 条记录，运行时间0.010s ）
你能看到 SQL 查询效率有了提升，同时显示出来的 student_id 还是按照 递增的顺序 进行展示的。这是因为索引会对数据按照某种顺序进行排序，所以在去重的时候也会快很多。

---

6、多表 JOIN 连接操作时，创建索引注意事项

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

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

        注意：对于用连接的字段创建索引，这些字段在多张表中的 类型必须一致 。比如 course_id 在student_info 表和 course 表中都为 int(11) 类型，而不能一个为 int 另一个为 varchar 类型。否则在查询时，虽然也会帮我们进行隐式的类型转换，转换时会使用函数，但会导致索引失效。

可以看到，没有使用索引后，多表连接查询效率高了两百多倍！

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

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

 注意：举个例子，这里有一个varchar(120)长度的字段，作为索引，我们取前12个长度作为索引。就算某两个字段前12个字符串重复了，但只要回表查询一下，就能确定真正需要的字段。因此，就算由于重复，也不会有所影响。

创建一张商户表，因为地址字段比较长，在地址字段上建立前缀索引

create table shop(address varchar(120) not null);

alter table shop add index(address(12));

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

先看一下字段在全部数据中的选择度:

select count(distinct address) / count(*) from shop;

通过不同长度去计算，与全表的选择性对比:

公式:

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

例如：

select count(distinct left(address,10)) / count(*) as sub10, -- 截取前10个字符的选择度 
count(distinct left(address,15)) / count(*) as sub11, -- 截取前15个字符的选择度 
count(distinct left(address,20)) / count(*) as sub12, -- 截取前20个字符的选择度 
count(distinct left(address,25)) / count(*) as sub13 -- 截取前25个字符的选择度
from shop;

【 强制 】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。
说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会 高达90% 以上 ，可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定。