![]()
我是架构精进之路，点击上方“关注”，坚持每天为你分享技术干货，私信我回复“01”，送你一份程序员成长进阶指南大礼包。
上篇回顾
继上篇：MySQL慢查询（上）：你知道为啥会慢么？
在上一篇内容中，我们一起探索了这些内容：
通过梳理 MySQL中的 SQL执行过程我们发现，任何流程的执行都存在其执行环境和规则，主要导致慢查询最根本的问题就是需要访问的数据太多，导致查询不可避免的需要筛选大量的数据。
如果将MySQL慢查询作为一个问题来拆解分析的话，上一篇算是问题分析，那今天来跟大家聊聊问题定位和问题解决。
![]()
本文主要内容包括：
1、如何定位慢查询问题
2、几种实用解决方案
废话不多说，直接开干~
1、如何定位问题呢？
发现了慢查询之后，关于如何定位问题发生原因，最常用的方法就是利用EXPLAIN关键字模拟查询优化器执行查询SQL，从而知道MySQL是如何处理你的查询SQL，通过执行计划来分析性能瓶颈。
通常我们使用EXPLAIN，会得到如下下的执行计划信息：
![]()
关于各字段含义，大家可以通过检索自行了解，在此就不再过多赘述。
关于定位分析问题，关键看如下几点：
1）select_type
表示查询类型，用于区别普通查询、联合查询、子查询等复杂查询。
2）type
显示查询使用类型，从好到差依次为：system > const > eq_ref > ref > range > index > all
3）possible_keys 和 key
分别指可能应用的索引和实际应用的索引。
注意：查询中若使用了覆盖索引（select 后要查询的字段刚好和创建的索引字段完全相同），则该索引仅出现在key列表中。
4）rows
大致估算出找到所需记录所需要读取的行数（从效率上来讲，数值越小越好）
5）Extra
重要的额外信息。包含MySQL解决查询的详细信息，也是关键参考项之一。
2、几种实用解决方案
我们通过EXPLAIN关键字模拟查询优化器执行查询SQL，发现了慢查询问题原因，那看看如何才能有效解决呢？
推进几种较为实用的解决方案给大家。
2.1 优化数据结构
2.1.1 选择索引的数据类型
MySQL支持很多数据类型，选择合适的数据类型存储数据对性能有很大的影响。
通常来说，可以遵循以下一些指导原则：
(1)越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。
(2)简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂。在MySQL中，应该用内置的日期和时间数据类型，而不是用字符串来存储时间；以及用整型数据类型存储IP地址。
(3)尽量避免NULL：应该指定列为NOT NULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。
2.1.2 范式与反范式
范式化模型要求满足下面三大范式：
1）数据库表中每个字段只包含最小的信息属性，不能再进行细化分解；
2）（在满足1的基础上）模型含有主键，非主键字段依赖主键；
比如用户这个模型，它的主键是用户ID，那么用户模型其它字段都应该依赖于用户ID
如商品ID和用户没有直接关系，则这个属性不应该放到用户模型而应该放到“用户-商品”中间表。
3）（在满足2的基础上）模型非主键字段不能相互依赖。
订单表(订单编号，订购日期，顾客编号，顾客姓名，……)
初看该表没有问题，满足第二范式，每列都和主键列”订单编号”相关。
再细看你会发现“顾客姓名”和“顾客编号”相关，“顾客编号”和“订单编号”又相关，最后经过传递依赖，“顾客姓名”也和“订单编号”相关。
为了满足第三范式，应去掉“顾客姓名”列，放入客户表中。
反范式化模型即不满足范式化的模型。主要是为了性能和效率的考虑适当的违反范式化设计要求，允许存在少量的数据冗余，即以空间换时间。
小结
可见一个良好而实用的数据模型往往是依赖于具体的需求场景的，在设计数据模型之前，仔细分析需求场景，不仅能提高效率，也能有效规避后期可能遇到的一些意外麻烦。
范式化设计和反范式化设计的优劣对比如下：
1、范式化可以尽量的减少数据冗余
2、范式化的更新操作比反范式化更快
3、范式化的表通常比反范式化的表要小
4、反范式化减少表的关联
5、反范式化相比范式化可以更好的对索引进行优化，例如使用覆盖索引。
关于数据库范式与反范式设计，详情可参考我之前的一篇文章：数据库范式与反范式设计，是一门艺术
2.2 应用索引策略
![]()
索引（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索引进行全关键字、关键字范围和关键字前缀查询，当然，如果想使用索引，必须保证按索引的最左边前缀(leftmost prefix of the index)来进行查询。
2.2.1 最左边前缀主要规则
-
匹配全值(Match the full value)：对索引中的所有列都指定具体的值。例如，上图中索引可以帮助你查找出生于1960-01-01的Cuba Allen。
-
匹配最左前缀(Match a leftmost prefix)：你可以利用索引查找last name为Allen的人，仅仅使用索引中的第1列。
-
匹配列前缀(Match a column prefix)：例如，你可以利用索引查找last name以J开始的人，这仅仅使用索引中的第1列。
-
匹配值的范围查询(Match a range of values)：可以利用索引查找last name在Allen和Barrymore之间的人，仅仅使用索引中第1列。
-
匹配部分精确而其它部分进行范围匹配(Match one part exactly and match a range on another part)：可以利用索引查找last name为Allen，而first name以字母K开始的人。
-
仅对索引进行查询(Index-only queries)：如果查询的列都位于索引中，则不需要读取元组的值。
由于B-树中的节点都是顺序存储的，所以可以利用索引进行查找(找某些值)，也可以对查询结果进行ORDER BY。
当然，使用B-tree索引有以下一些限制：
-
查询必须从索引的最左边的列开始。关于这点已经提了很多遍了。例如你不能利用索引查找在某一天出生的人。
-
不能跳过某一索引列。例如，你不能利用索引查找last name为Smith且出生于某一天的人。
-
存储引擎不能使用索引中范围条件右边的列。例如，如果你的查询语句为WHERE lastname="Smith" AND firstname LIKE 'J%' AND dob='1976-12-23'，则该查询只会使用索引中的前两列，因为LIKE是范围查询。
2.2.2 聚簇索引
聚簇索引保证关键字的值相近的元组存储的物理位置也相同（所以字符串类型不宜建立聚簇索引，特别是随机字符串，会使得系统进行大量的移动操作），且一个表只能有一个聚簇索引。因为由存储引擎实现索引，所以，并不是所有的引擎都支持聚簇索引。目前，只有solidDB和InnoDB支持。
InnoDB对主键建立聚簇索引。如果你不指定主键，InnoDB会用一个具有唯一且非空值的索引来代替。如果不存在这样的索引，InnoDB会定义一个隐藏的主键，然后对其建立聚簇索引。
2.3 查询缓存
MySQL查询缓存会保存查询返回的完整结果。当查询命中缓存，MySQL会立刻返回结果，而跳过了后续解析、优化以及执行阶段，会有效提升查询性能。
但是查询缓存不是银弹，它也会存在一些问题。
2.3.1 查询缓存注意事项
1）缓存情况严格
存在一些不确定函数情况无法使用查询缓存，如：NOW()、CURRENT_DATE() 等类似的函数；
超过 query_cache_size （设置查询缓存空间大小）的查询结果无法被缓存；
同时大小写敏感，只有字符串相等情况下查询SQL才使用相同缓存。
-- 不会使用同一个缓存select name from users where id = 1;SELECT name FROM users WHERE id = 1;
2）缓存易失效
假如缓存过查询结果，但是由于查询缓存设置内存不足，新缓存加入时MySQL会将某些缓存逐出，导致后续查询未命中。同时数据结构及数据修改，内存不足，缓存碎片都会导致缓存失效。
2.3.2 小结
查询缓存对应用程序完全透明，应用程序无需关心MySQL是通过查询缓存返回的还是实际执行返回的结果。但随着目前服务器性能越来越强，查询缓存被发现是一个影响服务器扩展性的因素，它很可能成为整个服务器的资源竞争点，大家采用生产环境开启应用时候一定要慎重考量。
2.4 重构查询方式
优化慢查询时候，我们可以转换下思路，我们的目标是找到一个更优的方法获取时间需要的结果，而不是一定从MySQL获取一模一样的结果集。重构查询的技巧很有必要。
2.4.1 复杂查询拆分
将一个复杂查询拆分多个简单查询，考虑是否需要将一个复杂查询拆分为多个简单查询。
实际开发过程中，大家往往会强调数据库层完成尽可能多的工作，这样做的初衷是认为网络通信、查询解析和优化是一件代价很高的事情，其实MySQL从设计上让连接和断开都很轻量级，同时在返回一个小查询结果方面很高效。况且目前网络速度也比之前快很多，无论是带宽还是延迟。
对于大查询我们要“分而治之”，将大查询切分成多个小查询。不过在一次查询能够胜任的情况下还拆成多个独立查询就不明智了。
例如：做数据库做10次查询，每次返回一行记录。
2.4.2 分解关联查询
将关联查询进行分解，对每一个表进行一次单表查询，然后将结果在应用程序中进行关联。
例如：
SELECT *FROM usersLEFT JOIN orders ON orders.user_id = users.user_idLEFT JOIN goods ON goods.good_id = orders.good_idWHERE users.name = 'zhangsan';
以上查询可以分解成下面的查询来代替：
SELECT * FROM users WHERE users.name = 'zhangsan';SELECT * FROM orders WHERE orders.user_id = 103;SELECT * FROM goods WHERE goods.good_id IN （123, 456, 789）;
为什么要这样做呢？看起来好像没有什么好处，而且返回数据结果也是一致的。实际上利用分解查询的方式来重构查询有很大的优势，主要表现为：
小结
MySQL慢查询问题细数起来，林林总总太多了，但行之有效的无外乎这几种：
-
优化数据结构
-
应用索引策略
-
查询缓存
-
重构查询方式
实践出真知，如果大家有任何其他好的解决方法可以留言与我交流，希望对你的工作有所帮助，谢谢！
闻道有先后，所有的技术成长无外乎不断的温习基础+实践锤炼，关注回复关键字【MySQL】，获取《高性能MySQL 第3版》PDF电子书，成就优秀的自己 ~
- END -
限于文章篇幅，同时为了大家更好的阅读体验，后面会连续产出系列文章：
MySQL慢查询（下）
主要内容包括 高性能查询难题优化内容点总结
最后，欢迎大家持续关注~
![]()
作者：架构精进之路，专注软件架构研究，技术学习与个人成长，关注并私信我回复“01”，送你一份程序员成长进阶指南大礼包。
![]()
「技术架构精进」专注架构研究，技术分享
Thanks for reading!
