如何正确优化SQL数据库

  

,,,

,,,要正确的优化SQL,我们需要快速定位能性的瓶颈点,也就是说快速找到我们SQL主要的开销在哪里?而大多数情况性能最慢的设备会是瓶颈点,如下载时网络速度可能会是瓶颈点,本地复制文件时硬盘可能会是瓶颈点,为什么这些一般的工作我们能快速确认瓶颈点呢,因为我们对这些慢速设备的性能数据有一些基本的认识,如网络带宽是2 mbps,硬盘是每分7200转钟等等,因此,为了快速找到SQL的性能瓶颈点,我们也需要了解我们计算机系统的硬件基本性能指标,下图展示的当前主流计算机性能指标数据

如何正确优化SQL数据库


,,,从图上可以看到基本上每种设备都有两个指标:

延时(响应时间):表示硬件的突发处理能力;

带宽(吞吐量):代表硬件持续处理能力。


,,,从上图可以看的出,计算机系统硬件性能从高到代依次为:

<强> CPU缓存(L1-L2-L3)——内存,SSD硬盘,网络,硬盘


,,,根据数据库知识,我们可以列出每种硬件主要的工作内容:

<强> CPU及内存:缓存数据访问,比较,排的序,事务检测,SQL解析,函数或逻辑运算;

<强>网络:结果数据传输,SQL请求,远程数据库访问(dblink);

<强>硬盘:数据访问,数据写入,日志记录,大数据量排序,大表连接。

,

,,,根据当前计算机硬件的基本性能指标及其在数据库中主要操作内容,可以整理出如下图所示的性能基本优化法则:

如何正确优化SQL数据库”>,,</p> <p>, </p> <p>这个优化法则归纳为5个层次:</p> <p> <强> 1,,,减少数据访问(减少磁盘访问)</强> </p> <p> <强> 2,,,返回更少数据(减少网络传输或磁盘访问)</强> </p> <p> <强> 3,,,减少交互次数(减少网络传输)</强> </p> <p> <强> 4,,,减少云服务器CPU开销(减少CPU及内存开销)</强> </p> <p> <强> 5,,,利用更多资源(增加资源)</强> </p> <p> <强>,</强> </p> <p>,,,,由于每一层优化法则都是解决其对应硬件的性能问题,所以带来的性能提升比例也不一样。传统数据库系统设计是也是尽可能对低速设备提供优化方法,因此针对低速设备问题的可优化手段也更多,优化成本也更低。我们任何一个SQL的性能优化都应该按这个规则由上到下来诊断问题并提出解决方案,而不应该首先想到的是增加资源解决问题。</p> <p>,,,,以下是每个优化法则层级对应优化效果及成本经验参考:</p> <p> </p> <p> </p> <p> </p> <p> </p> <p> </p> <p> </p> <p> </p> <p> </p> <p> </p> <p> </p> <p> </p> <p> </p> <p> </p> <p> </p> <p> </p> <p> </p> <p> </p> <p> </p> <p> <br/> </p> <p>,,接下来,我们针对5种优化法则列举常用的优化手段</p> <p> <强>答:表的设计合理化(符合3 nf) </强> </p> <p> <强> b:优化SQL语句(索引)</强> </p> <p> <强> c:分表技术(水平分割,垂直分割),分区技术</强> </p> <p> <强> d:读写(写:更新/删除/添加)分离</强> </p> <p> <强> e:存储过程(模块化编程,可以提高速度)</强> </p> <p> <强> f:对mysql配置优化(配置最大并发数,调整缓存大小)</强> </p> <p> <强> g: mysql服务器硬件升级</强> </p> <p> <强> h:定时的去清除不需要的数据,定时进行碎片整理</强> </p> <p> <br/> </p> <p> <强> 1,表的设计合理化(符合3 nf) </强> </p> <p> 1 nf(第一范式)</p> <p>,,,, 1 nf的限定条件如下:(只要数据库是关系型数据库,就自动的满足1 nf) </p> <p>,,,,,,,,1. 每个列必须有一个唯一的名称</p> <p>,,,,,,,,2. 行和列的次序无关紧要</p> <p>,,,,,,,,3.每一列都必须有单个数据类型</p> <p>,,,,,,,,4. 不允许包含相同值的两行</p> <p>,,,,,,,,5. 每一列都必须包含一个单值(一个列不能保存多个数据值)</p> <p>,,,,,,,,6. 列不能包含重复的组</p> <p>第一范式会存在更新,删除和插入异常。</p> <p> <br/> </p> <p> 2 nf(第二范式)</p> <p>,,,, 2 nf的限定条件如下:(通常我们设计一个主键来实现)</p> <p>,,,,,,,,1. 它符合第一范式</p> <p>,,,,,,,,2. 所有的非键值字段均依赖于所有的键值字段</p> <p>第二范式也会存在更新,删除和插入异常。</p> <p> <br/> </p> <p> 3 nf(第三范式)</p> <p>,,,, 3 nf的限定条件如下:,,,,</p> <p>,,,,,,,,1. 符合2 nf </p> <p>,,,,,,,,2. 不包含传递相关性(即,一个非键值字段的值依赖于另一个非键值字段的值),不含冗余数据</p> <p> <br/> </p> <p>反3 nf:没有冗余的数据库未必是最好的数据库,有时为了提高运行效率,就必须降低范式标准,适当保留冗余数据。</p> <p>具体做法:</p> <p>在概念数据模型设计时遵守第三范式,降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段,允许冗余。<h2 class=如何正确优化SQL数据库