Пожалуйста, перестаньте спрашивать меня, как оптимизировать производительность MySQL? Это тебе! ! !

предисловие

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

Несколько шагов оптимизации SQL

1. Пройтиshow statusКоманда для понимания эффективности выполнения различных SQL

show [session | global] status;

• Вы можете добавить параметры для отображения по мере необходимостиsessionуровень (в настоящее время подключен, по умолчанию) иglobalСтатистика по уровню (с момента последнего запуска базы до настоящего времени).

show status like 'Com_%';  ---显示当前连接所有统计参数的值。

• Com_xxxозначает каждыйxxxКоличество раз выполнения оператора обычно обращают внимание на следующие параметры:Com_select/Com_insert/Com_update/Com_delete.

2. Найдите операторы SQL с низкой эффективностью выполнения

• пройти черезshow processlistкоманда для просмотра выполнения текущего SQL в режиме реального времени;

• пройти через慢查询日志Проблемы с позиционированием.

3. ПройтиexplainилиdescАнализ планов выполнения для неэффективного SQL

• Вы можете обратиться к предыдущей статьеПодробное объяснение плана выполнения объяснения исследования Mysql.

4. Пройтиshow profileАнализ SQL.

• show profileЭто может помочь нам понять, куда уходит наше время.

• пройти черезshow profilesМы можем лучше понять процесс выполнения SQL;

5. ПройтиtraceПроанализируйте, как оптимизатор выбирает план выполнения

• MySQL5.6Обеспечивает трассировку SQLtrace, может помочь нам понять, почему оптимизатор выбирает план A вместо плана B, и лучше понять поведение оптимизатора.

6. Определите проблему и примите соответствующие меры по оптимизации.

Обычно используемые MySQL методы оптимизации операторов SQL

1. следует по возможности избегатьwhereиспользуется в статье!=или<>оператора, иначе движок откажется от использования индекса и выполнит полное сканирование таблицы.

2. Для оптимизации запросов следует по возможности избегать полного сканирования таблицы.whereа такжеorder byИндексы создаются для задействованных столбцов.

3. следует по возможности избегатьwhereполе в предложенииnullоценочное суждение, в противном случае движок откажется от использования индекса и выполнит полное сканирование таблицы. Такие как:

select id from t where num is null

• Вы можете установить значение по умолчанию 0 для num, чтобы убедиться, что для столбца num в таблице нет нулевых значений, а затем выполнить запрос следующим образом:

select id from t where num=0

4. избегать вwhereиспользуется в статьеorЧтобы присоединиться к условиям, если поле имеет индекс, а поле не имеет индекса, это заставит движок отказаться от использования индекса и выполнить полное сканирование таблицы.

5. Ведущий нечеткий запрос приведет к полному сканированию таблицы.

  select id from t where name like ‘%c%’

• Используйте индекс ниже

select id from t where name like ‘c%’

6. not inЕго также следует использовать с осторожностью, иначе это приведет к полному сканированию таблицы; для непрерывных значений вы можете использоватьbetweenне используйin, попробуй использоватьexistsзаменятьin.

7. если вwhereИспользование параметров в предложении также приведет к полному сканированию таблицы. Поскольку SQL разрешает локальные переменные только во время выполнения, оптимизатор не может отложить выбор плана доступа до времени выполнения; он должен выбрать его во время компиляции. Однако если план доступа создается во время компиляции, значение переменной неизвестно и не может использоваться в качестве входных данных для выбора индекса. Следующий оператор выполнит полное сканирование таблицы:

  select id from t where num=@num

• Вместо этого вы можете заставить запрос использовать индекс:

  select id from t with(index(索引名)) where num=@num

8. следует по возможности избегатьwhereВ этом предложении над полем выполняется выражение и функция или другая операция выражения, которая заставит механизм отказаться от использования индекса и выполнить полное сканирование таблицы. Такие как:

select id from t where num/2=100, следует изменить на:select id from t where num=100*2

select id from t where substring(name,1,3)='abc';nameкabcначалоid, следует изменить на:select id from t where name like 'abc%'

select id from t where datediff(day,createdate,'2005-11-30')=0 –'2005-11-30′Сгенерированный идентификатор должен быть изменен на:select id from t where createdate>=’2005-11-30′ and createdate<'2005-12-01'

