【MySQL】索引(下)

文章目录

1. 普通索引

MySQL除了会默认建立主键索引，也可能会按照其他列信息建立的索引，一般这种索引称为 普通索引
对于 储存引擎 myisam，普通索引和主键索引是没有区别的

以第二列作为键值，构建B+树，可以发现 与主键构建的B+树基本没有区别
因为两者叶子节点都是存储 对应数据的地址

使用储存引擎以 innodb， 建立普通索引

依旧按照指定列作为键值，构建B+树
但是 由于 储存引擎 为 innodb ， 所以 叶子节点存储 数据
此时叶子节点 存储的数据为 构建索引的键值 对应的主键值

(如果此时按照第三列构建索引，此时叶子节点 就会存储 对应第三列的主键值 )

2. 索引操作

创建主键索引

第一种方式
在创建表的时候，直接在字段名后 指定 primary key
create table user1(id int primary key );

第二种方式
在创建表的最后，指定某列或者某几列为主键索引
create table user2( id int, name varchar(20),primary key(id) );

第三种方式
创建表后再添加主键
create table use3(id int, name varchar(20));
alter table user3 add primary key(id);

查询索引

查询test1表，发现id是具有主键约束的

输入 show index from 表名; 查询索引
key_name表示索引名称为primary ，column_name 表示打在 id这一列上
index_type 表示 索引类型为 B+树

删除索引

输入 alter table 表名 drop pimay key; 删除对应的主键索引
此时发现test1表中的id 没有主键约束了

唯一索引的创建

第一种方式
在表定义时，在某列后直接指定unique唯一属性
create table user4( id int primary key, name varchar(20) unique);

第二种方式
创建表时，在表后面指定某列或某几列为 unique
create table user5(id int primary key, name varchar(20),unique(name));

第三种方式
在表创建好后，添加 某列为unique
create table user6(id int primary key ,name varchar(20));
zalter table user6 add unique(name);

以第三种方式为例，刚开始test1表中的name只是不为空，再对name添加唯一索引后
test1表 的 name 就有 唯一键 约束了

再次输入 show index from test1 \G 查看test1表的索引

key_name 表示 索引的名称
column_name 表示 索引以name为主
index_type 表示 索引的类型 为B+树

删除唯一键索引

alter table 表名 drop index 索引名;
索引名 是 show keys feom 表名中的 key_name 字段

输入 alter table test1 drop index name; 即可删除test1表中name 中的唯一键约束

普通索引的创建

第一种方式
create table user8(id int primary key,
name varchar(20),
email varchar(30),
index(name) –在表的定义最后，指定某列为索引
);

第二种方式
create table user9(id int primary key, name varchar(20), email
varchar(30));
alter table user9 add index(name); –创建完表以后指定某列为普通索引

输入 alter table test1 add index(name); 即将name变为普通索引

第三种方式
create table user10(id int primary key, name varchar(20), email
varchar(30));
create index idx_name on user10(name)– 创建一个索引名为 idx_name 的索引

输入 create table index myindex on test1(name);
在test1表中name列 创建一个索引名为 myindex 的索引

输入 show index from test1 \G 发现 key_name(索引的名称) 为 myindex

输入 alter table test1 drop index myindex; 就可以把myindex对应的索引名删除掉

全文索引

当对文章字段 或有大量文字的字段进行检索时，会使用到全文索引
若要使用全文索引，则要求表的储存引擎 为 MyISAM
并且默认的全文索引支持 英文，不支持中文

在表中添加 FULLTEXT ，让对应的列具有全文索引

创建一张表 articles ，内部包含 约束为 主键 并且 是自增长 的id 、200个字符 的title 、大文本的body
并将 title 和body 列 进行 全文索引

由于表中的id 是自增长的，所以只需插入 title 和 body 数据即可

输入 select * from articles; 查看当前表信息

想要在body列内容做搜索
如：查询articles表中 是否有 database 存在

输入 select * from articles where bidy like ‘database%’; 使用 like 进行模糊匹配 查询包含 database 及 带有后面的内容

使用 关键字 explain ，可以查看 是否使用索引

id 作为 这次请求的 id值，序号值 一般越大，越先执行
select_type 表示 查询类型 SIMPLE 表示 简单查询
table 表示 表名
type 表示 查询 方式 all 表示 全部遍历
key 表示 索引 NULL表示 没有使用索引查找

使用全文索引

输入 select * from articles where match(title,body) against(‘database’);
match 表示 设置条件
against 表示 匹配

再次使用 explain 查看是否使用索引

(type)遍历方式 为 使用全文索引(fulltext)
(key)索引 为 title ，表示使用 title 这个全文索引 进行查找