SQL优化 - SQL基础 - 《MySQL》

limit语句
隐式转换导致索引失效
关联更新

limit语句

SELECT *
FROM   operation
WHERE  type = 'SQLStats'
       AND name = 'SlowLog'
ORDER  BY create_time
LIMIT  1000, 10;

一般 DBA 想到的办法是在 type, name, create_time 字段上加组合索引。这样条件排序都能有效的利用到索引，性能迅速提升。
但当 LIMIT 子句变成 “LIMIT 1000000,10” 时，程序员仍然会抱怨：我只取10条记录为什么还是慢？，因为还是会从头扫描，扫描啦大量数据
在前端数据浏览翻页，或者大数据分批导出等场景下，是可以将上一页的最大值当成参数作为查询条件的。SQL 重新设计如下：
```
SELECT   *
FROM     operation
WHERE    type = 'SQLStats'
AND      name = 'SlowLog'
AND      create_time > '2017-03-16 14:00:00'
ORDER BY create_time limit 10;
```

在新设计下查询时间基本固定，不会随着数据量的增长而发生变化。

隐式转换导致索引失效

mysql> explain extended SELECT *
   > FROM   my_balance b
   > WHERE  b.bpn = 14000000123
   >       AND b.isverified IS NULL ;
mysql> show warnings;
| Warning | 1739 | Cannot use ref access on index 'bpn' due to type or collation conversion on field 'bpn'

其中字段 bpn 的定义为 varchar(20)，MySQL 的策略是将字符串转换为数字之后再比较。函数作用于表字段，索引失效

关联更新

UPDATE operation o
     JOIN  (SELECT o.id,
                          o.status
                   FROM   operation o
                   WHERE  o.group = 123
                          AND o.status NOT IN ( 'done' )
                   ORDER  BY o.parent,
                             o.id
                   LIMIT  1) t
       ON o.id = t.id
SET    status = 'applying'