一、分頁查詢優化很多時候我們業務系統實現分頁功能可能會用如下sql實現：
select*fromemployeeslimit10000,10;表示從表 employees 中取出從 10001 行開始的 10 行記錄。看似只查詢了 10 條記錄，實際這條 SQL 是先讀取 10010條記錄，然後拋棄前 10000 條記錄，然後讀到後面 10 條想要的數據。因此要查詢一張大表比較靠後的數據，執行效率是非常低的。這是典型的深度分頁問題。
分頁場景優化技巧1、根據自增且連續的主鍵排序的分頁查詢首先來看一個根據自增且連續主鍵排序的分頁查詢的例子：
select*fromemployeeslimit90000,5;
該 SQL 表示查詢從第 90001開始的五行數據，沒添加單獨 order by，表示通過主鍵排序。我們再看表 employees ，因為主鍵是自增並且連續的，所以可以改寫成按照主鍵去查詢從第 90001開始的五行數據，如下：
select*fromemployeeswhereid>90000limit5;
查詢的結果是一致的。我們再對比一下執行計劃：
EXPLAINselect*fromemployeeslimit90000,5;
EXPLAINselect*fromemployeeswhereid>90000limit5;
顯然改寫後的 SQL 走了索引，而且掃描的行數大大減少，執行效率更高。
但是，這條改寫的SQL 在很多場景並不實用，因為表中可能某些記錄被刪後，主鍵空缺，導致結果不一致，如下圖試驗所示（先刪除一條前面的記錄，然後再測試原 SQL 和優化後的 SQL）：
兩條 SQL 的結果並不一樣，因此，如果主鍵不連續，不能使用上面描述的優化方法。
另外如果原 SQL 是 order by 非主鍵的字段，按照上面說的方法改寫會導致兩條 SQL 的結果不一致。所以這種改寫得滿足以下兩個條件：
2、根據非主鍵字段排序的分頁查詢再看一個根據非主鍵字段排序的分頁查詢，SQL 如下：
select*fromemployeesORDERBYnamelimit90000,5;
EXPLAINselect*fromemployeesORDERBYnamelimit90000,5;
發現並沒有使用 name 字段的索引（key 字段對應的值為 null），具體原因是：掃描整個索引並查找到沒索引的行(可能要遍歷多個索引樹)的成本比掃描全表的成本更高，所以優化器放棄使用索引。
知道不走索引的原因，那麼怎麼優化呢？
其實關鍵是讓排序時返回的字段儘可能少，即考慮使用覆蓋索引進行優化，所以可以讓排序和分頁操作先查出主鍵，然後根據主鍵查到對應的記錄，SQL改寫如下:
select*fromemployeeseinnerjoin(selectidfromemployeesorderbynamelimit90000,5)edone.id=ed.id;
需要的結果與原 SQL 一致，執行時間減少了一半以上，此時查詢和排序都是在覆蓋索引樹上進行的，所以效率較高。我們再對比優化前後sql的執行計劃：
原 SQL 使用的是 file sort 排序，而優化後的 SQL 使用的是索引排序。
根據id判斷，會先執行id = 2的sql，此時使用了覆蓋索引，排序和查詢都是在索引樹上完成的。然後執行id=1的sql，這裡使用了eq_ref，即主鍵索引。最後執行join關聯的那張表，因為此時的table是derived, 是前面兩張表的關聯表，總共有5條記錄，所以即使全表掃描，也是比較快的。二、Join關聯查詢優化示例表：
‐‐示例表：CREATETABLE`t1`(`id`int(11)NOTNULLAUTO_INCREMENT,`a`int(11)DEFAULTNULL,`b`int(11)DEFAULTNULL,PRIMARYKEY(`id`),KEY`idx_a`(`a`))ENGINE=InnoDBDEFAULTCHARSET=utf8;createtablet2liket1;‐‐插入一些示例數據‐‐往t1表插入1萬行記錄dropprocedureifexistsinsert_t1;delimiter;;createprocedureinsert_t1()begindeclareiint;seti=1;while(i<=10000)doinsertintot1(a,b)values(i,i);seti=i+1;endwhile;end;;delimiter;callinsert_t1();‐‐往t2表插入100行記錄dropprocedureifexistsinsert_t2;delimiter;;createprocedureinsert_t2()begindeclareiint;seti=1;while(i<=100)doinsertintot2(a,b)values(i,i);seti=i+1;endwhile;end;;delimiter;callinsert_t2();mysql的表關聯常見有兩種算法
Block Nested-Loop Join 算法1、 嵌套循環連接 Nested-Loop Join(NLJ) 算法一次一行循環地從第一張表（稱為驅動表）中讀取行，在這行數據中取到關聯字段，根據關聯字段在另一張表（被驅動表）里取出滿足條件的行，然後取出兩張表的結果合集。
EXPLAINselect*fromt1innerjoint2ont1.a=t2.a;--//a字段有索引
從執行計劃中可以看到這些信息：
驅動表是 t2，被驅動表是 t1。先執行的就是驅動表(執行計劃結果的id如果一樣則按從上到下順序執行sql)；優化器一般會優先選擇小表做驅動表。所以使用inner join時，排在前面的表並不一定就是驅動表。當使用left join時，左表是驅動表，右表是被驅動表，當使用right join時，右表是驅動表，左表是被驅動表，當使用join時，mysql會選擇數據量比較小的表作為驅動表，大表作為被驅動表。使用了 NLJ算法。一般 join 語句中，如果執行計劃 Extra 中未出現Using join buffer則表示使用的 join 算法是 NLJ。上面sql的大致流程如下：
從表 t2 中讀取一行數據（如果t2表有查詢過濾條件的，會從過濾結果里取出一行數據）；從第 1 步的數據中，取出關聯字段 a，到表 t1 中查找；取出表 t1 中滿足條件的行，跟 t2 中獲取到的結果合併，作為結果返回給客戶端；整個過程會讀取 t2 表的所有數據(掃描100行)，然後遍歷這每行數據中字段 a 的值，根據 t2 表中 a 的值索引掃描 t1 表中的對應行(掃描100次 t1 表的索引，1次掃描可以認為最終只掃描 t1 表一行完整數據，也就是總共 t1 表也掃描了100行)。因此整個過程掃描了 200 行。
2、 基於塊的嵌套循環連接 Block Nested-Loop Join(BNL)算法把驅動表的數據讀入到join_buffer中，然後掃描被驅動表，把被驅動表每一行取出來跟join_buffer中的數據做對比。
EXPLAINselect*fromt1innerjoint2ont1.b=t2.b;--//b字段沒有索引
Extra 中 的Using join buffer (Block Nested Loop)說明該關聯查詢使用的是 BNL 算法。
上面sql的大致流程如下：
把 t2 的所有數據放入到join_buffer中把表 t1 中每一行取出來，跟join_buffer中的數據做對比整個過程對表 t1 和 t2 都做了一次全表掃描，因此掃描的總行數為10000(表 t1 的數據總量) + 100(表 t2 的數據總量) =10100。並且join_buffer里的數據是無序的，因此對表 t1 中的每一行，都要做 100 次判斷，所以內存中的判斷次數是100 * 10000= 100萬次。
這個例子裡表 t2 才 100 行，要是表 t2 是一個大表，join_buffer 放不下怎麼辦呢？
join_buffer 的大小是由參數join_buffer_size設定的，默認值是 256k。如果放不下表 t2 的所有數據話，策略很簡單，就是分段放。
比如 t2 表有1000行記錄，join_buffer一次只能放800行數據，那麼執行過程就是先往join_buffer里放800行記錄，然後從 t1 表里取數據跟join_buffer中數據對比得到部分結果，然後清空join_buffer，再放入 t2 表剩餘200行記錄，再次從 t1 表里取數據跟join_buffer中數據對比。所以就多掃了一次 t1 表。
被驅動表的關聯字段沒索引為什麼要選擇使用 BNL 算法而不使用Nested-Loop Join呢？
如果上面第二條sql使用Nested-Loop Join，那麼掃描行數為100 * 10000 = 100萬次，這個是磁盤掃描。
很顯然，用BNL磁盤掃描次數少很多，相比於磁盤掃描，BNL的內存計算會快得多。因此MySQL對於被驅動表的關聯字段沒索引的關聯查詢，一般都會使用 BNL 算法。如果有索引一般選擇 NLJ 算法，有索引的情況下 NLJ 算法比 BNL算法性能更高.
對於關聯sql的優化關聯字段加索引，讓mysql做join操作時儘量選擇NLJ算法小表驅動大表，寫多表連接sql時如果明確知道哪張表是小表可以用straight_join寫法固定連接驅動方式，省去mysql優化器自己判斷的時間straight_join解釋：straight_join功能同join類似，但能讓左邊的表來驅動右邊的表，能改表優化器對於聯表查詢的執行順序。
比如：select * from t2 straight_join t1 on t2.a = t1.a;代表指定mysql選着 t2 表作為驅動表。
straight_join只適用於inner join，並不適用於left join，right join。（因為left join，right join已經代表指定了表的執行順序）儘可能讓優化器去判斷，因為大部分情況下mysql優化器是比人要聰明的。使用straight_join一定要慎重，因為部分情況下人為指定的執行順序並不一定會比優化引擎要靠譜。對於小表定義的明確：在決定哪個表做驅動表的時候，應該是兩個表按照各自的條件過濾，過濾完成之後，計算參與 join 的各個字段的總數據量，數據量小的那個表，就是「小表」，應該作為驅動表。
三、in和exsits優化原則：小表驅動大表，即小的數據集驅動大的數據集。
In是In後的表先執行（適用於B表小於A表）：
select*fromAwhereidin(selectidfromB)Exists是Exists前面的表先執行（適用於A表小於B表）:
select*fromAwhereidin(selectidfromB)in：當B表的數據集小於A表的數據集時，in優於existsselect*fromAwhereidin(selectidfromB)//#等價於：for(selectidfromB){select*fromAwhereA.id=B.id}exists：當A表的數據集小於B表的數據集時，exists優於in將主查詢A的數據，放到子查詢B中做條件驗證，根據驗證結果（true或false）來決定主查詢的數據是否保留.
select*fromAwhereexists(select1fromBwhereB.id=A.id)//#等價於:for(select*fromA){select*fromBwhereB.id=A.id}//#A表與B表的ID字段應建立索引總結：1、EXISTS (subquery)只返回TRUE或FALSE,因此子查詢中的SELECT *也可以用SELECT 1替換,官方說法是實際執行時會忽略SELECT清單,因此沒有區別
2、EXISTS子查詢的實際執行過程可能經過了優化而不是我們理解上的逐條對比
3、EXISTS子查詢往往也可以用JOIN來代替，何種最優需要具體問題具體分析
四、count(*)查詢優化--臨時關閉mysql查詢緩存，為了查看sql多次執行的真實時間setglobalquery_cache_size=0;setglobalquery_cache_type=0;EXPLAINselectcount(1)fromemployees;EXPLAINselectcount(id)fromemployees;EXPLAINselectcount(name)fromemployees;EXPLAINselectcount(*)fromemployees;--注意：以上4條sql只有根據某個字段count不會統計字段
經過測試發現：四個sql的執行計劃一樣，說明這四個sql執行效率應該差不多
1、字段有索引：count(*)≈count(1)>count(字段)>count(主鍵 id)
字段有索引，count(字段)統計走二級索引，二級索引存儲數據比主鍵索引少，所以count(字段)>count(主鍵 id)
2、字段無索引:count(*)≈count(1)>count(主鍵 id)>count(字段)
字段沒有索引count(字段)統計走不了索引，count(主鍵 id)還可以走主鍵索引，所以count(主鍵 id)>count(字段)
count(1)跟count(字段)執行過程類似，不過count(1)不需要取出字段統計，就用常量1做統計，count(字段)還需要取出字段，所以理論上count(1)比count(字段)會快一點。
count(*)是例外，mysql並不會把全部字段取出來，而是專門做了優化(5.7版本)，不取值，按行累加，效率很高，所以不需要用count(列名)或count(常量)來替代count(*)。
為什麼對於count(id)，mysql最終選擇輔助索引而不是主鍵聚集索引？因為二級索引相對主鍵索引存儲數據更少，檢索性能應該更高，mysql內部做了點優化(應該是在5.7版本才優化)。
常見優化方法當表中數據量非常大的時候，count這種通過計算統計的都會很慢，所以需要一些優化手段。
1、查詢mysql自己維護的總行數對於myisam存儲引擎的表做不帶where條件的count查詢性能是很高的，因為myisam存儲引擎的表的總行數會被mysql存儲在磁盤上，查詢不需要計算.
對於innodb存儲引擎的表mysql不會存儲表的總記錄行數(因為有MVCC機制，後面會講)，查詢count需要實時計算.
2、show table status如果只需要知道表總行數的估計值可以用如下sql查詢，性能很高
3、將總數維護到Redis里插入或刪除表數據行的時候同時維護redis里的表總行數key的計數值(用incr或decr命令)，但是這種方式可能不准，很難保證表操作和redis操作的事務一致性.
4、增加數據庫計數表插入或刪除表數據行的時候同時維護計數表，讓他們在同一個事務里操作
五、阿里MySQL規範解讀(一) 建表規約【強制】表達是與否概念的字段，必須使用is_xxx的方式命名，數據類型是unsigned tinyint（1表示是，0表示否）。說明：任何字段如果為非負數，必須是unsigned。注意：POJO類中的任何布爾類型的變量，都不要加is前綴，所以，需要在設置從is_xxx到Xxx的映射關係。數據庫表示是與否的值，使用tinyint類型，堅持is_xxx的命名方式是為了明確其取值含義與取值範圍。正例：表達邏輯刪除的字段名is_deleted，1表示刪除，0表示未刪除。【強制】表名、字段名必須使用小寫字母或數字，禁止出現數字開頭，禁止兩個下劃線中間只出現數字。數據庫字段名的修改代價很大，因為無法進行預發布，所以字段名稱需要慎重考慮。說明：MySQL在Windows下不區分大小寫，但在Linux下默認是區分大小寫。因此，數據庫名、表名、字段名，都不允許出現任何大寫字母，避免節外生枝。正例：aliyun_admin，rdc_config，level3_name反例：AliyunAdmin，rdcConfig，level_3_name【強制】表名不使用複數名詞。說明：表名應該僅僅表示表裡面的實體內容，不應該表示實體數量，對應於DO類名也是單數形式，符合表達習慣。【強制】禁用保留字，如desc、range、match、delayed等，請參考MySQL官方保留字。【強制】主鍵索引名為pk_字段名；唯一索引名為uk_字段名；普通索引名則為idx_字段名。說明：pk_即primary key；uk_即unique key；idx_即index的簡稱。【強制】小數類型為decimal，禁止使用float和double。說明：在存儲的時候，float 和 double 都存在精度損失的問題，很可能在比較值的時候，得到不正確的結果。如果存儲的數據範圍超過 decimal 的範圍，建議將數據拆成整數和小數並分開存儲。【強制】如果存儲的字符串長度幾乎相等，使用char定長字符串類型。【強制】varchar是可變長字符串，不預先分配存儲空間，長度不要超過5000，如果存儲長度大於此值，定義字段類型為text，獨立出來一張表，用主鍵來對應，避免影響其它字段索引效率。【強制】表必備三字段：id, gmt_create,gmt_modified。說明：其中id必為主鍵，類型為bigint unsigned、單表時自增、步長為1。gmt_create,gmt_modified的類型均為datetime類型，前者現在時表示主動式創建，後者過去分詞表示被動式更新。【推薦】表的命名最好是遵循「業務名稱_表的作用」。正例：alipay_task/force_project/trade_config【推薦】如果修改字段含義或對字段表示的狀態追加時，需要及時更新字段注釋。【推薦】字段允許適當冗餘，以提高查詢性能，但必須考慮數據一致。冗餘字段應遵循：1） 不是頻繁修改的字段。2） 不是唯一索引的字段。3） 不是varchar超長字段，更不能是text字段。正例：各業務線經常冗餘存儲商品名稱，避免查詢時需要調用IC服務獲取。【推薦】單表行數超過500萬行或者單表容量超過2GB，才推薦進行分庫分表。說明：如果預計三年後的數據量根本達不到這個級別，請不要在創建表時就分庫分表。【參考】合適的字符存儲長度，不但節約數據庫表空間、節約索引存儲，更重要的是提升檢索速度。正例：無符號值可以避免誤存負數，且擴大了表示範圍。
(二) 索引規約【強制】業務上具有唯一特性的字段，即使是組合字段，也必須建成唯一索引。說明：不要以為唯一索引影響了insert速度，這個速度損耗可以忽略，但提高查找速度是明顯的；另外，即使在應用層做了非常完善的校驗控制，只要沒有唯一索引，根據墨菲定律，必然有髒數據產生。【強制】超過三個表禁止join。需要join的字段，數據類型保持絕對一致；多表關聯查詢時，保證被關聯的字段需要有索引。說明：即使雙表join也要注意表索引、SQL性能。【強制】在varchar字段上建立索引時，必須指定索引長度，沒必要對全字段建立索引，根據實際文本區分度決定索引長度。說明：索引的長度與區分度是一對矛盾體，一般對字符串類型數據，長度為20的索引，區分度會高達90%以上，可以使用count(distinct left(列名, 索引長度))/count(*)的區分度來確定。【強制】頁面搜索嚴禁左模糊或者全模糊，如果需要請走搜索引擎來解決。說明：索引文件具有B-Tree的最左前綴匹配特性，如果左邊的值未確定，那麼無法使用此索引。【推薦】如果有order by的場景，請注意利用索引的有序性。order by最後的字段是組合索引的一部分，並且放在索引組合順序的最後，避免出現file_sort的情況，影響查詢性能。正例：where a=? and b=? order by c;索引：a_b_c反例：索引如果存在範圍查詢，那麼索引有序性無法利用，如：WHERE a>10 ORDER BY b;索引a_b無法排序。【推薦】利用覆蓋索引來進行查詢操作，避免回表。說明：如果一本書需要知道第11章是什麼標題，會翻開第11章對應的那一頁嗎？目錄瀏覽一下就好，這個目錄就是起到覆蓋索引的作用。正例：能夠建立索引的種類分為主鍵索引、唯一索引、普通索引三種，而覆蓋索引只是一種查詢的一種效果，用explain的結果，extra列會出現：using index。【推薦】利用延遲關聯或者子查詢優化超多分頁場景。說明：MySQL並不是跳過offset行，而是取offset+N行，然後返回放棄前offset行，返回N行，那當offset特別大的時候，效率就非常的低下，要麼控制返回的總頁數，要麼對超過特定閾值的頁數進行SQL改寫。正例：先快速定位需要獲取的id段，然後再關聯：SELECT a.* FROM 表1 a, (select id from 表1 where 條件 LIMIT 100000,20 ) b where a.id=b.id【推薦】SQL性能優化的目標：至少要達到 range 級別，要求是ref級別，如果可以是consts最好。說明：1） consts 單表中最多只有一個匹配行（主鍵或者唯一索引），在優化階段即可讀取到數據。2） ref 指的是使用普通的索引（normal index）。3） range 對索引進行範圍檢索。反例：explain表的結果，type=index，索引物理文件全掃描，速度非常慢，這個index級別比較range還低，與全表掃描是小巫見大巫。【推薦】建組合索引的時候，區分度最高的在最左邊。正例：如果where a=? and b=?，a列的幾乎接近於唯一值，那麼只需要單建idx_a索引即可。說明：存在非等號和等號混合判斷條件時，在建索引時，請把等號條件的列前置。如：where c>? and d=?那麼即使c的區分度更高，也必須把d放在索引的最前列，即建立組合索引idx_d_c。【推薦】防止因字段類型不同造成的隱式轉換，導致索引失效。【參考】創建索引時避免有如下極端誤解：1） 索引寧濫勿缺。認為一個查詢就需要建一個索引。2） 吝嗇索引的創建。認為索引會消耗空間、嚴重拖慢記錄的更新以及行的新增速度。3） 抵制惟一索引。認為惟一索引一律需要在應用層通過「先查後插」方式解決。(三) SQL語句【強制】不要使用count(列名)或count(常量)來替代count()，count()是SQL92定義的標準統計行數的語法，跟數據庫無關，跟NULL和非NULL無關。說明：count(*)會統計值為NULL的行，而count(列名)不會統計此列為NULL值的行。【強制】count(distinct col)計算該列除NULL之外的不重複行數，注意count(distinct col1, col2)如果其中一列全為NULL，那麼即使另一列有不同的值，也返回為0。【強制】當某一列的值全是NULL時，count(col)的返回結果為0，但sum(col)的返回結果為NULL，因此使用sum()時需注意NPE問題。正例：可以使用如下方式來避免sum的NPE問題：SELECT IFNULL(SUM(column), 0) FROM table;【強制】使用ISNULL()來判斷是否為NULL值。說明：NULL與任何值的直接比較都為NULL。1）NULL<>NULL的返回結果是NULL，而不是false。2）NULL=NULL的返回結果是NULL，而不是true。3）NULL<>1的返回結果是NULL，而不是true。反例：在SQL語句中，如果在null前換行，影響可讀性。select * from table where column1 is null and column3 is not null;而ISNULL(column)是一個整體，簡潔易懂。從性能數據上分析，ISNULL(column)執行效率更快一些。【強制】代碼中寫分頁查詢邏輯時，若count為0應直接返回，避免執行後面的分頁語句。【強制】不得使用外鍵與級聯，一切外鍵概念必須在應用層解決。說明：（概念解釋）學生表中的student_id是主鍵，那麼成績表中的student_id則為外鍵。如果更新學生表中的student_id，同時觸發成績表中的student_id更新，即為級聯更新。外鍵與級聯更新適用於單機低並發，不適合分布式、高並發集群；級聯更新是強阻塞，存在數據庫更新風暴的風險；外鍵影響數據庫的插入速度。【強制】禁止使用存儲過程，存儲過程難以調試和擴展，更沒有移植性。【強制】數據訂正（特別是刪除或修改記錄操作）時，要先select，避免出現誤刪除，確認無誤才能執行更新語句。【強制】對於數據庫中表記錄的查詢和變更，只要涉及多個表，都需要在列名前加表的別名（或表名）進行限定。說明：對多表進行查詢記錄、更新記錄、刪除記錄時，如果對操作列沒有限定表的別名（或表名），並且操作列在多個表中存在時，就會拋異常。正例：select t1.name from table_first as t1 , table_second as t2 where t1.id=t2.id;反例：在某業務中，由於多表關聯查詢語句沒有加表的別名（或表名）的限制，正常運行兩年後，最近在某個表中增加一個同名字段，在預發布環境做數據庫變更後，線上查詢語句出現出1052異常：Column 『name』 in field list is ambiguous。【推薦】SQL語句中表的別名前加as，並且以t1、t2、t3、…的順序依次命名。說明：1）別名可以是表的簡稱，或者是根據表出現的順序，以t1、t2、t3的方式命名。2）別名前加as使別名更容易識別。正例：select t1.name from table_first as t1, table_second as t2 where t1.id=t2.id;【推薦】in操作能避免則避免，若實在避免不了，需要仔細評估in後邊的集合元素數量，控制在1000個之內。【參考】因國際化需要，所有的字符存儲與表示，均採用utf8字符集，那麼字符計數方法需要注意。說明：SELECT LENGTH(「輕鬆工作」)；返回為12SELECT CHARACTER_LENGTH(「輕鬆工作」)；返回為4 如果需要存儲表情，那麼選擇utf8mb4來進行存儲，注意它與utf8編碼的區別。【參考】TRUNCATE TABLE比 DELETE 速度快，且使用的系統和事務日誌資源少，但TRUNCATE無事務且不觸發trigger，有可能造成事故，故不建議在開發代碼中使用此語句。說明：TRUNCATE TABLE在功能上與不帶 WHERE 子句的 DELETE 語句相同。(四) ORM映射【強制】在表查詢中，一律不要使用*作為查詢的字段列表，需要哪些字段必須明確寫明。說明：1）增加查詢分析器解析成本。2）增減字段容易與resultMap配置不一致。3）無用字段增加網絡消耗，尤其是text類型的字段。【強制】POJO類的布爾屬性不能加is，而數據庫字段必須加is_，要求在resultMap中進行字段與屬性之間的映射。說明：參見定義POJO類以及數據庫字段定義規定，在sql.xml增加映射，是必須的。【強制】不要用resultClass當返回參數，即使所有類屬性名與數據庫字段一一對應，也需要定義；反過來，每一個表也必然有一個與之對應。說明：配置映射關係，使字段與DO類解耦，方便維護。【強制】sql.xml配置參數使用：#{}，#param#不要使用${}此種方式容易出現SQL注入。【強制】iBATIS自帶的queryForList(String statementName,int start,int size)不推薦使用。說明：其實現方式是在數據庫取到statementName對應的SQL語句的所有記錄，再通過subList取start,size的子集合。正例：
Map<String,Object>map=newHashMap<>();map.put(「start」,start);map.put(「size」,size);【強制】不允許直接拿HashMap與Hashtable作為查詢結果集的輸出。反例：某同學為避免寫一個，直接使用HashTable來接收數據庫返回結果，結果出現日常是把bigint轉成Long值，而線上由於數據庫版本不一樣，解析成BigInteger，導致線上問題。【強制】更新數據表記錄時，必須同時更新記錄對應的gmt_modified字段值為當前時間。【推薦】不要寫一個大而全的數據更新接口。傳入為POJO類，不管是不是自己的目標更新字段，都進行update table set c1=value1,c2=value2,c3=value3;這是不對的。執行SQL時，不要更新無改動的字段，一是易出錯；二是效率低；三是增加binlog存儲。【參考】@Transactional事務不要濫用。事務會影響數據庫的QPS，另外使用事務的地方需要考慮各方面的回滾方案，包括緩存回滾、搜索引擎回滾、消息補償、統計修正等。【參考】中的compareValue是與屬性值對比的常量，一般是數字，表示相等時帶上此條件；表示不為空且不為null時執行；表示不為null值時執行。六、MySQL數據類型選擇在MySQL中，選擇正確的數據類型，對於性能至關重要。一般應該遵循下面兩步：
在MySQL數據類型設置方面，儘量用更小的數據類型，因為它們通常有更好的性能，花費更少的硬件資源。並且，儘量 把字段定義為NOT NULL，避免使用NULL.
1、數值類型
優化建議：
如果整形數據沒有負數，如ID號，建議指定為UNSIGNED無符號類型，容量可以擴大一倍。建議使用TINYINT代替ENUM、BITENUM、SET。避免使用整數的顯示寬度(參看文檔最後)，也就是說，不要用INT(10)類似的方法指定字段顯示寬度，直接用INT。DECIMAL最適合保存準確度要求高，而且用於計算的數據，比如價格。但是在使用DECIMAL類型的時候，注意長度設置。建議使用整形類型來運算和存儲實數，方法是，實數乘以相應的倍數後再操作。整數通常是最佳的數據類型，因為它速度快，並且能使用AUTO_INCREMENT。2、日期和時間
優化建議：
建議用DATE數據類型來保存日期。MySQL中默認的日期格式是yyyy-mm-dd。用MySQL的內建類型DATE、TIME、DATETIME來存儲時間，而不是使用字符串。當數據格式為TIMESTAMP和DATETIME時，可以用CURRENT_TIMESTAMP作為默認（MySQL5.6以後），MySQL會自動返回記錄插入的確切時間。DATETIME的存儲格式是一個YYYYMMDD HH:MM:SS的整數，與時區無關，你存了什麼，讀出來就是什麼。除非有特殊需求，一般的公司建議使用TIMESTAMP，它比DATETIME更節約空間，但是像阿里這樣的公司一會用DATETIME，因為不用考慮TIMESTAMP將來的時間上限問題。有時人們把Unix的時間戳保存為整數值，但是這通常沒有任何好處，這種格式處理起來不太方便，我們並不推薦它。3、字符串
優化建議:
字符串的長度相差較大用VARCHAR；字符串短，且所有值都接近一個長度用CHAR。CHAR和VARCHAR適用於包括人名、郵政編碼、電話號碼和不超過255個字符長度的任意字母數字組合。那些要用來計算的數字不要用VARCHAR類型保存，因為可能會導致一些與計算相關的問題。換句話說，可能影響到計算的準確性和完整性。儘量少用BLOB和TEXT，如果實在要用可以考慮將BLOB和TEXT字段單獨存一張表，用id關聯。BLOB系列存儲二進制字符串，與字符集無關。TEXT系列存儲非二進制字符串，與字符集相關。PS：INT顯示寬度我們經常會使用命令來創建數據表，而且同時會指定一個長度，如下。但是，這裡的長度並非是TINYINT類型存儲的最大長度，而是顯示的最大長度。
CREATETABLE`user`(`id`TINYINT(2)UNSIGNED);這裡表示user表的id字段的類型是TINYINT，可以存儲的最大數值是255。所以，在存儲數據時，如果存入值小於等於255，如200，雖然超過2位，但是沒有超出TINYINT類型長度，所以可以正常保存；如果存入值大於255，如500，那麼MySQL會自動保存為TINYINT類型的最大值255。
在查詢數據時，不管查詢結果為何值，都按實際輸出。這裡TINYINT(2)中2的作用就是，當需要在查詢結果前填充0時，命令中加上ZEROFILL就可以實現，如：
`id`TINYINT(2)UNSIGNEDZEROFILL這樣，查詢結果如果是5，那輸出就是05。如果指定TINYINT(5)，那輸出就是00005，其實實際存儲的值還是5，而且存 儲的數據不會超過255，只是MySQL輸出數據時在前面填充了0。
換句話說，在MySQL命令中，字段的類型長度TINYINT(2)、INT(11)不會影響數據的插入，只會在使用ZEROFILL時有 用，讓查詢結果前填充0。
留言列表