学习篇-mysql-优化-必要性

一、mysql-优化-必要性

• 为什么要进行数据库优化
  • 数据库连接timeout产生页面5xx错误
  • 慢查询造成页面无法加载
  • 阻塞造成数据无法提交
• 增加数据库的稳定性
  • 很多数据库问题都是由于低效的查询引起的【比如全表扫描】
  • 随着时间的推移，系统变得极其臃肿，数据库中的数据量越来越大，数据检索越来越困难，对整个系统带来的资源消耗也就越来越大，系统越发不稳定
• 优化用户体验
  • 流畅的页面访问速度
  • 良好的网站功能体验

二、mysql-优化-数据库优化层面

• 商业需求

  • 不合理需要改造造成资源投入产出比过低
  • 无用功能堆积使系统过度复杂影响整体性能

• 系统架构

  • 数据库中存放的数据都是适合在数据库中存放的吗？

    对于有些开发人员来说，数据库就是一个操作最方便的万能存储中心，希望什么数据都存放在数据库中，不论是需要持久化的数据，还是临时存放的过程数据，不论是普通的纯文本格式的字符数据，还是多媒体的二进制数据，都喜欢全部塞进数据库中。因为对于应用服务器来说，数据库很多时候都是一个集中式的存储环境，不像应用服务器那样可能有很多台；而且数据库有专门的 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 之后，所占用的存储空间就是实际数据长度的两倍。所以，超大文本数据存放在数据库中不仅会带来性能低下的问题，还会带来空间占用的浪费问题

  • 是否合理的利用了应用层Cache机制？

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

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

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

  • 数据层实现都是最精简的吗？

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

    先来分析一个非常简单且经常会遇到类似情况的示例： 比如一个网站系统中，现在要实现每个用户查看各自相册列表（假设每个列表显示 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优化的目的之一就是减少中间结果集，降低物理IO

• 数据库表结构优化

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

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

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

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

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

• 系统配置优化

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

• 硬件配置优化

  • 数据库主机的IO性能是需要最优先考虑的一个因素
  • 数据库主机和普通的应用程序服务器相比，资源要相对集中很多，单台主机上所需要进行的计算量自然也就比较多，所以数据库主机的CPU处理能力也是一个重要的因素。
  • 数据库主机网络设备（一般指网卡等）的性能也可能会成为系统的瓶颈。