J'ai suivi cet article https://cloud.google.com/blog/products/gcp/sharding-of-timestamp-ordered-data-in-cloud-spanner et créé un schéma un peu similaire juste sans companyID:
Query #2 should scan 1 row Query #3 should scan 1 row Query #4 should scan 2 rows.
shard_id est un nombre aléatoire de 0 à 49. Ensuite, j'exécute un tas de sélections contre lui:
1: SELECT * FROM Foo@{FORCE_INDEX=OrderIndex} where shard_id=0 order by timestamp_order limit 1;
# this correctly scans 1 row
2: SELECT * FROM Foo@{FORCE_INDEX=OrderIndex} where shard_id<1 order by timestamp_order limit 1;
# this scans 192 rows
3: SELECT * FROM Foo@{FORCE_INDEX=OrderIndex} where shard_id BETWEEEN 1 AND 1 order by timestamp_order limit 1;
# this scans 185 rows
4: SELECT * FROM Foo@{FORCE_INDEX=OrderIndex} where shard_id BETWEEN 0 AND 1 order by timestamp_order limit 1;
# this scans 377 rows
Je m'attendais à quelque chose comme ça:
CREATE TABLE Foo ( random_id STRING(22) NOT NULL, shard_id INT64 NOT NULL, timestamp_order TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true), ) PRIMARY KEY(random_id); CREATE INDEX OrderIndex ON Foo(shard_id, timestamp_order);
Question: qu'est-ce que je fais de mal ici? Est-il possible d'avoir des requêtes ordonnées d'horodatage efficaces dans la clé?
3 Réponses :
Lorsque vous spécifiez plusieurs identifiants de partition (ou une expression qui pourrait produire plusieurs identifiants de partition), le jeu de résultats théorique à ce stade n'est plus trié par horodatage (alors que pour un seul fragment, il l'est), il doit donc être recouru à l'horodatage (et chaque ligne doit être prise en compte). Une optimisation théorique lorsque vous spécifiez une limite consisterait à prendre les N premiers de chaque partition et à les fusionner, mais il semble que l'optimisation n'est pas en place.
Vous pouvez l'implémenter au niveau de la couche application en exécutant une requête de limite 1 sur chaque partition pertinente en parallèle et en fusionnant les résultats.
Oui, j'ai été surpris que cette optimisation ne soit pas en place, alors que l'article de blog fait quelque chose qui a clairement besoin que cela fonctionne (le faire comme dans l'article déclencherait une analyse complète de la table / index, ce qui tuera la perf). Dans l'ensemble, cela semble être une façon très lourde de le faire, je me demande s'il existe une meilleure alternative (pas à cette requête spécifique, mais pour avoir un ordre d'horodatage global efficace).
L'article de blog est en fait pour obtenir un tri répertorié de tous les éléments de l'index qui correspondent à la condition de requête (aucune limite), de sorte que les performances ne sont pas affectées à cet endroit. Obtenir le top 1 ou le top N avec une limite a un impact sur les performances (vous scannez les mêmes lignes que s'il n'y avait pas de limite), mais la réponse de Campbell donne un moyen performant de le faire.
Vous pouvez obtenir une exécution efficace de cette requête en utilisant la construction HAVING MIN.
Réécrit # 2:
SELECT *
FROM
(
SELECT shard_id, ANY_VALUE(random_id HAVING MIN timestamp_order) AS random_id, MIN(timestamp_order) AS timestamp_order
FROM Foo@{FORCE_INDEX=OrderIndex, GROUPBY_SCAN_OPTIMIZATION=true}
GROUP BY shard_id
WHERE shard_id<1
)
ORDER BY timestamp_order
LIMIT 1;
L'efficacité viendra de la sous-requête interne. Il ne doit analyser qu'une seule ligne pour chaque shard_id, puis choisir le minimum parmi ces lignes. Si vous trouvez que ce n'est pas le cas, il existe un indice qui peut forcer ce comportement.
SELECT *
FROM
(
SELECT shard_id, ANY_VALUE(random_id HAVING MIN timestamp_order) AS random_id, MIN(timestamp_order) AS timestamp_order
FROM Foo@{FORCE_INDEX=OrderIndex}
GROUP BY shard_id
WHERE shard_id<1
)
ORDER BY timestamp_order
LIMIT 1;
Pour les autres requêtes, remplacez simplement la condition de filtre dans la sous-requête interne.
Il semble que GROUPBY_SCAN_OPTIMIZATION = true a fait l'affaire! Sans cela, il effectuait toujours une analyse complète. Essayer si je peux faire de même avec d'autres requêtes.
Cela résout le problème LIMIT 1, y a-t-il un moyen de le faire avec une limite arbitraire?
J'ai pu faire quelque chose de proche avec UNION ALL en récupérant LIMIT le nombre de lignes par chaque partition, puis en triant le résultat combiné. Cela semble fonctionner, mais pas encore très pratique.
J'ai ajouté une réponse supplémentaire pour le cas LIMIT arbitraire (c'était trop long pour être autorisé en tant que commentaire sur le fil).
un peu une note latérale, alors que cette requête fonctionne, il semble que le fait d'avoir un index sur la table ralentisse les performances de 4x. Sans cet index, j'ai des performances de mises à jour (mise à jour de l'horodatage avec commit_timestamp) proches de 3500 sur un seul nœud. Avec les performances d'index sont <800 écritures / s. Bien que l'indice ralentisse naturellement les performances, je ne m'attendais pas à autant. Est-ce que je fais mal?
HAVING of ANY_VALUE semble non documenté. ANY_VALUE documenter! cloud.google.com/bigquery/docs/reference/standard-sql/… . Les documents de ZetaSQL n'ont que la description. github.com/google/zetasql/blob/2020.04.1/docs/…
L'ajout d'un index secondaire ralentit inévitablement les écritures. L'impact des index supplémentaires sera incrémental sur ce premier index. Merci, oui, nous avons remarqué que la documentation pour HAVING MAX / MIN est manquante - c'est une fonctionnalité entièrement prise en charge et la documentation est en route.
HAVING MAX / MIN semble maintenant documenté. cloud.google.com/spanner/docs/…
J'ai vu le commentaire sur une LIMITE arbitraire. C'était trop long pour un commentaire de suivi (non autorisé), j'ai donc ajouté une autre réponse.
Pour une LIMITE arbitraire, une requête plus complexe est nécessaire pour obtenir la plus grande efficacité. Voici un modèle utilisant le filtre "shard_id <1000" et LIMIT x.
Le premier côté de la jointure extraira efficacement les valeurs shard_id qualifiantes, tout comme la requête d'origine avec LIMIT 1. Le deuxième côté de la jointure retournera à la table et récupérera les x premières lignes pour chaque shard_id. Le parent sélectionnera ensuite le x supérieur parmi toutes les valeurs shard_is éligibles. S'il y a plusieurs horodatages par shard_id, ce sera très efficace.
SELECT ff.*
FROM
(
SELECT shard_id
FROM Foo@{FORCE_INDEX=OrderIndex,
GROUPBY_SCAN_OPTIMIZATION=true}
WHERE shard_id < 1000
GROUP BY shard_id
) shards
JOIN UNNEST(ARRAY
(
SELECT AS STRUCT *
FROM Foo@{FORCE_INDEX=OrderIndex} AS f
WHERE f.shard_id = shards.shard_id
ORDER BY timestamp_order
LIMIT x
)) AS ff
ORDER BY ff.timestamp_order
LIMIT x;
Cela semble fonctionner! Je devais juste ajouter SELECT AS STRUCT * FROM Foo@{FORCE_INDEX=OrderIndex} sinon il se plaignait que le tableau ne puisse sélectionner qu'une seule colonne.
BTW, pourquoi ne peut-on pas sélectionner directement à partir de la sous-requête? Pourquoi avez-vous besoin d'un combo array / unnest?
C'est parce que la sous-requête fait référence à la colonne de l'autre côté de la jointure (shards.shard_id). C'est un peu encombrant mais une fois que vous le connaissez, tout va bien.
Il semble émuler LATERAL parce que le LATERAL standard n'est pas pris en charge dans le dialecte.
J'ai remarqué qu'il fallait une jointure arrière supplémentaire proportionnellement à [cardinality of shard_id] * [LIMIT] . Il peut être réduit par SELECT uniquement PK et par jointure explicite avec la table Foo à l'extérieur. github.com/gcpug/nouhau/blob/spanner/shard/spanner/note/shar d /… (désolé, c'est un article en japonais)
Ajoutez à votre question ce que vous avez obtenu.
@KaneKim Les lignes analysées dans votre cas étaient une analyse complète de la table ou une analyse de la table de filtrage.
@NirleyGupta c'était une analyse d'index (analyse de filtre) - mais toutes les lignes de l'index.