uForum.uz - [Без оффтопа] Развлекаемся с SQL

uForum.uz (https://uforum.uz/index.php)

- Разное/IT (https://uforum.uz/forumdisplay.php?f=398)

- - [Без оффтопа] Развлекаемся с SQL (https://uforum.uz/showthread.php?t=19923)

Развлекаемся с SQL

Ставим задачи - находим эффектные решения.
(В базе MySQL)

1. Виртуальный колхоз "Кизил пиёз. Пять бригад каждый день сдают на склад пиёз в кг.
Текущий отчёт:
За день. За неделю. За месяц. За квартал. За год.

ps. Без флейма плиз.
Красивые решения плюсуем.

2. Дано 1М записей;
Не самый производительный сервер;

Нужно: ORDER BY RAND() LIMIT 10;

В качестве временного решения работает:

Первый запрос: узнаем количество записей в базе ($min, $max)
В PHP генерим 10 "случайных" индексов: $id[$i] = rand($min, $max);
Второй запрос: UNION (SELECT * FROM `a` WHERE `id` = $id[0] LIMIT 1) UNION (SELECT * FROM `a` WHERE `id` = $id[1] LIMIT 1)… и так далее.
Проверяем результат, если записи по одному из ID не было (в ID бывают "дырки"), и получили меньше чем 10, добираем нужное количество запросов.

Такая штуковина работает быстро (в сотни, и даже тысячи раз быстрее родного BY RAND()), но неэстетично. :)

`id` in уже не ?

Я как-то работал с записями больше чем 1М, там я реализовал "сложную (с relations)" выборку через временные таблицы. Работает не то что быстро, а моментально.

Цитата:

Сообщение от Denis Shlyapnikov (Сообщение 945295)

там я реализовал "сложную (с relations)" выборку через временные таблицы

Чем это поможет при сортировке?
Или это к вопросу №1 было?

Нет, Руслан. Это к вашей задачи решение. Спокойно можно использовать ORDER BY RAND() + дополнительные маневры в виде where, group, join

Цитата:

Сообщение от Denis Shlyapnikov (Сообщение 945300)

к вашей задачи решение. Спокойно можно использовать ORDER BY RAND() + дополнительные маневры в виде where, group, join

Так хотелось бы решение как раз и увидеть.
Здесь его нет, только слово «маневры». :)

Зачем узнавать max если дано 1М записей?

Какое практическое значение у order by rand()? Сортировка по случайному значению?

Ну в общем читабельно тут

http://hudson.su/2010/09/16/mysql-optimizaciya-order-by-rand/

Цитата:

Сообщение от Alisher Umarov (Сообщение 945342)

Зачем узнавать max если дано 1М записей?

Это ведь приблизительно.
Элементы добавляются/удаляются.

Цитата:

Сообщение от Alisher Umarov (Сообщение 945342)

Какое практическое значение у order by rand()? Сортировка по случайному значению?

Порой нужно показать что-то в случайном порядке.

Примерно так можно работать с большим объемом данных, где нужно делать выборки:
create temporary table`temp` select * `db` .... , дальше уже обращаться ко временной таблице select .. from `temp`

Цитата:

Сообщение от Denis Shlyapnikov (Сообщение 945349)

Реально не понимаю, как это поможет при сортировке 1М полей с данными.
Можно подробнее?

Скорость Руслан, Скорость. Это альтернатива вашему временному решению.
После чего можно смело писать
select * fromt temp order by rand()

Цитата:

Сообщение от Denis Shlyapnikov (Сообщение 945355)

Так сортировка будет так же в памяти, что и вызывает основные тормоза при by rand().
Но за идею спасибо, нужно будет апробировать на практике. :187:

Мне понравился ответ с выборкой по номерам строк, которые не могут быть с дырами.

Предложение. Давайте примеры ближе к жизни. Нас люди (молодые патриоты) читают. ;)
Для специфики есть гугл или хабр.

