Vincent的博客

前面我们介绍过MySQL中的衍生表（From子句中的子查询）和它的局限性，MySQL8.0.14引入了横向衍生表，可以在子查询中引用前面出现的表，即根据外部查询的每一行动态生成数据，这个特性在衍生表非常大而最终结果集不需要那么多数据的场景使用，可以大幅降低执行成本。

在SQL的查询语句select …. from …中，跟在from子句后面的通常是一张拥有定义的实体表，而有的时候我们会用子查询来扮演实体表的角色，这个在from子句中的子查询会返回一个结果集，这个结果集可以像普通的实体表一样查询、连接，这个子查询的结果集就叫做衍生表。

我们日常在使用 insert into 语句向表中插入数据时，一定遇到过主键或唯一索引冲突的情况，当遇到这种情况时，MySQL默认的反应是报错并停止执行后续的语句，为了避免这种情况，你有3种选择。

我们日常使用 insert into 语句向表中插入数据时，一定遇到过主键或唯一索引冲突的情况，MySQL的反应是报错并停止执行后续的语句，而replace into语句可以实现强制插入。

MySQL的table语句可以查询指定表中的所有列，相比于select语句，写法更简单，但功能也更弱，适合快速查看小表数据的场景。

mysql_config_editor是MySQL自带的用户凭证管理工具，当安装完MySQL后，它就已经装好了。它的功能是加密管理用户连接信息，当使用其他的客户端工具例如mysql，mysqldump，mysqladmin连接数据库时，可以直接读取这些加密的连接信息，而不需要用户输入。

MySQL的load data infile语句可以从文本文件中读取数据，并且加载到数据库的表中。和select…into outfile只能导文件到本地数据库服务器不同，load data语句即可以从数据库服务器本地读取文件，也可以通过远程客户端（使用local关键字）读取，即可以远程将文件加载到数据库中。MySQL还提供了一个mysqlimport命令行工具也可以将数据从文件加载到数据库中，其原理也是通过load data infile语句完成的。

我们经常会遇到需要将SQL查询结果导出到文件，以便后续的传输或数据分析的场景。为了满足这个需求，MySQL的select语句提供了into子句可以将的查询结果直接导出到文本文件。本文就MySQL中select…into的用法进行演示。

在使用MySQL数据库的的过程中，不可避免的会出现忘记密码的现象。普通用户的密码如果忘记，可以用更高权限的用户（例如root）进行重置。但是如果root用户的密码忘记了，由于root用户本身就是最高权限，那这个方法就行不通了。本文介绍2种在忘记root用户用户密码的情况下，如何进行重设。

在MySQL中，如果只想获取select查询结果的一部分，可以使用limit子句来限制返回记录的数量，limit在获取到满足条件的数据量时即会立刻终止SQL的执行。相比于返回所有数据然后丢弃一部分，执行效率会更高。

在数据库应用中，很多SQL语句都会重复执行很多次，每次执行可能只是where条件中的变量值不同，但MySQL依然会解析SQL语法并生成执行计划。对于这类情况，可以利用prepared语句来避免重复解析SQL的开销。

count()是SQL中一个常用的聚合函数，其被用来统计记录的总数，下面通过几个示例来说明此类查询的注意事项及应用技巧。

MySQL连接控制是一个安全插件，当客户端出现指定次数的连接失败时（密码错误），之后的每次连接请求的响应都会逐渐增加延迟，此插件可以帮助数据库抵御类似DDOS攻击或暴力破解密码。

MySQL中有大量的系统变量控制服务器的行为，大部分的系统变量是不需要我们调整的，保持默认即可。但为了获得更高的性能和稳定性，有时需要适当对部分变量进行调整，本文总结了MySQL中系统变量的查看与设置方法。

MySQL8.0推出了专用数据库服务器自动配置参数，通过打开innodb_dedicated_server，数据库会自己完成缓冲池大小，重做日志，磁盘刷新方式等一系列配置，且配置还会根据服务器的配置升级自行调整。

"元数据"是用来描述数据对象定义的，而元数据锁（Metadata Lock MDL）即是加在这些定义上。通常我们认为非锁定一致性读（简单select）是不加锁的，这个是基于表内数据层面，其依然会对表的元数据加锁，保证读取数据期间表结构不会变更。

MySQL数据库为列提供了一种自增属性，当列被定义为自增时。Insert语句对该列即使不提供值，MySQL也会自动为该列生成递增的唯一标识，因此这个特性广泛用于主键的自动生成。

在MySQL的运行中，经常会遇到一些长事务。长事务意味着长时间持有系统资源，这在OLAP系统中很常见，但在OLTP系统中，长事务意味着争用、并发降低，等待。

