Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ MySQL query plan key_len know all     - Keepalived + HAProxy high availability load balancing (Server)

- Linux system security infrastructure Highlights (Linux)

- Linux System Getting Started Learning: Join cron job in Linux (Linux)

- Red Hat Enterprise Linux Server 6.5 installation GCC 4.9.2 (Linux)

- Introduction to Linux system process monitoring tools (Linux)

- BCP importing and exporting large amounts of data Practice (Database)

- Oracle 11g creates virtual private directory RMAN-06004 ORA-00942 error handling (Database)

- Use Bash script write CVS version control (Server)

- Use Elasticsearch + Logstash + Kibana set up centralized log Practice Analysis Platform (Server)

- Ubuntu 14.10 Server configuration wireless Internet access (Server)

- Linux server security - the web server configuration (Linux)

- MySQL Authorized a recovered (Database)

- Python function arguments * args and ** kwargs usage (Programming)

- Linux process scheduling opportunity (Programming)

- Oracle 11g RAC automatically play GI PSU patch (11.2.0.4.8) (Database)

- About phpwind 5.01-5.3 0day analysis of the article (Linux)

- To see the Linux device tree (Linux)

- Redis 3.0.3 Cluster Setup (Database)

- Android Dynamic efficiency articles: a brilliant Loading Analysis and Implementation (Programming)

- SSH automatic disconnection problem solving (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:      
 
- Memory leak analysis using Android studio (Programming)
- MySQL partition summary (Database)
- Android Studio quick overview of Gradle (Programming)
- Firewall chapter of Linux server security configuration (Linux)
- Linux and hardware (Linux)
- Quick paging ROW_NUMBER conducted (Database)
- Learn to read the source code of vmstat (Linux)
- Linux User Rights Study Notes (Linux)
- Xshell upload files to achieve Windows host Linux (Linux)
- CentOS NAT iptables (Linux)
- NFS installation process under the CentOS (Linux)
- Json data with double backslashes to a single backslash Json data processing (Programming)
- A list of the basics of Python, Ganso, Dictionary (Programming)
- Hadoop2.6.3 build clusters and the development of MapReduce WIN7 by Eclipse on Linux demo (Server)
- Linux system security audit tools scan nessus installation tutorial (Linux)
- Install Python 3.3.4 under CentOS 6.4 (Linux)
- Lua4.0 interpreter documents (Programming)
- Ubuntu uses the / etc / profile file to configure the JAVA environment variable (Linux)
- Eight sorting algorithm implemented in Python (Programming)
- Installation JDK 1.8 under CentOS7 (Linux)
     
           
     
  CopyRight 2002-2020 newfreesoft.com, All Rights Reserved.