数据库性能优化

举荐：应用NSDT场景编辑器助你疾速搭建可编辑的3D利用场景在一本书中搜寻特定主题时，咱们将首先拜访索引页面（该页面位于该书的结尾），并找到蕴含咱们感兴趣的主题的页码。当初，设想一下在没有索引页的书中找到特定主题是如许不不便。为此，咱们必须搜寻书中的每一页，这十分耗时且令人丧气。 当 SQL Server 从数据库中检索数据时，它也会呈现相似的问题。为了克服这个问题，SQL Server还应用索引来放慢数据检索过程，在本文中，咱们将介绍该局部。咱们将介绍为什么须要索引以及如何无效地创立和删除索引。本教程的先决条件是 SQL 命令的基本知识。 什么是索引？索引是一个架构对象，它应用指针从行中检索数据，从而缩小查找数据的 I/O（输出/输入）工夫。索引能够利用于咱们要搜寻的一个或多个列。它们将列存储在称为 B 树的独自数据结构中。B-Tree的次要长处之一是它以排序顺序存储数据。 如果您想晓得为什么如果对数据进行排序能够更快地检索数据，那么您必须浏览线性搜寻与二分搜寻。 索引是进步 SQL 查问性能的最驰名的办法之一。它们体积小、速度快，并且针对关系表进行了显著优化。当咱们想要搜寻没有索引的行时，SQL 会线性执行全表扫描。换句话说，SQL必须扫描每一行能力找到匹配条件，这是十分耗时的。另一方面，如上所述，索引使数据放弃排序。 然而咱们也应该小心，索引会创立一个独自的数据结构，这须要额定的空间，当数据库很大时，这可能会成为问题。出于良好做法，索引仅对罕用列无效，能够防止对很少应用的列应用。以下是索引编制可能有用的一些状况： 行数必须为 （>10000）。必须列蕴含大量值。必须的列不得蕴含大量 NULL 值。如果咱们常常依据特定列对数据进行排序或分组，这将很有帮忙。索引疾速检索排序后的数据，而不是执行齐全扫描。在以下状况下能够防止索引 桌子很小。或者当列的值很少应用时。或者当列的值频繁更改时。当优化程序检测到全表扫描破费的工夫少于索引表时，也可能不应用索引，即便它存在也是如此。当表较小或列频繁更新时，可能会产生这种状况。 创立示例数据库在开始之前，您必须在PC上设置MySQL工作台能力轻松遵循本教程。您能够参考此YouTube视频来设置工作台。 设置工作台后，咱们将创立一些随机数据，从中能够执行查问。 创立表： -- Create a table to hold the random dataCREATE TABLE employee_info (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100), age INT, email VARCHAR(100));插入数据： -- Insert random data into the tableINSERT INTO employee_info (name, age, email)SELECT CONCAT('User', LPAD(ROW_NUMBER() OVER (), 5, '0')), FLOOR(RAND() * 50) + 20, CONCAT('user', LPAD(ROW_NUMBER() OVER (), 5, '0'), '@xyz.com')FROM information_schema.tablesLIMIT 100;它将创立一个名为具备名称、年龄和电子邮件等属性的表。employee_info ...

如果说，人类进化动力源于挑战： 想突破速度，于是从太空飞行追逐到量子世界； 想追求稳定，于是在万变之中，求不变之道； 想打破繁复，于是用 1 台智能手机集成 N 种必需品，大道至简…… 那么，挑战充满不确定性的未来，就是 TiDB 这款开源产品的不断进化的内在基因——如何做到更好的性能、更强的稳定性、更惊人的易用性？这也是 Geek 聚集的 TiDB 社区不断前进的指引。 无挑战，不 Geek。相信在上周举办 TiDB Hackathon 2019 上，大家已经在短短两天里领略了 TiDB 社区伙伴们的 Geek 精神，促成了很多优秀项目的诞生。为了进一步激发社区的创造性，我们将开启 TiDB Challenge Program，这是一场从 Easy 到 Hard 的升级“打怪”竞赛，第一赛季将持续 3 个月，比赛过程中获得积分还可以兑换丰厚的礼品。万事俱备，就等各位来战！ TiDB Challenge Program 由 PingCAP 发起，旨在激发社区创造性，使选手可以通过完成一系列的任务提升 TiDB 产品的稳定性、性能和易用性。第一赛季主题为 Performance Improvement，将于 2019 年 11 月 4 日正式开启，赛事持续 3 个月。选手可选择个人或团队名义参赛完成任务并获得相应积分。积分可在赛季结束后进行奖项兑换。 欢迎大家加入 TiDB Community Slack Workspace 和 tikv-wg Slack Workspace，参赛过程中遇到任何问题都可以直接通过 #performance-challenge-program channel 与我们取得联系。 Highlights从 Easy 到 Hard本次大赛项目采用分级制度，充分照顾入门选手的「Easy」模式降低了参赛门槛，让 TiDB 小白也能快速上手。每个项目对应一定的积分，当选手累计到一定积分后可解锁「Medium」与「Hard」模式，犹如升级打怪般攻克重重障碍，成就感爆棚！ ...

