上篇回顾

继上篇：MySQL 慢查问（上）：你晓得为啥会慢么？

在上一篇内容中，咱们一起摸索了这些内容：

• SQL 执行过程
• 查问 SQL 为什么会慢

通过梳理 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 users
LEFT JOIN orders ON orders.user_id = users.user_id
LEFT JOIN goods ON goods.good_id = orders.good_id
WHERE 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 慢查问问题细数起来，林林总总太多了，但卓有成效的无外乎这几种：

• 优化数据结构
• 利用索引策略
• 查问缓存

实际出真知，如果大家有任何其余好的解决办法能够留言与我交换，心愿对你的工作有所帮忙，谢谢！

• END –

Thanks for reading!