2022年数据库索引及优化 .pdf

《2022年数据库索引及优化 .pdf》由会员分享，可在线阅读，更多相关《2022年数据库索引及优化 .pdf（14页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、据库索引 SQL Server 的 B树结构编辑本段 索引数据库索引好比是一本书后的索引，能加快数据库的查询速度。例如这样一个查询：SELECT*FROM TABLE1 WHERE ID=44。如果没有索引，必须遍历整个表，直到ID 等于 44 的这一行被找到为止；有了索引之后(必须是在 ID 这一列上建立的索引)，直接在索引里面找44，就可以得知这一行的位置，也就是找到了这一行。可见，索引是用来定位的。索引分为聚簇索引和非聚簇索引两种，聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引就不一样了；聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索很快。编辑本段 索引的优缺点概述建立

2、索引的目的是加快对表中记录的查找或排序。为表设置索引要付出代价的：一是增加了数据库的存储空间，二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。详述创建索引可以大大提高系统的性能。第一，通过创建唯一性索引，名师资料总结-精品资料欢迎下载-名师精心整理-第 1 页，共 14 页 -可以保证数据库表中每一行数据的唯一性。第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。第五，通过使用索引，可以在查询的过程中，使用优

3、化隐藏器，提高系统的性能。也许会有人要问：增加索引有如此多的优点，为什么不对表中的每一个列创建一个索引呢？因为，增加索引也有许多不利的方面。第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。索引是建立在数据库表中的某些列的上面。在创建索引的时候，应该考虑在哪些列上可以创建索引，在哪些列上不能创建索引。一般来说，应该在这些列上创建索引：在经常需要搜索的列上，

4、可以加快搜索的速度；在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；在经常需要排序的列上创名师资料总结-精品资料欢迎下载-名师精心整理-第 2 页，共 14 页 -建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。同样，对于有些列不应该创建索引。一般来说，不应该创建索引的的这些列具有下列特点：第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这

5、是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。第三，对于那些定义为text,image和 bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，

6、会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。编辑本段 B 树索引是一个称为 B 树的数据结构。图中每个矩形称为一个索引结名师资料总结-精品资料欢迎下载-名师精心整理-第 3 页，共 14 页 -点，或者一页索引。上述例子中的 44，位于最末一行的第二个结点，是通过类似于折半查找的方法找到的。如果是聚簇索引，这个结点中便包含了要找的整行信息；如果是非聚簇索引，这个结点中包含了要找的那一行的地址(即指向该行的指针)。MySQL数据库优化方法总结2008-10-21 03:21数据库优化是一项很复杂的工作，因为这

7、最终需要对系统优化的很好理解才行。尽管对系统或应用系统的了解不多的情况下优化效果还不错，但是如果想优化的效果更好，那么就需要对它了解更多才行。1、优化概述让系统运行得快得最重要因素是数据库基本的设计。并且还必须清楚您的系统要用来做什么，以及存在的瓶颈。最常见的系统瓶颈有以下几种：磁盘搜索。它慢慢地在磁盘中搜索数据块。对现代磁盘来说，平时的搜索时间基本上小于10 毫秒，因此理论上每秒钟可以做100 次名师资料总结-精品资料欢迎下载-名师精心整理-第 4 页，共 14 页 -磁盘搜索。这个时间对于全新的新磁盘来说提高的不多，并且对于只有一个表的情况也是如此。加快搜索时间的方法是将数据分开存放到多个

8、磁盘中。磁盘读/写。当磁盘在正确的位置上时，就需要读取数据。对现代磁盘来说，磁盘吞吐量至少是10-20MB/秒。这比磁盘搜索的优化更容易，因为可以从多个媒介中并行地读取数据。CPU周期。数据存储在主内存中(或者它已经在主内存中了)，这就需要处理这些数据以得到想要的结果。内存带宽。当 CPU要将更多的数据存放在CPU缓存中时，主内存的带宽就是瓶颈了。在大多数系统中，这不是常见的瓶颈，不过也是要注意的一个因素。1.1 MySQL 设计的局限性当使用 MyISAM存储引擎时，MySQL会使用一个 快速数据表锁以允许同时多个读取和一个写入。这种存储引擎的最大问题是发生在一个单一的表上同时做稳定的更新操

9、作及慢速查询。如果这种情况在某个表中存在，可以使用另一种表类型。名师资料总结-精品资料欢迎下载-名师精心整理-第 5 页，共 14 页 -MySQL可以同时在事务及非事务表下工作。为了能够平滑的使用非事务表(发生错误时不能回滚)，有以下几条规则：所有的字段都有默认值如果字段中插入了一个 错误的值，比如在数字类型字段中插入过大数值，那么 MySQL会将该字段值置为 最可能的值 而不是给出一个错误。数字类型的值是0，最小或者最大的可能值。字符串类型，不是空字符串就是字段所能存储的最大长度。所有的计算表达式都会返回一个值而报告条件错误，例如1/0 返回 NULL。这些规则隐含的意思是，不能使用MyS

