记录些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：正在实行预编译的SQL（PreparedStatement）。
    
  • 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_created：MySQL-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目前有非常多的存储引擎可选择，其中最为常用的则是InnoDB与MyISAM引擎，可以通过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/.ini：MySQL的设置文件，Windows下是.ini，其他系统大多为.cnf。
  

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

 由于MySQL是作为存储层部署在业务系统的最后端，所有的业务数据终极都要入库落盘，但随着一个项目在线上运行的时间越来越久，数据库中的数据量天然会越来越多，而数据体积出现增长后，当必要从表查询一些数据时，效率会越发低下。在正常环境下，表的查询性能和数据量是成反比的，也就是数据越多，查询越慢。
   这是什么原因导致的呢？由于MySQL默认的查询方式导致的。

1. SELECT * FROM `zz_student`;
2. +------------+--------+------+--------+
3. | student_id | name   | sex  | height |
4. +------------+--------+------+--------+
5. |          1 | 竹子   | 男   | 185cm  |
6. |          2 | 熊猫   | 女   | 170cm  |
7. |          3 | 子竹   | 男   | 182cm  |
8. |          4 | 棕熊   | 男   | 187cm  |
9. |          5 | 黑豹   | 男   | 177cm  |
10. |          6 | 脑斧   | 男   | 178cm  |
11. |          7 | 兔纸   | 女   | 165cm  |
12. +------------+--------+------+--------+
14. 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语句创建
  

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

复制代码

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

• indexName：当前创建的索引，创建乐成后叫啥名字。
  
• tableName：要在哪张表上创建一个索引，这里指定表名。
  
• columnName：要为表中的哪个字段创建索引，这里指定字段名。
  
• length：假如字段存储的值过长，选用值的前多少个字符创建索引。
  
• ASC|DESC：指定索引的排序方式，ASC是升序，DESC是降序，默认ASC。
  

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

• ②利用ALTER语句创建
  

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

复制代码

这里的参数都类似，所以不再重复赘述。

• ③建表时DDL语句中创建
  

1. CREATE TABLE tableName(  
2.   columnName1 INT(8) NOT NULL,   
3.   columnName2 ....,
4.   .....,
5.   INDEX [indexName] (columnName(length))  
6. );

复制代码

这种方式就比较得当在库表筹划时，已经确定了索引项的环境下建立。
1.2、查询、删除、指定索引

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

1. CREATE TABLE `zz_user`  (
2.   `user_id` int(8) NOT NULL AUTO_INCREMENT,
3.   `user_name` varchar(255) NULL DEFAULT "",
4.   `user_sex` varchar(255) NULL DEFAULT "",
5.   `user_phone` varchar(255) NULL DEFAULT "",
6.     PRIMARY KEY (`user_id`) USING BTREE
7. )
8.     ENGINE = InnoDB 
9.     CHARACTER SET = utf8 
10.     COLLATE = utf8_general_ci 
11.     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中并未提供修改索引的下令，也就说当你建错了索引，只能先删再重新建立一次，删除索引的语句如下：

1. DROP INDEX indexName ON tableName;

复制代码

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

1. 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，如何更换索引的数据结构呢？如下：

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

复制代码

也就是在创建索引时，通过USING关键字显示指定索引的数据结构（必须要为当前引擎支持的结构）。
同时索引会被分为有序索引和无序索引，这是指索引文件中存储索引节点时，会不会按照字段值去排序。那一个索引到底是有序还是无序，就是依据数据结构决定的，例如B+Tree、R-Tree等树结构都是有序，而哈希结构则是无序的。
2.2、字段数量条理

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

• 唯一索引：指索引中的索引节点值不允许重复，一般配合唯一约束利用。
  
• 主键索引：主键索引是一种特殊的唯一索引，和平凡唯一索引的区别在于不允许有空值。
  
• 平凡索引：通过KEY、INDEX关键字创建的索引就是这个类型，没啥限定，单纯的可以让查询快一点。
  

多列索引的概念前面解释过了，不过它也有许多种叫法，例如：

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

