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

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

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

優(yōu)化技巧

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

1. 查詢數(shù)據(jù)總條數(shù)時(shí)，使用max(id)而不是count(*)進(jìn)行總量計(jì)數(shù)。
   當(dāng)然，前提是id是從1開始自增長，并且沒有行被刪除過。
2. 對于常用的查詢字段建立索引。
   索引的速度優(yōu)勢顯而易見。未建立索引時(shí)，全表查詢是線性的。
3. 使用limit避免全表檢索。
   有的查詢明知道結(jié)果只會(huì)有一條，使用limit 1。如果查詢結(jié)果需要分頁顯示，那么不妨使用limit，多次查詢。
4. limit的偏移量較大時(shí)，先用索引進(jìn)行限制
   當(dāng)limit較大時(shí)，例如select * from users limit 5000000,1;，在搜索之前會(huì)先進(jìn)行500萬的偏移，相當(dāng)于進(jìn)行了一半的遍歷，需要根據(jù)實(shí)際情況進(jìn)行優(yōu)化。
5. 使用正確的數(shù)據(jù)類型
   比如phone我們常?？赡軙?huì)存儲(chǔ)為char(11)，那么在查詢時(shí)需要使用字符串類型，而非數(shù)字。（盡管mysql會(huì)對其轉(zhuǎn)義，但這依舊會(huì)增加查詢時(shí)間）
6. 對于無索引的查詢條件，將能夠過濾最多記錄的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';
7. 同一字段的where條件，使用in而不是or
   or的效率是接近于O(n)，而in的效率是O(Log n)

實(shí)驗(yàn)準(zhǔn)備

• mysql版本：5.7.23

  
  
  mysql版本

建表

建立一個(gè)很常見的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ù)

為了直觀感受速度，先寫一個(gè)存儲(chǔ)過程，插入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)用存儲(chǔ)過程，機(jī)器上花了兩分多鐘。

執(zhí)行存儲(chǔ)過程

實(shí)驗(yàn)過程

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

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

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

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

通過id計(jì)算總量

2. 常用字段建立索引

mysql對于主鍵會(huì)自動(dòng)創(chuàng)建索引，在建立了索引的字段上進(jìn)行查詢速度會(huì)變得非?？臁?br> 例如，我們對id（有索引）和phone（無索引）分別進(jìn)行一次查詢，比較他們的速度。

有無索引對比

id建立了索引，甚至不需要0.01秒就能查詢出來。而phone因?yàn)闆]有建立索引，花費(fèi)了3秒的時(shí)間。由此可見索引對于查詢速度的影響極大。

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

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

limit對比

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

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

查詢時(shí)間線性增長

最大id和全表檢索對比

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

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

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

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

對于沒有建立索引的多個(gè)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

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

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

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

image.png