SQL优化总结

前言

• 如何优化索引
• 分页优化
• 分库分表优化

工作中常见case

1，子查询优化后问题
有些字段的区分度飘忽飘忽不定，比如status字段。我们这个字段在项目中使用了索引。老版本优化时把主键ID查出来放到子查询中，再用status进行筛选，这样有效果。升级了mysql之后，出现慢查询。这个时候子查询不再适用，应使用ignore index (Status_IDX) 忽略区分度不高的索引。
引申：force index
2，mysql排序问题
如果使用非唯一字段进行order by，那么有可能会出现数据丢失。mysql在对重复数据排序时，选择是随即的，所以你第一页出现的数据，第二页有可能重复出现，导致应该第二页出现的数据丢失。这样的case解决方式是使用主键放在排序因子中。

3，使用gap锁引发的死锁
如果在代码中，先进行删除再进行插入。在并发的情况下，next-key锁会与插入意向锁冲突，形成阻塞，最终造成死锁

分页优化

1，子查询

SELECT * FROM product WHERE ID > =(select id from product limit 866613, 1) limit 20

使用 id 限定优化

select * from orders_history where id >= 1000001 limit 100;

select * from orders_history where id in
(select order_id from trade_2 where goods = ‘pen’)
limit 100;

使用临时表优化

参考：http://uusama.com/458.html