(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 | +----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+ 1 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 | +----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+ 1 row in set ( 0.00 sec) 看看mediumint类型索引长度，同样是 NOT NULL 和 NULL值的时候，分别是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 | +----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+ 1 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 |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+
1 row in set (0.00 sec)
(dg1)root@127.0.0.1 [mytest]>
2.浮点数类型
表结构
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类型的索引长度，NOT NULL和NULL的时候，分别是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 | +----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+ 1 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 | +----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+ 1 row in set ( 0.00 sec) 看看double类型的索引长度，NOT NULL和NULL的时候，分别是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 | +----+-------------+------------+------+---------------+----------+---------+-------+------+-------+ 1 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 | +----+-------------+------------+------+---------------+----------+---------+-------+------+-------+ 1 row in set ( 0.00 sec) (dg1)root@ 127.0 . 0.1 [mytest]>
3、看看时间类型
看看date类型的索引长度，在NOT NULL和NULL的时候，分别是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 | +----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+ 1 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 | +----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+ 1 row in set, 3 warnings ( 0.00 sec) (dg1)root@ 127.0 . 0.1 [mytest]> 在timestamp类型的时候索引长度，在NOT NULL 和 NULL的时候，分别是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 | +----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+ 1 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 | +----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+ 1 row in set, 3 warnings ( 0.00 sec) (dg1)root@ 127.0 . 0.1 [mytest]> (dg1)root@ 127.0 . 0.1 [mytest]> desc select * from table_key1 where c6 = ‘ 2015 - 05 - 06 11:23:38‘ ; +----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+ | 1 | SIMPLE | table_key1 | ref | c6_index | c6_index | 8 | const | 4 | Using index condition | +----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+ 1 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 c6 datetime ; 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_key1 where c6 = ‘ 2015 - 05 - 06 11:23:38‘ ; +----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+ | 1 | SIMPLE | table_key1 | ref | c6_index | c6_index | 9 | const | 4 | Using index condition | +----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+ 1 row in set, 3 warnings ( 0.00 sec)
4.字符类型
表结构，字符集是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 | | +---------+-------------+------+-----+---------+-------+ 6 rows in set ( 0.01 sec) 看看定长字符类型char的索引长度，在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 | +----+-------------+-----------+------+---------------+------------+---------+-------+------+----------+-----------------------+ 1 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 | +----+-------------+-----------+------+---------------+------------+---------+-------+------+----------+-----------------------+ 1 row in set, 1 warning ( 0.00 sec) 看看变长长字符类型varchar的索引长度，在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 | +----+-------------+-----------+------+---------------+---------------+---------+-------+------+----------+-----------------------+ 1 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 | +----+-------------+-----------+------+---------------+---------------+---------+-------+------+----------+-----------------------+ 1 row in set, 1 warning ( 0.00 sec) (dg1)root@ 127.0 . 0.1 [mytest]>
修改字符集为GBK，再来看看 (dg1)root@ 127.0 . 0.1 [mytest]> ALTER TABLE `table_key` DEFAULT CHARACTER SET=gbk COLLATE= gbk_chinese_ci; (dg1)root@ 127.0 . 0.1 [mytest]> ALTER database `mytest` DEFAULT CHARACTER SET=gbk COLLATE= gbk_chinese_ci; (dg1)root@ 127.0 . 0.1 [mytest]> show global variables like ‘ %char% ‘ ; +--------------------------+--------------------------------+ | Variable_name | Value | +--------------------------+--------------------------------+ | character_set_client | gbk | | character_set_connection | gbk | | character_set_database | gbk | | character_set_filesystem | binary | | character_set_results | gbk | | character_set_server | gbk | | character_set_system | utf8 | | character_sets_dir | /opt/app/mysql/share/charsets/ | +--------------------------+--------------------------------+ 8 rows in set ( 0.00 sec) 奇怪了，为什么还是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 | 33 | const | 1 | 100.00 | Using index condition | +----+-------------+-----------+------+---------------+---------------+---------+-------+------+----------+-----------------------+ 1 row in set, 1 warning ( 0.01 sec) (dg1)root@ 127.0 . 0.1 [mytest]> ALTER TABLE `table_key` MODIFY COLUMN `name` char ( 10 ) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL , DEFAULT CHARACTER SET=gbk COLLATE= gbk_chinese_ci; Query OK, 9 rows affected ( 0.04 sec) Records: 9 Duplicates: 0 Warnings: 0 (dg1)root@ 127.0 . 0.1 [mytest]> ALTER TABLE `table_key` MODIFY COLUMN `address` char ( 10 ) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL , DEFAULT CHARACTER SET=gbk COLLATE= gbk_chinese_ci; Query OK, 9 rows affected ( 0.03 sec) Records: 9 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 | 21 | const | 1 | 100.00 | Using index condition | +----+-------------+-----------+------+---------------+---------------+---------+-------+------+----------+-----------------------+ 1 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 address varchar( 10 ); Query OK, 9 rows affected ( 0.04 sec) Records: 9 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 | 23 | const | 1 | 100.00 | Using index condition | +----+-------------+-----------+------+---------------+---------------+---------+-------+------+----------+-----------------------+ 1 row in set, 1 warning ( 0.01 sec) (dg1)root@ 127.0 . 0.1 [mytest]> (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 | 20 | const | 1 | 100.00 | Using index condition | +----+-------------+-----------+------+---------------+------------+---------+-------+------+----------+-----------------------+ 1 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.00 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 | 21 | const | 1 | 100.00 | Using index condition | +----+-------------+-----------+------+---------------+------------+---------+-------+------+----------+-----------------------+ 1 row in set, 1 warning ( 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 | +----+-------------+-----------+------+--------------------+--------------------+---------+-------------+------+----------+-----------------------+ 1 row in set, 1 warning ( 0.00 sec) (dg1)root@ 127.0 . 0.1 [mytest]> (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 | +----+-------------+-----------+------+--------------------+--------------------+---------+-------------+------+----------+-----------------------+ 1 row in set, 1 warning ( 0.00 sec) (dg1)root@ 127.0 . 0.1 [mytest]> alter table table_key modify name char ( 10 ); Query OK, 0 rows affected ( 0.03 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_address_index | name_address_index | 21 | const | 1 | 100.00 | Using index condition | +----+-------------+-----------+------+--------------------+--------------------+---------+-------+------+----------+-----------------------+ 1 row in set, 1 warning ( 0.00 sec) (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 | +----+-------------+-----------+------+--------------------+--------------------+---------+-------------+------+----------+-----------------------+ 1 row in set, 1 warning ( 0.00 sec) (dg1)root@ 127.0 . 0.1 [mytest]>
总结：
1.整数类型，浮点数类型，时间类型的索引长度
NOT NULL=字段本身的字段长度
NULL=字段本身的字段长度+1，因为需要有是否为空的标记，这个标记需要占用1个字节
2.字符类型
varchr(n)变长字段且允许NULL = n * ( utf8= 3 ,gbk= 2 ,latin1= 1 )+ 1 (NULL)+ 2
varchr(n)变长字段且不允许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 )
变长字段需要额外的
查看更多关于MySQL执行计划explain的key_len解析的详细内容...
声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://haodehen.cn/did160823