在使用Mysql数据库的过程中,经常需要使用到备份和恢复数据库,最简单便捷的方法便是通过导出SQL数据文件和导入SQL数据文件来完成备份和恢复,但是随着项目的增长,数据量越来越大,每次恢复就成了一件很头疼的事情。 当我最近一次拉下项目中的5GB大小的数据库到本地进行恢复时,竟然需要耗时40-50分钟,想着日后的数据扩增,数据量越来越大,恢复成本也越来越高,于是便查阅了一些资料,可以通过以下设置来提高你的恢复效率. 1.更改备份参数首先我们需要在备份数据库的时候,可以通过更改参数来提高我们的恢复效率. mysqldump --extended-insertmysqldump的--extended-insert参数表示批量插入,会将多个insert语句合并成一个语句,与没有开启-extended-insert的备份导入效率相差3-4倍. 使用--extended-insert=false导出的sql文件数据是这样的,每行一条insert语句,执行效率非常低下 使用--extended-insert=true导出的表如下图这种，一个很长的insert语句,会进行批量插入。 2.调整MYSQL快速插入参数如果你的数据库储存引擎是MYISAM参数的话,可以将此参数设置到512M或256M,MyISAM会使用一种特殊的树状缓存来做出更快的批量插入。 相关文档https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_bulk_insert_buffer_size 该值默认是8M = 8388608byte 查看插入缓冲区大小 SHOW VARIABLES LIKE '%bulk%';设置插入缓冲区大小(全局) SET GLOBAL bulk_insert_buffer_size =1024*1024*512;设置插入缓冲区大小(session) SET bulk_insert_buffer_size =1024*1024*256;如果需要设置Mysql重新启动时,依然保留该值,需要将这段配置添加了my.cnf [mysqld]bulk_insert_buffer_size = 256M3.关闭检查项对于Innodb引擎中,我们可以关闭一些系统检查项来实现更快的插入的方案. //关闭自动提交SET autocommit=0;//关闭唯一检查set unique_checks = 0;//关闭外键检查SET foreign_key_checks=0;//备份的时候开启--extended-insert参数关于Innodb批量数据加载相关文档:https://dev.mysql.com/doc/refman/5.7/en/optimizing-innodb-bulk-data-loading.html 4.实践做好以上优化后,你的Mysql恢复效率瞬间会提升一个档次,在没做以上参数优化时,每次恢复数据库都需要耗费40分钟的时间,设置后只需要16分钟左右,我的数据库文件容量在5GB左右. 以上就这些,途观有更好的方案和建议的话,希望各位同学一起探讨,Happy Coding。

一、等待事件由来大家可能有些奇怪，为什么说等待事件，先谈到了指标体系。其实，正是因为指标体系的发展，才导致等待事件的引入。总结一下，Oracle的指标体系，大致经历了下面三个阶段： 以命中率为主要参考指标以各种命中率为主要的优化入口依据，常见的有”library cache hit radio“等。但这种方式弊端很大，一个命中率为99%的系统，不一定就比95%的系统优化的更好。在老的Oracle版本中，往往采用这种方式，如8i、9i等。 以等待事件为主要参考指标以各种等待事件为优化入口依据，常见的有"db file sequential read"等。可以较直观的了解，在一段时间内，数据库主要经历了那些等待。这些"瓶颈"，往往就是我们优化的着手点。在10g、11g版本中，广泛使用。 以时间模型为主要参考指标以各种资源整体消耗为优化入口依据。可以从整体角度了解数据库在一段时间内的消耗情况。较等待事件的方式，更有概括性。常见的如"DB Time"。Oracle在不断加强这个方面的工作。 从上面三个阶段可见，等待事件的引入，正是为了解决以命中率为指标的诸多弊端。与后面的时间模型相比，等待事件以更加直观、细粒度的方式观察Oracle的行为，往往作为优化的重要入口。而时间模型，更侧重于整体、系统性的了解数据库运行状态。两者的侧重点不同。 二、等待事件分类让我们首先从等待事件的分类入手，认识等待事件。从大的分类上来看，等待事件可分为空闲的、非空闲的两大部分。在非空闲的等待事件，又可进一步划分细的类别。 可以通过下面的方法，观察系统包含的等待事件数量及大致分类（以下语句在11g环境运行）。 其中WAIT_CLASS为“Idle”的等待事件就是空闲的，其他的都是非空闲的等待事件。 1. 区分 — 空闲与非空闲等待事件空闲等待事件，是指Oracle正等待某种工作，比如用sqlplus登录之后，但没有进一步发出任何命令，此时该session就处于SQL*Net message from/to client等待事件状态，等待用户发出命令，任何的在诊断和优化数据库的时候，一般不用过多注意这部分事件。 非空闲等待事件，专门针对Oracle的活动，指数据库任务或应用运行过程中发生的等待，这些等待事件是调整数据库的时候应该关注与研究的。 2. 等待事件分类说明管理类-Administrative此类等待事件是由于DBA的管理命令引起的，这些命令要求用户处于等待状态（比如，重建索引） 。 应用程序类-Application此类等待事件是由于用户应用程序的代码引起的（比如，锁等待） 。 群集类-Cluster此类等待事件和真正应用群集RAC的资源有关（比如，gc cr block busy等待事件） 。 提交确认类-Commit此类等待事件只包含一种等待事件——在执行了一个commit命令后，等待一个重做日志写确认（也就是log file sync） 。 并发类-Concurrency此类等待事件是由内部数据库资源引起的（比如闩锁） 。 配置类-Configuration此类等待事件是由数据库或实例的不当配置造成的（比如，重做日志文件尺寸太小，共享池的大小等） 。 空闲类-Idle此类等待事件意味着会话不活跃，等待工作（比如，sql * net messages from client） 。 网络类-Network和网络环境相关的一些等待事件（比如sql* net more data to dblink） 。 其它类-Other此类等待事件通常比较少见（比如wait for EMON to spawn） 。 调度类-Scheduler此类等待事件和资源管理相关（比如resmgr: become active'） 。 系统I/O类-System I/O此类等待事件通过是由后台进程的I/O操作引起的（比如DBWR等待-db file paralle write） 。 用户I/O类-User I/O此类等待事件通常是由用户I/O操作引起的（比如db file sequential read） 。 ...

