MySQL: определение, плюсы, минусы и способы работы

MySQL — опенсорс-система управления реляционной базой данных (БД), разрабатываемая и поддерживаемая Oracle. Это инструмент для упорядоченного хранения и управления данными, который позволяет использовать их в веб-приложениях.

Реляционная БД допускает нормализацию, и информация разбивается в ней на несколько таблиц, а не представляет собой массивную единицу хранения. Данные из этих двух таблиц можно связать и при необходимости манипулировать и комбинировать их по своему усмотрению.

Нормализация — проектирование схемы БД без логической избыточности или с минимальной избыточностью. Например, в базе данных авиакомпании Ф. И. О. пилота хранится только в таблице Pilots. Во всех других таблицах пилоты идентифицируются по PilotId. Такой подход исключает вероятность опечатки, и СУБД может различать двух людей с одинаковыми именами по их идентификаторам.

Взаимодействовать с MySQL можно через SQL — язык структурированных запросов. Он помогает запрашивать у системы управления БД конкретные сведения с помощью запросов, по сути, набора условий, определяющих, какие данные нужно получить из базы.

Работа с MySQL

Реляционная СУБД MySQL состоит из двух основных концепций: реляционной БД и модели «клиент — сервер».

Реляционная база данных

В реляционной БД данные хранятся в связанных друг с другом таблицах, которые довольно часто связаны друг с другом. Возьмём для примера две таблицы для авиакомпании: одна — для записи информации о пилотах, другая содержит сведения об отдельных рейсах. Их можно связать по одинаковому столбцу, как показано на изображении ниже.

Первичный ключ (primary key) -- — поле или сочетание нескольких полей, хранящее уникальные значения. На практике в качестве первичного ключа часто выбирают поле, хранящее ID. В нашем примере PilotId -- — первичный ключ таблицы PILOTS.

Модель «клиент — сервер»

В модели «клиент — сервер» сервер — место, где хранятся данные, а клиент — инструмент, который используется для доступа и извлечения. 

При работе с MySQL можно выбрать пакеты с открытым исходным кодом на разных языках программирования — Python, JavaScript, PHP, C++, Java и другие. Используя эти пакеты, вы сможете обрабатывать данные в СУБД внутри кода с помощью команд SQL, формирующих запросы. Чтобы выполнить или получить необходимые данные, существуют различные команды SQL. 

Запросы

Возьмём приведённый выше пример БД авиакомпании и сделаем к ней несколько наглядных запросов:

1. Отобразить всю информацию в таблице Pilots.

SELECT *
FROM Pilots;

2. Отобразить содержимое столбца Name в таблице Pilots.

SELECT Name
FROM Pilots;

3. Найти конкретного пилота по его идентификационному номеру.

SELECT *
FROM Pilots
WHERE PilotID = 10 ;

4. Создать новое меню для питания в полёте.

Если FlightID представляет собой целое число, название блюда должно содержать не более 25 символов, и количество каждого приёма пищи также является целочисленным значением.

CREATE TABLE MealsInfo(
FlightID INT,
MealName VARCHAR(25),
Quantity INT,
);

5. Добавить блюда в таблицу MealsInfo.

INSERT INTO MealsInfo (FlightID,MealName, Quantity)
VALUES 
(1002, Chicken, 46),
(1003, Beef , 28),
(1004, Veg , 39);

Все эти команды не зависят от языка программирования, который вы используете для создания приложения: набор команд для взаимодействия с БД всегда будет в SQL.

Таблицы

Таблицы — базовые структуры БД MySQL, хранящие информацию в своих ячейках. В каждой строке содержится один элемент данных — запись с определёнными атрибутами. Таблицы нужны для группировки связанных атрибутов, а также для лучшей организации данных и доступа к ним.

Теоретически нет ограничений на количество таблиц в MySQL. Однако разработчики баз данных стараются ограничить их, чтобы объём добавленной и извлечённой информации оставался в разумных пределах.

Как создать таблицу в MySQL

1. Подключаемся к базе данных MySQL.

Вы можете использовать клиент MySQL, например phpMyAdmin, с простым пользовательским интерфейсом для подключения и управления базами данных.

Чтобы установить соединение с сервером MYSQL через терминал, выбираем команду:

# mysql -u username -p

После ввода имени, пароля и входа в систему можно создать таблицу в MySQL.

2. Создаём базу данных.

В большинстве случаев на вашем сервере MySQL будет хотя бы одна база данных, в ином случае необходимо создать её с помощью следующей команды:

mysql> CREATE DATABASE <database_name>;

Затем проверяем создание новой базы при помощи оператора SHOW, отображающего список всех имеющихся БД:

mysql> SHOW DATABASES;

3. Выбираем базу данных.

Для переключения на нужную базу данных используем команду:

mysql> USE database_name;

Вы увидите сообщение о том, что БД изменена, значит, команда выполнена успешно.

4. Создаём таблицу в MySQL.

Используем для этого следующую команду:

mysql> CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
);

В примере ниже мы создали таблицу users с четырьмя столбцами: id, username, password и email. Все они имеют тип данных VARCHAR:

mysql> CREATE TABLE users (
id INT PRIMARY KEY,
username VARCHAR(50),
password VARCHAR(50),
email VARCHAR(50)
);

Индексы

Основное преимущество применения индексации — существенное увеличение скорости выборки или извлечения информации. Главный недостаток — замедление операций обновления, так как новая запись в индексируемый файл требует обновления и соответствующего индекса в нём.

MySQL использует индексы для быстрого поиска строк с определёнными значениями столбцов. Без индекса СУБД будет сканировать всю таблицу, чтобы найти соответствующие строки, и чем больше таблица, тем медленнее осуществляется поиск.

Рассмотрим на примере.

Создадим таблицу с индексом, содержащим два столбца: col2 и col3.

mysql> CREATE TABLE t_index(  
col1 INT PRIMARY KEY,  
col2 INT NOT NULL,  
col3 INT NOT NULL,  
col4 VARCHAR(20),  
 INDEX (col2,col3)   
);

Чтобы добавить индекс в таблицу, выберем оператор CREATE INDEX:

mysql> CREATE INDEX [index_name] ON [table_name] (column names)  

По умолчанию MySQL разрешает тип индекса BTREE, если мы не указали иное. Ниже показаны различные типы индексов в зависимости от способа хранения таблицы:

Однако индексы снижают эффективность операций INSERT и UPDATE, ведь их также придётся изменить соответствующим образом при обновлении значения данных в таблице. Поэтому использование индексов не всегда целесообразно.

Оптимизация работы с БД

Оптимизация базы данных MySQL — непростой процесс, но он важен для поддержания производительности приложений и предоставления качественных бизнес-услуг. Вот несколько основных рекомендаций по упрощению, ускорению и увеличению производительности работы СУБД:

1. Оптимизируйте SQL-запросы благодаря их индексации.

2. Следите за ресурсами аппаратной части сервера: процессором, оперативной памятью, пространством жёсткого диска и работой сети — и своевременно обновляйте его конфигурацию.

3. Используйте кеширование запросов MySQL для повышения производительности системы. Если популярные запросы сохранены, то в будущем ответы на них будут приходить намного быстрее.

4. Автоматизируйте настройку. Используйте инструменты для автоматизации производительности вашей базы данных MySQL и настройки конфигурации (Jet Profiler, DbSchema и др.). Они увеличат производительность MySQL, автоматизируя значительную часть рабочего процесса системы.

SQL в MySQL

Язык запросов SQL применяется для взаимодействия с такими СУБД, как MySQL, и позволяет разработчикам и пользователям беспрепятственно взаимодействовать с разными платформами баз данных.

Используя SQL с БД, можно выполнять следующие операции: извлечение, вставку, обновление и удаление информации, сложный поиск, фильтрацию, сортировку и агрегирование данных.

SQL предлагает надёжные механизмы обеспечения целостности информации при помощи функций ограничения, работы с внешними ключами и контроля транзакций (ACID).

SQL позволяет создавать, изменять схемы БД и управлять ими. Администраторы баз данных используют его для определения связей, представлений, индексов и других структурных элементов таблицы, необходимых для эффективной организации данных и управления ими.

Транзакции

Транзакции Mysql — атомарная единица, состоящая из нескольких запросов SQL, которые должны быть начаты и завершены или отменены при возникновении проблемы. В случае возникновения ошибки система либо выполнит все команды и зафиксирует транзакцию, либо воздержится от их выполнения и откатит транзакцию.

Свойства транзакции MySQL

MySQL, наряду с различными механизмами хранения данных, обеспечивает полную поддержку ACID. Термином ACID мы обозначаем набор следующих свойств транзакций:

1. Атомарность. Все операции внутри рабочей единицы должны быть выполнены успешно или не выполнены вовсе. Транзакция гарантированно не будет сохранена «частично».

2. Согласованность. Обеспечивает внесение изменений в базу данных в случае успешного совершения транзакции.

3. Изоляция. Параллельно выполняемые транзакции работают независимо друг от друга.

4. Надёжность. В случае сбоя системы изменения, внесённые в базу данных, будут успешно сохранены и останутся в БД.

Перечислим операторов для работы с транзакциями в MySQL:

1. START TRANSACTION, BEGIN и BEGIN WORK. Используются для начала транзакции в MySQL.

2. COMMIT. Когда все операторы внутри транзакции или рабочей единицы полностью выполнены, транзакция может быть зафиксирована с помощью оператора COMMIT.

3. ROLLBACK. Выполняет откат транзакции в случае какого-либо сбоя.

4. SET autocommit. По умолчанию все операции в MySQL фиксируются автоматически, а их изменения сохраняются навсегда. Чтобы отключить автоматическое подтверждение, установим свойство autocommit в значение off или 0:

SET autocommit = OFF;

Включение режима автофиксации:

SET autocommit = 1;

Заключение

Если вы в поисках удобного инструмента для хранения и управления данными, то MySQL — отличный вариант. Он быстро и эффективно обрабатывает запросы, стабилен и легко масштабируется, а также дает много возможностей для разработки и поддержки разных типов приложений.

Использование MySQL оптимизирует работу с данными, повышает эффективность и гарантирует надёжное хранение информации, что делает его удобным решением для различных проектов и приложений. Это система со свободной лицензией и хорошей документацией, она широко распространена и пользуется спросом на рынке СУБД.