mysql索引

一、索引的概念
1.1 什么是索引
索引是一个排序的列表,在列表当中存储索引的值以及索引值对应数据所在的物理行。

索引值和数据是一个映射关系。

1.2 索引的作用
使用索引之后,不需要扫描全表来定位数据。加快数据库查询速度。

索引可以是表中的一列,也可以是多个列。

设置了索引之后,数据库可以利用索引快速定位,能够大大提高查询速度。这也是创建索引的主要原因。
尤其是在表的数据很大,以及涉及多个表查询时,索引可以大大地提高查询速度。
建立索引不仅能提高查询速度,在恢复数据库的数据时,也能提高性能。
可以加快表与表之间连接查询的速度。
索引的副作用

创建的索引也需要占用额外的磁盘空间。INNODB存储引擎的表数据文件和索引文件在一块,相对占的空间小一点。
更新一个包含索引的表,比没有索引的表需要花费更多的时间。表要更新,索引也要更新,所以速度要慢一些。
1.3 创建索引的原则
表的主键和外键必须有索引。主键是唯一的,外键是关联主表的,查询时可以快速定位。
一般的,一张表有超过300行的数据,应该要创建索引。
经常与其他表进行连接的表,在连接字段上应该创建索引。
更新太频繁的字段不适合创建索引。
经常作为where语句条件列的字段,应该建立索引
经常使用group by 和 order by的字段上应该建立索引。
选择一个性能高的字段作为索引(重复量少)
索引应该建立在小字段上(字段长度较短)
1.4 索引的类型
b-tree 索引 :(b-树索引)
绝大部分的数据都是使用b-tree索引。b-tree索引是一个典型的数据结构,其包含的组件主要有以下几个:

叶子节点:包含的条目直接指向表里的数据行。叶子节点之间彼此相连,一个叶子节点有一个指向下一个叶子节点的指针。

分支节点:包含的条目指向索引里其他的分支节点或者叶子节点。

根节点:一个 b-tree索引只有一个根节点,实际上就是位于树的最顶端的分支节点。

基于这种树形数据结构,表中的每一行都会在索引上有一个对应值。因此,在表中进行数据查询时,可以根据索引值一步一步定位到数据所在的行。

b-tree索引可以进行全键值、键值范围和键值前缀查询,也可以对查询结果进行 ORDER BY 排序。但 b-tree索引必须遵循左边前缀原则,要考虑以下几点约束:

查询必须从索引的最左边的列开始。
查询不能跳过某一索引列,必须按照从左到右的顺序进行匹配。
存储引擎不能使用索引中范围条件右边的列。
hash索引 :哈希索引
根据索引对应的hash值的方法获取表的记录行,速度较慢。

二、创建索引(b-tree)
2.1 创建索引的语句
方法一:建表的时候创建索引index

create table member (id int(10),
name varchar(10),
cardid int(18),
phone int(11),
address varchar(50),
remark text,
index name_index (name)
);

解释:

remark text :text也是数据类型的一种,和char,varchar性质相同,都是字符串,text不需要长度的参数,可以作为大文本的列,可以存储65535个字符。
index name_index (name) :创建索引的格式
index :创建索引
name_index :索引的名称,定义是一般列名在前,index在后,下划线连接
(name) :创建索引的列
方法二:向表中添加索引

alter table member add index id_index(id);

explain :查询当前语句使用索引的情况

explain select * from member where id = 1;
#查询到的结果可以看到索引为 id_index
show index from member;
#查看member表里的索引

这两种方法添加的都是普通索引,没有额外选项。

2.2 删除索引
删除索引删除的是索引对应的Key_name

可以用drop和alter删除:

drop index phone_index on member;
alter table member drop index cardid_index;

2.3 主键索引/唯一键索引
主键(PRIMARY KEY)和唯一键(UNIQUE KEY)不需要额外声明,创建时系统自动添加索引,并自动为索引命名。

2.4 唯一索引
唯一索引 :unique index ,不允许索引的值重复。

可以用key的方式创建,也可以用索引的方式创建

create unique index cardid_index on member(cardid);
alter table member add unique index phone_index(phone);

2.5 组合索引(联合索引)
一次性给多个列创建索引,形成一个组合

alter table member
add constraint uc_name_cardid_phone unique(name,cardid,phone);

使用联合索引,需要同时查询联合索引所在的所有列。

constraint 约束名 unique (列名) :这样的形式定义了一个唯一性的联合约束。这样多列的唯一性约束要求不能所有被约束的列的内容完全一致,但是可以部分一致。比如三列联合约束,仅有两列的内容一致,则不会报错;如果存在这三列数据全都一致的行,则会报错。

2.6 全文索引
适用于模糊查询,检索大文本使用的。

create fulltext index remark_index on member (remark);

查看索引会发现,索引类型不是b-tree,是fulltext,对全文索引查询的方式有一些区别

select * from member where match(remark) against('vip');

MATCH(remark): MATCH() 是 MySQL 中用来执行全文搜索的函数。在这里,它指定了要搜索的列是 remark 列。
AGAINST(‘vip’): AGAINST() 是 MATCH() 函数的一部分,用于指定要搜索的文本或表达式。在这里,它表示搜索包含单词 ‘vip’ 的记录。
注意:MATCH...AGAINST的方式,against后匹配内容可以使用布尔运算符(+ - *)。 + :包含;- :不包含;* :模糊匹配," "用于精准匹配。

2.7 简单实例
商场做一个会员卡系统,包含以下字段:

会员编号 int 5

会员姓名 varchar 10

会员的身份证号 int 11

会员的地址 varchar 50

会员的入会时间 timestamp 当前时间

会员的备注信息 text

创建时设计好该表的索引:主键 ID

姓名创建 :普通索引

身份证 :唯一索引

会员信息 :模糊查询,全文索引

create table vip (
	id int(5) primary key,
    vipname varchar(10),
    idcard int(11),
    address varchar(50),
    intime timestamp not null,
    remark text,
    index vipname_index (vipname),
    unique index idcard_index (idcard),
    fulltext index remark_index (remark)
);

insert into vip values(1,'张三',1234567890,'南京',now(),'法外狂徒');
show index from vip;
select * from vip where match(remark) AGAINST('法外狂徒');
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值