<input id="ohw05"></input>
  • <table id="ohw05"><menu id="ohw05"></menu></table>
  • <var id="ohw05"></var>
  • <code id="ohw05"><cite id="ohw05"></cite></code>
    <label id="ohw05"></label>
    <var id="ohw05"></var>
  • Loading

    記錄一次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優化的應用,下次打算總結一下設計模式,和開發模型,

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

    posted @ 2022-03-22 15:54  余月七  閱讀(505)  評論(0編輯  收藏  舉報
    国产美女a做受大片观看