数据库-索引详解

数据库为什么引入索引

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

索引分类

主键索引(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 【表名】；