記錄一次SQL函式和優化的問題

余月七發表於2022-03-22
SQL函式優化

一、前言

上次在年前快要放假的時候記錄的一篇安裝SSL證書的內容，因為當時公司開始居家辦公了，我也打算回個家
畢竟自己在蘇州這半年一個人也是很想家的，所以就打算年過完來重新寫部落格。不巧的是，當時我2月中旬剛到蘇州，
沒想到蘇州疫情爆發了，直接隔離十四天，自己平時就完成公司的開發任務以及自己的畢設，把寫部落格的事有點淡忘了，
最近也算自己手頭的一些事都開始可以順利進行了，想起也是時候去記錄一下了。
很多新學的東西自己感覺就是入了個門，所以平時基本就直接放語雀上了，這次也是打算做一個簡短的總結吧！

二、SQL函式

注：以下基於官方文件理解（ MySQL5.7文件 ）

官方的內容，可以利用好CTRL+F，進行網頁內搜尋，隨時檢視各種函式用法，而且也會避免一些不規範的問題，比較官方的例子和要求最緻密。

內建函式和運算子參考

2.1字串函式

FIND_IN_SET(param1，param2)

這是一個字串相關的函式

FIND_IN_SET(needle,haystack) 裡面有兩個引數，我們可以簡單的看作find_in_set(param1,param2)

第一個引數param1：它是我們要查詢的某一個具體的值

第二個引數param2：它是我們要查詢的字串列表

  • 當param2在param2這個列表中的話，函式返回一個正整數
  • 當param1不在param2中，或者param2這個列表是個NULL，函式返回0
  • 當兩個引數param1或param2為NUll的時候，函式放回NULL

這裡以LEFT JOIN舉個“慄”子：

LEFT JOIN：說的簡單點就是，左表記錄會全部返回，同時如果與右表有記錄相等的資料會返回右表的一些相關資訊，如果沒有，右表返回的記錄就是NULL
（這個可以去參考CSDN此篇部落格：Sql之left join（左關聯）、right join（右關聯）、inner join（自關聯）的區別

這裡假設有a和b兩張表，a表中的id是個bigint型別，b表中的relate_a_id是個varchar型別，存放的是關聯的a表中的id
（這裡僅僅是舉個例子，表的設計一定要符合規範，比如這種關聯的可以新增一張關聯表的操作）
select a.id,
       a.name,
       IFNULL(b.id,0) AS flag,
       b.relate_a_id
       from a left join b on FIND_IN_SET(a.id,relate_a_id) and a.id = b.id
       
這樣如果a.id在這個b表中relate_a_id這個欄位的列表中的話就返回b.id，如果不在就放回0。這裡起了個別名為flag作為判斷量

單表的操作更簡單，總而言之，這個函式就是為了判斷一個值是否在一個字串列表中的操作。

同理和NULL、NOT NULL一樣，如果要判斷不在當中就直接NOT FIND_IN_SET()就可以了

這裡要提一點的就是，以上操作看起來和IN這個操作符很像，所以這裡我的理解是：

雖然
1 IN(1,2,3)  和  FIND_IN_SET(1,"1,2,3")  最終的結果是一樣的，但是如下：
IN它是“值”對“值”，而FIND_IN_SET(param1，param2)是“值”對“一個列表”，而且FIND_IN_SET這個函式有自己的固定的兩個引數
+ 不同點一：比較內容不同
+ 不同點二：函式格式不同

以上也是自己的一些淺見，如有錯誤，請各位大佬虛心賜教！

其他

字串函式其實還有很多比較常見的，比如：

CONCAT(param1,param2,……)

這個函式裡面也是有引數的，就是把兩個或多個引數組合到一起的函式，當然還有CONCAT_WS(seperator,param1,param2,……)
根據第一個引數“分隔符”，來組合引數列表。

