mysql一个索引对INSERT的巨大影响

发布时间 : 2020/08/03 20:55

索引影响insert效率吗

一.什么是索引?

1.索引 :索引是对数据库中 一列要么多列 的值进行 排序 的一种数据布局。

2.索引的作用 :索引的作用是为了 进步查询的速率

3.几个特点

  • ①MySQL中, 主键 唯一束缚 自带索引;
  • ②在查询时,只有使用到 有索引的列 ,才能进步查询速率;
  • ③索引会 降低插入速率 ,数据量越大,插入速率越慢。

4.索引的算法 Hash Btree

  • Hash索引:合适 等值查找 ,在范畴查找时有大概发生 Hash冲突
  • Btree索引:合适 范畴查找 ,没有Hash冲突问题。

二.验证索引对查询、插入速率的影响

试验工具:
  • Nacivat for MySQL
  • eclipse

试验步骤:
(一)试验环境创建:
1.使用 Nacivat for MySQL 创建表格 tb_Test
创建表格
此中,id为检测列,即用于插入索引的列。

2.使用 eclipse 连接MySQL操作表格 tb_Test

①eclipse代码: package forJDBC; import static org.junit.Assert.*; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import org.junit.After; import org.junit.Before; import org.junit.Test; public class Boker { private Connection conn; @Before public void setUp () throws Exception { conn=DriverManager.getConnection( "jdbc:mysql://localhost:3306/for1703" , "root" , "root" ); } @After public void tearDown () throws Exception { conn.close(); } @Test public void testInsert () throws SQLException { this .insertS(); } private void insertS () { try { Statement stat=conn.createStatement(); StringBuilder sb= new StringBuilder(); for ( int i= 0 ;i< 500 ;i++) { sb.append( "('idxxx" +i+ "'," +(i+ 1 )+ ")" ); if (i!= 499 ) { sb.append( "," ); } } String sql= "insert into tb_Test (id,idNumber) values" +sb.toString(); int num=stat.executeUpdate(sql); System.out.println(num); } catch (SQLException e) { e.printStackTrace(); } } @Test public void testSearch () throws SQLException { this .searchS(); } private void searchS () throws SQLException { Statement stat=conn.createStatement(); String sql= "select * from tb_Test where id='idxxx255'" ; ResultSet rs=stat.executeQuery(sql); while (rs.next()) { System.out.println(rs.getString( "id" )+ "\t" +rs.getInt( "idNumber" )+ "\t" ); } } @Test public void testDelete (){ this .deleteS(); } private void deleteS () { try { Statement stat =conn.createStatement(); String sql= "delete from tb_Test" ; System.out.println(stat.executeUpdate(sql)); } catch (SQLException e) { e.printStackTrace(); } } }

②运行 testInsert( ) 向表中插入500条数据,此中 id=“idxxx”+(idNumber-1)

这里写图片描述 (二)验证過逞:

1.当 id 列未添加索引时,运行 testSearch( ) 方法,查找“idxxx255”, 泯灭2.880s

无索引查找

2.应用 Navicat for MySQL 对id列添加索引,再次查找“idxxx255”, 耗时1.036s

添加索引 有索引查找 ※发现:查找相同内容,有索引时可以大大收缩查询时间。

3.运行 testDelete( ) 删除表内记载,并删去索引;

4.变动代码中i值为10000,再次运行 testInsert( ),向没有索引的表中插入10000组数据, 耗时1.371s

这里写图片描述

5.再次运行 testDelete( ) 删除全部数据后,对 id 列添加索引后,再次插入500组同样的数据, 耗时1.817s

这里写图片描述

※发现:插入相同数据,无索引 有索引 速率快一些。

三.结论

经过验证,索引的使用对查询和插入速率有以下影响:

    查询时,使用带有索引的列可以显着进步查询速率; 插入时,假如插入的列有索引,插入速率会减慢。

四.分析讨论

经过本次试验,在以后创建表格时,尽量保证将重要的数据都插入之后,再 添加索引 ,制止在 添加索引之后 进行数据插入,以保证服从最高。


留意:删除时,要把代码前面的插入和查询代码解释掉,防备删除失败。



有一个希奇的问题讨教列位
环境:ORACLE 11.2.0.3 RAC, 某运营商用户详单表,表上建有主键,并在用户标识列serv_id上建有的非唯一索引。由于应用是根据话单天生环境,及时插入详单表,以是使用的是 INSERT INTO VALUES () 的语句。表数据量大概5万万,未分区,表上操作满是INSERT,无DELETE,UPDATE。

