[Не ложиться спать допоздна] Спецификация базы данных, вы должны использовать ее

задняя часть база данных

Эта статья участвовала в мероприятии Haowen Convocation Order,Нажмите, чтобы просмотреть: Заявки на бэк-энд и фронт-энд двойные, призовой фонд в 20 000 юаней ждет вас, чтобы бросить вызов! "

[Не ложиться спать допоздна] Я считаю, что большинство студентов очень хорошо знают эти положения и условия. Я так думал раньше, но когда я написал это, я обнаружил, что есть некоторые моменты, которые я раньше не понимал глубоко, например, покрытие индексы, предварительная компиляция, драйверы mysql и т. д. Блоки и эти форматы строковых записей, COLLATE, заполнены.

  1. Соглашение об именовании баз данных

    Используйте строчные буквы, цифры (обычно не нужны) и символы подчеркивания. Использование «-» запрещено, а названия лаконичны и понятны.

  2. именование таблиц

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

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

    Рекомендуемое соглашение об именах

    тип префикс инструкция
    деловой стол tb_
    Реляционные таблицы tr_
    таблица истории th_
    Статистика ts_
    таблица журналов tl_xx_log
    Системная таблица, словарная таблица, кодовая таблица sys_
    Временные таблицы tmp_ Запрещено использовать
    резервная таблица bak_xx_ymd
    Посмотреть view_ избегать использования

  1. двигатель

    Используйте движок Innodb по умолчанию (по умолчанию после 5.5)

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

  2. набор символов --Обнажи меч и возьми Артемизию.👥👥👥👥

    • Наборы символов баз данных и таблиц унифицированы, максимально используется UTF8 (согласно бизнес-требованиям).

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

    • Ассоциация полей UTF8 и UTF8MB4 приведет к сбою индекса.

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

    • Без особых требований запрещается указывать таблицу COLLATE -----

      Основная функция COLLATE — правила сортировки и поиска. Набор символов utf8 по умолчанию — utf8_general_ci, а набор символов utf8mb4 — utf8mb4_general_ci. Буква ci в конце означает нечувствительность к регистру.

      COLLATE повлияет на порядок оператора ORDER BY, повлияет на результаты, отфильтрованные по знаку больше или меньше в условии WHERE, и повлияетDISTINCT,GROUP BY,HAVINGРезультат запроса оператора. Например: выберите * из теста, где имя похоже на «A%», в наборе символов utf8_bin поле «abc» не может быть получено, а порядок Abc и abc несовместим в случае сортировки.

    • Тщательно выбирайте row_format

      Барракуда: новый формат файла. Он поддерживает все форматы строк InnoDB, включая новые форматы строк:COMPRESSEDиDYNAMIC

      В msyql 5.7.9 и более поздних версиях формат строки по умолчанию определяется переменной innodb_default_row_format, значением по умолчанию которой являетсяDYNAMIC

      Формат innodb_file_format по умолчанию для db — Barracuda, а формат innodb_default_row_format по умолчанию — динамический; среди них максимальный коэффициент сжатия COMPRESSED равен 1/2, но будут дополнительные накладные расходы ЦП на чтение и запись, а память приложения применяется в соответствии с исходный размер после распаковки.В ситуациях с высокой степенью параллелизма легко вызвать проблемы с производительностью.

      Динамический формат строки, независимо от того, размещено ли хранилище столбцов на странице вне страницы, в основном зависит от размера строки, он поместит самый длинный столбец в строку на странице вне страницы, пока страница данных не сможет хранить следующие две строки. Столбцы TEXT или BLOB всегда существуют на страницах данных, если

      Физическая структура Compressed похожа на Dynamic.Другая функция формата записи строки Compressed заключается в том, что данные строки, хранящиеся в нем, будут сжаты алгоритмом zlib, поэтому данные большой длины, такие как BLOB, TEXT и VARCHAR, могут быть эффективно хранится (уменьшено на 40%, но больше загружает ЦП).

  3. Полевой дизайн --Ощущение жизни, кто знаменит и кто знаменит👍👍👍

    • Все таблицы и поля должны быть прокомментированы, используйте предложение comment для добавления комментариев к таблицам и столбцам Обслуживание словаря данных выполняется с самого начала

    • Старайтесь контролировать размер объема данных одной таблицы, рекомендуется контролировать его в пределах 5 млн.

      5 миллионов — это не предел базы данных MySQL.Чрезмерное количество совещаний вызовет большие проблемы при изменении структуры таблицы, резервном копировании и восстановлении.Архивирование исторических данных (применяется к данным журнала), подбаза данных и подтаблица (применяется к бизнес-данным ) и другие методы могут быть использованы для управления объемом данных

    • Осторожно используйте секционированные таблицы MySQL

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

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

    • MySQL ограничивает каждую таблицу для хранения до 4096 столбцов, а размер каждой строки данных не может превышать 65535 байт. Чтобы уменьшить дисковый ввод-вывод, обеспечьте частоту попаданий горячих данных в кеш-память (чем шире таблица, тем больше памяти занимает при загрузке таблицы в пул буферов памяти и тем больше будет потребляться операций ввода-вывода), а кеш-память может Избегайте чтения бесполезных холодных данных и помещайте столбцы, которые часто используются вместе, в одну таблицу (избегайте большего количества связанных операций). Для нестандартных полей рекомендуется разделить таблицу, расширив таблицу.

      Примечание. В 65535 байтах каждой строки данных в наборе символов utf8 каждая длина varchar занимает 3 байта, а в наборе символов utf8mb4 каждая длина занимает 4 байта.

    • Старайтесь не создавать зарезервированные поля в таблице

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

    • Ограничения внешнего ключа запрещены

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

  4. Спецификация дизайна поля базы данных ---Будьте осторожны, чтобы не быть персиком и сливой🏆🏆🏆🏆

    • О длине данных

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

    • Запрещено использовать тип TEXT/BLOB, а также запрещено хранить в базе данных большие двоичные данные, такие как изображения и файлы.

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

    • Избегайте использования типов ENUM (перечисление)

      Модификация значения ENUM требует использования оператора ALTER; операция ORDER BY типа ENUM неэффективна и требует дополнительных операций; запрещено использовать числовые значения в качестве значения перечисления ENUM

    • По возможности определяйте все столбцы как NOT NULL.

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

      Значения NULL следует обрабатывать специально при выполнении сравнений и расчетов.

      NULL может использовать только IS NULL или IS NOT NULL, и когда =/!=/in/not in, результат запроса легко может быть несовместим с логикой проекта.

    • Используйте тип TIMESTAMP (4 байта) или DATETIME (5 байтов) для хранения времени.

      Во многих блогах в Интернете написано, что DATETIME составляет 8 байт, на самом деле в версии 5.6.4 он был уменьшен до 5 байт.

      адрес github исходного кода mysql

      longlong TIME_to_longlong_datetime_packed(const MYSQL_TIME &my_time) {
 longlong ymd = ((my_time.year * 13 + my_time.month) << 5) | my_time.day;
 longlong hms = (my_time.hour << 12) | (my_time.minute << 6) | my_time.second;
 longlong tmp = my_packed_time_make(((ymd << 17) | hms), my_time.second_part);
 assert(!check_datetime_range(my_time)); /* Make sure no overflow */
 return my_time.neg ? -tmp : tmp;
}
根据上述算法，计算极限时间 9999-12-31 23:59:59
       时间各部分依次是 year-month-day hour:minute:second

