MySQL索引学习

一、索引的分类

1、从功能逻辑上分：普通索引、唯一索引、全文索引、主键索引。
2、按物理实现方式分：聚簇索引和非聚簇索引。
3、按作用字段个数分：单列索引和联合索引。

二、索引的创建

1、隐式的方式创建

在声明有主键约束、唯一性约束、外键约束的字段上，会自动添加相关索引。

2、显示的创建索引

a、在创建表的时候添加索引：

b、在已经存在的表上创建索引：

alter table … add …

create … index … on …

三、索引的删除

1、使用alter table

alter table 表名 drop index 索引名;

2、使用drop index

drop index 索引名 on 表名;

四、索引的设计原则

1、什么样的字段适合添加索引：

（1）、字段的数值有唯一性的限制
（2）、频繁作为where查询条件的字段
（3）、经常group by和order by的列
（4）、update、delete的where条件列
（5）、distinct字段需要创建索引
（6）、使用最频繁的字段放在联合索引的左侧
…

2、什么样的情况 不 适合添加索引：

（1）、在where、group by、order by中使用不到的字段
（2）、数据量小的字段最好不要使用索引
（3）、有大量重复数据的字段上不要建立索引
（4）、避免对经常更新的表建立过多的索引
…

五、索引失效

1、最佳左前缀法则：

select name from student where id = 1 and classx = '62班' and age = 5;
索引为
create index stu_id_sex_age on student(id,sex,age);（有效）
create index stu_sex_age on student(sex,age);（失效）  

2、计算、函数、类型转换导致失效：

计算：
select name from student where id+1 = 2;
create index stu_id on student(id);（失效） 
函数：
select id from student where left(name,3) = 'abc';
create index stu_name on student(name);（失效） 
类型转换：
select id from student where name = 123;//123自动转换为字符类型
create index stu_name on student(name);（失效）

3、范围条件右边的列索引失效

select * from student where id = 1 and age > 20 and name = '小王';
create index stu_id_age_name on student(id,age,name);（失效）
解决：
create index stu_id_name_age on student(id,name,age);（失效）
ps：如果不改成上行的索引，依旧失效。
select * from student where id = 1 and name = '小王' and age > 20;
//依旧失效，因为SQL优化器会自动给where重新排序

4、不等于（!=或<>）导致索引失效

select * from student where age <> 25;
create index stu_age on student(age);(失效)

同理：is null 索引有效，is not null 索引失效。

5、like以通配符%开头索引失效

select * from student where name = '%abc';
create index stu_name on student(name);(失效)

6、or前后存在非索引的列，索引失效

select * from student where name = '%abc' or age = 15;
create index stu_name on student(name);(失效)

7、数据库和表的字符集统一用utf8mb4
理解：相当于要使用字符集转换的函数，同2

六、innoDB中的索引

1、聚簇索引

1、什么是聚簇索引

以主键为索引的索引，没有主键会默认以字段唯一的作为主键，唯一索引没有系统会自动生成一个主键字段在内部作为主键索引，故innoDB中默认含有1个聚簇索引。

1、record_type：0代表普通记录，1代表目录记录，2代表最小记录，3代表最大记录。
2、同层节点间使用双向链表连接，节点内的数据使用单向链表连接。
3、叶子节点会存放数据库表中一行数据的所有数据。从上到下依次是record_type、col1(这里默认列一为主键列)、col2、col3…
4、非叶子节点从上到下依次存放record_type、存放主键所在列col1(这里默认列一为主键列)、对应的页表地址。

1、优点

2、缺点

3、限制

2、二级索引（辅助索引、非聚簇索引）

1、什么是二级索引

以非主键的字段作为索引的索引，因为没有存放完整数据，故需要进行回表操作。

1、record_type：0代表普通记录，1代表目录记录，2代表最小记录，3代表最大记录。
2、同层节点间使用双向链表连接，节点内的数据使用单向链表连接。
3、叶子节点会存放数据库表中主键字段和索引字段。从上到下依次是record_type、col2，col1(这里默认列一为主键列)
4、非叶子节点从上到下依次存放record_type、col2，col1(这里默认列一为主键列)、对应的页表地址。

3、聚簇索引与二级索引的区别

七、MyISAM中的索引

1、概述

MyISAM中无聚簇索引和二级索引之分，也可以说MyISAM中只有二级索引，但是与innoDB中的二级索引不同，MyISAM中的二级索引存放的不是数据，而是数据的地址。