数据库、数据表建立索引的原则

2023-11-11 来源：六九路网

数据库建立索引的原则 1，确定针对该表的操作是大量的查询操作还是大量的增删改操作。 2，尝试建立索引来帮助特定的查询。检查自己的sql语句，为那些频繁在where子句中出现的字段建立索引。 3，尝试建立复合索引来进一步提高系统性能。修改复合索引将消耗更长时间，同时，复合索引也占磁盘空间。 4，对于小型的表，建立索引可能会影响性能 5，应该避免对具有较少值的字段进行索引。 6，避免选择大型数据类型的列作为索引。索引建立的原则索引查询是数据库中重要的记录查询方法，要不要进入索引以及在那些字段上建立索引都要和实际数据库系统的查询要求结合来考虑，下面给出实际中的一些通用的原则： 1. 在经常用作过滤器的字段上建立索引； 2. 在SQL语句中经常进行GROUP BY、ORDER BY的字段上建立索引； 3. 在不同值较少的字段上不必要建立索引，如性别字段； 4. 对于经常存取的列避免建立索引； 5. 用于联接的列（主健/外健）上建立索引； 6. 在经常存取的多个列上建立复合索引，但要注意复合索引的建立顺序要按照使用的频度来确定； 7. 缺省情况下建立的是非簇集索引，但在以下情况下最好考虑簇集索引，如：含有有限数目（不是很少）唯一的列；进行大范围的查询；充分的利用索引可以减少表扫描I/0的次数，有效的避免对整表的搜索。当然合理的索引要建立在对各种查询的分析和预测中，也取决于DBA的所设计的数据库结构。

数据库、数据表建立索引的原则

标签：类型最好不同磁盘空间复合 order by 检查自己的数据库系统

小编还为您整理了以下内容，可能对您也有帮助：

建立索引应该遵循什么原则

建立索引的原则：

（
1
）定义有主键的数据列一定要建立索引。因为主键可以加速定位到表中的某一行

（
2
）定义有外间的数据列一定要建立索引。外键列通常用于表与表之间的连接，在其上创
建索引可以加快表间的连接。

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

建立索引应该遵循什么原则

mysql建索引的几大原则

1．选择唯一性索引

唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。

2．为经常需要排序、分组和联合操作的字段建立索引

经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。

3．为常作为查询条件的字段建立索引

如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。

4．*索引的数目

索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。

5．尽量使用数据量少的索引

如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。

6．尽量使用前缀来索引

如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。

7．删除不再使用或者很少使用的索引

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

8 . 最左前缀匹配原则，非常重要的原则。

mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a 1=”” and=”” b=”2” c=”“> 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

9 .=和in可以乱序。

比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

。
10 . 尽量选择区分度高的列作为索引。

区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录

11 .索引列不能参与计算，保持列“干净”。

比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

12 .尽量的扩展索引，不要新建索引。
比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可

注意：选择索引的最终目的是为了使查询的速度变快。上面给出的原则是最基本的准则，但不能拘泥于上面的准则。读者要在以后的学习和工作中进行不断的实践。根据应用的实际情况进行分析和判断，选择最合适的索引方式。

mysql建索引的几大原则

如何合理创建Oracle数据库索引的3个要求