但不管名称咋变，描述的寄义都是类似的，即由多个字段组合建立的索引。
   不过在利用多列索引时要留意：当建立多列索引后，一条SELECT语句，只有当查询条件中了包罗了多列索引的第一个字段时，才气利用多列索引。
  好比在用户表中，通过id、name、age三个字段建立一个多列索引，什么环境下会利用索引，什么时候不会呢？如下：

1. -- 无法使用多列索引的SQL语句
2. SELECT * FROM `zz_user` WHERE name = "竹子" AND age = "18";
4. -- 能命中多列索引的SQL语句
5. 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才生效。

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

复制代码

好比现在用户想要搜刮一篇文章，但是忘记文章全称了，只记得「诞生至结束」这个词汇，此时用户搜刮这个词汇，走全文索引的环境下，还是可以或许定位到上表中的第二条记录。
空间索引

空间索引这玩意儿其实用的不多，至少大部分项目标业务中不会用到，想要弄清晰空间索引，那么首先得知道一个概念：GIS空间数据，GIS是什么意思呢？是地理信息系统，这是一门新的学科，基于了盘算机、信息学、地理学等多科构建的，重要就是用于管理地理信息的数据结构，在国土、规划、出行、配送、地图等和地理有关的项目中，应用较为频繁。
地理空间数据重要包罗矢量数据、3D模子、影像文件、坐标数据等，说简单点，空间数据也就是可以将地理信息以模子的方式，在地图上标注出来。在MySQL中统共支持GEOMETRY、POINT、LINESTRING、POLYGON四种空间数据类型，而空间索引则是基于这些类型的字段建立的，也就是可以资助我们快捷检索空间数据。
2.4、存储方式条理

上面聊完了三种不同条理的索引分别后，接着从存储方式的层面再聊聊，从存储方式来看，MySQL的索引重要可分为两大类：

• 聚簇索引：也被称为聚集索引、簇类索引
  
• 非聚簇索引：也叫非聚集索引、非簇类索引、二级索引、辅助索引、次级索引
  

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

• 数组是物理空间上的连续，存储的所有元素都会按序存放在同一块内存区域中。
  
• 链表是逻辑上的连续，存储的所有元素可能不在同一块内存，元素之间以指针毗连。
  

为啥要说这个呢？由于聚簇索引和非聚簇索引的区别也大抵是类似的：

• 聚簇索引：逻辑上连续且物理空间上的连续。
  
• 非聚簇索引：逻辑上的连续，物理空间上不连续。
  

这里的连续和数组不同，由于索引大部分都是利用B+Tree结构存储，所以在磁盘中数据是以树结构存放的，所以连续并不是指索引节点，而是指索引数据和表数据，也就是说聚簇索引中，索引数据和表数据在磁盘中的位置是一起的，而非聚簇索引则是分开的，索引节点和表数据之间，用物理地址的方式维护两者的接洽。
不过一张表中只能存在一个聚簇索引，一般都会选用主键作为聚簇索引，其他字段上建立的索引都属于非聚簇索引，或者称之为辅助索引、次级索引。但也不要走进一个误区，虽然MySQL默认会利用主键上建立的索引作为聚簇索引，但也可以指定其他字段上的索引为聚簇索引，一般聚簇索引要求索引必须黑白空唯一索引才行。
   其实就算表中没有定义主键，InnoDB中会选择一个唯一的非空索引作为聚簇索引，但假如非空唯一索引也不存在，InnoDB隐式定义一个主键来作为聚簇索引。
  当然，主键或者说聚簇索引，一般得当接纳带有自增性的顺序值。
三、MySQL其他索引

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

唯一索引在创建时，必要通过UNIQUE关键字创建：如下：

1. -- 方式①
2. CREATE UNIQUE INDEX indexName ON tableName (columnName(length));
4. -- 方式②
5. ALTER TABLE tableName ADD UNIQUE INDEX indexName(columnName);
7. -- 方式③
8. CREATE TABLE tableName(  
9.   columnName1 INT(8) NOT NULL,   
10.   columnName2 ....,
11.   .....,
12.   UNIQUE INDEX [indexName] (columnName(length))  
13. );

