Как быстро заполнить таблицу 1 000 000 ТЕСТОВЫХ записей?
Создадим таблицу samples с точно такой же структурой, что и таблица tests . Заполним ее 10 тестовыми записями.
Для того, чтобы превратить 10 записей из таблицы samples в 1 000 000 записей произведем самообъединение таблицы samples шесть раз
Оператор JOIN, который в SQL может быть заменен обычной запятой, осуществляет декартово соединение таблиц, когда каждой записи одной таблицы сопоставляется каждая запись другой таблицы. Таким образом, если в одной таблице 10 записей и в другой таблице 10 записей, результирующая таблица, полученная их соединением через JOIN будет содержать 100 записей. Как нетрудно увидеть
Если в таблице samples будет 100 записей, для получения 1 000 000 записей будет достаточно соединений трех таких таблиц, если в samples будет 1000 записей, будет достаточно объединить таблицу саму с собой один раз.
Вставить результат в целевую таблицу tests можно при помощи оператора INSERT . SELECT .
SQL — Урок 3. Создание таблиц и наполнение их информацией
Итак, мы познакомились с типами данных, теперь будем усовершенствовать таблицы для нашего форума. Сначала разберем их. И начнем с таблицы users (пользователи). В ней у нас 4 столбца:
id_user — целочисленные значения, значит будет тип int, ограничим его 10 символами — int (10).
name — строковое значение varchar, ограничим его 20 символами — varchar(20).
email — строковое значение varchar, ограничим его 50 символами — varchar(50).
password — строковое значение varchar, ограничим его 15 символами — varchar(15).
Все значения полей обязательны для заполнения, значит надо добавить тип NOT NULL.
id_user int (10) NOT NULL
name varchar(20) NOT NULL
email varchar(50) NOT NULL
password varchar(15) NOT NULL
Первый столбец, как вы помните из концептуальной модели нашей БД, является первичным ключом (т.е. его значения уникальны, и они однозначно идентифицируют запись). Следить за уникальностью самостоятельно можно, но не рационально. Для этого в SQL есть специальный атрибут — AUTO_INCREMENT, который при обращении к таблице на добавление данных высчитывает максимальное значение этого столбца, полученное значение увеличивает на 1 и заносит его в столбец. Таким образом, в этом столбце автоматически генерируется уникальный номер, а следовательно тип NOT NULL излишен. Итак, присвоим атрибут столбцу с первичным ключом:
id_user int (10) AUTO_INCREMENT
name varchar(20) NOT NULL
email varchar(50) NOT NULL
password varchar(15) NOT NULL
Теперь надо указать, что поле id_user является первичным ключом. Для этого в SQL используется ключевое слово PRIMARY KEY (), в скобочках указывается имя ключевого поля. Внесем изменения:
id_user int (10) AUTO_INCREMENT
name varchar(20) NOT NULL
email varchar(50) NOT NULL
password varchar(15) NOT NULL
PRIMARY KEY (id_user)
Итак, таблица готова, и ее окончательный вариант выглядит так:
create table users (
id_user int (10) AUTO_INCREMENT,
name varchar(20) NOT NULL,
email varchar(50) NOT NULL,
password varchar(15) NOT NULL,
PRIMARY KEY (id_user)
);
Теперь разберемся со второй таблицей — topics (темы). Рассуждая аналогично, имеем следующие поля:
id_topic int (10) AUTO_INCREMENT
topic_name varchar(100) NOT NULL
id_author int (10) NOT NULL
PRIMARY KEY (id_topic)
Но в модели нашей БД поле id_author является внешним ключом, т.е. оно может иметь только те значения, которые есть в поле id_user таблицы users. Для того, чтобы указать это в SQL есть ключевое слово FOREIGN KEY (), которое имеет следующий синтаксис:
FOREIGN KEY (имя_столбца_которое_является_внешним_ключом) REFERENCES имя_таблицы_родителя (имя_столбца_родителя);
Укажем, что id_author — внешний ключ:
id_topic int (10) AUTO_INCREMENT
topic_name varchar(100) NOT NULL
id_author int (10) NOT NULL
PRIMARY KEY (id_topic)
FOREIGN KEY (id_author) REFERENCES users (id_user)
Таблица готова, и ее окончательный вариант выглядит так:
create table topics (
id_topic int (10) AUTO_INCREMENT,
topic_name varchar(100) NOT NULL,
id_author int (10) NOT NULL,
PRIMARY KEY (id_topic),
FOREIGN KEY (id_author) REFERENCES users (id_user)
);
Осталась последняя таблица — posts (сообщения). Здесь все аналогично, только два внешних ключа:
create table posts (
id_post int (10) AUTO_INCREMENT,
message text NOT NULL,
id_author int (10) NOT NULL,
id_topic int (10) NOT NULL,
PRIMARY KEY (id_post),
FOREIGN KEY (id_author) REFERENCES users (id_user),
FOREIGN KEY (id_topic) REFERENCES topics (id_topic)
);
Обратите внимание, внешних ключей у таблицы может быть несколько, а первичный ключ в MySQL может быть только один. В первом уроке мы удалили нашу БД forum, пришло время создать ее вновь.
Запускаем сервер MySQL (Пуск — Программы — MySQL — MySQL Server 5.1 — MySQL Command Line Client), вводим пароль, создаем БД forum (create database forum;), выбираем ее для использования (use forum;) и создаем три наших таблицы:
Обратите внимание, одну команду можно писать в несколько строк, используя клавишу Enter (MySQL автоматически подставляет символ новой строки ->), и только после разделителя (точки с запятой) нажатие клавиши Enter приводит к выполнению запроса.
Помните, если вы сделали что-то не так, всегда можно удалить таблицу или всю БД с помощью оператора DROP. Исправлять что-то в командной строке крайне неудобно, поэтому иногда (особенно на начальном этапе) проще писать запросы в каком-нибудь редакторе, например в Блокноте, а затем копировать и вставлять их в черное окошко.
Итак, таблицы созданы, чтобы убедиться в этом вспомним о команде show tables:
И, наконец, посмотрим структуру нашей последней таблицы posts:
Теперь становятся понятны значения всех полей структуры, кроме поля DEFAULT. Это поле значений по умолчанию. Мы могли бы для какого-нибудь столбца (или для всех) указать значение по умолчанию. Например, если бы у нас было поле с названием «Женаты\Замужем» и типом ENUM (‘да’, ‘нет’), то было бы разумно сделать одно из значений значением по умолчанию. Синтаксис был бы следующий:
married enum (‘да’, ‘нет’) NOT NULL default(‘да’)
Т.е. это ключевое слово пишется через пробел после указания типа данных, а в скобках указывается значение по умолчанию.
Но вернемся к нашим таблицам. Теперь нам необходимо внести данные в наши таблицы. На сайтах, вы обычно вводите информацию в какие-нибудь html-формы, затем сценарий на каком-либо языке (php, java. ) извлекает эти данные из формы и заносит их в БД. Делает он это посредством SQL-запроса на внесение данных в базу. Писать сценарии на php мы пока не умеем, а вот отправлять SQL-запросы на внесение данных сейчас научимся.
Для этого используется оператор INSERT. Синтаксис можно использовать двух видов. Первый вариант используется для внесения данных во все поля таблицы:
Давайте попробуем внести в нашу таблицу users следующие значения:
INSERT INTO users VALUES (‘1′,’sergey’, ‘sergey@mail.ru’, ‘1111’);
Второй вариант используется для внесения данных в некоторые поля таблицы:
В нашей таблице users все поля обязательны для заполнения, но наше первое поле имеет ключевое слово — AUTO_INCREMENT (т.е. оно заполняется автоматически), поэтому мы можем пропустить этот столбец:
INSERT INTO users (name, email, password) VALUES (‘valera’, ‘valera@mail.ru’, ‘2222’);
Если бы у нас были поля с типом NULL, т.е. необязательные для заполнения, мы бы тоже могли их проигнорировать. А вот если попытаться оставить пустым поле со значением NOT NULL, то сервер выдаст сообщение об ошибке и не выполнит запрос. Кроме того, при внесении данных сервер проверяет связи между таблицами. Поэтому вам не удастся внести в поле, являющееся внешним ключом, значение, отсутствующее в связанной таблице. В этом вы убедитесь, внося данные в оставшиеся две таблицы.
Но прежде внесем информацию еще о нескольких пользователях. Чтобы добавить сразу несколько строк, надо просто перечислять скобки со значениями через запятую:
Теперь внесем данные во вторую таблицу — topics (темы). Все тоже самое, но надо помнить, что значения в поле id_author должны присутствовать в таблице users (пользователи):
Теперь давайте попробуем внести еще одну тему, но с id_author, которого в таблице users нет (т.к. мы внесли в таблицу users только 5 пользователей, то не существует):
Сервер выдает ошибку и говорит, что не может внести такую строку, т.к. в поле, являющемся внешним ключом, стоит значение, отсутствующее в связанной таблице users.
Теперь внесем несколько строк в таблицу posts (сообщения), помня, что в ней у нас 2 внешних ключа, т.е. id_author и id_topic, которые мы будем вносить должны присутствовать в связанных с ними таблицах:
Итак, у нас есть 3 таблицы, в которых есть данные. Встает вопрос — как посмотреть, какие данные хранятся в таблицах. Этим мы и займемся на следующем уроке.
Научись программировать на Python прямо сейчас!
Если этот сайт оказался вам полезен, пожалуйста, посмотрите другие наши статьи и разделы.
Volume testing: быстрое наполнение тестовых таблиц в MySQL
Представим, что вы создаете новое приложение и вам нужно протестировать его на большом объеме данных (volume testing). В этом случае вы можете взять уже готовые данные, или же подготовить их самостоятельно. Если у вас есть набор данных для тестов достаточного объема – это просто замечательно, но чаще всего данных нужного объема у вас не будет и вам будет нужен способ для быстрого их создания. Ниже будут перечислены три способа создания больших наборов данных простых типов (чисел, слов, дат).
Числа
Создавать большие наборы числовых данных совсем не сложно.
Возможно вы захотите написать цикл на вашем любимом языке программирования, или даже цикл в хранимой процедуре на SQL, но это займет намного больше времени, чем предлагаемый ниже подход, который позволит заполнить таблицу за несколько секунд:
Данный способ намного более быстрый, нежели прямая вставка 1’000’000 строк. Мы вставляем в таблицу всего одну строку, и потом дублируем таблицу 20 раз, пока не получим 1’048’576 строк (2 20 ). Эта операция занимает менее 8 секунд на ноутбуке автора, который намного менее мощный, нежели средний сервер (на момент написания статьи – 2006г. // hudson). Даже если вы не хотите использовать хранимую процедуру, вы можете вручную вставить 1 строку и выполнить 20 раз следующий запрос:
Эта процедура не займет у вас больше 30 секунд.
Слова
Если вам нужно работать с большим объемом уникальных строковых данных, вы опять таки, можете написать программу на любом языке, однако это будет совсем не быстро (процесс вставки достаточно медленный). Наиболее быстрый способ – загрузить готовый список слов из файла. Все Unix системы содержат списки слов – от нескольких тысяч, до полумиллиона. Если вдруг у вас такого не оказалось, вы можете скачать его из множества доступных источников или собрать самостоятельно (для начала можете посмотреть здесь: ftp://ftp.cerias.purdue.edu/pub/dict/ или здесь ftp://ftp.ox.ac.uk/pub/wordlists/).
И, наконец, покажем как имея на руках файл с примерно полумиллионом слов /usr/share/dict/words , вы сможете наполнить тестовую таблицу:
Быстро, не правда ли? Но постойте-ка, у нас пока что есть только пол миллиона записей (ну… чуть больше). Так как нам нужны уникальные слова, мы можем попросить базу данных, изменить порядок букв в уже существующих (reverse()):
Вот так вот! Но мы все еще можем сомневаться, уникальны ли эти слова, так как изменение порядка букв одного слова может превратить его в другое (например mood <–> doom). Т.о. чтобы считать нашу задачу завершенной, нужно добавить UNIQUE индекс с опцией IGNORE, что позволит исключить дубликаты:
Вот так – миллион слов, не напрягаясь )
Даты
Наконец, давайте посмотрим, как можно быстро и просто создавать большие наборы дат. Фактически, миллион разных дат вам вряд ли понадобится, т.к. миллион дней это более 2700 лет. Т.о. только даты будут покрывать диапазон от 1000 до 10000 дней, вряд ли больше. Если же вам нужен миллион записей, то впору поговорить не о DATE а о DATETIME с интервалами в часы, минуты или даже секунды. Никто не запрещает вам использовать эту технику для создания сотни уникальных DATE, но при этом иметь в таблице что-то около миллиона записей. Итак, если мы хотим создать записи с минутным интервалом то нужно выполнить следующий код:
Выглядит знакомо, не правда ли? ) Неудивительно, ведь ту же технику мы использовали для заполнения таблицы с числами. Хотя в отличие от чисел, здесь мы использовали число записей в таблице для вычисления интервала в минутах между существующими записями и теми записями, которые будут добавлены в этой итерации. Но мы также дублируем таблицу 20 раз, чтобы получить 1’000’000 записей.
Данная процедура занимает немного больше времени, нежели для чисел, т.к. возникает оверхед за счет вычисления миллиона интервалов дат, но и в этом случае процедура заняла около 10 секунд, что вполне приемлемо для создания тестовой таблицы.
Заключение
Конечно же есть и другие техники для формирования больших тестовых наборов данных, но перечисленные три, позволяют делать это быстро и без применения сторонних инструментов.
Как изучить SQL за ночь или шпаргалка для системного аналитика
Помните, как вы были студентами, и готовились к экзаменам по ночам?
Предлагаю вашему вниманию простую шпаргалку по SQL с теорией и практикой, которой вы сможете воспользоваться в любое время.
Статья пригодится:
ИТ специалисту, которому необходимо быстро освоить минимальный уровень SQL для выполнения рабочих задач,
Системному аналитику, которому требуется освежить знания перед собеседованием или научиться, если раньше не было задач с SQL.
В статье есть:
Минимум теории для задач на работе или собеседовании (прим. операторов в SQL больше, но в своей работе использовала чаще всего эти);
Практические задания, которые можно выполнить у себя на ПК бесплатно, предварительно установив сервер баз данных.
SQL теория
SQL (structured query language) — язык структурированных запросов, который позволяет работать с данными (найти, изменить, удалить или создать) в реляционной базе данных (БД).
Реляционные БД — это базы, где связанная информация, представленная в виде двумерных таблиц (например, Postgres, Mysql, Oracle и др.).
СУБД — система управления БД, программа с помощью которой можно создавать, наполнять и просматривать БД .
ER диаграммы (Entity-Relationship model) — показывает структуру и связи таблиц в БД. Помогает в написании SQL запросов.
Для работы мозга студенту нужна энергия. Проще всего ее получить из сладкого. Значит будем учиться на примере базы данных сладостей. Изучать теорию мы с вами будем на реальном примере.
Наша БД состоит из таблиц:
ER диаграмма базы данных сладостей
(прим. показана часть БД с необходимыми таблицами для выполнения практических заданий)