MySQL 唯一性索引的唯一性的

MySQL通常使用B树（或其变体如B+树）作为唯一性索引的数据结构。这种结构允许高效的数据检索和插入操作。当插入新行或更新现有行的索引列时，MySQL首先在索引中检查是否已存在相同的键值。如果发现索引列的新值已存在于唯一性索引中，MySQL将阻止该插入或更新操作，并返回一个错误。

在支持事务的存储引擎（如InnoDB）中，事务机制和锁定协议有助于维护索引的唯一性。当一个事务正在修改索引列时，其他事务对相同键值的修改会被适当地阻塞，直到第一个事务提交或回滚，确保数据的一致性和唯一性。

此外，在实际写入数据到磁盘之前，MySQL也会执行约束检查，确保不会违反唯一性约束。

唯一索引允许NULL值吗？

在MySQL中，唯一索引可以允许NULL值存在，但这些NULL值的行为是未知的。所谓未知，指的是它们不相等，但也不能简单地说它们是不等的。

此外，InnoDB存储引擎在MySQL中支持唯一索引中存在多个NULL值。这是因为在MySQL中，NULL被视为“未知”，每个NULL值都被视为互不相同。因此，即使列被定义为唯一索引，也可以包含多个NULL值。

唯一性索引查询更快吗？

在数据库中，通过唯一性索引来创建唯一性约束，可以确保表中指定列的值是唯一的，从而避免了数据重复和错误插入的问题。

唯一性索引查询通常比非唯一性索引查询更快，因为唯一性索引能够快速定位到唯一的记录，而非唯一性索引则需要扫描整个索引并匹配符合条件的记录。

在应用中，如果我们能够设计合适的唯一性索引，也可以有效地提升查询性能和数据质量。

唯一性索引有什么缺点吗？

首先，唯一性索引需要确保索引列的唯一性，因此在插入数据时需要检查是否存在相同的索引值，这会对插入性能产生一定的影响。

如果需要更新唯一性索引列的值，需要先删除旧记录，再插入新记录，这会对更新操作的成本产生影响。

SQL 语句从诞生至结束历程

SQL是如何诞生的？

SQL语句都诞生于客户端，主要有两种方式产生一条SQL，一种是由开发者自己手动编写，另一种则是相关的ORM框架自动生成，一般情况下，MySQL运行过程中收到的大部分SQL都是由ORM框架生成的，比如Java中的MyBatis、Hibernate框架等。

同时，SQL生成的时机一般都与用户的请求有关，当用户在系统中进行了某项操作，一般都会产生一条SQL，例如我们在浏览器上输入如下网址：

https://juejin.cn/user/862486453028888/posts

此时，就会先请求掘金的服务器，然后由掘金内部实现中的ORM框架，根据请求参数生成一条SQL，类似于下述的伪SQL：

select * from juejin_article where userid = 862486453028888;

这条SQL大致描述的意思就是：根据用户请求的「作者ID」，在掘金数据库的文章表中，查询该作者的所有文章信息。

从上述这个案例中可以明显感受出来，用户浏览器上看到的数据一般都来自于数据库，而数据库执行的SQL则源自于用户操作，两者是相辅相成的关系，也包括任何写操作（增、删、改），本质上也会被转换一条条SQL，也举个简单的例子：

# 请求网址（Request URL）
https://www.xxx.com/user/register

# 请求参数（Request Param）
{
    user_name : "竹子爱熊猫",
    user_pwd : "123456",
    user_sex : "男",
    user_phone : "18888888888",
    ......
}
# 这里对于用户密码的处理不够严谨，没有做加密操作不要在意~

比如上述这个用户注册的案例，当用户在网页上点击「注册」按钮时，会向目标网站的服务器发送一个post请求，紧接着同样会根据请求参数，生成一条SQL，如下：

insert into table_user (user_name,user_pwd,user_sex,user_phone,....)
    VALUES ("竹子爱熊猫", "123456", "男", "18888888888", ....);

也就是说，一条SQL的诞生都源自于一个用户请求，在开发程序时，SQL的大体逻辑我们都会由业务层的编码决定，具体的SQL语句则是根据用户的请求参数，以及提前定制好的“SQL骨架”拼揍而成。当然，在Java程序或其他语言编写的程序中，只能生成SQL，而SQL真正的执行工作是需要交给数据库去完成的。

SQL执行前会经历的过程

经过上一步之后，一条完整的SQL就诞生了，为了SQL能够正常执行，首先会先去获取一个数据库连接对象，MySQL连接层中会维护着一个名为「连接池」的玩意儿，但相信大家也都接触过「数据库连接池」这个东西，比如Java中的C3P0、Druid、DBCP....等各类连接池。

那此时在这里可以思考一个问题，为什么数据库自己维护了连接池的情况下，在MySQL客户端中还需要再次维护一个数据库连接池呢？

2.1、数据库连接池的必要性

众所周知，当要在Java中创建一个数据库连接时，首先会去读取配置文件中的连接地址、账号密码等信息，然后根据配置的地址信息，发起网络请求获取数据库连接对象。在这个过程中，由于涉及到了网络请求，那此时必然会先经历TCP三次握手的过程，同时获取到连接对象完成SQL操作后，又要释放这个数据库连接，此时又需要经历TCP四次挥手过程。

从上面的描述中可以明显感知出，在Java中创建、关闭数据库连接的过程，过程开销其实比较大，而在程序上线后，又需要频繁进行数据库操作。因此如果每次操作数据库时，都获取新的连接对象，那整个Java程序至少会有四分之一的时间内在做TCP三次握手/四次挥手工作，这对整个系统造成的后果可想而知。

也正是由于上述原因，因此大名鼎鼎的「数据库连接池」登场了，「数据库连接池」和「线程池」的思想相同，会将数据库连接这种较为珍贵的资源，利用池化技术对这种资源进行维护。也就代表着之后需要进行数据库操作时，不需要自己去建立连接了，而是直接从「数据库连接池」中获取，用完之后再归还给连接池，以此达到复用的效果。

