性能優化之資料庫優化

1、何為性能優化
1.1.用戶請求到響應,網路+服務+資料庫+前端頁面渲染,缺一不可
1.2.二八原則,80%的性能問題出現在20%的程式碼,找到關鍵點進行優化,0.01秒的查詢再優化也提升不了體驗
 
2、資料庫優化
 
2.1.索引
2.1.1.分類
功能性:唯一索引,其他為輔助查詢
      唯一索引Unique,比如role_info的role_id
      普通索引Normal
2.1.2.三星索引
一星:創建索引把數據都放在一起,
反例:單列索引,會索引合併,效率不高
最好用聯合索引
二星:排序走索引(group by本質也是排序)
        把排序欄位一起放到聯合索引中
三星:只查索引不回表
        查詢的那幾個欄位正好都是有索引的
2.1.3.多列索引
a) 多列索引效率要比單列索引高
         聯合索引
b) 列順序非常重要,index(del_flag, form_status, customer_org_id, supplier_org_id)
     注意:最左原則
c) 將經常查詢的、枚舉的列放在最前,比如del_flag,當查詢全部時可以用del_flag in (1,0)來走索引
2.1.4.強制索引
當mysql沒有按照預想的索引解析,且效率較慢,可以使用force index(idx_test)來強制指定索引
from role_info force index(idx_test)
一般來說possible keys其中資料庫會擇優選擇一個好的索引key
2.1.5.其他
a) Null值不會被包含在索引中
b)唯一索引對null值不生效,例如:一個多列唯一索引unindex(A, B),當A為null時,B值相同仍能入庫;
索引的那個欄位不要為NULL,故欄位最好設個默認值0或」」,不要為NULL
 
2.2.查詢
a) InnoDB中的or語句(or查詢可以考慮用union all替換)、
where age + 1 = 12、where fun(age) = 12、<>、not in、!=不走索引,
查詢類型不一致也會導致不走索引(where a.meun_id = b.menu_id a表的是int而b表的是varchar)
注意:索引走的值是欄位age,如果對其修改了,比如計算+x,函數(age),這時都不會走索引
比如:select * from role_info where age = 18 or age = 28改成
select * from role_info where age = 18
union all
select * from role_info where age = 28
b) 查詢要根據實際業務場景,預估每個表內的大致數據量
c) 複雜查詢拆分為簡單查詢
d) 當發現索引和查詢已經無法繼續優化時,讓java做或者換種方式實現
注意:最好是單表查詢,每個表可以添加冗餘欄位(org_code和org_name),避免多表查詢(除了主表和明細表)
 
2.3.合格標準
a) 每個請求必須在0.5秒內執行完成
b) MySQL獲取到符合要求的數據就會停止查詢,所以當查詢語句有limit時,要測試最後一頁的查詢速度
     盡量不要select *
     分頁查詢,第一頁響應時間100ms,但最後一頁需400ms
 
2.4執行計劃
sql語句前,寫explain
explain
select *
from role_info  可以查看執行計劃
 
id:多個查詢,數字越大越先執行
rows:掃描的行數,數量盡量減少
possible_keys:查詢可能使用到的索引都會在這裡列出來
key:查詢用到的索引
ref:表示走了幾個索引欄位(const, const)
type:ref、range、index_merge、index、all
  最好可以到ref,不要all,除了all,其他都用到了索引 
 
2.5.補充
(1)索引就像書本的目錄,目錄可以快速找到所在頁數,資料庫中索引可以幫助快速找到數據,而不用全表掃描
(2)資料庫的優化:sql語句優化,索引優化等(一般只掌握這兩個基礎優化)
(3)sql語句的優化:
1.對查詢進行優化,應盡量避免全表掃描,首先應考慮在 where 及 order by 涉及的列上建立索引。
最好有where條件限制,where和order by的欄位做一個聯合索引。
2. MySQL只有對以下操作符才使用索引:<,<=,=,>,>=,BETWEEN,IN,以及某些時候的LIKE(abc%)。
3.in 和 not in 也要慎用,否則會導致全表掃描,對於連續的數值,能用 between 就不要用 in 了:Select id from t where num between 1 and 3
4. 索引固然可以提高相應的 select 的效率,但同時也降低了 insert 及 update 的效率,因為 insert 或 update 時有可能會重建索引。一個表的索引數最好不要超過6個
5. 將需要查詢的結果預先計算好放在表中,,查詢的時候再Select
表中添加需要的冗餘欄位
6. 盡量使用exists代替select count(1)來判斷是否存在記錄,count函數只有在統計表中所有行數時使用,而且count(1)比count(*)更有效率
7. 當有一批處理的插入或更新時,用批量插入或批量更新,絕不會一條條記錄的去更新!
不要在for循環中,一條條去插入或更新,for循環前新建List<SysRoleInfo> insertList
8. 在適當的情形下使用GROUP BY而不是DISTINCT
9. 索引創建規則:
表的主鍵、外鍵必須有索引(業務主鍵uuid建唯一索引);
經常與其他表進行連接的表,在連接欄位上應該建立索引;
經常出現在Where子句中的欄位,應該建立索引;
索引應該建在小欄位上,對於大的文本欄位甚至超長欄位,不要建索引;
如果複合索引中包含的欄位經常單獨出現在Where子句中,則分解為多個單欄位索引;
如果複合索引所包含的欄位超過3個,那麼仔細考慮其必要性,考慮減少複合的欄位;
頻繁進行數據操作的表,不要建立太多的索引;
刪除無用的索引,避免對執行計劃造成負面影響;
盡量不要對資料庫中某個含有大量重複的值的欄位建立索引。
 
