總結MySQL優化最最基礎的操作-mysql教程-PHP中文網

首頁

資料庫

mysql教程

總結MySQL優化最最基礎的操作

藏色散人

Nov 15, 2021 pm 03:47 PM

mysql最佳化

優化想法

詳細的MySQL最佳化步驟如下：

檢視資料表結構，改善不完全設計
跑一遍主要業務，收集常用的資料庫查詢SQL
分析查詢SQL，適當拆分，加入索引等最佳化查詢
優化SQL的同時，優化程式碼邏輯
#新增本機快取和redis快取

盡可能不要使用NULL值

因為建表的時候，如果沒有對建立的值設定預設值，MySQL都會設定預設為NULL。那麼為啥用NULL不好呢？

NULL使得索引維護更加複雜，強烈建議對索引列設定#NOT NULL
NOT IN 、!=等負向條件查詢在有NULL值的情況下傳回永遠為空結果，查詢容易出錯
NULL列需要一個額外位元組作為判斷是否為NULL的標誌位元
使用NULL時和該列其他的值可能不是同種類型，導致問題。（在不同的語言中表現不一樣）
MySQL難以優化對可為NULL的列的查詢

所以對於那些以前偷懶的字段，手動設定一個預設值吧，空字串呀，0呀補上。

雖然這種方法對於MySQL的效能來說沒有提升多少，但這是一個好習慣，而且以小見大，不要忽略這些細節。

新增索引

對於經常查詢的字段，請加上索引，有索引和沒有索引的查詢速度相差十倍甚至更多。

一般來說，每個表都需要有一個主鍵id欄位
常用於查詢的欄位應該設定索引
#varchar類型的字段，在建立索引的時候，最好指定長度
查詢有多個條件時，優先使用具有索引的條件
像LIKE 條件這樣的模糊搜尋對於字段索引是無效的，需要另外建立關鍵字索引來解決
#請盡量不要在資料庫層級約束表和表之間的關係，這些表之間的依賴應該在程式碼層面去解決

當表和表之間有約束時，雖然增刪查的SQL語句變簡單了，但是帶來的負面效果是插入等操作資料庫都會去檢查約束（雖然可以手動設定忽略約束），這樣相當於把一些業務邏輯寫到了資料庫層，不便於維護。

優化表格欄位結構

資料庫中那些可以用整形表示的資料就不要使用字串類型，到底是用varchar還是char要看欄位的可能值。

這種最佳化往往在資料庫中有大量資料以後是不可行的，最好在資料庫設計之前就設計好。

對於那些可能值很有限的欄位，使用tinyint取代VARCHAR，
- 例如記錄行動裝置平台，只有兩個值：android，ios，那麼就可以使用0表示android，1表示ios，這種欄位一定要寫好註解
- 為什麼不用ENUM呢？ ENUM擴充困難，例如後來移動平台又增加了一個ipad，那豈不是懵逼了，而tinyint加個2就行，而且 ENUM在程式碼裡面處理起來特別奇怪，是當成整形呢還是字串，各個語言不一樣。
- 這種方式，一定要在資料庫註解或程式碼裡面寫明各個值的意思
對於那些定長字串，可以使用char，例如郵編，總是5位元
對於那些長度未知的字串，使用varchar
不要濫用bigint，例如記錄文章數目的表id字段，用int就行了，21億篇文章上限夠了
適當打破資料庫範式添加冗餘字段，避免查詢時的表連接

查詢的時候，肯定int類型比varchar快，因為整數的比較直接呼叫底層運算器就可以實現，而字串比較要逐個字元比較。

定長資料比變長資料查詢快，因為比較定長資料與資料之間的偏移是固定的，很容易計算下一個資料的偏移。而變長資料則還需要多一步去查詢下一個資料的偏移。不過。定長資料可能會浪費更多的儲存空間。

大表拆分

對於那些資料量可能近期會超過500W或成長很快的表，一定要提前做好垂直分錶或水平分錶，當數據量超過百萬以後，查詢速度會明顯下降。

分庫分錶盡量在資料庫設計初期敲定方案，否則後期會大幅增加程式碼複雜度而且不易變更。

垂直分錶是依照日期等外部變數進行分錶，水平分錶是依照表中的某些欄位關係，使用hash映射等分錶。

分庫分錶的前提條件是在執行查詢語句之前，已經知道需要查詢的資料可能會落在哪一個分庫和哪一個分錶中。

最佳化查詢語句

這個才是許多系統資料庫瓶頸的始作俑者。

