索引的设计原则

一、适合创建索引的情况

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

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

3、经常 GROUP BY 和 ORDER BY 的列

4、UPDATE、DELETE 的 WHERE 条件列

5、DISTINCT 字段需要创建索引

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

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

比如：

CREATE TABLE user (
	info VARCHAR(120) NOT NULL
);

ALTER TABLE user ADD INDEX (info(12));

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

公式：

select count(distinct column) / count(*) from table_name;

例如：

select count(distinct left(info,10)) / count(*) as sub10 from user; #截取前10个字符的选择度
select count(distinct left(info,15)) / count(*) as sub10 from user; #截取前15个字符的选择度
select count(distinct left(info,20)) / count(*) as sub10 from user; #截取前20个字符的选择度
select count(distinct left(info,25)) / count(*) as sub10 from user; #截取前25个字符的选择度

结果越接近1越好

拓展：

在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。 Alibaba《Java开发手册》

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

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

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

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

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

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

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

二、不适合创建索引的情况

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

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

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

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

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

容易产生页分裂

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

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

CREATE TABLE user (
	id INT PRIMARY KEY,
	name VARCHAR (100),
	age INT (11) NOT NULL,
	info VARCHAR (100),
	INDEX idx_name_age (name(10),age),
	INDEX idx_name (name(10))
);

例如：已经有可以通过 idx_name_age 索引对 name 列快速搜索了，就不需要创建一个专门针对 name 列的索引了，这个索引就是冗余索引了，只会增加维护成本，并不能增加查询效率。