什么是数据库索引，如何设计索引以优化数据库查询性能

1、主键就是聚集索引

2、只要建立索引就能显著提高查询速度

3、把所有需要提高查询速度的字段都加进聚集索引，以提高查询速度

　(四)其他书上没有的索引使用经验总结

1、用聚合索引比用不是聚合索引的主键速度快

2、用聚合索引比用一般的主键作order by时速度快，特别是在小数据量情况下

3、使用聚合索引内的时间段，搜索时间会按数据占整个数据表的百分比成比例减少，而无论聚合索引使用了多少个

4 、日期列不会因为有分秒的输入而减慢查询速度

(五)其他注意事项

1 不要索引常用的小型表

2 不要把社会保障号码(SSN)或身份z号码(ID)选作键

3 不要用用户的键

4 不要索引 memo/notes 字段和不要索引大型文本字段(许多字符)

5 使用系统生成的主键

　二、改善SQL语句

1、Like语句是否属于SARG取决于所使用的通配符的类型

2、or 会引起全表扫描

3、非 *** 作符、函数引起的不满足SARG形式的语句

4、IN 的作用相当与OR

5、尽量少用NOT

6、exists 和 in 的执行效率是一样的

7、用函数charindex()和前面加通配符%的LIKE执行效率一样

8、union并不绝对比or的执行效率高

9、字段提取要按照“需多少、提多少”的原则，避免“select ”

10、count()不比count(字段)慢

11、order by按聚集索引列排序效率最高

12、高效的TOP

有效的数据模型是为应用服务的，设计构架的关键问题是文档模型适合使用嵌入式模型（embed）还是使用引用模型（references）。

嵌入式数据模型（Embedded Data Models）

在MongoDB中，你可能将相关数据嵌入到一个单一结构或文档，这些模式通常被称为“非正规”模型,但是它充分利用了MongoDB富文档模型的有点。

嵌入式数据模型允许应用程序存储相关的信息在一条数据库记录中，这样应用程序可能需要更少的查询和更新来完成常规的 *** 作。

设计一个数据库需要我们耐心收集和分析数据，仔细理清数据间的关系，消除对数据库应用不利的隐患等等。在整个设计过程中，我们必须按步骤认真完成。一个数据库的设计好坏将直接影响将来基于该数据库的应用。

另外，数据库也不是独立存在的，它总是与具体的应用相关的，为具体的应用而建立的。因此在设计数据库之前我们必须明确应用的目的，在设计数据库的时候也应时刻考虑用户需求，数据库与具体应用之间是相辅相成的关系。

数据库的设计过程一般包括以下几个步骤：

确定建立数据库的目的和收集数据；

建立概念模型；

建立数据模型；

实施与维护数据库；

1．确定建立数据库的目的和收集数据

数据库设计过程的第一个阶段是确定建立数据库的目的和收集数据。通常，我们也把确定建立数据库的目的称为需求分析。需求分析的任务就是通过详细调查要处理的对象来明确用户的各种需求。并且通过调查、收集和分析信息，以了解在数据库中需要存储哪些数据，要完成什么样的数据处理功能。这一过程是数据库设计的起点，它将直接影响到后面各个阶段的设计，并影响到设计结果是否合理和实用。

确定目的之后就需要根据目的收集有用的数据。在着手收集数据之前最重要的就是要调查用户的实际需求，然后分析与表达这些需求。调查用户需求的方法有很多，如查阅记录、访谈、开调查会、设计调查表请用户填写或回答相关问题等。其中比较有效的方法是访谈，我们可以借助一些设计合理的调查表来与用户直接交流。通过充分交流，可以了解他们平时是如何使用数据库的，以及对当前信息的要求，进而设计满足用户需求的字段，并根据设计的字段收集数据。

2．建立概念模型

确定建立数据库的目的以及完成数据收集后，就进入数据库设计过程的第二阶段——建立概念模型。这一阶段是整个数据库设计的关键。设计时，一般先根据应用的需求，画出能反映每个应用需求的E-R图，其中包括确定实体、属性和联系的类型。然后优化初始的E-R图，消除冗余和可能存在的矛盾。概念模型是对用户需求的客观反映，并不涉及具体的计算机软、硬件环境。因此，在这一阶段中我们必须将注意力集中在怎样表达出用户对信息的需求，而不考虑具体实现问题。

