Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ MySQL query plan key_len know all     - Oracle 12c In-Memory Study (Database)

- MySQL various log summary (Database)

- Oracle 11g RAC installation considerations Miscellany (Database)

- Oracle View Object Information (Database)

- Manually compile Hadoop 2.6.0 under Ubuntu 14.04 (Server)

- How do I cancel (almost) any operations in Git, (Linux)

- Sublime Text Add instructions to insert the current time zone (Linux)

- Linux system security (Linux)

- Intel Graphics Installer installation on Ubuntu 15.10 (Linux)

- The user of fedora is not in the sudoers file method to solve (Linux)

- CentOS static IP network configuration (Linux)

- Binding multiple network cards in Linux using command nmcli (Linux)

- How to merge two pictures in Cacti (Linux)

- MongoDB Study Notes (1) - Install MongoDB on Windows systems (Database)

- Install the latest development version of Wine on RedHat and Debian-based systems (Linux)

- RHEL6.5 install the latest version of Vim and increase support for the Python2.7.5 (Linux)

- Ubuntu 12.04 installation instructions under GAMIT10.40 (Linux)

- Offline (local) Yum source structures (Linux)

- CentOS 6/7 Series Docker Installation (Linux)

- Use in Linux ipmitool tool (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:      
 
- Ubuntu How to mount iso file (Linux)
- Installation and configuration to compile MySQL 5.6.10 under CentOS 5.9 (Database)
- Depth understanding of DB2 table space (Tablespace) (Database)
- Recovery from MySQL master data consistency summary (Database)
- pga_aggregate_target and _pga_max_size can not use absolute limit actual PGA (Database)
- MySQL how to export files with the date format (Database)
- Redis data types Introduction (Database)
- Lua study notes under ubuntu (Linux)
- Red-black tree in C ++ (Programming)
- ORA-00845: MEMORY_TARGET not supported on this system Problem (Database)
- Security Knowledge: redirection command application security (Linux)
- Netfilter / Iptables Comments (Linux)
- C ++: Postmodern systems programming language (Programming)
- Install snort intrusion detection system on Debian (Linux)
- Linux operating system security management skills notes (Linux)
- Merge sort Java implementation (Programming)
- Apache Kafka: the next generation of distributed messaging system (Server)
- Ubuntu dual-card system configuration method (Server)
- Several back door and log tool under Linux (Linux)
- CentOS6.6 ordinary users to use sudo command to borrow root user privileges (Linux)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.