оптимизация mysql | механизм хранения, построение таблиц, индексация, предложения по оптимизации sql

Лично некоторые обзоры по выбору механизмов хранения, построению таблиц, построению индексов и оптимизации sql предоставляют читателям некоторые справочные мнения.

Рекомендуется посетить мой личный сайт, макет лучше:Chen Mingyu.top/MySQL-op Тим…

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

Просмотрите sql поддерживаемых движков в mysql:

show engines;

Сравнение используемых в повседневной работе движков хранения: InnoDB, MyISAM

	InnoDB	MyISAM
предел хранения	64T	256T
Поддержка транзакций	yes	no
поддержка индекса	yes	yes
Поддержка полнотекстового индексирования	no	yes
Поддержка кэша данных	yes	no
Поддержка внешних ключей	yes	no
Поддержка хэш-индекса	no	no

Начиная с MySQL 5.6, InnoDB поддерживает создание полнотекстовых индексов.

innodb

Безопасность транзакций (ACID), которая поддерживает фиксацию, откат и устойчивость к сбоям, поддерживает блокировки строк, поддерживает ограничения целостности внешнего ключа.

подходит для сцены

Требуется обработка транзакции
Большой объем табличных данных, большое количество одновременных операций

MyISAM

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

подходит для сцены

много счетов
запрашивать очень часто

Другие механизмы хранения

ПАМЯТЬ двигатель

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

ОБЪЕДИНИТЬ двигатель

Таблица слияния представляет собой комбинацию группы таблиц MyISAM, поэтому в таблице слияния нет данных. Операция с этой таблицей на самом деле является операцией внутренней таблицы MyISAM. Объединение нескольких таблиц MyISAM подходит для некоторых операций, таких как отчеты.

АРХИВ двигателя

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

Лично я рекомендую движок Innodb. Компания также оговаривает, что движок Innodb должен использоваться при создании новой таблицы. Механизм Innodb может предоставлять больше функций, чем движок MyISAM. Кэши можно использовать для сценариев запросов не в реальном времени, а es можно использовать для запросов почти в реальном времени., конечно, это только личное мнение, все равно надо выбирать разные движки хранения для разных сценариев. Поэтому, зная характеристики различных механизмов хранения, вы можете выбрать подходящий механизм хранения в соответствии с различными бизнес-требованиями.

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

При построении таблицы старайтесь следовать следующим принципам

Попробуйте выбрать небольшой тип данных, попробуйте tinyint (1 байт) > smallint (2 байта) > int (4 байта) > bigint (8 байтов), например, поле логического удаления yn (1 означает, что доступно, 0 представляет), вы можно выбрать тип tinyint (1 байт)
Постарайтесь, чтобы длина типа данных поля была фиксированной.
Старайтесь не использовать значение null. Сложно оптимизировать запрос поля, используя значение null, которое влияет на индекс. Вместо этого можно использовать 0 или ''.
Избегайте широких таблиц, разделяйте их, когда они могут быть разделены, таблица часто соответствует предметной области, точно так же, как и при проектировании объектов, придерживайтесь единого принципа
Старайтесь избегать использования текста и больших двоичных объектов. Если вы должны использовать его, разделите поля типа текста и большого двоичного объекта в новую таблицу, а затем используйте первичный ключ для соответствия исходной таблице.
Запрещено использовать тип float или double. Эта яма слишком велика. Существуют проблемы с точностью в float или double. При сравнении или операциях сложения и вычитания точность будет потеряна, что приведет к ненормальным данным. При использовании типа float или double , подумайте, можно ли использовать int или bigint. Например, при использовании типа float или double в качестве единицы суммы вы можете вместо этого использовать типы int и bigint в центах, а затем преобразовать единицы по бизнес-коду.
Добавьте поля createUser, createTime.updateUser, updateTime в каждую таблицу
Имя должно быть стандартизировано, включая: имя библиотеки, имя таблицы, имя поля, имя индекса.
Запросите часто используемые поля, не забудьте добавить индексы
Старайтесь избегать использования внешних ключей, без ограничений внешнего ключа производительность выше, и тогда целостность данных управляется программой
Если прогнозируется, что количество таблиц будет очень большим, лучше всего разделить таблицы при построении таблицы, чтобы не вызывать проблем с эффективностью из-за большого объема данных за один раз.

Незаконченное будет добавлено,