9. Updateзаявление, если вы меняете только 1 или 2 поля, не обновляйте все поля, иначе частые вызовы вызовут значительный расход производительности и принесут много логов.

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

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

12. Индекс не больше, тем лучше, индекс, безусловно, может улучшить соответствующийselectэффективность, но и снижаетinsertа такжеupdateЭффективность, потому чтоinsertилиupdateИндексы могут время от времени перестраиваться. Желательно, чтобы количество индексов в таблице не превышало 6.

13. Обновлений следует избегать, насколько это возможноclusteredиндексировать столбцы данных, потому чтоclusteredПорядок столбцов данных индекса — это физический порядок хранения записей таблицы, при изменении значения этого столбца будет скорректирован порядок всех записей таблицы, что потребует значительных ресурсов.

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

15. Не использовать нигдеselect * from t, замененный определенным списком полей*, не возвращайте поля, которые не используются.

16. Для нескольких таблиц с большим объемом данных (сотни здесь считаются большими)JOIN, сначала разбить на страницы, а затемJOIN, в противном случае логическое чтение будет высоким, а производительность будет низкой.

17. Попробуйте использовать табличные переменные вместо временных таблиц.

18. рассмотрите возможность использования临时表Постановка промежуточных результатов. Временные таблицы не являются непригодными для использования, и их правильное использование может сделать некоторые запросы более эффективными, например, когда необходимо многократно обращаться к большой таблице или набору данных в часто используемой таблице. Временно хранить временные результаты во временной таблице, а последующие запросы вtempdbЭто позволяет избежать многократного сканирования основной таблицы в программе, а также значительно сократить количество раз во время выполнения программы.共享锁блокировать更新锁, уменьшая блокировку и улучшая параллельную производительность. Однако для разовых событий лучше использовать таблицу экспорта.

19. При создании новой временной таблицы, если за один раз вставляется большой объем данных, можно использоватьselect intoзаменятьcreate table, чтобы не вызвать большойlog, чтобы улучшить скорость; если объем данных невелик, чтобы облегчить ресурсы системной таблицы, вы должны сначалаcreate table,Потомinsert.

20. Если используются временные таблицы, все временные таблицы должны быть явно удалены в конце хранимой процедуры.truncate table,Потомdrop table, что позволяет избежать длительной блокировки системных таблиц.

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

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

23. Устанавливается в начале всех хранимых процедур и триггеровSET NOCOUNT ON, установить в концеSET NOCOUNT OFF.

24. Старайтесь не возвращать клиенту большие объемы данных.

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

26. использоватьwhereзамена пунктаHavingпункт

Избегайте использования предложения have. Наличие фильтрует результирующий набор только после извлечения всех записей. Эта обработка требует сортировки. Если вы можете ограничить количество записей с помощью предложения where, вы можете уменьшить эти накладные расходы. On, where и have могут добавлять условные предложения, on — это первое выполнение, where — второе, а have — последнее.

27. Используйте усечение вместо удаления

Используйте, когда вам нужно удалить записи из всей таблицыTruncateзаменятьdelete. В нормальных условиях сегменты отката (rollback segments) используется для хранения информации, которую можно восстановить.Если у вас нетCOMMITТранзакция ORACLE восстановит данные до состояния до удаления (точнее, до состояния до выполнения команды удаления), а при использовании TRUNCATE сегмент отката больше не хранит никакой восстанавливаемой информации. Данные не могут быть восстановлены, поэтому задействуется мало ресурсов, а время выполнения короткое.

28. Используйте псевдонимы таблиц:

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

29. использоватьunion allзаменятьunion

Когда оператор SQL требуетunionКогда используются два набора результатов запроса, два набора результатов будут объединены в объединение всеми способами, а затем отсортированы перед выводом окончательного результата. Если вместо объединения используется объединение всех, сортировка не требуется, а эффективность повышается.Следует отметить, что ОБЪЕДИНЕНИЕ ВСЕ будет многократно выводить одни и те же записи в двух результирующих наборах.

30. Используйте where вместо порядка:

    ORDER BYПредложение использует индекс только при двух строгих условиях: ①ORDER BYВсе столбцы должны быть включены в один и тот же индекс и поддерживать порядок в индексе; ②ORDER BYВсе столбцы должны быть определены как ненулевые;

