Привет всем, я Сяо Цай, Сяо Цай, который хочет быть Цай Буцаем в интернет-индустрии. Она может быть мягкой или жесткой, как она мягкая, а белая проституция жесткая!
Черт~ Не забудьте поставить мне тройку после прочтения!
Эта статья в основном знакомитЧто вы должны знать о разработке Mysql и собеседованиях
Эта статья длинная и разделена на две части (коллекционная, пыль не ешь)
При необходимости вы можете обратиться к
Если это поможет, не забудьтекак❥
【Статьи по Теме】
1. Архитектура MySQL
1) Введение в MySQL
MySQL — это система управления реляционными базами данных, разработанная MYSQL AB в Швеции и в настоящее время принадлежащая Oracle.
MySQL — это ассоциативная система управления базами данных, в которой данные хранятся в разных таблицах, а не в одном большом хранилище, что повышает скорость и гибкость.
Mysql имеет открытый исходный код и может быть настроен. Он принимает протокол GPL. Вы можете изменить исходный код для разработки собственной системы MySQL.
MySQL поддерживает большие базы данных. Может обрабатывать большие базы данных с десятками миллионов записей. MySQL разрешен в нескольких системах и поддерживает несколько языков. Эти языки программирования включают, среди прочего, C, C++, Python, Java, Perl, PHP, Eiffel, Ruby и Tcl.
MySQL поддерживает большие базы данных, хранилища данных с 5000 записей, 32-разрядные системные файлы таблиц могут поддерживать до 4 ГБ, а 64-разрядные системы поддерживают максимальный размер файла таблицы 8 ТБ.
2) Конфигурационный файл MySQL
-
binlog(二进制日志)
Для репликации master-slave и резервного восстановления: все записи операций хранятся в binlog, который можно использовать для восстановления. Эквивалент AOF в Redis, конфигурация binlog в my.ini (по умолчанию выключено)Как включить:
[mysqld]
log-bin = mysql-bin
binlog-format = row
-
Error log(错误日志)
По умолчанию он отключен.Обычно используется для записи ошибок при запуске сервера базы данных, перезапуске и репликации master-slave.Подробности журнала хранятся в файле, который удобно читать администраторам баз данных и разработчикам эксплуатации и обслуживания.Как включить:
[mysqld]
log-error=/data/mysql_error.log
-
慢查询日志log
По умолчанию отключено. Оператор SQL, который записывает запрос, если он включен, снизит общую производительность MySQL, поскольку ведение журнала также требует использования системных ресурсов.Как включить:
[mysqld]
slow_query_log = ON
slow_query_log_file = /usr/local/mysql/data/slow.log //linux
long_query_time = 1
-
数据文件
окна:..\mysql-8.0.19-winx64\dataХранить файлы базы данных в каталоге
линукс:
путь по умолчанию/var/lib/mysql(Мой-огромный.cnf в /usr/share/mysql/ можно изменить в файле конфигурации) Каждый каталог представляет собой библиотеку с тем же именем.
Метод хранения Myisam: - frm файл (фреймворк): хранить структуру таблицы
- myd файл (данные): хранить данные таблицы
- файл myi (индекс): сохранить индекс таблицы
Способ хранения innodb: - ibdata1: Механизм Innodb хранит данные всех таблиц в /usr/share/mysql/ibdata1, а файл frm хранится в пакете с тем же именем, что и библиотека.
- frm файл: сохранить структуру таблицы
-
配置方式- окна:my.iniконфигурационный файл
- линукс:my.cnfконфигурационный файл
3) Управление пользователями и полномочиями MySQL
-
MysSQL用户管理-
Создать пользователя
create user cbuc identified by '123456' -
О пользовательской таблице
select host,user,select_priv,insert_priv,drop_priv from mysql.user;
хозяин:Указывает тип соединения
Пользователь:Указывает имя пользователя
select_priv, insert_priv, drop_priv и т. д.:Разрешения, которые есть у этого пользователя -
Установка пароля
--- Изменить пароль текущего пользователя
set password = password('123456')
--- Изменить пароль пользователя
update mysql.user set password = password('123456') where user = 'cbuc' -
изменить пользователя
--- Изменить имя пользователя:
update mysql.user set user = 'cbuc' where user='c1';
--- Все изменения через пользовательскую таблицу должны использовать эту команду, чтобы они вступили в силу
flush privileges; -
удалить пользователей
--- не пройтиdelete from user t1 where t1.user='cbuc'Удалить, система сохранит остаточную информацию
drop user cbuc;
-
Создать пользователя
-
权限管理-
Предоставленное разрешение:
--- Если окажется, что такого пользователя нет, пользователь будет создан напрямую
предоставить разрешение 1, разрешение 2,..., разрешение n на имя базы данных.имя таблицы на имя пользователя@адрес пользователя, определяемый «паролем»
--- Дайте пользователю cbuc разрешение добавлять, удалять, изменять и проверять таблицу
grant select,insert,delete,update on db_crm.* to cbuc@localhost; -
Отозвать разрешение:
--- Если была назначена таблица всей базы данных, все разрешения всей базы данных и всей таблицы будут восстановлены
отозвать разрешение 1, разрешение 2,..., разрешение n на имя базы данных.имя таблицы от имени пользователя@адрес пользователя
revoke all privileges on mysql.* from cbuc@localhost -
Отозвать разрешение:
--- Просмотр текущих разрешений пользователя
show grants;
--- Просмотр глобальных разрешений пользователя
select * from mysql.user;
--- Просмотр разрешений библиотеки пользователя
select * from mysql.db;
--- просмотреть список привилегий пользователя
select * from mysql.tables_priv;
-
Предоставленное разрешение:
4) другая конфигурация MySQL
大小写问题
Системы Windows по умолчанию нечувствительны к регистру, но системы Linux чувствительны к регистру.
- 0 (по умолчанию):Деликатный случай
- 1:Нечувствительный к регистру. Созданные таблицы и базы данных хранятся на диске в нижнем регистре, а операторы SQL преобразуются в нижний регистр для поиска в БД таблицы.
- 2:Созданная таблица и БД сохраняются в соответствии с форматом выписки, и все поисковые запросы преобразуются в нижний регистр.
SHOW VARIABLES LIKE '%lower_case_table_names%';
настраивать:
set lower_case_table_names = 1; #此变量是只读权限，需要在配置文件中修改
- Добавьте в my.inni/my.cnf
[mysqld]
lower_case_table_names = 1
- Перезагрузите сервер (Преобразуйте исходную базу данных и таблицу в нижний регистр перед перезапуском, иначе имя базы данных не будет найдено после изменения)
sql_mode
sql_modeЭто переменная, которую легко игнорировать. Значение по умолчанию равно null. В этом параметре могут быть разрешены некоторые недопустимые операции, например разрешение вставки некоторых недопустимых данных. В рабочей среде это значение должно быть установлено в строгий режим, поэтому также необходимо установить базу данных среды разработки и тестирования, чтобы можно было найти проблемы на этапах разработки и тестирования.
Общие настройки:
[mysqld]
sql-mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
5) Механизм хранения MySQL
查看引擎
show engines;
Видно, что механизм хранения по умолчаниюInooDB
-
各引擎简介-
Механизм хранения InnoDB:
InnoDB — MySQL по умолчанию事务型引擎, предназначенный для обработки большого количества краткосрочных транзакций. Если нет особых причин для использования другого механизма хранения, предпочтение следует отдавать механизму InnoDB. имеют行级锁,外键,事务и другие преимущества, подходящие для高并发情况. -
Механизм хранения MyISAM:
MyISAM предоставляет большое количество функций, включая полнотекстовое индексирование, сжатие, пространственные функции (ГИС) и т. д., но MyISAM不支持事务和行级锁（MyISAM改表时会将整个表全锁住）, дефект:崩溃后无法安全恢复. -
Архивный движок:
механизм хранения архива只支持 insert 和 selectиндексы не поддерживаются до MySQL 5.1. Архивные таблицы подходят для ссылок на журналы и классы сбора данных.适合低访问量大数据等情况. -
Двигатель черной дыры
Движок Blackhole не реализует никакого механизма хранения, он отбрасывает все вставленные данные без сохранения. Но сервер будет регистрировать таблицу Blackhole, поэтому ее можно использовать для копирования данных в резервную базу данных или просто для записи в журнал. Однако этот метод применения столкнется со многими проблемами, поэтому не рекомендуется. -
движок CSV
Механизм CSV может обрабатывать обычные файлы CSV как таблицы MySQL, но не поддерживает индексы. Он может быть очень полезен как механизм обмена данными. Сохраненные данные можно прочитать непосредственно в операционной системе, с помощью текстового редактора или Excel. -
ПамятьДвигатель
Использование таблицы памяти очень полезно, если вам нужно быстро получить доступ к данным, и они не будут изменены и потеряны после перезапуска. Таблицы памяти как минимум на порядок быстрее, чем таблицы MyISAM. -
Объединенный движок
Механизм Federated — это прокси для доступа к другим серверам MySQL, и хотя этот механизм обеспечивает хорошую гибкость между серверами, он часто создает проблемы и поэтому по умолчанию отключен.
-
Механизм хранения InnoDB:
-
MyISAM和InnoDB比较Первичным ключом InnoDB является кластеризованный индекс, и эффективность добавления, удаления, изменения и выполнения запросов на основе кластеризованного индекса очень высока.
Кластерный индекс:Последовательная структура реального хранилища соответствует физической организации хранилища данных.
Некластеризованный индекс:Физический порядок записей не обязательно связан с логическим порядком и не имеет ничего общего с физической структурой хранения данных.
Во-вторых, анализ оптимизации индекса
1) Снижение производительности/длительное время выполнения SQL
-
Слишком много данных запроса
Если его можно разобрать, то можно разобрать, и условного фильтра должно быть как можно меньше. -
Слишком много соединений
Принцип JOIN: просмотрите все данные в таблице B с каждым фрагментом данных в таблице A, поэтому попробуйте сначала отфильтровать, а затем связать -
индекс не используется
индекс для列Создайте индекс, но невозможно построить индекс для каждого столбца.
Больше индексов не всегда лучше. При обновлении данных индекс будет скорректирован, что также приведет к снижению производительности.
И MySQL не использует все индексы, а выбирает только один индекс в соответствии со своим алгоритмом. Поэтому важно построить - Настройка сервера и настройка различных параметров (буферизация, количество потоков)
2) ПРИСОЕДИНЯЙТЕСЬ к запросу
-
Порядок выполнения SQL
人工读取顺序：SELECT (DISTINCT)
< select_list >
FROM
< left_table > < join_type >
JOIN < right_table > ON < join_condition >
WHERE
< where_condition >
GROUP BY
< group_by_list >
HAVING
< having_condition>
ORDER BY
< order_by_condition >
LIMIT < limit_number >引擎执行顺序：FROM < left_table >
ON < join_condition >
< join_type > JOIN < right_table >
WHERE < where_condition >
GROUP BY < group_by_list >
HAVING < having_condition >
SELECT
(DISTINCT) < select_list >
ORDER BY < order_by_condition >
LIMIT < limit_number >
总结：
-
общий/исключительный
Есть две таблицы: таблица сотрудников Employee и таблица отделов Dept. В таблице сотрудников есть поле Dept, которое соответствует идентификатору первичного ключа таблицы отделов.共有：Данные, удовлетворяющие employee.deptId = dept.id, называются общими для двух таблиц.独有：Данные employee.deptId dept.id называются уникальными для таблицы сотрудников. -
Семь видов JOIN
Есть две таблицы, таблица t1 — таблица сотрудников emp, таблица t1 — таблица отделов.
1、Таблица t1 и таблица t2 совместно используют (inner join)
select * from emp t1 inner join dept t2 on t1.deptId = t2.id
2、Общий для таблицы t1 и таблицы t2 + уникальный для таблицы t1 (left join)
select * from emp t1 left join dept t2 on t1.deptId = t2.id
3、Общий для таблицы t1 и таблицы t2 + уникальный для таблицы t2 (right join)
select * from emp t1 right join dept t2 on t1.deptId = t2.id
4、Уникальный для таблицы t1 (левое соединение... где t2.id равно нулю)
select * from emp t1 left join dept t2 on t1.deptId = t2.id where t2.id is null
5.Уникальный для таблицы t2 (правое соединение… где t1.id равен нулю)
6.таблица t1 и таблица t2 имеют (union)ПОЛНОЕ СОЕДИНЕНИЕ не поддерживается в MySQL
СОЮЗ:Дедуплицированные данные
СОЮЗ ВСЕХ:Не выполнять дедупликацию данных
select * from emp t1 left join dept t2 on t1.deptId = t2.id
union
select * from emp t1 right join dept t2 on t1.deptid = t2.id
7、Уникальный для таблицы t1 + Уникальный для таблицы t2 (union)
select * from emp t1 left join dept t2 on t1.deptId = t2.id where t2.id is null
UNION
select * from emp t1 right join dept t2 on t1.deptId = t2.id
where t1.deptId is null
3) Введение в индексирование
Официальное определение индекса в MySQL: Индекс — это структура данных, которая помогает MySQL эффективно получать данные. Суть индекса можно получить:Индексы — это структуры данных.Цель состоит в том, чтобы повысить эффективность запросов, которую можно сравнить со словарем.
-
просто понимается как"отсортированная структура данных быстрого поиска":
Помимо данных,Системы баз данных также поддерживают структуры данных, удовлетворяющие определенным алгоритмам поиска., эти структуры данных каким-то образом ссылаются (указывают) на данные.вставьте сюда описание изображения
Слева находится таблица данных, есть два столбца по семь данных, а крайний левый - физический адрес записи данных. Чтобы ускорить поиск Col2, можно сохранить двоичное дерево поиска, показанное справа. , Каждый узел содержит значение индекса и указатель на соответствующий указатель физического адреса записи данных, так что соответствующие данные могут быть получены с определенной сложностью с помощью двоичного поиска, чтобы быстро получить записи, которые соответствуют условия.
Бинарное дерево:Двоичное дерево, вероятно, будет несбалансированным с обеих сторон.
B-дерево:Он будет автоматически регулироваться в зависимости от ситуации с обеих сторон, так что два конца будут бесконечно близки к сбалансированному состоянию, что может сделать производительность наиболее стабильной. но插入/修改Когда операций слишком много, B-TREE будет постоянно корректировать баланс и снижать производительность. Вообще говоря, сам индекс тоже очень большой, и хранить его весь в памяти невозможно, поэтому индекс часто хранится на диске в виде индексных файлов.
Индекс, на который мы обычно ссылаемся, если не указано иное, относится кB-дерево(многоходовое дерево поиска, не обязательно бинарное) структурированный индекс. Среди них кластеризованный индекс, вторичный индекс, покрывающий индекс, составной индекс, префиксный индекс и уникальный индекс — все они по умолчанию используют индексы дерева B+, а также хэш-индексы.
-
索引优势- Подобно библиографическому указателю библиотечного резюме, он повышает эффективность поиска данных и снижает затраты на ввод-вывод базы данных.
- Сортировка данных по столбцам индекса снижает затраты на сортировку данных и потребление ЦП.
-
索引劣势- По сути, индекс — это тоже таблица, которая сохраняет первичный ключ и поля индекса и указывает на записи таблицы сущностей, поэтому столбец индекса также занимает место.
- Хотя индексы значительно улучшают скорость запросов, ониУменьшить скорость обновления таблицы, такие как INSERT, UPDATE и DELETE в таблице, потому что при обновлении таблицы MySQL не только сохраняет данные, но и сохраняет индексный файл.Каждый раз, когда обновляется поле с добавленным индексным столбцом, значение ключа изменяется обновление будет скорректировано.После индекса информации.
- Индексы — это только один из факторов повышения эффективности.Если в вашем MySQL большое количество таблиц, вам нужно потратить время на поиск лучшего индекса или оптимизацию оператора запроса.
-
索引结构-
Индекс BTree:
真实数据存在于叶子节点, то есть 3, 5, 9, 10, 13, 15, 28, 29, 36, 60, 75, 79, 90, 99.非叶子节点不存储真实的数据，只存储指引搜索方向的数据项, такие как 17, 35 фактически не существуют в техпаспорте.вставьте сюда описание изображения
【Процесс поиска】
Если вы хотите найти данные 29, то сначала в память будет загружен блок диска 1. В это время происходит ввод-вывод, и в памяти используется двоичный поиск, чтобы определить, что 29 находится между 17 и 35, и заблокирован указатель P2 дискового блока 1. Время памяти Поскольку оно очень короткое (по сравнению с вводом/выводом диска) и может быть проигнорировано, дисковый блок 3 загружается с диска в память по дисковому адресу диск указателя P2 дискового блока 1, и происходит второй IO, 29 находится между 26 и 30, блокировка указателя P2 дискового блока 3, загрузка дискового блока 8 в память через указатель, происходит третий IO, и в то же время сделать бинарный поиск в памяти до 29, завершить запрос, всего три IO -
Индекс B+дерева:
вставьте сюда описание изображения
Данные второго уровня B+Tree не могут быть извлечены напрямую, а используются только для индексации. В случае ограниченной памяти эффективность запросов выше, чем у BTree.
Второй уровень BTree можно вывести напрямую, древовидная структура относительно тяжелая и имеет преимущество, когда память бесконечна.
【B+Tree 和 BTree 的区别】
1)В случае ограниченной памяти B+Tree всегда лучше, чем BTree, и наоборот для неограниченной памяти.
2)Ключевые слова и записи B-дерева объединяются, а листовые узлы можно рассматривать как внешние узлы и не содержат никакой информации; в листовом узле B+ China у вас есть только ключевые слова и индекс, указывающий на следующий узел. , а записи размещаются только в листовых узлах. (Запрос может выполнять две операции ввода-вывода)
3)В B-дереве время поиска записи ближе к корневому узлу меньше, пока ключевое слово найдено, можно определить существование записи; в то время как время поиска каждой записи в B+ дереве в основном то же самое, ему нужно перейти от корневого узла к листовому узлу, И в конечных узлах также сравниваются ключевые слова. С этой точки зрения производительность B-дерева кажется лучше, чем у B+-дерева, но в практических приложениях производительность B+-дерева лучше. Поскольку неконечные узлы дерева B+ не хранят фактические данные, каждый узел может содержать больше элементов, чем B, а высота дерева меньше, чем у дерева B, что позволяет сократить количество обращений к диску. Хотя B+-дерево требует больше сравнений для поиска записи, чем B-дерево, одно время доступа к диску эквивалентно сотням или тысячам операций сравнения памяти, поэтому производительность B+-дерева на практике может быть выше, а конечные узлы Дерево B+ Используйте указатели для связи друг с другом, чтобы облегчить последовательный обход (например, просмотр всех файлов в каталоге, всех записей в таблице и т. д.)
4)Стоимость чтения и записи на диск дерева B+ ниже, а количество операций чтения и записи ввода-вывода относительно меньше.
5)Эффективность запросов дерева B+ более стабильна. Так как нетерминал — это не узел, указывающий на содержимое файла, а только индекс ключевого слова в листовом узле. Таким образом, любой поиск по ключевому слову должен проходить по пути от корневого узла к конечному узлу. Следовательно, длина пути запроса по ключевому слову одинакова, что обеспечивает одинаковую эффективность запроса для всех данных.-
кластеризованный индекс
вставьте сюда описание изображения 好处：
Согласно порядку сортировки кластеризованного индекса, когда запрос отображает определенный диапазон данных, поскольку данные тесно связаны, базе данных не нужно извлекать данные из нескольких блоков данных, поэтому сохраняется множество операций ввода-вывода.限制：- Для базы данных MySQL только механизм данных InnoDB в настоящее время поддерживает кластерные индексы, а MyISAM не поддерживает кластерные индексы.
- Поскольку существует только один способ сортировки физического хранилища данных, каждая таблица MySQL может иметь только один кластеризованный индекс. Как правило, столпервичный ключ.
-
полнотекстовый полнотекстовый указатель
Полнотекстовое индексирование (также называемое полнотекстовым поиском) является ключевой технологией, используемой в настоящее время поисковыми системами. он может использовать
分词技术Разнообразные алгоритмы интеллектуально анализируют частоту и важность ключевых слов в тексте, а затем интеллектуально отфильтровывают нужные нам результаты поиска в соответствии с определенными правилами алгоритма.查询：
# 传统 LIKE 查询
select * from blink t1 where t1.content like '%菜%'
# 全文检索
select * from blink t1 where MATCH(title,content) AGAINST('菜')-
限制：До MySQL 5.6.4 он поддерживался только MyISAM, а после 5.6.4 его поддерживала только InnoDB, но официальная версия не поддерживает сегментацию китайских слов, и требуется сторонний подключаемый модуль сегментации слов.
-
Хэш-индекс
- Хэш-индекс поддерживается только механизмами памяти и NDB, а механизм памяти поддерживает его по умолчанию.
- Хэш-индекс, если несколько хэш-значений совпадают и происходит коллизия хэшей, индекс сохраняется в связанном списке.
- NoSql принимает эту структуру индекса.
RT-индекс
R-Tree редко используется в MySQL и поддерживает только структуру данных геометрии.Единственными механизмами хранения, которые поддерживают этот тип, являются MyISAM, bdb, InnoDB, ndb и архив. По сравнению с B-Tree преимущество R-Tree заключается в том, что查找.
-
Индекс BTree:
-
索引分类-
индекс первичного ключа
После установки первичного ключа база данных автоматически возобновит работу индекса, и InnoDB будет использовать кластеризованный индекс.语法：
# 随表一起创建
CREATE TABLE emp (
# 使用AUTO_INCREMENT关键字的列必须要有索引
ID int(10) UNSIGNED AUTO_INCREMENT
, NAME varchar(8)
, PRIMARY KEY(ID)
)
# 单独建主键索引
ALTER TABLE emp add PRIMARY KEY emp(id);
# 删除主键索引
ALTER TABLE emp drop PRIMARY KEY; # 修改主键索引前必须删除(drop)原索引，再新建(add)索引-
однозначный индекс
То есть индекс содержит только один столбец, а таблица может иметь несколько индексов с одним столбцом.语法：
# 随表一起创建
CREATE TABLE emp (
# 使用AUTO_INCREMENT关键字的列必须要有索引
ID int(10) UNSIGNED AUTO_INCREMENT
, EMP_NO varchar(8)
, NAME varchar(8)
, KEY(EMP_NO)
)
# 单独建单列索引
create index idx_emp_no on emp(EMP_NO)
# 删除单列索引
drop index idx_emp_no-
уникальный индекс
Значения индексируемых столбцов должны быть уникальными, но допускаются нулевые значения.
Для создания уникального индекса необходимо убедиться, что все значения уникальны (кроме null), при наличии повторяющихся данных будет сообщено об ошибке
# 随表一起创建
CREATE TABLE emp (
# 使用AUTO_INCREMENT关键字的列必须要有索引
ID int(10) UNSIGNED AUTO_INCREMENT
, EMP_NO varchar(8)
, NAME varchar(8)
, UNIQUE(EMP_NO)
)
# 单独建唯一索引
create unique index idx_emp_no on emp(EMP_NO)
# 删除主键索引
drop index idx_emp_no on emp-
составной индекс
Во время операций с базой данных составные индексы требуют меньше накладных расходов, чем индексы с одним значением (индексирование одних и тех же нескольких столбцов);
Составные индексы можно использовать, когда количество строк в таблице намного превышает количество столбцов индекса.
# 随表一起创建
CREATE TABLE emp (
# 使用AUTO_INCREMENT关键字的列必须要有索引
ID int(10) UNSIGNED AUTO_INCREMENT
, EMP_NO varchar(8)
, NAME varchar(8)
, key(EMP_NO，NAME)
)
#建立唯一索引是必须保证所有的值是唯一的(除了null)，若有重复数据，会报错
# 单独建唯一索引
create index idx_no_name on emp(EMP_NO,NAME)
# 删除主键索引
drop index idx_no_name on emp【基本语法】# 创建
alter < table_name > add [unique] index <index_name> on <column_name>
# 删除
drop index <index_name> on <table_name>
#查看
show index from <table_name>
#使用ALTER命令
#方式1：该语句添加一个主键，这意味着索引值必须是唯一的，且不能为null
alter table <table_name> add primary key <column_name>
#方式2：该语句添加一个唯一索引，值必须是唯一的(null外，null可能会出现很多次)
alter table <table_name> add unique key <column_name>
#方式3：该语句添加普通索引，索引值可以出现很多次
alter table <table_name> add index <index_name>(column_name)
#方式4：该语句指定了索引为FULLTEXT，用户全文索引
alter table <table_name> add FULLTEXT <index_name>(column_name) -
индекс первичного ключа
-
哪些情况需要建立索引- Первичный ключ автоматически создает уникальный индекс
- часто какУсловия запросаПоля должны быть проиндексированы (оператор после where)
- Поля, связанные с другими таблицами в запросе, взаимосвязи внешнего ключа индексируются
- Выбор одиночного ключа/составного индекса (склонность к созданию составного индекса при высоком уровне параллелизма)
- Поле для сортировки в запросе, если доступ к полю сортировки осуществляется через индекс, скорость сортировки значительно возрастет.
- Статистика или группировка полей в запросе
-
哪些情况不需要建立索引- слишком мало записей в таблице
- Таблицы, которые часто добавляются, удаляются и изменяются (поскольку при обновлении таблицы MySQL сохраняет не только данные, но и индексный файл)
- Поля, которые не используются, где условия не индексируются
- Поля таблицы, в которых данные повторяются и равномерно распределяются, поэтому следует индексировать только наиболее часто запрашиваемые и наиболее часто сортируемые столбцы данных. Обратите внимание, что если столбец данных содержит много дубликатов, его индексация практически неэффективна.
4) Анализ производительности
-
MySQL常见瓶颈-
CPU
Сравнивайте, связывайте, сортируйте и группируйте большие объемы данных в SQL (наибольшая нагрузкасравнивать) -
IO
Память экземпляра не может удовлетворить потребности кэширования данных или сортировки, что приводит к большому количеству физических IP-адресов. Выполнение запроса неэффективно, сканируется слишком много строк данных. -
Замок
Неправильные настройки блокировки приводят к блокировке потоков и снижению производительности. Тупик, перекрестный вызов ресурсов между потоками, что приводит к тупику, программа зависает.
服务器硬件的性能瓶颈：
top, free, iostat и vmstat для просмотра состояния производительности системы. -
CPU
-
Explain的使用использоватьExplainКлючевые слова могут имитировать оптимизатор для выполнения оператора SQL-запроса, чтобы знать, как MySQL обрабатывает ваш оператор SQL. Проанализируйте узкое место производительности оператора запроса или структуры таблицы.
- Что можно посмотреть
- порядок чтения таблицы
- Какие индексы можно использовать
- Какие индексы используются на самом деле
- ссылки между таблицами
- Сколько строк в таблице запрашивается оптимизатором
-
как пользоваться
explain + SQL语句
Включенная информация: - Пояснение к каждому полю
1.【 id】
Порядковый номер запроса на выборку, который содержит набор чисел, указывающих порядок, в котором предложение выбора или таблица операций выполняются в запросе.
три ситуации:
-
ID тот же, порядок выполнения сверху вниз
-
Идентификатор другой.Если это подзапрос, то порядковый номер идентификатора будет увеличиваться.Чем больше значение идентификатора, тем выше приоритет и выполняться первым.
-
Одинаковые и разные идентификаторы существуют одновременно
Если id одинаковый, то его можно рассматривать как группу и выполнять по порядку сверху вниз; во всех группах, чем больше значение id, тем выше приоритет и тем раньше выполняется.
2.【select_type】
-
SIMPLE
Простой запрос на выборку, который не содержит подзапросов или UNION.
-
PRIMARY
Если запрос содержит какие-либо сложные подразделы, самый внешний запрос помечается как основной. -
DERIVED
Подзапросы, содержащиеся в списке FROM, помечаются как DERIVED.MySQL выполняет эти подзапросы рекурсивно, помещая результаты во временные таблицы.
-
SUBQUERY
Подзапрос включен в список SELECT или WHERE
-
DEPENDENT SUBQUERY
Подзапрос включается в список SELECT или WHERE, и подзапрос основан на внешнем
【dependent subquery 和 subquery 的区别】
Зависимые подзапросы: результаты подзапроса многозначны.
Подзапрос: результатом запроса является одно значение -
UNCACHEABLE SUBQUERY
Подзапросы, которые нельзя кэшировать
@@ указывает на то, что параметры окружения проверяются, нет возможности кешировать -
UNION
Если второй SELECT появляется после UNION, он помечается как UNION;
Если UNION включен в подзапрос предложения FROM, внешний SELECT будет помечен как: DERIVED -
UNION RESULT
SELECT, чтобы получить результаты из таблицы UNION
3.【table】
Показать, к какой таблице относятся данные в этой строке4.【type】
type показывает тип доступа, что является важным показателем.Результирующие значения отсортированы от лучшего к худшему:
system > const > eq_ef > ref > range(尽量保证) > index > ALL
一般来说，得保证查询至少达到range级别，最好能达到ref
-
system
Таблица имеет только одну строку записей (равную системной таблице), представляющую собой специальный столбец типа const, который обычно не отображается, и на это тоже можно не обращать внимания. -
const
Указывает, что индекс найден один раз, а const используется для сравнения первичного ключа или уникального индекса. Поскольку сопоставляется только одна строка данных, быстрое помещение первичного ключа в список where позволяет MySQL преобразовать запрос в константу. -
eq_ref
Уникальное сканирование индекса, для каждого ключа индекса ему соответствует только одна запись в таблице. Обычно встречается при сканировании первичного ключа или уникального индекса. -
ref
Неуникальное сканирование индекса, которое возвращает все строки, соответствующие одному значению, по сути, является доступом к индексу, который возвращает все строки, соответствующие одному значению, однако он может найти несколько совпадающих строк, поэтому он должен быть гибридом поиска и сканирования. -
range
ТолькоПолучить заданный диапазон строк, используя индекс для выбора строк. Ключевой столбец показывает, какой индекс используется, обычно в вашем операторе where.between,<,>,inи другие запросы. Этот индекс сканирования диапазона лучше, чем полное сканирование таблицы, потому что ему нужно начинать только в одной точке индекса и заканчивать в другой точке без сканирования всего индекса. -
index
Полное сканирование индекса, разница между индексом и ВСЕМИТип индекса только пересекает дерево индекса. Обычно это быстрее, чем ALL, потому что индексные файлы обычно меньше, чем файлы данных. (То есть, хотя all и index оба читают всю таблицу), но индекс читается из индекса, и все читается с диска. -
all
Полное сканирование таблицы, которое будет проходить по всей таблице, чтобы найти совпадающие строки.
5.【possible_keys】
Отображает один или несколько индексов, которые можно применить к этой таблице. Если в поле, задействованном в запросе, есть индекс, индекс будет указан,но не обязательно фактически используется запросом
6.【key】
Фактический используемый индекс, если NULL, индекс не используется. Если в запросе используется покрывающий индекс, он перекрывается с полем выбора запроса.
7.【key_len】
Указывает количество байтов, используемых в индексе, из которого можно вычислить длину индекса, используемого в запросе. Поле key_len может помочь вам проверить, полностью ли используется индекс.
Расчет:
Динамические типы включают в себя: varchar, detail text() перехватывает строки
Структура таблицы этого листа следующая:
Первый набор рассчитывается как:
key_len=deptno(int)+null+ename(varchar(20)3+динамический=4+1+203+2=67
Второй набор рассчитывается как:
key_len=deptno(int)+null=4+1=5
8.【ref】Показывает, какой столбец индекса используется, если возможно, константа. какие столбцы или константы используются для поиска значений в индексированных столбцах
9.【row】Столбец rows показывает количество строк, которые, по мнению MySQL, он должен проверить при выполнении запроса (чем меньше, тем лучше).
10.【Extra】Содержит важную дополнительную информацию, не подходящую для отображения в других столбцах.
-
Using filesort
Это означает, что mysql будет использовать внешний индекс для сортировки данных вместо того, чтобы читать их в соответствии с порядком индексов в таблице. Операция сортировки в MySQL, которую нельзя выполнить с помощью индекса, называется «сортировкой файлов». -
Using temporary
Промежуточный результат сохраняется с помощью временной таблицы, и MySQL использует временную таблицу при сортировке результатов запроса. Обычно сортируется по порядку и группирует запросы по группам. -
Using index
Операция индикации, соответствующая выбранному покрытию индекса (Covering Index), чтобы избежать строки данных в таблице, хорошая эффективность. Если оба появляютсяusing where, указывающий, что индекс используется для поиска значения ключа индекса; если он не присутствует в то же времяusing where, указывая, что индекс используется только для чтения данных, а не для выполнения поиска с использованием индекса.
覆盖索引：
Индекс, который включает (или охватывает) [предложение select] и все обязательные поля в условии запроса [предложение where], называется покрывающим индексом.
Уведомление:Выньте только необходимые столбцы, не выбирайте * и не индексируйте все поля вместе -
Using where
Указывает, что там, где используется фильтрация -
Using join buffer
используется кеш соединения
5) Оптимизация запросов
索引的使用
-
Полная стоимость соответствует моему любимому
Таблица Staffs имеет индекс idx_staffs_nameAgePos, который устанавливается в порядке имени, возраста и должности, а полные совпадающие идентификаторы сопоставляются по порядку. -
принцип наилучшего левого префикса
Если индексируется несколько столбцов, необходимо следовать принципу крайнего левого префикса.Начинать запрос стоит с самого левого переднего столбца индекса, аНе пропускать столбцы в индексе
и игнорировать левые и правые отношения, даже не по порядку, автоматически оптимизируется благодаря наличию оптимизатора -
Невыполнение какой-либо операции (вычисление, функция, (автоматическое или ручное) преобразование типа) с индексированным столбцом приведет к сбою индекса и переходу к полному сканированию таблицы.
-
Механизм хранения не может использовать столбец справа от условия диапазона в индексе.
Если диапазон имеет индекс, этот индекс можно использовать, и индекс в правой части условия диапазона будет недействительным (правая часть условия диапазона и условия диапазона используют один и тот же комбинированный индекс, правая сторона будет недействительным, если это другой индекс, он не будет недействительным) - ** Попробуйте использовать покрывающий индекс (запрос, который обращается только к индексу (столбец индекса соответствует столбцу запроса)) для уменьшения выбора ***
-
MySQL не может использовать индексы при использовании неравенства (!= или ), что приведет к полному сканированию таблицы.
где age != 10 и name = 'xxx'. В этом случае mysql автоматически оптимизируется, чтобы указать name = 'xxx' перед age != 10, name по-прежнему может использовать индекс, но индекс возраста недействителен -
не является нулевым, также не может использовать индекс, но является нулевым, может использовать индекс
-
Подобно тому, как начинается с подстановочного знака ('%xxx'), инвалидация индекса становится полным сканированием таблицы.
как '%xxx': тип типа станет всем
например 'xxx%': тип - это диапазон, который является диапазоном, и можно использовать индекс -
Строковый индекс без одинарных кавычек не работает
Индекс недействителен, когда базовый тип преобразуется, и функция используется, чтобы вызвать сбой индекса.
10.Используйте или меньше, индекс будет недействителен при подключении к нему.
【例子小节】
В этот момент составной индекс index (a, b, c)
【使用建议】
- Для индексов с одним ключом попробуйте выбрать индекс с лучшей фильтруемостью для текущего запроса.
- При выборе составного индекса поле с наилучшей фильтруемостью в текущем запросе находится в порядке полей индекса, и чем выше позиция, тем лучше. (Избегайте аннулирования индекса с хорошей фильтрацией индекса)
- При выборе составного индекса попробуйте выбрать индекс, который может содержать больше полей в предложении where текущего запроса.
- Насколько это возможно, анализируя статистическую информацию и корректируя написание запроса для достижения цели выбора соответствующего индекса.
-
关联查询优化
1. Убедитесь, что поле соединения управляемой таблицы проиндексировано (соединяемая таблица является управляющей таблицей).
2. Во время левого соединения выберите маленькую таблицу в качестве ведущей, а большую — в качестве ведомой (левое соединение должно быть ведущей таблицей слева и ведомой таблицей справа).
3. Во время внутреннего соединения MySQL поможет вам выбрать небольшой набор результатов в качестве управляющей таблицы. так какВ любом случае таблица драйверов будет полностью просканирована., поэтому чем меньше сканирований, тем лучше.
4. Старайтесь не ставить подзапросы в управляемую таблицу, и возможно, что индекс не будет использоваться.
# 未加索引，type为ALL
explain select * from class left join book on class.card = book.card
# 添加索引优化，第二行的type变成了ref
alter table book add index idx_card_B(card);
# 这是由左连接特效决定的，left join条件用于确定如何从右表搜索行，左边一定都有
# 继续优化，删除旧索引，新建新索引
drop index idx_card_B on book;
alter table class add index idx_card_A(card)
-
子查询优化
1. Внутреннее соединение лучше всего, когда есть индекс, за которым следует in, а существование — худшее.
2. Используйте маленькую таблицу для управления большой таблицей без индекса, потому что метод соединения требует отдельного, а отсутствие отдельного индекса потребляет много производительности, поэтому производительность exists является лучшей, за ней следует in, а производительность соединения худший
3. В случае отсутствия индекса большая таблица управляет маленькой таблицей, производительность in и exists должна быть близкой, обе относительно плохи, а exists немного лучше, но не превышает 5%. -
order by关键字优化
Попробуйте использовать для сортировки метод Index и избегайте использования метода FileSort для сортировки. MySQL поддерживает два способа сортировки:FileSortиIndex, среди которых эффективность индекса высока, что означает, что Mysql сам просматривает индекс для завершения сортировки, а метод FileSort менее эффективен. Если выполняются три условия, будет использована сортировка по индексу. - Оператор Order By использует самый верхний левый столбец индекса.
- Используйте предложение where и предложение order by, чтобы объединить условные столбцы, чтобы удовлетворить самый левый передний столбец индекса.
- Если запрос диапазона индекса появляется в предложении where (то есть диапазон появляется в объяснении), порядок по индексу будет недействительным.
例子：talA 表中有索引 （age,birth,name）
分页查询的优化--limit
explain select sql_no_chache * from emp order by deptno limit 10000,40
#加上索引
create index emp on emp(deptno)
# 通过以上结果可以看出加上索引并不能改变
# 进一步优化：先利用覆盖索引把要取的数据行的主键取到，然后再用这个主键列与数据表做关联（查询数据量小了后）
explain select sql_no_cache * from emp e inner join (select id from emp e order by deptno limit 10000,40)a on a.id = e.id
-
GROUP BY 关键字优化
1. Группировать по, по сути, сначала сортирует, а затем группирует в соответствии с лучшим левым префиксом, построенным индексом.
2. Когда индексный столбец нельзя использовать, увеличьте значение параметра max_length_for_sort_data + увеличьте значение параметра sort_buffer_size.
3. Где выше, чем иметь, и условия, которые можно прописать, где не ограничиваются наличием. -
去重优化
Старайтесь не использовать ключевое слово different для дедупликации.例子：
# 使用distinct关键字去重消耗性能
select distinct BOOK_NAME from book where id in(1,2,5,4,8)
# 使用 group by能够利用到索引
select BOOK_NAME from book where id in(1,2,5,4,8) group by BOOK_NAME
Эта статья длинная, вы можете увидеть здесь вселучшийиз! Путь к росту бесконечен~
Если вы будете усердно работать сегодня, завтра вы сможете сказать на одну вещь меньше, чтобы попросить о помощи!很久很久之前，有个传说，据说：
Не понравилось после прочтения, все хреново