MYSQL中的几个神仙功能，惊艳到我了，提升效率不是一点半点

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