Неэффективно: (индекс не используется)SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_TYPE

Эффективно: (с использованием индекса)SELECT DEPT_CODE FROM DEPT WHERE DEPT_TYPE > 0

31. Избегайте преобразования типов индексированных столбцов:

Предположим, что EMP_TYPE — индексированный столбец символьного типа.SELECT … FROM EMP WHERE EMP_TYPE = 123Это утверждение преобразуется в:SELECT … FROM EMP WHERE EMP_TYPE='123'; Из-за преобразования типов, которое происходит внутри, этот индекс не будет использоваться!Чтобы ORACLE не выполнял неявные преобразования типов в ваш SQL, лучше сделать преобразование типов явным.Обратите внимание, что при сравнении символов и чисел ORACLE преобразует числовые типы в сначала типы символов.

32. Оптимизировать группу по

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

Неэффективно:SELECT JOB , AVG(SAL) FROM EMP GROUP by JOB HAVING JOB = ‘PRESIDENT' OR JOB = ‘MANAGER'

Эффективный:SELECT JOB , AVG(SAL) FROM EMP WHERE JOB = ‘PRESIDENT' OR JOB = ‘MANAGER' GROUP by JOB

33. Избегайте ресурсоемких операций:

с участиемDISTINCT,UNION,MINUS,INTERSECT,ORDER BYОператор SQL запускает механизм SQL для выполнения функции ресурсоемкой сортировки (SORT).DISTINCTТребуется одна операция сортировки, в то время как для других требуется как минимум две сортировки.UNION, MINUS , INTERSECTОператоры SQL можно переписать другими способами.Если SORT_AREA_SIZE вашей базы данных хорошо настроен, использование UNION , MINUS, INTERSECT также может быть рассмотрено, в конце концов, они очень читабельны.

34. В работающем коде попробуйте использоватьPreparedStatementдля запроса не используйтеStatement.

Обычно используемые методы оптимизации индекса MySQL

• По поводу оптимизации индекса в предыдущей статье подробно рассказывалось о железных правилах 20. Если интересно, можете почитать.Эта статья поможет вам понять, как оптимизировать индекс!

Цели оптимизации, распространенные заблуждения и основные принципы баз данных MySQL

оптимизировать цель

• Двумя распространенными узкими местами в базах данных MySQL являются узкие места ЦП и ввода-вывода.Когда ЦП перегружен, это обычно происходит при загрузке данных в память или чтении с диска. Узкие места дискового ввода-вывода возникают, когда загруженные данные намного превышают объем памяти.

  • Сокращение количества операций ввода-вывода: ввод-вывод всегда является самым простым узким местом для базы данных, что определяется обязанностями базы данных.Более 90% времени в большинстве операций с базой данных занимают операции ввода-вывода.Уменьшение количества операций ввода-вывода заключается в том, что оптимизация SQL должна быть первоочередной задачей, и, конечно же, это также самый эффективный метод оптимизации.

  • Сокращение вычислений ЦП: Помимо узкого места ввода-вывода, при оптимизации SQL необходимо учитывать оптимизацию количества операций ЦП.order by, group by,distinct… сильно загружают ЦП (эти операции в основном представляют собой операции сравнения данных обработки ЦП в памяти). Когда наша оптимизация ввода-вывода достигает определенного этапа, сокращение вычислений ЦП становится важной целью нашей оптимизации SQL.

Общие недоразумения

1. count(1)а такжеcount(primary_key)лучше чемcount(*):

Чтобы подсчитать количество записей, многие люди используютcount(1)а такжеcount(primary_key)вместоcount(*)Они думают, что так лучше, на самом деле это недоразумение. Для некоторых сцен это может быть плохой производительностью, должна быть база данныхcount(*)Подсчет операции, чтобы сделать специальную оптимизацию. Как механизм Myisam, количество строк в таблице будет записано, используяcount（*）Значение можно получить напрямую.

2. count(column)а такжеcount(*)это то же самое

