在业务项目开发过程中，我们经常会面临处理MySQL慢查询的问题。我们应该如何分析和解决问题？有的同学主要是想加个索引来解决问题。添加索引确实是解决问题的好手段，但不是全部。既然是查询慢的问题，那么就要明确问题的原因，分析解决问题的路径。下面一起来get一下MySQL慢查询的正确姿势吧。查询SQL执行怎么了？首先需要明确：查询SQL执行到底经历了什么？数据库执行SQL的大致过程如下：与MySQL服务器建立连接（基本）客户端向数据库发送查询SQL，检查是否有执行权限MySQL服务器首先检查查询缓存，如果命中缓存，则立即返回缓存中存储的结果，否则继续流；MySQL服务器解析器进行词法和语法分析，预处理流程转入查询优化器生成并执行Plan，根据生成的执行计划，调用存储引擎暴露的API执行查询，返回查询执行结果到客户端，关闭MySQL连接，具体执行过程可能会因MySQL服务器的具体配置和执行场景而异。情况如下：如果应用查询缓存没有开启，则直接忽略对查询缓存的检查；在执行过程中，如果扫描到的行可能同时被锁定，也可能被其他SQL阻塞。为什么查询SQL慢？我们可以把查询SQL的执行看成是一个任务，它是由一系列的子任务组成的，每个子任务都消耗一定的时间。通常，导致查询慢的最根本问题是访问的数据过多，这导致不可避免地需要过滤大量数据进行查询。面对慢查询，我们需要注意以下两点：查询了太多不必要的数据扫描了额外的记录查询了太多不必要的数据MySQL并不是只返回需要的数据，而是返回所有的结果集，然后计算.特别是在多表关联查询select*的情况下，我们真的需要所有的列吗？如果没有，那我们直接指定对应的字段即可。比如我们要查询用户关联订单下的商品信息，如下：SELECT*FROMusersLEFTJOINordersONorders.user_id=users.user_idLEFTJOINgoodsONgoods.good_id=orders.good_idWHEREusers.name='张三';这将返回三个表的所有数据列，可以调整为只取需要的列：SELECTgoods.title,goods.descriptionFROMusersLEFTJOINordersONorders.user_id=users.user_idLEFTJOINgoodsONgoods.good_id=订单。good_idWHEREusers.name='张三';取出所有列会导致优化器无法完成索引覆盖扫描等优化，也会给服务器带来额外的I/O、内存和CPU消耗。扫描额外的记录。这种情况多是由于索引应用不当造成的（包括：建好的索引没有建好，或者没有应用最优索引）。示例表结构如下：CREATETABLE`test_table`(`id`bigint(20)UNSIGNEDNOTNULLAUTO_INCREMENT,`name`varchar(32)DEFAULTNULL,`desc`varchar(32)DEFAULTNULL,`age`int(16)DEFAULTNULL,KEY`idx_age`(`age`))ENGINE=InnoDBCHARSET=utf8mb4;示例查询SQL执行计划：EXPLAINSELECT*FROMtest_tableWHEREage=10;应用索引idx_age后，估计访问1行数据，如下图所示：如果删除有效索引，就会变成全表扫描（ALL）。估计需要扫描121524条记录才能完成查询，如下图所示：如何定位问题？找到慢查询后，如何定位查找问题原因最常见的方法是使用EXPLAIN关键字模拟查询优化器执行查询SQL，从而知道MySQL是如何处理你的查询SQL的，并通过执行计划分析性能瓶颈。通常我们使用EXPLAIN时，会得到如下执行计划信息：idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra关于各个字段的含义，大家可以自行搜索了解，这里不再赘述。关于定位分析的问题，重点如下。select_type表示查询类型，用于区分普通查询、联合查询、子查询等复杂查询。type表示使用的查询类型，从最好到最差：system>const>eq_ref>ref>range>index>allpossible_keys，key分别指可能应用的索引和实际应用的索引。注意：如果查询中使用了覆盖索引（select后要查询的字段与创建的索引字段完全相同），该索引只会出现在key列表中。Rows粗略估计找到所需记录需要读取的行数（就效率而言，该值越小越好）。额外重要的额外信息。包含MySQL如何解决查询的详细信息，是重点参考项目之一。SeveralPracticalSolutions我们使用EXPLAIN关键字模拟查询优化器执行查询SQL，找到了造成查询慢问题的原因，那么如何有效解决呢？给大家推荐几个比较实用的方案。优化数据结构选择索引的数据类型MySQL支持的数据类型很多，选择合适的数据类型存储数据对性能影响很大。一般来说，可以遵循一些准则。较小的数据类型通常更好较小的数据类型通常需要较少的磁盘、内存和CPU缓存空间，并且处理速度更快。简单的数据类型更好。处理整数比处理字符更便宜，因为字符串比较更复杂。在MySQL中，应该使用内置的日期和时间数据类型而不是字符串来存储时间；并使用整数数据类型来存储IP地址。尽量避免NULL并将该列指定为NOTNULL。在MySQL中，包含空值的列很难针对查询进行优化，因为它们会使索引、索引统计和比较操作变得更加复杂。可以用0、特殊值或空字符串替换NULL值。范式与反范式规范化模型需要以下三种范式：1.数据库表中的每个字段只包含最少的信息属性，不能进一步细化和分解。2、模型包含主键，非主键字段依赖于主键（在满足1的基础上）。例如，在用户模型中，它的主键是用户ID，那么用户模型的其他字段应该依赖于用户ID。如果产品ID与用户没有直接关系，则该属性不应放在用户模型中，而应放在“用户-产品”关联订单表中。3、模型非主键字段不能相互依赖（在满足2的基础上）。例如：设计一张订单表（订单ID，用户ID，用户名...）乍一看，该表满足第二范式，每一列都与主键列“订单ID”相关，但其中“用户ID”和“用户名”是相关的，“用户ID”和“订单ID”也是相关的，反过来推断：“用户名”和“订单ID”也是相关的。如果不满足第三范式，则要把order表中的“用户名”列去掉，放到user表中。非规范化非规范化模型是不满足规范化的模型。主要是为了性能和效率，适当违背了规范化的设计要求，允许少量的数据冗余，即以空间换时间。总结表明，一个好的、实用的数据模型往往取决于具体的需求场景。在设计数据模型之前，仔细分析需求场景，不仅可以提高效率，还可以有效避免后期可能遇到的一些意想不到的麻烦。规范化设计与非规范化设计对比：规范化设计最大限度的减少了数据冗余，表相对较小，更新操作更快；非规范化设计减少了表之间的关联，可以更好地优化索引（eg：覆盖索引）。应用索引策略索引（在MySQL中也称为“Key”）是存储引擎用来快速查找记录的一种数据结构。索引对于良好的性能至关重要，尤其是当表中的数据量越来越大时，索引对性能的影响越来越重要（不合适的索引对会导致性能随着数据量的增加而急剧下降).以下面的情况为例：假设数据库中的一个表有10^6条记录，DBMS的页大小为4K（大约可以存储100条记录）。如果没有索引，查询将扫描整个表。在最坏的情况下，如果所有数据页都不在内存中，则需要读取10^4页。如果这10^4页随机分布在磁盘上，则需要读取10^4次I/O，假设每次磁盘I/O时间为10ms（忽略数据传输时间），总共需要100s（但实际上要好得多）。如果在上面建立B-Tree索引，只需要做log100(10^6)=3页读取，最坏情况下需要30ms。这就是索引的效果。了解了索引的优点之后，其实正确的创建和使用索引是实现高性能查询的基础。您可以使用B-Tree索引执行全关键字、关键字范围和关键字前缀查询。当然，如果你要使用索引，你必须保证你是按照索引最左边的前缀来查询的。最左前缀原则最左前缀原则主要用在联合索引中。例如：联合索引idx_col1_col2_col3(col1,col2,col3)。匹配全值（Matchthefullvalue）为索引中的所有列指定特定的值：--下面可以应用于这个联合索引（在这种情况下，字段的顺序无关紧要）col1='a'ANDcol2='b'ANDcol3='c'col2='c'ANDcol3='b'ANDcol1='a'col3='c'ANDcol1='b'ANDcol2='a'匹配最左边的aprefix（匹配一个最左边的前缀）可以应用于联合索引中排在最前面的字段：--以下可以应用于联合索引中的部分索引--以下可以应用于联合索引中的部分索引col1='a'ANDcol2='b'col1='a'col1like'a%'索引覆盖（Index-onlyqueries）只查询索引，如果查询列都在索引中，则不需要读取列元组的值。selecta,b,cclusteredindex聚簇索引保证键值相近的元组存储在同一个物理位置（所以字符串类型不适合建聚簇索引，尤其是随机字符串，会导致系统执行大量的移动操作），一张表只能有一个聚簇索引。因为索引是由存储引擎实现的，并不是所有的引擎都支持聚集索引。目前仅支持SolidDB和InnoDB。InnoDB在主键上创建聚簇索引。如果不指定主键，InnoDB将使用具有唯一且非空值的索引来代替。如果不存在这样的索引，InnoDB定义一个隐藏的主键，然后在其上构建一个聚集索引。查询缓存MySQL查询缓存存储查询返回的完整结果。当查询命中缓存后，MySQL会立即返回结果，并跳过后续的解析、优化和执行阶段，有效提升查询性能。但是查询缓存不是灵丹妙药，它会出现一些问题。查询缓存注意事项1、缓存中有一些不确定的函数，不能使用查询缓存，例如：NOW()、CURRENT_DATE()之类的函数；超过query_cache_size（设置查询缓存空间大小）的查询结果无法缓存；同时区分大小写，查询SQL只在字符串相等时才使用同一个缓存。--不会使用来自id=1的用户的相同缓存选择名称；从id=1的用户中选择名称；2、缓存容易失效。如果查询结果被缓存，但是由于查询缓存设置内存不足，MySQL会添加新的缓存，部分缓存会被逐出，导致后续查询未命中；数据结构和数据修改、内存不足、缓存碎片都会导致缓存失效。总结查询缓存对应用程序是完全透明的，应用程序不需要关心MySQL是通过查询缓存返回结果还是实际执行结果。但是随着现在服务器的性能越来越强，查询缓存被发现是影响服务器扩展性的一个因素。很有可能成为整个服务器的资源争夺点。在生产环境中启动应用程序时，您必须仔细考虑它。在重构查询方法优化慢查询的时候，我们可以换个思路。我们的目标是找到一种更好的方法来获得当时需要的结果，而不是一定要从MySQL中获得相同的结果集。重构查询的技巧是必要的。复杂查询拆分将一个复杂查询拆分为多个简单查询，并考虑是否需要将一个复杂查询拆分为多个简单查询。在实际开发过程中，人们往往强调数据库层完成尽可能多的工作。这样做的初衷是网络通信、查询分析和优化是很昂贵的事情。其实MySQL允许连接和断开是从设计上考虑的。两者都是轻量级的，同时可以高效地返回小型查询的结果。而且现在的网速比以前快了很多，无论是带宽还是延迟。对于大查询，我们需要“分而治之”，将大查询分成多个小查询。但是，当一个查询可以完成这项工作时，拆分成多个独立的查询是不明智的。例如：对数据库进行10次查询，每次返回一行记录。分解关联查询分解关联查询，对每个表进行单表查询，然后在应用程序中关联结果。例如：SELECT*FROMusersLEFTJOINordersONorders.user_id=users.user_idLEFTJOINgoodsONgoods.good_id=orders.good_idWHEREusers.name='zhangsan';上面的查询可以分解为下面的查询：SELECT*FROMusersWHEREusers.name='zhangsan';SELECT*FROMordersWHEREorders.user_id=103;SELECT*FROMgoodsWHEREgoods.good_idIN(123,456,789);你为什么要这样做？好像没什么好处，返回的数据结果也是一致的。实际上，使用分解查询来重构查询有很大的优势，主要有：分解查询后，执行单个查询可以减少锁的竞争；应用层关联，更容易拆分数据库，更容易实现高性能和可扩展性；减少冗余记录的查询（关联在应用层是指某条记录的应用只需要查询一次，而数据库中的关联查询可能需要重复访问部分数据。）高性能查询问题优化总结如何处理高性能查询问题？如果将高性能查询比作一个“问题”，它实际上是多个子问题共同作用的结果。今天我们来总结一下，主要包括以下几类。数据结构优化优化好的数据库表数据结构设计原则是普遍适用的，但MySQL有自己的实现细节需要注意。总结应用原则如下，注意参考：尽量避免过度设计，使用小而简单的合适的数据类型，尽可能避免使用null，使用相同的数据类型存储相似或相关的值，注意变长字符串，存储在临时表中，排序时，可以按照最大长度分配内存。尝试使用整数来定义标识符。索引设计优化了常用的B-Tree索引，将数据按顺序存储，因此可以使用MySQL进行ORDERBY和GROUPBY操作。因为数据是有序的，所以将相关的列值存储在一起很方便。因为实际的列值存储在索引中，所以有些查询只能通过索引（如：聚簇索引）来完成。根据索引的特点，索引的优点总结如下：减少服务器需要扫描的数据量帮助服务器避免排序和临时表将随机I/O变为顺序I/O在编写查询时statements，应该尽可能选择合适的Indexes，避免单行查找，尽可能使用indexcoverage。根据执行计划依次扫描相关表中的行。IO存储引擎扫描不在databuffer中的表的性能消耗参考下表。消耗从大到小：全表扫描>全索引扫描>部分索引扫描>索引查找>唯一索引/主键查找>常量/空应用查询优化应用查询优化是基于良好的数据结构和合理的索引设计。主要包括以下几种情况。1、在重构查询方法优化慢查询时，目标应该是找到一个更好的方案来达到我们获取结果数据的目的。可以有多种权衡：直接从数据库查询计算中获取结果数据；拆分多个子查询，逐步获取结果数据；从数据库中获取基础数据，然后应用代码逻辑处理得到结果数据。2、尽可能让SQL满足查询优化器的执行要求。MySQL查询优化器并不适合所有查询。我们可以通过重写查询SQL，让数据库更高效地完成工作。常见的查询优化建议如下：对于任何查询，都应尽量避免全表扫描，首先考虑在where和orderby涉及的列上创建和应用索引。尽量避免在where子句中使用or来连接条件，判断字段的空值，匹配查询'%abc%'，!=或<>操作符，否则引擎会放弃使用索引，执行全表扫描。尽量避免在where子句中对字段进行表达式和函数操作，这样会导致引擎放弃使用索引而进行全表扫描。使用索引字段作为条件时，如果是复合索引，系统必须使用索引中的第一个字段作为条件，保证系统使用索引，否则索引不会被使用，该字段应该尽可能使用的顺序与索引顺序相匹配。添加索引应尽量避免区分度不高的字段。例如，当sex、male、female等50-50索引列存在大量重复数据时，即使在sex上建立索引也不会影响查询效率。一张表的索引最好不要超过6个。索引越多越好，索引可以提高相应select的效率，但是也会降低insert和update的效率，因为insert或者update的时候可能会重建索引，所以如何建立索引需要慎重考虑，具体要看情况。尽量使用数值字段，尽量不要将只包含数值信息的字段设计为字符类型，这样会降低查询和连接性能，增加存储开销。这是因为引擎在处理查询和连接时，会把字符串中的每个字符一个一个地进行比较，但是对于数字类型，只需要一个比较就可以了。尽量避免使用select*fromtable，将*替换为具体的字段列表，不要返回任何没有被使用的字段，尤其是在多表关联查询的情况下。MySQLv5.6之后，取消了MySQL原有的很多限制，让更多的查询能够以最高的效率完成。总结良好的表结构设计是高性能查询的基石，合理的索引设计是高性能查询的助推器，合理的查询应用也必不可少。数据结构优化、索引设计优化、应用查询优化犹如三叉戟，齐头并进，在高性能查询应用中缺一不可。写在全文最后总结一下，其实我们要学会按照数据库要求的方式去执行SQL。也就是说，要写出好的应用查询SQL，必须结合好的数据结构和合理的索引设计。其实，MySQL查询优化中的每一项，拆解起来都是一大章。在这里主要和大家分享解决问题的思路，希望对大家以后的工作有所帮助。
