Во-первых, спецификация команды базы данных
· Все имена объектов базы данных должны содержать строчные буквы, разделенные символом подчеркивания.Во всех именах объектов базы данных запрещено использовать зарезервированные ключевые слова mysql (если имя таблицы содержит ключевое слово запроса, оно должно быть заключено в одинарные кавычки)
· Имя объекта базы данных должно быть в курсе имени и не должно превышать 32 символа в конце
· Временная таблица базы данных должна быть префиксирована и суффиксирована с датой, резервная таблица должна быть префиксирована с Bak_ And Date (Time Tamp) Suffix
Все имена столбцов и типы столбцов, в которых хранятся одни и те же данные, должны быть согласованы (обычно используются как связанные столбцы, если связанные типы столбцов несовместимы во время запроса, тип данных будет автоматически неявно преобразован, что приведет к сбою индекса столбца). , что приводит к снижению эффективности запросов)
Во-вторых, основные технические характеристики базы данных.
1. Все таблицы должны использовать механизм хранения Innodb.
Если нет особых требований (то есть функций, которым Innodb не может соответствовать, таких как хранение столбцов, данных о пространстве хранения и т. д.), все таблицы должны использовать механизм хранения Innodb (Myisam используется по умолчанию до mysql5.5, а Innodb используется по умолчанию после версии 5.6) Innodb поддерживает транзакции, поддерживает блокировку на уровне строк, лучшее восстановление, лучшую производительность при высокой степени параллелизма2. Набор символов базы данных и таблицы использует единообразную кодировку UTF8.
Лучшая совместимость, унифицированный набор символов позволяет избежать искажения символов, вызванного преобразованием набора символов, а необходимость преобразования перед сравнением различных наборов символов приведет к сбою индекса.3. Все таблицы и поля необходимо прокомментировать
Используйте предложения комментариев для добавления комментариев к таблицам и столбцам Ведение словаря данных с самого начала4. Старайтесь контролировать размер объема данных одной таблицы, рекомендуется контролировать его в пределах 5 млн.
5 миллионов — это не предел базы данных MySQL, слишком большое количество встреч вызовет большие проблемы при изменении структуры таблиц, резервном копировании и восстановлении.Объем данных можно контролировать с помощью архивирования исторических данных (применительно к данным журнала), подбазы данных и подтаблицы (применительно к бизнес-данным) и т. д.
5. Осторожно используйте таблицы разделов MySQL
Секционированная таблица физически представлена в виде нескольких файлов, а логически представлена в виде таблицы. Тщательно выбирайте ключ секции, так как эффективность запросов между разделами может быть ниже. Для управления большими данными рекомендуется использовать физическое разделение.6. Попробуйте отделить горячие и холодные данные и уменьшить ширину таблицы
MySQL ограничивает каждую таблицу для хранения до 4096 столбцов, а размер каждой строки данных не может превышать 65535 байт, чтобы уменьшить дисковый ввод-вывод и обеспечить скорость попадания горячих данных в кеш-память (чем шире таблица, тем больше памяти занимает при загрузке данных). таблицы в пул буферов памяти. Чем больше размер, тем больше операций ввода-вывода будет потребляться) Более эффективное использование кеша, избегание чтения бесполезных холодных данных и часто используемых вместе столбцов в таблицу (избегая большего количества ассоциативных операций)7. Запрещено создавать зарезервированные поля в таблице
Название зарезервированных полей трудно определить. Зарезервированные поля не могут подтвердить тип хранимых данных, поэтому невозможно выбрать соответствующий тип. Изменение типа зарезервированного поля приведет к блокировке таблицы8. Запрещено хранить в базе данных большие бинарные данные, такие как изображения и файлы.
Обычно файл очень большой, что приведет к быстрому увеличению объема данных за короткий промежуток времени.Когда база данных читает базу данных, обычно выполняется большое количество случайных операций ввода-вывода.Информация об адресе файла9. Запрещено проводить стресс-тестирование базы данных онлайн
10. Запрещено прямое подключение к базе данных среды генерации из среды разработки и тестовой среды.
Три, спецификации дизайна поля базы данных
1. Отдайте предпочтение наименьшему типу данных, который соответствует потребностям хранения
· причинаЧем больше поле столбца, тем больше места требуется для индексации, поэтому количество узлов индекса, которые можно хранить на странице, также становится все меньше и меньше, и чем больше времени ввода-вывода требуется при обходе. , производительность индекса будет быть хуже
· метод
1) Преобразование строки в числовой тип для хранения, например: преобразование IP-адреса в целочисленные данные.
MySQL предоставляет два метода работы с IP-адресами:
Перед вставкой данных используйте inet_aton для преобразования IP-адреса в целое число, что может сэкономить место. При отображении данных используйте inet_ntoa для преобразования целочисленного IP-адреса в отображаемый адрес.
2) Для неотрицательных данных (таких как самоувеличивающийся идентификатор, целочисленный IP) лучше использовать беззнаковое целое число для хранения
Потому что: неподписанный может удвоить объем памяти по сравнению с подписанным
N в VARCHAR(N) представляет количество символов, а не количество байтов
Используйте UTF8 для хранения 255 китайских символов Varchar(255)=765 байт. Чрезмерная длина будет потреблять больше памяти
2. Избегайте использования типов данных TEXT и BLOB, наиболее распространенный тип TEXT может хранить 64 КБ данных.
· Рекомендуется разделить столбцы BLOB или TEXT в отдельные расширенные таблицы.Временные таблицы Mysql в памяти не поддерживают большие типы данных, такие как TEXT и BLOB.Если запрос содержит такие данные, временные таблицы в памяти нельзя использовать во время сортировки и других операций, но необходимо использовать дисковые временные таблицы.
И для такого рода данных Mysql по-прежнему должен выполнять вторичный запрос, что сильно ухудшит производительность SQL, но это не означает, что такой тип данных нельзя использовать.
Если вы должны использовать его, рекомендуется отделить столбец BLOB или TEXT в отдельную расширенную таблицу.При запросе вы не должны использовать select *, а должны только получать необходимые столбцы.Не запрашивать столбец, когда данные Столбец ТЕКСТ не нужен.
· Типы TEXT или BLOB могут использовать только префиксные индексы
Поскольку MySQL имеет ограничение на длину полей индекса, типы TEXT могут использовать только префиксные индексы, а столбцы TEXT не могут иметь значений по умолчанию.
3. Избегайте использования типов ENUM
· Изменение значения ENUM требует использования инструкции ALTER.· Операция ORDER BY типа ENUM неэффективна и требует дополнительных операций
· Запретить использование числовых значений в качестве значений перечисления для ENUM
4. Определите все столбцы как NOT NULL, насколько это возможно.
причина:· Индексация нулевых колонн требует дополнительного пространства для сохранения, поэтому он занимает больше места;
· Специальная обработка значений NULL при сравнениях и вычислениях
5. Используйте тип TIMESTAMP (4 байта) или DATETIME (8 байтов) для хранения времени.
TIMESTAMP хранит диапазон времени 1970-01-01 00:00:01 ~ 2038-01-19-03:14:07.TIMESTAMP занимает 4 байта и совпадает с INT, но более удобочитаем, чем INT.
Используйте тип DATETIME для хранения за пределами диапазона значений TIMESTAMP.
Часто люди используют строки для хранения данных типа даты (неправильная практика):
Недостаток 1: функции даты нельзя использовать для расчетов и сравнений.
Недостаток 2: хранение дат в виде строк занимает больше места
6. Данные суммы, связанные с финансами, должны использовать десятичный тип.
· Неточная плавающая точка: float, doubleТочность с плавающей запятой: десятичная
Тип Decimal — это точное число с плавающей запятой, которое не теряет точности при вычислениях. Занимаемое пространство определяется заданной шириной, каждые 4 байта могут хранить 9 цифр, а десятичная точка занимает 1 байт. Может использоваться для хранения целочисленных данных большего размера, чем bigint.
В-четвертых, спецификации дизайна индекса
1. Ограничьте количество индексов на каждую таблицу, рекомендуется не более 5 индексов на таблицу
Больше индексов не всегда лучше! Индексы могут повысить эффективность, а могут снизить эффективность.Индексы могут повысить эффективность запросов, но также снизить эффективность вставок и обновлений и даже в некоторых случаях снизить эффективность запросов.
Потому что, когда оптимизатор mysql выбирает, как оптимизировать запрос, он будет оценивать каждый доступный индекс в соответствии с унифицированной информацией для создания наилучшего плана выполнения.Если есть много индексов, которые можно использовать для запроса одновременно, он увеличится. время, затрачиваемое оптимизатором mysql на создание плана выполнения, а также снизит производительность запроса.
2. Запрещено создавать отдельный индекс для каждого столбца в таблице
До версии 5.6 SQL мог использовать только один индекс в таблице, после версии 5.6 хотя и существует метод оптимизации объединения индексов, но до метода запроса с использованием комбинированного индекса все еще далеко.3. Каждая таблица Innodb должна иметь первичный ключ
Innodb — это индексно-организованная таблица: логический порядок хранения данных такой же, как порядок индексов.Каждая таблица может иметь несколько индексов, но порядок хранения таблицы может быть только один.Innodb организует таблицу в соответствии с порядком индекса первичного ключа.
Не используйте часто обновляемые столбцы в качестве первичных ключей и не применяйте многостолбцовые первичные ключи (эквивалентные объединенным индексам) Не используйте UUID, MD5, HASH и строковые столбцы в качестве первичных ключей (последовательный рост данных не может быть гарантирован). ).
Рекомендуется использовать автоматически увеличивающееся значение идентификатора для первичного ключа.
Пять общих рекомендаций по индексным столбцам
· Столбцы, появляющиеся в предложении WHERE операторов SELECT, UPDATE и DELETE.· Поля, включенные в ORDER BY, GROUP BY, DISTINCT
Нет необходимости устанавливать индекс для столбцов, соответствующих полям в 1 и 2. Обычно лучше установить совместный индекс для полей в 1 и 2.
· Связанные столбцы для объединения нескольких таблиц
6. Как выбрать порядок столбцов индекса
Целью создания индекса является поиск данных по индексу, сокращение случайных операций ввода-вывода и повышение производительности запросов.Чем меньше данных может отфильтровать индекс, тем меньше данных считывается с диска.· Наивысшая дискриминация помещается в крайнюю левую часть объединенного индекса (дискриминация = количество различных значений в столбце / общее количество строк в столбце);
Постарайтесь поместить столбец с наименьшей длиной поля в крайнюю левую часть объединенного индекса (поскольку чем меньше длина поля, тем больше объем данных, которые можно хранить на одной странице, и тем выше производительность ввода-вывода);
· Наиболее часто используемые столбцы размещаются на левой стороне индекса соединения (так что меньше индексов можно построить).
7. Избегайте создания избыточных индексов и дублирующихся индексов
Потому что это увеличит время, необходимое оптимизатору запросов для создания плана выполнения.· Примеры повторяющихся индексов: первичный ключ(идентификатор), индекс(идентификатор), уникальный индекс(идентификатор)
Примеры избыточных индексов: index(a,b,c), index(a,b), index(a)
8. Отдайте предпочтение индексам покрытия
Для частых запросов предпочтительнее использовать покрывающий индекс.Покрывающий индекс: индекс, который включает все поля запроса (где, выбрать, упорядочить по полям и сгруппировать по полям).
Преимущества покрывающих индексов:
· Избегайте вторичных запросов к таблице Innodb для индексации
Innodb хранится в порядке кластеризованного индекса.Для Innodb вторичный индекс хранит информацию о первичном ключе строки в конечном узле.
Если для запроса данных используется вторичный индекс, после нахождения соответствующего значения ключа необходимо выполнить вторичный запрос через первичный ключ, чтобы получить действительно нужные нам данные. В покрывающем индексе все данные могут быть получены из значения ключа вторичного индекса, что позволяет избежать вторичного запроса первичного ключа, сокращает операцию ввода-вывода и повышает эффективность запроса.
Может превратить случайный ввод-вывод в последовательный ввод-вывод для повышения эффективности запросов.
Поскольку покрывающий индекс хранится в порядке следования значений ключа, для поиска по диапазонам с интенсивным вводом-выводом объем операций ввода-вывода намного меньше, чем случайное чтение данных каждой строки с диска.Операция чтения превращается в последовательный ввод-вывод для поиска по индексу.
Девять, спецификация индекса SET
Старайтесь избегать ограничений внешнего ключа· Не рекомендуется использовать ограничения внешнего ключа (внешний ключ), но обязательно строить индексы по связанным ключам между таблицами;
· Внешние ключи могут использоваться для обеспечения ссылочной целостности данных, но рекомендуется реализовать это на стороне бизнеса;
· Внешние ключи могут влиять на операции записи в родительские и дочерние таблицы и снижать производительность.
Десять, спецификации разработки базы данных SQL
1, рекомендуется использовать подготовленное утверждение для операций базы данных
Предкомснициальные заявления могут повторно использовать план, уменьшая время, необходимое для компиляции SQL, динамический SQL может также решить проблему, вызванную впрыском SQL, пропускать только параметры пропускания, пропускать операторы SQL и более эффективным, чем одно и то же утверждение, используя один раз, используйте много раз, улучшают обработку эффективность.2. Избегайте неявного преобразования типов данных
Неявные преобразования сделают индекс недействительным. Например: выберите имя, телефон от клиента, где id = '111';3. В полной мере используйте существующие индексы в таблице.
· Избегайте условий запроса с двойными знаками %.Например, как «% 123%» (если нет начального %, только конечный % может использоваться для индекса в столбце)
· Можно использовать только SQL для выполнения композитного индекса запроса диапазона
Например, если есть объединенный индекс столбцов a, b и c, а в условии запроса есть запрос диапазона столбца a, индекс столбцов b и c не будет использоваться. a справа от индекса объединения.
Используйте левое соединение или не существует, чтобы оптимизировать не в работе
Поскольку not in также обычно использует инвалидацию индекса.
4. При проектировании базы данных следует учитывать возможность расширения в будущем.
5. Программа подключается к разным базам данных и использует разные учетные записи для выполнения запросов между базами данных.
· Оставьте место для переноса базы данных и подтаблиц подбазы данных· Уменьшить деловую связь
· Избегайте угроз безопасности, вызванных чрезмерными разрешениями
6. Запретить использование SELECT * необходимо использовать запрос SELECT
причина:· Потребляйте больше ЦП и операций ввода-вывода для ресурсов пропускной способности сети
· Покрывающие индексы не могут быть использованы
· Уменьшить влияние изменений структуры таблицы
7. Запретить использование операторов INSERT без списка полей.
Такие как: вставка в значения ('a','b','c');следует использовать вставку в значения t(c1,c2,c3) ('a','b','c');
8. Избегайте использования подзапросов, вы можете оптимизировать подзапросы для операций соединения.
Обычно, когда подзапрос находится в предложении in, а подзапрос представляет собой простой SQL (за исключением предложений объединения, группировки, упорядочивания и ограничения), подзапрос можно преобразовать в связанный запрос для оптимизации.Причины низкой производительности подзапросов:
· Набор результатов подзапроса не может использовать индекс. Обычно набор результатов подзапроса хранится во временной таблице. Во временной таблице в памяти или на диске нет индекса, поэтому это повлияет на производительность запроса в некоторой степени;
· Особенно для подзапросов, которые возвращают большой набор результатов, тем больше воздействие на производительность запроса;
· Поскольку подзапрос будет генерировать большое количество временных таблиц и не будет индексировать, он будет потреблять слишком много ресурсов ЦП и ввода-вывода, что приведет к большому количеству медленных запросов.
9. Избегайте использования JOIN для связывания слишком большого количества таблиц
Для Mysql существует связанный кеш, и размер кеша можно задать параметром join_buffer_size.В Mysql для того, чтобы один и тот же SQL мог присоединиться к более чем одной таблице, будет выделен еще один ассоциативный кеш.Если в одном SQL связано больше таблиц, занимаемая память будет больше.
Если в программе используется большое количество операций многотабличных ассоциаций, а настройка join_buffer_size является необоснованной, легко вызвать переполнение памяти сервера, что повлияет на стабильность работы базы данных сервера.
В то же время для операции ассоциации будет сгенерирована операция временной таблицы, что влияет на эффективность запроса.Mysql позволяет связать максимум 61 таблицу, и рекомендуется не превышать 5.
10. Сократите количество взаимодействий с базой данных
База данных больше подходит для обработки пакетных операций. Объединение нескольких одинаковых операций вместе может повысить эффективность обработки.11. При вынесении или суждения, соответствующего тому же столбцу, используйте in вместо или
Значение in не должно превышать 500 в операциях, чтобы использовать индекс более эффективно, или редко использовать индекс в большинстве случаев.12. Запрещено использовать порядок по rand() для случайной сортировки
Он загрузит все подходящие данные в таблице в память, а затем отсортирует все данные в памяти в соответствии со случайно сгенерированными значениями и может сгенерировать случайное значение для каждой строки, если набор данных, который соответствует условиям, очень большой, он будет потреблять много ресурсов процессора, ввода-вывода и памяти.Рекомендуется получить случайное значение в программе, а затем получить данные из базы данных
13. Преобразование функций и вычисление столбцов запрещено в предложении WHERE
Индексы нельзя использовать, когда для столбца выполняются функциональные преобразования или вычисления.· Не рекомендуется:
· рекомендовать:
14. Используйте UNION ALL вместо UNION, когда ясно, что повторяющихся значений не будет.
· UNION поместит все данные двух результирующих наборов во временную таблицу, а затем выполнит операцию дедупликации.· Союз все не будет результатом, установленным на повторное управление
15. Разделите сложный большой SQL на несколько маленьких SQL
· Большой SQL: SQL, который является логически сложным и требует много ресурсов ЦП для вычислений.MySQL: один SQL может использовать только один процессор для вычислений
· Разделение SQL может выполняться параллельно для повышения эффективности обработки
11. Кодекс поведения при работе с базой данных
1. Операции пакетной записи (UPDATE, DELETE, INSERT) более 1 миллиона строк должны выполняться пакетами несколько раз.
Большие партии операций могут вызвать серьезную задержку мастера-рабаСреда ведущий-ведомый, операции с большими объемами могут вызвать серьезную задержку ведущий-ведомый, записи больших объемов обычно требуются для выполнения определенного периода времени, и только после завершения реализации основной библиотеки будут выполняться из библиотеки в другой, поэтому это вызовет задержки из основной библиотеки и библиотеки долго
· Создается большое количество журналов, когда журналы binlog имеют формат строки.
Большие пакеты операций записи будут генерировать много журналов, особенно для двоичных данных в формате строки.Поскольку изменение каждой строки данных записывается в формате строки, чем больше данных мы изменяем за один раз, тем больше журналов будет создано. Чем больше времени требуется для передачи и восстановления журнала, что является одной из причин задержки между ведущим и подчиненным.
· Избегайте операций с крупными транзакциями
Изменение данных в больших пакетах должно выполняться в транзакции, что приведет к блокировке большого количества данных в таблице, что приведет к большому количеству блокировок, что окажет большое влияние на производительность MySQL.
В частности, долгосрочная блокировка займет все доступные подключения к базе данных, что сделает другие приложения в производственной среде неспособными подключиться к базе данных, поэтому мы должны обратить внимание на пакетную обработку больших операций пакетной записи.
2. Для больших таблиц используйте модифицированную таблицу изменить таблицу PT-Online-Schema.
Избегайте значительных модификаций основной таблицы из-за задержек· Избегайте блокировки таблицы при изменении полей таблицы
Изменение структуры данных большой таблицы должно быть осторожным, так как это приведет к серьезным операциям блокировки таблицы, особенно в производственной среде, что недопустимо.
pt-online-schema-change Сначала создаст новую таблицу с той же структурой, что и исходная таблица, и изменит структуру таблицы в новой таблице, а затем скопирует данные из исходной таблицы в новую таблицу, а в исходная таблица Добавьте несколько триггеров.
Скопируйте недавно добавленные данные из исходной таблицы в новую таблицу.После копирования всех данных в строке назовите новую таблицу исходной таблицей и удалите исходную таблицу.
Разложите исходную операцию DDL на несколько небольших пакетов.