自学内容网 自学内容网

记录些MySQL题集(12)

MySQL 整体架构

MySQL整体结构浅析

图片

MySQL整体架构

从上往下看,依次会分为网络连接层、系统服务层、存储引擎层、以及文件系统层,往往编写SQL后,都会遵守着MySQL的这个架构往下走。

  • 连接层:主要是指数据库连接池,会负责处理所有客户端接入的工作。

  • 服务层:主要包含SQL接口、解析器、优化器以及缓存缓冲区四块区域。

  • 存储引擎层:这里是指MySQL支持的各大存储引擎,如InnoDB、MyISAM等。

  • 文件系统层:涵盖了所有的日志,以及数据、索引文件,位于系统硬盘上。

网络连接层

当一个客户端尝试与MySQL建立连接时,MySQL内部都会派发一条线程负责处理该客户端接下来的所有工作。而数据库的连接层负责的就是所有客户端的接入工作,MySQL的连接一般都是基于TCP/IP协议建立网络连接,因此凡是可以支持TCP/IP的语言,几乎都能与MySQL建立连接。

其实MySQL还支持另一种连接方式,就是Unix系统下的Socket直连,但这种方式一般使用的较少。

虽然MySQL是基于TCP/IP协议栈实现的连接建立工作,但并非使用HTTP协议建立连接的,一般建立连接的具体协议,都会根据不同的客户端实现,如jdbc、odbc...这类的。在这里先暂且不纠结连接MySQL时的协议类型,先来看看一般是怎么连接MySQL的?如下:

mysql -h 127.0.0.1 -uroot -p123456

例如上述这条指令,-h表示MySQL所在的服务器IP地址,-u表示本次连接所使用的用户名,-p则代表着当前用户的账号密码,当执行这条指令后,会与MySQL-Server建立网络连接,也就是会经历《TCP的三次握手过程》。当然,MySQL也支持SSL加密连接,如果采用这种方式建立连接,那还会经过《SSL多次握手过程》,当握手结束,网络建立成功后,则会开始正式的数据库连接建立工作。

TCP网络连接建立成功后,MySQL服务端与客户端之间会建立一个session会话,紧接着会对登录的用户名和密码进行效验,MySQL首先会查询自身的用户表信息,判断输入的用户名是否存在,如果存在则会判断输入的密码是否正确,如若密码错误或用户名不存在就会返回1045的错误码,如下信息:

ERROR 1045 (28000): Access denied for user 'zhuzi'@'localhost' (using password: YES)

如果你在连接数据库的过程中,出现了上述的错误信息,那绝对是你输入的用户名或密码错误导致的,当账号及密码正确时,此时就会进入MySQL的命令行,接下来可以执行SQL操作。

但实际上,在用户名和密码都正确的情况下,MySQL还会做一些些小动作,也就是会进行授权操作,查询每个用户所拥有的权限,并对其授权,后续SQL执行时,都会先判断是否具备执行相应SQL语句的权限,然后再执行。

经过上述流程后数据库连接就建立成功了,数据库连接建立成功后,MySQL与客户端之间会采用半双工的通讯机制工作,与之对应的还有“全双工、单工”的工作模式:

  • 全双工:代表通讯的双方在同一时间内,即可以发送数据,也可以接收数据。

  • 半双工:代表同一时刻内,单方要么只能发送数据,要么只能接受数据。

  • 单工:当前连接只能发送数据或只能接收数据,也就是“单向类型的通道”。

到这里,MySQL也会“安排”一条线程维护当前客户端的连接,这条线程也会时刻标识着当前连接在干什么工作,可以通过show processlist;命令查询所有正在运行的线程:

执行结果如下( root账号可以查询所有线程):

图片

线程查询

  • Id:当前线程的ID值,可以利用这个ID,使用kill强杀线程。

  • User:当前线程维护的数据库连接,与之对应的用户是谁。

  • Host:与当前线程保持连接关系的客户端地址(IP+Port)。

  • db:目前线程在哪个数据库中执行SQL

  • Command:当前线程正在执行的SQL类型,如:

    • Create DB:正在执行创建数据库的操作。

    • Drop DB:正在执行删除数据库的操作。

    • Execute:正在执行预编译的SQLPreparedStatement)。

    • Close Stmt:正在关闭一个PreparedStatement

    • Query:正在执行普通的SQL语句。

    • Sleep:正在等待客户端发送SQL语句。

    • Quit:当前客户端正在退出连接。

    • Shutdown:正在关闭MySQL服务端。

  • Time:表示当前线程处于目前状态的时间,单位是秒。

  • State:表示当前线程的状态,有如下几种:

    • Updating:当前正在执行update语句,匹配数据做修改操作。

    • Sleeping:正在等待客户端发送新的SQL语句。

    • Starting:目前正在处理客户端的请求。

    • Checking table:目前正在表中查询数据。

    • Locked:当前线程被阻塞,其他线程获取了执行需要的锁资源。

    • Sending Data:目前执行完成了Select语句,正在将结果返回给客户端。

  • Info:一般记录当前线程正在执行的SQL,默认显示前一百个字符,查看完整的SQL可以使用show full processlist;命令。

其实从这个结果上来看,我们能够很明显的看到数据库中各个线程的信息,这条指令对于以后做线上排查时有很大的作用,目前先简单了解,接着来看看数据库连接池。

数据库连接池(Connection Pool)

Connection Pool翻译过来的意思就是连接池,那为什么需要有这个东西呢?所有的客户端连接都需要一条线程去维护,而线程资源无论在哪里都属于宝贵资源,因此不可能无限量创建,所以这里的连接池就相当于Tomcat中的线程池,主要是为了复用线程、管理线程以及限制最大连接数的。

连接池的最大线程数可以通过参数max-connections来控制,如果到来的客户端连接超出该值时,新到来的连接都会被拒绝,关于最大连接数的一些命令主要有两条:

  • • show variables like '%max_connections%';:查询目前DB的最大连接数。

  • • set GLOBAL max_connections = 200;:修改数据库的最大连接数为指定值。

