MySQL优化数据库对象

发表于 更新于 分类于 阅读次数： 本文字数： 2.4k 阅读时长 ≈ 9 分钟

MySQL优化方法——优化数据库对象

如果没有进行特别说明，一般是基于MySQL8.0.28进行测试验证。官方文档非常具有参考意义。你可以将这篇博文，当成过度到MySQL8.0的参考资料。友情提示：经验是用来参考，不是拿来即用。如果您能看到并分享这篇文章，我很荣幸。如果有误导您的地方，我表示抱歉。

tips：图片资源可能被防盗链（寄）了，可以右键属性复制地址在地址栏查看哈。

MySQL优化方法——优化数据库对象

注意：在某些情况，你自己测试的结果可能与我演示有所不同，我省略了查询结果的部分参数。

本文侧重点在SQL优化流程以及MySQL锁问题（MyISAM和InnoDB存储引擎）。图片可能会挂，演示时尽量使用SQL查询语句返回结果进行示例。篇幅很长，因此使用markdown语法加了目录。

起初，也只是想看MySQL8.0.28有哪些变化，后面索性结合书籍和官方文档总结了一篇。花了将近两周，基本是每天完善一点，因为个人只有晚上和周末有时间总结并测试验证。如果有错别字，也请多多担待。如果你能看到并分享这篇文章，我很荣幸。如果有误导你的地方，我表示抱歉。

如果你是从MySQL5.6或者5.7版本过渡到MySQL8.0。学习之前，建议线看官方文档这一章节：1.3 What Is New MySQL8.0 。在做对比的时候，文档中带有Note标识是你应该注意的地方。比如下面这张截图：

优化数据库对象

第二部分，优化数据库对象。看看就行，因为没做过多示例介绍，以理论知识居多。

面对数据库设计过程，用户可能会遇到这类问题。是否完全遵循数据库设计三范式设计表结构？表的字段值大小到底设置为多长合适？这些问题看似很小，但设计不当则可能会给将来的应用带来很多性能问题。

01 优化表数据类型

设计表的时候，需要给定字段类型。

表需要使用何种数据类型应该依据实际应用来判断。当然，考虑到应用字段留有冗余是一个不错的选择。但并不推荐所有字段留有大量的冗余，因为浪费磁盘存储空间，同时在操作应用时也浪费物理内存。

在MySQL中，可以使用函数procedure analyse()对当前应用的表进行分析。该函数可以对数据表中列的数据类型提出优化建议，可以根据实际情况进行优化。

示例：MariaDB 10.5.6中使用procedure analyse()

1
2
3
4
5
6
7
8
9
10
11
12
MariaDB [test]> select * from student procedure analyse()\G
*************************** 1. row ***************************
             Field_name: test.student.ID
              Min_value: 1
              Max_value: 1000000
             Min_length: 1
             Max_length: 7
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 500000.5000
                    Std: 577357.8230
      Optimal_fieldtype: MEDIUMINT(7) UNSIGNED NOT NULL

最终给出的优化建议Optimal_fieldtype：MEDIUMINT(7) UNSIGNED NOT NULL，字段类型MEDIUMINT(7) 。

注意：在MySQL 5.x版本和MariaDB 10.5.6还可以使用。但在MySQL8.0.x版本已经被移除了，暂时没看到替代的方式。

PROCEDURE ANALYSE() syntax is removed.

摘自第1.3章节：Features Removed in MySQL 8.0

02 拆分表提高访问效率

看小标题已经描述很清晰，通过对数据表进行拆分。

假如针对MyISAM类型表进行，有如下两种方式：

  1. 垂直拆分：将主列和一些列存放至一张表中，然后将主列和另外的列存放到另一张表中。如果不好理解，可以想象一下垂直平分线的方式。如果一张表某些列常用，而另一些列不常用，则可以采取垂直拆分。

    垂直拆分可以使数据行变小，一个数据页可以存放更多数据，查询时会减少I/O次数。缺点在于需要管理冗余列，查询所有数据需要联合（union）操作。

  2. 水平拆分：根据一列或多列数据的值将数据行放入两张独立的表中。
    水平拆分通常在以下几种场景下使用：

    表很大，分割后可以降低在查询时需要读取的数据和索引页数。同时降低索引层数，提高查询速度。

    表中数据本就有独立性。比如，表中数据记录着不同地区的数据或者不同时间段的数据。区分常用数据和不常用数据，需要将数据存 放在多个介质上。