如何合理创建Oracle数据库索引的3个要求：
在Oracle数据库中，创建索引虽然比较简单。但是要合理的创建索引则比较困难了。笔者认为，在创建索引时要做到三个适当，即在适当的表上、适当的列上创建适当数量的索引。虽然这可以通过一句话来概括优化的索引的基本准则，但是要做到这一点的话，需要数据库管理员做出很大的努力。具体的来说，要做到这个三个适当有如下几个要求。
一、根据表的大小来创建索引。
虽然给表创建索引，可以提高查询的效率。但是数据库管理员需要注意的是，索引也需要一定的开销的。为此并不是说给所有的表都创建索引，那么就可以提高数据库的性能。这个认识是错误的。恰恰相反，如果不管三七二十一，给所有的表都创建了索引，那么其反而会给数据库的性能造成负面的影响。因为此时滥用索引的开销可能已经远远大于由此带来的性能方面的收益。所以笔者认为，数据库管理员首先需要做到，为合适的表来建立索引，而不是为所有的表建立索引。
一般来说，不需要为比较小的表创建索引。如在一个ERP系统的数据库中，department表用来存储企业部门的信息。一般企业的部分也就十几个，最多不会超过一百个。这100条记录对于人来说，可能算是比较多了。但是对于计算机来说，这给他塞塞牙缝都还不够。所以，对类似的小表没有必要建立索引。因为即使建立了索引，其性能也不会得到很大的改善。相反索引建立的开销，如维护成本等等，要比这个要大。也就是说，付出的要比得到的多，显然违反常理。
另外，就是对于超大的表，也不一定要建立索引。有些表虽然比较大，记录数量非常的多。但是此时为这个表建立索引并一定的合适。如系统中有一张表，其主要用来保存数据库中的一些变更信息。往往这些信息只给数据库管理员使用。此时为这张表建立索引的话，反而不合适。因为这张表很少用到，只有在出问题的时候才需要查看。其次其即使查看，需要查询的纪录也不会很多，可能就是最近一周的更新记录等等。对于对于一些超大的表，建立索引有时候往往不能够达到预计的效果。而且在打表上建立索引，其索引的开销要比普通的表大的多。那么到底是否给大表建立索引呢?笔者认为，主要是看两个方面的内容。首先是需要关注一下，在这张大表中经常需要查询的记录数量。一般来说，如果经常需要查询的数据不超过10%到15%的话，那就没有必要为其建立索引的必要。因为此时建立索引的开销可能要比性能的改善大的多。这个比例只是一个经验的数据。如果数据库管理员需要得出一个比较精确的结论，那么就需要进行测试分析。即数据库管理员需要测试一下全表扫描的时间，看看其是否比建立索引后的查询时间要长或者短。如果是长的话，则说明有建立索引的必要。但是如果没有的话，则说明还是全表扫描速度来的快。此时也就没有必要建立索引了。
总之，在考虑是否该为表建立索引时，一般来说小表没有建立索引的必要。而对于打表的话，则需要进行实际情况实际分析。简单一点的，可以根据大致的比率来确定。如果要精确一点的，则可以进行全表扫描性能分析，以判断建立索引后是否真的如预期那样改善了数据库性能。
二、根据列的特征来创建索引。
列的特点不同，索引创建的效果也不同。数据库管理员需要了解为哪些列创建索引可以起到事倍功半的效果。同时也需要了解为哪些列创建索引反而起到的是事倍功半的效果。这有利于他们了解到底给为怎么样的字段建立索引。
根据笔者的经验，往往为如下特征的列创建索引能够起到比较明显的效果。如对于一些重复内容比较少的列，特别是对于那些定义了唯一约束的列。在这些列上建立索引，往往可以起到非常不错的效果。如对于一些null值的列与非Null值的列混合情况下，如果用户需要经常查询所有的非Null值记录的列，则最好为其设置索引。如果经常需要多表连接查询，在用与连接的列上设置索引可以达到事半功倍的效果。
可见，索引设置的是否恰当，不仅跟数据库设计架构有关，而且还跟企业的经济业务相关。为此，对于一些套装软件，虽然一开始数据库管理员已经做了索引的优化工作。但是随着后来经济数据的增加，这个索引的效果会越来越打折扣。这主要是因为记录的表化影响到了索引优化的效果。所以笔者建议各位数据库管理员，即使采用的是大牌软件公司的套装软件，也需要隔一段时间，如一年，对数据库的索引进行优化。该去掉的去掉，该调整的调整，以提高数据库的性能。
如在数据库中有一张表是用来保存用户信息的。其中有个字段身份证号码，这是一个唯一的字段。在数据库设计时，给这个字段创建了索引。但是当这个数据库投入使用之后，用户不怎么输入用户的身份证号码。而且平时也基本不按这个号码来进行查询。当记录月来月多时，这个身份证号码上的索引字段不但不能够改善数据库的查询性能，反而成了鸡肋。对于这些有很多NULL值的列，而且不会经常查询所有的非NULL值记录的列，数据库管理员要下决心，即使清除这些列上的索引。
所以说索引的优化与调整是一个动态的过程，并不是说数据库设计好之后就不需要经过调整。数据库管理员往往需要根据记录的变化情况，来进行适当的变更。以提高索引的效果。
三、在一个表上创建多少索引合适?
虽然说，在表上创建索引的数量没有*，但是决不是越多越好。也就是说，在创建索引这项事情上，1+1〉2往往不成立。有时候，创建索引越多，其可能会得到适得其反的效果。那么在一个表上，到底给创建多少索引合适呢?这个没有一个明确的标准。而是需要数据库管理员根据实际的用途以及数据库中记录的情况，来进行判断。
通常来说，表的索引越多，其查询的速度也就越快。但是，表的更新速度则会降低。这主要是因为表的更新(如往表中插入一条记录)速度，反而随着索引的增加而增加。这主要是因为，在更新记录的同时需要更新相关的索引信息。为此，到底在表中创建多少索引合适，就需要在这个更新速度与查询速度之间取得一个均衡点。如对于一些数据仓库或者决策型数据库系统，其主要用来进行查询。相关的记录往往是在数据库初始化的时候倒入。此时，设置的索引多一点，可以提高数据库的查询性能。同时因为记录不怎么更新，所以索引比较多的情况下，也不会影响到更新的速度。即使在起初的时候需要导入大量的数据，此时也可以先将索引禁用掉。等到数据导入完毕后，再启用索引。可以通过这种方式来减少索引对数据更新的影响。相反，如果那些表中经常需要更新记录，如一些事务型的应用系统，数据更新操作是家常便饭的事情。此时如果在一张表中建立过多的索引，则会影响到更新的速度。由于更新操作比较频繁，所以对其的负面影响，要比查询效率提升要大的多。此时就需要*索引的数量，只在一些必要的字段上建立索引。
笔者在平时数据库优化时，往往会根据这些表的用途来为列设置索引。可以查询相关的动态视图，看看对于这张表的操作，是更新操作(包括更新、删除、插入等等)占的比例大，还是查询操作占的比例大。当过多的索引已经影响到更新操作的速度时，则数据库管理员就需要先禁用某些索引，以提高数据库的性能。
总之，在适当的表、适当的列上建立适当的索引。这一句话包含的意思有很多，以上内容只是一部分内容。俗话说，师傅领进门，修行靠自身。笔者在这里指能够点到为止。一些具体的索引优化内容还是需要各位读者在日常工作中去体会与总结