对于不同的机器配置,可以适当的调整连接池的最大连接数大小,以此可以在一定程度上提升数据库的性能。除了可以查询最大连接数外,MySQL本身还会对客户端的连接数进行统计,对于这点可以通过命令show status like "Threads%";查询:

图片

连接数查询

其中各个字段的释义如下:

  • Threads_cached:目前空闲的数据库连接数。

  • Threads_connected:当前数据库存活的数据库连接数。

  • Threads_createdMySQL-Server运行至今,累计创建的连接数。

  • Threads_running:目前正在执行的数据库连接数。

额外要说明的一点是Threads_cached这个字段,从名称上来看,似乎跟缓存有关系,其实也没错,因为这里是有一个数据库内部的优化机制。当一个客户端连接断开后,对于数据库连接却不会立马销毁,而是会先放入到一个缓存连接池当中。这样就能在下次新连接到来时,省去了创建线程、分配栈空间等一系列动作,但这个值不会是无限大的,一般都在32左右。

连接池的优化思想与Java线程池相同,会将数据库创建出的连接对象放入到一个池中,一旦出现新的访问请求会复用这些连接,一方面提升了性能,第二方面还节省了一定程度上的资源开销。

系统服务层

MySQL大多数核心功能都位于这一层,包括客户端SQL请求解析、语义分析、查询优化、缓存以及所有的内置函数(例如:日期、时间、统计、加密函数...),所有跨引擎的功能都在这一层实现,譬如存储过程、触发器和视图等一系列服务。

图片

服务层

主要包含SQL接口、解析器、优化器以及缓存相关的这些部分。当然[管理服务&工具组件],这块其实属于全局的,属于MySQL的基础设施服务。。

SQL接口

SQL接口组件,其实主要作用就是负责处理客户端的SQL语句,当客户端连接建立成功之后,会接收客户端的SQL命令,比如DML、DDL语句以及存储过程、触发器等,当收到SQL语句时,SQL接口会将其分发给其他组件,然后等待接收执行结果的返回,最后会将其返回给客户端。

简单来说,也就是SQL接口会作为客户端连接传递SQL语句时的入口,并且作为数据库返回数据时的出口。

简单展开两点,第一点是对于SQL语句的类型划分,第二点则是触发器。在SQL中会分为五大类:

  • DML:数据库操作语句,比如update、delete、insert等都属于这个分类。

  • DDL:数据库定义语句,比如create、alter、drop等都属于这个分类。

  • DQL:数据库查询语句,比如最常见的select就属于这个分类。

  • DCL:数据库控制语句,比如grant、revoke控制权限的语句都属于这个分类。

  • TCL:事务控制语句,例如commit、rollback、setpoint等语句属于这个分类。

再来聊一聊MySQL的触发器,想要了解触发器是什么,首先咱们还得先理解存储过程。

存储过程:是指提前编写好的一段较为常用或复杂SQL语句,然后指定一个名称存储起来,然后先经过编译、优化,完成后,这个“过程”会被嵌入到MySQL中。

也就是说,[存储过程]的本质就是一段预先写好并编译完成的SQL,触发器则是一种特殊的存储过程,但[触发器]与[存储过程]的不同点在于:存储过程需要手动调用后才可执行,而触发器可由某个事件主动触发执行。在MySQL中支持INSERT、UPDATE、DELETE三种事件触发,同时也可以通过AFTER、BEFORE语句声明触发的时机,是在操作执行之前还是执行之后。

说简单一点,[MySQL触发器]就类似于Spring框架中的AOP切面。

解析器

客户端连接发送的SQL语句,经过SQL接口后会被分发到解析器,解析器这东西其实在所有语言中都存在,Java、C、Go...等其他语言都有,解析器的作用主要是做词法分析、语义分析、语法树生成...这类工作的。

而解析器这一步的作用主要是为了验证SQL语句是否正确,以及将SQL语句解析成MySQL能看懂的机器码指令。稍微拓展一点大家就明白了,好比如我们编写如下一条SQL

select * form user;

然后运行会得到如下错误信息:

ERROR 1064 (42000): You have an error in your SQL syntax; check....

在上述SQL中,我们将from写成了form,结果运行时MySQL提示语法错误了,MySQL是如何发现的呢?就是在词法分析阶段,检测到了存在语法错误,因此抛出了对应的错误码及信息。当然,如果SQL正确,则会进行下一步工作,生成MySQL能看懂的执行指令。

优化器

解析器完成相应的词法分析、语法树生成....等一系列工作后,紧接着会来到优化器,优化器的主要职责在于生成执行计划,比如选择最合适的索引,选择最合适的join方式等,最终会选择出一套最优的执行计划。

当然,在这里其实有很多资料也会聊到,存在一个执行器的抽象概念,实际上执行器是不存在的,因此前面聊到过,每个客户端连接在MySQL中都用一条线程维护,而线程是操作系统的最小执行单位,因此所谓的执行器,本质上就是线程本身。

优化器生成了执行计划后,维护当前连接的线程会负责根据计划去执行SQL,这个执行的过程实际上是在调用存储引擎所提供的API

缓存&缓冲

主要分为了读取缓存与写入缓冲,读取缓存主要是指select语句的数据缓存,当然也会包含一些权限缓存、引擎缓存等信息,但主要还是select语句的数据缓存,MySQL会对于一些经常执行的查询SQL语句,将其结果保存在Cache中,因为这些SQL经常执行,因此如果下次再出现相同的SQL时,能从内存缓存中直接命中数据,自然会比走磁盘效率更高,对于Cache是否开启可通过命令查询。

  • show global variables like "%query_cache_type%";:查询缓存是否开启。

  • show global variables like "%query_cache_size%";:查询缓存的空间大小。

同时还可以通过show status like'%Qcache%';命令查询缓存相关的统计信息。

图片

