Быстрые вопросы и ответы по базе данных MySQL

предисловие

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

1. Разница между UNION ALL и UNION

• Ключевые слова UNION и UNION ALL объединяют два набора результатов в один.

• UNION будет отфильтровывать повторяющиеся записи после связывания таблиц, поэтому после связывания таблиц результирующий набор будет отсортирован, повторяющиеся записи будут удалены, а результат будет возвращен.

• Союз все просто просто возвращает два результата.

• Поскольку UNION требует сортировки и дедупликации, UNION ALL намного эффективнее, чем UNION.

2. Разница между TRUNCATE и DELETE

• TRUNCATE — это оператор DDL, а DELETE — это оператор DML.
• TRUNCATE заключается в том, чтобы сначала удалить всю таблицу, а затем перестроить ее. А DELETE удаляет строку за строкой, поэтому TRUNCATE определенно быстрее, чем DELETE.
• TRUNCATE нельзя откатить, DELETE можно.
• Результат выполнения TRUNCATE просто возвращается0 rows affected, Это можно интерпретировать как отсутствие результатов.
• Truncate сбросит горизонтальную линию (от начала столбца), а DELETE — нет.
• TRUNCATE может очистить только всю таблицу, DELETE может удалить в соответствии с условиями.
• В целом производительность TRUNCATE немного лучше, чем у DELETE.

3. Разница между TIMESTAMP и DATETIME

相同点

• Столбцы TIMESTAMP отображаются в том же формате, что и столбцы DATETIME. Ширина столбца дисплея фиксирована и составляет 19 символов, а форматYYYY-MM-DD HH:MM:SS.

不同点

• TIMESTAMP
  • 4 байта памяти, диапазон времени:1970-01-01 08:00:01~2038-01-19 11:14:07.
  • Значение сохраняется в формате UTC и включает преобразование часового пояса. Текущий часовой пояс преобразуется при сохранении, а затем преобразуется обратно в текущий часовой пояс при извлечении.
• DATETIME
  • 8 байт памяти, диапазон времени:1000-10-01 00:00:00~9999-12-31 23:59:59.
  • Фактический формат сохраняется независимо от часового пояса.

4. Что такое совместный индекс

Индекс по двум или более столбцам называется объединенным индексом, а объединенный индекс также называется составным индексом.

5. Зачем использовать индекс объединения

• 减少开销: построить совместный индекс (col1, col2, col3), который фактически эквивалентен построению трех индексов (col1), (col1, col2), (col1, col2, col3). Уменьшите накладные расходы на дисковое пространство.
• 覆盖索引: Для общего индекса (col1,col2,col3), если есть следующий sql: выберите col1,col2,col3 из теста, где col1=1 и col2=2. Затем MySQL может получать данные напрямую, просматривая индекс, инет формы возврата, что сокращает количество случайных операций ввода-вывода. Покрывающие индексы — одна из основных оптимизаций для повышения производительности.
• 效率高: чем больше столбцов индекса, тем меньше данных отфильтровывается индексом. Таблица с фрагментами данных 1000 Вт имеет следующий sqlselect from table where col1=1 and col2=2 and col3=3Предположим, что условия могут быть выбраны для каждых 10% данных, если только одно значение индекса, индекс можно проверить1000W*10%=100wданные, а затем вернуться к таблице, чтобы найти данные, соответствующие col2=2 и col3=3 из данных 100w, затем отсортировать и разбить на страницы; если это совместный индекс, отфильтровать данные через индекс1000w*10%*10%*10%=1w, эффективность значительно повысилась.

6. Крайний левый принцип сопоставления объединенного индекса MySQL

