MySQL的优化艺术Word文档下载推荐.docx
- 文档编号:21205008
- 上传时间:2023-01-28
- 格式:DOCX
- 页数:11
- 大小:23.01KB
MySQL的优化艺术Word文档下载推荐.docx
《MySQL的优化艺术Word文档下载推荐.docx》由会员分享,可在线阅读,更多相关《MySQL的优化艺术Word文档下载推荐.docx(11页珍藏版)》请在冰豆网上搜索。
5)Select,sort优化措施比较少。
6)适用于OLTP应用。
非标准化的特点:
1)在一张表中存储很多数据,数据冗余。
2)更新数据开销很大,更新一个属性可能会更新很多表,很多记录。
3)在删除数据是有可能丢失数据。
4)Select,order有很多优化的选择。
5)适用于DSS应用。
标准化和非标准化都有各自的优缺点,通常在一个数据库设计中可以混合使用,一部分表格标准化,一部分表格保留一些冗余数据:
1)对OLTP使用标准化,对DSS使用非标准化
2)使用物化视图。
MySQL不直接支持该数据库特性,但是可以用MyISAM表代替。
3)冗余一些数据在表格中,例如将ref_id和name存在同一张表中。
但是要注重更新问题。
4)对于一些简单的对象,直接使用value作为建。
例如IPaddress等
5)ReferencebyPRIMARY/UNIQUEKEY。
MySQL可以优化这种操作,例如:
java代码
1.selectcity_name
2.fromcity,state
3.wherestate_id=state.idandstate.code=‘CA’”convertedto“selectcity_namefromcitywherestate_id=12
2.1.2数据类型
最基本的优化之一就是使表在磁盘上占据的空间尽可能小。
这能带来性能非常大的提升,因为数据小,磁盘读入较快,并且在查询过程中表内容被处理所占用的内存更少。
同时,在更小的列上建索引,索引也会占用更少的资源。
可以使用下面的技术可以使表的性能更好并且使存储空间最小:
1)使用正确合适的类型,不要将数字存储为字符串。
2)尽可能地使用最有效(最小)的数据类型。
MySQL有很多节省磁盘空间和内存的专业化类型。
3)尽可能使用较小的整数类型使表更小。
例如,MEDIUMINT经常比INT好一些,因为MEDIUMINT列使用的空间要少25%。
4)假如可能,声明列为NOTNULL。
它使任何事情更快而且每列可以节省一位。
注重假如在应用程序中确实需要NULL,应该毫无疑问使用它,只是避免默认地在所有列上有它。
5)对于MyISAM表,假如没有任何变长列(VARCHAR、TEXT或BLOB列),使用固定尺寸的记录格式。
这比较快但是不幸地可能会浪费一些空间。
即使你已经用CREATE选项让VARCHAR列ROW_FORMAT=fixed,也可以提示想使用固定长度的行。
6)使用samplecharacterset,例如latin1。
尽量少使用utf-8,因为utf-8占用的空间是latin1的3倍。
可以在不需要使用utf-8的字段上面使用latin1,例如mail,url等。
2.1.3索引
所有MySQL列类型可以被索引。
对相关列使用索引是提高SELECT操作性能的最佳途径。
使用索引应该注重以下几点:
1)MySQL只会使用前缀,例如key(a,b)…whereb=5将使用不到索引。
2)要选择性的使用索引。
在变化很少的列上使用索引并不是很好,例如性别列。
3)在Unique列上定义Uniqueindex。
4)避免建立使用不到的索引。
5)在Btreeindex中(InnoDB使用Btree),可以在需要排序的列上建立索引。
6)避免反复的索引。
7)避免在已有索引的前缀上建立索引。
例如:
假如存在index(a,b)则去掉index(a)。
8)控制单个索引的长度。
使用key(name(8))在数据的前面几个字符建立索引。
9)越是短的键值越好,最好使用integer。
10)在查询中要使用到索引(使用explain查看),可以减少读磁盘的次数,加速读取数据。
11)相近的键值比随机好。
Auto_increment就比uuid好。
12)Optimizetable可以压缩和排序index,注重不要频繁运行。
13)Analyzetable可以更新数据。
2.2Designingqueries
查询语句的优化是一个Casebycase的问题,不同的sql有不同的优化方案,在这里我只列出一些通用的技巧。
1)在有index的情况下,尽量保证查询使用了正确的index。
可以使用EXPLAINselect…查看结果,分析查询。
2)查询时使用匹配的类型。
例如select*fromawhereid=5,假如这里id是字符类型,同时有index,这条查询则使用不到index,会做全表扫描,速度会很慢。
正确的应该是…whereid=”5”,加上引号表明类型是字符。
3)使用--log-slow-queries–long-query-time=2查看查询比较慢的语句。
然后使用explain分析查询,做出优化。
3.服务器端优化
3.1MySQL安装
MySQL有很多发行版本,最好使用MySQLAB发布的二进制版本。
也可以下载源代码进行编译安装,但是编译器和类库的一些bug可能会使编译完成的MySQL存在潜在的问题。
假如安装MySQL的服务器使用的是Intel公司的处理器,可以使用intelc++编译的版本,在LinuxWorld2005的一篇PPT中提到,使用intelC++编译器编译的MySQL查询速度比正常版本快30%左右。
Intelc++编译版本可以在MySQL官方网站下载。
3.2服务器设置优化
MySQL默认的设置性能很差,所以要做一些参数的调整。
这一节介绍一些通用的参数调整,不涉及具体的存储引擎(主要指MyISAM,InnoDB,相关优化在4中介绍)。
--character-set:
假如是单一语言使用简单的characterset例如latin1。
尽量少用Utf-8,utf-8占用空间较多。
--memlock:
锁定MySQL只能运行在内存中,避免swapping,但是假如内存不够时有可能出现错误。
--max_allowed_packet:
要足够大,以适应比较大的SQL查询,对性能没有太大影响,主要是避免出现packet错误。
--max_connections:
server答应的最大连接。
太大的话会出现outofmemory。
--table_cache:
MySQL在同一时间保持打开的table的数量。
打开table开销比较大。
一般设置为512。
--query_cache_size:
用于缓存查询的内存大小。
--datadir:
mysql存放数据的根目录,和安装文件分开在不同的磁盘可以提高一点性能。
4.存储引擎优化
MySQL支持不同的存储引擎,主要使用的有MyISAM和InnoDB。
4.1MyISAM
MyISAM管理非事务表。
它提供高速存储和检索,以及全文搜索能力。
MyISAM在所有MySQL配置里被支持,它是默认的存储引擎,除非配置MySQL默认使用另外一个引擎。
4.1.1MyISAM特性
4.1.1.1MyISAMProperties
1)不支持事务,宕机会破坏表
2)使用较小的内存和磁盘空间
3)基于表的锁,并发更新数据会出现严重性能问题
4)MySQL只缓存Index,数据由OS缓存
4.1.1.2TypicalMyISAMusages
1)日志系统
2)只读或者绝大部分是读操作的应用
3)全表扫描
4)批量导入数据
5)没有事务的低并发读/写
4.1.2MyISAM优化要点
1)声明列为NOTNULL,可以减少磁盘存储。
2)使用optimizetable做碎片整理,回收空闲空间。
注重仅仅在非常大的数据变化后运行。
3)Deleting/updating/adding大量数据的时候禁止使用index。
使用ALTERTABLEtDISABLEKEYS。
4)设置myisam_max_[extra]_sort_file_size足够大,可以显著提高repairtable的速度。
4.1.3MyISAMTableLocks
1)避免并发insert,update。
2)可以使用insertdelayed,但是有可能丢失数据。
3)优化查询语句。
4)水平分区。
5)垂直分区。
6)假如都不起作用,使用InnoDB。
4.1.4MyISAMKeyCache
1)设置key_buffer_sizevariable。
MyISAN最主要的cache设置,用于缓存MyISAM表格的index数据,该参数只对MyISAM有影响。
通常在只使用MyISAM的Server中设置25-33%的内存大小。
2)可以使用几个不同的KeyCaches(对一些hotdata)。
a)SETGLOBALtest.key_buffer_size=512*1024;
b)CACHEINDEXt1.i1,t2.i1,t3INtest;
2)Preloadindex到Cache中可以提高查询速度。
因为preloadingindex是顺序的,所以非常快。
a)LOADINDEXINTOCACHEt1,t2IGNORELEAVES;
4.2InnoDB
InnoDB给MySQL提供了具有提交,回滚和崩溃恢复能力的事务安全(ACID兼容)存储引擎。
InnoDB提供rowlevellock,并且也在SELECT语句提供一个Oracle风格一致的非锁定读。
这些特色增加了多用户部署和性能。
没有在InnoDB中扩大锁定的需要,因为在InnoDB中rowlevellock适合非常小的空间。
InnoDB也支持FOREIGNKEY约束。
在SQL查询中,你可以自由地将InnoDB类型的表与其它MySQL的表的类型混合起来,甚至在同一个查询中也可以混合。
InnoDB是为在处理巨大数据量时获得最大性能而设计的。
它的CPU使用效率非常高。
InnoDB存储引擎已经完全与MySQL服务器整合,InnoDB存储引擎为在内存中缓存数据和索引而维持它自己的缓冲池。
InnoDB存储它的表&索引在一个表空间中,表空间可以包含数个文件(或原始磁盘分区)。
这与MyISAM表不同,比如在MyISAM表中每个表被存在分离的文件中。
InnoDB表可以是任何大小,即使在文件尺寸被限制为2GB的操作系统上。
许多需要高性能的大型数据库站点上使用了InnoDB引擎。
闻名的Internet新闻站点Slashdot.org运行在InnoDB上。
Mytrix,Inc.在InnoDB上存储超过1TB的数据,还有一些其它站点在InnoDB上处理平均每秒800次插入/更新的负荷。
4.2.1InnoDB特性
4.2.1.1InnoDBProperties
1)支持事务,ACID,外键。
2)Rowlevellocks。
3)支持不同的隔离级别。
4)和MyISAM相比需要较多的内存和磁盘空间。
5)没有键压缩。
6)数据和索引都缓存在内存hash表中。
4.2.1.2InnoDBGoodFor
1)需要事务的应用。
2)高并发的应用。
3)自动恢复。
4)较快速的基于主键的操作。
4.2.2InnoDB优化要点
1)尽量使用short,integer的主键。
2)Load/Insert数据时按主键顺序。
假如数据没有按主键排序,先排序然后再进行数据库操作。
3)在Load数据是为设置SETUNIQUE_CHECKS=0,SETFOREIGN_KEY_CHECKS=0,可以避免外键和唯一性约束检查的开销。
4)使用prefixkeys。
因为InnoDB没有key压缩功能。
4.2.3InnoDB服务器端设定
innodb_buffer_pool_size:
这是InnoDB最重要的设置,对InnoDB性能有决定性的影响。
默认的设置只有8M,所以默认的数据库设置下面InnoDB性能很差。
在只有InnoDB存储引擎的数据库服务器上面,可以设置60-80%的内存。
更精确一点,在内存容量答应的情况下面设置比InnoDBtablespaces大10%的内存大小。
innodb_data_file_path:
指定表数据和索引存储的空间,可以是一个或者多个文件。
最后一个数据文件必须是自动扩充的,也只有最后一个文件答应自动扩充。
这样,当空间用完后,自动扩充数据文件就会自动增长(以8MB为单位)以容纳额外的数据。
innodb_data_file_path=/disk1/ibdata1:
900M;
/disk2/ibdata2:
50M:
autoextend两个数据文件放在不同的磁盘上。
数据首先放在ibdata1中,当达到900M以后,数据就放在ibdata2中。
一旦达到50MB,ibdata2将以8MB为单位自动增长。
假如磁盘满了,需要在另外的磁盘上面增加一个数据文件。
innodb_autoextend_increment:
默认是8M,假如一次insert数据量比较多的话,可以适当增加.
innodb_data_home_dir:
放置表空间数据的目录,默认在mysql的数据目录,设置到和MySQL安装文件不同的分区可以提高性能。
innodb_log_file_size:
该参数决定了recoveryspeed。
太大的话recovery就会比较慢,太小了影响查询性能,一般取256M可以兼顾性能和recovery的速度
。
innodb_log_buffer_size:
磁盘速度是很慢的,直接将log写道磁盘会影响InnoDB的性能,该参数设定了logbuffer的大小,一般4M。
假如有大的blob操作,可以适当增大。
innodb_flush_logs_at_trx_commit=2:
该参数设定了事务提交时内存中log信息的处理。
1)=1时,在每个事务提交时,日志缓冲被写到日志文件,对日志文件做到磁盘操作的刷新。
TrulyACID。
速度慢。
2)=2时,在每个事务提交时,日志缓冲被写到文件,但不对日志文件做到磁盘操作的刷新。
只有操作系统崩溃或掉电才会删除最后一秒的事务,不然不会丢失事务。
3)=0时,日志缓冲每秒一次地被写到日志文件,并且对日志文件做到磁盘操作的刷新。
任何mysqld进程的崩溃会删除崩溃前最后一秒的事务
innodb_file_per_table:
可以存储每个InnoDB表和它的索引在它自己的文件中。
transaction-isolation=READ-COMITTED:
假如应用程序可以运行在READ-COMMITED隔离级别,做此设定会有一定的性能提升。
innodb_flush_method:
设置InnoDB同步IO的方式:
1)Default–使用fsync()。
2)O_SYNC以sync模式打开文件,通常比较慢。
3)O_DIRECT,在Linux上使用DirectIO。
可以显著提高速度,非凡是在RAID系统上。
避免额外的数据复制和doublebuffering(mysqlbuffering和OSbuffering)。
innodb_thread_concurrency:
InnoDBkernel最大的线程数。
1)最少设置为(num_disks+num_cpus)*2。
2)可以通过设置成1000来禁止这个限制
5.缓存
缓存有很多种,为应用程序加上适当的缓存策略会显著提高应用程序的性能。
由于应用缓存是一个比较大的话题,所以这一部分还需要进一步调研。
1、定期分析表和检查表
分析表的语法如下:
引用
ANALYZE[LOCAL|NO_WRITE_TO_BINLOG]TABLEtb1_name[,tbl_name]...
以上语句用于分析和存储表的要害字分布,分析的结果将可以使得系统得到精确的统计信息,使得SQL能够生成正确的执行计划。
假如用户感觉实际执行计划并不是预期的执行计划,执行一次分析表可能会解决问题。
在分析期间,使用一个读取锁定对表进行锁定。
这对于MyISAM,DBD和InnoDB表有作用。
例如分析一个数据表
analyzetabletable_name
检查表的语法如下:
CHECKTABLEtb1_name[,tbl_name]...[option]...option={QUICK|FAST|MEDIUM|EXTENDED|CHANGED}
检查表的作用是检查一个或多个表是否有错误,CHECKTABLE对MyISAM
和InnoDB表有作用,对于MyISAM表,要害字统计数据被更新
CHECKTABLE也可以检查视图是否有错误,比如在视图定义中被引用的表不存在。
2.定期优化表
优化表的语法如下:
OPTIMIZE[LOCAL|NO_WRITE_TO_BINLOG]TABLEtb1_name[,tbl_name]...
假如删除了表的一大部分,或者假如已经对含有可变长度行的表(含有VARCHAR、BLOB或TEXT列的表)进行更多更改,则应使用OPTIMIZETABLE命令来进行表优化。
这个命令可以将表中的空间碎片进行合并,并且可以消除由于删除或者更新造成的空间浪费,但OPTIMIZETABLE命令只对MyISAM、BDB和InnoDB表起作用。
optimizetabletable_name
注重:
analyze、check、optimize执行期间将对表进行锁定,因此一定注重要在数据库不繁忙的时候执行相关的操作。
常用的SQL优化
我们在开发的时候经常用到的SQL语句,无非是INSERT、GROUPBY等等。
对于这些SQL语句,我们怎么进行优化?
1.大批量插入数据
当用load命令导入数据的时候,适当的设置可以提高导入的速度。
对于MyISAM存储引擎的表,可以通过如下方式快速的导入大量的数据
ALTERTABLEtb1_nameDISABLEKEYS;
loadingthedata
ALTERTABLEtb1_nameENABLEKEYS;
DISABLEKEYS和ENABLEKEYS用来打开或者关闭MyISAM表非唯一索引的更新。
在导入大量的数据到一个非空的MyISAM表时,通过设置这两个命令,可以提高导入的效率。
对于导入大量的数据到一个空的MyISAM表时,默认就是先导入数据然后才创建索引的,索引不用进行设置。
loaddatainfile'
/home/mysql/text_txt'
intotabletext
对于InnoDB类型的表,这种方式不能提高导入数据的效率,但也有几种针对InnoDB类型的表进行优化的方式。
1.因为InnoDB类型的表式按照主键的顺序保存的,所以将导入的数据按照主键的顺序排序,可以有效提高导入数据的效率。
2.在导入数据前执行SETUNIQUE_CHECKS=0,关闭唯一性校验,在导入结束后执行SETUNIQUE_CHECKS=1,恢复唯一性校验,可以提高导入的效率。
3.假如应用使用自动提交的方式,建议在导入前执行SETAUTOCOMMIT=0,关闭自动提交,导入结束后执行SETAUTOCOMMIT=1,打开自动提交,也可以提高导入效率。
优化INSERT语句
当进行数据INSERT的时候,可以考虑采用以下几种方式进行优化
1.假如同时从一个客户插入很多行,尽量使用多个值表的INSERT语句,这种方式将大大缩短客户端与数据库的链接、关闭等消耗,使得效率比分开执行的单个INSERT语句快.
insertintotestvalues(1,2)
insertintotestvalues(3,4)
insertintotestvalues(5,6)
将上面三句改为:
insertintotestvalues(1,2),(3,4),(5,6)......
2.假如从不同客户插入很多行,能通过使用INSERTDELAYED语句得到更高的速度。
DELAYED的含义是让INSERT语句连忙执行,其实数据都被放在内存的队列中,并没有真正写入磁盘,这比每条语句分别插入要快得多;
LOW_PRIORITY刚好相反,在所有其他用户对表的读写完后才进行插入。
3.将索引文件和数据文件分在不同的磁盘上存放
4.假如进行批量插入,可以增加bulk_insert_buffer_size变量值的方法来提高速度,但是,这只能对于MyISAM表使用。
5.当从一个文本文件中装载一个表时,使用LOADDATAINFILE。
这通常比使用很多insert语句快20倍左右。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- MySQL 优化 艺术