当然，连接池中维护的连接对象也不会一直都在，当长时间未进行SQL操作时，连接池也会销毁这些连接对象，而后当需要时再次创建，不过何时创建、何时销毁、连接数限制等等这些工作，都交给了连接池去完成，无需开发者自身再去关注。

在Java中，目前最常用的数据库连接池就是阿里的Druid，一般咱们都会用它作为生产环境中的连接池。

有了MySQL连接池为何还需要在客户端维护一个连接池？

两者都是利用池化技术去达到复用资源、节省开销、提升性能的目的，只不过针对的方向不同。

MySQL的连接池主要是为了实现复用线程的目的，因为每个数据库连接在MySQL中都会使用一条线程维护，而每次为客户端分配连接对象时，都需要经历创建线程、分配栈空间....这些繁重的工作，这个过程需要时间，同时资源开销也不小，所以MySQL利用池化技术解决了这些问题。

而客户端的连接池，主要是为了实现复用数据库连接的目的，因为每次SQL操作都需要经过TCP三次握手/四次挥手的过程，过程同样耗时且占用资源，因此也利用池化技术解决了这个问题。

MySQL连接池维护的是工作线程，客户端连接池则维护的是网络连接。

2.2、SQL执行前会发生的事情

当完整的SQL生成后，会先去连接池中尝试获取一个连接对象，那接下来会发生什么事情呢？如下图：

获取连接

当尝试从连接池中获取连接时，如果此时连接池中有空闲连接，可以直接拿到复用，但如果没有，则要先判断一下当前池中的连接数是否已达到最大连接数，如果连接数已经满了，当前线程则需要等待其他线程释放连接对象，没满则可以直接再创建一个新的数据库连接使用。

假设此时连接池中没有空闲连接，需要再次创建一个新连接，那么就会先发起网络请求建立连接。

首先会经过《TCP的三次握手过程》，当网络连接建立成功后，也就等价于在MySQL中创建了一个客户端会话，然后会发生下图一系列工作：

SQL执行前的工作

• ①首先会验证客户端的用户名和密码是否正确：

  • 如果用户名不存在或密码错误，则抛出1045的错误码及错误信息。

  • 如果用户名和密码验证通过，则进入第②步。

• ②判断MySQL连接池中是否存在空闲线程：

  • 存在：直接从连接池中分配一条空闲线程维护当前客户端的连接。

  • 不存在：创建一条新的工作线程（映射内核线程、分配栈空间....）。

• ③工作线程会先查询MySQL自身的用户权限表，获取当前登录用户的权限信息并授权。

到这里为止，执行SQL前的准备工作就完成了，已经打通了执行SQL的通道，下一步则是准备执行SQL语句，工作线程会等待客户端将SQL传递过来。

SQL语句在数据库中是如何执行的？

经过连接层的一系列工作后，接着客户端会将要执行的SQL语句通过连接发送过来，然后会进行MySQL服务层进行处理，不过根据用户的操作不同，MySQL执行SQL语句时也会存在些许差异，这里是指读操作和写操作，两者SQL的执行过程并不相同，下面先来看看select语句的执行过程。

3.1、查询SQL的执行过程

在分析查询SQL的执行流程之前，咱们先模拟一个案例，以便于后续分析：

-- SQL语句
SELECT user_id FROM `zz_user` WHERE user_sex = "男" AND user_name = "竹子④号";

-- 表数据
+---------+--------------+----------+-------------+
| user_id | user_name    | user_sex | user_phone  |
+---------+--------------+----------+-------------+
|       1 | 竹子①号      | 男       | 18888888888 |
|       2 | 竹子②号      | 男       | 13588888888 |
|       3 | 竹子③号      | 男       | 15688888888 |
|       4 | 熊猫①号      | 女       | 13488888888 |
|       5 | 熊猫②号      | 女       | 18588888888 |
|       6 | 竹子④号      | 男       | 17777777777 |
|       7 | 熊猫③号      | 女       | 16666666666 |
+---------+--------------+----------+-------------+

先上个SQL执行的完整流程图，后续再逐步分析每个过程：

SQL执行过程

• ①先将SQL发送给SQL接口，SQL接口会对SQL语句进行哈希处理。

• ②SQL接口在缓存中根据哈希值检索数据，如果缓存中有则直接返回数据。

• ③缓存中未命中时会将SQL交给解析器，解析器会判断SQL语句是否正确：

  • 错误：抛出1064错误码及相关的语法错误信息。

  • 正确：将SQL语句交给优化器处理，进入第④步。

• ④优化器根据SQL制定出不同的执行方案，并择选出最优的执行计划。

• ⑤工作线程根据执行计划，调用存储引擎所提供的API获取数据。

• ⑥存储引擎根据API调用方的操作，去磁盘中检索数据（索引、表数据....）。

• ⑦发生磁盘IO后，对于磁盘中符合要求的数据逐条返回给SQL接口。

• ⑧SQL接口会对所有的结果集进行处理（剔除列、合并数据....）并返回。

上述是一个简单的流程概述，一般情况下查询SQL的执行都会经过这些步骤，下面再将每一步拆开详细聊一聊。

SQL接口会干的工作

当客户端将SQL发送过来之后，SQL紧接着会交给SQL接口处理，首先会对SQL做哈希处理，也就是根据SQL语句计算出一个哈希值，然后去「查询缓存」中比对，如果缓存中存在相同的哈希值，则代表着之前缓存过相同SQL语句的结果，那此时则直接从缓存中获取结果并响应给客户端。

在这里，如果没有从缓存中查询到数据，紧接着会将SQL语句交给解析器去处理。

SQL接口除开对SQL进行上述的处理外，后续还会负责处理结果集。

解析器中会干的工作

解析器收到SQL后，会开始检测SQL是否正确，也就是做词法分析、语义分析等工作，在这一步，解析器会根据SQL语言的语法规则，判断客户端传递的SQL语句是否合规，如果不合规就会返回1064错误码及错误信息：

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

但如果SQL语句没有问题，此时就会对SQL语句进行关键字分析，也就是根据SQL中的SELECT、UPDATE、DELETE等关键字，先判断SQL语句的操作类型，是读操作还是写操作，然后再根据FROM关键字来确定本次SQL语句要操作的是哪张表，也会根据WHERE关键字后面的内容，确定本次SQL的一些结果筛选条件。

