MySQL数据库性能优化

1、MySQL数据库性能优化数据库性能优化李计刚2015-08参考书籍和文档参考书籍和文档n MySQL管理之道管理之道,性能调优、高可用与监控性能调优、高可用与监控第第5章章“性能调优性能调优”n 深入浅出深入浅出 MySQL数据库开发、优化与管理维护数据库开发、优化与管理维护(第第2版版)第三部分第三部分“优化篇优化篇” n MySQL技术内幕技术内幕(第第4版版)第第5章章“查询优化查询优化”n DB2 SQL性能调优秘笈性能调优秘笈全书，重点第全书，重点第4章章“SQL程序走查程序走查”n 构建高性能构建高性能Web站点站点第第11章章“数据库性能优化数据库性能优化”n SQL反模式反模式

2、第第13章章 “反模式反模式:无规划地使用索引无规划地使用索引”n MySQL核心内幕核心内幕,InnoDB存储引擎存储引擎目录目录n表设计表设计n正确创建和使用索引正确创建和使用索引nMySQL配置参数配置参数nSQL程序走查程序走查n优化案例介绍优化案例介绍数据库表需最大程度遵守三范式数据库表需最大程度遵守三范式n第一范式：数据库表中的字段都是单一属第一范式：数据库表中的字段都是单一属性的，不可再分；性的，不可再分；n第二范式：在第一范式基础上，除了主键第二范式：在第一范式基础上，除了主键以外的其它列都依赖于该主键；以外的其它列都依赖于该主键；n第三范式：如果满足第二范式，并且除了第三范式

3、：如果满足第二范式，并且除了主键以外的其它列都不传递依赖于主键列。主键以外的其它列都不传递依赖于主键列。数据表和字段设计的原则数据表和字段设计的原则n字段选择的一般原则是保小不保大，能占用字段选择的一般原则是保小不保大，能占用字节少的字段就不用大字段；字节少的字段就不用大字段；n字段的命名要有意义字段的命名要有意义; ;n不用使用无法加索引的类型作为关键字段，不用使用无法加索引的类型作为关键字段，比如比如text；n表中组合主键的字段个数越少越好；表中组合主键的字段个数越少越好；n没有冗余的数据库未必是最好的数据库，适没有冗余的数据库未必是最好的数据库，适当的时候需降低范式标准；当的时候需降低

4、范式标准；n根据应用场合选择表的存储引擎根据应用场合选择表的存储引擎数据表选择合适的引擎数据表选择合适的引擎MyISAM 特点n 数据存储方式简单，使用数据存储方式简单，使用 B+ Tree B+ Tree 进行索引使用三个文件进行索引使用三个文件定义一个表：定义一个表：.MYI.MYI、.MYD.MYD、.frm.frm；少碎片、支持大文件、能够进行索引压缩；少碎片、支持大文件、能够进行索引压缩；访问速度飞快，是所有访问速度飞快，是所有MySQLMySQL文件引擎中速度最快的；文件引擎中速度最快的；不支持一些数据库特性，比如 事务、外键约束等；n表级锁，性能稍差，更适合读取多的操作(查询和更

5、新操作并行时，查询操作需等待更新操作结束)；表数据容量有限，一般建议单表数据量介于表数据容量有限，一般建议单表数据量介于50w50w200w200w； 增删查改以后要使用增删查改以后要使用 myisamchk myisamchk 检查优化表检查优化表数据表选择合适的引擎数据表选择合适的引擎InnoDB 特点n使用使用 Table Space Table Space 的方式来进行数据存储的方式来进行数据存储 (ibdata1, (ibdata1, ib_logfile0)ib_logfile0)；n 支持事务、外键约束等数据库特性；支持事务、外键约束等数据库特性；n 行级锁, 读写性能都非常优秀

