精华内容
下载资源
问答
  • mysql并发选择插入数据库速度

    千次阅读 2021-01-19 22:54:19
    这个表与我必须从中读取数据的表相同,有时整个数据库由于选择数据而变得很,同时还有许多挂起的插入。我把索引放在我在where语句中使用的每个字段上,所以我真的不知道为什么select变得如此缓慢。有人能给我一个提示...

    我有一个很大的mysql表(大约5米行),我经常在上面插入数据。

    这个表与我必须从中读取数据的表相同,有时整个数据库由于选择数据而变得很慢,同时还有许多挂起的插入。

    我把索引放在我在where语句中使用的每个字段上,所以我真的不知道为什么select变得如此缓慢。

    有人能给我一个提示来解决这个问题吗?

    这是表和查询的SQL

    CREATE TABLE `messages` (

    `id` int(10) unsigned NOT NULL auto_increment,

    `user_id` int(10) unsigned NOT NULL default '0',

    `dest` varchar(20) character set latin1 default NULL,

    `body` text character set latin1,

    `sent_on` timestamp NOT NULL default CURRENT_TIMESTAMP,

    `md5` varchar(32) character set latin1 NOT NULL default '',

    `interface` enum('mobile','desktop') default NULL,

    PRIMARY KEY (`id`),

    KEY `user_id` (`user_id`),

    KEY `md5` (`md5`),

    FULLTEXT KEY `dest` (`dest`,`body`),

    FULLTEXT KEY `body` (`body`)

    ) ENGINE=MyISAM AUTO_INCREMENT=7074256 DEFAULT CHARSET=utf8

    这里的问题是:

    EXPLAIN SELECT SQL_CALC_FOUND_ROWS id, sent_on, dest AS who, body,interface FROM messages WHERE user_id = 2 ORDER BY sent_on DESC LIMIT 0,50 \G;

    *************************** 1. row ***************************

    id: 1

    select_type: SIMPLE

    table: messages

    type: ref

    possible_keys: user_id

    key: user_id

    key_len: 4

    ref: const

    rows: 13997

    Extra: Using where; Using filesort

    1 row in set (0.00 sec)

    展开全文
  •   数据库索引有多种类型,常见文件的索引包括顺序文件上的索引、B+数索引、散列索引、位图索引等。 1.索引的建立 一般格式为: CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名>(<列名> ...

     
    一、索引的建立,修改与删除

      当表的数据量比较大时,查询操作会比较耗时,建立索引是加快查询速度的有效手段。根据需要可以在基

    本表上建立一个或多个索引,以提供多种存取路径,加快查找速度。

      数据库索引有多种类型,常见文件的索引包括顺序文件上的索引、B+数索引、散列索引、位图索引等。

    1.索引的建立

    一般格式为:

      CREATE [UNIQUE] [CLUSTER] INDEX <索引名>
      ON <表名>(<列名> [<次序>] [,<列名>[<次序>]]...);
    

      索引可以建立在该表的一列或多列上,列名之间用逗号分割。各个列名之后还可以用<次序>指定索引值的排

    列次序,可选ASC(升序)或DESC(降序),默认值为ASC。

      NUIQUE表明此索引的每一个索引值只对应唯一的数据记录。

      CLUSTER表示要建立的索引是聚簇索引。

    【例 3.13】为学生-课程数据库中的Student、Course 和 SC 三个表建立索引。其中Student表按学号升序建唯一索引,Course表按课程号升序建唯一索引,SC表按学号升序和课程号降序建唯一索引。

      CREATE UNIQUE INDEX Stusno ON Student(Sno);
      CREATE UNIQUE INDEX Coucno ON Course(Cno);
      CREATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno DESC);
    

    在相应的表下查看对应的索引:
    在这里插入图片描述

    2.索引的修改

    对于已经建立的索引,可以使用ALTER INDEX语句,其格式为

    ALTER INDEX <旧索引名> RENAME TO <新索引名>;
    

    【例 3.14】将SC表的SCno索引名改为SCSno。

    ALTER INDEX SCno RENAME TO SCSno;
    

    但是enmm,报错啦。。。
    在这里插入图片描述
    查看啦一下,原因是alter不能改变索引名,格式应该如下:

      EXEC sp_rename '表名.旧索引名', '新索引名', 'INDEX';
    

    然后改了一下,确实成功啦,但是下面有一句注意提示:

      EXEC sp_rename 'SC.SCno','SCSno','index';
    

    在这里插入图片描述
    3.删除索引

      建立索引是为啦减少查询操作的时间,但如果数据增、删、改频繁,系统会花费很多时间来维护,降低啦查

    询效率,这时可以删除一下不必要的索引.

    在SQL中,删除索引使用DROP INDEX语句,其一般格式为:

    DROP INDEX <索引名>;
    

    【例 3.15】删除 Student 表的 Stusname 索引。

    DROP INDEX Stusname;
    

    但是
    在这里插入图片描述
    所以,应修改为下面语句:

      DROP INDEX Student.Stusname; --注意该索引应该存在,才能被删除。
    

    4.这里介绍一下数据字典的概念。

      数据字典是关系数据库管理系统内部的一组系统表,他记录了数据库中的所有的定义信息,包括关系模式、

    视图定义、索引定义、完整性约束定义、各类用户对数据库的操作权限、统计信息等。关系数据库管理系统在执

    行SQL的数据定义语句时,实际上就是在更新数据字典表中的相关信息。在进行查询优化和查询处理时,数据字

    典中的信息时其重要依据。
     

    二、向表里面插入数据

      SQL的数据插入语句insert通常有两种形式,一种是插入一个元组,另一种是插入子查询结果。后者可以一

    次性插入多个元组。

    1.插入元祖

    格式为:

      INSERT 
      INTO <表名> [(<属性列1>[,<属性列2>]...)]
      VALUES(<常量1> [,<常量2>]...);
    

      (1)INTO子句中没有出现的属性列,新元祖在这些列上将取空值。但必须注意的是,在表定义时说明啦NOT NULL的属性列不能取空值,否则会出错。

    【例 3.69】将一个新学生元祖(学号:201215128,姓名:陈东,性别:男,所在系:IS,年龄:18)插入到Student中。

      INSERT
      INTO Student(Sno,Sname,Ssex,Sdept,Sage)
      VALUES('201215128','陈冬','男','IS',18);
    

      (2)VALUES子句对新元素的各属性赋值,字符串常数要用单引号(英文字符)括起来。

    【例 3.70】将学生张成民的信息插入到Student表中。

      INSERT
      INTO Student 
      VALUES('201215126','张成民','男',18,'CS');
    

      (3)在INTO子句中只指出表名,无属性列名时,VALUES子句对新元祖的各属性赋值时,一定要注意值与表中的属性列要一一对应。

      (4)如果INTO子句没有指明任何属性列名,则新插入的元祖必须在每个属性列均有值。

    【例 3.71】插入一条选课记录。

      INSERT
      INTO SC(Sno,Cno)
      VALUES('201215128','1');
    

    也可以是:

      INSERT
      INTO SC
      VALUES('201215128','1',NULL);
    

      因为这里没有指出 SC 的属性名,在Grade列要明确给出空值。

     这里在进行Course表插入元祖时,可能会报错,因为这里面有外键约束,Cpno的数必须是Cno已经出现过的数,否则会报错。
    在这里插入图片描述
    把书本上对应的内容输进去,然后查询一下,如下图:

    INSERT
    INTO Student
    VALUES('201215121','李勇','男',20,'CS'),
    	  ('201215122','刘晨','女',19,'CS'),
    	  ('201215123','王敏','女',18,'MA'),
    	  ('201215125','张立','男',19,'IS');
    	  
    INSERT
    INTO Course 
    VALUES('1','数据库',NULL,4),
    	  ('2','数学',NULL,2),
    	  ('3','信息系统','1',4),
    	  ('4','操作系统',NULL,3),
    	  ('5','数据结构',NULL,4),
    	  ('6','数据处理',NULL,2),
    	  ('7','PASCAL语言','6',4);
    	  
    INSERT
    INTO SC
    VALUES('201215121','1',92),
    	  ('201215121','2',85),
    	  ('201215121','3',88),
    	  ('201215122','2',90),
    	  ('201215122','3',80);
    

    在这里插入图片描述
    然后再将Cpno对应的改为原来的对应的Cno!!

    三、数据查询

    一般格式为:

      SELECT [ALL|DISTINCT]<目标列表达式>[,<目标列表达式>]...
      FROM <表名或视图名>[,<表名或视图名>...]|(<SELECT 语句>)[AS]<别名>
      [WHERE<条件表达式>]
      [GROUP BY <列名1>[HAVING<条件表达式>]]
      [ORDER BY <列名2>[ASC|DESC]];
    

    1.单表查询

    单表查询是指仅涉及一个表的查询。

    (1)指定查询列

    【例 3.16】查询全体学生的学号和姓名。

      SELECT Sno,Sname
      FROM Student;
    

    【例 3.17】查询全体学生的姓名、学号、所在系。

      SELECT Sname,Sno,Sdep
      FROM Student;
    

    在这里插入图片描述
    (2)查询全部列

    【例 3.18】查询全体学生的详细记录。

      SELECT *
      FROM Student;
    

    等价于

      SELECT Sno,Sname,Ssex,Sage,Sdep
      FROM Student;
    

    在这里插入图片描述
    好啦,感谢观看。后面再进行补充!

    展开全文
  • 最近的项目需要导入大量的数据,插入的过程中还需要边查询边插入插入的数据量在100w左右。一开始觉得100w的数据量不大,于是就插啊插...分析是否是由主码,外码,索引造成的插入效率降低主码:由于主码是每张表必...

    最近的项目需要导入大量的数据,插入的过程中还需要边查询边插入。插入的数据量在100w左右。一开始觉得100w的数据量不大,于是就插啊插,吃了个饭,回来一看,在插入了50多w条数据后,每秒就只能插10条了。。觉得很奇怪,为啥越插越慢呢?  于是就开始分析插入的时间损耗,想到了如下的解决方案:(mysql使用的INNODB引擎)

    1.分析是否是由主码,外码,索引造成的插入效率降低

    主码:由于主码是每张表必须有的,不能删除。而mysql会对主码自动建立一个索引,这个索引默认是Btree索引,因此每次插入数据要额外的对Btree进行一次插入。这个额外的插入时间复杂度约为log(n)。这个索引无法删除,因此无法优化。但是每次插入的时候,由于主码约束需要检查主码是否出现,这又需要log(n),能否减少这个开销呢?答案是肯定的。我们可以设置主码为自增id  AUTO_INCREMENT ,这样数据库里会自动记录当前的自增值,保证不会插入重复的主码,也就避免了主码的重复性检查。

    外码:由于我的项目的插入表中存在外码,因此每次插入时需要在另一张表检测外码存在性。这个约束是与业务逻辑相关的,不能随便删除。并且这个时间开销应当是与另一张表大小成正比的常数,不应当越插入越慢才对。所以排除。

    索引:为了减少Btree插入的时间损耗,我们可以在建表时先不建索引,先将所有的数据插入。之后我们再向表里添加索引。该方法确实也降低了时间的开销。

    经过以上的折腾,再进行测试,发现速度快了一点,但是到了50w条后又开始慢了。看来问题的关键不在这里。于是继续查资料,又发现了个关键问题:

    2.将单条插入改为批量插入(参考:点击打开链接)

    由于java中的executeUpdate(sql)方法只是执行一条sql操作,就需要调用sql里的各种资源,如果使用for循环不停的执行这个方法来插入,无疑是开销很大的。因此,在mysql提供了一种解决方案:批量插入。 也就是每次的一条sql不直接提交,而是先存在批任务集中,当任务集的大小到了指定阈值后,再将这些sql一起发送至mysql端。在100w的数据规模中,我将阈值设置为10000,即一次提交10000条sql。最后的结果挺好,插入的速度比之前快了20倍左右。批量插入代码如下:

    public static void insertRelease() {

    Long begin = new Date().getTime();

    String sql = "INSERT INTO tb_big_data (count, create_time, random) VALUES (?, SYSDATE(), ?)";

    try {

    conn.setAutoCommit(false);

    PreparedStatement pst = conn.prepareStatement(sql);

    for (int i = 1; i <= 100; i++) {

    for (int k = 1; k <= 10000; k++) {

    pst.setLong(1, k * i);

    pst.setLong(2, k * i);

    pst.addBatch();

    }

    pst.executeBatch();

    conn.commit();

    }

    pst.close();

    conn.close();

    } catch (SQLException e) {

    e.printStackTrace();

    }

    Long end = new Date().getTime();

    System.out.println("cast : " + (end - begin) / 1000 + " ms");

    }

    3.一条UPDATE语句的VALUES后面跟上多条的(?,?,?,?)

    这个方法一开始我觉得和上面的差不多,但是在看了别人做的实验后,发现利用这个方法改进上面的批量插入,速度能快5倍。后来发现,mysql的导出sql文件中,那些插入语句也是这样写的。。即UPDATE table_name (a1,a2) VALUES (xx,xx),(xx,xx),(xx,xx)... 。也就是我们需要在后台自己进行一个字符串的拼接,注意由于字符串只是不停的往末尾插入,用StringBuffer能够更快的插入。下面是代码:

    public static void insert() {

    // 开时时间

    Long begin = new Date().getTime();

    // sql前缀

    String prefix = "INSERT INTO tb_big_data (count, create_time, random) VALUES ";

    try {

    // 保存sql后缀

    StringBuffer suffix = new StringBuffer();

    // 设置事务为非自动提交

    conn.setAutoCommit(false);

    // Statement st = conn.createStatement();

    // 比起st,pst会更好些

    PreparedStatement pst = conn.prepareStatement("");

    // 外层循环,总提交事务次数

    for (int i = 1; i <= 100; i++) {

    // 第次提交步长

    for (int j = 1; j <= 10000; j++) {

    // 构建sql后缀

    suffix.append("(" + j * i + ", SYSDATE(), " + i * j

    * Math.random() + "),");

    }

    // 构建完整sql

    String sql = prefix + suffix.substring(0, suffix.length() - 1);

    // 添加执行sql

    pst.addBatch(sql);

    // 执行操作

    pst.executeBatch();

    // 提交事务

    conn.commit();

    // 清空上一次添加的数据

    suffix = new StringBuffer();

    }

    // 头等连接

    pst.close();

    conn.close();

    } catch (SQLException e) {

    e.printStackTrace();

    }

    // 结束时间

    Long end = new Date().getTime();

    // 耗时

    System.out.println("cast : " + (end - begin) / 1000 + " ms");

    }

    做了以上的优化后,我发现了一个很蛋疼的问题。虽然一开始的插入速度的确快了几十倍,但是插入了50w条数据后,插入速度总是会一下突然变的非常慢。这种插入变慢是断崖式的突变,于是我冥思苦想,无意中打开了系统的资源管理器,一看发现:java占用的内存在不断飙升。 突然脑海中想到:是不是内存溢出了?

    4.及时释放查询结果

    在我的数据库查询语句中,使用到了pres=con.prepareStatement(sql)来保存一个sql执行状态,使用了resultSet=pres.executeQuery来保存查询结果集。而在边查边插的过程中,我的代码一直没有把查询的结果给释放,导致其不断的占用内存空间。当我的插入执行到50w条左右时,我的内存空间占满了,于是数据库的插入开始不以内存而以磁盘为介质了,因此插入的速度就开始变得十分的低下。因此,我在每次使用完pres和resultSet后,加入了释放其空间的语句:resultSet.close(); pres.close();。重新进行测试,果然,内存不飙升了,插入数据到50w后速度也不降低了。原来问题的本质在这里!

    这个事情折腾了一天,也学到了很多。希望这篇博客能帮助到大家!

    展开全文
  • 看了一篇博文索引 插入数据时,索引也会进行相应的维护,如果索引较多就会影响插入速度。 删除数据时同理,所以最好是逻辑删除而不是物理删除。

    看了一篇博文索引
    插入数据时,索引也会进行相应的维护,如果索引较多就会影响插入速度。
    删除数据时同理,所以最好是逻辑删除而不是物理删除。

    展开全文
  • [例3.13] 为学生-课程数据库中的Student,Course,SC三个表建立索引。 CREATE UNIQUE INDEX Stusno ON Student(Sno); CREATE UNIQUE INDEX Coucno ON Course (Cno); CREATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno ...
  • 一般情况下mysql上百万数据读取和插入更新是没什么问题了,但到了上千万级就会出现很,下面我们来看mysql千万级数据库插入速度和读取速度的调整记录吧。(1)提高数据库插入性能中心思想:尽量将数据一次性写入到...
  • 什么是数据库索引

    2021-05-18 20:06:46
    BTREE 索引 ; HASH 索引 ; R-tree 索引(空间索引);Full-text (全文索引);BTREE树的特点 ; B+TREE树 ; 索引分类 ; 索引的用法 ; 如何去很高效的去使用索引
  • 作者:从菜鸟到老菜鸟来源:简书Java常见面试题汇总-----------数据库(数据库索引及其实现)-1.jpg (78.54 KB, 下载次数: 0)2021-1-2 03:12 上传54、数据库索引索引的优缺点优点:1、大大加快数据的检索速度;...
  • 有时表中的字段要求唯一,不能重复,就需要添加唯一索引: 后端代码: 插入时 code字段不能重复 import org.springframework.dao.DuplicateKeyException; 插入实体时,捕获异常,然后自己抛一个异常 前端...
  • [例3.13] 为学生-课程数据库中的Student,Course,SC三个表建立索引。 CREATE UNIQUE INDEX Stusno ON Student(Sno); CREATE UNIQUE INDEX Coucno ON Course (Cno); CREATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno ...
  • 数据库实验报告 索引和视图 (9页) 本资源提供全文预览,点击全文预览即可全文预览,如果喜欢文档就下载吧,查找使用更方便哦!19.90 积分1西北师范大学计算机科学与工程学院学生实验报告西北师范大学计算机科学与工程...
  • 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性 可以加速表和表之间的连接 二、 如何加索引 1. 场景介绍 引入一个场景,以下面的表为例,这个表有5个字段,分别是id,name,time,subject和grade,...
  • 关于MySql数据库主键及索引的区别

    千次阅读 2021-01-18 21:17:38
    一、什么是索引索引用来快速地寻找那些具有特定值的记录,所有MySQL索引都以B-树的形式保存。如果没有索引,执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录,直至找到符合要求的记录。表里面的记录数量...
  • 本文主要向大家介绍了MySQL数据库数据库索引是什么?它的工作流程是怎样的? ,通过具体的内容向大家展现,希望对大家学习MySQL数据库有所帮助。我们通过一个简单的例子来开始教程,解释为什么我们需要数据库索引...
  • 问题描述在DMS控制台中对RDS MySQL实例或自建MySQL数据库(ECS自建数据库或IDC自建数据库)进行创建索引等操作时,提示类似如下信息。ERROR 1799 (HY000): Creating index 'XXX' required more than'innodb_online_...
  • Hello,这是数据库实验报告系列的最后一份了,我将要结束这个系列了。再次感谢能点开这个报告合集系列的你~ 一、 实验目的及实验环境 (1) 理解索引和视图的概念 (2) 掌握索引的使用方法 (3) 掌握视图的定义和...
  • 如果在装载数据之前创建了一个或多个索引,那么在插入每行时DM数据库都必须更改和维护每个索引,使得插入效率降低。1.2怎样创建正确的索引(1)如果需要经常地检索大表中的少量的行,就为查询键创建索引;(2)为了改善...
  • 一.索引的作用一般的应用系统,读写比例在10:1左右,而且插入操作和一般的更新操作很少...但是当数据量和访问量剧增的时候,就会发现mysql变,甚至down掉,这就必须要考虑优化sql了,给数据库建立正确合理的索引,...
  • 数据库】MySQL索引分析

    千次阅读 多人点赞 2021-11-04 21:30:48
    文章目录索引索引的理解索引优缺点索引的分类索引的基本操作索引结构数据加载(B+树的原因)总结索引优化没必要建立索引的场景索引失效的场景隐式类型转换问题联合索引违反最左前缀匹配原则对索引列运算is null 判断...
  • 数据库视图及索引的创建与使用数据库视图及索引的创建与使用1、实验内容或题目视图、索引的创建与使用2、实验目的与要求(1)掌握创建视图的方法(2)能够对视图进行修改(3)能够对视图进行修改(4)理解索引的概念和索引的...
  • 全文检索技术是智能信息...创建全文索引后全文索引插入任何索引信息。当用户填充全文索引时,系统才将定义了全文索引的文本列的内容进行分词,并根据分词结果填充索引。用户可以在进行全文索引填充的列上使用 CONT
  • 数据库里的BTREE索引

    2021-02-07 13:53:43
    提到索引自然是有四种主流索引,分别是:BTREE,HASH,R-TREE,Full-Text,不同的引擎支持不同的索引,这里主要理解BTREE索引。 BTree又叫多路平衡搜索树,一颗m叉的BTree特性如下: 树中每个节点最多包含m个孩子。 ...
  • 索引是一种数据结构 ,能够帮助我们快速的检索数据库中的数据。 索引是对数据库表中一个或多个列的值进行排序的结构。关键点是索引包含一个表中列的值,并且这些值存储在一个数据结构中。 例如对employee 表的姓名 ...
  • 数据库实验报告 索引和视图.doc

    千次阅读 2021-02-08 07:36:18
    PAGEPAGE 1西北师范大学计算机科学与工程学院学生实验报告学 号专业计算机科学与技术姓名课程名称数据库应用与开发班级实验名称索引和视图课程类型专业限选课实验目的:在SQL SEVER 2005中,影响其数据库性的因素有...
  • mysql数据库插入

    2021-01-28 02:36:42
    通过binlog恢复mysql数据库在上一篇文章,我们讲解了有关mysql的binlog日志的基础知识。这篇文章,我们来讲解如何通过mysql的binlog日志来恢复数据库。在使用binlog日志来恢复数据库之前,我们有一些前提工作需要做...
  • mysql数据库索引是一种能够让mysql数据查询更加快速的数据结构,我们在新建数据库的时候,如果设置了某个字段的Primary key主键,那么数据库会默认为我们的主键字段创建一个唯一索引(Unique Index)的东西,所以你就...
  • Q1:数据库有哪些索引?优缺点是什么?1.B树索引:大多数数据库采用的索引(innoDB采用的是b+树)。能够加快访问数据的速度,尤其是范围数据的查找非常快。缺点是只能从索引的最左列开始查找,也不能跳过索引中的列,...
  • DM数据库具有全文检索功能,根据词库可以建立全文索引,大大减少检索时间。使用上主要包括全文索引建立、更新、利用全文索引进行检索、以及索引的删除。 1. 创建全文索引 首先建立一个示例表,表名为famous_novles,...
  • 数据库索引

    2021-07-13 15:07:37
    数据库索引 参考文章1:数据库索引 ... 为了使表记录和索引的排列顺序一致,在插入记录的时候,会对数据页重新排序 非聚集索引:给普通字段加上索引 表记录和索引的排列顺序不一定一致,两种索引都采
  • 数据库中创建索引是为了提升查询性能,但是建立索引也会降低修改性能。下面是几个面试中会问到的关于索引如何使用的经典面试问题。虽然是面向SQL Server,但对于其他任何关系型数据基本都适用。 为什么不对表中的每...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 343,309
精华内容 137,323
关键字:

数据库插入慢索引