mysql 查询优化杂谈

一、把某些判断移动到应用层

我们需要在一张表里面删除某种类型的数据，大概的表结构类似这样:

CREATE TABLE t (
    id INT,
    tp ENUM ("t1", "t2"),
    PRIMARY KEY(id)
) ENGINE=INNODB;

 

假设我们需要删除类型为t2的数据，语句可能是这样delete from t where tp = "t2"，这样没啥问题，但我们这张表有5亿数据

delete from t where tp = "t2" limit 1000，

使用limit来限制一次删除的个数，，不过这有个很严重的问题，就是越往后，随着t2类型的减少，我们几乎都是全表遍历来删除，所以总的应该是O(n*n)的开销。

于是我让他考虑主键，每次操作的时候，记录当前最大的主键，这样下次就可以从这个主键之后开始删除了，首先 

select id from t where id > last_max_select_id and tp = "t2" limit 1000；

delete from t where id in (ids)；

虽然我们使用了主键，但是MySQL仍然需要不停的读取数据判断条件，加之t2类型的数据在表里面比较少量，所以为了limit 1000这个条件，MySQL需要持续的进行IO读取操作，结果自然是太慢了。

想清楚了这个，其实就好优化了，我们只需要让条件判断在应用层做，MySQL只查询数据返回，语句就是 

select id, tp from t where id > last_max_select_id limit 1000；

得到结果集之后，自行判断需要删除的id，然后delete。

看似我们需要额外处理逻辑，并且网络开销也增大了，但MySQL只是简单的IO读取，性能改善明显。

 

二、three start index

索引设计的原则

1. 查询谓词都能够通过index进行扫描

2. 排序谓词都能够利用index的有序性

3. index包含了查询所需要的所有字段