关于java:洋洋洒洒一万二千字彻底讲清楚MySQL的优化原理看不完先收藏

说起 MySQL 的查问优化，置信大家珍藏了一堆奇技淫巧：不能应用 SELECT *、不应用 NULL 字段、正当创立索引、为字段抉择适合的数据类型…

你是否真的了解这些优化技巧？是否了解其背地的工作原理？在理论场景下性能真有晋升吗？我想未必。因此了解这些优化倡议背地的原理就尤为重要，心愿本文能让你从新扫视这些优化倡议，并在理论业务场景下正当的使用。

能够下载一本 Java 技术栈手册，这本手册蕴含公布过的 MYSQL 技术博文。

如果能在头脑中构建一幅 MySQL 各组件之间如何协同工作的架构图，有助于深刻了解 MySQL 服务器。下图展现了 MySQL 的逻辑架构图。

MySQL 逻辑架构整体分为三层，最上层为客户端层，并非 MySQL 所独有，诸如：连贯解决、受权认证、平安等性能均在这一层解决。

MySQL 大多数外围服务均在两头这一层，包含查问解析、剖析、优化、缓存、内置函数(比方：工夫、数学、加密等函数)。所有的跨存储引擎的性能也在这一层实现：存储过程、触发器、视图等。

最上层为存储引擎，其负责 MySQL 中的数据存储和提取。和 Linux 下的文件系统相似，每种存储引擎都有其劣势和劣势。两头的服务层通过 API 与存储引擎通信，这些 API 接口屏蔽了不同存储引擎间的差别。

咱们总是心愿 MySQL 可能取得更高的查问性能，最好的方法是弄清楚 MySQL 是如何优化和执行查问的。一旦了解了这一点，就会发现：很多的查问优化工作实际上就是遵循一些准则让 MySQL 的优化器可能依照料想的正当形式运行而已。当向 MySQL 发送一个申请的时候，MySQL 到底做了些什么呢？

MySQL 客户端 / 服务端通信协议是“半双工”的：在任一时刻，要么是服务器向客户端发送数据，要么是客户端向服务器发送数据，这两个动作不能同时产生。

一旦一端开始发送音讯，另一端要接管残缺个音讯能力响应它，所以咱们无奈也毋庸将一个音讯切成小块独立发送，也没有方法进行流量管制。

客户端用一个独自的数据包将查问申请发送给服务器，所以当查问语句很长的时候，须要设置 max_allowed_packet 参数。然而须要留神的是，如果查问切实是太大，服务端会回绝接管更多数据并抛出异样。

与之相同的是，服务器响应给用户的数据通常会很多，由多个数据包组成。然而当服务器响应客户端申请时，客户端必须残缺的接管整个返回后果，而不能简略的只取后面几条后果，而后让服务器进行发送。因此在理论开发中，尽量放弃查问简略且只返回必须的数据，减小通信间数据包的大小和数量是一个十分好的习惯，这也是查问中尽量避免应用 SELECT * 以及加上 LIMIT 限度的起因之一。

整顿了一下 2021 年的 Java 工程师经典面试真题以及学习笔记，共 485 页大略 850 道含答案的面试题 PDF，蕴含了 Java、MyBatis、ZooKeeper、Dubbo、Elasticsearch、Memcached、Redis、MySQL、Spring、Spring Boot、Spring Cloud、RabbitMQ、Kafka、Linux 等简直所有技术栈，每个技术栈都有不少于 50 道经典面试真题和学习笔记，不敢说刷完包你进大厂，只是让你面对面试官的时候多几分底气还是没问题的。

在解析一个查问语句前，如果查问缓存是关上的，那么 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 个不同方面给出一些优化倡议。但请等等，还有一句忠告要先送给你：不要听信你看到的对于优化的“绝对真理”，包含本文所探讨的内容，而应该是在理论的业务场景下通过测试来验证你对于执行打算以及响应工夫的假如。

