Pourquoi la requête prend-elle le même temps pour obtenir les données même si le nombre de lignes est très différent ?

Question

J'ai 29 938 766 lignes dans ma table VISITS qui ressemble à ceci

USER_ID (INT)	VISITED_IN(DATETIME)
65	2020-08-26 07:57:43
1182	2019-03-15 02:46:48
1564	04/07/2015 10:59:44
73	2021-03-18 00:25:08
3791	2017-10-17 12:22:45
51	2022-05-02 19:11:09
917	2017-11-20 15:32:06
3	2019-12-29 15:15:51
51	08/02/2015 17:48:30
1531	2020-08-05 08:44:55
Attendez...	Attendez...

Lors de l'exécution de cette requête, cela prend 17 à 20 secondes et renvoie 63 514 (l'utilisateur a 63 514 visites)

SELECT COUNT(*) FROM VISITS WHERE USER_ID = 917

Lors de l'exécution de cette requête, cela prend 17 à 20 secondes et renvoie 193 (l'utilisateur a 193 visites)

SELECT COUNT(*) FROM VISITS WHERE USER_ID = 716

Le problème est que l'interrogation de 29 938 766 lignes prend toujours 17 à 20 secondes, même si l'utilisateur n'a que 3, 50, 70 ou 1 000 000 de visites.

Je pense que le problème est dû au fait qu'il parcourt toutes les lignes ?

La deuxième requête doit être plus rapide que la première requête. Cela dépend du nombre de lignes. Mais les deux requêtes prennent le même temps !

Quelles suggestions me faites-vous pour éviter ce problème ?

---

Structure du tableau

---

MISE À JOUR : Voici un nouveau scénario suggéré :

Lorsqu'un utilisateur accède à son profil ou à celui de quelqu'un d'autre, il peut voir le nombre de visites de profil et filtrer les visites de cette façon

Last 24 hours
|
---> SELECT COUNT(*) FROM VISITS WHERE USER_ID = 5 AND VISITED_IN >= DATE_SUB(NOW(), INTERVAL 1 DAY);

Last 7 days
|
---> SELECT COUNT(*) FROM VISITS WHERE USER_ID = 5 AND VISITED_IN >= DATE_SUB(NOW(), INTERVAL 7 DAY);

Last 30 days
|
---> SELECT COUNT(*) FROM VISITS WHERE USER_ID = 5 AND VISITED_IN >= DATE_SUB(NOW(), INTERVAL 30 DAY);

All time
|
---> SELECT VISITS FROM USERS WHERE USER_ID = 5;

De plus, je vais créer un événement récurrent qui exécutera cette commande chaque jour.

DELETE FROM VISITS WHERE VISITED_IN <= DATE_SUB(NOW(), INTERVAL 30 DAY);

De plus, lors de l'ajout d'une nouvelle ligne dans le tableau VISITES, je veillerai à incrémenter la colonne VISITES.

UPDATE USERS SET VISITS = VISITS + 1 WHERE ID = 5

Answer 1

INDEX(user_id, visited_in)

accélérera tout SELECTs que vous avez mentionné. Ils devront analyser une grande partie de l'index ; ils n'auront pas à "analyser la table entière".

DELETE Nécessite `INDEX(visited_in). Mais si vous ne l’exécutez pas assez souvent, des problèmes peuvent survenir. En effet, la suppression de milliers de lignes à la fois peut poser problème. Pensez à exécuter l’opération de suppression au moins une fois toutes les heures.

Si la table est très grande, etc., pensez à utiliser le partitionnement « séries chronologiques ». Avec DROP PARTITION, la vitesse est beaucoup plus rapide. Partition

Tout service de mise en cache fournira des décomptes obsolètes, mais parfois c'est plus rapide.

"La base de données est accessible à chaque fois que quelqu'un ouvre la page", mais seulement si la requête est suffisamment efficace. Faites l'indexation.

Dans ma réponse à votre autre question, j'ai expliqué comment les tableaux récapitulatifs peuvent accélérer encore plus les choses. Cependant, il suppose que les « N derniers jours » sont mesurés de minuit à minuit. Votre requête actuelle est NOW() - INTERVAL N DAY. C'est plus déroutant à mettre en œuvre que minuit. Souhaitez-vous changer la signification de « N derniers jours » ?

(Quelques bases d'INDEX...)

Une raison importante de tout index est sa capacité à trouver rapidement des lignes en fonction de certaines colonnes.

• INDEX est une liste de clés mappées sur des lignes.
• UNIQUE INDEX est UNIQUE INDEX 是 INDEX plus une contrainte d'unicité - ce qui signifie que deux lignes de l'index n'ont pas la même valeur.
• Unique PRIMARY KEY est un index unique spécifié pour identifier de manière unique chaque ligne du tableau.

« clé » et « index » sont des synonymes.

Les index (dans le moteur InnoDB de MySQL) sont implémentés sous forme de BTree (en fait un B+Tree ; voir Wikipédia). Dans le cas de PK, les colonnes restantes se trouvent là avec la valeur PK. Pour les clés « secondaires », la partie « valeur » du BTree est la colonne PK.

Tout index peut contenir 1 ou plusieurs colonnes (appelées « composites »)

INDEX(lastname) Il est peu probable que ce soit le seul INDEX(lastname,firstname) Il est encore peu probable qu'il soit unique, mais il est "composite".