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 |

+——+————-+—————–+——-+————————-+————————-+———+——+——-+—————————————+