主页 / dba / 问题 / 22091
Accepted
user4951
Asked: 2012-08-07 21:33:15 +0800 CST

复合索引如何帮助加快查询速度？

  • 772

假设我们在 mysql 表中有一堆列。

经纬度

假设我们制作了一个索引（纬度和经度）

那么，索引将首先按纬度排序，如果纬度相同，它将按经度排序。

应该会出现一些可疑的东西。纬度很少（如果有的话）完全相同。

假设我们想要找到 Lat、Long 都“在一个盒子里”的所有点，这些索引在地球上到底有多大用处。

注意：我试图了解索引是如何工作的。我并不是想势利地说复合索引很愚蠢。不必是完整的答案。任何指向都可以。

更重要的是，我怀疑一个查询只会使用 1 个索引，而查询使用 multilpe 索引的唯一方法是创建复合索引。

mysql index

2 个回答

  1. Best Answer

    在大多数情况下，索引是 B-Tree 结构（或某种）。有支持不同索引类型的 DBMS。

    当您谈论经度/纬度 PostgreSQL 及其 GIST 索引时，就会想到。除了 B-Tree 索引之外，Oracle 还有 Bitmap 索引。我确信 SQL Server 和 DB2 也有一些特殊的索引类型。然后是全文索引，使搜索文本非常有效。

    B-Tree 索引在查找特定值时非常有效——想想所有值都不同的主键索引。如果索引仅包含 PK 列（即它不是聚集索引），则通常通过特定 PK 值查找行需要不超过（大约）3-4 次 IO 操作（至少对于 Oracle）。2-3 找到索引块和一个额外的读取整行。如果索引包含额外的列，这样就不需要查找实际的表行，这会变得更有效率。术语是“覆盖索引”或“仅索引检索”。

    现在，对于“范围查找”（例如where foo > 42），索引非常有用，而且在大多数 DBMS 中，索引也可以根据谓词进行扫描。通常（同样这在很大程度上取决于 DBMS）这比直接查找效率稍低（同样这也取决于执行“仅索引检索”的能力）。

    我不知道有哪一款 BMS不能在查询中使用多个索引。考虑 PK 和 FK 列上的连接——根据数据分布，DBMS 可能会使用索引来查找父行（PK 查找）和子行（FK 查找）。

    但并非所有 DBMS 都可以在单个查询中对同一张表使用多个索引。

    毕竟是否使用索引取决于很多因素。

    我强烈推荐http://use-the-index-luke.com/，这是对跨所有主要 DBMS 建立索引的非常好的介绍。

    DBMS具体信息：

    甲骨文：http
    ://docs.oracle.com/cd/E11882_01/server.112/e25789/indexiot.htm PostgreSQL：http ://www.postgresql.org/docs/current/static/indexes.html

    • 4

  2. 值不需要精确即可使用索引。在您的特定示例中，范围扫描将限制检查的数据量。两个边界上的索引允许数据在两个轴上受到范围限制。

    范围扫描在概念上类似于您查看地图的方式。如果您想在纬度：38.889449 经度：-77.035232 处找到华盛顿纪念碑，您可以从纬度 30 到 40 度和经度 -70 到 -80 开始搜索。如果你只看 Latitude，你会看到比同时看两者时更多的地图。

    这是 Oracle 中的演示。

    > drop table t1
table T1 dropped.
> create table t1 as 
   (select dbms_random.value()*90 lat, dbms_random.value()*180 lon, level x 
      from dual connect by level<=5000)
table T1 created.
> create index x1 on t1 (lat)
index X1 created.
> set autotrace on explain
Autotrace Enabled
Displays the execution plan only.
> select * from t1 WHERE lat between 30 AND 40 AND lon BETWEEN 100 and 110
LAT LON X
--- --- -
30.3767657321169330960894098832402216675 104.010180757903737597756516124940719947 815 
30.5867860257718358281746146497630607937 105.59648614423201691420471610631375134 3308 
31.4193624839183326648532951557713565746 
...    
 25 rows selected 

Plan hash value: 3070918941

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |    25 |   975 |    12   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1   |    25 |   975 |    12   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | X1   |    23 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("LON">=100 AND "LON"<=110)
   2 - access("LAT">=30 AND "LAT"<=40)

Note
-----
   - dynamic sampling used for this statement (level=2)

> set autotrace off
Autotrace Disabled
> create index x2 on t1 (lat,lon)
index X2 created.
> set autotrace on explain
Autotrace Enabled
Displays the execution plan only.
> select * from t1 WHERE lat between 30 AND 40 AND lon BETWEEN 100 and 110
LAT LON X
--- --- -
30.3767657321169330960894098832402216675 104.010180757903737597756516124940719947 815 
30.5867860257718358281746146497630607937 105.59648614423201691420471610631375134 3308 
31.4193624839183326648532951557713565746 
...    
 25 rows selected 

Plan hash value: 1153438080

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |    25 |   975 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1   |    25 |   975 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | X2   |     1 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("LAT">=30 AND "LON">=100 AND "LAT"<=40 AND "LON"<=110)
       filter("LON">=100 AND "LON"<=110)

Note
-----
   - dynamic sampling used for this statement (level=2)

> set autotrace off
Autotrace Disabled
    • 3

相关问题