总之，经过关键字分析后，一条SQL语句要干的具体工作就会被解析出来。

解析了SQL语句中的关键字之后，解析器会根据分析出的关键字信息，生成对应的语法树，然后交给优化器处理。

在这一步也就相当于Java中的.java源代码变为.class字节码的过程，目的就是将SQL语句翻译成数据库可以看懂的指令。

优化器中会干的工作

经过解析器的工作后会得到一个SQL语法树，也就是知道了客户端的SQL大体要干什么事情了，接着优化器会对于这条SQL，给出一个最优的执行方案，也就是告诉工作线程怎么执行效率最高、最节省资源以及时间。

优化器最开始会根据语法树制定出多个执行计划，然后从多个执行计划中选择出一个最好的计划，交给工作线程去执行，但这里究竟是如何选择最优执行计划的，相信大家也比较好奇，那此时我们结合前面给出的案例分析一下。

SELECT user_id FROM `zz_user` WHERE user_sex = "男" AND user_name = "竹子④号";

先来看看，对于这条SQL而言，总共有几种执行方案呢？答案是两种。

• ①先从表中将所有user_sex="男"的数据查出来，再从结果中获取user_name="竹子④号"的数据。

• ②先从表中寻找user_name="竹子④号"的数据，再从结果中获得user_sex="男"的数据。

再结合前面给出的表数据，暂且分析一下上述两种执行计划哪个更好呢？

+---------+--------------+----------+-------------+
| user_id | user_name    | user_sex | user_phone  |
+---------+--------------+----------+-------------+
|       1 | 竹子①号      | 男       | 18888888888 |
|       2 | 竹子②号      | 男       | 13588888888 |
|       3 | 竹子③号      | 男       | 15688888888 |
|       4 | 熊猫①号      | 女       | 13488888888 |
|       5 | 熊猫②号      | 女       | 18588888888 |
|       6 | 竹子④号      | 男       | 17777777777 |
|       7 | 熊猫③号      | 女       | 16666666666 |
+---------+--------------+----------+-------------+

如果按照第①种方案执行，此时会先得到四条user_sex="男"的数据，然后再从四条数据中查找user_name="竹子④号"的数据。

如果按照第②中方案执行，此时会直接得到一条user_name="竹子④号"的数据，然后再判断一下user_sex是否为"男"，是则直接返回，否则返回空。

相较于两种执行方案的过程，前者需要扫一次全表，然后再对结果集逐条判断。而第二种方案扫一次全表后，只需要再判断一次就可以了，很明显可以感知出：第②种执行计划是最优的，因此优化器会给出第②种执行计划。

经过上述案例的讲解后，大家应该能够对优化器的工作进一步理解。但上述案例仅是为了帮助大家理解，实际的SQL优化过程会更加复杂，例如多表join查询时，怎么查更合适？单表复杂SQL查询时，有多条索引可以走，走哪条速度最快....，因此一条SQL的最优执行计划，需要结合多方面的优化策略来生成，例如MySQL优化器的一些优化准则如下：

• ❶多条件查询时，重排条件先后顺序，将效率更好的字段条件放在前面。

• ❷当表中存在多个索引时，选择效率最高的索引作为本次查询的目标索引。

• ❸使用分页Limit关键字时，查询到对应的数据条数后终止扫表。

• ❹多表join联查时，对查询表的顺序重新定义，同样以效率为准。

• ❺对于SQL中使用函数时，如count()、max()、min()等，根据情况选择最优方案。

  • max()函数：走B+树最右侧的节点查询（大的在右，小的在左）。

  • min()函数：走B+树最左侧的节点查询。

  • count()函数：如果是MyISAM引擎，直接获取引擎统计的总行数。

• ❻对于group by分组排序，会先查询所有数据后再统一排序，而不是一开始就排序。

总之，根据SQL不同，优化器也会基于不同的优化准则选择出最佳的执行计划。但需要牢记的一点是：**MySQL虽然有优化器，但对于效率影响最大的还是SQL本身，因此编写出一条优秀的SQL，才是提升效率的最大要素**。

存储引擎中会干的工作

经过优化器后，会得到一个最优的执行计划，紧接着工作线程会根据最优计划，去依次调用存储引擎提供的API，存储引擎主要就是负责在磁盘读写数据的，不同的存储引擎，存储在本地磁盘中的数据结构也并不相同，但这些底层实现并不需要MySQL的上层服务关心，因为上层服务只需要负责调用对应的API即可，存储引擎的API功能都是相同的。

工作线程根据执行计划调用存储引擎的API查询指定的表，最终也就是会发生磁盘IO，从磁盘中检索数据，当然，检索的数据有可能是磁盘中的索引文件，也有可能是磁盘中的表数据文件，这点要根据执行计划来决定，我们只需要记住，经过这一步之后总能够得到执行结果即可。

还记得最开始创建数据库连接时，对登录用户的授权步骤嘛？当工作线程去尝试查询某张表时，会首先判断一下线程自身维护的客户端连接，其登录的用户是否具备这张表的操作权限，如果不具备则会直接返回权限不足的错误信息。

不过存储引擎从磁盘中检索出目标数据后，并不会将所有数据全部得到后再返回，而是会逐条返回给SQL接口，然后会由SQL接口完成最后的数据聚合工作。

3.2、写入SQL的执行过程

   假设此时要执行下述这一条写入类型的SQL语句（还是基于之前的表数据）：

UPDATE `zz_user` SET user_sex = "女" WHERE user_id = 6;

上面这条SQL是一条典型的修改SQL，但除开修改操作外，新增、删除等操作也属于写操作，写操作的意思是指会对表中的数据进行更改。

相较于查询SQL，写操作的SQL执行流程明显会更复杂一些，这里也先简单总结一下每一步流程，然后再详细分析一下其中一些与查询SQL中不同的步骤：

• ①先将SQL发送给SQL接口，SQL接口会对SQL语句进行哈希处理。

• ②在缓存中根据哈希值检索数据，如果缓存中有则将对应表的所有缓存全部删除。

