Simple analysis of primary key function in Mysql _mysql

In 5.1.46, the optimizer made a little change in the selection of the primary key:

Performance:while looking for the shortest index for a covering index scan, the optimizer did not consider the full row l Ength for a clustered primary key, as in InnoDB. Secondary covering indexes'll now is preferred, making full table scans less.

This version adds the Find_shortest_key function, which can be thought of as selecting the minimum key length

Index to satisfy our query.

How does this function work:

Copy Code code as follows:

What Find_shortest_key should is the following. If The primary key is a covering index

And is clustered, like in MyISAM, then the behavior today should remain the same. If the

Primary key is clustered, like in InnoDB, then it should not consider using the primary

Key because then the storage engine would have to scan through much more data.

Call Primary_key_is_clustered (), when the return value is True, execute Find_shortest_key: Select the overlay index with the smallest key length (secondary covering indexes), Then to satisfy the query.

First Test in 5.1.45:

$mysql

-v MySQL Ver 14.14 distrib 5.1.45, for Unknown-linux-gnu (x86_64) using Editline wrapper root@test

03:49:45 >create table Test (ID int,name varchar (), name2 varchar (), D datetime,primary key (ID)) Engine=innodb;

Query OK, 0 rows affected (0.16 sec)

root@test 03:49:47>insert into test values (1, ' XC ', ' SDS ', Now ()), (2, ' XCX ', ' DD ' , now ()), (3, ' sdds ', ' ddd ', Now ()), (4, ' sdsdf ', ' DSD ', Now ()), (5, ' Sdsdaa ', ' SDS ', now ());

Query OK, 5 rows Affected (0.00 sec)

records:5 duplicates:0 warnings:0 root@test 03:49:51> root@test

03: 49:51>insert into test values (6, ' xce ', ' SDSD ', Now ()), (7, ' XCX ', ' SDSD ', Now ()), (8, ' SDDS ', ' SDS ', Now ()), (9, ' SDSDSDF ' , ' SDSDSD ', Now ()), (Ten, ' Sdssdfdaa ', ' SDSDSD ', now ());

Query OK, 5 rows Affected (0.00 sec)

Records:5 duplicates:0 warnings:0

To create an index ind_1:

Root@test 03:49:53>alter Table Test Add index Ind_1 (name,d);

Query OK, 0 rows affected (0.09 sec)

records:0 duplicates:0 warnings:0 root@test 03:50:08>explain

Select Cou NT (*) from test;

+--+ ————-+ ——-+ ——-+ ————— + ——— + ——— +--+--+ ————-+

| id | select_type | table | type | possible_keys | key   | key_len | r EF | Rows | Extra    |

+--+ ————-+ ——-+ ——-+ ————— + ——— + ——— +--+--+ ————-+

| 1 | Simple   | test | index | NULL     | PRIMARY | 4    | NULL |  10 | Using Index |

+--+ ————-+ ——-+ ——-+ ————— + ——— + ——— +--+--+ ————-+

1 row in Set (0.00 sec)

Add Ind_2:

Root@test 08:04:35>alter Table Test Add index ind_2 (d);

Query OK, 0 rows affected (0.07 sec)

records:0 duplicates:0 warnings:0 root@test 08:04:45>explain

Select Cou NT (*) from test;

+--+ ————-+ ——-+ ——-+ ————— + ——— + ——— +--+--+ ————-+

| id | select_type | table | type | possible_keys | key   | key_len | r EF | Rows | Extra    |

+--+ ————-+ ——-+ ——-+ ————— + ——— + ——— +--+--+ ————-+

| 1 | Simple   | test | index | NULL     | PRIMARY | 4    | NULL |  10 | Using Index |

+--+ ————-+ ——-+ ——-+ ————— + ——— + ——— +--+--+ ————-+

1 row in Set (0.00 sec)

In the above version "5.1.45", you can see that the optimizer chooses to use the primary key to complete the scan, and does not use Ind_1,ind_2 to complete the query;

Next is: 5.1.48

$mysql

-v MySQL Ver 14.14 distrib 5.1.48, for Unknown-linux-gnu (x86_64) using Editline wrapper root@test