6、。(在默认“可重复读”事务隔离下，查询和更新操作并行时，查询操作不需等待) ；n 能够承载大数据量的存储和访问；n 拥有自己独立的缓冲池，能够缓存数据和索引；拥有自己独立的缓冲池，能够缓存数据和索引；n 在关闭自动提交的情况下，与在关闭自动提交的情况下，与MyISAMMyISAM引擎速度差异不大引擎速度差异不大数据表的反范式化设计数据表的反范式化设计ped_target_detail 中字段中字段index_name、index_number完全可以通完全可以通过过index_id外键关联外键关联 ped_index表来获取到相应信息。表来获取到相应信息。这么设计：主要目标是减少这么设计：主要

7、目标是减少join链接链接。降低范式标准至第二范式降低范式标准至第二范式目录目录n表设计表设计n正确创建和使用索引正确创建和使用索引nMySQL配置参数配置参数nSQL程序走查程序走查n优化案例介绍优化案例介绍什么是索引？什么是索引？索引的好处与坏处索引的好处与坏处创建索引的好处 n帮助用户提高查询速度；帮助用户提高查询速度； n利用索引的唯一性来控制记录的唯一性；利用索引的唯一性来控制记录的唯一性； n可以加速表与表之间的连接可以加速表与表之间的连接 ；n降低查询中分组和排序的时间降低查询中分组和排序的时间创建索引的坏处n存储索引占用磁盘空间；存储索引占用磁盘空间；n执行数据修改操作执行数据

8、修改操作(INSERT、UPDATE、DELETE)产生索引维护产生索引维护索引创建的总体原则索引创建的总体原则n在了解表的具体应用场景基础上建立索引；在了解表的具体应用场景基础上建立索引；n为所有主键和外键列建立索引；为所有主键和外键列建立索引；n对出现在对出现在WHERE子句、子句、JOIN子句、子句、ORDER BY或或GROUP BY子句中的列考虑建立索引子句中的列考虑建立索引；n对需要确保唯一性的列考虑建立索引；对需要确保唯一性的列考虑建立索引；n对于对于WHEREWHERE子句中用子句中用ANDAND连接并频繁使用的列使用连接并频繁使用的列使用组合索引，组合索引，最频繁的列放在最左

9、边最频繁的列放在最左边；n数据更新频繁的列不宜建立索引；数据更新频繁的列不宜建立索引；n数据量较小的表也不宜建立索引数据量较小的表也不宜建立索引索引的正确使用索引的正确使用(一一)去除查询条件左端的任何标量函数去除查询条件左端的任何标量函数索引的正确使用索引的正确使用(二二)去除查询条件左端的任何数学运算去除查询条件左端的任何数学运算索引的正确使用索引的正确使用(三三)确保宿主变量定义与列数据类型匹配确保宿主变量定义与列数据类型匹配索引的正确使用索引的正确使用(四四)查询条件中避免使用查询条件中避免使用in索引的正确使用索引的正确使用(五五)尽可能用尽可能用UNION ALL取代取代UNION