Перебор базы в памяти работает во много раз быстрее, попробуйте, возможно, вы и не заметите задержки при рандомной выборки

Цитата:

Сообщение от Denis Shlyapnikov (Сообщение 945349)

На сколько я въехал для этого ещё базу подготовить стоит. Памяти для temp может не хватить.

Цитата:

Сообщение от Denis Shlyapnikov (Сообщение 945369)

Не все так просто: http://habrahabr.ru/post/54176/

Цитата:

В процессе выполнения этого запроса MySQL записывает во временную таблицу все (!!!) строки исходной таблицы, с одним новым полем, в которое записываются результаты функции RAND () — т.е. набор произвольных значений. Затем эта временная таблица сортируется filesort по добавленному полю с произвольными значениями и далее выбираются первые 10 записей. Полный ппц. А теперь представтье что будет если в исходной таблице 10 000 записей. А что если 1 000 000? А что если эту выборку надо делать раз десять в секунду. Да тут любой супер-пупер сервер надолго уйдет в раздумья.

Цитата:

Сообщение от Alisher Umarov (Сообщение 945367)

Давайте примеры ближе к жизни.

Задача, кстати, из практики.

ну, это да, к ~ для 1млн записей (таблица, в которой есть поле `text`) требуется 1GB RAM.
Мой способ это скорее исключение, чем правило для использования. Он будет оправдан, если с этой таблицой будут происходит несколько манипуляций, но и для order by random() вполне подойдет, мне кажется.

Руслан, у меня как раз есть подопытная таблица от форума gamertalk.uz (около 0.5М постов)
Завтра потестю и ваш и свой вариант. Результат скинут сюды.

Цитата:

Сообщение от JackDaniels (Сообщение 945372)

Цитата:

Сообщение от Alisher Umarov (Сообщение 945367)

Давайте примеры ближе к жизни.

Задача, кстати, из практики.

Не спорю. Просто с трудом представляю что в поле ID можно допустить "дыры".

имеется ввиду "дыры" - что запись удалили, при условии что там AI в таком режиме работает, что не заполняет удаленные ID

Цитата:

Сообщение от Denis Shlyapnikov (Сообщение 945378)

А, понятно. Операции в живой базе? Ясно.

Кстати есть скрипты забить базу данными для теста? С возможностью выбора кол строк?

Цитата:

Сообщение от Denis Shlyapnikov (Сообщение 945375)

Было бы шикарно — благодарю!

Цитата:

Сообщение от Alisher Umarov (Сообщение 945379)

Цитата:

Сообщение от Denis Shlyapnikov (Сообщение 945378)

Да в принципе, вот, написал сейчас небольшой скрипт, который генерирует и заполняет базу данными:

Скрытый текст:

PHP код:


		
			
<?php
/**
 * @author Denis
 * @copyright 2013
 */
 
// HEADER
header('Content-type: text/html; charset=utf-8');
mb_http_input('UTF-8'); 
mb_http_output('UTF-8'); 
mb_internal_encoding("UTF-8");

set_time_limit(0);

if(ini_get('max_execution_time') != 0)
    exit('Время выполнения ограничено!');
    
error_reporting(E_ALL | E_STRICT) ;
ini_set('display_errors', 'On');

