mysql 复合索引和单列索引优缺点及什么情况下使用

[sunshinexrain]

mysql复合索引，可以对多列建立索引，原则是“最左前缀”
问题：

1. 为什么是“最左前缀”？

比如字段a，b，c建立复合索引相当于a，ab，abc建立3个索引，为什么不能建立bc，c这2个索引？



2. sql查询时，只能使用一个最优索引？
比如 select * from tableName where a=1 and b=2 and c=3
2种情况下

1） a，b，c 建复合索引
2） a，b，c 各自建单列索引

有啥区别？


3. mysql5.2以上不是有组合索引的功能了吗？如果有多个单列索引，mysql也能像oracle一样使用多个索引组合使用

这个在以前肯定是只能使用一个最优索引，不知道对不对？

[ACMAIN_CHM]

[Quote]1. 为什么是“最左前缀”？

比如字段a，b，c建立复合索引相当于a，ab，abc建立3个索引，为什么不能建立bc，c这2个索引？
[/Quote]
where a=1 and b=4 and c=10
可以利用到索引 (a,b,c)
where a=1
可以利用到索引 (a,b,c)
where b=5
无法利用索引 (a,b,c)

[ACMAIN_CHM]

[Quote]2. sql查询时，只能使用一个最优索引？
比如 select * from tableName where a=1 and b=2 and c=3
2种情况下

1） a，b，c 建复合索引
2） a，b，c 各自建单列索引

有啥区别？[/Quote]

） a，b，c 建复合索引 可以被使用。
2） a，b，c 各自建单列索引 ，只能使用其中的一个索引。

[ACMAIN_CHM]

[Quote]3. mysql5.2以上不是有组合索引的功能了吗？如果有多个单列索引，mysql也能像oracle一样使用多个索引组合使用
这个在以前肯定是只能使用一个最优索引，不知道对不对？[/Quote]
是的，可以针对索引的结果进行merge操作。

建议参考一下MYSQL手册中关于优化的部分说明。
MySQL官方文档 http://dev.mysql.com/doc/refman/5.1/zh/index.html

[sunshinexrain]

[Quote=引用 1 楼 acmain_chm 的回复:]
引用1. 为什么是“最左前缀”？

    比如字段a，b，c建立复合索引相当于a，ab，abc建立3个索引，为什么不能建立bc，c这2个索引？

where a=1 and b=4 and c=10
可以利用到索引 (a,b,c)
where a=1
可以利用到索引 (a,b,c)
where b=5
无法利用索引 (a,b,c)

[/Quote]

对，我知道，但我想知道原理，应该是跟索引的存储结构有关吧，mysql 索引使用B-树结构
才会不能建立b,bc,c索引？

利用索引（a,b,c），具体过程是找索引a，找到一批数据，在这批数据上利用索引b，又过滤一批，最后利用索引c，查询出最后的数据？

[ACMAIN_CHM]

假设数据 表T (a,b,c) rowid 为物理位置
rowid a b c
(1) 1 1 1
(2) 2 1 13
(3) 2 2 14
(4) 1 3 3
(5) 2 3 12
(6) 1 2 5
(7) 2 3 9
(8) 1 2 2
(9) 1 3 6
(10) 2 2 11
(11) 2 2 8
(12) 1 1 7
(13) 2 3 15
(14) 1 1 4
(15) 2 1 10

当你创建一个索引 create index xxx on t(a,b), 则索引文件逻辑上等同于如下
a b rowid
1 1 1
1 1 12
1 1 14
1 2 6
1 2 8
1 3 4
1 3 9
2 1 2
2 1 15
2 2 3
2 2 10
2 2 11
2 3 5
2 3 7
2 3 13

当select * from T where a=1 and b=3 的时候， 数据库系统可以直接从索引文件中直接二分法找到A=1的记录，然后再B=3的记录。
但如果你 where b=3 则你需要遍历这个索引表的全部才行！


先读几遍《数据库系统概论（第四版）》 王珊 萨师煊 高等教育出版社 (掌握基础知识和概念)