10、索引的正确使用索引的正确使用(六六)查询条件中使用查询条件中使用like时避免宿主变量以时避免宿主变量以%开头开头索引的正确使用索引的正确使用(七七)避免使用避免使用“or” ，采用其它方式重写，采用其它方式重写索引的正确使用索引的正确使用(八八)避免使用避免使用，用其它方式改写，用其它方式改写索引的正确使用索引的正确使用(八八)使用组合索引时，注意使用组合索引时，注意“最左前缀最左前缀”这个基本原这个基本原则则n最左前缀：就是最左优先，我们创建了最左前缀：就是最左优先，我们创建了lname、fname和和age的多列索引的多列索引,相当于创相当于创建了建了lname单列索引，单列索引，(ln

11、ame,fname)的组的组合索引以及合索引以及(lname,fname,age)组合索引组合索引；nSELECT uid FROM people WHERE fname=Zhiqun AND age=26上述查询语句上述查询语句因违法“最左前缀”原则，系，系统统通常会扫描整表以匹配数据！以匹配数据！目录目录n表设计表设计n正确创建和使用索引正确创建和使用索引nMySQL配置参数配置参数nSQL程序走查程序走查n优化案例介绍优化案例介绍MySQL配置参数配置参数(一一)调整线程缓存的大小调整线程缓存的大小per_thread_buffers=(read_buffer_size+read_rnd

12、_buffer_size+sort_buffer_size+thread_stack+join_buffer_size+binlog_cache_size)*max_connections参数名称参数名称参数说明参数说明设置大小设置大小read_buffer_sizeMySQL读入缓冲区大小读入缓冲区大小默认默认128K,一般一般128K256Ksort_buffer_sizeMySQL执行排序使用的缓冲大小执行排序使用的缓冲大小默认默认2M，一般，一般128K256Kread_rnd_buffer_sizeMySQL的随机读缓冲区大小的随机读缓冲区大小默认默认256K,一般一般 128K25

13、6Kthread_stack每个线程的堆栈大小每个线程的堆栈大小默认默认192KBjoin_buffer_sizeJoin连接操作时，如果关联的字段连接操作时，如果关联的字段没有索引，会出现此参数。没有索引，会出现此参数。默认默认128K, 128K256Ktmp_table_sizeMySQL的临时表缓冲大小的临时表缓冲大小binlog_cache_size在事务过程中容纳二进制日志在事务过程中容纳二进制日志SQL 语句的缓存大小语句的缓存大小128K256Kmax_connections最大连接数最大连接数默认值默认值100，一般，一般5121000MySQL配置参数配置参数(二二)调整全

14、局缓存的大小调整全局缓存的大小global_buffers=innodb_buffer_pool_size+innodb_additional_mem_pool_size+innodb_log_buffer_size+key_buffer_size+query_cache_size参数名称参数名称参数说明参数说明设置大小设置大小innodb_buffer_pool_sizeInnoDB使用该参数指定大小的内存来缓冲数据使用该参数指定大小的内存来缓冲数据和索引和索引默认默认128M,要设置为物要设置为物理内存的理内存的60%70%innodb_additional_mem_pool_size指定

15、指定InnoDB用来存储数据字典和其他内部数据用来存储数据字典和其他内部数据结构的内存池大小结构的内存池大小默认默认8M，一般设置为，一般设置为16M即可。即可。innodb_log_buffer_size指定指定InnoDB用来存储日志数据的缓存大小，如用来存储日志数据的缓存大小，如果您的表操作中包含大量并发事务（或大规模事果您的表操作中包含大量并发事务（或大规模事务），并且在事务提交前要求记录日志文件，请务），并且在事务提交前要求记录日志文件，请尽量调高此项值，以提高日志效率尽量调高此项值，以提高日志效率默认默认8M，一般设置为，一般设置为16M64M即可。即可。key_buffer_si

16、ze决定索引处理的速度。决定索引处理的速度。key_buffer_size只对只对MyISAM表起作用。即使你不使用表起作用。即使你不使用MyISAM表，表，但是内部的临时磁盘表是但是内部的临时磁盘表是MyISAM表，也要使用表，也要使用该值。该值。64M即可。即可。query_cache_size缓存缓存select语句和结果集大小的参数语句和结果集大小的参数MySQL配置参数配置参数(三三)使用查询缓存使用查询缓存MySQL配置参数配置参数(四四)定位慢查询和不使用索引的查询定位慢查询和不使用索引的查询MySQL配置参数配置参数(五五)使用持久连接使用持久连接目录目录n表设计表设计n正确创