Фактически,count(column)а такжеcount(*)Это совершенно другая операция, и смысл, который она представляет, совершенно другой.count(column)сколько в результирующем набореcolumnЗаписи с ненулевыми полями обрабатывают только ненулевые значения.count(*)заключается в том, чтобы указать, сколько записей есть во всем наборе результатов и не будет пропущено.nullстоимость.

3. select a,b from …Сравниватьselect a,b,c from …Позволяет базе данных получать доступ к меньшему количеству данных

   На самом деле, большинство реляционных баз данных хранятся в строках, а операции доступа к данным основаны на единице ввода-вывода фиксированного размера (называемой блоком или страницей), обычно 4 КБ, 8 КБ... В большинстве случаев в каждой операции ввода-вывода хранится несколько строк. unit, и в каждой строке хранятся все поля строки (за исключением полей специального типа, таких как lob). Следовательно, независимо от того, возьмем ли мы одно поле или несколько полей, на самом деле объем данных, к которым база данных должна получить доступ в таблице, фактически одинаков.

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

4. order byТребуется операция сортировки

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

5. в плане выполненияfilesortсортировка файлов на диске

   Мы не можем винить нас за это недоразумение, но из-за проблемы с формулировками разработчиков MySQL.filesortмы используемexplainКоманда для просмотра плана выполнения SQL, вы можете увидеть сообщение в «Extra» отображается. На самом деле, до тех пор, пока необходим операции SQL-операторы SQL, будут отображатьсяUsing filesort, что не означает, что будет операция сортировки файлов.

Основной принцип

1. присоединяйтесь как можно реже

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

2. Сортировать как можно меньше

   Операция сортировки потребляет больше ресурсов ЦП, поэтому сокращение сортировки может значительно повлиять на время отклика SQL в сценариях с высокой частотой попаданий в кэш и достаточными возможностями ввода-вывода. Для MySQL существует много способов уменьшить сортировку, например: оптимизация с использованием индекса для сортировки, упомянутая в приведенном выше недоразумении; уменьшение количества записей, участвующих в сортировке; без необходимости сортировка данных.

3. попытайся избежатьselect *

   Многим людям трудно понять, увидев это.Разве вышесказанное не просто сказало по недоразумению, что количество полей в предложении select не повлияет на считывание данных? Да, в большинстве случаев это не повлияет на количество IO, но когда у нас все еще есть порядок по операции, поля в предложении select будут в значительной степени влиять на нашу эффективность сортировки.Кроме того, вышеприведенные недоразумения Нет, это просто в большинстве случаев это не повлияет на количество операций ввода-вывода.Когда результаты нашего запроса нужно найти только в индексе, это значительно уменьшит количество операций ввода-вывода.

4. использовать как можно большеjoinвместо подзапроса

   Хотя производительность Join невелика, она все же имеет очень большое преимущество в производительности по сравнению с подзапросами MySQL.

5. как можно меньшеor

   Когда в предложении where есть несколько условий, сосуществующих с «или», оптимизатор MySQL не очень хорошо решает проблему оптимизации своего плана выполнения в сочетании с уникальной многоуровневой архитектурой SQL и хранилища MySQL, что приводит к тому, что его производительность относительно низкая. , и он часто используетсяunion allилиunion(при необходимости) способ заменыorполучат лучшие результаты.

6. использовать как можно большеunion allзаменятьunion

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

7. фильтровать как можно раньше

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

8. Избегайте преобразования типов

   Упомянутое здесь «преобразование типов» относится кwhereпоявляется в статьеcolumnПреобразование типа, которое происходит, когда тип поля не соответствует типу параметра, переданного в

9. Отдайте предпочтение высокопараллельному SQL, а не некоторому «большому» SQL, который выполняется нечасто.

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

10. Оптимизация с глобальной точки зрения, а не односторонняя настройка

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

11. Объясните как можно подробнее каждый запуск SQL в базе данных.

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

Оптимизация структуры таблиц базы данных MySQL

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

выбор типа данных

1. Наиболее трудоемкой операцией в операциях с базой данных является обработка ввода-вывода, и более 90% большинства операций с базой данных тратится на чтение и запись операций ввода-вывода. Следовательно, максимальное сокращение количества операций ввода-вывода при чтении и записи может значительно повысить производительность операций с базой данных. Мы не можем изменить данные, которые должны храниться в базе данных, но мы можем подумать о том, как эти данные хранятся. Следующие предложения по оптимизации типов полей в основном применимы к сценариям с большим количеством записей и большим объемом данных, поскольку уточненные настройки типов данных могут увеличить затраты на обслуживание, а чрезмерная оптимизация также может вызвать другие проблемы.

