MySQL的索引

最新推荐文章于 2024-07-14 10:15:00 发布

吕维尧

最新推荐文章于 2024-07-14 10:15:00 发布

阅读量869

点赞数 1

文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/weixin_60589106/article/details/122751601

版权

介绍

索引时通过某种算法,构建出一个数据模型,用于快速找出某个列中一特定值得行,不使用索引,MySQL必须从第一条记录开始读完整个表,知道找出相关的行,表越大,查询数据所花费的时间就越多如果表中查询的列有一个索引,MySQL能够快速达到一个位置去搜索数据文件,而不必查看所有的数据,那么将会节省很大一部分时间.

索引的分类

索引时储存引擎用来快速查找记录的一种数据结构,按照显示的方式类分,主要有Hash索引和B+Tree索引

Hash索引

B+Tree索引

索引的操作

单列索引

一个索引只包单个列,单一个表中可以有多个单列索引

普通索引

MySQL中基本索引类型,没有什么限制,允许在定义索引的列中插入重复值和空值,纯粹为了查询数据更快一点

创建方式

create database mydb5;
use mydb5;
-- 方式1-创建表的时候直接指定
create table student(
	sid int primary key,
	card_id varchar(20),
	name varchar(20),
	gender varchar(20),
	age int,
	birth date,
	phone_num varchar(20),
	score double,
	index index_name(name) -- 给name列创建索引
);

-- 方式2-直接创建
create index index_gender on student(gender);

-- 方式3-修改表结构(添加索引) 
alter table student add index index_age(age);

注意:默认索引方式是BTREE

查看索引

操作

use student;
drop index index_gender on student;

唯一索引

唯一索引与前面的普通索引类似,不同的就是:索引列的值必须唯一,但是允许有空值,如果是组合索引,则列值得组合必须唯一.

创建方式

-- 方式1-创建表的时候直接指定
create table student2(
	sid int primary key,
	card_id varchar(20),
	name varchar(20),
	gender varchar(10),
	age int,
	brith date,
	phone_num varchar(20),
	score double,
	unique index_card_id(card_id) -- 给card_id列创建索引
);

-- 方式2-直接创建
create unique index index_card_id on student2(card_id);

-- 方式3-修改表结构(添加索引)
alter table student2 add unique index_phone_num(phone_num);

主键索引

每张表一般都会有自己的主键,当我们在创建表时,MySQL会自动在主键列上建立一个索引,这就是主键索引,主键是具有唯一性并不允许为null,所以他是一种特殊的唯一索引.

删除索引

组合索引

组合索引也叫复合索引,指的是我们在简历索引的时候使用多个字段,例如同时使用身份证和手机号建立索引,同样的可以建立为普通索引或者唯一索引,复合索引的使用复合最多原则

格式

普通组合索引

创建方式

-- 组合索引
-- 创建索引基本用法
create index index_phone_index on student(phone_num,name);

唯一组合索引

创建方式

-- 创建索引的基本语法 -- 唯一索引
/*
	1 a
	1 b
	2 a
	1 a -- 不行 唯一复合索引指的是两列不能完全一样
*/
create unique index index_phone_name on student(phone_num,name);

最左原则

如图三条sql只有234能使用到索引indx_phone_name,因为条件里必须包含索引前面的字段才能够进行匹配,而3和4相比where条件的顺序不一样,为什么4可以使用到索引呢?是因为mysql本身就有一层sql优化,他会根据sql来识别出来该用哪个索引,我们可以理解为3和4在mysql中是等价的.

全文索引

全文索引的关键字fulltext

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

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

概述

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

1.MySQL5.6以前版本只有MyISAM存储引擎支持全文索引

2.MySQL5.6及以后的版本,MyISAM和InnoDB存储引擎均支持全文索引

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

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

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

6.MySQL中的全文索引,有两个变量,最小搜索长度和最大搜索长度,对于长度小于最小搜索长度和大于最大搜索长度的语句,都不会被索引,通俗点就是说,相对一个词语使用全文索引搜索,那么这个词语长度必须在以上两个变量的区间内.默认存储引擎为innodb.这两个默认值可以使用以下命令去查看:

show variables like '%ft%';

创建全文索引

create table t_article(
	id int primary key auto_increment,
	title varchar(255),
	context varchar(1000),
	writing_date date
	-- fulltext (content) -- 创建表时添加全文所搜(不推荐)
);


insert into t_article values (null,'Hello','See u','2022-1-31');

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

-- 添加全文索引
create fulltext index index_content on t_article(content);

使用全文索引

和常用的模糊匹配使用like+%不同,全文索引有自己的语法和格式,使用match和against关键字,需要注意最小和最大搜索长度

-- 使用全文索引
select*from t_article where match(context) against ('See');

空间索引

介绍

1.MySQL5.7之后的版本支持了空间索引,而且支持OpenGIS几何数据模型

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

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

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

创建空间索引

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)
);