精华内容
下载资源
问答
  • 数据库面试题索引sql优化.pdf+数据库SQL优化总结之百万级数据库优化.pdf 附赠Oracle高性能sql优化
  • 百万级数据库优化方案
  • db2数据库优化技巧,个人总结版
  • 易语言EDB数据库优化源码,EDB数据库优化
  • 数据库优化资源

    2018-02-28 10:20:57
    数据库访问性能优化,本文只是面对数据库应用开发的程序员,不适合专业DBA,DBA在数据库性能优化方面需要了解更多的知识;
  • 数据库优化方案

    2018-05-07 08:48:18
    简单描述数据库优化方案,以及数据库一些常用的操作,包括一些简单的查询语句,函数使用等等
  • 德哥的Postgresql数据库优化的培训视频,从Postgtesql的安装,配置,到数据库配置文件postgresql.conf的讲解,执行计划的查看,非常详细。
  • Oracle数据库优化

    2018-03-01 15:21:15
    Oracle数据库优化方法,优化数据库提高数据库性能,提高代码执行性能
  • 数据库优化有很多可以讲,按照支撑的数据量来分可以分为两个阶段:单机数据库和分库分表,前者一般可以支撑500W或者10G以内的数据,超过这个值则需要考虑分库分表。另外,一般大企业面试往往会从单机数据库问起,...
  • 智慧IT Oracle数据库优化技术详解 技术创新变革未来 01 基于命中率的优化方法 目录 02 基于方法论 OWI的优化方法 Contents 03 详解ORACLE优化工具 01 传统数据库的分类 DB-Engines 发布了 2018 年 4 月份的数据库...
  • ORACLE数据库优化基础培训,涵盖了基础操作和常见的问题。 数据库培训内容: 1、Oracle之SQL语句性能优化 2、数据库常用管理命令 3、数据库常见问题处理
  • 金仓数据库优化

    2017-12-18 13:49:40
    该文档描述了金仓数据库性能瓶颈分析,问题解决,优化建议等等
  • 面试不再尬聊的Mysql数据库优化方案

    万次阅读 多人点赞 2020-03-23 11:41:10
    数据库优化是一个老生常谈的问题,刚入门的小白或者工作N年的光头对这个问题应该都不陌生,你要面试一个中高级工程师那么他就想"哥俩好"一样那么粘,面试官肯定会问这个问题,这篇文章我们就和它哥俩好!而且这个...

    点赞多大胆,就有多大产!有支持才有动力!将技术分享给每一个技术使用者和爱好者!

    干货满满,摆好姿势,点赞发车!

     前言

    数据库优化是一个老生常谈的问题,刚入门的小白或者工作N年的光头对这个问题应该都不陌生,你要面试一个中高级工程师那么他就想"哥俩好"一样那么粘,面试官肯定会问这个问题,这篇文章我们就和它哥俩好!而且这个问题就是一个送分题,数据库的优化方案基本就是那些,答案也都是固定的,大家只要好好准备这个问题就不会住你,可以在面试中安排面试官,不然就被面试官安排!话不多说下边就针对数据库优化展开讲!

    相关文章

    《MySQL索引从零上手使用》

    《MySQL索引数据结构,看看什么是B+树吧》

    《基于MyCat实现MySQL读写分离》

    《基于MyCat实现MySQL数据拆分》

    面试开始

    小伙子看你简历上写了Mysql,数据库优化了解吗?

    摸摸头之后笑着说数据库优化不是很了解嘿嘿~~~,这时和蔼的面试官头上出现了一抹红!

    如果这时你正好想到了我这篇文章,那么你就会说数据库优化方面我还是很有研究的,请您听我慢慢道来......

    首先

    面试官我想解释一下为什么做数据库优化(这个你心里知道就好了,面试的时候就不要说了)

    • 系统的数据都从数据库上来,数据库的吞吐量和速度一定程度决定系统的并发和响应速度
    • 系统运行与数据量成正比,数据读处理尤其是查询自然就慢
    • Mysql数据库的数据最终在磁盘上持久化存储,读写不如Redis等这些内存数据库

    其次

    面试官大人我想说一下数据库优化一般从以下几个方面来:

    • 数据库设计:数据表设计遵循三范式,使用合适的数据类型,使用合适的存储引擎
    • 适当创建索引
    • 数据库扩展:数据库的分表分库,读写分离等
    • SQL语句优化等

    接下来我们一一说明解释

    数据库设计

    数据库设计3范式

    数据库设计范式如果要满足N范式必须要先满足N-1范式

    第一范式1NF:字段原子性

    第一范式简单的说就是表中的字段是最小不可再分的,我们下边举个例子,我们看到以下一张用户表。里边的字段是不可再分的,这样就符合第一范式的原子性,可能有些朋友会疑问,这个地址还可以分成省份、城市、区/县三个字段,是的!如果是一个电商项目它需要再分,那么就不符合第一范式,所以具体还是看项目的需求,没有固定标准,在项目需求中它的设计已不可再分那么就符合第一范式!

    第二范式2NF: 消除对主键的部分依赖

    2NF的使用是需要满足1NF为前提,在表中添加一个业务字段,而主键不用来做业务处理,比如我们的商品表有商品id,商品id为商品的主键,但是需要创建一个商品编号列来专门处理业务,因为id太敏感,我们处理业务都是用商品编号来处理,比如展示商品时展示编号等等!

    第三范式3NF:在2NF的基础上添加外键

    3NF的使用必须满足2NF,要求表中的每一列只与主键直接相关而不是间接相关,(表中的每一列只能依赖于主键),比如下面的例子,订单表中有客户相关信息,在分离出客户表之后,订单表中只需要有一个用户id即可(外键),而不能有其他的客户信息。因为其他的客户信息直接关联于用户id,而不是直接与订单id直接相关。如下图所示:

    分离之后:

    三大范式只是一般设计数据库的基本理念,可以建立冗余较小、结构合理的数据库。如果有特殊情况,当然要特殊对待,数据库设计最重要的是看需求跟性能,需求>性能>表结构。所以不能一味的去追求范式建立数据库!需求才是粑粑

    数据类型

    尽量使用可以正确存储数据的最小数据类型

    更小的数据类型意味着更快,占用更少的磁盘,内存、缓存和处理时间

    尽量使用整型表示字符串

    因为字符集和校对规则,使处理字符比整型更复杂,比如:我们使用数据库内置的datetime类型存储时间而不是字符类型,我们使用整型存储ip而不是直接将ip字符串存到数据库中

    尽可能使用not null

    这个值是很烦人的,建字段时请尽量指定是否非空,NULL使得索引,统计,比较都变得更复杂,而且索引尽量不要创建到可以为null的字段上

    字符串类型

    VARCHAR是可变长字符串

    比定长字符串(CHAR)更节省空间,仅使用必要的空间另外VARCHAR需要额外字节记录字符串长度(不同情况需要字节数不同)

    CHAR类型是定长字符串

    开发中基本很少用(一些公司甚至基本上不考虑这种类型了),注意:字符串长度定义不是字节数,是字符数

    日期和时间类型

    datetime

    使用8字节存储空间,保存从1001年到9999年的秒数。与时区无关,默认情况下,Mysql以一种可排序的格式显示它的值,例如:"2018-10-14 22:30:08"

    timestamp

    只使用4字节存储,保存1970年1月1日午夜以来的秒数,依赖于系统时区,和UNIX时间戳相同,转换函数分别为FROM_UNIXTIME()和UNIX_TIMESTAMP(),可以设置根据当前时间戳更新,比如我们熟悉的update_time字段

    整数类型

    UNSIGNED

    属性表示不允许负值,可以使得正数的上限提高一倍,比如tinyint+unsigned可以使原本的-128~127的范围变为0~255

    tinyint

    我们一般用它存储状态值而不要用int,如果是Boolean类型,那么tinyint(1)当值为1和0时,查询结果自动转为true和false,条件参数相应的也可以直接传入true和false即可

    INT(11)

    不会限制值的范围,只是规定了一些客户端工具用来显示的字符的个数,所以对于存储和计算来说INT(11)和INT(1)相同

    IP地址

    实际上是32位无符号整数,用INT存储,Mysql提供转换函数为INET_ATON()和INET_NTOA()

    小数

    decimal不会损失精度,存储空间会随数据的增大而增大。double占用固定空间,较大数的存储会损失精度,通常存金额用decimal(11,2),这表示整数部分和小数部分分别为9位和2位注意!,当然可以根据具体的金额大小选择长度,注意这时候对应的java中用BigDecimal类来处理运算时要仔细,因为加减法和比较跟平常不一样

    存储引擎

    介绍

    数据库存储引擎是数据库底层组件,数据库管理系统使用数据引擎进行创建、查询、更新和删除数据操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能,使用不同的存储引擎还可以获得特定的功能。我们可以通过SHOW ENGINES;

    InnoDB存储引擎

    InnoDB越做越好从MySQL5.5版本之后,MySQL的默认内置存储引擎已经是InnoDB,主要特点有

    1. 容灾恢复性比较好
    2. 支持事务,默认事务隔离界别为可重复读
    3. 使用的锁粒度为行锁,可以支持更高的并发
    4. 支持外键
    5. 配合一些热备工具可以支持在线热备份
    6. 在InnoDB中存在着缓冲管理,通过缓冲池,将索引和数据全部缓存起来,加快查询的速度
    7. 对于InnoDB类型的表,其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。根据主键进行排序,数据和索引放在一块,都位于B+数的叶子节点上

    MyISAM存储引擎

    在5.5版本之前,MyISAM是MySQL的默认存储引擎,该存储引擎并发性差,不支持事务,所以使用场景比较少,主要特点有

    1. 不支持事务
    2. 不支持外键,如果强行增加外键,不会提示错误,只是外键不其作用
    3. 对数据的查询缓存只会缓存索引,不会像InnoDB一样缓存数据,而且是利用操作系统本身的缓存
    4. 默认的锁粒度为表级锁,所以并发度很差,加锁快,锁冲突较少,所以不太容易发生死锁
    5. 支持全文索引(MySQL5.6之后,InnoDB存储引擎也对全文索引做了支持),但是MySQL的全文索引基本不会使用,对于全文索引,现在有其他成熟的解决方案,比如:ElasticSearch,Solr,Sphinx等
    6. 数据库所在主机如果宕机,MyISAM的数据文件容易损坏,而且难恢复

    MEMORY存储引擎

    将数据存在内存中,和市场上的Redis,memcached等思想类似,为了提高数据的访问速度,主要特点有

    1. 支持的数据类型有限制,不支持TEXT和BLOB类型,对于字符串类型的数据,只支持固定长度的行,VARCHAR会被自动存储为CHAR类型
    2. 支持的锁粒度为表级锁。所以,在访问量比较大时,表级锁会成为MEMORY存储引擎的瓶颈
    3. 由于数据是存放在内存中,所以在服务器重启之后,所有数据都会丢失
    4. 查询的时候,如果有用到临时表,而且临时表中有BLOB,TEXT类型的字段,那么这个临时表就会转化为MyISAM类型的表,性能会急剧降低

    ARCHIVE存储引擎

    ARCHIVE存储引擎适合的场景有限,由于其支持压缩,故主要是用来做日志,流水等数据的归档,主要特点有

    1. 支持Zlib压缩,数据在插入表之前,会先被压缩
    2. 仅支持SELECT和INSERT操作,存入的数据就只能查询,不能做修改和删除;
    3. 只支持自增键上的索引,不支持其他索引

    CSV存储引擎

    数据中转试用,主要特点有

    1. 其数据格式为.csv格式的文本,可以直接编辑保存
    2. 导入导出比较方便,可以将某个表中的数据直接导出为csv,试用Excel办公软件打开

    选择依据

    如果没有特殊需求默认使用InnoDB引擎即可

    MyISAM:以读写插入为主的应用程序,比如博客系统、新闻门户网站。

    Innodb:更新(删除)操作频率也高,或者要保证数据的完整性;并发量高,支持事务和外键保证数据完整性。比如OA自动化办公系统

    索引

    已为客官备好,轻点哦《这小伙子把MySQL索引使用讲的真明白,真好,快来戳他》

    索引数据结构在这在这《搞懂MySQL数据库索引数据结构这一篇足够从此不再萌萌哒》

    MySQL读写分离

    点一下就会《看了这篇文章觉得MySQL读写分离这么简单》

    MySQL分表分库

    一样点一下就成《手把手基于Mycat实现MySQL数据拆分》

    SQL优化

    这里列举出来一些用过的,看到的欢迎大家评论区补充讨论

    1、查询尽量避免全表扫描,首先考虑在where、order by字段上添加索引

    2、避免在where字段上使用NULL值,所以在设计表时尽量使用NOT NULL约束,有些数据会默认为NULL,可以设置默认值为0或者-1

    3、避免在where子句中使用!=或<>操作符,Mysql只对<,<=,=,>,>=,BETWEEN,IN,以及某些时候的LIKE使用索引

    4、避免在where中使用OR来连接条件,否则可能导致引擎放弃索引来执行全表扫描,可以使用UNION进行合并查询

          select id from t where num = 30 union select id from t where num = 40;

    5、尽量避免在where子句中进行函数或者表达式操作

    6、最好不要使用select * from t,用具体的字段列表代替"*",不要返回用不到的任何字段

    7、in 和 not in 也要慎用,否则会导致全表扫描,如

    select id from t where num IN(1,2,3)如果是连续的值建议使用between and,select id from t where between 1 and 3;

    8、select id from t where col like %a%;模糊查询左侧有%会导致全表检索,如果需要全文检索可以使用全文搜索引擎比如es,slor

    9、limit offset rows关于分页查询,尽量保证不要出现大的offset,比如limit 10000,10相当于对已查询出来的行数弃掉前10000行后再取10行,完全可以加一些条件过滤一下(完成筛选),而不应该使用limit跳过已查询到的数据。这是一个==offset做无用功==的问题。对应实际工程中,要避免出现大页码的情况,尽量引导用户做条件过滤

    关注本系列文章的朋友应该发现,这里的未完待续已经消失,我们的MySQL优化就告一段落,主要从数据库设计、索引、数据库拆分和SQL语句上进行优化,更多优化方案希望大家通过评论区留言!


     路漫漫其修远兮,吾将上下而求索

    展开全文
  • 数据库优化

    2012-04-05 16:17:55
    高并发下MySQL数据库优化 高并发下MySQL数据库优化 高并发下MySQL数据库优化
  • mysql数据库优化方案

    2018-04-16 17:09:34
    简单描述数据库优化方案,以及数据库一些常用的操作,包括一些简单的查询语句,函数使用等等
  • orcal数据库优化

    2017-10-29 08:00:28
    数据库优化,提供性能。通过不同方法,提高执行速度等等。
  • 数据库高手给出的从九个不同方面介绍ORACLE数据库优化设计方案 一、数据库优化自由结构OFA(Optimal flexible Architecture) 二、充分利用系统全局区域SGA(SYSTEM GLOBAL AREA) 三、规范与反规范设计数据库 四、...
  • MySQL数据库优化SQL篇.ppt,适用于企业级项目开发
  • 数据库优化 - SQL优化

    万次阅读 多人点赞 2019-11-01 21:00:00
    以实际SQL入手,带你一步一步走上SQL优化之路!
    前面一篇文章从实例的角度进行数据库优化,通过配置一些参数让数据库性能达到最优。但是一些“不好”的SQL也会导致数据库查询变慢,影响业务流程。本文从SQL角度进行数据库优化,提升SQL运行效率。

    判断问题SQL

    判断SQL是否有问题时可以通过两个表象进行判断:

    • 系统级别表象
      • CPU消耗严重
      • IO等待严重
      • 页面响应时间过长
      • 应用的日志出现超时等错误

    可以使用sar命令,top命令查看当前系统状态。

    也可以通过Prometheus、Grafana等监控工具观察系统状态。(感兴趣的可以翻看我之前的文章)
    640?wx_fmt=png

    • SQL语句表象
      • 冗长
      • 执行时间过长
      • 从全表扫描获取数据
      • 执行计划中的rows、cost很大

    冗长的SQL都好理解,一段SQL太长阅读性肯定会差,而且出现问题的频率肯定会更高。更进一步判断SQL问题就得从执行计划入手,如下所示:640?wx_fmt=png

    执行计划告诉我们本次查询走了全表扫描Type=ALL,rows很大(9950400)基本可以判断这是一段"有味道"的SQL。

    获取问题SQL

    不同数据库有不同的获取方法,以下为目前主流数据库的慢查询SQL获取工具

    • MySQL

      • 慢查询日志
      • 测试工具loadrunner
      • Percona公司的ptquery等工具
    • Oracle

      • AWR报告
      • 测试工具loadrunner等
      • 相关内部视图如v$、$session_wait等
      • GRID CONTROL监控工具
    • 达梦数据库

      • AWR报告
      • 测试工具loadrunner等
      • 达梦性能监控工具(dem)
      • 相关内部视图如v$、$session_wait等

    SQL编写技巧

    SQL编写有以下几个通用的技巧:

    • 合理使用索引

    索引少了查询慢;索引多了占用空间大,执行增删改语句的时候需要动态维护索引,影响性能 选择率高(重复值少)且被where频繁引用需要建立B树索引;

    一般join列需要建立索引;复杂文档类型查询采用全文索引效率更好;索引的建立要在查询和DML性能之间取得平衡;复合索引创建时要注意基于非前导列查询的情况

    • 使用UNION ALL替代UNION

    UNION ALL的执行效率比UNION高,UNION执行时需要排重;UNION需要对数据进行排序

    • 避免select * 写法

    执行SQL时优化器需要将 * 转成具体的列;每次查询都要回表,不能走覆盖索引。

    • JOIN字段建议建立索引

    一般JOIN字段都提前加上索引

    • 避免复杂SQL语句

    提升可阅读性;避免慢查询的概率;可以转换成多个短查询,用业务端处理

    • 避免where 1=1写法

    • 避免order by rand()类似写法

    RAND()导致数据列被多次扫描

    SQL优化

    执行计划

    完成SQL优化一定要先读执行计划,执行计划会告诉你哪些地方效率低,哪里可以需要优化。我们以MYSQL为例,看看执行计划是什么。(每个数据库的执行计划都不一样,需要自行了解)explain sql640?wx_fmt=png

    字段解释
    id每个被独立执行的操作标识,标识对象被操作的顺序,id值越大,先被执行,如果相同,执行顺序从上到下
    select_type查询中每个select 字句的类型
    table被操作的对象名称,通常是表名,但有其他格式
    partitions匹配的分区信息(对于非分区表值为NULL)
    type连接操作的类型
    possible_keys可能用到的索引
    key优化器实际使用的索引(最重要的列) 从最好到最差的连接类型为consteq_regrefrangeindexALL。当出现ALL时表示当前SQL出现了“坏味道”
    key_len被优化器选定的索引键长度,单位是字节
    ref表示本行被操作对象的参照对象,无参照对象为NULL
    rows查询执行所扫描的元组个数(对于innodb,此值为估计值)
    filtered条件表上数据被过滤的元组个数百分比
    extra执行计划的重要补充信息,当此列出现Using filesort , Using temporary 字样时就要小心了,很可能SQL语句需要优化

    接下来我们用一段实际优化案例来说明SQL优化的过程及优化技巧。

    优化案例

    • 表结构

      CREATE TABLE `a`
      (
          `id`          int(11) NOT NULLAUTO_INCREMENT,
          `seller_id`   bigint(20)                                       DEFAULT NULL,
          `seller_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
          `gmt_create`  varchar(30)                                      DEFAULT NULL,
          PRIMARY KEY (`id`)
      );
      CREATE TABLE `b`
      (
          `id`          int(11) NOT NULLAUTO_INCREMENT,
          `seller_name` varchar(100) DEFAULT NULL,
          `user_id`     varchar(50)  DEFAULT NULL,
          `user_name`   varchar(100) DEFAULT NULL,
          `sales`       bigint(20)   DEFAULT NULL,
          `gmt_create`  varchar(30)  DEFAULT NULL,
          PRIMARY KEY (`id`)
      );
      CREATE TABLE `c`
      (
          `id`         int(11) NOT NULLAUTO_INCREMENT,
          `user_id`    varchar(50)  DEFAULT NULL,
          `order_id`   varchar(100) DEFAULT NULL,
          `state`      bigint(20)   DEFAULT NULL,
          `gmt_create` varchar(30)  DEFAULT NULL,
          PRIMARY KEY (`id`)
      );
      
    • 三张表关联,查询当前用户在当前时间前后10个小时的订单情况,并根据订单创建时间升序排列,具体SQL如下

      select a.seller_id,
             a.seller_name,
             b.user_name,
             c.state
      from a,
           b,
           c
      where a.seller_name = b.seller_name
        and b.user_id = c.user_id
        and c.user_id = 17
        and a.gmt_create
          BETWEEN DATE_ADD(NOW(), INTERVAL – 600 MINUTE)
          AND DATE_ADD(NOW(), INTERVAL 600 MINUTE)
      order by a.gmt_create;
      
    • 查看数据量  

      640?wx_fmt=png

    • 原执行时间
      640?wx_fmt=png

    • 原执行计划
      640?wx_fmt=png

    • 初步优化思路

    1. SQL中 where条件字段类型要跟表结构一致,表中 user_id 为varchar(50)类型,实际SQL用的int类型,存在隐式转换,也未添加索引。将b和c表 user_id 字段改成int类型。
    2. 因存在b表和c表关联,将b和c表 user_id创建索引
    3. 因存在a表和b表关联,将a和b表 seller_name字段创建索引
    4. 利用复合索引消除临时表和排序

    初步优化SQL

    alter table b modify `user_id` int(10) DEFAULT NULL;
    alter table c modify `user_id` int(10) DEFAULT NULL;
    alter table c add index `idx_user_id`(`user_id`);
    alter table b add index `idx_user_id_sell_name`(`user_id`,`seller_name`);
    alter table a add index `idx_sellname_gmt_sellid`(`gmt_create`,`seller_name`,`seller_id`);
    

    查看优化后执行时间

    640?wx_fmt=png

    查看优化后执行计划
    640?wx_fmt=png

    查看warnings信息
    640?wx_fmt=png

    继续优化alter table a modify "gmt_create" datetime DEFAULT NULL;

    查看执行时间

    640?wx_fmt=png

    查看执行计划
    640?wx_fmt=png

    总结

    1. 查看执行计划 explain
    2. 如果有告警信息,查看告警信息 show warnings;
    3. 查看SQL涉及的表结构和索引信息
    4. 根据执行计划,思考可能的优化点
    5. 按照可能的优化点执行表结构变更、增加索引、SQL改写等操作
    6. 查看优化后的执行时间和执行计划
    7. 如果优化效果不明显,重复第四步操作
     

    系列文章

     
     

    温馨提示

    如果你喜欢本文,请关注我的个人公众号!或者关注我的个人博客www.javadaily.cn

    图片

     

     

    展开全文
  • 数据库优化详解

    2020-12-14 18:06:50
    数据库优化从以下几个方面优化: 数据库设计—三大范式、字段、表结构 数据库索引 存储过程 (模块化编程,可以提高速度) 分表分库 (水平分割,垂直分割) 主从复制、读写分离 SQL 调优 对 MySQL 配置优化 (配置最大...
  • progress 数据库优化

    2016-08-01 22:02:00
    progress 数据库优化
  • 数据库优化 - 实例优化

    千次阅读 2019-10-25 10:30:00
    从网上去搜数据库优化基本都是从SQL层次进行优化的,很少有提及到数据库本身的实例优化。就算有也都是基于某个特定数据库的实例优化,本文涵盖目前市面上所有主流数据库的实例优化(Oralce、MySQL、POSTGRES、达梦)...
    从网上去搜数据库优化基本都是从SQL层次进行优化的,很少有提及到数据库本身的实例优化。就算有也都是基于某个特定数据库的实例优化,本文涵盖目前市面上所有主流数据库的实例优化(Oralce、MySQL、POSTGRES、达梦),按照文章的配置能够将你数据库性能用到80%或以上。

    数据库优化方法论

    这部分为理论知识,不感兴趣的同学可以直接跳到后面参数配置部分。

    数据库优化目标

    根据角色的不同,数据库优化分为以下几个目标:

    • 业务角度(关键用户): 减少用户页面响应时间

    • 数据库角度(开发): 减少数据库SQL响应时间

    • 数据库服务器角度(运维): 充分使用数据库服务器物理资源 减少数据库服务器CPU使用率 减少数据库服务器IO使用率 减少数据库服务器内存使用率

    数据库优化指标

    • SQL平均响应时间变短

      • 优化前:数据库平均响应时间500ms

      • 优化目标:数据库平均响应时间200ms

    • 数据库服务器CPU占用率变少

      • 优化前:数据库高峰期CPU使用率70%

      • 优化目标:数据库高峰期CPU使用率50%

    • 数据库服务器IO使用率变低

      • 优化前:数据库IO WAIT为30%

      • 优化目标:数据库IO WAIT低于10%

    数据库优化误区

    在进行数据库优化的时候可能会有以下几个误区:

    • 优化之前一定要深入了解数据库内部原理 优化是有“套路”的,照着这些“套路”你也可以很好的完成数据库优化

    • 不断调整数据库参数就可以最终实现优化 有时候设计不合理怎么调整参数都不行

    • 不断调整操作系统参数就可以最终实现优化 同上

    • 数据库性能由应用、数据库架构决定,与应用开发关系不大 恰恰相反,应用开发的关系很大

    • 必须要做读写分离,必须要弄分库分表 数据量级只有达到一定的比例才有必要做读写分离,分表分库,否则徒增复杂度。一般来说Oracle的单表量级可以达到1亿,MySQL到1000万~2000万

    数据库优化流程

    完整的数据库优化流程如下:

    640?wx_fmt=png

    首先需要尽可能的了解优化问题,收集问题期间系统信息并做好存档。根据当前系统问题表现制定优化目标并与客户沟通目标达成一致;通过一系列工具分析系统问题,制定优化方案,方案评审完成后由各负责人员进行实施。若达到优化目标则编写优化报告,否则需要重新制定优化方案。

    数据库实例优化

    数据库实例优化遵循三句口诀:日志不能小、缓存足够大、连接要够用。

    数据库事务提交后需要将事务对数据页的修改刷( fsync)到磁盘上,才能保证数据的持久性。这个刷盘,是一个随机写,性能较低,如果每次事务提交都要刷盘,会极大影响数据库的性能。数据库在架构设计中都会采用如下两个优化手法:

    • 先将事务写到日志文件RedoLog(WAL),将随机写优化成顺序写

    • 加一层缓存结构Buffer,将每次写优化成批量写

    所以日志跟缓存对数据库实例尤其重要。而连接如果不够用,数据库会直接抛出异常,系统无法访问。

    数据库参数优化

    主流数据库架构都有如下的共同点:

    • 数据缓存

    • SQL解析区

    • 排序内存

    • REDO及UNDO

    • 锁、LATCH、MUTEX

    • 监听及连接

    • 文件读写性能

    接下来我们根据不同的数据库调整参数以使数据库达到最佳性能。

    ORACLE

    参数分类参数名参数值备注
    数据缓存SGA_TAGET、MEMORY_TARGET物理内存70-80%越大越好
    数据缓存DB_CACHE_SIZE物理内存70-80%越大越好
    SQL解析SHARED_POOL_SIZE4-16G不建议设置过大
    监听及连接PROCESSES、SESSIONS、OPEN_CURSORS根据业务需求设置一般为业务预估连接数的120%
    其他SESSION_CACHED_CURSORS大于200软软解析

    MYSQL(INNODB)

    参数分类参数名参数值备注
    数据缓存INNODB_BUFFER_POOL_SIZE物理内存50-80%一般来说越大性能越好
    日志相关Innodb_log_buffer_size16-32M根据运行情况调整
    日志相关sync_binlog1、100、01安全性最好
    监听及连接max_connections根据业务情况调整可以预留一部分值
    文件读写性能innodb_flush_log_at_trx_commit2安全和性能的折中考虑
    其他wait_timeout,interactive_timeout28800避免应用连接定时中断

    POSTGRES

    参数分类参数名参数值备注
    数据缓存SHARED_BUFFERS物理内存10-25% 
    数据缓存CACHE_BUFFER_SIZE物理内存50-60% 
    日志相关wal_buffer8-64M不建议设置过大过小
    监听及连接max_connections根据业务情况调整一般为业务预估连接数的120%
    其他maintenance_work_mem512M或更大 
    其他work_mem8-16M原始配置1M过小
    其他checkpoint_segments32或者更大 

    达梦数据库

    参数分类参数名参数值备注
    数据缓存MEMROY_TARGET、MEMROY_POOL物理内存90% 
    数据缓存BUFFER物理内存60%数据缓存
    数据缓存MAX_BUFFER物理内存70%最大数据缓存
    监听及连接max_sessions根据业务需求设置一般为业务预估连接数的120%

    总结

    数据库的优化手法太多太多,有换磁盘阵列升级硬件,有改写SQL脚本添加索引,还有数据库参数调整优化性能,甚至还可以调整数据库架构。本文从数据库本身参数进行调优,大家根据上面几张表中的参数进行调整基本能达到数据库最佳性能的80%。

     

    640?wx_fmt=jpeg

     

    更多内容请访问:www.javadaily.cn

    展开全文
  • 数据库优化方案整理

    万次阅读 多人点赞 2018-08-29 16:05:16
    数据库优化策略有很多,设计初期,建立好的数据结构对于后期性能优化至关重要。因为数据库结构是系统的基石,基础打不好,使用各种优化策略,也不能达到很完美的效果。 B:数据库优化的几个方面 ​​ 可以看...

    一:优化说明

    A:有数据表明,用户可以承受的最大等待时间为8秒。数据库优化策略有很多,设计初期,建立好的数据结构对于后期性能优化至关重要。因为数据库结构是系统的基石,基础打不好,使用各种优化策略,也不能达到很完美的效果。

    B:数据库优化的几个方面
    这里写图片描述
    ​​
    可以看出来,数据结构、SQL、索引是成本最低,且效果最好的优化手段。

    C:性能优化是无止境的,当性能可以满足需求时即可,不要过度优化。

    二:优化方向

    1. SQL以及索引的优化

    首先要根据需求写出结构良好的SQL,然后根据SQL在表中建立有效的索引。但是如果索引太多,不但会影响写入的效率,对查询也有一定的影响。

    2. 合理的数据库是设计

    根据数据库三范式来进行表结构的设计。设计表结构时,就需要考虑如何设计才能更有效的查询。

    数据库三范式:
    第一范式:数据表中每个字段都必须是不可拆分的最小单元,也就是确保每一列的原子性;
    第二范式:满足一范式后,表中每一列必须有唯一性,都必须依赖于主键;
    第三范式:满足二范式后,表中的每一列只与主键直接相关而不是间接相关(外键也是直接相关),字段没有冗余。

    注意:没有最好的设计,只有最合适的设计,所以不要过分注重理论。三范式可以作为一个基本依据,不要生搬硬套。

    有时候可以根据场景合理地反规范化:
    A:分割表。
    B:保留冗余字段。当两个或多个表在查询中经常需要连接时,可以在其中一个表上增加若干冗余的字段,以 避免表之间的连接过于频繁,一般在冗余列的数据不经常变动的情况下使用。
    C:增加派生列。派生列是由表中的其它多个列的计算所得,增加派生列可以减少统计运算,在数据汇总时可以大大缩短运算时间。

    数据库五大约束:
    A:PRIMARY key:设置主键约束;
    B:UNIQUE:设置唯一性约束,不能有重复值;
    C:DEFAULT 默认值约束
    D:NOT NULL:设置非空约束,该字段不能为空;
    E:FOREIGN key :设置外键约束。

    字段类型选择:
    A:尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT,如果非负则加上UNSIGNED
    B:VARCHAR的长度只分配真正需要的空间
    C:使用枚举或整数代替字符串类型
    D:尽量使用TIMESTAMP而非DATETIME
    E:单表不要有太多字段,建议在20以内
    F:避免使用NULL字段,很难查询优化且占用额外索引空间

    3. 系统配置的优化

    例如:MySQL数据库my.cnf

    4. 硬件优化

    更快的IO、更多的内存。一般来说内存越大,对于数据库的操作越好。但是CPU多就不一定了,因为他并不会用到太多的CPU数量,有很多的查询都是单CPU。另外使用高的IO(SSD、RAID),但是IO并不能减少数据库锁的机制。所以说如果查询缓慢是因为数据库内部的一些锁引起的,那么硬件优化就没有什么意义。

    三:优化方案

    代码优化

    之所以把代码放到第一位,是因为这一点最容易引起技术人员的忽视。很多技术人员拿到一个性能优化的需求以后,言必称缓存、异步、JVM等。实际上,第一步就应该是分析相关的代码,找出相应的瓶颈,再来考虑具体的优化策略。有一些性能问题,完全是由于代码写的不合理,通过直接修改一下代码就能解决问题的,比如for循环次数过多、作了很多无谓的条件判断、相同逻辑重复多次等。

    举个栗子:
    一个update操作,先查询出entity,再执行update,这样无疑多了一次数据库交互。还有一个问题,update语句可能会操作一些无需更新的字段。

    我们可以将表单中涉及到的属性,以及updateTime,updateUser等赋值到entity,直接通过pdateByPrimaryKeySelective,去update特定字段。

    ​​

    定位慢SQL,并优化

    这是最常用、每一个技术人员都应该掌握基本的SQL调优手段(包括方法、工具、辅助系统等)。这里以MySQL为例,最常见的方式是,由自带的慢查询日志或者开源的慢查询系统定位到具体的出问题的SQL,然后使用explain、profile等工具来逐步调优,最后经过测试达到效果后上线。

    SqlServer执行计划:

    通过执行计划,我们能得到哪些信息:
    A:哪些步骤花费的成本比较高
    B:哪些步骤产生的数据量多,数据量的多少用线条的粗细表示,很直观
    C:每一步执行了什么动作

    具体优化手段:

    A:尽量少用(或者不用)sqlserver 自带的函数
    select id from t where substring(name,1,3) = ’abc’
    select id from t where datediff(day,createdate,’2005-11-30′) = 0
    可以这样查询:
    select id from t where name like ‘abc%’
    select id from t where createdate >= ‘2005-11-30’ and createdate < ‘2005-12-1’

    B:连续数值条件,用BETWEEN不用IN:SELECT id FROM t WHERE num BETWEEN 1 AND 5
    C:Update 语句,如果只更改1、2个字段,不要Update全部字段,否则频繁调用会引起明显的性能消耗
    D:尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型
    E:不建议使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段。尽量避免向客户 端返回大数据量,若数据量过大,应该考虑相应需求是否合理
    F:表与表之间通过一个冗余字段来关联,要比直接使用JOIN有更好的性能
    G:select count(*) from table;这样不带任何条件的count会引起全表扫描
    连接池调优
    我们的应用为了实现数据库连接的高效获取、对数据库连接的限流等目的,通常会采用连接池类的方案,即每一个应用节点都管理了一个到各个数据库的连接池。随着业务访问量或者数据量的增长,原有的连接池参数可能不能很好地满足需求,这个时候就需要结合当前使用连接池的原理、具体的连接池监控数据和当前的业务量作一个综合的判断,通过反复的几次调试得到最终的调优参数。

    ​​

    合理使用索引

    索引一般情况下都是高效的。但是由于索引是以空间换时间的一种策略,索引本身在提高查询效率的同时会影响插入、更新、删除的效率,频繁写的表不宜建索引。

    选择合适的索引列,选择在where,group by,order by,on从句中出现的列作为索引项,对于离散度不大的列没有必要创建索引。
    主键已经是索引了,所以primay key 的主键不用再设置unique唯一索引

    索引类型
    主键索引 (PRIMARY KEY)
    唯一索引 (UNIQUE)
    普通索引 (INDEX)
    组合索引 (INDEX)
    全文索引 (FULLTEXT)

    可以应用索引的操作符
    大于等于
    Between
    IN
    LIKE 不以 % 开头

    不能应用索引的操作符
    NOT IN
    LIKE %_ 开头

    如何选择索引字段
    A:字段出现在查询条件中,并且查询条件可以使用索引
    B:通常对数字的索引和检索要比对字符串的索引和检索效率更高
    C:语句执行频率高,一天会有几千次以上
    D:通过字段条件可筛选的记录集很小
    ​​

    无效索引
    A:尽量不要在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描
    B:应尽量避免在 where 子句中使用 != 或 <> 操作符,否则将引擎放弃使用索引而进行全表扫描。
    C:应尽量避免在 where 子句中使用 or 来连接条件,如果一个字段有索引,一个字段没有索引,将导致引擎放弃使用索引而进行全表扫描
    select id from t where num=10 or Name = ‘admin’
    可以这样查询:
    select id from t where num = 10
    union
    select id from t where Name = ‘admin’
    union all 返回所有数据,不管是不是重复。 union会自动压缩,去除重复数据。

    D:不做列运算
    where age + 1 = 10,任何对列的操作都将导致表扫描,它包括数据库教程函数、计算表达式等
    E:查询like,如果是 ‘%aaa’ 不会使用到索引

    分表

    分表方式
    水平分割(按行)、垂直分割(按列)

    分表场景
    A: 根据经验,mysql表数据一般达到百万级别,查询效率就会很低。
    B: 一张表的某些字段值比较大并且很少使用。可以将这些字段隔离成单独一张表,通过外键关联,例如考试成绩,我们通常关注分数,不关注考试详情。

    水平分表策略
    按时间分表:当数据有很强的实效性,例如微博的数据,可以按月分割。
    按区间分表:例如用户表 1到一百万用一张表,一百万到两百万用一张表。
    hash分表:通过一个原始目标id或者是名称按照一定的hash算法计算出数据存储的表名。

    读写分离

    当一台服务器不能满足需求时,采用读写分离【写: update/delete/add】的方式进行集群。
    一台数据库支持最大连接数是有限的,如果用户的并发访问很多,一台服务器无法满足需求,可以集群处理。mysql集群处理技术最常用的就是读写分离。

    主从同步:数据库最终会把数据持久化到磁盘,集群必须确保每个数据库服务器的数据是一致的。从库读主库写,从库从主库上同步数据。
    读写分离:使用负载均衡实现,写操作都往主库上写,读操作往从服务器上读。

    缓存

    缓存分类
    本地缓存:HashMap/ConcurrentHashMap、Ehcache、Guava Cache等
    缓存服务:Redis/Tair/Memcache等

    使用场景
    短时间内相同数据重复查询多次且数据更新不频繁,这个时候可以选择先从缓存查询,查询不到再从数据库加载并回设到缓存的方式。此种场景较适合用单机缓存。
    高并发查询热点数据,后端数据库不堪重负,可以用缓存来扛。

    缓存作用
    减轻数据库的压力,减少访问时间。

    缓存选择
    如果数据量小,并且不会频繁地增长又清空(这会导致频繁地垃圾回收),那么可以选择本地缓存。具体的话,如果需要一些策略的支持(比如缓存满的逐出策略),可以考虑Ehcache;如不需要,可以考虑HashMap;如需要考虑多线程并发的场景,可以考虑ConcurentHashMap。
    其他情况,可以考虑缓存服务。目前从资源的投入度、可运维性、是否能动态扩容以及配套设施来考虑,我们优先考虑Tair。除非目前Tair还不能支持的场合(比如分布式锁、Hash类型的value),我们考虑用Redis。

    缓存穿透
    一般的缓存系统,都是按照key去缓存查询,如果不存在对应的value,就应该去后端系统查找(比
    如DB)。如果key对应的value是一定不存在的,并且对该key并发请求量很大,就会对后端系统造
    成很大的压力。这就叫做缓存穿透。

    对查询结果为空的情况也进行缓存,缓存时间设置短点,或者该key对应的数据insert了之后清理缓存。

    缓存并发
    有时候如果网站并发访问高,一个缓存如果失效,可能出现多个进程同时查询DB,同时设置缓存的情况,
    如果并发确实很大,这也可能造成DB压力过大,还有缓存频繁更新的问题。

    对缓存查询加锁,如果KEY不存在,就加锁,然后查DB入缓存,然后解锁;其他进程如果发现有锁就
    等待,然后等解锁后返回数据或者进入DB查询。

    缓存雪崩(失效)
    当缓存服务器重启或者大量缓存集中在某一个时间段失效,这样在失效的时候,也会给后端系统(比如DB)
    带来很大压力。
    不同的key,设置不同的过期时间,让缓存失效的时间点尽量均匀.

    防止缓存空间不够用
    ① 给缓存服务,选择合适的缓存逐出算法,比如最常见的LRU。
    ② 针对当前设置的容量,设置适当的警戒值,比如10G的缓存,当缓存数据达到8G的时候,就开始发出报警,提前排查问题或者扩容。
    ③ 给一些没有必要长期保存的key,尽量设置过期时间。

    我们看下图,在WebServer(Dao层)和DB之间加一层cache,这层cache一般选取的介质是内存,因为我们都知道存入数据库的数据都具有持久化的特点,那么读写会有磁盘IO的操作,内存的读写速度远比磁盘快得多。(选用存储介质,提高访问速度:内存>>磁盘;减少磁盘IO的操作,减少重复查询,提高吞吐量)

    这里写图片描述

    ​​

    常用开源的缓存工具有:ehcache、memcache、redis。

    ehcache 是一个纯Java的进程内缓存框架,hibernate使用其做二级缓存。同时,ehcache可以通过多播的方式实现集群。本人主要用于本地的缓存,数据库上层的缓存。
    memcache是一套分布式的高速缓存系统,提供key-value这样简单的数据储存,可充分利用CPU多核,无持久化功能。在做web集群中可以用做session共享,页面对象缓存。
    redis高性能的key-value系统,提供丰富的数据类型,单核CPU有抗并发能力,有持久化和主从复制的功能。本人主要使用redis的redis sentinel,根据不同业务分为多组。

    redis注意事项
    A:在增加 key 的时候尽量设置过期时间,不然 Redis Server 的内存使用会达到系统物理内存的最大值,导致 Redis 使用 VM 降低系统性能;
    B:Redis Key 设计时应该尽可能短,Value 尽量不要使用复杂对象;
    C:将对象转换成 JSON 对象(利用现成的 JSON 库)后存入 Redis;
    D:将对象转换成 Google 开源二进制协议对象(Google Protobuf,和 JSON 数据格式类似,但是因为是二进制表现,所以性能效率以及空间占用都比 JSON 要小;缺点是 Protobuf 的学习曲线比 JSON 大得多);
    E:Redis 使用完以后一定要释放连接。

    读取缓存中是否有相关数据,如果缓存中有相关数据,则直接返回,这就是所谓的数据命中“hit”
    如果缓存中没有相关数据,则从数据库读取相关数据,放入缓存中,再返回。这就是所谓的数据未命中“miss”

    缓存的命中率 = 命中缓存请求个数/总缓存访问请求个数 = hit/(hit+miss)
    ​​

    NoSQL

    与缓存的区别
    先说明一下,这里介绍的和缓存不一样,虽然redis等也可以用来做数据存储方案(比如Redis或者Tair),但NoSql是把它作为DB来用。如果当作DB来用,需要有效保证数据存储方案的可用性、可靠性。

    使用场景
    需要结合具体的业务场景,看这块业务涉及的数据是否适合用NoSQL来存储,对数据的操作方式是否适合用NoSQL的方式来操作,或者是否需要用到NoSQL的一些额外特性(比如原子加减等)。
    如果业务数据不需要和其他数据作关联,不需要事务或者外键之类的支持,而且有可能写入会异常频繁,这个时候就比较适合用NoSQL(比如HBase)。
    比如,美团点评内部有一个对exception做的监控系统,如果在应用系统发生严重故障的时候,可能会短时间产生大量exception数据,这个时候如果选用MySQL,会造成MySQL的瞬间写压力飙升,容易导致MySQL服务器的性能急剧恶化以及主从同步延迟之类的问题,这种场景就比较适合用Hbase类似的NoSQL来存储。
    视图/存储过程
    普通业务逻辑尽量不要使用存储过程,定时任务或报表统计函数可以根据团队资源情况采用存储过程处理。

    GVM调优

    通过监控系统(如没有现成的系统,自己做一个简单的上报监控的系统也很容易)上对一些机器关键指标(gc time、gc count、各个分代的内存大小变化、机器的Load值与CPU使用率、JVM的线程数等)的监控报警,也可以看gc log和jstat等命令的输出,再结合线上JVM进程服务的一些关键接口的性能数据和请求体验,基本上就能定位出当前的JVM是否有问题,以及是否需要调优。

    异步/多线程

    针对某些客户端的请求,在服务端可能需要针对这些请求做一些附属的事情,这些事情其实用户并不关心或者用户不需要立即拿到这些事情的处理结果,这种情况就比较适合用异步的方式处理这些事情。

    异步作用
    A:缩短接口响应时间,使用户的请求快速返回,用户体验更好。
    B:避免线程长时间处于运行状态,这样会引起服务线程池的可用线程长时间不够用,进而引起线程池任务队列长度增大,从而阻塞更多请求任务,使得更多请求得不到技术处理。
    C:线程长时间处于运行状态,可能还会引起系统Load、CPU使用率、机器整体性能下降等一系列问题,甚至引发雪崩。异步的思路可以在不增加机器数和CPU数的情况下,有效解决这个问题。

    异步实现
    A:额外开辟线程,这里可以采用额外开辟一个线程或者使用线程池的做法,在IO线程(处理请求响应)之外的线程来处理相应的任务,在IO线程中让response先返回。
    B:使用消息队列(MQ)中间件服务

    搜索引擎

    例如:solr,elasticsearch


    我在微信订阅号等你!
    这里写图片描述

    展开全文
  • wordpress数据库优化介绍 这篇wordpress优化数据库的方法(详解版)添加了很多的说明,目的是让不清楚wordpress数据库的童鞋可以了解下理论知识。理论应用于实际么。如果只想看操作的本站总结了wordpress数据库优化的...
  • 优化SQLServer数据库的50种方法
  • SQL数据库优化方法

    2019-03-05 10:15:55
    数据库优化通常可以过对 网络、硬件操作系统数据库参数和应用程序的优化来进行。
  • 数据库优化之实战

    2018-04-12 10:06:39
    数据库优化方法,千万级数据库记录查询解决实战。1 对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引
  • 简单描述数据库优化方案,以及数据库一些常用的操作,包括一些简单的查询语句,函数使用,合适学习mysql的读者。 简单描述数据库优化方案,以及数据库一些常用的操作,包括一些简单的查询语句,函数使用,合适学习...
  • MySQL数据库优化

    2018-04-09 10:34:09
    MySQL数据库优化MySQL数据库优化MySQL数据库优化MySQL数据库优化
  • wordpress数据库优化介绍 这篇wordpress数据库优化的方法(简洁操作版),没什么理论的说明,如果想更具体wordpress数据库优化的操作的意义请查看:wordpress优化数据库的方法(详解版) 在wordpress允许一段时间后,...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 796,809
精华内容 318,723
关键字:

数据库优化