Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ MySQL query plan key_len know all     - CentOS 6.5 install Firefox (Linux)

- Eclipse configuration GTK (Linux)

- Installation Strongswan: on a Linux IPsec-based VPN tool (Linux)

- How to enable Software Collections (SCL) on CentOS (Server)

- error no.2013 lost connection Tom with SQLServer during query (Database)

- Kali Linux resolve GPG error KEYEXPIRED 1425567400 (Linux)

- Ubuntu installed racing game Speed Dreams 2.1 (Linux)

- HTML5 Fundamentals study notes (Programming)

- To convert into a binary search tree sorted doubly linked list (Programming)

- iOS used in the development --UITabBarController tag controller (Programming)

- VMware installed Linux system and JDK deployment (Linux)

- Chrome plug-in management, online-offline installation, part of the plug presentations (Linux)

- Manager Docker browser (Server)

- Mind mapping software installed in CentOS 7 in XMind (Linux)

- CentOS 6.7 compile and install LAMP (Server)

- Ubuntu under VirtualBox virtual machine serial port settings (Linux)

- The user how to install Notepadqq 0.41.0 under ubuntu and debian (Linux)

- Linux resource restriction level summary (Linux)

- CentOS of NFS (Server)

- Linux environment Duplex (multi-machine) automatic mutual backup scheme (Linux)

 
         
  MySQL query plan key_len know all
     
  Add Date : 2018-11-21      
         
         
         
  This paper introduces the MySQL query plan ken_len meaning; then introduces the calculation method key_len; and finally through a fake example to illustrate how key_len joint index to see how many columns are used.

key_len meaning

In MySQL, can be viewed explain SQL statements walking paths, as follows:

mysql> create table t (a int primary key, b int not null, c int not null, index (b));
Query OK, 0 rows affected (0.01 sec)
mysql> explain select b from t;
+ ---- + ------- + ------- + ------------- + -------------- - + --------- + ------ + ------ + ------ + ------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------- + ------- + ------------- + -------------- - + --------- + ------ + ------ + ------ + ------------- +
| 1 | SIMPLE | t | index | NULL | b | 4 | NULL | 1 | Using index |
+ ---- + ------- + ------- + ------------- + -------------- - + --------- + ------ + ------ + ------ + ------------- +
1 row in set (0.00 sec)

Wherein, key_len index represents the length of use, in bytes. In the above example, since the int type take 4 bytes, and the index contains only one, therefore, key_len 4.

Here is the joint index:

mysql> alter table t add index ix (b, c);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select b, c from t;
+ ---- + ------- + ------- + ------------- + -------------- - + --------- + ------ + ------ + ------ + ------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------- + ------- + ------------- + -------------- - + --------- + ------ + ------ + ------ + ------------- +
| 1 | SIMPLE | t | index | NULL | ix | 8 | NULL | 1 | Using index |
+ ---- + ------- + ------- + ------------- + -------------- - + --------- + ------ + ------ + ------ + ------------- +
1 row in set (0.00 sec)

Ix joint index contains two, and used to, so here is ken_len 8.

Here, we are able to understand the meaning of key_len, it seems to have nothing to talk about, but, MySQL key_len in computing, there are many areas requiring attention.

For example, we will b NOT NULL constraints of this column is removed, and then we would expect ken_len not the same, as follows:

mysql> alter table t modify b int;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
 
mysql> explain select b from t;
+ ---- + ------- + ------- + ------------- + -------------- - + --------- + ------ + ------ + ------ + ------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------- + ------- + ------------- + -------------- - + --------- + ------ + ------ + ------ + ------------- +
| 1 | SIMPLE | t | index | NULL | b | 5 | NULL | 1 | Using index |
+ ---- + ------- + ------- + ------------- + -------------- - + --------- + ------ + ------ + ------ + ------------- +
1 row in set (0.00 sec)
 

MySQL in key_len calculation rules

In MySQL, key_len calculation rules are as follows:
1. If the column is empty, then applied to the data type uses 1 byte basis, such as int type, can not be empty key_len 4, 5 can be empty key_len
2. If the column is variable length, the column on the data base percentage plus 2 bytes, as varbinary (10), can not be empty, key_len 10 + 2 can is empty key_len 10 + 2 +1
3. If the character is, you also need to consider character sets, such as the definition of a column is varchar (10), and is utf8, not empty, then key_len 10 * 3 + 2 can is empty key_len 10 * 3 + 2 + 1

In addition, the calculation method decimal column as above, if you can is empty, then applied to the data type uses 1 byte basis, however, the number of bytes occupied itself decimal calculation is more complicated.