复制代码

在已有的表基础上创建唯一索引时要留意，假如选用的字段，表中字段的值存在类似值时，这时唯一索引是无法创建的，好比：

1. SELECT * FROM `zz_article`;
2. +------------+--------------------------+-------------------+
3. | article_id | article_name             | special_column    |
4. +------------+--------------------------+-------------------+
5. |          1 | MySQL架构篇：.......     | 《全解MySQL》     |
6. |          2 | MySQL执行篇：.......     | 《全解MySQL》     |
7. |          3 | MySQL设计篇：.......     | 《全解MySQL》     |
8. |          4 | MySQL索引篇：.......     | 《全解MySQL》     |
9. |          5 | MySQL索引篇：.......     | 《全解MySQL》     |
10. +------------+--------------------------+-------------------+
12. CREATE UNIQUE INDEX i_article_name ON zz_article (article_name);

复制代码

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

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

复制代码

  在这种环境下，就只能先删除重复数据，然后才气创建唯一索引乐成。
  同时，当唯一索引创建乐成后，它同时会对表具备唯一约束的作用，当再利用INSERT语句插入类似值时，会同样会抛出1062错误码：

1. INSERT INTO `zz_article` VALUES(6,"MySQL索引篇：.......","《全解MySQL》");
3. 1062 - Duplicate entry 'MySQL索引篇：.......' for key 'i_article_name'

复制代码

这里会提示你插入的哪个值，已经在表中存在，因此无法插入当前这条数据。
3.2、主键索引的创建与利用

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

1. -- 方式①
2. ALTER TABLE tableName ADD PRIMARY KEY indexName(columnName);
4. -- 方式②
5. CREATE TABLE tableName(  
6.   columnName1 INT(8) NOT NULL,   
7.   columnName2 ....,
8.   .....,
9.   PRIMARY KEY [indexName] (columnName(length))  
10. );

复制代码

在这里要留意：

• 创建主键索引时，必须要将索引字段先设为主键，否则会抛1068错误码。
  
• 这里也不能利用CREATE语句创建索引，否则会提示1064语法错误。
  
• 同时创建索引时，关键字要换成KEY，并非INDEX，否则也会提示语法错误。
  

还是以之前的文章表为例，如下：

1. -- 对非主键字段创建主键索引
2. ALTER TABLE zz_article ADD PRIMARY KEY i_special_column(special_column);
3. -- 报错信息如下：
4. 1068 - Multiple primary key defined
6. -- 使用CREATE关键字创建主键索引
7. CREATE PRIMARY KEY i_article_id ON zz_article (article_id);
8. -- 报错信息如下：
9. 1064 - You have an error in your SQL syntax; check....
11. -- 使用INDEX关键字创建索引
12. ALTER TABLE zz_article ADD PRIMARY INDEX i_article_id(article_id);
13. -- 报错信息如下：
14. 1064 - You have an error in your SQL syntax; check....
16. -- 创建主键索引正确的方式
17. ALTER TABLE zz_article ADD PRIMARY KEY i_article_id(article_id);

复制代码

当然，一般主键索引都会在建表的DDL语句中创建，不会在表已经建立后再创建。
在一条SELECT语句来到MySQL时，会经历优化器优化的过程，而优化器则会自动帮咱们选择一个最合适的索引查询数据。当然，前提是查询条件中涉及到了索引字段才行。
   前面也说过，你不想让优化器自动选择，也可以手动通过FORCE INDEX关键字强制指定。
  3.3、全文索引的创建与利用

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

1. -- 方式①
2. ALTER TABLE tableName ADD FULLTEXT INDEX indexName(columnName);
4. -- 方式②
5. CREATE FULLTEXT INDEX indexName ON tableName(columnName);

复制代码

不过在创建全文索引时，有三个留意点：

• 5.6版本的MySQL中，存储引擎必须为MyISAM才气创建。
  
• 创建全文索引的字段，其类型必须要为CHAR、VARCHAR、TEXT等文本类型。
  
