Progettazione di database per ottenere articoli non letti in mysql (oltre una tabella di righe di 100M) - mysql, sql, database, database-design, mariadb

Questa informazione è molto condensata.

Ci sono 2 tavoli.

articolo

-----------------------------------
|id | weight | text               |
-----------------------------------
|1  | 10     | blah               |
|2  | 100    | blah               |
|3  | 50     | blah               |
|4  | 1000   | blah               |
-----------------------------------

leggere

-----------------------------------
| user_id | article_id            |
-----------------------------------
| 1       | 4                     |
| 1       | 2                     |
| 1       | 3                     |
| 2       | 3                     |
| 2       | 4                     |
-----------------------------------

Voglio ottenere articoli non letti usando la query di seguito (molto condensata)

SELECT
a.*
FROM
article a LEFT OUTER JOIN read r ON r.article_id = a.id and r.user_id = 1
WHERE
r.id IS NULL
ORDER BY
a.weight DESC
LIMIT 10

Informazioni importanti

  1. il numero di read table le righe sono inferiori a 1000 per utente. (rimuovi i vecchi dati)
  2. la colonna di peso nella tabella degli articoli viene modificata frequentemente. (Significa ordine non risolto)

il problema è ... (quando il numero di utenti: oltre 1 milione)

  1. il modo per ottenere articoli non letti usando la tabella di lettura (no in, join esterno non è importante)
    • numero di read table le righe saranno oltre 1G

Funziona bene finora (numero attuale di righe della tabella di lettura: 100 M). ma devo preparare il prossimo passo perché il numero di utenti sta aumentando rapidamente.

Qual è il modo migliore per un grande servizio in questo caso?

(sharding? tabella di partizionamento? o riprogettazione dell'architettura?)

Grazie in anticipo

risposte:

0 per risposta № 1

Il punto è, per utilizzare l'indice il più possibile.

SELECT a.*
FROM  a
LEFT JOIN read r
ON r.article_id = a.id and r.user_id =1
WHERE r.id IS NULL
ORDER BY a.weight DESC
LIMIT 10

Modificare: La preoccupazione per te è la dimensione dei dati di read tabella e dobbiamo ridurre la dimensione dei dati. Per questo abbiamo più opzioni:

  • Partizioni MySQL: crea partizioni su un intervallo di user_id (possono essere 100K utenti per partizione
  • Crea più tabelle: simile al partizionamento, ma avrai i dati in database diversi (anche in server DB diversi). In base a user_id, deciderai sulla tabella / database di unirti.

Inoltre, puoi pensare di avere periodicamente l'archiviazione di vecchi dati e l'applicazione dovrebbe essere abbastanza intelligente da decidere se deve eseguire query su tabelle archiviate o live table.


0 per risposta № 2
  • Aggiungi una colonna a article. Sarà una bandiera che dice se l'articolo è letto / non letto. (Non renderlo un conteggio utente o un timestamp; questo rallenterà i passaggi successivi.)
  • Ogni volta che un utente legge un articolo, controlla la bandiera e modificalo se necessario.
  • Avere `INDEX (flag, weight, id) - questo permetterà alla tua query di essere eseguita quasi istantaneamente. Questo dovrebbe essere OK su quella tabella da un milione di righe.

Un problema:Dato che stai eliminando (dopo 1000), alcuni articoli "letti" possono diventare "non letti". Per far fronte a questo, raggruppare l'eliminazione e raccogliere l'elenco distinto di articoli che sono stati eliminati. Quindi fai il noioso compito di ricalcolare la bandiera, ma solo per quegli articoli. INDEX(article_id) aiuterà; uso EXISTS ( SELECT * FROM read WHERE article_id = $aid ). (Questo può probabilmente essere trasformato in un'operazione batch piuttosto che in un aiuto alla volta.)

Un altro problema: le chiavi secondarie su tabelle da miliardi di righe sono costose e possono portare a un sacco di I / O. Prima di tentare di risolvere questo problema, si prega di fornire SHOW CREATE TABLE per entrambe le tabelle, oltre a qualsiasi altro comune SELECTs. Scegliere l'indice o gli indici giusti è molto importante per le prestazioni in tabelle da miliardi di righe ..


Menu