经常混迹于技术社区，频繁看到这个题目，今天干脆在自己博客重复一遍解决办法：针对mysql，sqlserver等关系型数据库单表数据过大的处理方式如果不是阿里云的分布式数据库 DRDS 那种多机器集群方案的话： 先考虑表分区 ；然后考虑分表 ；然后考虑分库。这个题目是我所经历过的，我做的是GPS应用，早期版本就是选用的关系型数据库Sql Server。当时我选取的方案就是第一种：表分区。 表分区的优势是，如果表结构合理，可以不涉及到程序修改。也就是说，对程序来讲依然是单表读写的效果！所有轨迹数据存入到一个巨大的表里。有多大呢？最大存储量超过10亿行。具体数值应该是12亿多点，由于系统设计为只存储30天轨迹，所以线上期间最大存储只到这个数，再后来采用云架构，上云替换成非关系性数据库，获得了更高的写入性能和存储压缩能力。 每日写入量就超过1500万行。上下班交通高峰时候每秒写入量平均超过500行。也就是500iops，距离系统设计的压测指标3000还有一大截这张大型单表设计要点：（一个聚集索引用于写入，一个联合索引用于查询，没有主键，使用表分区）明确主键用途：真的需要查询单行数据时候才需要主键！我采用无主键设计，用于避免写入时候浪费维护插入数据的性能。最早使用聚集的类似自增的id主键，压测写入超过5亿行的时候，写入性能缩减一半准确适用聚集：写入的数据在硬盘物理顺序上是追加，而不是插入！我把时间戳字段设置为聚集索引，用于聚集写入目的设计。保证硬盘上的物理写入顺序，不浪费性能用于插入数据职责足够单一： 用于精准索引！使用时间+设备联合索引，保证这张表只有一个查询用途。保证系统只有一种查询目的：按照设备号，查询一个时间段的数据。精确的表分区：要求查询时候限定最大量或者最大取值范围！按天进行表分区，实现大数据量下的高效查询。这里是本文重点，按照聚集索引进行，可以让目标数据局限在更小的范围进行，虽然单表数据上亿，但是查询基本上只在某一天的的几千万里进行索引查询每张表会有各自的特点，不可生搬硬套，总结下我这张表的特点：只增，不删，不改！关于不删除中：每天使用作业删除超过30天的那个分区数据除外，因为要清空旧的表分区，腾出新的表分区！只有一个业务查询：只按照设备编码查询某个时间段只有一个运维删除：删除旧的分区数据这张表，是我技术生涯中进步的一个大阶梯，让我我体会到了系统架构的意义。虽然我的这张举行表看似只有4个关键点，但是这四个非常精准的关键点设计，耗费了我一个月之久！正是这么足够精准的表结构设计，才撑起了后来压测并发量超过3000的并发写入量！压测的指标跟数据库所在的硬盘有直接关系，当时选取的硬盘是4块10000转的SAS盘做了Raid10的环境关于后来为什么没有更高的实际应用数值，是因为系统后来改版为云架构，使用了阿里云，更改为写入性能更高的非关系型数据库MongoDB存储轨迹数据。所以虽然距离压测指标还差很远，但是也没有实际跑到这个数据！单机应用再怎么改造，每次升级都是一件麻烦事，所以应当尽可能将瓶颈点提高，甚至消除，云架构的意义就在于弹性扩展，虽然我在数据库方面还没有这方面的成功案例可分享，但是这种架构的意义很明白：将来面对更大的压力，只需要增加服务器数量！ 最后提一句， 很多人觉得SSD就足够高的性能了，但是对于云服务器，ssd的性能才跟传统物理机的iops相持平，这是由于虚拟化层面的损失导致的！原文地址： https://www.opengps.cn/Blog/View.aspx?id=284 文章的更新编辑依此链接为准。欢迎关注源站原创文章！