关于MySQL优化的那些事

关于批量更新( update )

  首先批量插入很简单

INSERT INTO `Table`(a, b, c) VALUES
(aa, bb, cc), (xx, yy, zz), ...;

  有两种简单的方式实现批量update

1. replace into
   使用方法和 INSERT INTO 一样，其运行过程为：如果表中的旧记录的 PRIMARY KEY或 UNIQUE索引与新纪录的值相同，则删除旧记录，插入新纪录。（如果Table没有PRIMARY KEY或 UNIQUE索引...效果就与INSERT INTO无差）
   

   replace into `Table`(id, bb) values(1, 'ssss'),(2, 'mmm');

   
   
2. on duplicate key update
   用法：insert ... on duplicate key update ... ；
   该语法在进行 INSERT 操作时如果 INSERT的数据会引起PRIMARY KEY或 UNIQUE索引 的冲突，就会执行后边 UPDATE 的操作
   

   -- 假设 a 为主键
   INSERT INTO `Table`(a, b, c) VALUES(aa, bb, cc), (xx, yy, zz)
   on duplicate key update 
   b=values(b), c=values(c);

   
   

关于大表（百万千万级）的分页查询

最初写法

SELECT id FROM `user` LIMIT 100, 1 \G;
SELECT id FROM `user` LIMIT 100 OFFSET 1 \G;

页数小的时候还没有什么感觉，offset后过万后明显感觉慢到抓狂...甚至当到百万的时候需要耗时几十秒（我的机器的原因，用了 1min 26sec）

explain select id, email from users limit 100 offset 500;
+----------------------++------+------+-------------+
| key                  || ref  | rows | Extra       |
+----------------------++------+------+-------------+
| index_users_on_email || NULL | 1005 | Using index |
+----------------------++------+------+-------------+

explain select id, email from users order by id  limit 100 offset 500;
+---------++------+------+-------+
| key     || ref  | rows | Extra |
+---------++------+------+-------+
| PRIMARY || NULL |  600 |       |
+---------++------+------+-------+

explain select id  from users order by id limit 100 offset 500;
+---------++------+------+-------------+
| key     || ref  | rows | Extra       |
+---------++------+------+-------------+
| PRIMARY || NULL |  600 | Using index |
+---------++------+------+-------------+<span style="color:#666666;">
</span>

一目了然，这偏移量一大谁受得了...

查到一种优化方法

SELECT id FROM `users` WHERE id >= (SELECT id FROM `users` LIMIT 9000000, 1) LIMIT 100 \G;

速度提升的一大截，explain后仍觉不太满意
因我的处理过程是从原始大表中把数据经过转换后插入新表，计划分批处理一小块一小块的数据...

苦恼之余，查找发现了一个很不错的方法

SELECT id, FROM `users` WHERE (id< maxnum ) ORDER BY id desc LIMIT 200 \G;

-- 其中 maxnum = 2100000000 是我随便取的一个，表中最大id不会超过的数

每次更新maxnum为查出的结果集中最小的 id...

explain的结果也相当满意

删除大表( DROP )

索引对空间的消耗