优化MySQL查询：使用索引提高查询速度技术 优化MySQL查询：使用索引提高查询速度技术随着数据的不断增长，数据库的优化变得越来越重要。MySQL是最受欢迎的关系型数据库管理系统之一，但是当数据集变得庞大时，查询速度可能会变得很慢。针对这个问题，我们可以使用索引来提高查询速度。什么是索引？在MySQL中，索引是用于加速数据查找的数据结构。它是一种特殊的数据结构，能够让我们快速地查找数据。如果我们没有索引，我们需要遍历整个表来找到我们需要的数据，这会非常慢且低效。索引可以帮助我们加快查找，从而提高查询效率。什么时候使用索引？虽然索引可以提高查询速度，但是并不是所有的查询场景都适用索引。在查询小型数据集时，索引的优化效果可能并不明显，而且会增加写操作的负载。因此，在使用索引之前，我们需要考虑数据集的大小、查询频率以及之前的查询效率等因素。一般来说，当以下情况出现时，可以考虑使用索引来优化查询：1.在查询大型数据集时，建立索引可以提高查询速度。2.在经常用于检索的列上建立索引可以提高查询速度。3.在execute或where子句中经常使用的列上建立索引可以提高查询速度。如何使用索引？在了解了什么是索引以及什么时候该使用索引之后，下面我们来看一下如何使用索引来优化MySQL查询。创建索引在MySQL中创建索引非常简单。我们只需要向CREATE INDEX命令传递要创建索引的表和列名即可。例如，假设我们有一个名为“users”的表，并且我们想在“username”列上创建索引，那么我们可以使用以下命令来创建索引：CREATE INDEX username_index ON users (username);添加索引后，我们可以使用以下命令来查看MySQL使用的索引：EXPLAIN SELECT * FROM users WHERE username='test_user';此命令将告诉我们MySQL使用的索引以及查询的详细信息。使用多列索引在有些情况下，我们可能需要同时在多列上进行查询。例如，在一个在线商店中，我们可能会需要使用“category”和“price”列来筛选产品。在这种情况下，我们可以为多个列创建一个复合索引，以便同时按照多个列进行查找。CREATE INDEX category_price_index ON products (category, price);使用索引提示虽然MySQL通常会自动选择适当的索引，但是在某些情况下，我们可能需要手动指定使用哪个索引。为了实现这一点，我们可以使用索引提示，以便告诉MySQL使用哪个索引：SELECT * FROM users WHERE username='test_user' USE INDEX (username_index);在这个例子中，我们使用了“USE INDEX”来指定MySQL使用“username_index”。优化查询顺序除了使用索引之外，我们还可以通过优化查询顺序来提高查询效率。在MySQL中，查询操作通常包含以下三个步骤：1.执行FROM子句中指定的所有表。2.筛选满足WHERE子句的记录。3.执行SELECT子句中指定的操作。当MySQL查询操作执行时，它将尝试找到最佳的执行计划以最大化查询性能。如果我们想要一定的控制查询执行计划的顺序，我们可以使用“ORDER BY”和“GROUP BY”子句来指定查询计划的执行顺序。在选择执行计划时，MySQL将考虑许多因素，例如查询的复杂度、索引使用和数据统计。因此，我们不能保证通过改变查询的执行顺序来提高查询效率。不过，如果我们认为MySQL没有选择最佳的执行计划，这可行的解决方案之一就是指定查询的执行顺序。优化查询效率为了提高MySQL查询效率，除了使用索引之外，我们还可以采取以下一些行动：1.对于长时间运行的查询，我们可以将它们分解成较小的部分，并使用缓存表来提高查询效率。2.使用相关的子查询来快速过滤记录和排除不符合条件的记录。3.使用内联触发器或存储过程来对查询进行优化，以避免在每次查询时重复相同的代码。结论MySQL查询优化是提高数据查询速度的关键所在，而索引作为数据库优化的重要方式，不可忽视。本文介绍了如何使用索引来提高查询性能和一些优化查询性能的最佳做法。通过专门的一些高级技术，我们可以使我们的SQL查询更加高效，从而提高系统的性能。

