MySQL支持全文索引和搜索功能。在MySQL中可以在CHAR、VARCHAR或TEXT列使用FULLTETXT来创建全文索引。
FULLTEXT索引主要用MATCH()...AGAINST语法来实现搜索:
MySQL的全文搜索存在以下局限:
通常来说MySQL自带的全文搜索使用起来局限性比较大,性能和功能都不太成熟,主要适用于小项目,大项目还是建议使用elasticsearch来做全文搜索。
ElasticSearch是一个分布式的开源搜索和分析引擎,适用于所有类型的数据,包括文本、数字、地理空间、结构化和非结构化数据,以下简称ES。
Elasticsearch 在 Apache Lucene 的基础上开发而成,Elasticsearch 以其简单的 REST 风格 API、分布式特性、速度和可扩展性而闻名,是 Elastic Stack 的核心组件。Elastic Stack 是适用于数据采集、充实、存储、分析和可视化的一组开源工具。
Elasticsearch 的实现原理主要分为以下几个步骤,首先用户将数据提交到Elasticsearch 数据中心,再通过分词控制器去将对应的数据分词,将其权重和分词结果一并存入数据,当用户搜索数据时候,再根据权重将结果排名,打分,再将返回结果呈现给用户。
由于ES是基于RESTfull Web接口的,因此我们直接按照惯例传递JSON参数调用接口即可实现增删改查,并且不需要我们做额外的管理 *** 作就可以直接索引文档,ES已经内置了所有的缺省 *** 作,可以自动帮我们定义类型。
再次执行PUT,会对库中已有的id为1的数据进行覆盖,每修改一次_version字段的版本号就会加1。
默认搜索会返回前10个结果:
返回的几个关键词:
查询字符串搜索,可以像传递URL参数一样传递查询语句。
精确查询:
全文搜索:
以上两种方法都需要考虑数据更改后如何与ES进行同步。
MySQL 前缀索引能有效减小索引文件的大小,提高索引的速度。但是前缀索引也有它的坏处:MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引,也不能把它们用作覆盖索引(Covering Index)。
集一个索引包含多个列(最左前缀匹配原则)
索引列的值必须唯一,但允许有空值
全文索引为FUllText,在定义索引的列上支持值的全文查找,允许在这些索引列中插入重复值和空值,全文索引可以在CHAR,VARCHAR,TEXT类型列上创建
设定主键后数据会自动建立索引,InnoDB为聚簇索引
即一个索引只包含单个列,一个表可以有多个单列索引
覆盖索引是指一个查询语句的执行只用从所有就能够得到,不必从数据表中读取,覆盖索引不是索引树,是一个结果,当一条查询语句符合覆盖索引条件时候,MySQL只需要通过索引就可以返回查询所需要的数据,这样避免了查到索引后的回表 *** 作,减少了I/O效率
查看索引
列名解析:
删除索引
查看:
删除前:
删除后:
普通的索引,没有什么介绍
查看:(注意和前缀索引Sub_part的区别)
当索引的列是unique的时候,会生成唯一索引,唯一索引关于null有下列两种情况
SQLSERVER 下的唯一索引的列,允许null值,但最多允许有一个空值
MYSQL下的唯一索引的列,允许null值,并且允许多个空值
查看:
会建立两个索引,一个非聚簇索引,一个是唯一索引
结果:
可以插入两个空值(明人不说暗话,我喜欢MySQL)
一方面,它不会索引所有字段所有字符,会减小索引树的大小.
另外一方面,索引只是为了区别出值,对于某些列,可能前几位区别很大,我们就可以使用前缀索引。
一般情况下某个前缀的选择性也是足够高的,足以满足查询性能。对于BLOB,TEXT,或者很长的VARCHAR类型的列,必须使用前缀索引,因为MySQL不允许索引这些列的完整长度。
查看:
查看:
复合索引的最左前缀匹配原则 :
对于复合索引,查询在一定条件才会使用该索引
减少开销。 建一个联合索引(col1,col2,col3),实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引,都会增加写 *** 作的开销和磁盘空间的开销。对于大量数据的表,使用联合索引会大大的减少开销!
覆盖索引。 对联合索引(col1,col2,col3),如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据,而无需回表,这减少了很多的随机io *** 作。减少io *** 作,特别的随机io其实是dba主要的优化策略。所以,在真正的实际应用中,覆盖索引是主要的提升性能的优化手段之一。
效率高。 索引列越多,通过索引筛选出的数据越少。有1000W条数据的表,有如下sql:select from table where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出10%的数据,如果只有单值索引,那么通过该索引能筛选出1000W10%=100w条数据,然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据,然后再排序,再分页;如果是联合索引,通过索引筛选出1000w10% 10% *10%=1w。
在模糊搜索中很有效,搜索全文中的某一个字段,可以参考这篇博文
: https://zhuanlan.zhihu.com/p/88275060
我们先进行下面一个实验看看InnoDB下的主键索引的一个现象。
查看:
我们插入进去的时候,数据的id都是乱序的,为什么这里最后select查询出来的结果都是进行了排序?
这是因为InnoDB索引底层实现的是B+tree,B+tree具有下列的特点:
所以上面的排序是为了使用B+tree的结构 ,B+tree为了范围搜索,将主键按照从小到大排序后,拆分成节点。后续还有新的节点进入的时候,和B-tree相同的 *** 作,会进行分裂。
一般来说,聚簇索引的B+tree都是三层
InnoDB中主键索引一定是聚簇索引,聚簇索引一定是主键索引。
为什么这里辅助索引叶子结点不直接存储数据呢?
MYISAM只有非聚簇索引,索引最终指向的都是物理地址。
Q:既然有回表的存在,那么聚簇索引的优势在哪里?
Q:主键索引作为聚簇索引需要注意什么
在查询语句中使用LIke关键字进行查询时,如果匹配字符串的第一个字符为"%",索引不会使用。如果“%”不是在第一位,索引就会使用
多列索引是在表的多个字段上创建的索引,满足最左前缀匹配原则,索引才会被使用
查询语句只有Or关键字时候,如果OR前后的两个条件都是索引,这这次查询将会使用索引,否则Or前后有一个条件的列不是索引,那么查询中将不使用索引
使用索引是数据库性能优化的必备技能之一。在MySQL数据库中,有四种索引:聚集索引(主键索引)、普通索引、唯一索引以及我们这里将要介绍的全文索引(FULLTEXT INDEX)。全文索引(也称全文检索)是目前搜索引擎使用的一种关键技术。它能够利用「分词技术「等多种算法智能分析出文本文字中关键字词的频率及重要性,然后按照一定的算法规则智能地筛选出我们想要的搜索结果。在这里,我们就不追根究底其底层实现原理了,现在我们来看看在MySQL中如何创建并使用全文索引。
在MySQL中,创建全文索引相对比较简单。例如,我们有一个文章表(article),其中有主键ID(id)、文章标题(title)、文章内容(content)三个字段。现在我们希望能够在title和content两个列上创建全文索引,article表及全文索引的创建SQL语句如下:
--创建article表
CREATE TABLE article (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
content TEXT,
FULLTEXT (title, content) --在title和content列上创建全文索引
)
上面就是在创建表的同时建立全文索引的SQL示例。此外,如果我们想要给已经存在的表的指定字段创建全文索引,同样以article表为例,我们可以使用如下SQL语句进行创建:
--给现有的article表的title和content字段创建全文索引
--索引名称为fulltext_article
ALTER TABLE article
ADD FULLTEXT INDEX fulltext_article (title, content)
在MySQL中创建全文索引之后,现在就该了解如何使用了。众所周知,在数据库中进行模糊查询是使用LIKE关键字进行查询,例如:
SELECT * FROM article WHERE content LIKE '%查询字符串%'
那么,我们使用全文索引也是这样用的吗?当然不是,我们必须使用特有的语法才能使用全文索引进行查询。例如,我们想要在article表的title和content列中全文检索指定的查询字符串,可以如下编写SQL语句:
SELECT * FROM article WHERE MATCH(title, content) AGAINST('查询字符串')
强烈注意:MySQL自带的全文索引只能用于数据库引擎为MyISAM的数据表,如果是其他数据引擎,则全文索引不会生效。此外,MySQL自带的全文索引只能对英文进行全文检索,目前无法对中文进行全文检索。如果需要对包含中文在内的文本数据进行全文检索,我们需要采用Sphinx(斯芬克斯)/Coreseek技术来处理中文。本站将会在后续文章中对Sphinx以及Coreseek进行介绍。
备注1:目前,使用MySQL自带的全文索引时,如果查询字符串的长度过短将无法得到期望的搜索结果。MySQL全文索引所能找到的词的默认最小长度为4个字符。另外,如果查询的字符串包含停止词,那么该停止词将会被忽略。
备注2:如果可能,请尽量先创建表并插入所有数据后再创建全文索引,而不要在创建表时就直接创建全文索引,因为前者比后者的全文索引效率要高。
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)