mysql按照列构建索引_列存储索引增强功能–在线和离线（重新）构建

mysql按照列构建索引

In this article, we will explore ‘ Clustered columnstore online index build and rebuild’ feature of SQL Server 2019 including comparing execution plans, offline builds and more

在本文中，我们将探索SQL Server 2019的``集群式列存储在线索引构建和重建''功能，包括比较执行计划，脱机构建等

SQL Server 2019 is a cross-platform database system that provides support for big data, artificial intelligence, different development languages, and many more exciting features.

SQL Server 2019是一个跨平台的数据库系统，为大数据，人工智能，不同的开发语言以及更多令人兴奋的功能提供支持。

We have been exploring SQL Server 2019 in previous articles on SQLShack.

在之前有关SQLShack的文章中，我们一直在探索SQL Server 2019 。

In two previous articles, we explored enhancements in the Columnstore index, specifically.

在前两篇文章中，我们专门探讨了Columnstore索引的增强功能。

1. Columnstore index stats update in clone databases 克隆数据库中的列存储索引统计信息更新
2. ‘sp_estimate_data_compression_savings’ Compression Estimates for Columnstore 列存储的“ sp_estimate_data_compression_savings”压缩估计

For this article, we will use the ‘WideWorldStore’ sample database in both the SQL Server 2017 and SQL Server 2019 instance.

对于本文，我们将在SQL Server 2017和SQL Server 2019实例中使用``WideWorldStore''示例数据库。

In SQL Server 2017 and prior, we needed to build clustered indexes offline. We did not have the option to do this online.

在SQL Server 2017及更低版本中，我们需要离线构建群集索引。 我们没有在线选择的选项。

If we try to create a columnstore clustered index, we get the below error message

如果尝试创建列存储聚集索引，则会收到以下错误消息

The statement failed because the ONLINE option is not allowed when creating a columnstore index. Create the columnstore index without specifying the ONLINE option.

该语句失败，因为在创建列存储索引时不允许使用ONLINE选项。 在不指定ONLINE选项的情况下创建列存储索引。

It says we cannot use the ONLINE option while creating a columnstore index and we have to create the columnstore index without specifying the ONLINE option.

它说我们在创建列存储索引时不能使用ONLINE选项，而必须在不指定ONLINE选项的情况下创建列存储索引。

Let us run the command in SQL Server 2019. Before running, let us verify the instance version.

让我们在SQL Server 2019中运行命令。在运行之前，让我们验证实例版本。

We can see the current version – Microsoft SQL Server vNext (CTP2.0) – 15.0.1000.34 (X64) .

我们可以看到当前版本– Microsoft SQL Server vNext（CTP2.0）– 15.0.1000.34（X64）。

We also need to verify the database compatibility, it should be 150(SQL Server 2019).

我们还需要验证数据库兼容性，它应该是150（SQL Server 2019）。

select cmptlevel, name from sys.sysdatabases
where name ='WideWorldImporters'

Let us run the ‘create online columnstore clustered index’ command in this database.

让我们在此数据库中运行“创建在线列存储群集索引”命令。

Use SQLShackDemoColumnSore
Go
CREATE CLUSTERED COLUMNSTORE INDEX [CCS_Employees] ON [dbo]. [Employees] 
WITH (ONLINE = ON)

Drop the existing columnstore index and run the query again. We can see that the command completed successfully with ONLINE=ON.

删除现有的列存储索引，然后再次运行查询。 我们可以看到该命令以ONLINE = ON成功完成。

Let us try to rebuild the columnstore index online in SQL Server 2017 and SQL Server 2019. In SQL Server 2017, first, create the index without the online option.

让我们尝试在SQL Server 2017和SQL Server 2019中联机重建列存储索引。在SQL Server 2017中，首先，创建不具有联机选项的索引。

CREATE CLUSTERED COLUMNSTORE INDEX [CCS_Employees] ON [dbo]. [Employees]
Go
Use SQLShackDemoColumnSore
Go
ALTER INDEX [CCS_Employees]
  ON dbo.Employees
    REBUILD WITH (ONLINE = ON);

This command also fails in SQL Server 2017 with the error message.

此命令在SQL Server 2017中也会失败，并显示错误消息。

ALTER INDEX REBUILD statement failed because the ONLINE option is not allowed when rebuilding a columnstore index. Rebuild the columnstore index without specifying the ONLINE option.

