數據量和要求
- 數據量:
- 單表一千萬條記錄以上
- 要求:
- 單條sql查詢時間不超過1秒
優化技巧
先把每一條心得記錄在這裡,後面會進行實驗對其一一驗證。
- 查詢數據總條數時,使用max(id)而不是count(*)進行總量計數。
- 當然,前提是id是從1開始自增長,並且沒有行被刪除過。
- 對於常用的查詢字段建立索引。
- 索引的速度優勢顯而易見。未建立索引時,全表查詢是線性的。
- 使用limit避免全表檢索。
- 有的查詢明知道結果只會有一條,使用limit 1。如果查詢結果需要分頁顯示,那麼不妨使用limit,多次查詢。
- limit的偏移量較大時,先用索引進行限制
- 當limit較大時,例如select * from users limit 5000000,1;,在搜索之前會先進行500萬的偏移,相當於進行了一半的遍歷,需要根據實際情況進行優化。
- 使用正確的數據類型
- 比如phone我們常常可能會存儲為char(11),那麼在查詢時需要使用字符串類型,而非數字。(儘管mysql會對其轉義,但這依舊會增加查詢時間)
- 對於無索引的查詢條件,將能夠過濾最多記錄的where條件放在最後。
- 如果phone = '10000000'和create_time = '2018-11-05 03:22:56'都是查詢條件,而phone = '10000000'能夠過濾更多記錄,就將其寫在最右邊。
- select * from users where create_time = '2018-11-05 03:22:56' and phone = '10000000';
- 同一字段的where條件,使用in而不是or
- or的效率是接近於O(n),而in的效率是O(Log n)
實驗準備
- mysql版本:5.7.23
![Mysql優化小技巧](http://p2.ttnews.xyz/loading.gif)
- mysql版本
建表
建立一個很常見的users表
CREATE TABLE `homestead`.`users` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `name` VARCHAR(45) NOT NULL DEFAULT '用戶名', `phone` CHAR(11) NOT NULL, `status` TINYINT(1) NOT NULL DEFAULT '0', `create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`));
插入測試數據
為了直觀感受速度,先寫一個存儲過程,插入10000000(一千萬)條記錄到表中。
CREATE DEFINER=`homestead`@`%` PROCEDURE `create_user`()BEGINDECLARE i INT;START TRANSACTION;SET i=1;
WHILE i<=10000000 DO
INSERT INTO `homestead`.`users`(`name`, `phone`) VALUES ('測試用戶', i); SET i=i+1;END WHILE;COMMIT;END
通過call create_user();調用存儲過程,機器上花了兩分多鐘。
![Mysql優化小技巧](http://p2.ttnews.xyz/loading.gif)
執行存儲過程
實驗過程
1. 查詢數據總條數
常見的查詢方式是直接使用count函數,但是在數據量過大時,速度不夠快。
select count(*) from table;
通過count(id),count(*),count(1)三種方式計算,速度相差不大,都不夠快
通過count()函數查詢
通常在表設計之初,自增量id通常從1開始增長,並且每一行數據都不應該被直接delete,所以id的最大值就是總條數,因此也可以直接查詢id的最大值。
select max(id) from table;
通過id計算總量
比較之下,速度得到了極大的提高。
2. 常用字段建立索引
mysql對於主鍵會自動創建索引,在建立了索引的字段上進行查詢速度會變得非常快。
例如,我們對id(有索引)和phone(無索引)分別進行一次查詢,比較他們的速度。
有無索引對比
id建立了索引,甚至不需要0.01秒就能查詢出來。而phone因為沒有建立索引,花費了3秒的時間。由此可見索引對於查詢速度的影響極大。
3. 使用limit,避免全表索引
避免全表查詢能夠大幅提高查詢速度。有的時候我們明知道記錄可能只有一條,那麼就通過limit 1進行限制。mysql在執行時,一旦找到符合條件的記錄,達到了limit就將停止檢索,立即返回。
limit對比
4. 小插曲:無索引下的全表遍歷方式
在前面的嘗試過程中,我們似乎發現,id越小的行,總能越快查詢到,而id較大的行,速度更慢。由此我們猜測,mysql在無索引的字段上進行查詢時,是根據主鍵順序遍歷的。例如下面的時間比較:
查詢時間線性增長
可以發現,時間跟隨id變化,越來越久,而在id達到最大值時,和全表檢索的時間相差無幾。
最大id和全表檢索對比
5. 使用正確的數據類型
對於數字的字符串匹配,mysql會自動進行轉換而不會報錯,但這依舊會增加查詢時間。數據表users中的phone字段,我們是以char(11)存儲的,那麼在查詢時應該嚴格使用字符串。下面這個對比可以看出查詢的時間:不當的數據類型導致查詢時間變長。
使用正確的數據類型
6. 將過濾更多字段的where條件寫在語句的最後
對於沒有建立索引的多個where條件,mysql的執行順序是從右到左執行。
滿足phone = '10000000'的記錄只有一條,而滿足create_time = '2018-11-05 03:22:56'的卻有很多,因此phone = '10000000'能夠過濾更多記錄,應該將其寫在最右邊。
select * from users where create_time = '2018-11-05 03:22:56' and phone = '10000000';
image.png
對於建立了索引的條件,mysql會自動進行優化,優先查詢具有索引的字段。
例如select * from users where id = 10000000 and phone = '10000000' and create_time = '2018-11-05 03:22:56'這條語句,即使id=10000000寫在了最左邊,但查詢時依舊最先進行檢索,所以語句執行時間不到1ms。
建立了索引的字段不論順序先後,都優先查詢
7. 同一字段的where條件,使用in而不是or
例如要依據同一字段查詢多條記錄,應當使用in而不是or。or的複雜度更高,耗時更長。
鏈接:https://www.jianshu.com/p/59569cd496bb
鏈接:https://www.imooc.com/article/277634
閱讀更多 思夢PHP 的文章