Резюме на 30 000 слов, суть оптимизации Mysql

MySQL

В этой статье много знаний и много места, пожалуйста, наберитесь терпения, чтобы изучить

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

Зачем оптимизировать

  • Узким местом пропускной способности системы часто является скорость доступа к базе данных.
  • По мере работы приложения в базе данных будет все больше и больше данных, и время обработки будет соответственно медленнее
  • Данные хранятся на диске, а скорость чтения и записи не идет ни в какое сравнение с памятью

Как оптимизировать

  • При проектировании базы данных: таблица базы данных, дизайн поля, механизм хранения
  • Эффективно используйте функции, предоставляемые самой MySQL, такие как индексы и т. д.
  • Горизонтальное масштабирование: кластер MySQL, балансировка нагрузки, разделение чтения и записи.
  • Оптимизация операторов SQL (с небольшим успехом)

полевой дизайн

Выбор типов полей, проектные спецификации, парадигмы, общие случаи проектирования

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

Хранить IP

INET_ATON(str), адрес по номеру

INET_NTOA(number), номер для адреса

Тип перечисления (один выбор) и тип коллекции (множественный выбор) внутри MySQL

Однако он не часто используется из-за высокой стоимости обслуживания.Таблица ассоциацийспособ заменитьenum

Принцип: выбор типов данных фиксированной и нефиксированной длины.

Decimal не потеряет точности, а объем памяти будет увеличиваться с увеличением данных. double занимает фиксированное пространство, и хранение больших чисел потеряет точность. Также есть varchar, текст нефиксированной длины

количество

Точность данных высока, и есть проблемы с точностью при работе и хранении десятичных дробей (все десятичные дроби не могут быть преобразованы в двоичные).

десятичный с фиксированной запятой

price decimal(8,2)Числа с фиксированной точкой с двумя десятичными знаками, числа с фиксированной точкой поддерживают очень большие числа (даже больше, чемint,bigintколичество диапазонов хранения)

Небольшие единицы и большие суммы, чтобы избежать десятичных знаков

юань -> центы

Хранение строк

Фиксированная длинаchar, неопределенная длинаvarchar、text(до 65535, гдеvarcharтакже потребляет 1-3 байта длины записи, в то время какtextИспользуйте дополнительное пространство для записи длины)

Принцип: выбирайте небольшие типы данных и указывайте как можно меньше длины.

Принцип: по возможности используйте not null

НетnullПоля обрабатываются болееnullОбработка полей более эффективна! и не нужно судить о том,null.

nullВ MySQL это непросто, для хранения требуется дополнительное пространство, а для операций также требуются специальные операторы. какselect null = nullиselect null <> null(<>является знаком неравенства) имеет тот же результат, толькоis nullиis not nullопределить, является ли полеnull.

Как хранить? Каждая запись в MySQL требует дополнительного места для хранения, указывающего, является ли каждое полеnull. Поэтому для заполнителей обычно используются специальные данные, такие какint not null default 0,string not null default ‘’

Принцип: Аннотации полей должны быть полными, см. название

Принцип: в одной таблице не должно быть слишком много полей.

Двадцать или тридцать это предел

Принцип: Поля могут быть зарезервированы

Сначала удовлетворите потребности бизнеса, прежде чем использовать вышеуказанные принципы.

Дизайн таблицы ассоциаций

иностранный ключforeign keyВозможно только сопоставление «один к одному» или «один ко многим».

один ко многим

Использовать внешние ключи

многие ко многим

Создайте отдельную таблицу, чтобы разделить «многие ко многим» на две «один ко многим».

один на один

Например, основная информация о продукте (item) и сведения о продукте (item_intro), обычно используя тот же первичный ключ или добавляя поле внешнего ключа (item_id)

Обычный формат

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

Первая нормальная форма 1NF: атомарность поля

Поля атомарны, и поля не могут быть далее разделены.

Реляционная база данных, первая нормальная форма по умолчанию

Обратите внимание на подверженный ошибкам момент. В схеме «один ко многим» запятые используются для разделения нескольких внешних ключей. Хотя этот метод удобен для хранения, он не способствует обслуживанию и индексации (например, поиску тегов с этикетки).javaстатья)

Вторая нормальная форма: устранение частичных зависимостей от первичных ключей

То есть добавить в таблицу в качестве первичного ключа поле, не связанное с бизнес-логикой.

Первичный ключ: Поле или набор полей, которые однозначно идентифицируют запись.

course_name course_class рабочий день (день недели) course_teacher
MySQL Учебный корпус 1525 в понедельник Чжан Сан
Java Учебный корпус 1521 среда Ли Си
MySQL Учебный корпус 1521 Пятница Чжан Сан

Зависимость: поле может определять поле B, тогда поле B зависит от поля A. Например, если вы знаете, что следующий урок будет уроком математики, вы можете определить, кто будет учителем. тогдакоторый деньиследующий урокИ может сформировать составной первичный ключ, который может определить, в какой класс идти, кто учитель и т. д. Но мы часто добавляемidКак первичный ключ, так и устранить частичную зависимость от первичного ключа.

Частичная зависимость от первичного ключа: поле зависит от части составного первичного ключа.

Решение: добавьте отдельное поле в качестве первичного ключа.

Третья нормальная форма: устранение транзитивных зависимостей от первичных ключей

Транзитивная зависимость: поле B зависит от A, а поле C зависит от B. Например, в приведенном выше примере, кто является учителем, зависит от того, какой класс, а какой класс зависит от первичного ключа.id. Следовательно, эту таблицу необходимо разделить на две таблицы: расписание и учебный план (независимые данные и независимые таблицы):

id weekday course_class course_id
1001 в понедельник Учебный корпус 1521 3546
course_id course_name course_teacher
3546 Java Чжан Сан

Это снижает избыточность данных (даже если занятия Java проводятся каждый день с понедельника по воскресенье, это толькоcourse_id:3546появилось 7 раз)

выбор механизма хранения

Ранний вопрос: как выбрать между MyISAM и Innodb?

Этой проблемы больше нет, Innodb постоянно совершенствуется, догоняя MyISAM по всем параметрам, а также по умолчанию используется MySQL.

Механизм хранения Механизм хранения: то, как данные, индексы и другие объекты хранятся в MySQL, является реализацией набора файловых систем.

функциональные различия

show engines

Engine Support Comment
InnoDB DEFAULT Supports transactions, row-level locking, and foreign keys
MyISAM YES MyISAM storage engine

разница хранения

MyISAM Innodb
формат файла Данные и индексы хранятся отдельно, данные.MYD,показатель.MYI Данные и индексы хранятся централизованно,.ibd
Можно ли перенести файл Да, таблица соответствует.frm,MYD,MYI3 файла Нет, потому что есть такжеdataдругие файлы под
порядок хранения записей Сохранить в порядке размещения записи Вставить по размеру первичного ключа
фрагментация пространства (удаление записей иflush table 表名После этого размер файла таблицы не меняется) производить. Время: Используйте командуoptimize table 表名выполнить не производит
дела не поддерживается служба поддержки
иностранный ключ не поддерживается служба поддержки
Поддержка блокировки (блокировки — это механизм, позволяющий избежать конкуренции за ресурсы, блокировки MySQL почти прозрачны для пользователей) блокировка на уровне таблицы Блокировка на уровне строки, блокировка на уровне таблицы, низкое усилие блокировки, высокая возможность параллелизма

расширение блокировки

