I’m back！Fighting!

0. 序言

- Mysql建表原則

具體內容可參閱Mysql的建表規範與注意事項這篇文章
本內容參照MySql表、索引設計參考規範歸納

以下只說明特別注意點：
基礎規範：
（1）表儲存引擎強烈建議使用InnoDB；
（2）表字符集預設使用utf8，必要時候使用utf8mb4；（utf8通用，無亂碼風險，漢字3位元組，英文1位元組）
（3）禁止使用儲存過程，檢視，觸發器，Event；
（4）禁止在資料庫中儲存大檔案，例如照片，可以將大檔案儲存在物件儲存系統，資料庫中儲存路徑；
命名規範：
（5）庫名，表名，列名必須用小寫，採用下劃線分隔，必須見名知義，長度不要超過32字元；
（6）庫備份必須以bak為字首以日期為字尾，從庫必須以-s為字尾，備庫必須以-ss為字尾；
表設計規範：
（7）單例項表個數必須控制在2000個以內，單表分表個數必須控制在1024個以內；
（8）表必須有主鍵，推薦使用UNSIGNED整數為主鍵；（刪除無主鍵的表，如果是row模式的主從架構，從庫會掛住）
（9）建議將大欄位，訪問頻度低的欄位拆分到單獨的表中儲存，分離冷熱資料；
列設計規範：
（10）區分使用tinyint/int/bigint，分別佔用1/4/8位元組，區分用char/varchar；（欄位長度固定用char，欄位長度相差較大用varchar）
（11）區分使用datetime/timestamp，分別佔用5/4位元組，儲存時間（精確到秒）建議使用TIMESTAMP型別；（儲存年使用YEAR，儲存日期使用DATE，儲存時間使用datetime）
（12）必須把欄位定義為NOT NULL並設預設值；
（13）NULL需更多的儲存空間，NULL只能採用 IS NULL或者 IS NOT NULL，而在=/!=/in/not in時有大坑！！！
（14）使用varchar(20)儲存手機號，不要使用整數；
（15）使用TINYINT來代替ENUM；（ENUM增加新值要進行DDL操作）
（補）儲存精確浮點數必須使用DECIMAL替代FLOAT和DOUBLE；
（補）整型定義中不新增長度，比如使用INT，而不是INT(4)；
（補）儘可能不使用TEXT、BLOB型別；
索引規範：
（16）唯一索引使用 uniq_[欄位名] 來命名；
（17）非唯一索引使用 idx_[欄位名] 來命名；
（18）單張表索引數量建議控制在5個以內，組合索引欄位數不建議超過5個；
（19）不建議在頻繁更新的欄位上建立索引；
（20）非必要不要進行JOIN查詢，如果要進行JOIN查詢，被JOIN的欄位必須型別相同，並建立索引；
（21）理解組合索引最左字首原則，避免重複建設索引，如果建立了(a,b,c)，相當於建立了(a), (a,b), (a,b,c)；
SQL規範：
（22）禁止使用select *，只獲取必要欄位；
（23）insert必須指定欄位，禁止使用insert into T values()；
（24）禁止在where條件列使用函式或者表示式；（導致不能命中索引，全表掃描）
（25）禁止負向查詢以及%開頭的模糊查詢；（導致不能命中索引，全表掃描）
（26）禁止大表JOIN和子查詢；
（27）同一個欄位上的OR必須改寫為IN，IN的值必須少於50個；
（28）WHERE條件中的非等值條件（IN、BETWEEN、<、<=、>、>=）會導致後面的條件使用不了索引；

- Mysql不同引擎下查詢對比

本內容參照mysql時間欄位建立索引和不建立索引查詢效能分析歸納得出。
表格中完整查詢語句 “select count(id) from test_table where …”。

