Mysql優(yōu)化小技巧

不定時更新，記錄一些mysql優(yōu)化的技巧以及驗證的實驗豫尽。

數(shù)據(jù)量和要求

數(shù)據(jù)量：
單表一千萬條記錄以上
要求：
單條sql查詢時間不超過1秒

優(yōu)化技巧

先把每一條心得記錄在這里篙梢，后面會進(jìn)行實驗對其一一驗證。

查詢數(shù)據(jù)總條數(shù)時美旧，使用max(id)而不是count(*)進(jìn)行總量計數(shù)渤滞。
當(dāng)然，前提是id是從1開始自增長陈症，并且沒有行被刪除過蔼水。
對于常用的查詢字段建立索引。
索引的速度優(yōu)勢顯而易見录肯。未建立索引時趴腋，全表查詢是線性的。
使用limit避免全表檢索。
有的查詢明知道結(jié)果只會有一條优炬，使用limit 1颁井。如果查詢結(jié)果需要分頁顯示，那么不妨使用limit蠢护，多次查詢雅宾。
limit的偏移量較大時，先用索引進(jìn)行限制
當(dāng)limit較大時葵硕，例如select * from users limit 5000000,1;眉抬，在搜索之前會先進(jìn)行500萬的偏移，相當(dāng)于進(jìn)行了一半的遍歷懈凹，需要根據(jù)實際情況進(jìn)行優(yōu)化蜀变。
使用正確的數(shù)據(jù)類型
比如phone我們常常可能會存儲為char(11)介评，那么在查詢時需要使用字符串類型库北，而非數(shù)字。（盡管mysql會對其轉(zhuǎn)義们陆，但這依舊會增加查詢時間）
對于無索引的查詢條件寒瓦，將能夠過濾最多記錄的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)

實驗準(zhǔn)備

mysql版本：5.7.23

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`));

插入測試數(shù)據(jù)

為了直觀感受速度蜡镶，先寫一個存儲過程雾袱，插入10000000（一千萬）條記錄到表中。

CREATE DEFINER=`homestead`@`%` PROCEDURE `create_user`()
BEGIN
DECLARE 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();調(diào)用存儲過程官还，機(jī)器上花了兩分多鐘芹橡。

執(zhí)行存儲過程

實驗過程

1. 查詢數(shù)據(jù)總條數(shù)

常見的查詢方式是直接使用count函數(shù)，但是在數(shù)據(jù)量過大時望伦，速度不夠快林说。
select count(*) from table;
通過count(id),count(*),count(1)三種方式計算，速度相差不大屯伞，都不夠快

通過count()函數(shù)查詢

通常在表設(shè)計之初腿箩，自增量id通常從1開始增長，并且每一行數(shù)據(jù)都不應(yīng)該被直接delete劣摇，所以id的最大值就是總條數(shù)珠移，因此也可以直接查詢id的最大值。
select max(id) from table;

通過id計算總量

比較之下，速度得到了極大的提高钧惧。

2. 常用字段建立索引

mysql對于主鍵會自動創(chuàng)建索引暇韧，在建立了索引的字段上進(jìn)行查詢速度會變得非常快浓瞪。
例如懈玻，我們對id（有索引）和phone（無索引）分別進(jìn)行一次查詢，比較他們的速度乾颁。

有無索引對比

id建立了索引涂乌，甚至不需要0.01秒就能查詢出來。而phone因為沒有建立索引英岭，花費了3秒的時間骂倘。由此可見索引對于查詢速度的影響極大。

3. 使用limit巴席，避免全表索引

避免全表查詢能夠大幅提高查詢速度历涝。有的時候我們明知道記錄可能只有一條，那么就通過limit 1進(jìn)行限制漾唉。mysql在執(zhí)行時荧库，一旦找到符合條件的記錄，達(dá)到了limit就將停止檢索赵刑，立即返回分衫。

limit對比

4. 小插曲：無索引下的全表遍歷方式

在前面的嘗試過程中，我們似乎發(fā)現(xiàn)般此，id越小的行蚪战，總能越快查詢到，而id較大的行铐懊，速度更慢邀桑。由此我們猜測，mysql在無索引的字段上進(jìn)行查詢時科乎，是根據(jù)主鍵順序遍歷的壁畸。例如下面的時間比較：

查詢時間線性增長

可以發(fā)現(xiàn)，時間跟隨id變化茅茂，越來越久捏萍，而在id達(dá)到最大值時，和全表檢索的時間相差無幾空闲。

最大id和全表檢索對比

5. 使用正確的數(shù)據(jù)類型

對于數(shù)字的字符串匹配令杈，mysql會自動進(jìn)行轉(zhuǎn)換而不會報錯，但這依舊會增加查詢時間碴倾。數(shù)據(jù)表users中的phone字段逗噩，我們是以char(11)存儲的悔常，那么在查詢時應(yīng)該嚴(yán)格使用字符串。下面這個對比可以看出查詢的時間：不當(dāng)?shù)臄?shù)據(jù)類型導(dǎo)致查詢時間變長给赞。

使用正確的數(shù)據(jù)類型

6. 將過濾更多字段的where條件寫在語句的最后

對于沒有建立索引的多個where條件机打，mysql的執(zhí)行順序是從右到左執(zhí)行。
滿足phone = '10000000'的記錄只有一條片迅，而滿足create_time = '2018-11-05 03:22:56'的卻有很多残邀，因此phone = '10000000'能夠過濾更多記錄，應(yīng)該將其寫在最右邊柑蛇。
select * from users where create_time = '2018-11-05 03:22:56' and phone = '10000000';

image.png

對于建立了索引的條件芥挣，mysql會自動進(jìn)行優(yōu)化，優(yōu)先查詢具有索引的字段耻台。
例如select * from users where id = 10000000 and phone = '10000000' and create_time = '2018-11-05 03:22:56'這條語句空免，即使id=10000000寫在了最左邊，但查詢時依舊最先進(jìn)行檢索盆耽，所以語句執(zhí)行時間不到1ms蹋砚。

建立了索引的字段不論順序先后，都優(yōu)先查詢

7. 同一字段的where條件摄杂，使用in而不是or

例如要依據(jù)同一字段查詢多條記錄坝咐，應(yīng)當(dāng)使用in而不是or。or的復(fù)雜度更高析恢，耗時更長墨坚。