03:13:15 > CREATE TABLE Test (ID int,name varchar (), name2 varchar, d datetime,primary key (ID)) Engine=innodb;

Query OK, 0 rows Affected (0.00 sec)

root@test 03:48:04>insert into test values (1, ' XC ', ' SDS ', Now ()), (2, ' XCX ', ' DD ' , now ()), (3, ' sdds ', ' ddd ', Now ()), (4, ' sdsdf ', ' DSD ', Now ()), (5, ' Sdsdaa ', ' SDS ', now ());

Query OK, 5 rows Affected (0.00 sec)

records:5 duplicates:0 warnings:0 root@test into

Test V Alues (6, ' xce ', ' SDSD ', Now (), (7, ' XCX ', ' SDSD ', "Now"), (8, ' SDDS ', ' SDS ', Now ()), (9, ' sdsdsdf ', ' SDSDSD ', Now ()), (10, ' Sdssdfdaa ', ' SDSDSD ', now ());

Query OK, 5 rows affected (0.01 sec)

records:5 duplicates:0 warnings:0

To create an index ind_1:

Root@test 03:13:57>alter Table Test Add index Ind_1 (name,d); Query OK, 0 rows affected (0.01 sec) records:0 duplicates:0 warnings:0 root@test Select COUNT (*)

From Test; +--+ ————-+ ——-+ ——-+ ————— + ——-+ ——— +--+--+ ————-+ | ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows |

Extra | +--+ ————-+ ——-+ ——-+ ————— + ——-+ ——— +--+--+ ————-+ | 1 | Simple | Test | Index | NULL | Ind_1 | 52 |  NULL | 10 |

Using Index |

+--+ ————-+ ——-+ ——-+ ————— + ——-+ ——— +--+--+ ————-+ root@test 08:01:56>alter table Test Add index ind_2 (d);  Query OK, 0 rows affected (0.03 sec) records:0 duplicates:0 warnings:0 add ind_2:root@test 08:02:09>explain Select

Count (*) from test; +--+ ————-+ ——-+ ——-+ ————— + ——-+ ——— +--+--+ ————-+ | ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows |

Extra | +--+ ————-+ ——-+ ——-+ ————— + ——-+ ——— +--+--+ ————-+ | 1 | Simple | Test | Index | NULL | Ind_2 | 9 |  NULL | 10 |

Using Index | +--+--——-+ ——-+ ——-+ ————— + ——-+ ——— +--+--+ ————-+ 1 row in Set (0.00 sec)

 

Version "5.1.48" in the first wise choice ind_1 to complete the scan, and did not take into account the use of primary key (full index Scan) to complete the query, and then add Ind_2, because the Ind_1 key length is greater than the Ind_2 key length, so MySQL choose a better ind_ 2 to complete the query, you can see MySQL in the selection method is also slowly intelligent.

Observe performance:

5.1.48 root@test 08:49:32>set profiling = 1;

Query OK, 0 rows Affected (0.00 sec) Root@test 08:49:41>select count (*) from test; + ———-+ |

COUNT (*) | + ———-+ |

5242880 |

+ ———-+ 1 row in Set (1.18 sec) root@test 08:56:30>show profiles Cpu,block io for query 1; + —————————— –+ ———-+ ———-+ ———— + ———— –+ ————— + | Status | Duration | Cpu_user | Cpu_system | block_ops_in |

Block_ops_out | + —————————— –+ ———-+ ———-+ ———— + ———— –+ ————— + | Starting | 0.000035 |  0.000000 |      0.000000 |       0 |

0 | | Checking query cache for Query | 0.000051 |  0.000000 |      0.000000 |       0 |

0 | | Opening Tables | 0.000014 |  0.000000 |      0.000000 |       0 |

0 | | System Lock | 0.000005 |  0.000000 |      0.000000 |       0 |

0 | | Table Lock | 0.000010 |  0.000000 |      0.000000 |       0 |

0 | | init | 0.000015 |  0.000000 |      0.000000 |       0 |

0 | | Optimizing | 0.000007 |  0.000000 |      0.000000 | 0 |      0 | | Statistics | 0.000015 |  0.000000 |      0.000000 |       0 |

0 | | Preparing | 0.000012 |  0.000000 |      0.000000 |       0 |

0 | | Executing | 0.000007 |  0.000000 |      0.000000 |       0 |

0 | | Sending Data | 1.178452 |  1.177821 |      0.000000 |       0 |

0 | | End | 0.000016 |  0.000000 |      0.000000 |       0 |

0 | | Query End | 0.000005 |  0.000000 |      0.000000 |       0 |

0 | | Freeing items | 0.000040 |  0.000000 |      0.000000 |       0 |

0 | | Logging Slow Query | 0.000002 |  0.000000 |      0.000000 |       0 |

0 | | Logging Slow Query | 0.000086 |  0.000000 |      0.000000 |       0 |

0 | | Cleaning Up | 0.000006 |  0.000000 |      0.000000 |       0 |

0 |

 + —————————— –+ ———-+ ———-+ ———— + ———— –+ ————— +

Contrast performance:

5.1.45 root@test 08:57:18>set profiling = 1;

Query OK, 0 rows Affected (0.00 sec) Root@test 08:57:21>select count (*) from test; + ———-+ |

COUNT (*) | + ———-+ |

5242880 |

+ ———-+ 1 row in Set (1.30 sec) root@test 08:57:27>show profiles Cpu,block io for query 1; + —————————— –+ ———-+ ———-+ ———— + ———— –+ ————— + | Status | Duration | Cpu_user | Cpu_system | block_ops_in |

Block_ops_out | + —————————— –+ ———-+ ———-+ ———— + ———— –+ ————— + | Starting | 0.000026 |  0.000000 |      0.000000 |       0 |

0 | | Checking query cache for Query | 0.000041 |  0.000000 |      0.000000 |       0 |

0 | | Opening Tables | 0.000014 |  0.000000 |      0.000000 |       0 |

0 | | System Lock | 0.000005 |  0.000000 |      0.000000 |       0 |

0 | | Table Lock | 0.000008 |  0.000000 |      0.000000 |       0 |

0 | | init | 0.000015 |  0.000000 |      0.000000 |       0 |

0 | | Optimizing | 0.000006 |  0.000000 |      0.000000 | 0 |      0 | | Statistics | 0.000014 |  0.000000 |      0.000000 |       0 |

0 | | Preparing | 0.000012 |  0.000000 |      0.000000 |       0 |

0 | | Executing | 0.000007 |  0.000000 |      0.000000 |       0 |

0 | | Sending Data | 1.294178 |  1.293803 |      0.000000 |       0 |

0 | | End | 0.000016 |  0.000000 |      0.000000 |       0 |

0 | | Query End | 0.000004 |  0.000000 |      0.000000 |       0 |

0 | | Freeing items | 0.000040 |  0.000000 |      0.001000 |       0 |

0 | | Logging Slow Query | 0.000002 |  0.000000 |      0.000000 |       0 |

0 | | Logging Slow Query | 0.000080 |  0.000000 |      0.000000 |       0 |

0 | | Cleaning Up | 0.000006 |  0.000000 |      0.000000 |       0 |

0 |

 + —————————— –+ ———-+ ———-+ ———— + ———— –+ ————— +

From the above profile can be seen in the sending data, the difference is more obvious, MySQL does not need to scan the entire table page block, but scan the table index key to the shortest index page block to complete the query, which reduces a lot of unnecessary data.

Ps:innodb is the transaction engine, so in addition to storing the bank records in the leaf node, there will be more information about the transaction (db_trx_id, DB_ROLL_PTR, etc.), so the extra cost of a single line length is about 20 bytes, The most intuitive way is to convert MyISAM to InnoDB and storage space will increase significantly. Then in the main table is T (ID,NAME,PK (ID)), two-level index Ind_name (name,id), this time it is easy to confuse, even if only two fields, the first index is larger than the second index (can be done by innodb_table_ The internal structure of the Monitor watch table) when querying all IDs, the optimizer chooses the second index ind_name.