MySQL存储引擎：

存储引擎是服务于存储服务，通过存储引擎将数据进行保存.就跟计算机如何将数据保存到磁盘中一样，在数据库中，存储引擎的意思就是通过何种引擎将数据存储在磁盘中。

InnoDB 引擎：具备外键支持功能的事务存储引擎

MySQL从3.23.34a开始就包含InnoDB存储引擎。大于等于5.5之后，默认采用InnoDB引擎 。

InnoDB是默认的事务型引擎.用来处理大量的短期(short-lived)事务。可以确保事务的完整提交(Commit)和回滚(Rollback)。

除了增加和查询外，还需要更新、删除操作，那么，应优先选择InnoDB存储引擎

数据文件结构：（在《第02章_MySQL数据目录》章节已讲）

表名.frm 存储表结构（MySQL8.0时，合并在表名.ibd中）

表名.ibd 存储数据和索引

MyISAM只缓存索引,不缓存真实数据;InnoDB不仅缓存索引还要缓存正式数据.对内存要求较高,而且内存大小对性能有影响.

2.2 MyISAM 引擎：主要的非事务处理存储引擎

MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM 不支持事务、行级锁、外键 ，有一个毫无疑问的缺陷就是崩溃后无法安全恢复。

5.5之前默认的存储引擎

访问数据快,对事务的完整没有要求或者以或者以SELECT、INSERT为主的应用

针对数据统计有额外的常数存储。故而 count(*) 的查询效率很高

应用场景：只读应用或者以读为主的业务

数据文件结构：（在《第02章_MySQL数据目录》章节已讲）

表名.frm 存储表结构

表名.MYD 存储数据 (MYData)

表名.MYI 存储索引 (MYIndex)

表级锁：开销小，加锁块；不会出现死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低。

行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发性也最高。

页面锁：开销和加锁界于表锁和行锁之间，会出现死锁；锁定粒度界与表锁和行锁之间，并发一般。

1.InnoDB与MyISAM最大不同有两点：

1）.支持事务

2）.采用行级锁

(59条消息) MySQL数据库锁机制之MyISAM引擎表锁和InnoDB行锁详解_我爱默小兜的博客-CSDN博客

InnoDB索引和MyISAM索引的区别：

一是主索引的区别，InnoDB的数据文件本身就是索引文件。而MyISAM的索引和数据是分开的。

二是辅助索引的区别：InnoDB的辅助索引data域存储相应记录主键的值而不是地址。而MyISAM的辅助索引和主索引没有多大区别。

InnoDB表的优势

1、InnoDB表的优势 InnoDB存储引擎在实际应用中拥有诸多优势，比如操作便利、提高了数据库的性能、维护成本低等。如果由于硬件或软件的原因导致服务器崩溃，那么在重启服务器之后不需要进行额外的操作。InnoDB崩溃恢复功能自动将之前提交的内容定型，然后撤销没有提交的进程，重启之后继续从崩溃点开始执行。

InnoDB存储引擎在主内存中维护缓冲池，高频率使用的数据将在内存中直接被处理。这种缓存方式应用于多种信息，加速了处理进程。在专用服务器上，物理内存中高达80%的部分被应用于缓冲池。如果需要将数据插入不同的表中，可以设置外键加强数据的完整性。更新或者删除数据，关联数据将会被自动更新或删除。如果试图将数据插入从表，但在主表中没有对应的数据，插入的数据将被自动移除。如果磁盘或内存中的数据出现崩溃，在使用脏数据之前，校验和机制会发出警告。当每个表的主键都设置合理时，与这些列有关的操作会被自动优化。插入、更新和删除操作通过做改变缓冲自动机制进行优化。 InnoDB不仅支持当前读写，也会缓冲改变的数据到数据流磁盘。

InnoDB的性能优势不只存在于长时运行查询的大型表。在同一列多次被查询时，自适应哈希索引会提高查询的速度。使用InnoDB可以压缩表和相关的索引，可以在不影响性能和可用性的情况下创建或删除索引。对于大型文本和BLOB数据，使用动态行形式，这种存储布局更高效。通过查询 INFORMATION_SCHEMA库中的表可以监控存储引擎的内部工作。在同一个语句中，InnoDB表可以与其他存储引擎表混用。即使有些操作系统限制文件大小为2GB，InnoDB仍然可以处理。当处理大数据量时， InnoDB兼顾CPU，以达到最大性能。

2、InnoDB和ACID模型

ACID模型是一系列数据库设计规则，这些规则着重强调可靠性，而可靠性对于商业数据和任务关键型应

用非常重要。MySQL包含类似InnoDB存储引擎的组件，与ACID模型紧密相连，这样出现意外时，数据不

会崩溃，结果不会失真。如果依赖ACID模型，可以不使用一致性检查和崩溃恢复机制。如果拥有额外的

软件保护，极可靠的硬件或者应用可以容忍一小部分的数据丢失和不一致，可以将MySQL设置调整为只

依赖部分ACID特性，以达到更高的性能。下面讲解InnoDB存储引擎与ACID模型相同作用的四个方面。

1. 原子方面 ACID的原子方面主要涉及InnoDB事务，与MySQL相关的特性主要包括：

自动提交设置。
COMMIT语句。
ROLLBACK语句。
操作INFORMATION_SCHEMA库中的表数据。

2. 一致性方面 ACID模型的一致性主要涉及保护数据不崩溃的内部InnoDB处理过程，与MySQL相关的特性

主要包括：

InnoDB双写缓存。
InnoDB崩溃恢复。

3. 隔离方面 隔离是应用于事务的级别，与MySQL相关的特性主要包括：

自动提交设置。
SET ISOLATION LEVEL语句。
InnoDB锁的低级别信息。

4. 耐久性方面 ACID模型的耐久性主要涉及与硬件配置相互影响的MySQL软件特性。由于硬件复杂多样

化，耐久性方面没有具体的规则可循。与MySQL相关的特性有：

InnoDB双写缓存，通过innodb_doublewrite配置项配置。
配置项innodb_flush_log_at_trx_commit。
配置项sync_binlog。
配置项innodb_file_per_table。
存储设备的写入缓存。
存储设备的备用电池缓存。
运行MySQL的操作系统。
持续的电力供应。
备份策略。

对分布式或托管的应用，最主要的在于硬件设备的地点以及网络情况。3、InnoDB****架构

1. 缓冲池 缓冲池是主内存中的一部分空间，用来缓存已使用的表和索引数据。缓冲池使得经常被使用的

数据能够直接在内存中获得，从而提高速度。

2. 更改缓存 更改缓存是一个特殊的数据结构，当受影响的索引页不在缓存中时，更改缓存会缓存辅助索