• Когда MySQL создает совместный индекс, он будет следовать принципу сопоставления самого левого префикса, то есть самый левый сначала, и сопоставление будет начинаться с крайнего левого совмещенного индекса при извлечении данных.
• MySQL всегда будет сопоставляться справа, пока не встретит запрос диапазона (>, a = 1 and b = 2 and c > 3 and d = 4если установлено(a,b,c,d)Последовательный индекс, d не используется для индексации, если он установлен(a,b,d,c)Можно использовать все индексы, а порядок a, b, d можно регулировать произвольно.
• = и in могут быть не в порядке, например.a = 1 and b = 2 and c = 3Учреждать(a,b,c)Индекс может быть в любом порядке, и оптимизатор запросов MySQL поможет вам оптимизировать его в форме, которую индекс может распознать.

7. Что такое кластеризованные и некластеризованные индексы

• Кластерный индекс — это индекс, созданный с помощью первичного ключа.
• Неагрегированные индексы — это индексы, созданные неосновными ключами.

8. Что такое индекс покрытия

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

9. Что такое индекс префикса

Индекс префикса предназначен для создания индекса для первых нескольких символов текста (несколько конкретных символов, указанных при создании индекса), которые вместе создают индекс меньше. Но MySQL не может использовать индекс префикса в GROUP BY или ORDER BY, и при этом они не могут использоваться в качестве покрывающего индекса.

Синтаксис для создания индекса префикса:

ALTER TABLE table_name ADD
KEY(column_name(prefix_length))

10. Разница между структурой хранения индексов InnoDB и MyISAM

• Индексный файл MyISAM и файл данных разделены, и индексный файл сохраняет только адрес записи данных.
• В то время как в InnoDB,Сам файл данных таблицы представляет собой структуру индекса, организованную B+Tree, а поле данных конечного узла этого дерева сохраняет полные записи данных.. Ключ этого индекса является первичным ключом таблицы данных, поэтому сам файл данных таблицы InnoDB является первичным индексом, поэтому должен быть первичный ключ.Если нет явного определения, неявное поле автоматически генерируется как первичный ключ Длина этого поля составляет 6 байт, а тип — длинное целое.
• Вторичный индекс InnoDB (Secondary Index, то есть индекс, не являющийся первичным ключом) хранит толькоСтолбцы первичного ключа и индекса, если первичный ключ определен как большой, другие индексы также будут большими.
• Движок MyISAM использует B+Tree в качестве структуры индекса. Поле данных конечного узла индексного файла хранит адрес записи данных и указывает на соответствующее значение в файле данных. Каждый узел имеет только значение индекса. столбец.
• Нет никакой разницы в структуре между первичным индексом MyISAM и вторичным ключом (Secondary key), но первичный индекс требует, чтобы ключ был уникальным, а вторичный индекс может повторяться (поскольку вторичный индекс MyISAM хранит адрес запись данных на листовом узле и первичный ключ. Индекс тот же, поэтому нет необходимости снова проходить индекс первичного ключа).

Проще говоря:

1. 主索引的区别: файлы данных InnoDB сами по себе являются индексными файлами. А индекс и данные MyISAM разделены.

2. 辅助索引的区别: Полевые вспомогательные данные InnoDB хранит значение индекса не является адресом соответствующей записи первичной ключей. Вторичный индекс и основной индекс MyISAM не сильно отличается.

11. Зачем пытаться выбрать первичный ключ монотонно возрастающего числового типа

• Сами записи данных в InnoDB хранятся на листовых узлах основного индекса (дерево B+). Это требует, чтобы каждая запись данных в одном и том же листовом узле (размером является одна страница памяти или страница диска) хранилась в порядке первичного ключа, поэтому всякий раз, когда вставляется новая запись, MySQL будет вставлять ее в соответствующий узел в соответствии с его первичный ключ.Указатель и положение, если страница достигает коэффициента загрузки (InnoDB по умолчанию 15/16), открывается новая страница.

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

• Если используется несамоинкрементный первичный ключ, поскольку значение первичного ключа, вставляемого каждый раз, приблизительно случайно, каждая новая запись должна быть вставлена ​​в определенную позицию в середине существующей страницы индекса.В это время MySQL необходимо найти соответствующую новую запись.Элементы перемещаются в соответствии с их расположением, и даже целевая страница может быть записана обратно на диск и очищена из кеша.В это время она должна быть считана с диска, что увеличивает много накладных расходов, в то же время частые операции перемещения и подкачки вызывают много Fragmentation, получают недостаточно компактную структуру индекса, а затем приходится проходитьOPTIMIZE TABLEчтобы перестроить таблицу и оптимально заполнить страницу.

Проще говоря:

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

12. При построении таблицы значение длины после int

Сколько байтов занимает int, уже фиксировано, а длина представляет собой максимальную ширину дисплея. Если его недостаточно, он будет заполнен 0 слева, но его необходимо использовать с нулевым заполнением. То есть длина int не влияет на точность хранения данных, а длина связана только с отображением.

13. Что означает поле результата SHOW INDEX?

• Table:

  • Имя таблицы.

• Non_unique:

  • 0: Индекс не содержит дублирующих значений.
  • 1: Индекс может содержать повторяющиеся значения.

• Key_name:

  • имя индекса, если это индекс аннотаций, имя всегдаPRIMARY.

• Seq_in_index:

  • Порядковый номер этого столбца в индексе, начиная с 1. Например: есть совместный индексidx_a_b_c (a,b,c), тоSeq_in_index=1, б=2, с=3.

• Column_name:

  • имя столбца.

• Collation:

  • Порядок сортировки индексов: A (по возрастанию), D (по убыванию), NULL (не отсортировано).

• Cardinality:

  • Можно использовать значение, которое измеряет уникальную степень этого индекса.ANALYZE TABLE(InnoDB) илиmyisamchk -a(MyISAM), чтобы обновить значение.
  • Если в таблице слишком мало записей, это поле не имеет большого значения. Как правило, чем больше значение, тем выше эффективность индексации.

• Sub_part:

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

• Null:

  • YES: этот столбец допускает значения NULL.
  • '': этот столбец не допускает значений NULL.

• Index_type:

  • Тип индекса, в том числе (BTREE, FULLTEXT, HASH, RTREE).

14. Как решить проблему инвалидации индекса, если он похож на '%string%'?

• LIKE问题:like начинается с подстановочного знака ('%abc...'), и аннулирование индекса mysql станет операцией полного сканирования таблицы.
  • Виновник %, а неLIKE,LIKEтребованиеtype = rangeуровень
  • %xxx%: полное сканирование таблицы
  • %xxx: полное сканирование таблицы
  • xxx%:спектр
• 解决办法:
  • Используя индекс покрытия, вы можетеALLсталиINDEX,Зачем? После покрытия индекса вы можете использовать индекс для выполнения полного сканирования таблицы. Здесь следует отметить, что при использовании совпадающего индекса вы можете попасть в одно поле, а не во все.

15. Эффективная подкачка MySQL

• SQL существует:SELECT * FROM ttl_product_info ORDER BY id LIMIT N,M. вLIMIT N,MСамая большая проблема: удалить N+M строк, отбросить первые N строк и вернутьN ~ N+MДля записей строк, если значение N очень велико, эффективность крайне низкая (табличные записи 1500w, N=10000000, M=30 занимают 9 секунд).
• Решение: SQL:SELECT id FROM ttl_product_info WHERE id > N LIMIT M, столбец идентификатора является столбцом индекса,id > Nпринадлежатьrangeуровень, эффективность естественно высокая, и тогда с позиции берется 30 записей, что крайне эффективно (записей таблицы 1500w, N=10000000, M=30, и это занимает 0,9 миллисекунды).
• Безусловно, предпосылками для достижения вышеуказанного эффекта являются:
  1. id является уникальным индексом и монотонно возрастает.
  2. Значение N — это последний идентификатор записи, запрашиваемой в последний раз (его необходимо сохранить во внешнем интерфейсе, и его нельзя получить напрямую традиционными методами).
  3. Межстраничный запрос не поддерживается, и вы можете выполнять постраничный запрос только в соответствии со страницами 1, 2, 3 и 4.

Суммировать

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

Справочная статья:

• сегмент fault.com/ah/119000001…
• blog.CSDN.net/big tree_372…