检查是否存在并同步数百万行的数据

Question

我正在开发基于sms的服务平台。在印度，我们有两种不同的路线发送短信-宣传和运输。通过宣传路线发送的短信必须与国家维护的数据库(DND)进行核对，该数据库目前拥有231450000个移动电话号码。

我的问题是

1. 存储这些数据的有效方法是什么(DND)。
2. 检查的有效方法是什么，数字是否存在于这些数字中。
3. 每周两次，电信当局提供这些数字的增量转储(DND)。如何将转储与现有的db同步。
4. 我是否应该使用mysql来存储这些数据，这将包括移动号码，或任何其他内存中的存储？

编辑：

1. 每一行数据由移动号码和区号、用户偏好等几个首选项组成。
2. 每周提供两次的数据转储只包含增删，而不包含整个数据。
3. 查找频率可能非常大。实际上，从平台发送的每条短信都必须首先对数据(DND)进行查找。因此，如果平台每秒处理100条短信，那么它必须每秒对这些数据进行100次查找。查找简单地根据数据库中的数据(DND)检查特定移动号码的存在。
4. 新的增量数据转储(增删)只能在几个小时内同步，因为转储是由电信当局在白天提供的，而同样的转储必须在午夜00:00之前运行。因此，同步数据的总时间约为3小时。

样本DND转储

提供了DND转储的样品。这里，必须添加A下的Opstype意味着记录，而D则意味着必须删除记录。

Answer 1

我不会设计一个系统来做动态查找。相反，我将设计系统来验证一个给定的数字在添加到您的系统时是否在DND列表中，并且每周(在DND列表更新之后)。然后，你不需要检查号码，每次你发送短信。

如果您遵循这种设计模式，您只需要与DND表进行非常不频繁的交互，并且有时不会影响发送SMS消息的速度。

如果我这样做，我可能会使用，我会考虑类似于下面的设计。将这些SQL语句改编成您想要使用的任何类型的SQL应该是非常简单的。

保存DND行的表：

CREATE TABLE dbo.DND
(
    TelephoneNo BIGINT NOT NULL
        CONSTRAINT PK_DND
        PRIMARY KEY CLUSTERED
    , PhoneType INT NOT NULL
    , ServiceAreaCode INT NOT NULL
);

保存您的订户电话号码的表：

CREATE TABLE dbo.Subscribers
(
    TelephoneNo BIGINT NOT NULL
        CONSTRAINT PK_Subscribers
        PRIMARY KEY CLUSTERED
    , IsOnDND BIT NOT NULL
    , DNDLastChecked DATETIME NOT NULL
);

此查询可用于在更新Subscribers列表之后每周更新DND表。

UPDATE dbo.Subscribers 
SET IsOnDND = 1
    , DNDLastChecked = GETDATE()
WHERE EXISTS (
    SELECT 1
    FROM dbo.DND
    WHERE DND.TelephoneNo = Subscribers.TelephoneNo
    );

我将使用计划好的Server代理作业处理从每周更改列表中更新DND列表的问题。

保存每周更改的表可能如下所示：

CREATE TABLE dbo.DNDChanges
(
    TelephoneNo BIGINT NOT NULL
        CONSTRAINT PK_DNDChanges
        PRIMARY KEY CLUSTERED
    , Opstype CHAR(1) NOT NULL
    , PhoneType INT NOT NULL
    , ServiceAreaCode INT NOT NULL
);

计划的作业将执行下列步骤：

TRUNCATE TABLE dbo.DNDChanges;

--insert rows into the DNDChanges table using SSIS, BCP, etc

/* 
    Here we remove rows from the DND table where the number has 
    been deleted (Opstype = 'D')
*/
DELETE 
FROM dbo.DND
WHERE EXISTS (
    SELECT 1
    FROM dbo.DNDChanges
    WHERE DNDChanges.Opstype = 'D'
        AND DNDChanges.TelephoneNo = DND.TelephoneNo
    );

/*    
    This updates the PhoneType, and ServiceAreaCode from the DND 
    incremental change list.
*/
UPDATE dbo.DND
SET DND.PhoneType = DNDChanges.PhoneType
    , DND.ServiceAreaCode = DNDChanges.ServiceAreaCode
    , DND.TelephoneNo = DNDChanges.TelephoneNo
FROM dbo.DND
    INNER JOIN dbo.DNDChanges ON DND.TelephoneNo = DNDChanges.TelephoneNo;
    /* no need to add OPSType = 'A' to the WHERE clause
       since we've already removed those rows from the
       DND table */

/*
    Add new rows from the DND incremental change list
*/
INSERT INTO dbo.DND (
    TelephoneNo
    , PhoneType
    , ServiceAreaCode
    )
SELECT *
FROM dbo.DNDChanges 
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.DND
    WHERE dbo.DND.TelephoneNo = DNDChanges.TelephoneNo
    );