引页的更改。索引页被其他读取操作时会加载到缓存池，缓存的更改内容就会被合并。不同于集群索

引，辅助索引并非独一无二的。当系统大部分闲置时，清除操作会定期运行，将更新的索引页刷入磁

盘。更新缓存合并期间，可能会大大降低查询的性能。在内存中，更新缓存占用一部分InnoDB缓冲池。

在磁盘中，更新缓存是系统表空间的一部分。更新缓存的数据类型由innodb_change_buffering配置项管

理。

3. 自适应哈希索引 自适应哈希索引将负载和足够的内存结合起来，使得InnoDB像内存数据库一样运行，

不需要降低事务上的性能或可靠性。这个特性通过innodb_adaptive_hash_index选项配置，或者通过–

skip-innodb_adaptive_hash_index命令行在服务启动时关闭。

4. 重做日志缓存 重做日志缓存存放要放入重做日志的数据。重做日志缓存大小通过

innodb_log_buffer_size配置项配置。重做日志缓存会定期地将日志文件刷入磁盘。大型的重做日志缓存

使得大型事务能够正常运行而不需要写入磁盘。

5. 系统表空间 系统表空间包括InnoDB数据字典、双写缓存、更新缓存和撤销日志，同时也包括表和索引

数据。多表共享，系统表空间被视为共享表空间。

6. 双写缓存 双写缓存位于系统表空间中，用于写入从缓存池刷新的数据页。只有在刷新并写入双写缓存

后，InnoDB才会将数据页写入合适的位置。

7. 撤销日志 撤销日志是一系列与事务相关的撤销记录的集合，包含如何撤销事务最近的更改。如果其他

事务要查询原始数据，可以从撤销日志记录中追溯未更改的数据。撤销日志存在于撤销日志片段中，这

些片段包含于回滚片段中。

8. 每个表一个文件的表空间 每个表一个文件的表空间是指每个单独的表空间创建在自身的数据文件中，

而不是系统表空间中。这个功能通过innodb_file_per_table配置项开启。每个表空间由一个单独的.ibd数

据文件代表，该文件默认被创建在数据库目录中。

9. 通用表空间 使用CREATE TABLESPACE语法创建共享的InnoDB表空间。通用表空间可以创建在MySQL数

据目录之外能够管理多个表并支持所有行格式的表。

10. 撤销表空间 撤销表空间由一个或多个包含撤销日志的文件组成。撤销表空间的数量由

innodb_undo_tablespaces配置项配置。

11. 临时表空间 用户创建的临时表空间和基于磁盘的内部临时表都创建于临时表空间。

innodb_temp_data_file_path配置项定义了相关的路径、名称、大小和属性。如果该值为空，默认会在

innodb_data_home_dir变量指定的目录下创建一个自动扩展的数据文件。

12. 重做日志 重做日志是基于磁盘的数据结构，在崩溃恢复期间使用，用来纠正数据。正常操作期间，

重做日志会将请求数据进行编码，这些请求会改变InnoDB表数据。遇到意外崩溃后，未完成的更改会自

动在初始化期间重新进行。

MySQL服务器逻辑架构：

SQL执行过程