3．建立数据模型

完成上一阶段后，我们得到了一个与具体计算机软、硬件无关的概念模型。接着我们就可以着手建立数据库模型了，这是数据库设计过程的第三个阶段。在这一阶段中我们要将概念模型中得到的E-R图转换成具体的数据模型。通过前面的学习，我们已经了解到数据模型一般分为层次、网状、关系和面向对象模型等。目前比较常用的是关系数据模型，我们通常将E-R图转换成关系数据模型，实际上就是要将实体、实体的属性和实体之间的联系转换为关系模式。

4．实施与维护数据库

最后一个阶段是实施与维护数据库。完成数据模型的建立后，我们就必须对字段进行命名，确定字段的类型和宽度，并利用数据库管理系统或数据库语言创建数据库结构、输入数据和运行等，因此数据库的实施是数据库设计过程的“最终实现”。如果数据库运行很成功，则表明数据库设计任务基本结束，以后的重点就是数据库的维护工作，包括做好备份工作、数据库的安全性和完整性调整、改善数据库性能等。

数据库的设计在数据库应用系统的开发中占有很重要的地位。只有设计出合理的数据库，才能为建立在数据库上的应用提供方便。不过数据库的设计过程从来都不会有真正的结束，因为随着用户需求和具体应用的变化和扩大，数据库的结构也可能会随之变化。

数据库基本的功能：

信息浏览和查询；

信息的修改、添加和删除；

信息的统计、汇总等。

设计数据库时要注意保留以下内容：

设计文档、内容 *** 作说明，实例数据库、帮助及过程性文件（如下载的资源、工作日志）等。

影响数据库性能的因素

对于数据库爱好者们，数据库底层的各种细节，内幕，等待事件，隐藏参数等津津乐道，对于调整好一条SQL语句使之在查询优化器/查询引擎下能高性能运转具有巨大的满足感成功感，仿佛自己掌握了天下最有价值的真理，驾驭了天下最有难度的技术。但对于设计和开发出这个数据库系统的人来说，他们看到此情此景，只好躲在一边偷偷的笑了。那么问题来了，使用别人数据库的人被称为大师（如：OCM），那么自己写出一个数据库来的人又该称为什么呢？到底谁才是真正的高手呢？

数据库系统优化中的一些观点：

“系统性能出现问题进行优化，一定要深入了解数据库内部参数、等待事件、Latch、缓冲池、trace文件、查询/优化引擎等底层细节。”

这种观点往往出自数据库“高手”，这部分人以了解数据库底层实现细节而感到非常骄傲。但是从优化角度讲数据库的等待事件、Latch等指标高等等都只是问题的表象，懂得底层细节和内幕固然是好。但是解决问题的关键往往是在应用层进行优化。

“只要系统参数调整了，性能就能提高。系统优化应该调整那些参数…”

这种观点往往出自于一些偏运维和应用层的DBA，迷恋参数配置来调优。

调整系统参数是非常重要的，但不一定能解决性能问题，否则就不会有去IOE了，问题可能性最大的还是应用设计和开发问题。

同理，很多运维人员和系统架构师比较迷恋“Linux系统调优”。认为对“文件句柄数、磁盘子系统…”那些做了优化，就能提升整个应用系统的性能。其实不然。有些场景下，针对业务特点和应用类型做 *** 作系统调优是能取到立竿见影的效果，但是大多数时候往往提升并不明显。所以最关键的还是找出瓶颈所在，对症下药。/

“系统性能问题需要从架构上解决，与应用开发关系不大。”

系统性能与各个层面都有关，架构很重要，但应用开发也是非常重要的一环。

影响数据库性能的因素

1业务需求和技术选型

2应用系统的开发及架构

3数据库自身

31表结构的设计

32查询语句

33索引设计

34Mysql服务（安装、配置等）

35 *** 作系统调优

36硬件升级（SSD、更强的CPU、更大的内存）

4数据架构（读写分离、分库分表等）

在很多情况下，数据库可能是互联网应用系统的瓶颈。但是单纯从数据库角度去做优化，可能未必能达到理想的效果。