最全 MySQL 优化方法，从此优化不再难 最全 MySQL 优化方法，从此优化不再难说起MySQL的查询优化，相信大家收藏了一堆奇技淫巧：不能使用SELECT *、不使用NULL字段、合理创建索引、为字段选择合适的数据类型..... 你是否真的理解这些优化技巧？是否理解其背后的工作原理？在实际场景下性能真有提升吗？我想未必。因而理解这些优化建议背后的原理就尤为重要，希望本文能让你重新审视这些优化建议，并在实际业务场景下合理的运用。MySQL逻辑架构如果能在头脑中构建一幅MySQL各组件之间如何协同工作的架构图，有助于深入理解MySQL服务器。下图展示了MySQL的逻辑架构图。MySQL逻辑架构，来自：高性能MySQLMySQL逻辑架构整体分为三层，最上层为客户端层，并非MySQL所独有，诸如：连接处理、授权认证、安全等功能均在这一层处理。MySQL大多数核心服务均在中间这一层，包括查询解析、分析、优化、缓存、内置函数(比如：时间、数学、加密等函数)。所有的跨存储引擎的功能也在这一层实现：存储过程、触发器、视图等。最下层为存储引擎，其负责MySQL中的数据存储和提取。和Linux下的文件系统类似，每种存储引擎都有其优势和劣势。中间的服务层通过API与存储引擎通信，这些API接口屏蔽了不同存储引擎间的差异。MySQL查询过程我们总是希望MySQL能够获得更高的查询性能，最好的办法是弄清楚MySQL是如何优化和执行查询的。一旦理解了这一点，就会发现：很多的查询优化工作实际上就是遵循一些原则让MySQL的优化器能够按照预想的合理方式运行而已。当向MySQL发送一个请求的时候，MySQL到底做了些什么呢？MySQL查询过程客户端/服务端通信协议MySQL客户端/服务端通信协议是“半双工”的：在任一时刻，要么是服务器向客户端发送数据，要么是客户端向服务器发送数据，这两个动作不能同时发生。一旦一端开始发送消息，另一端要接收完整个消息才能响应它，所以我们无法也无须将一个消息切成小块独立发送，也没有办法进行流量控制。客户端用一个单独的数据包将查询请求发送给服务器，所以当查询语句很长的时候，需要设置max_allowed_packet参数。但是需要注意的是，如果查询实在是太大，服务端会拒绝接收更多数据并抛出异常。与之相反的是，服务器响应给用户的数据通常会很多，由多个数据包组成。但是当服务器响应客户端请求时，客户端必须完整的接收整个返回结果，而不能简单的只取前面几条结果，然后让服务器停止发送。因而在实际开发中，尽量保持查询简单且只返回必需的数据，减小通信间数据包的大小和数量是一个非常好的习惯，这也是查询中尽量避免使用SELECT *以及加上LIMIT限制的原因之一。查询缓存在解析一个查询语句前，如果查询缓存是打开的，那么MySQL会检查这个查询语句是否命中查询缓存中的数据。如果当前查询恰好命中查询缓存，在检查一次用户权限后直接返回缓存中的结果。这种情况下，查询不会被解析，也不会生成执行计划，更不会执行。MySQL将缓存存放在一个引用表（不要理解成table，可以认为是类似于HashMap的数据结构），通过一个哈希值索引，这个哈希值通过查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息计算得来。所以两个查询在任何字符上的不同（例如：空格、注释），都会导致缓存不会命中。如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、mysql库中的系统表，其查询结果 都不会被缓存。比如函数NOW()或者CURRENT_DATE()会因为不同的查询时间，返回不同的查询结果，再比如包含CURRENT_USER或者CONNECION_ID()的查询语句会因为不同的用户而返回不同的结果，将这样的查询结果缓存起来没有任何的意义。既然是缓存，就会失效，那查询缓存何时失效呢？MySQL的查询缓存系统会跟踪查询中涉及的每个表，如果这些表（数据或结构）发生变化，那么和这张表相关的所有缓存数据都将失效。正因为如此，在任何的写操作时，MySQL必须将对应表的所有缓存都设置为失效。如果查询缓存非常大或者碎片很多，这个操作就可能带来很大的系统消耗，甚至导致系统僵死一会儿。而且查询缓存对系统的额外消耗也不仅仅在写操作，读操作也不例外：任何的查询语句在开始之前都必须经过检查，即使这条SQL语句永远不会命中缓存如果查询结果可以被缓存，那么执行完成后，会将结果存入缓存，也会带来额外的系统消耗基于此，我们要知道并不是什么情况下查询缓存都会提高系统性能，缓存和失效都会带来额外消耗，只有当缓存带来的资源节约大于其本身消耗的资源时，才会给系统带来性能提升。但要如何评估打开缓存是否能够带来性能提升是一件非常困难的事情，也不在本文讨论的范畴内。如果系统确实存在一些性能问题，可以尝试打开查询缓存，并在数据库设计上做一些优化，比如：用多个小表代替一个大表，注意不要过度设计批量插入代替循环单条插入合理控制缓存空间大小，一般来说其大小设置为几十兆比较合适可以通过SQL_CACHE和SQL_NO_CACHE来控制某个查询语句是否需要进行缓存最后的忠告是不要轻易打开查询缓存，特别是写密集型应用。如果你实在是忍不住，可以将query_cache_type设置为DEMAND，这时只有加入SQL_CACHE的查询才会走缓存，其他查询则不会，这样可以非常自由地控制哪些查询需要被缓存。当然查询缓存系统本身是非常复杂的，这里讨论的也只是很小的一部分，其他更深入的话题，比如：缓存是如何使用内存的？如何控制内存的碎片化？事务对查询缓存有何影响等等，读者可以自行阅读相关资料，这里权当抛砖引玉吧。语法解析和预处理MySQL通过关键字将SQL语句进行解析，并生成一颗对应的解析树。这个过程解析器主要通过语法规则来验证和解析。比如SQL中是否使用了错误的关键字或者关键字的顺序是否正确等等。预处理则会根据MySQL规则进一步检查解析树是否合法。比如检查要查询的数据表和数据列是否存在等等。查询优化经过前面的步骤生成的语法树被认为是合法的了，并且由优化器将其转化成查询计划。多数情况下，一条查询可以有很多种执行方式，最后都返回相应的结果。优化器的作用就是找到这其中最好的执行计划。MySQL使用基于成本的优化器，它尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。在MySQL可以通过查询当前会话的last_query_cost的值来得到其计算当前查询的成本。mysql> select * from t_message limit 10; ...省略结果集 mysql> show status like 'last_query_cost'; +-----------------+-------------+ | Variable_name   | Value       | +-----------------+-------------+ | Last_query_cost | 6391.799000 | +-----------------+-------------+示例中的结果表示优化器认为大概需要做6391个数据页的随机查找才能完成上面的查询。这个结果是根据一些列的统计信息计算得来的，这些统计信息包括：每张表或者索引的页面个数、索引的基数、索引和数据行的长度、索引的分布情况等等。有非常多的原因会导致MySQL选择错误的执行计划，比如统计信息不准确、不会考虑不受其控制的操作成本（用户自定义函数、存储过程）、MySQL认为的最优跟我们想的不一样（我们希望执行时间尽可能短，但MySQL值选择它认为成本小的，但成本小并不意味着执行时间短）等等。MySQL的查询优化器是一个非常复杂的部件，它使用了非常多的优化策略来生成一个最优的执行计划：重新定义表的关联顺序（多张表关联查询时，并不一定按照SQL中指定的顺序进行，但有一些技巧可以指定关联顺序）优化MIN()和MAX()函数（找某列的最小值，如果该列有索引，只需要查找B+Tree索引最左端，反之则可以找到最大值，具体原理见下文）提前终止查询（比如：使用Limit时，查找到满足数量的结果集后会立即终止查询）优化排序（在老版本MySQL会使用两次传输排序，即先读取行指针和需要排序的字段在内存中对其排序，然后再根据排序结果去读取数据行，而新版本采用的是单次传输排序，也就是一次读取所有的数据行，然后根据给定的列排序。对于I/O密集型应用，效率会高很多）随着MySQL的不断发展，优化器使用的优化策略也在不断的进化，这里仅仅介绍几个非常常用且容易理解的优化策略，其他的优化策略，大家自行查阅吧。查询执行引擎在完成解析和优化阶段以后，MySQL会生成对应的执行计划，查询执行引擎根据执行计划给出的指令逐步执行得出结果。整个执行过程的大部分操作均是通过调用存储引擎实现的接口来完成，这些接口被称为handler API。查询过程中的每一张表由一个handler实例表示。实际上，MySQL在查询优化阶段就为每一张表创建了一个handler实例，优化器可以根据这些实例的接口来获取表的相关信息，包括表的所有列名、索引统计信息等。存储引擎接口提供了非常丰富的功能，但其底层仅有几十个接口，这些接口像搭积木一样完成了一次查询的大部分操作。返回结果给客户端查询执行的最后一个阶段就是将结果返回给客户端。即使查询不到数据，MySQL仍然会返回这个查询的相关信息，比如该查询影响到的行数以及执行时间等等。如果查询缓存被打开且这个查询可以被缓存，MySQL也会将结果存放到缓存中。结果集返回客户端是一个增量且逐步返回的过程。有可能MySQL在生成第一条结果时，就开始向客户端逐步返回结果集了。这样服务端就无须存储太多结果而消耗过多内存，也可以让客户端第一时间获得返回结果。需要注意的是，结果集中的每一行都会以一个满足①中所描述的通信协议的数据包发送，再通过TCP协议进行传输，在传输过程中，可能对MySQL的数据包进行缓存然后批量发送。回头总结一下MySQL整个查询执行过程，总的来说分为6个步骤：客户端向MySQL服务器发送一条查询请求服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段服务器进行SQL解析、预处理、再由优化器生成对应的执行计划MySQL根据执行计划，调用存储引擎的API来执行查询将结果返回给客户端，同时缓存查询结果性能优化建议看了这么多，你可能会期待给出一些优化手段，是的，下面会从3个不同方面给出一些优化建议。但请等等，还有一句忠告要先送给你：不要听信你看到的关于优化的“绝对真理”，包括本文所讨论的内容，而应该是在实际的业务场景下通过测试来验证你关于执行计划以及响应时间的假设。Scheme设计与数据类型优化选择数据类型只要遵循小而简单的原则就好，越小的数据类型通常会更快，占用更少的磁盘、内存，处理时需要的CPU周期也更少。越简单的数据类型在计算时需要更少的CPU周期，比如，整型就比字符操作代价低，因而会使用整型来存储ip地址，使用DATETIME来存储时间，而不是使用字符串。这里总结几个可能容易理解错误的技巧：通常来说把可为NULL的列改为NOT NULL不会对性能提升有多少帮助，只是如果计划在列上创建索引，就应该将该列设置为NOT NULL。对整数类型指定宽度，比如INT(11)，没有任何卵用。INT使用32位（4个字节）存储空间，那么它的表示范围已经确定，所以INT(1)和INT(20)对于存储和计算是相同的。UNSIGNED表示不允许负值，大致可以使正数的上限提高一倍。比如TINYINT存储范围是-128 ~ 127，而UNSIGNED TINYINT存储的范围却是0 - 255。通常来讲，没有太大的必要使用DECIMAL数据类型。即使是在需要存储财务数据时，仍然可以使用BIGINT。比如需要精确到万分之一，那么可以将数据乘以一百万然后使用BIGINT存储。这样可以避免浮点数计算不准确和DECIMAL精确计算代价高的问题。TIMESTAMP使用4个字节存储空间，DATETIME使用8个字节存储空间。因而，TIMESTAMP只能表示1970 - 2038年，比DATETIME表示的范围小得多，而且TIMESTAMP的值因时区不同而不同。大多数情况下没有使用枚举类型的必要，其中一个缺点是枚举的字符串列表是固定的，添加和删除字符串（枚举选项）必须使用ALTER TABLE（如果只只是在列表末尾追加元素，不需要重建表）。schema的列不要太多。原因是存储引擎的API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列，这个转换过程的代价是非常高的。如果列太多而实际使用的列又很少的话，有可能会导致CPU占用过高。大表ALTER TABLE非常耗时，MySQL执行大部分修改表结果操作的方法是用新的结构创建一个张空表，从旧表中查出所有的数据插入新表，然后再删除旧表。尤其当内存不足而表又很大，而且还有很大索引的情况下，耗时更久。当然有一些奇技淫巧可以解决这个问题，有兴趣可自行查阅。创建高性能索引索引是提高MySQL查询性能的一个重要途径，但过多的索引可能会导致过高的磁盘使用率以及过高的内存占用，从而影响应用程序的整体性能。应当尽量避免事后才想起添加索引，因为事后可能需要监控大量的SQL才能定位到问题所在，而且添加索引的时间肯定是远大于初始添加索引所需要的时间，可见索引的添加也是非常有技术含量的。接下来将向你展示一系列创建高性能索引的策略，以及每条策略其背后的工作原理。但在此之前，先了解与索引相关的一些算法和数据结构，将有助于更好的理解后文的内容。索引相关的数据结构和算法通常我们所说的索引是指B-Tree索引，它是目前关系型数据库中查找数据最为常用和有效的索引，大多数存储引擎都支持这种索引。使用B-Tree这个术语，是因为MySQL在CREATE TABLE或其它语句中使用了这个关键字，但实际上不同的存储引擎可能使用不同的数据结构，比如InnoDB就是使用的B+Tree。B+Tree中的B是指balance，意为平衡。需要注意的是，B+树索引并不能找到一个给定键值的具体行，它找到的只是被查找数据行所在的页，接着数据库会把页读入到内存，再在内存中进行查找，最后得到要查找的数据。在介绍B+Tree前，先了解一下二叉查找树，它是一种经典的数据结构，其左子树的值总是小于根的值，右子树的值总是大于根的值，如下图①。如果要在这课树中查找值为5的记录，其大致流程：先找到根，其值为6，大于5，所以查找左子树，找到3，而5大于3，接着找3的右子树，总共找了3次。同样的方法，如果查找值为8的记录，也需要查找3次。所以二叉查找树的平均查找次数为(3 + 3 + 3 + 2 + 2 + 1) / 6 = 2.3次，而顺序查找的话，查找值为2的记录，仅需要1次，但查找值为8的记录则需要6次，所以顺序查找的平均查找次数为：(1 + 2 + 3 + 4 + 5 + 6) / 6 = 3.3次，因此大多数情况下二叉查找树的平均查找速度比顺序查找要快。二叉查找树和平衡二叉树由于二叉查找树可以任意构造，同样的值，可以构造出如图②的二叉查找树，显然这棵二叉树的查询效率和顺序查找差不多。若想二叉查找数的查询性能最高，需要这棵二叉查找树是平衡的，也即平衡二叉树（AVL树）。平衡二叉树首先需要符合二叉查找树的定义，其次必须满足任何节点的两个子树的高度差不能大于1。显然图②不满足平衡二叉树的定义，而图①是一课平衡二叉树。平衡二叉树的查找性能是比较高的（性能最好的是最优二叉树），查询性能越好，维护的成本就越大。比如图①的平衡二叉树，当用户需要插入一个新的值9的节点时，就需要做出如下变动。平衡二叉树旋转通过一次左旋操作就将插入后的树重新变为平衡二叉树是最简单的情况了，实际应用场景中可能需要旋转多次。至此我们可以考虑一个问题，平衡二叉树的查找效率还不错，实现也非常简单，相应的维护成本还能接受，为什么MySQL索引不直接使用平衡二叉树？随着数据库中数据的增加，索引本身大小随之增加，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级。可以想象一下一棵几百万节点的二叉树的深度是多少？如果将这么大深度的一颗二叉树放磁盘上，每读取一个节点，需要一次磁盘的I/O读取，整个查找的耗时显然是不能够接受的。那么如何减少查找过程中的I/O存取次数？一种行之有效的解决方法是减少树的深度，将二叉树变为m叉树（多路搜索树），而B+Tree就是一种多路搜索树。理解B+Tree时，只需要理解其最重要的两个特征即可：第一，所有的关键字（可以理解为数据）都存储在叶子节点（Leaf Page），非叶子节点（Index Page）并不存储真正的数据，所有记录节点都是按键值大小顺序存放在同一层叶子节点上。其次，所有的叶子节点由指针连接。如下图为高度为2的简化了的B+Tree。简化B+Tree怎么理解这两个特征？MySQL将每个节点的大小设置为一个页的整数倍（原因下文会介绍），也就是在节点空间大小一定的情况下，每个节点可以存储更多的内结点，这样每个结点能索引的范围更大更精确。所有的叶子节点使用指针链接的好处是可以进行区间访问，比如上图中，如果查找大于20而小于30的记录，只需要找到节点20，就可以遍历指针依次找到25、30。如果没有链接指针的话，就无法进行区间查找。这也是MySQL使用B+Tree作为索引存储结构的重要原因。MySQL为何将节点大小设置为页的整数倍，这就需要理解磁盘的存储原理。磁盘本身存取就比主存慢很多，在加上机械运动损耗（特别是普通的机械硬盘），磁盘的存取速度往往是主存的几百万分之一，为了尽量减少磁盘I/O，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存，预读的长度一般为页的整数倍。页是计算机管理存储器的逻辑块，硬件及OS往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（许多OS中，页的大小通常为4K）。主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后一起返回，程序继续运行。MySQL巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。为了达到这个目的，每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了读取一个节点只需一次I/O。假设B+Tree的高度为h，一次检索最多需要h-1次I/O（根节点常驻内存），复杂度O(h) = O(logmN)。实际应用场景中，M通常较大，常常超过100，因此树的高度一般都比较小，通常不超过3。最后简单了解下B+Tree节点的操作，在整体上对索引的维护有一个大概的了解，虽然索引可以大大提高查询效率，但维护索引仍要花费很大的代价，因此合理的创建索引也就尤为重要。仍以上面的树为例，我们假设每个节点只能存储4个内节点。首先要插入第一个节点28，如下图所示。leaf page和index page都没有满接着插入下一个节点70，在Index Page中查询后得知应该插入到50 - 70之间的叶子节点，但叶子节点已满，这时候就需要进行也分裂的操作，当前的叶子节点起点为50，所以根据中间值来拆分叶子节点，如下图所示。Leaf Page拆分最后插入一个节点95，这时候Index Page和Leaf Page都满了，就需要做两次拆分，如下图所示。Leaf Page与Index Page拆分拆分后最终形成了这样一颗树。最终树B+Tree为了保持平衡，对于新插入的值需要做大量的拆分页操作，而页的拆分需要I/O操作，为了尽可能的减少页的拆分操作，B+Tree也提供了类似于平衡二叉树的旋转功能。当Leaf Page已满但其左右兄弟节点没有满的情况下，B+Tree并不急于去做拆分操作，而是将记录移到当前所在页的兄弟节点上。通常情况下，左兄弟会被先检查用来做旋转操作。就比如上面第二个示例，当插入70的时候，并不会去做页拆分，而是左旋操作。左旋操作通过旋转操作可以最大限度的减少页分裂，从而减少索引维护过程中的磁盘的I/O操作，也提高索引维护效率。需要注意的是，删除节点跟插入节点类似，仍然需要旋转和拆分操作，这里就不再说明。高性能策略通过上文，相信你对B+Tree的数据结构已经有了大致的了解，但MySQL中索引是如何组织数据的存储呢？以一个简单的示例来说明，假如有如下数据表：CREATE TABLE People(    last_name varchar(50) not null,    first_name varchar(50) not null,    dob date not null,    gender enum(\`m\`,\`f\`) not null,    key(last_name,first_name,dob) );对于表中每一行数据，索引中包含了last_name、first_name、dob列的值，下图展示了索引是如何组织数据存储的。索引如何组织数据存储，来自：高性能MySQL可以看到，索引首先根据第一个字段来排列顺序，当名字相同时，则根据第三个字段，即出生日期来排序，正是因为这个原因，才有了索引的“最左原则”。1、MySQL不会使用索引的情况：非独立的列“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数。比如：select * from where id + 1 = 5我们很容易看出其等价于 id = 4，但是MySQL无法自动解析这个表达式，使用函数是同样的道理。2、前缀索引如果列很长，通常可以索引开始的部分字符，这样可以有效节约索引空间，从而提高索引效率。3、多列索引和索引顺序在多数情况下，在多个列上建立独立的索引并不能提高查询性能。理由非常简单，MySQL不知道选择哪个索引的查询效率更好，所以在老版本，比如MySQL5.0之前就会随便选择一个列的索引，而新的版本会采用合并索引的策略。举个简单的例子，在一张电影演员表中，在actor_id和film_id两个列上都建立了独立的索引，然后有如下查询：select film_id,actor_id from film_actor where actor_id = 1 or film_id = 1老版本的MySQL会随机选择一个索引，但新版本做如下的优化：select film_id,actor_id from film_actor where actor_id = 1   union all  select film_id,actor_id from film_actor where film_id = 1 and actor_id <> 1当出现多个索引做相交操作时（多个AND条件），通常来说一个包含所有相关列的索引要优于多个独立索引。当出现多个索引做联合操作时（多个OR条件），对结果集的合并、排序等操作需要耗费大量的CPU和内存资源，特别是当其中的某些索引的选择性不高，需要返回合并大量数据时，查询成本更高。所以这种情况下还不如走全表扫描。因此explain时如果发现有索引合并（Extra字段出现Using union），应该好好检查一下查询和表结构是不是已经是最优的，如果查询和表都没有问题，那只能说明索引建的非常糟糕，应当慎重考虑索引是否合适，有可能一个包含所有相关列的多列索引更适合。前面我们提到过索引如何组织数据存储的，从图中可以看到多列索引时，索引的顺序对于查询是至关重要的，很明显应该把选择性更高的字段放到索引的前面，这样通过第一个字段就可以过滤掉大多数不符合条件的数据。索引选择性是指不重复的索引值和数据表的总记录数的比值，选择性越高查询效率越高，因为选择性越高的索引可以让MySQL在查询时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。理解索引选择性的概念后，就不难确定哪个字段的选择性较高了，查一下就知道了，比如：SELECT * FROM payment where staff_id = 2 and customer_id = 584是应该创建(staff_id,customer_id)的索引还是应该颠倒一下顺序？执行下面的查询，哪个字段的选择性更接近1就把哪个字段索引前面就好。select count(distinct staff_id)/count(*) as staff_id_selectivity,       count(distinct customer_id)/count(*) as customer_id_selectivity,       count(*) from payment多数情况下使用这个原则没有任何问题，但仍然注意你的数据中是否存在一些特殊情况。举个简单的例子，比如要查询某个用户组下有过交易的用户信息：select user_id from trade where user_group_id = 1 and trade_amount > 0MySQL为这个查询选择了索引(user_group_id,trade_amount)，如果不考虑特殊情况，这看起来没有任何问题，但实际情况是这张表的大多数数据都是从老系统中迁移过来的，由于新老系统的数据不兼容，所以就给老系统迁移过来的数据赋予了一个默认的用户组。这种情况下，通过索引扫描的行数跟全表扫描基本没什么区别，索引也就起不到任何作用。推广开来说，经验法则和推论在多数情况下是有用的，可以指导我们开发和设计，但实际情况往往会更复杂，实际业务场景下的某些特殊情况可能会摧毁你的整个设计。4、避免多个范围条件实际开发中，我们会经常使用多个范围条件，比如想查询某个时间段内登录过的用户：select user.* from user where login_time > '2017-04-01' and age between 18 and 30;这个查询有一个问题：它有两个范围条件，login_time列和age列，MySQL可以使用login_time列的索引或者age列的索引，但无法同时使用它们。5、覆盖索引如果一个索引包含或者说覆盖所有需要查询的字段的值，那么就没有必要再回表查询，这就称为覆盖索引。覆盖索引是非常有用的工具，可以极大的提高性能，因为查询只需要扫描索引会带来许多好处：索引条目远小于数据行大小，如果只读取索引，极大减少数据访问量索引是有按照列值顺序存储的，对于I/O密集型的范围查询要比随机从磁盘读取每一行数据的IO要少的多6、使用索引扫描来排序MySQL有两种方式可以生产有序的结果集，其一是对结果集进行排序的操作，其二是按照索引顺序扫描得出的结果自然是有序的。如果explain的结果中type列的值为index表示使用了索引扫描来做排序。扫描索引本身很快，因为只需要从一条索引记录移动到相邻的下一条记录。但如果索引本身不能覆盖所有需要查询的列，那么就不得不每扫描一条索引记录就回表查询一次对应的行。这个读取操作基本上是随机I/O，因此按照索引顺序读取数据的速度通常要比顺序地全表扫描要慢。在设计索引时，如果一个索引既能够满足排序，又满足查询，是最好的。只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向也一样时，才能够使用索引来对结果做排序。如果查询需要关联多张表，则只有ORDER BY子句引用的字段全部为第一张表时，才能使用索引做排序。ORDER BY子句和查询的限制是一样的，都要满足最左前缀的要求（有一种情况例外，就是最左的列被指定为常数，下面是一个简单的示例），其他情况下都需要执行排序操作，而无法利用索引排序。// 最左列为常数，索引：(date,staff_id,customer_id) select  staff_id,customer_id from demo where date = '2015-06-01' order by staff_id,customer_id7、冗余和重复索引冗余索引是指在相同的列上按照相同的顺序创建的相同类型的索引，应当尽量避免这种索引，发现后立即删除。比如有一个索引(A,B)，再创建索引(A)就是冗余索引。冗余索引经常发生在为表添加新索引时，比如有人新建了索引(A,B)，但这个索引不是扩展已有的索引(A)。大多数情况下都应该尽量扩展已有的索引而不是创建新索引。但有极少情况下出现性能方面的考虑需要冗余索引，比如扩展已有索引而导致其变得过大，从而影响到其他使用该索引的查询。8、删除长期未使用的索引定期删除一些长时间未使用过的索引是一个非常好的习惯。关于索引这个话题打算就此打住，最后要说一句，索引并不总是最好的工具，只有当索引帮助提高查询速度带来的好处大于其带来的额外工作时，索引才是有效的。对于非常小的表，简单的全表扫描更高效。对于中到大型的表，索引就非常有效。对于超大型的表，建立和维护索引的代价随之增长，这时候其他技术也许更有效，比如分区表。最后的最后，explain后再提测是一种美德。特定类型查询优化优化COUNT()查询COUNT()可能是被大家误解最多的函数了，它有两种不同的作用，其一是统计某个列值的数量，其二是统计行数。统计列值时，要求列值是非空的，它不会统计NULL。如果确认括号中的表达式不可能为空时，实际上就是在统计行数。最简单的就是当使用COUNT(*)时，并不是我们所想象的那样扩展成所有的列，实际上，它会忽略所有的列而直接统计行数。我们最常见的误解也就在这儿，在括号内指定了一列却希望统计结果是行数，而且还常常误以为前者的性能会更好。但实际并非这样，如果要统计行数，直接使用COUNT(*)，意义清晰，且性能更好。有时候某些业务场景并不需要完全精确的COUNT值，可以用近似值来代替，EXPLAIN出来的行数就是一个不错的近似值，而且执行EXPLAIN并不需要真正地去执行查询，所以成本非常低。通常来说，执行COUNT()都需要扫描大量的行才能获取到精确的数据，因此很难优化，MySQL层面还能做得也就只有覆盖索引了。如果不还能解决问题，只有从架构层面解决了，比如添加汇总表，或者使用redis这样的外部缓存系统。优化关联查询在大数据场景下，表与表之间通过一个冗余字段来关联，要比直接使用JOIN有更好的性能。如果确实需要使用关联查询的情况下，需要特别注意的是：确保ON和USING字句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。当表A和表B用列c关联的时候，如果优化器关联的顺序是A、B，那么就不需要在A表的对应列上创建索引。没有用到的索引会带来额外的负担，一般来说，除非有其他理由，只需要在关联顺序中的第二张表的相应列上创建索引（具体原因下文分析）。确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列，这样MySQL才有可能使用索引来优化。要理解优化关联查询的第一个技巧，就需要理解MySQL是如何执行关联查询的。当前MySQL关联执行的策略非常简单，它对任何的关联都执行嵌套循环关联操作，即先在一个表中循环取出单条数据，然后在嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为为止。然后根据各个表匹配的行，返回查询中需要的各个列。太抽象了？以上面的示例来说明，比如有这样的一个查询：SELECT A.xx,B.yy  FROM A INNER JOIN B USING(c) WHERE A.xx IN (5,6)假设MySQL按照查询中的关联顺序A、B来进行关联操作，那么可以用下面的伪代码表示MySQL如何完成这个查询：outer_iterator = SELECT A.xx,A.c FROM A WHERE A.xx IN (5,6); outer_row = outer_iterator.next; while(outer_row) {    inner_iterator = SELECT B.yy FROM B WHERE B.c = outer_row.c;    inner_row = inner_iterator.next;    while(inner_row) {        output\[inner_row.yy,outer_row.xx\];        inner_row = inner_iterator.next;    }    outer_row = outer_iterator.next; }可以看到，最外层的查询是根据A.xx列来查询的，A.c上如果有索引的话，整个关联查询也不会使用。再看内层的查询，很明显B.c上如果有索引的话，能够加速查询，因此只需要在关联顺序中的第二张表的相应列上创建索引即可。优化LIMIT分页当需要分页操作时，通常会使用LIMIT加上偏移量的办法实现，同时加上合适的ORDER BY字句。如果有对应的索引，通常效率会不错，否则，MySQL需要做大量的文件排序操作。一个常见的问题是当偏移量非常大的时候，比如：LIMIT 10000 20这样的查询，MySQL需要查询10020条记录然后只返回20条记录，前面的10000条都将被抛弃，这样的代价非常高。优化这种查询一个最简单的办法就是尽可能的使用覆盖索引扫描，而不是查询所有的列。然后根据需要做一次关联查询再返回所有的列。对于偏移量很大时，这样做的效率会提升非常大。考虑下面的查询：SELECT film_id,description FROM film ORDER BY title LIMIT 50,5;如果这张表非常大，那么这个查询最好改成下面的样子：SELECT film.film_id,film.description FROM film INNER JOIN (    SELECT film_id FROM film ORDER BY title LIMIT 50,5 ) AS tmp USING(film_id);这里的延迟关联将大大提升查询效率，让MySQL扫描尽可能少的页面，获取需要访问的记录后在根据关联列回原表查询所需要的列。有时候如果可以使用书签记录上次取数据的位置，那么下次就可以直接从该书签记录的位置开始扫描，这样就可以避免使用OFFSET，比如下面的查询：SELECT id FROM t LIMIT 10000, 10; 改为： SELECT id FROM t WHERE id > 10000 LIMIT 10;其他优化的办法还包括使用预先计算的汇总表，或者关联到一个冗余表，冗余表中只包含主键列和需要做排序的列。优化UNIONMySQL处理UNION的策略是先创建临时表，然后再把各个查询结果插入到临时表中，最后再来做查询。因此很多优化策略在UNION查询中都没有办法很好的时候。经常需要手动将WHERE、LIMIT、ORDER BY等字句“下推”到各个子查询中，以便优化器可以充分利用这些条件先优化。除非确实需要服务器去重，否则就一定要使用UNION ALL，如果没有ALL关键字，MySQL会给临时表加上DISTINCT选项，这会导致整个临时表的数据做唯一性检查，这样做的代价非常高。当然即使使用ALL关键字，MySQL总是将结果放入临时表，然后再读出，再返回给客户端。虽然很多时候没有这个必要，比如有时候可以直接把每个子查询的结果返回给客户端。结语理解查询是如何执行以及时间都消耗在哪些地方，再加上一些优化过程的知识，可以帮助大家更好的理解MySQL，理解常见优化技巧背后的原理。希望本文中的原理、示例能够帮助大家更好的将理论和实践联系起来，更多的将理论知识运用到实践中。其他也没啥说的了，给大家留两个思考题吧，可以在脑袋里想想答案，这也是大家经常挂在嘴边的，但很少有人会思考为什么？有非常多的程序员在分享时都会抛出这样一个观点：尽可能不要使用存储过程，存储过程非常不容易维护，也会增加使用成本，应该把业务逻辑放到客户端。既然客户端都能干这些事，那为什么还要存储过程？JOIN本身也挺方便的，直接查询就好了，为什么还需要视图呢？作者：CHEN川 链接：https://www.jianshu.com/p/d7665192aaaf 來源：简书

一个慢查询引发的思考之MySQL索引原理及慢查询优化 一个慢查询引发的思考之MySQL索引原理及慢查询优化背景MySQL凭借着出色的性能、低廉的成本、丰富的资源，已经成为绝大多数互联网公司的首选关系型数据库。虽然性能出色，但所谓“好马配好鞍”，如何能够更好的使用它，已经成为开发工程师的必修课，我们经常会从职位描述上看到诸如“精通MySQL”、“SQL语句优化”、“了解数据库原理”等要求。我们知道一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，所以查询语句的优化显然是重中之重。本人从2013年7月份起，一直在美团核心业务系统部做慢查询的优化工作，共计十余个系统，累计解决和积累了上百个慢查询案例。随着业务的复杂性提升，遇到的问题千奇百怪，五花八门，匪夷所思。本文旨在以开发工程师的角度来解释数据库索引的原理和如何优化慢查询。一个慢查询引发的思考select    count(*)  from    task  where    status=2     and operator_id=20839     and operate_time>1371169729     and operate_time<1371174603     and type=2;系统使用者反应有一个功能越来越慢，于是工程师找到了上面的SQL。并且兴致冲冲的找到了我，“这个SQL需要优化，给我把每个字段都加上索引”。我很惊讶，问道：“为什么需要每个字段都加上索引？”“把查询的字段都加上索引会更快”，工程师信心满满。“这种情况完全可以建一个联合索引，因为是最左前缀匹配，所以operate_time需要放到最后，而且还需要把其他相关的查询都拿来，需要做一个综合评估。”“联合索引？最左前缀匹配？综合评估？”工程师不禁陷入了沉思。多数情况下，我们知道索引能够提高查询效率，但应该如何建立索引？索引的顺序如何？许多人却只知道大概。其实理解这些概念并不难，而且索引的原理远没有想象的那么复杂。MySQL索引原理索引目的索引的目的在于提高查询效率，可以类比字典，如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。如果没有索引，那么你可能需要把所有单词看一遍才能找到你想要的，如果我想找到m开头的单词呢？或者ze开头的单词呢？是不是觉得如果没有索引，这个事情根本无法完成？索引原理除了词典，生活中随处可见索引的例子，如火车站的车次表、图书的目录等。它们的原理都是一样的，通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是我们总是通过同一种查找方式来锁定数据。数据库也是一样，但显然要复杂许多，因为不仅面临着等值查询，还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢？我们回想字典的例子，能不能把数据分成段，然后分段查询呢？最简单的如果1000条数据，1到100分成第一段，101到200分成第二段，201到300分成第三段……这样查第250条数据，只要找第三段就可以了，一下子去除了90%的无效数据。但如果是1千万的记录呢，分成几段比较好？稍有算法基础的同学会想到搜索树，其平均复杂度是lgN，具有不错的查询性能。但这里我们忽略了一个关键的问题，复杂度模型是基于每次相同的操作成本来考虑的，数据库实现比较复杂，数据保存在磁盘上，而为了提高性能，每次又可以把部分数据读入内存来计算，因为我们知道访问磁盘的成本大概是访问内存的十万倍左右，所以简单的搜索树难以满足复杂的应用场景。磁盘IO与预读前面提到了访问磁盘，那么这里先简单介绍一下磁盘IO和预读，磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分，寻道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下；旋转延迟就是我们经常听说的磁盘转速，比如一个磁盘7200转，表示每分钟能转7200次，也就是说1秒钟能转120次，旋转延迟就是1/120/2 = 4.17ms；传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计。那么访问一次磁盘的时间，即一次磁盘IO的时间约等于5+4.17 = 9ms左右，听起来还挺不错的，但要知道一台500 -MIPS的机器每秒可以执行5亿条指令，因为指令依靠的是电的性质，换句话说执行一次IO的时间可以执行40万条指令，数据库动辄十万百万乃至千万级数据，每次9毫秒的时间，显然是个灾难。下图是计算机硬件延迟的对比图，供大家参考：various-system-software-hardware-latencies考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。索引的数据结构前面讲了生活中索引的例子，索引的基本原理，数据库的复杂性，又讲了操作系统的相关知识，目的就是让大家了解，任何一种数据结构都不是凭空产生的，一定会有它的背景和使用场景，我们现在总结一下，我们需要这种数据结构能够做些什么，其实很简单，那就是：每次查找数据时把磁盘IO次数控制在一个很小的数量级，最好是常数数量级。那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢？就这样，b+树应运而生。详解b+树b+树如上图，是一颗b+树，关于b+树的定义可以参见B+树，这里只说一些重点，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。b+树的查找过程如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。b+树性质1.通过上面的分析，我们知道IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。2.当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。慢查询优化关于MySQL索引原理是比较枯燥的东西，大家只需要有一个感性的认识，并不需要理解得非常透彻和深入。我们回头来看看一开始我们说的慢查询，了解完索引原理之后，大家是不是有什么想法呢？先总结一下索引的几大基本原则：建索引的几大原则1.最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。2.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。3.尽量选择区分度高的列作为索引，区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。4.索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。5.尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。回到开始的慢查询根据最左匹配原则，最开始的sql语句的索引应该是status、operator_id、type、operate_time的联合索引；其中status、operator_id、type的顺序可以颠倒，所以我才会说，把这个表的所有相关查询都找到，会综合分析；比如还有如下查询：select * from task where status = 0 and type = 12 limit 10; `````` select count(*) from task where status = 0 ;那么索引建立成(status,type,operator_id,operate_time)就是非常正确的，因为可以覆盖到所有情况。这个就是利用了索引的最左匹配的原则查询优化神器 - explain命令关于explain命令相信大家并不陌生，具体用法和字段含义可以参考官网explain-output，这里需要强调rows是核心指标，绝大部分rows小的语句执行一定很快（有例外，下面会讲到）。所以优化语句基本上都是在优化rows。慢查询优化基本步骤0.先运行看看是否真的很慢，注意设置SQL_NO_CACHE1.where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高2.explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）3.order by limit 形式的sql语句让排序的表优先查4.了解业务方使用场景5.加索引时参照建索引的几大原则6.观察结果，不符合预期继续从0分析几个慢查询案例下面几个例子详细解释了如何分析和优化慢查询。复杂语句写法很多情况下，我们写SQL只是为了实现功能，这只是第一步，不同的语句书写方式对于效率往往有本质的差别，这要求我们对mysql的执行计划和索引原则有非常清楚的认识，请看下面的语句：select    distinct cert.emp_id  from    cm_log cl  inner join    (      select          emp.id as emp_id,          emp_cert.id as cert_id        from          employee emp        left join          emp_certificate emp_cert              on emp.id = emp_cert.emp_id        where          emp.is_deleted=0    ) cert        on (          cl.ref_table='Employee'           and cl.ref_oid= cert.emp_id       )        or (          cl.ref_table='EmpCertificate'           and cl.ref_oid= cert.cert_id       )  where    cl.last_upd_date >='2013-11-07 15:03:00'     and cl.last_upd_date<='2013-11-08 16:00:00';0.先运行一下，53条记录 1.87秒，又没有用聚合语句，比较慢53 rows in set (1.87 sec)1.explain+----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+| id | select_type | table      | type  | possible_keys                   | key                   | key_len | ref               | rows  | Extra                          | +----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+|  1 | PRIMARY     | cl         | range | cm_log_cls_id,idx_last_upd_date | idx_last_upd_date     | 8       | NULL              |   379 | Using where; Using temporary   | |  1 | PRIMARY     | <derived2> | ALL   | NULL                            | NULL                  | NULL    | NULL              | 63727 | Using where; Using join buffer | |  2 | DERIVED     | emp        | ALL   | NULL                            | NULL                  | NULL    | NULL              | 13317 | Using where                    | |  2 | DERIVED     | emp_cert   | ref   | emp_certificate_empid           | emp_certificate_empid | 4       | meituanorg.emp.id |     1 | Using index                    | +----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+简述一下执行计划，首先mysql根据idx_last_upd_date索引扫描cm_log表获得379条记录；然后查表扫描了63727条记录，分为两部分，derived表示构造表，也就是不存在的表，可以简单理解成是一个语句形成的结果集，后面的数字表示语句的ID。derived2表示的是ID = 2的查询构造了虚拟表，并且返回了63727条记录。我们再来看看ID = 2的语句究竟做了写什么返回了这么大量的数据，首先全表扫描employee表13317条记录，然后根据索引emp_certificate_empid关联emp_certificate表，rows = 1表示，每个关联都只锁定了一条记录，效率比较高。获得后，再和cm_log的379条记录根据规则关联。从执行过程上可以看出返回了太多的数据，返回的数据绝大部分cm_log都用不到，因为cm_log只锁定了379条记录。如何优化呢？可以看到我们在运行完后还是要和cm_log做join,那么我们能不能之前和cm_log做join呢？仔细分析语句不难发现，其基本思想是如果cm_log的ref_table是EmpCertificate就关联emp_certificate表，如果ref_table是Employee就关联employee表，我们完全可以拆成两部分，并用union连接起来，注意这里用union，而不用union all是因为原语句有“distinct”来得到唯一的记录，而union恰好具备了这种功能。如果原语句中没有distinct不需要去重，我们就可以直接使用union all了，因为使用union需要去重的动作，会影响SQL性能。优化过的语句如下：select    emp.id  from    cm_log cl  inner join    employee emp        on cl.ref_table = 'Employee'        and cl.ref_oid = emp.id   where    cl.last_upd_date >='2013-11-07 15:03:00'     and cl.last_upd_date<='2013-11-08 16:00:00'     and emp.is_deleted = 0  unionselect    emp.id  from    cm_log cl  inner join    emp_certificate ec        on cl.ref_table = 'EmpCertificate'        and cl.ref_oid = ec.id   inner join    employee emp        on emp.id = ec.emp_id   where    cl.last_upd_date >='2013-11-07 15:03:00'     and cl.last_upd_date<='2013-11-08 16:00:00'     and emp.is_deleted = 04.不需要了解业务场景，只需要改造的语句和改造之前的语句保持结果一致5.现有索引可以满足，不需要建索引6.用改造后的语句实验一下，只需要10ms 降低了近200倍！+----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+| id | select_type  | table      | type   | possible_keys                   | key               | key_len | ref                   | rows | Extra       | +----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+|  1 | PRIMARY      | cl         | range  | cm_log_cls_id,idx_last_upd_date | idx_last_upd_date | 8       | NULL                  |  379 | Using where | |  1 | PRIMARY      | emp        | eq_ref | PRIMARY                         | PRIMARY           | 4       | meituanorg.cl.ref_oid |    1 | Using where | |  2 | UNION        | cl         | range  | cm_log_cls_id,idx_last_upd_date | idx_last_upd_date | 8       | NULL                  |  379 | Using where | |  2 | UNION        | ec         | eq_ref | PRIMARY,emp_certificate_empid   | PRIMARY           | 4       | meituanorg.cl.ref_oid |    1 |             | |  2 | UNION        | emp        | eq_ref | PRIMARY                         | PRIMARY           | 4       | meituanorg.ec.emp_id  |    1 | Using where | | NULL | UNION RESULT | <union1,2> | ALL    | NULL                            | NULL              | NULL    | NULL                  | NULL |             | +----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+53 rows in set (0.01 sec)明确应用场景举这个例子的目的在于颠覆我们对列的区分度的认知，一般上我们认为区分度越高的列，越容易锁定更少的记录，但在一些特殊的情况下，这种理论是有局限性的。select    *  from    stage_poi sp  where    sp.accurate_result=1     and (       sp.sync_status=0        or sp.sync_status=2        or sp.sync_status=4    );0.先看看运行多长时间,951条数据6.22秒，真的很慢。951 rows in set (6.22 sec)1.先explain，rows达到了361万，type = ALL表明是全表扫描。+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       | +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+|  1 | SIMPLE      | sp    | ALL  | NULL          | NULL | NULL    | NULL | 3613155 | Using where | +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+2.所有字段都应用查询返回记录数，因为是单表查询 0已经做过了951条。3.让explain的rows 尽量逼近951。看一下accurate_result = 1的记录数：select count(*),accurate_result from stage_poi  group by accurate_result; +----------+-----------------+| count(*) | accurate_result | +----------+-----------------+|     1023 |              -1 | |  2114655 |               0 | |   972815 |               1 | +----------+-----------------+我们看到accurate_result这个字段的区分度非常低，整个表只有-1,0,1三个值，加上索引也无法锁定特别少量的数据。再看一下sync_status字段的情况：select count(*),sync_status from stage_poi  group by sync_status; +----------+-------------+| count(*) | sync_status | +----------+-------------+|     3080 |           0 | |  3085413 |           3 | +----------+-------------+同样的区分度也很低，根据理论，也不适合建立索引。问题分析到这，好像得出了这个表无法优化的结论，两个列的区分度都很低，即便加上索引也只能适应这种情况，很难做普遍性的优化，比如当sync_status 0、3分布的很平均，那么锁定记录也是百万级别的。4.找业务方去沟通，看看使用场景。业务方是这么来使用这个SQL语句的，每隔五分钟会扫描符合条件的数据，处理完成后把sync_status这个字段变成1,五分钟符合条件的记录数并不会太多，1000个左右。了解了业务方的使用场景后，优化这个SQL就变得简单了，因为业务方保证了数据的不平衡，如果加上索引可以过滤掉绝大部分不需要的数据。5.根据建立索引规则，使用如下语句建立索引alter table stage_poi add index idx_acc_status(accurate_result,sync_status);6.观察预期结果,发现只需要200ms，快了30多倍。952 rows in set (0.20 sec)我们再来回顾一下分析问题的过程，单表查询相对来说比较好优化，大部分时候只需要把where条件里面的字段依照规则加上索引就好，如果只是这种“无脑”优化的话，显然一些区分度非常低的列，不应该加索引的列也会被加上索引，这样会对插入、更新性能造成严重的影响，同时也有可能影响其它的查询语句。所以我们第4步调差SQL的使用场景非常关键，我们只有知道这个业务场景，才能更好地辅助我们更好的分析和优化查询语句。无法优化的语句select    c.id,    c.name,    c.position,    c.sex,    c.phone,    c.office_phone,    c.feature_info,    c.birthday,    c.creator_id,    c.is_keyperson,    c.giveup_reason,    c.status,    c.data_source,    from_unixtime(c.created_time) as created_time,    from_unixtime(c.last_modified) as last_modified,    c.last_modified_user_id   from    contact c   inner join    contact_branch cb        on  c.id = cb.contact_id   inner join    branch_user bu        on  cb.branch_id = bu.branch_id        and bu.status in (         1,      2)      inner join       org_emp_info oei           on  oei.data_id = bu.user_id           and oei.node_left >= 2875           and oei.node_right <= 10802           and oei.org_category = - 1      order by       c.created_time desc  limit 0 ,      10;还是几个步骤。0.先看语句运行多长时间，10条记录用了13秒，已经不可忍受。10 rows in set (13.06 sec)1.explain+----+-------------+-------+--------+-------------------------------------+-------------------------+---------+--------------------------+------+----------------------------------------------+| id | select_type | table | type   | possible_keys                       | key                     | key_len | ref                      | rows | Extra                                        | +----+-------------+-------+--------+-------------------------------------+-------------------------+---------+--------------------------+------+----------------------------------------------+|  1 | SIMPLE      | oei   | ref    | idx_category_left_right,idx_data_id | idx_category_left_right | 5       | const                    | 8849 | Using where; Using temporary; Using filesort | |  1 | SIMPLE      | bu    | ref    | PRIMARY,idx_userid_status           | idx_userid_status       | 4       | meituancrm.oei.data_id   |   76 | Using where; Using index                     | |  1 | SIMPLE      | cb    | ref    | idx_branch_id,idx_contact_branch_id | idx_branch_id           | 4       | meituancrm.bu.branch_id  |    1 |                                              | |  1 | SIMPLE      | c     | eq_ref | PRIMARY                             | PRIMARY                 | 108     | meituancrm.cb.contact_id |    1 |                                              | +----+-------------+-------+--------+-------------------------------------+-------------------------+---------+--------------------------+------+----------------------------------------------+从执行计划上看，mysql先查org_emp_info表扫描8849记录，再用索引idx_userid_status关联branch_user表，再用索引idx_branch_id关联contact_branch表，最后主键关联contact表。rows返回的都非常少，看不到有什么异常情况。我们在看一下语句，发现后面有order by + limit组合，会不会是排序量太大搞的？于是我们简化SQL，去掉后面的order by 和 limit，看看到底用了多少记录来排序。select   count(*)from    contact c   inner join    contact_branch cb        on  c.id = cb.contact_id   inner join    branch_user bu        on  cb.branch_id = bu.branch_id        and bu.status in (         1,      2)      inner join       org_emp_info oei           on  oei.data_id = bu.user_id           and oei.node_left >= 2875           and oei.node_right <= 10802           and oei.org_category = - 1  +----------+| count(*) | +----------+|   778878 | +----------+1 row in set (5.19 sec)发现排序之前居然锁定了778878条记录，如果针对70万的结果集排序，将是灾难性的，怪不得这么慢，那我们能不能换个思路，先根据contact的created_time排序，再来join会不会比较快呢？于是改造成下面的语句，也可以用straight_join来优化：select   c.id,   c.name,   c.position,   c.sex,   c.phone,   c.office_phone,   c.feature_info,   c.birthday,   c.creator_id,   c.is_keyperson,   c.giveup_reason,   c.status,   c.data_source,    from_unixtime(c.created_time) as created_time,    from_unixtime(c.last_modified) as last_modified,   c.last_modified_user_id    from    contact c  where    exists (       select         1        from          contact_branch cb         inner join          branch_user bu                     on  cb.branch_id = bu.branch_id                     and bu.status in (               1,            2)                inner join             org_emp_info oei                           on  oei.data_id = bu.user_id                           and oei.node_left >= 2875                           and oei.node_right <= 10802                           and oei.org_category = - 1                where             c.id = cb.contact_id           )        order by      c.created_time desc  limit 0 ,      10;验证一下效果 预计在1ms内，提升了13000多倍！10 rows in set (0.00 sec)本以为至此大工告成，但我们在前面的分析中漏了一个细节，先排序再join和先join再排序理论上开销是一样的，为何提升这么多是因为有一个limit！大致执行过程是：mysql先按索引排序得到前10条记录，然后再去join过滤，当发现不够10条的时候，再次去10条，再次join，这显然在内层join过滤的数据非常多的时候，将是灾难的，极端情况，内层一条数据都找不到，mysql还傻乎乎的每次取10条，几乎遍历了这个数据表！用不同参数的SQL试验下：select    sql_no_cache   c.id,    c.name,    c.position,    c.sex,    c.phone,    c.office_phone,    c.feature_info,    c.birthday,    c.creator_id,    c.is_keyperson,    c.giveup_reason,    c.status,    c.data_source,    from_unixtime(c.created_time) as created_time,    from_unixtime(c.last_modified) as last_modified,    c.last_modified_user_id     from    contact c    where    exists (      select          1               from          contact_branch cb                inner join          branch_user bu                                  on  cb.branch_id = bu.branch_id                                  and bu.status in (               1,            2)                          inner join             org_emp_info oei                                           on  oei.data_id = bu.user_id                                           and oei.node_left >= 2875                                           and oei.node_right <= 2875                                           and oei.org_category = - 1                          where             c.id = cb.contact_id                  )            order by       c.created_time desc  limit 0 ,      10; Empty set (2 min 18.99 sec)2 min 18.99 sec！比之前的情况还糟糕很多。由于mysql的nested loop机制，遇到这种情况，基本是无法优化的。这条语句最终也只能交给应用系统去优化自己的逻辑了。通过这个例子我们可以看到，并不是所有语句都能优化，而往往我们优化时，由于SQL用例回归时落掉一些极端情况，会造成比原来还严重的后果。所以，第一：不要指望所有语句都能通过SQL优化，第二：不要过于自信，只针对具体case来优化，而忽略了更复杂的情况。慢查询的案例就分析到这儿，以上只是一些比较典型的案例。我们在优化过程中遇到过超过1000行，涉及到16个表join的“垃圾SQL”，也遇到过线上线下数据库差异导致应用直接被慢查询拖死，也遇到过varchar等值比较没有写单引号，还遇到过笛卡尔积查询直接把从库搞死。再多的案例其实也只是一些经验的积累，如果我们熟悉查询优化器、索引的内部原理，那么分析这些案例就变得特别简单了。写在后面的话本文以一个慢查询案例引入了MySQL索引原理、优化慢查询的一些方法论;并针对遇到的典型案例做了详细的分析。其实做了这么长时间的语句优化后才发现，任何数据库层面的优化都抵不上应用系统的优化，同样是MySQL，可以用来支撑Google/FaceBook/Taobao应用，但可能连你的个人网站都撑不住。套用最近比较流行的话：“查询容易，优化不易，且写且珍惜！”参考文献：1.《高性能MySQL》 2.《数据结构与算法分析》

MySQL优化的五个维度，你学会了吗？ MySQL优化的五个维度，你学会了吗？面试官如果问你：你会从哪些维度进行MySQL性能优化?你会怎么回答?所谓的性能优化，一般针对的是MySQL查询的优化。既然是优化查询，我们自然要先知道查询操作要经过哪些环节，然后思考可以在哪些环节进行优化。SQL查询的环节下面从5个角度介绍一下MySQL优化的一些策略。1. 连接配置优化处理连接是MySQL客户端和MySQL服务端亲热的第一步，第一步都迈不好，也就别谈后来的故事了。既然连接是双方的事情，我们自然从服务端和客户端两个方面来进行优化喽。1.1 服务端配置服务端需要做的就是尽可能地多接受客户端的连接，或许你遇到过error 1040: Too many connections的错误?就是服务端的胸怀不够宽广导致的，格局太小!我们可以从两个方面解决连接数不够的问题：增加可用连接数，修改环境变量max_connections，默认情况下服务端的最大连接数为151个。mysql> show variables like 'max_connections';+-----------------+-------+| Variable_name   | Value |+-----------------+-------+| max_connections | 151   |+-----------------+-------+1 row in set (0.01 sec)1.2.3.4.5.6.7.及时释放不活动的连接，系统默认的客户端超时时间是28800秒(8小时)，我们可以把这个值调小一点。mysql> show variables like 'wait_timeout';+---------------+-------+| Variable_name | Value |+---------------+-------+| wait_timeout  | 28800 |+---------------+-------+1 row in set (0.01 sec)1.2.3.4.5.6.7.MySQL有非常多的配置参数，并且大部分参数都提供了默认值，默认值是MySQL作者经过精心设计的，完全可以满足大部分情况的需求，不建议在不清楚参数含义的情况下贸然修改。1.2 客户端优化客户端能做的就是尽量减少和服务端建立连接的次数，已经建立的连接能凑合用就凑合用，别每次执行个SQL语句都创建个新连接，服务端和客户端的资源都吃不消啊。解决的方案就是使用连接池来复用连接。常见的数据库连接池有DBCP、C3P0、阿里的Druid、Hikari，前两者用得很少了，后两者目前如日中天。但是需要注意的是连接池并不是越大越好，比如Druid的默认最大连接池大小是8，Hikari默认最大连接池大小是10，盲目地加大连接池的大小，系统执行效率反而有可能降低。为什么?对于每一个连接，服务端会创建一个单独的线程去处理，连接数越多，服务端创建的线程自然也就越多。而线程数超过CPU个数的情况下，CPU势必要通过分配时间片的方式进行线程的上下文切换，频繁的上下文切换会造成很大的性能开销。Hikari官方给出了一个PostgreSQL数据库连接池大小的建议值公式，CPU核心数*2+1。假设服务器的CPU核心数是4，把连接池设置成9就可以了。这种公式在一定程度上对其他数据库也是适用的，大家面试的时候可以吹一吹。2. 架构优化2.1 使用缓存系统中难免会出现一些比较慢的查询，这些查询要么是数据量大，要么是查询复杂(关联的表多或者是计算复杂)，使得查询会长时间占用连接。如果这种数据的实效性不是特别强(不是每时每刻都会变化，例如每日报表)，我们可以把此类数据放入缓存系统中，在数据的缓存有效期内，直接从缓存系统中获取数据，这样就可以减轻数据库的压力并提升查询效率。缓存的使用2.2 读写分离(集群、主从)项目的初期，数据库通常都是运行在一台服务器上的，用户的所有读写请求会直接作用到这台数据库服务器，单台服务器承担的并发量毕竟是有限的。针对这个问题，我们可以同时使用多台数据库服务器，将其中一台设置为为小组长，称之为master节点，其余节点作为组员，叫做slave。用户写数据只往master节点写，而读的请求分摊到各个slave节点上。这个方案叫做读写分离。给组长加上组员组成的小团体起个名字，叫集群。这就是集群注：很多开发者不满master-slave这种具有侵犯性的词汇(因为他们认为会联想到种族歧视、黑人奴隶等)，所以发起了一项更名运动。受此影响MySQL也会逐渐停用master、slave等术语，转而用source和replica替代，大家碰到的时候明白即可。使用集群必然面临一个问题，就是多个节点之间怎么保持数据的一致性。毕竟写请求只往master节点上发送了，只有master节点的数据是最新数据，怎么把对master节点的写操作也同步到各个slave节点上呢?主从技术来了!我在一条SQL更新语句是如何执行的?中粗浅地介绍了一下binlog日志，我直接搬过来了。binlog是实现MySQL主从功能的核心组件。master节点会将所有的写操作记录到binlog中，slave节点会有专门的I/O线程读取master节点的binlog，将写操作同步到当前所在的slave节点。主从这种集群的架构对减轻主数据库服务器的压力有非常好的效果，但是随着业务数据越来越多，如果某张表的数据量急剧增加，单表的查询性能就会大幅下降，而这个问题是读写分离也无法解决的，毕竟所有节点存放的是一模一样的数据啊，单表查询性能差，说的自然也是所有节点性能都差。这时我们可以把单个节点的数据分散到多个节点上进行存储，这就是分库分表。2.3 分库分表分库分表中的节点的含义比较宽泛，要是把数据库作为节点，那就是分库;如果把单张表作为节点，那就是分表。大家都知道分库分表分成垂直分库、垂直分表、水平分库和水平分表，但是每次都记不住这些概念，我就给大家详细说一说，帮助大家理解。2.3.1 垂直分库垂直分库在单体数据库的基础上垂直切几刀，按照业务逻辑拆分成不同的数据库，这就是垂直分库啦。垂直分库2.3.2 垂直分表垂直分表垂直分表就是在单表的基础上垂直切一刀(或几刀)，将一个表的多个字短拆成若干个小表，这种操作需要根据具体业务来进行判断，通常会把经常使用的字段(热字段)分成一个表，不经常使用或者不立即使用的字段(冷字段)分成一个表，提升查询速度。垂直分表拿上图举例：通常情况下商品的详情信息都比较长，而且查看商品列表时往往不需要立即展示商品详情(一般都是点击详情按钮才会进行显示)，而是会将商品更重要的信息(价格等)展示出来，按照这个业务逻辑，我们将原来的商品表做了垂直分表。2.3.3 水平分表把单张表的数据按照一定的规则(行话叫分片规则)保存到多个数据表上，横着给数据表来一刀(或几刀)，就是水平分表了。水平分表水平分表2.3.4 水平分库水平分库就是对单个数据库水平切一刀，往往伴随着水平分表。水平分库水平分库2.3.5 总结水平分，主要是为了解决存储的瓶颈;垂直分，主要是为了减轻并发压力。2.4 消息队列削峰通常情况下，用户的请求会直接访问数据库，如果同一时刻在线用户数量非常庞大，极有可能压垮数据库(参考明星出轨或公布恋情时微博的状态)。这种情况下可以通过使用消息队列降低数据库的压力，不管同时有多少个用户请求，先存入消息队列，然后系统有条不紊地从消息队列中消费请求。队列削峰3. 优化器——SQL分析与优化处理完连接、优化完缓存等架构的事情，SQL查询语句来到了解析器和优化器的地盘了。在这一步如果出了任何问题，那就只能是SQL语句的问题了。只要你的语法不出问题，解析器就不会有问题。此外，为了防止你写的SQL运行效率低，优化器会自动做一些优化，但如果实在是太烂，优化器也救不了你了，只能眼睁睁地看着你的SQL查询沦为慢查询。3.1 慢查询慢查询就是执行地很慢的查询(这句话说得跟废话似的。。。)，只有知道MySQL中有哪些慢查询我们才能针对性地进行优化。因为开启慢查询日志是有性能代价的，因此MySQL默认是关闭慢查询日志功能，使用以下命令查看当前慢查询状态。mysql> show variables like 'slow_query%';+---------------------+--------------------------------------+| Variable_name       | Value                                |+---------------------+--------------------------------------+| slow_query_log      | OFF                                  || slow_query_log_file | /var/lib/mysql/9e74f9251f6c-slow.log |+---------------------+--------------------------------------+2 rows in set (0.00 sec)1.2.3.4.5.6.7.8.slow_query_log表示当前慢查询日志是否开启，slow_query_log_file表示慢查询日志的保存位置。除了上面两个变量，我们还需要确定“慢”的指标是什么，即执行超过多长时间才算是慢查询，默认是10S，如果改成0的话就是记录所有的SQL。mysql> show variables like '%long_query%';+-----------------+-----------+| Variable_name   | Value     |+-----------------+-----------+| long_query_time | 10.000000 |+-----------------+-----------+1 row in set (0.00 sec)1.2.3.4.5.6.7.3.1.1 打开慢日志有两种打开慢日志的方式。修改配置文件my.cnf。此种修改方式系统重启后依然有效。# 是否开启慢查询日志 slow_query_log=ON#  long_query_time=2slow_query_log_file=/var/lib/mysql/slow.log1.2.3.4.5.动态修改参数(重启后失效)。mysql> set @@global.slow_query_log=1;Query OK, 0 rows affected (0.06 sec)mysql> set @@global.long_query_time=2;Query OK, 0 rows affected (0.00 sec)1.2.3.4.5.3.1.2 慢日志分析MySQL不仅为我们保存了慢日志文件，还为我们提供了慢日志查询的工具mysqldumpslow，为了演示这个工具，我们先构造一条慢查询：mysql> SELECT sleep(5);1.然后我们查询用时最多的1条慢查询：[root@iZ2zejfuakcnnq2pgqyzowZ ~]# mysqldumpslow -s t -t 1 -g 'select' /var/lib/mysql/9e74f9251f6c-slow.logReading mysql slow query log from /var/lib/mysql/9e74f9251f6c-slow.logCount: 1  Time=10.00s (10s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@localhost  SELECT sleep(N)1.2.3.4.5.其中，Count：表示这个SQL执行的次数。Time：表示执行的时间，括号中的是累积时间。Locks：表示锁定的时间，括号中的是累积时间。Rows：表示返回的记录数，括号中的是累积数。更多关于mysqldumpslow的使用方式，可以查阅官方文档，或者执行mysqldumpslow --help寻求帮助。3.2 查看运行中的线程我们可以运行show full processlist查看MySQL中运行的所有线程，查看其状态和运行时间，找到不顺眼的，直接kill。其中，Id：线程的唯一标志，可以使用Id杀死指定线程。User：启动这个线程的用户，普通账户只能查看自己的线程。Host：哪个ip和端口发起的连接。db：线程操作的数据库。Command：线程的命令。Time：操作持续时间，单位秒。State：线程的状态。Info：SQL语句的前100个字符。3.3 查看服务器运行状态使用SHOW STATUS查看MySQL服务器的运行状态，有session和global两种作用域，一般使用like+通配符进行过滤。-- 查看select的次数mysql> SHOW GLOBAL STATUS LIKE 'com_select';+---------------+--------+| Variable_name | Value  |+---------------+--------+| Com_select    | 168241 |+---------------+--------+1 row in set (0.05 sec)1.2.3.4.5.6.7.8.3.4 查看存储引擎运行信息SHOW ENGINE用来展示存储引擎的当前运行信息，包括事务持有的表锁、行锁信息;事务的锁等待情况;线程信号量等待;文件IO请求;Buffer pool统计信息等等数据。例如：SHOW ENGINE INNODB STATUS;1.上面这条语句可以展示innodb存储引擎的当前运行的各种信息，大家可以据此找到MySQL当前的问题，限于篇幅不在此意义说明其中信息的含义，大家只要知道MySQL提供了这样一个监控工具就行了，等到需要的时候再来用就好。3.5 EXPLAIN执行计划通过慢查询日志我们可以知道哪些SQL语句执行慢了，可是为什么慢?慢在哪里呢?MySQL提供了一个执行计划的查询命令EXPLAIN，通过此命令我们可以查看SQL执行的计划，所谓执行计划就是：优化器会不会优化我们自己书写的SQL语句(比如外连接改内连接查询，子查询优化为连接查询...)、优化器针对此条SQL的执行对哪些索引进行了成本估算，并最终决定采用哪个索引(或者最终选择不用索引，而是全表扫描)、优化器对单表执行的策略是什么，等等等等。EXPLAIN在MySQL5.6.3之后也可以针对UPDATE、DELETE和INSERT语句进行分析，但是通常情况下我们还是用在SELECT查询上。这篇文章主要是从宏观上多个角度介绍MySQL的优化策略，因此这里不详细说明EXPLAIN的细节，之后单独成篇。3.6 SQL与索引优化3.6.1 SQL优化SQL优化指的是SQL本身语法没有问题，但是有实现相同目的的更好的写法。比如：使用小表驱动大表;用join改写子查询;or改成union。连接查询中，尽量减少驱动表的扇出(记录数)，访问被驱动表的成本要尽量低，尽量在被驱动表的连接列上建立索引，降低访问成本;被驱动表的连接列最好是该表的主键或者是唯一二级索引列，这样被驱动表的成本会降到更低。大偏移量的limit，先过滤再排序。针对最后一条举个简单的例子，下面两条语句能实现同样的目的，但是第二条的执行效率比第一条执行效率要高得多(存储引擎使用的是InnoDB)，大家感受一下：-- 1. 大偏移量的查询mysql> SELECT * FROM user_innodb LIMIT 9000000,10;Empty set (8.18 sec)-- 2.先过滤ID（因为ID使用的是索引），再limitmysql> SELECT * FROM user_innodb WHERE id > 9000000 LIMIT 10;Empty set (0.02 sec)1.2.3.4.5.6.7.3.6.2 索引优化为慢查询创建适当的索引是个非常常见并且非常有效的方法，但是索引是否会被高效使用又是另一门学问了。4. 存储引擎与表结构4.1 选择存储引擎一般情况下，我们会选择MySQL默认的存储引擎存储引擎InnoDB，但是当对数据库性能要求精益求精的时候，存储引擎的选择也成为一个关键的影响因素。建议根据不同的业务选择不同的存储引擎，例如：查询操作、插入操作多的业务表，推荐使用MyISAM;临时表使用Memory;并发数量大、更新多的业务选择使用InnoDB;不知道选啥直接默认。4.2 优化字段字段优化的最终原则是：使用可以正确存储数据的最小的数据类型。4.2.1 整数类型MySQL提供了6种整数类型，分别是tinyintsmallintmediumintintintegerbigint不同的存储类型的最大存储范围不同，占用的存储的空间自然也不同。例如，是否被删除的标识，建议选用tinyint，而不是bigint。4.2.2 字符类型你是不是直接把所有字符串的字段都设置为varchar格式了?甚至怕不够，还会直接设置成varchar(1024)的长度?如果不确定字段的长度，肯定是要选择varchar，但是varchar需要额外的空间来记录该字段目前占用的长度;因此如果字段的长度是固定的，尽量选用char，这会给你节约不少的内存空间。4.2.3 非空非空字段尽量设置成NOT NULL，并提供默认值，或者使用特殊值代替NULL。因为NULL类型的存储和优化都会存在性能不佳的问题，具体原因在这里就不展开了。4.2.4 不要用外键、触发器和视图功能这也是「阿里巴巴开发手册」中提到的原则。原因有三个：降低了可读性，检查代码的同时还得查看数据库的代码;把计算的工作交给程序，数据库只做好存储的工作，并把这件事情做好;数据的完整性校验的工作应该由开发者完成，而不是依赖于外键，一旦用了外键，你会发现测试的时候随便删点垃圾数据都变得异常艰难。4.2.5 图片、音频、视频存储不要直接存储大文件，而是要存储大文件的访问地址。4.2.6 大字段拆分和数据冗余大字段拆分其实就是前面说过的垂直分表，把不常用的字段或者数据量较大的字段拆分出去，避免列数过多和数据量过大，尤其是习惯编写SELECT *的情况下，列数多和数据量大导致的问题会被严重放大!字段冗余原则上不符合数据库设计范式，但是却非常有利于快速检索。比如，合同表中存储客户id的同时可以冗余存储客户姓名，这样查询时就不需要再根据客户id获取用户姓名了。因此针对业务逻辑适当做一定程度的冗余也是一种比较好的优化技巧。5. 业务优化严格来说，业务方面的优化已经不算是MySQL调优的手段了，但是业务的优化却能非常有效地减轻数据库访问压力，这方面一个典型例子就是淘宝，下面举几个简单例子给大家提供一下思路：以往都是双11当晚开始买买买的模式，最近几年双11的预售战线越拉越长，提前半个多月就开始了，而且各种定金红包模式丛出不穷，这种方式叫做预售分流。这样做可以分流客户的服务请求，不必等到双十一的凌晨一股脑地集体下单;双十一的凌晨你或许想查询当天之外的订单，但是却查询失败;甚至支付宝里的小鸡的口粮都被延迟发放了，这是一种降级策略，集结不重要的服务的计算资源，用来保证当前最核心的业务;双十一的时候支付宝极力推荐使用花呗支付，而不是银行卡支付，虽然一部分考量是提高软件粘性，但是另一方面，使用余额宝实际使用的阿里内部服务器，访问速度快，而使用银行卡，需要调用银行接口，相比之下操作要慢了许多。MySQL优化的总结写到此就结束了，其中有不少细节没有提及，多少让我感觉这篇文章不完美。但是有些知识点掰开讲又太多了，不可能一下子全部写下，之后再好好写吧。

SQL查询语句优化 暂无简介