1、Scheme 设计与数据类型优化

抉择数据类型只有遵循 小而简略 的准则就好，越小的数据类型通常会更快，占用更少的磁盘、内存，解决时须要的 CPU 周期也更少。越简略的数据类型在计算时须要更少的 CPU 周期，比方，整型就比字符操作代价低，因此会应用整型来存储 ip 地址，应用 DATETIME 来存储工夫，而不是应用字符串。

这里总结几个可能容易了解谬误的技巧：
1. 通常来说把可为 NULL 的列改为 NOT NULL 不会对性能晋升有多少帮忙，只是如果打算在列上创立索引，就应该将该列设置为 NOT NULL。
2. 对整数类型指定宽度，比方 INT(11)，没有任何卵用。INT 应用 32 位（4 个字节）存储空间，那么它的示意范畴曾经确定，所以 INT(1)和 INT(20)对于存储和计算是雷同的。
3. UNSIGNED 示意不容许负值，大抵能够使负数的下限进步一倍。比方 TINYINT 存储范畴是 -128 ~ 127，而 UNSIGNED TINYINT 存储的范畴却是 0 – 255。
4. 通常来讲，没有太大的必要应用 DECIMAL 数据类型。即便是在须要存储财务数据时，依然能够应用 BIGINT。比方须要准确到万分之一，那么能够将数据乘以一百万而后应用 BIGINT 存储。这样能够防止浮点数计算不精确和 DECIMAL 准确计算代价高的问题。
5. TIMESTAMP 应用 4 个字节存储空间，DATETIME 应用 8 个字节存储空间。因此，TIMESTAMP 只能示意 1970 – 2038 年，比 DATETIME 示意的范畴小得多，而且 TIMESTAMP 的值因时区不同而不同。
6. 大多数状况下没有应用枚举类型的必要，其中一个毛病是枚举的字符串列表是固定的，增加和删除字符串（枚举选项）必须应用 ALTER TABLE（如果只只是在列表开端追加元素，不须要重建表）。
7. schema 的列不要太多。起因是存储引擎的 API 工作时须要在服务器层和存储引擎层之间通过行缓冲格局拷贝数据，而后在服务器层将缓冲内容解码成各个列，这个转换过程的代价是十分高的。如果列太多而理论应用的列又很少的话，有可能会导致 CPU 占用过高。
8. 大表 ALTER TABLE 十分耗时，MySQL 执行大部分批改表后果操作的办法是用新的构造创立一个张空表，从旧表中查出所有的数据插入新表，而后再删除旧表。尤其当内存不足而表又很大，而且还有很大索引的状况下，耗时更久。当然有一些奇技淫巧能够解决这个问题，有趣味可自行查阅。

2、创立高性能索引

索引是进步 MySQL 查问性能的一个重要途径，但过多的索引可能会导致过高的磁盘使用率以及过高的内存占用，从而影响应用程序的整体性能。该当尽量避免预先才想起增加索引，因为预先可能须要监控大量的 SQL 能力定位到问题所在，而且增加索引的工夫必定是远大于初始增加索引所须要的工夫，可见索引的增加也是十分有技术含量的。

接下来将向你展现一系列创立高性能索引的策略，以及每条策略其背地的工作原理。但在此之前，先理解与索引相干的一些算法和数据结构，将有助于更好的了解后文的内容。

3、索引相干的数据结构和算法

通常咱们所说的索引是指 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。

怎么了解这两个特色？MySQL 将每个节点的大小设置为一个页的整数倍（起因下文会介绍），也就是在节点空间大小肯定的状况下，每个节点能够存储更多的内结点，这样每个结点能索引的范畴更大更准确。

所有的叶子节点应用指针链接的益处是能够进行区间拜访，比方上图中，如果查找大于 20 而小于 30 的记录，只须要找到节点 20，就能够遍历指针顺次找到 25、30。如果没有链接指针的话，就无奈进行区间查找。这也是 MySQL 应用 B +Tree 作为索引存储构造的重要起因。