$sample_text = "Lorem ipsum dolor sit amet, consectetur adipiscing elit. Cras egestas, arcu at gravida bibendum, mi sapien tempor nisl, non malesuada justo nibh ac elit. Aenean ullamcorper felis at augue viverra porttitor ac sit amet orci. Donec rutrum suscipit interdum. Suspendisse molestie, diam et faucibus placerat, augue libero sollicitudin tortor, vel viverra justo justo a mi. Donec tempor nunc semper massa mattis tempus. Donec viverra, metus id porta auctor, ligula neque rutrum magna, sit amet vulputate ligula augue nec erat. Nulla facilisi. Phasellus adipiscing risus at dolor molestie commodo. Curabitur placerat, orci ut mollis tempus, urna enim sollicitudin felis, at facilisis tortor metus nec nulla.
Nulla sit amet quam diam. Phasellus rutrum magna eu enim tincidunt, et porta tortor hendrerit. Aliquam dapibus justo erat, nec rhoncus nunc ornare eget. Praesent eget augue vehicula, pulvinar diam a, rutrum orci. Donec tincidunt, elit vitae volutpat dictum, ligula nisl ornare nulla, et volutpat sem nisi blandit quam. Class aptent taciti sociosqu ad litora torquent per conubia nostra, per inceptos himenaeos. Interdum et malesuada fames ac ante ipsum primis in faucibus. Quisque semper magna lacinia fringilla viverra.
In eget ligula sapien. Praesent eget ligula egestas, ullamcorper arcu id, suscipit massa. Nam sagittis luctus vulputate. Nam id sodales nibh, at ultricies velit. Duis pretium bibendum justo nec molestie. Nullam adipiscing auctor ante, dignissim tincidunt odio scelerisque et. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Vestibulum et diam et arcu convallis tempor in pulvinar ante. Proin tempor, augue non interdum elementum, est ligula lacinia tellus, non laoreet turpis lectus ac mi. Duis vel dictum augue. Fusce viverra nisl a ipsum tristique, aliquam gravida eros semper. Aenean sem turpis, sagittis vel luctus nec, molestie eget velit.
Quisque faucibus, velit id sagittis pulvinar, erat augue tempus nisl, vitae ullamcorper risus arcu at massa. Duis convallis, tellus eu aliquam volutpat, turpis est lobortis nibh, eu lacinia orci risus et elit. Nunc et nunc nisi. Suspendisse sit amet vestibulum enim, id ullamcorper velit. Sed pulvinar velit eget venenatis pharetra. Aenean in enim vehicula, blandit sapien eu, ullamcorper sapien. In ut nunc a justo consectetur egestas. Proin vel orci semper, gravida felis nec, mattis orci.
Nullam et elit vitae mi venenatis dignissim et nec ligula. Phasellus sagittis velit eu consectetur tempor. Maecenas lacinia consequat risus, a dignissim justo volutpat vestibulum. Vivamus faucibus sed lacus et viverra. Sed rutrum tortor tortor, eget hendrerit mauris suscipit sed. Suspendisse laoreet rhoncus dolor, at semper arcu sodales vitae. Nulla id enim sit amet justo placerat accumsan nec quis nisl. Donec et justo ac purus venenatis sollicitudin nec id ipsum. Duis consequat lorem erat, sed molestie sapien iaculis et. In laoreet aliquam purus, fermentum laoreet orci euismod at. Cras ultrices pulvinar tempus. Suspendisse malesuada eleifend leo, sit amet laoreet diam cursus a. Duis metus diam, ultrices a erat a, ultrices tempor lectus. Donec facilisis fermentum diam a porta. Maecenas volutpat risus eget ornare vestibulum.";

/* Connecting, selecting database */
$db_link = mysql_connect("localhost", "root", "my_password");
if (!$db_link) {
   die("Could not connect: " . mysql_error());
}