對於這個函式比較熟悉的就是寫動態SQL的時候與LIKE操作符的應用，比如：
select a.name from a where a.is_delete =0
<if test="param.serachName!=''">
    and a.name like concat('%',#{param.serachName},'%')
</if>

這也是為了單純寫like去傳引數的話，會出現SQL隱碼攻擊的風險，所以採用這種方式來防止SQL隱碼攻擊

REPLACE(str,oldStr,newStr)

這裡要注意的是mysql擴充套件中REPLACE是個插入更新語句，但它沒有where字句，具體可以自行搜尋檢視

舉個例子：
REPLACE('aaa.yuyueq.cn'，'a'，'w')
結果為：www.yuyueq.cn

SUBSTRING(str,index)

它會從一個特定長度的位置開始，提取一個子字串。

也可以寫為SUBSTRING(str FROM index),舉個例子：
SUBSTRING('www.yuyueq.cn',5)
結果為：'yuyueq.cn'
要注意的是它不在遵循計算機的規律，也就是它是從1開始數的，並不是0，如果index引數是0.則返回一個空字串

當然也可以擷取字串中字串，比如

(substring(str,index,length)和下面這個是一樣的)
SUBSTRING(str FROM index FROM length),舉個例子：
SUBSTRING('www.yuyueq.cn',5,6)
結果為：'yuyueq'

TRIM([{BOTH|LEADING|TRAILING} [removed_str]] FROM str)

是從字串中刪除不需要的字元

TRIM ( [ [位置] [要移除的字串] FROM ] 字串): [位置] 的可能值為 LEADING (起頭), TRAILING (結尾), or BOTH (起頭及結尾)。
這個函式將把 [要移除的字串] 從字串的起頭、結尾，或是起頭及結尾移除。如果我們沒有列出 [要移除的字串] 是什麼的話，那空白就會被移除

trim操作個人感覺多用於動態SQL中吧，可以看看簡書的這篇：mybatis動態SQL - trim where set標籤

FORMAT(N,D,locale)

格式化具有特定區域設定的數字，舍入到小數位數。

N是要格式化的數字。

D是要舍入的小數位數。

locale是一個可選引數，用於確定千個分隔符和分隔符之間的分組。如果省略locale操作符，MySQL將預設使用en_US。以下連結提供MySQL支援的所有區域名稱：

LEFT()

獲取指定長度的字串的左邊部分。

LENGTH()函式&CHAR_LENGTH()

它是以位元組和字元獲取字串的長度。

2.2 聚合函式

COUNT()

首先官方已經說了,count(*)和count(1)沒有區別

InnoDB handles SELECT COUNT(*) and SELECT COUNT(1) operations in the same way. There is no performance difference.

其次，count(欄位)和count(*)、count(1)的區別是：

  • count(欄位)會進行全表掃描，效率會很差，不計算NULL值
  • count()、count(1)會計算NULL值，而且count()等同於count(0)
  • count如果沒有匹配的行，count()它直接返回0

四個計算

AVG()：取平均值

SUM()：求和

這裡要注意的是，如果沒有匹配的行，則 SUM()返回 NULL。

min()：最小值

max()：最大值

GROUP_CONCAT()

此函式返回一個字串結果

舉個例子:
假設a是使用者表，b是一張使用者興趣(id)關聯表，c是一張興趣表；
下面的意思就是我們查詢這個使用者相關資訊的時候，將相關聯的興趣放到一個字串欄位中，相當於顯示使用者詳情的操作
   SELECT
        group_concat(DISTINCT c.name) AS interestName
        FROM
        a
        left join b on a.id = b.user_id
        left join c on b.interest_id = c.id

官方例子：GROUP_CONCAT( [DISTINCT] expr [,expr ...] [ORDER BY {unsigned_integer | col_name | expr}[ASC | DESC] [,col_name ...] ] [SEPARATOR str_val])

由官方例子可以看出裡面可以進行去重、排序、用特定的分隔符展示（預設是“,”）

其次還要注意的是，它是不可以和IN操作符使用的，原因就和find_in_set那個函式一樣，IN的列表的是值列表，group_concat是個字串列表

2.3 控制流函式

控制流 是計算機執行一個程式中語句的順序。 程式會從第一行程式碼開始執行直至最後一行，除非遇到（實際中是非常普遍地）改變控制流的程式碼結構，比如條件語句和迴圈。

注：函式和sql語句的用法是不一樣，所以要多注意一點，此處都是函式的用法。

IF(expr1,expr2,expr3)

官方例子最致命
如果expr1是TRUE (expr1 不等於0 和 expr1 IS NOT NULL)，則返回expr2，否則，返回expr3.
mysql> SELECT IF(1>2,2,3);
        -> 3
mysql> SELECT IF(1<2,'yes','no');
        -> 'yes'
mysql> SELECT IF(STRCMP('test','test1'),'no','yes');
        -> 'no'

strcmp函式可以看這裡：https://dev.mysql.com/doc/refman/5.7/en/string-comparison-functions.html

IFNULL(expr1,expr2)

官方例子最致命
如果expr1不是 NULL， 則返回 expr1；否則返回 expr2。
mysql> SELECT IFNULL(1,0);
-> 1
mysql> SELECT IFNULL(NULL,10);
-> 10
mysql> SELECT IFNULL(1/0,10);
-> 10
mysql> SELECT IFNULL(1/0,'yes');
-> 'yes'

CASE

官方例子最致命
mysql> SELECT CASE 1 WHEN 1 THEN 'one'
->     WHEN 2 THEN 'two' ELSE 'more' END;
-> 'one'
mysql> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;
-> 'true'
mysql> SELECT CASE BINARY 'B'
->     WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;
-> NULL

NULLIF(expr1,expr2)

Returns NULL if expr1 = expr2 is true, otherwise returns expr1. This is the same as CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.
如果第一個引數等於第二個引數，則返回NULL，否則返回第一個引數

官方例子最致命
mysql> SELECT NULLIF(1,1);
-> NULL
mysql> SELECT NULLIF(1,2);
-> 1

2.4 日期函式

函式名 描述
ADDDATE() 將時間值（間隔）新增到日期值
ADDTIME() 新增時間
CONVERT_TZ() 從一個時區轉換到另一個時區
CURDATE() 返回當前日期
CURRENT_DATE(),CURRENT_DATE CURDATE() 的同義詞
CURRENT_TIME(),CURRENT_TIME CURTIME() 的同義詞
CURRENT_TIMESTAMP(),CURRENT_TIMESTAMP NOW() 的同義詞
CURTIME() 返回當前時間
DATE() 提取日期或日期時間表示式的日期部分
DATE_ADD() 將時間值（間隔）新增到日期值
DATE_FORMAT() 按指定格式日期
DATE_SUB() 從日期中減去時間值（間隔）
DATEDIFF() 減去兩個日期
DAY() DAYOFMONTH() 的同義詞
DAYNAME() 返回工作日的名稱
DAYOFMONTH() 返回月份中的第幾天 (0-31)
DAYOFWEEK() 返回引數的工作日索引
DAYOFYEAR() 返回一年中的某一天 (1-366)
EXTRACT() 提取日期的一部分
FROM_DAYS() 將天數轉換為日期
FROM_UNIXTIME() 將 Unix 時間戳格式化為日期
GET_FORMAT() 返回日期格式字串
HOUR() 提取小時
LAST_DAY 返回引數的月份的最後一天
LOCALTIME(),LOCALTIME 現在（）的同義詞
LOCALTIMESTAMP,LOCALTIMESTAMP() 現在（）的同義詞
MAKEDATE() 根據年份和日期建立日期
MAKETIME() 從小時、分鐘、秒建立時間
MICROSECOND() 從引數返回微秒
MINUTE() 從引數返回分鐘
MONTH() 從過去的日期返回月份
MONTHNAME() 返回月份的名稱
NOW() 返回當前日期和時間
PERIOD_ADD() 為年月新增期間
PERIOD_DIFF() 返回期間之間的月數
QUARTER() 從日期引數返回季度
SEC_TO_TIME() 將秒轉換為 'hh:mm:ss' 格式
SECOND() 返回第二個 (0-59)
STR_TO_DATE() 將字串轉換為日期
SUBDATE() 使用三個引數呼叫時 DATE_SUB() 的同義詞
SUBTIME() 減去時間
SYSDATE() 返回函式執行的時間
TIME() 提取傳遞的表示式的時間部分
TIME_FORMAT() 格式為時間
TIME_TO_SEC() 返回轉換為秒的引數
TIMEDIFF() 減去時間
TIMESTAMP() 使用單個引數，此函式返回日期或日期時間表示式；有兩個引數，引數的總和
TIMESTAMPADD() 向日期時間表示式新增間隔
TIMESTAMPDIFF() 從日期時間表示式中減去間隔
TO_DAYS() 返回轉換為天的日期引數
TO_SECONDS() 返回自第 0 年以來轉換為秒的日期或日期時間引數
UNIX_TIMESTAMP() 返回一個 Unix 時間戳
UTC_DATE() 返回當前 UTC 日期
UTC_TIME() 返回當前 UTC 時間
UTC_TIMESTAMP() 返回當前 UTC 日期和時間
WEEK() 返回週數
WEEKDAY() 返回工作日索引
WEEKOFYEAR() 返回日期的日曆周 (1-53)
YEAR() 返回年份
YEARWEEK() 返回年份和星期

三、SQL優化

上面的內容其實也設計到了很多規範的問題，但畢竟我是為了舉例子所以在這提一些規範的操作。

資料庫設計

  • 冷熱資料的分離，從而可以減少表的寬度
  • 列的欄位型別儘量可小去滿足ta，否則建立索引需要的空間會很大，影響效能
  • 儘量不要使用TEXT,BLOB資料型別
  • 儘可能把所有列定義為 NOT NULL，這也是為了防止查詢的時候NullPointException異常的出現
  • 及時給資料庫表和欄位增添註釋
  • TIMESTAMP(4 個位元組) 或 DATETIME 型別 (8 個位元組) 儲存時間
    • 兩者比時間戳更直觀，但TIMESTAMP會有2038年的問題，
    • TIMESTAMP具有'1970-01-01 00:00:01'UTC 到'2038-01-19 03:14:07'UTC 的範圍
    • 我個人是比較傾向於時間戳的，資料庫中用bigint儲存，程式設計中用Long值傳遞，至於前端展示就在前端做處理，很方便，就是資料庫檢視時間的時候不直觀
    • mysql 資料庫存時間最好是時間戳還是格式的時間

MySQL將TIMESTAMP值從當前時區轉換為UTC進行儲存，並從UTC返回到當前時區進行檢索。
(預設情況下，每個連線的當前時區是伺服器的時間。時區可以在每個連線的基礎上設定。只要時區設定保持不變，你就會得到與你儲存的相同的值。
如果你儲存一個TIMESTAMP值，然後改變時區並檢索該值，檢索到的值與你儲存的值不同。出現這種情況是因為在兩個方向的轉換中沒有使用相同的時區。當前的時區可以作為time_zone系統變數的值。
要注意MySQL中日期值解釋的某些屬性。
MySQL允許對指定為字串的值採用 "寬鬆 "格式，其中任何標點符號都可以用作日期部分或時間部分之間的分隔符。在某些情況下，這種語法可能具有欺騙性。例如，像'10:11:12'這樣的值可能看起來像一個時間值，因為有:，但如果在日期上下文中使用，則被解釋為年份'2010-11-12'。值'10:45:15'被轉換為'0000-00-00'，因為'45'不是一個有效的月份。
在日期和時間部分與小數秒部分之間，唯一可識別的分隔符是小數點。
伺服器要求月和日的值是有效的，而不僅僅是分別在1到12和1到31的範圍內。在禁用嚴格模式的情況下，無效的日期如'2004-04-31'被轉換為'0000-00-00'併產生一個警告。在啟用嚴格模式的情況下，無效的日期產生一個錯誤。要允許這樣的日期，請啟用ALLOW_INVALID_DATES。參見第5.1.10節 "伺服器SQL模式"，以瞭解更多資訊。
MySQL不接受在日或月列中包含零的TIMESTAMP值或不是有效日期的值。這個規則的唯一例外是特殊的 "零 "值"0000-00-00 00:00:00"，如果SQL模式允許這個值。準確的行為取決於是否啟用了嚴格的SQL模式和NO_ZERO_DATE SQL模式；參見章節5.1.10, "伺服器SQL模式"。
包含2位數年值的日期是模糊的，因為世紀是未知的。MySQL使用這些規則解釋2位數的年值。
00-69範圍內的年值成為2000-2069。
在70-99範圍內的年值成為1970-1999。

具體參見官方：https://dev.mysql.com/doc/refman/5.6/en/date-and-time-types.html

SQL語句

  • 儘量使用【select 欄位】，而不要去使用【select *】
  • 儘量將子查詢變為JOIN語句，並且也要減少JOIN語句的使用，如果業務存在特殊要求，可以嘗試使用虛擬表來提高查詢效率
  • where語句中，還是不要對列進行函式轉換和計算
  • 左右內連線要注意的是：ON後面的條件是為了生成兩者臨時表的條件，而where是為了篩選臨時表中內容的條件
    • 而且不管on上的條件是否為真都會返回left或right表中的記錄；但inner jion沒有這個特殊性，當條件放在on中和where中，沒有區別，返回的結果集是相同的
  • 我們都知道union關鍵字後，可以獲取去重後的資料，而union all關鍵字，獲取的是所有資料，包含重複的資料
    • 所以當我們知道查出來的資料中沒有重複值的時候選擇union all，而且一般情況下儘可能的去選擇union all，畢竟去重操作會遍歷排序等等操作，消耗cpu資源。
  • 以小表驅動大表
    • 小表並不是指資料量很小的表，而是與另一張表對比，在同一條件下，哪張表檢索量小，才是小表

要注意的是具體查詢的時候要根據業務需求來，確定主表，不能為了小表驅動大表，而破壞查詢邏輯
當連線查詢沒有where條件時，左連線查詢時，前面的表是驅動表，後面的表是被驅動表，右連線查詢時相反，內連線查詢時，哪張表的資料較少，哪張表就是驅動表
當連線查詢有where條件時，帶where條件的表是驅動表，否則是被驅動表
in 適用於左邊大表，右邊小表。
exists 適用於左邊小表，右邊大表。

  • 儘量不要在group by後面使用having語句，通常都是where在前，group by在後的過濾篩選操作

  • 對應同一列進行 or 判斷時，使用 in 代替 or

    • in 的值不要超過 500 個，in 操作可以更有效的利用索引，or 大多數情況下很少能利用到索引

  • 最後最重要的其實就是索引的問題，很多情況下要看sql到底有沒有走索引，導致查詢很慢，可以用explain命令去檢視

    • 索引這塊涉及點是比較多的，這裡不作過多內容

  • 索引失效的情況

四、最後

有些情況下，我們要根據自己的業務來判斷怎麼使用SQL，但大多數情況下還是要遵循開發中預設好的規範操作。

這次是簡單的記錄了一下對與sql函式的應用理解，以及對於sql優化的應用，下次打算總結一下設計模式，和開發模型，

以前是因為很大程度上都是自己悶頭學，企業級的專案也沒有接觸過，撐著這次實習把這些內容深刻的體會一下吧。

相關文章