MYSQL网页编程之mysql数据库优化(未完....待续....)
对于现有业务，可以轻松移植到MySQL。当你需要替换掉老的硬件，当你需要削减历史遗留下的老系统的时候，选用MySQL对于财务部门来说更具吸引力。mysql|数据|数据库|优化#######################################
#文件名:mysqloptimize,php#
#主题:mysql数据库的优化#
#作者:_Shakespeare(收集骑士)#
#最初修正工夫:2002--09--20#
#e-mail:shakespareshmily@sina.com.cn#
#######################################
下决计入手下手学oracle了,用mysql已有不短的工夫了,明天写下这些算是对本人的一个mysql之旅的一个交卸吧.以下仅仅是自己在利用mysql过程当中的一点团体的体味,大概存在很多忽略和毛病,还请斧正!!
起首,为了使一个体系更快,最主要的部分就是基本计划,不外有些器材是现无情况下没法超越的,好比说体系罕见的瓶颈.
我所能想到的:
1:磁盘寻道才能,以高速硬盘(7200转/秒),实际上每秒寻道7200次.这是没有举措改动的,优化的办法是----用多个硬盘,大概把数据分离存储.
2:硬盘的读写速率,这个速率十分的快(限于自己的常识所限,只晓得在每秒几十乃至上百MB).这个更简单办理--能够从多个硬盘上并行读写.
3:cpu.cpu处置内存中的数据,当有绝对内存较小的表时,这是最多见的限定要素.
4:内存的限定.当cpu必要超越合适cpu缓存的数据时,缓存的带宽就成了内存的一个瓶颈---不外如今内存年夜的惊人,一样平常不会呈现这个成绩.
第二步:
(自己利用的是黉舍网站的linux平台(LinuxADVX.Mandrakesoft.com2.4.3-19mdk))
1:调治服务器参数
用shell>mysqld-help这个命令声厂一张一切mysql选项和可设置变量的表.输入以下信息:
possiblevariablesforoption--set-variable(-o)are:
back_logcurrentvalue:5//请求mysql能有的毗连数目.back_log指出在mysql停息承受毗连的工夫内有几个毗连哀求能够被存在仓库中
connect_timeoutcurrentvalue:5//mysql服务器在用badhandshake(欠好翻译)应对前守候一个毗连的工夫
delayed_insert_timeoutcurrentvalue:200//一个insertdelayed在停止前守候insert的工夫
delayed_insert_limitcurrentvalue:50//insertdelayed处置器将反省是不是有任何select语句未实行,假如有,持续前实行这些语句
delayed_queue_sizecurrentvalue:1000//为insertdelayed分派多年夜的队
flush_timecurrentvalue:0//假如被设置为非0,那末每一个flush_time工夫,一切表都被封闭
interactive_timeoutcurrentvalue:28800//服务器在打开它之前在洋交互毗连上守候的工夫
join_buffer_sizecurrentvalue:131072//用与全体毗连的缓冲区巨细
key_buffer_sizecurrentvalue:1048540//用语索引块的缓冲区的巨细,增添它能够更好的处置索引
lower_case_table_namescurrentvalue:0//
long_query_timecurrentvalue:10//假如一个查询所用工夫年夜于此工夫,slow_queried计数将增添
max_allowed_packetcurrentvalue:1048576//一个包的巨细
max_connectionscurrentvalue:300//同意同时毗连的数目
max_connect_errorscurrentvalue:10//假如有多于该数目的中止毗连,将制止进一步的毗连,能够用flushhosts来办理
max_delayed_threadscurrentvalue:15//能够启动的处置insertdelayed的数目
max_heap_table_sizecurrentvalue:16777216//
max_join_sizecurrentvalue:4294967295//同意读取的毗连的数目
max_sort_lengthcurrentvalue:1024//在排序blob大概text时利用的字节数目
max_tmp_tablescurrentvalue:32//一个毗连同时翻开的一时表的数目
max_write_lock_countcurrentvalue:4294967295//指定一个值(一般很小)来启动mysqld,使得在必定数目的write锁定以后呈现read锁定
net_buffer_lengthcurrentvalue:16384//通讯缓冲区的巨细--在查询时被重置为该巨细
query_buffer_sizecurrentvalue:0//查询时缓冲区巨细
record_buffercurrentvalue:131072//每一个按次扫描的毗连为其扫描的每张表分派的缓冲区的巨细
sort_buffercurrentvalue:2097116//每一个举行排序的毗连分派的缓冲区的巨细
table_cachecurrentvalue:64//为一切毗连翻开的表的数目
thread_concurrencycurrentvalue:10//
tmp_table_sizecurrentvalue:1048576//一时表的巨细
thread_stackcurrentvalue:131072//每一个线程的巨细
wait_timeoutcurrentvalue:28800//服务器在封闭它3之前的一个毗连上守候的工夫
依据本人的必要设置以上信息会对你匡助.
第三:
1:假如你在一个数据库中创立大批的表,那末实行翻开,封闭，创立(表)的操纵就会很慢.
2:mysql利用内存
a:关头字缓存区(key_buffer_size)由一切线程共享
b:每一个毗连利用一些特定的线程空间.一个栈(默许为64k,变量thread_stack),一个毗连缓冲区(变量net_buffer_length)和一个了局缓冲区(net_buffer_length).特定情形下,毗连缓冲区和了局缓冲区主动态扩展到max_allowed_packet.
c:一切线程共享一个基存储器
d:没有内存暗射
e:每一个做按次扫描的哀求分派一个读缓冲区(record_buffer)
f:一切联合均有一遍完成而且年夜多半联合乃至能够不必一个一时表完成.最一时的表是基于内存的(heap)表
g:排序哀求分派一个排序缓冲区和2个一时表
h:一切语法剖析和盘算都在一个当地存储器完成
i:每一个索引文件只被翻开一次,而且数据文件为每一个并发运转的线程翻开一次
j:对每一个blob列的表，一个缓冲区静态的被扩展以便读进blob值
k:一切正在利用的表的表处置器被保留在一个缓冲器中而且作为一个fifo办理.
l:一个mysqladminflush-tables命令封闭一切不在利用的表而且在以后实行的线程停止时标志一切在利用的表筹办封闭
3:mysql锁定表
mysql中一切锁定不会成为逝世锁.
wirte锁定:
mysql的锁定道理:a:假如表没有锁定,那末锁定;b不然,把锁定哀求放进写锁定行列中
read锁定:
mysql的锁定道理:a:假如表没有锁定,那末锁定;b不然,把锁定哀求放进读锁定行列中
偶然候会在一个表中举行良多的select,insert操纵,能够在一个一时表中拔出行而且偶然用一时表的纪录更新真实的表
a:用low_priority属性给一个特定的insert,update大概delete较低的优先级
b:max_write_lock_count指定一个值(一般很小)来启动mysqld,使得在必定数目的write锁定以后呈现read锁定
c:经由过程利用setsql_low_priority_updates=1能够从一个特定的线程指定一切的变动应当由较低的优先级完成
d:用high_priority指定一个select
e:假如利用insert....select....呈现成绩,利用myisam表------由于它撑持由于它撑持并发的select和insert
4:最基础的优化是使数据在硬盘上占有的空间最小.假如索引做在最小的列上,那末索引也最小.完成办法:
a:利用尽量小的数据范例
b:假如大概，声明表列为NOTNULL.
c:假如有大概利用酿成的数据范例,如varchar(可是速率会受必定的影响)
d:每一个表应当有尽量短的主索引
e:创立的确必要的索引
f:假如一个索引在头几个字符上有独一的前缀,那末仅仅索引这个前缀----mysql撑持在一个字符列的一部分上的索引
g:假如一个表常常被扫描,那末试图拆分它为更多的表
第四步
1:索引的利用,索引的主要性就不说了,功效也不说了,只说怎样做.
起首要明白一切的mysql索引(primary,unique,index)在b树中有存储.索引次要用语:
a:疾速找到where指定前提的纪录
b:实行联合时,从其他表检索行
c:对特定的索引列找出max()和min()值
d：假如排序大概分组在一个可用键的最后面加前缀，排序或分组一个表
e：一个查询大概被用来优化检索值，而不必会见数据文件．假如某些表的列是数字型而且恰好是某个列的前缀，为了更快，值能够从索引树中掏出
２：存储大概更新数据的查询速率
grant的实行会稍稍的减低效力．
mysql的函数应当被高度的优化．能够用benchmark（loop_count,expression)来找出是不是查询有成绩
select的查询速率：假如想要让一个select．．．where．．．更快，我能想到的只要创建索引．能够在一个表上运转myisamchk－－analyze来更好的优化查询．能够用myisamchk－－sort－index－－sort－records＝１来设置用一个索引排序一个索引和数据．
３：mysql优化where子句
3.１：删除不用要的括号：
（（aANDb)ANDcOR(((aANDb)AND(aANDd))))>(aANDbANDc)OR(aANDbANDcANDd)
3.2:利用常数
（a<bANDb=c)ANDa=100>b>5ANDb=cANDa=5
3.3:删除常数前提
（b>=5ANDb=5)OR(b=6AND5=5)OR(b=100AND2=3)>b=5ORb=6
3.4:索引利用的常数表达式仅盘算一次
3.5：在一个表中，没有一个where的count(*)间接从表中检索信息
3.6:一切常数的表在查询中在任何其他表之前读出
3.7:对外联合表最好联合组合是实验了一切大概性找到的
3.8：假如有一个order by字句和一个分歧的group by子句大概order by大概group by包括不是来自联合的第一个表的列，那末创立一个一时表
3.9:假如利用了sql_small_result，那末msyql利用在内存中的一个表
3.10:每一个表的索引给查询而且利用超过少于３０％的行的索引．
3.11在每一个纪录输入前，跳过不婚配having子句的行
４：优化left join
在mysql中 aleftjoinb按以下体例完成
a：表b依附于表a
b：表a依附于一切用在left join前提的表（除b）
c：一切left join前提被移到where子句中
d：举行一切的联合优化，除一个表老是在一切他依附的表后读取．假如有一个轮回依附，那末将产生毛病
e：举行一切的尺度的where优化
f：假如在a中有一行婚配where子句，可是在b中没有任何婚配left join前提，那末，在b中天生的一切设置为ＮＵＬＬ的一行
g：假如利用left join来找出某些表中不存在的行而且在where部分有column_nameISNULL测试(column_name为NOTNULL列)．那末，mysql在它已找到了婚配left join前提的一行后，将中断在更多的行后寻觅
５：优化limit
a：假如用limit只选择一行，当mysql必要扫描全部表时，它的感化相称于索引
b：假如利用limit＃与order by，mysql假如找到了第＃行，将停止排序，而不会排序正个表
c：当分离limit＃和distinct时，mysql假如找到了第＃行，将中断
d：只需mysql已发送了第一个＃行到客户，mysql将保持查询
e：limit0一向会很快的前往一个空汇合．
f：一时表的巨细利用limit＃盘算必要几空间来办理查询
６：优化insert
拔出一笔记录的是由以下组成：
a:毗连（３）
b:发送查询给服务器（２）
c:剖析查询（２）
d:拔出纪录（１*纪录巨细）
e：拔出索引（１*索引）
f：封闭（１）
以上数字能够当作和总工夫成比例
改良拔出速率的一些办法：
6.1：假如同时从一个毗连拔出很多行，利用多个值的insert，这比用多个语句要快
6.2：假如从分歧毗连拔出良多行，利用insert delayed语句速率更快
6.3:用myisam，假如在表中没有删除的行，能在select：s正在运转的同时拔出行
6.4:当从一个文本文件装载一个表时，用load data infile．这个一般比insert快20
倍
6.5:能够锁定表然后拔出－－次要的速率不同是在一切insert语句完成后，索引缓冲区仅被存进到硬盘一次．一样平常与有分歧的insert语句那样屡次存进要快．假如能用一个单个语句拔出一切的行，锁定就不必要．锁定也下降毗连的全体工夫．可是对某些线程最年夜守候工夫将上升．比方：
thread1does1000inserts
thread2,3and4does1insert
thread5does1000inserts
假如不利用锁定，２，３，４将在１和５之前完成．假如利用锁定，２，３，４，将大概在１和５以后完成．可是全体工夫应当快４０％．由于insert，update，delete操纵在mysql中是很快的，经由过程为多于约莫５次接二连三的拔出或更新一行的器材加锁，将取得更好的全体功能．假如做良多一行的拔出，能够做一个lock tables，偶然随后做一个unlock tables（约莫每１０００行）以同意别的的线程存取表．这仍旧将招致取得好的功能．load data infile对装载数据仍旧是很快的．
为了对load data infile和insert失掉一些更快的速率，扩展关头字缓冲区．
７优化update的速率
它的速率依附于被更新数据的巨细和被更新索引的数目
使update更快的另外一个办法是推延修正，然后一行一行的做良多修正．假如锁定表，做一行一行的良多修正比一次做一个快
８优化delete速率
删除一个纪录的工夫与索引数目成反比．为了更快的删除纪录，能够增添索引缓存的巨细
从一个表删除一切行比删除这个表的年夜部分要快的多
第五步
１：选择一种表范例
1.1静态myisam
这类格局是最复杂且最平安的格局，它是磁盘格局中最快的．速率来自于数据能在磁盘上被找到的难易水平．当锁定有一个索引和静态格局的器材是，它很复杂，只是行长度乘以数目．并且在扫描一张表时，每次用磁盘读取来读进常数个纪录是很简单的．平安性来历于假如当写进一个静态myisam文件时招致盘算机down失落，myisamchk很简单指出每行在那里入手下手和停止，因而，它一般能发出一切纪录，除部分被写进的纪录．在mysql中一切索引总能被重修
1.2静态myisam
这类格局每行必需有一个头申明它有多长．当一个纪录在变动时代变长时，它能够在多于一个地位上停止．能利用optimize tablename或myisamchk收拾一张表．假如在统一个表中有像某些varchar大概blob列那样存取／改动的静态数据，将静态列移进别的一个表以免碎片．
1.2.1紧缩myisam，用可选的myisampack工具天生
1.2.2内存
这类格局对小型／中型表很有效．对拷贝／创立一个经常使用的查找表到洋heap表有大概加速多个表联合，用一样数据大概要快好几倍工夫．
selecttablename.a,tablename2.afromtablename,tablanem2,tablename3where
tablaneme.a=tablename2.aandtablename2.a=tablename3.aandtablename2.c!=0;
为了减速它，能够用tablename2和tablename3的联合创立一个一时表，由于用不异列（tablename1.a）查找．
ＣＲＥＡＴＥ ＴＥＭＰＯＲＡＲＹ ＴＡＢＬＥ testTYPE=HEAP
SELECT
tablename2.aasa2,tablename3.aasa3
FROM
tablenam2,tablename3
WHERE
tablename2.a=tablename3.aandc=0;
SELECTtablename.a,test.a3fromtablename,testwheretablename.a=test.a1;
SELECTtablename.a,test,a3,fromtablename,testwheretablename.a=test.a1and....;
1.3静态表的特性
1.3.1默许格局．用在表不包括varchar，blob，text列的时分
1.3.2一切的char，numeric和decimal列添补到列宽度
1.3.3十分快
1.3.4简单缓冲
1.3.5简单在down后重修，由于纪录位于流动的地位
1.3.6不用被从头构造（用myisamchk），除非是一个巨量的纪录被删除而且优化存储巨细
1.3.7一般比静态表必要更多的存储空间
1.4静态表的特性
1.4.1假如表包括任何varchar，blob，text列，利用该格局
1.4.2一切字符串列是静态的
1.4.3每一个纪录前置一个位．
1.4.4一般比定长表必要更多的磁盘空间
1.4.5每一个纪录仅仅利用所必要的空间，假如一个纪录变的很年夜，它按必要被分红良多段，这招致了纪录碎片
1.4.6假如用凌驾行长度的信息更新行，行被分段．
1.4.7在体系down失落今后欠好重修表，由于一个纪录能够是多段
1.4.8对静态尺寸纪录的希冀行长度是３＋（number of columns＋７）／８＋(number
ofcharcolumns)+packedsizeofnumericcolumns+lengthofstrings+(numberof
NULLcolumns+7)/8
对每一个毗连有６个字节的处分．不管什么时候变动引发纪录的变年夜，都有一个静态纪录被毗连．每一个新毗连最少有２０个字节，因而下一个变上将大概在统一个毗连中．假如不是，将有别的一个毗连．能够用myisamchk －狠毒反省有几毗连．一切毗连能够用myisamchk-r删除．
1.5紧缩表的特性
1.5.1一张用myisampack有用程序制造的只读表．
1.5.2解紧缩代码存在于一切mysql分发中，以便使没有myisampack的毗连也能读取用myisampack紧缩的表
1.5.3占有很小的磁盘空间
1.5.4每一个纪录被独自紧缩．一个纪录的头是一个定长的（１～～３个字节）这取决于表的最年夜纪录．每列以分歧的体例被紧缩．一些经常使用的紧缩范例是：
a:一般对每列有一张分歧的哈夫曼表
b:后缀空缺紧缩
c:前缀空缺紧缩
d:用值０的数字利用１位存储
e:假如整数列的值有一个小局限，列利用最小的大概范例来存储．比方：假如一切的值在０到２５５之间，一个bigint能够作为一个tinyint存储
g:假如列唯一大概值的一个小汇合，列范例被转换到enum
h:列可使用下面的紧缩办法的组合
1.5.5能处置定长或静态长度的纪录，往不克不及处置blob大概text列
1.5.6能用myisamchk解紧缩
mysql能撑持分歧的索引范例，但一样平常的范例是isam，这是一个Ｂ树索引而且能大略的为索引文件盘算巨细为(key_length+4)*0.67，在一切的键上的总和．
字符串索引是空缺紧缩的。假如第一个索引是一个字符串，它可将紧缩前缀假如字符串列有良多尾部空缺或是一个总部能甬道全长的varchar列，空缺紧缩使索引文件更小．假如良多字符串有不异的前缀．
1.6内存表的特性
mysql外部的heap表利用每偶溢进来的１００％静态哈希而且没有与删除有关的成绩．只能经由过程利用在堆表中的一个索引来用等式存取器材（一般用＇＝＇操纵符）
堆表的弱点是：
1.6.1想要同时利用的一切堆表必要充足的分外内存
1.6.2不克不及在索引的一个部分搜刮
1.6.3不克不及按按次搜刮下一个条目（即，利用这个索引做一个order by）
1.6.4mysql不克不及算出在２个值之间也许有几行．这被优化器利用是用来决意利用哪一个索引的，可是在另外一个方面乃至不必要磁盘寻道
（．．．待续．．．）
根据Evans的调查报告，“MySQL的使用在未来将继续呈成长趋势。” 数据库物理框架没有变动undo和redo都放在数据库得transaction中，个人感觉是个败笔。如果说我们在设计数据库的时候考虑分多个数据库，可能能在一定程度上避免I/O效率问题。 如果我们从集合论（关系代数）的角度来看，一张数据库的表就是一组数据元的关系，而每个SQL语句会改变一种或数种关系，从而产生出新的数据元的关系（即产生新的表）。 groupby子句可以将查询结果分组，并返回行的汇总信息Oracle按照groupby子句中指定的表达式的值分组查询结果。 作了些试验，发现使用CLR的存储过程或函数在达到一定的阀值的时候，系统性能会呈指数级下滑！这是非常危险的！只使用几个可能没有问题，当一旦大规模使用会造成严重的系统性能问题！ 一个百万级别的基本信息表A，一个百万级别的详细记录表B，A中有个身份证id，B中也有身份id；先要找出A中在B的详细记录。 始终遗憾SQLServer的登陆无法分配CPU/内存占用等指标数。如果你的SQLServer给别人分配了一个只可以读几个表的权限，而这个家伙疯狂的死循环进行连接查询，会给你的系统带来很大的负担。 XML字段类型更好的解决了XML数据的操作。XQuery确实不错，但是个人对其没好感。（CSDN的开发者应该是相当的熟了！） 原理很简单，对要求长时间计算某一时间点的报表生成和防用户操作错误很有帮助。但是比起Oracle10g的闪回技术还是细粒度不够。可惜！
页:
[1]