Подробная расшифровка MySQL в мануале Али

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

Его статутная сила делится на три категории от сильной до слабой: обязательная, рекомендуемая и справочная.

В этой статье подробно интерпретируется часть руководства Ali, посвященная MySQL.

Эта статья разделена на четыре части:

протокол построения таблицы
снижение индекса
Оператор SQL
ORM-отображение

1 Протокол построения таблицы

[Обязательно] Поля, выражающие понятие «да» или «нет», должны быть названы в форме is_xxx, а тип данных должен быть беззнаковым tinyint (1 означает «да», 0 означает «нет»).

Описание: Если какое-либо поле не является отрицательным, оно должно быть беззнаковым.
Примечание. Не добавляйте префикс is к какой-либо логической переменной в классе POJO, поэтому вам необходимо установить отношение сопоставления с is_xxx на Xxx. База данных представляет значение «да» или «нет», используя тип tinyint и настаивая на методе именования is_xxx, чтобы уточнить значение его значения и диапазон значений.
Положительный пример: имя поля is_deleted, выражающее надгробную плиту, 1 означает удалено, 0 означает не удалено.
Интерпретация: с точки зрения оптимизации соответствующий тип должен быть определен в соответствии с назначением поля. Чтобы выразить да или нет, достаточно tinyint длиной 1 байт.

[Обязательно] В имени таблицы и имени поля должны использоваться строчные буквы или цифры, причем начало числа запрещено, а также запрещены только числа между двумя символами подчеркивания. Модификация имен полей базы данных стоит дорого, поскольку предварительный выпуск не может быть выполнен, поэтому имена полей необходимо тщательно продумать.

Примечание: MySQL нечувствителен к регистру в Windows, но по умолчанию чувствителен к регистру в Linux. Поэтому имена баз данных, имена таблиц и имена полей не могут быть написаны заглавными буквами, чтобы избежать посторонних проблем.
Положительный пример: aliyun_admin, rdc_config, level3_name Пример встречный: AliyunAdmin, rdcConfig, level_3_name
Интерпретация: Разработано в среде Win, имя таблицы, используемое в коде, написано строчными буквами, а локальная база данных — прописными буквами, поэтому в среде win проблем нет, но в среде linux возникнут проблемы. После установки MySQL в Linux по умолчанию: имена таблиц с учетом регистра, а не имена столбцов с учетом регистра.

Имя базы данных MySQL, имя таблицы, имя столбца, правила регистра псевдонимов в Linux:

(1) Имя базы данных и имя таблицы строго чувствительны к регистру.

(2) Псевдоним таблицы строго чувствителен к регистру.

(3) Имена столбцов и псевдонимы столбцов нечувствительны к регистру во всех случаях.

(4) Имена переменных также строго чувствительны к регистру.

[Обязательно] В именах таблиц не используются существительные во множественном числе.

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

[Обязательно] Отключите зарезервированные слова, такие как desc, range, match, delayed и т. д. Пожалуйста, обратитесь к официальным зарезервированным словам MySQL.

[Обязательный] Имя индекса первичного ключа — имя pk_field, имя уникального индекса — имя uk_field, имя общего индекса — имя idx_field.

Описание: pk_ — первичный ключ, uk_ — уникальный ключ, idx_ — сокращение от index.

[Обязательный] Тип decimal — десятичный, использование float и double запрещено.

Объяснение: При сохранении значений float и double возникает проблема потери точности, и при сравнении значений можно получить неверные результаты. Если диапазон хранимых данных превышает диапазон десятичных знаков, рекомендуется разбивать данные на целые и десятичные числа и хранить их отдельно.
Интерпретация: float и double — это типы с плавающей запятой, а decimal — это тип с фиксированной запятой. Типы MySQL с плавающей запятой и фиксированной запятой могут быть представлены добавлением (M, D) к имени типа, где M представляет общую длину значения, а D представляет длину после десятичной точки.

Когда FLOAT и DOUBLE не указывают точность, они будут отображаться в соответствии с фактической точностью по умолчанию, а когда DECIMAL не указывает точность, целое число по умолчанию равно 10, а десятичное число равно 0. Поэтому рекомендуется определить (M, D) при определении таблицы.

Когда значения float и double превышают заданную длину, они будут автоматически округлены, decimal усечено, и будет выдано предупреждение.

Проблема потери точности: столбцы типа float и double будут терять точность при вычислении суммы, а decimal будет вычисляться точно.

