MySQL性能调优最佳实践


2012/4/6 1 MySQL 性能优化最佳实践 简朝阳(sky000) Oracle ACE(Expertise: MySQL) 技术保障部 @麦包包 Blog:http://isky000.com Twitter:@sky000 Weibo:@简朝阳 About me DTCC2012DTCC2012 2012/4/6 2 找出瓶颈 设定目标 实施优化 确认结果 优化 MySQL 性能优化最佳实践 优化过程 DTCC2012DTCC2012 2012/4/6 3 瓶颈 存储容量 容量白菜价 2TB很普及了 MySQL 性能优化最佳实践 找出瓶颈 DTCC2012DTCC2012 2012/4/6 4 瓶颈 Network(IOPS/吞吐量) 存储容量 一般很难跑满 万兆已经很多 MySQL 性能优化最佳实践 找出瓶颈 DTCC2012DTCC2012 2012/4/6 5 瓶颈 DRAM Network(IOPS/吞吐量) 存储容量 Linux单机支持过百G 价格较之过去已大降 MySQL 性能优化最佳实践 找出瓶颈 DTCC2012DTCC2012 2012/4/6 6 瓶颈 CPU DRAM Network(IOPS/吞吐量) 存储容量 X86 Nehalem,SMP,NUMA 4路 PC Server 32核 >30% MySQL 性能优化最佳实践 找出瓶颈 DTCC2012DTCC2012 2012/4/6 7 瓶颈 IO (IOPS/吞吐量) CPU DRAM Network(IOPS/吞吐量) 存储容量 OLTP:iops OLAP:吞吐量 >60% 瓶颈在 IO SSD? MySQL 性能优化最佳实践 找出瓶颈 DTCC2012DTCC2012 2012/4/6 8 目标 设备能力 极限不可 能突破 MySQL 性能优化最佳实践 设定目标 DTCC2012DTCC2012 2012/4/6 9 目标 设备能力 业务需求 极限不可 能突破 一切以需 求为导向 MySQL 性能优化最佳实践 设定目标 DTCC2012DTCC2012 2012/4/6 10 目标 设备能力 业务需求 应用环境 极限不可 能突破 一切以需 求为导向 环境影响 可行性 MySQL 性能优化最佳实践 设定目标 DTCC2012DTCC2012 2012/4/6 11 对象 Params Engine Schema Index SQL 实施 MySQL 性能优化最佳实践 实施优化 OS Hardware MySQL DTCC2012DTCC2012 2012/4/6 12 对象 实施 MySQL 性能优化最佳实践 实施优化 方 法 „ 方法 Params Engine Schema Index SQL OS Hardware MySQL DTCC2012DTCC2012 2012/4/6 13 对象 实施 MySQL 性能优化最佳实践 实施优化 方 法 „ 方法 误 区 „ 误区 Params Engine Schema Index SQL OS Hardware MySQL DTCC2012DTCC2012 2012/4/6 14 对象 实施 MySQL 性能优化最佳实践 实施优化 方 法 „ 方法 误 区 „ 误区 经 验 „ 最佳实践 Params Engine Schema Index SQL OS Hardware MySQL DTCC2012DTCC2012 2012/4/6 15 背景 磁盘 转速,容量,接口 HDD: ~150 iops, < 200MB SSD: 10x ~ 1000x, < 400MB MySQL 性能优化最佳实践 实施优化 DTCC2012DTCC2012 2012/4/6 16 磁盘 MySQL 性能优化最佳实践 实施优化 CPU 主频,多核,超线程 SMP, NUMA, MPP 背景 DTCC2012DTCC2012 2012/4/6 17 磁盘 索引 ~ Balance Tree 缩短检索路径 有序 MySQL 性能优化最佳实践 实施优化 CPU 背景 DTCC2012DTCC2012 2012/4/6 18 磁盘 索引 MySQL 性能优化最佳实践 实施优化 CPU SQL 执行计划 如何获得:explain 如何分析:Docs 背景 DTCC2012DTCC2012 2012/4/6 19 磁盘 索引 MySQL 性能优化最佳实践 实施优化 CPU SQL 背景 MySQL 简单,轻型,开放 多线程,插件式 SQL+Storage Engine … DTCC2012DTCC2012 2012/4/6 20 磁盘 索引 MySQL 性能优化最佳实践 实施优化 CPU SQL 背景 MySQL 存储引擎 插件式,可自由更换 开放型,可 自行开发 多样性,特性不一 并存性,可并存使用 DTCC2012DTCC2012 2012/4/6 21 Hardware 方法 Disk Raid Card CPU … MySQL 性能优化最佳实践 • 提高磁盘转速 • 增加磁盘数量 • 选好磁盘接口 DTCC2012DTCC2012 2012/4/6 22 Hardware Disk Raid Card CPU … MySQL 性能优化最佳实践 • 容量越大越好? • FC磁盘一定比SAS盘快? • 磁盘 Cache 越大越好? 误区 DTCC2012DTCC2012 2012/4/6 23 Hardware Disk Raid Card CPU … MySQL 性能优化最佳实践 • OLTP: 小容量”高”转速 • OLAP: 大容量”低”转速(钱多可以高转速) • 磁盘数量尽可能多 • 有钱可以上 SSD( IO 瓶颈场景下) 最佳实践 DTCC2012DTCC2012 2012/4/6 24 Hardware Disk Raid Card CPU … MySQL 性能优化最佳实践 • OLTP: 小容量”高”转速 • OLAP: 大容量”低”转速(钱多可以高转速) • 磁盘数量尽可能多 • 有钱可以上 SSD( IO 瓶颈场景下) • 增加Raid卡Cache容量 • 提升 Cache 利用率 • 确保数据安全 方法 DTCC2012DTCC2012 2012/4/6 25 Hardware Disk Raid Card CPU … MySQL 性能优化最佳实践 • OLTP: 小容量”高”转速 • OLAP: 大容量”低”转速(钱多可以高转速) • 磁盘数量尽可能多 • 有钱可以上 SSD( IO 瓶颈场景下) • 读写都是用 Cache 提升效率? • Raid10一定比Raid5快? • 带电池的 Raid 卡数据一定安全? 误区 DTCC2012DTCC2012 2012/4/6 26 Hardware Disk Raid Card CPU … MySQL 性能优化最佳实践 • OLTP: 小容量”高”转速 • OLAP: 大容量”低”转速(钱多可以高转速) • 磁盘数量尽可能多 • 有钱可以上 SSD( IO 瓶颈场景下) • Cache 只供写使用,Direct 读取 • OLTP Raid10,Strip Size 参考DB • OLAP Raid5 • 关注 Raid 卡充放电带来的 Cache 失效 • 预读只对连续读有效,OLTP 关闭预读 最佳实践 DTCC2012DTCC2012 2012/4/6 27 Hardware Disk Raid Card CPU … MySQL 性能优化最佳实践 • OLTP: 小容量”高”转速 • OLAP: 大容量”低”转速(钱多可以高转速) • 磁盘数量尽可能多 • 有钱可以上 SSD( IO 瓶颈场景下) • Cache 只供写使用,Direct 读取 • OLTP Raid10,Strip Size 参考DB • OLAP Raid5 • 关注 Raid 卡充放电带来的 Cache 失效 • 预读只对连续读有效,OLTP 关闭预读 方法 • 提高CPU运算能力(频率?) • 缩短 CPU 访问数据的路径(缓存?) DTCC2012DTCC2012 2012/4/6 28 Hardware Disk Raid Card CPU … MySQL 性能优化最佳实践 • OLTP: 小容量”高”转速 • OLAP: 大容量”低”转速(钱多可以高转速) • 磁盘数量尽可能多 • 有钱可以上 SSD( IO 瓶颈场景下) • Cache 只供写使用,Direct 读取 • OLTP Raid10,Strip Size 参考DB • OLAP Raid5 • 关注 Raid 卡充放电带来的 Cache 失效 • 预读只对连续读有效,OLTP 关闭预读 • CPU 越多越好? • Core 越多越好? 误区 DTCC2012DTCC2012 2012/4/6 29 Hardware Disk Raid Card CPU … MySQL 性能优化最佳实践 • OLTP: 小容量”高”转速 • OLAP: 大容量”低”转速(钱多可以高转速) • 磁盘数量尽可能多 • 有钱可以上 SSD( IO 瓶颈场景下) • Cache 只供写使用,Direct 读取 • OLTP Raid10,Strip Size 参考DB • OLAP Raid5 • 关注 Raid 卡充放电带来的 Cache 失效 • 预读只对连续读有效,OLTP 关闭预读 • 使用主频更高的 CPU • 使用缓存更大的 CPU • 8个Core 比较合适,不超过16 Core • Core比较多可以单机多实例 最佳实践 DTCC2012DTCC2012 2012/4/6 30 OS 方法 File System I/O Scheduler … MySQL 性能优化最佳实践 • 确保安全:有日志,能恢复 • OLTP: 提高大文件下随机I/O性能 • OLAP: 提高大文件下连续I/O性能 • 降低管理成本 CPU/DRAM DTCC2012DTCC2012 2012/4/6 31 OS File System I/O Scheduler … MySQL 性能优化最佳实践 • OS默认自带的就是最好的? • 功能最强的才是最好的? • 性能最高的才是最好的? 误区 CPU/DRAM DTCC2012DTCC2012 2012/4/6 32 OS File System I/O Scheduler … MySQL 性能优化最佳实践 • XFS 非常适合 MySQL •XFS要注意su(stripe size)和sw(stripe width) • ZFS 非常适合备份管理 最佳实践 CPU/DRAM DTCC2012DTCC2012 2012/4/6 33 OS File System I/O Scheduler … MySQL 性能优化最佳实践 • XFS 非常适合 MySQL •XFS要注意su(stripe size)和sw(stripe width) • ZFS 非常适合备份管理 方法 • 尽量减少不必要阻塞 • 尽量降低随机I/O访问的延时 • CFQ, Deadline,NOOP和Anticipatory 差异 CPU/DRAM DTCC2012DTCC2012 2012/4/6 34 OS File System I/O Scheduler … MySQL 性能优化最佳实践 • XFS 非常适合 MySQL •XFS要注意su(stripe size)和sw(stripe width) • ZFS 非常适合备份管理 • 公平的才是最合适的? • 智能的就是合适的? 误区 CPU/DRAM DTCC2012DTCC2012 2012/4/6 35 OS File System I/O Scheduler … MySQL 性能优化最佳实践 • XFS 非常适合 MySQL •XFS要注意su(stripe size)和sw(stripe width) • ZFS 非常适合备份管理 • CFQ适用于io大小非常均匀的场景 • 稍微复杂点的OLTP最好更换为 Deadline • I/O性能不是瓶颈的时候使用NOOP • Anticipatory不适用数据库场景 最佳实践 CPU/DRAM DTCC2012DTCC2012 2012/4/6 36 OS File System I/O Scheduler … MySQL 性能优化最佳实践 • XFS 非常适合 MySQL •XFS要注意su(stripe size)和sw(stripe width) • ZFS 非常适合备份管理 • CFQ适用于io大小非常均匀的场景 • 稍微复杂点的OLTP最好更换为 Deadline • I/O性能不是瓶颈的时候使用NOOP • Anticipatory不适用数据库场景 方法 • 提升 CPU 利用率 • 均衡 CPU 资源 • 提高内存利用率 CPU/DRAM DTCC2012DTCC2012 2012/4/6 37 OS File System I/O Scheduler … MySQL 性能优化最佳实践 • XFS 非常适合 MySQL •XFS要注意su(stripe size)和sw(stripe width) • ZFS 非常适合备份管理 • CFQ适用于io大小非常均匀的场景 • 稍微复杂点的OLTP最好更换为 Deadline • I/O性能不是瓶颈的时候使用NOOP • Anticipatory不适用数据库场景 • CPU越多越好? • NUMA 一定能提高性能? • 内存越大越好? 误区 CPU/DRAM DTCC2012DTCC2012 2012/4/6 38 OS File System I/O Scheduler CPU/DRAM … MySQL 性能优化最佳实践 • XFS 非常适合 MySQL •XFS要注意su(stripe size)和sw(stripe width) • ZFS 非常适合备份管理 • CFQ适用于io大小非常均匀的场景 • 稍微复杂点的OLTP最好更换为 Deadline • I/O性能不是瓶颈的时候使用NOOP • Anticipatory不适用数据库场景 • 单实例关闭 NUMA • CPU Core达到16最好双实例 • 多实例进行 CPU 绑定 • 单实例没必要超过64GB内存 最佳实践 DTCC2012DTCC2012 2012/4/6 39 Params 方法 Cache/Buffer Connction • query_cache:缓存结果集,极高效,与SQL语句一一对应 • binlog_cache_size:缓存binlog数据,影响所有写入操作的性能 • table_cache:缓存打开的表信息,MyISAM会占用较多,表多的需注意 • thread_cache:缓存连接线程,影响连接建立效率,对短连接影响较大 • key_buffer_size:缓存MyISAM索引,对MyISAM表性能影响极大 • innodb_db_buffer_pool_size:对InnoDB极大影响,缓存索引及数据 • innodb_log_buff_size:缓存InnoDB写入日志,影响写入效率 • innodb_max_dirty_pages_pct:设置InnoDB Buffer中脏页占比 延伸阅读:http://isky000.com/database/mysql-perfornamce-tuning-cache-parameter IO … MySQL 性能优化最佳实践 DTCC2012DTCC2012 2012/4/6 40 Params Cache/Buffer • query_cache:一定要有?越大越好? • binlog_cache_size:越大越好? • table_cache:越多越好? • thread_cache:越多越好? • key_buffer_size:缓存数据?越大越好? • innodb_db_buffer_pool_size:越大越好? • innodb_log_buff_size:越大越好? • innodb_max_dirty_pages_pct:脏页占比越多越快? 延伸阅读:http://isky000.com/database/mysql-perfornamce-tuning-cache-parameter 误区 Connction IO … MySQL 性能优化最佳实践 DTCC2012DTCC2012 2012/4/6 41 Params Cache/Buffer • query_cache:不超过256MB,除非基本静态,InnoDB无效 • binlog_cache_size:2MB~4MB,< 32MB • table_cache:1024,具体需要根据实际环境调整 • thread_cache:1024,< max_connectios • key_buffer_size:无MyISAM 16MB,否则所有MYI大小之内尽可能大 • innodb_db_buffer_pool_size:仅InnoDB,所有文件大小之内尽可能大 • innodb_log_buff_size:4MB~8MB,< 32MB • innodb_max_dirty_pages_pct:<1G/innodb_db_buffer_pool_size(G)*100 延伸阅读:http://isky000.com/database/mysql-perfornamce-tuning-cache-parameter Connction IO … MySQL 性能优化最佳实践 最佳实践 DTCC2012DTCC2012 2012/4/6 42 Params 方法 Cache/Buffer Connction 延伸阅读:http://isky000.com/database/mysql-perfornamce-tuning-cache-parameter • max_connections:影响能够保持的最大客户端连接数,属于自我保护类 • max_connect_errors:某个用户允许最大登录失败次数,类似于防破解 • back_log:影响突发连接暴增场景,比如服务器重启后瞬间 • skip-name-resolve:取消对客户端的 DNS 反解,影响连接和授权 • interactive_timeout和wait_timeout:影响空闲连接最大可空闲时间 IO … MySQL 性能优化最佳实践 • query_cache:不超过256MB,除非基本静态,InnoDB无效 • binlog_cache_size:2MB~4MB,< 32MB • table_cache:1024,具体需要根据实际环境调整 • thread_cache:1024,< max_connectios • key_buffer_size:无MyISAM 16MB,否则所有MYI大小之内尽可能大 • innodb_db_buffer_pool_size:仅InnoDB,所有文件大小之内尽可能大 • innodb_log_buff_size:4MB~8MB,< 32MB • innodb_max_dirty_pages_pct:<1G/innodb_db_buffer_pool_size(G)*100 DTCC2012DTCC2012 2012/4/6 43 Params Cache/Buffer 延伸阅读:http://isky000.com/database/mysql-perfornamce-tuning-cache-parameter 误区 Connction • max_connections:最大连接数越大越好? • max_connect_errors:最大错误数越小越好? • back_log:back log队列越长越好吗? • skip-name-resolve:一定要忽略 DNS 反解吗? • interactive_timeout和wait_timeout:空闲时间越长越好吗? IO … MySQL 性能优化最佳实践 • query_cache:不超过256MB,除非基本静态,InnoDB无效 • binlog_cache_size:2MB~4MB,< 32MB • table_cache:1024,具体需要根据实际环境调整 • thread_cache:1024,< max_connectios • key_buffer_size:无MyISAM 16MB,否则所有MYI大小之内尽可能大 • innodb_db_buffer_pool_size:仅InnoDB,所有文件大小之内尽可能大 • innodb_log_buff_size:4MB~8MB,< 32MB • innodb_max_dirty_pages_pct:<1G/innodb_db_buffer_pool_size(G)*100 DTCC2012DTCC2012 2012/4/6 44 Params Cache/Buffer • query_cache:不超过256MB,除非基本静态,InnoDB无效 • binlog_cache_size:2MB~4MB,< 32MB • table_cache:1024,具体需要根据实际环境调整 • thread_cache:1024,< max_connectios • key_buffer_size:无MyISAM 16MB,否则所有MYI大小之内尽可能大 • innodb_db_buffer_pool_size:仅InnoDB,所有文件大小之内尽可能大 • innodb_log_buff_size:4MB~8MB,< 32MB • innodb_max_dirty_pages_pct:<1G/innodb_db_buffer_pool_size(G)*100 延伸阅读:http://isky000.com/database/mysql-perfornamce-tuning-cache-parameter 最佳实践 Connction • max_connections:1000~2000,< 10000 • max_connect_errors:>1000,尽量大一点吧 • back_log:100,< OS网络层设置 • skip-name-resolve:建议启用,确保授权都是用IP • interactive_timeout和wait_timeout:86400,24小时基本足矣 IO … MySQL 性能优化最佳实践 DTCC2012DTCC2012 2012/4/6 45 Params 方法 Cache/Buffer Connction 延伸阅读:http://isky000.com/database/mysql-perfornamce-tuning-cache-parameter IO • innodb_flush_method:innodb文件打开方式,linux下文件系统影响较大 • innodb_flush_log_at_trx_commit:影响innodb日志事务刷新机制 • innodb_file_per_table:影响表存储方式,文件过大会影响性能 • sync_binlog:影响binlog日志刷新到磁盘的机制 … MySQL 性能优化最佳实践 • query_cache:不超过256MB,除非基本静态,InnoDB无效 • binlog_cache_size:2MB~4MB,< 32MB • table_cache:1024,具体需要根据实际环境调整 • thread_cache:1024,< max_connectios • key_buffer_size:无MyISAM 16MB,否则所有MYI大小之内尽可能大 • innodb_db_buffer_pool_size:仅InnoDB,所有文件大小之内尽可能大 • innodb_log_buff_size:4MB~8MB,< 32MB • innodb_max_dirty_pages_pct:<1G/innodb_db_buffer_pool_size(G)*100 • max_connections:1000~2000,< 10000 • max_connect_errors:>1000,尽量大一点吧 • back_log:100,< OS网络层设置 • skip-name-resolve:建议启用,确保授权都是用IP • interactive_timeout和wait_timeout:86400,24小时基本足矣 DTCC2012DTCC2012 2012/4/6 46 Params Cache/Buffer 延伸阅读:http://isky000.com/database/mysql-perfornamce-tuning-cache-parameter 误区 Connction IO • innodb_flush_method:注意系统之间的差异及文件系统差异 • innodb_flush_log_at_trx_commit:设为1最好吗? • innodb_file_per_table:独享表空间更好吗? • sync_binlog:刷新越频繁越好吗? … MySQL 性能优化最佳实践 • query_cache:不超过256MB,除非基本静态,InnoDB无效 • binlog_cache_size:2MB~4MB,< 32MB • table_cache:1024,具体需要根据实际环境调整 • thread_cache:1024,< max_connectios • key_buffer_size:无MyISAM 16MB,否则所有MYI大小之内尽可能大 • innodb_db_buffer_pool_size:仅InnoDB,所有文件大小之内尽可能大 • innodb_log_buff_size:4MB~8MB,< 32MB • innodb_max_dirty_pages_pct:<1G/innodb_db_buffer_pool_size(G)*100 • max_connections:1000~2000,< 10000 • max_connect_errors:>1000,尽量大一点吧 • back_log:100,< OS网络层设置 • skip-name-resolve:建议启用,确保授权都是用IP • interactive_timeout和wait_timeout:86400,24小时基本足矣 DTCC2012DTCC2012 2012/4/6 47 Params Cache/Buffer • query_cache:不超过256MB,除非基本静态,InnoDB无效 • binlog_cache_size:2MB~4MB,< 32MB • table_cache:1024,具体需要根据实际环境调整 • thread_cache:1024,< max_connectios • key_buffer_size:无MyISAM 16MB,否则所有MYI大小之内尽可能大 • innodb_db_buffer_pool_size:仅InnoDB,所有文件大小之内尽可能大 • innodb_log_buff_size:4MB~8MB,< 32MB • innodb_max_dirty_pages_pct:<1G/innodb_db_buffer_pool_size(G)*100 延伸阅读:http://isky000.com/database/mysql-perfornamce-tuning-cache-parameter 最佳实践 Connction • max_connections:1000~2000,< 10000 • max_connect_errors:>1000,尽量大一点吧 • back_log:100,< OS网络层设置 • skip-name-resolve:建议启用,确保授权都是用IP • interactive_timeout和wait_timeout:86400,24小时基本足矣 IO • innodb_flush_method:O_DIRECT(Linux) • innodb_flush_log_at_trx_commit:2,特别重要的设置为1,不建议0 • innodb_file_per_table:一般建议开启 • sync_binlog:4~8,非常频繁的系统可适当增大,但不建议0 … MySQL 性能优化最佳实践 DTCC2012DTCC2012 2012/4/6 48 Storage Engine 方法 MyISAM InnoDB • 尽量索引,MyISAM只缓存索引不缓存数据 • 调整读写优先级,根据实际需求,调整读写优先级 • 延迟插入,使用 insert delay,减少和 select 竞争 • 数据顺序操作,让insert全部到尾部,减少和select竞争 • 分解大操作,将大操作分解成多步小操作,防止长时间锁定 • 降低并发数,表锁会导致竞争激烈,通过排队机制提高效率 • 充分利用 Query Cache:对于静态数据,尽量使用 Query Cache •… … MySQL 性能优化最佳实践 DTCC2012DTCC2012 2012/4/6 49 Storage Engine MyISAM InnoDB • key_buffer 会缓存所有 MyISAM 的数据和索引? • MyISAM 读写一定是互斥的? • MyISAM 读效率一定高于 InnoDB? • 在 MyISAM 中所有的 count 都高效? •… … 误区 MySQL 性能优化最佳实践 DTCC2012DTCC2012 2012/4/6 50 Storage Engine MyISAM InnoDB • 不需要事务支持 • 并发相对较低 • 数据修改相对较少 • 以读为主 • 数据一致性要求较低 •… MySQL 性能优化最佳实践 最佳实践 … DTCC2012DTCC2012 2012/4/6 51 Storage Engine 方法 MyISAM InnoDB … • 主键尽可能小:所有非主键索引都需要存储主键 • 索引整合,减少冗余索引,降低数据量 • 避免全表扫描,因为会导致表锁 • 尽量自己控制事务,关闭 aotucommit • 尽量缓存所有数据和索引 • 合理设置 innodb_flush_log_at_trx_commit • 充分利用索引避开表锁 • 避免主键更新 •… MySQL 性能优化最佳实践 • 不需要事务支持 • 并发相对较低 • 数据修改相对较少 • 以读为主 • 数据一致性要求较低 •… DTCC2012DTCC2012 2012/4/6 52 Storage Engine MyISAM InnoDB … • Innodb_buffer_pool 只缓存索引? • 任何情况下都是行锁? • 事务越小越好? • 日志刷新越快越好? •… 误区 MySQL 性能优化最佳实践 • 不需要事务支持 • 并发相对较低 • 数据修改相对较少 • 以读为主 • 数据一致性要求较低 •… DTCC2012DTCC2012 2012/4/6 53 Storage Engine MyISAM InnoDB • 不需要事务支持 • 并发相对较低 • 数据修改相对较少 • 以读为主 • 数据一致性要求较低 •… • 需要事务支持 • 并发较大 • 数据变更比较频繁 • 数据一致性要求较高 • 硬件设备内存较大,远大于索引数据量 •… MySQL 性能优化最佳实践 最佳实践 … DTCC2012DTCC2012 2012/4/6 54 Schame 方法 优化数据类型 调整字符编码 … 适当拆分 适度冗余 • 合理设置长度 • 尽量避免使用lob字段 • 尽量使用更小的数据类型 •… 延伸阅读:http://isky000.com/database/mysql-perfornamce-tuning-schema MySQL 性能优化最佳实践 DTCC2012DTCC2012 2012/4/6 55 Schame 优化数据类型 调整字符编码 … 适当拆分 适度冗余 • 预留越长越好? • INT(1) 代表存放1位长度的整数值? • MySQL能够高效处理各种数据类型? •… 误区 延伸阅读:http://isky000.com/database/mysql-perfornamce-tuning-schema MySQL 性能优化最佳实践 DTCC2012DTCC2012 2012/4/6 56 Schame 优化数据类型 调整字符编码 … 适当拆分 适度冗余 • 避免DOUBLE,区分开 TINYINT / INT / BIGINT • 尽量避免TEXT,VARCHAR不要留过大缓冲 • 尽量TIMESTAMP,能用DATE不用DATETIME • 拒绝 LOB类型,可尝试 ENUM & SET 延伸阅读:http://isky000.com/database/mysql-perfornamce-tuning-schema MySQL 性能优化最佳实践 最佳实践 DTCC2012DTCC2012 2012/4/6 57 Schame 方法 优化数据类型 调整字符编码 … 适当拆分 适度冗余 • 够用就可以,选择更小的字符集 • 保证语言环境能够支持覆盖 •… 延伸阅读:http://isky000.com/database/mysql-perfornamce-tuning-schema MySQL 性能优化最佳实践 • 避免DOUBLE,区分开 TINYINT / INT / BIGINT • 尽量避免TEXT,VARCHAR不要留过大缓冲 • 尽量TIMESTAMP,能用DATE不用DATETIME • 拒绝 LOB类型,可尝试 ENUM & SET DTCC2012DTCC2012 2012/4/6 58 Schame 优化数据类型 调整字符编码 … 适当拆分 适度冗余 • 一定要整个 Server 统一? • 一定要全库统一? • 一定要全表统一? 误区 延伸阅读:http://isky000.com/database/mysql-perfornamce-tuning-schema MySQL 性能优化最佳实践 • 避免DOUBLE,区分开 TINYINT / INT / BIGINT • 尽量避免TEXT,VARCHAR不要留过大缓冲 • 尽量TIMESTAMP,能用DATE不用DATETIME • 拒绝 LOB类型,可尝试 ENUM & SET DTCC2012DTCC2012 2012/4/6 59 Schame 优化数据类型 调整字符编码 … 适当拆分 适度冗余 • 避免DOUBLE,区分开 TINYINT / INT / BIGINT • 尽量避免TEXT,VARCHAR不要留过大缓冲 • 尽量TIMESTAMP,能用DATE不用DATETIME • 拒绝 LOB类型,可尝试 ENUM & SET • 纯拉丁字符能表示的内容,没必要选择 latin1 • 数据类型可精确到字段,极端情况下单独设置 • 确定不需要多语言,就没必要UNICODE类型 延伸阅读:http://isky000.com/database/mysql-perfornamce-tuning-schema MySQL 性能优化最佳实践 最佳实践 DTCC2012DTCC2012 2012/4/6 60 Schame 方法 优化数据类型 调整字符编码 … 适当拆分 适度冗余 • 降低单条记录长度,使单个数据块中存放尽可 能多的纪录 •… 延伸阅读:http://isky000.com/database/mysql-perfornamce-tuning-schema MySQL 性能优化最佳实践 • 避免DOUBLE,区分开 TINYINT / INT / BIGINT • 尽量避免TEXT,VARCHAR不要留过大缓冲 • 尽量TIMESTAMP,能用DATE不用DATETIME • 拒绝 LOB类型,可尝试 ENUM & SET • 纯拉丁字符能表示的内容,没必要选择 latin1 • 数据类型可精确到字段,极端情况下单独设置 • 确定不需要多语言,就没必要UNICODE类型 DTCC2012DTCC2012 2012/4/6 61 Schame 优化数据类型 调整字符编码 … 适当拆分 适度冗余 • 数据表一定要和程序对象对应才叫合理的设计? • 只要不在 select 子句中的字段就不会被访问? •… 误区 延伸阅读:http://isky000.com/database/mysql-perfornamce-tuning-schema MySQL 性能优化最佳实践 • 避免DOUBLE,区分开 TINYINT / INT / BIGINT • 尽量避免TEXT,VARCHAR不要留过大缓冲 • 尽量TIMESTAMP,能用DATE不用DATETIME • 拒绝 LOB类型,可尝试 ENUM & SET • 纯拉丁字符能表示的内容,没必要选择 latin1 • 数据类型可精确到字段,极端情况下单独设置 • 确定不需要多语言,就没必要UNICODE类型 DTCC2012DTCC2012 2012/4/6 62 Schame 优化数据类型 调整字符编码 … 适当拆分 适度冗余 • 避免DOUBLE,区分开 TINYINT / INT / BIGINT • 尽量避免TEXT,VARCHAR不要留过大缓冲 • 尽量TIMESTAMP,能用DATE不用DATETIME • 拒绝 LOB类型,可尝试 ENUM & SET • 纯拉丁字符能表示的内容,没必要选择 latin1 • 数据类型可精确到字段,极端情况下单独设置 • 确定不需要多语言,就没必要UNICODE类型 • 将不常使用的字段以及大字段拆分到独立附属 表中 •… 延伸阅读:http://isky000.com/database/mysql-perfornamce-tuning-schema MySQL 性能优化最佳实践 最佳实践 DTCC2012DTCC2012 2012/4/6 63 Schame 方法 优化数据类型 调整字符编码 … 适当拆分 适度冗余 • 冗余常用字段,减少关联查询 •… 延伸阅读:http://isky000.com/database/mysql-perfornamce-tuning-schema MySQL 性能优化最佳实践 • 避免DOUBLE,区分开 TINYINT / INT / BIGINT • 尽量避免TEXT,VARCHAR不要留过大缓冲 • 尽量TIMESTAMP,能用DATE不用DATETIME • 拒绝 LOB类型,可尝试 ENUM & SET • 纯拉丁字符能表示的内容,没必要选择 latin1 • 数据类型可精确到字段,极端情况下单独设置 • 确定不需要多语言,就没必要UNICODE类型 • 将不常使用的字段以及大字段拆分到独立附属 表中 •… DTCC2012DTCC2012 2012/4/6 64 Schame 优化数据类型 调整字符编码 … 适当拆分 适度冗余 • 严格遵循第三范式的设计才是最高效的设计? •… 误区 延伸阅读:http://isky000.com/database/mysql-perfornamce-tuning-schema MySQL 性能优化最佳实践 • 避免DOUBLE,区分开 TINYINT / INT / BIGINT • 尽量避免TEXT,VARCHAR不要留过大缓冲 • 尽量TIMESTAMP,能用DATE不用DATETIME • 拒绝 LOB类型,可尝试 ENUM & SET • 纯拉丁字符能表示的内容,没必要选择 latin1 • 数据类型可精确到字段,极端情况下单独设置 • 确定不需要多语言,就没必要UNICODE类型 • 将不常使用的字段以及大字段拆分到独立附属 表中 •… DTCC2012DTCC2012 2012/4/6 65 Schame 优化数据类型 调整字符编码 … 适当拆分 适度冗余 • 避免DOUBLE,区分开 TINYINT / INT / BIGINT • 尽量避免TEXT,VARCHAR不要留过大缓冲 • 尽量TIMESTAMP,能用DATE不用DATETIME • 拒绝 LOB类型,可尝试 ENUM & SET • 纯拉丁字符能表示的内容,没必要选择 latin1 • 数据类型可精确到字段,极端情况下单独设置 • 确定不需要多语言,就没必要UNICODE类型 • 将不常使用的字段以及大字段拆分到独立附属 表中 •… • 被频繁引用且只能通过 Join 2张(或者更多) 表的方式才能得到的独立小字段,建议冗余 •… 延伸阅读:http://isky000.com/database/mysql-perfornamce-tuning-schema MySQL 性能优化最佳实践 最佳实践 DTCC2012DTCC2012 2012/4/6 66 Index 方法 合适的字段 合适的顺序 … 合适的比例 合理的维护 • 提高过滤性 • 降低索引的更新分裂 • 避免无效索引 • 非不得已不用外键 延伸阅读:http://isky000.com/database/mysql-performance-tuning-index MySQL 性能优化最佳实践 DTCC2012DTCC2012 2012/4/6 67 Index 合适的字段 合适的顺序 … 合适的比例 合理的维护 • 只要在 Where 条件中就应该创建索引? • 只要创建了索引,就能被 SQL 使用? • 使用索引一定比不使用索引快? 误区 延伸阅读:http://isky000.com/database/mysql-performance-tuning-index MySQL 性能优化最佳实践 DTCC2012DTCC2012 2012/4/6 68 Index 合适的字段 合适的顺序 … 合适的比例 合理的维护 延伸阅读:http://isky000.com/database/mysql-performance-tuning-index MySQL 性能优化最佳实践 最佳实践 • 给索引的字段设置默认值 • 不要让含NULL的字段进入组合索引 • 删除过滤性低的字段的索引,可能性能更差 • 不能在索引字段上做运算,会失效 • 避免频繁更新的字段进入索引,增加IO负担 • 尽量覆盖索引(MySQL排序效率不高) DTCC2012DTCC2012 2012/4/6 69 Index 方法 合适的字段 合适的顺序 … 合适的比例 合理的维护 • 提早过滤 • 减少排序 延伸阅读:http://isky000.com/database/mysql-performance-tuning-index MySQL 性能优化最佳实践 • 给索引的字段设置默认值 • 不要让含NULL的字段进入组合索引 • 删除过滤性低的字段的索引,可能性能更差 • 不能在索引字段上做运算,会失效 • 避免频繁更新的字段进入索引,增加IO负担 • 尽量覆盖索引(MySQL排序效率不高) DTCC2012DTCC2012 2012/4/6 70 Index 合适的字段 合适的顺序 … 合适的比例 合理的维护 • 只要将where条件中的字段全部放在索引中就可 以了? • 索引的顺序对 SQL 访问没有影响? 误区 延伸阅读:http://isky000.com/database/mysql-performance-tuning-index MySQL 性能优化最佳实践 • 给索引的字段设置默认值 • 不要让含NULL的字段进入组合索引 • 删除过滤性低的字段的索引,可能性能更差 • 不能在索引字段上做运算,会失效 • 避免频繁更新的字段进入索引,增加IO负担 • 尽量覆盖索引(MySQL排序效率不高) DTCC2012DTCC2012 2012/4/6 71 Index 合适的字段 合适的顺序 … 合适的比例 合理的维护 • 过滤性越高的字段需要越靠前 • 核心SQL覆盖索引,确保尽可能高效 • 不干扰过滤前提下,排序字段进入索引 • 多 SQL 综合考虑,重复利用索引 延伸阅读:http://isky000.com/database/mysql-performance-tuning-index MySQL 性能优化最佳实践 最佳实践 • 给索引的字段设置默认值 • 不要让含NULL的字段进入组合索引 • 删除过滤性低的字段的索引,可能性能更差 • 不能在索引字段上做运算,会失效 • 避免频繁更新的字段进入索引,增加IO负担 • 尽量覆盖索引(MySQL排序效率不高) DTCC2012DTCC2012 2012/4/6 72 Index 方法 合适的字段 合适的顺序 … 合适的比例 合理的维护 • 控制索引长度,尤其是较长的字符串字段 延伸阅读:http://isky000.com/database/mysql-performance-tuning-index MySQL 性能优化最佳实践 • 给索引的字段设置默认值 • 不要让含NULL的字段进入组合索引 • 删除过滤性低的字段的索引,可能性能更差 • 不能在索引字段上做运算,会失效 • 避免频繁更新的字段进入索引,增加IO负担 • 尽量覆盖索引(MySQL排序效率不高) • 过滤性越高的字段需要越靠前 • 核心SQL覆盖索引,确保尽可能高效 • 不干扰过滤前提下,排序字段进入索引 • 多 SQL 综合考虑,重复利用索引 DTCC2012DTCC2012 2012/4/6 73 Index 合适的字段 合适的顺序 … 合适的比例 合理的维护 • 索引可以无限大? • 索引只能使用整个字段? 误区 延伸阅读:http://isky000.com/database/mysql-performance-tuning-index MySQL 性能优化最佳实践 • 给索引的字段设置默认值 • 不要让含NULL的字段进入组合索引 • 删除过滤性低的字段的索引,可能性能更差 • 不能在索引字段上做运算,会失效 • 避免频繁更新的字段进入索引,增加IO负担 • 尽量覆盖索引(MySQL排序效率不高) • 过滤性越高的字段需要越靠前 • 核心SQL覆盖索引,确保尽可能高效 • 不干扰过滤前提下,排序字段进入索引 • 多 SQL 综合考虑,重复利用索引 DTCC2012DTCC2012 2012/4/6 74 Index 合适的字段 合适的顺序 … 合适的比例 合理的维护 • 过滤性越高的字段需要越靠前 • 核心SQL覆盖索引,确保尽可能高效 • 不干扰过滤前提下,排序字段进入索引 • 多 SQL 综合考虑,重复利用索引 • 必须回表取数据时,字符字段前缀索引(8) • 不用回表取数据时,建议整个字段 延伸阅读:http://isky000.com/database/mysql-performance-tuning-index MySQL 性能优化最佳实践 最佳实践 • 给索引的字段设置默认值 • 不要让含NULL的字段进入组合索引 • 删除过滤性低的字段的索引,可能性能更差 • 不能在索引字段上做运算,会失效 • 避免频繁更新的字段进入索引,增加IO负担 • 尽量覆盖索引(MySQL排序效率不高) DTCC2012DTCC2012 2012/4/6 75 Index 方法 合适的字段 合适的顺序 … 合适的比例 合理的维护 • 定期维护存在频繁增删改字段的索引 延伸阅读:http://isky000.com/database/mysql-performance-tuning-index MySQL 性能优化最佳实践 • 给索引的字段设置默认值 • 不要让含NULL的字段进入组合索引 • 删除过滤性低的字段的索引,可能性能更差 • 不能在索引字段上做运算,会失效 • 避免频繁更新的字段进入索引,增加IO负担 • 尽量覆盖索引(MySQL排序效率不高) • 过滤性越高的字段需要越靠前 • 核心SQL覆盖索引,确保尽可能高效 • 不干扰过滤前提下,排序字段进入索引 • 多 SQL 综合考虑,重复利用索引 • 必须回表取数据时,字符字段前缀索引(8) • 不用回表取数据时,建议整个字段 DTCC2012DTCC2012 2012/4/6 76 Index 合适的字段 合适的顺序 … 合适的比例 合理的维护 • 索引不会出现碎片? • 索引会自动维护? 误区 延伸阅读:http://isky000.com/database/mysql-performance-tuning-index MySQL 性能优化最佳实践 • 给索引的字段设置默认值 • 不要让含NULL的字段进入组合索引 • 删除过滤性低的字段的索引,可能性能更差 • 不能在索引字段上做运算,会失效 • 避免频繁更新的字段进入索引,增加IO负担 • 尽量覆盖索引(MySQL排序效率不高) • 过滤性越高的字段需要越靠前 • 核心SQL覆盖索引,确保尽可能高效 • 不干扰过滤前提下,排序字段进入索引 • 多 SQL 综合考虑,重复利用索引 • 必须回表取数据时,字符字段前缀索引(8) • 不用回表取数据时,建议整个字段 DTCC2012DTCC2012 2012/4/6 77 Index 合适的字段 合适的顺序 … 合适的比例 合理的维护 • 过滤性越高的字段需要越靠前 • 核心SQL覆盖索引,确保尽可能高效 • 不干扰过滤前提下,排序字段进入索引 • 多 SQL 综合考虑,重复利用索引 • 必须回表取数据时,字符字段前缀索引(8) • 不用回表取数据时,建议整个字段 • 每月维护(重建)非核心表上的索引(可以的前提) • 每季/年维护核心表上的索引(可以的前提) 延伸阅读:http://isky000.com/database/mysql-performance-tuning-index MySQL 性能优化最佳实践 最佳实践 • 给索引的字段设置默认值 • 不要让含NULL的字段进入组合索引 • 删除过滤性低的字段的索引,可能性能更差 • 不能在索引字段上做运算,会失效 • 避免频繁更新的字段进入索引,增加IO负担 • 尽量覆盖索引(MySQL排序效率不高) DTCC2012DTCC2012 2012/4/6 78 SQL 延伸阅读:http://isky000.com/database/mysql-performance-tuning-sql 方法 调整执行计划 • 缩短访问的路径 • 尽早过滤数据 • 尽可能减少排序 • 降低 SQL 复杂度 • 避开 MySQL 优化器 Bug,比如子查询 •… MySQL 性能优化最佳实践 DTCC2012DTCC2012 2012/4/6 79 SQL 延伸阅读:http://isky000.com/database/mysql-performance-tuning-sql 调整执行计划 • count(1)和count(primary_key) 优于 count(*) • count(column) 和 count(*) 一样 • select a,b from … 比 select a,b,c from … 可以让 数据库访问更少的数据量 • order by 一定需要排序操作 • 执行计划中有 filesort 就会进行磁盘文件排序 •… 误区 MySQL 性能优化最佳实践 DTCC2012DTCC2012 2012/4/6 80 SQL 延伸阅读:http://isky000.com/database/mysql-performance-tuning-sql 调整执行计划 • 减少表连接,减少复杂 SQL,拆分成简单SQL • 减少排序:非必要不排序,利用索引排序,减少 参与排序的记录数 • 尽量避免 select * • 尽量用 join 代替子查询 • 尽量少使用 or,使用 in 或者 union(union all) 代替 • 尽量用 union all 代替 union • 尽量早的将无用数据过滤:选择更优的索引,先 分页再Join… • 避免类型转换:索引失效 • 优先优化高并发的 SQL,而不是执行频率低某些 “大”SQL • 从全局出发优化,而不是片面调整 • 尽可能对每一条SQL进行 explain •… 优化原则 MySQL 性能优化最佳实践 DTCC2012DTCC2012 2012/4/6 81 确认 OS top vmstat iostat … MySQL 性能优化最佳实践 确认结果 DTCC2012DTCC2012 2012/4/6 82 确认 OS MySQL top vmstat iostat … show status MySQL 性能优化最佳实践 确认结果 DTCC2012DTCC2012 2012/4/6 83 确认 OS MySQL App top vmstat iostat … show status latency tps … 确认结果 MySQL 性能优化最佳实践 DTCC2012DTCC2012 2012/4/6 84 Thanks,Q & A http://isky000.com Twitter:@sky000 Weibo:@简朝阳 MySQL 性能优化最佳实践 DTCC2012DTCC2012
还剩83页未读

继续阅读

下载pdf到电脑,查找使用更方便

pdf的实际排版效果,会与网站的显示效果略有不同!!

需要 15 金币 [ 分享pdf获得金币 ] 3 人已下载

下载pdf

pdf贡献者

peterzyliu

贡献于2012-06-10

下载需要 15 金币 [金币充值 ]
亲,您也可以通过 分享原创pdf 来获得金币奖励!
下载pdf