- Все имена объектов базы данных должны быть написаны строчными буквами и разделены символами подчеркивания (MySQL чувствителен к регистру, имя должно быть знакомо с именем, желательно не более 32 символов)
- Во всех именах объектов базы данных запрещено использовать зарезервированные ключевые слова MySQL (такие как desc, range, match, delayed и т. д., см.Официальные зарезервированные слова MySQL)
- Временные библиотечные таблицы должны иметь префикс tmp и суффикс даты (tmp_)
- Библиотеки резервного копирования и библиотеки должны иметь префикс bak и суффикс даты (bak_)
- Все имена столбцов и типы столбцов, в которых хранятся одни и те же данные, должны быть согласованы. (Поля в нескольких таблицах, например user_id, должны быть одного типа)
- Механизмом хранения по умолчанию до mysql5.5 является myisam, особых требований нет, все таблицы должны использовать innodb (преимущества innodb поддерживают ошибки, блокировки на уровне строк, лучшую производительность при высокой степени параллелизма и дополнительную аппаратную поддержку для многоядерных, больших объемов памяти). , ssd и т.д. хорошо)
- Набор символов базы данных и таблицы должен максимально использовать utf8 (набор символов должен быть унифицирован, чтобы избежать искажения символов, вызванных преобразованием набора символов, а китайские символы занимают 3 байта в utf8)
- Все таблицы и поля должны быть аннотированы КОММЕНТАРИЙ, а ведение словаря данных должно осуществляться с самого начала
- Попробуйте контролировать размер объема данных одной таблицы в пределах 500 Вт.Если он превышает 500 Вт, вы можете использовать архивирование исторических данных, подбазу данных и подтаблицу для достижения (5 миллионов строк не предел базы данных MySQL. Если он слишком велик, будет очень сложно изменить структуру таблицы, создать резервную копию и восстановить.Большая проблема.У MySQL нет ограничений на хранилище, зависит от настроек хранилища и файловой системы)
- Используйте таблицы разделов mysql с осторожностью (таблицы разделов физически выглядят как несколько файлов и логически как таблица)
- Тщательно выбирайте ключ раздела, эффективность запросов между разделами может быть ниже.
- Рекомендуется использовать физические подтаблицы для управления большими данными.
- Попробуйте разделить горячие и холодные данные и уменьшить ширину таблицы (mysql ограничивает хранение до 4096 столбцов, а на количество строк ограничения нет, но общее количество байт в каждой строке не может превышать 65535. Столбец преимущества ограничения: уменьшить дисковый ввод-вывод и обеспечить память для горячих данных. Частота попаданий в кэш, чтобы избежать чтения бесполезных холодных данных)
- Запрещено создавать зарезервированные поля в таблице (сохраняемый тип данных не может быть подтвержден, и таблица будет заблокирована, если зарезервированный тип поля будет изменен)
- Запретить хранение изображений в данных, файловых двоичных данных (с использованием файлового сервера)
- Запрещено проводить стресс-тесты базы данных онлайн
- Запрещено прямое подключение к базе данных производственной среды из среды разработки и тестовой среды.
- Ограничьте количество индексов в каждой таблице.Рекомендуется, чтобы в одной таблице было не более 5 индексов (индексы повысят эффективность запросов, но снизят скорость вставки и обновления)
- Избегайте создания избыточных индексов и дублирующих индексов (избыточные: индекс (a, b, c) индекс (a, b) индекс (a))
- Запрещено создавать отдельный индекс для каждого столбца в таблице
- Каждая таблица innodb должна иметь первичный ключ, выбрать идентификатор автоинкремента (нельзя использовать часто обновляемые столбцы в качестве первичных ключей, не применять UUID, MD5, HASH, строковые столбцы в качестве первичных ключей)
- Наиболее различимый столбец помещается в крайнюю левую часть общего индекса.
- Попробуйте поместить столбец с наименьшей длиной поля в крайнюю левую часть индекса соединения.
- Старайтесь избегать использования внешних ключей (физические внешние ключи запрещены, рекомендуются логические внешние ключи)
- Предпочитайте наименьший тип данных, который соответствует потребностям хранения
- Предпочитаете использовать беззнаковые целые числа для хранения
- Предпочитаете хранить наименьший тип данных (varchar(N), N представляет количество символов, а не количество байтов, N представляет, сколько китайских символов может быть сохранено)
- Избегайте использования типов Text или Blob
- Избегайте использования типа данных ENUM (для изменения значения ENUM требуется оператор ALTER, операция ORDER BY типа ENUM неэффективна и требует дополнительных операций, запрещено использовать значение book в качестве значения перечисления ENUM
- Попробуйте определить все поля как NOT NULL (для сохранения индекса NULL требуется дополнительное пространство, поэтому необходимо временно использовать больше памяти, а для сравнения и расчета требуется специальная обработка значений NULL)
- Используйте метку времени или тип даты и времени для хранения времени
- Данные суммы, связанные с финансами, в десятичном виде (без потери точности, float и double запрещены)
- Избегайте использования двойных знаков % и т.п., а также строго запрещен поиск с нечеткой левой или полной нечеткостью (при необходимости используйте поисковую систему для ее решения. Индексный файл имеет функцию сопоставления крайнего левого префикса B-Tree, если левое значение не определено, то этот индекс использовать нельзя. )
- Рекомендуется использовать подготовленные операторы для операций с базой данных.
- Запрос между базами данных запрещен (оставьте место для переноса данных и подтаблицы подбазы данных, уменьшите связанность и уменьшите риск)
- Запретить select * query (потреблять больше ресурсов процессора, ввода-вывода и пропускной способности сети, не может использовать покрывающий индекс)
- Запретить операторы вставки без списка полей (значения вставки в t («a», «b», «c») не допускаются)
- Если срабатывания можно избежать, избегайте его, если невозможно избежать, необходимо тщательно оценить количество отстающих элементов множества и контролировать его в пределах 1000.
- Отключить случайную сортировку с порядком по rand()
- Запрещено выполнять преобразование функций и вычисления для столбца в предложении where (например: where date(createtime)='20160901' не сможет использовать индекс в столбце createtime. Измените на where createtime>='20160901 ' и время создания
- Попробуйте использовать union all вместо union
- Разделить сложный большой SQL на несколько маленьких SQL (один SQL в MySQL может использовать только один ЦП для вычислений)
- По возможности избегайте использования подзапросов. Вы можете оптимизировать подзапросы в операции соединения (результирующий набор подзапросов не может использовать индексы, а подзапросы будут генерировать операции с временными таблицами. Если объем данных в подзапросах повлияет на эффективность, он будет потреблять слишком много ресурсы процессора и ввода-вывода)
- Операции пакетной записи более 1 миллиона строк должны выполняться пакетами несколько раз (большие пакетные операции могут вызвать серьезные задержки между ведущим и подчиненным, журналы binlog в формате строки будут генерировать большое количество журналов, что позволяет избежать операций с большими транзакциями)
- Для больших таблиц используйте pt-online-schema-change для изменения структуры таблицы (чтобы избежать задержки ведущий-подчиненный, вызванной изменениями больших таблиц, и избежать блокировки таблицы при изменении полей таблицы).
- Для программ, подключающихся к учетным записям базы данных, следуйте принципу наименьших привилегий.
- Соединения запрещены для более чем трех таблиц. (Для полей, которые должны быть объединены, типы данных должны быть абсолютно согласованными; когда несколько таблиц связаны с запросами, убедитесь, что связанные поля должны иметь индексы. Даже если две таблицы объединены, обратите внимание на индексы таблиц и SQL. представление.)
- При создании индекса для поля типа varchar необходимо указать длину индекса. Нет необходимости создавать индекс для всего поля. Длину индекса можно определить в соответствии с фактической дискриминацией текста.
- Цель оптимизации производительности SQL: по крайней мере, достичь уровня диапазона, требование - уровень ссылки, если он может быть лучшим.
- Используйте ISNULL() для проверки значения NULL.
- Старайтесь не использовать физическое удаление (т.е. удалять напрямую, если хотите удалить, делайте резервную копию заранее), а используйте логическое удаление, для логического удаления используйте поле delete_flag, тип tinyint, 0 означает не удалено, 1 значит удален
- Если есть порядок по сценариям, обратите внимание на упорядоченность индекса. Последнее поле порядка является частью комбинации и индекса и помещается в конец порядка комбинации индексов, чтобы избежать сортировки файлов и повлиять на производительность запроса.
- При написании логики запроса подкачки в коде, если count равен 0, его следует возвращать напрямую, чтобы избежать выполнения последующих операторов подкачки.
Ссылаться на:
- «Руководство по разработке Java для Alibaba»
- "Проект создания высокопроизводительной масштабируемой базы данных MySQL и оптимизации архитектуры для электронной коммерции"
Исправления и дополнения приветствуются!