MySQL索引优化系列:(一)索引的类型

·  阅读 1586
MySQL索引优化系列:(一)索引的类型

「这是我参与11月更文挑战的第14天,活动详情查看:2021最后一次更文挑战

一、MySQL简介

MySQL 是最流行的关系型数据库管理系统,属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一,在 WEB 应用方面,MySQL是最好的 RDBMS (Relational Database Management System,关系数据库管理系统) 应用软件之一。

MySQL是一种开放源代码的关系型数据库管理系统,使用最常用的数据库管理语言--结构化查询语言(SQL)也就是我们常说的crud(增加(Create)、检索(Retrieve)、更新(Update)和删除(Delete)几个单词的首字母简写)来进行数据库管理。

简单来说,就是一个存储数据的仓库,对于网站运行MySQL就是它的命脉,同时MySQL 可以在几乎所有平台上(UNIX, Linux, Windows)运营。

这里有两个很重要的概念需要知道:

数据库

我们可以从其名字来说,数据库的意思是数据的集合。我们电脑上的文件夹可以看作一个数据库;使用音乐软件,里面的歌曲资源是存储在数据库里的;在社交媒体上发的动态同样也是存储在数据库里的。

用比较规范的语言描述一下就是:电子化的文件柜,是一个长期存储在计算机内的有组织的可共享的统一管理大量数据集合

SQL(结构化查询语言)

SQL 是用于访问和处理数据库的标准的计算机语言

结构化查询语言,是一种特殊目的的编程语言,是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统。

SQL 的数据定义语言 (DDL) 部分使我们有能力创建或删除表格。例如:CREATE DATABASE - 创建新数据库

二、索引分类

mysql的索引我们分为三大类来讲单列索引(普通索引,唯一索引,主键索引)、组合索引(联合索引/多列索引)、全文索引

索引

简单来说,索引就是一个指针,指向表里的数据。

  • 索引通常与相应的表时分开保存的,目的是提高检索的性能。
  • 索引的创建与删除不会影响数据本身,但会影响数据检索的速度。
  • 索引也会占据物理存储空间,可能比表本身还大,因此创建索引也要考虑存储空间。

单列索引

普通索引

没有任何限制,允许在定义索引的列中插入重复值和空值,创建一个单列索引的2种方式:

1]create index index_name on tbl_name(index_col_name)
2]alter table 表名 add index index_name on(index_col_name)
复制代码

这里,create index表示创建一个索引,index_name表示索引的名称,on tbl_name表示要创建索引的表, index_col_name表示表中要创建索引的列

唯一索引

索引列中的值必须是唯一的,但是允许为空值,也就是不允许具有索引值相同的行。

CREATE UNIQUE CLUSTERED INDEX myclumn_cindex ON mytable(mycolumn)
复制代码

这里,CLUSTERED INDEX是用来建立聚簇索引的关键字,此语句的意思是在表mytable上的mycolumn字段上创建一个名为myclumn_cindex的聚簇索引,且为唯一索引。

主键索引

是一种特殊的唯一索引,不允许有空值。简称主键,原文是PRIMARY KEY,由一个或多个列组成,用于唯一性标识数据表中的某一条记录。一个表可以没有主键,但最多只能有一个主键,并且主键值不能包含NULL。

可以这样来记忆:主键=主键索引=聚集索引

实例:一般id作为表的主键

 PRIMARY KEY (`id`),
复制代码

组合索引(联合索引/多列索引)

联合索引是指对表上的多个列进行索引,联合索引也是一棵B+树. 命名规则:表名_字段名

  • 1、需要加索引的字段,要在where条件中
  • 2、数据量少的字段不需要加索引
  • 3、如果where条件中是OR关系,加索引不起作用
  • 4、符合最左原则(where条件必须有联合索引的第一个字段)

例如:

CREATE TABLE `test` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `age` varchar(2) NOT NULL,
  `sex` varchar(2) NOT NULL,
  `phone` varchar(12) NOT NULL,
  `email` varchar(25) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_name_age_sex` (`name`,`age`,`sex`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
复制代码

这就是联合索引:

 `idx_name_age_sex` (`name`,`age`,`sex`) 
复制代码

使用组合索引时遵循最左前缀集合,这里由name、age和sex个字段构成的索引,索引行中就按name/age/sex的顺序存放,索引可以索引下面字段组合(name,age,sex)、(name,age)或者(name)或者(name,sex)。

举例具体的sql,以下都会走联合索引的:

 select  * from test where name = '张三' and age = '18' and sex = '男';
 select  * from test where name = '张三' and age = '18';
 select  * from test where name = '张三' ;
 select  * from test where name = '张三' and sex = '男';
 select  * from test where and sex = '男' and age = '18' name = '张三';
复制代码

注意,第5条sql语句是可以执行的,联合索引与where条件的顺序无关!

然后看几个不会走联合索引的:

 select  * from test where  and age = '18' and sex = '男';
 select  * from test where  and age = '18' or sex = '男';
 select  * from test where  and age = '18' ;
 select  * from test where  and sex = '男';
复制代码

覆盖索引

覆盖索引其实是一种特殊的联合索引,即是你查询的字段的所有数据都在索引上,不需要再进行一次回表查询,这样的索引即为覆盖索引。

 SELECT name,age,sex from test where name = '%三%';
复制代码

全文索引(fulltext/倒排文档技术)

只有在MyISAM引擎上才能使用,只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引,全文索引在平时的业务中用到的不多。不过全文索引技术是搜索引擎的关键技术。也被叫做倒排文档技术

创建全文索引实例:

CREATE TABLE test (
title VARCHAR(40),
FULLTEXT(title)
);
复制代码

全文索引的三种类型

  1. 自然语言搜索将搜索字符串解释为自然语言中短语。
  2. 布尔全文搜索
  3. 查询扩展搜索

这里就只作简单介绍,可以简单了解下,就不深入讲解了。

致谢

  1. 百度百科
  2. 2. MySQL是什么?
  3. w3school
  4. 索引的类型分类、区别、优缺点
  5. MySQL全文索引功能
  6. Mysql全文索引
分类:
后端
标签:
收藏成功!
已添加到「」, 点击更改