一、数据库优化的必要性

1、避免网站页面出现访问错误

1）、数据库连接timeout产生页面5xx错误

这个问题也是最直观的问题，页面上出现错误，在应用层面找开发的同学来排查，开发同学发现应用层面代码没有问题，最后检查发现是因为数据库超时引起的。那数据库层面为什么会超时呢？这里的原因可能有很多，比如数据库连接池已经满了，或者查询的数据量比较大，引起数据库线程的挂死，既然这些问题产生于数据库层面，那么就需要对数据库进行调优。

2）、慢查询造成页面无法加载

最典型的就是用户点开页面后屏幕一片白，为什么？说明你的数据没有加载进来，比如说电商系统，你点开了这个商品列表，在5秒之内还没有打开，那么这个时候你的系统基本上已经废了，这系统已经到了不得不优化的程度了。5秒钟，这个时间太夸张了，其实用户的极限基本上在1秒之内，系统数据超时达到了5秒，那肯定是页面一片白。这个原因说白了就是你数据查询比较慢，或者说你的表里的数据量很大，可能有上百万，或者千万的数据。如果开发的同学当时还忘记了在条件列加索引，那这简直就是一种灾难，别说5分钟了，半个小时都有可能出不来。

3）、阻塞造成数据无法提交

那这也是有可能的，用户在页面上比如提交一个付款的动作，那么你的后台如果线程繁忙引起阻塞，那这时候的阻塞基本上就是锁表之内的了。

其实我们优化数据库的时候。不应该等他出现问题的时候才想起去优化，我们应该在数据库设计之初就应该考虑的到各种问题出现的可能性，做好防范。等到数据库出现问题的时候再去优化就有点嫌晚了，因为这时候优化成本就大了。

2、增加数据库的稳定性

1）、很多数据库问题都是由于低效的查询引起的。

那么什么叫低效呢？低效最直接的体现就是你查询时候进行全表扫描，说白了就是没有经过索引，那么出现这个问题有可能是开发人员在开发的时候忘记加索引了。

系统可能在上线初期不会出现问题，为什么呢？因为你的用户量比较少，低并发，所以不会发现问题。但是当你的系统跑了2-3年之后，你的用户量暴增，这时候就会出现问题。比如你的公司已经成长为一个小独角兽了，用户量达到了上百万，那么这时候问题可能就来了，因为并发量上来了，并发上来之后对数据库资源的争抢就开始了。所以开发人员可能在设计表字段的初期没有预测到这个字段将来会作为条件查询，所以就没有在这个字段上加索引。或者呢虽然加了索引，由于随着时间的推移，这个字段的重复率可能越来越高，那么索引的威力就大大减弱了。

2）、随着时间的推移，系统变得极其臃肿，数据库中的数据量越来越大，数据检索越来越困难，对整个系统带来的资源消耗也就越来越大，系统越发不稳定。

这个问题其实刚讲到的，系统上线之后初期可能不会发生问题，但是随着你的业务量不断的膨胀，你的数据库必然会产生问题。所以数据库的调优工作是个长期的工作，现在各大互联网公司都有自己的dba，对数据库做24小时监控。

而且我们发现其实系统绝大部分性能问题都是出在数据库层面。因为公司的数据实际上是公司的核心资产，说白了你那些项目代码弄丢了其实都无所谓。但是呢，你数据库里的数据万万不能丢的，所以对数据库的维护优化应该作为一个头等大事。

3、优化用户体验

1）、流畅的页面访问速度

这个也是我们开发人员所追求的一个目标。就是说系统访问页面要非常的流畅，你不能动不动一个查询就是3-5秒钟，那这样的话你的客户就得跑光了。比如说电商系统，我在你家买东西，每次点页面，他都给我3-5秒的响应，那客户肯定会奔溃的，必然抛弃你去其他家去买了，因为你的系统太烂了。所以我们在应用层面页面都是做成静态页面，通过nginx+redis缓存来做了，热点数据直接就不走数据库了。

2）、良好的网站功能体验

这个也非常重要，如果说你的网站里面的功能动不动就不能用，动不动就一片白，那这种用户体验是非常差的。基本上用户来几次就跑光了，我们的目标是把访问页面控制在1秒之内，其实1秒都嫌多啦，像我们公司，优化的目标在500ms-600ms，这是我们的终极目标，1s是最极限的容忍程度。

二、mysql数据库优化层面

1、图解优化层面

2、商业需求

1）、不合理需求造成资源投入产出比过低

需求是否合理很多时候可能并不是很容易界定，尤其是作为技术人员来说，可能更难以确定一个需求的合理性。即使指出，也不一定会被产品经理们认可。那作为技术人员的我们怎么来证明一个需求是否合理呢？

第一、每次产品经理们提出新的项目（或者功能需求）的时候，应该要求他们同时给出该项目的预期收益的量化指标，以备项目上先后统计评估投入产出比率； 第二、在每次项目进行过程中，应该详细记录所有的资源投入，包括人力投入，硬件设施的投入，以及其他任何项目相关的资源投入； 第三、项目（或者功能需求）上线之后应该及时通过收集相关数据统计出项目的实际收益值，以便计算投入产出比率的时候使用； 第四、技术部门应该尽可能推动设计出一个项目（或者功能需求）的投入产出比率的计算规则。在项目上线一段时间之后，通过项目实际收益的统计数据和项目的投入资源量，计算出整个项目的实际投入产出值，并公布给所有参与项目的部门知晓，同时存放以备后查。

有了实际的投入产出比率，我们就可以和项目立项之初产品经理们的预期投入产出比率做出比较，判定出这个项目做的是否值得。而且当积累了较多的项目投入产出比率之后，我们可以根据历史数据分析出一个项目合理的投入产出比率应该是多少。这样，在项目立项之初，我们就可以判定出产品经理们的预期投入产出比率是否合理，项目是否真的有进行的必要。

有了实际的投入产出比率之后，我们还可以拿出数据给老板们看，让他知道功能并不是越多越好，让他知道有些功能是应该撤下来的，即使撤下该功能可能需要投入不少资源。实际上，一般来说，在产品开发及运营部门内部都会做上面所说的这些事情的。但很多时候可能更多只是一种形式化的过程。在有些比较规范的公司可能也完成了上面的大部分流程，但是要么数据不公开，要么公开给其他部门的数据存在一定的偏差，不具备真实性。为什么会样？其实就一个原因，就是部门之间的利益冲突及业绩冲突问题。产品经理们总是希望尽可能的让用户觉得自己设计的产品功能齐全，让老板觉得自己做了很多事情。但是从来都不会去关心因为做一个功能所带来的成本投入，或者说是不会特别的关心这一点。而且很多时候他们也并不能太理解技术方面带来的复杂度给产品本身带来的负面影响。

