MySQL数据库优化法则总结-外链论坛

5ep9lzv 发表于 2024-7-27 01:05:48

MySQL数据库优化法则总结

网上关于SQL优化的教程非常多，然则比较杂乱。近期有空整理了一下，写出来跟大众分享一下，其中有错误和不足的地区，还请大众纠正以及弥补
     这篇文案我花费了海量的时间查询资料、修改期盼大众阅读之后，感觉好的话举荐给更加多的人，让更加多的人看到、纠正以及弥补。
要正确的优化SQL，咱们需要快速定位能性的瓶颈点，亦便是说快速找到咱们SQL重点的开销在哪里？而大都数状况性能最慢的设备会是瓶颈点，如下载时网络速度可能会是瓶颈点，本地复制文件时硬盘可能会是瓶颈点，为何这些通常的工作咱们能快速确认瓶颈点呢，由于咱们对这些慢速设备的性能数据有有些基本的认识，如网络带宽是2Mbps，硬盘是每分钟7200转等等。因此呢，为了快速找到SQL的性能瓶颈点，咱们亦需要认识咱们计算机系统的硬件基本性能指标，下图展示的当前主流计算机性能指标数据
<img src="https://mmbiz.qpic.cn/mmbiz_png/Wc4rFKffLwQwTRF5gr6BjkV2CeJH6SBD06zWGMXCV7BdGl6LaSpoCmFqoIervzEzkhRFgvAarib4iavia5rU51Gww/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
     从图上能够看到基本上每种设备都有两个指标：
延时（响应时间）：暗示硬件的突发处理能力；
带宽（吞吐量）：表率硬件连续处理能力。
      从上图能够看出，计算机系统硬件性能从高到代依次为：
CPU——Cache(L1-L2-L3)——内存——SSD硬盘——网络——硬盘
     按照数据库知识，咱们能够列出每种硬件重点的工作内容：
CPU及内存：缓存数据拜访、比较、排序、事务检测、SQL解析、函数或规律运算；
网络：结果数据传输、SQL请求、远程数据库拜访（dblink）；
硬盘：数据拜访、数据写入、日志记录、大数据量排序、大表连接。
     按照当前计算机硬件的基本性能指标及其在数据库中重点操作内容，能够整理出如下图所示的性能基本优化法则：
<img src="https://mmbiz.qpic.cn/mmbiz_png/Wc4rFKffLwQwTRF5gr6BjkV2CeJH6SBDJicj9lV3D4hce7IuLecsPbNDN6WW2GHFlaeH4vgMKQfOLe4Ggmo7nbQ/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
这个优化法则归纳为5个层次：
1、  减少许据拜访（减少磁盘拜访）
2、  返回更少许据（减少网络传输或磁盘拜访）
3、  减少交互次数（减少网络传输）
4、  减少服务器CPU开销（减少CPU及内存开销）
5、  利用更加多资源（增多资源）
因为每一层优化法则都是处理其对应硬件的性能问题，因此带来的性能提高比例亦不同样。传统数据库系统设计是亦是尽可能对低速设备供给优化办法，因此呢针对低速设备问题的可优化手段亦更加多，优化成本亦更低。咱们任何一个SQL的性能优化都应该按这个规则由上到下来诊断问题并提出处理方法，而不该该首要想到的是增多资源处理问题。
        以下是每一个优化法则层级对应优化效果及成本经验参考：
优化法则
性能提高效果
优化成本
减少许据拜访
1~1000
低
返回更少许据
1~100
低
减少交互次数
1~20
低
减少服务器CPU开销
1~5
低
利用更加多资源
@~10
高
    接下来，咱们针对5种优化法则列举常用的优化手段
a: 表的设计恰当化(符合3NF)
b: 优化SQL语句（索引）
c: 分表技术(水平分割、垂直分割)、分区技术
d: 读写[写: update/delete/add]分离
e: 存储过程 [模块化编程，能够加强速度]
f: 对mysql配置优化 [配置最大并发数, 调节缓存体积 ]
g: mysql服务器硬件升级
h: 按时的去清除不需要的数据,按时进行碎片整理
1、表的设计恰当化(符合3NF)
1NF（第1范式）
    1NF的限定要求如下：（只要数据库是关系型数据库，就自动的满足1NF）
         1. 每一个列必须有一个独一的名叫作
         2. 行和列的秩序无关紧要
         3. 每一列都必须有单个数据类型
         4. 不准许包括相同值的两行