问题征象:在有SERV_ID索引的环境下,INSERT 速率很慢,每秒仅有150条,无SERV_ID索引环境下,每秒插入达1000条。

分析棤施:对该语句进行10046 跟踪,发现重要是 db file sequential read 的等候,而等候对象就是SERV_ID的索引。开端判定SERV_ID上索引是导致INSERT 迟钝的重要缘故。对SERV_ID索引和主键索引分别做 ANALYZE INDEX *** VALIDATE STRUCTURE;查询结果分别如下,第一行为主键,第二行为SERV_ID索引, 差异重要在BLOCKS,DISTINCT_KEYS , ROWS_PER_KEY ,BLKS_GETS_PER_ACCESS 。由于一个用户不可制止的会有多条详单记载,因此,SERV_ID列上重复值较多。
主键索引 SERV_ID索引
DISTINCT_KEYS : 45182484 189126
ROWS_PER_KEY : 1 238.9
BLKS_GETS_PER_ACCESS : 4 123


问题: 索引重复值较多是导致INSERT 服从降低10倍的缘故吗?如果,有没有优化的方法?是否另有其他缘故导致了这个征象呢?还请列位大家指教。



HEIGHT BLOCKS NAME LF_ROWS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_ROWS BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS OPT_CMPR_PCTSAVE
3 58880 PK_EVENT_TGPS_738_381403 45182484 58147 812828318 16188 58146 71 812097 16220 0 45182484 1 942435256 813640415 87 1 4 0


HEIGHT BLOCKS NAME LF_ROWS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_ROWS BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS OPT_CMPR_PCTSAVE
3 77760 I_TGPSSERVID_738_381403 45182484 75300 839305476 16188 75299 129 1228027 16220 0 189126 11982 1221048780 840533503 69 238.90149424 122.950747120967 40




晚上测试了下,INSERT 性能翻了一番,详细做法如下:
1.将月度详单表定时间分区,创建SERV_ID的local 索引及主键。
create table awr_base_5 partition by range("start_time") interval(numtodsinterval(5,'day')) (partition part1values less than (to_date('2014-03-05','yyyy-mm-dd'))) tablespace bill2b as select * from awr_base_0320_orig; create index i_serv_id_5 on awr_base_5(serv_id) local;
alter table awr_base_5 add constraint pk_awr_5 primary key(event_inst_id) using index;



2.对SERV_ID的各分区索引进行分析后发现 BLKS_GETS_PER_ACCESS 从 123 降低到 23。
3.继续测试导入,10046跟踪后发现 物理读淘汰到原来的1/4。


继续思索:1.INSERT 服从的进步是否和 BLKS_GETS_PER_ACCESS的淘汰有关?一个索引的 BLKS_GETS_PER_ACCESS的大小是反对定了对其索引的KEY进行修改的IO数目?是否理应越小越好?
2.这次只分了6个区,若分区数量更大点, BLKS_GETS_PER_ACCESS的数量应更会缩小,INSERT服从应更能进步,但是相应的根据SERV_ID进行SELECT的服从应该也会相应的降低,要均衡2者的关系才行。
3.对于KEY值重复度较高的索引,可以通过压缩索引的方法来节流空间,是否也能进步INSERT的服从?这个有空再测试下,看是否能再进步点。





近来,我理解了索引的神奇之处,性能有了明显的进步。然而,我所学到的所有好像都找不到这个问题的答案。

索引是很好的,但是为什么不能有人仅仅索引全部的字段,使表变得非常快?我相信有一个很好的原因不这样做,但是在一个30字段的表中有三个字段怎样?在30个字段中输入10?应该在那里画线,为什么?

  • 实验将一个值插入到一个表中,该表中有超越10个索引项,由于插入/删除,全部项都必须更新,这是一个巨大的时间开销,假如每个值都有一个索引,则内存开销会有所增长。
  • 除了空间和写入性能之外,另有一个缘故:对单个表访问使用多个索引服从非常低。这意味着,纵然每列有一个索引,假如在WHERE子句中访问了多个列,则select性能也不是很好。在这种环境下,多列索引是最好的。

索引占用内存(RAM)中的空间;索引太多或太大,数据库将不得不在磁盘之间互换索引。它们还增长了插入和删除时间(必须为插入/删除/更新的每一条数据更新每个索引)。

你没有无穷的记忆。使全部索引都合适RAM=good。

