Mysql Mysterious Handler command and implementation method _mysql

MySQL "Since ancient times" has a mysterious handler command, and this command is not SQL standard syntax, you can reduce the optimizer for SQL statements parsing and optimization overhead, thereby improving query performance. See here, may have a small partner is not calm, so good things why not widely used? Isn't this similar to a Handlersocket plug-in that was compacted a few years ago?

So, let's take a look at the handler syntax description:

HANDLER Tbl_name OPEN [[as] alias]
HANDLER tbl_name READ index_name {= | <= | >= | < | >} (value1,value2,...) [WHERE Where_condition] [LIMIT ...]
HANDLER Tbl_name READ index_name {i | NEXT | PREV | Last} [WHERE where_condition] [LIMIT ...]
HANDLER tbl_name READ {i | NEXT} [WHERE where_condition] [LIMIT ...]
HANDLER Tbl_name Close

First from the syntax, handler can access the data through the specified index. However, this syntax does not support DML operations. In addition, because of reduced SQL parsing, the performance of the handler command is really very good, according to inside June's simple primary key test, handler command faster than SQL 40%~45%. 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 the 24C test server of Inside June, the 64-line Cheng query ran to nearly 37W QPS, or very impressive. Compared to the SQL select query, the overall test results are shown in the following illustration:

The main implementation of command handler in the source code sql_handler.h, sql_handler.cc, set a breakpoint can observe the specific process. MySQL Upper and InnoDB storage engine layer main implementation function portal is:

Copy Code code 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 do not see the use of command handler in the production environment? This is mainly because the handler command has several main issues:

Non-conformance read???
Returns all columns in a clustered index (even two-level index access) and cannot return a specific column
Secondary indexes do not use the Limit keyword and can only return 1 rows of records
Students who know the command handler may think that handler read has a dirty read problem. Because the MySQL official document for handler read the description is that:

The handler interface does not have to provide a consistent look of the "for example, dirty reads are permitted)", so The storage engine can use optimizations this SELECT does not normally permit.
However, it is particularly noteworthy that the correct version of the MySQL documentation is that it allows for inconsistent reads. However, the handler implementation of the INNODB storage engine supports consistent reading, and inside does not have a dirty read problem. Of course, the source explains everything, you can find in the function Init_table_handle_for_handler will assign to the Readview, and the annotation also explains this:

/* We let HANDLER always todo 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;

Looks like using the handler command to master key query is good, reduce the cost of SQL parser, performance to enhance the leverage. But for this, the application has to pay huge changes, and the biggest advantage of SQL is standardization. I believe this is also the biggest problem that the NoSQL database encounters at present. For example, mongodb,inside June every time you write a query to open the official order table ...