本节讲解组合索引、全文索引及空间索引。空间索引了解即可,一般情况使用不多。

组合索引

  • 组合索引也叫复合索引,指的是我们在建立索引的时候使用多个字段。

  • 例如同时使用身份证和手机号建立索引,同样的可以建立为普通索引或者是唯一索引。

  • 复合索引的使用复合最左原则。

创建语法

普通索引:

-- 创建索引的基本语法
-- 后面跟的列名
create index indexname on table_name(column1(length),column2(length)); 

唯一索引:

create unique index indexname on table_name(column1(length),column2(length)); 

(这里的唯一索引只要求两列的值不完全相同,例如“张三 男”和“张三 女”是可以的,不算重复)

示例:

-- 创建索引的基本语法-- 普通索引
-- create index indexname on table_name(column1(length),column2(length)); 
create index index_phone_name on student(phone_num,name);

-- 操作-删除索引
 drop index index_phone_name on student; 

-- 创建索引的基本语法-- 唯一索引
create  unique index index_phone_name on student(phone_num,name); 

最左原则

需要注意的是,写组合索引的列名顺序,满足最左原则。

针对语句:create unique index index_phone_name on student(phone_num,name);

(主要是顺序phone_num,name

select * from student where name = '张三'; 

select * from student where phone_num = '15100046637'; 
select * from student where phone_num = '15100046637' and name = '张三'; 
select * from student where name = '张三' and phone_num = '15100046637'; 
/* 
  只有 2 、 3、4能使用的到索引idx_phone_name,因为条件里面必须包含索引前面的字段  才能够进行匹配。
  而3和4相比where条件的顺序不一样,为什么4可以用到索引呢?是因为mysql本身就有一层sql优化,他会根据sql来识别出来该用哪个索引,我们可以理解为3和4在mysql眼中是等价的。 
*/

全文索引

  • 全文索引的关键字是fulltext

  • 全文索引主要用来查找文本中的关键字,而不是直接与索引中的值相比较,它更像是一个搜索引擎,基于相似度的查询,而不是简单的where语句的参数匹配。

  • 用 like + % 就可以实现模糊匹配了,为什么还要全文索引?like + % 在文本比较少时是合适的,但是对于大量的文本数据检索,是不可想象的。全文索引在大量的数据面前,能比 like + % 快 N 倍,速度不是一个数量级,但是全文索引可能存在精度问题。

支持情况

全文索引的版本、存储引擎、数据类型的支持情况:

  • MySQL 5.6 以前的版本,只有 MyISAM 存储引擎支持全文索引;

  • MySQL 5.6 及以后的版本,MyISAM 和 InnoDB 存储引擎均支持全文索引;

  • 只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引;

  • 在数据量较大时候,现将数据放入一个没有全局索引的表中,然后再用create index创建fulltext索引,要比先为一张表建立fulltext然后再将数据写入的速度快很多;

  • 测试或使用全文索引时,要先看一下自己的 MySQL 版本、存储引擎和数据类型是否支持全文索引。

MySQL 中的全文索引,有两个变量,最小搜索长度和最大搜索长度,对于长度小于最小搜索长度和大于最大搜索长度的词语,都不会被索引。通俗点就是说,想对一个词语使用全文索引搜索,那么这个词语的长度必须在以上两个变量的区间内。这两个的默认值可以使用以下命令查看:(例如查询关键字'ft')

show variables like '%ft%';

几个需要注意的量

# 参数名称 默认值 最小值 最大值 作用
1 ft_min_word_len 4 1 3600 MyISAM 引擎表全文索引包含的最小词长度
2 ft_query_expansion_limit 20 0 1000 MyISAM引擎表使用 with query expansion 进行全文搜索的最大匹配数
3 innodb_ft_min_token_size 3 0 16 InnoDB 引擎表全文索引包含的最小词长度
4 innodb_ft_max_token_size 84 10 84 InnoDB 引擎表全文索引包含的最大词长度

创建索引

关键字fulltext

创建表的时候添加全文索引

create table t_article (
     id int primary key auto_increment ,
     title varchar(255) ,
     content varchar(1000) ,
     writing_date date , 
     fulltext (content) -- 创建全文检索
);

也可以在创建表之后添加全文索引。

-- 修改表结构添加全文索引
alter table t_article add fulltext index_content(content);
 
-- 直接添加全文索引
create fulltext index index_content on t_article(content);

使用索引

使用全文索引

和常用的模糊匹配使用 like + % 不同,全文索引有自己的语法格式,使用 matchagainst关键字,格式:

match (col1,col2,...)  against(expr [search_modifier])

示例:

select * from t_article where match(content) against('yo'); -- 没有结果 单词数需要大于等于3 
select * from t_article where match(content) against('you'); -- 有结果

案例

use mydb5;

-- 创建表的时候添加全文索引
create table t_article (
     id int primary key auto_increment ,
     title varchar(255) ,
     content varchar(1000) ,
     writing_date date -- , 
     -- fulltext (content) -- 创建全文检索
);

-- 添加数据
insert into t_article values(null,"Yesterday Once More","When I was young I listen to the radio",'2021-10-01');
insert into t_article values(null,"Right Here Waiting","Oceans apart, day after day,and I slowly go insane",'2021-10-02'); 
insert into t_article values(null,"My Heart Will Go On","every night in my dreams,i see you, i feel you",'2021-10-03');
insert into t_article values(null,"Everything I Do","eLook into my eyes,You will see what you mean to me",'2021-10-04');
insert into t_article values(null,"Called To Say I Love You","say love you no new year's day, to celebrate",'2021-10-05');
insert into t_article values(null,"Nothing's Gonna Change My Love For You","if i had to live my life without you near me",'2021-10-06');
insert into t_article values(null,"Everybody","We're gonna bring the flavor show U how.",'2021-10-07');

-- 修改表结构添加全文索引
-- alter table t_article add fulltext index_content(content);
 
-- 直接添加全文索引
create fulltext index index_content on t_article(content);

-- 使用全文索引
-- 使用了两个关键字:match和against
select * from t_article where match(content) against('yo'); -- 没有结果 单词数需要大于等于3 
-- 在前面的支持情况里已经做了说明
-- 最小搜索长度、最大搜索长度
select * from t_article where match(content) against('you'); -- 有结果

空间索引(了解)

  • MySQL在5.7之后的版本支持了空间索引,而且支持OpenGIS几何数据模型

  • 空间索引是对空间数据类型的字段建立的索引,MYSQL中的空间数据类型有4种,分别是GEOMETRY、POINT、LINESTRING、POLYGON。

  • MYSQL使用SPATIAL关键字进行扩展,使得能够用于创建正规索引类型的语法创建空间索引。

  • 创建空间索引的列,必须将其声明为NOT NULL。

  • 空间索引一般是用的比较少,了解即可。

MYSQL中的空间数据类型有4种,分别是GEOMETRY、POINT、LINESTRING、POLYGON。就是空间数据类型。

类型 含义 说明
Geometry 空间数据 任何一种空间类型
Point 坐标值
LineString 线 有一系列点连接而成
Polygon 多边形 由多条线组成

创建:

create table shop_info (
  id  int  primary key auto_increment comment 'id',
  shop_name varchar(64) not null comment '门店名称',
  geom_point geometry not null comment '经纬度',      -- 创建空间数据类型
  spatial key geom_index(geom_point)                 -- 
);

声明:内容来源于B站视频《2022黑马程序员最新MySQL知识精讲+mysql实战案例_零基础mysql数据库入门到高级全套教程》,博客内容仅作学习参考使用。



博主个人公众号
版权声明 ▶ 本网站名称:陶小桃Blog
▶ 本文链接:https://www.52txr.cn/2022/mysql27.html
▶ 本网站的文章部分内容可能来源于网络,仅供大家学习与参考,如有侵权,请联系站长进行核实删除。
▶ 转载本站文章需要遵守:商业转载请联系站长,非商业转载请注明出处!!

最后修改:2022 年 07 月 04 日
如果觉得我的文章对你有用,请随意赞赏