水平拆分会给应用增加复杂度，查询时通常需要联结多个表，查询所有数据需要使用UNION操作。考虑是否进行水平拆分，可以依据应用实际数据增长速率进行酌情处理。

03 逆规范

谈到逆规范，第一时间会想到规范，其次想到表中加入冗余字段便于操作。

从我们学习数据库知识起，已经深入到脑海里并理解满足规范设计的重要性。

是不是满足数据设计规范越高越好呢？以前数据库没那么多范式，最多满足3范式，现在到了N范式。个人理解，应该根据实际需求定，不应一概而论。规范越高，关系相对越复杂，表之间联结操作越频繁。如果是查询统计较多的应用，则大大影响查询性能。

设计逆规范时，我们想达到的目的是啥？降低联结操作需求、减少索引数目，也许还会减少表数目。如果带来数据完整性问题，如何处理。做逆规范，理应权衡利弊；弊大于利，则适得其反。如果优质索引可以解决，则不必增加逆规范。

使用逆规范前的思考：

  • 数据存储需求；
  • 常用表大小；
  • 特殊计算（比如合计）；
  • 物理存储位置。

常用逆规范技术手段：增加冗余列、派生列、重新组表和分割表。

使用逆规范操作，往往有一种比较友好的方式来应对处理，那就是触发器。对数据任何修改立即出发对复制列或派生列的相应修改。触发器是实时的，相应处理逻辑只在一个地方出现，易于维护。

04 中间表提高统计查询效率

曾几何时，你在面试时遇到是否有海量数据处理经验。如果是你来应对，如何处理，思考过如何回答么？

仔细想想，其实可以从单表存储数据过多，会带来哪些缺点进行思考。

对于数据量较大的表，进行统计查询通常效率会降低，并且还要考虑统计查询是否影响在线应用（负面影响）。通常在这种情况下，使用中间表可以提高查询效率。考虑前提，对转移（复制）当前表时间进行忽略。

使用方法进行示例：只需两步完成操作

1、创建新表使用源表数据结构（你也可以适当优化，比如常用字段加单独索引）。当时考虑Oracle中分批次生成1kw数据想到这种方法。

1
create table test.student01 as select * from test.student;

2、然后插入源表数据，这样做确实很方便。

1
insert into test.student01 select * from test.student;

做完之后，数据转移到中间表上进行统计，得到结果。既不影响在线应用，也可以快速查询统计。

中间表做统计查询优点：

  1. 复制源表部分数据，与源表隔离，中间表做统计查询不影响在线应用使用。
  2. 灵活添加索引，增加临时字段，最终达到提高统计查询效率。

参考资料&鸣谢：

  • 《深入浅出MySQL 第2版 数据库开发、优化与管理维护》。
  • 《MySQL技术内幕InnoDB存储引擎 第2版》。
  • MySQL8.0官网文档：refman-8.0-en.pdf，如果学习新版本，官方文档是非常不错的选择。

虽然书籍年份比较久远（停留在MySQL5.6.x版本），但仍然具有借鉴意义。

最后，对以上书籍和官方文档所有作者表示衷心感谢。让我充分体会到：前人栽树，后人乘凉。

莫问收获，但问耕耘

只停留在看上面，提升效果甚微。应该带着思考去测试佐证，或者使用（同类书籍）新版本进行对比，这样带来的效果更好。最重要的一环，养成阅读官方文档，是一个良好的习惯。能编写官方文档，至少证明他们在这个领域是有很高的造诣，对用法足够熟练。

能看到这里的，都是帅哥靓妹。以上是本次MySQL优化篇（上部分）全部内容，希望能对你的工作与学习有所帮助。感觉写的好，就拿出你的一键三连。如果感觉总结的不到位，也希望能留下您宝贵的意见，我会在文章中定期进行调整优化。好记性不如烂笔头，多实践多积累。你会发现，自己的知识宝库越来越丰富。原创不易，转载也请标明出处和作者，尊重原创。