![image-20220403110030497](http://qiniu.littlekim.top/image/image-20220403110030497.png

连接器

连接器的主要职责就是：

1、负责与客户端的通信，是半双工模式，这就意味着某一固定时刻只能由客户端向服务器请求或者服务器向客户端发送数据，而不能同时进行，其中MySQL在与客户端连接TC/IP的。

2、验证请求用户的账户和密码是否正确，如果账户和密码错误，会报错：Access denied for user ‘root‘@’localhost’ (using password: YES)

3、如果用户的账户和密码验证通过，会在MySQL自带的权限表中查询当前用户的权限。

MySQL中存在4个控制权限的表，分别为user表，db表，tables_priv表，columns_priv表：

user表：存放用户账户信息以及全局级别（所有数据库）权限，决定了来自哪些主机的哪些用户可以访问数据库实例

db表：存放数据库级别的权限，决定了来自哪些主机的哪些用户可以访问此数据库

tables_priv表：存放表级别的权限，决定了来自哪些主机的哪些用户可以访问数据库的这个表

columns_priv表：存放列级别的权限，决定了来自哪些主机的哪些用户可以访问数据库表的这个字段

MySQL权限表的验证过程为：

1、先从user表中的Host，User，Password这3个字段中判断连接的IP、用户名、密码是否存在，存在则通过验证。

2、通过身份认证后，进行权限分配，按照user，db，tables_priv，columns_priv的顺序进行验证。即先检查全局权限表user，如果user中对应的权限为Y，则此用户对所有数据库的权限都为Y，将不再检查db，tables_priv，columns_priv；如果为N，则到db表中检查此用户对应的具体数据库，并得到db中为Y的权限；如果db中为N，则检查tables_priv中此数据库对应的具体表，取得表中的权限Y，以此类推。

3、如果在任何一个过程中权限验证不通过，都会报错。

缓存

MySQL的缓存主要的作用是为了提升查询的效率，缓存以key和value的哈希表形式存储，key是具体的SQL语句，value是结果的集合。如果无法命中缓存，就继续走到分析器的这一步，如果命中缓存就直接返回给客户端。不过需要注意的是在MySQL的8.0版本以后，缓存被官方删除掉了。之所以删除掉，是因为查询缓存的失效非常频繁，如果在一个写多读少的环境中，缓存会频繁的新增和失效。对于某些更新压力大的数据库来说，查询缓存的命中率会非常低，MySQL为了维护缓存可能会出现一定的伸缩性的问题，目前在5.6的版本中已经默认关闭了，比较推荐的一种做法是将缓存放在客户端，性能大概会提升5倍左右。

MySQL8.0为什么取消查询缓存：

1、只需要对一个表的更新，那么这个表上所有的查询都会被清空

2、两个查询请求语句在任意一个字符的不同，都会导致缓存不会命中，MySQL的查询缓存命中率不高

分析器

（MySQL 需要知道你要做什么，因此需要对 SQL 语句做解析。）

分析器的主要职责是将客户端发过来的SQL语句进行分析，包括预处理和解析过程，在这个过程中会对SQL语句进行语义分析，并进行关键字和非关键字进行提取、解析，并组成一棵分析树。（主要解析语句是否合格）

词法分析，分析出里面的字符串代表什么？比如select关键字，会判断表是否存在、列是否存在等

语法分析，根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法

具体的关键词包括不限定于以下：select/update/delete/or/in/where/group by/having/count/limit等。如果分析到语法错误，会直接给客户端抛出异常：“ERROR:You have an error in your SQL syntax.”。

比如：select * from user where userId =1234;

在分析器中就通过语义规则器将select from where这些关键词提取和匹配出来，MySQL会自动判断关键词和非关键词，将用户的匹配字段和自定义语句识别出来。这个阶段也会做一些校验：比如校验当前数据库是否存在user表，同时假如user表中不存在userId这个字段同样会报错：“unknown column in field list.”

优化器

通过分析器，MySQL知道我们要做什么了。在开始执行之前，还要先经过优化器的处理。

优化器中会确定SQL语句的执行路径，找到其中最好的执行计划，比如是根据全表检索，还是根据索引检索等

优化器的作用就是决定选择哪一种执行方案。

优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段。

表T，对A、B、C列建立联合索引，在进行查询的时候，当SQL查询到的结果是：select xx where B=x and A=x and C=x，很多人会以为是用不到索引的，但其实会用到，虽然索引必须符合最左原则才能使用，但是本质上，优化器会自动将这条SQL优化为：where A=x and B=x and C=X，这种优化会为了底层能够匹配到索引，同时在这个阶段是自动按照执行计划进行预处理，MySQL会计算各个执行方法的最佳时间，最终确定一条执行的SQL交给最后的执行器。

执行器

MySQL 通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。

在执行器的阶段，此时会调用存储引擎的API，API会调用存储引擎，主要有一下存储的引擎，不过常用的还是myisam和innodb：

引擎以前的名字叫做：表处理器（其实这个名字我觉得更能表达它存在的意义）负责对具体的数据文件进行操作，对SQL的语义比如select或者update进行分析，执行具体的操作。在执行完以后会将具体的操作记录到binlog中，需要注意的一点是：select不会记录到binlog中，只有update/delete/insert才会记录到binlog中。而update会采用两阶段提交的方式，记录都redolog中。

执行的状态

可以通过命令：show full processlist，展示所有的处理进程，主要包含了以下的状态，表示服务器处理客户端的状态，状态包含了从客户端发起请求到后台服务器处理的过程，包括加锁的过程、统计存储引擎的信息，排序数据、搜索中间表、发送数据等。囊括了所有的MySQL的所有状态,其中具体的含义如下图：

SQL执行顺序：

例子：select distinct s.id from T t join S s on t.id=s.id where t.name=”Yrion” group by t.mobile having count(*)>2 order by s.create_time limit 5;

from

第一步就是选择出from关键词后面跟的表，这也是SQL执行的第一步：表示要从数据库中执行哪张表。

实例说明：在这个例子中就是首先从数据库中找到表T。

join on

join是表示要关联的表，on是连接的条件。通过from和join on选择出需要执行的数据库表T和S，产生笛卡尔积，生成T和S合并的临时中间表Temp1。on：确定表的绑定关系，通过on产生临时中间表Temp2。

实例说明：找到表S，生成临时中间表Temp1，然后找到表T的id和S的id相同的部分组成成表Temp2，Temp2里面包含着T和Sid相等的所有数据。

where

where表示筛选，根据where后面的条件进行过滤，按照指定的字段的值（如果有and连接符会进行联合筛选）从临时中间表Temp2中筛选需要的数据，注意如果在此阶段找不到数据，会直接返回客户端，不会往下进行。这个过程会生成一个临时中间表Temp3。注意：在where中不可以使用聚合函数，聚合函数主要是（min\max\count\sum等函数）。

实例说明：在temp2临时表集合中找到T表的name=”Yrion”的数据，找到数据后会成临时中间表Temp3，T。emp3里包含name列为”Yrion”的所有表数据。

group by

group by是进行分组，对where条件过滤后的临时表Temp3按照固定的字段进行分组，产生临时中间表Temp4，这个过程只是数据的顺序发生改变，而数据总量不会变化，表中的数据以组的形式存在。

实例说明：在Temp3表数据中对mobile进行分组，查找出mobile一样的数据，然后放到一起，产生Temp4临时表。

Having

对临时中间表Temp4进行聚合，这里可以为count等计数，然后产生中间表Temp5，在此阶段可以使用select中的别名。

实例说明：在Temp4临时表中找出条数大于2的数据，如果小于2直接被舍弃掉，然后生成临时中间表Temp5。

select

对分组聚合完的表挑选出需要查询的数据，如果为*会解析为所有数据，此时会产生中间表Temp6。

实例说明：在此阶段就是对Temp5临时聚合表中S表中的id进行筛选产生Temp6，此时Temp6就只包含有s表的id列数据，并且name=”Yrion”，通过mobile分组数量大于2的数据。

Distinct

Distinct对所有的数据进行去重，此时如果有min、max函数会执行字段函数计算，然后产生临时表Temp7。

实例说明：此阶段对Temp5中的数据进行去重，引擎API会调用去重函数进行数据的过滤，最终只保留id第一次出现的那条数据，然后产生临时中间表Temp7。

order by

会根据Temp7进行顺序排列或者逆序排列，然后插入临时中间表Temp8，这个过程比较耗费资源。

实例说明：这段会将所有Temp7临时表中的数据按照创建时间（create_time）进行排序，这个过程也不会有列或者行损失。

limit

limit对中间表Temp8进行分页，产生临时中间表Temp9，返回给客户端。
实例说明：在Temp7中排好序的数据，然后取前五条插入到Temp9这个临时表中，最终返回给客户端。

PS：实际上这个过程也并不是绝对这样的，中间MySQL会有部分的优化以达到最佳的优化效果，比如在select筛选出找到的数据集。

MySQL之索引

(38条消息) MySQL之索引_layman .的博客-CSDN博客

(38条消息) MySQL之优化案例_layman .的博客-CSDN博客_mysql 优化案例

(38条消息) MySQL之查询优化（EXPLAIN）_layman .的博客-CSDN博客

(43条消息) 一文彻底搞懂MySQL基础：B树和B+树的区别_公众号：码农富哥的博客-CSDN博客_mysqlbtree 与b+tree区别

(43条消息) 二叉树到 B+树的演化过程_huihui-6020的博客-CSDN博客_数据结构树的演变

(65条消息) Mysql高级部分系列（二）_prefect_start的博客-CSDN博客

为什么要使用索引:

假如给数据使用二叉树这样的数据结构进行存储

对字段Col2添加了索引，就相当于在硬盘上为Col 2维护了一个索引的数据结构，即这个二叉搜索树。二叉搜索树的每个结点存储的是（K，V)结构，key是Col 2，value是该key 所在行的文件指针（地址)。比如:该二叉搜索树的根节点就是(34,0x07)。现在对Col2添加了索引，这时再去查找Col2=89这条记录的时候会先去查找该二叉搜索树（二叉树的遍历查找)。读34到内存，89>34;继续右侧数据，读89到内存，89==- 89;找到数之后根据结点的地址值快速查找记录对应的地址,只需要定位到地址,,查询数据就提高了.

