MySql中几种批量更新/新增/删除的方法
通常情况下，我们会使用以下SQL语句来更新字段值：UPDATE mytable SET myfield='value' WHERE other_field='other_value';但是，如果你想更新多行数据，并且每行记录的各字段值都是各不一样，你会怎么办呢？刚开始你可能会想到使用循环执行多条UPDATE语句的方式，就像以下的python程序示例：for x in xrange(...
文章共5,596字 · 阅读需要大约19分钟
通常情况下，我们会使用以下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-26
2. 执行第二次：（与第一次的唯一(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)2，存放在硬盘中的表文件大小
[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 //结构文件占了12K3，查看一下索引信息
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)
1，查看一下.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
2，查看一下索引信息
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。
旨在为数千万中国开发者提供一个无缝且高效的云端环境，以支持学习、使用和贡献开源项目。
更多推荐
11
0- 0
已为社区贡献4条内容
所有评论(0)