5. 每一列都必须包括一个单值（一个列不可保留多个数据值）
         6. 列不可包括重复的组
第1范式会存在更新、删除和插进反常。
2NF（第二范式）
    2NF的限定要求如下：（一般咱们设计一个主键来实现）
         1. 它符合第1范式
         2. 所有的非键值字段均依赖于所有的键值字段
第二范式亦会存在更新、删除和插进反常。
3NF（第三范式）
3NF的限定要求如下：
         1. 符合2NF
         2. 不包括传递关联性，（即，一个非键值字段的值依赖于另一个非键值字段的值），不含冗余数据
反3NF ：无冗余的数据库未必是最好的数据库，有时为了加强运行效率，就必须降低范式标准，适当保存冗余数据。
详细做法：
在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时思虑。降低范式便是增多字段，准许冗余。
2、优化SQL语句
（1）快速的定位执行速度慢的语句 
     a 开启慢查找
     b 设置慢查找时间
     c 启用慢查找日志
     d 经过mysqldumoslow工具对慢日志进行归类汇总
（2）分析SQL语句
     a 经过explain分析查找 
b 通profiling能够得到更仔细的信息
（3）SQL语句优化
     a 创建索引(主键索引/独一索引/全文索引/普通索引)
     b 避免Select * (不查找多余的列与行)
     c Where中少用NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE、OR，它们会忽略索引，导致全表扫描
d 用Where子句替代having子句,having只会在检索出所有记录之后才对结果集进行过滤
     e 运用视图（经常被查找的列数据，并且这些数据不被经常的修改，删除）
    数据库索引的原理非常简单，但在繁杂的表中真正能正确运用索引的人很少，即使是专业的DBA亦不必定能完全做到最优。
索引会大大增多表记录的DML(INSERT,UPDATE,DELETE)开销，正确的索引能够让性能提高100，1000倍以上，不恰当的索引亦可能会让性能下降100倍，因此呢在一个表中创建什么样的索引需要平衡各样业务需要。
    倘若咱们把一个表的内容认为是一本字典，那索引就相当于字典的目录，如下图所示：

<img src="https://mmbiz.qpic.cn/mmbiz_png/Wc4rFKffLwQwTRF5gr6BjkV2CeJH6SBDEERg7MBPbSqL63nwcLyg6QU9bfKiaK3bDMRZECgAHxrMwFQIasibIfNA/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
图中是一个字典按部首+笔划数的目录，相当于给字典建了一个按部首+笔划的组合索引。
一个表中能够建多个索引，就如一本字典能够建多个目录同样（按拼音、笔划、部首等等）。
一个索引亦能够由多个字段构成，叫作为组合索引，如上图便是一个按部首+笔划的组合目录。
咱们通常在什么字段上建索引？
这是一个非常繁杂的专题，需要对业务及数据充分分析后再能得出结果。主键及外键一般都要有索引，其它需要建索引的字段应满足以下要求：
a 字段出此刻查找要求中，并且查找要求能够运用索引；
b 语句执行频率高，一天会有几千次以上；
c 经过字段要求可筛选的记录集很小，那数据筛选比例是多少才适合？
    这个无固定值，需要按照表数据量来评定，以下是经验公式，可用于快速评定：