2）、无用功能堆积使系统过度复杂影响整体性能

很多时候，为系统增加某个功能可能并不需要花费太多的成本，而要想将一个已经运行了一段时间的功能从原有系统中撤下来却是非常困难的。

首先，对于开发部门，可能要重新整理很多的代码，找出可能存在与增加该功能所编写的代码有交集的其他功能点，删除没有关联的代码，修改有关联的代码；

其次，对于测试部门，由于功能的变动，必须要回归测试所有相关的功能点是否正常。可能由于界定困难，不得不将回归范围扩展到很大，测试工作量也很大，如果你有自动化测试可能还会好一点。

最后，所有与撤除下线某个功能相关的工作参与者来说，又无法带来任何实质性的收益，而恰恰相反的是，带来的只可能是风险。

由于上面的这几个因素，可能很少有公司能够有很完善的项目（或者功能）下线机制，也很少有公司能做到及时将系统中某些不合适的功能下线。

所以，我们所面对的应用系统可能总是越来越复杂，越来越庞大，短期内的复杂可能并无太大问题，但是随着时间的积累，我们所面对的系统就会变得极其臃肿。不仅维护困难，性能也会越来越差。尤其是有些并不合理的功能，在设计之初或者是刚上线的时候由于数据量较小，带来不了多少性能损耗。可随着时间的推移，数据库中的数据量越来越大，数据检索越来越困难，对整个系统带来的资源消耗也就越来越大。而且，由于系统复杂度的不断增加，给后续其他功能的开发带来实现的复杂度，可能很多本来很简单的功能，因为系统的复杂而不得不增加很多的逻辑判断，造成系统应用程序的计算量不断增加，本身性能就会受到影响。而如果这些逻辑判断还需要与数据库交互通过持久化的数据来完成的话，所带来的性能损失就更大，对整个系统的性能影响也就更大了。

3、系统架构

1）、数据库中存放的数据都是适合在数据库中存放的吗？

对于有些开发人员来说，数据库就是一个操作最方便的万能存储中心，希望什么数据都存放在数据库中，不论是需要持久化的数据，还是临时存放的过程数据，不论是普通的纯文本格式的字符数据，还是多媒体的二进制数据，都喜欢全部塞进数据库中。因为对于应用服务器来说，数据库很多时候都是一个集中式的存储环境，不像应用服务器那样可能有很多台；而且数据库有专门的 DBA 去帮忙维护，而不像应用服务器很多时候还需要开发人员去做一些维护；还有一点很关键的就是数据库的操作非常简单统一，不像文件操作或者其他类型的存储方式那么复杂。

其实我个人认为，现在的很多数据库为我们提供了太多的功能，反而让很多并不是太了解数据库的人错误的使用了数据库的很多并不是太擅长或者对性能影响很大的功能，最后却全部怪罪到数据库身上。

实际上，以下几类数据都是不适合在数据库中存放的：

• 二进制多媒体数据 将二进制多媒体数据存放在数据库中，一个问题是数据库空间资源耗用非常严重，另一个问题是这些数据的存储很消耗数据库主机的CPU资源。这种数据主要包括图片，音频、视频和其他一些相关的二进制文件。这些数据的处理本不是数据的优势，如果我们硬要将他们塞入数据库，肯定会造成数据库的处理资源消耗严重。
• 流水队列数据 我们都知道，数据库为了保证事务的安全性（支持事务的存储引擎）以及可恢复性，都是需要记录所有变更的日志信息的。而流水队列数据的用途就决定了存放这种数据的表中的数据会不断的被 INSERT， UPDATe 和 DELETE，而每一个操作都会生成与之对应的日志信息。在 MySQL 中，如果是支持事务的存储引擎，这个日志的产生量更是要翻倍。而如果我们通过一些成熟的第三方队列软件（例如rabbitmq，rocketmq，kafka等）来实现这个Queue数据的处理功能，性能将会成倍的提升。
• 超大文本数据 对于 5.0.3 之前的 MySQL 版本， VARCHAR 类型的数据最长只能存放 255 个字节，如果需要存储更长的文本数据到一个字段，我们就必须使用 TEXT 类型（最大可存放 64KB）的字段，甚至是更大的LONGTEXT 类型（最大 4GB）。而TEXT类型数据的处理性能要远比 VARCHAR 类型数据的处理性能低下很多。从 5.0.3 版本开始， VARCHAR 类型的最大长度被调整到 64KB 了，但是当实际数据小于 255Bytes 的时候，实际存储空间和实际的数据长度一样，可一旦长度超过 255 Bytes 之后，所占用的存储空间就是实际数据长度的两倍。所以，超大文本数据存放在数据库中不仅会带来性能低下的问题，还会带来空间占用的浪费问题。

2）、是否合理的利用了应用层 Cache 机制？

对于 Web 应用，活跃数据的数据量总是不会特别的大，有些活跃数据更是很少变化。对于这类数据，我们是否有必要每次需要的时候都到数据库中去查询呢？如果我们能够将变化相对较少的部分活跃数据通过应用层的Cache机制Cache 到内存中，对性能的提升肯定是成数量级的，而且由于是活跃数据，对系统整体的性能影响也会很大。

当然，通过 Cache 机制成功的案例数不胜数，但是失败的案例也同样并不少见。如何合理的通过Cache 技术让系统性能得到较大的提升也不是通过寥寥几笔就能说明的清楚，这里我仅根据以往的经验列举一下什么样的数据适合通过 Cache 技术来提高系统性能：

• 系统各种配置及规则数据； 由于这些配置信息变动的频率非常低，访问概率又很高，所以非常适合存使用 Cache；
• 活跃用户的基本信息数据； 虽然我们经常会听到某某网站的用户量达到成百上千万，但是很少有系统的活跃用户量能够都达到这个数量级。也很少有用户每天没事干去将自己的基本信息改来改去。更为重要的一点是用户的基本信息在应用系统中的访问频率极其频繁。所以用户基本信息的 Cache，很容易让整个应用系统的性能出现一个质的提升。
• 活跃用户的个性化定制信息数据； 虽然用户个性化定制的数据从访问频率来看，可能并没有用户的基本信息那么的频繁，但相对于系统整体来说，也占了很大的比例，而且变更频率一样不会太多。现在普遍使用nosql的组件，例如用redis作为热点数据的存储引擎实现用户个性化定制数据，我们就能看出对这部分信息进行Cache 的价值了，Cache 技术的合理利用和扩充造就了项目整体的成功。
• 准实时的统计信息数据； 所谓准实时的统计数据，实际上就是基于时间段的统计数据。这种数据不会实时更新，也很少需要增量更新，只有当达到重新 Build 该统计数据的时候需要做一次全量更新操作。虽然这种数据即使通过数据库来读取效率可能也会比较高，但是执行频率很高之后，同样会消耗不少资源。既然数据库服务器的资源非常珍贵，我们为什么不能放在应用相关的内存 Cache 中呢？
• 其他一些访问频繁但变更较少的数据； 除了上面这四种数据之外，在我们面对的各种系统环境中肯定还会有各种各样的变更较少但是访问很频繁的数据。只要合适，我们都可以将对他们的访问从数据库移到Cache中。