说点题外话，最近看到很多公司使用中间件或者分布式数据访问层来做数据库分片，说明也许该公司业务发展很快。但另一个方面，也令人担忧，他们的数据库压力真的已经到了必须切分不可的程度了吗？分库分表真的像科普的那么简单吗？他们能搞定分库分表带来的成本和问题吗？有没有更合适的优化方法呢？

当然是有的。其实“过度设计”和“提前优化”就是系统万恶之源。

1、使你的数据库结构规范化，但是不要求一定达到第三范式，为了显示和打印目的可以有数据冗余2、评估你的系统中对性能影响的关键处，减少被频繁访问的核心表的数量，并在这些核心表上重点优化索引，表结构（尽量紧凑）。典型的核心表是代码表。3、对于统计类应用，如果可能应写成触发器和存储过程，这样就有可能把一个消耗大量时间的统计运算分布到每INSERT，DELETE，或者UPDATE来处理，从而极大提高查询类 *** 作的速度。查询选择群居索引最有效。其他索引也要针对业务进行选择。由于维护索引也要消耗系统资源和时间，所以过多的索引对性能是损害甚至是毫无效果的。5、如果可能，可以利用大数据库对SQL的一些特殊规定来进一步优化，比如查询暗示。6、适当选择硬件，综合考虑CPU，内存，I/O系统的性能，以当前的CPU，内存配置来看，很多数据库系统的瓶颈出在I/O系统上。所以如果有可能，最好使用RAID。当然如果你有足够的财力，可以买更好的服务器，或者搞服务器集群就更利害啦。7、可能的话，尽量使用存储过程，因为存储过程的执行计划可以重复使用，而且不需要象普通由CLIENT提交的SQL那样进行处理和编译。8、检查你的应用程序设计，如果有可能，尽量减少查询次数和在网络上往返的数据。为了获取少量字段而写SELECT 对性能的损害也比较利害。9、在应用程序中协调并发和一致性之间的矛盾。并不是所有业务都需要放在事务中。大量业务是允许脏读的，在不关键事务中使用脏读，或者读提交，可以大大降低DEADLOCK和进程之间彼此等待的机会，从而把由于互相锁定资源引起的等待降低到最小。不要在事务执行中进行大量计算或者与用户交互的 *** 作，因为事务的执行在要求上是不允许被打断的原子 *** 作（回滚是失败的），所以事务应该多而短小。长事务会锁住很多资源比较长的时间，因此也比较容易导致其他进程对资源的等待和死锁的机会。10、评估你开发系统的关键业务，在很多数据库系统对性能的要求是彼此矛盾的，比如OLTP应用和DSS是不同的。DSS倾向于使用各种索引加快检索速度，而大量的索引对OLTP则是负担。11、不要在应用程序中写怪异的SQL 查询，比如 WHERE money!40000，这样的语句，这种SQL查询，数据库的SQL优化器是无法进行优化的。12、定期维护和管理你的数据库系统，压缩掉那些垃圾空间，很多数据库系统执行类似删除，事务等 *** 作的时候，并不回收无用的物理空间。所以，制定一份合理的数据库维护计划，不要等日志文件或者LOG文件越长越大的时候才去整理数据库。还有很多很多要注意的东西，。。。。。。

摘要 结合DB 的使用经验 从数据库设计 查询优化 并发控制 客户/服务器模式四个方面来讨论数据库应用系统性能优化的一些原则 方法等

关键词 DB 性能优化 数据库设计 查询优化 并发控制 C/S模式

引言

DB 是一种高性能的大型关系数据库管理系统 广泛的应用在客户/服务器体系结构中 评价系统性能优化的标准有 吞吐量 响应时间 并行能力等 本文从数据库的设计 查询的优化 并发控制以及客户/服务器模式这四个角度来讨论优化系统性能

设计数据库

熟悉业务系统

对业务系统的熟悉程度对整个数据库系统的性能有很大影响 一个对业务不熟悉的设计人员 尽管有丰富的数据库知识 也很难设计出性能最佳的数据库应用系统

规范化与非规范化