[Обязательно] Если сохраненные строки почти одинаковой длины, используйте тип строки фиксированной длины char.

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

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

Интерпретация: В MySQL 5.0 и выше varchar может хранить максимум 65535 байт данных (1-2 байта используются для хранения информации о длине в начале содержимого, и два байта используются, когда она превышает 255, поэтому максимальное 65535).

Обычно мы устанавливаем кодировку U8, каждый символ занимает не более 3 байт, поэтому максимальная длина не может превышать 21845.

Если указанный выше предел превышен при определении, поле varchar будет принудительно преобразовано в текстовый тип, и будет сгенерировано предупреждение.

Кроме того, из-за ограничения длины строки MYSQL MySQL требует, чтобы определенная длина строки не превышала 65535. Если заданная длина таблицы превышает это значение, появится сообщение об ОШИБКЕ 1118 (42000): слишком большой размер строки.

varchar(20), определенный в базе данных, относится к 20 символам.

Предложение по поводу 5000: поскольку U8 обычно определяется как 3 байта на символ, для 5000 символов требуется 15000 байт.С учетом максимального ограничения длины строки и других столбцов, а также производительности запроса рекомендуется 5000.

[Обязательно] В таблице должно быть три поля: id, gmt_create, gmt_modified.

Описание: id должен быть первичным ключом, тип bigint unsigned, отдельная таблица автоматически увеличивается, а размер шага равен 1. Типы gmt_create и gmt_modified являются типами даты и времени, первый означает активное создание в настоящем времени, а второй означает пассивное обновление в причастии прошедшего времени.

[Рекомендация] В название таблицы лучше всего добавить "название компании_роль таблицы".

Положительный пример: alipay_task/force_project/trade_config

[Рекомендуется] Имя библиотеки и имя приложения должны максимально соответствовать друг другу.

[Рекомендуется] Если вы измените значение поля или добавите состояние, указанное полем, вам необходимо вовремя обновить комментарий к полю.

[Рекомендуется] Поля допускают соответствующую избыточность для повышения производительности запросов, но необходимо учитывать согласованность данных. Должны следовать избыточные поля:

1) Поля, которые не часто изменяются.

2) Это не сверхдлинное поле varchar, не говоря уже о текстовом поле.

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

[Рекомендуется] Сегментирование базы данных рекомендуется только в том случае, если количество строк в одной таблице превышает 5 миллионов строк или емкость одной таблицы превышает 2 ГБ.

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

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

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

тип	возраст	Тип поля	длина поля	Диапазоны
люди	в течение 150 лет	tinyint unsigned	1	Беззнаковое значение: 0~255

2 Соглашения об индексах

[Обязательно] Поле с уникальными характеристиками в бизнесе, даже если оно представляет собой комбинацию нескольких полей, должно быть встроено в уникальный индекс.

Примечание: Не думайте, что уникальный индекс влияет на скорость вставки. Эту потерю скорости можно игнорировать, но очевидно, что она улучшит скорость поиска. Кроме того, даже если на прикладном уровне осуществляется очень полный контроль проверки, пока поскольку уникального индекса нет, по закону Мерфи должны генерироваться грязные данные.
Толкование: Закон Мерфи: если есть шанс, что что-то пойдет не так, как бы мал он ни был, это произойдет.

[Обязательно] Регистрация запрещена для более чем трех столов. Типы данных полей, которые необходимо объединить, должны быть абсолютно согласованными; при запросе нескольких таблиц убедитесь, что связанные поля должны иметь индексы.

Примечание. Обратите внимание на индексы таблиц и производительность SQL даже при объединении двойных таблиц.

[Обязательно] При создании индекса для поля типа varchar необходимо указать длину индекса.Необязательно создавать индекс для всего поля.Длину индекса можно определить в соответствии с фактической дискриминацией текста.

Примечание: длина индекса и степень различения являются парой противоречий.Как правило, для данных строкового типа индекс длиной 20 имеет степень различения более 90 %.Вы можете использовать count(distant left (имя столбца, длина индекса))/ Определяется дискриминация count(*).
Интерпретация: Степень дискриминации относится к отношению значения уникального индекса к общему количеству записей в таблице данных в диапазоне (0,1). Чем выше значение, тем выше эффективность запроса.

Для столбцов blob, text, varchar необходимо использовать индекс префикса, MySQL не позволяет индексировать полную длину этих столбцов.

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

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