小表(记录数少于10000行的表)：筛选比例<10%；
大表：(筛选返回记录数)<(表总记录数*单条记录长度)/10000/16
单条记录长度≈字段平均内容长度之和+字段数*2
怎样晓得SQL是不是运用了正确的索引？
简单SQL能够按照索引运用语法规则判断，繁杂的SQL欠好办，判断SQL的响应时间是一种策略，然则这会受到数据量、主机负载及缓存等因素的影响，有时数据全在缓存里，可能全表拜访的时间比索引拜访时间还少。要准确晓得索引是不是正确运用，需要到数据库中查看SQL真实的执行计划，这个专题比较繁杂，详见SQL执行计划专题介绍。
索引对DML(INSERT,UPDATE,DELETE)附加的开销有多少？
这个无固定的比例，与每一个表记录的体积及索引字段体积密切关联，以下是一个普通表测试数据，仅供参考：
索引针对Insert性能降低56%
索引针对Update性能降低47%
索引针对Delete性能降低29%
       因此呢针对写IO压力比很强的系统，表的索引需要仔细评定必要性，另一索引亦会占用必定的存储空间。
切记，性能优化是无止境的，当性能能够满足需要时就可，不要过度优化。在实质数据库中咱们不可能把每一个SQL请求的字段都建在索引里，因此这种只经过索引拜访数据的办法通常只用于核心应用，亦便是那种对核心表拜访量最高且查找字段数据量很少的查找
3、分表技术(水平分割、垂直分割)、分区技术
为何要分表和分区？ 
倘若遇到大表的状况下，SQL语句优化已然没法继续优化了，咱们能够思虑分表和分区，目的便是减少许据库的包袱，加强数据库的效率，一般点来讲便是加强表的增删改查效率。
什么是分表？
分表是将一个大表根据必定的规则分解成多张拥有独立存储空间的实体表，咱们能够叫作为子表，每一个表都对应三个文件，MYD数据文件，.MYI索引文件，.frm表结构文件。这些子表能够分布在同一块磁盘上，亦能够在区别的设备上。app读写的时候按照事先定义好的规则得到对应的子表名，而后去操作它。
什么是分区？
分区和分表类似，都是根据规则分解表。区别在于分表将大表分解为若干个独立的实体表，而分区是将数据分段划分在多个位置存放，能够是同一起磁盘亦能够在区别的设备。分区后，表面上还是一张表，但数据散列到多个位置了。app读写的时候操作的还是大表名字，db自动去组织分区的数据。
mysql分表和分区有什么联系呢？
（1）都能加强mysql的性能，在高并发状态下都有一个良好的表现。
（2）分表和分区不矛盾，能够相互协同的，针对哪些大拜访量，并且表数据比较多的表，咱们能够采取分表和分区结合的方式，拜访量不大，然则表数据非常多的表，咱们能够采取分区的方式等。
（3）分表技术是比较麻烦的，需要手动去创建子表，app服务端读写时候需要计算子表名。采用merge好有些，但亦要创建子表和配置子表间的union关系。
（4）表分区相针对分表，操作方便，不需要创建子表。
4、读写[写: update/delete/add]分离
大型网站为了缓解海量的并发拜访，除了在网站实现分布式负载平衡，远远不足。倘若还是传统的数据结构，或只是单单靠一台服务器扛，如此多的数据库连接操作，数据库必然会崩溃，数据丢失的话，后果更加是不堪设想。此时候，咱们会思虑怎样减少许据库的联接，一方面采用优秀的代码框架，进行代码的优化，采用优秀的数据缓存技术如：memcached,倘若资金丰厚的话，必然会想到架设服务器群，来分担主数据库的压力
因此呢，通常来讲都是经过主从复制（Master-Slave）的方式来同步数据，再经过读写分离（MySQL-Proxy，是MySQL官方供给的MySQL中间件服务）来提高数据库的并发负载能力 这般的方法来进行部署与实施的

实现方式

第1种：php程序上自己做规律判断，写php代码的时候，自己在程序上做规律判读写匹配。select，insert、update、delete做正则匹配，按照结果选取写服务器（主服务器）。倘若是select操作则选取读服务器（从服务器器） mysql_connect(读写的区分)
第二种：MySQL中间件，基本的原理是让主数据库处理写操作（insert、update、delete），而从数据库处理查找操作（select）。而数据库的一致性则经过主从复制来实现。因此说主从复制是读写分离的基本。
      下面是有些常用的MySQL中间件的背景介绍
