MySql中几种批量更新/新增/删除的方法

通常情况下，我们会使用以下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                              //结构文件占了12K

 3，查看一下索引信息

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。