数字类型: Не используйте без крайней необходимостиDOUBLE, не только вопрос длины хранения, но и проблема точности. Точно так же не рекомендуется использовать десятичные дроби с фиксированной точностью.DECIMAL, рекомендуется умножить на фиксированное кратное и преобразовать в целочисленное хранилище, что может значительно сэкономить место для хранения и не принесет дополнительных затрат на обслуживание. Для хранения целых чисел, в случае большого объема данных, рекомендуется различатьTINYINT / INT / BIGINTПоскольку пространство для хранения, занимаемое этими тремя, также сильно различается, можно определить, что отрицательные поля не будут использоваться.Рекомендуется добавлять неподписанные определения. Конечно, если в базе данных небольшой объем данных, нет необходимости строго различать три целочисленных типа.

intТип увеличивает только поле первичного ключа => 4 байта => 8 бит на байт => 32 бита, когда ЦП загружает инструкцию, 4 байта связаны с работой регистров ЦП, например: 64 бита, потому что предыдущий Система, как правило, 32-битная, поэтому работать с 4-байтными данными в самый раз, а эффективность самая высокая.Однако, когда наша система в основном 64-битная, мы на самом деле не используем процессор лучше. , поэтому в поле таблицы параметров рекомендуется использовать 8-байтовый первичный ключ.bigint, вместо того, чтобы использовать int напрямую в качестве первичного ключа.