缓存统计

  • Qcache_free_blocks:查询缓存中目前还有多少剩余的blocks

  • Qcache_free_memory:查询缓存的内存大小。

  • Qcache_hits:表示有多少次查询SQL命中了缓存。

  • Qcache_inserts:表示有多少次查询SQL未命中缓存然后走了磁盘。

  • Qcache_lowmem_prunes:这个值表示有多少条缓存数据从内存中被淘汰。

  • Qcache_not_cached:表示由于自己设置了缓存规则后,有多少条数据不符合缓存条件。

  • Qcache_queries_in_cache:表示当前缓存中缓存的数据数量。

  • Qcache_total_blocks:当前缓存区中blocks的数量。

MySQL5.7版本,对于这些依旧可以查询到,但是在高版本的MySQL中,移除了查询缓存区,毕竟命中率不高,而且查询缓存这一步还要带来额外开销,同时一般程序都会使用Redis做一次缓存,因此结合多方面的原因就移除了查询缓存的设计。

简单了解了查询缓存后,再来看看写入缓冲,缓冲区的设计主要是:为了通过内存的速度来弥补磁盘速度较慢对数据库造成的性能影响。在数据库中读取某页数据操作时,会先将从磁盘读到的页存放在缓冲区中,后续操作相同页的时候,可以基于内存操作。

一般来说,当对数据库进行写操作时,都会先从缓冲区中查询是否有你要操作的页,如果有,则直接对内存中的数据页进行操作(例如修改、删除等),对缓冲区中的数据操作完成后,会直接给客户端返回成功的信息,然后MySQL会在后台利用一种名为Checkpoint的机制,将内存中更新的数据刷写到磁盘。

MySQL在设计时,通过缓冲区能减少大量的磁盘IO,从而进一步提高数据库整体性能。毕竟每次操作都走磁盘,性能自然上不去的。

PS:后续高版本的MySQL移除了查询缓存区,但并未移除缓冲区。

同时缓冲区是与存储引擎有关的,不同的存储引擎实现也不同,比如InnoDB的缓冲区叫做innodb_buffer_pool,而MyISAM则叫做key_buffer

存储引擎层

存储引擎也可以理解成MySQL最重要的一层,在前面的服务层中,聚集了MySQL所有的核心逻辑操作,而引擎层则负责具体的数据操作以及执行工作。

MySQL则因为其开源特性,所以存在很多很多款不同的存储引擎实现,MySQL为了能够正常搭载不同的存储引擎运行,因此引擎层是被设计成可拔插式的,也就是可以根据业务特性,为自己的数据库选择不同的存储引擎。

MySQL的存储引擎主要分为官方版和民间版,前者是MySQL官方开发的,后者则是第三方开发的。存储引擎在MySQL中,相关的规范标准被定义成了一系列的接口,如果你也想要使用自己开发的存储引擎,那么只需要根据MySQL AB公司定义的准则,编写对应的引擎实现即可。

MySQL目前有非常多的存储引擎可选择,其中最为常用的则是InnoDBMyISAM引擎,可以通过show variables like '%storage_engine%';命令来查看当前所使用的引擎。其他引擎如下:

图片

存储引擎

存储引擎是MySQL数据库中与磁盘文件打交道的子系统,不同的引擎底层访问文件的机制也存在些许细微差异,引擎也不仅仅只负责数据的管理,也会负责库表管理、索引管理等,MySQL中所有与磁盘打交道的工作,最终都会交给存储引擎来完成。

文件系统层

图片

文件层

这一层则是MySQL数据库的基础,本质上就是基于机器物理磁盘的一个文件系统,其中包含了配置文件、库表结构文件、数据文件、索引文件、日志文件等各类MySQL运行时所需的文件,这一层的功能比较简单,也就是与上层的存储引擎做交互,负责数据的最终存储与持久化工作。

这一层主要可分为两个板块:①日志板块。②数据板块。

6.1、日志模块

   在MySQL中主要存在七种常用的日志类型,如下:

  • binlog二进制日志,主要记录MySQL数据库的所有写操作(增删改)。

  • redo-log重做/重写日志,MySQL崩溃时,对于未落盘的操作会记录在这里面,用于重启时重新落盘(InnoDB专有的)。

  • undo-logs撤销/回滚日志:记录事务开始前[修改数据]的备份,用于回滚事务。

  • error-log:错误日志:记录MySQL启动、运行、停止时的错误信息。

  • general-log常规日志,主要记录MySQL收到的每一个查询或SQL命令。

  • slow-log:慢查询日志,主要记录执行时间较长的SQL

  • relay-log:中继日志,主要用于主从复制做数据拷贝。

上述列出了MySQL中较为常见的七种日志,但实际上还存在很多其他类型的日志,不过一般对调优、排查问题、数据恢复/迁移没太大帮助,用的较少。

6.2、数据模块

MySQL的所有数据最终都会落盘(写入到磁盘),而不同的数据在磁盘空间中,存储的格式也并不相同,因此再列举出一些MySQL中常见的数据文件类型:

  • db.opt文件:主要记录当前数据库使用的字符集和验证规则等信息。

  • .frm文件:存储表结构的元数据信息文件,每张表都会有一个这样的文件。

  • .MYD文件:用于存储表中所有数据的文件(MyISAM引擎独有的)。

  • .MYI文件:用于存储表中索引信息的文件(MyISAM引擎独有的)。

  • .ibd文件:用于存储表数据和索引信息的文件(InnoDB引擎独有的)。

  • .ibdata文件:用于存储共享表空间的数据和索引的文件(InnoDB引擎独有)。

  • .ibdata1文件:这个主要是用于存储MySQL系统(自带)表数据及结构的文件。

  • .ib_logfile0/.ib_logfile1文件:用于故障数据恢复时的日志文件。

  • .cnf/.iniMySQL的配置文件,Windows下是.ini,其他系统大多为.cnf

上述列举了一些MySQL中较为常见的数据文件类型,无论是前面的日志文件,亦或是现在的数据文件,这些都是后续深入剖析MySQL时会遇到的。