ALTER INDEX REBUILD语句失败，因为在重建列存储索引时不允许使用ONLINE选项。 在不指定ONLINE选项的情况下重建列存储索引。

Now we will try rebuilding a clustered columnstore index in SQL Server 2019. In below image, you can see that columnstore clustered index can be rebuilt online as well in SQL Server 2019.

现在，我们将尝试在SQL Server 2019中重建群集的列存储索引。在下图中，您可以看到可以在SQL Server 2019中在线重建列存储的聚集索引。

实际执行计划的比较 (Comparison of the Actual execution plans)

Let us view the actual execution plan for the columnstore clustered index activity. In the follow actual execution plan, we can see the operator ‘Online Index Insert’. Save this execution plan into the desired location. We will use this plan later to compare the execution plan.

让我们查看列存储聚集索引活动的实际执行计划。 在遵循的实际执行计划中，我们可以看到运算符“在线索引插入”。 将此执行计划保存到所需位置。 我们稍后将使用该计划来比较执行计划。

Below is the actual execution plan if we create the index without ONLINE=ON.

如果我们创建不带ONLINE = ON的索引，则下面是实际的执行计划。

Now we will compare both the execution plans (with ONLINE=ON and With ONLINE=OFF) using the ‘Compare Showplan’.

现在，我们将使用“比较显示计划”比较两个执行计划（ONLINE = ON和ONLINE = OFF）。

In ‘Compare Showplan’ give, the path of the same execution plan and it generates the comparison windows.

在“比较显示计划”中，给出相同执行计划的路径，并生成比较窗口。

In the execution plan comparison, you can get the following findings.

在执行计划比较中，您可以获得以下发现。

You can observe in the above comparison table, that operating cost increases with the online index operation.

您可以在上面的比较表中观察到，运行成本随着在线索引操作的增加而增加。

Similarly, let us view the comparison for the Insert operator.

同样，让我们​​查看“插入”运算符的比较。

In this comparison, the table also, Compile memory, Memory Grant; estimated subtree cost value is more in the online columnstore index operation. This seems to be reasonable though. During online index operations, SQL Server needs to perform extra steps that will obviously take the resources.

在此比较中，该表还包括“编译内存”，“内存授权”； 在线列存储索引操作中，估计的子树成本值更大。 不过，这似乎是合理的。 在联机索引操作期间，SQL Server需要执行额外的步骤，这些步骤显然会占用资源。

Let us do the same actual plan comparison for the rebuild clustered index operation with ONLINE=ON and ONLINE=OFF.

让我们对使用ONLINE = ON和ONLINE = OFF的重建聚集索引操作进行相同的实际计划比较。

In the below image, you can see the compiled memory and the memory grant is high for the online rebuild operation as well.

在下图中，您可以看到已编译的内存，并且在线重建操作的内存授权也很高。

联机和脱机Columnstore重建的日志大小增长 (Log Size Growth for Online and Offline Columnstore rebuilds)

In this section, we will look at the transaction log size for both the online and offline columnstore rebuild activity.

在本节中，我们将查看在线和离线列存储重建活动的事务日志大小。

Offline Columnstore Index Rebuild: First, issue a CHECKPOINT command to flush all dirty pages. We will use the sys.dm_db_log_stats dynamic management view to calculate the active log size in MB.

脱机列存储索引重建 ：首先，发出CHECKPOINT命令来刷新所有脏页。 我们将使用sys.dm_db_log_stats动态管理视图来计算活动日志大小（以MB为单位）。

• Top 8 new (or enhanced) SQL Server 2017 DMVs and DMFs for DBAs. 用于DBA的前8大新（或增强）SQL Server 2017 DMV和DMF 。

Execute the below query under the database context.

在数据库上下文中执行以下查询。

CHECKPOINT;
 
ALTER INDEX [CCS_Employees]
  ON dbo.Employees
    REBUILD WITH (ONLINE = OFF);
 
SELECT 
      DB_NAME(database_id) AS DBName, 
      recovery_model,
      total_log_size_mb,
      active_log_size_mb,
      active_vlf_count   
FROM sys.dm_db_log_stats(DB_ID())

From the below DMV, we can see the below stats during rebuild columnstore clustered index with ONLINE=OFF parameter.

从下面的DMV中，我们可以使用ONLINE = OFF参数在重建列存储聚集索引期间看到以下统计信息。

