最近几年用MYSQL数据库挺多的，发现了一些非常有用的小玩意，今天拿出来分享到大家，希望对你会有所帮助。
在我们平常的工作中，使用group by进行分组的场景，是非常多的。
比如想统计出用户表中，名称不同的用户的具体名称有哪些？
具体sql如下：
但如果想把name相同的code拼接在一起，放到另外一列中该怎么办呢？
答：使用group_concat函数。
例如：
执行结果：
使用group_concat函数，可以轻松的把分组后，name相同的数据拼接到一起，组成一个字符串，用逗号分隔。
有时候我们需要获取字符的长度，然后根据字符的长度进行排序。
MYSQL给我们提供了一些有用的函数，比如：char_length。
通过该函数就能获取字符长度。
获取字符长度并且排序的sql如下：
执行效果如图所示
name字段使用关键字模糊查询之后，再使用char_length函数获取name字段的字符长度，然后按长度升序。
有时候我们在查找某个关键字，比如：苏三，需要明确知道它在某个字符串中的位置时，该怎么办呢？
答：使用locate函数。
使用locate函数改造之后sql如下：
执行结果：
先按长度排序，小的排在前面。如果长度相同，则按关键字从左到右进行排序，越靠左的越排在前面。
除此之外，我们还可以使用：instr和position函数，它们的功能跟locate函数类似，在这里我就不一一介绍了，感兴趣的小伙伴可以找我私聊。
我们经常会有替换字符串中部分内容的需求，比如：将字符串中的字符A替换成B。
这种情况就能使用replace函数。
例如：
这样就能轻松实现字符替换功能。
也能用该函数去掉前后空格：
使用该函数还能替换json格式的数据内容，真的非常有用。
时间是个好东西，用它可以快速缩小数据范围，我们经常有获取当前时间的需求。
在MYSQL中获取当前时间，可以使用now()函数，例如：
返回结果为下面这样的：
它会包含年月日时分秒。
如果你还想返回毫秒，可以使用now(3)，例如：
返回结果为下面这样的：
使用起来非常方便好记。
在工作中很多时候需要插入数据。
传统的插入数据的sql是这样的：
它主要是用于插入少量并且已经确定的数据。但如果有大批量的数据需要插入，特别是是需要插入的数据来源于，另外一张表或者多张表的结果集中。
这种情况下，使用传统的插入数据的方式，就有点束手无策了。
这时候就能使用MYSQL提供的：insert into ... select语法。
例如：
这样就能将order表中的部分数据，非常轻松插入到brand表中。
不知道你有没有遇到过这样的场景：在插入1000个品牌之前，需要先根据name，判断一下是否存在。如果存在，则不插入数据。如果不存在，才需要插入数据。
如果直接这样插入数据：
肯定不行，因为brand表的name字段创建了唯一索引，同时该表中已经有一条name等于苏三的数据了。
执行之后直接报错了：
这就需要在插入之前加一下判断。
当然很多人通过在sql语句后面拼接not exists语句，也能达到防止出现重复数据的目的，比如：
这条sql确实能够满足要求，但是总觉得有些麻烦。那么，有没有更简单的做法呢？
答：可以使用insert into ... ignore语法。
例如：
这样改造之后，如果brand表中没有name为苏三的数据，则可以直接插入成功。
但如果brand表中已经存在name为苏三的数据了，则该sql语句也能正常执行，并不会报错。因为它会忽略异常，返回的执行结果影响行数为0，它不会重复插入数据。
MYSQL数据库自带了悲观锁，它是一种排它锁，根据锁的粒度从大到小分为：表锁、间隙锁和行锁。
在我们的实际业务场景中，有些情况并发量不太高，为了保证数据的正确性，使用悲观锁也可以。
比如：用户扣减积分，用户的操作并不集中。但也要考虑系统自动赠送积分的并发情况，所以有必要加悲观锁限制一下，防止出现积分加错的情况发生。
这时候就可以使用MYSQL中的select ... for update语法了。
例如：
这样在一个事务中使用for update锁住一行记录，其他事务就不能在该事务提交之前，去更新那一行的数据。
需要注意的是for update前的id条件，必须是表的主键或者唯一索引，不然行锁可能会失效，有可能变成表锁。
通常情况下，我们在插入数据之前，一般会先查询一下，该数据是否存在。如果不存在，则插入数据。如果已存在，则不插入数据，而直接返回结果。
在没啥并发量的场景中，这种做法是没有什么问题的。但如果插入数据的请求，有一定的并发量，这种做法就可能会产生重复的数据。
当然防止重复数据的做法很多，比如：加唯一索引、加分布式锁等。
但这些方案，都没法做到让第二次请求也更新数据，它们一般会判断已经存在就直接返回了。
这种情况可以使用on duplicate key update语法。
该语法会在插入数据之前判断，如果主键或唯一索引不存在，则插入数据。如果主键或唯一索引存在，则执行更新操作。
具体需要更新的字段可以指定，例如：
这样一条语句就能轻松搞定需求，既不会产生重复数据，也能更新最新的数据。
但需要注意的是，在高并发的场景下使用on duplicate key update语法，可能会存在死锁的问题，所以要根据实际情况酌情使用。
有时候，我们想快速查看某张表的字段情况，通常会使用desc命令，比如：
结果如图所示：
确实能够看到order表中的字段名称、字段类型、字段长度、是否允许为空，是否主键、默认值等信息。
但看不到该表的索引信息，如果想看创建了哪些索引，该怎么办呢？
答：使用show index命令。
比如：
也能查出该表所有的索引：
但查看字段和索引数据呈现方式，总觉得有点怪怪的，有没有一种更直观的方式？
答：这就需要使用show create table命令了。
例如：
执行结果如图所示：
其中Table表示表名，Create Table就是我们需要看的建表信息，将数据展开：
我们能够看到非常完整的建表语句，表名、字段名、字段类型、字段长度、字符集、主键、索引、执行引擎等都能看到。
非常直接明了。
有时候，我们需要快速备份表。
通常情况下，可以分两步走：
- 创建一张临时表
- 将数据插入临时表
创建临时表可以使用命令：
创建成功之后，就会生成一张名称叫：order_2022121819，表结构跟order一模一样的新表，只是该表的数据为空而已。
接下来使用命令：
执行之后就会将order表的数据插入到order_2022121819表中，也就是实现数据备份的功能。
但有没有命令，一个命令就能实现上面这两步的功能呢？
答：用create table ... select命令。
例如：
执行完之后，就会将order_2022121820表创建好，并且将order表中的数据自动插入到新创建的order_2022121820中。
一个命令就能轻松搞定表备份。
很多时候，我们优化一条sql语句的性能，需要查看索引执行情况。
答：可以使用explain命令，查看mysql的执行计划，它会显示索引的使用情况。
例如：
结果：
通过这几列可以判断索引使用情况，执行计划包含列的含义如下图所示：
如果你想进一步了解explain的详细用法，可以看看我的另一篇文章《explain | 索引优化的这把绝世好剑，你真的会用吗？》
说实话，sql语句没有走索引，排除没有建索引之外，最大的可能性是索引失效了。
下面说说索引失效的常见原因：
如果不是上面的这些原因，则需要再进一步排查一下其他原因。
有些时候我们线上sql或者数据库出现了问题。比如出现了数据库连接过多问题，或者发现有一条sql语句的执行时间特别长。
这时候该怎么办呢？
答：我们可以使用show processlist命令查看当前线程执行情况。
如图所示：
从执行结果中，我们可以查看当前的连接状态，帮助识别出有问题的查询语句。
- id 线程id
- User 执行sql的账号
- Host 执行sql的数据库的ip和端号
- db 数据库名称
- Command 执行命令，包括：Daemon、Query、Sleep等。
- Time 执行sql所消耗的时间
- State 执行状态
- info 执行信息，里面可能包含sql信息。
如果发现了异常的sql语句，可以直接kill掉，确保数据库不会出现严重的问题。
有时候我们需要导出MYSQL表中的数据。
这种情况就可以使用mysqldump工具，该工具会将数据查出来，转换成insert语句，写入到某个文件中，相当于数据备份。
我们获取到该文件，然后执行相应的insert语句，就能创建相关的表，并且写入数据了，这就相当于数据还原。
mysqldump命令的语法为： mysqldump -h主机名 -P端口 -u用户名 -p密码 参数1,参数2.... > 文件名称.sql
备份远程数据库中的数据库：
模式设计是在数据库中最应该重视的事情之一。这一数据库设计原则，早在20世纪70年代就被引入。自MySQL从5.6版本中转移到InnoDB作为默认存储引擎后，模式设计变得更加重要。
为什么会这样呢？在InnoDB中，所有的东西都是主键!这与InnoDB组织数据的方式有关。在InnoDB中，主键（Primary Key）是集群的，每一个辅助索引（Secondary Key）都会为主键增加一个入口指针。如果你在设计模式时没有考虑到这一点，那么性能将受到负面影响。
数据是使用B树索引存储的，因此以有序的方式插入数据（即使用准序列值）可以防止主键碎片化，从而减少寻找叶节点所需的I/O操作。
在一些用例中，顺序主键不是正确的选择--这里的一个很好的例子是通用唯一标识符或UUID。
辅助索引（Secondary Key）是由一个后台进程更新的。因此，对性能的影响并不像你所期望的那样严重。相反，问题是围绕着磁盘占用的，因为增加辅助索引（Secondary Key）会增加存储需求。
对一个没有索引的字段进行过滤，可能会导致每次查询运行时都要进行全表扫描。当然，这可能会导致巨大的性能影响。因此，有一个辅助索引（Secondary Key）比没有要好。
也就是说，不应该过度添加索引，因为增加过多的索引可能不会实现性能改进。同时，这些额外的索引可能会增加你的存储成本，而且InnoDB必须执行许多后台操作来保持索引的更新。
InnoDB可以直接从索引中找到并实际服务于行记录，而辅助索引（Secondary Key）则指向主键，主键则包含行记录本身。如果InnoDB缓冲池足够大，它也可以在内存中容纳大多数数据。你甚至可以使用复合键，这对查询来说通常比单独的每列键更有效。MySQL可以在每个表的访问中使用一个索引，所以如果你正在运行带有WHERE x=1和y=2这样的子句的查询，那么在x,y上建立联合索引比在每个列上有单独的索引要好。
此外，对x,y的联合索引也可以提高以下查询的性能。
复制
MySQL将使用覆盖索引，并从内存中的索引中提供Y。
在实践中，当你有机会的时候，你可以通过使用联合索引来提高性能。无论何时，当你设计联合索引时，可以通过从左到右的方式读取索引，所以给定一个这样的查询。
复制
那么，一个关于a,b的联合索引将有助于查询。但是如果查询是下面这个格式。
复制
那么，这个a，b的联合索引就是无效的，因为违反的最左原则，也就是从左往右读取索引，因此会导致全表扫描。总是从左边读取索引的想法也适用于其他一些情况。例如，给定以下查询。
复制
那么在a,b,c上的联合索引将只读取第一列，因为没有通过列b过滤的WHERE子句。所以在这种情况下，MySQL可以部分地读取索引，这比全表扫描要好，但仍然不足以获得查询的最佳性能。
另一个与查询设计有关的元素是最左边的索引方法，因为这是MySQL中常用的优化。例如，一个关于a,b,c的索引将不包括像select a,c where c=x这样的查询，因为该查询不能跳过索引的第一部分，即a,b。然而，如果你有一个类似select c,count(c) where a=x and b=y group by c的查询，它对a,b进行过滤并对c进行分组，那么a,b,c上的一个索引可以帮助进行过滤和分组。
仅仅拥有一辆一级方程式赛车并不能赢得比赛。如果你把一个没有经验的司机放在方向盘后面，而他们在第一个弯道就撞车了，那就不会赢。同样地，你可能有地球上调整得最好的MySQL服务器，但如果你有糟糕的查询，你的数据库就会比它应该的慢。
你应该随着时间的推移定期审查你的查询设计，因为你的应用程序会随着新功能和错误的修复而改变。应用程序的数据集和使用模式也可能随着时间的推移而改变，所有这些都会影响查询的性能。
留出时间进行查询审查和监控查询，执行时间是非常重要的。你可以为此使用慢速查询日志或性能模式，但实施监控工具将帮助你获得更好的数据。
请记住，并不总是最慢的查询才是最需要解决的问题。例如，你可能有一个耗时30秒但每天运行两次的查询，与一个耗时1秒但每分钟运行100次的查询并存。为了取得大的胜利，你应该开始优化第二个查询，因为从长远来看，改进这个查询可以节省大量的时间和资源。
监测是性能调整的关键因素之一。如果不了解当前的工作负载和模式，就很难给出任何具体的建议。近年来，MySQL改进了对低级别的MySQL/InnoDB指标的暴露，这有助于了解工作负载。
例如，在早期版本中，性能模式是一个瓶颈，有相当大的影响，特别是如果你有许多表。在最近的MySQL版本中，就存在许多变化，如新的数据字典，已经改善了性能，现在的版本可以有很多表，但并不会对性能造成大的影响。
大多数现代监控工具都以某种方式使用性能模式，所以一个很好的建议是查看这些工具并选择最适合你的工具。对性能数据的可见性可能是一笔巨大的财富。
调优工具给出的建议在大多数情况下是有效的。然而，每个工作负载和每个模式有所不同。在某些情况下，调优工具的建议并不奏效，在相信这些建议时，谨慎行事是明智的选择。对于MySQL而言，可以对配置进行如下更改。
例如，将innodb_buffer_pool_size设置为总内存的75%是好的经验法则。然而，现在在数百GB的内存服务器的情况下，如果你有512GB的内存，那就会留下128GB的自由空间，而不是专门用于缓冲池，这是一种很大的浪费。
innodb_log_file_size和innodb_log_files_in_group也是根据RAM的数量来定义。在内存超过128GB的服务器上，这个设置没有什么意义，因为它将创建64个重做日志文件（Redo log），每个2GB。这将导致128GB的重做日志（Redo log）存储在磁盘上。在大多数情况下，不需要大的重做日志文件（Redo log），即使在最繁忙的环境中。因此，这并不是一个好的建议。
innodb_flushing_method是启用自动配置时唯一正确配置的值。这个变量将flushing 方法设置为O_DIRECT_NO_FSYNC，这是使用Ext4或XFS文件系统时推荐的方法，因为它避免了数据的双重缓冲。
一个好的建议是，在专用服务器上将innodb_buffer_pool_size设置为75%或80%。在拥有大量内存的服务器上，即超过128GB的服务器，在对内存消耗进行适当的分析后，将其增加到90%甚至更多。同样，对于innodb_log_file_size和innodb_log_files_in_group 来说大多数情况下，从2GB的文件开始，监测写日志操作。通常情况下，在确定重做日志（Redo log）的大小时，建议覆盖大约一个小时的写入量。
关于innodb_flush_method，对于Ext4或XFS等现代Linux文件系统，这个选项应该被设置为O_DIRECT或O_DIRECT_NO_FSYNC。
MySQL和InnoDB试图最小化它们进行的I/O操作的数量，因为访问存储层在应用性能方面是昂贵的。有一些设置可以影响InnoDB执行的I/O操作的数量。其中有两个设置经常被误解，而改变它们往往会导致性能问题。
innodb_io_capacity和innodb_io_capacity_max是与后台Flushing的I/O操作数量有关的变量。许多客户增加这些设置的值，以利用现代固态硬盘的优势，它可以在相对较低的延迟下提供非常高的I/O容量。虽然这个想法看上去很合理，但增加I/O容量设置会导致一些问题。
第一个问题是通过使InnoDB过快地刷新脏页而导致性能下降，从而减少了“被刷新前多次修改一个页面的机会”。将脏页保留在内存中可以大大减少将数据写入存储的I/O操作。
其次，固态硬盘在出现性能下降之前有一个预期的写入次数。因此，增加写操作的数量会影响你的固态硬盘的寿命，即使你使用的是高端硬盘。
虽说云主机最近很流行，在云中运行MySQL服务实例也是可行的。然而，云中的服务器往往会有I/O限制，或者会对使用更多的I/O收取更多的费用。通过了解这些限制，你可以仔细配置这些参数，以确保不达到这些限制，并使I/O操作最小化。
提到innodb_lru_scan_depth也很重要，因为这个设置控制了缓冲池LRU页面列表中，页面清洁器线程在多远的位置扫描脏页。如果你有一个大的缓冲池和许多缓冲池实例的重写工作负载，你可以通过减少这个变量来减少I/O的操作。
一个好的建议是保持默认值，除非你知道你需要改变它们。
还值得一提的是，最新的固态硬盘是专门为交易型数据库而优化的。西部数据就是一个例子，该公司寻求专家的帮助，以帮助他们满足正在创建的新一轮应用的要求。
MySQL 8.0引入了通用表的表达式（CTE），这可以避免创建派生表的嵌套查询。这个功能允许创建一个自定义查询并引用结果，就好像是一个临时表或一个视图一样。不同的是，CTEs可以在一个事务中被多次引用，而不需要明确地创建和删除它们。
鉴于CTEs只被实例化一次，它们在运行多个查询的复杂事务中往往更快。另外，支持CTE递归，可以在SQL语言中轻松创建复杂的结构，如分层模型和系列。
对于MySQL部署，有许多不同的云选项值得考虑，从在虚拟机中实施MySQL服务器实例，到使用数据库即服务（DBaaS）解决方案，选择的范围很广。
许多这样的服务承诺提供显著的性能提升。在一些简单的用例中，这种做法是可行的。然而，即使是在云端，也必须理解数据库的基本原理，否则成本将大大增加。这种成本增加往往是通过增加更多的硬件来解决问题，而并没有从设计上找问题。
近年来，围绕着MySQL Replication进行了许多改进，在许多情况下，它无法及时同步主服务器写入操作。在最新的MySQL主要版本中，Replication默认是并行的，这意味着多个Replication线程正在运行并试图同时应用事务。
当然，执行效率在很大程度上取决于应用程序写入的工作量，在大多数情况下，并行复制可以帮助复制体跟上写入操作。你可以用replica_parallel_type和replica_parallel_workers这两个变量来控制。使用LOGICAL_CLOCK类型，事务被并行应用，并根据时间戳追踪依赖关系。