文章目录
- 1、索引的概念
- 2、索引的分类
- 2.1普通索引
- 2.2唯一索引
- 2.3全文索引
- 2.4单列索引
- 2.5多列索引
- 2.6空间索引
- 3、索引的创建与删除
- 3.1在创建表的时候创建索引
- 3.2在已存在的表上创建索引
- 3.2.1创建普通索引
- 3.2.2创建唯一性索引
- 3.2.3创建全文索引
- 3.3用alter table语句创建索引
- 3.4删除索引语句
- 4、索引的设计原则——控制索引的数量
- 5、索引底层数据结构
- 5.1B树
- 5.2B+树
- 5.3在MYISAM引擎下—非聚集索引
- 5.3.1主键索引
- 5.4在InnoDB存储引擎下——聚集索引
- 5.4.1主键索引
- 5.4.2辅助索引
- 6、联合查询使用索引
- 6.1explain命令
- 6.2联合查询如何使用索引：
- 7、InnoDB和MyISAM的比较
1、索引的概念
索引是创建在表上的，是对数据库表中一列或者多列的值进行排序的一种结构
底层通过B+树实现
优点：可以提高检索数据的速度
缺点：创建和维护需要消耗一定的时间，耗时随数据的增加而增加，需要占用一定的物理空间，增加、删除和修改数据时，需要动态的维护索引
2、索引的分类
2.1普通索引
没有任何限制条件的索引，该索引可以在任何数据类型中创建。字段本身的约束条件可以判断其值是否为空或唯一。
2.2唯一索引
属性唯一，有唯一性约束或者主键约束
2.3全文索引
全文索引只能创建在CHAR、VARCHAR、TEXT类型的字段上。查询数据量较大的字符串类型字段时，使用全文索引可以提高查询速度。
2.4单列索引
对一个字段建立索引索引。可以包括上述的三种索引方式。应用该索引的条件只需要保证该索引值对应一个字段即可。
2.5多列索引
在表的多个字段上创建一个索引。该索引只想创建时对应的多个字段，可以通过这几个字段进行查询。
2.6空间索引
使用SPATIAL参数可以设置控件索引。控件索引只能建立在控件数据类型（LINESTRING、POINT、GEOMETRY等）上，这样可以提高系统获取控件数据的效率。MySQL中只有MyISAM存储引擎支持空间索引，且该字段不能为空值。
3、索引的创建与删除
3.1在创建表的时候创建索引
create table 表名(属性名 数据类型[完整性约束], 属性名 数据类型[完整性约束], …… [unique|fulltext|spatial] index 索引名(属性名1[长度] [asc|desc] ); 例如：在创建一个表名为test的表，的id字段建立名为index——id的唯一性索引，升序排列
create table test(id int unique, name varchar(20), unique index index_id(id asc) ); 3.2在已存在的表上创建索引
create [unique|fulltext|spatial] index 索引名
on 表名(属性名 [长度] [asc|desc]); 3.2.1创建普通索引
例如：在test表的id字段建立名为index_id的索引
create index index_id on test(id); 3.2.2创建唯一性索引
例如：在test表的id字段上建立名为index_id的唯一性索引
create unique index index_id on test(id); 3.2.3创建全文索引
fulltext用来设置全文索引，表的存储引擎必须是MyISAM类型，创建索引的字段必须是char、varchar、text等类型
例如：在test表的info字段上建立名为index_info的索引
create fulltext index index_info on test(info); 3.3用alter table语句创建索引
alter table 表名 add [unique|fulltext|spatial] index 索引名(属性名1[长度] [asc|desc]); 3.4删除索引语句
drop index 索引名 on 表名; 4、索引的设计原则——控制索引的数量
（1）数据量比较低的时候不需要设计索引
（2）选择唯一性索引。——属性是不重复的
distinct（属性）：属性不重复的数据
（3）为经常需要排序、分组和联合操作的字段建立索引
（4）为常作为查询条件的字段建立索引
（5）限制索引的数目
（6）尽量使用数据量少的索引
（7）尽量使用前缀来索引
（8）删除不在使用或者很少使用的索引
5、索引底层数据结构
5.1B树
特点：分支数M>2
每个节点最多存储M-1个数据
在非叶子和叶子都存储数据
5.2B+树
特点：只有叶子节点存储数据，非叶子节点存储关键字
关键字的作用：帮助我们查找要寻找的数据
为所有的叶子节点增加了一个链指针，连成一个链表
5.3在MYISAM引擎下—非聚集索引
索引还可以分为：
主键索引：（索引建立在主键上）MySQL会默认创建主键索引，所有表一定会 主键，如果不指定主键，MySQL会默认以行号为主键
辅助索引：（索引建立在非主键上）与主键索引形式相同，叶子节点存储建立索引的属性和数据的地址。
5.3.1主键索引
.myd文件：存储表的数据
.myi文件：存储表的索引
注意：要将B+树的结构加载到内存中才能使用
并不是将整个B+树的结构加载到内存，而是一个节点一个节点的进行加载 ，首先加载根节点，然后比较大小判断加载那个节点
叶子节点：存储的是主键（建立索引的属性）和数据的地址
对于多列索引比较原则：从左往右
5.4在InnoDB存储引擎下——聚集索引
MySQL会默认创建主键索引，所有表一定会有主键
5.4.1主键索引
叶子节点：存储主键和所有的数据，
原因：数据和索引在同一个文件下，数据和索引都存储在**.ibd文件**下
5.4.2辅助索引
建立在非主键上，
叶子节点存储，建立索引的属性和主键
所以先通过建立索引的属性通过B+树找到主键，然后在根据主键的值通过主键的B+树查找。
聚集和非聚集的区别：
叶子节点存储的是数据本身还是数据地址。
6、联合查询使用索引
6.1explain命令
id：选择标识符
select_type：表示查询的类型
table：输出结果集的表
partitions：匹配的分区
type：表示表的连接类型
possible_keys：表示查询时，可能使用的索引
key：表示实际使用的索引
key_len：索引字段的长度 字节数 和你建立索引的属性是有关的
ref：列与索引的比较
rows：扫描出的行数（估算的行数）
filtered：按表条件过滤的行百分比
Extra：执行情况的描述和说明
使用方法
explain select * from student where name = 'abc' and cid = 123\G mysql在执行SQL语句过程时对SQL语句进行优化，根据自己索引调整SQL语句的顺序，前提是不改变SQL语句本身的结果
6.2联合查询如何使用索引：
MySQL会首先判断a和b哪个表小，这里表小主要指的是行少，假设b表小，MySQL会对b表进行整表遍历，然后在a表上根据id字段进行查询,所以b表就是小表，无论如何都是要整表遍历的，是使用不到索引的，但是大表a表的id字段创建索引，就能使用到了! 所在在连接查询的时候，小表总是要整表搜索的，索引没有用，大表创建索引是能提高查询效率的，小表决定查询次数，大表决定查询时间能不能用到索引，用到是那张表的索引。
7、InnoDB和MyISAM的比较
两种引擎下的索引那个更好？？
只考虑查询效率：InnoDB引擎下的索引更好，数据和主键在同一个文件下
难以维护，数据和索引在同一文件下，那么数据的修改必然引起索引的修改
MYISAM： 数据和索引分开存储，更好维护，查询效率低
如何选择引擎？？
要求更高的查询效率并且数据的修改不频繁。——>InnoDB
数据修改频繁——>MYIASM
如果需要的功能更多（事务、外键）这时只能使用InnoDB，外键和事务的维护也需要耗费时间的
本文地址：https://blog.csdn.net/qq_43833476/article/details/107912189