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

---

предисловие

Друзья, мы снова встречаемся. В прошлой статье мы говорили о методе MySQL sub-database и sub-table. В этой статье мы смоделируем метод оптимизации массивных данных в MySQL для предыдущей статьи. Статей много. , Я предлагаю вам поставить лайк , комментарии, избранное, подписаться и смотреть медленно

---

Напоминание: Ниже приведен текст этой статьи, дело только для справки.

1. Подготовьте данные таблицы

Построим таблицу пользователей, поля в таблице это ID пользователя, имя пользователя, адрес, время создания записи, как показано на рисунке

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

CREATE TABLE `t_user` (
  `id` int NOT NULL,
  `user_name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DELIMITER ;;
CREATE PROCEDURE user_insert()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i<1000000
DO
INSERT INTO t_user(id, user_name, address,  create_time) VALUES (i, CONCAT('mayun',i), '浙江杭州', now());
SET i=i+1;
END WHILE ;
commit;
END;;
CALL user_insert();

После вставки посмотрим на количество гистограмм

2. Метод оптимизации

1. Оптимизация поисковых запросов

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

• Когда лимит 1000

• Когда лимит 10000

• Когда лимит 100000

• Когда лимит 1000000

Видно, что чем больше предельное значение, тем больше времени занимает.Это всего один миллион данных.А если это десятки миллионов или 100 миллионов?

Хорошо, без глупостей, давайте немедленно оптимизируем пейджинг

• Оптимизация подзапросов

Видно, что эффективность значительно улучшилась по сравнению с предыдущим пределом 1000000 за 0,218 с.

• Разбиение на страницы с помощью JOIN

Видно, что эффективность 0,218 с также значительно улучшилась по сравнению с предыдущим пределом в 1000000.

• Используйте самый большой идентификатор из предыдущего запроса

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

• Пагинация по ID по псевдоколонке

Затем вы можете открыть несколько потоков для выполнения операций пакетного запроса для наиболее эффективных операторов запроса. 0~10000, 10001-20000.... Таким образом, полный объем данных может быть быстро запрошен и синхронизирован с кэшем.

Сводка по оптимизации пейджинга:Оптимизация запроса с использованием самого большого идентификатора предыдущего запроса является наиболее эффективным методом, но этот метод подходит только для операции нажатия на следующую страницу.Для синхронизации полных данных рекомендуемый метод использует псевдостолбцы для разбиения идентификатора на страницы и затем откройте несколько потоков для запроса одновременно, загрузите полный объем данных в кеш, и интервьюер спросит вас, какБыстро извлекайте массивные данные и загружайте их в кешВы должны знать, что ответить.

2. Оптимизация обычного индекса

Сначала посмотрите на эффективность запроса без оптимизации индекса

Вы можете видеть, что индекс в настоящее время не используется.После использования 0,305S, давайте посмотрим на результат после добавления индекса.

• Обычная оптимизация индекса

Нужно только 0,024 с, мы можем ОБЪЯСНИТЬ, чтобы увидетьВидно, что эффективность запросов значительно повышается после использования обычного индекса

3. Оптимизация составного индекса

Когда используются составные индексы? Зачем это использовать? Обсудив эти две проблемы, давайте сначала поговорим о том, когда следует использовать составные индексы.

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

Здесь мы предлагаем составной индекс

Когда MySQL строит составной индекс, фактически создаются три индекса (имя_пользователя), (имя_пользователя, адрес) и (имя_пользователя, адрес, время_создания).Все мы знаем, что каждый дополнительный индекс увеличивает накладные расходы на операции записи и накладные расходы дискового пространства. Для таблиц с массивными данными это немалые накладные расходы, поэтому вы обнаружите, что здесь мы используем составные индексы, чтобы уменьшить накладные расходы на операции записи и дисковое пространство.

Когда мы выбираем user_name, address, create_time из t_user, где user_name=xx и address = xxx, MySQL может напрямую получать данные, обходя индекс, не возвращаясь к таблице, что сокращает количество случайных операций ввода-вывода. Поэтому в реальном практическом применении это индекс покрытия, который является одним из основных методов оптимизации для повышения производительности составного индекса.

4. Оптимизация SQL-запросов

1. Избегайте использования ИЛИ, см. примеры

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

2. Не используйте как '%xx' % недействительность индекса слева

3. Принцип крайнего левого сопоставления не соблюдается при использовании составных индексов.ref: Этот тип соединения возникает только тогда, когда в запросе используются ключи, которые не являются уникальными или первичными ключами, или частями этих типов (например, с использованием крайнего левого префикса). Отсутствие значения указывает на то, что принцип крайнего левого префикса не используется.

Давайте посмотрим на другой пример, используя крайний левый префикс4. Не разрешайте неявное преобразование типов данных

См. следующие два примера 5. Не используйте not, , != в поле индекса, что приведет к сбою индекса. 6. Декомпозиция связанных запросов Например это предложение

можно разложить на

7. Маленький стол управляет большим столом То есть небольшие наборы данных управляют большими наборами данных. Например, возьмем в качестве примера таблицы t_user и t_order, эти две таблицы связаны полем id t_user.

当 t_order表的数据集小于t_user表时,用 in 优化 exist,使用 in,两表执行顺序是先查 t_order 表,再查t_user表
select * from t_user where id in (select user_id from t_order)
 
当 t_user 表的数据集小于 t_order 表时，用 exist 优化 in,使用 exists,两表执行顺序是先查 t_user  表,再查 t_order  表
select * from t_user where exists (select 1 from B where t_order.user_id= t_user.id)

5. Оптимизация сделки

Во-первых, поймите уровень изоляции транзакций.База данных определяет четыре уровня изоляции:

1. Сериализуемый: он может избежать возникновения грязных чтений, неповторяемых чтений и виртуальных чтений. (сериализовать)
2. Повторяемое чтение: позволяет избежать грязных чтений и неповторяющихся чтений. (повторяемое чтение)
3. Чтение зафиксировано: грязных чтений можно избежать (чтение зафиксировано).
4. Чтение незафиксированного: самый низкий уровень, ни одно из вышеперечисленных условий не может быть гарантировано. (читай незавершенный)

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

6. Оптимизация производительности базы данных

Включить кэш запросов

• Перед анализом запроса, если кеш запросов открыт, MySQL проверяет, соответствует ли запрос данным в кеше запросов. Если текущий запрос попадает в кеш запросов, результат в кеше будет возвращен сразу после однократной проверки разрешений пользователя. В этом случае запрос не будет разобран, план выполнения не сгенерирован и не будет выполнен. MySQL хранит кеш в справочной таблице (не следует понимать как таблицу, ее можно рассматривать как структуру данных, аналогичную HashMap), через индекс хеш-значения, это хэш-значение передается через сам запрос, текущая база данных в запрашиваемый номер версии протокола клиента и другую информацию, которая может повлиять на результат. Таким образом, разница между двумя запросами в любом символе (например, пробел, комментарий) приведет к промаху кеша.

• Если запрос содержит какие-либо определяемые пользователем функции, хранимые функции, пользовательские переменные, временные таблицы и системные таблицы в библиотеке mysql, результаты запроса не будут кэшироваться. Например, функции NOW() или CURRENT_DATE() вернут разные результаты запроса из-за разного времени запроса. Например, оператор запроса, содержащий CURRENT_USER или CONNECION_ID(), вернет разные результаты из-за разных пользователей. Кэшировать такие результаты запроса нет никакого смысла.

• Так как это кеш, он потерпит неудачу Когда произойдет сбой кеша запросов? Система кэширования запросов MySQL отслеживает каждую таблицу, участвующую в запросе, и если эти таблицы (данные или структура) изменяются, все кэшированные данные, связанные с этой таблицей, становятся недействительными. Из-за этого при любой операции записи MySQL должен аннулировать все кэши для соответствующей таблицы. Если кэш запросов очень велик или фрагментирован, эта операция может привести к значительному потреблению ресурсов системы или даже к зависанию системы на некоторое время. И дополнительный расход кеша запросов к системе не только в операциях записи, но и в операциях чтения:

   　　任何的查询语句在开始之前都必须经过检查，即使这条SQL语句永远不会命中缓存
   　　如果查询结果可以被缓存，那么执行完成后，会将结果存入缓存，也会带来额外的系统消耗
  

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

Пакетная вставка вместо циклической одной вставки Разумное пространство для контроля кэша, в общем, его размер установлен на десятки Trillo Может контролировать, необходимо ли кэшировать оператор запроса через sql_cache и sql_no_cache Окончательный совет нелегко открывает кэш запроса, особенно интенсивные приложения. Если вы не можете помочь, вы можете настроить Query_Cache_Type для запроса. В это время будет кэшировать только запросы, соединяющее SQL_Cache, и другие запросы не будут, которые могут быть очень свободны для контроля, какие запросы должны быть кэшированы. Конечно, сама система кэша запроса очень сложная. Это всего лишь небольшая часть обсуждения здесь, другие более глубокие темы, такие как кэш использует память? Как контролировать мусор памяти? Транзакция оказывает влияние на кэш запроса и т. Д. Читатели могут прочитать соответствующую информацию самостоятельно, вот право взять нефть. ​Разбор и предварительная обработка

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

7. Оптимизация параметров ядра системы

#基础配置
datadir=/data/datafile
socket=/var/lib/mysql/mysql.sock
log-error=/data/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
character_set_server=utf8
#允许任意IP访问
bind-address = 0.0.0.0
#是否支持符号链接，即数据库或表可以存储在my.cnf中指定datadir之外的分区或目录，为0不开启
#symbolic-links=0
#支持大小写
lower_case_table_names=1
#二进制配置
server-id = 1
log-bin = /data/log/mysql-bin.log
log-bin-index =/data/log/binlog.index
log_bin_trust_function_creators=1
expire_logs_days=7
#sql_mode定义了mysql应该支持的sql语法，数据校验等
#mysql5.0以上版本支持三种sql_mode模式：ANSI、TRADITIONAL和STRICT_TRANS_TABLES。
#ANSI模式：宽松模式，对插入数据进行校验，如果不符合定义类型或长度，对数据类型调整或截断保存，报warning警告。
#TRADITIONAL模式：严格模式，当向mysql数据库插入数据时，进行数据的严格校验，保证错误数据不能插入，报error错误。用于事物时，会进行事物的回滚。
#STRICT_TRANS_TABLES模式：严格模式，进行数据的严格校验，错误数据不能插入，报error错误。
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
#InnoDB存储数据字典、内部数据结构的缓冲池，16MB已经足够大了。
innodb_additional_mem_pool_size = 16M
#InnoDB用于缓存数据、索引、锁、插入缓冲、数据字典等
#如果是专用的DB服务器，且以InnoDB引擎为主的场景，通常可设置物理内存的60%
#如果是非专用DB服务器，可以先尝试设置成内存的1/4
innodb_buffer_pool_size = 4G
#InnoDB的log buffer，通常设置为 64MB 就足够了
innodb_log_buffer_size = 64M
#InnoDB redo log大小，通常设置256MB 就足够了
innodb_log_file_size = 256M
#InnoDB redo log文件组，通常设置为 2 就足够了
innodb_log_files_in_group = 2
#共享表空间:某一个数据库的所有的表数据，索引文件全部放在一个文件中，默认这个共享表空间的文件路径在data目录下。默认的文件名为:ibdata1 初始化为10M。
#独占表空间:每一个表都将会生成以独立的文件方式来进行存储，每一个表都有一个.frm表描述文件，还有一个.ibd文件。其中这个文件包括了单独一个表的数据内容以及索引内容，默认情况下它的存储位置也是在表的位置之中。
#设置参数为1启用InnoDB的独立表空间模式，便于管理
innodb_file_per_table = 1
#InnoDB共享表空间初始化大小，默认是 10MB，改成 1GB，并且自动扩展
innodb_data_file_path = ibdata1:1G:autoextend
#设置临时表空间最大4G
innodb_temp_data_file_path=ibtmp1:500M:autoextend:max:4096M
#启用InnoDB的status file，便于管理员查看以及监控
innodb_status_file = 1
#当设置为0，该模式速度最快，但不太安全，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。
#当设置为1，该模式是最安全的，但也是最慢的一种方式。在mysqld 服务崩溃或者服务器主机crash的情况下，binary log 只有可能丢失最多一个语句或者一个事务。
#当设置为2，该模式速度较快，也比0安全，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。
innodb_flush_log_at_trx_commit = 1
#设置事务隔离级别为 READ-COMMITED，提高事务效率，通常都满足事务一致性要求
#transaction_isolation = READ-COMMITTED
#max_connections：针对所有的账号所有的客户端并行连接到MYSQL服务的最大并行连接数。简单说是指MYSQL服务能够同时接受的最大并行连接数。
#max_user_connections : 针对某一个账号的所有客户端并行连接到MYSQL服务的最大并行连接数。简单说是指同一个账号能够同时连接到mysql服务的最大连接数。设置为0表示不限制。
#max_connect_errors：针对某一个IP主机连接中断与mysql服务连接的次数，如果超过这个值，这个IP主机将会阻止从这个IP主机发送出去的连接请求。遇到这种情况，需执行flush hosts。
#执行flush host或者 mysqladmin flush-hosts，其目的是为了清空host cache里的信息。可适当加大，防止频繁连接错误后，前端host被mysql拒绝掉
#在 show global 里有个系统状态Max_used_connections,它是指从这次mysql服务启动到现在，同一时刻并行连接数的最大值。它不是指当前的连接情况，而是一个比较值。如果在过去某一个时刻，MYSQL服务同时有10
00个请求连接过来，而之后再也没有出现这么大的并发请求时，则Max_used_connections=1000.请注意与show variables 里的max_user_connections的区别。#Max_used_connections / max_connections * 100% ≈ 85%
max_connections=600
max_connect_errors=1000
max_user_connections=400
#设置临时表最大值，这是每次连接都会分配，不宜设置过大 max_heap_table_size 和 tmp_table_size 要设置一样大
max_heap_table_size = 100M
tmp_table_size = 100M
#每个连接都会分配的一些排序、连接等缓冲，一般设置为 2MB 就足够了
sort_buffer_size = 2M
join_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
#建议关闭query cache，有些时候对性能反而是一种损害
query_cache_size = 0
#如果是以InnoDB引擎为主的DB，专用于MyISAM引擎的 key_buffer_size 可以设置较小，8MB 已足够
#如果是以MyISAM引擎为主，可设置较大，但不能超过4G
key_buffer_size = 8M
#设置连接超时阀值，如果前端程序采用短连接，建议缩短这2个值，如果前端程序采用长连接，可直接注释掉这两个选项，是用默认配置(8小时)
#interactive_timeout = 120
#wait_timeout = 120
#InnoDB使用后台线程处理数据页上读写I/0请求的数量,允许值的范围是1-64
#假设CPU是2颗4核的，且数据库读操作比写操作多，可设置
#innodb_read_io_threads=5
#innodb_write_io_threads=3
#通过show engine innodb status的FILE I/O选项可查看到线程分配
#设置慢查询阀值，单位为秒
long_query_time = 120
slow_query_log=1 #开启mysql慢sql的日志
log_output=table,File #日志输出会写表，也会写日志文件，为了便于程序去统计，所以最好写表
slow_query_log_file=/data/log/slow.log
##针对log_queries_not_using_indexes开启后，记录慢sql的频次、每分钟记录的条数
#log_throttle_queries_not_using_indexes = 5
##作为从库时生效,从库复制中如何有慢sql也将被记录
#log_slow_slave_statements = 1
##检查未使用到索引的sql
#log_queries_not_using_indexes = 1
#快速预热缓冲池
innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1
#打印deadlock日志
innodb_print_all_deadlocks=1

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

8. Оптимизация полей таблицы

Многие системы не рассматривали проблему разделения полей таблицы в начале, потому что разделение принесет различные сложности логики, развертывания, эксплуатации и обслуживания.Как правило, таблицы с целочисленными значениями меньше десятков миллионов, а строки main table меньше 5 млн. На самом деле, во многих случаях производительность отдельной таблицы MySQL все еще имеет много возможностей для оптимизации, и она даже может нормально поддерживать объем данных более 10 миллионов:

Давайте посмотрим непосредственно на то, как оптимизировать поля

1. Попробуйте использовать TINYINT, SMALLINT, MEDIUM_INT в качестве целочисленных типов вместо INT и добавьте UNSIGNED, если они неотрицательны.
2. В одной таблице не должно быть слишком много полей, рекомендуется не более 15.
3. Попробуйте использовать TIMESTAMP вместо DATETIME
4. Используйте enum или integer вместо строкового типа
5. Длина VARCHAR выделяет только то пространство, которое действительно необходимо.
6. Избегайте использования полей NULL, которые трудно оптимизировать с помощью запросов и занимают дополнительное место в индексе.
7. Используйте целое число для хранения IP

9. Общие методы оптимизации в распределенных сценариях

1. обновить оборудование

Масштабирование, это не так много, чтобы сказать.В зависимости от того, является ли MySQL интенсивным ЦП или интенсивным вводом-выводом, производительность MySQL может быть значительно улучшена за счет увеличения ЦП и памяти и использования SSD.

2. разделение чтения-записи

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

3. использовать кеш

   Кэширование может происходить на следующих уровнях:

   MySQL Internals: введены соответствующие настройки в System Kernel Parameter Optimization.

   Уровень доступа к данным: например, MyBatis кэширует операторы SQL, в то время как Hibernate может быть точным до одной записи Объекты, кэшированные здесь, в основном являются постоянными объектами Persistence Object

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

   Веб-уровень: кэширование веб-страниц

   Браузерный клиент: кэш на стороне клиента

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

   Сквозная запись: после того, как данные записываются в базу данных, кеш одновременно обновляется для поддержания согласованности между базой данных и кешем. Именно так работают большинство современных сред кэширования приложений, таких как Spring Cache. Эта реализация очень простая, хорошая синхронизация, но средняя эффективность.

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

4. Горизонтальный сплит: я упоминал об этом в своей прошлой статье, поэтому не буду повторяться здесь.

Суммировать

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

Примечание автора

Впереди долгий путь.Я готова идти вверх и вниз с вами.Большое спасибо за ваши лайки, подборки и комментарии от красивых парней и красивых девушек.До встречи в следующем выпуске.

Следуйте за мной, чтобы пройти путь роста архитектора

Адрес источника:Нажмите здесь, чтобы просмотреть исходный код.