数据库被规范化后 减少了数据冗余 数据量变小 数据行变窄 这样DB 的每一页可以包括更多行 那么每一区里的数据量更多 从而加速表的扫描 改进了单个表的查询性能 但是 当查询涉及多个表的时候 需要用很多连接 *** 作把信息从各个表中组合在一起 导致更高的CPU和I/O花销 那么 有很多时候需要在规范化和非规范化之间保持平衡 用适当的冗余信息来减少系统开销 用空间代价来换取时间代价 有订单信息表OrderDetail 它里面记录了投递员信息 收款员信息 物品信息 价格策略 客户信息… 这些信息分别在投递员信息表 收款员信息表 物品信息表 价格策略表 客户信息表中存放 如果按照规范化的要求 OrderDetail查询时就必须要与这么多个表进行连接或者嵌套查询 如果OrderDetail表中的数据量是在百万级的 那么一次查询所需要的时间可能会达到好几个小时 事实上 只要在设计时保证数据的逻辑有效性 很多信息都可以直接冗余在OrderDetail表中 这些冗余的数据能够极大的提高查询的效率 从而减少CPU和I/O *** 作

数据条带化

如果一个表的记录条数超过一定的规模 那么最基本的查询 *** 作也会受到影响 需要将该表根据日期水平划分 把最近 最经常用的数据和历史的 不经常用的数据划分开来 或是根据地理位置 部门等等进行划分 还有一种划分方式――垂直划分 即把一个属性列很多的表分割成好几个小表 比如把经常用到的属性放在一个表里 不经常用到的属性放在另一个表里 这样可以加快表的扫描 提高效率

选择数据类型

对每一属性选择什么样的数据类型很大程度上依据表的要求 但是在不违背表要求的前提下 选择适当的数据类型可以提高系统性能 比如有text列存放一本书的信息 用BLOB而不是character( ) BLOB存放的是指针或者文件参照变量 真正的文本信息可以放在数据库之外 从而减少数据库存储空间 使得程序运行的速度提高 DB 提供了UDT（User Defined Datatypes）功能 用户可以根据自己的需要定义自己的数据类型

选择索引

索引是数据库中重要的数据结构 它的根本目的就是为了提高查询效率 现在大多数的数据库产品都采用IBM最先提出的ISAM索引结构 使用索引可以快速 直接 有序的存取数据 索引的建立虽然加快了查询 另一方面却将低了数据更新的速度 因为新数据不仅要增加到表中 也要增加到索引中 另外 索引还需要额外的磁盘空间和维护开销 因此 要合理使用索引

●在经常进行连接 但是没有指定为外键的属性列上建立索引

●在频繁进行排序或分组（即进行group by或order by *** 作）的列上建立索引 按索引来排序或分组 可以提高效率

●在条件表达式中经常用到的不同值较多的列上建立检索 在不同值少的列上不要建立索引

●如果待排序的列有多个 可以在这些列上建立复合索引（pound index） 即索引由多个字段复合而成

查询优化

现在的数据库产品在系统查询优化方面已经做得越来越好 但由于用户提交的SQL语句是系统优化的基础 很难设想一个原本糟糕的查询计划经过系统的优化之后会变得高效 因此用户所写语句的优劣至关重要 下面重点说明改善用户查询计划的解决方案

． 排序

在很多时候 应当简化或避免对大型表进行重复的排序 当能够利用索引自动以适当的次序产生输出时 可以避免排序的步骤 当以下的情况发生时 排序就不能省略

●索引中不包括一个或几个待排序的列

●group by或order by子句中列的次序与索引的次序不一样

●排序的列来自不同的表

为了避免不必要的排序 就要正确地增建索引 合理地合并数据库表 尽管有时可能影响表的规范化 但相对于效率的提高是值得的 如果排序不可避免 那么应当试图简化它 如缩小排序列的范围等

． 主键

主键用整型会极大的提高查询效率 而字符型的比较开销要比整型的比较开销大很多 用字符型数据作主键会使数据插入 更新与查询的效率降低 数据量小的时候这点降低可能不会被注意 可是当数据量大的时候 小的改进也能够提高系统的响应速度

． 嵌套查询

在SQL语言中 一个查询块可以作为另一个查询块中谓词的一个 *** 作数 因此 SQL查询可以层层嵌套 例如在一个大型分布式数据库系统中 有订单表Order 订单信息表OrderDetail 如果需要两表关联查询