請盡量使用簡單的查詢，避免使用表格連結
請盡量避免全表掃描，會造成全表掃描的語句包含但不限於：
- where子句條件恆真或為空
- 使用LIKE
- #使用不等運算子（、!=）
- 查詢含有is null的列
- 在非索引列上使用or
多條件查詢時，請把簡單查詢條件或索引列查詢置於前面
請盡量指定需要查詢的列，不要偷懶使用select *
- 如果不指定，一方面會傳回多餘的數據，佔用頻寬等
- 另一方面MySQL執行查詢的時候，沒有欄位時會先去查詢表格結構有哪些欄位
大寫的查詢關鍵字比小寫快一點點
使用子查詢會建立臨時表，會比連結（JOIN）和聯合（UNION）稍慢
在索引欄位上查詢盡量不要使用資料庫函數，不便於快取查詢結果
當只要一行資料時，請使用LIMIT 1，如果資料過多，請適當設定LIMIT，分頁查詢
千萬不要ORDER BY RAND()，效能極低

#新增快取

使用redis等緩存，還有本機檔案快取等，可以大幅減少資料庫查詢次數。快取這個東西，一定要分析自己系統的資料特點，適當選擇。

對於一些常用的數據，例如配置資訊等，可以放在快取中
可以在本地快取資料庫的表結構
快取的資料一定要注意及時更新，還有設定有效期限
增加快取務必會增加系統複雜性，一定要注意權衡

檢查資料表結構

推薦學習：《mysql影片教學》

以上是總結MySQL優化最最基礎的操作的詳細內容。更多資訊請關注PHP中文網其他相關文章！

陳述

本文轉載於：learnku。如有侵權，請聯絡admin@php.cn刪除

如何識別和優化MySQL中的慢速查詢？（慢查詢日誌，performance_schema）Apr 10, 2025 am 09:36 AM

要優化MySQL慢查詢，需使用slowquerylog和performance_schema：1.啟用slowquerylog並設置閾值，記錄慢查詢；2.利用performance_schema分析查詢執行細節，找出性能瓶頸並優化。

MySQL和SQL：開發人員的基本技能Apr 10, 2025 am 09:30 AM

MySQL和SQL是開發者必備技能。 1.MySQL是開源的關係型數據庫管理系統，SQL是用於管理和操作數據庫的標準語言。 2.MySQL通過高效的數據存儲和檢索功能支持多種存儲引擎，SQL通過簡單語句完成複雜數據操作。 3.使用示例包括基本查詢和高級查詢，如按條件過濾和排序。 4.常見錯誤包括語法錯誤和性能問題，可通過檢查SQL語句和使用EXPLAIN命令優化。 5.性能優化技巧包括使用索引、避免全表掃描、優化JOIN操作和提升代碼可讀性。

描述MySQL異步主奴隸複製過程。Apr 10, 2025 am 09:30 AM

MySQL異步主從復制通過binlog實現數據同步，提升讀性能和高可用性。 1)主服務器記錄變更到binlog；2)從服務器通過I/O線程讀取binlog；3)從服務器的SQL線程應用binlog同步數據。

mysql：簡單的概念，用於輕鬆學習Apr 10, 2025 am 09:29 AM

MySQL是一個開源的關係型數據庫管理系統。 1）創建數據庫和表：使用CREATEDATABASE和CREATETABLE命令。 2）基本操作：INSERT、UPDATE、DELETE和SELECT。 3）高級操作：JOIN、子查詢和事務處理。 4）調試技巧：檢查語法、數據類型和權限。 5）優化建議：使用索引、避免SELECT*和使用事務。

MySQL：數據庫的用戶友好介紹Apr 10, 2025 am 09:27 AM

MySQL的安裝和基本操作包括：1.下載並安裝MySQL，設置根用戶密碼；2.使用SQL命令創建數據庫和表，如CREATEDATABASE和CREATETABLE；3.執行CRUD操作，使用INSERT,SELECT,UPDATE,DELETE命令；4.創建索引和存儲過程以優化性能和實現複雜邏輯。通過這些步驟，你可以從零開始構建和管理MySQL數據庫。

InnoDB緩衝池如何工作，為什麼對性能至關重要？Apr 09, 2025 am 12:12 AM

InnoDBBufferPool通過將數據和索引頁加載到內存中來提升MySQL數據庫的性能。 1)數據頁加載到BufferPool中，減少磁盤I/O。 2)臟頁被標記並定期刷新到磁盤。 3)LRU算法管理數據頁淘汰。 4)預讀機制提前加載可能需要的數據頁。

MySQL：初學者的數據管理易用性Apr 09, 2025 am 12:07 AM

MySQL適合初學者使用，因為它安裝簡單、功能強大且易於管理數據。 1.安裝和配置簡單，適用於多種操作系統。 2.支持基本操作如創建數據庫和表、插入、查詢、更新和刪除數據。 3.提供高級功能如JOIN操作和子查詢。 4.可以通過索引、查詢優化和分錶分區來提升性能。 5.支持備份、恢復和安全措施，確保數據的安全和一致性。