MySQL - 規模設計幫助

我將使用 Amazon Web Services RDS 服務來託管 MySQL 5.7 伺服器（目前 AWS 不支持 5.7，但他們已經宣布他們正在努力實施這個版本，並且很快就會推出）。我只會有一張桌子，但我希望它有很多行。它是一個消息表，將包含從使用者到其他使用者的消息。

消息只能由一個使用者發送給另一個使用者。第一次閱讀此消息時需要更新，說明它已被閱讀，因此如果已閱讀，則需要在其生命週期中進行一次更新。使用者必須能夠查看按最新排序的所有已發送消息和按最新排序收到的所有消息。

我最初的設計是這樣的：

消息表：

Message_ID (BIGINT) PK auto_increment
FromUser (INT 16)
ToUser (INT 16)
DateCreated (Timestamp)
MessageText (Varchar (500) )
HasRead (TINYINT (0=false, 1=true) )

將執行的查詢將包括：

SELECT * FROM messages WHERE FromUser = '10000000' ORDER BY DateCreared DESC LIMIT [starting pagination value], 10 ; //get all users sent messages - newest first, get 10 at a time

SELECT * FROM messages WHERE ToUser= '10000000' ORDER BY DateCreared DESC LIMIT [starting pagination value], 10 ; //get all Bob's received messages - newest first, get 10 at a time

UPDATE messages SET HasRead = 1 WHERE Message_ID = '123456789'; // When a message has been read update it to show it has been read.

結果，我將擁有以下索引：

-Message_ID (primary index)
-(FromUser,DateCreated) BTREE DESC
-(ToUser,DateCreated) BTREE DESC

額外的“希望具有功能，但如果它會顯著影響性能，則可以在沒有它的情況下生存”將是讓使用者與另一個使用者查看他們的消息：

SELECT * FROM messages WHERE (FromUser = '1000000 and ToUser = '2000000') OR (FromUser = '2000000' and ToUser = '1000000') ORDER BY DateCreated DESC [starting pagination value], 10 ; // Get all the messages sent between user 1000000 and user 2000000 - newest first, fetch 10 at a time. I was thinking of creating a new column which would be be a concatenation of [smaller_userid]-[larger_userid] and search on this field instead. If this was the case I would have an additional composite index on this new column + DateCreated. SELECT * FROM messages WHERE concateduser = '1000000-2000000' ORDER BY DateCreated DESC [starting pagination value], 10 ;

我們相信這會奏效，但我們目前擁有一個活躍的使用者社區，並且估計我們何時推出該功能將被大量使用。因此，我們還想為未來進行擴展（現在還為時過早，但我們認為這是一個非常簡單的功能，並希望現在設計好，以節省我們未來的時間）。

如果將來我們需要水平擴展，我們認為我們的設計不會很好地擴展。我們不相信自動遞增​​的 message_id pk 將在多節點環境中工作。我們研究了為這個專欄設置一個 UUID ( https://www.percona.com/blog/2007/03/13/to-uuid-or-not-to-uuid/ )，但是讀到這真的很傷人性能，因為索引會很大。閱讀這篇文章，我們看到分頁也可能是一個問題。http://www.psce.com/blog/2012/04/04/how-important-a-primary-key-can-be-for-mysql-performance/

在我們目前的設計中，我們並沒有真正看到可以用於我們所有查詢的出色分片鍵。如果可能，我們希望我們的查詢能夠到達一台共享伺服器。

所以我的問題是什麼是實現這個基本消息傳遞功能的有效方法，以便將來它可以很好地適應我們需要的查詢。我只使用過一個 MySQL 實例，所以我不是 MySQL 橫向擴展設計專家，我願意接受任何想法（也可以完全重新設計）！我們相信分片將是不可避免的，因為我們的實例類型不是很大。

PS：我們知道有些人可能會說 NoSQL 是這個場景的一個很好的選擇，但我們研究了這個功能的 NoSQL 選項（Cassandra、DynmoDB、Google Datastore、Azure DocumentDB、像 AWS S3 或 Azure 儲存這樣的文件系統）幾個月但由於性能成本（索引在託管 NoSQL 環境中非常昂貴），缺乏 ACID 合規性（我們有其他需要真正事務的想法），以及更多我們決定使用 MySQL。

微不足道，即使在規模上。

一些次要但重要的調整…

INT UNSIGNED是 4 個字節；16 是沒有意義的。它將持有高達 40 億的價值；您可能不會有那麼多使用者。

不要OFFSET用於分頁，而是“記住你離開的地方”，正如我在這裡討論的那樣。

對於額外的功能，將OR變成UNION以便您可以更好地使用另一個必要的索引 INDEX(FromUser, ToUser, DateCreated, MessageID)：：

     ( SELECT  *
           FROM  messages
           WHERE  FromUser = '1000000'
             and  ToUser = '2000000'
           ORDER BY  DateCreated DESC
           LIMIT  10 
     )
   UNION  ALL
     ( SELECT  *
           FROM  messages
           WHERE  FromUser = '2000000'
             and  ToUser = '1000000'
           ORDER BY  DateCreated DESC
           LIMIT  10 
      )
   ORDER BY  DateCreated DESC
   LIMIT  10 

“記住你離開的地方”可能太棘手了，但由於這應該是一個罕見的查詢並且結果不多，所以繼續使用OFFSET. 但是，例如第 15 頁，您需要LIMIT 160在內部和LIMIT 150,10外部。而且，我會拉另一個技巧：

SELECT  m.*
   FROM  messages AS m
   JOIN  ( 
             ( SELECT  Message_ID
                   FROM  messages
                   WHERE  FromUser = '1000000'
                     and  ToUser = '2000000'
                   ORDER BY  DateCreated DESC
                   LIMIT  160 
             )
           UNION  ALL
             ( SELECT  Message_ID
                   FROM  messages
                   WHERE  FromUser = '2000000'
                     and  ToUser = '1000000'
                   ORDER BY  DateCreated DESC
                   LIMIT  160 
              )
           ORDER BY  DateCreated DESC
           LIMIT  150,10 
         ) AS u ON u.Message_ID = m.Message_ID
   ORDER BY  DateCreated DESC; 

這次的訣竅是避免SELECT *直到您將其縮減到僅 10 行。請注意，建議的索引包含必要的Message_ID，因此內部查詢執行Using index並聚集以提高效率。中間查詢涉及一個 tmp 表和文件排序，但它們將在一個MEMORY表中。外部查詢將命中 10 行的磁碟。總磁碟命中數：大約 12 個。非常有效。

（這個技巧應該添加到我的另一個大查詢中；就目前而言，它將產生大約 21 次磁碟命中。）

引用自：https://dba.stackexchange.com/questions/129141