精华内容
下载资源
问答
  • 数据库优化方案整理

    万次阅读 多人点赞 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


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

    展开全文
  • 数据库优化一般思路(个人经验之谈)

    万次阅读 多人点赞 2017-10-17 10:38:54
    随着系统规模的不断增加,数据量和并发量不断增大,整个系统架构中最先受到冲击而形成瓶颈的,定然是数据库,因此数据库层面的优化,是一个程序员不可或缺的技能,以下是我在使用数据库中的一些心得,有不足之处,还...

      随着系统规模的不断增加,数据量和并发量不断增大,整个系统架构中最先受到冲击而形成瓶颈的,定然是数据库,因此数据库层面的优化,是一个程序员不可或缺的技能,以下是我在使用数据库中的一些心得,有不足之处,还望批评指正完善。

            首先简单介绍下sql执行的过程,如下图

            

          当你在sql客户端(如命令行或者navicat)提交一条sql后,sql解析引擎会被启动,此时,sql引擎会去解析优化这条sql,比如有时候你发现你写的sql 查询条件中的数据类型虽然和数据库表定义的数据类型不一致,但是还是能够得到正确的执         行,是因为数据库引擎帮你做了自动转化,比如

           select * from user where telephone=13937147647   telephone为varchar类型

            虽然类型不一致,但是sql会得到正确执行,是因为数据库引擎帮你做了自动类型转化

            不仅如此,数据库表定义了多个索引,你写的sql可能都会命中,sql引擎会帮你选择最优的一个,这些都是此阶段完成的 

            接下来,数据库引擎会拿着优化好的sql命令语义去硬盘中查找数据,然后将查找到的数据返回(如果此时返回的结果集过大,会造成数据库IO繁忙,会大大损伤sql效率,所以一般我们都使用分页的原因就在于此)

            我们熟悉了sql执行过程之后,开始了解各种优化方式吧:

            1、索引,建立索引是数据库优化各种方案之中成本最低,见效最快的解决方案,一般来讲,数据库规模在几十万和几百万级别的时候见效最快,即便是有不太复杂的表关联,也能大幅度提高sql的运行效率,这个在我们以前的项目应用中,有非常深刻的体会,本来耗时50s的sql,在增加索引后可以提升到1-2s,而且不需要有代码改动,成本低廉,见效明显

             建立索引需要注意的地方

             a、索引一般加在查询条件的关键字上,如果有多个查询条件关键字,还可以添加组合索引,写sql的时候需要注意,索引字段和sql字段需要保持一致,否则索引会无效,比如

                  简单粗暴一点儿,我直接使用我们主数据数据库(测试库)中的md_house_property_info表中的source_house_code_no字段,这个字段在数据库中被定义为了varchar类型,定义了多个索引,都包含了source_house_code_no字段

                 

               大家看,source_house_code_no我写成varchar类型的时候,是可以走索引的

              

             当我写成数字的时候,sql能够正确执行,但是却没有命中索引

             大家再细心一点儿会发现,我这里面有个possiable_keys,这个是指的可能命中的索引,此处出现了两个,但是数据库引擎会选择最优的一个idx_source_house_code_no,这个过程我在开始有介绍了

              

            b、不要在查询=前面使用函数,否则会导致索引不生效,举个栗子,where str=substring(“hello world”,6,8),这样是可以走索引的,但是 where substring(str,6,8)=“hello world” 是不会命中索引的

            c、建立索引的字段要区分度比较高,比如user表中有一个性别字段,性别字段无非男女两种值,区分度不好,建立索引效果不好,要选择区分度高的字段

            d、建立组合索引,可以持续提升sql运行效率,但是也不要盲目,同样的要注意区分度,如果区分度不够高,就不要加了,多个字段,尽可能把区分度高的字段放在前面,另外,还要注意索引长度,这个索引要同时兼顾索引长度和区分度的平衡

            e、索引会大幅提升查询效率,但是也会损耗查询后修改效率,要注意兼顾平衡,使用在一次插入,多次查询的表上效果最好,同时要注意的是,组合索引会不可避免的增加索引长度,会增加索引存储空间,注意索引长度和区分度平衡

            f、后来因为工作需要,意外发现mysql居然支持全文索引,没测试过效率,正常使用全文索引都是使用 lunce,以及在其之上的solr和现在正火的elastisearch,后面可以单独来说

             

            2、分库分表分区

            分库,可以按照业务分库,分流数据库并发压力,使数据库表更加有条理性,最起码更加好找吧,我们当时是把查询库和系统库(增删改比较频繁的表)分开了,这样如果有大查询,不影响系统库

            分表,刚才说了,索引适合应对百万级别的数据量,千万级别数据量使用的好,勉强也能凑合,但如果是上亿级别的数据量,索引就无能为力了,因为单索引文件可能就已经上百兆或者更多了,那么,轮到我们的分表分区登场了

            分表的方法有很多种

            a、如果这个业务是有流程的,那么我们通常会设计一个历史表或者归档表,用来存放历史数据,这样能保证实时数据效率比较高

            b、针对某一张大表,可以根据查询条件分成多张表,比如时间,我们可以将半个月或者10天的数据放到一张表里(看具体数据量,个人认为3000W是个上限,最好控制到百万级别),每过10天,我们就自动创建一张数据库表,然后将数据插入,如此,按照时间查询,就要先定位去那种表中去取数,这样,效率能够得到大幅度提升,当然,这么解决也有问题,比如跨表,需要union多张表,而且跨表没法支持索引

             c、上面的方法是我们直接通过程序和数据库实现的最原始的分表解决方案,现在市面上有一些成熟的软件如mycat,也是支持分表的,我们之前从事的公司有个专门做分布式数据库的,这些产品出现跨表,可以不使用程序union了,而且还是使索引生效,但是需要对产品有一定的掌握

             d、一般来讲,数据库中的大表毕竟只是一少部分,仅需要对这少部分大表进行分表就可以了,没必要小表也进行分表,增加维护开发难度

            分区

            分区的实现道理和分表一样,也是将相应规则的数据放在一起,唯一不同的是分区你只需要设定好分区规则,插入的数据会被自动插入到指定的区里,当然查询的时候也能很快查询到需要区,相当于是分表对外透明了,出现跨表数据库自动帮我们合并做了处理,使用起来比分表更加方便,但是分区也有自己的问题,每一个数据库表的并发访问是有上限的,也就是说,分表能够抗高并发,而分区不能,如何选择,要考虑实际情况

           3、数据库引擎

                也是偶尔听一个dba同事提到的,有一次我跟dba同事抱怨,我的数据库查询太慢,有没有好的优化方法,他一开始就问,数据量多大,有没有索引,使用的什么数据库引擎,这时我才意识到原来数据库引擎也算是一种优化方案

                mysql比较常用的数据库引擎有两种,一种是innodb、一种是myisam 

                我当时做过一个千万级数据量复杂sql测试,myisam的效率大概能够比innodb快1-2倍,虽然效率提升不是很明显,但是也有提升,后来查过一些资料,说之所以mysiam快,是因为他的数据存储结构、索引存储结构和innodb不一样的,mysiam的索引结构是在内存中存的

                当然,mysiam也有弱点,那就是他是表级锁,而innodb是行级锁,所以,mysiam适用于一次插入,多次查询的表,或者是读写分离中的读库中的表,而对于修改插入删除操作比较频繁的表,就很不合适了

           4、预处理

            一般来说,实时数据(当天的数据)还是比较有限的,真正数据量比较大的是历史数据,基于大表历史数据的查询,如果再涉及一些大表关联,这种sql是非常难以优化的

           a、实时数据(当天数据)

                通过对对业务的抽象,可以放在缓存里面,提升系统运行效率

           b、历史数据,大数据表历史数据且有表关联,通过常规sql难以优化,但是该数据通常有个共性,就是第二天去查询前一天的数据做分析报表,也就是说对时效性要求不高,这种情况的解决方案是预处理

                 做法是将这些复杂表关联sql写成个定时任务在半夜执行,将执行的结果存入到一张结果表中,第二天直接查询结果表,如此,效率能得到十分明显提升

           c、和b类似,可以将表关联结果存入solr或者elastisearch中,以此提升效率,目前我们的项目就是如此处理

           5、mysql  like查询

                大家都知道,like  “%str%” 不支持索引, "str%"号是支持索引的

                因此,如果业务允许,可以使用前匹配的方法是数据库快速定位到数据,在结果集中再进行like匹配,如果这个结果集不是很大,是可以大幅提升运行效率的,这个需要对业务和程序有灵活的变通

                如果业务实在不允许前匹配,那就可以采用solr或者elastisearch来进行模糊匹配,但是进行模糊匹配有个前提,原始数据是字符串的话,不要带有特殊符号,如#,&,% 等,这样会造成分词不准,最终导致查询结果不正确

          6、读写分离

               在数据库并发大的情况下,最好的做法就是进行横向扩展,增加机器,以提升抗并发能力,而且还兼有数据备份功能

    展开全文
  • 本篇文章只是给大家提供一些优化方面的方向和思路,而具体业务场景的不同,使用的MySQL服务版本不同,都会使得优化方案的制定也不同。 首先开看优化可能带来的问题 优化不总是对一个单纯的环境进行,还很可能是一个...
  • 面试求职:数据库常见面试题(数据库优化思路

    万次阅读 多人点赞 2017-03-09 16:36:29
    面试回答数据库优化问题从以下几个层面入手:(1)、根据服务层面:配置mysql性能优化参数;(2)、从系统层面增强mysql的性能:优化数据表结构、字段类型、字段索引、分表,分库、数据库集群、读写分离等等。(3)...

    原文地址:http://www.2cto.com/database/201504/390838.html

    1. 主键 超键 候选键 外键

    主 键:

    数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键,且主键的取值不能缺失,即不能为空值(Null)。

    超 键:

    在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键,多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。

    候选键:

    最小超键,即没有冗余元素的超键。

    外 键:

    在一个表中存在的另一个表的主键称此表的外键。

    2.数据库事务的四个特性及含义

    数据库事务transanction正确执行的四个基本要素。ACID,原子性(Atomicity)、一致性(Correspondence)、隔离性(Isolation)、持久性(Durability)。


    原子性:整个事务中的所有操作,要么全部完成,要么全部不完成,不可能停滞在中间某个环节。事务在执行过程中发生错误,会被回滚(Rollback)到事务开始前的状态,就像这个事务从来没有执行过一样。


    一致性:在事务开始之前和事务结束以后,数据库的完整性约束没有被破坏。


    隔离性:隔离状态执行事务,使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务,运行在相同的时间内,执行 相同的功能,事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化,为了防止事务操作间的混淆,必须串行化或序列化请 求,使得在同一时间仅有一个请求用于同一数据。


    持久性:在事务完成以后,该事务所对数据库所作的更改便持久的保存在数据库之中,并不会被回滚。

    3.视图的作用,视图可以更改么?

    视图是虚拟的表,与包含数据的表不一样,视图只包含使用时动态检索数据的查询;不包含任何列或数据。使用视图可以简化复杂的sql操作,隐藏具体的细节,保护数据;视图创建后,可以使用与表相同的方式利用它们。
    视图不能被索引,也不能有关联的触发器或默认值,如果视图本身内有order by 则对视图再次order by将被覆盖。
    创建视图:create view XXX as XXXXXXXXXXXXXX;
    对于某些视图比如未使用联结子查询分组聚集函数Distinct Union等,是可以对其更新的,对视图的更新将对基表进行更新;但是视图主要用于简化检索,保护数据,并不用于更新,而且大部分视图都不可以更新。

    4.drop,delete与truncate的区别

    drop直接删掉表 truncate删除表中数据,再插入时自增长id又从1开始 delete删除表中数据,可以加where字句。

    (1) DELETE语句执行删除的过程是每次从表中删除一行,并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存,删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。

    (2) 表和索引所占空间。当表被TRUNCATE 后,这个表和索引所占用的空间会恢复到初始大小,而DELETE操作不会减少表或索引所占用的空间。drop语句将表所占用的空间全释放掉。

    (3) 一般而言,drop > truncate > delete

    (4) 应用范围。TRUNCATE 只能对TABLE;DELETE可以是table和view

    (5) TRUNCATE 和DELETE只删除数据,而DROP则删除整个表(结构和数据)。

    (6) truncate与不带where的delete :只删除数据,而不删除表的结构(定义)drop语句将删除表的结构被依赖的约束(constrain),触发器(trigger)索引(index);依赖于该表的存储过程/函数将被保留,但其状态会变为:invalid。

    (7) delete语句为DML(data maintain Language),这个操作会被放到 rollback segment中,事务提交后才生效。如果有相应的 tigger,执行的时候将被触发。

    (8) truncate、drop是DLL(data define language),操作立即生效,原数据不放到 rollback segment中,不能回滚

    (9) 在没有备份情况下,谨慎使用 drop 与 truncate。要删除部分数据行采用delete且注意结合where来约束影响范围。回滚段要足够大。要删除表用drop;若想保留表而将表中数据删除,如果于事务无关,用truncate即可实现。如果和事务有关,或老师想触发trigger,还是用delete。

    (10) Truncate table 表名 速度快,而且效率高,因为:
    truncate table 在功能上与不带 WHERE 子句的 DELETE 语句相同:二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快,且使用的系统和事务日志资源少。DELETE 语句每次删除一行,并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据,并且只在事务日志中记录页的释放。

    (11) TRUNCATE TABLE 删除表中的所有行,但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值,请改用 DELETE。如果要删除表定义及其数据,请使用 DROP TABLE 语句。

    (12) 对于由 FOREIGN KEY 约束引用的表,不能使用 TRUNCATE TABLE,而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中,所以它不能激活触发器。

    5.索引的工作原理及其种类

    数据库索引,是数据库管理系统中一个排序的数据结构,以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树

    在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法。这种数据结构,就是索引。

    为表设置索引要付出代价的:一是增加了数据库的存储空间,二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。

    \

     

    上图展示了一种可能的索引方式。左边是数据表,一共有两列七条记录,最左边的是数据记录的物理地址(注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的)。为了加快Col2的查找,可以维护一个右边所示的二叉查找树,每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针,这样就可以运用二叉查找在O(log2n)的复杂度内获取到相应数据。

    创建索引可以大大提高系统的性能。

    第一,通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。

    第二,可以大大加快数据的检索速度,这也是创建索引的最主要的原因。

    第三,可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。

    第四,在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。

    第五,通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能。

    也许会有人要问:增加索引有如此多的优点,为什么不对表中的每一个列创建一个索引呢?因为,增加索引也有许多不利的方面。

    第一,创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。

    第二,索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大。

    第三,当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。

    索引是建立在数据库表中的某些列的上面。在创建索引的时候,应该考虑在哪些列上可以创建索引,在哪些列上不能创建索引。一般来说,应该在这些列上创建索引:在经常需要搜索的列上,可以加快搜索的速度;在作为主键的列上,强制该列的唯一性和组织表中数据的排列结构;在经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度;在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的;在经常需要排序的列上创建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间;在经常使用在WHERE子句中的列上面创建索引,加快条件的判断速度。

    同样,对于有些列不应该创建索引。一般来说,不应该创建索引的的这些列具有下列特点:

    第一,对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为,既然这些列很少使用到,因此有索引或者无索引,并不能提高查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求。

    第二,对于那些只有很少数据值的列也不应该增加索引。这是因为,由于这些列的取值很少,例如人事表的性别列,在查询的结果中,结果集的数据行占了表中数据行的很大比例,即需要在表中搜索的数据行的比例很大。增加索引,并不能明显加快检索速度。

    第三,对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为,这些列的数据量要么相当大,要么取值很少。

    第四,当修改性能远远大于检索性能时,不应该创建索引。这是因为,修改性能和检索性能是互相矛盾的。当增加索引时,会提高检索性能,但是会降低修改性能。当减少索引时,会提高修改性能,降低检索性能。因此,当修改性能远远大于检索性能时,不应该创建索引。

    根据数据库的功能,可以在数据库设计器中创建三种索引:唯一索引、主键索引和聚集索引

    唯一索引

    唯一索引是不允许其中任何两行具有相同索引值的索引。

    当现有数据中存在重复的键值时,大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如,如果在employee表中职员的姓(lname)上创建了唯一索引,则任何两个员工都不能同姓。 主键索引 数据库表经常有一列或列组合,其值唯一标识表中的每一行。该列称为表的主键。 在数据库关系图中为表定义主键将自动创建主键索引,主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时,它还允许对数据的快速访问。 聚集索引 在聚集索引中,表中行的物理顺序与键值的逻辑(索引)顺序相同。一个表只能包含一个聚集索引。

    如果某索引不是聚集索引,则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比,聚集索引通常提供更快的数据访问速度。

    局部性原理与磁盘预读

    由于存储介质的特性,磁盘本身存取就比主存慢很多,再加上机械运动耗费,磁盘的存取速度往往是主存的几百分分之一,因此为了提高效率,要尽量减少磁盘I/O。为了达到这个目的,磁盘往往不是严格按需读取,而是每次都会预读,即使只需要一个字节,磁盘也会从这个位置开始,顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理当一个数据被用到时,其附近的数据也通常会马上被使用。程序运行期间所需要的数据通常比较集中。

    由于磁盘顺序读取的效率很高(不需要寻道时间,只需很少的旋转时间),因此对于具有局部性的程序来说,预读可以提高I/O效率。

    预读的长度一般为页(page)的整倍数。页是计算机管理存储器的逻辑块,硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块,每个存储块称为一页(在许多操作系统中,页得大小通常为4k),主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时,会触发一个缺页异常,此时系统会向磁盘发出读盘信号,磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中,然后异常返回,程序继续运行。

    B-/+Tree索引的性能分析

    到这里终于可以分析B-/+Tree索引的性能了。

    上文说过一般使用磁盘I/O次数评价索引结构的优劣。先从B-Tree分析,根据B-Tree的定义,可知检索一次最多需要访问h个节点。数据库系统的设计者巧妙利用了磁盘预读原理,将一个节点的大小设为等于一个页,这样每个节点只需要一次I/O就可以完全载入。为了达到这个目的,在实际实现B-Tree还需要使用如下技巧:

    每次新建节点时,直接申请一个页的空间,这样就保证一个节点物理上也存储在一个页里,加之计算机存储分配都是按页对齐的,就实现了一个node只需一次I/O。

    B-Tree中一次检索最多需要h-1次I/O(根节点常驻内存),渐进复杂度为O(h)=O(logdN)。一般实际应用中,出度d是非常大的数字,通常超过100,因此h非常小(通常不超过3)。

    而红黑树这种结构,h明显要深的多。由于逻辑上很近的节点(父子)物理上可能很远,无法利用局部性,所以红黑树的I/O渐进复杂度也为O(h),效率明显比B-Tree差很多。

    综上所述,用B-Tree作为索引结构效率是非常高的。

    6.连接的种类

    查询分析器中执行:
    --建表table1,table2:
    create table table1(id int,name varchar(10))
    create table table2(id int,score int)
    insert into table1 select 1,'lee'
    insert into table1 select 2,'zhang'
    insert into table1 select 4,'wang'
    insert into table2 select 1,90
    insert into table2 select 2,100
    insert into table2 select 3,70
    如表
    -------------------------------------------------
    table1 | table2 |
    -------------------------------------------------
    id name |id score |
    1 lee |1 90|
    2 zhang| 2 100|
    4 wang| 3 70|
    -------------------------------------------------

    以下均在查询分析器中执行
    一、外连接
    1.概念:包括左向外联接、右向外联接或完整外部联接

    2.左连接:left join 或 left outer join
    (1)左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行,而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行,则在相关联的结果集行中右表的所有选择列表列均为空值(null)。
    (2)sql 语句
    select * from table1 left join table2 on table1.id=table2.id
    -------------结果-------------
    idnameidscore
    ------------------------------
    1lee190
    2zhang2100
    4wangNULLNULL
    ------------------------------
    注释:包含table1的所有子句,根据指定条件返回table2相应的字段,不符合的以null显示

    3.右连接:right join 或 right outer join
    (1)右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行,则将为左表返回空值。
    (2)sql 语句
    select * from table1 right join table2 on table1.id=table2.id
    -------------结果-------------
    idnameidscore
    ------------------------------
    1lee190
    2zhang2100
    NULLNULL370
    ------------------------------
    注释:包含table2的所有子句,根据指定条件返回table1相应的字段,不符合的以null显示

    4.完整外部联接:full join 或 full outer join
    (1)完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时,则另一个表的选择列表列包含空值。如果表之间有匹配行,则整个结果集行包含基表的数据值。
    (2)sql 语句
    select * from table1 full join table2 on table1.id=table2.id
    -------------结果-------------
    idnameidscore
    ------------------------------
    1lee190
    2zhang2100
    4wangNULLNULL
    NULLNULL370
    ------------------------------
    注释:返回左右连接的和(见上左、右连接)

    二、内连接
    1.概念:内联接是用比较运算符比较要联接列的值的联接

    2.内连接:join 或 inner join

    3.sql 语句
    select * from table1 join table2 on table1.id=table2.id
    -------------结果-------------
    idnameidscore
    ------------------------------
    1lee190
    2zhang2100
    ------------------------------
    注释:只返回符合条件的table1和table2的列

    4.等价(与下列执行效果相同)
    A:select a.*,b.* from table1 a,table2 b where a.id=b.id
    B:select * from table1 cross join table2 where table1.id=table2.id (注:cross join后加条件只能用where,不能用on)

    三、交叉连接(完全)

    1.概念:没有 WHERE 子句的交叉联接将产生联接所涉及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。(table1和table2交叉连接产生3*3=9条记录)

    2.交叉连接:cross join (不带条件where...)

    3.sql语句
    select * from table1 cross join table2
    -------------结果-------------
    idnameidscore
    ------------------------------
    1lee190
    2zhang190
    4wang190
    1lee2100
    2zhang2100
    4wang2100
    1lee370
    2zhang370
    4wang370
    ------------------------------
    注释:返回3*3=9条记录,即笛卡尔积

    4.等价(与下列执行效果相同)
    A:select * from table1,table2

    7.数据库范式

    1 第一范式(1NF)

    在任何一个关系数据库中,第一范式(1NF)是对关系模式的基本要求,不满足第一范式(1NF)的数据库就不是关系数据库。
    所谓第一范式(1NF)是指数据库表的每一列都是不可分割的基本数据项,同一列中不能有多个值,即实体中的某个属性不能有多个值或者不能有重复的属性。如果出现重复的属性,就可能需要定义一个新的实体,新的实体由重复的属性构成,新实体与原实体之间为一对多关系。在第一范式(1NF)中表的每一行只包含一个实例的信息。简而言之,第一范式就是无重复的列。

    2 第二范式(2NF)

    第二范式(2NF)是在第一范式(1NF)的基础上建立起来的,即满足第二范式(2NF)必须先满足第一范式(1NF)。第二范式(2NF)要求数据库表中的每个实例或行必须可以被惟一地区分。为实现区分通常需要为表加上一个列,以存储各个实例的惟一标识。这个惟一属性列被称为主关键字或主键、主码。
    第二范式(2NF)要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性,如果存在,那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体,新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列,以存储各个实例的惟一标识。简而言之,第二范式就是非主属性非部分依赖于主关键字。

    3 第三范式(3NF)

    满足第三范式(3NF)必须先满足第二范式(2NF)。简而言之,第三范式(3NF)要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。例如,存在一个部门信息表,其中每个部门有部门编号(dept_id)、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表,则根据第三范式(3NF)也应该构建它,否则就会有大量的数据冗余。简而言之,第三范式就是属性不依赖于其它非主属性。(我的理解是消除冗余)


    8.数据库优化的思路

    这个我借鉴了慕课上关于数据库优化的课程。

    1.SQL语句优化

    1)应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。
    2)应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:
    select id from t where num is null
    可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:
    select id from t where num=0
    3)很多时候用 exists 代替 in 是一个好的选择
    4)用Where子句替换HAVING 子句 因为HAVING 只会在检索出所有记录之后才对结果集进行过滤

    2.索引优化

    看上文索引

    3.数据库结构优化

    1)范式优化: 比如消除冗余(节省空间。。) 2)反范式优化:比如适当加冗余等(减少join) 3)拆分表: 分区将数据在物理上分隔开,不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。这样,当对这个表进行查询时,只需要在表分区中进行扫描,而不必进行全表扫描,明显缩短了查询时间,另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘I/O,一个精心设置的分区可以将数据传输对磁盘I/O竞争均匀地分散开。对数据量大的时时表可采取此方法。可按月自动建表分区。
    4)拆分其实又分垂直拆分和水平拆分: 案例: 简单购物系统暂设涉及如下表: 1.产品表(数据量10w,稳定) 2.订单表(数据量200w,且有增长趋势) 3.用户表 (数据量100w,且有增长趋势) 以mysql为例讲述下水平拆分和垂直拆分,mysql能容忍的数量级在百万静态数据可以到千万 垂直拆分: 解决问题:表与表之间的io竞争 不解决问题:单表中数据量增长出现的压力 方案: 把产品表和用户表放到一个server上 订单表单独放到一个server上 水平拆分: 解决问题:单表中数据量增长出现的压力 不解决问题:表与表之间的io争夺
    方案: 用户表通过性别拆分为男用户表和女用户表 订单表通过已完成和完成中拆分为已完成订单和未完成订单 产品表 未完成订单放一个server上 已完成订单表盒男用户表放一个server上 女用户表放一个server上(女的爱购物 哈哈)

    4.服务器硬件优化

    这个么多花钱咯!

    9.存储过程与触发器的区别

    触发器与存储过程非常相似,触发器也是SQL语句集,两者唯一的区别是触发器不能用EXECUTE语句调用,而是在用户执行Transact-SQL语句时自动触发(激活)执行。触发器是在一个修改了指定表中的数据时执行的存储过程。常通过创建触发器来强制实现不同表中的逻辑相关数据的引用完整性和一致性。由于用户不能绕过触发器,所以可以用它来强制实施复杂的业务规则,以确保数据的完整性。触发器不同于存储过程,触发器主要是通过事件执行触发而被执行的,而存储过程可以通过存储过程名称名字而直接调用。当对某一表进行诸如UPDATE、INSERT、DELETE这些操作时,SQLSERVER就会自动执行触发器所定义的SQL语句,从而确保对数据的处理必须符合这些SQL语句所定义的规则。


    10.面试回答数据库优化问题从以下几个层面入手

    (1)、根据服务层面:配置mysql性能优化参数;

    (2)、从系统层面增强mysql的性能:优化数据表结构、字段类型、字段索引、分表,分库、读写分离等等。

    (3)、从数据库层面增强性能:优化SQL语句,合理使用字段索引。

    (4)、从代码层面增强性能:使用缓存和NoSQL数据库方式存储,如MongoDB/Memcached/Redis来缓解高并发下数据库查询的压力。

    (5)、减少数据库操作次数,尽量使用数据库访问驱动的批处理方法。

    (6)、不常使用的数据迁移备份,避免每次都在海量数据中去检索。

    (7)、提升数据库服务器硬件配置,或者搭建数据库集群。

    (8)、编程手段防止SQL注入:使用JDBC PreparedStatement按位插入或查询;正则表达式过滤(非法字符串过滤);

    展开全文
  • 目录 一、数据库硬件优化 二、数据库结构优化 ...通过在日常项目中优化数据库的经验及网上查询到的资料,总结几点数据库优化思路,若有不足之处,还望批评、指正、完善。 一、数据库硬件优化 最最最...

    目录

    一、数据库硬件优化

    二、数据库结构优化

    三、SQL语句优化


          因为随着公司系统(环保型项目)规模的不断增加,导致数据量和并发量逐渐增大,因此不得不对数据库进行优化,从而达到减少数据冗余,节约数据储存空间,提高查询效率的目的。通过在日常项目中优化数据库的经验及网上查询到的资料,总结几点数据库优化的思路,若有不足之处,还望批评、指正、完善。

     

    一、数据库硬件优化

    最最最最最最最最最最最最最最最最最最最最最最最最最最最最最最最最最最最简单、粗暴的方法,就是烧钱咯。:)

     

    二、数据库结构优化

    (1)范式优化: 比如消除冗余(减少表中重复的字段,节省表空间);

    (2)反范式优化:比如适当加冗余等(增加表中重复字段,减少join连接);

    (3)劲量避免数据维护中出现更新,插入和删除异常:

    • 插入异常:如果表中的某个实体随着另一个实体而存在(单独插入某个字段失败,因为有的字段不能为空);
    • 更新异常:如果更改表中的某个实体的单独属性时,需要对多行进行更新;
    • 删除异常:如果删除表中的某一个实体则会导致其他实体的消失。

    (4)拆分表: 垂直拆分和水平拆分;

    分区将数据在物理上分隔开,不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。这样,当对这个表进行查询时,只需要在表分区中进行扫描,而不必进行全表扫描,明显缩短了查询时间,另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘I/O,一个精心设置的分区可以将数据传输对磁盘I/O竞争均匀地分散开。对数据量大的时时表可采取此方法。可按月自动建表分区。

    案例:

        简单购物系统暂设涉及如下表:

    1.产品表(数据量10w,稳定)

    2.订单表(数据量200w,且有增长趋势)

    3.用户表 (数据量100w,且有增长趋势)

    以mysql为例讲述下水平拆分和垂直拆分,mysql能容忍的数量级在百万静态数据可以到千万

     

    垂直拆分:

    解决问题:表与表之间的io竞争

    不解决问题:单表中数据量增长出现的压力

    方案:

    把产品表和用户表放到一个server上

    订单表单独放到一个server上

     

    水平拆分:

    解决问题:单表中数据量增长出现的压力

    不解决问题:表与表之间的io争夺

     

    方案:

    用户表通过性别拆分为男用户表和女用户表

    订单表通过已完成和完成中拆分为已完成订单和未完成订单

    产品表 未完成订单放一个server上

    已完成订单表盒男用户表放一个server上

    女用户表放一个server上(女的爱购物 哈哈)

     

    三、SQL语句优化

           在我们书写SQL语句的时候,其实书写的顺序、策略会影响到SQL的性能,虽然实现的功能是一样的,但是它们的性能会有些许差别。下面整理了几条优化的思路:

    (1)选择最有效率的表名顺序

    数据库的解析器按照从右到左的顺序处理FROM子句中的表名,FROM子句中写在最后的表将被最先处理

    在FROM子句中包含多个表的情况下:

    • 如果三个表是完全无关系的话,将记录和列名最少的表,写在最后,然后依次类推
    • 也就是说:选择记录条数最少的表放在最后

    如果有3个以上的表连接查询:

    • 如果三个表是有关系的话,将引用最多的表,放在最后,然后依次类推
    • 也就是说:被其他表所引用的表放在最后

    例如:查询员工的编号,姓名,工资,工资等级,部门名

    emp表被引用得最多,记录数也是最多,因此放在form字句的最后面

    
    select emp.empno,emp.ename,emp.sal,salgrade.grade,dept.dname
    from salgrade,dept,emp
    where (emp.deptno = dept.deptno) and (emp.sal between salgrade.losal and salgrade.hisal)  		
    
    
    

    (2)WHERE子句中的连接顺序

    数据库采用自右而左的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之左,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的之右

    emp.sal可以过滤多条记录,写在WHERE字句的最右边

    
          select emp.empno,emp.ename,emp.sal,dept.dname
          from dept,emp
          where (emp.deptno = dept.deptno) and (emp.sal > 1500) 
    

    (3)SELECT子句中避免使用*号

    我们当时学习的时候,“*”号是可以获取表中全部的字段数据的。

    • 但是它要通过查询数据字典完成的,这意味着将耗费更多的时间
    • 使用*号写出来的SQL语句也不够直观。

    (4)用TRUNCATE替代DELETE

    这里仅仅是:删除表的全部记录,除了表结构才这样做

    DELETE是一条一条记录的删除,而Truncate是将整个表删除,保留表结构,这样比DELETE快

    (5)多使用内部函数提高SQL效率

    例如使用mysql的concat()函数会比使用||来进行拼接快,因为concat()函数已经被mysql优化过了。

    (6)使用表或列的别名

    如果表或列的名称太长了,使用一些简短的别名也能稍微提高一些SQL的性能。毕竟要扫描的字符长度就变少了。。。

    (7)多使用commit

    comiit会释放回滚点...

    (8)善用索引

    索引就是为了提高我们的查询数据的,当表的记录量非常大的时候,我们就可以使用索引了。

    (9)SQL写大写

    我们在编写SQL 的时候,官方推荐的是使用大写来写关键字,因为Oracle服务器总是先将小写字母转成大写后,才执行

    (10)避免在索引列上使用NOT

    因为Oracle服务器遇到NOT后,他就会停止目前的工作,转而执行全表扫描

    (11)避免在索引列上使用计算

    WHERE子句中,如果索引列是函数的一部分,优化器将不使用索引而使用全表扫描,这样会变得变慢

    (12)用 >= 替代 >

          低效:
          SELECT * FROM EMP WHERE DEPTNO > 3   
          首先定位到DEPTNO=3的记录并且扫描到第一个DEPT大于3的记录
          高效:
          SELECT * FROM EMP WHERE DEPTNO >= 4  
          直接跳到第一个DEPT等于4的记录
    
    

    (13)用IN替代OR

    
          select * from emp where sal = 1500 or sal = 3000 or sal = 800;
          select * from emp where sal in (1500,3000,800);
    
    

    (14)总是使用索引的第一个列

    如果索引是建立在多个列上,只有在它的第一个列被WHERE子句引用时,优化器才会选择使用该索引。 当只引用索引的第二个列时,不引用索引的第一个列时,优化器使用了全表扫描而忽略了索引

    
          create index emp_sal_job_idex
          on emp(sal,job);
          ----------------------------------
          select *
          from emp  
          where job != 'SALES';	
          
          
          上边就不使用索引了。

     

    展开全文
  • Oracle 数据库性能优化的主要方法
  • 数据库优化之百万级数据方案

    千次阅读 2017-06-06 16:07:46
    做管理系统的,无论是bs结构的还是cs结构的,都不可避免的涉及到数据库表结构的设计,sql语句的编写等。因此在开发系统的时候,表结构设计是否合理,sql语句是否标准,写出的sql性能是否优化往往会成为公司衡量...
  • Oracle数据库优化的方法

    千次阅读 2018-05-26 22:54:30
    Oracle数据库优化的方法1、减少访问数据库的次数。2、不要让数据库做得太多。(1)SELECT子句中避免使用' * ':ORACLE在解析的过程中,会将' * '依次转换成所有的列名, 这个工作是通过查询数据字典完成的,这意味着...
  • 数据库查询条件优化方案
  • 数据库调优的5个阶段一:需求分析:应用情况的估算(对应用是哟个情况,数据量,数据库压力,峰值压力进行评估),系统选型策略(确定什么样的数据库可以适用应用需求)二:项目设计期:数据模型的设计(遵循关系...
  • 数据库面试

    千次阅读 多人点赞 2019-02-13 09:03:42
    一、数据库问答题 1. SQL语言包括哪些类型? 数据定义DDL:Create Table,Alter Table,Drop Table, Create/Drop Index等 数据操纵DML:Select ,insert,update,delete, 数据控制DCL:grant,revoke 2. 内联接,外联接...
  • 数据库优化相关面试题

    万次阅读 2019-01-05 10:51:07
    数据库优化,包括合理的事务隔离级别、SQL语句优化、索引的优化 使用缓存,尽量减少数据库 IO 分布式数据库、分布式缓存 服务器的负载均衡   2、实践中如何优化MySQL 四条从效果上第一条影响最大,后面...
  • 我们可能会采取各种方式去优化,比如缓存,SQL优化等等,除了这些方式以外,这里再分享几个针对数据库优化的常规手段:「数据读写分离」与「数据库Sharding」。 这两点基本上是大中型互联网项目中应用的非常普遍的...
  • 数据库优化思路

    2016-05-18 21:35:49
    数据库优化思路 1.SQL语句优化 1)应尽量避免在 where 子句中使用!=或 2)应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描, 如:select id from t where num is...
  • Oracle数据库访问性能优化

    万次阅读 多人点赞 2018-02-20 16:02:36
    所有数据库包括Oracle的sql优化都是针对程序员的,而不是针对dba的,第一,尽量防止模糊,明确指出,即用列名代替*,第二,在where语句上下工夫。第三多表查询和子查询,第四尽量使用绑定。 根据计算机硬件的基本...
  • 今天跟大家分享一篇刚哥写的关于数据库优化的文章,这篇文章会教会你面对问题解决的方法,真是很实用,这可是刚哥的经验之谈,感觉不错就分享给你的小伙伴吧! 原因分析 主要原因1:后台数据库中的数据过多,没做...
  • 摘要:本文主要从大型数据库ORACLE环境四个不同级别的调整分析入手,分析ORACLE的系统结构和工作机理,从九个不同方面较全面地总结了ORACLE数据库优化调整方案。从多个角度出发,对Oracle优化中的很多关键问题进行...
  • **本篇文章只是给大家提供一些优化方面的方向和思路,而具体业务场景的不同,使用的MySQL服务版本不同,都会使得优化方案的制定也不同。 首先开看优化可能带来的问题 优化不总是对一个单纯的环境进行,还很可能是一...
  • MySQL数据库中最重要的一个概念就是数据库引擎,不同的数据库引擎的工作原理存在很大差异最终造成MySQL数据库服务的性能差异。
  • 漫谈数据库查询速度优化方案

    千次阅读 2016-09-10 08:57:14
    摘要 从事前端开发的都知道,页面显示的数据一定要及时的呈现,否则会影响用户体现.那么导致页面加载数据慢或者显示滞后的原因又是什么呢? 原因分析 主要原因1:后台数据库中的... 这里总结了几种方案,如何提高数据库
  • 数据库查询优化技术 学习笔记(一)   我是看李海翔的《数据库技术丛书·数据库查询优化器的艺术:原理解析与SQL性能优化》这本书的视频讲解学习的,因为数据库的知识学的不多,直接看优化有些吃力,慢慢补吧。...
  • 数据库优化

    千次阅读 2018-07-15 20:14:58
    1. 主键 超键 候选键 外键主 键:数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键,且主键的取值不能缺失,即不能为空值(Null)。超 键:在关系中能唯一标识元组的属性...
  • 数据库优化整理之:冷热分离

    千次阅读 2021-01-13 10:52:43
    工作中,随着数据库表数据量的增大,我们会发现,对表数据的读写操作会变得越来越慢,有时候查询一条数据会耗费几十秒或几分钟才查出结果,甚至多点击几次查询还会出现宕机。 这个时候,我们可能首先会想到通过对表...
  • 方案一:优化现有Mysql数据库。优点:不影响限有业务,源程序不需要修改代码,成本最低。缺点:有优化瓶颈,数据量过亿就玩完了。 方案二:升级数据库类型,换一种100%兼容Mysql的数据库。 优点:不影响现有业务,源...
  • MYSQL 八大优化方案

    万次阅读 多人点赞 2018-04-05 16:38:00
    关于数据库优化,网上有不少资料和方法,但是不少质量参差不齐,有些总结的不够到位,内容冗杂。 偶尔发现了这篇文章,总结得很经典,文章流量也很大,所以拿到自己的总结文集中,积累优质文章,提升个人能力,希望...
  • 数据库优化其实也是一门巨大的学问,松哥以后看有时间写个连载和大家仔细聊聊这个话题。 4.热点数据分离 数据库中的数据,虽然是海量数据,但是这些数据并不见得所有数据都是活跃数据,例如用户注册,有的用户注册完...
  • Oracle数据库基本优化思路

    千次阅读 2012-12-26 17:05:33
     一般来讲,对于数据库而言,性能差主要体现在以下方面: 1.本机和客户端远程访问数据库,连接响应不及时,或者出现连接长时间 尝试后连接超时甚至无法连接; 2.在数据库后台上的操作,包括:执行普通的增、删、改...
  • 前言 当MySQL单表记录数过大...单表优化这块方法不是太全可以翻阅我的其它相关博文-----《MySQL高性能优化规范(这应该是我见过写的最好的优化规范建议了)》和《解析SQL语句执行速度慢的原因(SQL性能优化)》 单表优化...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 76,407
精华内容 30,562
关键字:

数据库优化方案思路