In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-03-30 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)06/01 Report--
Author: Qiu Wenhui, senior database expert of Walk Science and Technology
Preface when using Explain to view the execution plan of SQL, there is a column showing the value of key_len. According to this value, you can judge the length of the index. In the combined index, you can know more clearly which part of the field uses the index. In the following demonstration, let's not talk about the rationality of the table structure here, just to prove the method of calculating the index length. At present, most blog posts are index length calculation methods for character types. Here are several types of index length calculation methods:
1. Dg1 root@127.0.0.1 [mytest] > desc table_key
+-+ +
| | Field | Type | Null | Key | Default | Extra | |
+-+ +
| | id | int (11) | NO | | NULL |
| | sid | bigint (20) | NO | | 0 | |
| | name | char (10) | YES | | NULL |
| | age | tinyint (4) | YES | | NULL |
| | sex | tinyint (4) | NO | | NULL |
| | address | varchar (10) | YES | MUL | NULL |
+-+ +
Rows in set (0.01sec)
(dg1) root@127.0.0.1 [mytest] > create index age_index on table_key (age)
Let's take a look at the index length of type tinyint, which is 1 and 2 in NOT NULL and NULL, respectively. The length of the tinyint field is 1, because NULL needs an extra byte marked as empty.
(dg1) root@127.0.0.1 [mytest] > explain extended select * from table_key where age=38
+-- +
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+-- +
| | 1 | SIMPLE | table_key | ref | age_index | age_index | 1 | const | 1 | 100.00 | NULL |
+-- +
Row in set, 1 warning (0.00 sec)
(dg1) root@127.0.0.1 [mytest] > alter table table_key modify age tinyint (4)
(dg1) root@127.0.0.1 [mytest] > explain extended select * from table_key where age=38
+-- +
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+-- +
| | 1 | SIMPLE | table_key | ref | age_index | age_index | 2 | const | 1 | 100.00 | NULL |
+-- +
Row in set, 1 warning (0.00 sec)
(dg1) root@127.0.0.1 [mytest] >
Look at the index length of the bigint type, which is also the NOT NULL and NULL values, which are 8 and 9, respectively. You should know that the bigint length is 8.
(dg1) root@127.0.0.1 [mytest] > alter table table_key add key sid_index (sid)
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
(dg1) root@127.0.0.1 [mytest] > desc select * from table_key where sid=6
+-- +
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+-- +
| | 1 | SIMPLE | table_key | ref | sid_index | sid_index | 8 | const | 1 | NULL |
+-- +
Row in set (0.00 sec)
(dg1) root@127.0.0.1 [mytest] >
(dg1) root@127.0.0.1 [mytest] > alter table table_key modify sid bigint (20)
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
(dg1) root@127.0.0.1 [mytest] > desc select * from table_key where sid=6
+-- +
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+-- +
| | 1 | SIMPLE | table_key | ref | sid_index | sid_index | 9 | const | 1 | NULL |
+-- +
Row in set (0.00 sec)
Take a look at the smallint type index length, which is also NOT NULL and NULL values, 2 and 3 smallint length 2 respectively, allowing a byte token to be null
(dg1) root@127.0.0.1 [mytest] > alter table table_key modify sid smallint not null default 0
Query OK, 9 rows affected (0.04 sec)
Records: 9 Duplicates: 0 Warnings: 0
(dg1) root@127.0.0.1 [mytest] > desc select * from table_key where sid=6
+-- +
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+-- +
| | 1 | SIMPLE | table_key | ref | sid_index | sid_index | 2 | const | 1 | NULL |
+-- +
Row in set (0.00 sec)
(dg1) root@127.0.0.1 [mytest] > alter table table_key modify sid smallint
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
(dg1) root@127.0.0.1 [mytest] > desc select * from table_key where sid=6
+-- +
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+-- +
| | 1 | SIMPLE | table_key | ref | sid_index | sid_index | 3 | const | 1 | NULL |
+-- +
Row in set (0.00 sec)
Look at the mediumint type index length, which is also NOT NULL and NULL values, 3 and 4, respectively.
(dg1) root@127.0.0.1 [mytest] > alter table table_key modify sid mediumint NOT NULL
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
(dg1) root@127.0.0.1 [mytest] > desc select * from table_key where sid=6
+-- +
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+-- +
| | 1 | SIMPLE | table_key | ref | sid_index | sid_index | 3 | const | 1 | NULL |
+-- +
Row in set (0.00 sec)
(dg1) root@127.0.0.1 [mytest] >
(dg1) root@127.0.0.1 [mytest] > alter table table_key modify sid mediumint
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
(dg1) root@127.0.0.1 [mytest] > desc select * from table_key where sid=6
+-- +
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+-- +
| | 1 | SIMPLE | table_key | ref | sid_index | sid_index | 4 | const | 1 | NULL |
+-- +
Row in set (0.00 sec)
(dg1) root@127.0.0.1 [mytest] >
The integer type index length is related to the field length. If null is allowed, an extra byte is required to mark it as empty.
two。 Floating point type table structure
CREATE TABLE `table_ key1` (
`id` int NOT NULL AUTO_INCREMENT
`c1` float NOT NULL
`c2` double NOT NULL
`c3` decimal NOT NULL
`c4` date NOT NULL
`c5` timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP
`c6` datetime NOT NULL
PRIMARY KEY (`id`)
)
If you look at the index length of the float type, NOT NULL and NULL are 4 and 5, respectively.
(dg1) root@127.0.0.1 [mytest] > desc select * from table_key1 where C1 = '3.22'
+-+-
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+-+-
| | 1 | SIMPLE | table_key1 | ref | c1_index | c1_index | 4 | const | 8 | Using index condition |
+-+-
Row in set (0.00 sec)
(dg1) root@127.0.0.1 [mytest] > alter table table_key1 modify C1 float
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
(dg1) root@127.0.0.1 [mytest] > desc select * from table_key1 where C1 = '3.22'
+-+-
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+-+-
| | 1 | SIMPLE | table_key1 | ref | c1_index | c1_index | 5 | const | 8 | Using index condition |
+-+-
Row in set (0.00 sec)
When you look at the index length of the double type, NOT NULL and NULL, it's 8 and 9, respectively.
(dg1) root@127.0.0.1 [mytest] > alter table table_key1 add key c2_index (c2)
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
(dg1) root@127.0.0.1 [mytest] > desc select * from table_key1 where c2 = '3.22'
+-- +
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+-- +
| | 1 | SIMPLE | table_key1 | ref | c2_index | c2_index | 8 | const | 1 | NULL |
+-- +
Row in set (0.00 sec)
(dg1) root@127.0.0.1 [mytest] > alter table table_key1 modify c2 double
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
(dg1) root@127.0.0.1 [mytest] > desc select * from table_key1 where c2 = '3.22'
+-- +
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+-- +
| | 1 | SIMPLE | table_key1 | ref | c2_index | c2_index | 9 | const | 1 | NULL |
+-- +
Row in set (0.00 sec)
(dg1) root@127.0.0.1 [mytest] >
3. Look at the time type and look at the index length of the date type. In NOT NULL and NULL, it is 3 and 4, respectively.
(dg1) root@127.0.0.1 [mytest] > desc select * from table_key1 where c4 = '2015-05-06'
+-+-
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+-+-
| | 1 | SIMPLE | table_key1 | ref | c4_index | c4_index | 3 | const | 4 | Using index condition |
+-+-
Row in set, 3 warnings (0.00 sec)
(dg1) root@127.0.0.1 [mytest] >
(dg1) root@127.0.0.1 [mytest] > alter table table_key1 modify c4 date
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
(dg1) root@127.0.0.1 [mytest] > desc select * from table_key1 where c4 = '2015-05-06'
+-+-
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+-+-
| | 1 | SIMPLE | table_key1 | ref | c4_index | c4_index | 4 | const | 4 | Using index condition |
+-+-
Row in set, 3 warnings (0.00 sec)
(dg1) root@127.0.0.1 [mytest] >
For timestamp, the index length is 4 and 5 for NOT NULL and NULL, respectively.
(dg1) root@127.0.0.1 [mytest] > desc select * from table_key1 where c5 = '2015-05-06 11 purl 23 mytest 21'
+-+-
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+-+-
| | 1 | SIMPLE | table_key1 | ref | c5_index | c5_index | 4 | const | 5 | Using index condition |
+-+-
Row in set, 3 warnings (0.00 sec)
(dg1) root@127.0.0.1 [mytest] >
Dg1) root@127.0.0.1 [mytest] > alter table table_key1 modify c5 timestamp ON UPDATE CURRENT_TIMESTAMP
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
(dg1) root@127.0.0.1 [mytest] >
(dg1) root@127.0.0.1 [mytest] > desc select * from table_key1 where c5 = '2015-05-06 110purl 23pur21'
+-+-
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+-+-
| | 1 | SIMPLE | table_key1 | ref | c5_index | c5_index | 5 | const | 5 | Using index condition |
+-+-
Row in set, 3 warnings (0.00 sec)
(dg1) root@127.0.0.1 [mytest] >
# # datetime is known to be eight bytes in length. Let's see if this is true.
(dg1) root@localhost [mytest] > alter table table_key1 modify c6 datetime not null
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
(dg1) root@localhost [mytest] > desc select * from table_key1 where c6 = '2015-05-06 11 mytest 10 mytest 36'
+-- +
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+-- +
| | 1 | SIMPLE | table_key1 | ref | c6_index | c6_index | 5 | const | 1 | NULL |
+-- +
Row in set (0.00 sec)
(dg1) root@localhost [mytest] > alter table table_key1 modify c6 datetime null
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
(dg1) root@localhost [mytest] > desc select * from table_key1 where c6 = '2015-05-06 11 mytest 10 mytest 36'
+-- +
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+-- +
| | 1 | SIMPLE | table_key1 | ref | c6_index | c6_index | 6 | const | 1 | NULL |
+-- +
Row in set (0.00 sec)
Subverted our understanding, datetime is not 8 bytes, let's take a look at the version of MySQL, yes, MySQL5.6 is datetime length is 5 bytes
(dg1) root@localhost [mytest] >\ s
-
Mysql Ver 14.14 Distrib 5.6.22, for linux-glibc2.5 (x86 / 64) using EditLine wrapper
Connection id: 3
Current database: mytest
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile:''
Using delimiter:
Server version: 5.6.22-log MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: gbk
Db characterset: gbk
Client characterset: gbk
Conn. Characterset: gbk
UNIX socket: / opt/app/mysql/mysql3307.socket
Uptime: 4 min 47 sec
Threads: 1 Questions: 19 Slow queries: 0 Opens: 75 Flush tables: 1 Open tables: 64 Queries per second avg: 0.066
-
(dg1) root@localhost [mytest] >
Summary: in the MySQL5.6 version, whether to use the timestamp type should be a matter of opinion. Datetime is five bytes, and the range of timestamp is relatively narrow (1970-2037). Do not rule out the possibility that subsequent versions will modify its range.
4. Character type table structure, character set is UTF8
(dg1) root@127.0.0.1 [mytest] > desc table_key
+-+ +
| | Field | Type | Null | Key | Default | Extra | |
+-+ +
| | id | int (11) | NO | | NULL |
| | sid | bigint (20) | NO | | 0 | |
| | name | char (10) | YES | | NULL |
| | age | tinyint (4) | YES | | NULL |
| | sex | tinyint (4) | NO | | NULL |
| | address | varchar (10) | YES | MUL | NULL |
+-+ +
Rows in set (0.01sec)
Look at the index length of the fixed-length character type char, which is 10'3 and 10'3'1 in NOT NULL and NULL, respectively.
(dg1) root@127.0.0.1 [mytest] > alter table table_key add index name_index (name)
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
(dg1) root@127.0.0.1 [mytest] > explain extended select * from table_key where name='zhangsan'
+-+-
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+-+-
| | 1 | SIMPLE | table_key | ref | name_index | name_index | 30 | const | 1 | 100.00 | Using index condition |
+-+-
Row in set, 1 warning (0.00 sec)
(dg1) root@127.0.0.1 [mytest] >
(dg1) root@127.0.0.1 [mytest] > alter table table_key modify name char (10)
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
(dg1) root@127.0.0.1 [mytest] > explain extended select * from table_key where name='zhangsan'
+-+-
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+-+-
| | 1 | SIMPLE | table_key | ref | name_index | name_index | 31 | const | 1 | 100.00 | Using index condition |
+-+-
Row in set, 1 warning (0.00 sec)
Look at the index length of the variable length character type varchar, which is 10'3'2 and 10'3'2'1 in NOT NULL and NULL, respectively.
(dg1) root@127.0.0.1 [mytest] > explain extended select * from table_key where address='shanghai'
+-+-
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+-+-
| | 1 | SIMPLE | table_key | ref | address_index | address_index | 32 | const | 1 | 100.00 | Using index condition |
+-+-
Row in set, 1 warning (0.01sec)
(dg1) root@127.0.0.1 [mytest] >
(dg1) root@127.0.0.1 [mytest] > alter table table_key modify address varchar (10)
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
(dg1) root@127.0.0.1 [mytest] > explain extended select * from table_key where address='shanghai'
+-+-
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+-+-
| | 1 | SIMPLE | table_key | ref | address_index | address_index | 33 | const | 1 | 100.00 | Using index condition |
+-+-
Row in set, 1 warning (0.00 sec)
(dg1) root@127.0.0.1 [mytest] >
Let's take a look at the key_len of the composite index, (just tested the GBK character set, the character set has been converted to GBK)
(dg1) root@127.0.0.1 [mytest] > desc table_key
+-+ +
| | Field | Type | Null | Key | Default | Extra | |
+-+ +
| | id | int (11) | NO | | NULL |
| | sid | bigint (20) | NO | | 0 | |
| | name | char (10) | NO | |
| | age | tinyint (4) | YES | | NULL |
| | sex | tinyint (4) | NO | | NULL |
| | address | varchar (10) | NO | MUL | |
+-+ +
Rows in set (0.01sec)
(dg1) root@127.0.0.1 [mytest] > alter table table_key drop index name_index
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
(dg1) root@127.0.0.1 [mytest] > alter table table_key drop index address_index
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
(dg1) root@127.0.0.1 [mytest] > alter table table_key add index name_address_index (name,address)
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
(dg1) root@127.0.0.1 [mytest] > explain extended select * from table_key where address='shanghai' and name='zhangsan'
+- -- +
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+- -- +
| | 1 | SIMPLE | table_key | ref | name_address_index | name_address_index | 42 | const,const | 1 | 100.00 | Using index condition |
+- -- +
Row in set, 1 warning (0.00 sec)
Look at the length of the composite index, because it can all be used in the composite index, so it is: 2 * (10) + 2 * (20) + 2 * 42. Let's allow the name field to be empty. Let's take a look.
(dg1) root@127.0.0.1 [mytest] > alter table table_key modify name char (10)
(dg1) root@127.0.0.1 [mytest] > explain extended select * from table_key where name='zhangsan' and address='shanghai'
+- -- +
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+- -- +
| | 1 | SIMPLE | table_key | ref | name_address_index | name_address_index | 43 | const,const | 1 | 100.00 | Using index condition |
+- -- +
Row in set, 1 warning (0.00 sec)
Look at the length of the composite index, because it can all be used in the composite index, so it is: 2 * (10) + 1 # 2 * (20) + 2 # 43
(dg1) root@127.0.0.1 [mytest] > explain extended select * from table_key where name='zhangsan'
+- -+
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+- -+
| | 1 | SIMPLE | table_key | ref | name_address_index | name_address_index | 21 | const | 1 | 100.00 | Using index condition |
+- -+
Row in set, 1 warning (0.00 sec)
So let's take a look at the part that uses the composite index: 2 * (10) + 1, set address to allow null, and then take a look.
(dg1) root@127.0.0.1 [mytest] > alter table table_key modify address varchar (10)
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
(dg1) root@127.0.0.1 [mytest] > explain extended select * from table_key where name='zhangsan' and address='shanghai'
+- -- +
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+- -- +
| | 1 | SIMPLE | table_key | ref | name_address_index | name_address_index | 44 | const,const | 1 | 100.00 | Using index condition |
+- -- +
Row in set, 1 warning (0.00 sec)
(dg1) root@127.0.0.1 [mytest] >
At this time, key_len=2* (10) + 1x 2* (10) + 2m 1m 44
Summary
1. Index length of integer type, floating point type, time type
The length of the NOT NULL= field itself
The field length of the NULL= field itself is + 1, because an empty tag is required, which takes up 1 byte
The field length of the datetime type in 5.6is 5 bytes.
two。 Character type
Varchr (n) variable length field and allow NULL = n * (utf8=3,gbk=2,latin1=1) + 1 (NULL) + 2
Varchr (n) variable length field and NULL = n * (utf8=3,gbk=2,latin1=1) + 2 is not allowed
Char (n) fixed field and allows NULL = n * (utf8=3,gbk=2,latin1=1) + 1 (NULL)
Char (n) fixed field and allow NULL = n * (utf8=3,gbk=2,latin1=1)
Variable length fields require an additional 2 bytes (VARCHAR values are saved with only the number of characters required, plus one byte to record the length (two bytes are used if the declared length of the column is greater than 255), so add 2 to the VARCAHR index length calculation, while fixed-length fields do not require additional bytes. Null requires an extra byte of space, so it's best not to have the index field NULL, because NULL makes statistics more complex and requires extra storage space. This conclusion has been confirmed here that the composite index has the characteristic of the leftmost prefix. If the composite index can be fully used, it is the sum of the index length of the composite index field, which can also be used to determine whether the composite index is partially used or all.
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.