mysql_query("CREATE DATABASE IF NOT EXISTS `test_db`", $db_link) or die("Error creating database: " . mysql_error($db_link));
mysql_select_db("test_db", $db_link) or die("Could not select database");
mysql_query("
    CREATE TABLE IF NOT EXISTS `test` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(250) NOT NULL,
      `text` text NOT NULL,
      `category_id` int(10) unsigned NOT NULL DEFAULT '0',
      `user_id` int(10) unsigned NOT NULL DEFAULT '0',
      `created` int(10) unsigned NOT NULL DEFAULT '0',
      `modified` int(10) unsigned NOT NULL DEFAULT '0',
      PRIMARY KEY (`id`),
      KEY `category_id` (`category_id`),
      KEY `user_id` (`user_id`),
      KEY `created` (`created`),
      KEY `modified` (`modified`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0;
", $db_link) or die("ERROR Create Table: " . mysql_error($db_link));

/* Performing SQL query */

class SampleText
{
    var $text = '';
    var $min  = 1;
    var $max  = 0;
    var $iter = 1000000;
    var $db_link;
    
    public function __construct($sample_text, $db_link)
    {
        $this->text = $sample_text;
        $this->max = strlen($this->text);
        $this->db_link = $db_link;
    }
    
    private function randText($max = 0, $min = 0)
    {
        $max = intval($max);
        $min = intval($min);
        
        $min = ($min > 0 && $min >= $this->min) ? $min : $this->randNum();
        $max = ($max > 0 && $max <= $this->max) ? $max : $this->randNum();
        
        return substr($this->text, $min, $max);
    }
    
    private function randNum($s = 0, $e = 0)
    {
        $s = intval($s);
        $e = intval($e);
        
        $s = $s > 0 ? $s : $this->min;
        $e = $e > 0 ? $e : $this->max;
        
        return rand($s, $e);
    }
    
    public function insert($iter = 0)
    {
        $iter = intval($iter);
        $iter = $iter > 0 && $iter <= $this->iter ? $iter : $this->iter;
        $sql  = array();
        $i    = 0;
        $time = time();
        
        while($iter > 0)
        {
            if($i > 10)
            {   
                if(count($sql))
                    mysql_query('
                        insert into `test` 
                        (`name`, `text`, `category_id`, `user_id`, `created`, `modified`) 
                        values '.implode(',', $sql),
                        $this->db_link
                    ) or die("Insert ERROR: " . mysql_error($this->db_link));
                
                $i = 0;
                $sql = array();
            }
            
            array_push($sql, "('" . mysql_real_escape_string($this->randText(120)) . "', '" . mysql_real_escape_string($this->randText()) . "', " . $this->randNum(0, 10000) . ", " . $this->randNum(0, 10000) . ", " . $time . ", " . $time . ")");
            
            $i++ ;
            $iter--;   
        }
    }
}

$s = new SampleText($sample_text, $db_link);
$s->insert(100);

/* Closing connection */
mysql_close($db_link);
?>

Интересные строки тут: 28 и 124. В 28-ой строчке настраиваем доступ к базе (обязательно рут, либо чтобы были права на создание базы и таблиц, ну и собственно на инсерт)
На 124 строке указываем кол-во строк, которое надо вбить в базу. :187:

О! Респект!
Сейчас прогоним.

Цитата:

Сообщение от Denis Shlyapnikov (Сообщение 945402)

На 124 строке указываем кол-во строк, которое надо вбить в базу.

Интересно.

Создано 99 строк при 100.
Создано 990 строк при 1000. ;)

Цитата:

Сообщение от Alisher Umarov (Сообщение 945429)

Создано 990 строк при 1000.

Пофиг, генерьте 990 000 :)

Итак, результаты по трем методам выборок из базы,в которой > 1млн записей, и имеется поле text:
http://i.pro-wizard.ru/test/select_db_tablerows.png

Обычный способ order by rand() http://i.pro-wizard.ru/test/select_db_method-rand.png
Выборка через `id` in - сначала получаем рандонмные ID из таблицы, потом их просто вытаскиваем через where http://i.pro-wizard.ru/test/select_d..._by_script.png
Решение со временными таблицами: :116:http://i.pro-wizard.ru/test/select_d..._temptable.png

Победитель очевиден ))

Вот скрипт для тестов:

Скрытый текст:

PHP код:


		
			
<?php
/**
 * @author Denis
 * @copyright 2013
 */
 
// HEADER
header('Content-type: text/html; charset=utf-8');
mb_http_input('UTF-8'); 
mb_http_output('UTF-8'); 
mb_internal_encoding("UTF-8");

set_time_limit(0);

if(ini_get('max_execution_time') != 0)
    exit('Время выполнения ограничено!');
    
