РЕально быстрая выборка Select из базы данных, как?

Тема в разделе "PHP", создана пользователем silmarion, 20 сен 2014.

Статус темы:
Закрыта.
Модераторы: latteo
  1. silmarion

    silmarion

    Регистр.:
    21 июн 2012
    Сообщения:
    195
    Симпатии:
    19
    Доброго дня, в общем уже несколько дней мучаюсь вопросом.

    Есть огромная база(200кк)
    вида
    id;author;book
    id;author;book
    id;author;book

    мне присылают файлы вида list.txt
    издательство:автор
    издательство:автор
    издательство:автор

    Нужно брать из файла колонку 'книга'

    делать поиск по базе по автору

    и сохранять если найдено совпадение по колонке автор в файл found.txt
    издательство:книга
    издательство:книга
    издательство:книга

    а если не найдено, то сохранять в файл not_found.txt
    издательство:автор
    издательство:автор
    издательство:автор

    Набросал скрипт для этого, но пипец как всё медленно идет, рылся по форумам по поводу многопоточного select и т.п. но толком ничего не вышло из этого.

    Вот пример скрипта
    Код:
    <?php
    set_time_limit(0);
    //ini_set('display_errors',1);error_reporting(E_ALL);   
    $dbhost = "127.0.0.1";$dbuser = "root";$dbpassword = "";$dbname = "i";$link = mysql_connect($dbhost, $dbuser, $dbpassword);mysql_select_db($dbname, $link);
    /////////////////////////////////////////////////////
    $sqlCount = 0;$sqlTime = 0;$timeStart = microtime( TRUE);
    /////////////////////////////////////////////////////
    $data = file("find.txt");
    $ff = fopen("found.txt", "a");
    $nf = fopen("found_not.txt", "a");
    for ($i = 0; $i < count($data); $i++) {
    $pieces = explode(":", $data[$i]);
    $pie = preg_replace("/\s/","",$pieces[1]);
    
    $ath = mysql_query("select `author` from `izd` where `book` = '$pie'  LIMIT 1");
    $sqlCount++;
    if($ath) {
    $author = mysql_fetch_array($ath);
    
    if(!empty($author['author'])){
    $mytext1 = $pieces[0].":".$author['author']."\n";
    $test = fwrite($ff, $mytext1); }
    
    if(empty($author['author'])){
    $mytext2 = $pieces[0].":".$pieces[1];
    $test = fwrite($nf, $mytext2); }
    
    }
    else { echo "<p><b>Error: ".mysql_error()."</b></p>"; exit(); }
    }
    //////////////////////////////////////////////////////////////
    $sqlTime += microtime( TRUE) - $timeStart;
    printf( '%d запросов за %01.2f секунд.', $sqlCount, $sqlTime);
    /////////////////////////////////////////////////////////////
    ?>
    
    Как всё это дело ускорить?
    Потому что ну оооооочень долго выборка идет.

    Пишут на форумах что можно подобное запускать в много потоков и т.п.
    Но как реализовать не нашел.
     
  2. SocMaster

    SocMaster aka Hakerok

    Регистр.:
    26 июл 2011
    Сообщения:
    202
    Симпатии:
    47
    Ну как вариант, самый простой. если это единоразово сделать надо. то создайте например 5 файлов, в каждом поставьте лимит в выборке, например в первом файле, первых 500 000 в втором с 500 000 записи по миллионную и так далее
     
    silmarion нравится это.
  3. CAPAXA

    CAPAXA

    Регистр.:
    7 июн 2007
    Сообщения:
    896
    Симпатии:
    535
    Попробуйте:
    1) Использовать индексы в БД
    2) Делать запрос в БД, не по каждой строчке отдельно, а "пакетно" (например по 100) используя IN

    Или как вариант выгружать из БД всю инфу, перегонять в ассоциативный массив, и по ключам делать проверку на наличие.
     
    unpunk и silmarion нравится это.
  4. silmarion

    silmarion

    Регистр.:
    21 июн 2012
    Сообщения:
    195
    Симпатии:
    19
    индексы проставлены

    Насчет ассоциативного массива идея интересная, но там ведь нельзя делать проверку на уникальность колонок?

    А не мог бы сделать набросок с IN?
    Код:
    $query="select `author` from `izd` where `book` in (";
    for($j = 0; $j < count($urls); $j++) {
    $pieces = explode(":", $urls[$j]);
    $pie = preg_replace("/\s/","",$pieces[1]);
    $query.="'$pie', ";
    }
    $query.=")";
    $query=str_replace("', )","')",$query);
    //echo $query;
    
    $ath = mysql_query($query);
    $sqlCount++;
    while($author = mysql_fetch_array($ath)){
        printf ("%s:%s<br>", $pieces[0], $author[0]);
    }
    
    Намного быстрее чем по одиночке, но опять проблема возникла, как и с multi_query пробовал, как если найдено совпадение подставить если найдена book в бд как его совместить с издательством?
    И если большой текстовый файл делать то получаю MySQL server has gone away
    Надо еще на части разбить запросы


    Все равно медленно.
     
    Последнее редактирование: 20 сен 2014
  5. CAPAXA

    CAPAXA

    Регистр.:
    7 июн 2007
    Сообщения:
    896
    Симпатии:
    535
    Забыл особенность IN, он не работает с индексами. Поэтому получится медленнее чем было.
    Что значит уникальность колонок?
     
  6. Hannibal86

    Hannibal86 Создатель

    Регистр.:
    17 сен 2014
    Сообщения:
    37
    Симпатии:
    17
    Отсутствие повторяющихся записей
     
  7. CAPAXA

    CAPAXA

    Регистр.:
    7 июн 2007
    Сообщения:
    896
    Симпатии:
    535
    Так это пусть БД контролирует.
     
  8. Hannibal86

    Hannibal86 Создатель

    Регистр.:
    17 сен 2014
    Сообщения:
    37
    Симпатии:
    17
    100%. Иначе лишняя ресурсозатрата.
     
    silmarion и CAPAXA нравится это.
  9. silmarion

    silmarion

    Регистр.:
    21 июн 2012
    Сообщения:
    195
    Симпатии:
    19
    а как насчет multi_query?

    Код:
    $urls = file("ttt.txt");
    $query  = "SELECT CURRENT_USER();";
    for ($i = 0; $i < count($urls); $i++) {
    $pieces = explode(":", $urls[$i]);
    $pie = preg_replace("/\s/","",$pieces[1]);
    $query.= "select `author` from `izd` where `book` = '$pie';";
    }
    if (mysqli_multi_query($link, $query)) {
      do {
      
      if ($result = mysqli_store_result($link)) {
      while ($row = mysqli_fetch_row($result)) {
        
      printf("%s\n", $row[0]);
      $sqlCount++;
      }
      mysqli_free_result($result);
      }
      
      if (mysqli_more_results($link)) {
      //  printf("-----------------\n");
      }
      } while (mysqli_next_result($link));
    }
    
    
    mysqli_close($link);
    
    
    Тоже вроде побыстрее, но не могу вкурить как мне выводить и сохранять $pieces[0].":".$row[0]
    и если не найдено сохранять $pieces[0].":".$pieces[1]
    ?
    никак не могу с порядком for разобраться((
    найденные $row[0] выводит
    а если подставляешь $pieces[0] то естественно выводит первое значение во всех строках
     
    Последнее редактирование: 21 сен 2014
  10. Шумадан

    Шумадан Хабарра!!11

    Регистр.:
    6 фев 2008
    Сообщения:
    1.722
    Симпатии:
    2.097
    выгружайте файл в отдельную таблицу и решайте джоинами, результат выкидывайте в файл
    по сути вам нужно будет один-два запрос на существования, остальное это инверсия от существующего набора
    а потом результат уже итерацией в файл

    допустим есть табличка
    books
    id;author;book

    вы на основе файла создаёте другую таблицу
    search_book
    id;publisher,author

    на оба поля автора (author) в обоих таблицах ставите индекс

    потом
    SELECT sb.id, sb.publisher, sb.author, b.author as found_author FROM search_book sb
    LEFT JOIN books b ON b.author = sb.author

    в результате получите табличку
    id, publisher, author, found_author

    где если автор найден в поле found_author будет имя, в противном случай - в поле found_author будет null
    в таком подходе вы полагаетесь на быстродействие мускула
     
    Последнее редактирование: 21 сен 2014
    silmarion нравится это.
Статус темы:
Закрыта.