SELECT CreateUserFROM Order WHERE OrderNo IN(SELECT OrderNoFROM OrderDetail WHERE Price= )

在这个查询中 找出报纸单价为 元的收订员名单 下层查询返回一组值给上层查询 然后由上层查询块再根据下层块提供的值继续查询 在这种嵌套查询中 对上层查询的每一个值OrderNo 下层查询都要对表OrderDetail进行全部扫描 执行效率显然不会高 在该查询中 有 层嵌套 如果每层都查询 行 那么这个查询就要查询 万行数据 在系统开销中 对表Order的扫描占 ％ 对表OrderDetail的搜索占 ％ 如果我们用连接来代替 即

SELECT CreateUserFROM Order OrderDetailWHERE Order OrderNo=OrderDetail OrderNo AND Praice=

那么对表Order的扫描占 ％ 对表OrderDetail的搜索占 ％

而且 一个列的标签同时在主查询和where子句中的查询中出现 那么很可能当主查询中的列值改变之后 子查询必须重新查询一次 查询嵌套层次越多 效率越低 因此应当尽量避免子查询 如果子查询不可避免 那么要在子查询中过滤掉尽可能多的行

． 通配符

在SQL语句中 LIKE关键字支持通配符匹配 但这种匹配特别耗费时间 例如 SELECT FROM Order WHERE CreateUser LIKE M_ _ _ 即使在CreateUser字段上建立了索引 在这种情况下也还是采用顺序扫描的方式 Order表中有 条记录 就需要比较 次 如果把语句改为SELECT FROM Order WHERE CreateUser > M AND CreateUser < N 在执行查询时就会利用索引来查询 显然会大大提高速度

． distinct

使用distinct是为了保证在结果集中不出现重复值 但是distinct会产生一张工作表 并进行排序来删除重复记录 这会大大增加查询和I/O的 *** 作次数 因此应当避免使用distinct关键字

． 负逻辑

负逻辑如!= <> not in等 都会导致DB 用表扫描来完成查询 当表较大时 会严重影响系统性能 可以用别的 *** 作来代替

． 临时表

使用临时表时数据库会在磁盘中建立相应的数据结构 因为内存的访问速度远远大于外部存储器的访问速度 在复杂查询中使用临时表时 中间结果会被导入到临时表中 这种磁盘 *** 作会大大降低查询效率 另外 在分布式系统中 临时表的使用还会带来多个查询进程之间的同步问题 所以 在进行复杂查询时最好不要使用临时表

． 存储过程

DB 中的Stored Procedure Builder可以产生存储过程 运行并测试存储过程 存储过程可以包含巨大而复杂的查询或SQL *** 作 经过编译后存储在DB 数据库中 用户在多次使用同样的SQL *** 作时 可以先把这些SQL *** 作做成存储过程 在需要用到的地方直接引用其名字进行调用 存储过程在第一次执行时建立优化的查询方案 DB 将查询方案保存在高速缓存里 以后调用运行时可以直接从高速缓存执行 省去了优化和编译的阶段 节省了执行时间 从而提高效率和系统利用率

最优的查询方案按照某些标准选择往往不可行 要根据实际的要求和具体情况 通过比较进行选择 DB 提供的Query Patroller可以对不同的查询方案的查询代价进行比较 通过追踪查询语句 返回查询不同阶段的系统开销 从而作出最佳选择 DB 提供的Performance Monitor也对整个数据库系统的性能进行监控 包括I/O时间 查询次数 排序时间 同步读写时间等等

数据库系统的并发控制也能影响系统性能 多个用户的同时 *** 作可能导致数据的不一致性 DB 为了防止同时修改造成数据丢失和访问未被提交的数据 以及数据的保护读 采用Lock机制来实现控制

lishixinzhi/Article/program/DB2/201311/21921

(1)存储记录结构设计综合分析数据存储要求和应用需求，设计存储记录格式

(2)存储空间分配存储空间分配有两个原则：①存取频度高的数据尽量安排在快速、随机设备上，存取频度低的数据则安排在速度较慢的设备上

②相互依赖性强的数据尽量存储在同一台设备上，且尽量安排在邻近的存储空间上

从提高系统性能方面考虑，应将设计好的存储记录作为一个整体合理地分配物理存储区域

