我在MySQL中处理大数据集时遇到了困难,正在尝试许多不同的索引方式。请问如果我同时声明多个索引有什么区别。
ALTER TABLE `db`.`test` ADD INDEX `someindex` (field1, field2, field3);
与其分别声明它们?
ALTER TABLE `db`.`test` ADD INDEX `f1` (field1), ADD INDEX `f2` (field2);
为什么要一起声明或分开声明?
我在MySQL中处理大数据集时遇到了困难,正在尝试许多不同的索引方式。请问如果我同时声明多个索引有什么区别。
ALTER TABLE `db`.`test` ADD INDEX `someindex` (field1, field2, field3);
与其分别声明它们?
ALTER TABLE `db`.`test` ADD INDEX `f1` (field1), ADD INDEX `f2` (field2);
为什么要一起声明或分开声明?
如果您有按名字排序再按姓氏排序的电话簿,则书的排序将帮助您解决上述情况#2和#3,但不适用于情况#1。
这解释了查找精确值的情况,但是如果您正在查找值的范围怎么办? 假设您想查找所有名字为John且姓氏以'S'开头的人(Smith,Saunders,Staunton,Sherman等)。 在每个姓氏下,John被排序为J,但是如果您想要所有名字为John且姓氏以S开头的人,则John再次分散。因此,您最终不得不扫描以“S”开头的所有姓氏名称。而如果按名字然后按姓氏组织电话簿,则可以找到所有John在一起,然后在John内,所有以S开头的姓氏将被分组在一起。
因此,在多列索引中,列的顺序确实很重要。一种类型的查询可能需要索引的某个列顺序。如果您有几种类型的查询,则可能需要几个索引来帮助它们,并且列的顺序也不同。
想要了解更多详情和示例,请参阅我的演示文稿《如何真正设计索引》。或者在视频上观看我的演示。
为了澄清何时使用单列索引与多列索引,请考虑使用电话簿通过姓氏和名字的组合查找一个人,例如“Sarah Smith”。
如果你有两本电话簿,一本按姓氏排序,另一本按名字排序,你可以在按姓氏排序的电话簿中搜索“Smith”,在按名字排序的电话簿中搜索“Sarah”,然后以某种方式找到两个结果的交集。MySQL有时会尝试使用索引合并算法来实现这一点。
如果存在按姓氏和名字排序的单个索引,就像真正的电话簿一样,那么搜索将会在该索引的子集中找到“Smiths”的结果集,然后在该子集内部高效地查找“Sarahs”,因为子集是按名字排序的。
数据库通常只能在每次查询中使用一个索引,因此假设您的“where”子句中包含所有三个列,则需要单个复合索引。
但是,复合索引只能从左到右部分使用,因此如果您有另一个仅针对field1的查询,那么复合索引仍将被使用。但是,对于仅在“where”子句中具有field2的查询,该索引无法使用,您需要一个仅在field2上或以field2开头的复合索引。
这在[MySQL文档]中有详细说明。