掌握必要的sql優化知識對于程序員來說是必須的,因為各種程序本質來說就是對數據的處理,無非就是簡單的增刪改查和復雜的增刪改查而已,所以對于數據的分析處理是非常重要的
首先推薦一本書<深入淺出MYSQL>,由網易DBA團隊編寫,寫的非常好,感興趣的朋友可以買來看看
下面介紹sql優化:
1.主鍵查詢
查詢盡量使用主鍵查詢,因為有主鍵索引
其它索引后面介紹
2.合理使用字段,及字段長度
原則就是能保證存儲要求,也需預留空間
如果字段長度過長,會增加不必要的空間
例如郵政編碼字段,顯然VARCHAR(6)就行了,不需要VARCHAR(255)
3.盡量使用join關聯查詢替代子查詢
子查詢就是將一個查詢的結果作為另一個查詢的條件或者要查詢的表再次查詢
這樣查詢本質上屬于兩次查詢了,mysql會創建臨時表來存儲第一次查詢結果,這樣會消耗空間
每次查詢都是要消耗資源的,所以這種效率不是很好
數據量小看不出來,數據量大的話使用關聯查詢速度更快,可以去試驗
如果子查詢非要用的話,最好只出現一次,出現兩次或以上嚴重影響效率,因為計算機會對sql語句生成執行計劃,再去執行,如果子查詢過多,人都看暈了,同樣計算機識別語句的意思,然后生成執行計劃也會慢很多
4.網上看到有說使用外鍵的,工作中從來沒用過外鍵,這種說法不行,現在都是快速開發
外鍵的作用被業務層取代了,直接寫在代碼中
用外鍵不易修改/刪除數據,很麻煩,實際開發中也很少/幾乎沒有公司使用外鍵
所以該方法不可取
5.少寫或不寫select * ,要什么字段就取什么字段
6.查詢緩存的使用
mysql默認是開啟查詢緩存了
就是說相同的查詢,第二次會直接訪問緩存結果
但是有些sql中的函數如now(),這種是時時變化的,寫在sql語句中不會開啟查詢緩存
所以在sql中可不使用這些函數就不使用,用變量傳入sql中,這樣就開啟緩存了
7.當只要一行數據時使用limit 1,mysql數據庫引擎會在找到一條數據后停止搜索，而不是繼續往后查找下一條符合的數據,如果需的數據不是很多,但是返回量很大的時候也要使用limit限制返回量
8.使用EXPLAIN + 查詢語句可以知道sql語句執行的信息
可以看出查詢掃描類型,這個后面介紹
也可以直接在客戶端navicat查看概況,里面會顯示查詢時間主要消耗在哪里...
9.在比如數據量小且固定的情況下,如性別,省市區等等,可以使用ENUM字段(枚舉),ENUM 類型是非常快的,它實際上保存的是TINYINT,但其外表上顯示為字符串..
10.復雜sql語句的拆分與避免鎖表
在訪問量很高的項目中/特別是互聯網項目中盡量避免寫復雜的sql語句,因為復雜的sql語句執行時間相對而言要長,會造成鎖表,就是說當前sql執行的這段時間,別的sql不能操作該表,在訪問量很高的網站上,這時就會有大量的sql操作請求積累在數據庫中,這樣很可能數據庫就崩了
11.建立索引(重點)
這個是最重要的,因為不用索引和用索引的查詢速度相差了幾十倍
如果不用索引,mysql會從第一條數據開始讀取,讀完整張表然后獲取需要的數據
用了索引,mysql會按照索引快速的到一個位置去手搜索數據
就把索引比作字典前面那些拼音筆畫索引,顯然用這個查比你拿著字典從第一頁一頁頁的翻要快的多
但是如果你獲取的是大部分數據,這時就是順序讀取要快了
索引分類:
主鍵索引
唯一索引
普通索引
最合適索引字段是常用于在where語句后的字段,也就是查詢條件
字段中值基數越大,越不同,索引效果越好,如果數據量小且值又很多相同的,效果就不明顯,就不需要用索引了
不要過度索引,因為額外的索引都是要占用磁盤空間的,修改表數據時,索引必然是要更新的,這樣也是要浪費時間的,還有sql生成執行計劃的時候也會考慮各個索引,這樣看來多余的索引反而不好,不利于查詢優化
12.EXPLAIN + 查詢語句分析
可以看到type:訪問類型,key:實際使用的索引,rows:掃描的行數
可以檢查設置的索引有沒有用到,掃描行數越少顯然速度越快了
訪問類型就是mysql是怎么找到所需的數據的
type分類:
all:
全表掃描,就是mysql從第一條數據開始,一條條查詢
index:
索引全掃描,就是遍歷整個索引查找
如:select title from film
title上有索引,但是根據的是所有的索引去查詢的數據,這沒能很好體現索引的效果
range:
索引范圍掃描,常用于<,<=,>,between等操作符,就是設定索引值的范圍
如:select * from payment where customer_id <= 350
ref:
使用非唯一性索引匹配單個值然后返回
非唯一索引相對唯一索引而言的,唯一索引包括主鍵和設置其它唯一索引
如:select * from payment where customer_id = 350
eq_ref:
類似ref,但是使用的是唯一索引,對于每個索引鍵值,表中只一條記錄匹配,簡單的說就是多表連接中使用主鍵或者唯一性索引字段作為關聯條件
const/system:
最多匹配到一條數據
就是根據主鍵或者唯一性索引字段來查詢單表,速度最快

?

轉載于:https://www.cnblogs.com/jaro/p/8862165.html

總結

以上是生活随笔為你收集整理的01-sql优化及索引的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。