According to official documents can know, decimal is defined as decimal (M, D), where, M is the total number of bits, D digits after the decimal point. Before decimal digit after the decimal point and stored separately, and the median of a group of 9 to save 4 bytes, if less than 9 digits, the number of bytes required are as follows:

Leftover Digits Number of Bytes
-----------------------------
| 0 | 0 |
| 1-2 | 1 |
| 3-4 | 2 |
| 5-6 | 3 |
| 7-9 | 4 |
-----------------------------

E.g:
• decimal (20,6) => the left of the decimal point 14, the right of the decimal point 6 => left of the decimal grouped into 5 + 9, requires 3 bytes + 4 bytes of storage, a decimal point packet requires 3 bytes of storage => total of 10 bytes
• decimal (18,9) => 9 left of the decimal digits, 9 digits to the right of the decimal point =>, respectively, using 4 bytes of storage => requires a total of 8 bytes
• decimal (18,2) => 16 digits left of the decimal, 2 digits to the right of the decimal point => Group of 7 + 9, requiring 8 bytes of storage, the right of the decimal point one byte memory => requires a total 9 bytes

Joint analysis by key_len index

As shown below, we define a table t, t table contains a, b, c, d total of four:

mysql> show create table t G
*************************** 1. row ******************** *******
      Table: t
Create Table: CREATE TABLE `t` (
  `A` int (11) NOT NULL,
  `B` int (11) DEFAULT NULL,
  `C` int (11) DEFAULT NULL,
  `D` int (11) DEFAULT NULL,
  PRIMARY KEY ( `a`),
  KEY `ix_x` (` b`, `d`,` c`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8
1 row in set (0.00 sec)

Now we want to execute a SQL statement is as follows:

select a from t where b = 5 and d = 10 order by c;

Suppose we have an index ix_x (b, d, c), by explain get the following output:

mysql> explain select a from t where b = 5 and d = 10 order by c;
+ ------------- + ---- + ------- + ------ + --------------- + ------------- + --------- + ------ + ------ + ----------- --------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ------------- + ---- + ------- + ------ + --------------- + ------------- + --------- + ------ + ------ + ----------- --------------- +
| 1 | SIMPLE | t | ref | ix_x | ix_x | 10 | const, const | 1 | Using where; Using index |
+ ------------- + ---- + ------- + ------ + --------------- + ------------- + --------- + ------ + ------ + ----------- --------------- +
1 row in set (0.00 sec)

You can see, the query using the joint index b and d two to filter the data.

If we define the joint index is not `ix_x (b, d, c)`, but `ix_x (b, c, d)`, obtained by explain the following entries:

mysql> alter table t drop index ix_x;
mysql> alter table t add index ix_x (b, c, d);
mysql> explain select a from t where b = 5 and d = 10 order by c;
+ ------------- + ---- + ------- + ------ + --------------- + --------- + ------- + ------ + ------ + ------ --------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ------------- + ---- + ------- + ------ + --------------- + --------- + ------- + ------ + ------ + ------ --------- +
| 1 | SIMPLE | t | ref | ix_x | ix_x | 5 | const | 2 | Using where; Using index |
+ ------------- + ---- + ------- + ------ + --------------- + --------- + ------- + ------ + ------ + ------ --------- +
1 row in set (0.00 sec)

key_len 5, that is, only use the joint index in the first column, you can see that although the joint index contains all the columns that we want to query, but, since the order of the problem definition, SQL statements do not take full advantage of index.
     
         
         
         
  More:      
 
- SaltStack installation and testing (Server)
- Ceph tuning --Journal and tcmalloc (Server)
- Nginx concerning the location and rewrite applications proxy_pass (Server)
- Linux Mint 17.2 64 bit installation Docker and management software seagull (Linux)
- MySQL appears data too long for column 'name' at row 1 to solve (Database)
- How to restart after a crash Cinnamon (Linux)
- Share useful bash aliases and functions (Linux)
- Several configuration changes illustrate deployment of PHP (Server)
- LaTeX Getting Started Tutorial (Linux)
- Oracle Database Delete Delete million or more common method of heap table data (Database)
- Linux virtual machine settings network, hostname ssh access (Linux)
- To install the Contiki development toolchain on Ubuntu (Linux)
- About Linux operating system security (Linux)
- C ++ thread creates transmission parameters are changed (Programming)
- Linux / UNIX: Use the dd command to create a 1GB size binary (Linux)
- Rails 4.1.6 start being given Could not find a JavaScript runtime (Linux)
- Transfer files to Windows and Linux (Linux)
- Changes in C # asynchronous programming model (Programming)
- Linux batch copy data script (Linux)
- mydumper installation error process (Database)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.