MySQL 索引初识

 由于MySQL是作为存储层部署在业务系统的最后端,所有的业务数据最终都要入库落盘,但随着一个项目在线上运行的时间越来越久,数据库中的数据量自然会越来越多,而数据体积出现增长后,当需要从表查询一些数据时,效率会越发低下。在正常情况下,表的查询性能和数据量是成反比的,也就是数据越多,查询越慢。

这是什么原因导致的呢?由于MySQL默认的查询方式导致的。

SELECT * FROM `zz_student`;
+------------+--------+------+--------+
| student_id | name   | sex  | height |
+------------+--------+------+--------+
|          1 | 竹子   | 男   | 185cm  |
|          2 | 熊猫   | 女   | 170cm  |
|          3 | 子竹   | 男   | 182cm  |
|          4 | 棕熊   | 男   | 187cm  |
|          5 | 黑豹   | 男   | 177cm  |
|          6 | 脑斧   | 男   | 178cm  |
|          7 | 兔纸   | 女   | 165cm  |
+------------+--------+------+--------+

SELECT * FROM `zz_student`  WHERE name = "脑斧";

上面给出了一张学生表,其中有七位学生信息,而此时要查询姓名为「脑斧」的学生信息时,MySQL底层是如何检索数据的呢?会触发磁盘IO,对表中的数据进行逐条读取并判断,也就是说,在这里想要查找到符合要求的数据,至少要经过六次磁盘IO才能检索到目标(暂且先不考虑局部性读取原理与随机IO)。

  • 那假设这个表中有1000W条数据呢?要查的目标数据位于表的900W行以后怎么办?岂不是要触发几百万次磁盘IO才能检索到数据啊,如果真的这样去干,其效率大家可想而知。

在这种情况下,又该如何去提升数据库的查询性能呢?因为查询往往都是一个业务系统中最频繁的操作,一般项目的写/读请求比例都遵循三七定律,也就是30%的请求会涉及到写库操作,另外70%则属于查库类型的操作。

一、MySQL索引机制概述

索引就是用来帮助表快速检索目标数据的。此时先来简单回顾一下MySQL中索引是如何使用的呢?首先需要创建索引,MySQL可以通过CREATE、ALTER、DDL三种方式创建一个索引。

1.1、MySQL索引的创建方式

  •  ①使用CREATE语句创建

CREATE INDEX indexName ON tableName (columnName(length) [ASC|DESC]);

这种创建方式可以给一张已存在的表结构添加索引,其中需要指定几个值:

  • indexName:当前创建的索引,创建成功后叫啥名字。

  • tableName:要在哪张表上创建一个索引,这里指定表名。

  • columnName:要为表中的哪个字段创建索引,这里指定字段名。

  • length:如果字段存储的值过长,选用值的前多少个字符创建索引。

  • ASC|DESC:指定索引的排序方式,ASC是升序,DESC是降序,默认ASC

当然,上述语句中的INDEX也可更改为KEY,作用都是创建一个普通索引,而对于其他的索引类型,这点在后续的索引分类中再聊。

  • ②使用ALTER语句创建

ALTER TABLE tableName ADD INDEX indexName(columnName(length) [ASC|DESC]);

这里的参数都相同,所以不再重复赘述。

  • ③建表时DDL语句中创建

CREATE TABLE tableName(  
  columnName1 INT(8) NOT NULL,   
  columnName2 ....,
  .....,
  INDEX [indexName] (columnName(length))  
);

这种方式就比较适合在库表设计时,已经确定了索引项的情况下建立。

1.2、查询、删除、指定索引

但不管通过哪种方式建立索引,本质上创建的索引都是相同的,当索引创建完成后,可通过SHOW INDEX FROM tableName;这条命令查询一个表中拥有的索引,如下:

CREATE TABLE `zz_user`  (
  `user_id` int(8) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(255) NULL DEFAULT "",
  `user_sex` varchar(255) NULL DEFAULT "",
  `user_phone` varchar(255) NULL DEFAULT "",
    PRIMARY KEY (`user_id`) USING BTREE
)
    ENGINE = InnoDB 
    CHARACTER SET = utf8 
    COLLATE = utf8_general_ci 
    ROW_FORMAT = Compact;

在上述的建表SQL中,为user_id创建了一个主键索引,然后来查一下当前表的索引信息:

图片

索引查询
简单的概述一下查询后,每个字段的含义:

  • Table:当前索引属于那张表。

  • Non_unique:目前索引是否属于唯一索引,0代表是的,1代表不是。

  • Key_name:当前索引的名字。

  • Seq_in_index:如果当前是联合索引,目前字段在联合索引中排第几个。

  • Column_name:当前索引是位于哪个字段上建立的。

  • Collation:字段值以什么方式存储在索引中,A表示有序存储,NULL表无序。

  • Cardinality:当前索引的散列程度,也就是索引中存储了多少个不同的值。

  • Sub_part:当前索引使用了字段值的多少个字符建立,NULL表示全部。

  • Packed:表示索引在存储字段值时,以什么方式压缩,NULL表示未压缩,

  • Null:当前作为索引字段的值中,是否存在NULL值,YES表示存在。

  • Index_type:当前索引的结构(BTREE, FULLTEXT, HASH, RTREE)。

  • Comment:创建索引时,是否对索引有备注信息。

这条命令在后续排除问题、性能调优时,会有不小的作用,比如可以通过分析其中的Cardinality字段值,如果该值少于数据的实际行数,那目前索引有可能失效(对于这些后续排查篇和SQL优化篇再聊)。

MySQL中并未提供修改索引的命令,也就说当你建错了索引,只能先删再重新建立一次,删除索引的语句如下:

DROP INDEX indexName ON tableName;

当然,当建立了一条索引后,也可以强制性的为SELECT语句指定索引,如下:

SELECT * FROM table_name FORCE INDEX(index_name) WHERE .....;

FORCE INDEX关键字可以为一条查询语句强制指定走哪个索引查询,但要牢记的是:如果当前的查询SQL压根不会走指定的索引字段,哪这种方式是行不通的,这个关键字的用法是:一条查询语句在有多个索引可以检索数据时,显式指定一个索引,减少优化器选择索引的耗时。

