MySQ概述及优化

,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,MySQL,概述及优化简介,2009-12-05,演讲人：张秀程,MySQL,概述,MySQL,优化,MySQL,核心技术手册,简介,目录索引,MySQL,概述,什么是,MySQL,MySQL,是一个小型关系型数据库管理系统，开发者为瑞典,MySQL AB,公司。目前,MySQL,被广泛地应用在,Internet,上的中小型网站中。由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，许多中小型网站为了降低网站总体拥有成本而选择了,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,架构,MySQL,存储引擎比较,MyISAM vs InnoDB,MyISAM,是,MySQl,的默认类型,基于传统的,ISAM,类型，它是存储记录和文件的标准方法。与其他存储引擎比较，它具有检查和修复表格的大多数工具。,MyISAM,表可以被压缩,并支持全文搜索。它们不是事务安全的，而且也不支持外键。,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,MyISAM vs InnoDB,InnoDB,：最受欢迎的存储引擎 ，支持事物处理 ，,ACID,、外键、日志修复等。,InnoDB,表的速度很快。如果需要一个事务安全的存储引擎或者是需要大量并发的,INSERT,或,UPDATE,，则应该使用,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,有多数据库特性、更良好的数据存储性能和查询性能。,NDB,支持事务，用于集群（,cluster,），实现高可用，但性能仍欠佳。,MySQL,优化,主要内容,为什么要优化,如何找到软肋,如何优化,为什么要优化,老板要求,客户要求,机器发飙,自己主动,机器发飙,网站打开速度很慢，但,web,服务器负载较低，或打开静态页面的速度很快，这就有可能是数据库的问题,load average 5,Iowait 10,vmstat procs(r b),值较高,top,中,CPU,的,idle,很小，,sys,或,wait,较高,服务器的,swap,严重,mysql,的内存命中率很低，如,myisam_key_read_hit_ratio,或,innodb_buffer_hit_ratio,较低,瓶颈定位,netstat,等系统级工具,explain,slow query,show status/show processlist/show engine innodb status,其他，例如,mysqlreport,，,profiling,等。,瓶颈定位,- Explain,Explain,都能提供何种信息？,表的读取顺序、每个表都是如何读取的？,可能用到哪些索引，实际使用了哪些索引？,表间的引用关系,查询优化器从每个表中预计读取的记录数,其他信息，如是否使用了内存表，是否引发排序等,瓶颈定位,续,将,LONG_QUERY_TIME,设为最小值；建议打一下,patch,，单位可以设为微秒，并可查看详细执行计划,执行,SHOW GLOBAL STATUS/PROCESSLIST,查看当前运行状态，从结果中发现可能的问题,执行,SHOW ENGINE INNODB STATUS,以查看,INNODB,的状态,定期检查冗余的索引以及没有使用索引的慢查询,利用,mysqlreport,产生可读性更强的报告,利用,Profiling,剖析一次查询瓶颈所在,系统（硬件、软件及网络）优化,配置优化（,MySQL,参数设置）,应用程序及架构,优化,MySQL,优化方式,通常硬件（,CPU,、内存、磁盘、网络等,）是优化的最佳入口，,使用好的硬件，更快的硬盘、大内存、多核,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,用来存放,MyISAM,索引缓冲的大小,建议,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,之间,配置优化,应用程序及架构优化,垂直（水平）切分服务器,/,数据库、表,开启,MySQL,复制，实现读、写分离,在复制的基础上，增加负载均衡,采用集群（,Cluster,）,+,复制,(MySQL 6.0+),经常更新的表，可以将其分离成父表及子表,(,内存表,),用统计表保存定时统计结果，而不是在大表上直接统计,编写存储过程或函数来代替大量的外部应用程序交互,设计合理的数据表结构：适当的数据冗余,对数据表建立合适有效的数据库,索引,数据,查询,：编写简洁高效的,SQL,语句,应用优化,应用优化方式,应用优化,-,续,确保索引合理利用，尽量使用复合索引,适当加大查询缓存,(query cache),，尽量减少交互次数,尽量使用固定格式的,SQL,语句，查询语句中少用运算或函数,缩短每个事务,使用适当的字段类型；适当的长度，有需要的时候再扩充,分解复杂查询为多个小查询,字符型字段采用前缀索引,应用优化,表结构设计原则,选择合适的,数据类型,：如果能够定长尽量定长,不要使用无法加,索引,的类型作为关键字段，比如,text,类型,为了避免联表查询，有时候可以适当的数据冗余，比如 邮箱、姓名这些不容易更改的数据,选择合适的存储引擎,，有时候,MyISAM,适合，有时候,InnoDB,适合,为保证,查询性能,，最好每个表都建立有,auto_increment,字段， 建立合适的数据库索引,最好给每个字段都设定,default,值,应用优化,索引建立原则,一般针对数据分散的关键字进行建立索引，比如,ID,、,QQ,， 像性别、状态值等等建立索引没有意义,尽量使用短索引，一般对,int,、,char/varchar,、,date/time,等 类型的字段建立索引,需要的时候建立联合索引，但是要注意查询,SQL,语句的编写,谨慎建立,unique,类型的索引（唯一索引）,一般建议每条记录最好有一个能快速定位的独一无二的 唯一标示（索引）,不要过度索引，单表建立的索引不要超过,5,个，否则更新索 引将很耗时,编写高效的,SQL,能够快速缩小结果集的,WHERE,条件写在前面，如果有恒量条件， 也尽量放在前面,尽量避免使用,GROUP BY,、,DISTINCT,、,OR,、,IN,等语句的使用， 避免使用联表查询和子查询，因为将使执行效率大大下降,能够使用索引的字段尽量进行有效的合理排列，如果使用了 联合索引，请注意提取字段的前后顺序,如果在,SQL,里使用了,MySQL,部分自带函数，索引将失效，同时将无法 使用,MySQL,的,Query Cache,，比如,LEFT(), SUBSTR(), TO_DAYS() DATE_FORMAT(),等，如果使用了,OR,或,IN,，索引也将失效,使用,Explain,语句来帮助改进我们的,SQL,语句,MySQL,书籍,/,网站分享,MySQL,核心技术手册（第二版）,高性能,MySQL(,第二版,),MySQL,官方手册,: Performance Blog,: