![瓶颈定位 – 续 将 LONG_QUERY_TIME 设为最小值；建议打一下 patch ，单位可以设为微秒，并可查看详细执行计划 执行 SHOW [GLOBAL] STATUS/PROCESSLIST 查看当前运行状态，从结果中发现可能的问题 执行 SHOW ENGINE INNODB STATUS 以查看 INNODB 的状态 定期检查冗余的索引以及没有使用索引的慢查询 利用 mysqlreport 产生可读性更强的报告 利用 Profiling 剖析一次查询瓶颈所在](https://image.slidesharecdn.com/sina-mysq-100505201958-phpapp01/85/Sina-my-sq-24-320.jpg)
![结束 作者邮箱： [email_address]](https://image.slidesharecdn.com/sina-mysq-100505201958-phpapp01/85/Sina-my-sq-41-320.jpg)
Sina my sq概述及优化
- 1. MySQL 概述及优化介绍 2009-12-05 演讲人：张秀程
- 2. MySQL 概述 MySQL 优化 《 MySQL 核心技术手册》介绍 目录索引
- 3. MySQL 概述
- 4. 什么是 MySQL MySQL 是一个小型关系型数据库管理系统，开发者为瑞典 MySQL AB 公司。目前 MySQL 被广泛地应用在 Internet 上的中小型网站中。由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，许多中小型网站为了降低网站总体拥有成本而选择了 MySQL 作为网站数据库。 MySQL 官方网站： http://www.mysql.com
- 5. MySQL 历史 1979 年， 报表工具，数据引擎 1996 年， MySQL 1.0 (3.11.1) 发布，支持 SQL 2000 年，成立 MySQL AB 公司 2008 年 1 月， Sun 公司以 10 亿美元收购 MySQL AB 公司 2009 年 4 月， Oracle 公司以 74 亿美元收购 Sun 公司
- 6. MySQL 架构
- 7. MySQL 存储引擎比较
- 8. MyISAM vs InnoDB MyISAM 是 MySQl 的默认类型 , 基于传统的 ISAM 类型，它是存储记录和文件的标准方法。与其他存储引擎比较，它具有检查和修复表格的大多数工具。 MyISAM 表可以被压缩 , 并支持全文搜索。它们不是事务安全的，而且也不支持外键。
- 9. MyISAM 特点 MyISAM vs InnoDB 数据存储方式简单，使用 B-tree 进行索引 使用三个文件定义一个表： .MYI .MYD .frm 少碎片、支持大文件、能够进行索引压缩 二进制层次的文件可以移植 (Linux ? Windows) 访问速度飞快，是所有 MySQL 文件引擎中速度最快的 不支持一些数据库特性，比如 事务、外键约束等 Table level lock ，性能稍差，更适合读取多的操作 表数据容量有限，一般建议单表数据量介于 50w – 200w 增删查改以后要使用 myisamchk 检查优化表
- 10. MyISAM 存储结构 MyISAM vs InnoDB
- 11. MyISAM 索引结构 MyISAM vs InnoDB
- 12. MyISAM vs InnoDB InnoDB ：最受欢迎的存储引擎 ，支持事物处理 ， ACID 、外键、日志修复等。 InnoDB 表的速度很快。如果需要一个事务安全的存储引擎或者是需要大量并发的 INSERT 或 UPDATE ，则应该使用 InnoDB 表。
- 13. InnoDB 特性 MyISAM vs InnoDB 使用 Table Space 的方式来进行数据存储 ( ibdata1, ib_logfile0 ) 支持 事务、外键约束等数据库特性 Rows level lock , 读写性能都非常优秀 能够承载大数据量的存储和访问 拥有自己独立的缓冲池，能够缓存数据和索引 在关闭自动提交的情况下，与 MyISAM 引擎速度差异不大
- 14. InnoDB 索引结构 MyISAM vs InnoDB
- 15. MyISAM vs InnoDB 性能测试 数据量 / 单位 : 万 MyISAM InnoDB 备注 : my.cnf 特殊选项 插入： 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=0 innodb_buffer_pool_size=256M
- 16. 测试结果 MyISAM vs InnoDB 性能测试 可以看出在 MySQL 5.0 里面， MyISAM 和 InnoDB 存储引擎性能差别并不是很大，针对 InnoDB 来说，影响性能的主要是 innodb_flush_log_at_trx_commit 这个选项，如果设置为 1 的话，那么每次插入数据的时候都会自动提交，导致性能急剧下降，应该是跟刷新日志有关系，设置为 0 效率能够看到明显提升，当然，同样你可以在 SQL 中提交 “ SET AUTOCOMMIT = 0 ” 来设置达到好的性能。 同时也可以看出值得使用 InnoDB 来替代 MyISAM 引擎来进行开发，毕竟 InnoDB 有多数据库特性、更良好的数据存储性能和查询性能。
- 17. NDB 支持事务，用于集群（ cluster ），实现高可用，但性能仍欠佳。
- 18. MySQL 优化
- 19. 主要内容 为什么要优化 如何找到软肋 如何优化
- 20. 为什么要优化 老板要求 客户要求 机器发飙 自己主动
- 21. 机器发飙 网站打开速度很慢，但 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 较低
- 22. 瓶颈定位 netstat 等系统级工具 explain slow query show status/show processlist/show engine innodb status 其他，例如 mysqlreport ， profiling 等。
- 23. 瓶颈定位 - Explain Explain 都能提供何种信息？ 表的读取顺序、每个表都是如何读取的？ 可能用到哪些索引，实际使用了哪些索引？ 表间的引用关系 查询优化器从每个表中预计读取的记录数 其他信息，如是否使用了内存表，是否引发排序等
- 24. 瓶颈定位 – 续 将 LONG_QUERY_TIME 设为最小值；建议打一下 patch ，单位可以设为微秒，并可查看详细执行计划 执行 SHOW [GLOBAL] STATUS/PROCESSLIST 查看当前运行状态，从结果中发现可能的问题 执行 SHOW ENGINE INNODB STATUS 以查看 INNODB 的状态 定期检查冗余的索引以及没有使用索引的慢查询 利用 mysqlreport 产生可读性更强的报告 利用 Profiling 剖析一次查询瓶颈所在
- 25. 系统（硬件、软件及网络）优化 配置优化（ MySQL 参数设置） 应用程序及架构 优化 MySQL 优化方式
- 26. 通常硬件（ CPU 、内存、磁盘、网络等 ）是优化的最佳入口， 使用好的硬件，更快的硬盘、大内存、多核 CPU ，专业的存储服务器（ NAS 、 SAN ） 客户端与服务器在一个高速的局域网内 通常，新版本的效率不如旧版本，不过可以利用新版本的新功能从另一方面得到性能上的提升 设计合理的架构，如果访问 MySQL 比较 频繁，建议应用 Master/Slave 读写分离；数据库分表、数据库切片（分布式），也考虑使用相应缓存服务帮助 MySQL 缓解访问压力 系统优化
- 27. 配置合理的 MySQL 服务器，尽量在应用本身达到一个 MySQL 最合理的使用 针对 MyISAM 或 InnoDB 引擎进行不同定制性配置 针对不同的应用情况进行合理配置 针对 my.cnf 进行配置，后面设置是针对内存为 2G 的服务器进行的合理设置 配置优化 MySQL 配置原则
- 28. 配置优化 公共选项 选项 缺省值 推荐值 说明 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 个文件描述符。
- 29. 配置优化 MyISAM 选项 选项 缺省值 推荐值 说明 key_buffer_size 8M 256M 用来存放 MyISAM 索引缓冲的大小 , 建议 128M 以上 ，不要大于内存的 30% read_buffer_size 128K 16M 用来 存放 MyISAM 表全表扫描的缓冲大小 . 为从数据表顺序读取数据的读操作保留的缓存区的长度 myisam_sort_buffer_size 16M 128M 设置 , 恢复 , 修改表的时候使用的缓冲大小，值不要设的太大
- 30. 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 之间
- 31. 应用程序及架构优化 垂直（水平）切分服务器 / 数据库、表 开启 MySQL 复制，实现读、写分离 在复制的基础上，增加负载均衡 采用集群（ Cluster ） + 复制 (MySQL 6.0+) 经常更新的表，可以将其分离成父表及子表 ( 内存表 ) 用统计表保存定时统计结果，而不是在大表上直接统计 编写存储过程或函数来代替大量的外部应用程序交互
- 32. 设计合理的数据表结构：适当的数据冗余 对数据表建立合适有效的数据库 索引 数据 查询 ：编写简洁高效的 SQL 语句 应用优化 应用优化方式
- 33. 应用优化 - 续 确保索引合理利用，尽量使用复合索引 适当加大查询缓存 (query cache) ，尽量减少交互次数 尽量使用固定格式的 SQL 语句，查询语句中少用运算或函数 缩短每个事务 使用适当的字段类型；适当的长度，有需要的时候再扩充 分解复杂查询为多个小查询 字符型字段采用前缀索引
- 34. 应用优化 表结构设计原则 选择合适的 数据类型 ：如果能够定长尽量定长 不要使用无法加 索引 的类型作为关键字段，比如 text 类型 为了避免联表查询，有时候可以适当的数据冗余，比如 邮箱、姓名这些不容易更改的数据 选择合适的存储引擎 ，有时候 MyISAM 适合，有时候 InnoDB 适合 为保证 查询性能 ，最好每个表都建立有 auto_increment 字段， 建立合适的数据库索引 最好给每个字段都设定 default 值
- 35. 应用优化 索引建立原则 一般针对数据分散的关键字进行建立索引，比如 ID 、 QQ ， 像性别、状态值等等建立索引没有意义 尽量使用短索引，一般对 int 、 char/varchar 、 date/time 等 类型的字段建立索引 需要的时候建立联合索引，但是要注意查询 SQL 语句的编写 谨慎建立 unique 类型的索引（唯一索引） 一般建议每条记录最好有一个能快速定位的独一无二的 唯一标示（索引） 不要过度索引，单表建立的索引不要超过 5 个，否则更新索 引将很耗时
- 36. 编写高效的 SQL 能够快速缩小结果集的 WHERE 条件写在前面，如果有恒量条件， 也尽量放在前面 尽量避免使用 GROUP BY 、 DISTINCT 、 OR 、 IN 等语句的使用， 避免使用联表查询和子查询，因为将使执行效率大大下降 能够使用索引的字段尽量进行有效的合理排列，如果使用了 联合索引，请注意提取字段的前后顺序 如果在 SQL 里使用了 MySQL 部分自带函数，索引将失效，同时将无法 使用 MySQL 的 Query Cache ，比如 LEFT(), SUBSTR(), TO_DAYS() DATE_FORMAT(), 等，如果使用了 OR 或 IN ，索引也将失效 使用 Explain 语句来帮助改进我们的 SQL 语句
- 37. MySQL 书籍 / 网站分享 《MySQL 核心技术手册（第二版） 》 《 高性能 MySQL( 第二版 )》 MySQL 官方手册 :http://dev.mysql.com/doc/refman/5.1/en/ MySQL Performance Blog :http://www.mysqlperformanceblog.com/
- 39. 基本内容 《 MySQL 核心技术手册》这本书，该书在内容编排上首先从 MySQL 的历史讲起，介绍了 MySQL 的价值、使用许可协议及邮件列表和相关参考书籍，还介绍了 MySQL 在不同操作系统上地安装、配置以及测试方法，如何创建数据库，以及如何操纵表中的数据。紧接着介绍了 MySQL 的各种语句和函数，最后介绍了如何使用 C 、 Perl 和 PHP 语言创建应用程序。本书是为 MySQL 用户、数据库管理员和开发人员准备的一本综合性的参考书。它包含了 MySQL （包括 MySQL5.0 和 5.1 版本的详尽信息）。
- 40. 目标 本书的目标是，帮助广大读者在自己的操作系统平台上学习如何最好的配置和使用 MySQL 以及进行程序开发。无论读者是 MySQL 的新手，还是有着多年数据库管理经验的数据库管理员，本书都是一本及有价值的参考书，相信本书必将会成为读者案头的实用的技术参考手册。
- 42. ?
Editor's Notes
- #31: 注意 ：有些选项是分配给每个线程的，不能将其设置得太大。 Innodb_ log_buffer_ size 不宜设置过大，如果事务量相对较大 , 则可以考虑设置得稍微大些。 mysql 自身的 query cache 效率一般，可以采用 memcached 来补充。