数据库索引以及数据库优化

发表于   |   分类于   |     |   阅读次数

聚集索引和非聚集索引

聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续。就像字段，聚集索引是连续的，a后面肯定是b，非聚集索引就不连续了，就像图书馆的某个作者的书，有可能在第1个货架上和第10个货架上。还有一个小知识点就是：聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个。

索引建立的原则

  • 定义主键的数据列一定要建立索引。

  • 定义有外键的数据列一定要建立索引。

  • 对于经常查询的数据列最好建立索引。

  • 对于需要在指定范围内的快速或频繁查询的数据列;

  • 经常用在WHERE子句中的数据列。

  • 经常出现在关键字order by、group by、distinct后面的字段，建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用。

  • 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。

  • 对于定义为text、image和bit的数据类型的列不要建立索引。

  • 对于经常存取的列避免建立索引

  • 限制表上的索引数目。对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。索引虽说提高了访问速度，但太多索引会影响数据的更新操作。

  • 对复合索引，按照字段在查询条件中出现的频度建立索引。在复合索引中，记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录，系统再按照第二个字段的取值排序，以此类推。因此只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用,因此将应用频度高的字段，放置在复合索引的前面，会使系统最大可能地使用此索引，发挥索引的作用。

SQL的优化

  1. 有大量重复值、且经常有范围查询（between, >,< ，>=,< =）和order by、group by发生的列，可考虑建立群集索引；

  2. 经常同时存取多列，且每列都含有重复值可考虑建立组合索引；

  3. 组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。遇到以下情况，执行计划不会选择覆盖查询：

    • select选择的字段中含有不在索引中的字段 ，也即索引没有覆盖全部的列。
    • where 条件中不能含有对索引进行like的操作。

  4. 任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。

  5. 要善于使用存储过程，它使SQL变得更加灵活和高效。

    • 存储过程减少了网络传输、处理及存储的工作量，且经过编译和优化，执行速度快，易于维护，且表的结构改变时，不影响客户端的应用程序

    • 使用存储过程，视图，函数有助于减少应用程序中SQL复制的弊端，因为现在只在一个地方集中处理SQL

  6. IN、NOT IN 操作符

　　IN和EXISTS 性能有外表和内表区分的，但是在大数据量的表中推荐用EXISTS 代替IN 。

　　Not IN 不走索引的是绝对不能用的，可以用NOT EXISTS 代替

  1. IS NULL 或IS NOT NULL操作
    索引是不索引空值的，所以这样的操作不能使用索引，可以用其他的办法处理，例如：数字类型，判断大于0，字符串类型设置一个默认值，判断是否等于默认值即可

  2. <> 操作符（不等于）
    　　不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 用其它相同功能的操作运算代替，如 a<>0 改为 a>0 or a<0 a<="">’’ 改为 a>’’

  3. 用全文搜索搜索文本数据，取代like搜索，全文搜索始终优于like搜索 　　

  4. 在查询中不要使用 select *
  5. where使用原则
    • 第一个原则：在where子句中应把最具限制性的条件放在最前面。
    • 第二个原则：where子句中字段的顺序应和索引中字段顺序一致。
      1
      2
      3
      4
      5
      select field3,field4 from tb where field2 like 'R%' 快
      select field3,field4 from tb where field2 like '%R' 慢，不使用索引
      select field3,field4 from tb where field1>='sdf' 快 可以迅速定位索引
      select field3,field4 from tb where field1>'sdf' 慢

范式

  1. 第一范式：属性(字段)的原子性约束，要求属性具有原子性，不可再分割；

  2. 第二范式：记录的惟一性约束，要求记录有惟一标识，每条记录需要有一个属性来做为实体的唯一标识。

  3. 第三范式：属性(字段)冗余性的约束，即任何字段不能由其他字段派生出来，在通俗点就是：主键没有直接关系的数据列必须消除(消除的办法就是再创建一个表来存放他们，当然外键除外)

数据库设计的实用原则是：在数据冗余和处理速度之间找到合适的平衡点

满足范式的表一定是规范化的表，但不一定是最佳的设计。很多情况下会为了提高数据库的运行效率，常常需要降低范式标准：适当增加冗余，达到以空间换时间的目的

主键和外键的必要性

主键与外键的设计，在全局数据库的设计中，占有重要地位。 因为：主键是实体的抽象，主键与外键的配对，表示实体之间的连接。

主键：根据第二范式，需要有一个字段去标识这条记录，主键无疑是最好的标识，但是很多表也不一定需要主键，但是对于数据量大，查询频繁的数据库表，一定要有主键，主键可以增加效率、防止重复等优点。

主键的选择也比较重要，一般选择总的长度小的键，小的键的比较速度快，同时小的键可以使主键的B树结构的层次更少。
主键的选择还要注意组合主键的字段次序，对于组合主键来说，不同的字段次序的主键的性能差别可能会很大，一般应该选择重复率低、单独或者组合查询可能性大的字段放在前面。

外键：外键作为数据库对象，很多人认为麻烦而不用，实际上，外键在大部分情况下是很有用的，理由是：外键是最高效的一致性维护方法

数据库的一致性要求，依次可以用外键、CHECK约束、规则约束、触发器、客户端程序，一般认为，离数据越近的方法效率越高。
谨慎使用级联删除和级联更新，级联删除和级联更新作为SQL SERVER 2000当年的新功能，在2005作了保留，应该有其可用之处。我这里说的谨慎，是因为级联删除和级联更新有些突破了传统的关于外键的定义，功能有点太过强大，使用前必须确定自己已经把握好其功能范围，否则，级联删除和级联更新可能让你的数据莫名其妙的被修改或者丢失。从性能看级联删除和级联更新是比其他方法更高效的方法。

分割你的表，减小表尺寸

如果你发现某个表的记录太多，例如超过一千万条，则要对该表进行水平分割。水平分割的做法是，以该表主键的某个值为界线，将该表的记录水平分割为两个表。

如果你若发现某个表的字段太多，例如超过八十个，则垂直分割该表，将原来的一个表分解为两个表

字段设计原则

字段是数据库最基本的单位，其设计对性能的影响是很大的。需要注意如下：

A、数据类型尽量用数字型，数字型的比较比字符型的快很多。

B、 数据类型尽量小，这里的尽量小是指在满足可以预见的未来需求的前提下的。

C、 尽量不要允许NULL，除非必要，可以用NOT NULL+DEFAULT代替。

D、少用TEXT和IMAGE，二进制字段的读写是比较慢的，而且，读取的方法也不多，大部分情况下最好不用。

E、 自增字段要慎用，不利于数据迁移

减少持有行级锁的时间

比如对于一个秒杀系统，成功秒杀一件商品需要先在商品表里减库存再将购买记录插入到购买记录表：

减库存  update product set number=number-1 where product.id=id
插入购买记录：insert into sales(id,..,..,) value (...);

这两个sql是在事务管理下的，是原子的，减库存的行级锁需要等到插入操作执行完之后才能释放锁。所以整个过程都必须持有行级锁，由于减库存一个高热点的操作，如果持有锁的时间太长会降低秒杀的效率。为了降低持有锁的时间，可以将插入操作放到update前面。