2.6.實例
(1)
SELECT
count(*) AS value,
date_format(add_time, ‘%Y-%m-%d’) AS name
FROM
order_purchase_info
WHERE
    del_flag = 0
AND form_type = 1
AND customer_org_id = ‘9395C427D83D4986AB0932A33D1C75BB’
AND date_format(add_time, ‘%Y-%m-%d’) BETWEEN “2019-03-18” AND “2019-03-25”
GROUP BY
date_format(add_time, ‘%Y-%m-%d’)
 
1.創建一個索引idx_dfca(del_flag, form_type, customer_org_id, add_time)
查詢列與索引列次序可以不一致,最好一致
2.AND date_format(add_time, ‘%Y-%m-%d’) BETWEEN “2019-03-18” AND “2019-03-25”
改成AND add_time BETWEEN “2019-03-18” AND “2019-03-25 23:59:59”
原先的條件里欄位add_time被修改了,故不會走索引
3.排序的add_time被改動了,故排序這的add_time不會走索引
4.最左原則(索引次序):
比如有1000條數據,del_flag=0有700條,則接下去就會在這700條數據中,
繼續查詢form_type=1的,如果有400條,則繼續在這400條中查詢
執行計劃中,ref有const,const,const,說明就走了三個索引,dfc
如果索引idx_dfac(del_flag, form_type, add_time, customer_org_id)
因為add_time這個索引沒有走,即條件add_time = 無,
所以就不會走下一個所以c,故ref就兩個const
àà索引次序從左往右走,有一個索引,該索引不在條件中,則停止走了
5.查詢指定日期的,用mysql函數:
今天TO_DAYS(add_time) = TO_DAYS(NOW())
昨天DATEDIFF(NOW(), add_time) = 1,上周同期DATEDIFF(NOW(), add_time) = 7
都改成add_time BETWEEN “2019-03-21” AND “2019-03-21 23:59:59″,才會走索引
 
(2)
SELECT
rm.menu_id
FROM
role_menu_relation_info rm
WHERE
rm.del_flag = 0
AND EXISTS (
SELECT role_id
FROM user_role_relation_info ri
WHERE  rm.role_id = ri.role_id
AND ri.del_flag = 0
AND ri.user_id = ‘E45EA2B1599D5A5CE040007F010020E1’
AND ri.org_id = ‘9395C427D83D4986AB0932A33D1C75BB’
)
改成
SELECT
rm.menu_id
FROM
role_menu_relation_info rm
INNER JOIN user_role_relation_info ri
ON rm.role_id = ri.role_id
AND ri.del_flag = 0
AND ri.user_id = ‘E45EA2B1599D5A5CE040007F010020E1’
AND ri.org_id = ‘9395C427D83D4986AB0932A33D1C75BB’
WHERE
rm.del_flag = 0
 
1. 用EXISTS子查詢,外表rm:all,並沒有走索引,內表ri走索引了
select * from a where a_name in (select b_name from b)
select * from a where exists (select b_id from b where b.b_name=a.a_name)
exists子句返回的結果並不是從資料庫中取出的結果集,而是一個布爾值,如果子句查詢到數據,那麼返回true,反之返回false。
所以子句中選擇的列根本就不重要,而重要的是where 後的條件。如果返回了true,那麼相當於直接執行了子句 where 後的部分,即把a_name 和 b_name 作比較,如果相等則返回這條數據
2. 用多表連接代替EXISTS子句
INNER JOIN   ON 兩個表都走索引了
3.盡量用EXISTS代替IN
   一般INNER JOIN > EXISTS >IN,可以比較所用時間和查詢執行計劃,用效率高的
 
(3)
SELECT
count(*) AS countNumber,
1 AS type
FROM
order_deliver_info
WHERE
del_flag =0
AND customer_org_id = ‘9395C427D83D4986AB0932A33D1C75BB’
AND add_time BETWEEN “2019-03-21” AND “2019-03-21 23:59:59”
UNION ALL
SELECT
count(*) AS countNumber,
2 AS type
FROM
order_deliver_info
WHERE
del_flag =0
AND customer_org_id = ‘9395C427D83D4986AB0932A33D1C75BB’
AND add_time BETWEEN “2019-03-20” AND “2019-03-20 23:59:59”
UNION ALL
SELECT
count(*) AS countNumber,
3 AS type
FROM
order_deliver_info
WHERE
del_flag =0
AND customer_org_id = ‘9395C427D83D4986AB0932A33D1C75BB’
AND DATEDIFF(NOW(), add_time) = 7
 
union all的使用,結果type 1 2 3 對應countNumber 10 20 30
 
 

Tags: