sql优化

https://mp.weixin.qq.com/s/WsQZhZhuzfs2YZgamrGUOw

分批处理

大量数据的update和delete操作可采取分批处理。

业务描述：更新用户所有已过期的优惠券为不可用状态。

SQL语句：

update status=0 FROM `coupon` WHERE expire_date <= #{currentDate} and status=1;

如果大量优惠券需要更新为不可用状态，执行这条SQL可能会堵死其他SQL，分批处理伪代码如下：

int pageNo = 1;
int PAGE_SIZE = 100;
while(true) {
    List batchIdList = queryList('select id FROM `coupon` WHERE expire_date <= #{currentDate} and status = 1 limit #{(pageNo-1) * PAGE_SIZE},#{PAGE_SIZE}');
    if (CollectionUtils.isEmpty(batchIdList)) {
        return;
    }
    update('update status = 0 FROM `coupon` where status = 1 and id in #{batchIdList}')
    pageNo ++;
}

Like优化

like用于模糊查询，举个例子（field已建立索引）：

SELECT column FROM table WHERE field like '%keyword%';

这个查询未命中索引，换成下面的写法：

SELECT column FROM table WHERE field like 'keyword%';

去除了前面的%查询将会命中索引，但是产品经理一定要前后模糊匹配呢？全文索引fulltext可以尝试一下，但Elasticsearch才是终极武器。

Limit优化

limit用于分页查询时越往后翻性能越差，解决的原则：缩小扫描范围，如下所示：

select * from orders order by id desc limit 100000,10

耗时0.4秒

select * from orders order by id desc limit 1000000,10

耗时5.2秒

先筛选出ID缩小查询范围，写法如下：

select * from orders where id > (select id from orders order by id desc limit 1000000, 1) order by id desc limit 0,10

耗时0.5秒

如果查询条件仅有主键ID，写法如下：

select id from orders where id between 1000000 and 1000010 order by id desc

耗时0.3秒

如果以上方案依然很慢呢？只好用游标了，感兴趣的朋友阅读JDBC使用游标实现分页查询的方法。

定位并优化慢查询sql

使用查询日志代码。查看日志的具体情况

slow_query_log

当这个参数设置为ON，可以搜寻到时间超过慢查询定义的时间（即1S）SQL语句，默认是off关闭的，使用时，需要改为on 打开。

long_query_time

默认是10S，执行后会浏览所有SQL语句，当某些SQL语句执行超过设定时间后，系统会自动的将该条语句记录到日志中。　　　　

slow_query_log_file 记录的是慢日志的记录文件

SHOW STATUS LIKE '%slow_queries%' 查看慢查询状态

Slow_queries 记录的是慢查询数量当有一条sql执行一次比较慢时，这个vlue就是1 （记录的是本次会话的慢sql条数）

注意：

　　如何打开慢查询： SET GLOBAL slow_query_log = ON;

　　将默认时间改为1S： SET GLOBAL long_query_time = 1;
　（设置完需要重新连接数据库，PS：仅在这里改的话，当再次重启数据库服务时，所有设置又会自动恢复成默认值，永久改变需去my.ini中改）

https://www.cnblogs.com/xk920/p/11132038.html

https://mp.weixin.qq.com/s/D0wp9DJUvAZpCVU8eIyLdg

。。。