如何优化才能提升性能-SQL数据库引擎配置不当

SQL数据库引擎的配置是数据库管理中一项至关重要的任务，它直接关系到数据库系统的性能、稳定性和资源利用效率，一个经过精心调优的配置，能够使数据库在有限的硬件资源下发挥出最大的潜力，应对高并发的访问请求和复杂的数据处理任务，反之，不当的配置则可能导致性能瓶颈、资源浪费甚至系统崩溃，深入理解并掌握数据库引擎的配置方法,是每一位数据库管理员和后端开发者的必备技能。

配置的核心领域

数据库引擎的配置涉及多个方面，但通常可以归纳为以下几个核心领域，理解这些领域的作用,是进行有效配置的基础。

内存管理 内存是影响数据库性能最关键的因素，数据库引擎会使用内存来缓存数据页、索引、执行计划以及进行排序和连接等操作，合理的内存配置可以大幅减少磁盘I/O,这是数据库操作中最耗时的部分。

I/O与存储 磁盘I/O往往是数据库的主要性能瓶颈，配置的目标是尽可能让I/O操作更高效、更可预测。

并发与连接 数据库需要同时处理来自多个客户端的连接和请求，并发配置的目标是在保证数据一致性的前提下,最大化系统的吞吐量。

查询处理器优化 查询优化器负责为sql语句选择最佳的执行计划，虽然优化器通常是自动的,但我们可以通过配置来影响它的决策。

配置方法论：一个迭代过程

数据库配置并非一蹴而就，而是一个持续的、迭代优化的过程,遵循科学的方法论至关重要。

主流数据库引擎关键参数对比

为了更直观地理解不同数据库的配置,下表对比了几个主流数据库引擎在核心领域的关键参数。

配置领域	MySQL (InnoDB)	PostgreSQL
数据缓冲区	innodb_buffer_pool_size	shared_buffers	max server memory (MB)
工作内存	sort_buffer_size , join_buffer_size	, maintenance_work_mem	max memory per query (KB)
最大连接数	max_connections	max_connections	max user connections
日志刷新策略	innodb_flush_log_at_trx_commit	wal_sync_method	recovery model
检查点	innodb_max_dirty_pages_pct	checkpoint_timeout , max_wal_size	recovery interval (min)

最佳实践与常见陷阱

在进行配置时，应遵循一些最佳实践,并警惕常见的陷阱。

最佳实践：

常见陷阱：

相关问答 (FAQs)

问题1：我应该如何开始配置数据库引擎，有没有一个“万金油”方案？

解答： 不存在一个适用于所有场景的“万金油”配置方案，最佳配置高度依赖于你的具体工作负载（读多写少还是写多读少）、数据量、硬件资源（CPU核心数、内存大小、磁盘类型）以及业务对性能和一致性的要求，正确的起步方式是：确保数据库的默认配置正在运行，并建立起一套完善的监控体系，通过压力测试和慢查询日志分析，找到当前系统最明显的性能瓶颈，针对这个瓶颈，遵循“一次只改一个参数”的原则，进行小范围、可衡量的调整，并观察效果，配置是一个基于数据和观察的科学迭代过程,而非一次性的设定。

问题2：调整配置后，性能反而下降了，可能是什么原因？

解答： 这是一个常见但棘手的问题，可能的原因有多种，最常见的是 内存过度分配 ，你将数据缓冲区设置得过大，导致操作系统内存紧张，开始频繁地进行内存交换，而磁盘交换的速度远慢于直接访问物理内存，从而导致整体性能下降，可能是 参数误用 ，将排序工作内存设置得过小，导致数据库不得不使用磁盘临时文件来完成排序操作，增加了I/O负担。 参数间的相互影响 也可能导致问题，某个参数的调整可能给系统的另一部分带来了意想不到的压力，要确保你的 测试方法是可靠的 ，调整前后的测试负载和环境应该保持一致，否则性能对比就失去了意义，遇到这种情况，最好的做法是回滚最近的配置更改,然后重新审视该参数的作用和影响。

mysql数据库性能测试

