本文转载自微信公众号“Java中文社区”，作者雷哥。转载本文请联系Java中文社区公众号。年轻的时候不知道优化的痛苦，但是遇到坑就会知道优化的难。——存口王大叔全文内容预览：当然这篇文章也是讲性能优化的，那么性能优化应该是一梭子吧？还是应该符合一些规范和原则？那么，在开始（MySQL优化）之前，我们先来说说性能优化的一些原则。性能优化原则及分类性能优化一般可以分为：主动优化被动优化所谓主动优化是指在没有外力的情况下自发进行的行为，比如当服务没有明显的卡顿、宕机、硬件异常时指标情况下，自启动优化的行为可以称为主动优化。被动优化与主动优化正好相反。指只有在服务器卡顿、服务异常、物理指标异常时才进行优化的行为。性能优化原则无论是主动优化还是被动优化，都必须遵守以下性能优化原则：优化不能改变服务运行的逻辑，但必须保证服务的正确性；优化过程和结果必须保证服务的安全性；不能为了追求性能而牺牲程序的稳定性。例如，为了提高Redis的运行速度，不能关闭持久化功能，因为Redis服务器重启或断电后，存储的数据会丢失。以上原则看似废话，但给了我们一个启示，那就是我们的性能优化方法应该是：预防性能问题为主+被动优化为辅。也就是说，我们应该把重点放在性能问题的预防上，在开发阶段尽量避免出现性能问题。一般情况下，我们应该尽量避免主动优化，以防未知风险（除非是为了KPI，或者闲着没事），尤其是生产环境，被动优化是最后要考虑的事情。PS：当性能缓慢下降或者硬件指标缓慢上升，比如内存使用率今天是50%，明天是70%，后天是90%，而且没有恢复的迹象，我们应该及早发现并处理此类问题（这种情况也是一种被动优化）。MySQL被动性能优化所以本文我们将重点介绍MySQL被动性能优化的相关知识。基于被动性能优化的知识，你可以得到一些预防性能问题的方法，从而避免MySQL性能问题。在这篇文章中，我们将从问题入手，然后考虑导致这个问题的原因以及相应的优化方案。在实际开发中，我们通常会遇到以下三个问题：单条SQL运行速度慢；一些SQL运行缓慢；整个SQL运行缓慢。问题一：单条SQL运行慢的分析单条SQL运行慢的常见原因有两种：未正常创建或使用索引；表中的数据量太大。解决方案一：正确创建和使用索引索引是帮助MySQL提高查询效率的主要手段，所以一般情况下，我们遇到的单条SQL性能问题，通常都是因为没有正确创建和使用索引造成的，所以在单条SQL的情况下运行缓慢，首先需要检查这张表的索引是否正常创建。如果表的索引已经创建，下一步就是检查SQL语句是否正常触发索引查询。如果出现以下情况，MySQL将无法正常使用索引：在where子句中使用!=或<>运算符，查询引用将放弃索引，进行全表扫描；不能使用前导模糊查询，即'%XX'或'%XX%'，因为前导模糊不能使用索引的顺序，必须逐条查找，看是否满足条件，这样会导致全索引扫描或全表扫描；如果条件中有or，即使条件中有索引，索引也不会被正常使用。如果要使用or并且想让索引生效，那么只能在or条件下的每一列都使用索引才能正常使用；对where子句中的字段执行表达式操作。因此，您应尽量避免出现上述情况。除了正常使用索引，我们还可以通过以下技巧来优化索引的查询速度：尽量使用主键查询，而不是其他索引，因为主键查询不会触发回表查询;查询语句尽可能简单，将大语句拆分成小语句以减少锁定时间；尽量使用数值型字段，尽量不要把只包含数值信息的字段设计成字符类型；使用存在而不是查询；避免使用isnull和isonindexcolumnsnotnull。回表查询：普通索引查询到主键索引后，又回到查找主键索引树的过程，称为回表查询。方案二：数据拆分当表中数据量过大时，SQL查询会变慢。可以考虑拆分表，减少每个表的数据量，从而提高查询效率。1.垂直拆分是指拆分一个表，将一个列很多的表拆分成多个表。比如user表中有些字段是经常访问的，把这些字段放在一个表中，把一些不常用的字段放在另一个表中。插入数据时，使用事务来保证两张表数据的一致性。垂直拆分的原则：将不常用的字段放在单独的表中；把text、blob等大字段拆分出来放到附表中；将经常组合和查询的列放在一个表中。2.水平拆分是指对数据表的行进行拆分。当表的行数超过200万时，速度就会变慢。这时可以将一张表的数据拆分成多张表存储。通常，我们使用取模来拆分表。比如一个400W的用户表users，为了提高它的查询效率，我们把它分成四张表users1，users2，users3，users4，然后通过用户ID取模的方法，同时查询，更新，和删除也是采用取模的方法进行操作。表的其他优化方案：使用能存储数据的最小数据类型；使用简单的数据类型，int在MySQL中比varchar类型更容易处理；尝试使用tinyint、smallint、mediumint作为整数类型而不是int；尽量不要用null定义字段，因为null占用4字节空间；尽量少用文本类型，必要时考虑分表；尝试使用时间戳而不是日期时间；单表字段不要太多，建议20个字段以内。问题二：部分SQL运行缓慢问题分析部分SQL运行缓慢。我们首先要做的是先定位到这些SQL，然后检查这些SQL是否正确创建和使用了索引。也就是说，我们首先要使用慢查询工具定位到具体的SQL，然后再使用问题1的解决方案来处理慢SQL。解决方案：慢查询分析MySQL自带慢查询日志功能，可以用来记录MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的SQL，会被记录在慢查询中查询日志。long_query_time的默认值是10，表示运行语句超过10S。默认情况下，MySQL数据库是不开启慢查询日志的，需要我们手动设置这个参数。如果非调优需要，一般不建议开启该参数，因为开启慢查询日志会对MySQL服务器带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。使用mysql>showvariableslike'%slow_query_log%';查看慢查询日志是否开启，执行效果如下图：当slow_query_log的值为OFF时，表示没有开启慢查询日志。打开慢查询日志要打开慢查询日志，可以使用以下MySQL命令：mysql>setglobalslow_query_log=1但是这种设置方法只对当前数据库有效，如果重启MySQL也会失效。如果要永久生效，必须修改mysql的配置文件my.cnf配置如下：slow_query_log=1slow_query_log_file=/tmp/mysql_slow.log开启慢查询日志后，所有的慢查询SQL都会记录在slow_query_log_file参数配置的文件，默认为/tmp/mysql_slow.log文件，此时我们可以打开日志查询所有慢SQL进行优化。问题三：整个SQL运行缓慢的分析当整个SQL运行缓慢时，说明数据库当前的承载能力已经达到了峰值，需要通过一些数据库扩容的方式来缓解MySQL服务器。解决方案：读写分离对于数据库一般是“多读少写”。也就是说，数据库的压力大部分是由于大量的读取数据的操作造成的。我们可以采用数据库集群方案，使用一个库作为主库，负责写入数据；其他库为从库，负责读取数据。这样可以减轻数据库访问的压力。MySQL常见的读写分离方案有两种：1、应用层方案，可以通过应用层路由数据源，实现读写分离。比如使用SpringMVC+MyBatis，可以将SQL路由交给Spring，通过AOP或者Annotation控制代码展示的数据源。优点：路由策略的扩展性和可控性强。缺点：需要在Spring中添加耦合控制代码。2、中间件方案采用MySQL中间件作为主从集群，如：MysqlProxy、Amoeba、Atlas等中间件均可满足要求。优点：与应用层解耦。缺点：为服务维护增加风险点，性能和稳定性有待测试，需要支持代码强制执行主从和事务。知识扩展：SQL语句分析在MySQL中，我们可以使用explain命令来分析SQL的执行情况，例如：explainselect*fromtwhereid=5;如下图所示：其中：id——选择标识符，id越大优先级越高，最先执行；select_type—表示查询的类型；table——输出结果集的表；partitions—匹配的分区；type——表示表的连接类型；possible_keys——表示查询时可能使用的索引；key—表示实际使用key_len—索引字段的长度；ref—列与索引的比较；rows—粗略估计的行数；filtered—表条件过滤的行的百分比；Extra——实现的描述和解释。其中最重要的是type字段，type值类型如下：all——扫描全表数据；index—遍历索引；range—索引范围搜索；index_subquery—在子查询中使用ref；unique_subquery—在子查询中使用eq_ref；ref_or_null—用于索引null的优化ref；fulltext—使用全文索引；ref—使用非唯一索引查找数据；eq_ref—在连接查询中使用主键或唯一索引关联；const——在where后面放置一个主键作为条件查询，MySQL优化器可以将这个查询优化转化为一个常量。如何以及何时转换取决于优化器，它比eq_ref效率更高一点。小结在本文中，我们介绍了MySQL性能优化的原则和分类。MySQL性能优化可以分为：主动优化和被动优化，但无论哪种优化，都必须保证服务的正确性、安全性和稳定性。启发我们应该采用：预防+被动优化的方案来保证MySQL服务器的稳定性，被动优化的常见问题是：单条SQL运行缓慢；一些SQL运行缓慢；整个SQL运行缓慢。因此，我们给出了每种被动优化方案的问题分析和解决方案。我希望这篇文章可以帮助你。