3）、数据层实现都是最精简的吗？

以往的经验来看，一个合理的数据存取实现和一个拙劣的实现相比，在性能方面的差异经常会超出一个甚至几个数量级。

我们先来分析一个非常简单且经常会遇到类似情况的示例： 比如一个网站系统中，现在要实现每个用户查看各自相册列表（假设每个列表显示 10 张相片）的时候，能够在相片名称后面显示该相片的留言数量。这个需求大家认为应该如何实现呢？我想90%的开发开发工程师会通过如下两步来实现该需求：

• 通过“SELECT id,subject,url FROM photo WHERe user_id = ? limit 10” 得到第一页的相片相关信息；
• 通过第 1 步结果集中的 10 个相片 id 循环运行十次“ SELECt COUNT(*) FROM photo_comment WHERe photh_id = ?” 来得到每张相册的回复数量然后再拼装展现对象。

此外可能还有部分人想到了如下的方案：

• 和上面完全一样的操作步骤；
• 通过程序拼装上面得到的 10 个 photo 的 id，再通过 in 查询“SELECt photo_id,count(*) FROM photo_comment WHERe photo_id in (?) GROUP BY photo_id” 一次得到 10 个 photo 的所有回复数量，再组装两个结果集得到展现对象。

我们来对以上两个方案做一下简单的比较：

• 从 MySQL 执行的 SQL 数量来看 ，第一种解决方案为11（1+10=11）条SQL语句，第二种解决方案 为2条SQL语句（1+1）;
• 从应用程序与数据库交互来看，第一种为11次，第二种为2次；
• 从数据库的IO操作来看，简单假设每次SQL为1个IO，第一种最少11次IO，第二种小于等于11次IO，而且只有当数据非常之离散的情况下才会需要11次；
• 从数据库处理的查询复杂度来看，第一种为两类很简单的查询，第二种有一条SQL语句有GROUP BY 操作，比第一种解决方案增加了排序分组操作；
• 从应用程序结果集处理来看，第一种11次结果集的处理，第二种2次结果集的处理，但是第二种解决方案中第二次结果处理数量是第一次的10倍；
• 从应用程序数据处理来看，第二种比第一种多了一个拼装photo_id 的过程。

我们先从以上 6 点来做一个性能消耗的分析：

• 由于MySQL对客户端每次提交的 SQL 不管是相同还是不同，都需要进行完全解析，这个动作主要消耗的资源是数据库主机的CPU，那么这里第一种方案和第二种方案消耗CPU的比例是11:2。SQL语句的解析动作在整个 SQL 语句执行过程中的整体消耗的CPU比例是较多的;
• 应用程序与数据库交互所消耗的资源基本上都在网络方面，同样也是11：2；
• 数据库 IO 操作资源消耗为小于或者等于1：1；
• 第二种解决方案需要比第一种多消耗内存资源进行排序分组操作，由于数据量不大，多出的消耗在语句整体消耗中占用比例会比较小，大概不会超过 20%，大家可以针对性测试；
• 结果集处理次数也为11：2，但是第二种解决方案第二次处理数量较大，整体来说两次的性能消耗区别不大；
• 应用程序数据处理方面所多出的这个photo_id的拼装所消耗的资源是非常小的，甚至比应用程序与MySQL做一次简单的交互所消耗的资源还要少。

综合上面的这6点比较，我们可以很容易得出结论，从整体资源消耗来看，第二种方案会远远优于第一种解决方案。而在实际开发过程中，我们的程序员却很少选用。主要原因其实有两个，一个是第二种方案在程序代码实现方面可能会比第一种方案略为复杂，尤其是在当前编程环境中面向对象思想的普及，开发工程师可能会更习惯于以对象为中心的思考方式来解决问题。还有一个原因就是我们的程序员同学可能对SQL语句的使用并不是特别的熟悉，并不一定能够想到第二条SQL 语句所实现的功能。对于第一个原因，我们可能只能通过加强开发工程师的性能优化意识来让大家能够自觉纠正，而第二个原因的解决就正是需要我们这个专题的重点。SQL语句的调优正是我们必须要具备的，定期对初级开发工程师进行一些相应的数据库知识包括SQL语句方面的优化培训，可能会给大家带来意想不到的收获的。这里我们还仅仅只是通过一个很长见的简单示例来说明数据层架构实现的区别对整体性能的影响，实际上可以简单的归结为过渡依赖嵌套循环的使用或者说是过渡弱化 SQL 语句的功能造成性能消耗过多的实例。后面我将进一步分析一下更多的因为架构实现差异所带来的性能消耗差异。

4、SQL及索引优化

这个主题将是我们研究的重点，我们从以下两个方面来阐述。

1）、根据需求写出良好的SQL，并创建有效的索引，实现某一种需求可以多种写法，我们就要选择一种效率最高的写法，这个时候就要了解sql优化。

下面我们将通过一两个具体的示例来分析写法不一样而功能完全相同的两条 SQL 的在性能方面的差异。 示例一 需求：取出某个 group（假设 id 为 100）下的用户编号（id），用户昵称（nick_name）、用户性别（ sexuality ）、用户签名（ sign ）和用户生日（ birthday ），并按照加入组的时间（user_group.gmt_create）来进行倒序排列，取出前 20 个。

解决方案一

SELECt id,nick_name
FROM user,user_group
WHERe user_group.group_id = 1
and user_group.user_id = user.id
limit 100,20;

解决方案二

SELECt user.id,user.nick_name
FROM (
SELECt user_id
FROM user_group
WHERe user_group.group_id = 1
ORDER BY gmt_create desc
limit 100,20) t,user
WHERe t.user_id = user.id;

我们先来看看执行计划：