我理解的是你希望了解mysql性能测试的方法：其实常用的一般：选取最适用的字段属性MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。 因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。 例如，在定义邮政编码这个字段时，如果将其设置为CHAR(255),显然给数据库增加了不必要的空间，甚至使用VARCHAR这种类型也是多余的，因为CHAR(6)就可以很好的完成任务了。 同样的，如果可以的话，我们应该使用MEDIUMINT而不是BIGIN来定义整型字段。 另外一个提高效率的方法是在可能的情况下，应该尽量把字段设置为NOT NULL，这样在将来执行查询的时候，数据库不用去比较NULL值。 对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。 因为在MySQL中，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。 这样，我们又可以提高数据库的性能。 2、使用连接（JOIN）来代替子查询(Sub-Queries)MySQL从4.1开始支持SQL的子查询。 这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。 例如，我们要将客户基本信息表中没有任何订单的客户删除掉，就可以利用子查询先从销售信息表中将所有发出订单的客户ID取出来，然后将结果传递给主查询，如下所示：DELETE FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。 但是，有些情况下，子查询可以被更有效率的连接（JOIN）.. 替代。 例如，假设我们要将所有没有订单记录的用户取出来，可以用下面这个查询完成：SELECT * FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )如果使用连接（JOIN）.. 来完成这个查询工作，速度将会快很多。 尤其是当salesinfo表中对CustomerID建有索引的话，性能将会更好，查询如下：SELECT * FROM customerinfo LEFT JOIN salesinfoON =salesinfo. CustomerID WHERE IS NULL连接（JOIN）.. 之所以更有效率一些，是因为 MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。 3、使用联合(UNION)来代替手动创建的临时表MySQL 从 4.0 的版本开始支持 UNION 查询，它可以把需要使用临时表的两条或更多的 SELECT 查询合并的一个查询中。 在客户端的查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。 使用 UNION 来创建查询的时候，我们只需要用 UNION作为关键字把多个 SELECT 语句连接起来就可以了，要注意的是所有 SELECT 语句中的字段数目要想同。 下面的例子就演示了一个使用 UNION的查询。 SELECT Name, Phone FROM client UNION SELECT Name, BirthDate FROM authorUNIONSELECT Name, Supplier FROM product4、事务尽管我们可以使用子查询（Sub-Queries）、连接（JOIN）和联合（UNION）来创建各种各样的查询，但不是所有的数据库操作都可以只用一条或少数几条SQL语句就可以完成的。 更多的时候是需要用到一系列的语句来完成某种工作。 但是在这种情况下，当这个语句块中的某一条语句运行出错的时候，整个语句块的操作就会变得不确定起来。 设想一下，要把某个数据同时插入两个相关联的表中，可能会出现这样的情况：第一个表中成功更新后，数据库突然出现意外状况，造成第二个表中的操作没有完成，这样，就会造成数据的不完整，甚至会破坏数据库中的数据。 要避免这种情况，就应该使用事务，它的作用是：要么语句块中每条语句都操作成功，要么都失败。 换句话说，就是可以保持数据库中数据的一致性和完整性。 事物以BEGIN 关键字开始，COMMIT关键字结束。 在这之间的一条SQL操作失败，那么，ROLLBACK命令就可以把数据库恢复到BEGIN开始之前的状态。 BEGIN;INSERT INTO salesinfo SET CustomerID=14;UPDATE inventory SET Quantity=11WHERE item=book;COMMIT;事务的另一个重要作用是当多个用户同时使用相同的数据源时，它可以利用锁定数据库的方法来为用户提供一种安全的访问方式，这样可以保证用户的操作不被其它的用户所干扰。 5、锁定表尽管事务是维护数据库完整性的一个非常好的方法，但却因为它的独占性，有时会影响数据库的性能，尤其是在很大的应用系统中。 由于在事务执行的过程中，数据库将会被锁定，因此其它的用户请求只能暂时等待直到该事务结束。 如果一个数据库系统只有少数几个用户来使用，事务造成的影响不会成为一个太大的问题；但假设有成千上万的用户同时访问一个数据库系统，例如访问一个电子商务网站，就会产生比较严重的响应延迟。 其实，有些情况下我们可以通过锁定表的方法来获得更好的性能。 下面的例子就用锁定表的方法来完成前面一个例子中事务的功能。 LOCK TABLE inventory WRITESELECT Quantity FROM inventoryWHEREItem=book; inventory SET Quantity=11WHEREItem=book;UNLOCK TABLES这里，我们用一个 SELECT 语句取出初始数据，通过一些计算，用 UPDATE 语句将新值更新到表中。 包含有 WRITE 关键字的 LOCK TABLE 语句可以保证在 UNLOCK TABLES 命令被执行之前，不会有其它的访问来对 inventory 进行插入、更新或者删除的操作。 6、使用外键锁定表的方法可以维护数据的完整性，但是它却不能保证数据的关联性。 这个时候我们就可以使用外键。 例如，外键可以保证每一条销售记录都指向某一个存在的客户。 在这里，外键可以把customerinfo 表中的CustomerID映射到salesinfo表中CustomerID，任何一条没有合法CustomerID的记录都不会被更新或插入到salesinfo中。 CREATE TABLE customerinfo( CustomerID INT NOT NULL , PRIMARY Key ( CustomerID )) TYPE = INNODB;CREATE TABLE salesinfo( SalesID INT NOT NULL, CustomerID INT NOT NULL, PRIMARY KEY(CustomerID, SalesID), FOREIGN KEY (CustomerID) REFERENCES customerinfo (CustomerID) ON DELETECASCADE) TYPE = INNODB;注意例子中的参数“ON DELETE CASCADE”。 该参数保证当 customerinfo 表中的一条客户记录被删除的时候，salesinfo 表中所有与该客户相关的记录也会被自动删除。 如果要在 MySQL 中使用外键，一定要记住在创建表的时候将表的类型定义为事务安全表 InnoDB类型。 该类型不是 MySQL 表的默认类型。 定义的方法是在 CREATE TABLE 语句中加上 TYPE=INNODB。 如例中所示。 7、使用索引索引是提高数据库性能的常用方法，它可以令数据库服务器以比没有索引快得多的速度检索特定的行，尤其是在查询语句当中包含有MAX(), MIN()和ORDERBY这些命令的时候，性能提高更为明显。 那该对哪些字段建立索引呢？一般说来，索引应建立在那些将用于JOIN, WHERE判断和ORDER BY排序的字段上。 尽量不要对数据库中某个含有大量重复的值的字段建立索引。 对于一个ENUM类型的字段来说，出现大量重复值是很有可能的情况，例如customerinfo中的“province”.. 字段，在这样的字段上建立索引将不会有什么帮助；相反，还有可能降低数据库的性能。 我们在创建表的时候可以同时创建合适的索引，也可以使用ALTER TABLE或CREATE INDEX在以后创建索引。 此外，MySQL从版本3.23.23开始支持全文索引和搜索。 全文索引在MySQL 中是一个FULLTEXT类型索引，但仅能用于MyISAM 类型的表。 对于一个大的数据库，将数据装载到一个没有FULLTEXT索引的表中，然后再使用ALTER TABLE或CREATE INDEX创建索引，将是非常快的。 但如果将数据装载到一个已经有FULLTEXT索引的表中，执行过程将会非常慢。 8、优化的查询语句绝大多数情况下，使用索引可以提高查询的速度，但如果SQL语句使用不恰当的话，索引将无法发挥它应有的作用。 下面是应该注意的几个方面。 首先，最好是在相同类型的字段间进行比较的操作。 在MySQL 3.23版之前，这甚至是一个必须的条件。 例如不能将一个建有索引的INT字段和BIGINT字段进行比较；但是作为特殊的情况，在CHAR类型的字段和VARCHAR类型字段的字段大小相同的时候，可以将它们进行比较。 其次，在建有索引的字段上尽量不要使用函数进行操作。 例如，在一个DATE类型的字段上使用YEAE()函数时，将会使索引不能发挥应有的作用。 所以，下面的两个查询虽然返回的结果一样，但后者要比前者快得多。 SELECT * FROM order WHERE YEAR(OrderDate)<2001;SELECT * FROM order WHERE OrderDate<2001-01-01;同样的情形也会发生在对数值型字段进行计算的时候：SELECT * FROM inventory WHERE Amount/7<24;SELECT * FROM inventory WHERE Amount<24*7;上面的两个查询也是返回相同的结果，但后面的查询将比前面的一个快很多。 第三，在搜索字符型字段时，我们有时会使用 LIKE 关键字和通配符，这种做法虽然简单，但却也是以牺牲系统性能为代价的。 例如下面的查询将会比较表中的每一条记录。 SELECT * FROM booksWHERE name like MySQL%但是如果换用下面的查询，返回的结果一样，但速度就要快上很多：SELECT * FROM booksWHERE name>=MySQLand name