1.3、数据库索引的本质

索引本质上在数据库中是什么呢?数据库是基于磁盘工作的,所有的数据都会放到磁盘上存储,而索引也是数据的一种,因此与表数据相同,最终创建出的索引也会在磁盘生成本地文件。

不过索引文件在磁盘中究竟以何种方式存储,这是由索引的数据结构来决定的。同时,由于索引机制最终是由存储引擎实现,因此不同存储引擎下的索引文件,其保存在本地的格式也并不相同。

在这里有一个点需要注意:建立索引的工作在表数据越少时越好,如果你想要给一张百万、千万条数据级别的表新创建一个索引,那创建的耗时也不短,这是为什么呢?

索引本质上和表是一样的,都是磁盘中的文件,那也就代表着创建一个索引,并不像单纯的给一张表加个约束那么简单,而是会基于原有的表数据,重新在磁盘中创建新的本地索引文件。假设表中有一千万条数据,那创建索引时,就需要将索引字段上的1000W个值全部拷贝到本地索引文件中,同时做好排序并与表数据产生映射关系。

二、MySQL的索引分类

聚簇索引、非聚簇索引、唯一索引、主键索引、联合索引、全文索引、单列索引、多列索引、复合索引、普通索引、二级索引、辅助索引、次级索引、有序索引、B+Tree索引、R-Tree索引、T-Tree索引、Hash索引、空间索引、前缀索引等。

2.1、数据结构层次

索引建立后也会在磁盘生成索引文件,那每个具体的索引节点该如何在本地文件中存放呢?这点是由索引的数据结构来决定的。比如索引的底层结构是数组,那所有的索引节点都会以Node1→Node2→Node3→Node4....这样的形式,存储在磁盘同一块物理空间中,不过MySQL的索引不支持数组结构,或者说数组结构不适合作为索引结构,MySQL索引支持的数据结构如下:

  • B+Tree类型:MySQL中最常用的索引结构,大部分引擎支持,有序。

  • Hash类型:大部分存储引擎都支持,字段值不重复的情况下查询最快,无序。

  • R-Tree类型:MyISAM引擎支持,也就是空间索引的默认结构类型。

  • T-Tree类型:NDB-Cluster引擎支持,主要用于MySQL-Cluster服务中。

在上述的几种索引结构中,B+树和哈希索引是最常见的索引结构,几乎大部分存储引擎都实现了,对于后续两种索引结构在某些情况下也较为常见,但除开列出的几种索引结构外,MySQL索引支持的数据结构还有R+、R*、QR、SS、X树等结构。

索引到底支持什么数据结构,这是由存储引擎决定的,不同的存储引擎支持的索引结构也并不同,目前较为常用的引擎就是MyISAM、InnoDB

索引结构由存储引擎决定,而MySQL引擎层,属于可拔插式引擎。

MySQL中创建索引时,其默认的数据结构就为B+Tree,如何更换索引的数据结构呢?如下:

CREATE INDEX indexName ON tableName (columnName(length) [ASC|DESC]) USING HASH;

也就是在创建索引时,通过USING关键字显示指定索引的数据结构(必须要为当前引擎支持的结构)。

同时索引会被分为有序索引和无序索引,这是指索引文件中存储索引节点时,会不会按照字段值去排序。那一个索引到底是有序还是无序,就是依据数据结构决定的,例如B+Tree、R-Tree等树结构都是有序,而哈希结构则是无序的。

2.2、字段数量层次

前面从索引的数据结构层次出发,可以将索引分为不同结构的类型,而从表字段的层次来看,索引又可以分为单列索引和多列索引,单列索引是指索引是基于一个字段建立的,多列索引则是指由多个字段组合建立的索引。单列索引也会分为很多类型,比如:

  • 唯一索引:指索引中的索引节点值不允许重复,一般配合唯一约束使用。

  • 主键索引:主键索引是一种特殊的唯一索引,和普通唯一索引的区别在于不允许有空值。

  • 普通索引:通过KEY、INDEX关键字创建的索引就是这个类型,没啥限制,单纯的可以让查询快一点。

多列索引的概念前面解释过了,不过它也有很多种叫法,例如:

  • 组合索引、联合索引、复合索引、多值索引....

但不管名称咋变,描述的含义都是相同的,即由多个字段组合建立的索引。

不过在使用多列索引时要注意:当建立多列索引后,一条SELECT语句,只有当查询条件中了包含了多列索引的第一个字段时,才能使用多列索引。

比如在用户表中,通过id、name、age三个字段建立一个多列索引,什么情况下会使用索引,什么时候不会呢?如下:

-- 无法使用多列索引的SQL语句
SELECT * FROM `zz_user` WHERE name = "竹子" AND age = "18";

-- 能命中多列索引的SQL语句
SELECT * FROM `zz_user` WHERE name = "竹子" AND id = 6;

到这里就根据字段数量的层面出发,简单讲明了单列和多列索引的概念,但无论是单列还是多列,都可以存在一个前缀索引的概念,啥叫前缀索引呢?还记得创建索引时指定的length字段吗?

  • length:如果字段存储的值过长,选用值的前多少个字符创建索引。

使用一个字段值中的前N个字符创建出的索引,就可以被称为前缀索引,前缀索引能够在很大程度上,节省索引文件的存储空间,也能很大程度上提升索引的性能,这是为什么呢?后面分析索引实现原理的时候细聊。

2.3、功能逻辑层次

其实主要就是指MySQL索引从逻辑上可以分为那些类型,以功能逻辑划分索引类型,这也是最常见的划分方式,从这个维度来看主要可划分为五种:

  • 普通索引、唯一索引、主键索引、全文索引、空间索引

在主键字段上建立的索引被称为主键索引,非主键字段上建立的索引一般被称为辅助索引或、二级索引或次级索引,接着聊一下全文索引和空间索引。

