Sql 先插数据后建索引和先建索引后插数据两个速度上的对比

最新推荐文章于 2024-04-24 11:00:00 发布
最新推荐文章于 2024-04-24 11:00:00 发布
阅读量1.4k 收藏
点赞数 3
分类专栏： Mysql 文章标签： Sql 先插数据后建索引和先建索引后插数据 Sql 先插数据后建索引 Sql 先建索引后插数据
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/u013010499/article/details/103511570
版权
通过对比实验，本文发现对于大规模数据处理，先插入数据再建立索引的方法相较于先建索引后插数据，能显著提高效率。实测数据显示，随着数据量增加，两者的性能差距进一步拉大。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

1.先建索引后插数据

 2.先插数据后建索引

 

 结论：同为四百万级数据，先建索引后插数据共用1316s

            先插数据后建索引共用330+233=563s

            先后试了几次数据量越大两者差距越大

mysql百万数据加索引前后对比
余生一个帆的博客
06-16 668
索引类型 1.主键索引：PRIMARY KEY，只有一个列作为主键，唯一标识，不可重复； 2.唯一索引：UNIQUE，唯一性索引和“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现一次，即值必须唯一，同一张表里的多个列都可以设置唯一索引。 3.普通索引：NORMAL，一张表可以创建多个普通索引，一个普通索引可以包含多个字段，允许数据重复，允许 NULL 值插入；（是我们经常使用到的索引） 4.全文索引：FULLTEXT，在特定的数据库引擎下才支持，作用是快速定位数据。 5.多列索引：让搜索关键词更
数据结构-索引-实验6：索引优化（MySQL-5.6）
饮啖茶食个包
11-28 1151
文章目录数据结构-索引-实验6：索引优化（MySQL-5.6）一、实验目的及要求二、实验环境及相关情况（包含使用软件、实验设备、主要仪器及材料等）1、实验设备：2、软件系统：三、实验内容四、实验步骤及结果（包含简要的实验步骤流程、结论陈述，可附页）（一）数据准备1、新建表结构2、检查（1）表结构（2）索引（3）表信息4、插入基础数据5、插入千万级别数据6、索引建立与删除SQL（二）概念：基数1、准...
先插入数据再建立索引与先建立索引再插入数据的区别
从未完美过的博客
09-29 9515
1、新建一个表结构，创建索引，将百万或千万级的数据使用insert导入该表。 2、新建一个表结构，将百万或千万级的数据使用isnert导入该表，再创建索引。 这两种效率哪个高呢？或者说用时短呢？ 表记录越大，索引个数越多，差异越明显。以前有过一个记录。 某表记录有1亿条左右，12个索引，删除全部索引的插入速度和保留这12个索引的，插入速度百倍。其实原理很简单，边插入边维护索引，开销太大了。 ...
关于先插入数据在维护索引 还是先加索引再插入数据
从未完美过的博客
11-06 3656
近期在更新数据库时，经常会遇到一个问题，先插入数据在加索引，还是先加索引再插入数据 以下通过测试了几次得到如下结果如图： 这个表大概是200多万行，加了两个组合索引，最终数据长度是190MB，索引长度是6MB，msyql8.0版本。 结论如下： 在新建表结构的情况下，先加索引后插入数据会快一些； 但是其他情况下如本身表格已经存有数据，在这个基础上再插入数据的情况还待下次再测试下。 ...
先插入数据再建立索引与先建立索引再插入数据 的区别
songyaxing的博客
09-26 9617
1、新建一个表结构，创建索引，将百万或千万级的数据使用insert导入该表。 2、新建一个表结构，将百万或千万级的数据使用isnert导入该表，再创建索引。 这两种效率哪个高呢？或者说用时短呢？ 我感觉无论先建还是后建索引，当有数据时都需要update索引数据，问题是有索引的情况下插数据与有数据的情况下建立索引，各自的消耗。 回复： 表记录
【Oracle】-【索引】先查数据再建索引，还是先建索引再插数据？
bisal的专栏
06-27 9411
问题： 1、新建一个表结构，创建索引，将百万或千万级的数据使用insert导入该表。 2、新建一个表结构，将百万或千万级的数据使用isnert导入该表，再创建索引。 这两种效率哪个高呢？或者说用时短呢？ 我感觉无论先建还是后建索引，当有数据时都需要update索引数据，问题是有索引的情况下插数据与有数据的情况下建立索引，各自的消耗。 实验： 100w记录， 1、先创建
数据库索引实践经验·关于数据库建索引和插数据两者先后顺序对效率的影响
专注交流
08-07 2518
  数据库索引实践经验·关于数据库建索引和插数据两者先后顺序对效率的影响   案例2·新数据库建索引和导数据   （1） 先定义索引 （schema）  再 （2) load 数据   比   （2)(1）快的理论分析（前提是实践下来确实是这样吗？ 你们谁实践了之后可以说一声）    【(1)(2)】的话是边写入数据边建立索引将索引写数据库； 【（2）（1）】 的话先把数据全部写入， （...
数据结构-索引-实验7：索引优化（MySQL-8.0）
饮啖茶食个包
11-28 1031
文章目录数据结构-索引-实验7：索引优化（MySQL-8.0）一、实验目的及要求二、实验环境及相关情况（包含使用软件、实验设备、主要仪器及材料等）1、实验设备：2、软件系统：三、实验内容四、实验步骤及结果（包含简要的实验步骤流程、结论陈述，可附页）（一）数据准备1、新建表结构2、检查（1）表结构（2）索引（3）表信息4、插入基础数据5、插入千万级别数据6、索引建立与删除SQL（二）概念：基数1、准...
Mysql数据库索引数据结构(HashMap,BTree,B+Tree)
F道人
04-25 1046
目录 概述 HashMap结构的索引 二叉树数据结构 B Tree数据结构 B+Tree数据结构 一：概述 1. 索引的定义 MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是一种数据结构。 数据库查询是数据库的主要功能之一，最基本的查询算法是顺序查找（linear search）时间复杂度为O(n)，显然在数据...
mysql先删后增并发时出现死锁_MySQL死锁案例分析一(先delete，再insert，导致死锁)...
weixin_39899021的博客
02-10 2938
一、死锁案例MySQL版本：Percona MySQL Server 5.7.19隔离级别：可重复读(RR)业务逻辑：并发下按某个索引字段先delete记录，再insert记录比如：begin;deletefromtbwhereorder_id=xxx;insertintotb(order_id)values(xxx);commit;二、MySQL锁基本概念S：共享锁(行级锁)X...
spring 事务中先删除再插入后唯一键冲突 delete then insert duplicate key
Mosiclone的博客
11-30 5321
今天在同事碰到了一个比较有意思的问题，为了实现某个场景中的数据更新和删除，想通过 delete all entities 然后 insert new entities 的方式减少判断数据是否删除的操作，结果由于表内有其他唯一索引报错唯一键冲突。并且也没有对唯一索引进行更新，从而导致后面插入新版本数据的过程中，如果是修改的数据那么会在唯一索引找到重复的键从而导致冲突。后面在 debug 的过程中，用 evaluate 查询数据确实不在，但是 insert 的时候会报唯一键冲突导致插入失败事务回滚。
oracle集合索引从0还是1开始,【Oracle】-【索引】先查数据再建索引，还是先建索引再插数据？...
weixin_42310891的博客
04-06 608
问题：1、新建一个表结构，创建索引，将百万或千万级的数据使用insert导入该表。2、新建一个表结构，将百万或千万级的数据使用isnert导入该表，再创建索引。这两种效率哪个高呢？或者说用时短呢？我感觉无论先建还是后建索引，当有数据时都需要update索引数据，问题是有索引的情况下插数据与有数据的情况下建立索引，各自的消耗。实验：100w记录，1、先创建表和索引，再插入数据，大约1.3min。2、...
MySQL索引详解之索引的利弊以及高效使用
X先生说
04-21 446
前言 很多人对数据库索引可能都是知其然却不知其所以然，对索引没有很深入的理解，在使用过程中也一知半解，导致没有办法准确高效地使用索引，甚至存在不少误用的情况，导致使用索引反而降低了系统的性能。下面就以MySQL索引为对象，通过几篇文章来带大家好好的学习下索引的知识。 索引的数据结构 索引的存储方式 索引的利弊以及高效使用 索引利弊 在前面的文章，我们学习了索引的数据结构和存储方式，下面再来理解索引...
MySQL先建索引后迁移数据与先迁移数据后建索引对比记录
qq_41970622的博客
09-28 708
前言: MySQL版本5.7.30 建表: CREATE TABLE `product` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`), KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /** * 创建副表 */ CREATE TABLE `product2` ( `i
MySQL数据库索引比较
weixin_44804108的博客
07-15 244
本文主要探索创建索引和不创建索引对查找效率的影响。
创建表、插入表数据和索引得准则
weixin_38225763的博客
04-24 487
这是因为在插入数据时，数据库需要同时更新索引，这会增加插入操作的开销。特别是对于FULLTEXT索引，由于它需要对文本数据进行分词和存储大量的索引数据，所以在插入数据时创建索引可能会非常慢。但是，创建FULLTEXT索引本身对于大容量的数据表来说是一个资源密集型的操作。当你有一个较大的数据集，并且你想要在这个数据集的某个或某些列上创建FULLTEXT索引时，通常的做法是先插入数据，然后再创建索引。因此，在决定为一个大表创建FULLTEXT索引之前，你应该仔细考虑这些资源消耗，并计划好操作的时间和空间。
【MySQL】-【索引的创建与设计原则】
CaraYQ的博客
01-11 1305
唯一性索引与唯一性约束是一一对应关系，你给某个字段添加了唯一性约束，它会自动生成唯一性索引，你给某个字段添加了唯一性索引，他会自动具备唯一性约束。MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。主键约束上自动就会有主键索引，主键索引就是聚簇索引，一张表里最多只能有一个主键索引，因为一张表中最多只能有一个主键。如果需要全文索引的是大量数据，建议先添加数据，再创建索引。一、隐式的方式创建索引。在声明有主键约束、唯一性约束、外键约束的字段上，会自动的添加相关的索引。
索引的创建方式与设计原则
Serendipity的博客
03-13 737
索引的创建方式与设计原则
insert into插入数据
最新发布
05-09
<think>嗯，用户现在想了解INSERT INTO语句在插入数据时的行为，以及主键设置对数据插入的影响。我之前已经回答过主键设置的影响，但这次用户还特别提到了INSERT INTO的具体行为，需要结合新的引用内容来补充。 首先，我需要回顾之前的回答，确保不重复，同时整合新引用中的信息。比如，引用[1]提到replace into的行为，这和主键冲突处理有关，用户可能想知道除了replace之外的其他方法，比如INSERT INTO的冲突处理选项。引用[2]讨论了处理主键重复的方法，比如忽略或更新重复行，这需要详细说明。引用[4]提到了批量插入和语法，这可能影响主键生成的效率。引用[5]有关MyBatis返回自增ID的方法，需要补充ORM框架的处理方式。 接下来，我需要组织内容结构。先解释主键如何影响INSERT INTO的基本行为，比如唯一性约束和自增机制。然后分情况讨论，当主键重复时INSERT INTO的不同处理方式，比如报错、忽略、更新，可能还要对比replace into。接着，结合ORM框架如MyBatis的配置，说明如何正确获取自增主键。最后，给出最佳实践和常见问题解答。 需要注意引用来源的正确标注，比如replace into的行为来自引用[1]，主键重复的解决方法来自引用[2]，批量插入语法来自引用[4]，MyBatis配置参考引用[5]。同时，用户之前的回答中提到的IDENTITY_INSERT和ORM配置需要与新内容整合，避免矛盾。 可能用户还关心性能问题，比如批量插入时主键自增的影响，或者分布式系统下的主键生成策略。但根据当前问题，应集中在INSERT INTO的行为和主键影响，不过可以适当提及优化建议，如使用批量插入提升效率，引用[4]的内容支持这一点。 需要确保回答结构清晰，分点列出，使用示例代码和实际场景说明。比如，在解释ON DUPLICATE KEY UPDATE时，给出SQL示例；在讨论ORM配置时，展示MyBatis的XML配置片段。 最后，检查是否覆盖了用户的所有潜在需求：主键如何影响插入行为，如何处理冲突，不同语句（INSERT vs REPLACE）的区别，ORM框架的配置，以及性能优化。相关问题部分需要引导用户深入探讨，比如主键设计的最佳实践或分布式系统中的问题。</think>### INSERT INTO语句的插入行为与主键影响解析 主键作为数据库表的核心约束，直接影响`INSERT INTO`语句的执行逻辑和结果。以下是详细分析： --- ### 一、主键对`INSERT INTO`的**核心影响** #### 1. **唯一性强制校验** - **行为**：插入数据时，数据库会检查主键列的值是否已存在： ```sql INSERT INTO users (id, name) VALUES (1, 'Alice'); -- 成功 INSERT INTO users (id, name) VALUES (1, 'Bob'); -- 报错：主键重复[^2] ``` - **机制**：主键唯一性约束由索引（如B+树）快速校验，时间复杂度为$O(\log n)$[^4]。 #### 2. **自增主键的自动生成** - **行为**：若主键设置为自增（`AUTO_INCREMENT`或`IDENTITY`），插入时无需显式赋值： ```sql -- MySQL示例 INSERT INTO orders (product, quantity) VALUES ('Book', 2); -- id自动生成 ``` - **原理**：数据库通过内部计数器管理自增值，保证线程安全[^4]。 #### 3. **隐式锁竞争** - **行为**：高并发插入时，自增主键可能因锁争用导致性能下降： ```sql -- 批量插入优化（减少锁粒度） INSERT INTO logs (content) VALUES ('log1'), ('log2'), ('log3'); -- 单次提交多条[^4] ``` --- ### 二、主键冲突时的**处理策略** #### 1. **默认行为：报错终止** ```sql INSERT INTO table (pk_col) VALUES (100); -- 若pk_col=100已存在，直接抛出异常[^2] -- 错误示例： -- Duplicate entry '100' for key 'PRIMARY' (MySQL) -- Violation of PRIMARY KEY constraint (SQL Server) ``` #### 2. **忽略冲突（INSERT IGNORE）** - **适用场景**：允许部分数据插入失败： ```sql -- MySQL语法 INSERT IGNORE INTO table (pk_col) VALUES (100); -- 存在则跳过，不报错[^2] ``` #### 3. **覆盖或更新冲突行** - **REPLACE INTO**（删除旧行后插入新数据）： ```sql REPLACE INTO table (pk_col, data) VALUES (100, 'new_value'); -- 若存在则删除再插入[^1] ``` - **ON DUPLICATE KEY UPDATE**（更新指定列）： ```sql -- MySQL特有语法 INSERT INTO table (pk_col, data) VALUES (100, 'new_value') ON DUPLICATE KEY UPDATE data = VALUES(data); -- 仅更新data列[^2] ``` --- ### 三、ORM框架中的**主键处理** #### 1. **MyBatis自增主键回填** - **配置**：通过`useGeneratedKeys`获取插入后的自增主键值： ```xml <!-- XML配置 --> <insert id="insertUser" useGeneratedKeys="true" keyProperty="id"> INSERT INTO users (name) VALUES (#{name}) </insert> ``` - **结果**：插入后实体对象的`id`字段自动填充[^5]。 #### 2. **批量插入优化** - **问题**：默认逐条插入自增主键效率低。 - **方案**：启用批处理模式（如MyBatis的`ExecutorType.BATCH`）[^4]。 --- ### 四、主键设计**最佳实践** 1. **自增主键适用场景** - 单机数据库：简化开发，避免手动分配冲突。 - 示例：用户表`users(id INT AUTO_INCREMENT)`。 2. **分布式主键方案** - **UUID**：全局唯一但无序，影响索引性能。 - **雪花算法**：生成有序唯一ID（如Twitter Snowflake）。 3. **复合主键使用原则** - 仅在业务强关联字段组合唯一时使用（如订单号+商品ID）。 --- ### 五、性能影响与优化 1. **插入速度对比** | 操作 | 时间复杂度 | 适用场景 | |---------------------|------------|---------------------| | 单条`INSERT` | $O(1)$ | 低频写入 | | 批量`INSERT` | $O(n)$ | 数据导入 | | `REPLACE INTO` | $O(\log n + 1)$ | 覆盖更新 | 2. **索引维护开销** - 主键索引：每次插入需更新B+树，批量插入可减少页分裂次数[^4]。 --- ### 六、常见问题解答 #### Q1: 主键冲突时如何获取已存在的行数据？ ```sql -- 使用INSERT ... ON DUPLICATE KEY UPDATE获取旧值（MySQL） INSERT INTO table (pk_col, data) VALUES (100, 'new_value') ON DUPLICATE KEY UPDATE old_data = data, data = 'new_value'; ``` #### Q2: 自增主键用尽怎么办？ - **方案**：修改列类型（如`INT`→`BIGINT`），需停机维护： ```sql ALTER TABLE table MODIFY id BIGINT AUTO_INCREMENT; ``` --- ### 引用总结 - `REPLACE INTO`通过先删后插实现覆盖写入[^1]。 - 主键冲突可通过`IGNORE`、`REPLACE`或`ON DUPLICATE`灵活处理[^2][^4]。 - ORM框架需正确配置以支持自增主键回填[^5]。 --- ### 相关问题 1. 如何设计分布式系统下的高可用主键生成方案？ 2. `INSERT ... ON DUPLICATE KEY UPDATE`与`REPLACE INTO`在事务处理上有何差异？ 3. 如何评估自增主键与UUID主键的索引性能差异？
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值