SQL优化

索引

使用索引优化查询条件及排序字段

ti_f_identity 近两百万数据，原查询需要 3 秒。建立索引后，小于 0.1 秒。

create index i_ti_f_identity_env_reg on ti_f_identity(environment_id, register_at); 

SELECT   *
FROM     ti_f_identity
WHERE    register_state = 3
AND      environment_id = '1722454762444832770'
ORDER BY register_at DESC 
LIMIT 10

SELECT   *
FROM     ti_f_identity
WHERE    1=1
and      register_state = 2
-- and      template_code  = '42cbd45b-f03b-4840-bdb0-965238c4c071'
AND      environment_id = 'ABC123'
ORDER BY register_at DESC 
LIMIT 10