什么时候应该使用复合索引？什么时候应该使用复合索引？(When should I use a com

1. 我什么时候应该在数据库中使用复合指数？
2. 什么是使用综合指数）的表现衍生物？
3. 为什么要使用使用复合指数？

例如，我有一个homes表：

CREATE TABLE IF NOT EXISTS `homes` (
  `home_id` int(10) unsigned NOT NULL auto_increment,
  `sqft` smallint(5) unsigned NOT NULL,
  `year_built` smallint(5) unsigned NOT NULL,
  `geolat` decimal(10,6) default NULL,
  `geolng` decimal(10,6) default NULL,
  PRIMARY KEY  (`home_id`),
  KEY `geolat` (`geolat`),
  KEY `geolng` (`geolng`),
) ENGINE=InnoDB  ;

是否有意义，我使用的复合指数都geolat和geolng ，使得：

我更换：

  KEY `geolat` (`geolat`),
  KEY `geolng` (`geolng`),

有：

KEY `geolat_geolng` (`geolat`, `geolng`)

如果是这样：

• 为什么？
• 什么是使用综合指数）的表现衍生物？

更新：

由于很多人都表示这完全取决于我进行查询，下面是执行的最常见的查询：

SELECT * FROM homes
WHERE geolat BETWEEN ??? AND ???
AND geolng BETWEEN ??? AND ???

更新2：

用下面的数据库模式：

CREATE TABLE IF NOT EXISTS `homes` (
  `home_id` int(10) unsigned NOT NULL auto_increment,
  `primary_photo_group_id` int(10) unsigned NOT NULL default '0',
  `customer_id` bigint(20) unsigned NOT NULL,
  `account_type_id` int(11) NOT NULL,
  `address` varchar(128) collate utf8_unicode_ci NOT NULL,
  `city` varchar(64) collate utf8_unicode_ci NOT NULL,
  `state` varchar(2) collate utf8_unicode_ci NOT NULL,
  `zip` mediumint(8) unsigned NOT NULL,
  `price` mediumint(8) unsigned NOT NULL,
  `sqft` smallint(5) unsigned NOT NULL,
  `year_built` smallint(5) unsigned NOT NULL,
  `num_of_beds` tinyint(3) unsigned NOT NULL,
  `num_of_baths` decimal(3,1) unsigned NOT NULL,
  `num_of_floors` tinyint(3) unsigned NOT NULL,
  `description` text collate utf8_unicode_ci,
  `geolat` decimal(10,6) default NULL,
  `geolng` decimal(10,6) default NULL,
  `display_status` tinyint(1) NOT NULL,
  `date_listed` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `contact_email` varchar(100) collate utf8_unicode_ci NOT NULL,
  `contact_phone_number` varchar(15) collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`home_id`),
  KEY `customer_id` (`customer_id`),
  KEY `city` (`city`),
  KEY `num_of_beds` (`num_of_beds`),
  KEY `num_of_baths` (`num_of_baths`),
  KEY `geolat` (`geolat`),
  KEY `geolng` (`geolng`),
  KEY `account_type_id` (`account_type_id`),
  KEY `display_status` (`display_status`),
  KEY `sqft` (`sqft`),
  KEY `price` (`price`),
  KEY `primary_photo_group_id` (`primary_photo_group_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=8 ;

使用下面的SQL：

EXPLAIN SELECT  homes.home_id,
                    address,
                    city,
                    state,
                    zip,
                    price,
                    sqft,
                    year_built,
                    account_type_id,
                    num_of_beds,
                    num_of_baths,
                    geolat,
                    geolng,
                    photo_id,
                    photo_url_dir
            FROM homes
            LEFT OUTER JOIN home_photos ON homes.home_id = home_photos.home_id
                AND homes.primary_photo_group_id = home_photos.home_photo_group_id
                AND home_photos.home_photo_type_id = 2
            WHERE homes.display_status = true
            AND homes.geolat BETWEEN -100 AND 100
            AND homes.geolng BETWEEN -100 AND 100

EXPLAIN的回报：

id  select_type  table        type  possible_keys                                    key                  key_len  ref     rows  Extra
----------------------------------------------------------------------------------------------------------
1   SIMPLE       homes        ref   geolat,geolng,display_status                     display_status       1        const   2     Using where
1  SIMPLE        home_photos  ref   home_id,home_photo_type_id,home_photo_group_id   home_photo_group_id  4        homes.primary_photo_group_id   4  

我不太明白如何读取EXPLAIN命令。 这看起来好还是坏。 现在，我不使用geolat和geolng一个综合指数。 我可以做？

Answer 1:

当您使用的是从中受益的查询，您应该使用一个综合指数。 复合索引，看起来像这样：

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是如何丢失的。

在你原来的例子，两个维度的复合指数将主要通过自身受益，通过本身的两个维度或最左边的维度查询查询，但不是最右边的维度。 如果你总是在询问两个维度，一个综合指数是走，其实并不重要，其是第一个（最有可能）的方式。

Answer 2:

想象一下，您有以下三个查询：

查询我：

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 ），只查询我和查询II（它是使用组合大指数的第一部分）使用索引。 在这种情况下，查询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）。 前两个指数产生单独和第三索引是复合材料。 正如你可以看到你可以搜索基于复合之一，因为它是由geolat索引geolng，但是它可以通过geolat或“geolat和geolng”（因为geolng是第二级指数）进行查询。

另外，看看MySQL如何使用索引手册中。

Answer 3:

有可能是一个什么指数做误解。 很多人认为，综合指数可以用来只要来优化搜索查询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列。

正如威尔所说，MySQL提供了空间扩展的支持。 一种空间点被存储在单个列中，而不是两个单独的lat lng列。 空间索引可以应用到这样的列。 然而，效率可根据我个人的经验被高估。 这可能是因为空间索引不能解决二维问题，但仅仅是加快了使用搜索R-树与二次分裂 。

权衡的是，空间点占用更多的内存 ，因为它使用了八个字节双精度数存储的坐标。 纠正我，如果我错了。

Answer 4:

复合索引是有用

• 0以上“=”条款，加
• 最多一个范围条款。

组合索引不能处理两个范围。 我在进一步讨论这个指数食谱 。

查找最近的 -如果真正的问题是关于优化

WHERE geolat BETWEEN ??? AND ???
  AND geolng BETWEEN ??? AND ???

然后没有索引能真正处理两个维度。

相反，人们必须“跳出思维的”。 如果一个维度是通过分区来实现，另一种是实现通过仔细挑选PRIMARY KEY ，可以得到更好的显著效率纬度/经度的查找非常大的表。 我的经纬度博客进入如何实现“查找最近”地球上的细节。 它包含的代码。

该PARTITIONs是纬度范围的条纹。 在PRIMARY KEY故意经度启动，让有用的行很可能是在同一个块。 存储日常编排乱码做order by... limit...并为目标周围越来越多的“广场”，直到你有足够的咖啡厅（或其他）。 它也负责大圆计算和处理国际日期变更线和电线杆。

Answer 5:

复合索引，因为它们非常强大：

• 强制结构的完整性
• 启用过滤后的ID进行排序

ENFORCE结构完整性

复合索引并不仅仅是另一种类型的索引; 他们可以通过实施诚信作为主键提供必要的结构表。

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已经不存在。

这样，复合索引可以实施结构的完整性。

启动排序上的FILTERED 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查询

SELECT * FROM User_Wall_Posts WHERE user_id = 10 ORDER BY timestamp DES

复合PK使MySQL来过滤和利用索引的结果进行排序; MySQL将不必使用临时文件或文件排序，以获取结果。 如果没有组合键，这是不可能的，并会造成一个非常低效的查询。

因此，组合键都非常强大，适合比“我要寻找简单的问题更column_a, column_b所以我将使用组合键。对于我目前的数据库架构，我有同样多的组合键为单键。唐“T忽视复合键的使用！

Answer 6:

没有黑与白，一刀切的答案。

你应该用一个综合指数，当你的查询工作负载将从一个受益。

您需要配置您的查询工作负载，以确定这一点。

复合索引进场时，查询可以完全从该索引来满足。

UPDATE（响应编辑张贴的问题）：如果您选择*从表中可以使用的综合指数，也可能不是。 您将需要运行EXPLAIN PLAN是肯定的。

Answer 7:

要做到空间搜索，你需要一个R树算法，它允许很快搜索的地理区域。 正是你需要这份工作是什么。

某些数据库有内置的空间索引。快速谷歌搜索结果显示，MySQL的5拥有它们（这看你的SQL我猜你使用MySQL）。

Answer 8:

当你要优化综合指数可能是有用的group by条款（本文取http://dev.mysql.com/doc/refman/5.0/en/group-by-optimization.html ）。 请注意：

用于使用索引GROUP BY的最重要的先决条件是所有GROUP BY列从相同的索引引用的属性，并且该索引存储其在键顺序（例如，这是一个BTREE索引，而不是一个散列索引）

Answer 9:

我和@米奇，完全取决于您的查询。 幸运的是，你可以创建并随时删除索引，你可以在前面加上EXPLAIN关键字您的查询，看是否查询分析器使用索引。

如果您要查找的确切经纬度/长对这个指数很可能是有意义的。 但是，你很可能会寻找一个特定地点的一定距离内的家园，让你的查询会是这个样子（见源 ）：

select *, sqrt(  pow(h2.geolat - h1.geolat,  2) 
               + pow(h2.geolng - h1.geolng, 2) ) as distance
from homes h1, homes h2
where h1.home_id = 12345 and h2.home_id != h1.home_id
order by distance

而该指数很可能不会在所有帮助。 对于地理空间查询，你需要像这样 。

更新：此查询：

SELECT * FROM homes
WHERE geolat BETWEEN ??? AND ???
AND geolng BETWEEN ??? AND ???

查询分析器可以单独使用上geolat索引，或单独geolng，或者可能是两个索引的索引。 我不认为它会用一个综合指数。 但它很容易尝试每种排列在真实数据集，然后（一）看到EXPLAIN告诉你和（b）测量查询确实需要时间。

文章来源: When should I use a composite index?