寫一手好SQL,該從哪裡入手最好?
有時候我們會遇到:在查詢sql的時候,假如有100w條資料,會出現慢sql告警,這個時候你就應該到處sql日誌來查詢原因了。這裡很有可能的主要原因就是沒有命中索引和沒有分頁處理(原因有很多種,主要分析你的日誌)。那接下來我們就得去優化sql了。
如何優化呢?下面我們來談談有關的問題。
一、從sql優化入手
談到sql效能優化,那我們就離不開談到大資料量和併發數,MySQL沒有限制單表的最大記錄數,它只是取決於作業系統對檔案大小的限制。看錶:
從表中我們可以看出,如果單錶行數超過500萬行或者單表容量超過2GB,才推薦分庫分表。效能由綜合因素決定,拋開業務複雜度,影響程度依次是硬體配置、MySQL配置、資料表設計、索引優化。500萬這個值僅供參考,並非鐵律。
假如你在操作過超過4億行資料的單表,你可以用分頁查詢,分頁查詢最新的20條記錄耗時0.6秒,SQL語句大致是select field_1,field_2 from table where id < #{prePageMinId} order by id desc limit 20
,prePageMinId是上一頁資料記錄的最小ID。
這個查詢出來的查詢速度還湊合,不過隨著資料不斷增長,有朝一日必定不堪重負。所以分庫分表是個週期長而風險高的大活兒,應該儘可能在當前結構上優化,比如升級硬體、遷移歷史資料等等,實在沒轍了再分。
最大併發數
併發數是指同一時刻資料庫能處理多少個請求,由max_connections和max_user_connections決定。max_connections是指MySQL例項的最大連線數,上限值是16384,max_user_connections是指每個資料庫使用者的最大連線數。
MySQL會為每個連線提供緩衝區,意味著消耗更多的記憶體。如果連線數設定太高硬體吃不消,太低又不能充分利用硬體。一般要求兩者比值超過10%,計算方法如下:
max_used_connections / max_connections * 100% = 3/100 *100% ≈ 3%
檢視最大連線數與響應最大連線數如下:
show variables like '%max_connections%';
show variables like '%max_user_connections%';
在配置檔案my.cnf中你可以修改最大連線數
[mysqld]
max_connections = 100
max_used_connections = 20
查詢耗時0.5秒
建議將單次查詢耗時控制在0.5秒以內,0.5秒是個經驗值,源於使用者體驗的3秒原則。如果使用者的操作3秒內沒有響應,將會厭煩甚至退出。響應時間=客戶端UI渲染耗時+網路請求耗時+應用程式處理耗時+查詢資料庫耗時,0.5秒就是留給資料庫1/6的處理時間。
二、資料庫設計也是影響效能的關鍵
資料型別的選擇原則:更簡單或者佔用空間更小。你可以這樣設計:
1 如果長度能夠滿足,整型儘量使用tinyint、smallint、medium_int而非int。
2 如果字串長度確定,採用char型別。
3 如果varchar能夠滿足,不採用text型別。
4 精度要求較高的使用decimal型別,也可以使用BIGINT,比如精確兩位小數就乘以100後儲存。
5 儘量採用timestamp而非datetime。
相比datetime,timestamp佔用更少的空間,以UTC的格式儲存自動轉換時區。
避免空值
MySQL中欄位為NULL時依然佔用空間,會使索引、索引統計更加複雜。從NULL值更新到非NULL無法做到原地更新,容易發生索引分裂影響效能。儘可能將NULL值用有意義的值代替,也能避免SQL語句裡面包含is not null的判斷。
text型別優化
由於text欄位儲存大量資料,表容量會很早漲上去,影響其他欄位的查詢效能。建議抽取出來放在子表裡,用業務主鍵關聯。
三、索引優化,這個經常談到
索引的分類有哪些?
1 普通索引:最基本的索引
2 組合索引:多個欄位上建立的索引,能夠加速複合查詢條件的檢索。
3 唯一索引:與普通索引類似,但索引列的值必須唯一,允許有空值
4 組合唯一索引:列值的組合必須唯一
5 主鍵索引:特殊的唯一索引,用於唯一標識資料表中的某一條記錄,不允許有空值,一般用primary key約束。
6 全文索引:用於海量文字的查詢,MySQL5.6之後的InnoDB和MyISAM均支援全文索引。由於查詢精度以及擴充套件性不佳,更多的企業選擇Elasticsearch。
索引優化
1 分頁查詢很重要,如果查詢資料量超過30%,MYSQL不會使用索引。
2 單表索引數不超過5個、單個索引欄位數不超過5個。
3 字串可使用字首索引,字首長度控制在5-8個字元。
4 欄位唯一性太低,增加索引沒有意義,如:是否刪除、性別。
合理使用覆蓋索引,如下所示:
select login_name, nick_name from member where login_name = ?
四,sql語句如何優化
業務描述:更新使用者所有已過期的優惠券為不可用狀態。
update status=0 FROM `coupon` WHERE expire_date <= #{currentDate} and status=1;
如果大量優惠券需要更新為不可用狀態,執行這條SQL可能會堵死其他SQL,分批處理虛擬碼如下:
int pageNo = 1;
int PAGE_SIZE = 100;
while(true) {
List<Integer> batchIdList = queryList('select id FROM `coupon` WHERE expire_date <= #{currentDate} and status = 1 limit #{(pageNo-1) * PAGE_SIZE},#{PAGE_SIZE}');
if (CollectionUtils.isEmpty(batchIdList)) {
return;
}
update('update status = 0 FROM `coupon` where status = 1 and id in #{batchIdList}')
pageNo ++;
}
操作符<>優化
通常<>操作符無法使用索引,舉例如下,查詢金額不為100元的訂單:
select id from orders where amount != 100;
如果金額為100的訂單極少,這種資料分佈嚴重不均的情況下,有可能使用索引。鑑於這種不確定性,採用union聚合搜尋結果,改寫方法如下:
(select id from orders where amount > 100)
union all
(select id from orders where amount < 100 and amount > 0)
OR優化
在Innodb引擎下or無法使用組合索引,比如:
select id,product_name from orders where mobile_no = '13421800407' or user_id = 100;
OR無法命中mobile_no + user_id的組合索引,可採用union,如下所示:
(select id,product_name from orders where mobile_no = '13421800407')
union
(select id,product_name from orders where user_id = 100);
此時id和product_name欄位都有索引,查詢才最高效。
IN優化
IN適合主表大子表小,EXIST適合主表小子表大。由於查詢優化器的不斷升級,很多場景這兩者效能差不多一樣了。
嘗試改為join查詢,舉例如下:
select id from orders where user_id in (select id from user where level = 'VIP');
採用JOIN如下所示:
select o.id from orders o left join user u on o.user_id = u.id where u.level = 'VIP';
不做列運算
通常在查詢條件列運算會導致索引失效,如下所示:
查詢當日訂單
select id from order where date_format(create_time,'%Y-%m-%d') = '2019-07-01';
date_format函式會導致這個查詢無法使用索引,改寫後:
select id from order where create_time between '2019-07-01 00:00:00'
and '2019-07-01 23:59:59';
避免Select all
如果不查詢表中所有的列,避免使用SELECT *,它會進行全表掃描,不能有效利用索引。
Like優化
like用於模糊查詢,舉個例子(field已建立索引):
SELECT column FROM table WHERE field like '%keyword%';
這個查詢未命中索引,換成下面的寫法:
SELECT column FROM table WHERE field like 'keyword%';
去除了前面的%查詢將會命中索引,但是產品經理一定要前後模糊匹配呢?全文索引fulltext可以嘗試一下,但Elasticsearch才是終極武器。
Join優化
join的實現是採用Nested Loop Join演算法,就是通過驅動表的結果集作為基礎資料,通過該結資料作為過濾條件到下一個表中迴圈查詢資料,然後合併結果。如果有多個join,則將前面的結果集作為迴圈資料,再次到後一個表中查詢資料。
驅動表和被驅動表儘可能增加查詢條件,滿足ON的條件而少用Where,用小結果集驅動大結果集。
被驅動表的join欄位上加上索引,無法建立索引的時候,設定足夠的Join Buffer Size。
禁止join連線三個以上的表,嘗試增加冗餘欄位。
Limit優化
limit用於分頁查詢時越往後翻效能越差,解決的原則:縮小掃描範圍,如下所示:
select * from orders order by id desc limit 100000,10
耗時0.4秒
select * from orders order by id desc limit 1000000,10
耗時5.2秒
先篩選出ID縮小查詢範圍,寫法如下:
select * from orders where id > (select id from orders order by id desc
limit 1000000, 1) order by id desc limit 0,10
耗時0.5秒
如果查詢條件僅有主鍵ID,寫法如下:
select id from orders where id between 1000000 and 1000010 order by id desc
耗時0.3秒
如果以上方案依然很慢呢?只好用遊標了。
以上內容希望幫助到大家,更多PHP大廠PDF面試文件,PHP進階架構視訊資料,PHP精彩好文免費獲取可以關注公眾號:PHP開源社群,或者訪問:
- 基於Nginx的負載均衡原理與實戰
- PHP控制反轉(IOC)和依賴注入(DI)
- 深入理解PHP7核心之Reference
- php中類的不定引數使用示例
- php單例模式的常見應用場景
- laravel 配置MySQL讀寫分離
- PHP的垃圾回收機制(建議收藏)
- 【shell指令碼】字串和陣列的使用
- PHP-FPM是什麼東東?
- PHP 編寫守護程序
- PHP命令列指令碼接收傳入引數的三種方式
- php專案中類的自動載入
- 複習下Linux去除重複項命令uniq
- 深入理解PHP核心:變數及資料型別
- Swoole協程與傳統fpm同步模式比較
- PHP中Session ID的實現原理
- 寫一手好SQL,該從哪裡入手最好?
- PHP命令列指令碼接收傳入引數的三種方式
- 使用 Shell 在多伺服器上批量操作
- PHP實現使用者異地登入提醒功能的方法