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 |

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