• 假如想要创建出的全文索引支持中文，必要在最后指定解析器：with parser ngram。
  

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

1. ALTER TABLE 
2.     zz_article ADD 
3. FULLTEXT INDEX 
4.     ft_article_name(article_name) 
5. WITH PARSER NGRAM;

复制代码

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

全文索引参数
多余的参数就不先容了，重点讲一下其中的几个重要参数：

• ft_min_word_len：利用MyISAM引擎的表中，全文索引最小搜刮长度。
  
• ft_max_word_len：利用MyISAM引擎的表中，全文索引最大搜刮长度。
  
• ft_query_expansion_limit：MyISAM中利用with query expansion搜刮的最大匹配数。
  
• innodb_ft_min_token_size：InnoDB引擎的表中，全文索引最小搜刮长度。
  
• innodb_ft_max_token_size：InnoDB引擎的表中，全文索引最大搜刮长度。
  

那么究竟做最小搜刮长度、最大搜刮长度的作用是什么呢？其实这个是一个限定，对于长度小于最小搜刮长度和大于最大搜刮长度的词语，都无法触发全文索引。也就是说，假如想要利用全文索引对一个词语进行搜刮，那这个词语的长度必须在这两个值之间。
   其实这两个值本身可以手动调整的，最小值可以手动调整为1，MyISAM引擎的最大值可以调整为3600，但InnoDB引擎最大似乎就是84。
  了解全文索引中的一些概念后，接下来看看如何利用全文索引，全文索引中有两个专门用于检索的关键字，即MATCH(column)、AGAINST(关键字)，同时这两个检索函数也支持三种搜刮模式：

• 天然语言模式(默认搜刮模式)
  
• 布尔搜刮模式
  
• 查询拓展搜刮
  

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

这种模式也是在利用全文索引时，默认的搜刮模式，利用方法如下：

1. +------------+--------------------------+-------------------+
2. | article_id | article_name             | special_column    |
3. +------------+--------------------------+-------------------+
4. |          1 | MySQL架构篇：.......     | 《全解MySQL》     |
5. |          2 | MySQL执行篇：.......     | 《全解MySQL》     |
6. |          3 | MySQL设计篇：.......     | 《全解MySQL》     |
7. |          4 | MySQL索引篇：.......     | 《全解MySQL》     |
8. +------------+--------------------------+-------------------+
10. SELECT 
11.     COUNT(article_id) AS '搜索结果数量' 
12. FROM 
13.     `zz_article` 
14. WHERE 
15.     MATCH(article_name) AGAINST('MySQL');
17. -- 运行结果如下：
18. +--------------+
19. | 搜索结果数量 |
20. +--------------+
21. |           4 |
22. +--------------+

复制代码

一眼看过去，SQL就能看懂，究竟都可以排版了一下SQL，不过多先容了。唯一要留意的是，假如给定的关键词长度小于默认的最小搜刮长度，那是无法利用全文索引的，好比下述这条SQL就不会触发：

1. SELECT 
2.     COUNT(article_id) AS '搜索结果数量' 
3. FROM 
4.     `zz_article` 
5. WHERE 
6.     MATCH(article_name) AGAINST('M');

复制代码

布尔搜刮模式

布尔搜刮模式有些特殊，由于在这种搜刮模式中，还必要把握特定的搜刮语法：

• +：表示必须匹配的行数据必须要包罗相应关键字。
  
• -：和上面的+相反，表示匹配的数据不能包罗相应的关键字。
  
• >：提拔指定关键字的相关性，在查询效果中靠前显示。
  
• <：降低指定关键字的相关性，在查询效果中靠后显示。
  
• ~：表示允许出现指定关键字，但出现时相关性为负。
  
• *：表示以该关键字开头的词语，如A*，可以匹配A、AB、ABC....
  
• ""：双引号中的关键字作为团体，检索时不允许再分词。
  
• "X Y"@n：""包罗的多个词语之间的距离必须要在n之间，单位-字节，如：
  
  
  
  • 竹子 熊猫@10：表示竹子和熊猫两个词语之间的距离要在10字节内。
    
  
  