Описание: Индексный файл имеет функцию сопоставления крайнего левого префикса B-Tree, если левое значение не определено, то индекс нельзя использовать.

[Рекомендация] Если есть порядок по сценариям, обратите внимание на упорядоченность индекса. Последнее поле порядка является частью составного индекса и помещается в конец составного индекса, чтобы избежать сортировки файлов и повлиять на производительность запроса.

Положительный пример: где a=? и b=?, упорядоченные по c; индекс: a_b_c
Пример счетчика: если в индексе есть поиск по диапазону, нельзя использовать порядок индекса, например: WHERE a>10 ORDER BY b; Индекс a_b не может быть отсортирован.
Интерпретация: file_sort — это сортировка файлов, которая происходит во время сортировки.Если индекс не используется во время сортировки, будет сгенерирован file_sort. Порядок индекса можно использовать только в том случае, если порядок столбцов индекса точно такой же, как в предложении order by, и направление упорядочения всех столбцов одинаково. Если запросу необходимо связать несколько таблиц, порядок индекса можно использовать только в том случае, если все поля, на которые ссылается предложение order by, относятся к первой таблице.

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

Пояснение: Если книге нужно знать название главы 11, перевернется ли она на страницу, соответствующую главе 11? Просто просмотрите каталог, этот каталог должен играть роль охватывающего индекса.
Положительный пример: существует три типа индексов, которые могут быть созданы: индекс первичного ключа, уникальный индекс и общий индекс, а покрывающий индекс является только результатом запроса.В результате объяснения появится дополнительный столбец: использование индекса .
Интерпретация: если индекс содержит значения всех полей, которые необходимо запросить, он называется «покрывающим индексом».

Поскольку покрывающий индекс должен хранить значение индексированного столбца, хэш-индекс, пространственный индекс и полнотекстовый индекс не хранят значение столбца, в качестве покрывающего индекса можно использовать только индекс B-Tree MySQL. Например: создайте индекс по трем полям id, name и title, и значения этих трех столбцов будут храниться в индексе.Если вы запросите: выберите id, name, title из таблицы, где id

Если запрос select * из таблицы, где id

[Рекомендуется] Используйте отложенные ассоциации или подзапросы для оптимизации сценариев многостраничного пейджинга.

Примечание. Предельный запрос MySQL не пропускает строку смещения, а берет строку смещения + N, затем возвращает предыдущую строку смещения и возвращает строку N, затем, когда смещение особенно велико, эффективность очень низкая, или контролируйте возвращаемую сумму. количество страниц или переписать SQL для страниц, которые превышают определенный порог.
Интерпретация: Дело

select count(*) from user_game_info; // всего 956176 фрагментов данных

выберите * из user_game_info лимит 900000, 20; // Этот запрос занимает 0,547 с

выберите t1.* из user_game_info t1, (выберите id из user_game_info limit 900000, 20) t2, где t1.id = t2.id // время 0,178 с после оптимизации

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

инструкция:

1) В одиночной таблице consts существует не более одной соответствующей строки (первичный ключ или уникальный индекс), и данные могут быть прочитаны на этапе оптимизации.

2) ref относится к использованию нормального индекса.

3) range выполняет поиск диапазона по индексу.

Пример счетчика: результат таблицы объяснения, тип=индекс, физический файл индекса просматривается полностью, скорость очень низкая, уровень индекса ниже диапазона, что незначительно по сравнению с полным сканированием таблицы.
Интерпретация: это поле типа (тип соединения) в объяснении.

Несколько распространенных типов: all, index, range, ref, eq_ref, const, и эффективность увеличивается последовательно слева направо.

1) Все: полное сканирование таблицы

2) Индекс: сначала просканируйте все индексы, а затем вернитесь к таблице для получения данных, производительность не лучше, чем у всех

3) Диапазон: сканирование индекса с диапазоном между/и/>/в/или может быть запущено

4) Ссылка: столбец условия поиска использует индекс и не является первичным ключом и уникальным.

5) Eq_ref: оптимизатор знает, что есть только один результат запроса, который срабатывает при использовании первичного ключа или уникального индекса.

6) Const: поставить первичный ключ позади, где сделать эквивалентный запрос, например: select * from user_game_info, где id = 100;

[Рекомендация] При построении составного индекса тот, у которого наивысшая степень дискриминации, находится в крайнем левом углу.

