一、 数据表staff
现有数据表staff,字符集utf8,并创建有一些字段单独索引及组合索引index_age_birth_salary包含三个字段:
sf_age tinyint DEFAULT NULL,
sf_birthday date DEFAULT NULL,
sf_salary int DEFAULT NULL,
CREATE TABLE `staff` (
`sf_id` int NOT NULL AUTO_INCREMENT,
`sf_name` varchar(10) DEFAULT NULL,
`sf_gender` tinyint(1) DEFAULT NULL COMMENT '0:女 1:男',
`sf_age` tinyint DEFAULT NULL,
`sf_birthday` date DEFAULT NULL,
`sf_email` varchar(20) DEFAULT NULL,
`sf_salary` int DEFAULT NULL,
`sf_phone` char(11) DEFAULT NULL,
`sf_dpt` varchar(20) DEFAULT NULL,
PRIMARY KEY (`sf_id`),
KEY `index_age` (`sf_age`),
KEY `index_salary` (`sf_salary`),
KEY `index_age_birth_salary` (`sf_age`,`sf_birthday`,`sf_salary`),
KEY `index_name` (`sf_name`),
KEY `index_phone` (`sf_phone`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8
现有数据如下
标注:表中数据较少,有时候虽然有索引,并且查询语句不会导致索引失效,但通过explain发现并没有走索引的情况,这是因为mysql预测到走全表扫描比索引快,而不使用索引,所以下面的例子是特意选一些会去走索引的查询语句。
推荐使用数据量大的表去分析索引使用情况
二、 key_len计算
执行explain查询语句:
EXPLAIN SELECT * FROM staff WHERE sf_age=18 AND sf_birthday='2021-07-12' AND sf_salary>3000;
以经验预判断一下,组合索引中三个字段索引都命中了,执行结果如下:
注意key_len=11,11表示用到的索引长度为11(单位byte),我们可以根据key_len来验证用到的索引字段:
key_len = 组合索引中,用到的每个索引字段所占字节之和
sf_age为tinyint占1一个字节,sf_birthday为DATA类型占3字节,sf_salary为int类型占4字节,另外三个字段都是default null,null标志位需要额外一个字节,所以总共是:
(1+1)+(3+1)+(4+1)=11
三、验证NULL占一个字节
为了证明null字段会比NOT NULL字段多占一个字节,(由于表建的不是很典型,随便找了个凑合用的)我们利用主键sf_id这个字段来试一下:
EXPLAIN SELECT sf_id FROM staff WHERE sf_id =10;
int字段的sf_id由于主键非空约束,该字段无需额外的一个字节来标志NULL,因此该字段为4字节,对应的key_len=4
四、varcahr与char的情况
字符串的情况就比较复杂,先看一下char(11)的字段
EXPLAIN SELECT * FROM staff WHERE sf_phone LIKE '223%';
在utf8编码下,中文字符占3字节、数字英文1字节,而key_len计算需要统一一下长度,因此不论中英文,都会按照一个字符3字节来计算,34=11字符x3 + 1(1是NULL 的标志位)
另外,如果字段是varchar并且可以为NULL的字段则需要额外两个或者三个字节(varchar要用1-2字节来存储字段长度,小于255的1字节,大于255的2字节),key_len计算时为了便于计算,统一采用2字节来存储字段长度,测试一下:
EXPLAIN SELECT * FROM staff WHERE sf_name LIKE 'mar%';
sf_name字段varchar(10) 占10x3 + 2 + 1 =33字节,10个字符utf8下占30字节,加上两个字节存储长度,以及一个存储标记NULL的,所以索引index_name索引长度是33
五、utf8mb4
如果数据表用的是utf8mb4编码(兼容性更强,可以存储emoji表情及复杂繁体字),则字符占4字节,注意区分