目的就是为了减少磁盘io的次数,加快查询数据

索引是什么？

MySQL 官方对索引的定义为：索引（Index）是帮助 MySQL 高效获取数据的数据结构。可以得到索引的本质： 索引是数据结构。可以简单理解为排好序的快速查找数据结构。

索引是在存储引擎中实现的，因此每种存储引擎的索引不一定完全相同，并且每种存储引擎不一定支持所有索引类型。同时，存储引擎可以定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引，总索引长度至少为256字节。有些存储引擎支持更多的索引数和更大的索引长度。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在物理磁盘上。

索引没有特别指明，都是指B-Tree（多路搜索树，并不一定是二叉树）结构组织索引

其中聚集索引，次要索引、覆盖索引，复合索引、前缀索引默认都是使用B+树索引

除了B+树索引，还有哈希索引。

索引的优势和劣势：

优势：

提高数据检索的效率，降低磁盘IO成本
通过索引对数据进行排序，降低CPU消耗
在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间，降低了CPU的消耗。

劣势：

索引实际上也是一张数据表，该表保存了主键与索引字段，并指向实体表的记录，所以索引也需要占用空间，一般而言，索引表占用的空间是数据表的1.5倍.(索引需要占用磁盘空间)
索引虽然能够提高查询速度，但是同时也会降低更新表的速度，因为更新数据时，也需要更新索引(更新慢,删除快)

MySQL的索引结构：

Btree 索引

初始化介绍

一棵b树，浅蓝色的块称为磁盘块，，可以看到每个磁盘块包含几数据项（深蓝色)和指针

磁盘块 1 包含数据项 17 和 35，包含指针 P1、P2、P3， P1 表示小于 17 的磁盘块，P2 表示在 17 和 35 之间的磁盘块，P3 表示大于 35 的磁盘块。

非叶子节点不存储真实数据，只存储指引搜索方向的数据项，如17、35并不是存储在数据表中

查找过程

如果要查找数据项 29，那么首先把磁盘块 1 由磁盘加载到内存，此时发生一次 IO。

在内存中用二分查找确定 29 在 17 和 35 之间，锁定磁盘块 1 的 P2 指针，内存时间因为非常短（相比磁盘IO）可以忽略不计。通过磁盘块 1 的 P2 指针的磁盘地址把磁盘块 3 由磁盘加载到内存，发生第二次 IO。

29 在 26 和 30 之间，锁定磁盘块 3 的 P2 指针，通过指针加载磁盘块 8 到内存，发生第三次 IO。

同时内存中做二分查找找到 29，结束查询，总计三次 IO。

B+tree 索引

B树和B+树的区别 - iVictor - 博客园 (cnblogs.com)

B+树中只有叶子节点会带有指向记录的指针（ROWID），而B树则所有节点都带有，在内部节点出现的索引项不会再出现在叶子节点中。

在 B-树中，越靠近根节点的记录查找时间越快，只要找到关键字即可确定记录的存在

B+树中的每个节点查找时间都是基本相同，都需要从根节点走到叶子节点，而且在叶子节点还需要比较关键字。

从这个角度看 B- 树的性能好像要比 B+树好，而在实际应用中却是 B+树的性能要好些。因为 B+树的非叶子节点不存放实际的数据，这样每个节点可容纳的元素个数比 B-树多，树高比 B-树小，这样带来的好处是减少磁盘访问次数。尽管 B+树找到一个记录所需的比较次数要比 B-树多，但是一次磁盘访问的时间相当于成百上千次内存比较的时间，因此实际中 B+树的性能可能还会好些，而且 B+树的叶子节点使用指针连接在一起，方便顺序遍历（例如查看一个目录下的所有文件，一个表中的所有记录等），这也是很多数据库和文件系统使用 B+树的缘故。

B+树的优势

1、磁盘读写代价更低

B+树的磁盘读写更低

B+树内部没有指向具体关键字具体信息的指针，内部节点相对于B树更小。如果把同一个内部节点关键字存放在同一个磁盘中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说 IO 读写次数也就降低了.

2、查询速度更加稳定。

B+树的查询效率更加稳定，由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

区别

B-Tree 中，所有节点都会带有指向具体记录的指针；B+Tree 中只有叶子结点会带有指向具体记录的指针。

B-Tree 中不同的叶子之间没有连在一起；B+Tree 中所有的叶子结点通过指针连接在一起。

B-Tree 中可能在非叶子结点就拿到了指向具体记录的指针，搜索效率不稳定；B+Tree 中，一定要到叶子结点中才可以获取到具体记录的指针，搜索效率稳定。

基于上面两点分析，我们可以得出如下结论：

B+Tree 中，由于非叶子结点不带有指向具体记录的指针，所以非叶子结点中可以存储更多的索引项，这样就可以有效降低树的高度，进而提高搜索的效率。

B+Tree 中，叶子结点通过指针连接在一起，这样如果有范围扫描的需求，那么实现起来将非常容易，而对于 B-Tree，范围扫描则需要不停的在叶子结点和非叶子结点之间移动。

对于第一点，一个 B+Tree 可以存多少条数据呢？以主键索引的 B+Tree 为例（二级索引存储数据量的计算原理类似，但是叶子节点和非叶子节点上存储的数据格式略有差异），我们可以简单算一下。

计算机在存储数据的时候，最小存储单元是扇区，一个扇区的大小是 512 字节，而文件系统（例如 XFS/EXT4）最小单元是块，一个块的大小是 4KB。InnoDB 引擎存储数据的时候，是以页为单位的，每个数据页的大小默认是 16KB，即四个块。

基于这样的知识储备，我们可以大致算一下一个 B+Tree 能存多少数据。

假设数据库中一条记录是 1KB，那么一个页就可以存 16 条数据（叶子结点）；对于非叶子结点存储的则是主键值+指针，在 InnoDB 中，一个指针的大小是 6 个字节，假设我们的主键是 bigint ，那么主键占 8 个字节，当然还有其他一些头信息也会占用字节我们这里就不考虑了，我们大概算一下，小伙伴们心里有数即可：

1	16*1024/(8+6)=1170

即一个非叶子结点可以指向 1170 个页，那么一个三层的 B+Tree 可以存储的数据量为：

1	1170117016=21902400

可以存储 2100万条数据。

在 InnoDB 存储引擎中，B+Tree 的高度一般为 2-4 层，这就可以满足千万级的数据的存储，查找数据的时候，一次页的查找代表一次 IO，那我们通过主键索引查询的时候，其实最多只需要 2-4 次 IO 操作就可以了。

