关于数据库优化的一些想法

优化table结构

#1 列数据类型尽量使用数字类型,避免使用字符类型,后者不仅会占用较多存储空间而且会降低查询效率(逐字符比较);

#2 优先使用VARCHAR,变长字段存储空间小,还可以提升查询效率;

#3 对需要经常作为where条件出现的column添加索引,通过添加constraint设置为unique key(自动添加索引);

#4 对于单DB而言,primary key使用AUTO_INCREMENT的BIGINT是最合适的,自增类型的ID便于分页和索引(UUID的key具有无序性,而且字符类型耗费资源);对于DB集群而言,以DB自身的算法生成ID不能保证唯一性,所以需要引入一个全局的ID generator保证唯一性的同时提供较好的性能,Redis和ZooKeeper是比较好的选择;另外通过给不同的DB或者App加入一个唯一的SN也能最大程度保证唯一性。


优化SQL语句

#1 对于查询语句尽量添加NO_LOCK标志,提升DB并发量;

#2 筛选条件放置位置按优先级从高到低依次为on, where, having,越到后面需要保存的数据和进行的计算就越多:

#3 在where子句中避免使用[!=, <>, or, in, not in, %, like, is null, is not null],原因是会导致DB引擎放弃使用index而进行全文扫描,推荐使用exist或者not exist;

#4 如果某列的重复数据较多,则index不能发挥较好的性能,反而过多的索引还会降低insert和update的效率;

#5 对于涉及多张表的联合查询,依次将数据量最小的表按从右到左的顺序放置,保证中间虚拟表只包含最少的数据;

#6 DB引擎解析一条SQL语句的流程

1 [8] SELECT [9] DISTINCT [11] TOP # <column list>
2 [1]   FROM <left table> [3] <join type> JOIN <right table> [2] ON <join condition>
3 [4]   WHERE <where condition>
4 [5]   GROUP BY <group by list>
5 [6]   WITH <CUBE | ROLLUP>
6 [7]   HAVING <having condition>
7 [10]  ORDER BY <order by list>;


对于sql中每一个子句都会生成一张virtual table用于存储中间数据,因此需要尽可能的减少每一步中VT的数据,上述SQL语句的执行顺序如下:
【1-3】:FROM子句中如果只有一张表,则不做任何处理;如果有两张表,则对其做笛卡尔积处理生成VT1;然后根据join condition执行ON子句进行筛选并生成VT2;接着根据join type(LEFT, RIGHT, INNER, OUTTER)执行JOIN子句生成VT3;如果有超过两张表,则依次处理完前两张表之后在VT3的基础上继续处理之后的表,直到所有的表都处理完最终生成VT4;SQL解释器一般按照从右到左的顺序处理表,因此需要保证越是靠右的表的数据量是越少的。
【4】:根据where condition执行WHERE子句,对中间表数据进行筛选并生成VT5。
【5】:根据group by list执行GROUP子句,对中间表进行分组并生成VT6。
【6】:根据CUBE还是ROLLUP执行WITH子句,对分组结果进行汇总并生成VT7。
【7】:根据having condition执行HAVING子句,对分组结果进行筛选并生成VT8。
【8】:根据column list执行SELECT子句,对中间表进行数据映射并生成VT9。
【9】:执行DISTINCT子句,将重复的行从表中去除并生成VT10。
【10】:根据order by list执行ORDER BY子句,对中间表进行排序并生成VT11。
【11】:根据#(数量或者比例)执行TOP子句,从中间表中去除相应数据量结果并生成VT12。

定期查看SQL执行status

SQL server profiler,需要定期查看DB中超过某个执行时间限制的, 或者是资源占用率超过某些限制的SQL语句;

读写分离

对于单库DB设计而言,如果读写请求同时操作一个库,势必极大的降低DB性能;而一般情况读操作远远多于写操作,这时候就可以通过master-slave的设计,将同一个库的数据做多个备份,写操作仅针对master进行,读操作在master和salve都可以进行;master定期将自身的数据更新到slave上,保证数据的最终一致性同时提升DB的QPS。


分库分表

对于单库多表而言,业务相关的数据尽量放置于同一个库1里面(垂直切分,业务拆分),比如用户A支付B元购买了C商品寄送到D地址,这样可以保证较好的读取性能,这样也能将事务限制在一个库的范围内;但是当库1的某张表的数据记录超过某个限制(单库单表数据量在800万条以内具有比较好的读写性能),需要另起一个库2放置新增加的各个表的数据(水平切分),库1和库2具有相同的表组成,数据量增长的时候也以此类推,之后新的业务数据需要访问和操作某个库时,需要根据某种映射算法(key % n)选择对应的库进行数据读写,这个key必须是满足分布式ID的全局唯一性;

在异地多活中心的架构中,分库的设计可以解决高并发访问的问题,比如有100个手机库存,如果放到一个库里,则所有并发都需要竞争同一把锁,但如果将100个手机库存平均分配到4个库里,每个库有25个库存,则并发访问可以同时竞争四把锁,极大提升了并发效率;另外一种设计是将100个手机库存维护在一个DB库中,但使用redis等缓存基于窗口机制一次性获取多个库存到缓存中,比如redis-01预获取0-24号库存,则更新DB库存为75,redis-02预获取25-49库存,则更新DB库存为50,这样的设计可以减少较慢的DB锁的竞争,而使用较快的cache锁的竞争替代。

使用cache替代DB检索

使用redis或者memcached作为数据缓存

转载于:https://www.cnblogs.com/leo-chen-2014/p/9977861.html

评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符 “速评一下”
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页