数据库优化包括哪些相关操作？

此文章主要向大家介绍的是MySQL数据库优化，其中还包括MySQL数据库的性能优化， 常用的SQL语句的优化以及MySQL数据库对INSERT语句进行优化的实际操作方案的描述，望你会有所收获。 MySQL InnoDB 的性能问题讨论 MySQL性能优化 InnoDB delete from xxx速度暴慢原因 推荐圈子: mysql研究 更多相关推荐 1、定期分析表和检查表 分析表的语法如下： 引用 [LOCAL | NO_WRITE_TO_BINLOG] TABLE tb1_name[, tbl_name]... 以上语句用于分析和存储表的关键字分布，分析的结果将可以使得系统得到准确的统计信息，使得SQL能够生成正确的执行计划。 如果用户感觉实际执行计划并不是预期的执行计划，执行一次分析表可能会解决问题。 在分析期间，使用一个读取锁定对表进行锁定。 这对于MyISAM，DBD和InnoDB表有作用。 例如分析一个数据表 引用 table table_name 检查表的语法如下： 引用 TABLE tb1_name[,tbl_name]...[option] = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED} 检查表的作用是检查一个或多个表是否有错误，CHECK TABLE 对MyISAM 和 InnoDB表有作用，对于MyISAM表，关键字统计数据被更新 CHECK TABLE 也可以检查视图是否有错误，比如在视图定义中被引用的表不存在。 2. 定期优化表 MySQL数据库优化表的语法如下： 引用 [LOCAL | NO_WRITE_TO_BINLOG] TABLE tb1_name [,tbl_name]... 如果删除了表的一大部分，或者如果已经对含有可变长度行的表(含有 VARCHAR、BLOB或TEXT列的表)进行更多更改，则应使用OPTIMIZE TABLE命令来进行表优化。 这个命令可以将表中的空间碎片进行合并，并且可以消除由于删除或者更新造成的空间浪费，但OPTIMIZE TABLE 命令只对MyISAM、 BDB 和InnoDB表起作用。 例如： optimize table table_name 注意： analyze、check、optimize执行期间将对表进行锁定，因此一定注意要在数据库不繁忙的时候执行相关的操作。 常用的SQL优化 我们在开发的时候常常用到的SQL语句，无非是INSERT、GROUPBY等等。 对于这些SQL语句，我们怎么进行优化？ 1. 大批量插入数据 当用load命令导入数据的时候，适当的设置可以提高导入的速度。 对于MyISAM存储引擎的表，可以通过如下方式快速的导入大量的数据 引用 TABLE tb1_name DISABLE KEYS; the data TABLE tb1_name ENABLE KEYS; DISABLE KEYS 和 ENABLE KEYS 用来打开或者关闭MyISAM表非唯一索引的更新。 在导入大量的数据到一个非空的MyISAM表时，通过设置这两个命令，可以提高导入的效率。 对于导入大量的数据到一个空的MyISAM表时，默认就是先导入数据然后才创建索引的，索引不用进行设置。 引用 data infile /home/mysql/text_txt into table text 对于InnoDB类型的表，这种方式不能提高导入数据的效率，但也有几种针对InnoDB类型的表进行MySQL数据库优化的方式。 1. 因为InnoDB类型的表式按照主键的顺序保存的，所以将导入的数据按照主键的顺序排序，可以有效提高导入数据的效率。 2. 在导入数据前执行 SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SET UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率。 3. 如果应用使用自动提交的方式，建议在导入前执行SET AUTOCOMMIT=0，关闭自动提交，导入结束后执行SET AUTOCOMMIT=1，打开自动提交，也可以提高导入效率。 MySQL数据库优化INSERT语句 当进行数据INSERT的时候，可以考虑采用以下几种方式进行优化 1. 如果同时从一个客户插入很多行，尽量使用多个值表的INSERT语句，这种方式将大大缩短客户端与数据库的链接、关闭等消耗，使得效率比分开执行的单个INSERT语句快. 例如： into test values(1,2) into test values(3,4) into test values(5,6) 将上面三句改为:insert into test values(1,2),(3,4),(5,6)...... 2. 如果从不同客户插入很多行，能通过使用INSERT DELAYED 语句得到更高的速度。 DELAYED 的含义是让INSERT 语句马上执行，其实数据都被放在内存的队列中，并没有真正写入磁盘，这比每条语句分别插入要快得多；LOW_PRIORITY刚好相反，在所有其他用户对表的读写完后才进行插入。 3. 将索引文件和数据文件分在不同的磁盘上存放 4. 如果进行批量插入，可以增加bulk_insert_buffer_size变量值的方法来提高速度，但是，这只能对于MyISAM表使用。 5. 当从一个文本文件中装载一个表时，使用LOAD DATA INFILE。 这通常比使用很多insert语句快20倍左右。 以上的相关内容就是对MySQL数据库优化方法的介绍，望你能有所收获。

