Forum Programmation.autre table SQL à optimiser

Posté par (page perso) . Licence CC by-sa.
Tags :
0
14
avr.
2014

Je connais très peu les bases de données et j'aimerais savoir comment optimiser une table pour un projet Python3/sqlite3. Voici les contraintes :

  • 5 champs dont deux sur lesquels portent les recherches dans la base : "form"(text) et "searchfreq"(integer)
  • je n'ai pour le moment pas créé d'index unique
  • la base, une fois créée, s'enrichit à l'aide d'INSERT successifs; aucune autre opération n'est effectuée en écriture sur la base.
  • une fois la base créée, des recherches portant sur les champs "form" et "searchfreq" sont possibles. Une recherche typique permet de chercher la/les ligne(s) correspondant à un "form" donné; une autre recherche possible est de récupérer les lignes ayant le "searchfreq" le plus élevé.
  • la base de données contiendra quelques centaines de milliers de lignes.

Pour le moment, j'ai très simplement créé la table de la manière suivante :

sql_order = "CREATE TABLE forms " \
     "(form TEXT, " \
     "searchfreq INTEGER, " \
     "sourceword TEXT, " \
     "inflection_id INTEGER, " \
     "context TEXT)"

Tout ceci fonctionne bien mais j'aimerais savoir comment accélérer les recherches sur "form" et sur "searchfreq" : pour le moment, la base de données est encore petite et j'aimerais anticiper la suite.