MySQL 为何将节点大小设置为页的整数倍，这就须要了解磁盘的存储原理。磁盘自身存取就比主存慢很多，在加上机械运动损耗（特地是一般的机械硬盘），磁盘的存取速度往往是主存的几百万分之一，为了尽量减少磁盘 I /O，磁盘往往不是严格按需读取，而是每次都会预读，即便只须要一个字节，磁盘也会从这个地位开始，程序向后读取肯定长度的数据放入内存，预读的长度个别为页的整数倍。

页是计算机管理存储器的逻辑块，硬件及 OS 往往将主存和磁盘存储区宰割为间断的大小相等的块，每个存储块称为一页（许多 OS 中，页的大小通常为 4K）。主存和磁盘以页为单位替换数据。

当程序要读取的数据不在主存中时，会触发一个缺页异样，此时零碎会向磁盘收回读盘信号，磁盘会找到数据的起始地位并向后间断读取一页或几页载入内存中，而后一起返回，程序持续运行。

MySQL 奇妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只须要一次 I / O 就能够齐全载入。为了达到这个目标，每次新建节点时，间接申请一个页的空间，这样就保障一个节点物理上也存储在一个页里，加之计算机存储调配都是按页对齐的，就实现了读取一个节点只需一次 I /O。

假如 B +Tree 的高度为 h，一次检索最多须要 h -1I/O（根节点常驻内存），复杂度 O(h)=O(logMN)。理论利用场景中，M 通常较大，经常超过 100，因而树的高度个别都比拟小，通常不超过 3。

最初简略理解下 B +Tree 节点的操作，在整体上对索引的保护有一个大略的理解，尽管索引能够大大提高查问效率，但保护索引仍要花费很大的代价，因而正当的创立索引也就尤为重要。

仍以下面的树为例，咱们假如每个节点只能存储 4 个内节点。首先要插入第一个节点 28，如下图所示。

接着插入下一个节点 70，在 Index Page 中查问后得悉应该插入到 50 – 70 之间的叶子节点，但叶子节点已满，这时候就须要进行也决裂的操作，以后的叶子节点终点为 50，所以依据两头值来拆分叶子节点，如下图所示。

最初插入一个节点 95，这时候 Index Page 和 Leaf 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 列的值，下图展现了索引是如何组织数据存储的。

能够看到，索引首先依据第一个字段来排列程序，当名字雷同时，则依据第三个字段，即出生日期来排序，正是因为这个起因，才有了索引的“最左准则”。

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 > 0