• ③经过缓存后会将SQL交给解析器，解析器会判断SQL语句是否正确：

  • 错误：抛出1064错误码及相关的语法错误信息。

  • 正确：将SQL语句交给优化器处理，进入第④步。

• ④优化器根据SQL制定出不同的执行方案，并择选出最优的执行计划。

• ⑤在执行开始之前，先记录一下undo-log日志和redo-log(prepare状态)日志。

• ⑥在缓冲区中查找是否存在当前要操作的行记录或表数据（内存中）：

  • 不存在：⑦根据执行计划，调用存储引擎的API。

  • ⑧发生磁盘IO，对磁盘中的数据做写操作。

  • 存在：⑦直接对缓冲区中的数据进行写操作。

  • ⑧然后利用Checkpoint机制刷写到磁盘。

• ⑨写操作完成后，记录bin-log日志，同时将redo-log日志中的记录改为commit状态。

• ⑩将SQL执行耗时及操作成功的结果返回给SQL接口，再由SQL接口返回给客户端。

整个写SQL的执行过程，前面的一些步骤与查SQL执行的过程没太大差异，唯一一点不同的在于缓存那里，原本查询时是从缓存中尝试获取数据。而写操作时，由于要对表数据发生更改，因此如果在缓存中发现了要操作的表存在缓存，则需要将整个表的所有缓存清空，确保缓存的强一致性。

唯一性判断主要是针对插入、修改语句来说的，因为如果表中的某个字段建立了唯一约束或唯一索引后，当插入/修改一条数据时，就会先检测一下目前插入/修改的值，是否与表中的唯一字段存在冲突，如果表中已经存在相同的值，则会直接抛出异常，反之会继续执行。

由于CPU和磁盘之间的性能差距实在过大，因此MySQL中会在内存中设计一个「缓冲区」的概念，主要目的是在于弥补CPU与磁盘之间的性能差距。

缓冲区中会做的工作

在真正调用存储引擎的API操作磁盘之前，首先会在「缓冲区」中查找有没有要操作的目标数据/目标表，如果存在则直接对缓冲区中的数据进行操作，然后MySQL会在后台以一种名为Checkpoint的机制，将缓冲区中更新的数据刷回到磁盘。只有当缓冲区没有找到目标数据时，才会去真正调用存储引擎的API，然后发生磁盘IO，去对应磁盘中的表数据进行修改。

虽然缓冲区中有数据时会先操作缓冲区，然后再通过Checkpoint机制刷写磁盘，但这两个过程不是连续的。也就是说，当线程对缓冲区中的数据操作完成后，会直接往下走，数据落盘的工作则会交给后台线程。
不过虽然两者之间是异步的，但对于人而言，这个过程不会有太大的感知，毕竟CPU在运行的时候，都是按纳秒、微秒级作为单位。

但不管数据是在缓冲区还是磁盘，本质上数据更改的动作都是发生在内存的，就算是修改磁盘数据，也是将数据读到内存中操作，然后再将数据写回磁盘。不过在「写SQL」执行的前后都会记录日志。

写操作时的日志

执行「读SQL」一般都不会有状态，也就是说：MySQL执行一条select语句，几乎不会留下什么痕迹。但这里为什么用几乎这个词呢？因为查询时也有些特殊情况会留下“痕迹”，就是慢查询SQL：

**慢查询SQL**：查询执行过程耗时较长的SQL记录。
在执行查询SQL时，大多数的普通查询MySQL并不关心，但慢查询SQL除外，这类SQL一般是引起响应缓慢问题的“始作俑者”，所以当一条查询SQL的执行时长超过规定的时间限制，就会被“记录在案”，也就是会记录到慢查询日志中。

与「查询SQL」恰恰相反，任何一条写入类型的SQL都是有状态的，也就代表着只要是会对数据库发生更改的SQL，执行时都会被记录在日志中。首先所有的写SQL在执行之前都会生成对应的撤销SQL，撤销SQL也就是相反的操作，比如现在执行的是insert语句，那这里就生成对应的delete语句....，然后记录在undo-log撤销/回滚日志中。但除此之外，还会记录redo-log日志。

redo-log日志是InnoDB引擎专属的，主要是为了保证事务的原子性和持久性，这里会将写SQL的事务过程记录在案，如果服务器或者MySQL宕机，重启时就可以通过redo_log日志恢复更新的数据。在「写SQL」正式执行之前，就会先记录一条prepare状态的日志，表示当前「写SQL」准备执行，然后当执行完成并且事务提交后，这条日志记录的状态才会更改为commit状态。

除开上述的redo-log、undo-log日志外，同时还会记录bin-log日志，这个日志和redo-log日志很像，都是记录对数据库发生更改的SQL，只不过redo-log是InnoDB引擎专属的，而bin-log日志则是MySQL自带的日志。

不过无论是什么日志，都需要在磁盘中存储，而本身「写SQL」在磁盘中写表数据效率就较低了，此时还需写入多种日志，效率定然会更低。对于这个问题MySQL以及存储引擎的设计者自然也想到了，所以大部分日志记录也是采用先写到缓冲区中，然后再异步刷写到磁盘中。

比如redo-log日志在内存中会有一个redo_log缓冲区中，bin-log日志也同理，当需要记录日志时，都是先写到内存中的缓冲区。

那内存中的日志数据何时会刷写到磁盘呢？对于这点则是由刷盘策略来决定的，redo-log日志的刷盘策略由innodb_flush_log_at_trx_commit参数控制，而bin-log日志的刷盘策略则可以通过sync_binlog参数控制：

• innodb_flush_log_at_trx_commit：

  • 0：有事务提交的情况下，每间隔一秒时间刷写一次日志到磁盘。

  • 1：每次提交事务时，都刷写一次日志到磁盘（性能最差，最安全，默认策略）。

  • 2：每当事务提交时，把日志记录放到内核缓冲区，刷写的时机交给OS控制（性能最佳）。

• sync_binlog：

  • 0：同上述innodb_flush_log_at_trx_commit参数的2，交给OS控制（默认）。

  • 1：同上述innodb_flush_log_at_trx_commit参数的1，每次提交事务都会刷盘。

SQL执行完成后是如何返回的？

