01.提高数据库查询效率

一：使用索引

数据库设计方面：

• 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
• 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num is null可以在num上设置默认值0，确保表中num列没有null值，然后这样查询： select id from t where num=0
• 并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时,查询可能不会去利用索引。
• 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
• 应尽可能的避免更新索引数据列，因为索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新索引数据列，那么需要考虑是否应将该索引建为索引。
• 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了
• 尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
•  尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。
• 避免频繁创建和删除临时表，以减少系统表资源的消耗。
•  临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。
• 在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。
• 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

• 列类型尽量定义成数值类型，且长度尽可能短，如主键和外键，类型字段等等
• 建立单列索引
• 根据需要建立多列联合索引
• 当单个列过滤之后还有很多数据，那么索引的效率将会比较低，即列的区分度较低
• 如果在多个列上建立索引，那么多个列的区分度就大多了，将会有显著的效率提高。
• 根据业务场景建立覆盖索引只查询业务需要的字段，如果这些字段被索引覆盖，将极大的提高查询效率
• 多表连接的字段上需要建立索引，这样可以极大提高表连接的效率
• where条件字段上需要建立索引
• 排序字段上需要建立索引
• 分组字段上需要建立索引
• Where条件上不要使用运算函数，以免索引失效

如对于一些经常需要用到的查询功能，我们需要为没有指定外键的列建立索引;如有查询大表数据，而且又需根据好几个字段的值对其进行排序，也需要在这些列上建立复合索引。特别是在一些应用系统上，往往可以按以下字段的名称，就会对这个字段进行排序。遇到这种情况的话，更加需要在这些频繁进行排序的列上建立索引，以提高重新排序的效率。可见，若在查询的时候，若不能利用索引提高查询效率的话，则就好像跑车失去四轮驱动，速度会大受影响。

数据库建立索引的原则

铁律一：天下没有免费的午餐，使用索引是需要付出代价的

  索引的优点有目共睹，但是，却很少有人关心过采用索引所需要付出的成本。若数据库管理员能够对索引所需要付出的代价有一个充分的认识，也就不会那么随意到处建立索引了。仔细数数，其实建立索引的代价还是蛮大的。如创建索引和维护索引都需要花费时间与精力。特别是在数据库设计的时候，数据库管理员为表中的哪些字段需要建立索引，要调研、要协调。如当建有索引的表中的纪录又增加、删除、修改操作时，数据库要对索引进行重新调整。

虽然这个工作数据库自动会完成，但是，需要消耗服务器的资源。当表中的数据越多，这个消耗的资源也就越多。如索引是数据库中实际存在的对象，所以，每个索引都会占用一定的物理空间。若索引多了，不但会占用大量的物理空间，而且，也会影响到整个数据库的运行性能。可见，数据库管理员若要采用索引来提高系统的性能，自身仍然需要付出不少的代价。数据库管理员现在要考虑的就是如何在这两个之间取得一个均衡。或者说，找到一个回报与投入的临界点。

铁律二：对于查询中很少涉及的列或者重复值比较多的列，不要建立索引在查询的时候，如果我们不按某个字段去查询，则在这个字段上建立索引也是浪费。如现在有一张员工信息表，我们可能按员工编号、员工姓名、或者出身地去查询员工信息。但是，我们往往不会按照身份证号码去查询。虽然这个身份证号码是唯一的。此时，即使在这个字段上建立索引，也不能够提高查询的速度。相 反，增加了系统维护时间和占用了系统空间。这简直就是搬起石头砸自己的脚呀。

另外，如上面的员工信息表，有些字段重复值比较多。如性别字段主要就是“男、“女”;职位字段中也是有限的几个内容。此时，在这些字段上添加索引也不会显著的增加查询速度，减少用户响应时间。相反，因为需要占用空间，反而会降低数据库的整体性能。数据库索引管理中的第二条铁律就是，对于查询中很少涉及的列或者重复值比较多的列，不要建立索引。 

铁律三：对于按范围查询的列，最好建立索引在信息化管理系统中，很多时候需要按范围来查询某些交易记录。如在ERP系统中，经常需要查询当月的销售订单与销售出货情况，这就需要按日期范围来查询交易记录。如有时候发现库存不对时，也需要某段时期的库存进出情况，如5月1日到12月3日的库存交易情况等等。此时，也是根据日期来进行查询。对于这些需要在指定范围内快速或者频繁查询的数据列，需要为其建立索引。因为索引已经排序，其保存的时候指定的范围是连续的，查询可以利用索引的排序，加快查询时间，减少用户等待时间。

 不过，若虽然可能需要按范围来进行查询，但是，若这个范围查询条件利用的不多的情况下，最好不好采用索引如在员工信息表中，可能需要查询2008 年3月份以前入职的员工明细，要为他们增加福利。但是，由于表中记录不多，而且，也很少进行类似的查询。若维这个字段建立索引，虽然无伤大雅，但是很明显，索引所获得的收益要低于其成本支出。对数据库管理员来说，是得不偿失的。

 再者，若采用范围查询的话，最好能利用TOP关键字来限制一次查询的结果。如第一次按顺序只显示前面的500条记录等等。把TOP关键字跟范围一起使用，可以大大的提高查询的效率。

铁律四：表中若有主键或者外键，一定要为其建立索引

 定义有主键的索引列，一定要为其建立索引。因为主键可以加速定位到表中的某一行。结合索引的作用，可以使得查询的速度加倍。如在员工信息表中，我们 往往把员工编号设置为主键。因为这不但可以提高查询的速度，而且因为主键要求记录的唯一，还可以保证员工编号的唯一性。此时，若再把这个员工编号字段设置为索引，则通过员工编号来查询员工信息，其效率要比没有建立索引高出许多。

