Как ускорить время SELECT MySQL?

Question

[Никита]

Существует база с 1 таблицей на 11 млн строк. Поиск выполняется по 1 столбцу varchar(128). Стоит задача осуществлять поиск не более чем за 1 секунду, сейчас это значение намного выше, при чем ресурсы сервера при поиске используются по минимуму (не более 30%). Возможно ли реализовать эту задачу и если да, то какие есть методы?

EXPLAIN SELECT * FROM `main` WHERE `partname` LIKE "%поисковой запрос%" LIMIT 100

По железу: 2 ядра, 2Гб памяти и SSD - это все что мне удалось узнать


my.cnf

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

Answer 1

[FanatPHP]

Во-первых не позорься, убери тег big data. 11 лямов - это не бигдата, а кошкины слёзки.
Во-вторых, надо учиться задавать вопросы.
Не вопрос вида "доктор, у меня рука болит, как сделать чтобы не болела", а подробно излагать, что за поиск, что за запрос, что за данные, что за столбец, есть ли индекс, что показывает explain.

В текущем же виде ответ только один - помазать зеленкой добавить индекс
если индекс есть, и поиск идет по полному совпадению, то смотреть, влезает ли индекс в память
Если поиск идет по частичному совпадению - то выносить его в отдельный сервис, sphinx/elastic

Comments

[Никита]

Поправил вопрос

[FanatPHP]

поиск лайк с процентом спереди не использует индекс.
надо или добавить фуллтекст индекс и использовать запрос match, или, как я говорил выше, выносить его в отдельный сервис, sphinx/elastic

или посмотреть что мы такое ищем и можно ли уйти от лайка или хотя бы от процента спереди

[Никита]

FanatPHP, я изменил запрос на следующий

SELECT * FROM `main` WHERE partname="поисковой запрос"

при этом phpmyadmin говорит, что запрос занимает 0.0004 сек, что является очень хорошим результатом, но вот сервис через который я проверяю время выполнения скрипта, показывает другое время 0.4296 сек ссылка на сервис

замеры делаю по последней строке в базе

PHP скрипт выглядит так

<?php
    $host = '*****';
    $user = '*****';
    $pass = '*****';
    $base = '******';

    $search = '';

    # Проверка наличия параметра search
    if(isset($_REQUEST['search']) && $_REQUEST['search'] != '') {
        $search = str_replace('%', '', $_REQUEST['search']);;
    }

    # Соединение с базой данных
    if($dbh = mysqli_connect($host, $user, $pass)) {
        mysqli_select_db($dbh, $base);

        $sth = mysqli_query($dbh, "SELECT * FROM `main` WHERE partname=".addslashes($search));

        if(mysqli_errno($dbh) > 0) {
            header("HTTP/1.1 500 Internal Server Error");
            print "<h1>500 Internal Server Error</h1>\nQuery Error";
            exit;
        }

        ob_start();
        print "<data version='2.0'>\n";

        if(mysqli_num_rows($sth) > 0) {
            # Идем по каждой найденной записи
            while($row = mysqli_fetch_array($sth)) {

                print "<item>\n";

                # Название позиции
                print "<part>".$row['partname']."</part>\n";

                #
                print "<instock>1</instock>\n";

                # Производитель
                if($row['Mfr'] != '')
                    print "<mfg>".$row['Mfr']."</mfg>\n";

                # Описание
                if($row['Description'] != '')
                    print "<note>".$row['Description']."</note>\n";

                # Ссылка на PDF
                if($row['Misc'] != '')
                    print "<pdf>".$row['Misc']."</pdf>\n";

                # Артикул
                if($row['Compare'] != '')
                    print "<sku>".$row['Compare']."</sku>\n";

                # Мин. заказ
                if($row['Min Qty'] != '')
                    print "<moq>1</moq>\n";

                # Цена
                if($row['Price'] != '')
                    print "<pb qty=\""."1\">".str_replace('$', '', $row['Price'])."</pb>\n";

                # Валюта
                print "<cur>USD</cur>\n";

                # Сроки доставки
                print "<dlv>Доставка от 5 до 7 дней</dlv>\n";

                # Сылка на изображение
                if($row['ImgUrl'] != '')
                    print "<img>https:".$row['ImgUrl']."</img>\n";

                # Тип упаковки
                if($row['Package'] != '')
                    print "<pack>".$row['Package']."</pack>\n";

                # Состояние склада
                print "<stock>В наличии</stock>\n";

                print "</item>\n";
                mysqli_free_result($sth);
            }

            print "</data>\n";
            $content = ob_get_contents();
            ob_clean();

            header("Content-type: application/xml");
            print '<?xml version="1.0" encoding="UTF8" ?>'."\n".$content;

            mysqli_close($dbh);
        } else { 
            header("HTTP/1.1 500 Internal Server Error");
            print "<h1>500 Internal Server Error</h1>\nCould not connecto to database";
            exit;
        }
    }