Merci d'avance !

  • # Re:

    Posté par . Évalué à 2.

    Salut,

    "Anticiper la suite" c'est d'abord mettre une clé primaire sur ta table. Il faut que tu puisses identifier une ligne facilement sans te baser sur "form" qui plus est est au format TEXT.

    Pour les index c'est simple: un sur form, un sur searchfreq.

    Après, les INTEGER c'est très bien, par contre les TEXT ca l'est moins. Si tu peux éviter, évite, ou remplace les au moins par des VARCHAR si leur contenu ne dépasse pas qqs centaines de caractères.

    Je ne sais pas ce que ta table va contenir, mais par exemple, si la colonne context contient souvent la même chose, crée plutôt une table contexts (context_id INTEGER, context VARCHAR) que tu pourras référencer dans ta table forms.

    • [^] # Re:

      Posté par (page perso) . Évalué à 1.

      apkwa : Merci pour tous ces renseignements. J'ai pu ajouter toutes les modifications recommandées sauf le "context_id" car "context" est rempli d'informations non redondantes. Je publierai bientôt sur LinuxFr un journal faisant référence au coup de pouce que tu viens de me donner.

      Trust the Python !

      • [^] # Re:

        Posté par . Évalué à 2.

        De rien.
        Je viens de voir ton journal, et le projet est intéressant. Bon courage!

    • [^] # Re:

      Posté par (page perso) . Évalué à 2.

      j'ai une question un peu idiote…

      un index sur un champ qui va être unique ? La probabilité qu'il y ait pas 2 champs form identique est faible, ainsi il me semble que le parcours de l'index sera aussi long que le parcours de la table, rendant l'opération pas tellement intéressante, sauf à augmenter (doubler ?) la place sur le disque ?

      • [^] # Re:

        Posté par . Évalué à 1.

        Salut,

        Ben en fait, dans le post original, il est indiqué que pour l'instant, il n'y a aucune clé unique.
        Ceci dit, si tu ajoutes une contrainte d'unicité sur ta colonne, en fait tu n'as pas le choix: un index est créé implicitement.
        Si ce n'était pas le cas, ca vaudrait tout de même le coup d'en créer un car il est ordonné, ce qui n'est pas le cas de ta table. Donc la recherche est tout de même (beaucoup) plus rapide avec un index, même si tu as des valeurs uniques dans tous tes champs (tu évites le full scan).

        • [^] # Re:

          Posté par (page perso) . Évalué à 2.

          je ne parlais QUE pour le champ text qui va se retrouver avec le contenu du champ form. Dans mon esprit pervers, c'est un champ libre ou on peut taper ce que l'on veut, y mettre un index, c'est … intéressant, à étudier.

          J'avais il y a une pelle d'année mis des index full search sur des champs textes dans le seul but de faire une recherche par pertinence et je n'ai été que moyennement convaincu, parce que je n'avais pas assez d'enregistrement moins de 1000 avec peu de proximités et plein de mots équivalent et des textes long pour que cela change vraiment la donne : par exemple avoir entre 1.5 et 4.5 % de pertinence sur une recherche ça affole pas les foules, même si avec très peu de travail ca claque sa mère pour un lambda (ce que sont les utilisateurs).

          A faire des tests (mais là encore pas assez d'enregistrements pour faire un vrai test et dupliquer ne sert à rien car on sort du cadre) je ne voyais pas une grosse différence avec une recherche like %tructruc% or like %tructruc% … à part la simplicité de ne pas se faire chier à gérer ses requêtes multis mots et à part le fait de ne plus pouvoir faire des +mots -mots pour choisir d'avoir ou pas certains mots.

          Je ne pense pas que le tri alphabétique des enregistrements soit un phénomène pertinent puisqu'il m'avait semblé comprendre que le concept de bdd c'est que chaque enregistrement (la feuille) est à la même distance du tronc que n'importe quel autre et donc que chercher le mot werrtd va aussi vite que chercher azert et les rentrer dans l'ordre ne change pas grand chose.

          Enfin des bases comme mysql sont/étaient(?) pauvres en fonctionnalités par choix de pouvoir traiter rapidement de grosses masse de données, contrairement à certaines autres (oracle,posgresql..) qui pour offrir plus de fonctionnalités avait des temps de recherche moins performant hors tweekenning complexe à mettre en oeuvre et adapté au cas par cas à la suite de tests et tests en tout sens.

          Mais je veux bien envisager que je me fourvoie, maintenant le coup de l'ordre alphabétique et du tri, ca fait tomber un peu tout un pan de ce que je croyais savoir.

          • [^] # Re:

            Posté par . Évalué à 3.

            Tout d'abord, attention sur les index sur des colonnes TEXT, ce n'est pas supporté par tout le monde. Je ne sais pas pour sqlite, mais MySQL ne le permet qu'avec un moteur MyISAM.

            je ne voyais pas une grosse différence avec une recherche like %tructruc% or like %tructruc%

            C'est normal, car dans ce cas, tu n'utilises pas l'index. L'index créé implicitement n'est utilisé que si tu as le début des valeurs contenues dans tes colonnes (càd pas de %pattern% mais des pattern%). Dans ce cas, il faut créer un index full-text, et utiliser les fonctions qui vont avec (cf MATCH pour du MySQL) mais la encore, je renvoie au début de ma réponse: tout le monde ne supporte pas forcément le full-text search.

            puisqu'il m'avait semblé comprendre que le concept de bdd c'est que chaque enregistrement (la feuille) est à la même distance du tronc que n'importe quel autre et donc que chercher le mot werrtd va aussi vite que chercher azert et les rentrer dans l'ordre ne change pas grand chose.

            Oui, tout est "presque à la même distance" si tu utilises des index. Ce n'est pas du tout vrai sans index.
            Certains SGBD permettent d'ordonner ta table, de telle sorte que les nouvelles lignes s'insèrent au bon endroit (pas toujours au tout bas de ta table). Dans ce cas, effectivement, ta table te sert d'index directement, mais ce n'est pas fait sans action de ta part (cf clustered index, avec pas mal de limitations en MySQL).

            En fait, je crois que tu penses que quand tu insères une nouvelle ligne dans une table, celle-ci est rangée dans l'ordre alphabétique ou croissant. En vrai, non: ta nouvelle ligne est simplement mise à la fin. Du coup, sans index, tes recherches doivent regarder chaque lignes de la première à la dernière.

            Enfin des bases comme mysql sont/étaient(?) pauvres en fonctionnalités par choix de pouvoir traiter rapidement de grosses masse de données, contrairement à certaines autres (oracle,posgresql..) qui pour offrir plus de fonctionnalités avait des temps de recherche moins performant hors tweekenning complexe à mettre en oeuvre et adapté au cas par cas à la suite de tests et tests en tout sens.

            Oui, MySQL est toujours assez pauvre, mais ca s'améliore beaucoup avec le temps. Malgré tout, il y aura toujours un fossé entre MySQL et Postgres ou Oracle que tu cites.
            Pour les temps de recherche, je ne suis pas d'accord. Globalement ils se valent à peu près (hors tweakening comme tu dis :) sinon MySQL est à la ramasse)

            Et pour le adapté au cas par cas, oui, malheureusement, chaque BDD doit être adaptée au cas par cas… Il n'y a jamais de solution universelle.

            • [^] # Re:

              Posté par (page perso) . Évalué à 3.

              Globalement d'accord, sauf sur

              En fait, je crois que tu penses que quand tu insères une nouvelle ligne dans une table, celle-ci est rangée dans l'ordre alphabétique ou croissant. En vrai, non: ta nouvelle ligne est simplement mise à la fin. Du coup, sans index, tes recherches doivent regarder chaque lignes de la première à la dernière.

              En fait, cela dépend de comment est créé l'index. Je prends l'exemple de SQL Server (sorry, mais c'est la base de données que je connais le mieux !), il existe des index cluster et non-cluster. La différence ? Les données sont ordonnées selon l'index (cluster) ou pas (non cluster). Bien entendu, il ne peut y avoir qu'un seul index cluster par table (et c'est généralement la clé primaire d'ailleurs)

              L'avantage est que cela permet d'accélérer les requêtes qui manipulent des enregistrements "proches" selon l'index. Par exemple, si on a une table de personne et que l'on fait une recherche par nom, alors la requête sera accélérée car les données seront présentes dans des pages proches voir contigües sur le disque. Tandis qu'un index non cluster ne permettrait pas de regrouper les enregistrement. Il faudrait alors charger plus de pages moins bien répartie (plus d'accès disque, donc requête plus lente).

              Bon, ça c'est surtout si la bd est conséquente et ne peut tenir en mémoire. Si tout est en mémoire, l'impact n'est pas aussi sensible…

              • [^] # Re:

                Posté par . Évalué à 2. Dernière modification le 16/04/14 à 10:24.

                Tout à fait d'accord.
                Je ne connais pas MS SQL, mais effectivement, les index cluster permettent d'ordonner tes données. Je me suis peut-être mal exprimé dans ma réponse précédente (un peu au dessus du passage que tu cites, je parle des clustered index de mysql).

                Ceci dit, merci pour tes explications, car je ne connais pas très bien ces types d'index.

  • # MySQL ou sqlite ?

    Posté par (page perso) . Évalué à 6.

    Tu parles d'optimiser du MySQL pour un projet qui utilise sqlite3. T'es sûr que tu ne veux pas de l'aide sur Postgresql ?

    • [^] # Re: MySQL ou sqlite ?

      Posté par . Évalué à 3.

      j'avais pas osé lui demandé ;)

      • [^] # Re: MySQL ou sqlite ?

        Posté par (page perso) . Évalué à 1.

        J'ai "pertinenté" vos remarques : je suis convaincu de l'intérêt de PostGreSQL mais il me manque un peu de temps pour basculer mes applications. Disons que le passage par sqlite3 n'est qu'un début.

        Trust the Python !

  • # La règle sainte de l'optimisation de base

    Posté par . Évalué à 2.

    "Quand ta base dans le sac sera par optimiser tes requêtes et tes indexes tu commencera."

  • # des pistes...

    Posté par (page perso) . Évalué à 2.

    Plusieurs possibilités.

    Tout d'abord, au sujet de l'absence de clé primaire. Ce n'est pas une obligation et n'est a priori pas nécessaire pour la suite. Tant que tu n'as pas besoin d'identifier une ligne avec précision, c'est inutile. Mais si un jour tu as besoin de modifier/supprimer des enregistrements, alors il est plus que conseiller d'en avoir une. Est-ce que la colonne "form" est contient des valeurs unique ?

    D'après ce que tu dis, tu cherches surtout à optimiser les recherches. Comme tu ne parles pas de l'insertion, je ne prendrais pas en compte l'optimisation des requêtes d'insertions de données.

    Commençons par le plus simple : searchfreq. C'est un entier, un index dessus sera très bien. Il devrait même être possible d'y mettre un partitionnement basé sur cette colonne. Cela augmentera la vitesse des requêtes sur la recherche via cette colonne mais pourra avoir un léger impact sur les autres (mais là, tant que tu n'auras pas fait de test, impossible de le savoir).

    Maintenant, la colonne form. Tout d'abord, il faut choisir avec soin le type de la colonne. Mais cela dépend de ton SGBD. Il faut donc te renseigner ou au moins nous dire avec précision le SGBD que tu souhaites utiliser. Mais en règle général, si la colonne contient de "petites" chaines de caractères, il vaut mieux un VARCHAR(n), voire un CHAR(n) en fonction des besoins.

    Un aspect important à prendre en compte, c'est la collation. Kesako ? Lorsque tu as un champs texte, la collation permet de définir comment les recherches sont effectuées (prise en compte de la casse ou pas, gestion des accents, etc…). Le mieux, pour accélérer les recherches, c'est d'avoir une recherche sensible à la casse et aux accents. Ainsi, le SGBD fait une comparaison binaire lors des recherches.

    Si tu n'as pas besoin de tenir compte de la casse et que tu te fiches des accents ('e', 'é' et 'è' sont égaux), alors, peut être qu'ajouter une colonne contenant une version normalisée de ta colonne "form" pourrait apporter un gain de performance. Quand je dis normalisé, je veux dire une version sans accent et en minuscule par exemple. Et faire les recherches sur cette colonne et non sur la colonne form. Et mettre l'index sur cette nouvelle colonne, bien entendu ! Mais encore une fois, difficile de dire à l'avance l'impact que cela peut avoir sans avoir de données. Car le soucis de cette méthode, c'est qu'elle va augementer de manière sensible la taille de ta base de données. Si cette augmentation provoque une pénurie de mémoire vive et provoque donc de la pagination, alors cela va drastiquement ralentir ta base de données au lieu de l'accélérer !

    Maintenant, pour t'aider au mieux, voici ce qu'il nous faudrait :
    - un jeu de données d'exemple
    - des requêtes SQL types à optimiser

    • [^] # Re: des pistes...

      Posté par (page perso) . Évalué à 1. Dernière modification le 16/04/14 à 22:50.

      Merci pour ta longue réponse !

      "form" peut contenir plusieurs enregistrements identiques. Par exemple, "vis" peut venir du verbe "voir" (1 enregistrement) ou du verbe "vivre" (un autre enregistrement).

      Je cherche avant tout à optimiser la recherche, pas l'insertion.

      J'ai remplacé mon type TEXT par un VARCHAR(255).

      La recherche doit se faire en tenant compte des accents, de la casse, … Je ne peux pas simplifier le texte à ce niveau.

      Je donne ici un exemple d'image texte de la base de données. Les champs sont séparés par un carré.

      L'unique requête SQL que je pensais autoriser était très simplement :

                  sql_order = "SELECT sourceword, inflection_id, context, searchfreq "
                              "FROM forms "
                              "WHERE form=?"
      

      Merci de t'être penché sur mon projet !

      Trust the Python !

      • [^] # Re: des pistes...

        Posté par (page perso) . Évalué à 2.

        La recherche doit se faire en tenant compte des accents, de la casse, … Je ne peux pas simplifier le texte à ce niveau.

        Si ta recherche doit tenir compte de la casse et des accents, alors il n'y a rien à simplifier ! Il faut juste s'assurer que la recherche fasse une recherche en prenant en compte ses caractéristiques.

        Ainsi, pour être sur que ta recherche soit sensible à la casse et aux accents tu peux :
        - soit lors de la création de ta table préciser la collation ;

        CREATE TABLE forms
             (form TEXT,
             searchfreq INTEGER,
             sourceword TEXT, 
             inflection_id INTEGER, 
             context TEXT) COLLATE utf8_general_cs
        • soit dans ta requête de recherche, préciser la collation que tu souhaites.
        SELECT sourceword, inflection_id, context, searchfreq
                 FROM forms 
                 WHERE form COLLATE utf8_general_cs = ?

        Vu le type de données que tu vas avoir, je t'invite également à t'assurer que l'index que tu créés au niveau de la colonne 'form' sera un index cluster (au sens SQL Server, je ne sais pas si MySQL utilise la même définission). Cela permet de s'assurer que les enregistrements de la table sont physiquement organisé en fonction de l'index. Ainsi, des enregistrements proches seront proches physiquement, limitant le nombre d'accès disque nécessaire.

        PS : Pour les exemples de requête, je ne les ai pas testé, j'ai juste fait une rapide recherche sur le net. Il est possible qu'il faille les adapter un peu. Mais au niveau de la collation, le suffixe _cs est très important (cs = Case Sensitive). Souvent, par défaut, c'est du ci (Case Insensitive)

Suivre le flux des commentaires

Note : les commentaires appartiennent à ceux qui les ont postés. Nous n'en sommes pas responsables.