MySQL：索引

1.定义

索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。
索引也是一张表，该表中存储着索引的值和这个值的数据所在行的物理地址，使用索引后可以不用扫描全表来定位某行的数据，而是通过索引表来找到该行数据对应的物理地址

2.原理

在 InnoDB 中，索引使用的数据结构是 B+ Tree，这里的 B 是 Balance 的意思。B 类树的一个很鲜明的特点就是树的层数比较少，而每层的节点都非常多，树的每个叶子节点到根节点的距离都是相同的
树的每一个节点都是一个数据页，这样每个节点只需要一次 IO 就可以全部读取。这样的结构保证了查询数据时能尽量少地进行磁盘 IO，同时保证 IO 的稳定性。

3.适用字段

1. 表的主键、外键必须有索引。外键是唯一的，而且经常会用来查询
2. 经常与其他表进行连接的表，在连接字段上应该建立索引
3. 数据量超过300的表应该有索引
4. 重要的SQL或调用频率高的SQL，比如经常出现在where子句中的字段，order by,group by, distinct的字段都要添加索引
5. 经常用到排序的列上，因为索引已经排序
6. 经常用在范围内搜索的列上创建索引，因为索引已经排序了，其指定的范围是连续的

4.索引分类（从逻辑角度）

4.1 普通索引

普通索引是最基本的索引，它没有任何限制，允许在定义索引的列中插入重复值和空值
直接创建：

create index index_name on table_name (column(length));

4.2 唯一索引

索引列的值必须唯一，允许有空值，如果是组合索引，列值的组合必须唯一

create unique index index_name on table_name (column(length));

4.3 主键索引

主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值，一般是在创建表的时候指定主键，主键默认就是主键索引

create table table_name(
........
primary key(column)
)

4.4组合索引

多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用

alter table table_name add index index_name(column,column,column..);

4.5全文索引

全文索引（fulltext）仅可以适用于MyISAM引擎的数据表；作用于CHAR、VARCHAR、TEXT数据类型的列。

5.索引分类（从物理存储角度）

5.1聚集索引

聚集索引确定表中数据的物理顺序，一个表中只能包含一个聚集索引，但该索引可以包含多个列，聚集索引对于经常要搜索范围值的列特别有效，使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻

5.2非聚集索引

可能是通过其他算法规则构成的一种索引结构，索引的结构和实际数据存储的结构是不同的

6.索引设计原则

• 避免对经常更新的表创建过多的索引，索引中的列要尽可能的小；经常要查询的字段应该创建索引，但是要避免添加不必要的字段
• 数据量小的表最好不要使用索引
• 在条件表达式中经常用到的不同值较多的列上建立索引，在不同值少的列上不要建立索引
• 当唯一性是某种数据本身的特性时，建立唯一索引
• 在频繁进行排序或者分组的列上建立索引

7.索引失效情况

• 如果条件中有or，即使其中有部分条件带索引也不会使用（要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引）
• 对于复合索引，如果不使用前列，后续列也将无法使用
• like查询是以%开头
• 存在索引列的数据类型隐形转换，则用不上索引，比如列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
• where 子句里对索引列上有数学运算，用不上索引
• where 子句里对有索引列使用函数，用不上索引
• 如果mysql估计使用全表扫描要比使用索引快,则不使用索引