Mysql知識梳理
資料型別
// todo
儲存引擎
InnoDB
- InnoDB是是Mysql預設的事務性儲存引擎
- InnoDB才有MVCC來支援高併發，並且實現了四個標準的隔離級別，預設級別是可重複讀
- InnoDB儲存引擎下的表是基於聚簇索引建立的，對主鍵的查詢效能有很高的提升
MyISAM
- 提供了大量的特性，包括全文索引、壓縮、空間函式等
- 不支援事物和行級鎖
InnoDB與MyISAM的比較
InnoDB：支援事物、線上熱備份、行鎖
MyISAM：支援全文索引、地理空間索引
索引
索引是幫助MySQL高效獲取資料的資料結構，所以索引本質上是一種資料結構
索引分類
- B-Tree索引
B-Tree索引是大多數Mysql儲存引擎預設的索引型別
使用B-Tree索引後，不用再進行全表掃描，只需要對樹進行搜尋即可，因此查詢速度會快很多
可以指定多個列作為索引列，多個索引列共同組成鍵
B-Tree 索引適用於全鍵值、鍵值範圍和鍵字首查詢，其中鍵字首查詢只適用於最左字首查詢
除了用於查詢，還可以用於排序和分組 - 雜湊索引
基於雜湊表的實現，優點是查詢非常快
在Mysql中只有Memory儲存引擎支援雜湊索引 - 空間索引(R-Tree)
MyISAM儲存引擎支援空間索引，可以用於地理資料儲存 - 全文索引
MyISAM儲存引擎支援全文索引，用於查詢文字中的關鍵字，而不是直接比較索引中的值
索引的優點
- 加快資料查詢方式，提高資料庫查詢效能
- 大大減少了伺服器需要掃描的資料量
- 幫助伺服器避免進行排序和建立臨時表
- 將隨機 I/O 變為順序 I/O
索引的缺點
- 實際上索引也是一張表，該表儲存了主鍵與索引欄位，並指向實體表的記錄，所以索引列也是要佔用空間的。
- 雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對錶進行insert,update和delete。因為更新表時，MySQL不僅要儲存資料，還要儲存一下索引檔案每次更新新增索引列的欄位，都會調整因為更新所帶來的鍵值變化後的索引資訊。
- 索引只是提高效率的一個因素，如果你的MySQL有大資料量的表，就需要花時間 研究建立最優秀的索引，或優化查詢語句。
建立索引的兩種方式
-
create index
CREATE INDEX index_name ON table_name (column_list) -
alter table
ALTER TABLE `table_name` ADD INDEX index_name (column_list)
操作索引
以articles的type欄位為例
-- 建立索引
CREATE INDEX idx_type ON articles (type);
-- 刪除索引
drop index idx_type on articles
-- 檢視索引
show index from articles使用索引的時機
一般情況下，在where或join子句中出現的列需要新增索引。但是，因為MySQL只對<,<=,=,>,>=,between,in,以及某些時候的like才會使用索引（使用like時，以萬用字元%和_查詢時，MySQL不會使用索引）
-
哪些情況下需要建立索引
- 主鍵自動建立唯一索引
- 頻繁作為查詢條件的欄位應該建立索引
- 查詢中與其他表關聯的欄位，外來鍵關係建立索引
- 單鍵/組合索引的選擇問題(在高併發下傾向組合索引)
- 查詢中排序的欄位，排序欄位通過索引去訪問將大大提高排序速度
- 查詢中統計或分組欄位
-
哪些情況下不需要建立索引
- 表記錄太少，網上有建議2000為界限，2000以下不建立
- 經常增刪改的表（因為每次增刪改不僅要運算元據還要操作索引）
- where條件用不到的欄位
- 資料重複且分佈平均的欄位，索引的選擇性較低，即當前欄位不重複的索引值與表中當前欄位的記錄數比值，值越大越不建議建索引
建立索引技巧
-
給維度高的列建立索引
- 資料列中不重複值出現的個數，數量越高，維度越高
- 重複資料會降低維度
- 給緯度高的列建立索引，比如使用者表的
年齡維度就高於性別 - 性別這種低緯度的列不適合建索引
- 對where,on,group by,order by中出現的列使用索引
- 對較小的資料列使用索引，這樣會使索引檔案更小，同時記憶體中也可以裝載更多的索引建
- 為較長的字串使用字首索引
- 不要過多建立索引，過多的索引會增加額外的磁碟空間，對DML操作速度影響很大，因為每增刪改查一次就得重新建立索引
- 使用組合索引，可以減少檔案索引大小，在使用時速度要優於多個單列索引
使用索引的注意事項
- 索引不會包含有Null值的列，所以我們在資料庫設計時不要讓欄位的預設值為NULL
-
使用短索引
- 對字串列進行索引,如果可能應該指定一個字首長度
-
索引列排序
- MySQL查詢只使用一個索引，因此如果where子句中已經使用了索引的話，那麼order by中的列是不會使用索引的。因此資料庫預設排序可以符合要求的情況下不要使用排序操作；儘量不要包含多個列的排序，如果需要最好給這些列建立複合索引。
- 不在索引列進行運算操作
- 建組合索引的時候，區分度最高的在最左邊
索引優化
-
獨立的列
在查詢時，索引不能是表示式的一部分，也不能是函式的引數，否則無法使用索引
-
字首索引
對於blob，text,varchar型別的列，必須使用字首索引，只索引開始的部分字元
-
多列索引
在需要使用多個列作為條件查詢時，使用多列索引比使用單列索引效能要好
-
索引列的順序
在寫查詢語句時，將選擇性強的列放在前面
join語句的優化
left join是由左邊決定的，左邊一定都有，所以右邊是我們的關鍵點，建立索引要建右邊的。當然如果索引在左邊，可以用右連線。
儘可能減少Join語句中的NestedLoop的迴圈次數：“永遠用小結果集驅動大的結果集”
避免索引失效
- 最佳左字首法則：如果查詢中使用了多個索引列，要遵循最左字首法則，指的是查詢從索引的最左前列開始並且不跳過索引中列。
- 不在索引列上做任何操作（計算、函式、（自動/手動）型別轉換），會導致索引失效而轉向全表掃描。
-
where條件的列
=的判斷放在比較運算子>、<等的左邊，放在比較運算子右邊的索引會失效
比如：select * from user where username="saboran" and age > 18 and mobile = "18862612345"其中username、age、mobile都有索引，但是隻有username和age的索引會生效，mobile索引用不到
- select查詢時儘量減少
select *操作，用需要的欄位代替* - 在使用
!=或者<>的時候無法使用索引，會導致全表掃描 - is null 和 is not null 也無法使用索引
-
like 以萬用字元開頭，mysql索引會失效變成全表掃描
所以最好用右邊萬用字元匹配like `tssk%`
如果要使用兩邊萬用字元匹配，則將like條件放在最後一個
比如：select age from users where a = 3 and b = 4 and c like "%abcd%";這樣a、b、c都有索引的話，a、b用的上，c用不上
- 字串不加單引號索引會失效
- 少用or，用它連線時會索引失效
- 避免子查詢，使用join
一般性建議
- 對於單鍵索引，儘量選擇針對當前查詢語句過濾性更好的索引作為查詢條件
- 在選擇組合索引時，當前query中過濾性最好的索引放在where條件的位置越靠前越好
- 儘可能通過分析統計資訊和調整query的寫法來達到選擇合適索引的目的
查詢效能優化
Explain
用來分析SQL語句，分析結果中比較重要的欄位有：
- select_type:查詢型別，有簡單查詢、聯合查詢和子查詢
- key:使用的索引
- rows:掃描的行數
減少返回的列
慢查詢主要是因為訪問了過多資料，除了訪問過多行之外，也包括訪問了過多列。最好不要使用select * 語句，要根據需要選擇查詢的列
減少查詢的行
最好使用limit語句取出想要的那些行，還可以建立索引來減少條件語句的全表掃描
常用函式
數學函式
-
ABS(x) // 返回x的絕對值
select abs(age) from users limit 1; -- 18 -
BIN(x) // 返回x的二進位制數
select bin(age) from users limit 1; -- 10010 -
CEILING(x) // 返回大於x的最小整數值
SELECT CEILING (19.1) ; -- 20 -
FLOOR(x) // 返回小於x的最大值
SELECT floor (19.1) ; -- 19 -
RAND() // 返回0到1的隨機數
SELECT rand() ; -- 0.8320153586864615 隨機數 -
ROUND(x,y) // 返回引數x的四捨五入的y位小數值
SELECT ROUND(100.123456,3); -- 100.123
聚合函式(常用與group by從句的select查詢中)
-
AVG(col) // 返回指定列的平均數
select avg(age) from users ; -- 14.0000 -
COUNT(col) // 返回指定列中非null值的個數
SELECT count(id) from users ; -- 2 -
MIN(col) // 返回指定列的最小值
select min(age) from users ; -- 10 -
MAX(colcol) // 返回指定列的最大值
select max(age) from users ; -- 18 -
SUM(col) // 返回指定列所有值的和
select sum(age) from users ; -- 28 -
GROUP_CONCAT(col) // 返回由屬於一組的列值連線組合而成的結果
select GROUP_CONCAT(age) from users ; -- 18,20
字串函式
-
CONCAT(s1,s2,s3,sn) // 將s1,s2,s3,sn連線為字串
select CONCAT(id,age,name) from users limit 1; -- 118安小下 -
CONCAT_WS(`|`) // 將s1,s2,s3,sn連線為字串，並使用
|分隔，|可以替換為任意分隔符SELECT CONCAT_WS(`|`,id,name,age) from users limit 1; -- 1|安小下|18
日期和時間函式
-
CURDATE()/CURRENT_DATE() // 返回當前日期
SELECT CURRENT_DATE(); -- 2018-03-08 -
CURTIME()/CURRENT_TIME() // 返回當前時間
SELECT CURRENT_TIME(); -- 08:54:15 -
DATE_FORMAT(date,fmt) // 按照fmt格式，格式化date
SELECT DATE_FORMAT(CURRENT_DATE(),`%Y/%m/%d`); -- 2018/03/08 -
DAYOFWEEK(date) // 返回date為一週之內的第幾天,從0開始，0代表第一天
SELECT DAYOFWEEK(CURRENT_DATE()); -- 5 -
DAYOFMONTH(date) // 返回date為一月之內的第幾天
SELECT DAYOFMONTH(CURRENT_DATE()); -- 8 -
DAYOFYEAR(date) // 返回date為一年之內的第幾天
SELECT DAYOFYEAR(CURRENT_DATE()); -- 67 -
DAYNAME(date) // 返回date的星期名
SELECT DAYNAME(CURRENT_DATE()); -- Thursday -
FROM_UNIXTIME(timestimps,fmt) // 時間戳轉成fmt格式的字串時間
SELECT FROM_UNIXTIME(1520500384,"%Y/%m/%d"); -- 2018/03/08 -
HOUR(time) // 返回time的小時值(0-23)
SELECT HOUR(`20:10`); -- 20 -
MINUTE(time) // 返回time的分鐘值(0-59)
SELECT HOUR(`20:10`); -- 10 -
MONTH(date) // 返回date的月份值(1-12)
SELECT MONTH(CURRENT_DATE()); -- 3 -
MONTHNAME(date) // 返回date的月份名
SELECT MONTHNAME(CURRENT_DATE()); -- March -
NOW() // 獲取當前日期和時間
SELECT NOW(); -- 2018-03-08 09:26:55 -
WEEK(date) // 返回日期date為一年中的第幾周
SELECT WEEK(CURDATE()); -- 9 -
YEAR(date) // 返回日期date的年份
SELECT YEAR(CURDATE()); -- 2018
加密函式
- MD5(str) // 計算字串str的MD5檢驗值
- PASSWORD(str) // 返回字串str的加密版本，這個加密是不可逆的
- SHA(str) // 計算字串str的安全雜湊演算法檢驗值
控制流程函式
// todo
格式化函式
- INET_ATON(ip) // 返回ip代表額數字
- INET_NTOA(num) // 返回數字代表的ip
Distinct去重
單獨的distinct只能放在開頭
-- 會報錯
select id,DISTINCT(name) from test;
-- 不會報錯
select DISTINCT(name) from test;