mysql> explain
-> SELECt id,nick_name
-> FROM user,user_group
-> WHERe user_group.group_id = 1
-> and user_group.user_id = user.id
-> ORDER BY user_group.gmt_create desc
-> limit 100,20G
************************* 1. row ***************************
id: 1
select_type: SIMPLE
table: user_group
type: ref
possible_keys: user_group_uid_gid_ind,user_group_gid_ind
key: user_group_gid_ind
key_len: 4
ref: const
rows: 31156Extra: Using where; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: user
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: example.user_group.user_id
rows: 1
Extra:

mysql> explain
-> SELECT user.id,user.nick_name
-> FROM (
-> SELECt user_id
-> FROM user_group
-> WHERe user_group.group_id = 1
-> ORDER BY gmt_create desc
-> limit 100,20) t,user
-> WHERe t.user_id = user.idG
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 20
Extra:
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: user
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: t.user_id
rows: 1
Extra:
*************************** 3. row ***************************
id: 2
select_type: DERIVED
table: user_group
type: ref
possible_keys: user_group_gid_ind
key: user_group_gid_ind
key_len: 4
ref: const
rows: 31156
Extra: Using filesort

执行计划对比分析：

解决方案一中的执行计划显示 MySQL 在对两个参与 Join 的表都利用到了索引， user_group 表利用了 user_group_gid_ind 索引（ key: user_group_gid_ind ）， user 表利用到了主键索引（ key:PRIMARY），在参与 Join 前 MySQL 通过 Where 过滤后的结果集与 user 表进行 Join，最后通过排序取出Join 后结果的“limit 100,20” 条结果返回。

解决方案二的 SQL 语句利用到了子查询，所以执行计划会稍微复杂一些，首先可以看到两个表都和解决方案 1 一样都利用到了索引（所使用的索引也完全一样），执行计划显示该子查询以 user_group为驱动，也就是先通过user_group 进行过滤并马上进行这一论的结果集排序，也就取得了 SQL 中的“limit 100,20” 条结果，然后与 user 表进行 Join，得到相应的数据。这里可能有人会怀疑在自查询中从user_group表所取得与user 表参与 Join的记录条数并不是 20条，而是整个group_id=1 的所有结果。

那么请大家看看该执行计划中的第一行，该行内容就充分说明了在外层查询中的所有的 20 条记录全部被返回。 通过比较两个解决方案的执行计划，我们可以看到第一种解决方案中需要和 user 表参与 Join 的记录 数 MySQL 通过统计数据估算出来是 31156，也就是通过 user_group 表返回的所有满足 group_id=1 的记录 数（系统中的实际数据是 20000）。

而第二种解决方案的执行计划中， user 表参与 Join 的数据就只有 20条，两者相差很大，通过本节最初的分析，我们认为第二中解决方案应该明显优于第一种解决方案。

2）、sql优化的目的之一就是减少中间结果集，降低物理IO

例如：如何优化select t1.id,t2.name from t1,t2 where t1.pid=t2.id;

以这条sql语句为例我们来看一下他的执行流程

• from语句把t1表 和 t2表从数据库文件加载到内存中。
• 这时候相当于对两张表做了乘法运算，把t1表中的每一行记录按照顺序和t2表中记录依次匹配。
• 匹配完成后，我们得到了一张有 （t1表中记录数 × t2表中记录数）条的临时表。 在内存中形成的临时表称为‘笛卡尔积表’。

针对以上的理论，我们提出一个问题，难道表连接的时候都要先形成一张笛卡尔积表吗？如果两张表的数据量都比较大的话，那样就会占用很大的内存空间这显然是不合理的。所以，我们在进行表连接查询的时候一般都会使用JOIN xxx ON xxx的语法，ON语句的执行是在JOIN语句之前的，也就是说两张表数据行之间进行匹配的时候，会先判断数据行是否符合ON语句后面的条件，再决定是否JOIN。

因此，有一个显而易见的SQL优化的方案是，当两张表的数据量比较大，又需要连接查询时，应该使用 FROM table1 JOIN table2 ON xxx的语法，避免使用 FROM table1,table2 WHERe xxx 的语法，因为后者会在内存中先生成一张数据量比较大的笛卡尔积表，增加了内存的开销。

5、数据库表结构优化

1）、根据数据库的范式，设计表结构，表结构设计的好坏直接关系到SQL语句的复杂度

正常情况之下我们都会依据数据库设计范式来设计数据库。针对一般的系统我们会设计到第三范式就差不多了，最多到BC范式。

那如果对系统分类特别严格的，我们一般是先判断当前系统是OLTP系统还是OLAP系统，如果是OLAP系统的话，那么查询语句会相对比较多，那我们在设计表的时候就会适当的进行数据冗余，可以设计到第二范式，这样设计的结果就是“少表多字段”，这样人为的设计成冗余字段的表在查询数据的时候就减少了多表联表查询，从而减少了中间的结果集，本质上就是减少了IO，提高了查询效率。这是典型的以空间换时间的案例。

那如果是OLTP系统，那么增，删，改操作比较多，那我们可以设计成“多表少字段”，将表尽量拆分，此类系统一般不需要索引或者只要少量索引，因为如果索引多了会影响效率，因为做增，删，改操作的时候，对应字段的索引页需要被维护（索引会自行进行裂变等消耗资源的操作）。

2）、适当的将表进行拆分，原本需要做join的查询只需要一张单表查询就可以了

这就是上面提到的OLAP系统的情况。这里不再赘述。

6、系统配置优化

大多数运行在Linux机器上，如tcp连接数的限制、打开文件数的限制、安全性的限制，因此我们要对这些配置进行相应的优化。

7、硬件配置优化

1）、数据库主机的IO性能是需要最优先考虑的一个因素

2）、数据库主机和普通的应用程序服务器相比，资源要相对集中很多，单台主机上所需要进行的计算量自然也就比较多，所以数据库主机的CPU处理能力也是一个重要的因素

3）、数据库主机的网络设备（一般指网卡等）的性能也可能会成为系统的瓶颈

三、SQL及索引优化

要进行sql优化，我们得先安装mysql。

1、mysql安装（在线安装）

1）、访问：
https://dev.mysql.com/downloads/repo/yum/

我的操作系统是centos7，所以我选择如下的版本，大家可以根据自己的系统版本选择对应的版本来安装。

2）、点击Download，选择最下面的No thanks，just start my download，如下图：

3）、安装过程