блокировки на уровне таблицы (table-level lock):lock tables <table_name1>,<table_name2>... read/write,unlock tables <table_name1>,<table_name2>.... вreadЭто общая блокировка, и однажды заблокированная, она не может быть прочитана ни одним клиентом;writeЭто исключительная блокировка/запись, только заблокированный клиент может читать и писать, а другие клиенты не могут ни читать, ни писать. Блокируется одна таблица или несколько таблиц.

блокировка на уровне строки (row-level lock): блокирует одну или несколько строк записей. Общий замок:select * from <table_name> where <条件> LOCK IN SHARE MODE;, добавить общую блокировку в запись запроса;select * from <table_name> where <条件> FOR UPDATE;, добавьте монопольную блокировку к записи запроса. здесьпримечательныйявляется:innodbБлокировка строки на самом деле является блокировкой поддиапазона, которая блокирует часть диапазона в соответствии с условием, а не сопоставляет ее с определенной строкой, поэтому у нее также есть научное название: блокировка пробела. Напримерselect * from stu where id < 20 LOCK IN SHARE MODEзаблокируетidсуществует20вокруг следующего диапазона, вы не сможете вставитьidза18или22новый рекорд.

основа выбора

Если нет особых требований, используйте значение по умолчанию.InnodbВот и все.

MyISAM: Приложения, основанные на вставке для чтения и записи, такие как системы блогов, новостные порталы.

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

показатель

Отношение отображения между ключевыми словами и данными называется индексом (

Содержит ключевое слово и соответствующий адрес, записанный на диск
). Ключевые слова — это определенный контент, извлеченный из данных для идентификации и извлечения данных.

Почему поиск индекса выполняется быстро?

  • Ключевое слово относится к самим данным,
    небольшой объем данных
  • ключевое слово
    аккуратный
    Да, бинарный поиск может быстро определить местоположение

Библиотеки добавляют порядковые номера к каждой книге (категория-этаж-полка), словари составляют списки в алфавитном порядке для пояснений слов и так далее.

Типы индексов в MySQL

нормальный индекс(key),уникальный индекс(unique key),индекс первичного ключа(primary key),полный текстовый указатель(fulltext key)

Методы индексации трех индексов одинаковы, но существуют разные ограничения на ключевые слова индекса:

  • Обычный индекс: без ограничений по ключевым словам
  • Уникальный индекс: ключ, предоставленный записью, не может быть повторен
  • Индекс первичного ключа: требуется, чтобы ключ был уникальным, а не нулевым.

синтаксис управления индексом

индекс просмотра

show create table 表名:

desc 表名

создать индекс

Создайте индекс после создания таблицы

create TABLE user_index(
	id int auto_increment primary key,
	first_name varchar(16),
	last_name VARCHAR(16),
	id_card VARCHAR(18),
	information text
);

-- 更改表结构
alter table user_index
-- 创建一个first_name和last_name的复合索引，并命名为name
add key name (first_name,last_name),
-- 创建一个id_card的唯一索引，默认以字段名作为索引名
add UNIQUE KEY (id_card),
-- 鸡肋，全文索引不支持中文
add FULLTEXT KEY (information);

show create table user_index:

Укажите индекс при создании таблицы

CREATE TABLE user_index2 (
	id INT auto_increment PRIMARY KEY,
	first_name VARCHAR (16),
	last_name VARCHAR (16),
	id_card VARCHAR (18),
	information text,
	KEY name (first_name, last_name),
	FULLTEXT KEY (information),
	UNIQUE KEY (id_card)
);

падение индекса

Удалите обычные индексы, уникальные индексы и полнотекстовые индексы в соответствии с именем индекса:alter table 表名 drop KEY 索引名

alter table user_index drop KEY name;
alter table user_index drop KEY id_card;
alter table user_index drop KEY information;

Удалите индекс первичного ключа:alter table 表名 drop primary key(потому что есть только один первичный ключ). Здесь стоит отметить, что вы не можете сделать это напрямую, если первичный ключ автоматически увеличивается (автоинкремент зависит от индекса первичного ключа):

Нужно отменить саморазвитие и потом удалить:

alter table user_index
-- 重新定义字段
MODIFY id int,
drop PRIMARY KEY

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

план выполненияобъяснить

CREATE TABLE innodb1 (
	id INT auto_increment PRIMARY KEY,
	first_name VARCHAR (16),
	last_name VARCHAR (16),
	id_card VARCHAR (18),
	information text,
	KEY name (first_name, last_name),
	FULLTEXT KEY (information),
	UNIQUE KEY (id_card)
);
insert into innodb1 (first_name,last_name,id_card,information) values ('张','三','1001','华山派');

мы можем пройтиexplain selelctЧтобы проанализировать план выполнения перед выполнением оператора SQL:

Из приведенного выше рисунка видно, что этот оператор SQL извлекается в соответствии с индексом первичного ключа.

План выполнения таков: при выполнении оператора SQL он сначала проанализирует и оптимизирует, чтобы сформировать план выполнения, а затем выполнит его в соответствии с планом выполнения.

Сценарии использования индекса (выделено)

where

На рисунке выше, согласноidзаписи запросов, потому чтоidДля поля устанавливается только индекс первичного ключа, поэтому необязательным индексом для этого выполнения SQL является только индекс первичного ключа.Если их несколько, в качестве основы для извлечения будет выбран лучший.

-- 增加一个没有建立索引的字段
alter table innodb1 add sex char(1);
-- 按sex检索时可选的索引为null
EXPLAIN SELECT * from innodb1 where sex='男';

Когда поле не проиндексировано, вы можете попытаться проиндексировать поле в соответствии с эффективностью запроса поля (alter table 表名 add index(字段名)), та же эффективность выполнения SQL, вы обнаружите, что эффективность запросов будет значительно повышена (чем больше объем данных, тем более очевидно).

order by

когда мы используемorder byПри сортировке результатов запроса по полю, если поле не проиндексировано, план выполнения будет использовать внешнюю сортировку для всех запрошенных данных (считывать данные с жесткого диска в память пакетами и использовать внутреннюю сортировку, и, наконец, объединить результаты сортировки), эта операция сильно влияет на производительность, поскольку все данные, участвующие в запросе, необходимо считать с диска в память (если один фрагмент данных слишком велик или объем данных слишком велик, эффективность снизится), не говоря уже о сортировке после чтения памяти.

Но если мы проиндексируем полеalter table 表名 add index(字段名), то, поскольку сам индекс упорядочен, данные можно извлекать один за другим непосредственно в соответствии с порядком индекса и отношением отображения. И если он разбит на страницы, то просто используйтеПолучить данные, соответствующие индексу в определенном диапазоне индексной таблицы, вместо вышеуказанногоудалить все данныеСортировка и возврат диапазона данных. (извлечение данных с диска больше всего влияет на производительность)

join

правильноjoinОтношение сопоставления утверждений (on) индексирование задействованных полей может повысить эффективность

покрытие индекса

Если все запрашиваемые поля были проиндексированы, механизм будет напрямую запрашивать индексную таблицу, не обращаясь к исходным данным (в противном случае, пока одно поле не проиндексировано, будет выполнено полное сканирование таблицы), что называется индексным покрытием. . Поэтому нам нужноselectЗадний

Пишите только необходимые поля запроса
, чтобы увеличить вероятность покрытия индексом.

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

Детали синтаксиса (точки)

В сценарии, соответствующем использованию индекса (where/order by/join onили охват индекса), индекс не обязательно используется

Поля должны отображаться независимо

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