Hash索引

full-text 全文索引

R-Tree索引

聚簇索引和非聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。术语‘聚簇’表示数据行和相邻的键值聚簇的存储 在一起。如下图，左侧的索引就是聚簇索引，因为数据行在磁盘的排列和索引排序保持一致。

在这里插入图片描述

索引按照物理实现方式，索引可以分为 2 种：聚簇（聚集）和非聚簇（非聚集）索引。我们也把非聚集索引称为二级索引或者辅助索引。

1. 聚簇索引

特点：

使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：

页内的记录是按照主键的大小顺序排成一个单向链表。

各个存放 用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。

存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。

B+树的叶子节点存储的是完整的用户记录。所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。

优点：

数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快

聚簇索引对于主键的排序查找和范围查找速度非常快

按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的io操作。

缺点：

插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键

更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新*

二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据

聚簇索引的限制

对于 mysql 数据库目前只有 innodb 数据引擎支持聚簇索引，而 Myisam 并不支持聚簇索引

由于数据物理存储排序方式只能有一种，所以每个 Mysql 的表只能有一个聚簇索引。一般情况下就是该表的主键。 为了充分利用聚簇索引的聚簇的特性，所以 innodb 表的主键列尽量选用有序的顺序 id，而不建议用无序的 id，比如 uuid。

二级索引（辅助索引、非聚簇索引）

主键索引之外，其他的都称之为非主键索引，非主键索引也被称为二级索引（Secondary Index），或者叫作辅助索引

什么是回表

什么是 MySQL 的“回表”？ - 文章详情 (itpub.net)

区别：

对于主键索引和非主键索引，使用的数据结构都是 B+Tree，唯一的区别在于叶子结点中存储的内容不同：

主键索引的叶子结点存储的是一行完整的数据。

非主键索引的叶子结点存储的则是主键值。

所以，当我们需要查询的时候：

如果是通过主键索引来查询数据，例如 select * from user where id=100，那么此时只需要搜索主键索引的 B+Tree 就可以找到数据。
如果是通过非主键索引来查询数据，例如 select * from user where username='javaboy'，那么此时需要先搜索 username 这一列索引的 B+Tree，搜索完成后得到主键的值，然后再去搜索主键索引的 B+Tree，就可以获取到一行完整的数据。

对于第二种查询方式而言，一共搜索了两棵 B+Tree，第一次搜索 B+Tree 拿到主键值后再去搜索主键索引的 B+Tree，这个过程就是所谓的回表。

从上面的分析中我们也能看出，通过非主键索引查询要扫描两棵 B+Tree，而通过主键索引查询只需要扫描一棵 B+Tree，所以如果条件允许，还是建议在查询中优先选择通过主键索引进行搜索。

索引的声明与使用

1.1索引的分类：

索引的分类：

常见的索引类型有：主键索引、唯一索引、普通索引、全文索引、组合索引等

从功能上：分为普通索引、唯一索引、主键索引、全文索引

从物理实现方式上：聚簇索引和非聚簇索引

按照作用个数进行划分：单值索引和联合索引

不同的存储引擎支持的索引类型也不一样：

**InnoDB:**支持 B-tree、Full-text 等索引,不支持 Hash索引；

MyISAM ：支持 B-tree、Full-text 等索引，不支持 Hash 索引；

Memory ：支持 B-tree、Hash 等索引，不支持 Full-text 索引；

NDB ：支持 Hash 索引，不支持 B-tree、Full-text 等索引；

Archive ：不支持 B-tree、Hash、Full-text 等索引；

索引的分类和建索引命令语句

单值索引：

一个索引只包含单个列，一个表可以有多个单列索引

1
2
3

CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name
VARCHAR(200), PRIMARY KEY(id), KEY (customer_name), UNIQUE (customer_no)
);

1 2	CREATE INDEX idx_customer_name ON customer(customer_name);

主键索引：即主索引，根据主键pk_clolum（length）建立索引，不允许重复，不允许空值；

1	ALTER TABLE 'table_name' ADD PRIMARY KEY pk_index('col')；

唯一索引：

用来建立索引的列的值必须是唯一的，**允许空值**

1	ALTER TABLE [table_name] ADD UNIQUE index_name([colum]);

普通索引

用表中的普通列构建的索引，没有任何限制

1	ALTER TABLE [table_name] ADD INDEX idx_[table_name]_index_name([colum]);

全文索引

用大文本对象的列构建的索引,只能使用在char、varchar和Text列创建索引。索引总是对整个列进行，不支持局部（前缀）索引

1	ALTER TABLE [table_name] ADD FULLTEXT INDEX ft_index([colum]);=

不同于like方式的的查询：

1	SELECT * FROM papers WHERE content LIKE ‘%查询字符串%’;

全文索引用match+against方式查询：

1	SELECT * FROM papers WHERE MATCH(title,content) AGAINST (‘查询字符串’);

注意点

使用全文索引前，搞清楚版本支持情况；

全文索引比 like + % 快 N 倍，但是可能存在精度问题；

如果需要全文索引的是大量数据，建议先添加数据，再创建索引。

组合索引

用多个列组合构建的索引，这多个列中的值不允许有空值

1	ALTER TABLE [table_name] ADD INDEX index_name([colum1],[colum2],[colum3]);

查看索引

1	SHOW INDEX FROM 'table_name'

删除索引

1	DROP INDEX index_name ON 'table_name'

通过命令查看索引

1 2	show create TABLE dept show index from book

/*创建索引*/
CREATE TABLE book( book_id INT , book_name VARCHAR(100), authors VARCHAR(100), info VARCHAR(100) , comment VARCHAR(100), year_publication YEAR, INDEX(year_publication) );

/*查看索引*/
show create TABLE dept
show index from book

/*创建唯一性索引，添加数据不能是空值，主键索引*/
CREATE TABLE test1( id INT NOT NULL, name varchar(30) NOT NULL, UNIQUE INDEX uk_idx_id(id) );
/*性能分析工具*/
EXPLAIN select * from book  where book_name ='mysql高级'

/*通过删除主键约束的方式删除主键索引*/

alter table book;
drop PRIMARY key;

/*创建联合索引*/

CREATE TABLE test3( id INT(11) NOT NULL, name CHAR(30) NOT NULL, age INT(11) NOT NULL, info VARCHAR(255), INDEX multi_idx(id,name,age) );

select * from 
show index from test3;

索引创建的时机

索引设计不合理或者缺少索引会对数据库和应用程序的性能造成障碍，高效的索引对于获得良好的性能非常重要。设计索引时候，应该考虑相对应的准则。

