предисловие
В настоящее время интернет-индустрия больше всего использует MySQL.Однако для старших веб-интервьюеров, особенно для соискателей, ищущих работу до 30 000, в основном задействованы многие точки знаний, связанные с MySQL.Если ваши соответствующие знания расплывчаты и неуместны во время собеседования, большинство интервьюеров чуть получше компаний в принципе вами не интересуются.Ведь немного крупный проект в конце концов позволит вам углубиться в базу данных, и большинство финальных узких мест тоже появятся в базе в первую очередь.
основы MySQL
Основные понятия базы данных
Файл на диске --> Иерархическая модель --> Сетчатая модель --> Реляционная модель (Кодда)
СУБД — это системное программное обеспечение для управления базой данных, реализующее различные функции системы баз данных. является ядром системы баз данных
DBA: отвечает за планирование, проектирование, координацию, обслуживание и управление базой данных.
Отношение: Отношение представляет собой двумерную таблицу. И удовлетворять следующим свойствам: Порядок строк и столбцов в таблице не важен.
row row: каждая строка в таблице, также известная как запись
Столбец: каждый столбец в таблице, называемый атрибутом, полем
Первичный ключ: поле, используемое для однозначного определения записи, составной первичный ключ: несколько полей объединяются в первичный ключ (НЕ NULL);
Уникальный ключ: комбинация одного или нескольких полей, заполненные данные должны быть в состоянии однозначно идентифицировать эту строку в этой таблице; допускается NULL, и таблица может иметь несколько
ограничение:
Ограничения первичного ключа: уникальный, не пустой, в таблице может быть только один;
Ограничение уникального ключа: уникально, их может быть несколько;
Ограничения внешнего ключа: ссылочные ограничения;
Проверить ограничение: проверить;
Трехслойная модель:
Физический уровень --> SA (определяет формат хранения данных, то есть то, как РСУБД организует файлы на диске)
Логический уровень --> DBA (опишите, какие данные хранятся и какая связь существует между данными)
Уровень просмотра --> Кодер (описать некоторые данные в БД)
домен: диапазон значений атрибута, например, пол может быть только «мужской» и «женский».
поверхность: строка столбец;
реляционные операции: выделение, проекция
база данных: таблица, индекс, представление (виртуальная таблица), SQL, процедура хранимой процедуры, процедура не имеет возвращаемого значения, функция хранимой функции, процедура имеет возвращаемое значение, триггер триггера, планировщик событий планировщика событий, расписание задач;
транзакция транзакция: несколько операций обрабатываются как единая ACID: атомарность, согласованность, изоляция, также известная как независимость, долговечность.
атомарность: Все операции во всей транзакции либо завершены, либо не завершены, и нельзя застаиваться на каком-то звене посередине. Если во время выполнения транзакции возникает ошибка, происходит ее откат (Rollback) до состояния до начала транзакции, как если бы транзакция никогда не выполнялась.
последовательность: ограничения целостности базы данных не нарушаются до начала транзакции и после ее завершения.
изоляция: Выполнение двух транзакций не мешает друг другу, и одна транзакция не может видеть данные в определенный момент посередине, когда выполняются другие транзакции.
Упорство: после завершения транзакции изменения, внесенные транзакцией в базу данных, сохраняются в базе данных и не подлежат откату.
манипулирование данными: • Извлечение данных: извлечение интересующего содержимого из коллекции данных. (ВЫБРАТЬ) • Обновление данных: изменение данных в базе данных. (ВСТАВИТЬ, УДАЛИТЬ, ОБНОВИТЬ)
Приложение относится к приложениям на основе базы данных: СУБД --> РСУБД (реляционная система управления базами данных)
При разработке реляционной базы данных, для выполнения различных спецификаций, разработать разумную реляционную базу данных, которая называется разными спецификациями различных парадигм, принцип, представляющих различные парадигмы, чем выше, чем меньше парадигма избыточной базы данных.
Существует шесть парадигм реляционных баз данных:
В настоящее время существует шесть нормальных форм для реляционных баз данных: первая нормальная форма (1NF), вторая нормальная форма (2NF), третья нормальная форма (3NF), нормальная форма Бардеско (BCNF), четвертая нормальная форма (4NF) и пятая нормальная форма (5NF). ), также известный как идеальная парадигма). Нормальной формой, удовлетворяющей минимальным требованиям, является первая нормальная форма (1NF). На основе первой нормальной формы та, которая в дальнейшем удовлетворяет более нормативным требованиям, называется второй нормальной формой (2НФ), а остальные нормальные формы выводятся по аналогии. Вообще говоря, база данных должна удовлетворять только третьей нормальной форме (3NF).
СУРБД:
Нормальная форма: первая нормальная форма, вторая нормальная форма, третья нормальная форма;
1NF: Нет повторяющихся столбцов, каждый столбец является неделимым элементом базовых данных, и в одном столбце не может быть нескольких значений.
2NF: атрибут полностью зависит от первичного ключа, вторая нормальная форма должна сначала удовлетворять первой нормальной форме, и каждая строка в таблице должна быть однозначно различима.
3НФ: атрибуты не зависят от других не основных свойств, соответствуют третьей парадигем, должны быть полны второй парадигмы. Третья парадигма требует, чтобы таблица базы данных не содержит информации, не включенной в другие таблицы, и поле Non-PK не может быть доступным.
SQL: Язык структурных запросов: Язык структурированных запросов --- Интерпретатор SQL --- Протокол хранения данных: Протокол прикладного уровня, C/S
olacle(tcp:1521),mysql(tcp:3306),sql server(tcp:1433)
Базовая структура mysql и структура приложения
Возможности MySQL: подключаемый модуль хранения, однопроцессорная многопоточность.
архитектура mysql:
Установка: сервер mysql-сервер и клиент mysql, установка yum, установка бинарника, компиляция исходного кода и установка
Улучшите безопасность: запустите mysql_secure_installation
Серверная программа:
mysqld, mysqld_safe, mysqld_multi
Клиентская программа:
mysql, mysqldump, mysqlbinlog, mysqladmin, ...
Гипервизоры неклиентского класса:
myisamchk, myisampack, ...
Файлы конфигурации: /etc/my.cnf и /etc/my.cnf.d/*.cnf
параметры команды mysql:
-uUSERNAME: имя пользователя, по умолчанию root
-host: Host сервер; по умолчанию localhost
-pPASSWORD: пароль пользователя, рекомендуется использовать -p, по умолчанию пустой пароль
Учетная запись пользователя mysql состоит из двух частей: 'USERNAME'@'HOST' HOST используется для ограничения того, какие удаленные хосты этот пользователь может использовать для подключения к службе mysql.
Поддерживаются подстановочные знаки: % соответствует любому символу любой длины 172.16.64.0/16 или 172.16.%.% _ соответствует любому одиночному символу.
Параметры, которые обычно изменяются перед запуском mysql
Добавьте две опции в [mysqld] в файле конфигурации:
innodb_file_per_table = on включает механизм хранения innodb
Skip_name_Resolve = на запрещенное разрешение хоста имена
Основа языка MYSQL
В системах баз данных операторы SQL не чувствительны к регистру (рекомендуется использовать верхний регистр), но строковые константы чувствительны к регистру.
Операторы SQL могут быть записаны в одну или несколько строк, заканчиваясь на «;», а ключевые слова не могут занимать несколько строк или сокращаться.
Используйте пробелы и отступы, чтобы улучшить читаемость инструкций, обычно в отдельных строках, для более удобного редактирования и лучшей читабельности.
Комментарий: Стандарт SQL: /*Содержимое комментария*/ Многострочный комментарий -- содержимое комментария Однострочный комментарий, обратите внимание, что есть пробелы
Примечания к MySQL:
Правила именования объектов базы данных: 1. Должно начинаться с буквы 2. Может включать цифры и три специальных символа (# _ $) 3. Не использовать зарезервированные слова MySQL 4. Объекты в одной схеме не могут иметь одинаковые имена
Типы данных MySQL: символьные, числовые, даты и времени, встроенные типы
Тип символа: CHAR(#), BINARY(#): тип фиксированной длины; CHAR не чувствителен к регистру, а BINARY.
VARCHAR(#), VARBINARY(#): ТЕКСТ переменной длины: TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT
BLOB: TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB
Численное:
Плавающая точка: приблизительное число с плавающей запятой, двойное число, вещественное число, бит
Целое: Точное: ЦЕЛОЕ: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, DECIMAL
Дата Время: Дата: ДАТА; Время: ВРЕМЯ; Дата j Время: ДАТАВРЕМЯ; Отметка времени: TIMESTAMP; Год: ГОД(2), ГОД(4)
Встроенный: ENUM: Перечисление: ENUM('Вс','Пн','Вт','Среда'); SET: Коллекция
Модификатор типа:
Тип символа: NOT NULL, NULL, DEFALUT ‘STRING’, CHARACET SET ‘CHARSET’, COLLATION ‘коллокация’
Целое число: NOT NULL, NULL, значение ПО УМОЛЧАНИЮ, AUTO_INCREMENT, UNSIGNED (использование числа без знака удваивает переменную длину данных)
Дата и время: НЕ NULL, NULL, ПО УМОЛЧАНИЮ
DML:
INSERT/REPLACE (увеличить), DELETE (удалить), SELECT (проверить), UPDATE (изменить), ALTER.
Создайте базу данных: CREATE DATABASE |SCHEMA [ЕСЛИ НЕ СУЩЕСТВУЕТ] 'ИМЯ_БД';
Удалить базу данных: DROP DATABASE | SCHEMA [ЕСЛИ СУЩЕСТВУЕТ] 'ИМЯ_БД';
Посмотреть все поддерживаемые наборы символов: SHOW CHARACTER SET;
Посмотреть все поддерживаемые варианты сортировки: SHOW COLLATION;
Просмотрите список баз данных: ПОКАЗАТЬ БАЗЫ ДАННЫХ;
Создайте таблицу: CREATE TABLE 'DB_TABLE_NAME';
Удалить таблицу: DROP TABLE [IF EXISTS] tb_name;
Просмотр таблицы: ИСПОЛЬЗУЙТЕ 'DB_NAME' , SHOW TABLES ;
Просмотр поддерживаемых типов двигателей: SHOW ENGINES;
Просмотрите структуру таблицы: DESC [db_name.]tb_name;
Посмотреть статус таблицы: SHOW TABLE STATUS LIKE 'tbl_name'\G
Пример:
Создать таблицу студентов (ID INT UNSIGNED NOT NULL Primary Key, Name Varchar (20) Not Null, Age Tinyint Unsigned;
DESC students;
CREATE TABLE student2 (id int UNSIGNED NOT NULL, name VARCHAR(20) NOT NULL, age tinyint UNSIGNED, PRIMARY KEY (id, name));
ИЗМЕНИТЬ ТАБЛИЦУ учащихся ПЕРЕИМЕНОВАТЬ s1; (переименовать)
ALTER TABLE s1 ADD phone varchar(11) AFTER name;
ALTER TABLE s1 MODIFY phone int;
ALTER TABLE s1 CHANGE COLUMN phone mobile char(11);
ALTER TABLE s1 DROP COLUMN mobile;
ИЗМЕНИТЬ ТАБЛИЦУ студентов ДОБАВИТЬ пол ENUM('m','f') (перечисление, значения поля равны m и f)
ALETR TABLE students CHANGE id sid int UNSIGNED NOT NULL PRIMARY KEY;
ИЗМЕНИТЬ ТАБЛИЦУ ДОБАВИТЬ УНИКАЛЬНЫЙ КЛЮЧ (имя); (уникальный ключ)
ИЗМЕНИТЬ ТАБЛИЦУ студентов ДОБАВИТЬ ИНДЕКС(возраст); (добавить индекс возраста)
SHOW INDEXES FROM students;
ALTER TABLE students DROP age;
Индекс: определение поля, которое используется в качестве условия поиска при поиске ----Преимущество: повышение скорости запроса; Недостаток: занимает дополнительное место и влияет на скорость вставки (для создания индекса необходимо создать имя индекса)
Создайте индекс: CREATE INDEX index_name ON tbl_name (index_col_name,...);
Чтобы удалить индекс: DROP INDEX index_name ON tbl_name;
Просмотр индекса: ПОКАЗАТЬ ИНДЕКСЫ ИЗ [db_name.]tbl_name;
ВСТАВИТЬ: (вставить, увеличить)
INSERT [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} (val1,...),(...),...
ОБНОВЛЕНИЕ: (изменить)
UPDATE tbl_name SET col1=val1, col2=val2, ... [WHERE clause] [ORDER BY 'col_name' [DESC]] [LIMIT [m,]n];
УДАЛИТЬ:: (удалить)
DELETE FROM tbl_name [предложение WHERE] [ORDER BY 'col_name' [DESC]] [LIMIT [m,]n]; Вы можете сначала отсортировать, а затем указать количество строк для удаления
Выберите COL1, COL2, ... из TBL_NAME [Где предложение] [Order by 'col_name' [desc]] [limited [m,] n]; предел m, n пропускает m, к n
Обозначение поля SELECT: *: все поля как: псевдоним поля, col1 AS alias1 Пример:
WHERE clause:
Операторы: >, =,
LIKE: %: любой символ любой длины, _: любой одиночный символ;
RLIKE: сопоставление шаблонов регулярных выражений
IS NULL , НЕ NULL IN (значение1,значение2,…)
Условные логические операции: и, или, не
Пример:
DESC students;
ВСТАВЬТЕ В СТУДЕНТЫ ЗНАЧЕНИЯ(1,'том','м'),(2,'алиса','ф');
INSERT INTO students (id,name) VALUES(3,'jack'),(4,'allen');
SELECT * FROM students WHERE id < 3;
SELECT * FROM students WHERE gender='m';
SELECT * FROM students WHERE gender IS NULL;
SELECT * FROM students WHERE gender IS NOT NULL;
ВЫБЕРИТЕ * ОТ студентов ORDER BY name DESC LIMIT 2; (первые два подходящих)
SELECT * FROM students ORDER BY name DESC LIMIT 1,2;
SELECT * FROM students WHERE id >=2 and id <=4;
SELECT * FROM students WHERE BETWEEN 2 AND 4 ;
ВЫБЕРИТЕ * ОТ студентов, ГДЕ имя НРАВИТСЯ 't%'
SELECT * FROM students WHERE name RLIKE '.*[lo].*';
SELECT id stuid , name as stuname FROM students
Путь выполнения запроса:
Запрос -> кэш запроса
Запрос -> Кэш запросов -> Резолвер -> Препроцессор -> Оптимизатор -> Механизм выполнения запросов -> Механизм хранения -> Кэш -> Ответ
Поток выполнения инструкции SELECT: FROM --> WHERE --> Group By --> Наличие --> Order BY --> SELECT --> Limit
mysql управление пользователями и авторизацией
Создание пользователей, просмотр, удаление и управление паролями
Создайте пользователя: CREATE USER 'username'@'host' [IDENTIFIED BY 'password'];
Просмотр текущего пользователя: SELECT user();
Просмотр пользователя: ВЫБЕРИТЕ пользователя, хост, пароль от пользователя;
Чтобы удалить пользователя: DROP USER 'username'@'host' Пример: для удаления пустого пользователя по умолчанию DROP USER ''@'localhost';
Чтобы изменить пароль: 1) УСТАНОВИТЕ ПАРОЛЬ ДЛЯ 'user'@'host' = PASSWORD('password');
2) ОБНОВИТЬ пароль пользователя SET=ПАРОЛЬ('magedu') ГДЕ Пользователь='root' ;
Примечание. Приведенная выше команда для изменения таблицы не вступит в силу немедленно и должна быть выполнена.FLUSH PRIVILEGESвступить в силу
- /usr/local/mysql/bin/mysqladmin -u root –poldpassword пароль 'новый пароль
4) Удалите базу данных mysql в каталоге базы данных mysql /var/lib/mysql/mysql/, в это время вся пользовательская информация будет потеряна, и вы сможете войти в систему с пустым паролем.
Забыли пароль администратора? Решение:
(1) При запуске процесса mysqld используйте параметры --skip-grant-tables и --skip-networking;
CentOS 7: mariadb.service
CentOS 6: /etc/init.d/mysqld
(2) Измените пароль администратора с помощью команды UPDATE;
(3) Запустите процесс mysqld обычным способом;
авторизация mysql
Уровни привилегий: административные привилегии, базы данных, таблицы, поля, сохраненные процедуры
GRANT priv_type,... ON [object_type] db_name.tb_name TO 'user'@'host' [IDENTIFIED BY 'password'] [WITH GRANT OPTION];
priv_type: ALL [PRIVILEGES] ,**select,insert,**update,delete,alter
Пример: GRANT SELECT,DELETE на testdb.* TO 'testuser'@'%' IDENTIFIED BY 'testpass'; (один шаг включает создание пользователя и авторизацию)
Просмотр разрешений, полученных указанным пользователем:
Help SHOW
GRANTS SHOW GRANTS FOR 'user'@'host';
SHOW GRANTS FOR CURRENT_USER();
Восстановить авторизацию: REVOKE priv_type, ... ON db_name.tb_name FROM 'user'@'host
Пример: REVOKE DELETE ON testdb.* FROM 'testuser'@'%'
Примечание. Когда процесс службы MariaDB запускается, он считывает все таблицы авторизации в библиотеке mysql в память.
(1) Операции с разрешениями на выполнение, такие как GRANT или REVOKE, будут храниться в системной таблице, и процесс службы MariaDB обычно автоматически перечитывает таблицу авторизации, чтобы сделать ее эффективной.
(2) Для команд, которые не могут или не могут вовремя перечитать таблицу авторизации, вы можете вручную попросить сервисный процесс MariaDB перечитать таблицу авторизации: mysql>FLUSH PRIVILEGES;
Практикуйтесь и думайте:
Импортируйте hellodb.sql для создания базы данных
(1) В таблице студентов запросите имена и возраст студентов мужского пола старше 25 лет;
SELECT Name,Age FROM students WHERE Age > 25 AND Gender='M';
(2) На основе ClassID отображается средний возраст каждой группы;
SELECT ClassID,avg(age) FROM students GROUP BY ClassID;
(3) Показать группы и средний возраст групп, средний возраст которых превышает 30 лет в вопросе 2;
SELECT ClassID,avg(age) AS Aging FROM students GROUP BY ClassID HAVING Aging>30;
(4) Показать информацию об имени имени, начинающемся с L;
SELECT * FROM students WHERE Name LIKE 'L%';
(5) Отображать соответствующую информацию студентов, чьи пользователь не пусты;
SELECT * FROM students WHERE TeacherID IS NOT NULL;
(6) После сортировки по возрасту отображается информация о 10 лучших учениках самого старшего возраста;
SELECT * FROM students ORDER BY Age DESC LIMIT 10;
(7) Запросить информацию о студентах, возраст которых больше или равен 20 и меньше или равен 25, используя три метода;
Упражнение: импортируем hellodb.sql, над таблицей студентов выполняются следующие операции
1. Сгруппируйте по ClassID, чтобы отобразить количество учеников в каждом классе;
SELECT ClassID,count(StuID) FROM students GROUP BY ClassID;
2. Группировать по полу и отображать сумму их возрастов;
SELECT Gender,SUM(Age) FROM students GROUP BY Gender;
3. Сгруппируйте по ClassID, чтобы отобразить классы, средний возраст которых превышает 25 лет;
SELECT ClassID,avg(age) FROM students GROUP BY ClassID HAVING avg(Age) > 25;
4. Группировать по полу и отображать сумму возрастов учащихся старше 25 лет в каждой группе;
Упражнение: Импортируйте hellodb.sql и выполните следующие задачи:
1. Показать имена, курсы и оценки 5 лучших студентов;
2. Отображать имена и курсы студентов, чьи оценки выше 80;
3. Найдите средние оценки каждого из 8 лучших студентов по каждому из двух курсов и расположите их в порядке убывания;
4. Показать название курса каждого курса и количество студентов, которые изучали этот курс;
считать:
1. Как отображать имена одноклассников, возраст которых больше среднего возраста?
2. Как отобразить имена студентов, курсы которых являются 1-м, 2-м, 4-м или 7-м курсом?
3. Как отобразить учеников в классе, где есть как минимум 3 члена, чей возраст больше, чем средний возраст их одноклассников?
4. Подсчитайте учеников в каждом классе, чей возраст больше, чем средний возраст всей школы.
расширенный MySQL
механизм хранения
SHOW TABLE STATUS
Общие механизмы хранения: SHOW ENGINES;
MyISAM, Aria, InnoDB, MRG_MYISAM, CSV, BLACKHOLE, MEMORY, PERFORMANCE_SCHEMA, ARCHIVE, FEDERATED
ИнноБД: ИнноБейс
SHOW ENGINE INNODB STATUS;
МойISAM:
Формат строки: {ПО УМОЛЧАНИЮ|ДИНАМИЧЕСКИЙ|ФИКСИРОВАННЫЙ|СЖАТЫЙ|ИЗБЫТОЧНЫЙ|КОМПАКТНЫЙ}
Другие механизмы хранения:
CSV: обрабатывать файл CSV (текстовый файл с полями, разделенными запятыми) как файл таблицы MySQL;
MRG_MYISAM: виртуальная таблица, объединяющая несколько таблиц MyISAM;
Blackole: аналогично / dev / null, не очень хранить данные;
ПАМЯТЬ: механизм хранения в памяти, поддерживает хэш-индексы, блокировки на уровне таблиц, обычно используется для временных таблиц;
FEDERATED: интерфейс механизма хранения для доступа к таблицам на других удаленных серверах MySQL;
Управление параллелизмом: Блокировка Механизм: Блокировка
дела
Транзакция: набор атомарных запросов SQL или независимая единица работы, состоящая из одного или нескольких операторов SQL;
Журнал транзакций:
innodb_log_files_in_group
innodb_log_group_home_dir
innodb_log_file_size
innodb_mirrored_log_groups
Кислотный тест:
A: АВТОМАТИЗАЦИЯ, атомарность, все операции во всей транзакции либо выполняются успешно, либо откатываются после всех неудач;
C: CONSISTENCY, непротиворечивость, база данных всегда должна переходить из одного согласованного состояния в другое согласованное состояние;
I: ИЗОЛЯЦИЯ, изоляция; могут ли операции, выполняемые транзакцией, быть видны другим транзакциям до совершения; с целью обеспечения параллельных операций существует несколько уровней изоляции;
D: ДОЛГОВЕЧНОСТЬ, постоянство; как только транзакция будет зафиксирована, ее модификации будут постоянно сохранены;
Autocommit: транзакции с одним оператором
mysql> SELECT @@autocommit;
+------------------------+
| @@autocommit |
+------------------------+
| 1 |
+------------------------+
mysql> SET @@session.autocommit=0;
Контролировать транзакции вручную:
Начало: НАЧАТЬ ТРАНЗАКЦИЮ
Отправить: СОВЕРШИТЬ
Откат: ОТКАТ
Транзакции поддерживают точки сохранения:
SAVEPOINT identifier
ROLLBACK [WORK] TO [SAVEPOINT] identifier
RELEASE SAVEPOINT identifier
Уровень изоляции транзакции:
READ-UNCOMMITTED: чтение незафиксированных --> грязное чтение;
READ-COMMITTED: фиксация чтения --> неповторяемое чтение;
ПОВТОРЯЕМОЕ ЧТЕНИЕ: Повторяемое чтение --> фантомное чтение;
SERIALIZABLE: сериализовать;
mysql> SELECT @@session.tx_isolation;
+----------------------------------+
| @@session.tx_isolation |
+----------------------------------+
| REPEATABLE-READ |
+----------------------------------+
Просмотрите информацию о состоянии механизма хранения InnoDB:
SHOW ENGINE innodb STATUS;
Просмотр грантов: ПОКАЗАТЬ ГРАНТЫ [ДЛЯ 'пользователя'@'хоста']
Деавторизовать: REVOKE priv_type [(column_list)][ priv_type [(column_list)]] ... ON [object_type] priv_level FROM 'user'@'host' [ 'user'@'host'] ...
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ..
Индекс MySQL:
Индекс: извлеките данные из поля таблицы, в которой создан индекс, и создайте уникальную структуру данных;
Роль индекса: ускорение операций запросов, побочные эффекты: снижение производительности операций записи;
Подмножество данных в таблице: извлеките данные одного или нескольких полей в таблице и сохраните их как данные, упорядоченные по определенной структуре данных;
Поле или несколько полей: поля, используемые в предложении WHERE;
Тип индекса: B+ TREE, HASH, B- TREE
B+ TREE: Последовательное хранение, расстояние от каждого конечного узла до корневого узла одинаковое; индекс левого префикса, подходящий для запроса данных типа диапазона; (индекс по умолчанию mysql)
Типы запросов, применимые к индексам B+ TREE: полное значение ключа, диапазон значений ключа или префикс ключа;
Полное совпадение значения: точное соответствие значению, ГДЕ КОЛОНКА = 'значение';
Сопоставьте крайний левый префикс: точно сопоставьте только начальную часть WEHRE COLUMN LIKE 'PREFIX%';
Соответствие значений диапазона: точное соответствие столбцу, диапазон соответствует другому столбцу, запросы, которые обращаются только к индексу: охватывают индекс;
index(Name)
SELECT Name FROM students WHERE Name LIKE 'L%';
Не применимо для индексов B+ TREE:
Если условие запроса не начинается с крайнего левого столбца, индекс недействителен;
index(age,Fname), WHERE Fname='Jerry'; , WHERE age>30 AND Fname='Smith';
Столбец в индексе нельзя пропустить;
index(name,age,gender)
WHERE name='black' and age > 30;
WHERE name='black' AND gender='F';
Если столбец в запросе является запросом диапазона, то столбцы в правой части запроса больше не могут использовать индекс для оптимизации запроса, ГДЕ возраст>30 И Fname='Smith';
Хэш-индекс: реализован на основе хеш-таблицы, особенно подходит для запросов на точное соответствие значений;
Применимые сценарии: поддерживаются только запросы сравнения равенства, такие как =, IN(),
Бесполезные сценарии: все запросы с неточными значениями, MySQL поддерживает только явные хеш-индексы для механизмов хранения в памяти;
Преимущества индекса:
Уменьшите объем данных, которые необходимо сканировать, и количество операций ввода-вывода;
Может помочь избежать операций сортировки и использования временных таблиц;
Помогите преобразовать случайный ввод-вывод в последовательный ввод-вывод;
Стратегия высокопроизводительного индексирования:
(1) Используйте колонки отдельно в WHERE и старайтесь избегать их участия в операциях, например, WHERE age+2 > 32;
(2) Индекс левого префикса: Индекс строится по крайним левым символам поля.Селективность индекса оценивается по селективности индекса: отношение уникального значения индекса к общему количеству записей в таблице данных;
(3) Индекс с несколькими столбцами: несколько условий запроса соединения AND больше подходят для использования индекса с несколькими столбцами вместо нескольких индексов с одним ключом;
(4) Выберите соответствующий порядок столбцов индекса: самая высокая селективность помещается слева;
EXPLAIN для анализа достоверности индекса:
EXPLAIN [explain_type] SELECT select_options
Выходной результат:
id: номер первого оператора SELECT в текущем операторе запроса;
select_type: тип запроса:
таблица: таблица для запроса;
Тип: тип ассоциации или тип доступа, то есть то, как MySQL запрашивает строки в таблице.
ALL: полное сканирование таблицы;
индекс: Полное сканирование таблицы выполняется в соответствии с порядком индекса, но при этом, если в столбце Дополнительно стоит «Используя индекс», это означает, что используется покрывающий индекс;
диапазон: сканирование диапазона реализовано на основе индекса с ограничениями диапазона, позиция сканирования начинается с одного элемента в индексе и заканчивается на другом элементе;
ref: все строки, соответствующие одному значению в таблице, возвращаются в соответствии с индексом (более одной строки, соответствующей заданному значению);
eq_ref: Одна строка, соответствующая одному значению в таблице, возвращается по индексу, возвращается только одна строка, но ее нужно сравнивать с дополнительным эталонным значением, а не с константой;
const, system: сравнить с константой и вернуть только одну строку;
possiable_keys: индексы, которые можно использовать в запросе;
ключ: индекс, используемый в запросе;
key_len: длина индекса, используемого в запросе;
ref: столбец или постоянное значение, на которое ссылаются при использовании индекса, отображаемого ключевым полем, для выполнения операции запроса;
строки: MySQL оценивает количество строк, которые необходимо прочитать, чтобы найти все целевые элементы;
Дополнительно: дополнительная информация
Использование индекса: запрос с использованием покрывающего индекса;
Используя where: снова отфильтруйте после получения данных;
Использование временной: для выполнения запроса используется временная таблица;
Использование файловой сортировки: использует внешний индекс для сортировки результатов;
расширенный MySQL
бревно
- Журнал запросов: general_log
- Журнал медленных запросов: log_slow_queries
- Журналы ошибок: log_error, log_warnings
- Двоичный журнал: binlog
- Журнал реле: relay_log
- Журнал транзакций: innodb_log
1. Журнал запросов
记录查询语句，日志存储位置：FILE
表：table (mysql.general_log)
general_log={ON|OFF}
general_log_file=HOSTNAME.log
log_output={FILE|TABLE|NONE}
2. Медленный журнал запросов
慢查询：运行时间超出指定时长的查询； long_query_time
存储位置：文件：FILE
表：TABLE，mysql.slog_log
log_slow_queries={ON|OFF}
slow_query_log={ON|OFF}
slow_query_log_file=
log_output={FILE|TABLE|NONE}
log_slow_filter=admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
log_slow_rate_limit
log_slow_verbosity
3. Журнал ошибок
记录如下几类信息：
(1) mysqld启动和关闭过程中输出的信息；
(2) mysqld运行中产生的错误信息；
(3) event scheduler运行时产生的信息；
(4) 主从复制架构中，从服务器复制线程启动时产生的日志；
log_error=
/var/log/mariadb/mariadb.log|OFF
log_warnings={ON|OFF｝
4. Двоичный журнал
用于记录引起数据改变或存在引起数据改变的潜在可能性的语句（STATEMENT）或改变后的结果（ROW），也可能是二者混合；
功用：“重放”
binlog_format={STATEMENT|ROW|MIXED}
STATEMENT：语句；
ROW：行；
MIXED：混编；
查看二进制日志文件列表：
SHOW MASTER|BINARY LOGS;
查看当前正在使用的二进制日志文件：
SHOW MASTER STATUS；
查看二进制 日志文件中的事件：
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
服务器变量：
log_bin=/PATH/TO/BIN_LOG_FILE
只读变量；
session.sql_log_bin={ON|OFF}
控制某会话中的“写”操作语句是否会被记录于日志文件中；
max_binlog_size=1073741824
sync_binlog={1|0}
Команда журнала запросов:
mysqlbinlog:
YYYY-MM-DD hh:mm:ss
--start-datetime=
--stop-datetime=
-j, --start-position=#
--stop-position=#
--user, --host, --password
Двоичный формат событий журнала:
View Code
Журнал реле:
Записывать события с сервера, которые синхронизируются из бинарного лог-файла главного сервера;
Журнал транзакций:
Механизм хранения транзакций innodb используется для обеспечения лог-файла характеристик транзакций.
MySQL: резервное копирование и восстановление (данные)
Восстановление на момент времени: бинарные журналы;
Тип резервной копии:
Полное резервное копирование, инкрементное резервное копирование, дифференциальное резервное копирование:
полная резервная копия
Инкрементное резервное копирование: резервное копирование только переменных данных с момента последнего полного или добавочного резервного копирования;
Дифференциальное резервное копирование: резервное копирование только переменных данных с момента последнего полного резервного копирования;
Физическая резервная копия, логическая резервная копия:
Физическая резервная копия: резервная копия, созданная путем копирования файлов данных;
Логическое резервное копирование: экспорт данных из базы данных и сохранение их в один или несколько файлов;
В зависимости от того, находится ли служба данных в сети:
Горячее резервное копирование: резервное копирование, сделанное в состоянии, когда могут выполняться операции чтения и записи;
Теплая резервная копия: резервная копия доступна для чтения, но не для записи;
Холодное резервное копирование: резервные копии, сделанные в состоянии, когда нельзя выполнять ни операции чтения, ни записи;
Стратегия резервного копирования:
полный + разн. + бинлог
полный + инкрементный + бинлог
Средства резервного копирования: физические, логические
Инструмент резервного копирования:
mysqldump: инструмент резервного копирования, входящий в состав службы mysql, инструмент логического резервного копирования, полное и частичное резервное копирование,
InnoDB: горячий резерв;
MyISAM: горячий резерв;
cp/tar
l vm2: моментальный снимок (запросить глобальную блокировку) и снять блокировку сразу же после этого для достижения эффекта почти горячего резервного копирования, физического резервного копирования;
Примечание: нельзя создавать резервные копии только файлов данных, необходимо также создать резервную копию журнала транзакций;
Предпосылка: файлы данных и журналы транзакций должны находиться на одном логическом томе;
xtrabackup: Предоставляется Percona, инструментом с открытым исходным кодом, который поддерживает горячее резервное копирование InnoDB, инструмента физического резервного копирования;
mysqlhotcopy: (почти холодный бэкап, никто не использует)
Выбрать:
Резервное копирование: предложение SELECT INTO OUTFILE 'FILENAME';
Восстановление: СОЗДАТЬ ТАБЛИЦУ
Импорт: ЗАГРУЗИТЬ ДАННЫЕ
InnoBase: Innodb --> XtraDB, Innobackup --> Xtrabackup
Стратегия резервного копирования:
xtrabackup: полный + разность + бинлог или полный + инкрементный + бинлог
mysqldump: полный + бинлог
mysqldump:
View Code
Бэкап на основе lvm2:
View Code
Innobackup --> Xtrabackup
Экстрабэкап:
MyISAM: теплое резервное копирование, не поддерживает инкрементное резервное копирование;
InnoDB: горячий резерв, инкрементный;
Физическое резервное копирование, быстрое и надежное, автоматическая проверка наличия набора результатов резервного копирования после завершения резервного копирования, быстрое восстановление и т. д.
Usage: [innobackupex [--defaults-file=#] --backup | innobackupex [--defaults-file=#] --prepare] [OPTIONS]
резервное копирование --> журнал приложений --> восстановление
Журнал приложений: --apply-log
восстановить: --copy-back
Полная резервная копия:
Полный + бинлог (резюме):
Резервное копирование: innobackupex --user= --password= --host= /PATH/TO/BACKUP_DIR
Подготовьте: innobackupex --apply-log /PATH/TO/BACKUP_DIR
Восстановление: innobackupex --copy-back
Примечание: --copy-back необходимо выполнить локально на хосте mysqld, служба mysqld не может быть запущена, и может потребоваться сбросить innodb_log_file_size;
Репликация MySQL:
Master/Slave
Master: write/read
Slaves: read
Конфигурация:
Главный сервер:
View Code
С сервера:
View Code
** Репликация «главный-главный»: ** Взаимный главный-ведомый: оба узла должны открыть бинарный журнал и релейный журнал соответственно;
1. Данные противоречивы;
2. Автоматически увеличивать идентификатор;
Определить узел с нечетным идентификатором
auto_increment_offset=1
auto_increment_increment=2
Другой узел использует четный идентификатор
auto_increment_offset=2
auto_increment_increment=2
Конфигурация:
1. server_id должен использовать разные значения;
2. Включены как binlog, так и relay log;
3. Имеется таблица с автоматическим ростом id, для того, чтобы id не конфликтовал, необходимо определить метод ее автоматического роста;
После запуска службы выполните следующие два шага:
4. Авторизовать учетные записи пользователей с правами копирования;
5. Каждый назначает другую сторону главным узлом;
Проблемы, на которые стоит обратить внимание при копировании:
1. Установите для ведомой службы режим «только для чтения»;
Запустить read_only с сервера, но только для пользователей с не-SUPER привилегиями;
Заблокировать всех пользователей:
mysql> FLUSH TABLES WITH READ LOCK;
2. Постарайтесь обеспечить безопасность транзакций во время репликации
Включить параметры на главном узле:
sync_binlog = ON
Если вы используете механизм хранения InnoDB:
innodb_flush_logs_at_trx_commit=ON
innodb_support_xa=ON
3. Старайтесь избегать автоматического запуска потока репликации при неожиданном завершении работы подчиненного сервера.
4. Ведомый узел: установите параметры
sync_master_info=ON
sync_relay_log_info=ON
полусинхронная репликация
Поддерживает несколько плагинов: /usr/lib64/mysql/plugins/
Требуется установка для использования:
mysql> INSTALL PLUGIN plugin_name SONAME 'shared_library_name';
Полусинхронная репликация: semisync_master.so, semisync_slave.so
главный узел:
View Code
Из узла:
View Code
SHOW GLOBAL VARIABLES LIKE '%rpl%';
SHOW GLOBAL STATUS LIKE '%rpl%';
оптимизация mysql
Фильтр репликации MySQL и обслуживание мониторинга
Репликация только ограниченного числа данных, связанных с базой данных, но не всех, выполняемая фильтрами репликации;
Есть две идеи реализации:
(1) Главный сервер
View Code
(2) Ведомый сервер
View Code
Мониторинг и обслуживание репликации:
(1) Очистить журнал: ОЧИСТИТЬ
PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr };
(2) Мониторинг репликации
MASTER: SHOW MASTER STATUS; SHOW BINLOG EVENTS; SHOW BINARY LOGS;
SLAVE: ПОКАЗАТЬ СТАТУС SLAVE;
(3) Чтобы определить, согласуются ли данные главного и подчиненного узлов, проверьте КОНТРОЛЬНУЮ СУММУ таблицы и используйте pt-table-checksum в percona-tools;
(4) Метод восстановления, когда данные ведущий-ведомый несовместимы: повторное копирование;
Разделение чтения-записи при репликации master-slave
Разделение чтения-записи при репликации master-slave:
ProxySQL
www.proxysql.com/, ProxySQL is a high performance, high availability, protocol aware proxy for MySQL and forks (like Percona Server and MariaDB).
GitHub.com/sys own/pro выбирает…
Пример конфигурации ProxySQL:
View Code
Пример конфигурации maxscale:
View Code
Обычно используемые параметры оптимизации MySQL
Параметры, относящиеся к механизму хранения InnoDB:
innodb_buffer_pool_size: индекс, данные, буфер при вставке данных, обычно 70-80% выделенного сервера;
Если сам набор данных невелик, можно установить разумное значение в соответствии с диапазоном изменения данных и запланированной продолжительностью, которая немного превышает предполагаемое целевое значение;
innodb_buffer_pool_instances: количество разделов (экземпляров) buffer_pool;
innodb_file_per_table: многие расширенные функции innodb зависят от этого параметра;
innodb_read_io_threads:
innodb_write_io_threads
Количество потоков ввода-вывода для чтения и записи файлов; его можно настроить соответствующим образом в зависимости от параллелизма и количества ядер ЦП;
innodb_open_files: верхний предел количества файлов, которые могут быть открыты innodb;
innodb_flush_method:
innodb_thread_concurrency=
skip_name_resolve = ON
max_connections
Журнал транзакций:
innodb_log_files_in_group: количество лог-файлов в группе, не менее 2;
innodb_log_file_size: размер файла журнала, по умолчанию 5M, рекомендуется увеличить это значение;
innodb_flush_logs_at_trx_commit:
0: буфер журнала (память) синхронизируется с файлом журнала один раз в секунду, и в это же время будет выполняться операция синхронизации из файла журнала в файл данных;
1: при каждой отправке буфер журнала синхронизируется с файлом журнала, и операция синхронизации из файла журнала в файл данных выполняется одновременно;
2: при отправке каждый раз буфер журнала синхронизируется с файлом журнала, но операция синхронизации из файла журнала в файл данных не будет выполняться одновременно;
Предложение: отключите автоматическую фиксацию, затем установите для этого значения значение 1 или 2;
Содержание небольшой серии здесь!
Редактор собрал некоторые основные технические материалы MySQL и коллекцию последних материалов интервью по Java в 2020 году.
Публичный номер: Kylin меняет ошибку