select * from user where id = 20-1;
select * from user where id+1 = 20;

как запрос, не может начинаться с подстановочных знаков

Например, заголовок поиска содержитmysqlстатья:

select * from article where title like '%mysql%';

Этот план выполнения SQL не использует индексы (likeВыражение сопоставления операторов начинается с подстановочного знака), поэтому оно может выполнять только полное сканирование таблицы, что крайне неэффективно и почти не используется в практических проектах. Как правило, используется полнотекстовый индекс, поддерживающий китайский язык, предоставленный третьей стороной.

нозапрос ключевого словаФункция напоминания о горячем поиске все еще может быть выполнена, например, набравmysqlнапомнить позжеmysql 教程,mysql 下载,mysql 安装步骤Ждать. Используются следующие предложения:

select * from article where title like 'mysql%';

этоlikeМожно использовать индекс (конечно, при условии, чтоtitleполе индексируется).

Составные индексы действительны только для первого поля.

Создайте составной индекс:

alter table person add index(first_name,last_name);

Принцип заключается в том, чтобы сначала установить индекс в соответствии сfirst_nameОтсортируйте ключевые слова, извлеченные изlast_nameИзвлеченные ключевые слова сортируются, то есть индексная таблица основана только на записях.first_nameЗначения полей упорядочены.

следовательноselect * from person where first_name = ?индекс доступен иselect * from person where last_name = ?Невозможно использовать index.

Итак, каков сценарий применения этого составного индекса?

Комбинированный запрос

например, дляselect * person from first_name = ? and last_name = ?, составной индекс сравниваетсяfirst_nameиlast_nameСамо по себе индексирование более эффективно. Понятно, первый двоичный поиск по составному индексу сfirst_name = ?сопоставление записей, а затем бинарный поиск в этих записях с помощьюlast_nameСоответствующие записи включают только одну индексную таблицу. Отдельное индексирование выполняется вfirst_nameРазделите индексную таблицу, чтобы найти иfirst_name = ?совпадающие записи, затем вlast_nameРазделите индексную таблицу, чтобы найти иlast_name = ?записи, пересечение двух.

или оба условия имеют доступные индексы

Если на одной стороне нет доступного индекса, это приведет к полному сканированию таблицы всего оператора SQL.

Значение статуса, не простой в использовании индекс

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

Как создать индекс

  • Построить базовый индекс: atwhere、order by、joinИндекс на поле.
  • Оптимизация, составной индекс: на основе бизнес-логики
    • Если условия часто появляются вместе, рассмотрите возможность обновления многостолбцового индекса до
      составной индекс
    • Если за счет увеличения индекса отдельных полей может появиться
      покрытие индекса
      , то вы можете рассмотреть возможность индексации этого поля
    • При запросе индексы, которые обычно не используются, должны быть удалены.

индекс префикса

грамматика:index(field(10)), используйте первые 10 символов значения поля для построения индекса, по умолчанию для построения индекса используется все содержимое поля.

Предпосылка: Префикс имеет высокую степень идентификации. Например, пароли подходят для построения префиксного индекса, потому что пароли практически разные.

Сложность практики
: длина перехвата префикса.

мы можем использоватьselect count(*)/count(distinct left(password,prefixLen));, регулируя отprefixLenЗначение (самоувеличивающееся от 1) для просмотра средней степени совпадения различных длин префикса, хорошо, когда оно близко к 1 (представляющее префикс пароля)prefixLenсимволы могут почти определить уникальную запись)

структура хранения индексов

BTree

btree (многостороннее сбалансированное дерево поиска) является широко используемым

Индексация на диске
Структура данных, которая также является реализацией большинства индексных таблиц базы данных.

отadd index(first_name,last_name)Например:

Узел BTree может хранить несколько ключевых слов, а размер узла зависит от файловой системы компьютера, поэтому мы можем заставить узел хранить больше ключевых слов, уменьшив длину поля индекса. Если ключевые слова в узле заполнены, таблица индексов может быть расширена с помощью указателей дочерних узлов между каждым ключевым словом, но порядок структуры не может быть разрушен, например, согласноfirst_nameПервый заказ,last_nameВторое упорядоченное правило, недавно добавленное韩香может быть подключен к韩康после.白起 < 韩飞 < 韩康 < 李世民 < 赵奢 < 李寻欢 < 王语嫣 < 杨不悔. Это та же идея, что и у бинарного дерева поиска, за исключением того, что эффективность поиска бинарного дерева поиска равнаlog(2,N)(логарифм N по основанию 2), а эффективность поиска BTree равнаlog(x,N)(где x — количество ключевых слов узла, которое может достигать более 1000).

отlog(1000+,N)Можно видеть, что небольшое количество операций чтения с диска может пройти через большой объем данных, что также является целью разработки btree.

Структура кластера B+Tree

В структуре кластера (также обновленной на BTree) ключевые слова и записи хранятся вместе.

Только в MySQLInnodbиз

Индекс первичного ключа представляет собой кластерную структуру
, другие индексы включаютInnodbИндексы непервичных ключей являются типичными структурами BTree.

хэш-индекс

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

кэш запросов

тайникselectрезультат запроса оператора

Включить кеширование в файле конфигурации

на окнах даmy.ini, в линуксе естьmy.cnf

существует[mysqld]конфигурация в сегментеquery_cache_type:

  • 0: не включено
  • 1: включено, все кеши кэшируются по умолчанию, и их необходимо добавить в операторе SQL.select sql-no-cacheпредложить сбросить кеш
  • 2: On, по умолчанию не кешируется, его нужно добавить в оператор SQLselect sql-cacheактивно кэшировать (
    Обычно используется
    )

После изменения конфигурации необходимо выполнить перезагрузку, чтобы конфигурация вступила в силу.После перезагрузки можно пройтиshow variables like ‘query_cache_type’;видеть:

show variables like 'query_cache_type';
query_cache_type	DEMAND
Установить размер кеша на стороне клиента

через элементы конфигурацииquery_cache_sizeустанавливать:

show variables like 'query_cache_size';
query_cache_size	0

set global query_cache_size=64*1024*1024;
show variables like 'query_cache_size';
query_cache_size	67108864
кешировать результаты запроса

select sql_cache * from user;

сбросить кеш

reset query cache;

Проблема инвалидации кеша (большая проблема)

При изменении таблицы данных все кэши, основанные на этой таблице данных, удаляются. (Управление на поверхностном уровне не является управлением на уровне записи, поэтому частота отказов выше)

Меры предосторожности
  1. приложение, не должно заботитьсяquery cacheиспользование. Вы можете попробовать использовать его, но неquery cacheОпределитесь с бизнес-логикой, потому чтоquery cacheУправляется администратором баз данных.
  2. Кэш хранится с оператором SQL в качестве ключа, поэтому, даже если оператор SQL имеет ту же функцию, если есть дополнительное пространство или другой регистр, кеш не будет сопоставлен.

раздел

В общем, таблица, которую мы создаем, соответствует набору файлов хранилища, используяMyISAMмеханизм хранения.MYIи.MYDфайл, использоватьInnodbмеханизм хранения.ibdи.frm(структура таблицы).

Когда объем данных большой (как правило, более 10 миллионов записей), производительность MySQL начнет снижаться.В это время нам необходимо распределить данные по нескольким наборам файлов хранилища.

Гарантия эффективности выполнения одного файла
.

Наиболее распространенная схема разделенияidраздел следующим образомidХэш-значение по модулю 10 для равномерного распределения данных по 10..ibdВ файле хранилища:

create table article(
	id int auto_increment PRIMARY KEY,
	title varchar(64),
	content text
)PARTITION by HASH(id) PARTITIONS 10

Проверятьdataсодержание:

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

Алгоритмы секционирования, предоставляемые MySQL

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

hash(field)

Тот же вход дает тот же результат. Результаты вывода не имеют ничего общего с тем, является ли ввод регулярным или нет.

Только для целочисленных полей

key(field)

иhash(field)того же характера, за исключением того, чтоkeyда

обрабатывать строки
да, чемhash()Еще один шаг — вычислить целое число из строки, чтобы выполнить операцию по модулю.

create table article_key(
	id int auto_increment,
	title varchar(64),
	content text,
	PRIMARY KEY (id,title)	-- 要求分区依据字段必须是主键的一部分
)PARTITION by KEY(title) PARTITIONS 10

алгоритм диапазона

это

Условное разбиение
Алгоритм разделения в соответствии с диапазоном размера данных (распределение данных по разным разделам с использованием определенного условия).

Таким образом, согласно времени публикации статьи, данные хранятся в разделах в августе, сентябре и октябре 2018 года:

create table article_range(
	id int auto_increment,
	title varchar(64),
	content text,
	created_time int,	-- 发布时间到1970-1-1的毫秒数
	PRIMARY KEY (id,created_time)	-- 要求分区依据字段必须是主键的一部分
)charset=utf8
PARTITION BY RANGE(created_time)(
	PARTITION p201808 VALUES less than (1535731199),	-- select UNIX_TIMESTAMP('2018-8-31 23:59:59')
	PARTITION p201809 VALUES less than (1538323199),	-- 2018-9-30 23:59:59
	PARTITION p201810 VALUES less than (1541001599)	-- 2018-10-31 23:59:59
);

Примечание. Условные операторы можно использовать только с

less than
, что означает, что меньший диапазон должен быть помещен первым, как показано выше.p201808,p201819,p201810Порядок, в котором определяются разделы,created_timeЗначения варьируются от малых до больших и не могут быть обращены вспять.

insert into article_range values(null,'MySQL优化','内容示例',1535731180);
flush tables;	-- 使操作立即刷新到磁盘文件

В связи со временем публикации вставленной статьи1535731180меньше, чем1535731199(2018-8-31 23:59:59), поэтому хранится вp201808В каком разделе хранится этот алгоритм, зависит от состояния данных.

алгоритм списка

Это также условное разбиение, разбиение по значению списка (in (值列表)).

create table article_list(
	id int auto_increment,
	title varchar(64),
	content text,
	status TINYINT(1),	-- 文章状态：0-草稿，1-完成但未发布，2-已发布
	PRIMARY KEY (id,status)	-- 要求分区依据字段必须是主键的一部分
)charset=utf8
PARTITION BY list(status)(
	PARTITION writing values in(0,1),	-- 未发布的放在一个分区	
	PARTITION published values in (2)	-- 已发布的放在一个分区
);

insert into article_list values(null,'mysql优化','内容示例',0);
flush tables;

Синтаксис управления разделами

range/list

добавить раздел

В предыдущей статье мы пытались использоватьrangeСтатьи архивируются по месяцам, по мере увеличения времени нужно добавлять месяц:

alter table article_range add partition(
	partition p201811 values less than (1543593599)	-- select UNIX_TIMESTAMP('2018-11-30 23:59:59')
	-- more
);

удалить раздел

alter table article_range drop PARTITION p201808

Уведомление:

После удаления раздела исходные данные в разделе также будут удалены!

key/hash

Добавить раздел

alter table article_key add partition partitions 4

уничтожить раздел

alter table article_key coalesce partition 6

key/hashУправление разделами не удаляет данные, но каждая корректировка (добавление или удаление раздела) перезаписывает все данные в новый раздел.

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

использование перегородок

Когда объем данных в таблице данных велик, повышение эффективности, вызванное секционированием, будет только проявляться.

Повышение эффективности, вызванное секционированием, будет более очевидным только в том случае, если поле поиска является секционированным полем. следовательно,

Выбор поля раздела важен
,и
Бизнес-логика должна быть скорректирована соответствующим образом в соответствии с полем раздела, насколько это возможно.
(Попробуйте использовать поля раздела в качестве условий запроса).

Горизонтальное разделение и вертикальное разделение

Горизонтальное разделение: храните данные отдельно, создавая несколько таблиц с одинаковой структурой.

Вертикальное разбиение: Поместите поля, которые часто используются вместе, в отдельную таблицу, и между записями разделенной таблицы будет взаимно однозначное соответствие.

Причина разделения таблицы
  • Распаковать базу данных
  • Ограничения алгоритма разделения
  • Поддержка базы данных не полная (5.1послеmysqlПоддерживает только работу с разделами)
Решение для дублирующего идентификатора
  • Заимствуйте сторонние приложения, такие какmemcache、redisизidавтоматическое приращение
  • Создайте отдельный, содержащий толькоidТаблица полей, каждый раз, когда поле увеличивается как запись данныхid

кластер

Горизонтальное расширение: Принципиально (производительность аппаратной обработки одной машины ограничена) для повышения производительности базы данных. Связанные технологии из этого:

Разделение чтения-записи, балансировка нагрузки

Установите и настройте репликацию master-slave

окрестности

Установить и настроить

Разархивируйте в каталог службы, предоставляемой извне (я создал ее сам/export/serverхранить)

tar xzvf mysql-5.7.23-linux-glibc2.12-x86_64.tar.gz -C /export/server
cd /export/server
mv mysql-5.7.23-linux-glibc2.12-x86_64 mysql

Добавить кmysqlГруппа и владелец каталога:

groupadd mysql
useradd -r -g mysql mysql
cd /export/server
chown -R mysql:mysql mysql/
chmod -R 755 mysql/

Создайтеmysqlкаталог хранения данных (где/export/dataэто каталог, который я создал для хранения данных для различных сервисов)

mkdir /export/data/mysql

инициализацияmysqlСлужить

cd /export/server/mysql
./bin/mysqld --basedir=/export/server/mysql --datadir=/export/data/mysql --user=mysql --pid-file=/export/data/mysql/mysql.pid --initialize

В случае успеха он покажетmysqlизrootНачальный пароль учетной записи, запишите его для последующих входов. Если в ошибке отсутствуют зависимости, используйтеyum installyУстановить последовательно

настроитьmy.cnf

vim /etc/my.cnf

[mysqld]
basedir=/export/server/mysql
datadir=/export/data/mysql
socket=/tmp/mysql.sock
user=mysql
server-id=10 # 服务id，在集群时必须唯一，建议设置为IP的第四段
port=3306
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/export/data/mysql/error.log
pid-file=/export/data/mysql/mysql.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

Добавить сервис в автозапуск при загрузке

cp /export/server/mysql/support-files/mysql.server /etc/init.d/mysqld

запустить службу

service mysqld start

Настройте переменные среды в/etc/profileдобавить следующее в

# mysql env
MYSQL_HOME=/export/server/mysql
MYSQL_PATH=$MYSQL_HOME/bin
PATH=$PATH:$MYSQL_PATH
export PATH

Сделайте так, чтобы конфигурация вступила в силу

source /etc/profile

использоватьrootАвторизоваться

mysql -uroot -p
# 这里填写之前初始化服务时提供的密码

