當(dāng)用Explain查看SQL的執(zhí)行計(jì)劃時(shí)，里面有列顯示了 key_len 的值，根據(jù)這個(gè)值可以判斷索引的長(zhǎng)度，在組合索引里面可以更清楚的了解到了哪部分字段使用到了索引。下面演示中，表結(jié)構(gòu)的合理性這邊暫且不說(shuō)，只是證明一下索引長(zhǎng)度的計(jì)算方法。目前大部分博文是字符類型的索引長(zhǎng)度計(jì)算方法，下面列舉幾個(gè)類型的索引長(zhǎng)度計(jì)算方法：
1、整數(shù)類型
(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.01 sec)
(dg1)root@127.0.0.1 [mytest]>create index age_index on table_key (age);
來(lái)看看tinyint類型的索引長(zhǎng)度，在NOT NULL 和 NULL 的時(shí)候 分別是1和2,tinyint字段長(zhǎng)度為1，因?yàn)镹ULL 需要額外一個(gè)字節(jié)標(biāo)記為空
(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]>
看看bigint類型的索引長(zhǎng)度，同樣是 NOT NULL 和 NULL值的時(shí)候，分別是8和9,聰明的你應(yīng)該知道了，bigint長(zhǎng)度為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)
看看smallint類型索引長(zhǎng)度，同樣是 NOT NULL 和 NULL值的時(shí)候，分別是2和3 smallint長(zhǎng)度為2，允許為空需要一個(gè)字節(jié)標(biāo)記
(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)
看看mediumint類型索引長(zhǎng)度，同樣是 NOT NULL 和 NULL值的時(shí)候，分別是3和4
(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]>
整數(shù)類型索引長(zhǎng)度跟字段長(zhǎng)度有關(guān)，如果允許為空，需要額外一個(gè)字節(jié)去標(biāo)記為空
2.浮點(diǎn)數(shù)類型
表結(jié)構(gòu)
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`)
)
看看float類型的索引長(zhǎng)度，NOT NULL和NULL的時(shí)候，分別是4和5
(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)
看看double類型的索引長(zhǎng)度，NOT NULL和NULL的時(shí)候，分別是8和9
(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、看看時(shí)間類型
看看date類型的索引長(zhǎng)度，在NOT NULL和NULL的時(shí)候，分別是3和4
(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]>
在timestamp類型的時(shí)候索引長(zhǎng)度，在NOT NULL 和 NULL的時(shí)候，分別是4和5
(dg1)root@127.0.0.1 [mytest]> desc select * from table_key1 where c5 = '2015-05-06 11:23: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 110:23:21';
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
| 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]>
##############################在大家認(rèn)識(shí)里datetime是八個(gè)字節(jié)的長(zhǎng)度，下面就來(lái)看看，是不是真的這樣
(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:10: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:10: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)
顛覆了我們認(rèn)識(shí)，datetime不是8個(gè)字節(jié)么，下面來(lái)看一下MySQL的版本，沒(méi)錯(cuò)MySQL5.6是datetime長(zhǎng)度是5個(gè)字節(jié)
(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]>
小結(jié)：在MySQL5.6版本，是否還得使用timestamp類型應(yīng)該是仁者見(jiàn)仁智者見(jiàn)智的問(wèn)題了，datetime是五個(gè)字節(jié)，timestamp范圍比較窄（1970-2037年），不排除后續(xù)版本會(huì)修改其范圍值
4.字符類型
表結(jié)構(gòu)，字符集是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.01 sec)
看看定長(zhǎng)字符類型char的索引長(zhǎng)度，在NOT NULL 和NULL中分別為10*3和10*3+1
(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)
看看變長(zhǎng)長(zhǎng)字符類型varchar的索引長(zhǎng)度，在NOT NULL 和NULL中分別為10*3+2和10*3+2+1
(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.01 sec)
(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]>
來(lái)看看復(fù)合索引的key_len,（剛才測(cè)試GBK字符集，字符集轉(zhuǎn)換成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.01 sec)
(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)
看看復(fù)合索引的長(zhǎng)度，因?yàn)槟苋渴褂玫浇M合索引，所以是：2*（10）+2*（20）+2=42,下面將name字段允許為空，再來(lái)看看
(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)
看看復(fù)合索引的長(zhǎng)度，因?yàn)槟苋渴褂玫浇M合索引，所以是：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)
那么我們來(lái)看看部分使用復(fù)合索引：2*（10）+1,將address設(shè)置為允許為空，再來(lái)看看
(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]>
這時(shí)候key_len=2*（10）+1+2*（10）+2+1=44
總結(jié)
1.整數(shù)類型，浮點(diǎn)數(shù)類型，時(shí)間類型的索引長(zhǎng)度
NOT NULL=字段本身的字段長(zhǎng)度
NULL=字段本身的字段長(zhǎng)度+1，因?yàn)樾枰惺欠駷榭盏臉?biāo)記，這個(gè)標(biāo)記需要占用1個(gè)字節(jié)
datetime類型在5.6中字段長(zhǎng)度是5個(gè)字節(jié)
2.字符類型
varchr(n)變長(zhǎng)字段且允許NULL = n * ( utf8=3,gbk=2,latin1=1)+1(NULL)+2
varchr(n)變長(zhǎng)字段且不允許NULL = n * ( utf8=3,gbk=2,latin1=1)+2
char(n)固定字段且允許NULL = n * ( utf8=3,gbk=2,latin1=1)+1(NULL)
char(n)固定字段且允許NULL = n * ( utf8=3,gbk=2,latin1=1)
變長(zhǎng)字段需要額外的2個(gè)字節(jié)（VARCHAR值保存時(shí)只保存需要的字符數(shù)，另加一個(gè)字節(jié)來(lái)記錄長(zhǎng)度(如果列聲明的長(zhǎng)度超過(guò)255，則使用兩個(gè)字節(jié))，所以VARCAHR索引長(zhǎng)度計(jì)算時(shí)候要加2），固定長(zhǎng)度字段不需要額外的字節(jié)。而null都需要1個(gè)字節(jié)的額外空間,所以索引字段最好不要為NULL，因?yàn)镹ULL讓統(tǒng)計(jì)更加復(fù)雜，并且需要額外的存儲(chǔ)空間。這個(gè)結(jié)論在此得到了證實(shí)，復(fù)合索引有最左前綴的特性，如果復(fù)合索引能全部使用上，則是復(fù)合索引字段的索引長(zhǎng)度之和，這也可以用來(lái)判定復(fù)合索引是否部分使用，還是全部使用。
