mysql多个字段建立组合索引时候,字段顺序可以随意,但最好是遵循一定顺序的,如索引(a,b,c)与(b,a,c)肯定不一样的,顺序不一样索引的效果也不一样,所以要计算其先后顺序。
如表mc_k12_wechat_user_info有如下字段
一、计算组合索引建立的顺序
1、常用的字段放在最前面
现在要建立组合索引(phone_number,provice),phone_number肯定是经常差的,要放在前边,provice不经常查,放在后边
2、等值条件尽量在前边
等值条件尽量在前边,在扫描的时候,找到的索引块都是有效数据。若非等值条件放在前边,那么需要进行索引跳跃扫描,或者范围扫描,这是就扫描了很多无效的索引,非等值条件放在后边可以,减少无效的索引扫描,提高查找效率。
3、离散值较高的字段往前放
现在要建立组合索引(phone_number,union_id,open_id)
SELECT
COUNT(DISTINCT phone_number)/COUNT(*) phone_number,
COUNT(DISTINCT union_id)/COUNT(*) union_id ,
COUNT(DISTINCT open_id)/COUNT(*) open_id
from mc_k12_wechat_user_info
结果如下,按照ji计算值由大到小的顺序建立组合索引,值越大表明该字段不为空索引命中的几率越大。
由计算结果可以看到 open_id > phone_number > union_id,组合索引建立的顺序就是(open_id,phone_number,union_id)
ALTER TABLE mc_k12_wechat_user_info INDEX idx_wechat_user_info (open_id,phone_number,union_id)
2、EXPLAIN 之key_len计算规则
各种数据类型计算规则如下:
char和varchar类型key_len计算公式:
varchr(N)变长字段且允许NULL = N * ( character set:utf8mb4为4字节,utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)
varchr(N)变长字段且不允许NULL = N * ( character set:utf8mb4为4字节,utf8=3,gbk=2,latin1=1)+2(变长字段)
char(N)固定字段且允许NULL = N * ( character set:utf8mb4为4字节,utf8=3,gbk=2,latin1=1)+1(NULL)
char(N)固定字段且允许NULL = N * ( character set:utf8mb4为4字节,utf8=3,gbk=2,latin1=1)
数值数据的key_len计算公式:
TINYINT允许NULL = 1 + 1(NULL)
TINYINT不允许NULL = 1
SMALLINT允许为NULL = 2+1(NULL)
SMALLINT不允许为NULL = 2
INT允许为NULL = 4+1(NULL)
INT不允许为NULL = 4
日期时间型的key_len计算:(针对mysql5.5及之前版本)
DATETIME允许为NULL = 8 + 1(NULL)
DATETIME不允许为NULL = 8
TIMESTAMP允许为NULL = 4 + 1(NULL)
TIMESTAMP不允许为NULL = 4
在MySQL5.6.4这个版本之后,TIME、DATETIME、TIMESTAMP这几种类型添加了对毫秒、微秒的支持。由于毫秒、微秒都不到1秒,所以也被称为小数秒,MySQL最多支持6位小数秒的精度,前三位标识毫秒,后三位标识微秒。
以datetime为例,一般情况下时间格式为YYYY-MM-DD HH:MM:SS,为了让我们的datetime类型支持小数秒,可以这么写:dateime(小数秒位数),其中小数秒位数可以在0、1、2、3、4、5、6中选择。比如DATETIME(0)表示精确到秒,DATETIME(3)表示精确到毫秒,DATETIME(5)表示精确到10微秒。如果你在选择TIME、DATETIME、TIMESTAMP这几种类型的时候添加了对小数秒的支持,那么所需的存储空间需要相应的扩大,保留不同的小数秒位数,那么增加的存储空间大小也不同,如下表:
计算索引长度时,datetime类型下,如果不使用小数秒,则只占用5个字节长度,datetime相当于是datetime(0),仅占用5字节空间,而datetime(1)或datetime(2)占用6字节、datetime(3)或datetime(4)占用7字节、datetime(5)或datetime(6)占用8字节。
3、举例说明索引idx_union_id的计算规则
EXPLAIN 查看执行计划
EXPLAIN SELECT * from mc_k12_wechat_user_info where union_id = 'oNC5b6PvqwyLCpbc8b1aPMLNrtgk'
看到key_len=194
查看表结构
desc mc_k12_wechat_user_info
可以看到union_id的字段类型为varchar,长度为64且不允许为空,表字符编码为utf8
所以key_len= varchar(64) * utf8(3) + 2 = 64 * 3 + 2 = 194
组合索引遵循索引最左原则,组合索引的key_len计算亦遵循此原则,以最左边的索引字段长度为计算准则
案例二
索引长度计算如下:(varchar50*4+2变长+1空值)+(varchar10*4+2变长)+(datetime(3)5+2)+(varchar6*4+2变长)=203+42+7+26=278
由此可看出idx_test的所有字段都走了索引。