举个几个例子利用一下，如下：

1. -- 查询文章名中包含 [MySQL] 但不包含 [设计] 的数据
2. SELECT 
3.     *
4. FROM 
5.     `zz_article` 
6. WHERE 
7.     MATCH(article_name) AGAINST('+MySQL -设计' IN BOOLEAN MODE);
9. -- 查询文章名中包含 [MySQL] 和 [篇] 的数据，但两者间的距离不能超过10字节
10. SELECT 
11.     *
12. FROM 
13.     `zz_article` 
14. WHERE 
15.     MATCH(article_name) AGAINST('"MySQL 篇"@10' IN BOOLEAN MODE);
16.     
17. -- 查询文章名中包含[MySQL] 的数据，
18. --    但包含 [执行] 关键字的行相关性要高于包含 [索引] 关键字的行数据
19. SELECT 
20.     *
21. FROM 
22.     `zz_article` 
23. WHERE 
24.     MATCH(article_name) AGAINST('+MySQL +(>执行 <索引)' IN BOOLEAN MODE);
26. -- 查询文章名中包含 [MySQL] 的数据，但包含 [设计] 时则将相关性降为负
27. SELECT 
28.     *
29. FROM 
30.     `zz_article` 
31. WHERE 
32.     MATCH(article_name) AGAINST('+MySQL ~设计' IN BOOLEAN MODE);
34. -- 查询文章名中包含 [执行] 关键字的行数据
35. SELECT 
36.     *
37. FROM 
38.     `zz_article` 
39. WHERE 
40.     MATCH(article_name) AGAINST('执行*' IN BOOLEAN MODE);
42. -- 查询文章名中必须要包含 [MySQL架构篇] 关键字的数据
43. SELECT 
44.     *
45. FROM 
46.     `zz_article` 
47. WHERE 
48.     MATCH(article_name) AGAINST('"MySQL架构篇"' IN BOOLEAN MODE);

复制代码

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

查询拓展搜刮其实是对天然语言搜刮模式的拓展，好比举个例子：

1. SELECT 
2.     COUNT(article_id) AS '搜索结果数量' 
3. FROM 
4.     `zz_article` 
5. WHERE 
6.     MATCH(article_name) AGAINST('MySQL' WITH QUERY EXPANSION);

复制代码

在天然语言模式的查询语句基础上，最后面多加一个WITH QUERY EXPANSION表示利用查询拓展搜刮，这种模式下会比天然语言模式多一次检索过程，好比上述的例子中：

• 首先会根据指定的关键字MySQL进行一次全文检索。
  
• 然后第二阶段还会对指定的关键进行分词，然后再进行一次全文检索。
  

之前先容全文索引参数时，也列出来了一个名为ft_query_expansion_limit的参数，这个参数就是控制拓展搜刮时的拓展行数的，最大可以调整到1000。但由于Query Expansion的全文检索可能带来许多非相关性的查询效果，因此在现实环境中要慎用。
   现实上，全文索引引入MySQL后，可以用它代替之前的like%模糊查询，效率会更高。
  3.4、空间索引的创建与利用

空间索引这玩意儿现实上许多项目不会用到，但假如你要用到这个索引，那可以通过SPATIAL关键字创建，如下：

1. ALTER TABLE tableName ADD SPATIAL KEY indexName(columnName);

复制代码

但在创建空间索引的时候，有几个留意点必要牢记：

• 目前MySQL常用引擎中，仅有MyISAM支持空间索引，所以表引擎必须要为它。
  
• 空间索引必须要建立在类型为GEOMETRY、POINT、LINESTRING、POLYGON的字段上。
  

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

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

1. CREATE INDEX indexName ON tableName (column1(length),column2...);
2. ALTER TABLE tableName ADD INDEX indexName(column1(length),column2...);

复制代码

• 可以利用INDEX关键字，让多个列组成一个平凡联合索引
  
• 也可以利用UNIQUE INDEX关键字，让多个列组成一个唯一联合索引
  
• 乃至还可以利用FULLTEXT INDEX关键字，让多个列组成一个全文联合索引
  

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

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。