• 先移除mariadb数据库：yum remove mariadb-libs.x86_64
• 创建mysql目录：mkdir /etc/mysql cd /etc/mysql
• 下载安装wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
• 添加到本地yum localinstall mysql80-community-release-el7-3.noarch.rpm
• 正式安装yum install mysql-community-server
• 启动测试service mysqld start service mysqld status
• 查看默认密码并且登录查看默认密码：cat /var/log/mysqld.log | grep password 使用默认密码登陆：mysql -uroot -p
• 修改密码set global validate_password.policy=0; set global validate_password.length=1; ALTER USER “root”@”localhost” IDENTIFIED BY “123456”; ##新密码为123456
• 退出exit
• 输入密码即可登录mysql -uroot -p

2、数据库版本选择

1）查看数据库的版本

执行命令

select @@version;

注意：这里是2个@符号

我们用的是当前最新的mysql8.0.20版本。

2）准备数据

我们要演示的案例的sql脚本来源于官网的sakila这个数据库，这是一个影片租售商店的系统数据库，大概10几张表，下面带大家下载并导入。

访问mysql官网。

示例数据：
https://dev.mysql.com/doc/sakila/en/sakila-installation.html

具体的下载操作请同学们观看视频，这里不再赘述。

3）导入数据

参照官网：
https://dev.mysql.com/doc/sakila/en/sakila-installation.html

官网上每一步都有具体说明，同学们可以参考官网说明一步一步操作；或者跟着我的视频操作也是可以的。

4）表结构关系

这里有个简单的表结构关系，这是pdman导出的物理模型。

我在视频讲解中也带大家用pd导出了整个sakila库的表物理模型，通过物理模型，我们可以对表与表之间的关系有一个清晰的了解。

为什么要带大家做这个导出物理模型的事情呢？因为导出物理模型在开发中还是很常见的，比如我们接手了一个老的项目，整个项目的相关文档早就缺失了，当年做这个项目的同学也早就跳槽了，那么我们要维护这样的一个什么资料都没有，也没有人可以咨询的这样的一个遗留系统的话，我们该如何去理清它的业务逻辑呢？这时候物理模型就能派上用场，导出来之后我们可以一目了然的看到表与表之间的关系，通过这层关系有助于我们理解整个系统的业务逻辑。

3、问题SQL筛查步骤

1）检查慢查日志是否开启：

执行如下命令，查看是否开启：OFF为关闭，ON为开启

show slow_query_log

2）检查慢查日志路径：

执行如下命令查看日志路径

show variables like‘%slow_query_log%';

3）开启慢查日志：

执行如下命令开启慢查日志

set global slow_query_log=on;

4）慢查日志判断标准（默认查询时间大于10s的sql语句）：

show variables like 'long_query_time';

为了测试方便可以修改为1秒

set global  long_query_time=1;

5）慢查日志测试：

执行如下sql，休眠11秒，超过10秒，迫使该sql进慢查日志

select sleep(11);

执行

tail -f /var/lib/mysql/myshop02-slow.log

检查慢查日志记录情况,发现已经记录进来了。

6）为了测试方便，所有查询都记录进慢查日志：

先检查下使用索引情况

show variables like'%log%';

设置开启即可

set global log_queries_not_using_indexes=on;

4、Jmeter压测mysql

打开jmeter，加入相关测试配置项，具体操作请观看视频

5、MySQL慢查日志的存储格式解析

具体的详细解析请观看视频。

四、mysql慢查询日志分析工具

1、mysqldumpslow

1）、简介：

​ 如果开启了慢查询日志，就会生成大量的数据，然后我们就可以通 过对日志的分析，生成分析报表，通过报表进行优化。

2）、用法：

执行 mysqldumpslow –help 查看详细用法

注意：在mysql数据库所在的服务器上，而不是在mysql>命令行中

3）、 执行结果：

mysqldumpslow --help

具体参数讲解请观看视频

2、mysqldumpslow用法示例

查看慢查询日志的前10条记录

mysqldumpslow -t 10 /var/lib/mysql/myshop02-slow.log

mysqldumpslow 分析的结果如下：

日志显示：select sleep(N)语句共执行了4次，共花费了36s，平均9s/次，锁表时间0.00s，共返回行rows=4，平均1行。

更详细的解释请观看视频

3、mysqldumpslow优缺点

这个工具是最常用的工具，通过安装mysql进行附带安装，但是该工具统计的结果比较少，对我们的优化所提供的信息还是比较少，比如cpu，io等信息都没有，所以我们需要更强大的工具，就是我们下节要讲的

pt-query-digest。

4、pt-query-digest

1）、简介

pt-query-digest是用于分析mysql慢查询的一个第三方工具，它可以分析binlog、General log、slowlog，也可以通过SHOWPROCESSLIST或者通过tcpdump抓取的MySQL协议数据来进行分析。

可以把分析结果输出到文件中，分析过程是先对查询语句的条件进行参数化，然后对参数化以后的查询进行分组统计，统计出各查询的执行时间、次数、占比等，可以借助分析结果找出问题进行优化。

2）、pt-query-digest本质是perl脚本，所以首先安装perl模块

执行如下命令安装perl

 yum install -y perl-CPAN perl-Time-HiRes

​

3）、快速安装

执行wget命令，wget下载后进行本地安装，执行yum localinstall -y，命令如下

wget https://www.percona.com/downloads/percona-toolkit/3.2.0/binary/redhat/7/x86_64/percona-toolkit-3.2.0-1.el7.x86_64.rpm && yum localinstall –y percona-toolkit-3.2.0-1.el7.x86_64.rpm

4）检查是否安装完成

执行

pt-query-digest --help

如出现下图信息表示已经安装成功。

5、pt-query-digest常用命令详解

1）查看服务器信息

命令：

pt-summary

可以看到服务器相关的具体信息：有系统日期，主机，更新时间，内核，平台，进程数等。

具体信息详细解释请观看视频

2）查看磁盘开销使用信息

命令：

pt-diskstats

可以看到磁盘开销相关的具体信息：磁盘使用率，繁忙度等信息。

具体信息详细解释请观看视频

3）查看mysql数据库信息

命令：

pt-mysql-summary --user=root --password=123456

可以看到mysql相关的具体信息：有系统时间，数据库实例，msyql服务路径，主从信息等信息。

具体信息详细解释请观看视频

4）分析慢查询日志(重点)

命令：

pt-query-digest /var/lib/mysql/myshop02-slow.log

这个统计信息内容有很多，以总-分的形式统计了慢查询日志的分析信息，有执行总时间，最小时间，最大时间，平均时间，达到95%的时间是多少，标准时间，中位时间（也就是从小到大排列，排在中间的值）等详细信息，该日志非常重要，是我们判断慢查询的重要依据。

具体信息的剖析我在视频课程里都有详细的讲解。

