Говоря об оптимизации MySQL, большинство из вас должно быть с ней знакомо. Эти ключевые слова давно запомнены в нашей памяти: избегайте использования SELECT*, избегайте использования суждений NULL, стройте индексы в соответствии с требованиями, оптимизируйте параметры MySQL... Но есть ли у вас какие-либо вопросы об этих методах оптимизации? это и его соответствующий принцип работы? Можете ли вы полностью применить его в нашем реальном процессе разработки? Я думаю, это еще предстоит увидеть. Поэтому в этой статье будут подробно представлены методы оптимизации MySQL и соответствующие им технические принципы.Я надеюсь, что после прочтения вы сможете более четко и непосредственно понять эти схемы оптимизации и применить их к нашей работе.
01 Принципиальный анализ
1 Принцип архитектуры MySQL
Перед этим нам нужно понять рабочую структуру каждой части MySQL, чтобы мы могли понять сервер MySQL. Как показано ниже:
Рабочую архитектуру MySQL можно условно разделить на три уровня, верхним является клиент, такой как обработка соединения, авторизация, аутентификация, безопасность и другие функции, которые обрабатываются на этом уровне.
Основные службы MySQL находятся на среднем уровне, включая синтаксический анализ запросов, анализ, оптимизацию, кэширование и встроенные функции. На этом уровне также реализована вся функциональность кросс-хранилища: хранимые процедуры, триггеры, представления и т. д.
Самый нижний уровень — это механизм хранения, отвечающий за хранение и извлечение данных в MySQL. Подобно файловым системам в Linux, каждый механизм хранения имеет свои преимущества и недостатки. Сервисный уровень в середине взаимодействует с механизмом хранения через API, и эти интерфейсы API скрывают различия между различными механизмами хранения.
2 Принцип запроса MySQL
Если мы хотим, чтобы MySQL достигла более высокой производительности запросов, нам сначала нужно понять, как MySQL оптимизирует и выполняет запросы. Давайте сначала посмотрим, когда мы отправляем запрос в MySQL, что он делает?
Протокол связи клиент/сервер
Протокол связи клиент/сервер MySQL не происходит одновременно.В любой момент либо сервер отправляет данные клиенту, либо клиент отправляет данные на сервер.Как только один конец начинает отправлять сообщение, другой конец должен чтобы получить все сообщение перед ответом, поэтому мы не можем и не должны разрезать сообщение на мелкие части и отправлять его независимо друг от друга, и нет возможности выполнить управление потоком.
Клиент отправляет запрос на сервер в одном пакете, поэтому, когда оператор запроса очень длинный, необходимо установить параметр max_allowed_packet. Но следует отметить, что если запрос будет слишком большим, сервер откажется от получения дополнительных данных и выдаст исключение.
Напротив, данные, которые сервер отвечает пользователю, обычно велики и состоят из нескольких пакетов данных. Но когда сервер отвечает на запрос клиента, клиент должен получить весь возвращенный результат, а не просто взять первые несколько результатов и позволить серверу прекратить отправку. Поэтому в реальной разработке очень полезно делать запрос как можно более простым и возвращать только необходимые данные.Очень хорошая привычка уменьшать размер и количество пакетов данных между сообщениями.
кэш запросов
Перед анализом запроса, если кеш запросов открыт, MySQL проверяет, соответствует ли запрос данным в кеше запросов. Если текущий запрос попадает в кеш запросов, результат в кеше будет возвращен сразу после однократной проверки разрешений пользователя. В этом случае запрос не будет разобран, план выполнения не сгенерирован и не будет выполнен.
MySQL хранит кеш в справочной таблице (не следует понимать как таблицу, ее можно рассматривать как структуру данных, аналогичную HashMap), через индекс хеш-значения, это хэш-значение передается через сам запрос, текущая база данных в запрашиваемый номер версии протокола клиента и другую информацию, которая может повлиять на результат. Таким образом, разница между двумя запросами в любом символе (например, пробел, комментарий) приведет к промаху кеша.
Если запрос содержит какие-либо определяемые пользователем функции, хранимые функции, пользовательские переменные, временные таблицы и системные таблицы в библиотеке mysql, результаты запроса не будут кэшироваться. Например, функции NOW() или CURRENT_DATE() вернут разные результаты запроса из-за разного времени запроса. Например, оператор запроса, содержащий CURRENT_USER или CONNECION_ID(), вернет разные результаты из-за разных пользователей. Кэшировать такие результаты запроса нет никакого смысла.
Так как это кеш, он потерпит неудачу Когда произойдет сбой кеша запросов? Система кэширования запросов MySQL отслеживает каждую таблицу, участвующую в запросе, и если эти таблицы (данные или структура) изменяются, все кэшированные данные, связанные с этой таблицей, становятся недействительными. Из-за этого при любой операции записи MySQL должен аннулировать все кэши для соответствующей таблицы. Если кэш запросов очень велик или фрагментирован, эта операция может привести к значительному потреблению ресурсов системы или даже к зависанию системы на некоторое время. И дополнительный расход кеша запросов к системе не только в операциях записи, но и в операциях чтения:
-
Любое утверждение запроса должно быть проверено перед запуском, даже если оператор SQL никогда не попадет в кеш
-
Если результаты запроса могут быть кэшированы, результаты будут сохранены в кеше после завершения выполнения, что также приведет к дополнительному потреблению системы.
Исходя из этого, нам нужно знать, что кэширование запросов не улучшит производительность системы при любых обстоятельствах.Кэширование и инвалидация приведут к дополнительному потреблению.Только когда экономия ресурсов, обеспечиваемая кешем, больше, чем ресурсы, которые он потребляет, он доводит производительность до система продвигает. Но как оценить, может ли открытие кеша привести к улучшению производительности — очень сложная вещь, и она выходит за рамки этой статьи. Если в системе действительно есть некоторые проблемы с производительностью, вы можете попробовать открыть кеш запросов и внести некоторые оптимизации в структуру базы данных, например:
-
Замените одну большую таблицу несколькими маленькими таблицами, будьте осторожны, чтобы не переусердствовать
-
Пакетная вставка вместо круглой одиночной вставки
-
Разумно контролируйте размер кэш-памяти, вообще говоря, целесообразнее установить размер в несколько десятков мегабайт.
-
Вы можете контролировать запрос SQL_NO_CACHE SQL_CACHE и необходимость кэширования
Последний совет: не открывайте кеш запросов, особенно в приложениях с интенсивной записью. Если уж ничем помочь не можете, то можете поставить query_cache_type в DEMAND, тогда только добавленный SQL_CACHE запрос пойдет на кеширование, остальные запросы не пойдут, так что вы можете совершенно свободно контролировать какие запросы нужно кешировать.
Конечно, сама система кэширования запросов очень сложна, и здесь обсуждается лишь малая часть, другие более глубокие темы, например: как кэш использует память? Как контролировать фрагментацию памяти? Как транзакция влияет на кеш запросов и т. д., читатели могут сами прочитать соответствующую информацию.
Разбор и предварительная обработка
MySQL анализирует операторы SQL по ключевым словам и генерирует соответствующее дерево разбора. В этом процессе синтаксический анализатор в основном проверяет и анализирует правила грамматики. Например, используются ли в SQL неправильные ключевые слова, правильный ли порядок ключевых слов и так далее. Предварительная обработка дополнительно проверит, соответствует ли дерево синтаксического анализа правилам MySQL. Например, проверьте, существуют ли запрашиваемые таблица данных и столбец данных и т. д.
Оптимизация запросов
Синтаксическое дерево, созданное на предыдущих шагах, считается допустимым и преобразуется оптимизатором в план запроса. В большинстве случаев запрос можно выполнить разными способами и, в конце концов, вернуть соответствующие результаты. Роль оптимизатора состоит в том, чтобы найти среди них наилучший план выполнения.
MySQL использует оптимизатор на основе затрат, который пытается предсказать запрос, используя стоимость времени, и выбирает минимальную стоимость. В MySQL вы можете получить стоимость текущего запроса, запросив значение last_qury_cost текущего сеанса.
mysql> select * from t_message limit 10;...省略结果集mysql> show status like 'last_query_cost';+-----------------+-------------+| Variable_name | Value |+-----------------+-------------+| Last_query_cost | 6391.799000 |+-----------------+-------------+
Результаты в примере показывают, что оптимизатор считает случайным образом, что вам нужно сделать 6391 страницу данных для выполнения вышеуказанного запроса. Этот результат рассчитывается на основе статистической информации некоторых столбцов, включая: количество страниц, индексы, индексы, и распределение индексов, и т.п. индекса, и распределение индексов, и распределение индекса, и раздача индекса, и раздача индекса, и раздача индекса, и раздача индекса, и раздача индекса, и раздача индекса, и раздача индекса , и распределение индекса, и распределение индекса, и распределение индекса, и распределение индекса, и распределение индекса, и распределение индекса, и распределение индекс, индекс и длина индекса, индекс и распределение индекса и т.п.
Есть много причин, по которым MySQL выбирает неправильный план выполнения, например, неточная статистика, не учитывая стоимость операций, которые не находятся под ее контролем (пользовательские функции, хранимые процедуры), и то, что MySQL считает оптимальным, отличается от того, что мы То же самое (мы хотим, чтобы время выполнения было как можно короче, но значение MySQL выбирает, что считает, что стоимость низкая, но низкая стоимость не означает короткое время выполнения) и так далее.
Оптимизатор запросов MySQL — очень сложный компонент, использующий множество стратегий оптимизации для создания оптимального плана выполнения:
-
Переопределить порядок ассоциации таблицы (когда с запросом связано несколько таблиц, он не обязательно следует порядку, указанному в SQL, но есть некоторые приемы для указания порядка ассоциации)
-
Оптимизируйте функции MIN() и MAX() (чтобы найти минимальное значение столбца, если у столбца есть индекс, вам нужно только найти крайний левый конец индекса B+Tree, в противном случае вы можете найти максимальное значение, см. ниже конкретный принцип)
-
Завершить запрос досрочно (например: при использовании Limit запрос будет прекращен сразу после нахождения достаточного количества наборов результатов)
-
Оптимизированная сортировка (в старой версии MySQL используется двухпереходная сортировка, то есть сначала чтение указателя строки и сортируемого поля, сортировка его в памяти, а затем чтение строки данных по результату сортировки, при этом новая версия использует одиночную сортировку Sub-transfer, то есть чтение всех строк данных сразу, а затем сортировку по заданному столбцу.Для приложений с интенсивным вводом-выводом эффективность будет намного выше)
С непрерывным развитием MySQL стратегии оптимизации, используемые оптимизатором, также постоянно развиваются.Вот лишь несколько очень распространенных и простых для понимания стратегий оптимизации.Для других стратегий оптимизации вы можете проверить их самостоятельно.
механизм выполнения запросов
После того, как этапы синтаксического анализа и оптимизации будут завершены, MySQL сгенерирует соответствующий план выполнения, и механизм выполнения запросов будет постепенно выполнять инструкции, указанные в плане выполнения, для получения результата. Большинство операций во всем процессе выполнения выполняются путем вызова интерфейсов, реализованных механизмом хранения, которые называются API-интерфейсами обработчиков. Каждая таблица в процессе запроса представлена экземпляром обработчика. Фактически MySQL создает экземпляр обработчика для каждой таблицы на этапе оптимизации запроса, и оптимизатор может получить соответствующую информацию о таблице в соответствии с интерфейсом этих экземпляров, включая все имена столбцов таблицы, статистику индекса и т. д. Интерфейс механизма хранения предоставляет очень богатые функции, но на нижнем уровне всего несколько десятков интерфейсов, и эти интерфейсы выполняют большинство операций запроса, например строительные блоки.
Вернуть результат клиенту
Завершающим этапом выполнения запроса является возврат результатов клиенту. Даже если никакие данные не запрашиваются, MySQL все равно вернет соответствующую информацию о запросе, такую как количество строк, затронутых запросом, и время выполнения.
Если кэширование запросов включено, и запрос можно кэшировать, MySQL также будет хранить результат в кэше.
Возврат результирующего набора клиенту — это поэтапный процесс. Возможно, MySQL начал постепенно возвращать результирующий набор клиенту, когда был сгенерирован первый результат. Таким образом, серверу не нужно хранить слишком много результатов и потреблять слишком много памяти, а также позволяет клиенту получить возвращаемые результаты с первого раза. Следует отметить, что каждая строка в результирующем наборе будет отправлена как пакет данных, который удовлетворяет протоколу связи, описанному в ①, а затем передан через протокол TCP.В процессе передачи пакеты данных MySQL могут быть кэшированы, а затем отправлены. партиями.
Вернитесь назад и суммируйте весь процесс выполнения запросов MySQL, который обычно делится на 6 шагов:
-
Клиент отправляет запрос на сервер MySQL
-
Сервер сначала проверяет кеш запроса, и если он попадает в кеш, он немедленно возвращает результат, хранящийся в кеше. В противном случае перейти к следующему этапу
-
Сервер выполняет синтаксический анализ SQL, предварительную обработку, а оптимизатор генерирует соответствующий план выполнения.
-
В соответствии с планом выполнения MySQL вызывает API механизма хранения для выполнения запроса.
-
Возврат результата клиенту при кэшировании результата запроса
02 План оптимизации
1 Предложения по оптимизации
После прочтения вышеприведенных принципов у вас должно быть четкое представление о принципах работы MySQL.Далее я оптимизирую его с учетом этих аспектов.
SQL-оптимизация
Мы должны обратить внимание на следующие моменты: сначала выберите SQL, который необходимо оптимизировать, обычно начинайте с объяснения и профиля, всегда используйте небольшие наборы результатов для управления большими наборами результатов, выполните сортировку в индексе, используйте самые маленькие столбцы и используйте наиболее эффективные условия фильтрации, избегая сложных JOIN и подзапросов. Конкретная оптимизация выглядит следующим образом:
Анализировать частоту выполнения SQL
show status例如：分析读为主，还是写为主
Низкая эффективность позиционирования SQL
慢查询日志定位-log-slow-queries = xxx（指定文件名）SHOW PROCESSLIST查看当前正在进行的线程，包括线程状态、是否锁表
Анализ планов выполнения SQL
explain "your sql"desc "your sql"- 部分参数分析select_type: SIMPLE 简单表，不使用表连接或子查询PRIMARY 主查询，即外层的查询UNION SUBQUER 子查询的第一个selecttype: ALL 全表扫描index 索引全扫描range 索引范围扫描ref 使用非唯一索引或唯一索引的前缀扫描eq_ref 类似ref，使用的索引是唯一索引const/system 单表中最多有一个匹配行NULL 不用访问表或者索引，直接得到结果
показать профиль Анализировать SQL
select @@have_profiling 是否支持select @@profiling 是否开启执行 "your sql"show profiles show profile block io for QUERY 17
Оптимизация индекса
Здесь я анализирую его с трех аспектов, а именно типа, метода и создания:
Пример выглядит следующим образом:
Классификация хранения индексов
B-TREE索引：常见，大部分都支持HASH索引：只有memory引擎支持R-TREE索引：空间索引是MyISAM的一个特殊索引类型，主要用于地理空间数据类型full-text索引：全文索引，MyISAM的一个特殊索引类型，innodb从5.6开始支持
Создание и удаление индекса
添加索引ALTER Table `table_name` ADD PRIMARY KEY（`column`）ALTER Table `table_name` ADD UNIQUE（`column`）ALTER Table `table_name` ADD INDEX（`column`）ALTER Table `table_name` ADD FULLTEXT（`column`）删除ALTER Table `table_name` drop index index_name
Как MySQL может использовать индексы
匹配全值匹配值范围查询匹配最左前缀仅仅对索引进行查询（覆盖查询）匹配列前缀 （添加前缀索引）部分精确+部分范围
Когда нельзя использовать индекс
以%开关的like查询数据类型出现隐式转换复合索引查询条件不包含最左部分使用索引仍比全表扫描慢用or分割开的条件
Оптимизация выписки
Регулярно оптимизируйте таблицу
optimize table table_name 合并表空间碎片，对MyISAM、BDB、INNODB有效如果提示不支持，可以用 mysql --skip-new 或者 mysql --safe-mode 来重启，以便让其他引擎支持
Общие оптимизации
尽量避免全表扫描，对where及orderby的列建立索引尽量避免where使用 != 或 <>尽量避免where子句用 or 连接条件乱用%导致全表扫描尽量避免where子句对字段进行表达式操作尽量避免where子句对字段进行函数操作覆盖查询，返回需要的字段优化嵌套查询，关联查询优于子查询组合索引或复合索引，最左索引原则用exist代替in当索引列有大量重复数据时，SQL查询可能不会去利用索引
ПРИСОЕДИНЯЙТЕСЬ к оптимизации
ПРИСОЕДИНЯЙТЕСЬ принцип
在mysql中使用Nested Loop Join来实现join； A JOIN B：通过A表的结果集作为循环基础，一条一条的通过结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果
ПРИСОЕДИНЯЙТЕСЬ к принципу оптимизации
1，尽可能减少Join 语句中的Nested Loop 的循环总次数，用小结果集驱动大结果集；2，优先优化Nested Loop 的内层循环；3，保证Join 语句中被驱动表上Join 条件字段已经被索引；4，扩大join buffer的大小；
Оптимизация объектов базы данных
Оптимизация табличных типов данных
PROCEDURE ANALYSE (16,256) 排除多于16个，大于256字节的ENUM建议"your sql" PROCEDURE ANALYSE ()
разделение стола
垂直拆分针对某些列常用、某些列不常用水平拆分表很大表中的数据有独立性，能简单分类需要在表存放多种介质
антипарадигма
增加冗余列、增加派生列、重新组表和分割表
Используйте промежуточную таблицу
数据查询量大数据统计、分析场景
Оптимизация MySQL Server.
Сравнение движка MySQL:
Команды для движка таблиц:
show engines; 查看myql所支持的存储引擎show variables like '%storage_engine'; 查看mysql默认的存储引擎show create table table_name 查看具体表使用的存储引擎
InnoDB
1. 提供事务、回滚、系统崩溃修复能力、多版本并发控制事务2. 支持自增列3. 支持外键4. 支持事务以及事务相关联功能5. 支持mvcc的行级锁
MyISAM
1. 不支持事务、不支持行级锁，只支持并发插入的表锁，主要用于高负载的select2. 支持三种不同的存储结构：静态、动态、压缩
Регулировка параметров параллелизма MySQL
Параметры параллелизма MySQL
max_connections 最大连接数，默认151back_log 短时间内处理大量连接，可适当增大table_open_cache 控制所有SQL执行线程可打开表缓存的数量，受其他参数制约thread_cache_size 控制缓存客户服务线程数量，加快数据库连接速度，根据threads_created/connections来衡量是否合适innodb_lock_wait_timeout 控制事务等待行锁时间，默认50ms
Оптимизация памяти для улучшения фоновой службы MySQL
Оптимизация памяти MyISAM
#修改相应服务器位置的配置文件 my.cnfkey_buffer_size决定myisam索引块缓存区的大小，直接影响表的存取效率，建议1/4可用内存read_buffer 读缓存write_buffer 写缓存
Оптимизация памяти InnoDB
innodb_buffer_pool_size 存储引擎表数据和索引数据的最大缓存区大小innodb_old_blocks_pct LRU算法 决定old sublist的比例innodb_old_blocks_time LRU算法 数据转移间隔时间
Оптимизация приложений
Причина использования
数据的重要性mysql 服务及自身性能瓶颈保证大型系统稳定可靠运行
Оптимизация
使用连接池减少对 mysql 的真实连接a. 避免相同数据重复执行（查询缓存）b. 使用 mysql 缓存（sql 缓存）负载均衡a. LVS 分布式b. 读写分离（主主复制、主从复制保证数据一致性
Объяснение оптимизации MySQL в этом выпуске здесь первым.Конечно, план оптимизации — это гораздо больше.Вы также можете активно обсуждать в области сообщений.Я надеюсь, что эта статья может сыграть положительную роль в вашем ежедневном процессе разработки. .
Поднимите палец вверх, чтобы доказать, что вы все еще любите меня.