武安城乡建设网站,wordpress大访问量,怎么用vs2008做网站,福建省建设厅网站节能办MySQL是世界上使用最广泛的开源数据库#xff0c;它在业界的受欢迎程度让其他数据库望尘莫及。它是一个关系型数据库管理系统#xff0c;多年来依然是应用程序的核心。在过去几年里#xff0c;MySQL有一些重要发展。因此#xff0c;整理更新10个MySQL性能调优技巧。 模式设…MySQL是世界上使用最广泛的开源数据库它在业界的受欢迎程度让其他数据库望尘莫及。它是一个关系型数据库管理系统多年来依然是应用程序的核心。在过去几年里MySQL有一些重要发展。因此整理更新10个MySQL性能调优技巧。 模式设计很重要 辅助索引Secondary Key 行可以从索引中获得服务 审查与回顾 可见性很重要 谨慎使用调优工具 I/O操作仍然昂贵 利用通用表的表达式优势 注意云计算 保持Replication的最新状态
1、模式设计很重要
模式设计是在数据库中最应该重视的事情之一。自MySQL从5.6版本中转移到InnoDB作为默认存储引擎后模式设计变得更加重要。
在InnoDB中所有东西都是主键这与InnoDB组织数据方式有关。在InnoDB中主键Primary Key是集群的每一个辅助索引Secondary Key都会为主键增加一个入口指针。在设计模式时如没有考虑到这点性能将受到负面影响。
数据使用B树索引存储以有序的方式插入数据即准序列值防止主键碎片化从而减少寻找叶节点所需的I/O操作。
2、辅助索引Secondary Key
辅助索引Secondary Key是由一个后台进程更新对性能影响并不大。主要影响磁盘空间占用即增加辅助索引会增加存储需求。
对一个没有索引的字段进行过滤可能会导致每次查询运行时都要进行全表扫描。因此过度添加索引不会实现性能改进反而会增加存储成本而且InnoDB必须执行许多后台操作来保持索引的更新。
3、行可以从索引中获得服务
InnoDB可以直接从索引中找到并服务于行记录而辅助索引Secondary Key指向主键主键包含行记录本身。如果InnoDB缓冲池足够大可以在内存中容纳大多数数据。甚至可以使用复合键这比单独的每列键更有效。
MySQL可以在每个表的访问中使用一个索引如果正在运行带有WHERE x1和y2这样的子句的查询那么在x,y上建立联合索引比在每个列上有单独的索引要好。
对x,y的联合索引提高查询性能 SELECT y FROM table WHERE x1
MySQL将使用覆盖索引并从内存中的索引中提供Y。
在实践中可以通过使用联合索引来提高性能。无论何时当你设计联合索引时可以通过从左到右的方式读取索引。
所以给定一个这样的查询 SELECT a,b,c FROM table WHERE a1 and b2
那么一个关于a,b的联合索引将有助于查询。
但是如果查询是下面这个格式 SELECT a,b,c FROM table WHERE b2
那么这个ab的联合索引就是无效的因为违反最左原则即从左往右读取索引导致全表扫描。总是从左边读取索引的想法也适用于其他一些情况。
例如给定以下查询。 SELECT a,b,c FROM table WHERE a1 and c2
那么在a,b,c上的联合索引将只读取第一列因为没有通过列b过滤的WHERE子句。这种情况下MySQL可以部分读取索引这比全表扫描要好但仍不足以获得查询的最佳性能。
另一个与查询设计有关的元素是最左边的索引方法这是MySQL中常用的优化。例如一个关于a,b,c的索引将不包括像select a,c where cx这样的查询因为该查询不能跳过索引的第一部分即a,b。然而如果你有一个类似select c,count(c) where ax and by group by c的查询它对a,b进行过滤并对c进行分组那么a,b,c上的一个索引可以帮助进行过滤和分组。
4、审查与回顾
定期审查设计因为应用程序会随着新功能和错误的修复而改变。应用程序的数据集和使用模式会随着时间的推移而改变这些都会影响查询的性能。
定期审查执行时间非常重要使用慢速查询日志或性能模式或使用监控工具可以快速获取数据。
当然并不是最慢的查询才是最需要解决的问题。例如一个耗时30秒但每天运行两次的查询与一个耗时1秒但每分钟运行100次的查询并存。真正应该优化的是第二个查询因为优化这个查询可以节省大量的时间和资源。
5、可见性很重要
监测是性能调整的关键因素之一。如果不了解当前的工作负载和模式就很难给出具体的建议。近年来MySQL改进了对低级别的MySQL/InnoDB指标的暴露这有助于了解工作负载。
在早期版本中性能模式是一个瓶颈有相当大的影响特别是如果你有许多表。在最近的MySQL版本中就有明显变化如新的数据字典已经改善了性能即使有很多表也不会对性能产生大的影响。
6、谨慎使用调优工具
调优工具给出的建议在大多数情况下是有效的。然而每个工作负载和每个模式有所不同。在某些情况下调优工具的建议并不奏效在相信这些建议时谨慎行事是明智的选择。对于MySQL而言可以对配置进行如下更改。
例如将innodb_buffer_pool_size设置为总内存的75%是好的经验法则。然而在数百GB的内存服务器的情况下如果你有512GB的内存那就会留下128GB的自由空间而不是专门用于缓冲池这是一种很大的浪费。
innodb_log_file_size和innodb_log_files_in_group也是根据RAM的数量来定义。在内存超过128GB的服务器上这个设置没有什么意义因为它将创建64个重做日志文件Redo log每个2GB。这将导致128GB的重做日志Redo log存储在磁盘上。在大多数情况下不需要大的重做日志文件Redo log即使在最繁忙的环境中。因此这并不是一个好的建议。
innodb_flushing_method是启用自动配置时唯一正确配置的值。这个变量将flushing 方法设置为O_DIRECT_NO_FSYNC这是使用Ext4或XFS文件系统时推荐的方法因为它避免了数据的双重缓冲。
一个好的建议是在专用服务器上将innodb_buffer_pool_size设置为75%或80%。在拥有大量内存的服务器上即超过128GB的服务器在对内存消耗进行适当的分析后将其增加到90%甚至更多。同样对于innodb_log_file_size和innodb_log_files_in_group 来说大多数情况下从2GB的文件开始监测写日志操作。通常情况下在确定重做日志Redo log的大小时建议覆盖大约一个小时的写入量。
关于innodb_flush_method对于Ext4或XFS等现代Linux文件系统这个选项应该被设置为O_DIRECT或O_DIRECT_NO_FSYNC。
7、I/O操作仍然昂贵
MySQL和InnoDB试图最小化进行I/O操作数量因为访问存储层在应用性能方面是昂贵的。有些设置会影响InnoDB执行的I/O操作数量。其中有两个设置经常被误解改变它们往往会导致性能问题。
innodb_io_capacity和innodb_io_capacity_max是与后台Flushing的I/O操作数量有关的变量。许多客户增加这些设置的值以利用现代固态硬盘的优势它可以在相对较低的延迟下提供非常高的I/O容量。虽然看上去很合理但增加I/O容量设置会导致一些问题。
第一个问题是通过使InnoDB过快地刷新脏页导致性能下降从而减少了“被刷新前多次修改一个页面的机会”。将脏页保留在内存中可以大大减少将数据写入存储的I/O操作。
其次固态硬盘在出现性能下降之前有一个预期的写入次数。因此增加写操作的数量会影响你的固态硬盘的寿命即使你使用的是高端硬盘。
虽说云主机最近很流行在云中运行MySQL服务实例也是可行的。然而云中的服务器往往会有I/O限制或者会对使用更多的I/O收取更多的费用。通过了解这些限制你可以仔细配置这些参数以确保不达到这些限制并使I/O操作最小化。
提到innodb_lru_scan_depth也很重要因为这个设置控制了缓冲池LRU页面列表中页面清洁器线程在多远的位置扫描脏页。如果你有一个大的缓冲池和许多缓冲池实例的重写工作负载你可以通过减少这个变量来减少I/O的操作。
一个好的建议是保持默认值。
8、利用通用表的表达式优势
MySQL 8.0引入通用表的表达式CTE可以避免创建派生表的嵌套查询。这个功能允许创建一个自定义查询并引用结果就好像是一个临时表或一个视图一样。不同的是CTEs可以在一个事务中被多次引用而不需要明确地创建和删除它们。
在运行多个查询的复杂事务中往往更快。支持CTE递归可以在SQL语言中轻松创建复杂的结构如分层模型和系列。
9、注意云计算
对于MySQL部署有许多不同的云选项值得考虑从在虚拟机中实施MySQL服务器实例到使用数据库即服务DBaaS解决方案选择的范围很广。即使是在云端也必须理解数据库的基本原理否则成本将大大增加。往往通过增加更多硬件来解决问题并未从设计上找问题。
10、保持Replication的最新状态
近年来围绕着MySQL Replication进行许多改进在多数情况下无法及时同步主服务器写入操作。在最新的MySQL主要版本中Replication默认是并行的即多个Replication线程正在运行并试图同时应用事务。
执行效率很大程度上取决于应用程序写入的工作量在大多数情况下并行复制可以帮助复制体跟上写入操作。可以用replica_parallel_type和replica_parallel_workers这两个变量来控制。使用LOGICAL_CLOCK类型事务被并行应用并根据时间戳追踪依赖关系。
总体来说MySQL是数百万开发者的领先开源数据库它将继续成为世界范围内创建应用程序的首选平台。通过研究围绕模式设计、索引、调整和I/O的问题可以极大地提高应用程序的性能。当然转移到云端部署方法不同也会有性能差异