5）、查找mysql的从库和同步状态

命令：

pt-slave-find --host=localhost --user=root --password=123456

这个命令通常DBA同学会用到，这是做完集群之后，查看主从之间的同步状态的。

6）、查看mysql的死锁信息

命令：

pt-deadlock-logger --run-time=10 --interval=3 --create-dest-table --dest D=test,t=deadlocks u=root,p=123456

这个命令开发的同学经常会用，通常会用来进行故障排查，当怀疑系统发生死锁的时候或者已经发生死锁的时候就可以执行这个命令进行查看。

具体死锁演示和日志信息解释见视频课程

7）、从慢查询日志中分析索引使用情况

命令：

pt-index-usage --user=root --password=123456 --host=localhost   /var/lib/mysql/myshop02-slow.log

这里日志显示，我对test库的t2表做了删除索引“idx_t2_id”这个动作。

8）、从慢查找数据库表中重复的索引

命令：

pt-duplicate-key-checker --host=localhost --user=root --password=123456

日志显示，我对test库的t3表做了删除索引“idx_idc_name_id”后，又新增了这个索引。

最后三行显示了重复索引相关信息。

9）、查看mysql表和文件的当前活动IO开销（不要在高峰时用）

命令：

pt-ioprofile

当没有大量IO的时候，查询不到信息，因为没有IO开销

10）、查看不同mysql配置文件的差异（集群常用，双方都生效的变量）

命令：

pt-config-diff /etc/my.cnf /root/my_master.cnf

这个通常也是用在集群中，主从复制或者出现问题排查，主机和从机差异的时候经常用到。

具体演示可以参见视频，这里不再赘述。

11）、pt-find查找mysql表和执行命令，示例如下：

• 查找数据库里大于1M的表

pt-find --user=root --password=123456 --tablesize +1M

这里显示有4张表的大小超过1M，这个命令通常可以用来决定当表膨胀之后，比如达到百万级，这时候就需要判断是否要对表做水平或者垂直拆分，那么判断标准就可以看表的大小。

• 查看表和索引大小并排序

pt-find --user=root --password=123456 --printf "%Tt%D.%Nn" | sort -rn

可以看到都已经从大到小进行排列了，最大的是test库t1表。

12）、pt-kill 杀掉符合标准的mysql进程，示例如下：

• 显示查询时间大于3秒的查询

pt-kill --user=root --password=123456 --busy-time 3 --print

这个命令只打印信息，不kill进程，如需要强杀进程可执行下面的命令

• kill掉大于3秒的查询

pt-kill --user=root --password=123456 --busy-time 3 --kill

13）、查看mysql授权（集群常用，授权复制），示例如下：

pt-show-grants --user=root --password=123456

pt-show-grants --user=root --password=123456 --separate --revoke

可以看到大量的授权信息，这个命令主要也是用在集群的时候，在从机上需要和主机上进行一模一样的授权，直接可以通过这个命令获取所有的授权，然后复制过去就ok了。

14）、验证数据库复制的完整性（集群常用，主从复制后检验），示例如下：

pt-table-checksum --user=root --password=123456

这个信息主要查看点是ERRORS和DIFFS的信息，如果不为0就要注意了，检查主从复制的时候是不是出现了问题。

五、利用pt-query-digest利器查找三大类有问题的SQL

1、查询次数多且每次查询占用时间长的sql

通常为pt-query-digest分析的前几个查询，该工具可以很清楚的看出每个SQL执行的次数及百分比等信息，执行的次数多，占比比较大的SQL

具体的排查分析讲解请观看视频

2、IO大的sql

注意pt-query-digest分析中的Rows examine项，扫描的行数越多，IO越大。

Rows examine项的具体讲解在日志分析的视频课程中，请自行查看

3、未命中索引的SQL

pt-query-digest分析中的Rows examine 和Rows Send的对比。说明该SQL的索引命中率不高，对于这种SQL，我们要重点进行关注。

Rows examine 和Rows Send的关系和区别在视频中有详细讲解。

六、通过explain分析SQL执行计划

1、使用explain查询SQL的执行计划

SQL的执行计划反映出了SQL的执行效率，在执行的SQL前面加上explain即可

2、执行计划的字段解释与举例

以下列出了常见的字段解释，具体举例请观看视频

1）、id列

数字越大越先执行，如果数字一样大，那么就从上往下依次执行，id列为null就表示这是一个结果集，不需要使用它来进行查询。

2）、select_type列

• simple：表示不需要union操作或者不包含子查询的简单select查询，有连接查询时，外层的查询为simple，且只有一个。
• primary：一个需要union操作或者含有子查询的select，位于最外层的查询，select_type即为primary，且只有一个。
• union：union连接的两个select查询，第一个查询是dervied派生表，除了第一个表外，第二个以后的表select_type都是union。
• union result：包含union的结果集，在union和union all语句中,因为它不需要参与查询，所以id字段为null。
• dependent union：与union一样，出现在union 或union all语句中，但是这个查询要受到外部查询的影响。
• subquery：除了from子句中包含的子查询外，其他地方出现的子查询都可能是subquery。
• dependent subquery：与dependent union类似，表示这个subquery的查询要受到外部表查询的影响。
• derived：from子句中出现的子查询，也叫做派生表，其他数据库中可能叫做内联视图或嵌套select。
• materialization :物化通过将子查询结果作为一个临时表来加快查询执行速度，正常来说是常驻内存，下次查询会再次引用临时表。

3）、table列

显示的查询表名，如果查询使用了别名，那么这里显示的是别名，如果不涉及对数据表的操作，那么这显示为null，如果显示为尖括号括起来的就表示这个是临时表，后边的N就是执行计划中的id，表示结果来自于这个查询产生。如果是尖括号括起来的，与类似，也是一个临时表，表示这个结果来自于union查询的id为M,N的结果集。

4）、type列

• system：表中只有一行数据或者是空表，且只能用于myisam和memory表，如果是Innodb引擎表，type列在这个情况通常都是all或者index。
• const：使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const，其他数据库也叫做唯一索引扫描。
• eq_ref：出现在要连接多个表的查询计划中，驱动表循环获取数据，这行数据是第二个表的主键或者唯一索引，作为条件查询只返回一条数据，且必须为not null，唯一索引和主键是多列时，只有所有的列都用作比较时才会出现eq_ref。
• ref：不像eq_ref那样要求连接顺序，也没有主键和唯一索引的要求，只要使用相等条件检索时就可能出现，常见与辅助索引的等值查找或者多列主键、唯一索引中，使用第一个列之外的列作为等值查找也会出现，总之，返回数据不唯一的等值查找就可能出现。
• fulltext：全文索引检索，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引。
• ref_or_null：与ref方法类似，只是增加了null值的比较，实际用的不多。
• unique_subquery：用于where中的in形式子查询，子查询返回不重复值唯一值。
• index_subquery：用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。
• range：索引范围扫描，常见于使用>,<,is null,between ,in ,like等运算符的查询中。
• index_merge：表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取多个索引，性能可能大部分时间都不如range。
• index：索引全表扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。
• all：这个就是全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。

