MySQL的索引

索引是什么？

        索引是为了加速对表中数据行的检索而创建的一种分散的存储结构。索引是针对表而建立的，它是由数据页面以外的索引页面组成的，每个索引页面中的行都会含有逻辑指针，以便加速检索物理数据。 ​

通俗来说:索引是好比书的目录，可以更加快速准确的找到想要搜寻的内容。

优点：提升数据查找的效率  

缺点：创建的需要时间较长，需要占用额硬盘空间，需要维护，会降低数据增删改的速度

索引的类型

索引按功能分为：

• 普通索引，没有特别的约束

• 主键索引，数据唯一不能为空，一个表只能有一个

• 唯一索引，数据唯一

• 全文索引，对长文本进行匹配，text

• 组合索引，将多列组合到一起进行查询

按索引结构分为：

• 聚簇索引

  索引键的顺序和实际数据的顺序一致

  类似：新华字典的拼音目录

  一个表只能有一个聚簇索引

  主键属于聚簇索引

• 非聚簇索引

  索引键的顺序和实际数据的顺序不一致

  类似：新华字典的偏旁目录

  一个表可以有多个

创建普通索引：
create index 索引名 on 表(列);
alter table 表 add index 索引名(列);

创建主键索引
alter table 表 add primary key 索引名(列);

创建唯一索引
alter table 表 add unique index 索引名(列);

创建全文索引
alter table 表 add fulltext index 索引名(列);

创建组合索引
alter table 表 add index 索引名(列,列,列,列);

删除索引
drop index 索引名 on 表;

查询索引
show index from 表;

索引生效和失效情况

生效：在列创建索引后，使用列排序或查询

失效：

1. 模糊查询，关键字前面加% like '%关键字'

2. 使用or关键字，or两边的字段上都要有索引否则会失效

3. 查询条件中字段上使用函数， where length(name) = 5

4. 查询条件中字段参与运算，where price + 1 > 4

5. 查询条件中字段使用>、<、>=、<= 没有同时设置最大值和最小值

6. 查询条件中使用组合索引，没有使用最左边的字段（最左前缀原则，将最重要的列放左边） 组合索引 a,b,c where b = xx and c = xx

查看索引使用情况

explain 查询语句

 

字段解释：

• select_type 查询类型，simple简单、连接、子查询

• table 表名

• type 索引的使用类型

• prossible_keys 可能使用到的索引

• key 实际使用的索引

• ken_len 索引键长度

• ref 索引引用

• rows 行数

• extra 额外信息

索引的数据结构

索引的数据结构使用B+Tree

B+Tree是从B-Tree演变来的，B-Tree是平衡多叉树，采用二分查找搜索，树的查找效率跟树的高度有关，高度越低，效率越高

 B+Tree在B-Tree的基础上进行优化，将除叶子节点外的所有数据，放到叶子节点，每一层只保存键和指针，每层能保存更多节点，树的高度降低，查找效率提升