字符类型: Не используйте тип данных TEXT без крайней необходимости.Метод его обработки определяет, что его производительность ниже, чем у типов char или varchar. Для полей фиксированной длины рекомендуется использовать тип CHAR. Для полей переменной длины по возможности используйте VARCHAR и устанавливайте только подходящую максимальную длину, а не произвольно задавайте большой предел максимальной длины. Из-за разных диапазонов длины , MySQL также будет иметь другой процесс хранения. `char(10)`` Вне зависимости от того, хранит поле данные или нет, оно занимает 10 символов места для хранения.При этом в char(10) есть ямка, то есть после сохранения данных abc изменить значение поля базы данных до "abc 7 пробелов", В точном запросе (где) необходимо привести следующие 7 пробелов. Когда varchar не существует, он не занимает места и занимает столько места, сколько хранятся данные.

Тип времени: Используйте как можно большеTIMESTAMPтип, поскольку для его хранения требуется толькоDATETIMEполовина типа. Для типов данных, которые должны быть точными только до определенного дня, рекомендуется использовать тип DATE, поскольку для его хранения требуется всего 3 байта, что меньше, чем TIMESTAMP. Сохранение значения временной метки unix через класс типов INT не рекомендуется, так как это слишком неинтуитивно, вызывает ненужную головную боль при обслуживании и не дает никакой пользы.

ENUM & SET: Для поля состояния вы можете попробовать использовать ENUM для его хранения, потому что это может сильно уменьшить пространство для хранения, и даже если вам нужно добавить новый тип, пока он добавляется в конце, структура не необходимо восстановить данные таблицы. Что, если он предназначен для хранения предопределенных атрибутивных данных? Вы можете попробовать использовать тип SET, даже если есть несколько свойств, он все равно может работать хорошо, а также может сэкономить много места для хранения.

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

字符编码: Набор символов напрямую определяет метод кодирования хранения данных в MySQL.Поскольку одно и то же содержимое представлено разными наборами символов, занимаемое пространство будет совершенно разным, поэтому использование подходящего набора символов может помочь нам сократить как можно больше. возможного объема данных, тем самым уменьшая количество операций ввода-вывода. ① Для содержимого, которое может быть представлено чистыми латинскими символами, нет необходимости выбирать другие кодировки символов, кроме latin1, потому что это сэкономит много места для хранения; ② Если мы можем быть уверены, что нам не нужно хранить несколько языков, нет необходимости использовать UTF8 или другие типы символов UNICODE, что приведет к трате большого количества места для хранения; ③Тип данных MySQL может быть точным для поля, поэтому, когда нам нужно хранить многобайтовые данные в большой базе данных, мы можем значительно уменьшить объем хранилища данных, используя разные типы данных для разных полей в разных таблицах, тем самым уменьшая операций ввода-вывода и повысить частоту попаданий в кэш.

适当拆分: иногда нам может понадобиться сопоставить полный объект с таблицей базы данных, что очень хорошо для разработки приложений, но иногда это может вызвать большие проблемы с производительностью. Когда в нашей таблице есть большое поле, похожее на TEXT или большой тип VARCHAR, если нам не нужно это поле при доступе к этой таблице по большей части, мы должны без колебаний разделить его на другое.В отдельной таблице, чтобы уменьшить объем памяти, занимаемый часто используемыми данными. Очевидным преимуществом этого является то, что количество фрагментов данных, которые могут быть сохранены в каждом блоке данных, может быть значительно увеличено, что не только уменьшает количество физических операций ввода-вывода, но также значительно повышает частоту попаданий в кэш-память.

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

适度冗余: Зачем нам избыточность? Разве это не увеличивает размер каждой части данных и не уменьшает количество записей, которые могут храниться в каждом блоке данных? Действительно, это увеличит размер каждой записи и уменьшит количество фрагментов данных, которые могут храниться в каждой записи, но в некоторых сценариях нам все равно придется это сделать: ① Независимые малые поля, на которые часто ссылаются и которые можно получить только путем объединения 2 (или более) больших таблиц: в таком сценарии, поскольку каждое соединение предназначено только для получения значения небольшого поля, записи, полученные с помощью объединения, друг друга.Если он большой, это вызовет много ненужных операций ввода-вывода, которые можно оптимизировать, заменив пространство на время. Однако избыточность также необходима для обеспечения того, чтобы согласованность данных не была нарушена, а также для обеспечения того, чтобы избыточные поля также обновлялись во время обновления.

尽量使用 NOT NULL: Тип NULL особенный, и SQL сложно оптимизировать. Хотя тип MySQL NULL отличается от типа NULL Oracle и будет входить в индекс, если это составной индекс, то это поле типа NULL сильно повлияет на эффективность всего индекса. Многие люди думают, что NULL сэкономит место, поэтому попробуйте использовать NULL для достижения цели экономии ввода-вывода, но в большинстве случаев это будет контрпродуктивно.Хотя может быть некоторая экономия места, это приносит много других проблем с оптимизацией. количество операций ввода-вывода сохраняется, но количество операций ввода-вывода SQL увеличивается. Поэтому постарайтесь убедиться, что значение DEFAULT не равно NULL, что также является хорошей практикой оптимизации структуры таблицы.

Оптимизация параметров кеша для базы данных MySQL

• бесполезно, не обращайте внимания

Суммировать

• Наиболее часто используемые методы оптимизации для баз данных: операторы и индексы SQL, структура таблиц базы данных, конфигурация системы и аппаратное обеспечение.
• Эффект оптимизации: оператор SQL и индекс > структура таблицы базы данных > конфигурация системы > аппаратное обеспечение, но стоимость варьируется от низкой до высокой.
• Краткое изложение методов оптимизации базы данных:
  1. Создайте базу данных, которая соответствует парадигме
  2. Выберите правильный механизм хранения
  3. Оптимизация операторов SQL
  4. Оптимизация индекса: индексирование полей с высоким разделением
  5. Структура таблицы SQL, оптимизация полей
  6. Оптимизация параметров базы данных: параметры ввода-вывода, параметры ЦП
  7. Подбиблиотека и подтаблица: вертикальная сегментация и горизонтальная сегментация
  8. Разбиение на разделы: размещение данных таблицы в разных разделах в соответствии с определенными правилами, повышение эффективности ввода-вывода диска и повышение производительности базы данных.
  9. Репликация master-slave и разделение чтения-записи: три основных потока, файлы bin-log, файлы relay_log, главная база данных отвечает за операции записи, а подчиненная база данных отвечает за операции чтения
  10. Балансировка нагрузки
  11. кластер базы данных
  12. аппаратное обеспечение
• Для получения дополнительных статей, пожалуйста, обратите внимание на публичный аккаунт WeChat [Code Ape Technology Column]