MySQL数据库索引
目录
0.知识回顾
1.数据库约束
一.索引
1.什么是索引
2.为什么要使用索引(作用)
3.索引的使用场景
4.如何使用索引
1.查看索引
2.创建索引
3.修改索引
4.删除索引
5.索引的分类
1.使用场景不同
2.按列区分
3.按数据组织方式
二.索引的数据结构
1.HASH
2.二叉搜索树和红黑树
3.N叉搜索树---B 树
4.B+树
三.explain的使用
1.explain的使用
2.explain查询的属性含义
四.索引覆盖
1.索引覆盖
2.回表查询
五.索引失效
1.什么是索引失效
0.知识回顾
1.数据库约束
在认识索引之前,我们有必要回顾一下数据库约束的知识,这对我们之后学习索引有一定的帮助
|
约束类型
|
说明
|
示例 |
|
NULL 约束
|
使用 NOT NULL 指定列不为
空
|
name varchar(20) not null, |
|
UNIQUE唯一约束
|
指定列为唯一的、不重复的 | name varchar(20) unique, |
|
DEFAULT 默认值约
束
|
指定列为空时的默认值 | age int default 20, |
|
主键约束
|
NOT NULL 和 UNIQUE 的
结合
|
id int primary key |
|
外键约束
|
关联其他表的 主键 或 唯一键
|
表 ( 列 )
|
|
CHECK 约束（了
解）
|
保证列中的值符合指定的条件
|
check (sex =' 男 ' or sex=' 女 ')
|
一.索引
1.什么是索引
2.为什么要使用索引(作用)
在前边我们介绍过了使用索引的主要作用就是提高查询效率
我们也提及了保存索引是需要空间的,所以这是一个典型的以空间换时间的操作(类似于数据结构里面的哈希表,但是索引的实现可不是哈希实现的,具体见下文),索引的主要目的就是提高查找的效率(select),但是对于更新(update),删除(delete),插入(insert)的时间开销还是相对较大的,因为不仅要更新表中的数据信息,相应的索引也要进行更新操作.
比如字典中我们要新增和删除一些字的时候,我们在原来的基础上新增了数据,后面内容对应的页码也会发生改变,所以我们对索引也要进行对应的更新
3.索引的使用场景
在上一条中我们分析了索引使用的好处,以及索引使用的一些弊端,这样我们可以来总结一下索引使用的场景.
- 数据量庞大,且经常对这些数据进行查询操作
- 对这些数据很少使用插入,删除和修改操作
- 索引会占用额外的内存空间,内存条件允许
知道这些条件之后,我们在开发中会更好的使用索引进行相应的操作.
4.如何使用索引
在讲述这个之前我们先来创建两张表classes和student
创建classes表
DROP TABLE IF EXISTS classes;
CREATE TABLE classes (id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20),`desc` VARCHAR(100)
);创建student表
DROP TABLE IF EXISTS student;
CREATE TABLE student (id INT PRIMARY KEY AUTO_INCREMENT,sn INT UNIQUE,NAME VARCHAR(20) DEFAULT 'unknown',qq_mail VARCHAR(20),classes_id INT,FOREIGN KEY (classes_id) REFERENCES classes(id)
);
1.查看索引
show index from 表名 ;
案例:查看student表的索引
show index from student;
之前我们说过了创建的主键约束,唯一约束,外键约束会自动创建对应的索引,这个就可以很好的展现出来
1.Table
表的名称。
2.Non_unique
如果索引不能包括重复词，则为0。如果可以，则为1。
3.Key_name
索引的名称。
4.Seq_in_index
索引中的列序列号，从1开始。
5.Column_name
列名称。
6.Collation
列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）'D'(降序)或NULL（无分类）。
7.Cardinality
索引中唯一值的数目的估计值。通过运行analyze table或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。
8.Sub_part
如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则NULL。
9.Packed
指示关键字如何被压缩。如果没有被压缩，则为NULL。
10.Null
如果列含有NULL，则含有YES。如果没有，则该列含有NO。即是否为not null
11.Index_type
使用的索引方法（BTREE(B树), FULLTEXT(全文索引), HASH(哈希), RTREE(R树)）。
12.Comment
多种评注。
2.创建索引
create index 索引名 on 表名 ( 字段名 ASC,字段名 DESC......);
可以同时创建多个字段的索引.可以指定是升序还是降序进行创建索引
3.修改索引
alter index 旧索引名 rename to 新索引名
4.删除索引
drop index 索引名 on 表名 ;
5.索引的分类
1.使用场景不同
1.普通索引
普通索引是最基本的索引类型,对数据类型没有限制,主要的作用就是增加访问速度
create index 索引名 on 表名(字段名);
例如给sn字段创建索引名为index_id的索引
create index index_sn on student(sn);
2.唯一索引
唯一索引与普通索引类似，不同的是创建唯一性索引的目的不是为了提高访问速度，而是为了避免数据出现重复。
create unique index index_id on student(id);
3.主键索引
主键索引是一种特殊的唯一索引，不允许值重复或者值为空。
创建主键索引通常使用 primary key 关键字。不能使用 create index 语句创建主键索引。
4.空间索引
空间索引主要用于地理空间数据类型 GEOMETRY,并且要求声明的数据类型是NOT NULL,空间索引只能在存储引擎为 MyISAM 的表中创建。初学者很少使用
CREATE SPATIAL INDEX index_line ON tb_student(line);
5. 全文索引
全文索引主要用来查找文本中的关键字，只能在 char、varchar或 text类型的列上创建。在 MySQL 中只有 MyISAM 存储引擎支持全文索引。全文索引允许为null和重复值.
create fulltext index index_name on student(name);
2.按列区分
1.单列索引
单列索引:创建的索引只包含一个原表中的字段,在表中的一个字段建立索引.
单列索引可以使普通索引,唯一索引和主键索引
例如
create (unique) index index_id on student(id (ASC or DESC));
2.组合索引
组合索引也叫做多列索引和复合索引.多列索引在一个表的多个字段建立一个索引.该索引指向表中的多个字段,可以通过多个字段组合进行查询,但是,只有当查询条件中包含了这些字段的第一个字段的时候,组合索引才会生效.
例如,对student表中的name和qq_mail字段建立组合索引
create index index_name_qqmail on student(name,qq_mail);
该索引创建好了以后，查询条件中必须有 name 字段才能使用索引。 比如我们查询name='张三'的人的信息,这个时候组合索引会生效,但是我们仅仅查询age=18的人,这个组合索引便不会生效
3.按数据组织方式
1.聚簇索引
2.非聚簇索引
二.索引的数据结构
对于数据库来说,索引使用什么数据结构可以做到时间复杂度最低呢?
1.HASH
索引主要针对的就是查找操作,在数据结构中我们学习过哈希这种数据结构,查找的时间复杂度O(1),这看起来十分符合索引的目的(也就是查找),但是仔细思考就可以发现,哈希主要针对的是查找某一条的数据,而数据库查找中,我们需要查找的是符合条件的一组数据(范围查找),比如我们查找student表中id在10到20之间的学生信息,这个时候hash明显是不支持的
2.二叉搜索树和红黑树
那么二叉搜树是否可以作为索引的数据结构呢?仔细想想是不可以的,平均时间复杂度O(logn),但是最差的时间复杂度为O(n)(插入有序的一组元素),和全表搜索一样了.
这时候我们是否可以考虑平衡二叉搜索树或者红黑树呢?这种数据结构可以很好的解决二叉搜索树的缺点同时利用其优点.
其实也是不合适的.为什么呢? 因为当数据量很大的时候,树的高度会很高,增大了磁盘的IO次数
我们先这样理解:树的高度决定了磁盘的IO次数.每向孩子结点访问一级,就会发生磁盘IO,而在一个系统中,对性能影响最大的就是磁盘IO,所以我们需要一种能控制树的高度的树结构
3.N叉搜索树---B 树
B树是一颗多叉搜索树,因此可以很大程度上减少树的高度.我们可以自主的规定一个结点保存多少个值,当结点大于这个值的时候才可能去增加树的高度
但是数据库默认使用的不是B树这种数据结构来实现索引,使用的却是B+树,B+树其实是在B树的基础上进行了优化
4.B+树
观察B+树我们可以发现
三.explain的使用
1.explain的使用
我们可以使用explain来查看SQL语句的执行
explain select * from student;
2.explain查询的属性含义
以下全部详细解析explain各个属性含义：
id: 查询的序列号
select_type: 查询的类型，主要是区别普通查询和联合查询、子查询之类的复杂查询
SIMPLE：查询中不包含子查询或者UNION- 查询中若包含任何复杂的子部分，最外层查询则被标记为：
PRIMARY - 在
SELECT或WHERE列表中包含了子查询，该子查询被标记为：SUBQUERY
table: 输出的行所引用的表
type: 访问类型
从左至右，性能由差到好
- ALL: 扫描全表
- index: 扫描全部索引树
- range: 扫描部分索引，索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于between、<、>等的查询
- ref: 使用非唯一索引或非唯一索引前缀进行的查找
（eq_ref和const的区别：） - eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
- const, system: 单表中最多有一个匹配行，查询起来非常迅速，例如根据主键或唯一索引查询。system是const类型的特例，当查询的表只有一行的情况下， 使用system。
- NULL: 不用访问表或者索引，直接就能得到结果，如
select 1 from test where 1
possible_keys: 表示查询时可能使用的索引。如果是空的，没有相关的索引。这时要提高性能，可通过检验WHERE子句，看是否引用某些字段，或者检查字段不是适合索引
key: 显示MySQL实际决定使用的索引。如果没有索引被选择，是NULL
key_len: 使用到索引字段的长度
注：key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。
ref: 显示哪个字段或常数与key一起被使用
rows: 这个数表示mysql要遍历多少数据才能找到，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数，在innodb上可能是不准确的
Extra: 执行情况的说明和描述。包含不适合在其他列中显示但十分重要的额外信息。
- Using index：表示使用索引，如果只有 Using index，说明他没有查询到数据表，只用索引表就完成了这个查询，这个叫覆盖索引。
- Using where：表示条件查询，如果不读取表的所有数据，或不是仅仅通过索引就可以获取所有需要的数据，则会出现 Using where。
内容来源:explain属性
四.索引覆盖
1.索引覆盖
索引覆盖是指在查询过程中，所需的所有数据都可以从索引中获取，而不必读取数据表的实际数据页。因为索引只包含表中的部分列数据，而且索引存储在内存或磁盘中，所以在使用索引进行查询时可以大大降低磁盘I/O操作的次数，提高查询效率。同时，由于不必读取数据表的实际数据页，索引覆盖可以减少查询所需的内存空间，降低内存压力。通常情况下，如果一个查询只需要从索引中获取所需的所有列数据，那么就可以称之为索引覆盖查询。
create index index_name_qqmail student(name,qq_mail)
当我们执行以下查询的时候,我们就进行了索引覆盖
select name,qq_mail from student where name='张三'
因为我们之前创建了name和qq_mail的索引,所以我们在进行索引查找找到了name='张三'的数据之后,我们根据索引存储的name和qq_mail信息,便可以直接把查询到的信息输出,没有必要再去主表进行查询操作了
2.回表查询
回表查询和上边的操作有一些区别,当我们在索引查询到指定字段时候,但是不能完全满足查询条件(比如查询id,sn,name,qq_mail,classes_id,而索引只包含name和qq_mail的信息),这个时候我们使用到id到主键索引中查询到完整的信息(主键索引包含当前数据行中所有列的值)
比如创建了组合索引name和qq_mail,这个时候我们执行下面的select语句
select * from student where name='张三'
这个时候name和qq_mail的组合索引中并没有*的全部信息,所以我们查询到符合条件的name和qq_mail的id,然后到根据id到主键索引中寻找到完整的信息
五.索引失效
1.什么是索引失效
索引失效指的是索引在某些查询条件下无法使用，导致查询效率降低或者查询无法执行的情况。索引失效可能出现的原因包括但不限于：
比如我们常见了一个组合索引(name,qq_mail),而我们查询的时候只是用了qq_mail进行筛选,这个时候索引就失效了
explain select * from student where name='张三'
explain select * from student where qq_mail='张三'
2. 判断不等：每个都要判断