type列总结：

依次性能从好到差：system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，ALL，除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一个索引。一般来说，好的sql查询至少达到range级别，最好能达到ref。

5）、possible_keys列

查询可能使用到的索引。

6）、key列

查询真正使用到的索引，select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。

7）、key_len列

用于处理查询的索引长度，如果是单列索引，那就是整个索引长度，如果是多列索引，那么查询不一定都能使用到所有的列，具体使用到了多少个列的索引，这里就会计算进去，没有使用到的列不会计算进去。留意下这个列的值，算一下你的多列索引总长度就知道有没有使用到所有的列了。另外，key_len只计算where条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到key_len中。

8）、ref列

如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func

9）、rows列

这里是执行计划中估算的扫描行数，不是精确值。

10）、extra列

• no tables used：不带from字句的查询或者From dual查询。
• NULL：查询的列未被索引覆盖，并且where筛选条件是索引的前导列，意味着用到了索引，但是部分字段未被索引覆盖，必须通过“回表”来实现，不是纯粹地用到了索引，也不是完全没用到索引。
• using index：查询时不需要回表查询，直接通过索引就可以获取查询的数据。
• Using where：查询的列未被索引覆盖，where筛选条件非索引的前导列。
• Using where Using index：查询的列被索引覆盖，并且where筛选条件是索引列之一但是不是索引的前导列，意味着无法直接通过索引查找来查询到符合条件的数据。
• Using index condition：与Using where类似，查询的列不完全被索引覆盖，where条件中是一个前导列的范围。
• using temporary：表示使用了临时表存储中间结果。临时表可以是内存临时表和磁盘临时表，执行计划中看不出来，需要查看status变量，used_tmp_table，used_tmp_disk_table才能看出来。
• using filesort：mysql 会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。此时mysql会根据联接类型浏览所有符合条件的记录，并保存排序关键字和行指针，然后排序关键字并按顺序检索行信息。这种情况下一般也是要考虑使用索引来优化的。
• using intersect：表示使用and的各个索引的条件时，该信息表示是从处理结果获取交集。
• using union：表示使用or连接各个使用索引的条件时，该信息表示从处理结果获取并集。
• using sort_union和using sort_intersection：用and和or查询信息量大时，先查询主键，然后进行排序合并后返回结果集。
• firstmatch(tb_name)：5.6.x开始引入的优化子查询的新特性之一，常见于where字句含有in()类型的子查询。如果内表的数据量比较大，就可能出现这个。
• loosescan(m..n)：5.6.x之后引入的优化子查询的新特性之一，在in()类型的子查询中，子查询返回的可能有重复记录时，就可能出现这个。

11）、filtered列

使用explain extended时会出现这个列，5.7之后的版本默认就有这个字段，不需要使用explain extended了。这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数。

七、慢查询优化思路及案例

1、慢查询的总体优化思路

• 优化更需要优化的SQL
• 定位优化对象的性能瓶颈
• 明确的优化目标
• 从explain执行计划入手
• 永远用小结果集驱动大的结果集
• 尽可能在索引中完成排序
• 只取出自己需要的列，不要用select *
• 仅使用最有效的过滤条件
• 尽可能避免复杂的join和子查询
• 小心使用order by，group by，distinct语句
• 合理设计并利用索引

以上优化思路的具体说明请观看视频

2、永远用小结果集驱动大的结果集（join操作表小于百万级别）

1）、驱动表的定义

当进行多表连接查询时， [驱动表] 的定义为：

• 指定了联接条件时，满足查询条件的记录行数少的表为[驱动表]
• 未指定联接条件时，行数少的表为[驱动表]

2）、mysql关联查询的概念

MySQL 表关联的算法是 Nest Loop Join，是通过驱动表的结果集作为循环基础数据，然后一条一条地通过该结果集中的数据作为过滤条件到下一个表中查询数据，最后合并结果。

3）、left join，right join，inner join的区别

具体说明见视频

• left joinselect * from t2 left join t3 on t2.id =t3.id and t3.id in(1,2,3) order by t2.id desc;

• right joinselect * from t2 right join t3 on t2.id =t3.id and t3.id in(1,2,3);

• inner joinselect * from t2 inner join t3 on t2.id =t3.id and t3.id in(1,2,3);

3、join的实现原理

具体的原理讲解请观看视频

1）、mysql只支持一种join算法：

Nested-Loop Join（嵌套循环连接），但Nested-Loop Join有三种变种：

• Simple Nested-Loop Join（简单嵌套循环）
• Index Nested-Loop Join（索引嵌套循环）
• Block Nested-Loop Join（块嵌套循环）

2）、Simple Nested-Loop Join（简单嵌套循环）

3）、Index Nested-Loop Join（索引嵌套循环）

4）、Block Nested-Loop Join（块嵌套循环）

5）、Block Nested-Loop Join（3表）

4、join的优化思路

具体的思路讲解见视频

1）、尽可能减少join语句中的Nested Loop的循环总次数

2）、优先优化Nested Loop的内层循环

3）、保证join语句中被驱动表上join条件字段已经被索引

4）、无法保证被驱动表的 Join 条件字段被索引且内存资源充足的前提下，不要太吝惜join Buffer的设置

5、join的优化思路总结

1）、并发量太高的时候，系统整体性能可能会急剧下降。

2）、复杂的 Join 语句，所需要锁定的资源也就越多，所阻塞的其他线程也就越多

3）、复杂的 Query 语句分拆成多个较为简单的 Query 语句分步执行

6、只取出需要的列，不要用select *

1）、如果取出的列过多，则传输给客户端的数据量必然很大，浪费带宽

2）、若在排序的时候输出过多的列，则会浪费内存（Using filesort）

3）、若在排序的时候输出过多的列，还有可能改变执行计划

具体讲解及演示见视频

7、仅使用最有效的过滤条件

1）、Where字句中条件越多越好吗？

2）、若在多种条件下都使用了索引，那如何选择？

3）、最终选择方案：key_len的长度决定使用哪个条件

具体讲解及演示见视频