全文索引和空间索引都是MySQL5.7版本后开始支持的索引类型,不过这两种索引都只有MyISAM引擎支持。对于全文索引而言,其实在MySQL5.6版本中就有了,但当时并不支持汉字检索,到了5.7.6版本的时候才内嵌ngram全文解析器,才支持亚洲语种的分词,同时InnoDB引擎也开始支持全文索引,在5.7版本之前,只有MyISAM引擎支持。

全文索引

全文索引类似于ES、Solr搜索中间件中的分词器,或者说和之前常用的like+%模糊查询很类似,它只能创建在CHAR、VARCHAR、TEXT等这些文本类型字段上,而且使用全文索引查询时,条件字符数量必须大于3才生效。

+------------+--------------------------------------------+------------------+
| article_id | article_name                               | special_column   |
+------------+--------------------------------------------+------------------+
|          1 | MySQL架构篇:自顶向下深入剖析MySQL整体架构 | 《全解MySQL》    |
|          2 | MySQL执行篇:一条SQL语句从诞生至结束的历程 | 《全解MySQL》    |
|          3 | MySQL设计篇:数据库六范式与反范式设计准则!| 《全解MySQL》    |
|          4 | MySQL索引篇:索引概述、分类及建立索引的原则| 《全解MySQL》    |
+------------+--------------------------------------------+------------------+

比如现在用户想要搜索一篇文章,但是忘记文章全称了,只记得「诞生至结束」这个词汇,此时用户搜索这个词汇,走全文索引的情况下,照样能够定位到上表中的第二条记录。

空间索引

空间索引这玩意儿其实用的不多,至少大部分项目的业务中不会用到,想要弄清楚空间索引,那么首先得知道一个概念:GIS空间数据,GIS是什么意思呢?是地理信息系统,这是一门新的学科,基于了计算机、信息学、地理学等多科构建的,主要就是用于管理地理信息的数据结构,在国土、规划、出行、配送、地图等和地理有关的项目中,应用较为频繁。

地理空间数据主要包含矢量数据、3D模型、影像文件、坐标数据等,说简单点,空间数据也就是可以将地理信息以模型的方式,在地图上标注出来。在MySQL中总共支持GEOMETRY、POINT、LINESTRING、POLYGON四种空间数据类型,而空间索引则是基于这些类型的字段建立的,也就是可以帮助我们快捷检索空间数据。

2.4、存储方式层次

上面聊完了三种不同层次的索引划分后,接着从存储方式的层面再聊聊,从存储方式来看,MySQL的索引主要可分为两大类:

  • 聚簇索引:也被称为聚集索引、簇类索引

  • 非聚簇索引:也叫非聚集索引、非簇类索引、二级索引、辅助索引、次级索引

重点说一说这两类索引存储方式的区别,在说之前先回忆一下数组和链表的区别:

  • 数组是物理空间上的连续,存储的所有元素都会按序存放在同一块内存区域中。

  • 链表是逻辑上的连续,存储的所有元素可能不在同一块内存,元素之间以指针连接。

为啥要说这个呢?因为聚簇索引和非聚簇索引的区别也大致是相同的:

  • 聚簇索引:逻辑上连续且物理空间上的连续。

  • 非聚簇索引:逻辑上的连续,物理空间上不连续。

这里的连续和数组不同,因为索引大部分都是使用B+Tree结构存储,所以在磁盘中数据是以树结构存放的,所以连续并不是指索引节点,而是指索引数据和表数据,也就是说聚簇索引中,索引数据和表数据在磁盘中的位置是一起的,而非聚簇索引则是分开的,索引节点和表数据之间,用物理地址的方式维护两者的联系。

不过一张表中只能存在一个聚簇索引,一般都会选用主键作为聚簇索引,其他字段上建立的索引都属于非聚簇索引,或者称之为辅助索引、次级索引。但也不要走进一个误区,虽然MySQL默认会使用主键上建立的索引作为聚簇索引,但也可以指定其他字段上的索引为聚簇索引,一般聚簇索引要求索引必须是非空唯一索引才行。

其实就算表中没有定义主键,InnoDB中会选择一个唯一的非空索引作为聚簇索引,但如果非空唯一索引也不存在,InnoDB隐式定义一个主键来作为聚簇索引。

当然,主键或者说聚簇索引,一般适合采用带有自增性的顺序值。

三、MySQL其他索引

3.1、唯一索引的创建与使用

唯一索引在创建时,需要通过UNIQUE关键字创建:如下:

-- 方式①
CREATE UNIQUE INDEX indexName ON tableName (columnName(length));

-- 方式②
ALTER TABLE tableName ADD UNIQUE INDEX indexName(columnName);

-- 方式③
CREATE TABLE tableName(  
  columnName1 INT(8) NOT NULL,   
  columnName2 ....,
  .....,
  UNIQUE INDEX [indexName] (columnName(length))  
);

在已有的表基础上创建唯一索引时要注意,如果选用的字段,表中字段的值存在相同值时,这时唯一索引是无法创建的,比如:

SELECT * FROM `zz_article`;
+------------+--------------------------+-------------------+
| article_id | article_name             | special_column    |
+------------+--------------------------+-------------------+
|          1 | MySQL架构篇:.......     | 《全解MySQL》     |
|          2 | MySQL执行篇:.......     | 《全解MySQL》     |
|          3 | MySQL设计篇:.......     | 《全解MySQL》     |
|          4 | MySQL索引篇:.......     | 《全解MySQL》     |
|          5 | MySQL索引篇:.......     | 《全解MySQL》     |
+------------+--------------------------+-------------------+

CREATE UNIQUE INDEX i_article_name ON zz_article (article_name);

比如上述文章表中,第4、5条数据是重复的,此时创建利用SQL语句创建唯一索引,就会抛出1062错误码:

ERROR 1062 (23000): Duplicate entry 'MySQL索引篇:.......' for key 'i_article_name'

在这种情况下,就只能先删除重复数据,然后才能创建唯一索引成功。

同时,当唯一索引创建成功后,它同时会对表具备唯一约束的作用,当再使用INSERT语句插入相同值时,会同样会抛出1062错误码:

INSERT INTO `zz_article` VALUES(6,"MySQL索引篇:.......","《全解MySQL》");

