当前位置:   article > 正文

MySQL索引_mysql全文索引

mysql全文索引

认识索引

索引的作用:
提高数据库的性能,加快查询速度。但是查询速度的提高是以插入、更新、删除的速度为代价的,这些写操作,增加了大量的IO。所以它的价值在于提高海量数据的检索速度。

索引的分类:
主键索引(primary key)
唯一索引(unique)
普通索引(index)
全文索引(fulltext)

索引创建原则:
比较频繁作为查询条件的字段应该创建索引
唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件
更新非常频繁的字段不适合作创建索引
不出现在where子句中的字段没必要创建索引

mysql和磁盘的关系

MySQL 给用户提供存储和管理数据的服务,数据存储在磁盘这个外设当中。而管理数据就会涉及到数据的读写,磁盘IO的效率比较低,所以MySQL如何提交效率是很重要的。

磁盘一个扇区是512字节,单次磁盘IO的数据量如果太小就需要多次磁盘IO,效率低。
操作系统读取磁盘是以块为单位的,单位是4KB 。
MySQL为了提高效率,磁盘IO的基本单位是16KB,在MySQL中叫做page(不是系统的page)。
在这里插入图片描述
16KB=16*1024B=16384B

系统和MySQL和磁盘交互都遵循局部性原理,减少了IO

局部性原理:软件设计要尽量遵循“数据读取集中”与“使用到一个数据,大概率会使用其附近的数据”,这样磁盘预读能充分提高磁盘IO。

磁盘预读:磁盘读写并不是按需读取,而是按页预读,一次会读一页的数据,每次加载更多的数据,如果未来要读取的数据就在这一页中,可以避免未来的磁盘IO,提高效率;通常,一页数据是4K。

在这里插入图片描述

MySQL 的增删查改操作,都需要CPU的计算功能,所以需要把磁盘数据读取到内存当中。
也就是特定时间内,MySQL的数据磁盘中有,内存中也有。
对内存中的数据进行操作之后,再以特定的策略刷新到磁盘,此时IO的基本单位就是page。
为了减少磁盘IO,提高效率, MySQL 服务器运行时,申请了名为 Buffer Pool 的大块内存空间来和磁盘数据进行IO交互。

MySQL管理的数据可能十分庞大,这意味着MySQL中存在大量page,MySQL需要高效地管理这些page。

MySQL如何管理page

存储数据记录的page:

单个page里,数据是链表结构,但为了查询效率,数据按主键有序,就像书本的页数是有序的,才方便我们查找某一页。单个page中存在目录,可以加快查询效率,也和书本的目录很像。

一个page并不能存下数据的时候,会存在多个page,多个page构成双向链表结构。

目录page:
存放数据记录的多个page的双向链表也是通过目录管理的,目录存放在其他page当中,而且这些目录page只存目录不存数据,目录page也通过指针连接构成双向链表,目录page的链表也通过目录管理,形成了多级目录,这个结构其实就是B+树

有了这颗B+树,进行查找的时候自顶向下,按需加载page到内存,大大减少了磁盘IO

为什么选择B+树

  1. 如果选择普通的链表,查找是线性遍历,磁盘IO很频繁,效率低。
  2. 二叉搜索树有可能退化成线性结构,也不合适。
  3. AVL树或者红黑树虽然平衡性好,但是是二叉结构,和B+树相比,树的高度高,也就意味着自顶向下查找时候需要加载的page更多。
  4. MySQL有的存储引擎也支持哈希结构,哈希的查找效率很高,但是进行范围查找就比较困难。
  5. 如果选择B树,B树的节点既存数据,又存其他page的目录,意味着存储目录的page比B+树多,树高也就更高,效率不如B+树,而且B树的叶子节点没有相连,不利于范围查找。

聚簇索引和非聚簇索引

上文的B+树结构以聚簇索引为例, InnoDB就是采用聚簇索引的存储引擎。
非聚簇索引的B+树的叶子节点不直接存放数据记录,而是存放数据记录的指针,也就是非聚簇索引的索引page和数据page分离,MyISAM采用非聚簇索引。

创建一张表,使用MyISAM:
在这里插入图片描述
查看生成的文件
在这里插入图片描述
再创建一张表,使用InnoDB:
在这里插入图片描述

在这里插入图片描述
除了主键索引,MySQL用户可以建立其他索引,叫做辅助索引(普通索引)

InnoDB建立辅助索引后,叶子节点不是存放整个条数据记录(节省空间),而是存放辅助索引对应的主键, 查找时通过找到的主键到主键索引中查找,这个过程叫做回表,如果就是要找到主键就不用回表

MyISAM的辅助索引和主键索引结构一样,叶子节点存的都是数据记录的地址。

索引相关SQL操作

创建主键索引:

一个表中,最多有一个主键索引,当然可以使复合主键
主键索引的效率高(主键不重复)
创建主键索引的列,它的值不能为null
主键索引的列基本上是int

创建主键索引的操作就是设置主键,三种方式:

create table user1(id int primary key, name varchar(30));
  • 1
create table user2(id int, name varchar(30), primary key(id));
  • 1
create table user3(id int, name varchar(30));
-- 创建表以后再添加主键
alter table user3 add primary key(id);
  • 1
  • 2
  • 3

创建唯一键:

一个表中,可以有多个唯一索引
如果在某一列建立唯一索引,必须保证这列不能有重复数据,查询效率高
如果一个唯一索引上指定not null,等价于主键索引

创建唯一键索引就是设置唯一键,三种方式:

create table user4(id int primary key, name varchar(30) unique);
  • 1
create table user5(id int primary key, name varchar(30), unique(name));
  • 1
create table user6(id int primary key, name varchar(30));
alter table user6 add unique(name);
  • 1
  • 2

创建普通索引:
一个表中可以有多个普通索引,普通索引在实际开发中用的比较多
如果某列需要创建索引,但是该列有重复的值,那么我们就应该使用普通索引

三种创建方式:

create table user8(id int primary key,
  name varchar(20),
  email varchar(30),
  index(name) --在表的定义最后,指定某列为索引
);
  • 1
  • 2
  • 3
  • 4
  • 5
create table user9(id int primary key, name varchar(20), email varchar(30));
alter table user9 add index(name); --创建完表以后指定某列为普通索引
  • 1
  • 2
create table user10(id int primary key, name varchar(20), email varchar(30));
-- 创建一个索引名为 idx_name 的索引
create index idx_name on user10(name);
  • 1
  • 2
  • 3

创建全文索引:
对大量文字的字段进行检索时,会使用到全文索引。MySQL支持全文索引的存储引擎是MyISAM,默认的全文索引支持英文,不支持中文。

创建全文索引:

CREATE TABLE articles (
  id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  title VARCHAR(200),
  body TEXT,
  FULLTEXT (title,body)
)engine=MyISAM;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

使用全文索引:

SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('某些关键词');
  • 1

查询索引:

 show keys from 表名;
  • 1
show index from 表名;
  • 1
 desc 表名; --信息简略
  • 1

删除索引:

 alter table 表名 drop primary key;--删除主键索引
  • 1
 alter table 表名 drop index 索引名; 
  • 1
 drop index 索引名 on 表名;
  • 1
声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/小蓝xlanll/article/detail/168423?site
推荐阅读
相关标签
  

闽ICP备14008679号