一条 SQL 优化 实例

==今天DBA 找过来 说TiDb 压力太大了 需要优化SQL 代码如下==

SELECT
    *
FROM
  table force index (idx_all)
WHERE
  `from` = 'sinoc'
  AND `to` = 'eth'
  AND uid = 1
  and side = 'buy'
  and status in (2, 3)
  AND `created` > 1561349605
  AND `created` < 1561954405
ORDER BY
  id desc
limit
  0, 100

==索引如下==

PRIMARY KEY (`id`),
KEY `idx_all` (`uid`, `created`, `from`, `to`, `status`)

==表数据量==

Tidb 压力已经高达 80% 优化后 40%

SELECT
    *
FROM
  table force index (idx_all)
WHERE
  `from` = 'sinoc'
  AND `to` = 'eth'
  AND uid = 1
  and side = 'buy'
  and status in (2, 3)
  AND `created` > 1561349605
  AND `created` < 1561954405
ORDER BY
  created desc
limit
  0, 100

仅通过更换排序字段 id 转 created 性能大幅优化

DBA 解释: 因为 where 中已经使用了created 并使用了 created 参与的 idx_all 索引 所以使用 created 做排序减少了 使用主键 id 排序的索引消耗