你好赵伟

这个屌丝很懒，什么也没留下！

热门标签

mysql 1539_MySQL 5.7：desc order by反向定位

作者：你好赵伟 | 2024-04-27 00:56:10

踩

mysql order by 取反

定位模式为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 ;

+----+-------------+---------------+------------+-------+---------------+------+---------+------+-------+----------+-----------------------+

+----+-------------+---------------+------------+-------+---------------+------+---------+------+-------+----------+-----------------------+

| 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

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/你好赵伟/article/detail/494009