1062 - Duplicate entry 'MySQL索引篇:.......' for key 'i_article_name'

这里会提示你插入的哪个值,已经在表中存在,因此无法插入当前这条数据。

3.2、主键索引的创建与使用

前面聊到过,主键索引其实是一种特殊的唯一索引,但主键索引却并不是通过UNIQUE关键字创建的,而是通过PRIMARY关键字创建:

-- 方式①
ALTER TABLE tableName ADD PRIMARY KEY indexName(columnName);

-- 方式②
CREATE TABLE tableName(  
  columnName1 INT(8) NOT NULL,   
  columnName2 ....,
  .....,
  PRIMARY KEY [indexName] (columnName(length))  
);

在这里要注意:

  • 创建主键索引时,必须要将索引字段先设为主键,否则会抛1068错误码。

  • 这里也不能使用CREATE语句创建索引,否则会提示1064语法错误。

  • 同时创建索引时,关键字要换成KEY,并非INDEX,否则也会提示语法错误。

还是以之前的文章表为例,如下:

-- 对非主键字段创建主键索引
ALTER TABLE zz_article ADD PRIMARY KEY i_special_column(special_column);
-- 报错信息如下:
1068 - Multiple primary key defined

-- 使用CREATE关键字创建主键索引
CREATE PRIMARY KEY i_article_id ON zz_article (article_id);
-- 报错信息如下:
1064 - You have an error in your SQL syntax; check....

-- 使用INDEX关键字创建索引
ALTER TABLE zz_article ADD PRIMARY INDEX i_article_id(article_id);
-- 报错信息如下:
1064 - You have an error in your SQL syntax; check....

-- 创建主键索引正确的方式
ALTER TABLE zz_article ADD PRIMARY KEY i_article_id(article_id);

当然,一般主键索引都会在建表的DDL语句中创建,不会在表已经建立后再创建。

在一条SELECT语句来到MySQL时,会经历优化器优化的过程,而优化器则会自动帮咱们选择一个最合适的索引查询数据。当然,前提是查询条件中涉及到了索引字段才行。

前面也说过,你不想让优化器自动选择,也可以手动通过FORCE INDEX关键字强制指定。

3.3、全文索引的创建与使用

全文索引和其他索引不同,首先如果你想要创建全文索引,那么MySQL版本必须要在5.7及以上,同时使用时也需要手动指定,一起来先看看如何创建全文索引,此时需要使用FULLTEXT关键字:

-- 方式①
ALTER TABLE tableName ADD FULLTEXT INDEX indexName(columnName);

-- 方式②
CREATE FULLTEXT INDEX indexName ON tableName(columnName);

不过在创建全文索引时,有三个注意点:

  • 5.6版本的MySQL中,存储引擎必须为MyISAM才能创建。

  • 创建全文索引的字段,其类型必须要为CHAR、VARCHAR、TEXT等文本类型。

  • 如果想要创建出的全文索引支持中文,需要在最后指定解析器:with parser ngram

此时还依旧是以文章表为例,为文章名称字段创建一个全文索引,命令如下:

ALTER TABLE 
    zz_article ADD 
FULLTEXT INDEX 
    ft_article_name(article_name) 
WITH PARSER NGRAM;

创建好全文索引后,当你想要使用全文索引时,优化器这时不能自动选择,因为全文索引有自己的语法,但在了解如何使用之前,得先清楚两个概念:最小搜索长度和最大搜索长度,先来看看全文索引的一些参数,可通过show variables like '%ft%';命令查询,如下:

图片

全文索引参数

多余的参数就不介绍了,重点讲一下其中的几个重要参数:

  • ft_min_word_len:使用MyISAM引擎的表中,全文索引最小搜索长度。

  • ft_max_word_len:使用MyISAM引擎的表中,全文索引最大搜索长度。

  • ft_query_expansion_limitMyISAM中使用with query expansion搜索的最大匹配数。

  • innodb_ft_min_token_sizeInnoDB引擎的表中,全文索引最小搜索长度。

  • innodb_ft_max_token_sizeInnoDB引擎的表中,全文索引最大搜索长度。

那么究竟做最小搜索长度、最大搜索长度的作用是什么呢?其实这个是一个限制,对于长度小于最小搜索长度和大于最大搜索长度的词语,都无法触发全文索引。也就是说,如果想要使用全文索引对一个词语进行搜索,那这个词语的长度必须在这两个值之间。

其实这两个值自己可以手动调整的,最小值可以手动调整为1MyISAM引擎的最大值可以调整为3600,但InnoDB引擎最大似乎就是84

了解全文索引中的一些概念后,接下来看看如何使用全文索引,全文索引中有两个专门用于检索的关键字,即MATCH(column)、AGAINST(关键字),同时这两个检索函数也支持三种搜索模式:

  • 自然语言模式(默认搜索模式)

  • 布尔搜索模式

  • 查询拓展搜索

MATCH()主要是负责指定要搜索的列,这里要指定创建全文索引的字段,AGAINST()则指定要搜索的关键字,也就是要搜索的词语,接下来简单的讲一下三种搜索模式。

自然语言模式

这种模式也是在使用全文索引时,默认的搜索模式,使用方法如下:

+------------+--------------------------+-------------------+
| article_id | article_name             | special_column    |
+------------+--------------------------+-------------------+
|          1 | MySQL架构篇:.......     | 《全解MySQL》     |
|          2 | MySQL执行篇:.......     | 《全解MySQL》     |
|          3 | MySQL设计篇:.......     | 《全解MySQL》     |
|          4 | MySQL索引篇:.......     | 《全解MySQL》     |
+------------+--------------------------+-------------------+

SELECT 
    COUNT(article_id) AS '搜索结果数量' 
FROM 
    `zz_article` 
WHERE 
    MATCH(article_name) AGAINST('MySQL');

-- 运行结果如下:
+--------------+
| 搜索结果数量 |
+--------------+
|           4 |
+--------------+