После авторизации изменитеrootПароль учетной записи (пароль для удобства поменял на root), иначе при работе с БД будет сообщение об ошибке

set password=password('root');
flush privileges;

Сделать сервис доступным для всех удаленных клиентов

use mysql;
update user set host='%' where user='root';
flush privileges;

Это можно использовать на хостеnavicatУдаленно подключиться к mysql на виртуальной машине linux

Настройте главные и подчиненные узлы

настроить мастер

отlinux(192.168.10.10)Вверхmysqlзаmaster, гостья (192.168.10.1)ВверхmysqlзаslaveНастройте репликацию master-slave.

Исправлятьmasterизmy.cnfследующее

[mysqld]
basedir=/export/server/mysql
datadir=/export/data/mysql
socket=/tmp/mysql.sock
user=mysql
server-id=10
port=3306
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

log-bin=mysql-bin	# 开启二进制日志
expire-logs-days=7  # 设置日志过期时间，避免占满磁盘
binlog-ignore-db=mysql	# 不使用主从复制的数据库
binlog-ignore-db=information_schema
binlog-ignore-db=performation_schema
binlog-ignore-db=sys
binlog-do-db=test	#使用主从复制的数据库

[mysqld_safe]
log-error=/export/data/mysql/error.log
pid-file=/export/data/mysql/mysql.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

перезагружатьmaster

service mysqld restart

АвторизоватьсяmasterПроверьте, вступила ли конфигурация в силу (ONвключен, по умолчаниюOFF):

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+

существуетmasterСоздайте резервную учетную запись в базе данных:backupэто имя пользователя,%представляет собой любой удаленный адрес, пользователяbackможно использовать пароль1234Подключение через любой удаленный клиентmaster

grant replication slave on *.* to 'backup'@'%' identified by '1234'

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

mysql> use mysql
mysql> select user,authentication_string,host from user;
+---------------+-------------------------------------------+-----------+
| user          | authentication_string                     | host      |
+---------------+-------------------------------------------+-----------+
| root          | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | %         |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| backup        | *A4B6157319038724E3560894F7F932C8886EBFCF | %         |
+---------------+-------------------------------------------+-----------+

новыйtestбазу данных, создатьarticleформу для последующего тестирования

CREATE TABLE `article` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(64) DEFAULT NULL,
  `content` text,
  PRIMARY KEY (`id`)
) CHARSET=utf8;

Перезапустите службу и сбросьте состояние базы данных в файл хранилища (with read lockУказывает, что во время этого процесса клиент может только считывать данные для получения согласованного моментального снимка)

[root@zhenganwen ~]# service mysqld restart
Shutting down MySQL.... SUCCESS! 
Starting MySQL. SUCCESS! 
[root@zhenganwen mysql]# mysql -uroot -proot
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

Проверятьmasterв текущем двоичном журнале и смещении (обратите внимание наFileиPosition)

mysql> show master status \G
*************************** 1. row ***************************
             File: mysql-bin.000002
         Position: 154
     Binlog_Do_DB: test
 Binlog_Ignore_DB: mysql,information_schema,performation_schema,sys
Executed_Gtid_Set: 
1 row in set (0.00 sec)

FileУказывает лог, реализующий функцию репликации, то есть приведенный выше рисунокBinary log;PositionозначаетBinary logПосле смещения файл журнала будет синхронизирован сslave, то те, что перед смещением, необходимо импортировать вручную.

Любая модификация на главном сервере будет сохранена в двоичном журнале, запустить поток ввода-вывода с сервера (фактически клиентский процесс основного сервера), подключиться к главному серверу, чтобы запросить чтение двоичного журнала, а затем поместить Прочитанный двоичный журнал записывается в локальный журнал Realy. Запустите поток SQL с сервера, чтобы регулярно проверять журнал Realy, и, если будут обнаружены какие-либо изменения, немедленно выполнить измененное содержимое на локальном компьютере.

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

Импорт вручную изmasterэкспортировать данные в

mysqldump -uroot -proot -hlocalhost test > /export/data/test.sql

будетtest.sqlСодержание вslaveВыполните его снова.

Настроить ведомое устройство

Исправлятьslaveизmy.iniв файле[mysqld]часть

log-bin=mysql
server-id=1 #192.168.10.1

Перезапустите после сохранения измененийslave,WIN+R->services.msc->MySQL5.7-> перезагрузить

Авторизоватьсяslaveэкзаменlog_binСледует ли включить:

show VARIABLES like 'log_bin';

настроить сmasterСинхронная репликация:

stop slave; 
change master to
    master_host='192.168.10.10',	-- master的IP
    master_user='backup',		    -- 之前在master上创建的用户
    master_password='1234',
    master_log_file='mysql-bin.000002', -- master上 show master status \G 提供的信息
    master_log_pos=154;

включитьslaveузел и просмотр состояния

mysql> start slave;
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.10
                  Master_User: backup
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 154
               Relay_Log_File: DESKTOP-KUBSPE0-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 537
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 10
                  Master_UUID: f68774b7-0b28-11e9-a925-000c290abe05
             Master_Info_File: C:\ProgramData\MySQL\MySQL Server 5.7\Data\master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

Обратите внимание на строки 4, 14 и 15. Если они совпадают со мной, значитslaveНастроено успешно

контрольная работа

закрытиеmasterблокировка чтения

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

В направленииmasterвставить часть данных

mysql> use test
mysql> insert into article (title,content) values ('mysql master and slave','record the cluster building succeed!:)');
Query OK, 1 row affected (0.00 sec)

ПроверятьslaveСинхронизируются ли данные автоматически

mysql> insert into article (title,content) values ('mysql master and slave','record the cluster building succeed!:)');
Query OK, 1 row affected (0.00 sec)

На данный момент настройка репликации master-slave прошла успешно! :)

Используйте команду mysqlreplicate для быстрой сборки репликации master-slave Mysql.

разделение чтения-записи

Разделение чтения-записи основано на репликации ведущий-ведомый, которая, в свою очередь, обеспечивает разделение чтения-записи. Поскольку для репликации master-slave требуетсяslaveНе могу писать, могу только читать (если правильноslaveвыполнить операцию записи, затемshow slave statusпредставитSlave_SQL_Running=NO, на этом этапе вам нужно вручную синхронизировать, как упоминалось ранееslave).

Схема 1. Определить два подключения

Как мы определили, когда изучали JDBCDataBaseТочно так же мы можем извлечьReadDataBase,WriteDataBase implements DataBase, но таким образом нельзя использовать превосходную технологию пула потоков, такую ​​какDruidDataSourceПомогите нам управлять соединениями, также не можете воспользоватьсяSpring AOPсделать пару соединенийDAOслой прозрачный.

Вариант 2, используя Spring AOP

если вы можете использоватьSpring AOPЧтобы решить проблему переключения источника данных, вы можетеMybatis,Druidинтегрированы вместе.

мы интегрируемSpring1иMybatis, нам нужно только написать интерфейс DAO и соответствующийSQLутверждение, то кто создал экземпляр DAO? фактическиSpringСозданный для нас, он использует источник данных, который мы внедрили, чтобы помочь нам получить от него соединение с базой данных и использовать это соединение для выполнения.SQLПроцесс оператора и, наконец, процесс возврата соединения с источником данных.

Если мы можем вызвать интерфейс DAO в соответствии с соглашением об именах методов интерфейса (увеличениеaddXXX/createXXX,УдалитьdeleteXX/removeXXX,изменятьupdateXXXX,чекselectXX/findXXX/getXX/queryXXX) динамически выбирать источник данных (чтение источника данных, соответствующее соединениюmasterИсточник данных записи соответствует соединениюslave), тогда может быть достигнуто разделение чтения и записи.

