mysql索引优化篇（三）-CSDN博客

本文链接：https://blog.csdn.net/c_manito/article/details/70195724

引论：根据上俩篇，讲解了一些关于索引的详情和查询的优化，在这儿我将为大家分享索引在大数据下对插入的影响，做相关测试。

测试一：向数据库中插入100万级的数据，过程如下

在这儿我们可以看到我创建的是采用innodb存储引擎的数据表,当我测试的时候我就知道应该非常的慢，然后去吃了个午饭回来一看，吓一跳。居然还没跑完，我以为是不是数据库出问题了，等了一会才好，插入100万数据居然花了我55min20.36sec。这个时间实在是太长了，因此我们要对此做一些优化。

当数据量较大时，如上百万甚至上千万记录时，向MySQL数据库中导入数据通常是一个比较费时的过程。通常可以采取以下方法来加速这一过程：

1、对于Myisam类型的表，可以通过以下方式快速的导入大量的数据。 ALTER TABLE tblname DISABLE KEYS; loading the data ALTER TABLE tblname ENABLE KEYS; 这两个命令用来打开或者关闭Myisam表非唯一索引的更新。在导入大量的数据到一个非空的Myisam表时，通过设置这两个命令，可以提高导入的效率。对于导入大量数据到一个空的Myisam表，默认就是先导入数据然后才创建索引的，所以不用进行设置。

2、对于Innodb类型的表，有以下几种方式可以提高导入的效率： ①因为Innodb类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。如果Innodb表没有主键，那么系统会默认创建一个内部列作为主键，所以如果可以给表创建一个主键，将可以利用这个优势提高导入数据的效率。

②在导入数据前执行SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SET UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率。

③如果应用使用自动提交的方式，建议在导入前执行SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。

当我对数据表做以下修改：

上图可以看到在这儿我关了该数据表的唯一性索引（unique）和自增性auto_increment索引，然后在向数据库中插入100万数据。

结果如下：

在这儿可以看到。当我把该表的索引禁止时，再向数据库中插入100万级数据所消耗的时间只有1min 42.58sec。效率提高了30多倍。所以说索引是会降低插入的效率的。

为什么会降低插入效率？

索引的创建会占据磁盘空间。在插入时不仅要插入数据还要对索引进行更新。索引更新就是对数据的操作，当数据量非常大时，索引更新是非常耗时的。特别是唯一性（unique）索引。该索引会对每条数据做唯一性检查，所以在有索引的数据表中当往其中插入大量数据时耗时是非常大效率非常低。

测试结论：

首先说明，该结论是整个《浅谈数据库索引》的总结，前面介绍索引，索引对查询的影响，以及在这儿索引对插入的影响。

索引的特点

优点：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构，

作为通用规则，只有当经常查询索引列中的数据时，才需要在表上创建索引。索引占用磁盘空间，并且降低添加、删除和更新行的速度。在多数情况下，索引用于数据检索的速度优势大大超过它的。

缺点：建立太多的索引将会影响更新和插入的速度，因为它需要同样更新每个索引文件。对于一个经常需要更新和插入的表格，就没有必要为一个很少使用的where字句单独建立索引了，对于比较小的表，排序的开销不会很大，也没有必要建立另外的索引。在某些情况下，建立索引的话也未必会快，例如索引放在一个不连续的存储空间时，这会增加读磁盘的负担，因此，哪个是最优，应该通过实际的使用环境来检验。

建立合适索引需要考虑的因素：

总的来说，索引建立之前需要结合实际应用和数据分布进行分析再分析，通常会考虑下面几点