<img src="https://mmbiz.qpic.cn/mmbiz_png/Wc4rFKffLwQwTRF5gr6BjkV2CeJH6SBD1skNXrkSH3tFkhsyrBRQAuUMed9syS3uOiaiaVr0kSCpVce0cuVhnvZw/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
5、存储过程 
（1）为何需要存储过程
a 数据不安全，网络传送SQL代码，容易被未授权者截获
     b 每次提交SQL代码都要经过语法编译后在执行，影响应用程序的运行性能
     c 网络流量大，针对反复执行的SQL代码，在网络上多次传送，影响网络传输量
（2）什么是存储过程
存储过程是SQL语句和掌控语句的预编译集合，保留在数据库中，可有应用程序调用执行，况且准许用户声明变量、规律掌控语句及其他强大的编程功能。包括规律掌控语句和数据操作语句，能够接收参数、输出参数、返回单个或多个结果值及返回值
（3）运用存储过程的优点
     a 模块化程序设计，只需创建一次，以后就可调用该存储过程任意次
     b 执行速度快，效率高
     c 减少网络流量
     d 拥有良好的安全性
6、对mysql配置优化   
下面是有些配置的优化，详细参数的解释就不写了，请自动查询资料
<img src="https://mmbiz.qpic.cn/mmbiz_png/Wc4rFKffLwQwTRF5gr6BjkV2CeJH6SBD8N0u7ViaDTpqyBNCUEbNwOuFf3uguDg5DrIkZ6qqWelicRXj9JDV7qgQ/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
7、mysql服务器硬件升级
（1）磁盘 
MySQL每秒钟都在进行海量、繁杂的查找操作，对磁盘的读写量可想而知。因此，一般认为磁盘I/O是制约MySQL性能的最大原因之一
处理方法：运用RAID-10 、磁盘阵列设备SAN
（2）CPU  针对MySQL应用，举荐运用S.M.P.架构的多路对叫作CPU
（3）内存  越大越好
（4）网卡  最少两个网卡，均为1GBE。一般我会将这两个nics绑定在一块以供给冗余
8、按时的去清除不需要的数据,按时进行碎片整理
什么是磁盘碎片？
简单的说,删除数据必然会在数据文件中导致不连续的空白空间,而当插进数据时,这些空白空间则会被利用起来.于是造成为了数据的存储位置不连续,以及理学存储次序与理论上的排序次序区别,这种是数据碎片.实质上数据碎片分为两种,一种是单行数据碎片,另一种是多行数据碎片.前者的意思便是一行数据,被分成N个片段,存储在N个位置.后者的便是多行数据并未根据规律上的次序摆列.
当有海量的删除和插进操作时,必然会产生非常多未运用的空白空间,这些空间便是多出来的额外空间.索引亦是文件数据,因此亦会产生索引碎片,理由同上,大概便是次序错乱的问题.Engine 区别,OPTIMIZE 的操作亦不同样的,MyISAM 由于索引和数据是掰开的,因此 OPTIMIZE 能够整理数据文件,并重排索引。这般不仅会浪费空间，并且查找速度亦更慢。
处理方法：
（1）查看表碎片的办法
select ROW_FORMAT,TABLE_ROWS,DATA_LENGTH,INDEX_LENGTH,MAX_DATA_LENGTH,DATA_FREE,ENGINE from TABLES where TABLE_SCHEMA=test_db and TABLE_NAME=table_name limit 1;
（2）Innodb存储引擎清理碎片办法
ALTER TABLE tablename ENGINE=InnoDB
（3）Myisam存储引擎清理碎片办法
OPTIMIZE TABLE table_name
切记，必定要在夜里执行，表越大，越耗资源时间，不要频繁修复，能够几个月乃至一年修复一次，倘若表频繁被更改，能够做个计划任务，按周/月来整理。

7wu1wm0 发表于 2024-8-22 11:11:06

你说得对，我们一起加油，未来可期。

nykek5i 发表于 2024-10-27 22:22:53

祝福你、祝你幸福、早日实现等。

4lqedz 发表于 2024-11-6 20:28:39

你的言辞如同繁星闪烁，点亮了我心中的夜空。

页: [1]

外链论坛's Archiver

MySQL数据库优化法则总结