3.3 数据库优化总结

参考资料：oracle数据库优化：https://blog.csdn.net/weixin_39106371/article/details/82117148、

详细的SQL优化方案：https://blog.csdn.net/jianzhang11/article/details/102867120?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-2.channel_param

 模糊查询原文链接：https://blog.csdn.net/Weixiaohuai/article/details/83513957

 

  1. 性能优化是什么

    1. 就是发挥机器本来的性能所以首先保证集群的高可用，然后在做具体的性能优化
      1. 编写的新应用上线前在性能上无法满足需求，这个时候需要对系统进行性能调优
      2. 应用系统在线上运行后随着系统数据量的不断增长、访问量的不断上升，系统的响应速度通常越来越慢，不满足业务需要，这个时候也需要对系统进行性能调优

  2. 外部手段

    1. 缓存机制
      1. 使用Redis缓存数据库内容，减少数据库访问压力
      2. 使用Redis集群
      3. 使用Redis读写分离
      4. 使用Redis的哨兵监听模式，选举策略
    2. Nginx服务器
      1. 反向代理（隐藏IP）
      2. 负载均衡，搭建集群
      3. 使用CDN分发加速
      4. 网站动静分离
    3. 项目优化

  3. 数据库优化

    1. 数据库优化系统执行慢的SQL

      1. 慢查询，通过MYSQL命令可以观察，优化慢SQL
      2. 如果是缓存可以通过slowlog 查看ID，去日志查询对应的慢SQL

    2. 数据库性能优化相关

      1. 数据库访问优化法则
        1. 减少数据访问（减少磁盘访问）
          1. 数据块和rowid
            1. 数据块是什么
              1. 数据块是数据库中数据在磁盘中存储的最小单位，也是一次IO访问的最小单位，一个数据块通常可以存储多条记录，数据块大小是DBA在创建数据库或表空间时指定，可指定为2K、4K、8K、16K或32K字节。下图是一个Oracle数据库典型的物理结构，一个数据库可以包括多个数据文件，一个数据文件内又包含多个数据块；
            2. rowid是什么
              1. ROWID是每条记录在数据库中的唯一标识，通过ROWID可以直接定位记录到对应的文件号及数据块位置。ROWID内容包括文件号、对像号、数据块号、记录槽号，如下图所示：
          2. 索引的类型
            1. ORacle的索引类型
              1. 唯一索引
                1. 何时创建：当某列任意两行的值都不相同
                2. 当建立Primary Key(主键)或者Unique constraint(唯一约束)时，唯一索引将被自动建立
                3. 语法：CREATE UNIQUE INDEX index ON table (column);
              2. 组合索引
                1. 何时创建：当两个或多个列经常一起出现在where条件中时，则在这些列上同时创建组合索引
                2. 2组合索引中列的顺序是任意的，也无需相邻。但是建议将最频繁访问的列放在列表的最前面
                3.  演示(组合列，单独列)
              3. 位图索引
                1. 何时创建：列中有非常多的重复的值时候。例如某列保存了 “性别”信息。
              4. 分区索引
              5. 基于函数的索引
              6. 反向键索引
            2. Mysql的两种存储引擎的索引实现：MyISAM索引和InnoDB索引
              1. MyIsam索引的数据文件和索引文件是分开存储的。MyISAM使用B+树构建索引树时，叶子节点中存储的键值为索引列的值，数据为索引所在行的磁盘地址。
                1. 主键索引: 数据列不允许重复，不允许为NULL，一个表只能有一个主键。
                2. 辅助索引
              2. InnoDB索引：每个InnoDB表都有一个聚簇索引 ，聚簇索引使用B+树构建，叶子节点存储的数据是整行记录。一般情况下，聚簇索引等同于主键索引，当一个表没有创建主键索引时，InnoDB会自动创建一个ROWID字段来构建聚簇索引。1.先主键2.找非空的唯一3.InnoDB 会使用一个6 字节长整型的隐式字段 ROWID字段构建聚簇索引
                1. 主键索引: 数据列不允许重复，不允许为NULL，一个表只能有一个主键。
                2. 辅助索引：除聚簇索引之外的所有索引都称为辅助索引，InnoDB的辅助索引只会存储主键值而非磁盘地址。
                3. 组合索引：最左前缀匹配原则和联合索引的索引存储结构和检索方式是有关系的。最底层的叶子节点按照第一列a列从左到右递增排列，但是b列和c列是无序的，b列只有在a列值相等的情况下小范围内递增有序，而c列只能在a，b两列相等的情况下小范围内递增有序。
                  1. 组合索引的最左前缀匹配原则：使用组合索引查询时，mysql会一直向右匹配直至遇到范围查询(>、<、between、like)就停止匹配。
                4. 覆盖索引：覆盖索引并不是说是索引结构，覆盖索引是一种很常用的优化手段。因为在使用辅助索引的时候，我们只可以拿到主键值，相当于获取数据还需要再根据主键查询主键索引再获取到数据。但是试想下这么一种情况，在上面abc_innodb表中的组合索引查询时，如果我只需要abc字段的，那是不是意味着我们查询到组合索引的叶子节点就可以直接返回了，而不需要回表。这种情况就是覆盖索引。
                5. 联合索引：在建立索引的时候，尽量在多个单列索引上判断下是否可以使用联合索引。联合索引的使用不仅可以节省空间，还可以更容易的使用到索引覆盖。
                  1. 联合索引的使用
                    1. 考虑当前是否已经存在多个可以合并的单列索引，如果有，那么将当前多个单列索引创建为一个联合索引。
                    2. 当前索引存在频繁使用作为返回字段的列，这个时候就可以考虑当前列是否可以加入到当前已经存在索引上，使其查询语句可以使用到覆盖索引。
                  2. 联合索引的创建原则，在创建联合索引的时候因该把频繁使用的列、区分度高的列放在前面，频繁使用代表索引利用率高，区分度高代表筛选粒度大，这些都是在索引创建的需要考虑到的优化场景，也可以在常需要作为查询返回的字段上增加到联合索引中，如果在联合索引上增加一个字段而使用到了覆盖索引，那我建议这种情况下使用联合索引。
                6. 普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值。
                7. 唯一索引: 数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。
                8. 全文索引： 是目前搜索引擎使用的一种关键技术。
                9. 空间索引：MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型。MySQL在空间索引这方面遵循OpenGIS几何数据模型规则。
                10. 前缀索引：在文本类型如CHAR,VARCHAR,TEXT类列上创建索引时，可以指定索引列的长度，但是数值类型不能指定。
                11. 其他（按照索引列数量分类）
                  1. 单列索引
                  2. 组合索引：组合索引的使用，需要遵循最左前缀匹配原则（最左匹配原则）。一般情况下在条件允许的情况下使用组合索引替代多个单列索引使用。
          3. 验证索引的命中
            1. MYSQL
              1. 最左匹配原则
                1. 先定位该sql的查询条件，有哪些，那些是等值的，那些是范围的条件。
                2. 等值的条件去命中索引最左边的一个字段，然后依次从左往右命中，范围的放在最后。
                3. = 和 in 可以乱序, 比如 a=1 and b=2 and c=3 建立(a,b,c)索引可以任意顺序, mysql 的查询优化器会帮你优化成索引可以识别的形式.
              2. 索引验证
                1. 字段：EXPLAIN
                2. Oracle的索引命中
                  1. 在运行tool-->explain plan-Explain plan Window视图中
                  2. 使用Explain plan for select t.*, t.rowid from tb_user t;
                  3. select * from table(DBMS_XPLAN.display);
                    1. 如果现实
            2. 索引的优缺点
              1. 优点
                1. 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
                2. 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
              2. 缺点
                1. 时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；
                2. 空间方面：索引需要占物理空间。
            1. 索引是满足某种特定查找算法的数据结构，而这些数据结构会以某种方式指向数据，从而实现高效查找数据。
            2. 具体来说 MySQL 中的索引，不同的数据引擎实现有所不同，但目前主流的数据库引擎的索引都是 B+ 树实现的，B+ 树的搜索效率，可以到达二分法的性能，找到数据区域之后就找到了完整的数据结构了，所有索引的性能也是更好的。
            3. 如果我们把一个表的内容认为是一本字典，那索引就相当于字典的目录，如下图所示：
              1. 图中是一个字典按部首+笔划数的目录，相当于给字典建了一个按部首+笔划的组合索引。
              2. 一个表中可以建多个索引，就如一本字典可以建多个目录一样（按拼音、笔划、部首等等）。
              3. 一个索引也可以由多个字段组成，称为组合索引，如上图就是一个按部首+笔划的组合目录。
              4. 叶子节点内容：索引字段内容+表记录ROWID
            4. B树和B+树的区别
              1. 如图所示，区别有以下两点：
                1. 1. B+树中只有叶子节点会带有指向记录的指针（ROWID），而B树则所有节点都带有，在内部节点出现的索引项不会再出现在叶子节点中。
                2. 2. B+树中所有叶子节点都是通过指针连接在一起，而B树不会。
              2. B+树的优点：
                1. 1. 非叶子节点不会带上ROWID，这样，一个块中可以容纳更多的索引项，一是可以降低树的高度。二是一个内部节点可以定位更多的叶子节点。
                2. 2. 叶子节点之间通过指针来连接，范围扫描将十分简单，而对于B树来说，则需要在叶子节点和内部节点不停的往返移动。
              3. B树的优点：
                1. 对于在内部节点的数据，可直接得到，不必根据叶子节点来定位。
          5. 索引创建 create index 索引名 on 表名（列名）
            1. 最左前缀匹配原则，合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询
            2. 主键，外键，有特殊意义适合当索引
            3. 较频繁作为查询条件的字段才去创建索引
            4. 首先应考虑在 where 及 order by 涉及的列上建立索引
            5. 定义有外键的数据列一定要建立索引
            6. 更新频繁字段不适合创建索引
            7. 若是不能有效区分数据的列不适合做索引列、
            8. 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引
          6. 使用索引
            1. INDEX_COLUMN <= ?
            2. INDEX_COLUMN between ? and ?
            3. INDEX_COLUMN in (?,?,...,?)
            4. INDEX_COLUMN like ?||'%'（后导模糊查询）
            5. T1. INDEX_COLUMN=T2. COLUMN1（两个表通过索引字段关联）
          7. 不会使用索引，避免全表扫描
            1. 尽量避免在 where 子句中使用 != 或 <> 操作符以及is null、
            2. 不用or 来连接条件、
            3. in和 not in 也要慎用
            4. 经过普通运算或函数运算后的索引字段不能使用索引
            5. 含前导模糊查询的Like语法不能使用索引
            6. B-TREE索引里不保存字段为NULL值记录，因此IS NULL不能使用索引
        2.  返回更少数据（减少网络传输或磁盘访问）
          1. 数据分页处理
            1. 客户端(应用程序或浏览器)分页
              1. 优点：编码简单，减少客户端与应用服务器网络交互次数
              2. 缺点：首次交互时间长，占用客户端内存
            2. 应用服务器分页
              1. 优点：编码简单，只需要一次SQL交互，总数据与分页数据差不多时性能较好。
              2. 缺点：总数据量较多时性能较差。
            3. 数据库SQL分页
              1. 优点：性能好
              2. 缺点：编码复杂，各种数据库语法不同，需要两次SQL交互。
              3. 采用rownum来进行分页两种实现方式
                1. 直接通过rownum分页：
                  1. 数据访问开销=索引IO+索引全部记录结果对应的表数据IO
                2. 采用rowid分页语法
                  1. 优化原理是通过纯索引找出分页记录的ROWID，再通过ROWID回表返回数据，要求内层查询和排序字段全在索引里。
                  2. 数据访问开销=索引IO+索引分页结果对应的表数据IO
          2. 只返回需要的字段
            1. 调整前：select * from product where company_id=?;
            2. 调整后：select id,name from product where company_id=?;
        3. 减少交互次数（减少网络传输）
        4. 减少服务器CPU开销（减少CPU及内存开销）
        5. 利用更多资源（增加资源）
      2. 数据库优化架构方向
        1. 调整数据库结构的设计：需要考虑是否使用分区功能、是否建立索引等
        2. 调整数据库的SQL语句
        3. 调整服务器的内存分配
        4. 调整硬盘I/O
        5. 调整操作系统参
      3. 数据库优化总结
        1. 外部手段
          1. 使用Redis缓存
          2. 主从复制，
          3. 读写分离
        2. 数据库手段
          1. 分区分表
          2. 根据数据库访问优化法则第一个法则：减少数据访问（减少磁盘访问）
            1. 使用索引
              1. 主键，外键，有特殊意义适合当索引
              2. 较频繁作为查询条件的字段才去创建索引
              3. 首先应考虑在 where 及 order by 涉及的列上建立索引
              4. 定义有外键的数据列一定要建立索引
              5. 更新频繁字段不适合创建索引
              6. 若是不能有效区分数据的列不适合做索引列、
              7. 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引
            2. 避免全表扫描
              1. 1. IN操作符：在业务密集的SQL当中尽量不采用IN操作符，用EXISTS 方案代替。用EXISTS替代IN、用NOT EXISTS替代NOT IN：用EXISTS替换DISTINCT：
              2. 2. 采用UNION ALL操作符替代UNION，因为UNION ALL操作只是简单的将两个结果合并后就返回。用UNION替换OR (适用于索引列)
              3. 3. ORDER BY语句决定了Oracle如何将返回的查询结果排序它们会降低性能。解决这个问题的办法就是重写order by语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在order by子句中使用表达式。
              4. 尽量避免在 where 子句中使用 != 或 <> 操作符以及is null、
              5. 不用or 来连接条件、
              6. 经过普通运算或函数运算后的索引字段不能使用索引
              7. 含前导模糊查询的Like语法不能使用索引
              8. B-TREE索引里不保存字段为NULL值记录，因此IS NULL不能使用索引
          3.  根据数据库访问优化法则第二个法则：返回更少数据（减少网络传输或磁盘访问）
            1. 1. 数据分页处理
            2. 1. 只返回需要的字段
          4. 根据数据库访问优化法则第三个法则：减少交互次数（减少网络传输）
          5. 根据数据库访问优化法则第四个法则：减少服务器CPU开销（减少CPU及内存开销）
          6. 根据数据库访问优化法则第五个法则：利用更多资源（增加资源）
          7. 其它优化方法
            1. 增加中间表
            2. 拆分字段多的表
            3. SQL书写的影响
              1.  同一功能同一性能不同写法SQL的影响。
              2.  WHERE后面的条件顺序影响
              3. 查询表顺序的影响
            4. 用TRUNCATE替代DELETE：
            5. 删除重复记录：
              1. 最高效的删除重复记录方法 ( 因为使用了ROWID)例子：
              2.  DELETE  FROM  EMP E  WHERE  E.ROWID > (SELECT MIN(X.ROWID) FROM  EMP X  WHERE  X.EMP_NO = E.EMP_NO)。 

    3. 主从复制

    4. MYSQL其它优化方案

      1. 表的设计合理化(符合3NF)
      2. 添加适当索引(index) [四种: 普通索引、主键索引、唯一索引unique、全文索引]
      3. SQL语句优化
      4. 分表技术(水平分割、垂直分割)
      5. 读写[写: update/delete/add]分离
      6. 存储过程 [模块化编程，可以提高速度]
      7. 对mysql配置优化 [配置最大并发数my.ini, 调整缓存大小 ]
      8. mysql服务器硬件升级
      9. 定时的去清除不需要的数据,定时进行碎片整理(MyISAM)

 

posted @ 2020-09-28 22:18  Smileing  阅读(109)  评论(0)  编辑  收藏  举报