多列索引实验
来源:互联网 发布:失业金数据与非农数据 编辑:程序博客网 时间:2024/06/05 19:33
多列索引经典题目:
http://www.zixue.it/thread-9218-1-4.html
假设某个表有一个联合索引(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=x and c5=x order by c2,c3
对于B: select * from t4 where c1=1 and c2=2 and c4=3 order by c3
c1 ,c2索引用上了,在c2用到索引的基础上,c3是排好序的,因此不用额外排序.
而c4没发挥作用.
对于 C: 只用到c1索引,因为group by c3,c2的顺序无法利用c2,c3索引
D语句: C1确定的基础上,c2是有序的,C2之下C3是有序的,因此c2,c3发挥的排序的作用.
因此,没用到filesort
http://www.zixue.it/thread-9218-1-4.html
假设某个表有一个联合索引(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=x and c5=x order by c2,c3
E where c1=x and c2=x and c5=? order by c2,c3
<span style="font-size:18px;">create table t4 (c1 tinyint(1) not null default 0,c2 tinyint(1) not null default 0,c3 tinyint(1) not null default 0,c4 tinyint(1) not null default 0,c5 tinyint(1) not null default 0,index c1234(c1,c2,c3,c4));insert into t4 values (1,3,5,6,7),(2,3,9,8,3),(4,3,2,7,5);</span>
对于A:
c1=x and c2=x and c4>x and c3=x <==等价==> c1=x and c2=x and c3=x and c4>x
因此 c1,c2,c3,c4都能用上. 如下:
<span style="font-size:18px;">explain select * from t4 where c1=1 and c2=2 and c4>3 and c3=3 \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t4 type: rangepossible_keys: c1234 key: c1234 key_len: 4 #可以看出c1,c2,c3,c4索引都用上 ref: NULL rows: 1 Extra: Using where </span>
对于B: select * from t4 where c1=1 and c2=2 and c4=3 order by c3
c1 ,c2索引用上了,在c2用到索引的基础上,c3是排好序的,因此不用额外排序.
而c4没发挥作用.
注:因为c3是排好序的,就好像搭木板过河一样,c3已经排好序,就好比如搭了c1和c2的木板,但是c3已经默认有的了(其实是没有的,就好比如人家默认是做好的了,不提供你材料),所以c4就没有办法搭建了
<span style="font-size:18px;">explain select * from t4 where c1=1 and c2=2 and c4=3 order by c3 \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t4 type: refpossible_keys: c1234 key: c1234 key_len: 2 ref: const,const rows: 1 Extra: Using where1 row in set (0.00 sec)mysql> explain select * from t4 where c1=1 and c2=2 and c4=3 order by c5 \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t4 type: refpossible_keys: c1234 key: c1234 key_len: 2 ref: const,const rows: 1 Extra: Using where; Using filesort1 row in set (0.00 sec)</span>
对于 C: 只用到c1索引,因为group by c3,c2的顺序无法利用c2,c3索引
注:group by 的原理:先对分组字段进行排序(order by),然后再进行分组操作,即group by 等价于order by c2, c3
<span style="font-size:18px;">explain select * from t4 where c1=1 and c4=2 group by c3,c2 \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t4 type: refpossible_keys: c1234 key: c1234 key_len: 1 #只用到c1,因为先用c3后用c2分组,导致c2,c3索引没发挥作用 ref: const rows: 1 Extra: Using where; Using temporary; Using filesort1 row in set (0.00 sec)</span>
<span style="font-size:18px;">explain select * from t4 where c1=1 and c4=2 group by c2,c3 \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t4 type: refpossible_keys: c1234 key: c1234 key_len: 1 ref: const rows: 1 Extra: Using where1 row in set (0.00 sec)</span>
D语句: C1确定的基础上,c2是有序的,C2之下C3是有序的,因此c2,c3发挥的排序的作用.
因此,没用到filesort
<span style="font-size:18px;">explain select * from t4 where c1=1 and c5=2 order by c2,c3 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t4 type: refpossible_keys: c1234 key: c1234 key_len: 1 ref: const rows: 1 Extra: Using where1 row in set (0.00 sec)</span>
E: 这一句等价与 elect * from t4 where c1=1 and c2=3 and c5=2 order by c2, c3;
因为c2的值既是固定的,参与排序时并不考虑
注:因为c1和c2是固定值,那么order by c2(一个常量), c3,这里c2已经是一个常量了,所以不需要做排序
即order by c2, c3 等价于order by c3
<span style="font-size:18px;">explain select * from t4 where c1=1 and c2=3 and c5=2 order by c2,c3 \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t4 type: refpossible_keys: c1234 key: c1234 key_len: 2 ref: const,const rows: 1 Extra: Using where1 row in set (0.00 sec)</span>
0 0
- 多列索引实验
- 多列索引实验
- 多列索引
- Mysql多列索引
- 多列索引
- MySQL多列索引
- mysql多列索引
- 单列索引和多列索引
- 单列索引和多列索引
- mysql 单列索引与多列索引
- 单列索引和多列索引
- 正确理解Mysql的列索引和多列索引
- 正确理解Mysql的列索引和多列索引
- 正确理解MySQL中列索引和多列索引
- Mysql的列索引和多列索引
- 正确理解Mysql的列索引和多列索引
- 正确理解Mysql的列索引和多列索引
- mysql 多列索引疑问
- DES 算法详述
- Ubuntu 中sendmail 的安装、配置与发送邮件的具体实现
- 数据结构与算法——简易通讯录
- 字符串处理函数的实现
- DirectX9 顶点格式
- 多列索引实验
- 统计字符数判断一个由a~z这26个字符组成的字符串中哪个字符出现的次数最多。
- eclipse: building cdt
- ECB CBC and 3DES
- 黑马程度员——OC语言—— 内存管理原则
- Linux button按键驱动 多次中断控制相应LED灯亮灭闪
- UILabel添加点击事件
- struts2学习之访问Servlet对象的两种实现方式
- java学习路线2