一条「读SQL」或「写SQL」执行完成后，由于SQL操作的属性不同，两者之间也会存在差异性。

4.1、读类型的SQL返回

MySQL执行一条查询SQL时，数据是逐条返回的模式，因为如果等待所有数据全部查出来之后再一次性返回，必然会导致撑满内存。

不过这里的返回，并不是指返回客户端，而是指返回SQL接口，因为从磁盘中检索出目标数据时，一般还需要对这些数据进行再次处理，举个例子理解一下。

SELECT user_id FROM `zz_user` WHERE user_sex = "男" AND user_name = "竹子④号";

还是之前那条查询SQL，这条SQL要求返回的结果字段仅有一个user_id，但在磁盘中检索数据时，会直接将这个字段单独查询出来吗？并不是的，而是会将整条行数据全部查询出来，如下：

+---------+--------------+----------+-------------+
| user_id | user_name    | user_sex | user_phone  |
+---------+--------------+----------+-------------+
|    6    |   竹子④号    |    男    | 17777777777 |
+---------+--------------+----------+-------------+

从行记录中筛选出最终所需的结果字段，这个工作是在SQL接口中完成的，也包括多表联查时，数据的合并工作，同样也是在SQL接口完成，其他SQL亦是同理。

还有一点需要牢记：就算没有查询到数据，也会将执行状态、执行耗时这些信息返回给SQL接口，然后由SQL接口向客户端返回NULL。

不过当查询到数据后，在正式向客户端返回之前，还会顺手将结果集放入到缓存中。

4.2、写类型的SQL返回

写SQL执行的过程会比读SQL复杂，但写SQL的结果返回却很简单，写类型的操作执行完成之后，仅会返回执行状态、受影响的行数以及执行耗时，比如：

UPDATE `zz_user` SET user_sex = "女" WHERE user_id = 6;

这条SQL执行成功后，会返回Query OK, 1 row affected (0.00 sec)这组结果，最终返回给客户端的则只有「受影响的行数」，如果写SQL执行成功，这个值一般都会大于0，反之则会等于0。

4.3、执行结果是如何返回给客户端的？

对于这个问题的答案其实很简单，由于执行当前SQL的工作线程，本身也维护着一个数据库连接，这个数据库连接实际上也维持着客户端的网络连接。当结果集处理好了之后，直接通过Host中记录的地址，将结果集封装成TCP数据报，然后返回即可。

数据返回给客户端之后，除非客户端主动输入exit等退出连接的命令，否则连接不会立马断开。

如果要断开客户端连接时，又会经过TCP四次挥手的过程。

不过就算与客户端断开了连接，MySQL中创建的线程并不会销毁，而是会放入到MySQL的连接池中，等待其他客户端复用当前连接。一般情况下，一条线程在八小时内未被复用，才会触发MySQL的销毁工作。

MySQL 如何实现将数据实时同步到 ES ？

1. 数据双写

这个方案应该是比较常用的，即在写入数据的时候，先将数据写入 MySQL 然后在将数据写入 ES，这种方案实现起来比较简单，但是如果你在写入 MySQL 之后，服务发生了宕机，这个时候就可能产生不一致的情况，这个时候就可能需要重新进行写入。

伪代码如下：

/**
  * 新增商品
*/
@Transactional(rollbackFor = Exception.class)
public void addGoods(GoodsDto goodsDto) {
    //1、保存Mysql
    Goods goods = new Goods();
    BeanUtils.copyProperties(goodsDto,goods);
    GoodsMapper.insert();
    
    //2、保存ES
    IndexRequest indexRequest = new IndexRequest("goods_index","_doc");
    indexRequest.source(JSON.toJSONString(goods), XContentType.JSON);
    indexRequest.setRefreshPolicy(WriteRequest.RefreshPolicy.IMMEDIATE);
    highLevelClient.index(indexRequest);
}

这个方案的优缺点如下：

• 优点：

  • 实现起来逻辑简单，而且实时性较高

• 缺点：

  • 硬编码问题严重，并且业务耦合程度高

  • 如果服务或者 Elasticsearch 发生宕机情况，就有数据丢失的风险

2. MQ 异步同步

在执行完 MySQL 的写入操作之后，将操作交给 MQ，然后通过 MQ 告诉 ES 需要进行数据的同步。

这个方案优缺点如下：

• 优点：

  • 这个方案最直接的点就是性能高，并且实现了业务的解耦合，并且可以利用 MQ 的重试机制，在写入失败的时候进行重试，降低了数据丢失的风险。

  • 这样还支持多个数据源的写入，提高了扩展性，不会出现由于单个数据源写入异常从而导致其他数据源写入受到影响的问题。

• 缺点：

  • 硬编码问题，在接入新的数据源的时候需要实现新的消费者代码，代码侵入性较强

  • 引入了消息队列，提高了运维的成本，增加了系统的复杂程度

  • 可能出现延时问题，因为消息队列是异步消费模型，用户写入的数据不一定可以马上看到结果，有一定的延迟。

3. 基于 Binlog 实现数据同步

上面的这两个方案总结下来就两个问题，第一个问题就是硬编码并且代码侵入性较强，另外一个点就是没有办法实现数据的实时同步，那么有没有其他方案，答案肯定是有的，那就是利用 MySQL 的 Binlog 日志来实现数据同步，如下图所示：

具体步骤如下：

• 优点：

  • 没有代码侵入，没有硬编码

  • 原有的系统没有任何变化，可以实现无感知，性能较高

  • 业务解耦合，这个和消息队列是差不多实现思路的，不过这个不需要关注原来系统的业务实现

• 缺点：

  • 如果采用 MQ 消费解析 Binlog 日志的话，又会回到方案二引入消息队列产生的问题

针对以上方案，我们可以使用一种新的解决方案，就是阿里巴巴开源的 Canal 中间件。

Canal 方案

什么是 Canal ？

Canal：译意为水道/管道/沟渠，主要用途是基于 MySQL 数据库增量日志解析，提供增量数据订阅和消费。

根据 MySQL 的 BinLog 日志进行增量同步数据。要理解 Canal 的原理，就要先了解 MySQL 的主从复制原理，如下：

