mysql存储引擎介绍及优化.ppt

,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,MySQL,介绍和优化分享,2009-07,MySQL,基本介绍,MySQL,优化方式,MySQL,技巧分享,Q,&,A,目录索引,MySQL,基本介绍,什么是,MySQL,MySQL,是一个小型关系型数据库管理系统，开发者为瑞典,MySQL AB,公司。目前,MySQL,被广泛地应用在,Internet,上的中小型网站中。由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，许多中小型网站为了降低网站总体拥有成本而选择了,MySQL,作为网站数据库。,MySQL,官方网站：,谁在用,MySQL,MySQL,历史,1979,年，报表工具，数据引擎,1996,年，,MySQL 1.0(3.11.1),发布，支持,SQL,2000,年，成立,MySQL AB,公司,2008,年,1,月，,Sun,公司以,10,亿美元收购,MySQL AB,公司,2009,年,4,月，,Oracle,公司以,74,亿美元收购,Sun,公司,MySQL,里程碑,3.11.1 First public release,3.23,集成,Berkeley DB,支持事务，抽象出,Storage Engine,4.0,集成,InnoDB,4.1,重大改进，子查询、,unicode,、,c/s,通信协议,5.0 stored procedure,、,view,、,triggers,、,query optimizer,5.1 File NDB,、,record replication.,MySQL,架构,MySQL,存储引擎比较,MyISAM,特点,MyISAM vs InnoDB,数据存储方式简单，使用,B+Tree,进行索引,使用三个文件定义一个表：,.MYI .MYD .frm,少碎片、支持大文件、能够进行索引压缩,二进制层次的文件可以移植,(Linux,Windows),访问速度飞快，是所有,MySQL,文件引擎中速度最快的,不支持一些数据库特性，比如 事务、外键约束等,Table level lock,，性能稍差，更适合读取多的操作,表数据容量有限，一般建议单表数据量介于,50w,200w,增删查改以后要使用,myisamchk,检查优化表,MyISAM,存储结构,MyISAM vs InnoDB,MyISAM,索引结构,MyISAM vs InnoDB,InnoDB,特点,MyISAM vs InnoDB,使用,Table Space,的方式来进行数据存储,(,ibdata1,ib_logfile0,),支持 事务、外键约束等数据库特性,Rows level lock,读写性能都非常优秀,能够承载大数据量的存储和访问,拥有自己独立的缓冲池，能够缓存数据和索引,在关闭自动提交的情况下，与,MyISAM,引擎速度差异不大,InnoDB,数据结构,MyISAM vs InnoDB,MyISAM vs InnoDB,性能测试,数据量,/,单位,:,万,MyISAM,InnoDB,备注,:f,特殊选项,插入：,1w,3,秒,219,秒,innodb_flush_log_at_trx_commit=1,插入：,10w,29,秒,2092,秒,innodb_flush_log_at_trx_commit=1,插入：,100w,287,秒,N/A,innodb_flush_log_at_trx_commit=1,插入：,1w,3,秒,3,秒,innodb_flush_log_at_trx_commit=0,插入：,10w,30,秒,29,秒,innodb_flush_log_at_trx_commit=0,插入：,100w,273,秒,423,秒,innodb_flush_log_at_trx_commit=0,插入：,1w,N/A,3,秒,innodb_flush_log_at_trx_commit=0 innodb_buffer_pool_size=256M,插入：,10W,N/A,26,秒,innodb_flush_log_at_trx_commit=0 innodb_buffer_pool_size=256M,插入：,100W,N/A,379,秒,innodb_flush_log_at_trx_commit=0innodb_buffer_pool_size=256M,测试结果,MyISAM vs InnoDB,性能测试,可以看出在,MySQL 5.0,里面，,MyISAM,和,InnoDB,存储引擎性能差别并不是很大，针对,InnoDB,来说，影响性能的主要是,innodb_flush_log_at_trx_commit,这个选项，如果设置为,1,的话，那么每次插入数据的时候都会自动提交，导致性能急剧下降，应该是跟刷新日志有关系，设置为,0,效率能够看到明显提升，当然，同样你可以,SQL,中提交,“,SET AUTOCOMMIT=0,”,来设置达到好的性能。,同时也可以看出值得使用,InnoDB,来替代,MyISAM,引擎来进行开发，毕竟,InnoDB,有多数据库特性、更良好的数据存储性能和查询性能,MySQL,优化方式,系统优化：硬件、架构,服务优化,应用优化,MySQL,优化方式,使用好的硬件，更快的硬盘、大内存、多核,CPU,，专业的存储服务器（,NAS,、,SAN,）,设计合理架构，如果,MySQL,访问频繁，考虑,Master/Slave,读写分离；数据库分表、数据库切片（分布式），也考虑使用相应缓存服务帮助,MySQL,缓解访问压力,系统优化,配置合理的,MySQL,服务器，尽量在应用本身达到一个,MySQL,最合理的使用,针对,MyISAM,或,InnoDB,不同引擎进行不同定制性配置,针对不同的应用情况进行合理配置,针对,f,进行配置，后面设置是针对内存为,2G,的服务器进行的合理设置,服务优化,MySQL,配置原则,服务优化,公共选项,选项,缺省值,推荐值,说明,max_connections,100,1024,MySQL,服务器同时处理的数据库连接的最大数量,query_cache_size,0(,不打开）,16M,查询缓存区的最大长度，按照当前需求，一倍一倍增加，本选项比较重要,sort_buffer_size,512K,16M,每个线程的排序缓存大小，一般按照内存可以设置为,2M,以上，推荐是,16M,，该选项对排序,order by,，,group by,起作用,record_buffer,128K,16M,每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区，可以设置为,2M,以上,table_cache,64,512,为所有线程打开表的数量。增加该值能增加,mysqld,要求的文件描述符的数量。,MySQL,对每个唯一打开的表需要,2,个文件描述符。,服务优化,MyISAM,选项,选项,缺省值,推荐值,说明,key_buffer_size,8M,256M,用来存放索引区块的,缓存值,建议,128M,以上,，不要大于内存的,30%,read_buffer_size,128K,16M,用来做,MyISAM,表全表扫描的缓冲大小,.,为从数据表顺序读取数据的读操作保留的缓存区的长度,myisam_sort_buffer_size,16M,128M,设置,恢复,修改表的时候使用的缓冲大小，值不要设的太大,服务优化,InnoDB,选项,选项,缺省值,推荐值,说明,innodb_buffer_pool_size,32M,1G,InnoDB,使用一个缓冲池来保存索引和原始数据,这里你设置越大,你在存取表里面数据时所需要的磁盘,I/O,越少，一般是内存的一半，不超过,2G,，否则系统会崩溃，这个参数非常重要,innodb_additional_mem_pool_size,2M,128M,InnoDB,用来保存,metadata,信息,如果内存是,4G,，最好本值超过,200M,innodb_flush_log_at_trx_commit,1,0,0,代表日志只大约每秒写入日志文件并且日志文件刷新到磁盘,;1,为执行完没执行一条,SQL,马上,commit;2,代表日志写入日志文件在每次提交后,但是日志文件只有大约每秒才会刷新到磁盘上,.,对速度影响比较大，同时也关系数据完整性,innodb_log_file_size,8M,256M,在日志组中每个日志文件的大小,一般是,innodb_buffer_pool_size,的,25%,，官方推荐是,innodb_buffer_pool_size,的,40-50%,设置大一点来避免在日志文件覆写上不必要的缓冲池刷新行为,innodb_log_buffer_size,128K,8M,用来缓冲日志数据的缓冲区的大小,.,推荐是,8M,，官方推荐该值小于,16M,，最好是,1M-8M,之间,设计合理的数据表结构：适当的数据冗余,对数据表建立合适有效的数据库索引,数据查询：编写简洁高效的,SQL,语句,应用优化,应用优化方式,应用优化,表结构设计原则,选择合适的数据类型：如果能够定长尽量定长,不要使用无法加索引的类型作为关键字段，比如,text,类型,为了避免联表查询，有时候可以适当的数据冗余，比如 邮箱、姓名这些不容易更改的数据,选择合适的表引擎，有时候,MyISAM,适合，有时候,InnoDB,适合,为保证查询性能，最好每个表都建立有,auto_increment,字段，建立合适的数据库索引,最好给每个字段都设定,default,值,应用优化,索引建立原则,一般针对数据分散的关键字进行建立索引，比如,ID,、,QQ,，像性别、状态值等等建立索引没有意义,尽量使用短索引，一般对,int,、,char/varchar,、,date/time,等 类型的字段建立索引,需要的时候建立联合索引，但是要注意查询,SQL,语句的编写,谨慎建立,unique,类型的索引（唯一索引）,一般建议每条记录最好有一个能快速定位的独一无二定位的 唯一标示（索引）,不要过度索引，单表建立的索引不要超过,5,个，否则更新索 引将很耗时,应用优化,编写高效的,SQL,能够快速缩小结果集的,WHERE,条件写在前面，如果有恒量条件，也尽量放在前面,尽量避免使用,GROUP BY,、,DISTINCT,、,OR,、,IN,等语句的使用，避免使用联表查询和子查询，因为将使执行效率大大下降,能够使用索引的字段尽量进行有效的合理排列，如果使用了 联合索引，请注意提取字段的前后顺序,针对索引字段使用,=,=,=,IF NULL,和,BETWEEN,将会使用 索引，如果对某个索引字段进行,LIKE,查询，使用,LIKE%abc%,不能使用索引，使用,LIKE abc%,将能够使用索引,如果在,SQL,里使用了,MySQL,部分自带函数，索引将失效，同时将无法 使用,MySQL,的,Query Cache,，比如,LEFT(),SUBSTR(),TO_DAYS()DATE_FORMAT(),等，如果使用了,OR,或,IN,，索引也将失效,使用,Explain,语句来帮助改进我们的,SQL,语句,MySQL,技巧分享,MySQL,技巧分享,常用技巧,使用,Explain/DESC,来分析,SQL,的执行情况,使用,SHOW PROCESSLIST,来查看当前,MySQL,服务器线程 执行情况，是否锁表，查看相应的,SQL,语句,设置,f,中的,long-query-time,和,log-slow-queries,能够 记录服务器那些,SQL,执行速度比较慢,另外有用的几个查询：,SHOW VARIABLES,、,SHOW STATUS,、,SHOW ENGINES,使用,DESC TABLE xxx,来查看表结构，使用,SHOW INDEX FROM xxx,来查看表索引,使用,LOAD DATA,导入数据比,INSERT INTO,快多了,SELECT COUNT(*)FROM Tbl,在,InnoDB,中将会扫描全表,MyISAM,中则效率很高,MySQL,技巧分享,Explain,使用,语法：,EXPLAIN SELECT,select_options,Type:,类型，是否使用了索引还是全表扫描,const,eg_reg,ref,range,index,ALL Key:,实际使用上的索引是哪个字段,Ken_len:,真正使用了哪些索引，不为,NULL,的就是真实使用的索引,Ref:,显示了哪些字段或者常量被用来和,key,配合从表中查询记录出来,Rows:,显示了,MySQL,认为在查询中应该检索的记录数,Extra:,显示了查询中,MySQL,的附加信息，关心,Using filesort,和,Using temporary,，性能杀手,MySQL,技巧分享,索引实践,MySQL,技巧分享,函数和索引,MySQL,技巧分享,使用,UNION,来取代,IN,和,OR,原,SQL,：,select*from city where id in(1,3,4),或,select*from city where id=1 or id=3 or id=4,explain,结果：,id select_type table type possible_keys key key_len ref rows Extra 1,SIMPLE,city,ALL,PRIMARY,NULL NULL,NULL,5,Using where,修改后,SQL,：,SELECT*FROM city where id=1 UNION ALL SELECT*FROM city where id=3 UNION ALL SELECT*FROM city,explain,结果：,id,select_type table,type possible_keys key,key_len ref,rows Extra 1,PRIMARY,city,const PRIMARY,PRIMARY 4,const 1,2,UNION,city,const PRIMARY,PRIMARY 4,const 1,3,UNION,city,const PRIMARY,PRIMARY 4,const 1,NULL UNION RESULT ALL,NULL,NULL,NULL,NULL NULL,MySQL,技巧分享,MySQL Slow Log,分析工具,mysqldumpslow-mysql,官方提供的慢查询日志分析工具,mysqlsla-,推出的一款日志分析工具，功能 非常强大,mysql-explain-slow-log,德国工程师使用,Perl,开发的把,Slow Log,输出到屏幕，功能简单,mysql-log-filter,-Google code,上一个开源产品，报表简洁,myprofi,-,纯,PHP,开发的开源,log,查看工具，功能详细,MySQL,技巧分享,MySQL,优化网站,/,书籍分享,MySQL Performance Blog,MySQL,中文网,MySQL,性能调优与架构设计,深入浅出,MySQL,Q&A,结束,谢谢大家！,