10、QL来检查字段内容。相反地，必须在存储到数据库前在应用程序中来检查。1.2 应用设计的可移植性由于各种不同的数据库实现了各自的SQL标准，这就需要我们尽量使用可移植的SQL应用。查询和插入操作很容易就能做到可移植，不过由于更多的约束条件的要求就越发困难。想要让一个应用在各种数据库系统上快速运行，就变得更困难了。名师资料总结-精品资料欢迎下载-名师精心整理-第 6 页，共 14 页 -为了能让一个复杂的应用做到可移植，就要先看这个应用运行于哪种数据库系统之上，然后看这些数据库系统都支持哪些特性。每个数据库系统都有某些不足。也就是说，由于设计上的一些妥协，导致了性能上的差异。可以用 MySQL 的

11、 crash-me 程序来看选定的数据库服务器上可以使用的函数，类型，限制等。crash-me 不会检查各种可能存在的特性，不过这仍然是合乎情理的理解，大约做了450 次测试。一个crash-me 的信息类型的例子就是，它会告诉您如果想使用Informix 或 DB2的话，就不能使字段名长度超过18 个字符。crash-me 程序和 MySQL基准使每个准数据库都实现了的。可以通过阅读这些基准程序是怎么写的，自己就大概有怎样做才能让程序独立于各种数据库这方面的想法了。这些程序可以在MySQL源代码的 sql-bench 目录下找到。他们大部分都是用Perl 写的，并且使用DBI 接口。由于它提

12、供了独立于数据库的各种访问方式，因此用DBI来解决各种移植性的问题。如果您想努力做到独立于数据库，这就需要对各种SQL服务器的瓶颈都有一些很好的想法。例如，MySQL对于 MyISAM 类型的表在检索以及更新记录时非常快，但是在有并发的慢速读取及写入记录时名师资料总结-精品资料欢迎下载-名师精心整理-第 7 页，共 14 页 -却有一定的问题。作为 Oracle来说，它在访问刚刚被更新的记录时有很大的问题(直到结果被刷新到磁盘中)。事务数据库一般地在从日志表中生成摘要表这方面的表现不怎么好，因为在这种情况下，行记录锁几乎没用。为了能让应用程序真正的做到独立于数据库，就必须把操作数据的接口定义的

