公眾號 MySQL學習

定位模式為PAGE_CUR_L，反向定位,想定位到最後一行然後使用handler::ha_index_prev掃描上一行。
但是5.7的代價比8.0的逆序索引高。

- 0x4e    0x20 為20000

```

mysql> desc  select * from sortdescpart1 where a1=4 and a2>100 and a2<20000 order by a2  desc limit 10 ;
+----+-------------+---------------+------------+-------+---------------+------+---------+------+-------+----------+-----------------------+
| id | select_type | table         | partitions | type  | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                 |
+----+-------------+---------------+------------+-------+---------------+------+---------+------+-------+----------+-----------------------+
|  1 | SIMPLE      | sortdescpart1 | p5         | range | a1            | a1   | 10      | NULL | 15151 |   100.00 | Using index condition |
+----+-------------+---------------+------------+-------+---------------+------+---------+------+-------+----------+-----------------------+
1 row in set, 1 warning (2.92 sec)

*************************** 1. row ***************************
       Table: sortdescpart1
Create Table: CREATE TABLE `sortdescpart1` (
  `id` int(11) DEFAULT NULL,
  `a1` int(11) DEFAULT NULL,
  `a2` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  KEY `a1` (`a1`,`a2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (a1)
(PARTITION p1 VALUES LESS THAN (1) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (3) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (4) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (5) ENGINE = InnoDB,
 PARTITION p6 VALUES LESS THAN (6) ENGINE = InnoDB,
 PARTITION p7 VALUES LESS THAN (7) ENGINE = InnoDB,
 PARTITION p8 VALUES LESS THAN (8) ENGINE = InnoDB,
 PARTITION p9 VALUES LESS THAN (9) ENGINE = InnoDB,
 PARTITION p10 VALUES LESS THAN (10) ENGINE = InnoDB,
 PARTITION p11 VALUES LESS THAN (11) ENGINE = InnoDB) */
```

定位：
```

#0  btr_cur_search_to_nth_level (index=0x7ffebd313ae0, level=0, tuple=0x7ffebd2cd118, mode=PAGE_CUR_L, latch_mode=1, cursor=0x7ffebd2d0a68, has_search_latch=0, 
    file=0x22b7150 "/home/mysql/soft/percona-server-5.7.29-32/storage/innobase/row/row0sel.cc", line=5274, mtr=0x7fffe83ddaf0)
    at /home/mysql/soft/percona-server-5.7.29-32/storage/innobase/btr/btr0cur.cc:798
#1  0x0000000001af45c4 in btr_pcur_open_with_no_init_func (index=0x7ffebd313ae0, tuple=0x7ffebd2cd118, mode=PAGE_CUR_L, latch_mode=1, cursor=0x7ffebd2d0a68, has_search_latch=0, 
    file=0x22b7150 "/home/mysql/soft/percona-server-5.7.29-32/storage/innobase/row/row0sel.cc", line=5274, mtr=0x7fffe83ddaf0)
    at /home/mysql/soft/percona-server-5.7.29-32/storage/innobase/include/btr0pcur.ic:544
#2  0x0000000001afedb9 in row_search_mvcc (buf=0x7ffebd2c9ee0 "\377", mode=PAGE_CUR_L, prebuilt=0x7ffebd2cccf0, match_mode=0, direction=0)
    at /home/mysql/soft/percona-server-5.7.29-32/storage/innobase/row/row0sel.cc:5272
#3  0x0000000001961dfa in ha_innobase::index_read (this=0x7ffebd2c9860, buf=0x7ffebd2c9ee0 "\377", key_ptr=0x7ffebcd312b0 "", key_len=10, find_flag=HA_READ_BEFORE_KEY)
    at /home/mysql/soft/percona-server-5.7.29-32/storage/innobase/handler/ha_innodb.cc:9970
#4  0x0000000000eb8e04 in handler::index_read_map (this=0x7ffebd2c9860, buf=0x7ffebd2c9ee0 "\377", key=0x7ffebcd312b0 "", keypart_map=3, find_flag=HA_READ_BEFORE_KEY)
    at /home/mysql/soft/percona-server-5.7.29-32/sql/handler.h:2990
#5  0x000000000199246c in ha_innopart::index_read_map_in_part (this=0x7ffebd2c9860, part=4, record=0x7ffebd2c9ee0 "\377", key=0x7ffebcd312b0 "", keypart_map=3, 
    find_flag=HA_READ_BEFORE_KEY) at /home/mysql/soft/percona-server-5.7.29-32/storage/innobase/handler/ha_innopart.cc:2221
#6  0x00000000013d38e8 in Partition_helper::handle_ordered_index_scan (this=0x7ffebd2c9d60, buf=0x7ffebd2c9ee0 "\377")
    at /home/mysql/soft/percona-server-5.7.29-32/sql/partitioning/partition_handler.cc:3286
#7  0x00000000013d2360 in Partition_helper::common_index_read (this=0x7ffebd2c9d60, buf=0x7ffebd2c9ee0 "\377", have_start_key=true)
    at /home/mysql/soft/percona-server-5.7.29-32/sql/partitioning/partition_handler.cc:2583
