MYSQL mysterious HANDLER command and implementation method_MySQL

MySQL has had a mysterious HANDLER command "since ancient times", and this command has non-SQL standard syntax, which can reduce the optimizer's parsing and optimization overhead for SQL statements, thereby improving query performance. Seeing this, some friends may not be calm. Why is such a good thing not widely used? Isn't this similar to the handlersocket plug-in that was very popular a few years ago?

So, let’s take a look at the Handler syntax description first:

HANDLER tbl_name OPEN [ [AS] alias]
HANDLER tbl_name READ index_name { = | <= | >= | < | > } (value1,value2,…) [ WHERE where_condition ] [LIMIT … ]
HANDLER tbl_name READ index_name { FIRST | NEXT | PREV | LAST } [ WHERE where_condition ] [LIMIT … ]
HANDLER tbl_name READ { FIRST | NEXT } [ WHERE where_condition ] [LIMIT … ]
HANDLER tbl_name CLOSE

First of all, from a syntactic point of view, HANDLER can access data through the specified index. But this syntax does not support DML operations. In addition, due to the reduction of SQL parsing, the performance of the Handler command is really good. According to Inside's simple primary key test, the Handler command is 40% to 45% faster than SQL. The test script is as follows:

SET @id=FLOOR(RAND()*1000000);
HANDLER sbtest.sbtest1 OPEN AS c;
HANDLER C READ `PRIMARY` = (@id);
HANDLER C CLOSE;

On Inside’s 24C test server, the 64-thread primary key query ran to nearly 37W QPS, which is still very impressive. Comparing the SQL SELECT query, the overall test results are as shown below:

The main implementation of command HANDLER is in the source code sql_handler.h, sql_handler.cc. You can observe the specific process by setting a breakpoint. The main function entrances for the MySQL upper layer and InnoDB storage engine layer are:

The code is as follows:

Sql_cmd_handler_open::execute
Sql_cmd_handler_read::execute
Sql_cmd_handler_close::execute
ha_innobase::init_table_handle_for_HANDLER
ha_partition::init_table_handle_for_HANDLER() (version 7 supports HANDLER operation partition table)

Since the performance is good, why don’t you see the use of command HANDLER in the production environment? Mainly because the HANDLER command has the following major problems:

Non-consistent reads? ? ?
Returns all columns in the clustered index (even secondary index access), not a specific column
The secondary index does not use the LIMIT keyword and can only return 1 row of records
Students who know the command HANDLER may think that there is a dirty read problem in HANDLER reading. Because the official MySQL documentation says this about HANDLER reading:

The handler interface does not have to provide a consistent look of the data (for example, dirty reads are permitted), so the storage engine can use optimizations that SELECT does not normally permit.
It is important to note, however, that the MySQL documentation accurately states that inconsistent reads are allowed. However, the HANDLER implementation of the InnoDB storage engine supports consistent reading. According to Insider's personal test, there is indeed no dirty reading problem. Of course, the source code speaks for itself. You can find that READVIEW is allocated in the function init_table_handle_for_HANDLER, and the comments also illustrate this:

/* We let HANDLER always to do the reads as consistent reads, even
if the trx isolation level would have been specified as SERIALIZABLE */
m_prebuilt->select_lock_type = LOCK_NONE;
m_prebuilt->stored_select_lock_type = LOCK_NONE;

It seems that it is good to use the HANDLER command to query the primary key. It reduces the overhead of the SQL parser and greatly improves the performance. But for this, the application has to make huge changes, and the biggest advantage of SQL is standardization. I believe this is also the biggest problem currently encountered by NoSQL databases. For example, in MongoDB, Insider must open the official command comparison table every time he writes a query...