按天分类统计的优化

统计一月中，每天的数量的sql优化

原始版本，按照查询当月每天的统计量

select DATE_FORMAT(alarm_time, '%d') as `key`, COUNT(*) as `value`
        from `alarm`
        WHERE is_deleted = 0
          and DATE_FORMAT(alarm_time
                  , '%Y%m') = DATE_FORMAT(CURDATE()
                  , '%Y%m')
        GROUP BY `key````

性能：一坨屎
优化：在数据表中添加年、月、日的列，在这三列上添加索引

ALTER table alarm add INDEX idx_ymd (`year`,`month`,`day`)

explain 分析

EXPLAIN select day as `key`, COUNT(*) as `value`
        from `alarm`
        WHERE is_deleted = 0
          and `year` = 2022 and `month` = 3
        GROUP BY `key`

有了明显改进，牺牲空间换了效率