#8  0x00000000013d20bb in Partition_helper::ph_index_read_map (this=0x7ffebd2c9d60, buf=0x7ffebd2c9ee0 "\377", key=0x7ffebcd312b0 "", keypart_map=3, find_flag=HA_READ_BEFORE_KEY)
    at /home/mysql/soft/percona-server-5.7.29-32/sql/partitioning/partition_handler.cc:2497
#9  0x00000000019980db in ha_innopart::index_read_map (this=0x7ffebd2c9860, buf=0x7ffebd2c9ee0 "\377", key=0x7ffebcd312b0 "", keypart_map=3, find_flag=HA_READ_BEFORE_KEY)
    at /home/mysql/soft/percona-server-5.7.29-32/storage/innobase/handler/ha_innopart.h:1317
#10 0x0000000000ea9ead in handler::ha_index_read_map (this=0x7ffebd2c9860, buf=0x7ffebd2c9ee0 "\377", key=0x7ffebcd312b0 "", keypart_map=3, find_flag=HA_READ_BEFORE_KEY)
    at /home/mysql/soft/percona-server-5.7.29-32/sql/handler.cc:3261
#11 0x0000000001725fda in QUICK_SELECT_DESC::get_next (this=0x7ffebc002c90) at /home/mysql/soft/percona-server-5.7.29-32/sql/opt_range.cc:11604
#12 0x00000000013d82ff in rr_quick (info=0x7ffebcd2eac8) at /home/mysql/soft/percona-server-5.7.29-32/sql/records.cc:406
#13 0x00000000014767dc in join_init_read_record (tab=0x7ffebcd2ea78) at /home/mysql/soft/percona-server-5.7.29-32/sql/sql_executor.cc:2507
#14 0x0000000001473949 in sub_select (join=0x7ffebcd2d020, qep_tab=0x7ffebcd2ea78, end_of_records=false) at /home/mysql/soft/percona-server-5.7.29-32/sql/sql_executor.cc:1284
#15 0x00000000014732dc in do_select (join=0x7ffebcd2d020) at /home/mysql/soft/percona-server-5.7.29-32/sql/sql_executor.cc:957
#16 0x0000000001471243 in JOIN::exec (this=0x7ffebcd2d020) at /home/mysql/soft/percona-server-5.7.29-32/sql/sql_executor.cc:206
#17 0x000000000150d2d5 in handle_query (thd=0x7ffebc012a40, lex=0x7ffebc0150f0, result=0x7ffebcd2c8e0, added_options=0, removed_options=0)
    at /home/mysql/soft/percona-server-5.7.29-32/sql/sql_select.cc:192
#18 0x00000000014c1097 in execute_sqlcom_select (thd=0x7ffebc012a40, all_tables=0x7ffebc021d48) at /home/mysql/soft/percona-server-5.7.29-32/sql/sql_parse.cc:5490
#19 0x00000000014ba323 in mysql_execute_command (thd=0x7ffebc012a40, first_level=true) at /home/mysql/soft/percona-server-5.7.29-32/sql/sql_parse.cc:3016
#20 0x00000000014c2025 in mysql_parse (thd=0x7ffebc012a40, parser_state=0x7fffe83e04a0, update_userstat=false) at /home/mysql/soft/percona-server-5.7.29-32/sql/sql_parse.cc:5927
#21 0x00000000014b6c5f in dispatch_command (thd=0x7ffebc012a40, com_data=0x7fffe83e0c90, command=COM_QUERY) at /home/mysql/soft/percona-server-5.7.29-32/sql/sql_parse.cc:1539
#22 0x00000000014b5a94 in do_command (thd=0x7ffebc012a40) at /home/mysql/soft/percona-server-5.7.29-32/sql/sql_parse.cc:1060
#23 0x00000000015e9d32 in handle_connection (arg=0x63a8fb0) at /home/mysql/soft/percona-server-5.7.29-32/sql/conn_handler/connection_handler_per_thread.cc:325
#24 0x00000000018b97f2 in pfs_spawn_thread (arg=0x6359f60) at /home/mysql/soft/percona-server-5.7.29-32/storage/perfschema/pfs.cc:2198
#25 0x00007ffff7bc6ea5 in start_thread () from /lib64/libpthread.so.0
#26 0x00007ffff5f2b8dd in clone () from /lib64/libc.so.6
(gdb) p *tuple->fields
$12 = {data = 0x7ffebd2ccf08, ext = 0, spatial_status = 3, len = 4, type = {prtype = 1027, mtype = 6, len = 4, mbminmaxlen = 0}}
(gdb) x/16bx 0x7ffebd2ccf08
0x7ffebd2ccf08: 0x80    0x00    0x00    0x04    0x80    0x00    0x4e    0x20
0x7ffebd2ccf10: 0x80    0x00    0x00    0x04    0x80    0x00    0x4e    0x20

MySQL 5.7：desc order by反向定位

相關文章