JSON（JavaScript Object Notation）是一种常见的信息交换格式，其简单易读且非常适合程序处理。MySQL从5.7版本开始支持JSON数据类型，本文对MySQL中JSON数据类型的使用进行一个总结。

在MySQL中，innodb是按照表的聚簇索引（主键）来组织数据存储的，也就是主键的顺序决定了数据存储的顺序。这也是为什么我们通常推荐用整型，自增的数字来作为表的主键，当新数据插入时，主键一定是最大的，只要放在叶子层中最后的数据页即可，对已有的数据不会有影响。可以看到uuid作为主键的长度是146，而数字做主键的长度为4，这意味着当数据量非常大的时候，UUID的索引会非常臃肿，查询性能会很低。

在'pattern'中，可以使用'_'来匹配单个字符或'%'来匹配任意字符串（包含空字符，但不会匹配null）。like 匹配的方式可以完成一些简单的模糊查询，例如你可以用%abc%来匹配任意包含abc的数据，但是如果问题换成：包含a或b或c，那么你就要写3次匹配，如果问题更复杂一点（例如匹配特定次数），like可能就无法完成了，此时你就需要采用正则表达式匹配。需要注意的一点是，如果匹配的字段上有索引，如果遇到'%str'这种将%放在模式开头，那么将会导致索引失效，使用中需要斟酌一下性能。

MySQL的账号由User和Host两部分组成，其格式为'User'@'Host'，其中Host部分限制了用户可以登录的地址。在登录的时候，MySQL需要对我们的账号进行验证，这个验证的操作是由专门的认证插件来完成的。角色（role）是权限的集合，你可以将权限赋给角色，然后将角色赋给账号，这会方便权限的集中管理，如果涉及通用权限调整，只需要调整角色的权限即可，部分用户如果需要特别的权限，可以单独赋予。

死锁是事务型数据库中一种现象，为了在高并发环境下不出错，数据库引入了"锁"这一数据结构来控制资源的并发访问，但也会导致死锁。

由于大部分数据库都是高并行发的，即允许多个事务同时对数据进行读写和修改，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。不同的隔离级别代表了数据库在性能和可靠性之间的取舍。那么MySQL是如何保证事务基本特性ACID中的I（Isolation 隔离性）？答案就是InnoDB的多版本并发控制（Multi-Version Concurrency Control MVCC）。

百分比排序，返回当前行在组内的百分比位置，返回值范围为[0, 1]，可以用 当前行排序/(行数-1) 计算得出，但与rank不同，这里排序是从0开始而不是从1（相当于rank-1），因此第1行的百分比位置是0%，相当于：（rank-1）/(rows-1)。没有参数，返回当前行在组内的位置编号，从1开始，order by子句会影响行的编号顺序，如果没有order by，那么行的编号是不确定的。另外，即使行完全相同，它们的编号也是不同的，这点和后面的 rank() 和 dense_rank() 不同。

组复制部署完成后，即进入日常运维阶段。你需要监控各成员的运行状态，并在必要的时候还需要进行一些调整操作。

部署组复制时，建议将各个成员分布在不同的物理服务器上以达到容错的功能。但由于组复制成员的状态需要时刻保持同步，组内各成员需要时刻保持双向通信，因此其性能受到网络的延迟和带宽影响很大，建议部署在网络稳定，延迟较低的环境中（例如同一机房）。要完成基本组复制的部署（具备容错功能），至少需要3台服务器，这里用3台服务器来演示基本部署步骤。如果是自己练习可以装1台虚拟机然后直接复制2台出来（记得修改主机名、IP和MySQL数据目录下auto.cnf文件中的MySQL服务器的UUID）。

组复制中的成员可以独立的发起和提交事务（多主模式），当事务准备提交时，需要在组中广播将要修改的数据集，将事务加入全局事务列表，所有组成员必须就全局事务列表中给定事务的顺序达成一致，且事务必须由组服务批准后才能提交。当服务器故障时，组复制虽然可以继续提供数据库服务，但那些连接到故障服务器上的会话必须重新连接，或者failover到其他正常的服务器上，组复制并不提供会话failover的功能，需要其他的解决方案，例如MySQL Router。当单一主库负载过大时，可以通过组复制的多主模式分散写入负载。

MySQL默认的复制是全库复制，从库会执行所有主库发送过来的事务。但某些应用场景我们不需要全库复制，例如将不同的数据分散到不同的从库上，此时我们需要用到复制过滤来控制仅复制数据库的一部分。

MySQL从5.6版本开始引入了全局事务标识符（Global Transaction Identifiers, GTID）的概念，为每个事务都分配一个唯一的标识符。GTID以事务为单位管理复制，不再需要靠log_file和log_pos来定位复制位置，在主从切换、故障恢复时更加简单。source_id是发起事务服务器的UUID，用来保证"全局"唯一，transaction_id是事务的编号，按照事务提交的顺序从1开始单调递增。这保证的事务在整个复制拓扑中的是唯一的。