• Active log Size in MB: 0.2695 MB
  活动日志大小（MB）：0.2695 MB
• active_vlf_count: 1
  active_vlf_count：1

ONLINE Columnstore Index Rebuild: let us run the command with ONLINE=ON.

ONLINE列存储索引重建 ：让我们以ONLINE = ON运行命令。

CHECKPOINT;
 
ALTER INDEX [CCS_Employees]
  ON dbo.Employees
    REBUILD WITH (ONLINE = ON);
 
 
SELECT 
      DB_NAME(database_id) AS DBName, 
      recovery_model,
      total_log_size_mb,
      active_log_size_mb,
      active_vlf_count
   FROM sys.dm_db_log_stats(DB_ID())

From the below DMV, we can see that during rebuild columnstore clustered index with ONLINE=ON parameter.

从下面的DMV中，我们可以看到在重建带有ONLINE = ON参数的列存储聚集索引期间。

• Active log Size in MB: 134.875MB
  活动日志大小（MB）：134.875MB
• active_vlf_count: 11
  active_vlf_count：11

It is clear from the above comparison that if we rebuild the columnstore index with ONLINE=ON, we require more transaction log space.

从上面的比较可以明显看出，如果我们使用ONLINE = ON重建列存储索引，则需要更多的事务日志空间。

Let us perform the same analysis to create the columnstore index as well. We have already created the index, so we need to drop it first before creating it again. Therefore, in the below query, the first statement you can see is DROP INDEX.

让我们执行相同的分析以创建列存储索引。 我们已经创建了索引，因此我们需要先删除它，然后再创建它。 因此，在下面的查询中，您可以看到的第一个语句是DROP INDEX。

>DROP INDEX [CCS_Employees] ON [dbo].[Employees]
GO
 
CHECKPOINT;
 
CREATE CLUSTERED COLUMNSTORE INDEX [CCS_Employees] ON [dbo]. [Employees] 
WITH (ONLINE = OFF)
 
 
SELECT 
      DB_NAME(database_id) AS DBName, 
      recovery_model,
      total_log_size_mb,
      active_log_size_mb,
      active_vlf_count
   FROM sys.dm_db_log_stats(DB_ID())

For creating a new clustered columnstore index with ONLINE=OFF

用于使用ONLINE = OFF创建新的集群列存储索引

• Active log Size in MB: 0.261718
  活动日志大小（MB）：0.261718
• active_vlf_count: 1
  active_vlf_count：1

Now run the query with ONLINE=ON parameter and observe the log size and vlf count.

现在，使用ONLINE = ON参数运行查询，并观察日志大小和vlf计数。

>DROP INDEX [CCS_Employees] ON [dbo].[Employees]
GO
 
CHECKPOINT;
 
CREATE CLUSTERED COLUMNSTORE INDEX [CCS_Employees] ON [dbo]. [Employees] 
WITH (ONLINE = ON)
 
SELECT 
      DB_NAME(database_id) AS DBName, 
      recovery_model,
      total_log_size_mb,
      active_log_size_mb,
      active_vlf_count
   FROM sys.dm_db_log_stats(DB_ID())

• Active log Size in MB: 145
  活动日志大小（以MB为单位）：145
• active_vlf_count: 11
  active_vlf_count：11

Therefore, to create new columnstore clustered index also, more transaction log space will be required.

因此，还要创建新的列存储聚集索引，将需要更多的事务日志空间。

结论 (Conclusion)

We have explored the various aspects of creating and rebuilding the columnstore index with online and offline options. This looks promising to me. Explore this into your environment and share your experiences in the comments below.

我们已经探索了使用在线和离线选项创建和重建列存储索引的各个方面。 这对我来说似乎很有希望。 在您的环境中进行探索，并在下面的评论中分享您的经验。

目录 (Table of contents)

Columnstore Index Enhancements – Index stats update in clone databases

Columnstore Index Enhancements – data compression, estimates and savings

Columnstore Index Enhancements – online and offline (re)builds

列存储索引增强功能–克隆数据库中的索引统计信息更新

列存储索引增强功能–数据压缩，估计和节省

列存储索引增强功能–在线和离线（重新）构建

翻译自: https://www.sqlshack.com/columnstore-index-enhancements-online-and-offline-rebuilds/

mysql按照列构建索引