La requête de la base de données MySQL prend un certain temps

Question

J'ai une grande base de données de messages avec 24 000 lignes :

Afficher les lignes 0 à 24 (2 455 455 lignes au total, la requête prend 0,0006 seconde). ≪/pré>
<p>, je dois donc charger les conversations plus rapidement. Pour les utilisateurs ayant moins de conversations, le chargement est le suivant (l'utilisateur a 3,2 000 conversations) : </p>
<pre class="brush:php;toolbar:false;">Afficher les lignes 0 à 24 (3 266 lignes au total, la requête prend 0,0345 secondes) [id : 5009666... - 4375619...]. ≪/pré>
<p>Chargement plus lent pour les utilisateurs ayant de nombreuses conversations (l'utilisateur a 40 000 conversations) : </p>
<pre class="brush:php;toolbar:false;">Afficher les lignes 0 à 24 (40 296 lignes au total, la requête prend 5,1763 secondes) [id : 5021561... - 5015545...]. ≪/pré>
<p>J'utilise des clés d'index pour ces colonnes : </p>
<pre class="brush:php;toolbar:false;">id, to_id, from_id, heure, vu

Table de base de données :

CREATE TABLE `messages` (
  `id` int(255) NON NULL,
  `to_id` int(20) NON NULL,
  `from_id` int(20) NON NULL,
  `message` texte long NON NULL,
  `time` double NON NULL,
  `vu` int(2) NON NULL,
) MOTEUR=InnoDB CHARSET PAR DÉFAUT=latin1;



INSÉRER DANS `messages` (`id`, `to_id`, `from_id`, `message`, `time`, `seen`) VALEURS
(2, 6001, 2, 'Bonjour', 1587581995.5222, 1) ;


ALTER TABLE `messages`
  AJOUTER UNE CLÉ PRIMAIRE (`id`),
  AJOUTER UNE CLÉ `time_idx` (`time`),
  AJOUTER LA CLÉ `from_idx` (`from_id`),
  AJOUTER LA CLÉ `to_idx` (`to_id`),
  AJOUTER UNE CLÉ `seenx` (`seen`),
  AJOUTER UNE CLÉ `idx` (`id`);


ALTER TABLE `messages`
  MODIFIER `id` int(255) NON NULL AUTO_INCREMENT, AUTO_INCREMENT=5021570 ;
COMMIT;</pré>
<p>J'utilise la requête suivante : </p>
<pre class="brush:php;toolbar:false;">SELECT
  *
DEPUIS
  messages,
  (
    SÉLECTIONNER
      MAX(id) comme dernier identifiant
    DEPUIS
      messages
    OÙ
      (
        messages.to_id = '1' -- l'ID à comparer (l'ID de l'utilisateur connecté)
        OU messages.from_id = '1' -- l'ID à comparer (l'ID de l'utilisateur connecté)
      )
    PAR GROUPE
      CONCAT(
        LE MOINS (messages.to_id, messages.from_id),
        '.',
        GREATEST(messages.to_id, messages.from_id)
      )
  ) comme conversations
OÙ
  identifiant = conversations.lastid
COMMANDÉ PAR
  messages.id DESC

Je ne sais pas comment accélérer les choses pour les utilisateurs ayant beaucoup de conversations, si je dois recréer la structure de la base de données.

Answer 1

Hmm, vous pourriez peut-être essayer d'ajouter des index à vos tables : https://www.drupal.org/docs/7/guidelines-for-sql/the-benefits-of-indexing-large-mysql-tables# : ~ :text=Création%20Indexes&text=Le%20statement%20to%20create%20index,le%20index%20must%20be%20distinct. Assurez-vous d'ajouter un index composite basé sur les lignes que vous interrogez.

Si cela n'améliore pas le temps de votre requête, alors la requête doit être améliorée.

Answer 2

Remarque :

• Utilisez UNION au lieu de OR (voir ci-dessous)
• Des clés redondantes existent. PRIMARY KEY est une clé, alors supprimez KEY(id)
• Ne créez pas aveuglément des index pour chaque colonne ; utilisez plutôt des requêtes pour déterminer quels index, en particulier les index composés, sont réellement utiles.
• Dans GROUP BY et ORDER BY, CONCAT est inutile et peut être contre-productif.
• Pour le type INT, le champ longueur est ignoré. Ce que vous avez est une limite de 2 milliards de valeurs. (Qu'est-ce qui est excessif pour vu, en supposant qu'il n'y ait que 0 ou 1 ?)
• Utilisez la nouvelle syntaxe : JOIN..ON.
• Si vu est uniquement vrai/faux, supprimez son index. (Ou montrez-moi une requête dont vous pensez bénéficier.)

CONCAT-LEAST-GREATEST - Est-ce pour construire un "friends_id" ? Peut-être que ce que vous voulez vraiment, c'est un « conversation_id » ? Actuellement, deux utilisateurs ne peuvent jamais avoir plusieurs « conversations », n’est-ce pas ?

Créez une nouvelle colonne pour conversation_id si vous en avez vraiment besoin. (Actuellement, GROUP BY est inefficace.) Le code suivant élimine le besoin d'un tel identifiant.

( SELECT lastid FROM (
    ( SELECT from_id, MAX(id) AS lastid FROM messages
           WHERE to_id = ? GROUP BY from_id )
    UNION DISTINCT
    ( SELECT to_id,   MAX(id) AS lastid FROM messages 
           WHERE from_id = ? GROUP BY to_id )
                     ) AS x
) AS conversations

Et avoir ces index « couvrants » et « composites » :

INDEX(to_id, from_id, id)
INDEX(from_id, to_id, id)

Suppression de KEY(to_id), KEY(from_id) car mon nouvel index peut gérer toutes les autres tâches de ces deux index.

Je pense que cela a le même effet mais fonctionne plus vite.

Combinez-les :

SELECT  *
    FROM (
            ( SELECT from_id AS other_id,
                     MAX(id) AS lastid
                  FROM messages
                  WHERE to_id = ? GROUP BY from_id )
            UNION ALL
            ( SELECT to_id AS other_id,
                     MAX(id) AS lastid
                  FROM messages 
                  WHERE from_id = ? GROUP BY to_id )
         ) AS latest
    JOIN  messages  ON messages.id = latest.lastid
    ORDER BY  messages.id DESC

(Ajoutez ces deux index)

Plus

J'ai pensé un jour à tort que UNION DISTINCT pourrait remplacer le besoin de conversation_id. mais ce n'est pas la vérité. J'ai tout de suite vu quelques solutions :

• Ajoutez un conversation_id et utilisez-le pour la déduplication. (De plus, j'ai remplacé UNION DISTINCT par UNION ALL, ce qui rend la requête légèrement plus rapide sans modifier les résultats.)
• Mettez les résultats de ma requête dans une table temporaire contenant (from_id, to_id, lastid) ; puis utilisez votre astuce CONCAT-LEAST-GREATEST pour dédupliquer la conversation et enfin JOIN-la avec la table des messages pour obtenir d'autres colonnes ;
• Cette technologie de table temporaire facilite l'écriture et le débogage. Ma troisième suggestion est simplement de combiner ces parties en une seule requête (illisible) avec des instructions Select imbriquées sur 3 niveaux de profondeur.