创建索引

对查询频次较高，且数据量比较大的表建立索引。
主键自动创建建立唯一索引

业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。（来源：Alibaba）

说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的。

频繁作为查询条件的字段应该创建索引

某个字段在select 语句中where 条件经常被使用到，那么就需要给这个字段创建索引。尤其是在数据量大的条件下，创建索引可以大幅度提升查询的效率。

与其他表关联，外键应该创建索引
常 GROUP BY 和 ORDER BY 的列

索引就是让数据按照某种顺序进行存储或检索，因此当我们使用 GROUP BY 对数据进行分组查询，或者使用 ORDER BY 对数据进行排序的时候，就需要对分组或者排序的字段进行索引。

DISTINCT 字段需要创建索引

如果使用某个字段进行去重，使用DISTINCT，对这个字段创建索引，也会提升效率。

多表 JOIN 连接操作时，创建索引注意事项

首先，连接的表数量尽量不要超过3张，因为每增加一张表就相当于增加了依次嵌套的循环，数量级增长的非常快。严重影响查询的效率

使用列的类型小的创建索引
使用字符串前缀创建索引
使用最频繁的列放到联合索引的左侧

利用最左前缀，N个列组合而成的组合索引，那么相当于是创建了N个索引，如果查询时where子句中使用了组成该索引的前几个字段，那么这条查询SQL可以利用组合索引来提升查询效率。

创建复合索引

CREATE INDEX idx_name_email_status ON tb_seller(NAME,email,STATUS);
1
就相当于

对 name 创建索引 ; 对 name , email 创建了索引 ; 对 name , email, status 创建了索引 ;

在多个字段都要创建索引的情况下，联合索引优于单值索引

无需创建索引

数据表的记录值太少

在数据表中的数据行数比较少的情况下，比如不到 1000 行，是不需要创建索引的。

频繁更新的字段
数据重复且分布平均的字段，不易创建索引

非聚簇索引存储了对主键的引用，如果select字段不在非聚簇索引类，就需要跳到主键索引。

如果非聚簇索引值重复率高，那么查询时就会大量出现上图中从右边跳到左边的情况，导致整个流程很慢

非聚簇索引

. 数据库服务器的优化步骤

2.查看系统性能参数

在Mysql中，可以使用SHOW STATUS 语句查询一些MySQL数据库服务器的性能参数、执行频率。

SHOW STATUS语句语法如下：

1	SHOW [GLOBAL\|SESSION] STATUS LIKE '参数';

一些常用的性能参数如下：

• Connections：连接MySQL服务器的次数。

• Uptime：MySQL服务器的上线时间。

• Slow_queries：慢查询的次数。

• Innodb_rows_read：Select查询返回的行数

• Innodb_rows_inserted：执行INSERT操作插入的行数

• Innodb_rows_updated：执行UPDATE操作更新的行数

• Innodb_rows_deleted：执行DELETE操作删除的行数

• Com_select：查询操作的次数。

• Com_insert：插入操作的次数。对于批量插入的 INSERT 操作，只累加一次。 • Com_update：更新操作

的次数。

• Com_delete：删除操作的次数。

定位执行慢的 SQL：慢查询日志

MySQL的慢查询日志，用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long. query _time值
的SQL,则会被记录到慢查询日志中。long. query _time的默认值为10，意思是运行10秒以上(不含10秒)的语
句，认为是超出了我们的最大忍耐时间值。
它的主要作用是，帮助我们发现那些执行时间特别长的SQL查询，并且有针对性地进行优化，从而提高系统的整
体效率。当我们的数据库服务器发生阻塞、运行变慢的时候,检查一下慢查询日志，找到那些慢查询,对解决问
题很有帮助。比如一条sq|执行超过5秒钟,我们就算慢SQL,希望能收集超过5秒的sql,结合explain进行全面分
析。
默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。如果不是调优需要的话，- -般
不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。
慢查询日志支持将日志记录写入文件。

开启慢查询日志：

1.开启shlow_query_log

1	mysql > set global slow_query_log='ON'

你能看到这时慢查询分析已经开启，同时文件保存在 /var/lib/mysql/atguigu02-slow.log 文件中。

2.修改long_query_time阈值

1	mysql > show variables like '%long_query_time%';

1	show VARIABLES like '%slow_query_log%'

这里如果我们想把时间缩短，比如设置为 1 秒，可以这样设置：

#测试发现：设置global的方式对当前session的long_query_time失效。对新连接的客户端有效。所以可以一并 执行下述语句 
mysql > set global long_query_time = 1; 
mysql> show global variables like '%long_query_time%'; 
mysql> set long_query_time=1; 
mysql> show variables like '%long_query_time%';

查看慢查询数目：

1	SHOW GLOBAL STATUS LIKE '%Slow_queries%';

步骤一：建表

