在使用受益的查询时,应使用复合索引.一个如下所示的复合索引:
index( column_A, column_B, column_C )
将使用这些字段进行连接,过滤和有时选择的查询受益.它还将有益于使用该组合中最左侧列的子集的查询.所以上面的索引也将满足需要的查询
index( column_A, column_B, column_C ) index( column_A, column_B ) index( column_A )
但它不会(至少不直接,也许它可以帮助部分,如果没有更好的索引)帮助查询需要
index( column_A, column_C )
注意column_B是如何丢失的.
在您的原始示例中,两个维度的复合索引将主要有益于在两个维度或最左侧维度上查询的查询,而不是最右侧维度.如果你总是在查询两个维度,那么复合索引是要走的路,首先(最有可能)并不重要.
在使用受益的查询时,应使用复合索引.一个如下所示的复合索引:
index( column_A, column_B, column_C )
将使用这些字段进行连接,过滤和有时选择的查询受益.它还将有益于使用该组合中最左侧列的子集的查询.所以上面的索引也将满足需要的查询
index( column_A, column_B, column_C ) index( column_A, column_B ) index( column_A )
但它不会(至少不直接,也许它可以帮助部分,如果没有更好的索引)帮助查询需要
index( column_A, column_C )
注意column_B是如何丢失的.
在您的原始示例中,两个维度的复合索引将主要有益于在两个维度或最左侧维度上查询的查询,而不是最右侧维度.如果你总是在查询两个维度,那么复合索引是要走的路,首先(最有可能)并不重要.
想象一下,您有以下三个查询:
查询I:
SELECT * FROM homes WHERE `geolat`=42.9 AND `geolng`=36.4
查询II:
SELECT * FROM homes WHERE `geolat`=42.9
问题III:
SELECT * FROM homes WHERE `geolng`=36.4
如果每列有单独的索引,则所有三个查询都使用索引.在MySQL中,如果您有复合索引(geolat,geolng),则只有查询I和查询II(使用composit索引的第一部分)使用索引.在这种情况下,查询III需要全表搜索.
在手册的多列索引部分,清楚地解释了多列索引如何工作,所以我不想重新键入手册.
从MySQL参考手册页面:
多列索引可以视为一个排序数组,其中包含通过连接索引列的值创建的值.
如果对geolat和geolng列使用单独的索引,则表中有两个不同的索引可以独立搜索.
INDEX geolat ----------- VALUE RRN 36.4 1 36.4 8 36.6 2 37.8 3 37.8 12 41.4 4 INDEX geolng ----------- VALUE RRN 26.1 1 26.1 8 29.6 2 29.6 3 30.1 12 34.7 4
如果使用复合索引,则两列只有一个索引:
INDEX (geolat, geolng) ----------- VALUE RRN 36.4,26.1 1 36.4,26.1 8 36.6,29.6 2 37.8,29.6 3 37.8,30.1 12 41.4,34.7 4
RRN是相对记录号(简化,你可以说ID).前两个索引生成单独的,第三个索引是复合的.正如你所看到的,你可以根据geolng在geolat上进行搜索,因为它是由geolat索引的,但是可以通过geolat或"geolat AND geolng"进行搜索(因为geolng是二级索引).
另外,看看MySQL如何使用索引手册部分.
关于复合索引的作用可能存在误解.很多人认为,综合指数可以用来只要来优化搜索查询where条款涵盖了索引列,你的情况geolat和geolng.让我们深入研究:
我相信你家的坐标数据是随机小数,因此:
home_id geolat geolng 1 20.1243 50.4521 2 22.6456 51.1564 3 13.5464 45.4562 4 55.5642 166.5756 5 24.2624 27.4564 6 62.1564 24.2542 ...
因为geolat和geolng价值观几乎不会重演.上的一个综合指数geolat,并geolng会是这个样子:
index_id geolat geolng 1 20.1243 50.4521 2 20.1244 61.1564 3 20.1251 55.4562 4 20.1293 66.5756 5 20.1302 57.4564 6 20.1311 54.2542 ...
因此复合索引的第二列基本没用!使用复合索引的查询速度可能与geolat列中的索引类似.
正如Will所说,MySQL提供了空间扩展支持.空间点存储在单个列中,而不是两个单独的lat lng列中.空间索引可以应用于这样的列.但是,根据我的个人经验,效率可能会被高估.可能是空间索引不能解决二维问题,而只是使用二次分裂的R树加速搜索.
权衡是空间点消耗更多的内存,因为它使用八字节双精度数来存储坐标.如果我错了,请纠正我.
复合索引非常有用
0或更多"="子句,加上
最多一个范围条款.
复合索引无法处理两个范围.我在索引食谱中进一步讨论了这一点.
查找最近的 - 如果问题实际上是关于优化
WHERE geolat BETWEEN ??? AND ??? AND geolng BETWEEN ??? AND ???
那么没有索引可以真正处理这两个维度.
相反,人们必须"开箱即用".如果通过分区实现一个维度而通过仔细选择实现另一个维度PRIMARY KEY,那么对于非常大的lat/lng查找表,可以获得明显更好的效率.我的latlng博客详细介绍了如何在全球范围内实现"最近找到".它包括代码.
该PARTITIONs是纬度范围的条纹.该PRIMARY KEY故意经度启动,让有用的行很可能是在同一个块.一个存储的例程编排了杂乱的代码,用于做order by... limit...和围绕目标增长"正方形",直到你有足够的咖啡店(或其他).它还负责大圆计算和处理日期线和极点.
复合索引非常强大,因为它们:
加强结构完整性
启用对FILTERED ID进行排序
强制结构完整性
复合索引不仅仅是另一种索引; 他们可以通过强制完整性作为主键来为表提供NECESSARY结构.
Mysql的Innodb支持群集,以下示例说明了为什么可能需要复合索引.
要创建朋友的表(即社交网络),我们需要2列:user_id, friend_id.
表结构
user_id (medium_int) friend_id (medium_int) Primary Key -> (user_id, friend_id)
因此,主键(PK)是唯一的,通过创建复合PK,Innodb将自动检查user_id, friend_id添加新记录时是否存在重复项.这是预期的行为,因为没有用户应该具有多于1个记录(关系链接)friend_id = 2.
如果没有复合PK,我们可以使用代理键创建此模式:
user_friend_id user_id friend_id Primary Key -> (user_friend_id)
现在,每当添加新记录时,我们将不得不检查具有该组合的先前记录user_id, friend_id是否已经存在.
因此,复合索引可以强制执行结构完整性.
在过滤ID上启用分类
通过帖子的时间(时间戳或日期时间)对一组记录进行排序是很常见的.通常,这意味着在给定的ID上发布.这是一个例子
表User_Wall_Posts(想想Facebook的墙贴)
user_id (medium_int) timestamp (timestamp) author_id (medium_int) comment_post (text) Primary Key -> (user_id, timestamp, author_id)
我们想查询并查找所有帖子,user_id = 10并按timestamp(日期)对评论帖子进行排序.
SQL QUERY
SELECT * FROM User_Wall_Posts WHERE user_id = 10 ORDER BY timestamp DES
复合PK使Mysql能够使用索引对结果进行过滤和排序; Mysql不必使用临时文件或filesort来获取结果.如果没有复合键,这将是不可能的,并且会导致查询效率非常低.
因此,复合键非常强大,并且比"我想要搜索的简单问题更适合column_a, column_b所以我将使用复合键.对于我当前的数据库模式,我有与单键一样多的复合键.不要忽视复合键的用途!
京公网安备 11010802040832号 | 京ICP备19059560号-6 