MariaDB中的一次慢日志查询

问题：

应用中查询超时严重，数据库中CPU满载，是如下SQL频繁访问引起的

CPU utilization (%) is 100 %, crossd waring (95) threshold.

原因

MariaDB [sns_metric]> explain SELECT user_id FROM met_active_user WHERE create_time_id / 1000 >= UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL -1 DAY)) ORDER BY create_time_id DESC , record_times limit 25000; +——+————-+—————–+——+—————+——+———+——+———+—————————–+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +——+————-+—————–+——+—————+——+———+——+———+—————————–+ | 1 | SIMPLE | met_active_user | ALL | NULL | NULL | NULL | NULL | 5439839 | Using where; Using filesort | +——+————-+—————–+——+—————+——+———+——+———+—————————–+

MariaDB [sns_metric]> explain SELECT user_id FROM met_active_user WHERE time_id / 1000 >= UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL -1 DAY)) ORDER BY time_id DESC , record_times limit 25000 ; +——+————-+—————–+——+—————+——+———+——+———+—————————–+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +——+————-+—————–+——+—————+——+———+——+———+—————————–+ | 1 | SIMPLE | met_active_user | ALL | NULL | NULL | NULL | NULL | 5439800 | Using where; Using filesort | +——+————-+—————–+——+—————+——+———+——+———+—————————–+

##解决问题 已添加索引 alter table met_active_user add index idx_timeid_record_times(time_id desc,record_times desc);

但是该查询走不了索引，因为where子句中 time_id / 1000 >= UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL -1 DAY)) time_id字段进行了运算，需要前端修改SQL，将1000调整到等号右边，修改后的SQL如下：

MariaDB [sns_metric]> explain SELECT user_id FROM met_active_user WHERE create_time_id >= UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL -1 DAY))*1000 ORDER BY create_time_id DESC , record_times limit 25000; +——+————-+—————–+——-+——————–+——————–+———+——+——+—————————————+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +——+————-+—————–+——-+——————–+——————–+———+——+——+—————————————+ | 1 | SIMPLE | met_active_user | range | idx_create_time_id | idx_create_time_id | 9 | NULL | 4042 | Using index condition; Using filesort | +——+————-+—————–+——-+——————–+——————–+———+——+——+—————————————+

MariaDB [sns_metric]> explain SELECT user_id FROM met_active_user WHERE time_id >= UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL -1 DAY))*1000 ORDER BY time_id DESC , record_times limit 25000 ; +——+————-+—————–+——-+————————-+————————-+———+——+——-+—————————————+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +——+————-+—————–+——-+————————-+————————-+———+——+——-+—————————————+ | 1 | SIMPLE | met_active_user | range | idx_timeid_record_times | idx_timeid_record_times | 8 | NULL | 95972 | Using index condition; Using filesort | +——+————-+—————–+——-+————————-+————————-+———+——+——-+—————————————+