показатель

Индекс – это разрозненный результат данных, созданный для ускорения поиска строк данных в таблице. Он создан для таблицы и состоит из индексных страниц, отличных от страниц данных. Строки на каждой индексной странице содержат логические указатели, используемые для ускорения поиск физических данных. Цель создания индексов — повысить эффективность запросов. Все индексы Innodb реализованы на основе b-дерева.

тип индекса

Обычный индекс: самый простой индекс, неограниченный

#方式1
CREATE INDEX idx_username ON sys_user(user_name(32)); 
#方式2
ALTER table sys_user ADD INDEX idx_username(user_name(32))

Индекс первичного ключа: таблица может иметь только один индекс первичного ключа и не может быть пустой.

Как правило, индекс первичного ключа создается одновременно с построением таблицы.

CREATE TABLE `sys_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(32) DEFAULT NULL,
  `pass_word` varchar(32) DEFAULT NULL,
  `token` varchar(32) DEFAULT NULL,
  `token_expire` int(11) DEFAULT NULL,
  `yn` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=348007 DEFAULT CHARSET=utf8;

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

CREATE UNIQUE INDEX idx_token ON sys_user(token_expire)

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

ALTER TABLE sys_user ADD INDEX idx_un_te (user_name(32),token_expire);

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

CREATE FULLTEXT INDEX idx_ ON sys_user(pass_word)

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

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

Например, если установлено значение varchar(10), при создании индекса будут сохранены только первые 10 байт поля. Если число байтов, установленных в поле, относительно мало, это может привести к тому, что в индекс, а затем вернуться к таблице, что приведет к снижению производительности. , поэтому вам решать, какое значение установлено для поля.
Следуйте принципу левого соответствия индекса
Обратите внимание на использование попыток не использовать как «% A%», поэтому не принимайте индекс, вы можете использовать «A%», возьмите индекс
Не вычисляйте столбец индекса, например, выберите * из sys_user, где token_expire + 1 = 10000, такой оператор не будет иметь индекса
Какое поле для построения индекса — это столбец, который часто появляется в том месте, где, группировать, упорядочивать, лучше всего добавить индекс
При использовании объединенного индекса постарайтесь учесть оптимизацию индекса pushdown.
Для условия использования или индекс будет использоваться только в том случае, если условия вокруг или являются индексом, в противном случае вместо этого можно использовать полное сканирование таблицы, вы можете вместо этого использовать объединение
Избегайте использования select *. Для полей, которые должны запрашивать только первичный ключ или только индекс в условии where, покрывающий индекс будет использоваться для создания меньшего количества таблиц.
Избегайте неявных преобразований в операторах SQL. В MySQL, если строка сравнивается с числом, строка преобразуется в число. Например, поле имеет тип varchar, но входной параметр имеет тип int, даже если поле имеет индекс, то не пойдет.Потому что здесь будет неявное преобразование

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

Недостатки индексации

Хотя индекс может повысить эффективность запроса, он снизит эффективность при вставке, обновлении и удалении, поскольку индекс также сохраняется при сохранении данных.

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

объяснить анализ sql

Вы можете использовать объяснение для анализа выполнения sql, например

explain select * from sys_user where token_expire = 10000;

Как сказано в мануале разработчика Али, стандарт для оптимизации производительности sql: хоть дойди до диапазона, требуй ref level, если он может быть константным, то лучше всего

Чтобы объяснить, уровень здесь относится к полю типа на рисунке выше:

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

SQL-оптимизация

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

Предложения по оптимизации

При запросе вы должны помнить об использовании предела для ограничения
Используйте ограничение 1 для запросов, в результате которых требуется только одна часть данных.
использоватьcount(*)Или count (1), чтобы подсчитать количество строк для запроса.При использовании count (column) вам нужно проверить, является ли столбец нулевым, и он не будет учитывать случай, когда этот столбец имеет значение null, а mysql имеет对count(*)оптимизированный
Не используйте select * для запроса данных, используйте имя столбца, требуемое select, и запрашивайте таким образом
Используйте ссылки для присоединения вместо подзапросов
Не используйте внешние ключи, ограничения внешних ключей могут быть решены в программе
Контролируйте количество наборов в операциях, не слишком большое
Используйте объяснение, чтобы проанализировать причины медленных запросов, а затем оптимизируйте sql, чтобы максимально использовать индекс.

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