?>

Могут ли быть проблемы на этот раз в PHP скрипте, а не в БД? БД расположена на том же сервере, что и скрипт.

[FanatPHP]

а индекс, индекс-то на эту строку есть?

[Никита]

FanatPHP, выставлял запросом ALTER TABLE `main` ADD INDEX(`partname`)

[FanatPHP]

а explain что говорит?

explain  SELECT * FROM `main` WHERE partname="поисковой запрос"

[Никита]

FanatPHP,

[FanatPHP]

И этот запрос отрабатывает пол-секунды?
показывай
show index from main;
show table status from имя_базы where Name=main;
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

[Никита]

show index from main


show table status from имя_базы where Name=main;
#1054 - Unknown column 'main' in 'where clause'

innodb_buffer_pool_size = 134217728

[FanatPHP]

128 метров под индексы - это какой-то адов мазохизм.
кто вам базу данных настраивал?
зачем индексы дублируются? места под них и так нету, а ты их еще и удваиваешь.

показывай
show table status from имя_базы where Name='main';

[Никита]

FanatPHP,

[FanatPHP]

ну вот смотри, у тебя индексы только для этой таблицы занимают 66 метров
а всего базе под буфер отведено 128 метров.
судя по всему базе тупо не хватает оперативки. надо выделить под буфер больше памяти
в общем случае под innodb_buffer_pool_size выделяют 80% оперативки, имеющейся на сервере БД

ну и надо убрать дублирующиеся индексы

я только не понял, откуда взялось 11 лимонов строк, если у тебя тут только 500 000

[Никита]

FanatPHP, всего в базе 11 млн, по некоторым причинам пока что уменьшилось кол-во строк, но и для 500к мне кажется слишком долго выполняется запрос.

1. Убираю дубл. индексы
2. Под innodb_buffer_pool_size через my.cnf выделяю 80% оперативки

Верно?

[FanatPHP]

верно, если это только сервер БД, а не выполняет и другие функции

[Никита]

FanatPHP, нет не выполняет, сервак только под БД

[FanatPHP]

ну тогда 80% смело

[FanatPHP]

смотри, размер индекса для этой колонки - 387 байт на одну строку
это дает 4 гига на 11 миллионах строк.

в общем, если памяти не будет хватать, то есть варианты
например делать индекс не по всему полю, а первые n символов
или сделать дополнительное поле с кодировкой latin1 с crc32 от названия, и проиндексировать по нему, а с самого partname индекс снять
и искать
explain SELECT * FROM `main` WHERE crc=crc32("поисковой запрос") and partname="поисковой запрос"

[Никита]

FanatPHP, в принципе увеличить оперативку до тех же 8 гб не проблема, 2 гб взяли для теста, главное что бы это помогло. Спасибо.

[FanatPHP]

ну надо понимать что буфер используется не только для одного индекса, и не только для индексов. то есть и 8 может не хватить. и в этом случае есть варианты, о которых я написал

в общем пробуйте, о результатах пишите

Answer 2

[Vitaly Karasik]

Возможно ли реализовать эту задачу и если да, то какие есть методы?

Шлите нам
- параметры железа
- explain вашего запроса
- my.cnf

Comments

[Никита]

Поправил вопрос

[Vitaly Karasik]

Все как сказал FanatPHP - MySQL не может эффективно работать с таким текстовым поиском, то есть не умеет использовать индекс.
Если нельзя использовать что-то типа

WHERE `partname` LIKE "поисковой запрос"

то быстро не получится.