Структура проекта

импортировать зависимости

Среди них, чтобы облегчить доступ к базе данных,mybatisиdruid, реализация динамического переключения источников данных в основном зависит отspring-aopиspring-aspects

<dependencies>
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis-spring</artifactId>
        <version>1.3.2</version>
    </dependency>
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.4.6</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-core</artifactId>
        <version>5.0.8.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-aop</artifactId>
        <version>5.0.8.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>5.0.8.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.1.6</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>6.0.2</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-context</artifactId>
        <version>5.0.8.RELEASE</version>
    </dependency>

    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-aspects</artifactId>
        <version>5.0.8.RELEASE</version>
    </dependency>

    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.16.22</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-test</artifactId>
        <version>5.0.8.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.12</version>
    </dependency>

</dependencies>

класс данных

package top.zhenganwen.mysqloptimize.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Article {

    private int id;
    private String title;
    private String content;
}

файл конфигурации весны

вRoutingDataSourceImplЭто основной класс, реализующий функцию динамического переключения, которая будет представлена ​​позже.

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">

    <context:property-placeholder location="db.properties"></context:property-placeholder>

    <context:component-scan base-package="top.zhenganwen.mysqloptimize"/>

    <bean id="slaveDataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="driverClassName" value="${db.driverClass}"/>
        <property name="url" value="${master.db.url}"></property>
        <property name="username" value="${master.db.username}"></property>
        <property name="password" value="${master.db.password}"></property>
    </bean>

    <bean id="masterDataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="driverClassName" value="${db.driverClass}"/>
        <property name="url" value="${slave.db.url}"></property>
        <property name="username" value="${slave.db.username}"></property>
        <property name="password" value="${slave.db.password}"></property>
    </bean>

    <bean id="dataSourceRouting" class="top.zhenganwen.mysqloptimize.dataSource.RoutingDataSourceImpl">
        <property name="defaultTargetDataSource" ref="masterDataSource"></property>
        <property name="targetDataSources">
            <map key-type="java.lang.String" value-type="javax.sql.DataSource">
                <entry key="read" value-ref="slaveDataSource"/>
                <entry key="write" value-ref="masterDataSource"/>
            </map>
        </property>
        <property name="methodType">
            <map key-type="java.lang.String" value-type="java.lang.String">
                <entry key="read" value="query,find,select,get,load,"></entry>
                <entry key="write" value="update,add,create,delete,remove,modify"/>
            </map>
        </property>
    </bean>

    <!-- Mybatis文件 -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="configLocation" value="classpath:mybatis-config.xml" />
        <property name="dataSource" ref="dataSourceRouting" />
        <property name="mapperLocations" value="mapper/*.xml"/>
    </bean>

    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="top.zhenganwen.mysqloptimize.mapper" />
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" />
    </bean>
</beans>

dp.properties

master.db.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC
master.db.username=root
master.db.password=root

slave.db.url=jdbc:mysql://192.168.10.10:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC
slave.db.username=root
slave.db.password=root

db.driverClass=com.mysql.jdbc.Driver

mybatis-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <typeAliases>
        <typeAlias type="top.zhenganwen.mysqloptimize.entity.Article" alias="Article"/>
    </typeAliases>
</configuration>

интерфейс маппера и конфигурационный файл

ArticleMapper.java

package top.zhenganwen.mysqloptimize.mapper;

import org.springframework.stereotype.Repository;
import top.zhenganwen.mysqloptimize.entity.Article;

import java.util.List;

@Repository
public interface ArticleMapper {

    List<Article> findAll();

    void add(Article article);

    void delete(int id);

}

ArticleMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="top.zhenganwen.mysqloptimize.mapper.ArticleMapper">
    <select id="findAll" resultType="Article">
        select * from article
    </select>

    <insert id="add" parameterType="Article">
        insert into article (title,content) values (#{title},#{content})
    </insert>

    <delete id="delete" parameterType="int">
        delete from article where id=#{id}
    </delete>
</mapper>

основной класс

RoutingDataSourceImpl
package top.zhenganwen.mysqloptimize.dataSource;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

import java.util.*;

/**
 * RoutingDataSourceImpl class
 * 数据源路由
 *
 * @author zhenganwen, blog:zhenganwen.top
 * @date 2018/12/29
 */
public class RoutingDataSourceImpl extends AbstractRoutingDataSource {

    /**
     * key为read或write
     * value为DAO方法的前缀
     * 什么前缀开头的方法使用读数据员，什么开头的方法使用写数据源
     */
    public static final Map<String, List<String>> METHOD_TYPE_MAP = new HashMap<String, List<String>>();

    /**
     * 由我们指定数据源的id，由Spring切换数据源
     *
     * @return
     */
    @Override
    protected Object determineCurrentLookupKey() {
        System.out.println("数据源为："+DataSourceHandler.getDataSource());
        return DataSourceHandler.getDataSource();
    }

    public void setMethodType(Map<String, String> map) {
        for (String type : map.keySet()) {
            String methodPrefixList = map.get(type);
            if (methodPrefixList != null) {
                METHOD_TYPE_MAP.put(type, Arrays.asList(methodPrefixList.split(",")));
            }
        }
    }
}

Его основная функция заключается в том, что изначально мы настроили только один источник данных, поэтомуSpringЭтот источник данных используется непосредственно при динамическом проксировании интерфейса DAO.Теперь, когда у нас есть два источника данных для чтения и записи, нам нужно добавить некоторую собственную логику, чтобы указать, какой интерфейс использовать, какой источник данных вызывать (интерфейс для чтения данные используетslave, интерфейс для записи данных используетmaster. это говоритSpringКласс источника данных для использованияAbstractRoutingDataSource, метод, который должен быть переопределенdetermineCurrentLookupKeyВозвращает идентификатор источника данных в сочетании сspringФайл конфигурации (строки 5 и 6 следующего кода)

<bean id="dataSourceRouting" class="top.zhenganwen.mysqloptimize.dataSource.RoutingDataSourceImpl">
    <property name="defaultTargetDataSource" ref="masterDataSource"></property>
    <property name="targetDataSources">
        <map key-type="java.lang.String" value-type="javax.sql.DataSource">
            <entry key="read" value-ref="slaveDataSource"/>
            <entry key="write" value-ref="masterDataSource"/>
        </map>
    </property>
    <property name="methodType">
        <map key-type="java.lang.String" value-type="java.lang.String">
            <entry key="read" value="query,find,select,get,load,"></entry>
            <entry key="write" value="update,add,create,delete,remove,modify"/>
        </map>
    </property>
</bean>

еслиdetermineCurrentLookupKeyвозвращениеreadзатем используйтеslaveDataSource, если вернутьсяwriteпросто используйтеmasterDataSource.

DataSourceHandler
package top.zhenganwen.mysqloptimize.dataSource;

/**
 * DataSourceHandler class
 * <p>
 * 将数据源与线程绑定，需要时根据线程获取
 *
 * @author zhenganwen, blog:zhenganwen.top
 * @date 2018/12/29
 */
public class DataSourceHandler {

    /**
     * 绑定的是read或write，表示使用读或写数据源
     */
    private static final ThreadLocal<String> holder = new ThreadLocal<String>();

    public static void setDataSource(String dataSource) {
        System.out.println(Thread.currentThread().getName()+"设置了数据源类型");
        holder.set(dataSource);
    }

    public static String getDataSource() {
        System.out.println(Thread.currentThread().getName()+"获取了数据源类型");
        return holder.get();
    }
}
DataSourceAspect
package top.zhenganwen.mysqloptimize.dataSource;

import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.context.annotation.EnableAspectJAutoProxy;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Set;

import static top.zhenganwen.mysqloptimize.dataSource.RoutingDataSourceImpl.METHOD_TYPE_MAP;

/**
 * DataSourceAspect class
 *
 * 配置切面，根据方法前缀设置读、写数据源
 * 项目启动时会加载该bean，并按照配置的切面（哪些切入点、如何增强）确定动态代理逻辑
 * @author zhenganwen,blog:zhenganwen.top
 * @date 2018/12/29
 */
@Component
//声明这是一个切面，这样Spring才会做相应的配置，否则只会当做简单的bean注入
@Aspect
@EnableAspectJAutoProxy
public class DataSourceAspect {

    /**
     * 配置切入点：DAO包下的所有类的所有方法
     */
    @Pointcut("execution(* top.zhenganwen.mysqloptimize.mapper.*.*(..))")
    public void aspect() {

    }

    /**
     * 配置前置增强，对象是aspect()方法上配置的切入点
     */
    @Before("aspect()")
    public void before(JoinPoint point) {
        String className = point.getTarget().getClass().getName();
        String invokedMethod = point.getSignature().getName();
        System.out.println("对 "+className+"$"+invokedMethod+" 做了前置增强，确定了要使用的数据源类型");

        Set<String> dataSourceType = METHOD_TYPE_MAP.keySet();
        for (String type : dataSourceType) {
            List<String> prefixList = METHOD_TYPE_MAP.get(type);
            for (String prefix : prefixList) {
                if (invokedMethod.startsWith(prefix)) {
                    DataSourceHandler.setDataSource(type);
                    System.out.println("数据源为："+type);
                    return;
                }
            }
        }
    }
}

Проверка разделения чтения и записи

Как проверить чтение изslaveЧто насчет средней школы? Копирование после записи может бытьslaveДанные в данных изменяются, а затем считывают данные, чтобы знать, чтоslaveчитать в.

Уведомление
, но правильноslaveПосле завершения операции записи необходимо вручную сброситьslaveиmasterСинхронизируйте, иначе репликация master-slave не удастся.

package top.zhenganwen.mysqloptimize.dataSource;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import top.zhenganwen.mysqloptimize.entity.Article;
import top.zhenganwen.mysqloptimize.mapper.ArticleMapper;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = "classpath:spring-mybatis.xml")
public class RoutingDataSourceTest {

    @Autowired
    ArticleMapper articleMapper;

    @Test
    public void testRead() {
        System.out.println(articleMapper.findAll());
    }

    @Test
    public void testAdd() {
        Article article = new Article(0, "我是新插入的文章", "测试是否能够写到master并且复制到slave中");
        articleMapper.add(article);
    }

    @Test
    public void testDelete() {
        articleMapper.delete(2);
    }
}

балансировки нагрузки

алгоритм балансировки нагрузки

  • голосование
  • Взвешенный циклический перебор: взвешивание по вычислительной мощности
  • Распределение нагрузки: в соответствии с текущим состоянием простоя (но проверьте использование памяти, загрузку ЦП и т. д. каждого узла, а затем сравните и выберите самый простой, эффективность слишком низкая)
Высокая доступность

В серверной архитектуре, чтобы гарантировать, что сервер не будет отключен и подключен к сети 7x24, необходимо предоставить резервные машины для каждого одноточечного сервера (сервер, предоставляемый одним сервером, например, сервер записи, промежуточное программное обеспечение базы данных).

Для сервера записи должен быть предоставлен тот же сервер с резервированием записи.Когда сервер записи исправен (избыточность записи определяется пульсом), резервирование записи действует как ведомое устройство для репликации содержимого сервера записи и выполнения Синхронизация: когда сервер записи выходит из строя, появляется резервный сервер записи, который продолжает служить сервером записи. Этот процесс прозрачен для внешнего мира, то есть внешний мир получает доступ к сервису только через один IP.

Типичный SQL

Онлайн DDL

DDL (язык определения базы данных) относится к определению структуры таблицы базы данных (create table)И обслуживание(alter table)язык. Выполните DDL онлайн, нижеMySQL5.6Управление версиями приведет к тому, что вся таблица будет монопольно заблокирована. В настоящее время таблица находится в состоянии обслуживания и не работает, что приведет к тому, что любой доступ к таблице в течение этого периода не будет отвечать. Но когдаMySQL5.6После этого поддержитеOnline DDL, что значительно сокращает время блокировки.

Техника оптимизации заключается в использовании DDL для поддержки структуры таблицы (например, добавление столбца или добавление индекса).

copy
Стратегия. Идея: создать новую таблицу, удовлетворяющую новой структуре, и преобразовать данные старой таблицы.
пункт за пунктом
Импортируйте (скопируйте) в новую таблицу, чтобы гарантировать
Меньше контента, заблокированного за один раз
(Импортируемые данные заблокированы), в то время как другие задачи можно выполнять на старой таблице. В процессе импорта все операции над старой таблицей записываются в виде журналов, после завершения импорта журнал обновлений выполняется снова на новой таблице (для обеспечения согласованности). Наконец, новая таблица заменяет старую таблицу (делается в приложении или переименовывается в базе данных, вид готов).

Но с обновлением MySQL проблема практически исчезла.

оператор импорта базы данных

При восстановлении данных может быть импортирован большой объем данных. На данный момент, чтобы импортировать быстро, вам нужно освоить некоторые навыки:

  1. при импорте
    Сначала отключите индексы и ограничения
    :
alter table table-name disable keys

После завершения импорта данных откройте индекс и ограничения и одновременно создайте индекс.

alter table table-name enable keys
  1. база данных, если используемый движокInnodb, Затем это
    По умолчанию транзакция добавляется к каждой инструкции записи.
    (Это также займет определенное время), поэтому рекомендуется вручную запустить транзакцию, затем выполнить определенный объем пакетного импорта и, наконец, отправить транзакцию вручную.
  2. Если формат команды SQL для пакетного импорта тот же, но данные разные, то сначала следуетprepare
    предварительно скомпилировано
    Это может сэкономить много времени на повторную компиляцию.
limit offset,rows

Старайтесь, чтобы не было большихoffset,Напримерlimit 10000,10Эквивалентно отбрасыванию количества запрошенных строк10000взять за линию10Line, вы можете добавить некоторые условия для фильтрации (заполнить фильтр), и не следует использоватьlimitПропустить уже запрошенные данные. Это

offsetделать бесполезную работу
Проблема. В соответствии с реальным проектом необходимо избегать ситуации с большими номерами страниц и пытаться направлять пользователей к условной фильтрации.

выберите *, чтобы использовать его экономно

То есть попробуй выбрать нужные тебе поляselect, но это влияние не очень большое, потому что передача по сети составляет более десятков сотен байт без особых задержек, а сейчас используются популярные ORM-фреймворкиselect *, но при оформлении таблицы мы уделяем внимание разделению полей с большим объемом данных, например, сведения о товаре можно отделить от таблицы сведений о товаре, чтобы это не повлияло на скорость загрузки при просмотре краткой страницы товара.

порядок по rand() не использовать

Его логика — случайная сортировка (генерирует случайное число для каждого фрагмента данных, а затем сортирует по размеру случайного числа). какselect * from student order by rand() limit 5Эффективность выполнения очень низкая, потому что он генерирует случайные числа и сортирует для каждого фрагмента данных в таблице, а нам нужны только первые 5 фрагментов.

Решение. В приложении сгенерируйте случайный первичный ключ и используйте первичный ключ для его извлечения из базы данных.

Однотабличные и многотабличные запросы

Многотабличный запрос:join, подзапросы — это запросы, включающие несколько таблиц. если вы используетеexplainПроанализировав план выполнения, вы обнаружите, что многотабличный запрос также представляет собой потабличный процесс, и, наконец, результаты объединяются. Следовательно, можно сказать, что запрос к одной таблице оказывает вычислительную нагрузку на приложение, в то время как запрос с несколькими таблицами оказывает вычислительную нагрузку на базу данных.

Теперь есть структура ORM, которая поможет нам решить проблему сопоставления объектов, вызванную запросом одной таблицы (при запросе одной таблицы, если обнаружен внешний ключ для автоматического запроса связанной таблицы, это запрос таблицы за таблицей) .

count(*)

существуетMyISAMВ механизме хранения количество строк в таблице записывается автоматически, поэтому используйтеcount(*)возможность быстро вернуться. иInnodbВнутри такого счетчика нет, нам нужно вручную считать количество записей, решение — использовать отдельную таблицу:

id table count
1 student 100
limit 1

Если можно определить, что получен только один элемент, рекомендуется добавитьlimit 1, на самом деле в этом нам помогает фреймворк ORM (автоматически добавится операция запроса одной записиlimit 1).

журнал медленных запросов

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

Включить журнал медленных запросов

Элемент конфигурации:slow_query_log

можно использоватьshow variables like ‘slov_query_log’Проверьте, включен ли он, если значение состоянияOFF,можно использоватьset GLOBAL slow_query_log = onчтобы включить его, он будетdatadirпроизвести следующийxxx-slow.logдокумент.

установить критическое время

Элемент конфигурации:long_query_time

Проверять:show VARIABLES like 'long_query_time', в секундах

настраивать:set long_query_time=0.5

На практике следует ставить с длинного на короткое время, то есть оптимизировать самый медленный SQL

Просмотр журналов

Как только SQL превысит установленное нами критическое время, он будет зарегистрирован вxxx-slow.logсередина

информация профиля

Элемент конфигурации:profiling

открыть профиль

set profiling=on

Если этот параметр включен, все детали выполнения SQL будут автоматически записываться.

mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | OFF   |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> set profiling=on;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Просмотр информации о профиле

show profiles

mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | ON    |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> insert into article values (null,'test profile',':)');
Query OK, 1 row affected (0.15 sec)

mysql> show profiles;
+----------+------------+-------------------------------------------------------+
| Query_ID | Duration   | Query                                                 |
+----------+------------+-------------------------------------------------------+
|        1 | 0.00086150 | show variables like 'profiling'                       |
|        2 | 0.15027550 | insert into article values (null,'test profile',':)') |
+----------+------------+-------------------------------------------------------+
Просмотр времени всех подробных шагов SQL по Query_ID

show profile for query Query_ID

вышеshow profilesРезультаты каждого SQL имеютQuery_ID, вы можете использовать его, чтобы увидеть, какие шаги были предприняты для выполнения SQL, каждый из которых потребляет несколько раз

Типичная конфигурация сервера

Все следующие конфигурации зависят от фактической операционной среды.

  • max_connections, максимальное количество клиентских подключений

    mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+

  • table_open_cache, кеш дескриптора файла таблицы (данные таблицы хранятся на диске, дескриптор файла кеша диска удобен для открытия файла для чтения данных)

    mysql> show variables like 'table_open_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| table_open_cache | 2000  |
+------------------+-------+

  • key_buffer_size, размер кэша индекса (индекс, прочитанный с диска, кэшируется в памяти, которую можно установить больше, что способствует быстрому поиску)

    mysql> show variables like 'key_buffer_size';
+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| key_buffer_size | 8388608 |
+-----------------+---------+

  • innodb_buffer_pool_size,InnodbРазмер пула кэша механизма хранения (дляInnodbДля наиболее важной конфигурации, если все таблицы используютInnodb, то даже рекомендуется ставить значение 80% физической памяти,InnodbМногие улучшения производительности, такие как индексация, зависят от этого)

    mysql> show variables like 'innodb_buffer_pool_size';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| innodb_buffer_pool_size | 8388608 |
+-------------------------+---------+

  • innodb_file_per_table(innodb, данные таблицы хранятся в.ibdфайл, если элемент конфигурации установлен вON, то таблица соответствуетibdфайл, иначе всеinnodbобщее табличное пространство)

Инструмент измерения давления mysqlslap

Инструмент стресс-тестирования поставляется с установкой MySQL.mysqlslap(родыbinПод содержанием)

Автоматически генерировать sql-тесты
C:\Users\zaw>mysqlslap --auto-generate-sql -uroot -proot
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
        Average number of seconds to run all queries: 1.219 seconds
        Minimum number of seconds to run all queries: 1.219 seconds
        Maximum number of seconds to run all queries: 1.219 seconds
        Number of clients running queries: 1
        Average number of queries per client: 0
Параллельное тестирование
C:\Users\zaw>mysqlslap --auto-generate-sql --concurrency=100 -uroot -proot
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
        Average number of seconds to run all queries: 3.578 seconds
        Minimum number of seconds to run all queries: 3.578 seconds
        Maximum number of seconds to run all queries: 3.578 seconds
        Number of clients running queries: 100
        Average number of queries per client: 0
        
C:\Users\zaw>mysqlslap --auto-generate-sql --concurrency=150 -uroot -proot
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
        Average number of seconds to run all queries: 5.718 seconds
        Minimum number of seconds to run all queries: 5.718 seconds
        Maximum number of seconds to run all queries: 5.718 seconds
        Number of clients running queries: 150
        Average number of queries per client: 0
несколько раундов тестирования
C:\Users\zaw>mysqlslap --auto-generate-sql --concurrency=150 --iterations=10 -uroot -proot
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
        Average number of seconds to run all queries: 5.398 seconds
        Minimum number of seconds to run all queries: 4.313 seconds
        Maximum number of seconds to run all queries: 6.265 seconds
        Number of clients running queries: 150
        Average number of queries per client: 0
тест механизма хранения
C:\Users\zaw>mysqlslap --auto-generate-sql --concurrency=150 --iterations=3 --engine=innodb -uroot -proot
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
        Running for engine innodb
        Average number of seconds to run all queries: 5.911 seconds
        Minimum number of seconds to run all queries: 5.485 seconds
        Maximum number of seconds to run all queries: 6.703 seconds
        Number of clients running queries: 150
        Average number of queries per client: 0

C:\Users\zaw>mysqlslap --auto-generate-sql --concurrency=150 --iterations=3 --engine=myisam -uroot -proot
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
        Running for engine myisam
        Average number of seconds to run all queries: 53.104 seconds
        Minimum number of seconds to run all queries: 46.843 seconds
        Maximum number of seconds to run all queries: 60.781 seconds
        Number of clients running queries: 150
        Average number of queries per client: 0

Ну напишу это первым.Это не просто организовать.Люблю ставить лайки.

оригинал:Краткое изложение из 30 000 слов, суть оптимизации Mysql - Требуется программист


Категории