Положительный пример: Если where a=? и b=? , если столбец a почти близок к уникальному значению, то нужно построить только индекс idx_a.
Примечание. При наличии сочетания неравных знаков и знаков равенства, пожалуйста, добавьте столбцы условия знака равенства при создании индекса. Например:где с>?иd=?Тогда даже если с имеет более высокую степень дискриминации, d нужно поместить в первую колонку индекса, то есть индекс idx_d_c.

[Рекомендуется] Предотвратить неявное преобразование, вызванное разными типами полей, которое приведет к сбою индекса.

Интерпретация: например, добавьте индекс к полю имени пользователя (varchar) таблицы tb_user_account, упомянутой выше. Поскольку поле имеет тип varchar, тип запроса на приведенном выше рисунке совпадает и соответствует индексу. Рисунок ниже соответствует тип int, который не может быть индексом попадания.

Найти как строку, указать индекс

Индекс пропущен из-за неявного преобразования

Неявные правила преобразования:

Если хотя бы один из двух параметров имеет значение NULL, результатом сравнения также является NULL. Исключением является то, что использование для сравнения двух значений NULL возвращает 1. В обоих случаях преобразование типов не требуется.
Оба параметра являются строками и будут сравниваться по строкам без преобразования типов.
Оба параметра являются целыми числами, и они сравниваются по целым числам без преобразования типов.
Шестнадцатеричные значения обрабатываются как двоичные строки по сравнению с нечисловыми
Один параметр — TIMESTAMP или DATETIME, а другой параметр — константа, константа будет преобразована в метку времени.
Один параметр имеет десятичный тип. Если другой параметр десятичный или целочисленный, целое число будет преобразовано в десятичное для сравнения. Если другой параметр является числом с плавающей запятой, десятичное число будет преобразовано в число с плавающей запятой для сравнения.
Во всех остальных случаях оба аргумента преобразуются в числа с плавающей запятой и сравниваются

[Ссылка] Избегайте следующих крайних недоразумений при создании индексов:

Нет недостатка в злоупотреблениях. Думайте, что запрос должен построить индекс.
Лучше не злоупотреблять. Считается, что индексы занимают место и сильно замедляют обновления и новые добавления.
Сопротивляйтесь уникальным индексам. Считается, что уникальность бизнеса нужно решать методом «сначала проверить, а потом вставить» на прикладном уровне.

3 оператора SQL

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

Примечание: count(*) будет подсчитывать строки со значениями NULL, а count(имя столбца) не будет подсчитывать строки со значениями NULL в этом столбце.

[Обязательный] count(distinctcol) вычисляет количество различных строк, кроме NULL в этом столбце.Обратите внимание, что count(distinctcol1, col2) возвращает 0, если один из столбцов имеет значение NULL, даже если другой столбец имеет другие значения.

[Обязательно] Когда все значения столбца равны NULL, возвращаемый результат count(col) равен 0, но возвращаемый результат sum(col) равен NULL, поэтому вам нужно обратить внимание на проблему NPE при использовании sum( ).

Положительный пример: проблемы суммы NPE можно избежать, используя: SELECT IF(ISNULL(SUM(g)),0,SUM(g))FROM table;

[Обязательно] Используйте ISNULL(), чтобы определить, является ли значение NULL.

Примечания: Прямое сравнение NULL с любым значением равно NULL.

1) Результатом возврата NULLNULL является NULL, а не ложь.

2) Возвращаемый результат NULL=NULL равен NULL, а не истине.

3) Возвращаемый результат NULL1 равен NULL, а не истине.

[Обязательно] При написании логики запроса подкачки в коде, если count равен 0, его следует возвращать напрямую, чтобы избежать выполнения последующих операторов подкачки.

Интерпретация: сначала запросить COUNT, затем запросить данные пейджинга.

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

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

[Обязательно] Использование хранимых процедур запрещено.Хранимые процедуры трудно отлаживать и расширять, и они не переносимы.

[Обязательно] При исправлении данных (особенно при удалении и изменении записей) сначала выберите, чтобы избежать случайного удаления, а затем после подтверждения выполните оператор обновления.

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

[Рекомендация] Избегайте в работе, если этого можно избежать.Если это неизбежно, вам нужно тщательно оценить количество элементов набора позади и контролировать его в пределах 1000.

[Справка] При необходимости интернационализации все символы хранятся и представляются в кодировке utf-8.Обратите внимание на отличие функций статистики символов.

