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('法外狂徒');