一眼看过去,SQL就能看懂,毕竟都可以排版了一下SQL,不过多介绍了。唯一要注意的是,如果给定的关键词长度小于默认的最小搜索长度,那是无法使用全文索引的,比如下述这条SQL就不会触发:

SELECT 
    COUNT(article_id) AS '搜索结果数量' 
FROM 
    `zz_article` 
WHERE 
    MATCH(article_name) AGAINST('M');
布尔搜索模式

布尔搜索模式有些特殊,因为在这种搜索模式中,还需要掌握特定的搜索语法:

  • +:表示必须匹配的行数据必须要包含相应关键字。

  • -:和上面的+相反,表示匹配的数据不能包含相应的关键字。

  • >:提升指定关键字的相关性,在查询结果中靠前显示。

  • <:降低指定关键字的相关性,在查询结果中靠后显示。

  • ~:表示允许出现指定关键字,但出现时相关性为负。

  • *:表示以该关键字开头的词语,如A*,可以匹配A、AB、ABC....

  • "":双引号中的关键字作为整体,检索时不允许再分词。

  • "X Y"@n""包含的多个词语之间的距离必须要在n之间,单位-字节,如:

    • 竹子 熊猫@10:表示竹子和熊猫两个词语之间的距离要在10字节内。

举个几个例子使用一下,如下:

-- 查询文章名中包含 [MySQL] 但不包含 [设计] 的数据
SELECT 
    *
FROM 
    `zz_article` 
WHERE 
    MATCH(article_name) AGAINST('+MySQL -设计' IN BOOLEAN MODE);

-- 查询文章名中包含 [MySQL] 和 [篇] 的数据,但两者间的距离不能超过10字节
SELECT 
    *
FROM 
    `zz_article` 
WHERE 
    MATCH(article_name) AGAINST('"MySQL 篇"@10' IN BOOLEAN MODE);
    
-- 查询文章名中包含[MySQL] 的数据,
--    但包含 [执行] 关键字的行相关性要高于包含 [索引] 关键字的行数据
SELECT 
    *
FROM 
    `zz_article` 
WHERE 
    MATCH(article_name) AGAINST('+MySQL +(>执行 <索引)' IN BOOLEAN MODE);

-- 查询文章名中包含 [MySQL] 的数据,但包含 [设计] 时则将相关性降为负
SELECT 
    *
FROM 
    `zz_article` 
WHERE 
    MATCH(article_name) AGAINST('+MySQL ~设计' IN BOOLEAN MODE);

-- 查询文章名中包含 [执行] 关键字的行数据
SELECT 
    *
FROM 
    `zz_article` 
WHERE 
    MATCH(article_name) AGAINST('执行*' IN BOOLEAN MODE);

-- 查询文章名中必须要包含 [MySQL架构篇] 关键字的数据
SELECT 
    *
FROM 
    `zz_article` 
WHERE 
    MATCH(article_name) AGAINST('"MySQL架构篇"' IN BOOLEAN MODE);

同样的,上述的SQL语句应该都能看明白,最后的IN BOOLEAN MODE表示使用布尔搜索模式,除此外,大家唯一疑惑的就在于:相关性这个词,其实这个词也不难理解,就是检索数据后,数据的优先级顺序,当相关性越高,对应数据在结果中越靠前,当相关性为负,则相应的数据排到最后。

查询拓展搜索

查询拓展搜索其实是对自然语言搜索模式的拓展,比如举个例子:

SELECT 
    COUNT(article_id) AS '搜索结果数量' 
FROM 
    `zz_article` 
WHERE 
    MATCH(article_name) AGAINST('MySQL' WITH QUERY EXPANSION);

在自然语言模式的查询语句基础上,最后面多加一个WITH QUERY EXPANSION表示使用查询拓展搜索,这种模式下会比自然语言模式多一次检索过程,比如上述的例子中:

  • 首先会根据指定的关键字MySQL进行一次全文检索。

  • 然后第二阶段还会对指定的关键进行分词,然后再进行一次全文检索。

之前介绍全文索引参数时,也列出来了一个名为ft_query_expansion_limit的参数,这个参数就是控制拓展搜索时的拓展行数的,最大可以调整到1000。但由于Query Expansion的全文检索可能带来许多非相关性的查询结果,因此在实际情况中要慎用。

实际上,全文索引引入MySQL后,可以用它代替之前的like%模糊查询,效率会更高。

3.4、空间索引的创建与使用

空间索引这玩意儿实际上很多项目不会用到,但如果你要用到这个索引,那可以通过SPATIAL关键字创建,如下:

ALTER TABLE tableName ADD SPATIAL KEY indexName(columnName);

但在创建空间索引的时候,有几个注意点需要牢记:

  • 目前MySQL常用引擎中,仅有MyISAM支持空间索引,所以表引擎必须要为它。

  • 空间索引必须要建立在类型为GEOMETRY、POINT、LINESTRING、POLYGON的字段上。

3.5、联合索引的创建与使用

联合索引呢,实际上并不是一种逻辑索引分类,它是索引的一种特殊结构,前面给出的所有案例中,都仅仅是在单个字段的基础上建立索引,而联合索引的意思是可以使用多个字段建立索引。那该如何创建联合索引呢,不需要特殊的关键字,方法如下:

CREATE INDEX indexName ON tableName (column1(length),column2...);
ALTER TABLE tableName ADD INDEX indexName(column1(length),column2...);
  • 可以使用INDEX关键字,让多个列组成一个普通联合索引

  • 也可以使用UNIQUE INDEX关键字,让多个列组成一个唯一联合索引

  • 甚至还可以使用FULLTEXT INDEX关键字,让多个列组成一个全文联合索引

但是前面也提过,SELECT语句的查询条件中,必须包含组成联合索引的第一个字段,此时才会触发联合索引,否则是无法使用联合索引的。


原文地址:https://blog.csdn.net/lichunericli/article/details/140417286

免责声明:本站文章内容转载自网络资源,如本站内容侵犯了原著者的合法权益,可联系本站删除。更多内容请关注自学内容网(zxcms.com)!