建立组合索引mp_id,send_time ，如果 mp_id 大一点效率就不行了。看下面的对比
idx_mp_id_send_time的执行计划：
{
“EXPLAIN ANALYZE\r\nSELECT\r\n mn.id,\r\n mn.title,\r\n IF(\r\n mn.template_url IS NOT NULL,\r\n mn.template_url,\r\n mn.screen_url\r\n ) AS cover,\r\n mn.send_time,\r\n mn.create_time,\r\n mn.media_id,\r\n mn.content,\r\n mn.news_type,\r\n mn.like_num,\r\n mn.collect_num,\r\n mn.read_num,\r\n mn.comment_num,\r\n mn.mp_id\r\nFROM\r\n mp_news mn force INDEX (idx_mp_id_send_time)\r\nWHERE\r\n mn.is_delete = 0\r\n AND mn.is_push = 1\r\n AND mn.mp_id IN (\r\n 1815578536521687042,\r\n1815582786249474049,\r\n1815648372239687682,\r\n1815662856870092802,\r\n1815669108643528705,\r\n1815952623272525825,\r\n1815954241409843201,\r\n1815961401420197890,\r\n1815961989092519937\r\n )\r\nORDER BY\r\n mn.send_time DESC,\r\n mn.id DESC\r\nLIMIT\r\n 10”: [
{
“id” : “Projection_7”,
“estRows” : “10.00”,
“actRows” : “10”,
“task” : “root”,
“access object” : “”,
“execution info” : “time:338.8ms, loops:2, RU:4186.480730, Concurrency:OFF”,
“operator info” : “liu_test.mp_news.id, liu_test.mp_news.title, if(not(isnull(liu_test.mp_news.template_url)), liu_test.mp_news.template_url, liu_test.mp_news.screen_url)->Column#27, liu_test.mp_news.send_time, liu_test.mp_news.create_time, liu_test.mp_news.media_id, liu_test.mp_news.content, liu_test.mp_news.news_type, liu_test.mp_news.like_num, liu_test.mp_news.collect_num, liu_test.mp_news.read_num, liu_test.mp_news.comment_num, liu_test.mp_news.mp_id”,
“memory” : “13.9 KB”,
“disk” : “N/A”
},
{
“id” : “└─TopN_9”,
“estRows” : “10.00”,
“actRows” : “10”,
“task” : “root”,
“access object” : “”,
“execution info” : “time:338.8ms, loops:2”,
“operator info” : “liu_test.mp_news.send_time:desc, liu_test.mp_news.id:desc, offset:0, count:10”,
“memory” : “28.5 KB”,
“disk” : “N/A”
},
{
“id” : " └─IndexLookUp_17",
“estRows” : “10.00”,
“actRows” : “500”,
“task” : “root”,
“access object” : “”,
“execution info” : “time:338.6ms, loops:3, index_task: {total_time: 211.2ms, fetch_handle: 20ms, build: 27.5µs, wait: 191.1ms}, table_task: {total_time: 2.35s, num: 24, concurrency: 8}, next: {wait_index: 1.22ms, wait_table_lookup_build: 85.6µs, wait_table_lookup_resp: 336.6ms}”,
“operator info” : “”,
“memory” : “23.9 MB”,
“disk” : “N/A”
},
{
“id” : " ├─IndexRangeScan_13(Build)“,
“estRows” : “279919.00”,
“actRows” : “280022”,
“task” : “cop[tikv]”,
“access object” : “table:mn, index:idx_mp_id_send_time(mp_id, send_time)”,
“execution info” : “time:5.58ms, loops:284, cop_task: {num: 15, max: 1.45ms, min: 265.3µs, avg: 667.4µs, p95: 1.45ms, max_proc_keys: 2016, p95_proc_keys: 2016, tot_proc: 2.46ms, tot_wait: 1.34ms, copr_cache_hit_ratio: 0.67, build_task_duration: 32µs, max_distsql_concurrency: 2}, rpc_info:{Cop:{num_rpc:15, total_time:9.78ms}}, tikv_task:{proc max:40ms, min:0s, avg: 9.4ms, p80:22ms, p95:40ms, iters:330, tasks:15}, scan_detail: {total_process_keys: 3712, total_process_keys_size: 204160, total_keys: 3717, get_snapshot_time: 973.4µs, rocksdb: {key_skipped_count: 3712, block: {cache_hit_count: 20}}}, time_detail: {total_process_time: 2.46ms, total_suspend_time: 10.5µs, total_wait_time: 1.34ms, total_kv_read_wall_time: 1ms, tikv_wall_time: 5.17ms}”,
“operator info” : “range:[1815578536521687042,1815578536521687042], [1815582786249474049,1815582786249474049], [1815648372239687682,1815648372239687682], [1815662856870092802,1815662856870092802], [1815669108643528705,1815669108643528705], [1815952623272525825,1815952623272525825], [1815954241409843201,1815954241409843201], [1815961401420197890,1815961401420197890], [1815961989092519937,1815961989092519937], keep order:false”,
“memory” : “N/A”,
“disk” : “N/A”
},
{
“id” : " └─TopN_16(Probe)”,
“estRows” : “10.00”,
“actRows” : “500”,
“task” : “cop[tikv]”,
“access object” : “”,
“execution info” : “time:2.26s, loops:36, cop_task: {num: 71, max: 212.7ms, min: 0s, avg: 57.8ms, p95: 178.4ms, max_proc_keys: 13890, p95_proc_keys: 12129, tot_proc: 3.42s, tot_wait: 9.9ms, copr_cache_hit_ratio: 0.14, build_task_duration: 6.63ms, max_distsql_concurrency: 11, max_extra_concurrency: 1, store_batch_num: 1}, rpc_info:{Cop:{num_rpc:70, total_time:4.1s}}, tikv_task:{proc max:201ms, min:0s, avg: 55.6ms, p80:116ms, p95:168ms, iters:314, tasks:71}, scan_detail: {total_process_keys: 274575, total_process_keys_size: 196659322, total_keys: 306309, get_snapshot_time: 7.01ms, rocksdb: {key_skipped_count: 182588, block: {cache_hit_count: 774082}}}, time_detail: {total_process_time: 3.42s, total_suspend_time: 426.9ms, total_wait_time: 9.9ms, total_kv_read_wall_time: 3.78s, tikv_wall_time: 4s}”,
“operator info” : “liu_test.mp_news.send_time:desc, liu_test.mp_news.id:desc, offset:0, count:10”,
“memory” : “N/A”,
“disk” : “N/A”
},
{
“id” : " └─Selection_15",
“estRows” : “226405.65”,
“actRows” : “223995”,
“task” : “cop[tikv]”,
“access object” : “”,
“execution info” : “tikv_task:{proc max:197ms, min:0s, avg: 54.7ms, p80:111ms, p95:166ms, iters:314, tasks:71}”,
“operator info” : “eq(liu_test.mp_news.is_delete, 0), eq(liu_test.mp_news.is_push, 1)”,
“memory” : “N/A”,
“disk” : “N/A”
},
{
“id” : " └─TableRowIDScan_14",
“estRows” : “279919.00”,
“actRows” : “280022”,
“task” : “cop[tikv]”,
“access object” : “table:mn”,
“execution info” : “tikv_task:{proc max:196ms, min:0s, avg: 54.5ms, p80:111ms, p95:166ms, iters:314, tasks:71}”,
“operator info” : “keep order:false”,
“memory” : “N/A”,
“disk” : “N/A”
}
]}
走idx_send_time的执行计划：
{
“EXPLAIN ANALYZE\r\nSELECT\r\n mn.id,\r\n mn.title,\r\n IF(\r\n mn.template_url IS NOT NULL,\r\n mn.template_url,\r\n mn.screen_url\r\n ) AS cover,\r\n mn.send_time,\r\n mn.create_time,\r\n mn.media_id,\r\n mn.content,\r\n mn.news_type,\r\n mn.like_num,\r\n mn.collect_num,\r\n mn.read_num,\r\n mn.comment_num,\r\n mn.mp_id\r\nFROM\r\n mp_news mn \r\nWHERE\r\n mn.is_delete = 0\r\n AND mn.is_push = 1\r\n AND mn.mp_id IN (\r\n 1815578536521687042,\r\n1815582786249474049,\r\n1815648372239687682,\r\n1815662856870092802,\r\n1815669108643528705,\r\n1815952623272525825,\r\n1815954241409843201,\r\n1815961401420197890,\r\n1815961989092519937\r\n )\r\nORDER BY\r\n mn.send_time DESC,\r\n mn.id DESC\r\nLIMIT\r\n 10”: [
{
“id” : “Projection_7”,
“estRows” : “10.00”,
“actRows” : “10”,
“task” : “root”,
“access object” : “”,
“execution info” : “time:2.87ms, loops:2, RU:5.814234, Concurrency:OFF”,
“operator info” : “liu_test.mp_news.id, liu_test.mp_news.title, if(not(isnull(liu_test.mp_news.template_url)), liu_test.mp_news.template_url, liu_test.mp_news.screen_url)->Column#27, liu_test.mp_news.send_time, liu_test.mp_news.create_time, liu_test.mp_news.media_id, liu_test.mp_news.content, liu_test.mp_news.news_type, liu_test.mp_news.like_num, liu_test.mp_news.collect_num, liu_test.mp_news.read_num, liu_test.mp_news.comment_num, liu_test.mp_news.mp_id”,
“memory” : “13.9 KB”,
“disk” : “N/A”
},
{
“id” : “└─Limit_12”,
“estRows” : “10.00”,
“actRows” : “10”,
“task” : “root”,
“access object” : “”,
“execution info” : “time:2.83ms, loops:2”,
“operator info” : “offset:0, count:10”,
“memory” : “N/A”,
“disk” : “N/A”
},
{
“id” : " └─IndexLookUp_28",
“estRows” : “10.00”,
“actRows” : “10”,
“task” : “root”,
“access object” : “”,
“execution info” : “time:2.83ms, loops:1, index_task: {total_time: 2.04ms, fetch_handle: 1.93ms, build: 108.6µs, wait: 8.9µs}, table_task: {total_time: 6.42ms, num: 5, concurrency: 8}, next: {wait_index: 1.32ms, wait_table_lookup_build: 34.1µs, wait_table_lookup_resp: 1.43ms}”,
“operator info” : “”,
“memory” : “129.7 KB”,
“disk” : “N/A”
},
{
“id” : " ├─IndexFullScan_25(Build)“,
“estRows” : “20.05”,
“actRows” : “704”,
“task” : “cop[tikv]”,
“access object” : “table:mn, index:idx_send_time(send_time)”,
“execution info” : “time:2.62ms, loops:7, cop_task: {num: 3, max: 1.09ms, min: 748.5µs, avg: 942.3µs, p95: 1.09ms, max_proc_keys: 480, p95_proc_keys: 480, tot_proc: 714.5µs, tot_wait: 911.2µs, copr_cache_hit_ratio: 0.00, build_task_duration: 32.3µs, max_distsql_concurrency: 2}, rpc_info:{Cop:{num_rpc:3, total_time:2.77ms}}, tikv_task:{proc max:1ms, min:0s, avg: 333.3µs, p80:1ms, p95:1ms, iters:8, tasks:3}, scan_detail: {total_process_keys: 704, total_process_keys_size: 32384, total_keys: 707, get_snapshot_time: 831.3µs, rocksdb: {key_skipped_count: 706, block: {cache_hit_count: 16}}}, time_detail: {total_process_time: 714.5µs, total_wait_time: 911.2µs, total_kv_read_wall_time: 1ms, tikv_wall_time: 2.05ms}”,
“operator info” : “keep order:true, desc”,
“memory” : “N/A”,
“disk” : “N/A”
},
{
“id” : " └─Selection_27(Probe)”,
“estRows” : “10.00”,
“actRows” : “21”,
“task” : “cop[tikv]”,
“access object” : “”,
“execution info” : “time:5.6ms, loops:6, cop_task: {num: 1, max: 1.28ms, proc_keys: 32, tot_proc: 518.4µs, tot_wait: 266.6µs, copr_cache_hit_ratio: 0.00, build_task_duration: 33.2µs, max_distsql_concurrency: 1, max_extra_concurrency: 1}, rpc_info:{Cop:{num_rpc:1, total_time:1.26ms}}, tikv_task:{time:0s, loops:2}, scan_detail: {total_process_keys: 32, total_process_keys_size: 41559, total_keys: 34, get_snapshot_time: 244.5µs, rocksdb: {key_skipped_count: 22, block: {cache_hit_count: 75}}}, time_detail: {total_process_time: 518.4µs, total_wait_time: 266.6µs, tikv_wall_time: 978.3µs}”,
“operator info” : “eq(liu_test.mp_news.is_delete, 0), eq(liu_test.mp_news.is_push, 1), in(liu_test.mp_news.mp_id, 1815578536521687042, 1815582786249474049, 1815648372239687682, 1815662856870092802, 1815669108643528705, 1815952623272525825, 1815954241409843201, 1815961401420197890, 1815961989092519937)”,
“memory” : “N/A”,
“disk” : “N/A”
},
{
“id” : " └─TableRowIDScan_26",
“estRows” : “20.05”,
“actRows” : “32”,
“task” : “cop[tikv]”,
“access object” : “table:mn”,
“execution info” : “tikv_task:{time:0s, loops:2}”,
“operator info” : “keep order:false”,
“memory” : “N/A”,
“disk” : “N/A”
}
]}
