数据库建表和索引的建议

2019独角兽企业重金招聘Python工程师标准>>>

建表：

1、避免将字段设为“允许为空”

2、尽量使用数字型字段，提高查询和连接的性能，减少存储开销。因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

比如，枚举类型尽量用数值型字段

3、尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

4、尽量不要使用TEXT数据类型

建索引：

1、正确的选择数据类型

Int，bigint， smallint，和tinyint等这些数据类型都非常适合于用在索引中，因为他们都占用相同大小的空间并且可以很容易地实现比较操作。其他的数据类型如char和varchar的效率都非常低，因为这些数据类型都不适合于执行数学操作，并且执行比较操作的时间都比上面提到数据类型要长。

2、创建多列索引时，需要注意列的顺序

数据库将根据第一列索引的值来排列记录，然后进一步根据第二列的值来排序，依次排序直到最后一个索引排序完毕。哪一列唯一数据值较少，哪一列就应该为第一个索引，这样可以确保数据可以通过索引进一步交叉排序。

3、在clustered（聚集）索引中限制列的数量

在clustered索引中用到的列越多，在nonclustered索引中包含的clustered索引参考位置就越多，需要存储的数据也就越多。这样将增加包含索引的数据表的大小，并且将增加基于索引的搜索时间。

4、避免频繁更新clustered索引数据列

clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。

nonclustered 索引依赖于clustered 索引，所以如果构成clustered 索引的数据列频繁更新，将导致在nonclustered中存储的行定位器也将随之频繁更新。对于所有与这些列相关的查询来说，如果发生记录被锁定的情况时，这将可能导致性能成本的增加。

若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。

5、分开操作（如果可能的话）

对于一个表来说，如果需要进行频繁的执行插入、更新操作，同时还有大量读操作的话，在可能的情况下尝试将这个表分开操作。所有的插入和更新操作可以在一个没有索引的表中操作，然后将其复制到另外一个表中，在这个表里有大量的索引可以优化读数据的能力。

6、适当的重建索引

Nonclustered索引包含clustered索引的指针，这样一来Nonclustered索引将从属于clustered 索引。当重建clustered索引时，首先是丢弃原来的索引，然后再使用CREATE INDEX 来创建索引，或者在使用CREATE INDEX 声明的同时将DROP_EXISTING 子句作为重建索引的一部分。将丢弃和创建分为几步将会导致多次重建nonclustered 索引，而不象使用DROP_EXISTING 子句那样，只重建一次nonclustered 索引。

7、索引有大量数据重复，SQL查询可能不会利用索引，如字段sex，male和female几乎各一半，建了索引也没有用。

8、索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

 

转载于:https://my.oschina.net/u/2342541/blog/1794640