Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ MySQL query plan key_len know all     - 20 Advanced Java interview questions summary (Programming)

- Ubuntu 15.10 under Python + Apache + CGI fully configured (Server)

- Use SocketServer write a sockets program (Programming)

- Linux see whether there is a hacker program (Linux)

- GitLab Installation Guide -Ubuntu 14.04 LTS (Server)

- Linux kernel TCP / IP parameters analysis and tuning (Linux)

- MongoDB uses aggregate, group, match mysql achieve in having (count (1)> 1) features (Database)

- Linux environment RabbitMQ installation and monitoring of plug-in installation (Linux)

- Linux-based Heartbeat high availability configuration httpd service (Server)

- Write perfect printf (Programming)

- crontab task scheduling Health Check (Linux)

- CMake Quick Start Tutorial (Linux)

- RPM package creation Getting Started (Linux)

- The YUM package management under Linux (Linux)

- Android screen rotation processing and ProgressDialog the best AsyncTask (Programming)

- Nginx log cutting and MySQL script regular backup script (Server)

- The next key to install Linux bash script PowerShell (Linux)

- Django 1.8 TEMPLATE_DIRS configuration and STATICFILES_DIRS (Server)

- No password on Oracle and MySQL login (Database)

- Httpclient4.4 of principle (Http execution context) (Programming)

 
         
  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:      
 
- Linux in order to make NMAP hide and seek with the firewall (Linux)
- Shutdown - an advanced shutdown artifact (Linux)
- Boot automatically remove Linux operating history records (Linux)
- Web server security policy (Linux)
- zBackup: A versatile tool to remove duplicate backup (Linux)
- Configuring LIDS build Linux kernel security intrusion detection system (Linux)
- SQL Server memory Misunderstanding (Database)
- Linux data recovery software efficiently practical application extundelete (Linux)
- MySQL DATE_FORMAT () function (Database)
- Storm how to assign tasks and load balancing (Programming)
- Share Practical Tutorial GitHub (Linux)
- The lambda expression Java8 (constructor references) (Programming)
- Linux `dirname $ 0` (Linux)
- Linux compiler installation Redis (Database)
- Emacs install clojure development environment (Linux)
- Android View event delivery (Programming)
- CentOS 6.x systems installation + NIC driver installation (Realtek PCIe GBE Family Controller for Linux) (Linux)
- Ubuntu apt-mirror established local private sources (Linux)
- Linux RAID Set RAID 10 or 0 + 1 (Linux)
- Linux SVN installation and configuration graphic tutorials (Server)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.