如何合理创建Oracle数据库索引的3个要求

哪位高手跟我说说,数据库中'索引'的用法,怎么创建,和在select语句中怎么使用???

建立数据库的目的是管理大量数据，而建立索引的目的就是提高数据检索效率，改善数据库工作性能，提高数据访问速度。当查询大量数据时不建索引带条件的查询会很慢的,索引的创建不同的数据库有不同的写法,oracle的写法是create index 索引名 on 表名();

在select 语句中的where条件每个字段都要建索引.

索引原理

索引的基本概念索引是一种特殊类型的数据库对象，它与表有着密切的联系。索引是为检索而存在的。如一些书籍的末尾就专门附有索引，指明了某个关键字在正文中的出现的页码位置，方便我们查找，但大多数的书籍只有目录，目录不是索引，只是书中内容的排序，并不提供真正的检索功能。可见建立索引要单独占用空间；索引也并不是必须要建立的，它们只是为更好、更快的检索和定位关键字而存在。再进一步说，我们要在图书馆中查阅图书，该怎么办呢？图书馆的前台有很多叫做索引卡片柜的小柜子，里面分了若干的类别供我们检索图书，比如你可以用书名的笔画顺序或者拼音顺序作为查找的依据，你还可以从作者名的笔画顺序或拼音顺序去查询想要的图书，反正有许多检索方式，但有一点很明白，书库中的书并没有按照这些卡片柜中的顺序排列——虽然理论上可以这样做，事实上，所有图书的脊背上都人工的粘贴了一个特定的编号①，它们是以这个顺序在排列。索引卡片中并没有指明这本书摆放在书库中的第几个书架的第几本，仅仅指明了这个特定的编号。管理员则根据这一编号将请求的图书返回到读者手中。这是很形象的例子，以下的讲解将会反复用到它。SQLS在安装完成之后，安装程序会自动创建master、model、tempdb等几个特殊的系统数据库，其中master是SQLS的主数据库，用于保存和管理其它系统数据库、用户数据库以及SQLS的系统信息，它在SQLS中的地位与WINDOWS下的注册表相当。master中有一个名为sysindexes的系统表，专门管理索引。SQLS查询数据表的操作都必须用到它，毫无疑义，它是本文主角之一。查看一张表的索引属性，可以在查询分析器中使用以下命令：select * from sysindexes where id=object_id(‘tablename’) ；而要查看表的索引所占空间的大小，可以使用系统存储过程命令：sp_spaceused tablename，其中参数tablename为被索引的表名。

如何正确合理的建立MYSQL数据库索引

MySQL索引类型包括：

（1）普通索引

这是最基本的索引，它没有任何。它有以下几种创建方式：

◆创建索引

CREATE INDEX indexName ON mytable(username(length)); 如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length，下同。

◆修改表结构

ALTER mytable ADD INDEX [indexName] ON (username(length))

◆创建表的时候直接指定

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) ); 删除索引的语法：

DROP INDEX [indexName] ON mytable;

（2）唯一索引

与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

◆创建索引

CREATE UNIQUE INDEX indexName ON mytable(username(length))

◆修改表结构

ALTER mytable ADD UNIQUE [indexName] ON (username(length))

◆创建表的时候直接指定

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) );

（3）主键索引

它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引：

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) ); 当然也可以用 ALTER 命令。记住：一个表只能有一个主键。

（4）组合索引

为了形象地对比单列索引和组合索引，为表添加多个字段：

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, city VARCHAR(50) NOT NULL, age INT NOT NULL ); 为了进一步榨取MySQL的效率，就要考虑建立组合索引。就是将 name, city, age建到一个索引里：

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age); 建表时，usernname长度为 16，这里用 10。这是因为一般情况下名字的长度不会超过10，这样会加速索引查询速度，还会减少索引文件的大小，提高INSERT的更新速度。

如果分别在 usernname，city，age上建立单列索引，让该表有3个单列索引，查询时和上述的组合索引效率也会大不一样，远远低于我们的组合索引。虽然此时有了三个索引，但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。

建立这样的组合索引，其实是相当于分别建立了下面三组组合索引：

usernname,city,age usernname,city usernname 为什么没有 city，age这样的组合索引呢？这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引，下面的几个SQL就会用到这个组合索引：

SELECT * FROM mytable WHREE username="admin" AND city="郑州" SELECT * FROM mytable WHREE username="admin" 而下面几个则不会用到：

SELECT * FROM mytable WHREE age=20 AND city="郑州" SELECT * FROM mytable WHREE city="郑州"

（5）建立索引的时机

一般来说，在WHERE和JOIN中出现的列需要建立索引，但也不完全如此，因为MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引。例如：

SELECT t.Name FROM mytable t LEFT JOIN mytable m ON t.Name=m.username WHERE m.age=20 AND m.city='郑州' 此时就需要对city和age建立索引，由于mytable表的userame也出现在了JOIN子句中，也有对它建立索引的必要。

刚才提到只有某些时候的LIKE才需建立索引。因为在以通配符%和_开头作查询时，MySQL不会使用索引。例如下句会使用索引：

SELECT * FROM mytable WHERE username like'admin%' 而下句就不会使用：

SELECT * FROM mytable WHEREt Name like'%admin' 因此，在使用LIKE时应注意以上的区别。

（6）索引的不足之处

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：

◆虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

◆建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。

索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

（7）使用索引的注意事项

使用索引时，有以下一些技巧和注意事项：

◆索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

◆使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

◆索引列排序

MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

◆like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

◆不要在列上进行运算

select * from users where YEAR(adddate)<2007; 将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成

select * from users where adddate<‘2007-01-01’;

◆不使用NOT IN和<>操作

显示全文

全部栏目

数据库、数据表建立索引的原则