1. 计算 longlong ymd
   year*13 + month = 9999*13 + 12 = 129999
   将 129999 左移 5 位，再与 31 进行或运算
       ‬0000 0000 0011 1111 0111 1001 111[0 0000]   --- 129999 左移 5 位 （年*13 + 月）
       0000 0000 0000 0000 0000 0000 ‭0001 1111‬     ---  31 （日）
     = ‬0000 0000 0011 1111 0111 1001 1111 1111     ---  得出 longlong ymd 低位，极限有         22 位
    
2. 计算 longlong hms
    将 hour 左移 12 位，与 minute 左移 6 位，再与 second 进行或运算
    0001 0111 [0000 0000 0000]   ---   23 左移 12 位 （时）
              1110 11‬[00 0000]   ---   59 左移 6 位 （分）
                      11 1011    ---   59 （秒）
   = 0001 0111 1110 1111 1011    ---   得出 longlong hms 的低位，极限有 17 位

3. 计算 longlong tmp
     ymd 右移 17 位，与 hms 进行或运算，这样刚好存到 39 位。（至此，再加上 1 位标识位，也           就刚好 40 位，为 5 字节了）
     再使用 my_packed_time_make(）函数，将 ymd 与 小数秒部分 连起来。

      Диапазон времени хранения TIMESTAMP: 1970-01-01 00:00:01 ~ 2038-01-19-03:14:07.

      TIMESTAMP занимает 4 байта и совпадает с INT, но более удобочитаем, чем INT.

      Используйте тип DATETIME для хранения за пределами диапазона значений TIMESTAMP.

    • Данные суммы, связанные с финансами {дизайн для использования десятичных знаков}, должны использовать десятичный тип

      Тип Decimal — это точное число с плавающей запятой, которое не теряет точности при вычислениях.

    • Определения полей с одинаковым значением должны быть одинаковыми

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

    • Запрещено указывать after при добавлении полей

    • VARCHAR(N), N как можно меньше

      Если N=256, для хранения длины будут использоваться два байта.

    • Числовое поле, значение по умолчанию рекомендуется 0

  5. Спецификация проекта индекса ---Общее обязательство❤❤❤❤

    • Создаваемая таблица должна иметь первичный ключ (PRIMARY KEY), рекомендуется использовать Snowflake или Pear Flower.

    • Не используйте столбцы UUID, MD5, HASH, string в качестве первичных ключей (рост порядка данных не гарантируется).

    • Ограничьте количество индексов в каждой таблице

      Больше индексов не всегда лучше! Индексы могут повысить эффективность, а могут снизить эффективность. Индексы могут повысить эффективность запросов, но также снизить эффективность вставок и обновлений и даже в некоторых случаях снизить эффективность запросов. Потому что, когда оптимизатор mysql выбирает, как оптимизировать запрос, он будет оценивать каждый доступный индекс в соответствии с унифицированной информацией для создания наилучшего плана выполнения.Если есть много индексов, которые можно использовать для запроса одновременно, будетУвеличьте время, необходимое оптимизатору mysql для создания плана выполнения., что также снижает производительность запросов.

    • Наивысшая степень дискриминации помещается в крайний левый угол объединенного индекса (степень дискриминации = количество различных значений в столбце / общее количество строк в столбце);

    • Постарайтесь поместить столбец с наименьшей длиной поля в крайнюю левую часть объединенного индекса (поскольку чем меньше длина поля, тем больше объем данных, которые можно хранить на одной странице, и тем выше производительность ввода-вывода);

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

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

      Пример повторяющегося индекса: первичный ключ (идентификатор), индекс (идентификатор), уникальный индекс (идентификатор)

      Примеры избыточных индексов: index(a,b,c), index(a,b), index(a)

    • Приоритет индексов покрытия

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

      Преимущества покрывающего индекса: 1. Он может превратить случайный ввод-вывод в последовательный ввод-вывод для повышения эффективности запросов 2. Он может избежать запросов обратно к таблице и повысить эффективность запросов.

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

  6. планирование разработки sql ---Луна не меняет света, меч не меняет жесткости❤️❤️❤️❤️

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

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

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

      Так же как знакомый #{} прекомпилируется, это безопасно; ${} не прекомпилируется, просто принимает значение переменной, это не безопасно, есть SQL-инъекция

      Драйвер MySQL поддерживает два условия для предварительной компиляции сервера.Кэш с использованием стратегии LRU на основе LinkedHashMap, соответственно, serverSideStatementCheckCache используется для кэширования того, могут ли операторы SQL кэшироваться сервером, а serverSideStatementCache используется для кэширования предварительно скомпилированных операторов SQL на стороне сервера. Размер этих двух кэшей определяетсяprepStmtCacheSizeконтроль параметров.

    • Избегайте неявных преобразований типов данных

      Неявные преобразования сделают индекс недействительным. Например: выберите имя, телефон от клиента, где id = '111';

    • Полное использование существующих индексов в таблице

    • Избегайте условий запроса с двойным знаком %

      Например, "%123%" (если нет начального %, для индекса столбца можно использовать только конечный %).

    • SQL может использовать только один столбец в составном индексе для запроса диапазона.

      Например, если есть объединенный индекс столбцов a, b и c, а в условии запроса есть запрос диапазона столбца a, индекс столбцов b и c не будет использоваться. a справа от индекса объединения.

    • Преобразования функций и вычисления в столбцах запрещены в предложении WHERE.

      Не рекомендуется: где date(create_time)=20190101

      Рекомендуется: где create_time >= 20190101 и create_time

    • Используйте, когда ясно, что повторяющихся значений не будет.UNION ALLвместо СОЮЗА

      UNION поместит все данные двух наборов результатов во временную таблицу, а затем выполнит операции дедупликации и сортировки.

      UNION ALL больше не будет выполнять операции дедупликации и сортировки результирующего набора.

    • Разделить сложный большой SQL на несколько маленьких SQL

    • Цель оптимизации производительности SQL: как минимум достичь уровня диапазона, требование — уровень ссылки, и лучше всего, если он может быть константным.

    • Не используйте count(имя столбца) или count(константа) вместо count(), считать() — это стандартный синтаксис для подсчета строк, определенный в SQL92, который не имеет ничего общего с базой данных и не имеет ничего общего с NULL и не-NULL.

Своевременное поощрение, время никого не ждет

Люди, которые могут видеть здесь, все Элита. ❤❤️❤️❤️❤

Большое спасибо Элиты могут увидеть это здесь, если эта статья хорошо написана, если вы думаете, что есть что-то, пожалуйста, ставьте лайк 👍 подписывайтесь ❤️, пожалуйста, поделитесь 👥 Это действительно очень полезно для теплого человека! ! !

Если в этом блоге есть какие-либо ошибки, пожалуйста, критикуйте и советуйте, это очень ценится!

Категории