儲存引擎	是否建索引	索引欄位	時間欄位型別	查詢語句	耗時(秒)	備註
MyISAM引擎	否	d_int	int	where d_int > 400000 AND d_int < 600000;	0.078	無索引，MyISAM引擎效能較高
MyISAM引擎	否	d_int	int	where d_int > UNIX_TIMESTAMP(‘1970-01-05 23:06:40’) AND d_int < (‘1970-01-08 06:40:00’);	0.078	無索引，MyISAM引擎效能較高
MyISAM引擎	否	d_timestamp	timestamp	where d_timestamp > ‘1970-01-05 23:06:40’ AND d_timestamp < ‘1970-01-08 06:40:00’;	0.4368	直接和日期比較查詢效率，低於UNIX_TIMESTAMP內建函式查詢
MyISAM引擎	否	d_timestamp	timestamp	where UNIX_TIMESTAMP(d_timestamp) > 400000 AND UNIX_TIMESTAMP(d_timestamp) < 600000;	0.078	UNIX_TIMESTAMP內建函式查詢效率，高出直接和日期比較
MyISAM引擎	否	d_datetime	datetime	where d_datetime > ‘1970-01-05 23:06:40’ AND d_datetime < ‘1970-01-08 06:40:00’;	0.137	直接和日期比較查詢效率，高於UNIX_TIMESTAMP內建函式查詢
MyISAM引擎	否	d_datetime	datetime	where UNIX_TIMESTAMP(d_datetime) > 400000 AND UNIX_TIMESTAMP(d_datetime) < 600000;	0.7498	UNIX_TIMESTAMP內建函式查詢效率，低出直接和日期比較
MyISAM引擎	是	d_int	int	where d_int > 400000 AND d_int < 600000;	0.39	有索引，MyISAM引擎效能反而低
MyISAM引擎	是	d_int	int	where d_int > UNIX_TIMESTAMP(‘1970-01-05 23:06:40’) AND d_int < (‘1970-01-08 06:40:00’);	0.3824	有索引，MyISAM引擎效能反而低
MyISAM引擎	是	d_timestamp	timestamp	where d_timestamp > ‘1970-01-05 23:06:40’ AND d_timestamp < ‘1970-01-08 06:40:00’;	0.5696	有無索引區別不大，直接和日期比較查詢效率，低於UNIX_TIMESTAMP內建函式查詢
MyISAM引擎	是	d_timestamp	timestamp	where UNIX_TIMESTAMP(d_timestamp) > 400000 AND UNIX_TIMESTAMP(d_timestamp) < 600000;	0.078	有無索引區別不大，UNIX_TIMESTAMP內建函式查詢效率，高出直接和日期比較
MyISAM引擎	是	d_datetime	datetime	where d_datetime > ‘1970-01-05 23:06:40’ AND d_datetime < ‘1970-01-08 06:40:00’;	0.4508	有索引的效率反而低
MyISAM引擎	是	d_datetime	datetime	where UNIX_TIMESTAMP(d_datetime) > 400000 AND UNIX_TIMESTAMP(d_datetime) < 600000;	0.7614	有索引的效率反而低
InnoDB引擎	否	d_int	int	where d_int > 400000 AND d_int < 600000;	0.3198	無索引，InnoDB引擎效能較MyISAM引擎降低
InnoDB引擎	否	d_int	int	where d_int > UNIX_TIMESTAMP(‘1970-01-05 23:06:40’) AND d_int < (‘1970-01-08 06:40:00’);	0.3092	無索引，InnoDB引擎效能較MyISAM引擎降低
InnoDB引擎	否	d_timestamp	timestamp	where d_timestamp > ‘1970-01-05 23:06:40’ AND d_timestamp < ‘1970-01-08 06:40:00’;	0.7092	直接和日期比較查詢效率，低於UNIX_TIMESTAMP內建函式查詢
InnoDB引擎	否	d_timestamp	timestamp	where UNIX_TIMESTAMP(d_timestamp) > 400000 AND UNIX_TIMESTAMP(d_timestamp) < 600000;	0.316	UNIX_TIMESTAMP內建函式查詢效率，高出直接和日期比較
InnoDB引擎	否	d_datetime	datetime	where d_datetime > ‘1970-01-05 23:06:40’ AND d_datetime < ‘1970-01-08 06:40:00’;	0.3834	直接和日期比較查詢效率，高於UNIX_TIMESTAMP內建函式查詢
InnoDB引擎	否	d_datetime	datetime	where UNIX_TIMESTAMP(d_datetime) > 400000 AND UNIX_TIMESTAMP(d_datetime) < 600000;	0.9794	UNIX_TIMESTAMP內建函式查詢效率，低出直接和日期比較
InnoDB引擎	是	d_int	int	where d_int > 400000 AND d_int < 600000;	0.0522	有索引，InnoDB引擎效能較MyISAM有大幅提高
InnoDB引擎	是	d_int	int	where d_int > UNIX_TIMESTAMP(‘1970-01-05 23:06:40’) AND d_int < (‘1970-01-08 06:40:00’);	0.0624	有索引，InnoDB引擎效能較MyISAM有大幅提高
InnoDB引擎	是	d_timestamp	timestamp	where d_timestamp > ‘1970-01-05 23:06:40’ AND d_timestamp < ‘1970-01-08 06:40:00’;	0.1776	直接和日期比較查詢效率，高於UNIX_TIMESTAMP內建函式查詢
InnoDB引擎	是	d_timestamp	timestamp	where UNIX_TIMESTAMP(d_timestamp) > 400000 AND UNIX_TIMESTAMP(d_timestamp) < 600000;	0.2944	UNIX_TIMESTAMP內建函式查詢效率，低出直接和日期比較
InnoDB引擎	是	d_datetime	datetime	where d_datetime > ‘1970-01-05 23:06:40’ AND d_datetime < ‘1970-01-08 06:40:00’;	0.082	直接和日期比較查詢效率，高於UNIX_TIMESTAMP內建函式查詢
InnoDB引擎	是	d_datetime	datetime	where UNIX_TIMESTAMP(d_datetime) > 400000 AND UNIX_TIMESTAMP(d_datetime) < 600000;	0.9994	UNIX_TIMESTAMP內建函式查詢效率，低出直接和日期比較