CREATE TABLE `student` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `stuno` INT NOT NULL , `name` VARCHAR(20) DEFAULT NULL, `age` INT(3) DEFAULT NULL, `classId` INT(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

步骤二：设置参数

log_bin_trust_function_creators

命令开启允许创建函数设置：

1	set global log_bin_trust_function_creators=1; # 不加global只是当前窗口有效。

步骤三：创建函数

产生随机数值：

1 2	CREATE DEFINER=`root`@`%` FUNCTION `rand_num`(from_num INT ,to_num INT) RETURNS int(11) BEGIN DECLARE i INT DEFAULT 0; SET i = FLOOR(from_num +RAND()*(to_num - from_num+1)) ; RETURN i; END

随机产生字符串

1
2

CREATE DEFINER=`root`@`%` FUNCTION `rand_string`(n INT) RETURNS varchar(255) CHARSET utf8mb4
BEGIN DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'; DECLARE return_str VARCHAR(255) DEFAULT ''; DECLARE i INT DEFAULT 0; WHILE i < n DO SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1)); SET i = i + 1; END WHILE; RETURN return_str; END

创建存储过程：

CREATE DEFINER=`root`@`%` PROCEDURE `insert_stu1`( START INT , max_num INT )
BEGIN DECLARE i INT DEFAULT 0; SET autocommit = 0; #设置手动提交事务 

REPEAT #循环 
SET i = i + 1; #赋值 
INSERT INTO student (stuno, NAME ,age ,classId ) VALUES ((START+i),rand_string(6),rand_num(10,100),rand_num(10,1000)); UNTIL i = max_num END REPEAT; COMMIT; #提交事务 
END

调用存储过程：

1	#调用刚刚写好的函数, 4000000条记录,从100001号开始 CALL insert_stu1(100001,4000000);

测试查询分析：

1	SELECT * FROM student WHERE stuno = 3455655;

1	SELECT * FROM student WHERE name = 'oQmLUr';

查看慢查询数量：

1	show status like 'slow_queries';

从上面可以看出：

查询时间均达到了秒级以上，效率还是比较低的。

慢查询日志分析工具：mysqldumpslow

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow 。

mysqldumpslow 命令的具体参数如下：

1	mysqldumpslow --help

-a: 不将数字抽象成N，字符串抽象成S

-s: 是表示按照何种方式排序：

c: 访问次数

l: 锁定时间

r: 返回记录

t: 查询时间

al:平均锁定时间

ar:平均返回记录数

at:平均查询时间（默认方式）

ac:平均查询次数

-t: 即为返回前面多少条的数据；

-g: 后边搭配一个正则匹配模式，大小写不敏感的；

工作常用参考：

#得到返回记录集最多的10个SQL 
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log 
#得到访问次数最多的10个SQL 
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log 
#得到按照时间排序的前10条里面含有左连接的查询语句 
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log 
#另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况 
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

关闭慢查询日志：

关闭慢查询有两种方式：

方式一:永久性：

修改my.cnf或者my.ini文件，把[mysqld]组下的slow_ query_ log值设置为OFF，修改保存后，再重启MySQL服务, 即
可生效;

1 2	[mysqld] slow_query_log=OFF

或者，把slow_query_log一项注释掉或删除

1 2	[mysqld] #slow_query_log =OFF

1 2	show variables like '%slow%' ; #查询慢查询日志所在的目录 show variables like '%long_query_time%'#查询超时时间

方式二：临时性

使用set语句设置

（1）停止MySQL慢查询日志功能，具体使用sql

1	SET GLOBAL slow_query_log=off;

1	systemctl restart mysqld

（2）重启MySQL服务，使用SHOW语句查询慢查询日志功能信息，具体SQL语句如下

1
2
3

SHOW VARIABLES LIKE '%slow%'; 
#以及 
SHOW VARIABLES LIKE '%long_query_time%';

删除慢查询日志：

刷新日志文件：

查看SQL执行成本：show profile

会查看最近执行的成本

1	mysql > show variables like 'profiling';

通过设置 profiling=’ON’ 来开启 show profile：

1	show profile cpu,block io for query 2;

show profile****的常用查询参数：

① ALL：显示所有的开销信息。

② BLOCK IO：显示块IO开销。

③ CONTEXT SWITCHES：上下文切换开销。

④ CPU：显示CPU开销信息。

⑤ IPC：显示发送和接收开销信息。

⑥ MEMORY：显示内存开销信息。

⑦ PAGE FAULTS：显示页面错误开销信息。

⑧ SOURCE：显示和Source_function，Source_file， Source_line相关的开销信息。

⑨ SWAPS：显示交换次数开销信息。

MySQL之查询优化（EXPLAIN）

在这里插入图片描述

分析查询语句：EXPLAIN

定位查询的慢SQL之后，我们就可以使用EXPlAIN或DESCRIBE工具做针对性的分析查询语句。DESCRIBE语句的使用方法与EXPALIN语句是一样的，并且分析结果也是一样的

MySQl中与专门的负责优化select语句的优化期模块，

主要功能：通过计算分析系统中手机到的统计信息，为客户端的请求的query提供他认为最优的执行计划。

这个执行计划展示接下来具体执行查询到方式，比如多表连接的顺序是什么每个表采用什么方式，具体的执行查询等等。具体执行查询等等。MySQL为我们提供了EXPLAIN语句来帮助我们查看某个查询语句的具体执行计划，大家看懂EXPLAIN语句的各个输出项，可以有针对性的提升我们查询语句的性能。

能做什么：

表的读取顺序：

数据读取操作的操作类型：

哪些索引可以使用

哪些索引被实际使用

表之间的引用

每张表有多少被优化器查询

https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

explain select * FROM s1;
explain  SELECT * from s1 INNER JOIN s2

select * from s1 where key1='a'
explain select * from s1 UNION select * FROM s2

执行计划字段介绍

1.`id`

select查询的序列号，包含一组数字，表示查询中执行select字句或操作表的顺序，该值一共有三种情况

id相同：执行顺序由上至下
id不同：值越大优先级越高，越先被执行
说明：id相同为同组，从上往下顺序执行，id不同，则值越大，优先级越高，越先执行

2. `select_type`

查询类型，用于区别普通查询，联合查询，子查询等复杂查询，共有6个值

3. table

略

4. `type`

4.possible-keys

显示可能应用到的索引，一个或多个。如果查询语句where条件的字段上存在索引，则该索引会被列出，但不一定在查询中被实际使用。

5.key

实际使用的索引，若为null，则没有使用索引。查询中若使用了复合索引，则该索引仅出现在key列表中。

6.key_len

表示索引中使用的字节数，在不损失精确性的情况下，长度越短越好。它表示索引字段的最大可能长度，并非实际使用长度。它是根据表定义计算而得，不是通过表内检索得出的。

7.ref

显示索引的那一列被使用了，如果可能的话，是一个常量，表示查找索引列上的值。

8.,rows

根据表统计信息及索引选用情况，大致估算出找到所需记录所需要读取的行数，理论上越少越好

9.ExTra

包含不适合在其他列中显示但十分重要的额外信息。

索引优化与查询优化

全职匹配：

最佳左前缀法则：

MySQL可以为多个字段创建索引,一个索引可以包括16个字段。对于多列索引，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。如果查询条件中没有使用这些字段中第1个字段时，多列(或联合)索引不会被使用。

主键插入顺序：

最后主键是递增的数据

如果此时再插入一条主键值为 9 的记录，那它插入的位置就如下图：

数据页已经满了，再插进来需要把页面分列成两个页面，把本页的一些记录移到新创建的数据页中，

就会造成性能损失，所以如果我们想尽量，避免这样无谓的性能损耗，最好让插入的记录的主键值依次递增，这样就不会发生这样的性能损耗了。

所以我们建议：让主键具有 AUTO_INCREMENT ，让存储引擎自己为表生成主键，而不是我们手动插入，

我们自定义的主键列 id 拥有 AUTO_INCREMENT 属性，在插入记录时存储引擎会自动为我们填入自增的主键值。这样的主键占用空间小，顺序写入，减少页分裂。

计算、函数、类型转换导致索引失效

能够使用上索引

无法使用上索引，无法确定查询的字段。

student表的字段stuno上设置有索引

索引优化生效：

1	EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno = 900000;

student表的字段name上设置有索引

1	EXPLAIN SELECT id, stuno, NAME FROM student WHERE NAME LIKE 'abc%';

类型转换导致索引失效

1 2	# 未使用到索引 EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=123;

1 2	# 使用到索引 EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name='123';

name=123发生类型转换，索引失效。

范围条件右边的列索引失效

如果三个索引中，应该把范围索引写在最后。

1	EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' ;

1	create index idx_age_name_classid on student(age,name,classid);

将范围查询条件放置语句最后：

1	EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.name = 'abc' AND student.classId>20 ;

不等于(!= 或者<>)索引失效

is null可以使用索引,is not null无法使用索引

#直接找null的字段
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL;


#需要一个个找
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;

like以通配符%开头索引失效

不能够确定扫描范围

页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决

OR前后存在非索引的列，索引失效

# 未使用到索引

1	EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;

1 2	#使用到索引 EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR name = 'Abel';

前后都需要进行建立索引

数据库和表的字符集统一使用utf8mb4

统一使用utf8mb4( 5.5.3版本以上支持)兼容性更好，统-字符集可以避免由于字符集转换产生的乱码。不同的字符集进行比较前需要进行转换会造成索引失效。

关联查询优化：

Join 语句原理

什么是驱动表，什么是被驱动表

当连接查询没有where条件时，左连接查询时，前面的表是驱动表，后面的表是被驱动表，右连接查询时相反，内连接查询时，哪张表的数据较少，哪张表就是驱动表
当连接查询有where条件时，带where条件的表是驱动表，否则是被驱动表

MySQL连接查询到底什么是驱动表？看了这个你应该就明白了 - 知乎 (zhihu.com)

Simple Nested-Loop Join Algorithms （简单嵌套循环连接算法）

双重循环，从驱动表中循环获取每一行数据，再在被驱动表匹配满足条件的行。

Index Nested-Loop Join Algorithms （索引嵌套循环连接算法)

