日期：2011-05-02 15:44:00 来源：本站整理

mysql数据库优化[MySQL防范]

赞助商链接

分享到： QQ空间新浪微博腾讯微博人人网

　　本文“mysql数据库优化[MySQL防范]”是由七道奇为您精心收集，来源于网络转载，文章版权归文章作者所有，本站不对其观点以及内容做任何评价，请读者自行判断，以下是其具体内容：
首先,为了使一个系统更快,最重要的部份就是底子计划,不过有些东西是现有情形下无法超越的,比方说系统常见的瓶颈.
我所能想到的:

1:磁盘寻道本领,以高速硬盘(7200转/秒),理论上每秒寻道7200次.这是没有办法改变的,优化的办法是----用多个硬盘,大概把数据分离存储.

2:硬盘的读写速度,这个速度非常的快(限于本人的知识所限,只知道在每秒几十乃至上百MB).这个更简单办理--可以从多个硬盘上并行读写.

3:cpu.cpu处理内存中的数据,当有相对内存较小的表时,这是最常见的限制因素.

4:内存的限制.当cpu需求超越合适cpu缓存的数据时,缓存的带宽就成了内存的一个瓶颈---不过目前内存大的惊人,普通不会呈现这个问题.

第二步:

(本人利用的是学校网站的Linux平台(Linux ADVX.Mandrakesoft.com 2.4.3-19mdk ))

1:疗养服务器参数

用shell>MySQLd-help这个号令声厂一张全部mysql选项和可配置变量的表.输出以下信息:

possible variables for option--set-variable(-o) are:

back_log current value:5 //要求mysql能有的衔接数目.back_log指出在mysql暂停承受衔接的时间内有多少个衔接恳求可以被存在仓库中

connect_timeout current value:5 //mysql服务器在用bad handshake(不好翻译)应答前等候一个衔接的时间

delayed_insert_timeout current value:200 //一个insert delayed在终止前等候insert的时间

delayed_insert_limit current value:50 //insert delayed处理器将查抄能否有任何select语句未履行,假若有,持续前履行这些语句

delayed_queue_size current value:1000 //为insert delayed分配多大的队

flush_time current value:0 //假如被设置为非0,那么每个flush_time 时间,全部表都被关闭

interactive_timeout current value:28800 //服务器在关上它之前在洋交互衔接上等候的时间

join_buffer_size current value:131072 //用与全部衔接的缓冲区大小

key_buffer_size current value:1048540 //用语索引块的缓冲区的大小,增添它可以更好的处理索引

lower_case_table_names current value:0 //

long_query_time current value:10 //假如一个查询所用时间大于此时间,slow_queried计数将增添

max_allowed_packet current value:1048576 //一个包的大小

max_connections current value:300 //答应同时衔接的数目

max_connect_errors current value:10 //假若有多于该数目的中止衔接,将禁止进一步的衔接,可以用flush hosts来办理

max_delayed_threads current value:15 //可以启动的处理insert delayed的数目

max_heap_table_size current value:16777216 //

max_join_size current value:4294967295 //答应读取的衔接的数目

max_sort_length current value:1024 //在排序blob大概text时利用的字节数目

max_tmp_tables current value:32 //一个衔接同时翻开的暂时表的数目

max_write_lock_count current value:4294967295 //指定一个值(普通很小)来启动mysqld,使得在一定数目的write锁定之后呈现read锁定

net_buffer_length current value:16384 //通信缓冲区的大小--在查询时被重置为该大小

query_buffer_size current value:0 //查询时缓冲区大小

record_buffer current value:131072 //每个次序扫描的衔接为其扫描的每张表分配的缓冲区的大小

sort_buffer current value:2097116 //每个举行排序的衔接分配的缓冲区的大小

table_cache current value:64 //为全部衔接翻开的表的数目

thread_concurrency current value:10 //

tmp_table_size current value:1048576 //暂时表的大小

thread_stack current value:131072 //每个线程的大小

