首页 > 代码库 > mysql 多列索引学习-经典实例
mysql 多列索引学习-经典实例
索引优化 ,b-tree
假设某个表有一个联合索引(c1,c2,c3,c4) 以下 只能使用该联合索引的c1,c2,c3部分
A. where c1 = x and c2 = x and c4>x and c3 = x
B. where c1 = x and c2 = x and c4=x order by c3
C. where c1 = x and c4 = x group by c3,c2
D. where c1 = ? and c5 = ? order by c2,c3
E. where c1 = ? and c2 = ? and c5=? order by c2,c3
实验:
#utf8 一个字符3个字节 , 注意：order by(索引能发挥都是要 按顺序查找， desc就用不上)
create table t6(
c1 char(1) not null default ‘‘,
c2 char(1) not null default ‘‘,
c3 char(1) not null default ‘‘,
c4 char(1) not null default ‘‘,
c5 char(1) not null default ‘‘,
key(c1,c2,c3,c4)
)engine myisam charset=utf8
#插入数据
insert into t6 values (‘a‘,‘b‘,‘c‘,‘d‘,‘e‘),(‘A‘,‘b‘,‘c‘,‘d‘,‘e‘),(‘a‘,‘B‘,‘c‘,‘d‘,‘e‘);
A-E都否使用索引？ 为什么
-------------------------------------- A 能用到 c1,c2,c3,c4 , mysql优化器会把A语句优化(不影响语意) where c1 = x and c2 = x and c3 = x and c4>x explain select * from t6 where c1=‘a‘ and c2=‘b‘ and c4>‘a‘ and c3=‘c‘\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t6 type: range possible_keys: c1 key: c1 key_len: 12 ref: NULL rows: 2 Extra: Using index condition 1 row in set (0.00 sec) key_len: 12 #代表4个索引全部用上( c1,c2,c3,c4 ) 4个索引 * 3字节 ------------------------
------------------------ B 只能用到 c1,c2, c3排序 explain select * from t6 where c1=‘a‘ and c2=‘b‘ and c4=‘d‘ order by c3\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t6 type: ref possible_keys: c1 key: c1 key_len: 6 ref: const,const rows: 2 Extra: Using index condition; Using where 1 row in set (0.00 sec) key_len: 12 #2个索引用上( c1,c2) 2个索引 * 3字节 ps:这里c3索引用在了排序上 可以通过下面来比较 explain select * from t6 where c1=‘a‘ and c2=‘b‘ and c4=‘d‘ order by c5\G 注意观察Extra : Using filesort ------------------------
------------------------ C 只能用到 c1 explain select * from t6 where c1 = ‘a‘ and c4 = ‘d‘ group by c3,c2\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t6 type: ref possible_keys: c1 key: c1 key_len: 3 ref: const rows: 2 Extra: Using index condition; Using where; Using temporary; Using filesort 1 row in set (0.00 sec) key_len: 12 #用上1个索引( c1) 1个索引 * 3字节 Extra： Using temporary->使用到临时表 详解 有group by语句一般要先按分组字段顺序排列,如果此字段没排序好，mysql内部会先用临时表排序 explain select * from t6 where c1 = ‘a‘ and c4 = ‘d‘ group by c2,c3\G 因为查找用到c1, 正好c2是顺序,c3 不会建立临时表 ----------------------
---------------------- D 只能用到 c1, c2,c3排序 explain select * from t6 where c1 = ‘a‘ and c5 = ‘e‘ order by c2,c3\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t6 type: ref possible_keys: c1 key: c1 key_len: 3 ref: const rows: 2 Extra: Using index condition; Using where 1 row in set (0.00 sec) key_len: 12 #用上1个索引( c1) 1个索引 * 3字节 Extra：发现没有用文件排序 , (c2,c3顺序)正好用上 ----------------------------
---------------------------- E 只能用到 c1,c2,c3 explain select * from t6 where c1 = ‘a‘ and c2=‘b‘ and c5 = ‘e‘ order by c2,c3\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t6 type: ref possible_keys: c1 key: c1 key_len: 6 ref: const,const rows: 2 Extra: Using index condition; Using where 1 row in set (0.00 sec) key_len: 12 #用上2个索引( c1,c2) 2个索引 * 3字节 Extra：发现没有用文件排序 , (c2,c3顺序)正好用上 倒过来: explain select * from t6 where c1 = ‘a‘ and c2=‘b‘ and c5 = ‘e‘ order by c3,c2\G Extra:没用到文件排序 ， 因为查找的时候 已经找到了c2 ,是一个常量 对比 explain select * from t6 where c1 = ‘a‘ and c5 = ‘e‘ order by c3,c2\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t6 type: ref possible_keys: c1 key: c1 key_len: 3 ref: const rows: 2 Extra: Using index condition; Using where; Using filesort 1 row in set (0.00 sec) Extra: 文件排序
mysql 多列索引学习-经典实例
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。