17、建和使用索引正确创建和使用索引nMySQL配置参数配置参数nSQL程序走查程序走查n优化案例介绍优化案例介绍SQL程序走查程序走查(一一)走查时，要提供的内容：走查时，要提供的内容：n排列清晰明了的排列清晰明了的SQL代码；代码；nSQL所涉及表结构的详细定义、所建立索引情况等；所涉及表结构的详细定义、所建立索引情况等；n此此SQL解析的解析的EXPLAIN截图；截图；n此此SQL执行后的相关信息，包括获取的行数、执行时间等等。执行后的相关信息，包括获取的行数、执行时间等等。SQL程序走查程序走查(二二)分析分析SQL的的EXPLAIN：n检查所有表空间扫描；检查所有表空间扫描；n检查发生的所

18、有排序，是否需要排序检查发生的所有排序，是否需要排序?；n检查发生的所有排序，是否有其它办法编写查询来消除排序？检查发生的所有排序，是否有其它办法编写查询来消除排序？SQL程序走查程序走查(三三)分析具体的分析具体的SQL：nSQL语句中选择的列是否都需要，不需要的移除掉；语句中选择的列是否都需要，不需要的移除掉；n检查各个查询中的所有列。其中有没有可为检查各个查询中的所有列。其中有没有可为null的列？确保的列？确保编写相应的编写相应的SQL来处理来处理null值；值；n对于对于AVG、MIN、MAX或或SUM语句，确保编写相应的语句，确保编写相应的SQL来来处理处理null值；值；n内联接

19、和外联接的使用是否正确？内联接和外联接的使用是否正确？n检查所有的检查所有的Union SQL语句。是否需要写为语句。是否需要写为Union，或者，或者SQL语句是否可以写为语句是否可以写为Union All?n检查所有的检查所有的Order By语句。从业务上考量是否需要这些语句。从业务上考量是否需要这些Order By语句么？有没有一个索引支持排序？如果与一个索引语句么？有没有一个索引支持排序？如果与一个索引匹配，匹配，Order By可能非常高效。可能非常高效。SQL程序走查程序走查(三三)续续分析具体的分析具体的SQL：n检查所有检查所有Distinct语句。是否可能出现重复？如果有重

20、复，考语句。是否可能出现重复？如果有重复，考虑改写该语句以避免排序；虑改写该语句以避免排序；n检查所有谓词，确保所有数学计算都在操作符的另一边完成，检查所有谓词，确保所有数学计算都在操作符的另一边完成，而不是应用在列本身；而不是应用在列本身；n检查所有谓词，确保左端无任何标量函数；检查所有谓词，确保左端无任何标量函数；n检查所有的检查所有的“非非”逻辑，按正面的方式来改写此非逻辑，这逻辑，按正面的方式来改写此非逻辑，这样会更高效。样会更高效。n检查所有的检查所有的“”,对其进行改写，这样会更高效；对其进行改写，这样会更高效；SQL程序走查程序走查(三三)再续再续分析具体的分析具体的SQL：n检

21、查所有检查所有OR语句语句,对其进行改写，这样更高效；对其进行改写，这样更高效；n检查所有的检查所有的LIKE语句，确保其宿主变量不以语句，确保其宿主变量不以“%”开头；开头；n检查所有的组合索引，确保符合检查所有的组合索引，确保符合“最左前缀最左前缀”原则；原则；n检查所有的检查所有的“In”语句，对其进行改写，这样会更高效；语句，对其进行改写，这样会更高效；n确保宿主变量定义与列数据类型匹配确保宿主变量定义与列数据类型匹配目录目录n表设计表设计n正确创建和使用索引正确创建和使用索引nMySQL配置参数配置参数nSQL程序走查程序走查n优化案例介绍优化案例介绍优化案例优化案例(一一)优化案例优化案例(二二)优化案例优化案例(三三)优化案例优化案例(四四)n考勤表考勤表(att_attendance_record)分区处理；分区处理；n考勤调整表考勤调整表(att_attendance_adjust)对字段对字段 attendance_id 建立索引，避免全表建立索引，避免全表扫描；扫描；n考勤表考勤表(att_attendance_re