尽可能充分利用物理顺序特点，把不同类型的存储记录指派到不同的物理群中

(3)访问方法的设计一个访问方法包括存储结构和检索机构两部分

存储结构限定了访问存储记录时可以使用的访问路径；检索机构定义了每个应用实际使用的访问路径

(4)物理设计的性能评价①查询响应时间从查询开始到有结果显示之间所经历的时间称为查询响应时间

查询响应时间可进一步细分为服务时间、等待时间和延迟时间

在物理设计过程中，要对系统的性能进行评价

性能评价包括时间、空间、效率、开销等各个方面

⊙CPU服务时间和I/O服务时间的长短取决于应用程序设计

⊙CPU队列等待时间和I/O队列等待时间的长短受计算机系统作业的影响

⊙设计者可以有限度地控制分布式数据库系统的通信延迟时间

②存储空间存储空间存放程序和数据

程序包括运行的应用程序、DBMS子程序、OS子程序等

数据包括用户工作区、DBMS工作区、OS工作区、索引缓冲区、数据缓冲区等

存储空间分为主存空间和辅存空间

设计者只能有限度地控制主存空间，例如可指定缓冲区的分配等

但设计者能够有效地控制辅存空间

③开销与效率设计中还要考虑以下各种开销，开销增大，系统效率将下降

⊙事务开销指从事务开始到事务结束所耗用的时间

更新事务要修改索引、重写物理块、进行写校验等 *** 作，增加了额外的开销

更新频度应列为设计的考虑因素

⊙报告生成开销指从数据输入到有结果输出这段时间

报告生成占用CPU及I/O的服务时间较长

设计中要进行筛选，除去不必要的报告生成

⊙对数据库的重组也是一项大的开销

设计中应考虑数据量和处理频度这两个因数，做到避免或尽量减少重组数据库

在物理设计阶段，设计、评价、修改这个过程可能要反复多次，最终得到较为完善的物理数据库结构说明书

建立数据库时，DBA依据物理数据库结构说明书，使用DBMS提供的工具可以进行数据库配置

在数据库运行时，DBA监察数据库的各项性能，根据依据物理数据库结构说明书的准则，及时进行修正和优化 *** 作，保证数据库系统能够保持高效率地运行

6

程序编制及调试在逻辑数据库结构确定以后，应用程序设计的编制就可以和物理设计并行地展开程序模块代码通常先在模拟的环境下通过初步调试，然后再进行联合调试

联合调试的工作主要有以下几点：(1)建立数据库结构根据逻辑设计和物理设计的结果，用DBMS提供的数据语言(DDL)编写出数据库的源模式，经编译得到目标模式，执行目标模式即可建立实际的数据库结构

(2)调试运行数据库结构建立后，装入试验数据，使数据库进入调试运行阶段

运行应用程序，测试(3)装入实际的初始数据在数据库正式投入运行之前，还要做好以下几项工作：(1)制定数据库重新组织的可行方案

(2)制定故障恢复规范(3)制定系统的安全规范7

运行和维护数据库正式投入运行后，运行维护阶段的主要工作是：(1)维护数据库的安全性与完整性

按照制定的安全规范和故障恢复规范，在系统的安全出现问题时，及时调整授权和更改密码

及时发现系统运行时出现的错误，迅速修改，确保系统正常运行

把数据库的备份和转储作为日常的工作，一旦发生故障，立即使用数据库的最新备份予以恢复

(2)监察系统的性能

运用DBMS提供的性能监察与分析工具，不断地监控着系统的运行情况

当数据库的存储空间或响应时间等性能下降时，立即进行分析研究找出原因，并及时采取措施改进

例如，可通修改某些参数、整理碎片、调整存储结构或重新组织数据库等方法，使数据库系统保持高效率地正常运作

(3)扩充系统的功能在维持原有系统功能和性能的基础上，适应环境和需求的变化，采纳用户的合理意见，对原有系统进行扩充，增加新的功能

以上就是关于什么是数据库索引，如何设计索引以优化数据库查询性能全部的内容，包括:什么是数据库索引，如何设计索引以优化数据库查询性能、如何设计高性能mongodb数据库、谈谈与数据库设计步骤相关的话题( 1 ) 同学们已学习完“项目一-需求分析”模等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！