-
2021-01-27 05:10:57
最近的项目需要导入大量的数据,插入的过程中还需要边查询边插入。插入的数据量在100w左右。一开始觉得100w的数据量不大,于是就插啊插,吃了个饭,回来一看,在插入了50多w条数据后,每秒就只能插10条了。。觉得很奇怪,为啥越插越慢呢? 于是就开始分析插入的时间损耗,想到了如下的解决方案:(mysql使用的INNODB引擎)
1.分析是否是由主码,外码,索引造成的插入效率降低
主码:由于主码是每张表必须有的,不能删除。而mysql会对主码自动建立一个索引,这个索引默认是Btree索引,因此每次插入数据要额外的对Btree进行一次插入。这个额外的插入时间复杂度约为log(n)。这个索引无法删除,因此无法优化。但是每次插入的时候,由于主码约束需要检查主码是否出现,这又需要log(n),能否减少这个开销呢?答案是肯定的。我们可以设置主码为自增id AUTO_INCREMENT ,这样数据库里会自动记录当前的自增值,保证不会插入重复的主码,也就避免了主码的重复性检查。
外码:由于我的项目的插入表中存在外码,因此每次插入时需要在另一张表检测外码存在性。这个约束是与业务逻辑相关的,不能随便删除。并且这个时间开销应当是与另一张表大小成正比的常数,不应当越插入越慢才对。所以排除。
索引:为了减少Btree插入的时间损耗,我们可以在建表时先不建索引,先将所有的数据插入。之后我们再向表里添加索引。该方法确实也降低了时间的开销。
经过以上的折腾,再进行测试,发现速度快了一点,但是到了50w条后又开始慢了。看来问题的关键不在这里。于是继续查资料,又发现了个关键问题:
更多相关内容 -
MySQL插入数据
2021-02-06 23:52:45MySQL 插入数据MySQL 表中使用 INSERT INTO SQL语句来插入数据。你可以通过 mysql> 命令提示窗口中向数据表中插入数据,或者通过PHP脚本来插入数据。语法以下为向MySQL数据表插入数据通用的 INSERT INTO SQL语法...MySQL 插入数据
MySQL 表中使用 INSERT INTO SQL语句来插入数据。
你可以通过 mysql> 命令提示窗口中向数据表中插入数据,或者通过PHP脚本来插入数据。
语法
以下为向MySQL数据表插入数据通用的 INSERT INTO SQL语法:
INSERT INTO table_name ( field1, field2,...fieldN )
VALUES
( value1, value2,...valueN );
如果数据是字符型,必须使用单引号或者双引号,如:"value"。
通过命令提示窗口插入数据
以下我们将使用 SQL INSERT INTO 语句向 MySQL 数据表 CodingDict_tbl 插入数据
实例
以下实例中我们将想 CodingDict_tbl 表插入三条数据:
root@host# mysql -u root -p password;
Enter password:*******
mysql> use CodingDict;
Database changed
mysql> INSERT INTO CodingDict_tbl
->(CodingDict_title, CodingDict_author, submission_date)
->VALUES
->("Learn PHP", "John Poul", NOW());
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO CodingDict_tbl
->(CodingDict_title, CodingDict_author, submission_date)
->VALUES
->("Learn MySQL", "Abdul S", NOW());
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO CodingDict_tbl
->(CodingDict_title, CodingDict_author, submission_date)
->VALUES
->("JAVA Tutorial", "Sanjay", '2007-05-06');
Query OK, 1 row affected (0.01 sec)
mysql>
注意: 使用箭头标记(->)不是SQL语句的一部分,它仅仅表示一个新行,如果一条SQL语句太长,我们可以通过回车键来创建一个新行来编写SQL语句,SQL语句的命令结束符为分号(;)。
在以上实例中,我们并没有提供 CodingDict_id 的数据,因为该字段我们在创建表的时候已经设置它为 AUTO_INCREMENT(自动增加) 属性。 所以,该字段会自动递增而不需要我们去设置。实例中 NOW() 是一个 MySQL 函数,该函数返回日期和时间。
使用PHP脚本插入数据
你可以使用PHP 的 mysql_query() 函数来执行 SQL INSERT INTO 命令来插入数据。
该函数有两个参数,在执行成功时返回 TRUE,否则返回 FALSE。
语法
bool mysql_query( sql, connection );
参数描述
sql必需。规定要发送的 SQL 查询。注释:查询字符串不应以分号结束。
connection可选。规定 SQL 连接标识符。如果未规定,则使用上一个打开的连接。
实例
以下实例中程序接收用户输入的三个字段数据,并插入数据表中:
向 MySQL 数据库添加数据if(isset($_POST['add']))
{
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('Could not connect: ' . mysql_error());
}
if(! get_magic_quotes_gpc() )
{
$CodingDict_title = addslashes ($_POST['CodingDict_title']);
$CodingDict_author = addslashes ($_POST['CodingDict_author']);
}
else
{
$CodingDict_title = $_POST['CodingDict_title'];
$CodingDict_author = $_POST['CodingDict_author'];
}
$submission_date = $_POST['submission_date'];
$sql = "INSERT INTO CodingDict_tbl ".
"(CodingDict_title,CodingDict_author, submission_date) ".
"VALUES ".
"('$CodingDict_title','$CodingDict_author','$submission_date')";
mysql_select_db('CodingDict');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
die('Could not enter data: ' . mysql_error());
}
echo "Entered data successfullyn";
mysql_close($conn);
}
else
{
?>
Tutorial Title Tutorial Author Submission Date [ yyyy-mm-dd ] }
?>
在我们接收用户提交的数据时,为了数据的安全性我们需要使用 get_magic_quotes_gpc() 函数来判断特殊字符的转义是否已经开启。如果这个选项为off(未开启),返回0,那么我们就必须调用addslashes 这个函数来为字符串增加转义。
你也可以添加其他检查数据的方法,比如邮箱格式验证,电话号码验证,是否为整数验证等。
-
MySQL插入数据的三种方法
2021-07-26 16:49:00Mysql 插入数据 1、mysql中常用的三种插入数据的方法 insert into:正常的插入数据,插入数据的时候会检查主键或者唯一索引,如果出现重复就会报错。 replace into:替换数据。插入时,如果表中已经存在相同的...Mysql 插入数据
1、mysql中常用的三种插入数据的方法
- insert into:正常的插入数据,插入数据的时候会检查主键或者唯一索引,如果出现重复就会报错。
- replace into:替换数据。插入时,如果表中已经存在相同的primary key或者unique索引,则用新数据替换;如果没有相同的primary key或者unique索引,则直接插入。
- insert ignore into:插入时,如果表中已经存在相同的primary key或unique索引,则不插入;如果没有相同的primary key或者unique索引,则直接插入。这样就不用校验是否已经存在了,有则忽略,无则添加
注:对于以上三种方法,如果表中没有设置主键或唯一索引;则效果都是一样的,即直接插入数据。
2、语法介绍
2.1 insert into
每个字段与其值是严格一一对应的。也就是说:每个值、值的顺序、值的类型必须与对应的字段相匹配。 语法如下:# 第一种方式 insert into 表名(字段名1,字段名2,...) values(值1,值2,...); # 第二种方式:按照表中所有字段进行插入数据,一定要与字段在表中定义的顺序一致 insert into 表名 values(值1,值2,...);
2.2 insert ignore into
这种方式的语法跟insert into 是一样的 语法如下:# 第一种方式 insert ignore into 表名(字段名1,字段名2,...) values(值1,值2,...); # 第二种方式:按照表中所有字段进行插入数据,一定要与字段在表中定义的顺序一致 insert ignore into 表名 values(值1,值2,...);
2.3 replace into
语法与上面大同小异,如下所示:# 第一种方式 replace into 表名(字段名1,字段名2,...) values(值1,值2,...); # 第二种方式:按照表中所有字段进行插入数据,一定要与字段在表中定义的顺序一致 replace into 表名 values(值1,值2,...);
3、使用示例
初始表准备: 创建一个actor 表, actor_id 是主键CREATE TABLE actor ( actor_id smallint(5) NOT NULL PRIMARY KEY, first_name varchar(45) NOT NULL, last_name varchar(45) NOT NULL, last_update DATETIME NOT NULL); insert into actor values ('3', 'WD', 'GUINESS', '2006-02-15 12:34:33');
此时查询:
select * from actor
开始测试:
(一)测试insert into
例如,现在再插入actor_id 为 3 的数据insert into actor values ('3', 'ED', 'CHASE', '2006-02-15 12:34:33');
结果:
/* SQL错误(1062):Duplicate entry ‘3’ for key ‘actor.PRIMARY’*/
此时表中的数据:actor_id 为 3的数据是没有插入成功的(二)测试insert ignore into
例如,做同样的事情,现在再插入actor_id 为 3 的数据insert ignore into actor values ('3', 'ED', 'CHASE', '2006-02-15 12:34:33');
结果:
insert ignore into actor values (‘3’, ‘ED’, ‘CHASE’, ‘2006-02-15 12:34:33’)
Affected rows: 0
时间: 0s此时表中的数据:actor_id 为 3的新数据是没有插入的,表中的所有数据没有发生变化。此时并没有报错,所以当我们需要插入一条未知是否已存在的数据时,可以直接使用该语句插入,而不用提前进行校验。
(四)测试 replace into
经过上面的操作,目前的表数据并未发生变化。
此时做同样的事情,现在再插入actor_id 为 3 的数据
replace into actor values ('3', 'ED', 'CHASE', '2006-02-15 12:34:33');
结果:
replace into actor values (‘3’, ‘ED’, ‘CHASE’, ‘2006-02-15 12:34:33’)
Affected rows: 2
时间: 0.006s再次查询表中数据,发现actor_id为3 的数据已经被更新。
插入成功。另外说明:之所以Affected rows为2是因为replace返回的数值是由新行和旧行的变化一起决定的,在本例子中旧行消失了,增加了一条新行,所以返回值是2。当我们插入一条主键不重复的数据时,replace返回的值为1 ,因为只增加了一个新行。例子如下:replace into actor values ('4', 'AD', 'JASON', '2006-02-15 12:34:33');
replace into actor values (‘4’, ‘AD’, ‘JASON’, ‘2006-02-15 12:34:33’)
Affected rows: 1
时间: 0.005s此时表中的数据如下:
对于表中未设置主键的情况下,三种插入数据的方法,读者可自行尝试。
-
python操作mysql插入数据
2021-12-02 15:34:20python操作mysql插入数据首先安装pymysql这个库pycharm连接数据库操作mysql语句连接数据库插入数据 由于有时候,数据存在excel表格中,需要借助python去读取数据然后再插入到数据库中 首先安装pymysql这个库 ...python操作mysql插入数据
由于有时候,数据存在excel表格中,需要借助python去读取数据然后再插入到数据库中
首先安装pymysql这个库
pycharm连接数据库
这一步可要可不要,虽然那个sql语句会报红,但是其实运行起来也不会报错,连接了过后,pycharm会连接到那个数据库,可以识别插入语句,也就不会报红了
操作mysql语句
连接数据库
def db_connect(): connection = pymysql.connect(host='localhost', user='root', password='123456', database='charge', charset="utf8") connection.select_db('charge') #选择数据库 # print(connection) return connection
插入数据
当然也有创建数据库这些操作,都要自己写sql语句,然后直接使用cusor.execute直接使用该语句即可
def db_insert(connection,data): cusor = connection.cursor() # 使用 cursor() 方法创建一个游标对象 cursor,cursor返回一个游标实例对象,其中包含了很多操作数据的方法 # insert=cusor.executemany(sql,[(4,'wen',20),(5,'tom',10),(6,'test',30)]) 多行插入 sqlWord = "insert into charge_record values(%s,%s,%s,%s,%s,%s,%s,%s)" #sql语句 sqlWord1 = "insert into charge_record values(null,'root','2021/12/1 21.01', '饮料', '饮食', '副食', '4.0', '')" # cusor.execute(sqlWord1) # cusor.execute(sqlWord,(null,'root','2021/12/1 21.01', '饮料', '饮食', '副食', '4.0', '')) #以上为单行插入 try: # cusor.execute(sqlWord1) # print(1) cusor.executemany(sqlWord,data) #data是你的数据,列表套元祖,例子:[('1'),('2'),('3')] cusor.connection.commit() #如果成功,则commit,connection.commit()也可以直接这样 except Exception as e: connection.rollback() #如果错误,回滚操作 print(e) print("失败") finally: connection.close() #操作完过后都要去关闭连接
-
MySQL插入数据很慢优化思路
2021-04-13 20:49:42MySQL插入数据很慢优化思路解决方法与思路 解决方法与思路 插入数据量非常大的场景 加大mysql配置中的bulk_insert_buffer_size,这个参数默认为8M bulk_insert_buffer_size=100M 修改该条记录有助于千万级别数据... -
Mysql 插入数据的几种方式
2019-11-01 09:14:47Mysql 插入数据 -
mysql插入数据失败原因分析
2021-01-19 03:31:19其实操作起来也很简单的,但是中途出现了问题,100条左右的数据,总会有10来条数据导入失败,我不想浪费这些资源啊,还是想要这些数据,如实开始分析原因。select查询数据库A里面的信息,print_f打印出来,完成正... -
mySQL插入数据自动生成时间
2021-03-16 19:52:40mySQL插入数据自动生成时间 使用navcat for mysql工具创建数据库时,如果需要设置一个时间字段,并且在插入数据时让这个时间是自动生成。可以参考下面方法: 1、在表中time字段是需要在添加新数据的时候,自动生成... -
七种MYSQL插入数据后返回自增主键ID的方法
2020-10-26 10:09:34MYSQL插入数据后返回自增ID的方法 mysql和oracle插入的时候有一个很大的区别是,oracle支持序列做id,mysql本身有一个列可以做自增长字段,mysql在插入一条数据后,如何能获得到这个自增id的值呢? 1、使用last_... -
nodejs之MySQL插入数据
2019-09-06 12:59:30const mysql=require(‘mysql’); //创建链接池 var pool=mysql.createPool({ host:‘127.0.0.1’, port:3306, user:‘root’, password:‘123456’, database:‘course’, //使用的数据库 ...//插入数据 pool... -
mysql插入数据的时候丢失数据
2019-09-08 10:36:42mysql插入数据的时候丢失数据 直接po截图和代码 什么时候会丢失数据? character_set_connection和服务器的字符集比character_set_client小时 如下 什么时候才不会丢失数据? 如下才不会丢失数据 要... -
mysql 插入数据后返回该条数据id
2019-08-27 12:21:38在做关联表插入操作时,需要根据主表的 主键id作详情表的属性值,最笨的方法就是,先插入主表,然后通过查询返回刚刚插入的 主键id,继续 添加详情表数据。 解决办法: 在mybatis的配置文件中,有个叫keyProperty... -
如何提高mysql插入数据的速度
2019-07-04 13:12:53提高mysql插入数据的速度 在myisam engine下: 尽量使用insert into table_name values (…), (…),(…)这样的形式插入数据,避免使用inset into table_name values (); inset into table_name values (); inset ... -
如何获取 MySQL 插入数据的自增 ID
2020-06-15 14:15:43点击上方Java后端,选择设为星标优质文章,及时送达mysql和oracle插入的时候有一个很大的区别是,oracle支持序列做id,mysql本身有一个列可以做自增长字段,mysq... -
MySql 插入数据前判断数据是否存在
2019-12-27 16:27:58貌似 mysql是为了跟oracle的语法兼容而加了这个假表,仅仅是想保证 select 。。 from table 的这个格式而已。 因为mysql 可以直接select 不需要 from table,而oracle 不行。 比如,mysql支持, SELECT NOW();获取... -
MYSQL插入数据后返回主键
2018-10-30 08:42:22最近做项目,要求不能使用mybatis等框架,所以一切数据库访问操作都是用原生的JDBC。...这个mysql内置函数可以返回当前连接最后一条插入的数据的主键,值得注意的是,如果你一次插入了多条记录,这个函数返回的是第... -
MySQL插入数据出错及解决方案
2019-03-23 23:14:22项目中对MySQL插入数据的时候,出现错误信息,查阅网上相关文章后解决,记录之,下次供参考。 -
mysql插入数据自动生成主键uuid
2019-11-19 16:06:38DemoMapper.java //注意方法的返回值必须是void; void add(Demo demo); ============================================== demoMapper.xml: <insert id="add" parameterType="com.demo.pojo.Demo">...sel... -
mysql插入数据时学要加引号的数据类型
2019-06-18 12:55:33mysql中批量插入数据 insert into 表名 values (数据1), (数据2)…… 在这些插入的数据中有些数据类型需要加引号,具体总结如下 字符串类型和日期类型:需要加引号 (’ ') 如CHAR、VARCHAR、TEXT、DATE、DATETIME、... -
mysql插入数据后返回自增ID的方法(AUTO_INCREMENT)
2019-06-25 15:06:19mysql插入数据后返回自增ID的方法 mysql在插入一条数据后,如何能获得到这个自增id的值呢?即怎么获取设置为自增主键的id(AUTO_INCREMENT)?? 方法一:是使用last_insert_id mysql> SELECT LAST_INSERT_ID();... -
向mysql插入数据显示乱码的问题
2018-01-18 16:22:59向mysql数据库插入数据显示乱码的问题 在做一个java web工程时,有时会碰到在向数据库添加数据库时,结果出现乱码”???“的问题。针对该问题的主要解决办法有几种: 第一:确保是否添加了字符集过滤器 在项目的... -
Mysql插入数据去重方法
2018-03-28 11:10:13最近写爬虫,有需求需要将数据插入mysql,由于爬虫采用分布式,可能会多次爬重复页,这时候就需要对数据去重假设:表:test 含 id,a,b (3个字段)主键id自增。1.根据单个字段去重以navicat为例,右键设计表-》... -
mysql插入数据,获取最新插入的ID(自增列)的思路和python获取MySQL自增ID代码三种实现
2018-08-10 11:56:15mysql插入数据,获取最新插入的ID(自增列)的思路和python获取MySQL自增ID代码三种实现 mysql本身有一个列可以做自增长字段,mysql在插入一条数据后,如何能获得到这个自增id的值呢? (1)方法一 是使用last_... -
mysql插入数据,怎么根据时间字段自动排序
2016-12-11 09:30:29我要写个php代码,在插入数据的时候获取当前时间,并且插入到数据库对应的字段里面,然后整个表根据时间的先后,改变原来的ID值,让最新的数据始终保持ID值为1。 能行么 -
MyBatis向MySQL插入数据,全是0问题
2019-04-29 17:31:50INSERT into `user` (id,login_name,pass_word,phone,email,create_time,update_time) VALUES (id= '11',login_...此时 mysql 会将 id ='11'等数据当做逻辑判断处理, 为 id ='11'=false=0 -
MySQL插入数据时反斜杠 '\' 丢失
2019-11-19 18:45:14背景:获取的文件路径在添加进MySQL数据库中时,'\' 消失 解决办法:将 '\' 转换为 '/' 存入数据库 filepath.replaceAll("\\\\", "/") -
mysql 插入数据后返回自增 ID 的七种方法
2018-06-30 17:57:57mysql 在插入一条数据后,如何能获得到这个自增 id 的值呢? 一:使用 last_insert_id()SELECT LAST_INSERT_ID(); 1. 每次 mysql 的 query 操作在 mysql 服务器上可以理解为一次“原子”操作, ...