error_reporting(E_ALL | E_STRICT) ;
ini_set('display_errors', 'On');

/* Connecting, selecting database */
$db_link = mysql_connect("localhost", "root", "my_password");
if (!$db_link) {
   die("Could not connect: " . mysql_error());
}

// rand , rand_by_script, rand_by_temptable
$method = 'rand_by_temptable'; // test method
$limit = 10;

mysql_select_db("test_db", $db_link) or die("Could not select database");

$time_init = microtime(true);

function convert($size)
{
    $unit = array('b','kb','mb','gb','tb','pb');
    return @round($size/pow(1024,($i=floor(log($size,1024)))),2).' '.$unit[$i];
}

echo '<!doctype html><html><head><meta charset="UTF-8"/><title>test</title></head><body>';
echo "<span style='color:blue'>START MEMORY:</span> <strong>" . convert(memory_get_usage()) . '</strong>';
echo "<hr/>";

$limit = intval($limit);
$rows = array();

function print_array($rows)
{
    if(is_array($rows) && count($rows))
    {
        foreach($rows as $key => $row)
        {
            echo '<div>';
            echo '<h2>NUM: <span style="color:green">';
            echo $key + 1 . '</span>.'; 
            echo $row['name'] . '</h2>'; 
            echo '<p>' . $row['text'] . '</p>';
            echo '<ul>';
            echo '<li>CATEGORY_ID: ' . $row['category_id'] . '</li>';
            echo '<li>USER_ID: ' . $row['user_id'] . '</li>';
            echo '<li>CREATE TIME: ' . $row['created'] . '</li>';
            echo '<li>MODIFIE TIME: ' . $row['modified'] . '</li>';
            echo '</ul>';
            echo '<hr/>';
            echo '</div>';
        }
    }
}

switch($method)
{
    case 'rand':
    
        $sql = 'select * from `test` order by rand() limit ' . $limit;
        $query = mysql_query($sql, $db_link) or die("Mysql query ERROR: " . mysql_error());

        while($row = mysql_fetch_assoc($query))
            $rows[] = $row;

    break;
    
    case 'rand_by_script':
        
        $query = mysql_query('select max(`id`) as `max` from `test`', $db_link) or die("Mysql query ERROR: " . mysql_error());
        $max   = mysql_fetch_assoc($query);
        $max   = $max['max'];
        
        $query = mysql_query('select min(`id`) as `min` from `test`', $db_link) or die("Mysql query ERROR: " . mysql_error());
        $min   = mysql_fetch_assoc($query);
        $min   = $min['min'];
        
        $ids   = array();

        function getRandId()
        {
            global $min, $max;
            
            return rand($min, $max);
        }
        
        function getIds($iter, $ids)
        {
            global $limit;
            
            while($iter > 0)
            {
                array_push($ids, getRandId());
                $iter--;
            }
            
            $ids = array_unique($ids);
            $sum = count($ids) - $limit;
            
            if($sum > 0)
                return getIds($sum, $ids);
            
            return $ids;
        }

        function rquery($sql_limit, $ids)
        {
            global $db_link, $limit;   
            static $q;
            
            $query = mysql_query('select * from `test` where `id` in (' . implode(',', $ids) . ') limit ' . $sql_limit, $db_link) or die("Mysql query ERROR: " . mysql_error());
            $sum   = $limit - mysql_num_rows($query);
            
            $q[] = $query;

            if($sum > 0)
                return rquery($sum, getIds($sum, array()));

            return $q;
        }

        $querys = rquery($limit, getIds($limit, $ids));

        if(count($querys))
        {
            foreach($querys as $query)
            {
                if(!is_resource($query))
                    continue;

                while($row = mysql_fetch_assoc($query))
                    $rows[] = $row;
            }
        }
        
    break;
    
    case 'rand_by_temptable':
    
        mysql_query('create temporary table `temp_db` select * from `test`', $db_link) or die("Mysql query ERROR: " . mysql_error());
        
        $sql = 'select * from `temp_db` order by rand() limit ' . $limit;
        $query = mysql_query($sql, $db_link) or die("Mysql query ERROR: " . mysql_error());

        while($row = mysql_fetch_assoc($query))
            $rows[] = $row;
    
    break;
}