1. MySQL 所有的增删改操作都会进入MySQL 主从复杂的主节点，即 Master 节点。

2. Master 节点会生成 Binlog 日志文件，每次操作 MySQL 数据库就会记录到 Binlog 日志文件中。

3. Slave 节点会订阅 Master 节点的 Binlog 日志，以增量备份的形式同步数据到 Slave 数据。

Canal 同步流程

Canal 的原理就是伪装成 MySQL主从复制的从节点（Slave 节点），从而订阅 MySQL主节点的 Binlog日志，主要流程如下：

1. Canal 服务端向 MySQL 的 Master 节点传输 Dump 协议

2. MySQL 的 Master 节点接收到 Dump 请求后推送 Binlog日志给 Canal 服务端，解析 Binlog 日志对象（原始为字节流对象），然后转换成 JSON 数据格式

3. Canal 客户端通过 TCP 协议或 MQ 形式监听 Canal 服务端，然后将同步数据到ES，到此，同步过程就完成了。

总结

1）数据双写是最简单的实现方式，可以最大程度的保证数据的同步写入，不过问题很明显，就是代码侵入性太强了，而且容易因为中间件故障导致写入失败。

2）MQ 异步同步，这个方案引入了消息队列，实现了业务的解耦合，而且性能较高，吞吐量也比较大，并且支持多数据源的数据同步，不过由于 MQ 是异步消费模型，所以可能出现数据同步延迟的情况，所以实时性要求比较高的场景可能没办法实现。

3）基于 Binlog 日志实现，主要原理就是通过监听 MySQL 的 Binlog 日志数据实现增量数据同步，这个方案其基本不会产生代码侵入，而且数据同步的实时性也有一定的保障，不过弊端也比较明显，就是 Binlog 系统的实现可能较为复杂，所以可以考虑使用第三方日志同步组件，最典型的实现就是 Canal 实现。

其他补充

使用 Canal 进行 MySQL 到 Elasticsearch 数据同步具有多方面的好处，同时也可能面临一些潜在的问题。

1. 实时性高: Canal 可以实时监听 MySQL 的 Binlog 日志变更，将变更数据迅速同步到 Elasticsearch。这种实时性能够满足许多对数据更新要求较高的场景，如搜索引擎、实时分析等。

2. 无代码侵入: 使用 Canal 进行数据同步，不需要在应用程序中添加额外的写入逻辑或代码。它作为一个独立的服务监听 Binlog，对原有系统没有侵入性，不会修改原有的业务逻辑。

3. 减少系统负担: 相比于直接在应用程序中实现数据双写或通过消息队列进行异步同步，Canal 可以减少数据库的负担。它直接监听 Binlog，而不需要额外的数据库读取操作，因此对数据库性能的影响较小。

4. 支持增量同步: Canal 的核心功能是基于 MySQL 的增量日志（Binlog）进行数据同步。这意味着可以精确捕获每一次数据变更，避免了全量同步的性能瓶颈和数据一致性问题。

5. 灵活性和可扩展性: Canal 支持多种数据源的同步，并且可以配置多个消费端，从而提高了系统的扩展性和灵活性。可以根据具体需求灵活配置同步的规则和目标。

6. 开源社区支持: Canal 是阿里巴巴开源的项目，在社区中有较为活跃的维护和支持，可以从社区中获取到丰富的文档、问题解答和更新。

尽管使用 Canal 可以带来诸多好处，但也有一些可能的问题需要注意：

1. 性能消耗: Canal 需要实时监听 MySQL 的 Binlog，并且将数据转发到目标系统（如 Elasticsearch）。在高并发或大数据量的情况下，可能会对服务器资源（CPU、内存、网络带宽）造成一定压力。

2. 部署和维护复杂性: Canal 作为一个独立的服务需要单独部署和维护，这增加了系统的复杂性和运维成本。特别是在需要监控、调优和升级时，可能需要专门的运维人员或团队来管理。

3. 数据一致性: 尽管 Canal 本身是为了实现数据同步而设计的，但在实际应用中，仍然需要注意数据一致性的问题。特别是在网络传输或数据处理过程中可能出现的中断、延迟或错误，都有可能导致数据同步的不一致。

4. 版本兼容性: Canal 的版本更新可能会引入新的功能或修复问题，但同时也可能带来与现有环境不兼容的风险。在升级 Canal 版本时需要谨慎测试和规划，以确保不影响现有业务的稳定性。

5. 安全性: Canal 需要连接到 MySQL 数据库获取 Binlog 日志，因此在安全设置方面需要注意，确保 Canal 的访问权限受到正确的限制和保护，避免因权限问题导致的安全风险。

综上所述，使用 Canal 进行 MySQL 到 Elasticsearch 的数据同步是一种高效、实时的解决方案，但需要在性能、部署复杂性、数据一致性和安全性等方面进行综合考量和管理。

慢 SQL 监控

在开发和维护数据库驱动的应用程序时，监控慢 SQL 查询是确保系统性能和稳定性的关键一环。慢 SQL 查询可能会导致系统性能下降、资源浪费和用户体验差等问题。因此，及时监控和优化慢 SQL 查询对于保障系统的正常运行和用户满意度至关重要。

数据库系统自带的性能监控工具

许多数据库系统都提供了自带的性能监控工具，如 MySQL 的 Performance Schema、PostgreSQL 的 pg_stat_statements 等。这些工具可以跟踪 SQL 查询的执行时间、访问频率、IO 操作等关键指标，并生成性能报告和统计信息，帮助开发人员识别慢 SQL 查询。

Performance Schema 的功能特性

1）性能监控： Performance Schema 可以监控 MySQL 数据库的各种性能指标，包括 SQL 查询的执行时间、锁等待、IO 操作、线程状态、连接状态等。

2）统计信息： Performance Schema 收集并统计数据库的性能数据，生成各种性能报告和统计信息，帮助开发人员了解数据库的性能状况。

3）性能分析： Performance Schema 提供了丰富的性能数据和分析工具，可以帮助开发人员识别和分析慢查询、性能瓶颈和资源消耗等问题。

4）动态配置： Performance Schema 允许开发人员动态配置监控项和采样频率，以满足不同场景下的性能监控需求。

Performance Schema 的使用

要启用 Performance Schema，需要确保 MySQL 的版本在5.5及以上，并且在编译 MySQL 时启用 Performance Schema 功能。启用 Performance Schema 后，可以通过 MySQL 的命令行客户端或者其他 MySQL 管理工具来查看和分析数据库的性能数据。

以下是一些常用的 Performance Schema 命令和操作：

1）查看 Performance Schema 的配置信息：SHOW VARIABLES LIKE 'performance_schema%'

2）查看 Performance Schema 的监控项：SELECT * FROM performance_schema.setup_instruments;

3）查看 Performance Schema 收集的性能数据：SELECT * FROM performance_schema.events_statements_summary_by_digest;

慢查询日志

在 my.cnf 或 my.ini 中添加如下配置

[mysqld]
slow_query_log = 1
slow_query_log_file = /path/to/slow-query.log
long_query_time = 1

分析慢查询日志可以用 MySQL 提供的工具

mysqldumpslow /path/to/slow-query.log

一旦识别出执行时间较长的 SQL 查询，可以考虑优化这些查询以提高数据库的性能。

方式如下

• 添加合适的索引以加速查询。

• 优化 SQL 查询语句，减少不必要的查询和计算。

• 调整数据库配置参数以优化性能。

慢查询日志脚本监控

import re
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart

# MySQL慢查询日志文件路径
slow_log_path = "/var/log/mysql/mysql-slow.log"

# 邮件配置
smtp_server = 'smtp.example.com'
smtp_port = 587
smtp_user = 'your_username'
smtp_password = 'your_password'
sender_email = 'your_email@example.com'
receiver_email = 'recipient@example.com'

# 设置慢查询阈值（单位：秒）
slow_query_threshold = 5

def send_email(subject, body):
    msg = MIMEMultipart()
    msg['From'] = sender_email
    msg['To'] = receiver_email
    msg['Subject'] = subject
    msg.attach(MIMEText(body, 'plain'))

    with smtplib.SMTP(smtp_server, smtp_port) as server:
        server.starttls()
        server.login(smtp_user, smtp_password)
        server.sendmail(sender_email, receiver_email, msg.as_string())

def monitor_slow_queries():
    with open(slow_log_path, 'r') as log_file:
        for line in log_file:
            if line.startswith('# Time:'):
                query_time = float(line.split(':')[-1].strip())
            elif line.startswith('# Query_time:'):
                query_time = float(re.findall(r'\d+\.\d+', line)[0])
                if query_time > slow_query_threshold:
                    send_email('Slow Query Alert', f'A slow query with execution time {query_time} seconds was detected.')
                    # 可以在这里添加更多处理逻辑，如记录日志、执行优化操作等

if __name__ == "__main__":
    monitor_slow_queries()

其他补充

1. 查询语句准备

首先，准备要分析的查询语句。这可以是任何 SELECT 查询，通常是在开发或优化阶段遇到的性能瓶颈较大的查询。

2. 使用 EXPLAIN

使用 EXPLAIN 关键字来执行查询语句，如下所示：

EXPLAIN SELECT * FROM table_name WHERE condition;

3. 分析执行计划

执行 EXPLAIN 后，MySQL 将返回一组关于查询执行计划的信息。这些信息通常包括以下内容：

• id： 查询标识符，用于标识查询的顺序。

• select_type： 查询的类型，如 SIMPLE、PRIMARY、SUBQUERY 等。

• table： 查询涉及的表。

• type：

  访问类型，表示 MySQL 在表中找到所需行的方式。常见的类型有：

  • ALL： 全表扫描，需要检查表中的每一行。

  • index： 使用索引扫描，但是需要读取索引的全部内容。

  • range： 使用索引来确定范围，只检索给定范围内的行。

  • ref： 使用非唯一索引来查找匹配某个值的行。

  • const： 使用常量值来访问表中的一行。

• possible_keys： 可能使用的索引。

• key： 实际使用的索引。

• key_len： 使用的索引的长度。

• ref： 表示索引的哪一列与查询的哪一列进行了比较。

• rows： 估计需要检查的行数。

• Extra： 额外信息，如使用了临时表、使用了文件排序等。

4. 根据结果进行优化

根据 EXPLAIN 的结果，你可以分析查询的执行计划，发现可能存在的性能瓶颈，并进行相应的优化。一些常见的优化方法包括：

• 确保查询涉及的列上有合适的索引。

• 尽量避免全表扫描，优先选择索引扫描或范围扫描。

• 使用覆盖索引来减少访问表的次数。

• 减少查询结果集的大小，只返回必要的列。

• 避免在 WHERE 子句中使用函数，以免影响索引的使用。

• 根据查询的实际情况调整表结构和索引设计。

7个常见的SQL慢查询问题，及其解决方法

1. LIMIT语句

分页是最常用的方案之一，但也容易出现问题。例如，对于以下简单的语句，DBA通常建议的解决方案是添加一个包含type、name和create_time字段的复合索引。这样，条件和排序就可以有效利用索引，从而显著提高性能。

SELECT *
FROM   operation
WHERE  type = 'SQLStats'
       AND name = 'SlowLog'
ORDER  BY create_time
LIMIT  1000, 10;

这可能会解决90%以上DBA的问题。但是，当LIMIT子句变成“LIMIT 1000000, 10”时，程序员仍会抱怨“为什么在只查询10条记录的时候，速度还这么慢?” 要知道，数据库不知道第1000000条记录从何处开始，所以即使有索引，它仍需要从头开始计算。在大多数情况下，这个性能问题是由于懒惰编程造成的。

在前端数据浏览或批量导出大量数据的场景中，可以使用上一页的最大值作为查询参数。SQL可以重新设计如下:

SELECT   *
FROM     operation
WHERE    type = 'SQLStats'
AND      name = 'SlowLog'
AND      create_time > '2017-03-16 14:00:00'
ORDER BY create_time
LIMIT    10;

采用这种新设计后，查询时间保持不变，不会随着数据量的增加而变化。

2. 隐式转换

SQL语句中另一个常见的错误是查询变量和字段定义的类型不匹配。以下面的语句为例：