MySQL复制有三种模式异步复制/同步复制/半同步复制，3种模式各有利弊，下面对各种复制模式的优缺点做个简要的介绍：异步复制（Asynchronous replicaton）这是MySQL默认的复制模式，异步复制指主库写binlog、从库I/O线程读binlog并写入relaylog、从库SQL线程重放事务这三步之间是异步的。异步复制的优点是主库不需要关心备库的状态，主库不保证事务被传输到从库，如果主库崩溃，某些事务可能还未发送到从库，切换后可能导致事务的丢失。其优点是可以有更高的吞吐量，缺点是不能保持数据

MySQL的复制就是将来自一个MySQL数据库服务器（主库）的数据复制到一个或多个MySQL数据库服务器（从库）。其工作原理是通过binlog（二进制日志）记录事务变更然后传送到从库并重放事务，保持数据一致。

公共表表达式（Common Table Expressions, CTE）是MySQL在单一语句中的临时结果集。有时我们需要在一个SQL中重复执行同一个子查询，而每次子查询都会重新计算结果，带来性能的浪费。采用CTE可以在查询的一开始就定义好子查询的结果集，MySQL只会计算一次结果，然后在查询中可以反复引用。

这里介绍二进制文件安装方式，二进制文件使用高级编译器进行预编译，并采用最佳选项构建，以实现最佳性能。

当我们优化一个低性能的SQL时，通常第一件事就是查看执行计划。通过执行计划我们可以查看MySQL是如何处理一个SQL语句的，包括表的连接顺序、预估成本、索引使用情况等。

在关系型数据系统（RDBMS）中，数据被存储分布在大量不同的表中。但查询的时候，我们经常需要将多个数据源合并成一个结果集，这个合并的过程，即是连接。通常情况下，连接会提供一个连接条件（join condition），用于匹配两表中的数据。如果两表的连接没有条件，则表中的每一条记录都会与另一个表中的每条记录进行匹配，产生的结果即称为笛卡尔积。两个100条记录的表，笛卡尔积即100*100=10000条，如果三张100条记录的表这个结果将达到100万，很明显这通常不是我们想要的结果。对于连接的方法，除了上面提到

对于数据库，我们使用最多的就是查询。大部分业务功能的瓶颈都因SQL性能低下引发，所以SQL语句的优化是必备的技能，而要优化SQL就必须先搞清楚数据库后台是如何执行SQL，访问数据的。当执行一个SQL时，最终都是通过执行计划访问表中的数据，而根据SQL写法的不同，访问表中数据的方法是不同的，MySQL中的访问方法分为下列几种，我们可以在SQL的执行计划type列看到这些访问方法：const（system） eq_ref ref ref_or_null fulltext index_merge

从什么地方开始优化？调优前先试着获取用户的反馈信息，什么时候系统开始变慢了？哪些功能出现了卡顿？性能问题出现之前系统作了哪些变更？用户的反馈可以帮助我们缩小问题排查的范围，重点关注系统性能下降前的变更。从数据库层面，MySQL数据库的优化可以从以下几个方面考虑：表的设计是否合理？字段类型定义是否正确？字段的大小是否合适？合适的大小是指可以满足业务需求前提下最小的。字段长度小意味着更少的磁盘占用、更少的I/O、更小的内存占用及更小的索引。每一项优势都会为系统提速。这些都是在设计表时容易忽略的内

生产环境中，经常会遇到需要迁移表的情况，正常情况下都会使用mysqldump工具。但是mysqldump属于逻辑导入，需要通过SQL对数据进行导入，然后还要进行索引重建，当表的数据量非常大的时候，mysqldump的效率会非常低，耗时会很长。此场景可以利用lnnodb的可传输表空间的特性，直接通过操作系统的复制命令拷贝表空间文件，可以快速迁移数据，提升效率。前提条件可传输表空间并不是总是可用，如果要利用这一特性，必须要满足以下条件：表的存储必须是独立表空间（innodb_file_per_t

通常情况下，同一张表的数据在物理层面都是存放在一起的。随着业务增长，当同一张表的数据量过大时，会带来管理上的不便。而分区特性可以将一张表从物理层面根据一定的规则将数据划分为多个分区，多个分区可以单独管理，甚至存放在不同的磁盘/文件系统上，提升效率。分区表的优点：数据可以跨磁盘/文件系统存储，适合存储大量数据。 数据的管理非常方便，以分区为单位操作数据，不会影响其他分区的正常运行。 数据查询上在某些条件可以利用分区裁剪(partition pruning)特性，将搜索范围快速定位到特性分区，提升查