数据库-索引详解

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/weixin_40921797/article/details/85276258

数据库为什么引入索引

在数据库操作中我们最常用的就是查询,因为数据量庞大,为了提高查询速度,提高数据库性能。引入索引。 但是查询速度的提高的代价-空间换时间-就是 插入,更新,删除速度的降低。

索引分类

主键索引(primary key) 主键约束就是通过主键索引实现的
1.主键索引尽量为int,效率高
2.一个表中只能创建一个主键索引
3.创建主键索引的属性列 不但不能重复而且不能为空
innodb存储引擎的主键索引是一种聚簇索引(clustered index),就是索引和值放在一起

除了主键索引的其他索引叫做二级索引多查询一次:
查找都是先找对应规定的索引然后再检索主键索引才找到数据。
唯一索引(unique) 唯一约束同理也是通过唯一索引实现的
1.唯一索引一张表可以有多个。要求不能有重复。

普通索引(index)
1.用的最多。
2.允许重复。当创建外键就是创建的普通索引
全文索引(fulltext) 长文索引,查词 全文索引比较慢,效率比较差
1.默认支持英文,不支持中文。因为字符集的原因。如果需要增加一个转换插件parser plugin即可

索引的基本原理

普通的数据查询就是从头线性遍历所以效率很差。
引入索引,存在于磁盘中,就是空间换时间,在结构上索引类似于一个二叉树的变种B-tree(B树)。
数据库innodb采用的就是B+tree的形式,而且数据和索引是存放在一起的所以查询速度更快。
当我们查询的时候根据索引,形成的一个有序的表,进行二分查找。就实现快速查找。如果我们通过一个索引查询不能够解决所有查询问题的时候,还可以分别给字段建立索引。
1.索引 的创建过程 就是建表过程,就是把信息一条条拿出来生成树调整的过程时间复杂度O(n*lg(n))。
2.当我们插入新的数据到数据库的时候,还需要维护这个树,把索引插入树中并调整位置。
3.当我们删除一个结点数据的时候,通用需要维护这个数,删除树中结点,并调整树的结构。

索引的创建

索引一般作用在查询比较频繁,更新不频繁的字段。唯一性太差及重复太高的字段不适合创建索引。
创建主键索引

//在创建表的时候完成主键索引的创建
create table  user(
	id int primary key, 
	name varchar(20)
	);
desc  user;则可以查看key一栏出现pri 即主键索引的标志。
通过show create table user;可以查看创建的索引的名字。
//在创建表的最后 添加主键索引
create table user(
	id int,
	name varchar(20)
	primary key (id)
	);
//在表创建之后,更新添加索引
alter table user add primary key(id);

创建唯一索引

//创建时属性后追加
create table user(
	id int primary key, 
	name varchar(30) unique
	);
//创建后单独写出
create table user(
	id int primary key, 
	name varchar(30), 
	unique(name)
	);
//表创建完成后单独alter 添加索引约束
create table user(id int primary key, name varchar(30)); 
alter table user add unique(name);

普通的索引的创建

//创建的时候底部单独列出
create table user(
	id int primary key,     
	name varchar(20),     
	email varchar(30),     
	index(name) 
	);
//表创建完成后alter添加索引
create table user(id int primary key,  name varchar(20),  email varchar(30)); 
alter table user add index(name);
// 注意:这个最重要。这种在 表创建成功后追加索引,并且这种写法可以自己设定索引名称,注意名称唯一不可重复(全库不可重复)
create table user(id int primary key,  name varchar(20),  email varchar(30));    
create index idx_name on user(name);

全文索引的创建

//在表创建时最后追加。这里的例子是一个复合索引。
CREATE TABLE articles (    
	id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,    
	title VARCHAR(200),    
	body TEXT,    
	FULLTEXT (title,body) 
	);

查看是否在查询的时候使用了索引

我们创建索引肯定是为了在查询到时候提高效率。那我们如何此次查询是否使用了索引呢?在select前加explain
举个例子:查询哪些text句子中使用了关键字mysql过程是否使用了索引。
explain select * from emp \G
在这里插入图片描述

如何查询我们定义的索引及其相关信息

show keys from 【表名】;//详细展开表相关索引信息
show index from 【表名】;//和上等价
desc 【表名】;//简单查看索引

如何使用索引进行查询

SELECT 【属性列】 FROM 【表名】WHERE MATCH 【定义的全文索引的格式 eg:(title,body)】 AGAINST (‘【所全文匹配的文本】’);
举个例子:

 SELECT * 
 FROM articles    
 WHERE MATCH (title,body) AGAINST ('mysql');

删除索引

删除速度远小于创建索引速度

//删除主键索引
alter table 【表名】 drop primary key;
 //删除其他索引 
alter table 【表名】 drop index 【索引名】;
//或者 
drop index 【索引名】 on 【表名】;
展开阅读全文

没有更多推荐了,返回首页