MySQ概述及优化.ppt

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)发布，支持SQL2000年，成立 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，性能稍差，更适合读取多的操作 表数据容量有限，一般建议单表数据量介于 50w200w 增删查改以后要使用 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 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 5Iowait 10vmstat procs(r b)值较高top中CPU的idle很小，sys或wait较高服务器的swap严重mysql的内存命中率很低，如 myisam_key_read_hit_ratio或innodb_buffer_hit_ratio较低,瓶颈定位,netstat 等系统级工具explainslow queryshow 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配置原则,配置优化,公共选项,配置优化,MyISAM 选项,InnoDB 选项,配置优化,应用程序及架构优化,垂直（水平）切分服务器/数据库、表开启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官方手册MySQL Performance Blog,MySQL核心技术手册简介,基本内容,MySQL核心技术手册这本书，该书在内容编排上首先从MySQL的历史讲起，介绍了MySQL的价值、使用许可协议及邮件列表和相关参考书籍，还介绍了MySQL在不同操作系统上地安装、配置以及测试方法，如何创建数据库，以及如何操纵表中的数据。紧接着介绍了MySQL的各种语句和函数，最后介绍了如何使用C、Perl和PHP语言创建应用程序。本书是为MySQL用户、数据库管理员和开发人员准备的一本综合性的参考书。它包含了MySQL（包括MySQL5.0和5.1版本的详尽信息）。,目标,本书的目标是，帮助广大读者在自己的操作系统平台上学习如何最好的配置和使用MySQL以及进行程序开发。无论读者是MySQL的新手，还是有着多年数据库管理经验的数据库管理员，本书都是一本及有价值的参考书，相信本书必将会成为读者案头的实用的技术参考手册。,结束,作者邮箱：,