你没有无穷的时间。只对需要索引的列进行索引可以最小化插入/删除/更新性能的影响。

  • 很好的答复能让人大抵明白,但在确定索引的底线方面没有太大帮助。你怎么知道?只需将它们添加到常见的范畴,并盼望得到最好的结果?
  • @一年半之后,安德鲁,你找到了问题的答案了吗?
  • @Sinjai通常将它们添加到d列中,这大概是一个很好的履历规则。但是假如你想成为指数专家的话,你可以做大量的阅读。比方stackoverflow.com/questions/3049283/&hellip;
  • 不要忘掉磁盘空间。

请记着,每次更新、插入或删除行时,都必须更新每个索引。以是索引越多,写操作的性能就越慢。

别的,每个索引占用更多的磁盘空间和内存空间(调用时),因此它也大概降低读取操作的速率(对于大型表)。看看这个

  • 链接用于MS SQL Server;此问题用于MySQL
  • @OMG链接中的大多数点实用于全部重要RDBMS
  • @Richardaka-Cyberkiwi:ansi不包括索引——这是一个古迹,每个供给商都使用了类似的术语。但即便如此,只有SQL Server和MySQL才使用术语"集群"和"非集群"索!引——这意味着在SQL Server中比在MySQL中更重要。没有什么可以保证一个供给商的提议应实用于另一个供给商。
  • @OMG前6点实用于任何DBMS。跳过非/聚集索引,下面是关于常规索引的更多点,也是关于点的更多点。假如你想指出一些详细的事情,打电话给他们。不然,就似乎你否认了评述中的全部答案(包括你删除的答案),没有人同意你的评估。

你必须均衡积垢的需要。写表格会变慢。至于画线的位置,这取决于数据的访问方法(排序过滤等)。

  • 并且每个索引都占用一些数据库空间
  • @Acanthus:可用的最小硬盘以GB为单位。
  • @但不是布莱恩指出的拉姆。储存超越你需要的量从来都不是一个好主意。RAM中的数据/索引缓存、备份介质(实用于每个磁带的版本等)都受到无用索引的影响。
  • 资源的丰富不是浪费或服从低下的缘故。
  • 是的,但这些限定并不是10多年前的环境。

索引将占用更多从驱动器和RAM分派的空间,但也会大大进步性能。不幸的是,当它到达内存限定时,系统将放弃驱动器空间并冒性能风险。实际上,您不应该索引任何您以为不涉及任何种类数据遍历算法的字段,既不插入也不搜索(WHERE子句)。但假如不是的话,你应该这样做。默认环境下,您必须索引全部字段。您应该思量取消索引的字段是,假如查询仅由版主使用,除非它们也需要速率。


这个答案是我个人的看法,我用我的数学逻辑往返答

第二个问题是关于界限在那里停止,首先让我们做一些数学盘算,假设我们在一个表中有n行和l个字段,假如我们对全部字段进行索引,我们将得到一个l个新的索引表,此中每个表都将以故意义的方法对索引字段的数据进行排序,乍一看,假如您的表是w权重,它将变为w*2。(1太拉将变为2太拉)假如你有100个大桌子(我已经在项目中工作过,在1800个桌子上分列了表号),你将浪费100倍的空间(100太拉),这是远远不够明智的。

假如我们将在全部表中应用索引,我们将不得不思量索引更新是一次更新触发全部索引更新这是一次全选无序等效时间

由此我得出的结论是,在这种环境下,假如您要开释这一时间,最幸亏select或update中丢失它,由于假如您要选择一个未编入索引的字段,则不会在全部未编入索引的字段上触发另一个select。

索引什么?

外键:必须基于

主键:我还不确定是否有人读过这篇文章可以帮助解决这个问题

其他范畴:第一个自然答案是剩下的一半:为什么:假如你应该索引更多,你就离最好的答案不远,假如你应该索引更少,你也不远,由于我们知道没有索引是坏的,全部索引也是坏的。

从这3点我可以得出结论,假如我们有由K键构成的L字段,那么极限应该在靠近 ((L-K)/2)+K 的某个地方,或多或少是l/10。

这个答案是基于我的逻辑和个人履历


索引表中的全部列不是一个好主意。虽然这将使表的读取速率非常快,但写入速率也会慢得多。写入一个索引了每个列的表将涉及将新记载放入该表中,然后将每个列的信息放入其自己的索引表中。

  • 我不确定这是否会让读取表的速率极快,特殊是假如数据表只有100MB,而index.table300MB或更高。
  • 你说的每一句话都是从前说过的。

本文网址: http://www.directapkdownloader.com/d/202073211345_4144_51088104/home