Описание: SELECT LENGTH("Простая работа"); Возвращается 12, подсчитывается количество байтов SELECT CHARACTER_LENGTH("Простая работа"); Возвращается 4, количество символов Если вам нужно хранить выражения, то выбирайте utf8mb4 для хранения обратите внимание, что это то же самое, что и разница в кодировке utf-8.
Интерпретация: utf8 может хранить 3 байта данных, utf8mb4 может хранить четыре байта и специально разработан для совместимости с 4 байтами Unicode.utf8mb4 является надмножеством utf8, и для изменения кодировки с utf8 на utf8mb4 не требуется никакого дополнительного преобразования .

Выражения эмодзи не входят в 3-байтовый диапазон представления utf8 и могут храниться в utf8mb4.

[Ссылка] TRUNCATE TABLE быстрее, чем DELETE, и использует меньше ресурсов системы и журнала транзакций, но TRUNCATE не имеет транзакций и не запускает триггеры, которые могут вызвать аварии, поэтому не рекомендуется использовать этот оператор в коде разработки. Примечания: TRUNCATE TABLE функционально идентична инструкции DELETE без предложения WHERE.

4 ORM-отображение

[Обязательно] В табличном запросе никогда не используйте * в качестве списка полей запроса, какие поля должны быть четко указаны.

инструкция:

1) Увеличить стоимость разбора анализатора запросов.

2) Добавление или вычитание полей может легко противоречить конфигурации resultMap.

3) Бесполезные поля увеличивают потребление сети, особенно поля текстового типа.

Интерпретация: лучше использовать «индекс покрытия».

[Обязательно] Логические атрибуты классов POJO нельзя добавлять с помощью is, а поля базы данных необходимо добавлять с помощью is_, что требует сопоставления полей и атрибутов в resultMap.

Примечание. Обратитесь к определению класса POJO и определению полей базы данных, необходимо добавить сопоставление в . В код, сгенерированный MyBatis Generator, необходимо внести соответствующие изменения.

[Обязательно] Не используйте resultClass в качестве возвращаемого параметра, даже если все имена атрибутов класса соответствуют полям базы данных одно за другим, их все равно нужно определить, и наоборот, каждая таблица должна иметь соответствующий ей класс POJO.

Описание: Настройте отношение сопоставления, чтобы отделить поля от класса DO для простоты обслуживания.

[Обязательно] В параметрах конфигурации sql.xml используются: #{}, #param# Не используйте ${}. Этот метод подвержен SQL-инъекциям.

Интерпретация: Разница между # и $: Обработка при предварительной компиляции отличается. #{} При предварительной обработке часть параметра будет заменена заполнителем ?, например:

select * from user where name = ?;

А ${} – это просто замена строки. На этапе динамического анализа оператор sql будет преобразован в

выберите * от пользователя, где имя = 'zhangsan';

Выше подстановка параметра #{} происходит в СУБД, а ${} происходит во время динамического анализа.

[Обязательно] Не рекомендуется использовать queryForList(String statementName, int start, int size), предоставленный iBATIS.

Примечание. Метод реализации заключается в получении всех записей оператора SQL, соответствующего имени оператора, в базе данных, а затем получении вложенной коллекции начала и размера через подсписок.
Положительный пример: Map map = new HashMap();map.put("start", start);map.put("size", size);

[Обязательно] Не допускается прямое использование HashMap и Hashtable в качестве вывода набора результатов запроса.

Описание: resultClass="Hashtable", имя поля и значение атрибута будут размещены, но тип значения не контролируется.

[Рекомендация] Не пишите большой и исчерпывающий интерфейс обновления данных. Передача в качестве класса POJO, независимо от того, является ли это вашим собственным целевым полем обновления или нет, обновляет набор таблиц c1=value1,c2=value2,c3=value3; это неправильно. При выполнении SQL не обновлять неизмененные поля, во-первых, это чревато ошибками, во-вторых, неэффективно, в-третьих, увеличивает объем бинлога.

[Ссылка] Не злоупотребляйте транзакциями @Transactional. Транзакции будут влиять на QPS базы данных.Кроме того, при использовании транзакций следует учитывать различные схемы отката, включая откат кэша, откат поисковой системы, компенсацию сообщений и статистическую коррекцию.

CompareValue в [Reference] — это константа, которая сравнивается со значением атрибута, которое обычно является числом, что означает, что это условие применяется, когда оно равно, это означает, что оно выполняется, когда оно не пусто и не равно null; это означает, что он выполняется, когда он не равен нулю.

Добро пожаловать в мой публичный аккаунт WeChat