MySQL性能调优最佳实践

CKBOODwasrevisedintheearlymorningofDecember17,2020.MySQL性能调优最佳实践MySQL性能优化最佳实践Aboutme简朝阳(sky000)OracleACE(Expertise:MySQL)技术保障部@麦包包Blog：HYPERLINKTwitter：@sky000Weibo：@简朝阳DTCC20122012/4/61MySQL性能优化最佳实践优化过程找出瓶颈确认结果优化设定目标实施优化DTCC20122012/4/62MySQL性能优化最佳实践找出瓶颈2012/4/6瓶颈3存储容量容量白菜价2TB很普及了DTCC2012MySQL性能优化最佳实践找出瓶颈一般很难跑满存储容量万兆已经很多Network(IOPS/吞吐量)瓶颈DTCC20122012/4/64MySQL性能优化最佳实践找出瓶颈存储容量Network(IOPS/吞吐量)Linux单机支持过百G价格较之过去已大降DRAM瓶颈DTCC20122012/4/65MySQL性能优化最佳实践找出瓶颈存储容量Network(IOPS/吞吐量)DRAMX86Nehalem,SMP,NUMA4路PCServer32核>30%CPU瓶颈DTCC20122012/4/66MySQL性能优化最佳实践找出瓶颈存储容量Network(IOPS/吞吐量)DRAMCPUOLTP：iopsOLAP：吞吐量2012/4/6瓶颈7IO(IOPS/吞吐量)>60%瓶颈在IOSSD?DTCC2012MySQL性能优化最佳实践设定目标极限不可能突破设备能力目标DTCC20122012/4/68MySQL性能优化最佳实践设定目标极限不可能突破设备能力业务需求一切以需求为导向目标DTCC20122012/4/69MySQL性能优化最佳实践设定目标极限不可能突破设备能力业务需求一切以需求为导向目标应用环境环境影响可行性DTCC20122012/4/610MySQL性能优化最佳实践实施优化对象HardwareOSParamsEngineSchemaIndexSQL实施DTCC20122012/4/611MySQLMySQL性能优化最佳实践实施优化对象HardwareOSParamsEngineSchemaIndexSQL方法方法实施DTCC20122012/4/612MySQL„MySQL性能优化最佳实践实施优化2012/4/6对象HardwareOSParamsEngineSchemaIndexSQL方法方法实施13误区误区DTCC2012MySQL„„MySQL性能优化最佳实践实施优化对象方法误区最佳实践HardwareOSParamsEngine方法误区经验SchemaIndexSQL实施DTCC20122012/4/614MySQL„„„MySQL性能优化最佳实践实施优化转速，容量，接口HDD:~150iops,<200MBSSD:10x~1000x,<400MB磁盘背景DTCC20122012/4/615MySQL性能优化最佳实践实施优化主频，多核，超线程SMP,NUMA,MPP2012/4/6磁盘CPU背景16DTCC2012MySQL性能优化最佳实践实施优化2012/4/6磁盘CPU背景17索引~BalanceTree缩短检索路径有序DTCC2012MySQL性能优化最佳实践实施优化磁盘CPU背景索引SQL执行计划如何获得：explain如何分析：DocsDTCC20122012/4/618MySQL性能优化最佳实践实施优化磁盘CPU背景索引2012/4/6MySQL简单，轻型，开放多线程，插件式SQL+StorageEngine…19SQLDTCC2012MySQL性能优化最佳实践实施优化磁盘CPU插件式，可自由更换存储引擎背景索引开放型，可自行开发多样性，特性不一MySQLSQL并存性，可并存使用DTCC20122012/4/620MySQL性能优化最佳实践方法Disk···提高磁盘转速增加磁盘数量选好磁盘接口RaidCardCPUDTCC20122012/4/621Hardware…MySQL性能优化最佳实践误区Disk···容量越大越好？FC磁盘一定比SAS盘快？磁盘Cache越大越好？RaidCardCPUDTCC20122012/4/622Hardware…MySQL性能优化最佳实践最佳实践·OLTP:小容量”高”转速Disk···OLAP:大容量”低”转速(钱多可以高转速)磁盘数量尽可能多有钱可以上SSD(IO瓶颈场景下)RaidCardCPUDTCC20122012/4/623Hardware…MySQL性能优化最佳实践方法·OLTP:小容量”高”转速DiskRaidCard······OLAP:大容量”低”转速(钱多可以高转速)磁盘数量尽可能多有钱可以上SSD(IO瓶颈场景下)增加Raid卡Cache容量提升Cache利用率确保数据安全CPUDTCC20122012/4/624Hardware…MySQL性能优化最佳实践误区·OLTP:小容量”高”转速DiskRaidCard······OLAP:大容量”低”转速(钱多可以高转速)磁盘数量尽可能多有钱可以上SSD(IO瓶颈场景下)读写都是用Cache提升效率？Raid10一定比Raid5快？带电池的Raid卡数据一定安全？CPUDTCC20122012/4/625Hardware…MySQL性能优化最佳实践最佳实践·OLTP:小容量”高”转速DiskRaidCard········OLAP:大容量”低”转速(钱多可以高转速)磁盘数量尽可能多有钱可以上SSD(IO瓶颈场景下)Cache只供写使用，Direct读取OLTPRaid10,StripSize参考DBOLAPRaid5关注Raid卡充放电带来的Cache失效预读只对连续读有效，OLTP关闭预读CPUDTCC20122012/4/626Hardware…MySQL性能优化最佳实践方法·OLTP:小容量”高”转速2012/4/6DiskRaidCardCPU··········OLAP:大容量”低”转速(钱多可以高转速)磁盘数量尽可能多有钱可以上SSD(IO瓶颈场景下)Cache只供写使用，Direct读取OLTPRaid10,StripSize参考DBOLAPRaid5关注Raid卡充放电带来的Cache失效预读只对连续读有效，OLTP关闭预读提高CPU运算能力（频率）缩短CPU访问数据的路径（缓存）27DTCC2012Hardware…MySQL性能优化最佳实践误区·OLTP:小容量”高”转速2012/4/6DiskRaidCardCPU··········OLAP:大容量”低”转速(钱多可以高转速)磁盘数量尽可能多有钱可以上SSD(IO瓶颈场景下)Cache只供写使用，Direct读取OLTPRaid10,StripSize参考DBOLAPRaid5关注Raid卡充放电带来的Cache失效预读只对连续读有效，OLTP关闭预读CPU越多越好？Core越多越好？28DTCC2012Hardware…MySQL性能优化最佳实践最佳实践·OLTP:小容量”高”转速2012/4/6DiskRaidCardCPU············OLAP:大容量”低”转速(钱多可以高转速)磁盘数量尽可能多有钱可以上SSD(IO瓶颈场景下)Cache只供写使用，Direct读取OLTPRaid10,StripSize参考DBOLAPRaid5关注Raid卡充放电带来的Cache失效预读只对连续读有效，OLTP关闭预读使用主频更高的CPU使用缓存更大的CPU8个Core比较合适，不超过16CoreCore比较多可以单机多实例29DTCC2012Hardware…MySQL性能优化最佳实践方法·确保安全：有日志，能恢复FileSystem···OLTP:提高大文件下随机I/O性能OLAP:提高大文件下连续I/O性能降低管理成本I/OSchedulerCPU/DRAMDTCC20122012/4/630OS…MySQL性能优化最佳实践误区FileSystem···OS默认自带的就是最好的？功能最强的才是最好的？性能最高的才是最好的？I/OSchedulerCPU/DRAMDTCC20122012/4/631OS…MySQL性能优化最佳实践最佳实践FileSystem···XFS非常适合MySQLXFS要注意su(stripesize)和sw(stripewidth)ZFS非常适合备份管理I/OSchedulerCPU/DRAMDTCC20122012/4/632OS…MySQL性能优化最佳实践方法FileSystemI/OScheduler······XFS非常适合MySQLXFS要注意su(stripesize)和sw(stripewidth)ZFS非常适合备份管理尽量减少不必要阻塞尽量降低随机I/O访问的延时CFQ,Deadline,NOOP和Anticipatory差异CPU/DRAMDTCC20122012/4/633OS…MySQL性能优化最佳实践误区FileSystemI/OScheduler·····XFS非常适合MySQLXFS要注意su(stripesize)和sw(stripewidth)ZFS非常适合备份管理公平的才是最合适的？智能的就是合适的？CPU/DRAMDTCC20122012/4/634OS…MySQL性能优化最佳实践最佳实践FileSystemI/OScheduler·······XFS非常适合MySQLXFS要注意su(stripesize)和sw(stripewidth)ZFS非常适合备份管理CFQ适用于io大小非常均匀的场景稍微复杂点的OLTP最好更换为DeadlineI/O性能不是瓶颈的时候使用NOOPAnticipatory不适用数据库场景CPU/DRAMDTCC20122012/4/635OS…MySQL性能优化最佳实践方法2012/4/6FileSystemI/OSchedulerCPU/DRAM··········XFS非常适合MySQLXFS要注意su(stripesize)和sw(stripewidth)ZFS非常适合备份管理CFQ适用于io大小非常均匀的场景稍微复杂点的OLTP最好更换为DeadlineI/O性能不是瓶颈的时候使用NOOPAnticipatory不适用数据库场景提升CPU利用率均衡CPU资源提高内存利用率36DTCC2012OS…MySQL性能优化最佳实践误区2012/4/6FileSystemI/OSchedulerCPU/DRAM··········XFS非常适合MySQLXFS要注意su(stripesize)和sw(stripewidth)ZFS非常适合备份管理CFQ适用于io大小非常均匀的场景稍微复杂点的OLTP最好更换为DeadlineI/O性能不是瓶颈的时候使用NOOPAnticipatory不适用数据库场景CPU越多越好？NUMA一定能提高性能？内存越大越好？37DTCC2012OS…MySQL性能优化最佳实践最佳实践2012/4/6FileSystemI/OSchedulerCPU/DRAM···········XFS非常适合MySQLXFS要注意su(stripesize)和sw(stripewidth)ZFS非常适合备份管理CFQ适用于io大小非常均匀的场景稍微复杂点的OLTP最好更换为DeadlineI/O性能不是瓶颈的时候使用NOOPAnticipatory不适用数据库场景单实例关闭NUMACPUCore达到16最好双实例多实例进行CPU绑定单实例没必要超过64GB内存38DTCC2012OS…MySQL性能优化最佳实践方法···query_cache：缓存结果集，极高效，与SQL语句一一对应binlog_cache_size：缓存binlog数据，影响所有写入操作的性能table_cache：缓存打开的表信息，MyISAM会占用较多，表多的需注意Cache/Buffer·····thread_cache：缓存连接线程，影响连接建立效率，对短连接影响较大key_buffer_size：缓存MyISAM索引，对MyISAM表性能影响极大innodb_db_buffer_pool_size：对InnoDB极大影响，缓存索引及数据innodb_log_buff_size：缓存InnoDB写入日志，影响写入效率innodb_max_dirty_pages_pct：设置InnoDBBuffer中脏页占比ConnctionIO延伸阅读：database/mysql-perfornamce-tuning-cache-parameter2012/4/639Params…DTCC2012MySQL性能优化最佳实践误区···query_cache：一定要有？越大越好？binlog_cache_size：越大越好？table_cache：越多越好？Cache/Buffer·····thread_cache：越多越好？key_buffer_size：缓存数据越大越好innodb_db_buffer_pool_size：越大越好？innodb_log_buff_size：越大越好？innodb_max_dirty_pages_pct：脏页占比越多越快？ConnctionIO延伸阅读：database/mysql-perfornamce-tuning-cache-parameter2012/4/640Params…DTCC2012MySQL性能优化最佳实践最佳实践···query_cache：不超过256MB，除非基本静态，InnoDB无效binlog_cache_size：2MB~4MB，<32MBtable_cache：1024，具体需要根据实际环境调整Cache/Buffer·····thread_cache：1024，1000，尽量大一点吧back_log：100，1000，尽量大一点吧back_log：100，1000，尽量大一点吧back_log：100，1000，尽量大一点吧back_log：100， 记录 混凝土 养护记录下载土方回填监理旁站记录免费下载集备记录下载集备记录下载集备记录下载 长度，使单个数据块中存放尽可能多的纪录…适度冗余延伸阅读：database/mysql-perfornamce-tuning-schema2012/4/660Schame…DTCC2012MySQL性能优化最佳实践误区·避免DOUBLE，区分开TINYINT/INT/BIGINT优化数据类型调整字符编码适当拆分·········尽量避免TEXT，VARCHAR不要留过大缓冲尽量TIMESTAMP，能用DATE不用DATETIME拒绝LOB类型，可尝试ENUM&SET纯拉丁字符能表示的内容，没必要选择latin1数据类型可精确到字段，极端情况下单独设置确定不需要多语言，就没必要UNICODE类型数据表一定要和程序对象对应才叫合理的 设计 领导形象设计圆作业设计ao工艺污水处理厂设计附属工程施工组织设计清扫机器人结构设计 只要不在select子句中的字段就不会被访问？…适度冗余延伸阅读：database/mysql-perfornamce-tuning-schema2012/4/661Schame…DTCC2012MySQL性能优化最佳实践最佳实践·避免DOUBLE，区分开TINYINT/INT/BIGINT优化数据类型调整字符编码适当拆分········尽量避免TEXT，VARCHAR不要留过大缓冲尽量TIMESTAMP，能用DATE不用DATETIME拒绝LOB类型，可尝试ENUM&SET纯拉丁字符能表示的内容，没必要选择latin1数据类型可精确到字段，极端情况下单独设置确定不需要多语言，就没必要UNICODE类型将不常使用的字段以及大字段拆分到独立附属表中…适度冗余延伸阅读：database/mysql-perfornamce-tuning-schema2012/4/662Schame…DTCC2012MySQL性能优化最佳实践方法·避免DOUBLE，区分开TINYINT/INT/BIGINT优化数据类型调整字符编码适当拆分适度冗余··········尽量避免TEXT，VARCHAR不要留过大缓冲尽量TIMESTAMP，能用DATE不用DATETIME拒绝LOB类型，可尝试ENUM&SET纯拉丁字符能表示的内容，没必要选择latin1数据类型可精确到字段，极端情况下单独设置确定不需要多语言，就没必要UNICODE类型将不常使用的字段以及大字段拆分到独立附属表中…冗余常用字段，减少关联查询…延伸阅读：database/mysql-perfornamce-tuning-schema2012/4/663Schame…DTCC2012MySQL性能优化最佳实践误区·避免DOUBLE，区分开TINYINT/INT/BIGINT优化数据类型调整字符编码适当拆分适度冗余··········尽量避免TEXT，VARCHAR不要留过大缓冲尽量TIMESTAMP，能用DATE不用DATETIME拒绝LOB类型，可尝试ENUM&SET纯拉丁字符能表示的内容，没必要选择latin1数据类型可精确到字段，极端情况下单独设置确定不需要多语言，就没必要UNICODE类型将不常使用的字段以及大字段拆分到独立附属表中…严格遵循第三范式的设计才是最高效的设计？…延伸阅读：database/mysql-perfornamce-tuning-schema2012/4/664Schame…DTCC2012MySQL性能优化最佳实践最佳实践·避免DOUBLE，区分开TINYINT/INT/BIGINT优化数据类型调整字符编码适当拆分适度冗余··········尽量避免TEXT，VARCHAR不要留过大缓冲尽量TIMESTAMP，能用DATE不用DATETIME拒绝LOB类型，可尝试ENUM&SET纯拉丁字符能表示的内容，没必要选择latin1数据类型可精确到字段，极端情况下单独设置确定不需要多语言，就没必要UNICODE类型将不常使用的字段以及大字段拆分到独立附属表中…被频繁引用且只能通过Join2张（或者更多）表的方式才能得到的独立小字段，建议冗余…延伸阅读：database/mysql-perfornamce-tuning-schema2012/4/665Schame…DTCC2012MySQL性能优化最佳实践方法·提高过滤性合适的字段合适的顺序合适的比例合理的维护···降低索引的更新分裂避免无效索引非不得已不用外键延伸阅读：database/mysql-performance-tuning-indexDTCC20122012/4/666Index…MySQL性能优化最佳实践误区·只要在Where条件中就应该创建索引？合适的字段合适的顺序合适的比例合理的维护··只要创建了索引，就能被SQL使用？使用索引一定比不使用索引快？延伸阅读：database/mysql-performance-tuning-indexDTCC20122012/4/667Index…MySQL性能优化最佳实践最佳实践合适的字段合适的顺序合适的比例合理的维护······给索引的字段设置默认值不要让含NULL的字段进入组合索引删除过滤性低的字段的索引，可能性能更差不能在索引字段上做运算，会失效避免频繁更新的字段进入索引，增加IO负担尽量覆盖索引(MySQL排序效率不高)延伸阅读：database/mysql-performance-tuning-indexDTCC20122012/4/668Index…MySQL性能优化最佳实践方法合适的字段合适的顺序合适的比例合理的维护········给索引的字段设置默认值不要让含NULL的字段进入组合索引删除过滤性低的字段的索引，可能性能更差不能在索引字段上做运算，会失效避免频繁更新的字段进入索引，增加IO负担尽量覆盖索引(MySQL排序效率不高)提早过滤减少排序延伸阅读：database/mysql-performance-tuning-indexDTCC20122012/4/669Index…MySQL性能优化最佳实践误区合适的字段合适的顺序合适的比例合理的维护········给索引的字段设置默认值不要让含NULL的字段进入组合索引删除过滤性低的字段的索引，可能性能更差不能在索引字段上做运算，会失效避免频繁更新的字段进入索引，增加IO负担尽量覆盖索引(MySQL排序效率不高)只要将where条件中的字段全部放在索引中就可以了？索引的顺序对SQL访问没有影响？延伸阅读：database/mysql-performance-tuning-indexDTCC20122012/4/670Index…MySQL性能优化最佳实践最佳实践合适的字段合适的顺序合适的比例合理的维护··········给索引的字段设置默认值不要让含NULL的字段进入组合索引删除过滤性低的字段的索引，可能性能更差不能在索引字段上做运算，会失效避免频繁更新的字段进入索引，增加IO负担尽量覆盖索引(MySQL排序效率不高)过滤性越高的字段需要越靠前核心SQL覆盖索引，确保尽可能高效不干扰过滤前提下，排序字段进入索引多SQL综合考虑，重复利用索引延伸阅读：database/mysql-performance-tuning-indexDTCC20122012/4/671Index…MySQL性能优化最佳实践方法合适的字段合适的顺序合适的比例合理的维护···········给索引的字段设置默认值不要让含NULL的字段进入组合索引删除过滤性低的字段的索引，可能性能更差不能在索引字段上做运算，会失效避免频繁更新的字段进入索引，增加IO负担尽量覆盖索引(MySQL排序效率不高)过滤性越高的字段需要越靠前核心SQL覆盖索引，确保尽可能高效不干扰过滤前提下，排序字段进入索引多SQL综合考虑，重复利用索引控制索引长度，尤其是较长的字符串字段延伸阅读：database/mysql-performance-tuning-indexDTCC20122012/4/672Index…MySQL性能优化最佳实践误区合适的字段合适的顺序合适的比例合理的维护············给索引的字段设置默认值不要让含NULL的字段进入组合索引删除过滤性低的字段的索引，可能性能更差不能在索引字段上做运算，会失效避免频繁更新的字段进入索引，增加IO负担尽量覆盖索引(MySQL排序效率不高)过滤性越高的字段需要越靠前核心SQL覆盖索引，确保尽可能高效不干扰过滤前提下，排序字段进入索引多SQL综合考虑，重复利用索引索引可以无限大？索引只能使用整个字段？延伸阅读：database/mysql-performance-tuning-indexDTCC20122012/4/673Index…MySQL性能优化最佳实践最佳实践合适的字段合适的顺序合适的比例合理的维护············给索引的字段设置默认值不要让含NULL的字段进入组合索引删除过滤性低的字段的索引，可能性能更差不能在索引字段上做运算，会失效避免频繁更新的字段进入索引，增加IO负担尽量覆盖索引(MySQL排序效率不高)过滤性越高的字段需要越靠前核心SQL覆盖索引，确保尽可能高效不干扰过滤前提下，排序字段进入索引多SQL综合考虑，重复利用索引必须回表取数据时，字符字段前缀索引(8)不用回表取数据时，建议整个字段延伸阅读：database/mysql-performance-tuning-indexDTCC20122012/4/674Index…MySQL性能优化最佳实践方法合适的字段合适的顺序合适的比例合理的维护·············给索引的字段设置默认值不要让含NULL的字段进入组合索引删除过滤性低的字段的索引，可能性能更差不能在索引字段上做运算，会失效避免频繁更新的字段进入索引，增加IO负担尽量覆盖索引(MySQL排序效率不高)过滤性越高的字段需要越靠前核心SQL覆盖索引，确保尽可能高效不干扰过滤前提下，排序字段进入索引多SQL综合考虑，重复利用索引必须回表取数据时，字符字段前缀索引(8)不用回表取数据时，建议整个字段定期维护存在频繁增删改字段的索引延伸阅读：database/mysql-performance-tuning-indexDTCC20122012/4/675Index…MySQL性能优化最佳实践误区合适的字段合适的顺序合适的比例合理的维护··············给索引的字段设置默认值不要让含NULL的字段进入组合索引删除过滤性低的字段的索引，可能性能更差不能在索引字段上做运算，会失效避免频繁更新的字段进入索引，增加IO负担尽量覆盖索引(MySQL排序效率不高)过滤性越高的字段需要越靠前核心SQL覆盖索引，确保尽可能高效不干扰过滤前提下，排序字段进入索引多SQL综合考虑，重复利用索引必须回表取数据时，字符字段前缀索引(8)不用回表取数据时，建议整个字段索引不会出现碎片？索引会自动维护？延伸阅读：database/mysql-performance-tuning-indexDTCC20122012/4/676Index…MySQL性能优化最佳实践最佳实践合适的字段合适的顺序合适的比例合理的维护··············给索引的字段设置默认值不要让含NULL的字段进入组合索引删除过滤性低的字段的索引，可能性能更差不能在索引字段上做运算，会失效避免频繁更新的字段进入索引，增加IO负担尽量覆盖索引(MySQL排序效率不高)过滤性越高的字段需要越靠前核心SQL覆盖索引，确保尽可能高效不干扰过滤前提下，排序字段进入索引多SQL综合考虑，重复利用索引必须回表取数据时，字符字段前缀索引(8)不用回表取数据时，建议整个字段每月维护（重建）非核心表上的索引(可以的前提)每季/年维护核心表上的索引(可以的前提)延伸阅读：database/mysql-performance-tuning-indexDTCC20122012/4/677Index…MySQL性能优化最佳实践方法·缩短访问的路径调整执行计划·····尽早过滤数据尽可能减少排序降低SQL复杂度避开MySQL优化器Bug，比如子查询…延伸阅读：database/mysql-performance-tuning-sqlDTCC20122012/4/678SQLMySQL性能优化最佳实践误区调整执行计划······count(1)和count(primary_key)优于count(*)count(column)和count(*)一样selecta,bfrom…比selecta,b,cfrom…可以让数据库访问更少的数据量orderby一定需要排序操作执行计划中有filesort就会进行磁盘文件排序…延伸阅读：database/mysql-performance-tuning-sqlDTCC20122012/4/679SQLMySQL性能优化最佳实践优化原则·····减少表连接，减少复杂SQL，拆分成简单SQL减少排序：非必要不排序，利用索引排序，减少参与排序的记录数尽量避免select*尽量用join代替子查询尽量少使用or，使用in或者union(unionall)代替调整执行计划·······尽量用unionall代替union尽量早的将无用数据过滤：选择更优的索引，先分页再Join…避免类型转换：索引失效优先优化高并发的SQL，而不是执行频率低某些“大”SQL从全局出发优化，而不是片面调整尽可能对每一条SQL进行explain…延伸阅读：database/mysql-performance-tuning-sqlDTCC20122012/4/680SQLMySQL性能优化最佳实践确认结果OStop2012/4/6vmstatiostat…确认81DTCC2012MySQL性能优化最佳实践确认结果topOSMySQL2012/4/6vmstatiostat…确认82showstatusDTCC2012MySQL性能优化最佳实践确认结果topOSMySQLvmstatiostat…确认showstatusApplatencytps…DTCC20122012/4/683MySQL性能优化最佳实践Thanks，Q&ATwitter：@sky000Weibo：@简朝阳DTCC20122012/4/684