print_array($rows);

echo "<span style='color:blue'>METHOD:</span> <strong>" . $method . '</strong>';
echo '<br/>';
echo "<span style='color:blue'>SQL_LIMIT:</span> <strong>" . $limit . '</strong>'; 
echo "<hr/>";
echo "<span style='color:blue'>END MEMORY:</span> <strong>" . convert(memory_get_usage()) . '</strong>';
echo '<br/>';
echo "<span style='color:blue'>PEAK MEMORY:</span> <strong>" . convert(memory_get_peak_usage()) . '</strong>';
echo "<hr/>";
echo "<span style='color:blue'>TIME:</span> <strong>" . round(microtime(true) - $time_init, 2) . ' sec.</strong>';

echo '</body></html>';

Цитата:

Сообщение от Denis Shlyapnikov (Сообщение 945438)

Победитель очевиден ))

1. 28.5 с
2. 0.14 с
3. 207.8 с

Вполне логично, спасибо.

Цитата:

Сообщение от JackDaniels (Сообщение 945439)

Цитата:

Сообщение от Denis Shlyapnikov (Сообщение 945438)

Победитель очевиден ))

1. 28.5 с
2. 0.14 с
3. 207.8 с

Вполне логично, спасибо.

Вторым методом и более лучший результат можно получить, например 0.03 было )) и это на win, думаю, что на linux еще быстрее :)

Может кто затестит все три теста на мощном linux сервере? Интересно результаты посмотреть ))

Цитата:

Сообщение от Denis Shlyapnikov (Сообщение 945440)

Цитата:

Сообщение от JackDaniels (Сообщение 945439)

Цитата:

Сообщение от Denis Shlyapnikov (Сообщение 945438)

Победитель очевиден ))

1. 28.5 с
2. 0.14 с
3. 207.8 с

Вполне логично, спасибо.

Ну второй будет, конечно, самым быстрым, там же MySQL просто делает банальную выборку.
Минус в том, что если "дырок" в ID много, то пока получишь 10 (а если нужно 100-1000) элементов, то цикл может повторяться значительное количество раз…
Еще минус, что нет возможности добавить дополнительное условие, ведь вытаскивается конкретная запись по ID.

В итоге имеем, что часто оптимален родной RAND(). :187:

Если это условие одинаков для всех найденных айди, то его можно использовать. Если нет, то тогда и запрос надо по-другому строить.

Я так и не понял, почему создание временной таблицы должно было уменьшить время выборки?
Мускуль ведь для этого создает временный файл и пишет туда данные. Отсюда и временные затраты

файл != оперативная память (ram)

Цитата:

Сообщение от Denis Shlyapnikov (Сообщение 945468)

файл != оперативная память (ram)

Да что вы говорите. А я то думал... А вон оно как оказывается!

Тогда наверное я неправильно вашего вопроса понял

На
mysql> select max(id) as maxid from test;
+-------+
| maxid |
+-------+
| 1980 |
+-------+
1 row in set (0.00 sec)

1. Rand = 0.09
2. Script = 0
3. Temptable =0.31

Для

mysql> select max(id) as maxid from test;
+-------+
| maxid |
+-------+
| 99 |
+-------+
1 row in set (0.00 sec)

1. Rand = 0.02
2. Script = 0
3. Temptable =0.04

Цитата:

Сообщение от Denis Shlyapnikov (Сообщение 945479)

Тогда наверное я неправильно вашего вопроса понял

Вы это к чему написали:

Цитата:

Сообщение от Denis Shlyapnikov (Сообщение 945468)

файл != оперативная память (ram)