目錄
- 開發規范
- ※謹慎使用 MySQL 磁區表
- ※經常一起使用的列放到一個表中
- ※禁止在資料庫中存盤檔案（比如圖片）這類大的二進制資料
- 所有表必須使用 InnoDB 存盤引擎
- 資料庫和表的字符集統一使用 UTF8
- 盡量控制單表資料量的大小，建議控制在 500 萬以內
- ※優先選擇符合存盤需要的最小的資料型別
- ※盡可能把所有列定義為 NOT NULL
- 使用 TIMESTAMP(4 個位元組) 或 DATETIME 型別 (8 個位元組) 存盤時間
- 索引規范
- 建議單張表索引不超過 5 個
- 禁止給表中的每一列都建立單獨的索引
- 每個 InnoDB 表必須有個主鍵
- 常見索引列建議
- 創建聯合索引如何選擇索引列的順序
- 對于頻繁的查詢優先考慮使用覆寫索引
- ※索引 SET 規范
- SQL陳述句優化
- 如何找出需要優化的 SQL 陳述句
- 禁止使用 SELECT * 必須使用 SELECT <欄位串列> 查詢
- 充分利用表上已經存在的索引
- 盡量避免使用子查詢
- 用IN來替換OR
- 讀取適當的記錄LIMIT M,N
- 若兩個結果集沒有重復使用UNION ALL
- 盡可能批量Insert插入
- WHERE 從句中禁止對列進行函式轉換和計算
- 優化Group By陳述句
- 優化Join陳述句
1、大多數情況下很正常，偶爾很慢，則有如下原因
(1)、資料庫在重繪臟頁，例如 redo log 寫滿了需要同步到磁盤，
(2)、執行的時候，遇到鎖，如表鎖、行鎖，
(3)、sql寫的爛
2、這條 SQL 陳述句一直執行的很慢，則有如下原因
(1)、沒有用上索引或索引失效：例如該欄位沒有索引；或則由于對欄位進行運算、函式操作導致無法用索引，
(2)、有索引但走了全表掃描
怎樣判斷是否走全表掃描：
索引區分度（索引的值不同越多，區分度越高），稱為基數，而資料量大時不可能全部掃描一遍得到基數，而是采樣部分資料進行預測，那有可能預測錯了，導致走全表掃描，
優化角度：索引+sql陳述句+資料庫結構優化+優化器優化+架構優化
對開發者來說，調優重點在開發規范、索引和線上慢查詢，
開發規范
※謹慎使用 MySQL 磁區表
磁區表在物理上表現為多個檔案，在邏輯上表現為一個表；
謹慎選擇磁區鍵，跨磁區查詢效率可能更低；
建議采用物理分表的方式管理大資料，
※經常一起使用的列放到一個表中
避免更多的關聯操作，經常聯合查詢的表，可以考慮建立中間表，
※禁止在資料庫中存盤檔案（比如圖片）這類大的二進制資料
在資料庫中存盤檔案會嚴重影響資料庫性能，消耗過多存盤空間，
檔案（比如圖片）這類大的二進制資料通常存盤于檔案服務器，資料庫只存盤檔案地址資訊，
所有表必須使用 InnoDB 存盤引擎
InnoDB 支持事務，支持行級鎖，更好的恢復性，高并發下性能更好，
資料庫和表的字符集統一使用 UTF8
兼容性更好，統一字符集可以避免由于字符集轉換產生的亂碼，不同的字符集進行比較前需要進行轉換會造成索引失效，如果資料庫中有存盤 emoji 表情的需要，字符集需要采用 utf8mb4 字符集，
盡量控制單表資料量的大小，建議控制在 500 萬以內
可以用歷史資料歸檔（應用于日志資料），分庫分表（應用于業務資料）等手段來控制資料量大小
※優先選擇符合存盤需要的最小的資料型別
存盤位元組越小，占用也就空間越小，性能也越好，
- 某些字串可以轉換成數字型別存盤比如可以將 IP 地址轉換成整型資料，
數字是連續的，性能更好，占用空間也更小，
MySQL 提供了兩個方法來處理 ip 地址
INET_ATON()： 把 ip 轉為無符號整型 (4-8 位)INET_NTOA():把整型的 ip 轉為地址
插入資料前，先用 INET_ATON() 把 ip 地址轉為整型，顯示資料時，使用 INET_NTOA() 把整型的 ip 地址轉為地址顯示即可，
- 對于非負型的資料 (如自增 ID,整型 IP，年齡) 來說,要優先使用無符號整型來存盤，
因為無符號相對于有符號可以多出一倍的存盤空間
- 小數值型別（比如年齡、狀態表示如 0/1）優先使用 TINYINT 型別，
※盡可能把所有列定義為 NOT NULL
除非有特別的原因使用 NULL 值，應該總是讓欄位保持 NOT NULL，
- 索引 NULL 列需要額外的空間來保存，所以要占用更多的空間；
- 進行比較和計算時要對 NULL 值做特別的處理，
使用 TIMESTAMP(4 個位元組) 或 DATETIME 型別 (8 個位元組) 存盤時間
TIMESTAMP 存盤的時間范圍 1970-01-01 00:00:01 ~ 2038-01-19-03:14:07
TIMESTAMP 占用 4 位元組和 INT 相同，但比 INT 可讀性高
超出 TIMESTAMP 取值范圍的使用 DATETIME 型別存盤
經常會有人用字串存盤日期型的資料（不正確的做法）
- 缺點 1：無法用日期函式進行計算和比較
- 缺點 2：用字串存盤日期要占用更多的空間
索引規范
建議單張表索引不超過 5 個
因為 MySQL 優化器在選擇如何優化查詢時，會根據統一資訊，對每一個可以用到的索引來進行評估，以生成出一個最好的執行計劃，如果同時有很多個索引都可以用于查詢，就會增加 MySQL 優化器生成執行計劃的時間，同樣會降低查詢性能，
禁止給表中的每一列都建立單獨的索引
不如使用一個聯合索引
每個 InnoDB 表必須有個主鍵
InnoDB 是一種索引組織表：資料的存盤的邏輯順序和索引的順序是相同的，每個表都可以有多個索引，但是表的存盤順序只能有一種，
InnoDB 是按照主鍵索引的順序來組織表的
- 不要使用更新頻繁的列作為主鍵，不適用多列主鍵（相當于聯合索引）
- 不要使用 UUID,MD5,HASH,字串列作為主鍵（無法保證資料的順序增長）
- 主鍵建議使用自增 ID 值
常見索引列建議
- 出現在 SELECT、UPDATE、DELETE 陳述句的 WHERE 從句中的列
- 包含在 ORDER BY、GROUP BY、DISTINCT 中的欄位
- 不用將符合 1 和 2 中的欄位的列都建立一個索引， 通常將 1、2 中的欄位建立聯合索引效果更好
- 多表 join 的關聯列
創建聯合索引如何選擇索引列的順序
建立索引的目的是：希望通過索引進行資料查找，減少隨機 IO，增加查詢性能 ，索引能過濾出越少的資料，則從磁盤中讀入的資料也就越少，
- 區分度最高的放在聯合索引的最左側（區分度=列中不同值的數量/列的總行數）
- 盡量把欄位長度小的列放在聯合索引的最左側（因為欄位長度越小，一頁能存盤的資料量越大，IO 性能也就越好）
- 使用最頻繁的列放到聯合索引的左側（這樣可以比較少的建立一些索引）
對于頻繁的查詢優先考慮使用覆寫索引
覆寫索引：就是包含了所有查詢欄位 (where, select, order by, group by 包含的欄位) 的索引
※索引 SET 規范
盡量避免使用外鍵約束
- 不建議使用外鍵約束（foreign key），但一定要在表與表之間的關聯鍵上建立索引
- 外鍵可用于保證資料的參照完整性，但建議在業務端實作
- 外鍵會影響父表和子表的寫操作從而降低性能
SQL陳述句優化
如何找出需要優化的 SQL 陳述句
-
通過查詢 MySQL 的慢查詢日志來發現需要進行優化的 SQL 陳述句；
-
explain查看SQL的執行計劃，這樣就知道是否命中索引了，當
explain與SQL一起使用時，MySQL將顯示來自優化器的有關陳述句執行計劃的資訊，
重點關注
type、rows、filtered、extra、key-
type
type表示連接型別，查看索引執行情況的一個重要指標，以下性能從好到壞依次：
system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL- system：這種型別要求資料庫表中只有一條資料，是
const型別的一個特例，一般情況下是不會出現的， - const：通過一次索引就能找到資料，一般用于主鍵或唯一索引作為條件，這類掃描效率極高，，速度非常快，
- eq_ref：常用于主鍵或唯一索引掃描，一般指使用主鍵的關聯查詢
- ref : 常用于非主鍵和唯一索引掃描，
- ref_or_null：這種連接型別類似于
ref，區別在于MySQL會額外搜索包含NULL值的行 - index_merge：使用了索引合并優化方法，查詢使用了兩個以上的索引，
- unique_subquery：類似于
eq_ref，條件用了in子查詢 - index_subquery：區別于
unique_subquery，用于非唯一索引，可以回傳重復值， - range：常用于范圍查詢，比如：between ... and 或 In 等操作
- index：全索引掃描
- ALL：全表掃描
- system：這種型別要求資料庫表中只有一條資料，是
-
rows
該串列示MySQL估算要找到我們所需的記錄，需要讀取的行數，對于InnoDB表，此數字是估計值，并非一定是個準確值，
-
filtered
該列是一個百分比的值，表里符合條件的記錄數的百分比，簡單點說，這個欄位表示存盤引擎回傳的資料在經過過濾后，剩下滿足條件的記錄數量的比例，
-
extra
該欄位包含有關MySQL如何決議查詢的其他資訊，它一般會出現這幾個值：
- Using filesort：表示按檔案排序，一般是在指定的排序和索引排序不一致的情況才會出現，一般見于order by陳述句
- Using index ：表示是否用了覆寫索引，
- Using temporary: 表示是否使用了臨時表,性能特別差，需要重點優化，一般多見于group by陳述句，或者union陳述句，
- Using where : 表示使用了where條件過濾.
- Using index condition：MySQL5.6之后新增的索引下推，在存盤引擎層進行資料過濾，而不是在服務層過濾，利用索引現有的資料減少回表的資料，
-
key
該串列示實際用到的索引，一般配合
possible_keys列一起看，
-
-
show profile 分析
了解SQL執行的執行緒的狀態及消耗的時間，
默認是關閉的，開啟陳述句“set profiling = 1;”
SHOW PROFILES ; SHOW PROFILE FOR QUERY #{id}; -
trace
trace分析優化器如何選擇執行計劃，通過trace檔案能夠進一步了解為什么選擇A執行計劃而不選擇B執行計劃，
禁止使用 SELECT * 必須使用 SELECT <欄位串列> 查詢
SELECT *消耗更多的 CPU 和 IO 以網路帶寬資源SELECT *無法使用覆寫索引SELECT <欄位串列>可減少表結構變更帶來的影響
充分利用表上已經存在的索引
一個 SQL 只能利用到復合索引中的一列進行范圍查詢，如：有 a,b,c 列的聯合索引，在查詢條件中有 a 列的范圍查詢，則在 b,c 列上的索引將不會被用到，
在定義聯合索引時，如果 a 列要用到范圍查找的話，就要把 a 列放到聯合索引的右側，使用 left join 或 not exists 來優化 not in 操作，因為 not in 也通常會使用索引失效，
盡量避免使用子查詢
用join連接
用IN來替換OR
# 優化前
SELECT * FROM t WHERE id = 10 OR id = 30;
# 優化后
SELECT * FROM t WHERE id IN (10, 30);
對于連續的數值，能用between就不要用in了；再或者使用join連接來替換，
讀取適當的記錄LIMIT M,N
# 優化前
SELECT id.name FROM t LIMIT 866613, 20
對于limit m,n 的分頁查詢，越往后面翻頁（即m越大的情況下）SQL的耗時會越來越長，對于這種應該先取出主鍵id，然后通過主鍵id跟原表進行Join關聯查詢，因為MySQL并不是跳過offset行，而是取offset+N行，然后放棄前offset行，回傳N行，那當offset特別大的時候，效率就非常的低下，要么控制回傳的總頁數，要么對超過特定閾值的頁數進行SQL改寫，
優化的方法如下：可以取前一頁的最大行數的id（將上一次遍歷到的最末尾的資料ID傳給資料庫，然后直接定位到該ID處，再往后面遍歷資料），然后根據這個最大的id來限制下一頁的起點，比如此列中，上一頁最大的id是866612，sql可以采用如下的寫法：
# 優化后
SELECT id.name FROM table_name WHERE id> 866612 LIMIT 20
若兩個結果集沒有重復使用UNION ALL
union 和 union all 的差異主要是前者需要將結果集合并后再進行唯一性過濾操作，這就會涉及到排序，增加大量的CPU運算，加大資源消耗及延遲，
當然，union all 的前提條件是兩個結果集沒有重復資料，所以一般是我們明確知道不會出現重復資料的時候才建議使用 union all 提高速度，
盡可能批量Insert插入
INSERT INTO t(id, name) VALUES(1, 'aaa');
INSERT INTO t(id, name) VALUES(2, 'bbb');
INSERT INTO t(id, name) VALUES(3, 'ccc');
-->
INSERT INTO t(id, name) VALUES(1, 'aaa'),(2, 'bbb'),(3, 'ccc');
WHERE 從句中禁止對列進行函式轉換和計算
對列進行函式轉換或計算時會導致無法使用索引不推薦：
where date(create_time)='20190101'
推薦：
where create_time >= '20190101' and create_time < '20190102'
優化Group By陳述句
如果對group by 陳述句的結果沒有排序要求，要在陳述句后面加上order by null (group默認會排序)；
盡量讓group by 程序用上表的索引，確認方法是explain結果里沒有Using temporary和Using filesort;
如果group by 需要統計的資料量不大，盡量只使用記憶體臨時表；也可以通過適當調大tmp_table_size引數，來避免用到磁盤臨時表；
如果資料量實在太大，使用SQL_BIG_RESULT這個提示，來告訴優化器直接使用排序演算法（直接用磁盤臨時表）得到group by 的結果，
使用where字句替換having子句：避免使用having子句，having只會在檢索出所有記錄之后才會對結果集進行過濾，這個處理需要排序分組，如果能通過where子句提前過濾查詢的數目，就可以減少這方面的開銷，
# 優化前
SELECT JOB, AVG(SAL) FROM EMP GROUP BY JOB HAVING JOB = 'PRESIDENT' OR JOB = 'MANAGER'
# 優化后
SELECT JOB, AVG(SAL) FROM EMP WHERE JOB = 'PRESIDENT' OR JOB = 'MANAGER' GROUP BY JOB
優化Join陳述句
當我們執行兩個表的Join的時候，就會有一個比較的程序，逐條比較兩個表的陳述句是比較慢的，因此可以把兩個表中資料一次讀進一個記憶體塊中，在MySQL中執行：
show variables like 'join_buffer_size'
可以看到Join在記憶體中的快取池大小，其大小將會影響Join陳述句的性能，在執行Join的時候，資料庫會選擇一個表把它要回傳以及需要進行和其他表進行比較的資料放進join_buffer，
- 當連接查詢沒有where條件時
- left join前面的表是驅動表，后面的表是被驅動表
- right join后面的表是驅動表，前面的表是被驅動表
- inner join / join會自動選擇表資料比較少的作為驅動表
- traight_join(≈join)直接選擇左邊的表作為驅動表（語意上與join類似，但去除了join自動選擇小表作為驅動表的特性）
- 當連接查詢有where條件時，帶where條件的表是驅動表，否則是被驅動表
假如有表如右邊：t1與t2表完全一樣，a欄位有索引，b無索引，t1有100條資料，t2有1000條資料
- 對被驅動表的join欄位上建立索引;
- 當被驅動表的join欄位上無法建立索引時，設定足夠的 Join Buffer Size;
- 盡量用 inner join(因為其會自動選擇小表去驅動大表)，避免 left join（一般我們使用left join 的場景是大表驅動小表）和 NULL
- left join是由左邊決定的，左邊一定都有，所以右邊是我們的關鍵點，建立索引要建在右邊，當然如果索引是在左邊的，我們可以考慮使用右連接
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/543827.html
標籤：其他