wait_timeout current value: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:一个mysqladmin flush-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:通过利用set sql_low_priority_updates=1可以从一个特定的线程指定全部的更改应当由较低的优先级完成

d:用high_priority指定一个select

e:假如利用insert....select....呈现问题,利用myisam表------因为它支持因为它支持并发的select和insert

4:最基本的优化是使数据在硬盘上占据的空间最小.假如索引做在最小的列上,那么索引也最小.实现办法:

a:利用尽大概小的数据范例

b:假如大概,声明表列为NOT NULL.

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.１:删除不必要的括号:

　（（a AND b) AND c OR (((a AND b) AND (a AND d))))>(a AND b AND c) OR (a AND b AND c AND d)

3.2:利用常数

　（a<b AND b=c) AND a=100 > b>5 AND b=c AND a=5

3.3:删除常数条件

（b>=5 AND b=5) OR (b=6 AND 5=5) OR (b=100 AND 2=3) > b=5 OR b=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中　a left join b按以下方法实现

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_name IS NULL测试(column_name为NOT NULL列)．那么,mysql在它已经找到了匹配left　join条件的一行后,将终止在更多的行后探求

５:优化limit

a:假如用limit只挑选一行,当mysql需求扫描整个表时,它的作用相当于索引

b:假如利用limit＃与order　by,mysql假如找到了第＃行,将完毕排序,而不会排序正个表

c:当结合limit＃和distinct时,mysql假如找到了第＃行,将终止

d:只要mysql已经发送了第一个＃行到客户,mysql将放弃查询

e:limit 0一向会很快的返回一个空调集．

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语句那样多次存入要快．假如能用一个单个语句插入全部的行,锁定就不需求．锁定也降低衔接的整体时间．但是对某些线程最大等候时间将上升．比方:

thread 1 does 1000 inserts

thread 2,3 and 4 does 1 insert

thread 5 does 1000 inserts

假如不利用锁定,２,３,４将在１和５之前完成．假如利用锁定,２,３,４,将大概在１和５之后完成．但是整体时间应当快４０％．因为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表有大概加快多个表联合,用一样数据大概要快好几倍时间．

select tablename.a,tablename2.a from tablename,tablanem2,tablename3 where

tablaneme.a=tablename2.a and tablename2.a=tablename3.a and tablename2.c!=0;

为了加快它,可以用tablename2和tablename3的联合成立一个暂时表,因为用相同列（tablename1.a）查找．

ＣＲＥＡＴＥ　ＴＥＭＰＯＲＡＲＹ　ＴＡＢＬＥ　test TYPE=HEAP

SELECT

tablename2.a as a2,tablename3.a as a3

FROM

tablenam2,tablename3

WHERE

tablename2.a=tablename3.a and c=0;

SELECT tablename.a,test.a3 from tablename,test where tablename.a=test.a1;

SELECT tablename.a,test,a3,from tablename,test where tablename.a=test.a1 and ....;

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

of char columns)+packed size of numeric columns+length of strings +(number of

NULL columns+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不能算出在２个值之间大约有多少行．这被优化器利用是用来决意利用哪个索引的,但是在另一个方面乃至不需求磁盘寻道
　　以上是“mysql数据库优化[MySQL防范]”的内容，如果你对以上该文章内容感兴趣，你可以看看七道奇为您推荐以下文章：

Windows 搭配 IIS7 PHP MySQL 环境

mysql Out of memory (Needed 16777224 bytes)的错误办理

mysql提醒[Warning] Invalid (old?) table or database name问题的办理办法

mysql启用skip-name-resolve情势时呈现Warning的处理办法

MySQL Order By语法介绍

<b>MySQL ORDER BY 的实现解析</b>

mysql数据库插入速度和读取速度的调整记录

MySQL Order By索引优化办法

MySQL Order By用法分享

mysql #1062 –Duplicate entry ''1'' for key ''PRIMARY''

MySQL Order By Rand()效率解析

本文地址：