数据库为什么引入索引
在数据库操作中我们最常用的就是查询,因为数据量庞大,为了提高查询速度,提高数据库性能。引入索引。 但是查询速度的提高的代价-空间换时间-就是 插入,更新,删除速度的降低。
索引分类
主键索引(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 【表名】;