mysql索引小结

什么是索引

索引是创建在表上的，帮我们快速检索数据的一种数据结构。

索引的分类

主键索引：表设立主键之后会自动创建一个主键索引，innodb引擎里面主键索引是聚簇索引

普通索引：普通索引也就是单值索引，只包含一个列，一个表可以有多个普通索引

唯一索引：索引列的值是唯一的，和主键索引的区别是唯一索引的值可以为空

组合索引：一个索引包含多个列（这里有面试题，也就是索引失效的问题）

全文索引：全文索引只能用于InnoDB或MyISAM表，只能为CHAR、VARCHAR、TEXT列创建

索引的优缺点

1、优缺点

大大加快数据检索的书读，降低IO成本
索引会给数据排序，降低了CPU的消耗

2、索引会消耗磁盘空间

数据库增删改的时候会动态维护索引，导致增删改的时间变长

什么时候适合建索引

适合建立索引的场景

1、主键自动建立唯一索引

2、频繁作为查询条件的字段应该建立索引

3、查询中与其他表关联的字段，外键关系建立索引

4、单键/组合索引的选择问题，组合索引性价比更高

5、查询中排序的字段

不适合建立索引的场景

1、表记录少的

2、经常增删改的表或者字段

3、where条件里用不到的字段不创建索引

4、过滤性不好的不适合建索引，比如（性别）

创建索引

1、创建表的时候

create table `person` (
 `id` int UNSIGNED auto_increment,
 `name` varchar(64),
 `age` int,
 `sex` varchar(5),
  PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

2、create方式

CREATE INDEX nameIndex ON person (name);

3、alter方式

ALTER TABLE person ADD INDEX ageIndex(age);

删除索引

1、drop方式

drop index index_name on table_name ;

2、alter方式

alter table table_name drop index index_name ;

查看表的索引

show index from table_name ;

索引底层

innodb底层结构用的是B+tree，一个表有一个聚簇索引的B+tree，以及一些其他的非聚簇索引B+tree，聚簇索引的叶子节点上是行数据，而非聚簇索引的叶子结点上面是所以包含的列以及聚簇索引的主键，

当我们通过非聚簇索引查询数据的时候首先查到的是聚簇索引的主键，然后根据主键再去查询聚簇索引得到行数据，这个过程我们也称之为回查。

MyISAM里面的都是非聚簇索引（包括主键索引），他们的数据是包含在一个独立的空间，不像聚簇索引是保存在聚簇索引。

B+tree和B-tree的区别

B+tree的根节点和枝结点不存储数据，只存储指针之类的，只有叶子结点存储数据
B-tree的各个节点都存储数据

聚簇索引

一个表只能有一个聚簇索引，叶子结点上存放的是行数据。

非聚簇索引

一个表可以有多个非聚簇索引，非聚簇索引的叶子结点上面是所包含的列以及聚簇索引的主键

索引失效

1、 违背了最左前缀规则导致索引失效；

2、 在索引列上进行计算，函数，类型转换 导致索引失效

3、不等于 != <> 索引失效

解决方法：使用覆盖索引（select 的字段都在索引列上）

4、 like 以通配符 %为开头的索引失效

5、隐性转换，比如字符串数字 ‘1’不加引号

6、or的前后条件只要有一个不是索引字段都不走索引

7、通过索引扫描的行记录数超过全表的10%~30%左右,优化器不会走索引，而变成全表扫描

回表查询

当我们通过非聚簇索引查询数据的时候首先查到的是聚簇索引的主键，然后根据主键再去查询聚簇索引得到行数据，这个过程我们也称之为回查

索引覆盖

当我们通过非聚簇索引查询数据的时候发现要查询的数据在非聚簇索引里面都存在，那么就不需要再去查询聚簇索引，这个也称之为索引覆盖。