join查询的优化思路就是小表驱动大表,而且在大表上创建索引(也就是被动表创建索引)，如果驱动表创建了索引,MySQL是不会使用的.

1
2
3

for (row1 : 驱动表) {
    索引在被驱动表中命中，不用再遍历被驱动表了
}

驱动表中

从驱动表中拿出一条数据，到非驱动表中进行查询，如果是主键索引，直接获取，如果是联合索引，需要回表操作。

Block Nested-Loop Join Algorithm(基于块的连接嵌套循环算法)

如果存在索引，那么会使用index的方式进行join,如果join的列没有索引,被驱动表要拉描的次数太多了。每次访问被驱动表，其表中的记录都会被加载到内存中,然后再从驱动表中取一条与其匹配，匹配结束后清除内存,然后再从驱动表中加载一条记录，然后把被驱动表的记录在加载到内存匹配，这样周而复始，大大增加了IO的次数。为了减少被驱动表的I0次数,就出现了Block Nested-Loop Join的方式。

不再是逐条获取驱动表的数据，而是-块- 块的获取，引入了join buffen缓冲区，将驱动表join相关的部分数据列(大小受join buffer的限制)缓存到join buffer中，然后全表打3描被驱动表,被驱动表的每一条记录一次性和joinbuffer中的所有驱动表记录进行匹配(内存中操作) ,将简单嵌套循环中的多次比较合并成- -次, 降低了被驱动表的访问频率。

缓存的不只是关联表的列，select后面的列也会缓存起来,在有N个join关联的sql中会分配n-1个join,所以查询的时候尽量减少不必要的字段，可以让join buff中存放更多的列。

子查询优化：

子查询Mysql的一项重要的功能，可以帮助我们通过一个sql语句实现比较复杂，但是子查询的效率不高。原因：

①执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。这样会消耗过多的CPU和I0资源，产生大量的慢查询。
②子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定
的影响。
③对于返回结果集比较大的子查询,其对查询性能的影响也就越大。

在MySQL中，可以使用连接（JOIN）查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引的话，性能就会更好.

结论：尽量不要使用NOT IN 或者 NOT EXISTS，用LEFT JOIN xxx ON xx WHERE xx IS NULL替代

排序优化：

问题：在where 条件的字段上加上索引，但是为什么在order by字段上还要加上索引

在MySQL中，支持两种排序方式，分别是FileSort和Index排序。
●Index 排序中，索引可以保证数据的有序性,不需要再进行排序，效率更高。
●FileSort 排序则一般在内存中进行排序，占用CPU较多。如果待排结果较大，会产生临时文件I/0到磁盘进行排序的情况，效率较低。

优化建议：

SQL 中，可以在 WHERE 子句和 ORDER BY 子句中使用索引，目的是在 WHERE 子句中避免全表扫描，在 ORDER BY 子句避免使用 FileSort 排序。当然，某些情况下全表扫描，或者 FileSort 排序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。

尽量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列；如果不同就使用联合索引。

无法使用 Index 时，需要对 FileSort 方式进行调优

INDEX a_b_c(a,b,c) order by 能使用索引最左前缀
- ORDER BY a
- ORDER BY a,b
- ORDER BY a,b,c
- ORDER BY a DESC,b DESC,c DESC 如果WHERE使用索引的最左前缀定义为常量，则order by 能使用索引
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b = const ORDER BY c
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b > const ORDER BY b,c 不能使用索引进行排序
- ORDER BY a ASC,b DESC,c DESC /* 排序不一致 */
- WHERE g = const ORDER BY b,c /*丢失a索引*/
- WHERE a = const ORDER BY c /*丢失b索引*/
- WHERE a = const ORDER BY a,d /*d不是索引的一部分*/
- WHERE a in (...) ORDER BY b,c /*对于排序来说，多个相等条件也是范围查询*/

案例实战：

ORDER BY子句，尽量使用Index方式排序，避免使用FileSort方式排序。执行案例前先清除student上的索引，只留主键

DROP INDEX idx_age ON student; 
DROP INDEX idx_age_classid_stuno ON student; D
ROP INDEX idx_age_classid_name ON student; 
#或者 
call proc_drop_index('atguigudb2','student');

filesort算法：

双路排序和单路排序

双路排序（慢）

MySQL 4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和

order by列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取

对应的数据输出从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。取一批数据，要对磁盘进行两次扫描，众所周知，IO是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序。

单路排序（快）

从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为它把每一行都保存在内存中了。

结论及引申出的问题

由于单路是后出的，总体而言好过双路但是用单路有问题

GROUP BY优化:

group by 使用索引的原则几乎跟order by一致，group by 即使没有过滤条件用到索引，也可以直接使用索引。
group by 先排序再分组，遵照索引建的最佳左前缀法则
当无法使用索引列，增大 max_length_for_sort_data 和 sort_buffer_size 参数的设置
where效率高于having，能写在where限定的条件就不要写在having中了
减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。Order by、group by、distinct这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。
包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢。