狠狠撸

狠狠撸Share a Scribd company logo

浅谈 MySQL 性能调优

?
?
Sky Jian
Sky Jian

第二届华东地区数据库大会分享主题笔笔罢

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

More Related Content

浅谈 MySQL 性能调优

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