|
使用idx_aid_day_pv索引的效果：
- mysql> explain select aid,sum(pv) as num from article_rank force index(idx_aid_day_pv) where day>=20181220 and day<=20181224 group by aid order by null limit 10;
-
- +----+-------------+--------------+------------+-------+-------------------------------+----------------+---------+------+------+----------+--------------------------+
-
- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-
- +----+-------------+--------------+------------+-------+-------------------------------+----------------+---------+------+------+----------+--------------------------+
-
- | 1 | SIMPLE | article_rank | NULL | index | idx_day_aid_pv,idx_aid_day_pv | idx_aid_day_pv | 12 | NULL | 10 | 11.11 | Using where; Using index |
-
- +----+-------------+--------------+------------+-------+-------------------------------+----------------+---------+------+------+----------+--------------------------+
查看 optimizer trace 信息
- # 开启optimizer_trace
- set optimizer_trace='enabled=on';
- # 执行 sql
- select aid,sum(pv) as num from article_rank force index(idx_aid_day_pv) where day>=20181220 and day<=20181224 group by aid order by num desc limit 10;
- # 查看 trace 信息
- select trace from `information_schema`.`optimizer_trace`G;
摘取里面最后的执行结果如下
- {
- "join_execution": {
- "select#": 1,
- "steps": [
- {
- "creating_tmp_table": {
- "tmp_table_info": {
- "table": "intermediate_tmp_table",
- "row_length": 20,
- "key_length": 0,
- "unique_constraint": false,
- "location": "memory (heap)",
- "row_limit_estimate": 838860
- }
- }
- },
- {
- "filesort_information": [
- {
- "direction": "desc",
- "table": "intermediate_tmp_table",
- "field": "num"
- }
- ],
- "filesort_priority_queue_optimization": {
- "limit": 10,
- "rows_estimate": 552213,
- "row_size": 24,
- "memory_available": 262144,
- "chosen": true
- },
- "filesort_execution": [
- ],
- "filesort_summary": {
- "rows": 11,
- "examined_rows": 552203,
- "number_of_tmp_files": 0,
- "sort_buffer_size": 352,
- "sort_mode": "<sort_key, rowid>"
- }
- }
- ]
- }
- }
执行流程如下
1. 创建一张临时表，临时表上有两个字段，aid和num字段（sum(pv) as num）； （编辑：晋中站长网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
