数据库索引

背景：MySQL数据库，InnoDB存储引擎

1.何为数据库索引

**索引是对数据库表中一个或多个列的值进行排序的结构。**正是因为进行了排序，才会使索引可快速访问数据库表中的特定信息。当然，索引本身也很重要。

2.索引分类

实际上根据不同划分标准有多种分类方式

2.1 聚集（聚簇）索引与非聚集（聚簇）索引

1）聚集索引

1. 在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。
2. 每张表只允许有一个聚集索引，所以聚集索引一般是表中的主键索引，如果表中没有显示指定主键，则会选择表中的第一个不允许为NULL的唯一索引，如果还是没有的话，就采用Innodb存储引擎为每行数据内置的6字节ROWID作为聚集索引。

2)非聚集（聚簇）索引

除聚集索引以外的其他索引

2.2 单列索引和多列（联合，组合，复合）索引

1）单列索引

顾名思义就是只匹配一列字段创建的索引。需要注意的是聚集索引是特殊的单列索引

2） 多列索引

顾名思义就是匹配多列字段创建的索引，但是一个索引可以包含最多16列。需要注意最左前缀匹配规则
举例创建多列索引：

ALTER TABLE people ADD INDEX lname_fname_age (lame,fname,age);

最左前缀匹配原则：

1. 如果你创建一个联合索引, 那 这个索引的任何前缀都会用于查询, (col1, col2, col3)这个联合索引的所有前缀 就是(col1), (col1, col2), (col1, col2, col3), 包含这些列的查询都会启用索 引查询.
2. mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整
3. =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

顾名思义，就是最左优先，上例中我们创建了lname_fname_age多列索引,相当于创建了(lname)单列索引，(lname,fname)组合索引以及(lname,fname,age)组合索引**。需要注意的是在mysql中执行查询时，只能使用一个索引。**

3. 索引失效

3.1 单列索引

MySQL索引在一些情况下会失效
（1）查询条件使用（！= 或者 < >）判断，使用 is null / is not null 判断

SELECT * FROM `t_index` WHERE key1 <> '1';

（2）查询条件类型不一致(key1为非数字乐类型)

SELECT * FROM `t_index` WHERE key1 = 1;

（3）对建立索引的列使用函数计算

SELECT * FROM `t_index` WHERE key1 + 1 = 1;
SELECT * FROM `t_index` WHERE CHAR_LENGTH(key1) = 1;

（4）模糊查询的前缀模糊(第二行的索引失效，其他行索引生效)

SELECT * FROM `t_index` WHERE key1 LIKE  '3';
SELECT * FROM `t_index` WHERE key1 LIKE  '%3';
SELECT * FROM `t_index` WHERE key1 LIKE  '3%';

3.2 组合索引

（1）查询条件使用（！= 或者 < >）判断，使用 is null / is not null 判断

SELECT * FROM `t_index` WHERE key1 <> '1' AND key2 = '2' AND key3 = '3';
SELECT * FROM `t_index` WHERE key1 = '1' AND key2 <> '2' AND key3 = '3';
SELECT * FROM `t_index` WHERE key1 = '1' AND key2 = '2' AND key3 <> '3';

结果：全部不走索引
（2）查询条件类型不一致

SELECT * FROM `t_index` WHERE key1 = 1 AND key2 = '2' AND key3 = '3';
SELECT * FROM `t_index` WHERE key1 = '1' AND key2 = 2 AND key3 = '3';
SELECT * FROM `t_index` WHERE key1 = '1' AND key2 = '2' AND key3 = 3;

结果：从第一个查询条件开始，第N个参数类型不一致，索引能使用前N-1列
（3）查询条件使用函数计算

SELECT * FROM `t_index` WHERE key1 + 1 = '1' AND key2 = '2' AND key3 = '3';
SELECT * FROM `t_index` WHERE key1 = '1' AND key2 + 1 = '2' AND key3 = '3';
SELECT * FROM `t_index` WHERE key1 = '1' AND key2 = '2' AND key3 + 1 = '3';

结果：从第一个查询条件开始，第N个参数类型不一致，索引能使用前N-1列
注意点：不要在索引上做任何操作（计算、函数、自动/手动类型转换），不然会导致索引失效而转向全表扫描
小总结：

4.何时不使用索引？

虽然索引可以提高查询效率，但是创建索引会增加额外的磁盘空间开销；对索引进行频繁增删会频繁调整B+树的结构，会增加性能开销；在数据量较小的情况下，是否建立索引对查询效率影响不大。综上，可知并不是所有情况都适合建立数据库索引。
哪些情况不适合建索引？

1. 表记录太少。

2. 经常增删改的表的字段。

3. 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。如果某个数据列包含许多重复的内容，为它建立索引就没太大的实际效果。