存储过程是不是对内存消耗很大呢？

存储过程的优缺点优点：1.由于应用程序随着时间推移会不断更改，增删功能，T－SQL过程代码会变得更复杂，StoredProcedure为封装此代码提供了一个替换位置。 2.执行计划（存储过程在首次运行时将被编译，这将产生一个执行计划－－ 实际上是 Microsoft SQL Server为在存储过程中获取由 T-SQL 指定的结果而必须采取的步骤的记录。 ）缓存改善性能。 ........但sql server新版本，执行计划已针对所有 T-SQL 批处理进行了缓存，而不管它们是否在存储过程中，所以没比较优势了。 3.存储过程可以用于降低网络流量，存储过程代码直接存储于数据库中，所以不会产生大量T-sql语句的代码流量。 4.使用存储过程使您能够增强对执行计划的重复使用，由此可以通过使用远程过程调用 (RPC) 处理服务器上的存储过程而提高性能。 RPC 封装参数和调用服务器端过程的方式使引擎能够轻松地找到匹配的执行计划，并只需插入更新的参数值。 5.可维护性高，更新存储过程通常比更改、测试以及重新部署程序集需要较少的时间和精力。 6.代码精简一致，一个存储过程可以用于应用程序代码的不同位置。 7.更好的版本控制，通过使用 Microsoft Visual sourceSafe 或某个其他源代码控制工具，您可以轻松地恢复到或引用旧版本的存储过程。 8.增强安全性：a、通过向用户授予对存储过程（而不是基于表）的访问权限，它们可以提供对特定数据的访问；b、提高代码安全，防止 SQL注入（但未彻底解决，例如，将数据操作语言－－DML，附加到输入参数）；c、SqlParameter 类指定存储过程参数的数据类型，作为深层次防御性策略的一部分，可以验证用户提供的值类型（但也不是万无一失，还是应该传递至数据库前得到附加验证）。 缺点：1.如果更改范围大到需要对输入存储过程的参数进行更改，或者要更改由其返回的数据，则您仍需要更新程序集中的代码以添加参数、更新 GetValue() 调用，等等，这时候估计比较繁琐了。 2.可移植性差由于存储过程将应用程序绑定到 SQL Server，因此使用存储过程封装业务逻辑将限制应用程序的可移植性。 如果应用程序的可移植性在您的环境中非常重要，则将业务逻辑封装在不特定于 RDBMS 的中间层中可能是一个更佳的选择。 没有内存消耗问题