数据库优化⽅案整理
⽂章来⾃于 :
⼀：优化说明
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数据库myf
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：缩短接⼝响应时间，使⽤户的请求快速返回，⽤户体验更好。
redis支持的五种数据类型
B：避免线程长时间处于运⾏状态，这样会引起服务线程池的可⽤线程长时间不够⽤，进⽽引起线程池任务队列长度增⼤，从⽽阻塞更多请求任务，使得更多请求得不到技术处理。
C：线程长时间处于运⾏状态，可能还会引起系统Load、CPU使⽤率、机器整体性能下降等⼀系列问题，甚⾄引发雪崩。异步的思路可以在不增加机器数和CPU数的情况下，有效解决这个问题。
异步实现
A：额外开辟线程，这⾥可以采⽤额外开辟⼀个线程或者使⽤线程池的做法，在IO线程（处理请求响应）之外的线程来处理相应的任务，在IO线程中让response先返回。
B：使⽤消息队列（MQ）中间件服务
搜索引擎
例如：solr，elasticsearch

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。