MySQL是否在创建新索引时使用现有索引? [英] Does MySQL use existing indexes on creating new indexes?

问题描述

我有一张有数百万条记录的大桌子.

I have a large table with millions of records.

Table `price`
------------
id 
product 
site 
value

该表是全新的，并且没有创建索引.

The table is brand new, and there are no indexes created.

然后我通过以下查询发出了创建新索引的请求:

I then issued a request for new index creation with the following query:

CREATE INDEX ix_price_site_product_value_id ON price (site, product, value, id);

这花了很长时间，上一次我因为机器而检查了5000多秒.

This took long long time, last time I was checking ran for 5000+ seconds, because of the machine.

我想知道是否发布另一个索引创建，它会在流程计算中使用现有索引吗?如果可以的话，是什么形式?

I am wondering if I issue another index creation, will it use the existing index in the process calculation? If so in what form?

下一步运行查询1:

CREATE INDEX ix_price_product_value_id ON price (product, value, id);

下一步运行查询2:

CREATE INDEX ix_price_value_id ON price (value, id);

推荐答案

如果您使用的是MySQL 5.1版和InnoDB存储引擎，则可能要使用

If you're using MySQL version 5.1, and the InnoDB storage engine, you may want to use the InnoDB Plugin 1.0, which supports a new feature called Fast Index Creation. This allows the storage engine to create indexes without copying the contents of the entire table.

InnoDB插件概述:

Overview of the InnoDB Plugin:

从5.1版开始，MySQL AB提倡可插拔"存储引擎体系结构的思想，该体系结构允许将多个存储引擎添加到MySQL.但是，当前，大多数用户仅访问由MySQL AB分发并链接到二进制(可执行)发行版的存储引擎.

Starting with version 5.1, MySQL AB has promoted the idea of a "pluggable" storage engine architecture, which permits multiple storage engines to be added to MySQL. Currently, however, most users have accessed only those storage engines that are distributed by MySQL AB, and are linked into the binary (executable) releases.

自2001年以来，MySQL AB随其发行版(源代码和二进制文件)一起分发了InnoDB事务存储引擎.从MySQL 5.1版开始，用户可以换出一个版本的InnoDB并使用另一个版本.

Since 2001, MySQL AB has distributed the InnoDB transactional storage engine with its releases (both source and binary). Beginning with MySQL version 5.1, it is possible for users to swap out one version of InnoDB and use another.

来源: InnoDB简介插件

快速索引创建概述:

在MySQL最高为5.0的版本中，如果表中有很多行，则在具有现有数据的表上添加或删除索引会非常慢. CREATE INDEX和DROP INDEX命令通过创建一个新的空表来工作，该表定义了所请求的索引集.然后，它将现有行一张一张地复制到新表中，并随即更新索引.以这种方式将项插入索引中，即不对键值进行排序，这需要对索引节点进行随机访问，并且远非最佳.复制原始表中的所有行后，将删除旧表，并将副本重命名为原始表的名称.

In MySQL versions up to 5.0, adding or dropping an index on a table with existing data can be very slow if the table has many rows. The CREATE INDEX and DROP INDEX commands work by creating a new, empty table defined with the requested set of indexes. It then copies the existing rows to the new table one-by-one, updating the indexes as it goes. Inserting entries into the indexes in this fashion, where the key values are not sorted, requires random access to the index nodes, and is far from optimal. After all rows from the original table are copied, the old table is dropped and the copy is renamed with the name of the original table.

从5.1版开始，MySQL允许存储引擎创建或删除索引，而无需复制整个表的内容.但是，MySQL 5.1版中的标准内置InnoDB没有利用此功能.但是，使用InnoDB插件，在大多数情况下，用户可以比以前的版本更高效地添加和删除索引.

Beginning with version 5.1, MySQL allows a storage engine to create or drop indexes without copying the contents of the entire table. The standard built-in InnoDB in MySQL version 5.1, however, does not take advantage of this capability. With the InnoDB Plugin, however, users can in most cases add and drop indexes much more efficiently than with prior releases.

...

更改聚集索引需要复制数据，即使使用InnoDB插件也是如此.但是，使用InnoDB插件添加或删除辅助索引要快得多，因为它不涉及复制数据.

Changing the clustered index requires copying the data, even with the InnoDB Plugin. However, adding or dropping a secondary index with the InnoDB Plugin is much faster, since it does not involve copying the data.

来源:快速索引概述创作

这篇关于MySQL是否在创建新索引时使用现有索引?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！