我要评论通常情况下，我们会使用以下sql语句来更新字段值：
update mytable set myfield='value' where other_field='other_value';
这种方法并没有什么任何错误，并且代码简单易懂，mysql 批量更新大数据是困难的事情，特别是当数据量特别大、sql语句运行时间超长时。
在做系统优化的时候，我们总是想尽可能的减少数据库查询的次数，以减少资源占用，同时可以提高系统速度
1、replace into 批量更新(unique索引或primary key)
replace into test_tbl (id,dr) values (1,'2'),(2,'3'),...(x,'y');
2、insert into ...on duplicate key update批量更新
- 导致在一个unique索引或primary key中出现重复值，则在出现重复值的行执行update。如果不会导致唯一值列重复的问题，则插入新行。
- values(col_name)函数只是取当前插入语句中的插入值，并没有累加功能。
- 每次更新都会更新该表的自增主键id，如果更新频率很快，会导致主键id自增的很快，过段时间就超过数字类型的的范围了，解决方法：innodb_autoinc_lock_mode=0
- 事务隔离级别repeatable-read下会发生死锁。
创建案例表 word_count_0626（单词计数表）
create table if not exists word_count_0626 (
id int(11) not null auto_increment,
word varchar(64) not null,
count int(11) default 0,
date date not null,
primary key (id),
unique key word (word, date)
) engine=innodb default charset=utf8;1. 执行第一次：（首次数据库表中没有数据，正常插入）
insert into word_count_0626 (word, count, date) values
('a',5,curdate())
on duplicate key update count=values(count);
# 结果显示：
id word count date
1 a 5 2019-06-262. 执行第二次：（与第一次的唯一(word,date)冲突，执行更新）
insert into word_count_0626 (word, count, date) values
('a',6,curdate())
on duplicate key update count=values(count);
# 结果显示：
id word count date
1 a 6 2019-06-26 （更新）3. 执行第三次：
insert into word_count_0626 (word, count, date) values
('a',6,curdate()-1), // 取前一天，不会冲突
on duplicate key update count=values(count);
# 结果显示：
id word count date
3 a 6 2019-06-25 （新插入,id跳跃了）
#批量
insert into table1(`id`,`name`,`password`)
values('4','修改4','xiugaimima4'),
('5,'修改5','xiugaimima5')
on duplicate key update
name=values(name),password=values(password);业务代码实现
/**
* 使用on duplicate key判断是插入还是更新.
*
* @param string $tablename
* @param array $data
* @param string $uniquekeys 'field,field2'
*
* @return mixed 正常返回添加或者更新记录的id值，出错返回错误信息，默认返回false
*
*/
final public function insertonduplicatekeyupdate($tablename, array $data, $uniquekeys = ''): bool
{
$result = false;
$tablename = $tablename ?: $this->tablename;
if (!empty($data) && is_array($data) && count($data) !== count($data, count_recursive)) {
try {
$keys = array_keys(current($data));
$unique = explode(',', $uniquekeys);
$items = $updates = [];
foreach ($data as $item) {
if (array_keys($item) === $keys) {
$items[] = sprintf("('%s')", implode("','", array_values($item)));
} else {
throw new \exception('批量保存时[field-values]字段列表位置不一致,保存失败');
}
foreach ($keys as $keyname) {
if (in_array($keyname, $unique) || empty($uniquekeys)) {
$updates[] = "`$keyname`='{$item[$keyname]}'";
}
}
}
$sql = sprintf('insert into `%s` (`%s`) values %s on duplicate key update %s', $tablename, implode('`,`', $keys), implode(',', $items), implode(',', $updates));
if (!empty($items)) {
$this->conn->exec($sql);
$result = true;
}
} catch (\exception $exception) {
die($exception->getmessage());
}
}
return $result;
}
/**
* 批量插入数据.
*
* @param string $tablename
* @param array $data
*
* @return bool
*/
final public function addbatch($tablename, array $data): bool
{
$result = false;
$tablename = $tablename ?: $this->tablename;
if (!empty($data) && is_array($data) && count($data) !== count($data, count_recursive)) {
try {
$keys = array_keys(current($data));
$sql = sprintf('insert ignore into `%s` (`%s`) values ', $tablename, implode('`,`', $keys));
$items = [];
foreach ($data as $item) {
if (array_keys($item) === $keys) {
$items[] = sprintf("('%s')", implode("','", array_values($item)));
} else {
throw new \exception('批量保存时[field-values]字段列表位置不一致,保存失败');
}
}
if (!empty($items)) {
$sql .= implode(',', $items);
$this->conn->exec($sql);
$result = true;
}
} catch (\exception $exception) {
$this->writelog($exception->getmessage());
}
}
return $result;
}
/**
* 快速导出csv文件.
*
* @param $file "/var/lib/mysql-files/app.csv"
* @param $tablename 表名
*/
public function outfilecsv($file, $tablename)
{
$page = 1;
do {
$filesuffix = explode('.', $file)[1];
$newfile = explode('.', $file)[0]."_{$page}.$filesuffix";
if (is_file($newfile)) {
//fixed mysql: general error: 1086 file already exists
unlink($newfile);
}
$start = ($page - 1) * self::limit_file_rows;
$end = $page * self::limit_file_rows;
$sql = <<<outfile
select * into outfile '$newfile' character set gbk fields terminated by ',' lines terminated by '\r\n' from $tablename where id>$start and id <=$end;
outfile;
$result = $this->conn->exec($sql);
++$page;
} while ($result);
}3、使用mysql 自带case when构建批量更新
update book
set author = case id
when 1 then '黄飞鸿'
when 2 then '方世玉'
when 3 then '洪熙官'
end
where id in (1,2,3)程序实现:case when的方式经过测试建议将修改记录条数控制在1w左右，不要超过2w，否则会耗费的时间也是成倍增加的
<?php
/**
* 批量更新函数
* @param $data array 待更新的数据，二维数组格式
* @param $table string 更新的表名
* @param string $field string 值不同的条件，默认为id
* @param array $params array 值相同的条件，键值对应的一维数组
* @return bool|string
*/
function batchupdateusecasewhen($data, $table, $field, $params = [])
{
if (!is_array($data) || !$field || !is_array($params)) {
return false;
}
$updates = parseupdate($data, $field);
$where = parseparams($params);
// 获取所有键名为$field列的值，值两边加上单引号，保存在$fields数组中
$fields = array_column($data, $field);
$fields = implode(',', array_map(function ($value) {
return "'" . $value . "'";
}, $fields));
$sql = sprintf("update `%s` set %s where `%s` in (%s) %s", $table, $updates, $field, $fields, $where);
return $sql;
}
/**
* 将二维数组转换成case when then的批量更新条件
* @param $data array 二维数组
* @param $field string 列名
* @return string sql语句
*/
function parseupdate($data, $field)
{
$sql = '';
$keys = array_keys(current($data));
foreach ($keys as $column) {
$sql .= sprintf("`%s` = case `%s`", $column, $field);
foreach ($data as $line) {
$sql .= sprintf("when '%s' then '%s'", $line[$field], $line[$column]);
}
$sql .= "end,";
}
return rtrim($sql, ',');
}
/**
* 解析where条件
* @param $params
* @return array|string
*/
function parseparams($params)
{
$where = [];
foreach ($params as $key => $value) {
$where[] = sprintf("`%s` = '%s'", $key, $value);
}
return $where ? ' and ' . implode(' and ', $where) : '';
}以上方案大大减少了数据库的查询操作次数，大大节约了系统资源不过这个有个缺点 ：
要注意的问题是sql语句的长度，需要考虑程序运行环境所支持的字符串长度，当然这也可以更新mysql的设置来扩展。
show variables like 'max_allowed_packet'; vim /etc/my.cnf [mysqld] max_allowed_packet = 1024m set global max_allowed_packet=10000000; //sql语句修改全局级 or set session max_allowed_packet=10000000; //sql语句修改会话级
方法2控制sql的长度
$batchitems = [];//入库记录条数数组
foreach ($costitems as $bitem) {
$bitem['cost_id'] = $costid;
$batchitems[] = $bitem;
if (count($batchitems) >=5000) {
$this->addbatch($this->tablecostitem, $batchitems);
$batchitems = [];
}
}
if ($batchitems) { //小于5000的记录
$this->addbatch($this->tablecostitem, $batchitems);
}4、带主键批量遍历更新，走主键索引会很快
$pagesize = 300000;
$page = 1;
$sql = "select max(id) from win_call_cdr_{$formatmonth} limit 1";
$maxid = $conn->fetchcolumn($sql);
do {
$start = ($page - 1) * $pagesize;
$end = $page * $pagesize;
$sql = "update win_call_cdr_{$formatmonth} set handle_status=0 where id>$start and id<=$end;";
$output->writeln($sql);
$page++;
$conn->exec($sql);
} while ($end < $maxid);5、创建临时表，先更新临时表，然后从临时表中联表update
create temporary table tmp(id int(4) primary key,dr varchar(50)); insert into tmp values (0,'gone'), (1,'xx'),...(m,'yy'); update test_tbl, tmp set test_tbl.dr=tmp.dr where test_tbl.id=tmp.id;
注意：这种方法需要用户有temporary 表的create 权限。临时表在建立连接时可见，关闭时表结构和表数据都没了；临时表可以通过show create table table_name查看。一般在数据量比较大的查询中，用in（）等查询条件，会严重影响查询效率。这时可以用 create temporary table table_name select id,name from table 创建临时表
6、insert into table select * from table_b
批量插入优化
1.这样的单条单条的insert语句改造成
insert into tablename(f1,f2,...) values (d1,d2,...),(d1,d2,...),(d1,d2,...);
这种一次insert多条记录，性能会提升比较明显，拼接出来的sql语句就成了
2.将数据放到文件中，千万级别以上轻松导出
show variables like '%secure%'; #不限制导出目录 [mysqld] #secure_file_priv = #开启mysql用户file权限 update mysql.user set file_priv='y' where user='user_file'; #兼容中文 null导出\n解决ifnull(name,'') select * into outfile '/var/lib/mysql-files/app.csv' character set gbk fields terminated by ',' lines terminated by '\r\n' from dn_location; load data infile '/var/lib/mysql-files/app.csv' into table dn_location2 character set gbk fields terminated by ',' lines terminated by '\r\n'; #带表头导出csv,方便用excel查看 select * into outfile 'd:\\tmp\\columns.csv' fields terminated by ',' lines terminated by '\n' from (select 'name','age' union select name,age from test) b;
通过该方法导出的数据，是将各字段（只有数据，不导出表结构）数据存在一个文件中，中间以逗号分隔，因为文件中并不包含数据库名或者表名，因此需要在导入导出的时候些明确。
该方法在18分钟内导出1.6亿条记录，46min内导入6472w条记录，平均速度：8442w条/h。mysql官方文档也说明了，该方法比一次性插入一条数据性能快20倍。
如果没有outfile权限，使用mysql -e结合linux sed命令重定向导出csv
#!/bin/bash
#shell脚本操作mysql数据库，使用mysql的-e参数可以执行各种sql的(创建，删除，增，删，改、查)等各种操作 export_sql.sh
hostname="192.168.111.84" #数据库信息
port="3306"
username="root"
password=""
dbname="test_db_test" #数据库名称
tablename="test_table_test" #数据库中表的名称
select_sql="select * from ${tablename}"
mysql -h${hostname} -p${port} -u${username} -p${password} ${dbname} -e"${select_sql}" |sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g"
#下面是php版本实现
<?php
$sql = "select * from test";
$cmdarr = array(
'd:\\xampp\\mysql\\bin\\mysql -uroot -pa12345 -e '.$sql,
);
$tmparr = array();
foreach ($cmdarr as $cmd) {
exec($cmd, $tmparr, $return);
var_export($tmparr);
echo $return;
}3.使用insert delayed into，是客户端提交数据给mysql，mysql返回ok状态给客户端。而这是并不是已经将数据插入表，而是存储在内存里面等待排队。当mysql有空余时，再插入。这样的好处是，提高插入的速度，客户端不需要等待太长时间。坏处是，不能返回自动递增的id，以及系统崩溃时，mysql还没有来得及插入数据的话，这些数据将会丢失
4.预处理语句会在服务器上缓存查询的语法和执行过程，而只在服务器和客户端之间传输有变化的列值，以此来消除这些额外的开销。而实际开发中我们最常用的是预处理语句，简单的说预处理语句预先将sql命令分析一次，可以多次执行，提高了处理效率，而且能有效防止sql注入。在执行单个查询时快于直接使用query()或exec()的方法，速度快且安全，所以强烈推荐使用预处理语句。
<?php
$db_host = '127.0.0.1';
$db_name = 'student';
$db_user = 'root';
$db_pwd = '';
$dsn = "mysql:host=$db_host;dbname=$db_name";
$pdo = new pdo($dsn,$db_user,$db_pwd);
$pdo->query('set names utf8');
$query = "insert into tb_chengji set xuesheng=:xuesheng,yuwen=:yuwen";
$result = $pdo->prepare($query);
$result->execute(array(':xuesheng'=>'赵天平',':yuwen'=>90));
$result->execute(array(':xuesheng'=>'张冬雪',':yuwen'=>115));
?>5.使用写锁或事务，可以减少io,加快速度
6.对于myisam类型的表，这两个命令用来打开或者关闭myisam表非唯一索引的更新。在导入大量的数据到一个非空的myisam表时，通过设置这两个命令，可以提高导入的效率。
#关闭： alter table `test` disable keys ; #开启： alter table `test` enable keys; show variables like '%unique_checks%'; show variables like '%autocommit%';
而对于innodb类型的表，这种方式并不能提高导入数据的效率。对于innodb类型的表，我们有以下几种方式可以提高导入的效率：
- 因为innodb类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。如果innodb表没有主键，那么系统会默认创建一个内部列作为主键，所以如果可以给表创建一个主键，将可以利用这个优势提高 导入数据的效率。
- 在导入数据前执行
set unique_checks=0，关闭唯一性校验，在导入结束后执行set unique_checks=1，恢复唯一性校验，可以提高导入的效率。 - 如果应用使用自动提交的方式，建议在导入前执行
set autocommit=0，关闭自动提交，导入结束后再执行set autocommit=1，打开自动提交，也可以提高导入的效率。
以上几点可以结合起来一起使用
删除表大量数据
假设有一个表(syslogs)有1000万条记录，需要在业务不停止的情况下删除其中statusid=1的所有记录，差不多有600万条，直接执行
delete from syslogs where statusid=1
会发现删除失败，因为lock wait timeout exceed的错误。因为这条语句所涉及的记录数太多，因此我们通过limit参数分批删除，比如每10000条进行一次删除，那么我们可以利用 mysql这样的语句来完成
do {
$sql = "delete from syslogs where status=1 limit 100000;";
$bs = $this->conn->exec($sql);
} while ($bs);然后分多次执行就可以把这些记录成功删除。
delete命令根本不会回收空间，也就是说之前假如这个文件占了100g ,delete后，文件大小没有改变。当全表扫描的时候，还是扫这么多的数据块。当执行完alter table 命令后，它会回收空间。假如删了80g,它的物理文件会只占20g空间。
alter table table_name engine=innodb;
当您的库中删除了大量的数据后，您可能会发现数据文件尺寸并没有减小。这是因为删除操作后在数据文件中留下碎片所致。optimize table 是指对表进行优化。
如果已经删除了表的一大部分数据，或者如果已经对含有可变长度行的表（含有 varchar 、 blob 或 text 列的表）进行了很多更改，就应该使用 optimize table 命令来进行表优化。
这个命令可以将表中的空间碎片进行合并，并且可以消除由于删除或者更新造成的空间浪费 。
optimize table 命令只对 myisam 、 bdb 和 innodb 表起作用 。
表优化的工作可以每周或者每月定期执行，对提高表的访问效率有一定的好处，但是需要注意的是，优化表期间会锁定表，所以一定要安排在空闲时段进行。
原始数据
mysql> select count(*) as total from ad_visit_history; +---------+ | total | +---------+ | 1187096 | //总共有118万多条数据 +---------+ 1 row in set (0.04 sec)
存放在硬盘中的表文件大小
[root@blackghost test1]# ls |grep visit |xargs -i du {}
382020 ad_visit_history.myd //数据文件占了380m
127116 ad_visit_history.myi //索引文件占了127m
12 ad_visit_history.frm //结构文件占了12k查看一下索引信息
mysql> show index from ad_visit_history from test1; //查看一下该表的索引信息 +------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+ | table | non_unique | key_name | seq_in_index | column_name | collation | cardinality | sub_part | packed | null | index_type | comment | +------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+ | ad_visit_history | 0 | primary | 1 | id | a | 1187096 | null | null | | btree | | | ad_visit_history | 1 | ad_code | 1 | ad_code | a | 46 | null | null | yes | btree | | | ad_visit_history | 1 | unique_id | 1 | unique_id | a | 1187096 | null | null | yes | btree | | | ad_visit_history | 1 | ad_code_ind | 1 | ad_code | a | 46 | null | null | yes | btree | | | ad_visit_history | 1 | from_page_url_ind | 1 | from_page_url | a | 30438 | null | null | yes | btree | | | ad_visit_history | 1 | ip_ind | 1 | ip | a | 593548 | null | null | yes | btree | | | ad_visit_history | 1 | port_ind | 1 | port | a | 65949 | null | null | yes | btree | | | ad_visit_history | 1 | session_id_ind | 1 | session_id | a | 1187096 | null | null | yes | btree | | +------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+ 8 rows in set (0.28 sec)
索引信息中的列的信息说明。
- table :表的名称。
- non_unique :如果索引不能包括重复词，则为0。如果可以，则为1。
- key_name :索引的名称。
- seq_in_index :索引中的列序列号，从1开始。
- column_name :列名称。
- collation :列以什么方式存储在索引中。在mysqlshow index语法中，有值’a’（升序）或null（无分类）。
- cardinality :索引中唯一值的数目的估计值。通过运行analyze table或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，mysql使用该索引的机会就越大。
- sub_part :如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为null。
- packed :指示关键字如何被压缩。如果没有被压缩，则为null。
- null :如果列含有null，则含有yes。如果没有，则为空。
- index_type ：存储索引数据结构方法（btree, fulltext, hash, rtree）
删除一半数据
mysql> delete from ad_visit_history where id>598000; //删除一半数据
query ok, 589096 rows affected (4 min 28.06 sec)
[root@blackghost test1]# ls |grep visit |xargs -i du {} //相对应的myd，myi文件大小没有变化
382020 ad_visit_history.myd
127116 ad_visit_history.myi
12 ad_visit_history.frm按常规思想来说，如果在数据库中删除了一半数据后，相对应的.myd,.myi文件也应当变为之前的一半。但是删除一半数据后，.myd.myi尽然连1kb都没有减少 ，这是多么的可怕啊。
我们在来看一看，索引信息
mysql> show index from ad_visit_history; +------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+ | table | non_unique | key_name | seq_in_index | column_name | collation | cardinality | sub_part | packed | null | index_type | comment | +------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+ | ad_visit_history | 0 | primary | 1 | id | a | 598000 | null | null | | btree | | | ad_visit_history | 1 | ad_code | 1 | ad_code | a | 23 | null | null | yes | btree | | | ad_visit_history | 1 | unique_id | 1 | unique_id | a | 598000 | null | null | yes | btree | | | ad_visit_history | 1 | ad_code_ind | 1 | ad_code | a | 23 | null | null | yes | btree | | | ad_visit_history | 1 | from_page_url_ind | 1 | from_page_url | a | 15333 | null | null | yes | btree | | | ad_visit_history | 1 | ip_ind | 1 | ip | a | 299000 | null | null | yes | btree | | | ad_visit_history | 1 | port_ind | 1 | port | a | 33222 | null | null | yes | btree | | | ad_visit_history | 1 | session_id_ind | 1 | session_id | a | 598000 | null | null | yes | btree | | +------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+ 8 rows in set (0.00 sec)
对比一下，这次索引查询和上次索引查询，里面的数据信息基本上是上次一次的一本，这点还是合乎常理。
用optimize table来优化一下
mysql> optimize table ad_visit_history; //删除数据后的优化 +------------------------+----------+----------+----------+ | table | op | msg_type | msg_text | +------------------------+----------+----------+----------+ | test1.ad_visit_history | optimize | status | ok | +------------------------+----------+----------+----------+ 1 row in set (1 min 21.05 sec)
查看一下.myd,.myi文件的大小
[root@blackghost test1]# ls |grep visit |xargs -i du {}
182080 ad_visit_history.myd //数据文件差不多为优化前的一半
66024 ad_visit_history.myi //索引文件也一样，差不多是优化前的一半
12 ad_visit_history.frm查看一下索引信息
mysql> show index from ad_visit_history; +------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+ | table | non_unique | key_name | seq_in_index | column_name | collation | cardinality | sub_part | packed | null | index_type | comment | +------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+ | ad_visit_history | 0 | primary | 1 | id | a | 598000 | null | null | | btree | | | ad_visit_history | 1 | ad_code | 1 | ad_code | a | 42 | null | null | yes | btree | | | ad_visit_history | 1 | unique_id | 1 | unique_id | a | 598000 | null | null | yes | btree | | | ad_visit_history | 1 | ad_code_ind | 1 | ad_code | a | 42 | null | null | yes | btree | | | ad_visit_history | 1 | from_page_url_ind | 1 | from_page_url | a | 24916 | null | null | yes | btree | | | ad_visit_history | 1 | ip_ind | 1 | ip | a | 598000 | null | null | yes | btree | | | ad_visit_history | 1 | port_ind | 1 | port | a | 59800 | null | null | yes | btree | | | ad_visit_history | 1 | session_id_ind | 1 | session_id | a | 598000 | null | null | yes | btree | | +------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+ 8 rows in set (0.00 sec)
从以上数据我们可以得出，ad_code，ad_code_ind，from_page_url_ind等索引机会差不多都提高了85％，这样效率提高了好多。
小结
结合mysql官方网站的信息，个人是这样理解的。当你删除数据 时，mysql并不会回收，被已删除数据的占据的存储空间，以及索引位。而是空在那里，而是等待新的数据来弥补这个空缺，这样就有一个缺少，如果一时半 会，没有数据来填补这个空缺，那这样就太浪费资源了。所以对于写比较频烦的表，要定期进行optimize，一个月一次，看实际情况而定了。
举个例子来说吧。有100个php程序员辞职了，但是呢只是人走了，php的职位还在那里，这些职位不会撤销，要等新的php程序来填补这些空位。招一个好的程序员，比较难。我想大部分时间会空在那里。哈哈。
手册中关于optimize的一些用法和描述
optimize [local | no_write_to_binlog] table tbl_name [, tbl_name] ...
如果您已经删除了表的一大部分，或者如果您已经对含有可变长度行的表（含有varchar, blob或text列的表）进行了很多更改，则应使用optimize table。
被删除的记录被保持在链接清单中，后续的insert操作会重新使用旧的记录位置。您可以使用optimize table来重新利用未使用的空间，并整理数据文件的碎片。
在多数的设置中，您根本不需要运行optimize table。即使您对可变长度的行进行了大量的更新，您也不需要经常运行，每周一次或每月一次即可，只对特定的表运行。
optimize table只对myisam, bdb和innodb表起作用。注意，在optimize table运行过程中，mysql会锁定表。
mysql 重置主键
开发时总是要向数据库写入测试数据，删掉以后id（自增主键）依然还是在增长
alter table table_name auto_increment=1;
执行之后，不一定再插入的id就是1；而是表中id最大值+1。
总结
以上为个人经验，希望能给大家一个参考，也希望大家多多支持代码网。
发表评论