13、简单且可扩展。由于C 在很多系统上都可以使用，因此使用 C 作为数据库的基类结果很合适。如果使用了某些数据库独有的特定功能(比如REPLACE 语句就只在 MySQL中独有)，这就需要通过编写替代方法来在其他数据库中实现这个功能。尽管这些替代方法可能会比较慢，但是它能让其他数据库实现同样的功能。在 MySQL中，可以在查询语句中使用/*!*/语法来增加 MySQL特有的关键字。然而在很多其他数据库中，/*/却被当成了注释(并且被忽略)。如果有时候更高的性能比数据结果的精确更重要，就像在一些Web 应用中那样，这可以使用一个应用层来缓存结果，这可能会有更高的性能。通过让旧数据在一定时间后过期，来

14、合理的更新缓存。这是处理负载高峰期时的一种方法，这种情况下，可以通过加大缓存容名师资料总结-精品资料欢迎下载-名师精心整理-第 8 页，共 14 页 -量和过期时间直到负载趋于正常。这种情况下，建表信息中就要包含了初始化缓存的容量以及正常刷新数据表的频率。一个实现应用层缓存的可选方案是使用MySQL的查询缓存(query cache)。启用查询缓存后，数据库就会根据一些详情来决定哪些结果可以被重用。它大大简化了应用程序。1.3 我们都用 MySQL来做什么在 MySQL最开始的开发过程中，MySQL本来是要准备给大客户用的，他们是瑞典的 2 个最大的零售商，他们用于货物存储数据管理。我们每周从

15、所有的商店中得到交易利润累计结果，以此给商店的老板提供有用的信息，帮助他们分析如果更好的打广告以影响他们的客户。数据量相当的大(每个月的交易累计结果大概有7 百万)，而且还需要显示 4-10 年间的数据。我们每周都得到客户的需求，他们要求能瞬间地得到数据的最新报表。我们把每个月的全部信息存储在一个压缩的交易表中以解决这个问题。我们有一些简单的宏指令集，它们能根据不同的标准从存名师资料总结-精品资料欢迎下载-名师精心整理-第 9 页，共 14 页 -储的交易表中根据字段分组(产品组、客户id、商店等等)取得结果。我们用一个小Perl 脚本动态的生成Web 页面形式的报表。这个脚本解析 Web 页

16、面，执行 SQL语句，并且插入结果。我们还可以用PHP或者 mod_perl 来做这个工作，不过当时还没有这2 个工具。为了得到图形数据，我们还写了一个简单的C语言工具，用于执行 SQL查询并且将结果做成GIF图片。这个工具同样是Perl脚本解析Web 页面后动态执行的。很多情况下，只要拷贝现有的脚本简单的修改里面的SQL查询语句就能产生新的报表了。有时候，就需要在现存的累计表中增加更多的字段或者新建一个。这个操作十分简单，因为我们在磁盘上存储有所有的交易表(总共大概有 50G 的交易表以及 20G的其他客户资料)。我们还允许客户通过ODBC直接访问累计表，这样的话，那些高级用户就可以自己利用

17、这些数据做试验了。这个系统工作的很好，并且在适度的 Sun Ultra SPARC 工作站(2x200MHz)上处理数据没有任何问题。最终这个系统移植到了Linux上。1.4 MySQL 基准套件基准套件就是想告诉用户执行什么样的SQL查询表现的更好或名师资料总结-精品资料欢迎下载-名师精心整理-第 10 页，共 14 页 -者更差。请注意，这个基准是单线程的，因此它度量了操作执行的最少时间。我们未来打算增加多线程测试的基准套件。想要使用基准套件，必备以下几个条件：基准脚本是用 Perl写的，它用 Perl 的 DBI模块来连接数据库，因此必须安装 DBI模块。并且还需要每个要做测试的服务器上

18、都有特定的 BDB驱动程序。例如，为了测试MySQL、PostgreSQL 和 DB2，就必须安装DBD:mysql,DBD:Pg 及 DBD:DB2 模块。详情请看 2.7 Perl Installation Note。取得 MySQL的分发源代码后，就能在 sql-bench 目录下看到基准套件。想要运行这些基准测试，请先搭建好服务，然后进入sql-bench 目录，执行run-all-tests 脚本：shell cd sql-bench shell perl run-all-tests-server=server_name server_name 可以是任何一个可用的服务。想要列出所有

19、的可用选项和支持的服务，只要调用以下命令：名师资料总结-精品资料欢迎下载-名师精心整理-第 11 页，共 14 页 -shell perl run-all-tests-help crash-me 脚本也是放在sql-bench 目录下。crash-me 通过执行真正的查询以试图判断数据库都支持什么特性、性能表现以及限制。例如，它可以判断：都支持什么字段类型支持多少索引支持什么样的函数能支持多大的查询VARCHAR 字段类型能支持多大1.5 使用您自己的基准请确定对您的数据库或者应用程序做基准测试，以发现它们的瓶颈所在。解决这个瓶颈(或者使用一个假的模块来代替)之后，就能很容易地找到下一个瓶颈了

20、。即使应用程序当前总体的表现可以接受，不过还是至少要做好找到每个瓶颈的计划，说不定某天您就希望应用名师资料总结-精品资料欢迎下载-名师精心整理-第 12 页，共 14 页 -程序能有更好的性能。从 MySQL的基准套件中就能找到一个便携可移植的基准测试程序了。详情请看 7.1.4 The MySQL Benchmark Suite。您可以从基准套件中的任何一个程序，做适当的修改以适合您的需要。通过整个方式，您就可以有各种不同的办法来解决问题，知道哪个程序才是最快的。当系统负载十分繁重的时候，通常就会发生问题。我们就有很多客户联系我们说他们有一个(测试过的)生产系统也遭遇了负载问题。在很多情况下

21、，性能问题归结于数据库的基本设计(例如，在高负载下扫描数据表的表现不好)、操作系统、或者程序库等因素。很多时候，这些问题在还没有正式用于生产前相对更容易解决。2、优化SELECT 语句及其他查询首先，影响所有语句的一个因素是：您的权限设置越复杂，那么开销就越大。使用比较简单的GRANT 语句能让MySQL 减少在客户端执行语句时权限检查的开销。例如，如果没有设定任何表级或者字段级的权限，那么服务器就无需检查tables_priv 和 columns_priv 表的记录了。同样地，如果没有对帐户设定任何资源限制的话，那么服务器也就无需做资源使用统计了。如果有大量查询的话，花点时间来规划简单的授权机制以减少服务器权限检查的开销是值得的。名师资料总结-精品资料欢迎下载-名师精心整理-第 13 页，共 14 页 -如果问题处在一些MySQL特定的表达式或者函数上，则可以通过 mysql 客户端程序使用BENCHMARK()函数做一个定时测试。它的语法是：BENCHMARK(loop_count,expression)。例如：名师资料总结-精品资料欢迎下载-名师精心整理-第 14 页，共 14 页 -