8、尽可能在索引中完成排序

• order by 字句中的字段加索引（扫描索引即可，内存中完成，逻辑io）。
• 若不加索引的话会可能会启用一个临时文件辅助排序（落盘，物理io）。

具体讲解及演示见视频

9、order by排序原理及优化思路

• order by排序可利用索引进行优化，order by子句中只要是索引的前导列都可以使索引生效，可以直接在索引中排序，不需要在额外的内存或者文件中排序。
• 不能利用索引避免额外排序的情况，例如：排序字段中有多个索引，排序顺序和索引键顺序不一致（非前导列）

具体讲解及演示见视频

10、order by排序算法

MySQL对于不能利用索引避免排序的SQL，数据库不得不自己实现排序功能以满足用户需求，此时SQL的执行计划中会出现“Using filesort”，这里需要注意的是filesort并不意味着就是文件排序，其实也有可能是内存排序，这个主要由sort_buffer_size参数与结果集大小确定。MySQL内部实现排序主要有3种方式，常规排序，优化排序和优先队列排序，主要涉及3种排序算法：快速排序、归并排序和堆排序

1）、order by常规排序算法

步骤：

• 从表t1中获取满足WHERe条件的记录。
• 对于每条记录，将记录的主键+排序键(id,col2)取出放入sort buffer。
• 如果sort buffer可以存放所有满足条件的(id,col2)对，则进行排序；否则sort buffer满后，进行排序并固化到临时文件中。(排序算法采用的是快速排序算法)
• 若排序中产生了临时文件，需要利用归并排序算法，保证临时文件中记录是有序的。
• 循环执行上述过程，直到所有满足条件的记录全部参与排序。
• 扫描排好序的(id,col2)对，并利用id去捞取SELECT需要返回的列(col1,col2,col3)。
• 将获取的结果集返回给用户。

2）、order by优化排序算法

常规排序方式除了排序本身，还需要额外两次IO。

优化的排序方式相对于常规排序，减少了第二次IO。

主要区别在于，放入sort buffer不是(id,col2),而是(col1,col2,col3)。由于sort buffer中包含了查询需要的所有字段，因此排序完成后可以直接返回，无需二次捞数据。这种方式的代价在于，同样大小的sort buffer，能存放的(col1,col2,col3)数目要小于(id,col2)，如果sort buffer不够大，可能导致需要写临时文件，造成额外的IO。

3）、order by优先队列排序算法

5.6及之后的版本针对Order by limit M，N语句，在空间层面做了优化，加入了一种新的排序方式–优先队列，这种方式采用堆排序实现。堆排序算法特征正好可以解limit M，N 这类排序的问题，虽然仍然需要所有元素参与排序，但是只需要M+N个元组的sort buffer空间即可，对于M，N很小的场景，基本不会因为sort buffer不够而导致需要临时文件进行归并排序的问题。

对于升序，采用大顶堆，最终堆中的元素组成了最小的N个元素，对于降序，采用小顶堆，最终堆中的元素组成了最大的N的元素。

11、order by排序不一致问题

1）、MySQL5.6发现分页出现了重复值

2）、MySQL8查询正常

3）、原因分析及解决方案

针对limit M,N的语句采用了优先队列，而优先队列采用堆实现，比如上述的例子order by idc limit 0，3 需要采用大小为3的大顶堆；limit 3，3需要采用大小为6的大顶堆。由于idc为3的记录有3条，而堆排序是非稳定的(对于相同的key值，无法保证排序后与排序前的位置一致)，所以导致分页重复的现象。为了避免这个问题，我们可以在排序中加上唯一值，比如主键id，这样由于id是唯一的，确保参与排序的key值不相同。

12、order by排序案例演示

演示sql及思路：

explain select idc, max(name) from t3 where id>2 and id<10 order by idc,name,idG

分别在查询字段，where条件，分组字段上做出各种可能的组合，主要就是看有无索引，索引在以上三个关注点上的生效情况。

order by排序的多种案例优化演示请观看视频

13、group by分组优化思路

group by本质上也同样需要进行排序操作，而且与 order by相比，group by主要只是多了排序之后的分组操作。如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在group by的实现过程中，与group by一样也可以利用到索引。

具体案例及优化演示讲观看视频

14、group by的类型

三种实现类型：

• Loose Index Scan（松散的索引扫描）

扫描过程：

先根据group by后面的字段进行分组，分组不需要读取所有索引的key，例如index（key1，key2，key3），group by key1，key2。此时只要读取索引中的key1，key2。然后再根据where条件进行筛选。

• Tight Index Scan（紧凑的索引扫描）

扫描过程：

紧凑索引扫描需要在扫描索引的时候，读取所有满足条件的索引键，然后再根据读取的数据来完成 GROUP BY 操作得到相应结果。

两者区别就是紧凑索引扫描是先执行where操作，再进行分组，松散索引扫描刚好相反

• Using temporary 临时表实现 （非索引扫描）

扫描过程：

MySQL 在进行GROUP BY 操作的时候当MySQL Query Optimizer无法找到合适的索引可以利用的时候，就不得不先读取需要的数据，然后通过临时表来完成 GROUP BY操作。

15、group by分组案例演示

演示sql及思路：

explain select idc, max(name) from t3 where id>2 and id<10 group by idc,name,idG

和order by一样，分别在查询字段，where条件，分组字段上做出各种可能的组合，主要就是看有无索引，索引在以上三个关注点上的生效情况。

具体案例及优化演示讲观看视频

16、distinct的实现及优化思路

1）、distinct的原理

distinct实际上和 GROUP BY 的操作非常相似，在GROUP BY之后的每组中只取出一条记录而已。所以，DISTINCT的实现和GROUP BY的实现也基本差不多，同样可以通过松散索引扫描或者是紧凑索引扫描来实现，当然，在无法仅仅使用索引即能完成DISTINCT的时候， MySQL只能通过临时表来完成。但是，和GROUP BY有一点差别的是，DISTINCT并不需要进行排序。

2）、distinct案例演示

演示sql及思路：

explain select distinct name from t3 where idc=3G

（索引中完成，索引默认是排好序的）

explain select distinct name from t3 where idc>1G

（非索引中完成，暗藏排序问题）

具体案例及优化演示讲观看视频

17、合理的设计并利用索引

索引原理

18、索引失效案例

具体案例见视频

19、优化终级奥义

• 针对百万数量级，放弃在mysql中的join操作，推荐分别根据索引单表取数据，然后在程序里面做join，merge数据。
• 尽量使用nosql，例如redis， memcached 等来缓存热点数据，从而缓解mysql压力。