MySQL Source: index-related data structure

http://www.orczhou.com/index.php/2012/11/mysql-source-code-data-structure-about-index/

This article will try to describe the data structure associated with the MySQL index store. program = data structure + algorithm, understand the data structure, then you can learn more about how to use the index in MySQL source code, how to choose their own execution plan.

Directory [Hide]

• 1. How MySQL describes the index of a data table
• 2. GDB Prints Observation Index information
  • 2.1 Print Index basic information
  • 2.2 Printing The basic information for a column
  • 2.3 Print index a column details
• 3. How to use index information in MySQL source code

1. How MySQL describes the index of a data table

MySQL uses a Table object to describe a data table, so how does the index of the data table describe, and how does the index's statistics be stored? For example, we have the following data sheet:

CREATE TABLE ' users ' (' id ' int (one) not null, ' nick ' varchar (+) default NULL, ' Reg_date ' datetime default NULL, PRI MARY key (' ID '), key ' Ind_nick ' (' NICK '), key ' Ind_regdate ' (' reg_date '))

The table has indexes, PRIMARY KEY, Ind_nick, Ind_regdate, and let's look at how MySQL internally stores the three indexes and how to use the statistics for those indexes. , which describes the main structure for storing a data table index:

• MySQL uses a Table object to describe a data table whose member Key_info (Type key) describes the full index of the table
• Key_info is an array, each element is a key (Vim-t key View) object, representing an index, in order, the entire array represents the full index of the table
• The member of the Key_info (Key object), Key_part, is a pointer to the Key_part_info array that describes all the indexed column information for an index, and each element of the array represents an indexed column
• Finally, the member of the Key_part field points to the table field corresponding to the index column

2. GDB Prints Observation index information2.1 Print index basic information

The basic information for an index (for example, Ind_nick) is stored in the key structure (table->key_info array), where we print the basic information of the index Ind_nick to observe the key structure:

(GDB) p s->table->key_info[1] $26 = {Key_length =, flags = 104, Key_parts = 1, extra_length = 3, Usable_ke Y_parts = 1, block_size = 1024x768, algorithm = ha_key_alg_undef, {parser = 0x0, Parser_name = 0x0}, Key_part = 0x7f65140208 Name = 0x7f651401fc11 "Ind_nick", Rec_per_key = 0x7f651401fa68, handler = {bdb_return_if_eq = 0}, table = 0x7f651402 3D30}

Key_length = 67: Indicates that the index entry length is 67 (excluding ROWID), 32*2 + 1 (NULL) + varchar becomes information

Flags = 104 This is a flag bit of index information that stores some important attributes of the index, such as whether it is not a unique index, whether there is a variable length field in the index, is not a full-text index, etc., 104 is binary, that is 1101000, also equals 8+32+64, that is, ha_var_ Length_key|ha_binary_pack_key|ha_null_part_key

More flags types:

#define HA_NOSAME 1/* Set if not dupplicated records */#define HA_PACK_KEY 2/* PACK string key to previous key */ #define HA_SPACE_PACK_USED 4/* Test for if space_pack used */#define HA_VAR_LENGTH_KEY 8 #define HA_AUTO_KEY #define Ha_binary_pack_key/* Packing of all keys to prev KEY */#define HA_NULL_PART_KEY #define HA_FULLTEXT */For FU Ll-text Search */#define HA_UNIQUE_CHECK/* CHECK the key for uniqueness */#define HA_SPATIAL 1024x768/* for SPATIAL SE Arch */#define HA_NULL_ARE_EQUAL 2048/* NULL in key is CMP as EQUAL */#define HA_GENERATED_KEY 8192/* automaticly Gen erated Key *

/

Key_part = 0x7f6514020860 This pointer points to an KEY_PART_INFO array that stores information about the individual columns of the index.

2.2 Printing The basic information for a column

(GDB) p s->table->key_info[1]->key_part[0] $29 = {field = 0x7f6514020588, offset = 5, Null_offset = 0, len Gth = +, Store_length = Key_type = 32768, FIELDNR = 2, Key_part_flag = 1, type = [\017 ', null_bit = ' \001 '}

field = 0x7f6514020588 points to the column that uses the data table for this index column

offset = 5 Changing the index column starts at the first offset of the index string

Null_offset = 0 NULL Information Store offset

Length = 64, index string lengths

Store_length = 67, entire index entry length

...

2.3 Print index a column details

(GDB) p *s->table->key_info[1]->key_part[0]->field $36 = {... table = 0x7f6514023d30, ... field_name = 0x7f651401fcf4 "Nick", ... key_start = {map = 2}, Part_of_key = {map = 2}, part_of_key_not_clustered = {map = 2}, PA Rt_of_sortkey = {map = 2}, ...}

Table = 0x7f6514023d30 points to the data table where the column is located

Field_name = 0x7f651401fcf4 "Nick" column name

Key_start = 2 contains this field and is the index number of the first field in the index, and the binary of 2 is 10, that is, the second index starts with the field

3. How to use index information in MySQL source code

The following excerpt of the function Store_create_info (Show show create TABLE command) Displays the index information section in the following code:

1351 for (uint i=0; i < Share->keys; i++,key_info++) 1352 {1353 Key_part_info *key_part= key_info->key_p Art ... 1357 if (i = = Primary_key &&!strcmp (Key_info->name, Primary_key_name)) 1358 {1359 found_primary=1; 1364 Packe T->append (String_with_len ("PRIMARY KEY")); 1365} 1366 Else if (Key_info->flags & Ha_nosame) 1367 packet->append (String_with_len ("UNIQUE key")); ... 1372 Else 1373 Packet->append (String_with_len ("KEY")); ... 1380 for (UINT J=0; j < Key_info->key_parts; j++,key_part++) 1381 {... 1401} 1402 Packet->append (') '); 1403 Store_key_options (THD, packet, table, key_info); 1404 if (key_info->parser) 1405 {... 1410} 1411}

Summary:

(1) You can see how MySQL iterates through all the indexes of a table

key_info= table->key_info; for (uint i=0; i < Share->keys; i++,key_info++) {...}

(2) You can see how MySQL differentiates unique indexes based on key_info->flags fields, full-text indexes, etc.

(3) You can see how MySQL iterates through all the fields of an index:

key_part_info *key_part= key_info->key_part; for (UINT J=0; j < Key_info->key_parts; j++,key_part++) {.. . }

(4) View the complete function you can also see how MySQL reads the individual column of the data table

MySQL Source: index-related data structure