另外，若要使得某个字段的值唯一，可以通过两种索引方式实现。一种就是上面所讲的主键索引。还有一种就是唯一索引，利用UNIQUE关键字指定字段内容的唯一性。这两种方式都会在表中的指定列上自动创建唯一索引。这两种方式的结果没有明显的区别。

查询优化器不会区分到底是哪种方式建立的唯一性索引，而且他们进行数据查询的方式也是相同的。若某张表中的数据列定义有外键，则最好也要为这个字段建立索引。因为外键的主要作用就在于表与表之间的连接查询。若在外键上建立索引，可以加速表与表之间的连接查询。如在员工基本信息表中，有一个字段为员工职位。由于员工职位经常在变化，在这里，存储的其实只是一个员工职位的代码。在另外一张职位信息表中详细记录着该职位的相关信息。

此时，这个员工职位字段就是外键。若在这个字段上建立外键，则可以显著提高两张表的连接速度。而且，记录越多，其效果越加明显。

 所以，当表中有外键或者主键的时候，就最好为其建立索引。通过索引，可以强化主键与外键的作用，提高数据库的性能。

 铁律五：对于一些特殊的数据类型，不要建立索引

 在表中，有些字段比较特殊。如文本字段(TXT)、图像类型字段(IMAGE)等等。如果表中的字段属于这些数据类型，则最好不要为其建立索引。因为这些字段有一些共同的特点。如长度不确定，要么很长，几个字符;要么就是空字符串。如文本数据类型常在应用系统的数据库表中用来做备注的数据类型。有时候备注很长，但有时候又没有数据。若这种类型的字段上建立索引，那根本起不了作用。相反，还增加了系统的负担。

所以，在一些比较特殊的数据类型上，建立索引要谨慎。在通常情况下，没有必要为其建立索引。但是，也有特殊的情况。如有时候，在ERP系统中，有产 品信息这个表，其中有个产品规格这个字段。有时候，其长度可能长达5000个字符。此时，只有文本型的数据类型可以容纳这么大的数据量。而且，在查询的时候，用户又喜欢通过规格这个参数来查询产品信息。此时，若不为这个字段建立索引的话，则查询的速度会很慢。遇到这种情况时，数据库管理员只有牺牲一点系统资源，为其建立索引。

从这里也可以看出，虽然以上几条说的时铁律，但是，是否需要遵循，还是需要数据库管理员根据企业的实际情况，做出合理的选择。

铁律六：索引可以跟Where语句的集合融为一体 

用户在查询信息的时候，有时会经常会用到一些限制语句。如在查询销售订单的时候，经常会用到客户以及下单日期的条件集合;如在查询某个产品的库存交易情况时，就会利用产品编号与交易日期起止日期的条件集合。对于这些经常用在Where子句中的数据列，将索引建立在Where子句的集合过程中，对于需要加速或者频繁检索的数据列，可以让这些经常参与查询的数据列按照索引的排序进行查询，以加快查询的时间。

总之，索引就好像一把双刃剑，即可以提高数据库的性能，也可能对数据库的性能起到反面作用。作为数据库管理员，要有这个能力判断在合适的时间、合适的业务、合适的字段上建立合适的索引。以上六个铁律，只是对建立索引的一些基本要求

二、使用缓存

可以放入缓存的数据，用缓存来处理，查询效果会提升一个档次；高频数据，可以考虑放在redis之类的缓存中。不再走数据库。

三、分库分表

如果真的是数据量比较大，就要考虑分库分表了，按阿里的数据库规划标准，如果一张表大于 2G，或者超过 500 万行数据，就可以考虑分库分表

四、必要的时候，限制用户所使用的行

如我们往往在查询语句中，利用top 100 来让数据库只显示前100条记录。如此的话，可以明显的缩短用户的等待时间。默认情况下，是根据记录创建的时间顺序，来显示记录的。最迟创建的记录，其显示在最前。以此类推。
　　当用户需要的数据在前面100条之内，则就不需要再查看其他记录了。相反，若不在的话，则就需要查询全部记录信息了。

五、只查询时必须的字段

有时候，用户不同的查询需求都要用到同一张表。如在员工信息表中包含了很多内容。有时候用户想要知道正式员工有多少;管理层员工有多少;生产线员工又有哪些;或者想知道合同即将到期的员工有哪些。为此，就遇到一个问题，因为这些内容基本上都是在同一张表中，那是在同一个视图中实现，还是根据需求不同，设计不同的视图呢?
　　若单从技术上考虑，两这都是可以实现的，不会有多大的难度。但是，若是从数据库性能上考虑在，则还是采用不同的视图来实现不同的需求为好。
　　一方面，若从安全方面讲，则可以根据不同的视图来控制相关的访问权限。可见，把视图细化，在权限控制上则会更加的灵活。

六、合理处理NULL字段

一是在设计表的时候，对于这些需要参与运算的字段，要设置默认值。

二是在查询的时候，需要考虑到这个NULL值的影响

七、多多利用模糊查询

八、慎用Like等通配符

当用户在一张大表中采用这个LIKE语句的话，就会发现这个查询语句的运行效率非常的慢。这是什么原因造成的呢?其实，不管是Like 关键字，若采用MATCHES关键字的话，若在大量数据中查找符合条件的记录，则其运行效率也比较低。这主要是其技术特性所造成的，在数据库系统设计中，要尽量避免采用Like或者Matche关键字。有时候，我们可以利用其他运算符号来代替。如我们可以利用〉(大于)或者<(小于)符号来达到类似的需求。