mysql> explain extended SELECT *
     > FROM   my_balance b
     > WHERE  b.bpn = 14000000123
     >       AND b.isverified IS NULL ;
mysql> show warnings;
| Warning | 1739 | Cannot use ref access on index 'bpn' due to type or collation conversion on field 'bpn'

在这种情况下，字段bpn被定义为varchar(20)，而MySQL的策略是在比较之前将字符串转换为数字。这会导致函数被应用到表字段上，从而使索引失效。

这种情况可能是由应用程序框架自动填充参数造成的，而不是程序员的本意。如今，应用程序框架通常都很复杂，虽然它们提供了便利，但也可能带来隐患。

3. 连接更新和删除

尽管MySQL 5.6引入了物化，但它只优化了SELECT语句。对于UPDATE或DELETE语句，需要使用JOIN手动重写。

例如，请看下面的UPDATE语句。MySQL实际上执行了一个循环/嵌套子查询(DEPENDENT SUBQUERY)，执行时间可想而知。

UPDATE operation o
SET    status = 'applying'
WHERE  o.id IN (SELECT id
                FROM   (SELECT o.id,
                               o.status
                        FROM   operation o
                        WHERE  o.group = 123
                               AND o.status NOT IN ( 'done' )
                        ORDER  BY o.parent,
                                  o.id
                        LIMIT  1) t);

执行计划如下:

+----+--------------------+-------+-------+---------------+---------+---------+-------+------+-----------------------------------------------------+
| id | select_type        | table | type  | possible_keys | key     | key_len | ref   | rows | Extra                                               |
+----+--------------------+-------+-------+---------------+---------+---------+-------+------+-----------------------------------------------------+
| 1  | PRIMARY            | o     | index |               | PRIMARY | 8       |       | 24   | Using where; Using temporary                        |
| 2  | DEPENDENT SUBQUERY |       |       |               |         |         |       |      | Impossible WHERE noticed after reading const tables |
| 3  | DERIVED            | o     | ref   | idx_2,idx_5   | idx_5   | 8       | const | 1    | Using where; Using filesort                         |
+----+--------------------+-------+-------+---------------+---------+---------+-------+------+-----------------------------------------------------+

将其重写为JOIN后，子查询的选择类型从DEPENDENT SUBQUERY变为DERIVED，执行时间显著得从7秒缩短到2毫秒。

UPDATE operation o
       JOIN  (SELECT o.id,
                            o.status
                     FROM   operation o
                     WHERE  o.group = 123
                            AND o.status NOT IN ( 'done' )
                     ORDER  BY o.parent,
                               o.id
                     LIMIT  1) t
         ON o.id = t.id
SET    status = 'applying';

简化后的执行计划如下:

+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key   | key_len | ref   | rows | Extra                                               |
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------------------------------+
| 1  | PRIMARY     |       |      |               |       |         |       |      | Impossible WHERE noticed after reading const tables |
| 2  | DERIVED     | o     | ref  | idx_2,idx_5   | idx_5 | 8       | const | 1    | Using where; Using filesort                         |
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------------------------------+

4. 混合排序

MySQL无法利用索引进行混合排序。但是，在某些场景下，仍然可以使用特殊方法来提高性能。

SELECT *
FROM   my_order o
       INNER JOIN my_appraise a ON a.orderid = o.id
ORDER  BY a.is_reply ASC,
          a.appraise_time DESC
LIMIT  0, 20;

执行计划显示的是全表扫描:

+----+-------------+-------+--------+-------------+---------+---------+---------------+---------+-+
| id | select_type | table | type   | possible_keys     | key     | key_len | ref      | rows    | Extra
+----+-------------+-------+--------+-------------+---------+---------+---------------+---------+-+
|  1 | SIMPLE      | a     | ALL    | idx_orderid | NULL    | NULL    | NULL    | 1967647 | Using filesort |
|  1 | SIMPLE      | o     | eq_ref | PRIMARY     | PRIMARY | 122     | a.orderid |       1 | NULL           |
+----+-------------+-------+--------+---------+---------+---------+-----------------+---------+-+

由于is_reply只有0和1两种状态，我们可以将其重写如下，从而将执行时间从1.58秒缩短到2毫秒:

SELECT *
FROM   ((SELECT *
         FROM   my_order o
                INNER JOIN my_appraise a
                        ON a.orderid = o.id
                           AND is_reply = 0
         ORDER  BY appraise_time DESC
         LIMIT  0, 20)
        UNION ALL
        (SELECT *
         FROM   my_order o
                INNER JOIN my_appraise a
                        ON a.orderid = o.id
                           AND is_reply = 1
         ORDER  BY appraise_time DESC
         LIMIT  0, 20)) t
ORDER  BY  is_reply ASC,
          appraisetime DESC
LIMIT  20;

5. EXISTS语句

在处理EXISTS子句时，MySQL仍然使用嵌套子查询进行执行。以下面的SQL语句为例:

SELECT *
FROM   my_neighbor n
       LEFT JOIN my_neighbor_apply sra
              ON n.id = sra.neighbor_id
                 AND sra.user_id = 'xxx'
WHERE  n.topic_status < 4
       AND EXISTS(SELECT 1
                  FROM   message_info m
                  WHERE  n.id = m.neighbor_id
                         AND m.inuser = 'xxx')
       AND n.topic_type <> 5;

+----+--------------------+-------+------+-----+------------------------------------------+---------+-------+---------+ -----+
| id | select_type        | table | type | possible_keys | key     | key_len | ref      | rows    | Extra
+----+--------------------+-------+------+ -----+------------------------------------------+---------+-------+---------+ -----+
|  1 | PRIMARY            | n     | ALL  |  | NULL     | NULL    | NULL    | 1086041 | Using where                   |
|  1 | PRIMARY            | sra   | ref  |  | idx_user_id | 123     | const |       1 | Using where          |
|  2 | DEPENDENT SUBQUERY | m     | ref  |  | idx_message_info   | 122     | const |       1 | Using index condition; Using where |
+----+--------------------+-------+------+ -----+------------------------------------------+---------+-------+---------+ -----+

通过删除EXISTS子句并将其更改为JOIN, 我们可以避免嵌套子查询，并将