数据库建立索引常用的规则

数据库建立索引常用的规则数据库建立索引常用的规则如下:1、表的主键、外键必须有索引; 2、数据量…

大家好,又见面了,我是你们的朋友全栈君。

数据库建立索引常用的规则如下:

1、表的主键、外键必须有索引; 
2、数据量超过300的表应该有索引; 
3、经常与其他表进行连接的表,在连接字段上应该建立索引; 
4、经常出现在Where子句中的字段,特别是大表的字段,应该建立索引; 
5、索引应该建在选择性高的字段上; 
6、索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引; 
7、复合索引的建立需要进行仔细分析;尽量考虑用单字段索引代替:

A、正确选择复合索引中的主列字段,一般是选择性较好的字段;

B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中?单字段查询是否极少甚至没有?如果是,则可以建立复合索引;否则考虑单字段索引;

C、如果复合索引中包含的字段经常单独出现在Where子句中,则分解为多个单字段索引;

E、如果既有单字段索引,又有这几个字段上的复合索引,一般可以删除复合索引;

8、频繁进行数据操作的表,不要建立太多的索引; 
9、删除无用的索引,避免对执行计划造成负面影响;

以上是一些普遍的建立索引时的判断依据。 
索引的建立必须慎重,对每个索引的必要性都应该经过仔细分析,要有建立的依据。 
因为太多的索引与不充分、不正确的索引对性能都毫无益处:在表上建立的每个索引都会增加存储开销,索引对于插入、删除、更新操作也会增加处理上的开销。 另外,过多的复合索引,在有单字段索引的情况下,一般都是没有存在价值的;相反,还会降低数据增加删除时的性能,特别是对频繁更新的表来说,负面影响更大。 
总的来说,小型表肯定不建索引, 
或者数据库记录在亿条数据级以上,还是建议使用非关系型数据库。 
还有些特殊字段的数据库,比如BLOB,CLOB字段肯定也不适合建索引。 
其实这个问题更感觉偏向于做软件项目的一种经验。


对千万级MySQL数据库建立索引的事项及提高性能的手段

一、注意事项:

首先,应当考虑表空间和磁盘空间是否足够。我们知道索引也是一种数据,在建立索引的时候势必也会占用大量表空间。因此在对一大表建立索引的时候首先应当考虑的是空间容量问题。 
其次,在对建立索引的时候要对表进行加锁,因此应当注意操作在业务空闲的时候进行。

二、性能调整方面:

首当其冲的考虑因素便是磁盘I/O。物理上,应当尽量把索引与数据分散到不同的磁盘上(不考虑阵列的情况)。逻辑上,数据表空间与索引表空间分开。这是在建索引时应当遵守的基本准则。

其次,我们知道,在建立索引的时候要对表进行全表的扫描工作,因此,应当考虑调大初始化参数db_file_multiblock_read_count的值。一般设置为32或更大。

再次,建立索引除了要进行全表扫描外同时还要对数据进行大量的排序操作,因此,应当调整排序区的大小。

9i之前,可以在session级别上加大sort_area_size的大小,比如设置为100m或者更大。

9i以后,如果初始化参数workarea_size_policy的值为TRUE,则排序区从pga_aggregate_target里自动分配获得。

最后,建立索引的时候,可以加上nologging选项。以减少在建立索引过程中产生的大量redo,从而提高执行的速度。

MySql在建立索引优化时需要注意的问题

设计好MySql的索引可以让你的数据库飞起来,大大的提高数据库效率。设计MySql索引的时候有一下几点注意:

1,创建索引

对于查询占主要的应用来说,索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的,或者说没有添加更为有效的索引导致。如果不加索引的话,那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描,如果一张表的数据量很大而符合条件的结果又很少,那么不加索引会引起致命的性能下降。但是也不是什么情况都非得建索引不可,比如性别可能就只有两个值,建索引不仅没什么优势,还会影响到更新速度,这被称为过度索引。

2,复合索引

比如有一条语句是这样的:select * from users where area=’beijing’ and age=22; 
如果我们是在area和age上分别创建单个索引的话,由于mysql查询每次只能使用一个索引,所以虽然这样已经相对不做索引时全表扫描提高了很多效 
率,但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area, age, 
salary)的复合索引,那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引,这被称为最佳左前缀 
特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边,依次递减。

3,索引不会包含有NULL值的列

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

4,使用短索引

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

5,排序的索引问题

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

6,like语句操作

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

7,不要在列上进行运算

select * from users where 
YEAR(adddate)

8,不使用NOT IN和操作

NOT IN和操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替,id3则可使用id>3 or id


添加索引示例:

添加索引示例:


 
 
 
  1. CREATE INDEX IDX_AUDITSTATUS ON [shanghaiDB].[dbo].[Activity](AUDITSTATUS) WITH( ONLINE= ON)
  2. CREATE INDEX IDX_ANUMMID ON [nantongDB].[dbo].[Orders](ANUM, MID) WITH( ONLINE= ON)
  3. CREATE INDEX IDX_SiteCode ON Usercenter.[dbo].MO(SiteCode) WITH( ONLINE= ON)
  4. CREATE INDEX IDX_AccessDt ON [all].[dbo].[AccessLog](AccessDt) WITH( ONLINE= ON)

Create index注意n如果是大表建立索引,切记加上ONLINE参数

Create index注意n如果是大表建立索引,切记加上ONLINE参数

这几天在做数据库的优化,有个2亿记录的表,发现需要添加一个联合索引,结果就采用普通的create index index_name on tablename (entp_id,sell_date),结果悲剧了,把所有的DML语句都阻塞了,导致系统不能正常使用,还好是晚上10点,用户不是非常多,1个小时候,索引结束,阻塞解决;

上网查了一下,如果加上 online参数后,就可以在线做索引,而不需要阻塞所有的DML语句,血的教训,拿出来与各位共勉,具体online与不加online区别如下:

1. DML操作对create index 的影响。 如果在create的时候,有其他的进程在对这个index 所对应的数据进行DML操作,create会受影响:


 
 
 
  1. SQL> create table test (id number, name varchar2(20));
  2. Table created.
  •  

然后重新开一个session:


 
 
 
  1. SQL> insert into test values ( 1, 'lms');
  2. 1 row created.
  3. <no commit>
  4. SQL> create index t1 on test(id);
  5. create index t1 on test(id)
  6. *
  7. ERROR at line 1:
  8. ORA- 00054: resource busy and acquire with NOWAIT specified
  •  

2. 加online这个参数,这个参数加上以后,除了create过程中index 保持online状态,Oracle还会在create index之前等待所有DML操作结束,然后得到DDL锁,开始create.


 
 
 
  1. SQL> create index t1 on test(id) online;
  2. <hold before commit>
  3. <after commit>
  4. SQL> commit;
  5. Commit complete.
  6. Index altered.
  •  

如果不commit,上面的操作就会一直hold。

所以以后create索引和rebuild索引的时候最好加上online。

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/161995.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(1)


相关推荐

  • Docker方式安装showdoc

    Docker方式安装showdoc

  • 计算机vb语言程序设计_vb语言程序设计基础

    计算机vb语言程序设计_vb语言程序设计基础VisualBasic程序设计教程:计算机类语音编辑锁定讨论上传视频《VisualBasic程序设计教程:计算机类》是中国科学技术出版社出版的图书,作者是王东、刘福来。中文名VisualBasic程序设计教程:计算机类出版社中国科学技术出版社定价39.20作者王东刘福来ISBN9787504646798VisualBasic程序设计教程:计算机类内容简介编辑语音《全国高等…

  • DOS常用命令_dos格式化硬盘命令

    DOS常用命令_dos格式化硬盘命令启动方式1:进入DOS页面:win+R;键入:cmd启动方式2:“开始”→“运行”→输入“cmd”回车,此时将出现一个显示命令提示符的窗口,如下图。1,help命令:help——》查看所有命令帮助;help某某某——》查看具体某个命令的帮助2,dir命令该命令显示一个目录下的文件和子目录列表以及文件的其他详细资料,包括文件大小,创建日期和时间等。语法是:…

    2022年10月23日
  • java中集合转数组中_JAVA中集合转数组遍历[通俗易懂]

    java中集合转数组中_JAVA中集合转数组遍历[通俗易懂]JAVA中集合的遍历的一种方法时集合转数组遍历,也是就调用Collection中的toArray().代码:publicstaticvoidmain(String[]args){//TODOAuto-generatedmethodstubCollectionc=newArrayList();c.add(newStudent(“kj”,12));c.add(newStude…

  • idea2022最新激活码[最新免费获取]

    (idea2022最新激活码)2021最新分享一个能用的的激活码出来,希望能帮到需要激活的朋友。目前这个是能用的,但是用的人多了之后也会失效,会不定时更新的,大家持续关注此网站~https://javaforall.cn/100143.htmlIntelliJ2021最新激活注册码,破解教程可免费永久激活,亲测有效,上面是详细链接哦~1M…

  • netstat命令详解

    netstat命令详解简介netstat命令用于显示各种网络相关信息,如网络连接,路由表,接口状态(InterfaceStatistics),masquerade连接,多播成员(MulticastMember

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号