總結與建議：
（1）MyISAM引擎下，推薦在無索引進行查詢，未建立索引效率從高到低：
int > UNIX_TIMESTAMP(timestamp) > datetime（直接和時間比較）>timestamp（直接和時間比較）>UNIX_TIMESTAMP(datetime) 。

（2）InnoDB引擎下，推薦在有索引進行查詢，不建議在無索引下進行查詢，建立索引效率從高到低：
int > datetime（直接和時間比較） > timestamp（直接和時間比較）> UNIX_TIMESTAMP(timestamp) > UNIX_TIMESTAMP(datetime)。

對於MyISAM引擎，採用 UNIX_TIMESTAMP(timestamp) 比較；對於InnoDB引擎，建立索引，採用 int 或 datetime直接時間比較。

- Mysql查詢語句執行計劃

1. MySQL時間型別欄位的範圍查詢不走索引了？

• 情況一：時間索引使用函式會失效

# 索引失效sql，create_time使用了DATE_FORMAT()函式
SELECT * FROM test_table WHERE DATE_FORMAT(create_time,"%Y-%m-%d") >= '2020-10-27';

# 索引生效sql
SELECT * FROM test_table WHERE create_time >= str_to_date('2020-10-27', '%Y-%m-%d')

• 情況二：查詢優化器影響
  我們建的索引並不是總會起作用的，中間有查詢優化器插足，它會判斷一個查詢SQL是否走索引查得更快，若是，就走索引，否則做全表掃描。以前有個百分比（30%）決定SQL是走索引還是走全表掃描，就是說如果總共有100行記錄，走索引查詢出來的記錄超過30條，那還不如不走索引了。但是現在MySQL不這麼幹了，不只通過這個百分比來決定走不走索引，而是要參考更多因素來做決定。

未完待續。。。。。