MySQL 为这个查问抉择了索引(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_id

7、冗余和反复索引

冗余索引是指在雷同的列上依照雷同的程序创立的雷同类型的索引，该当尽量避免这种索引，发现后立刻删除。比方有一个索引 (A,B)，再创立索引(A) 就是冗余索引。冗余索引常常产生在为表增加新索引时，比方有人新建了索引(A,B)，但这个索引不是扩大已有的索引(A)。

大多数状况下都应该尽量扩大已有的索引而不是创立新索引。但有极少状况下呈现性能方面的思考须要冗余索引，比方扩大已有索引而导致其变得过大，从而影响到其余应用该索引的查问。

8、删除长期未应用的索引

定期删除一些长时间未应用过的索引是一个十分好的习惯。

对于索引这个话题打算就此打住，最初要说一句，索引并不总是最好的工具，只有当索引帮忙进步查问速度带来的益处大于其带来的额定工作时，索引才是无效的。对于十分小的表，简略的全表扫描更高效。

对于中到大型的表，索引就十分无效。对于超大型的表，建设和保护索引的代价随之增长，这时候其余技术兴许更无效，比方分区表。最初的最初，explain 后再提测是一种美德。

优化 COUNT()查问

COUNT()可能是被大家误会最多的函数了，它有两种不同的作用，其一是统计某个列值的数量，其二是统计行数。统计列值时，要求列值是非空的，它不会统计 NULL。如果确认括号中的表达式不可能为空时，实际上就是在统计行数。最简略的就是当应用 COUNT(*)时，并不是咱们所设想的那样扩大成所有的列，实际上，它会疏忽所有的列而间接统计所有的行数。

咱们最常见的误会也就在这儿，在括号内指定了一列却心愿统计后果是行数，而且还经常误以为前者的性能会更好。但理论并非这样，如果要统计行数，间接应用 COUNT()，意义清晰，且性能更好。

有时候某些业务场景并不需要齐全准确的 COUNT 值，能够用近似值来代替，EXPLAIN 进去的行数就是一个不错的近似值，而且执行 EXPLAIN 并不需要真正地去执行查问，所以老本非常低。通常来说，执行 COUNT()都须要扫描大量的行能力获取到准确的数据，因而很难优化，MySQL 层面还能做得也就只有笼罩索引了。如果不还能解决问题，只有从架构层面解决了，比方增加汇总表，或者应用 redis 这样的内部缓存零碎。

优化关联查问

在大数据场景下，表与表之间通过一个冗余字段来关联，要比间接应用 JOIN 有更好的性能。如果的确须要应用关联查问的状况下，须要特地留神的是：

1. 确保 ON 和 USING 字句中的列上有索引。在创立索引的时候就要思考到关联的程序。当表 A 和表 B 用列 c 关联的时候，如果优化器关联的程序是 A、B，那么就不须要在 A 表的对应列上创立索引。没有用到的索引会带来额定的累赘，一般来说，除非有其余理由，只须要在关联程序中的第二张表的相应列上创立索引（具体起因下文剖析）。

2. 确保任何的 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;

其它优化的方法还包含应用事后计算的汇总表，或者关联到一个冗余表，冗余表中只蕴含主键列和须要做排序的列。

优化 UNION

MySQL 解决 UNION 的策略是先创立长期表，而后再把各个查问后果插入到长期表中，最初再来做查问。因而很多优化策略在 UNION 查问中都没有方法很好的时候。常常须要手动将 WHERE、LIMIT、ORDER BY 等字句“下推”到各个子查问中，以便优化器能够充分利用这些条件先优化。

除非的确须要服务器去重，否则就肯定要应用 UNION ALL，如果没有 ALL 关键字，MySQL 会给长期表加上 DISTINCT 选项，这会导致整个长期表的数据做唯一性查看，这样做的代价十分高。当然即便应用 ALL 关键字，MySQL 总是将后果放入长期表，而后再读出，再返回给客户端。尽管很多时候没有这个必要，比方有时候能够间接把每个子查问的后果返回给客户端。

了解查问是如何执行以及工夫都耗费在哪些地方，再加上一些优化过程的常识，能够帮忙大家更好的了解 MySQL，了解常见优化技巧背地的原理。心愿本文中的原理、示例可能帮忙大家更好的将实践和实际分割起来，更多的将理论知识使用到实际中。

其余也没啥说的了，给大家留两个思考题吧，能够在脑袋里想想答案，这也是大家常常挂在嘴边的，但很少有人会思考为什么？

1. 有十分多的程序员在分享时都会抛出这样一个观点：尽可能不要应用存储过程，存储过程十分不容易保护，也会减少应用老本，应该把业务逻辑放到客户端。既然客户端都无能这些事，那为什么还要存储过程？
2. JOIN 自身也挺不便的，间接查问就好了，为什么还须要视图呢？

关于java:洋洋洒洒一万二千字彻底讲清楚MySQL的优化原理看不完先收藏

前言

MySQL 逻辑架构

MySQL 查问过程

客户端 / 服务端通信协议

查问缓存

语法解析和预处理

查问优化

查问执行引擎

返回后果给客户端

性能优化倡议

高性能策略

特定类型查问优化

结语