Float、Decimal 存儲金額的區(qū)別?
MySQL中存在 float, double 等非標(biāo)準(zhǔn)數(shù)據(jù)類型, 也有 decimal 這種標(biāo)準(zhǔn)數(shù)據(jù)類型
其區(qū)別在于: float, double等非標(biāo)準(zhǔn)類型在DB中保存的是近似值, 而Decimal則以字符串的形式保存數(shù)值.
float和double 數(shù)據(jù)的精確度取決于分配給每種數(shù)據(jù)類型的存儲長度, 其中float分配了4字節(jié), 而double分配了8字節(jié), 采用float和double本來就是不準(zhǔn)的decimal類型是MySQL官方唯一指定能精確存儲的類型, 和金錢相關(guān)的類型都要存儲為decimal
Datetime、Timestamp 存儲時間的區(qū)別?
Datetime
Timestamp
CHAR, VARCHAR, BINARY, VARBINARY 存儲字符的區(qū)別?
CHAR
CHAR(N) 用來存儲非二進制字符串, 插入少于N個字符的會自動在尾部加空格, 查詢時, 尾部的空格就會被丟棄掉(客戶端可以忽略這個, 當(dāng)作存入和取出的一致就行). CHAR 使用固定長度的空間進行存儲, CHAR(4)存儲4個字符, 根據(jù)編碼方式的不同占用不同的字節(jié), GBK 編碼不論是中文還是英文, 每個字符占用2個字節(jié), UTF8編碼每個字符占用3個字節(jié).
VARCHAR
VARCHAR(N) 用來存儲非二進制字符串, 插入少于N個字符的不填補空格, 查詢時, 尾部的空格不會被丟棄掉
BINARY
BINARY(N)存儲二進制字符串, 插入少于N個字節(jié)的會自動在尾部加0x00, 取出時, 所有的字節(jié)都保留, 返回定義長度的字節(jié)長度, 在比較的時候, 所有的字節(jié)都是有效的, 并且0x00 小于 space (space對應(yīng)的是0x20)
VARBINARY
VARBINARY 在插入不會去填補0x00字節(jié), 查詢的時候也不會丟棄任何字節(jié), 在比較的時候, 所有的字節(jié)都是有效的,
區(qū)別和選擇
如果需要存儲的字符串的長度跟所有值的平均長度相差不大, 適合用 CHAR, 如MD5; 對于經(jīng)常改變的值, CHAR 優(yōu)于 VARCHAR, 原因是固定長度的行不容易產(chǎn)生碎片.對于很短的列, CHAR 優(yōu)于 VHARCHAR, 原因是 VHARCHAR 需要額外一個或兩個字節(jié)存儲字符串的長度.
BINARY 和 VARBINARY 是和編碼無關(guān)的存儲, 適合存儲二進制數(shù)據(jù).
MySQL 有哪些存儲引擎? 都有什么區(qū)別?
MySQL 幾種存儲引擎的應(yīng)用場景?
MyISAM
- 較高的插入和查詢速度, 不支持事務(wù)
- 如果數(shù)據(jù)表主要用來插入和查詢記錄, 則MyISAM能提供較高的處理效率
InnoDB
事務(wù)型數(shù)據(jù)庫的首選引擎, 支持事務(wù)安全表 ACID, 支持行鎖定和外鍵, 是默認(rèn)的引擎
如果要提供提交、回滾、崩潰恢復(fù)能力的事務(wù)安全（ACID兼容）能力, 并要求實現(xiàn)并發(fā)控制, InnoDB是一個好的選擇
InnoDB 和 MyISAM之間的區(qū)別:
MEMORY/HEAP
存儲引擎將表中的數(shù)據(jù)存儲到內(nèi)存中, 為查詢和引用其他表數(shù)據(jù)提供快速訪問
如果只是臨時存放數(shù)據(jù), 數(shù)據(jù)量不大, 并且不需要較高的數(shù)據(jù)安全性, 可以選擇將數(shù)據(jù)保存在內(nèi)存中的Memory引擎, MySQL中使用該引擎作為臨時表, 存放查詢的中間結(jié)果, 數(shù)據(jù)的處理速度很快但是安全性不高.
Archive
只允許INSERT和SELECT操作. Archive支持高并發(fā)的插入操作, 但是本身不是事務(wù)安全的. Archive非常適合存儲歸檔數(shù)據(jù), 如記錄日志信息可以使用Archive
MyISAM 和 InnoDB 的區(qū)別
第一個重大區(qū)別是InnoDB的數(shù)據(jù)文件本身就是索引文件. MyISAM索引文件和數(shù)據(jù)文件是分離的, 索引文件僅保存數(shù)據(jù)記錄的地址. 而在InnoDB中, 表數(shù)據(jù)文件本身就是按B+Tree組織的一個索 引結(jié)構(gòu), 這棵樹的葉節(jié)點data域保存了完整的數(shù)據(jù)記錄. 這個索引的key是數(shù)據(jù)表的主鍵, 因此InnoDB表數(shù)據(jù)文件本身就是主索引.InnoDB要求表必須有主鍵（MyISAM可以沒有）, 如果沒有顯式指定, 則MySQL系統(tǒng)會自動選擇一個可以唯一標(biāo)識數(shù)據(jù)記錄的列 作為主鍵, 如果不存在這種列, 則MySQL自動為InnoDB表生成一個隱含字段作為主鍵, 這個字段長度為6個字節(jié), 類型為長整形
第二個與MyISAM索引的不同是InnoDB的輔助索引data域存儲相應(yīng)記錄主鍵的值而不是地址. 換句話說, InnoDB的所有輔助索引都引用主鍵作為data域
了解不同存儲引擎的索引實現(xiàn)方式對于正確使用和優(yōu)化索引都非常有幫助, 例如知道了InnoDB的索引實現(xiàn)后, 就很容易明白為什么不建議使用過長的字段作為 主鍵, 因為所有輔助索引都引用主索引, 過長的主索引會令輔助索引變得過大. 再例如, 用非單調(diào)的字段作為主鍵在InnoDB中不是個好主意, 因為 InnoDB 數(shù)據(jù)文件本身是一棵 B+Tree, 非單調(diào)的主鍵會造成在插入新記錄時數(shù)據(jù)文件為了維持B+Tree的特性而頻繁的分裂調(diào)整, 十分低效, 而使用 自增字段作為主鍵則是一個很好的選擇
使用場景
- Archive 用來存日志
- memory用來存session
- MyISAM盡量不用
- 其他的都用InnoDB
MySQL索引類型有?
- 僅僅能滿足”=”,”IN”和””查詢, 不能使用范圍查詢
- 其檢索效率非常高, 索引的檢索可以一次定位, 不像B-Tree 索引需要從根節(jié)點到枝節(jié)點, 最后才能訪問到頁節(jié)點這樣多次的IO訪問, 所以 Hash 索引的查詢效率要遠高于 B-Tree 索引
- 只有Memory存儲引擎顯式支持hash索引
對比一下B+樹索引和 Hash索引
B+樹
一個平衡的多叉樹. B+樹從根節(jié)點到葉子節(jié)點的搜索效率基本相當(dāng), 不會出現(xiàn)大幅波動
哈希索引
采用一定的哈希算法, 把鍵值換成新的哈希值, 檢索時不需要類似B+樹那樣從根節(jié)點逐級查找, 只需一次哈希算法即可立刻定位到相應(yīng)的位置, 查詢效率要遠高于 B-Tree 索引
區(qū)別
等值查詢哈希索引具有絕對優(yōu)勢（前提是: 沒有大量重復(fù)鍵值, 如果大量重復(fù)鍵值時, 哈希索引的效率很低, 因為存在所謂的哈希碰撞問題. Hash 索引在任何時候都不能避免表掃描, 即使取滿足某個 Hash 鍵值的數(shù)據(jù)的記錄條數(shù), 也無法從 Hash 索引中直接完成查詢, 還是要通過訪問表中的實際數(shù)據(jù)進行相應(yīng)的比較, 并得到相應(yīng)的結(jié)果
哈希索引不適用的場景:
MySQL中, 只有HEAP/MEMORY引擎才顯式支持哈希索引, 而常用的InnoDB引擎中默認(rèn)使用的是B+樹索引, 不能指定使用哈希索引, 只能通過設(shè)置自適應(yīng)哈希索引間接使用.
- InnoDB不支持HASH索引(但是InnoDB在內(nèi)部利用哈希索引來實現(xiàn)其自適應(yīng)哈希索引功能)
- InnoDB會根據(jù)表的使用情況自動為表生成hash索引, 不能人為干預(yù)是否在InnoDB一張表中創(chuàng)建HASH索引
- 當(dāng)InnoDB某些索引值被使用的特別頻繁時, 會在內(nèi)存中基于Btree的索引之上再創(chuàng)建一個HASH索引, 這樣BTREE索引也具備了HASH索引的一些優(yōu)點
unique key unique_username using btree(user_name)
這里的using btree 只是顯式指定的使用的索引的方式為b+樹, 對于innodb來說默認(rèn)的索引方式也是用b+樹, 因此可以不寫
聚簇索引和非聚簇索引的區(qū)別?
聚簇索引是對磁盤上實際數(shù)據(jù)重新組織以按指定的一個或多個列的值排序的算法. 特點是存儲數(shù)據(jù)的順序和索引順序一致. 一般情況下主鍵會默認(rèn)創(chuàng)建聚簇索引, 且一張表只允許存在一個聚簇索引.
聚簇索引的葉子節(jié)點就是數(shù)據(jù)節(jié)點, 而非聚簇索引的葉子節(jié)點仍然是索引節(jié)點, 只不過有指向?qū)?yīng)數(shù)據(jù)塊的指針
MyISAM的是非聚簇索引, B+Tree的葉子節(jié)點上的data, 并不是數(shù)據(jù)本身, 而是數(shù)據(jù)存放的地址. 主索引和輔助索引沒啥區(qū)別, 只是主索引中的key一定得是唯一的
InnoDB使用的是聚簇索引, 將主鍵組織到一棵B+樹中, 而行數(shù)據(jù)就儲存在葉子節(jié)點上, 若使用”where id = 14″這樣的條件查找主鍵, 則按照B+樹的檢索算法即可查找到對應(yīng)的葉節(jié)點, 之后獲得行數(shù)據(jù)
非聚集索引中的聚集索引鍵
在MySQL 5.6.9版本前, Innodb的非聚集索引中包含聚集索引的索引鍵, 但只起到通過非聚集索引定位記錄的作用, 但在MySQL 5.6.9之后版本中, 優(yōu)化器會考慮非聚集索引中包含的聚集索引鍵來提升查詢性能, 并提供優(yōu)化器選項use_index_extensions來開啟或關(guān)閉該特性.
假設(shè)有表TB1(ID,C1,C2), ID為主鍵聚集索引, 然后在列C1建立索引IDX_C1(C1):
- 在MySQL 5.6版本前, 索引類似于IDX_C1(C1) INCLUDE(ID);
- 在MySQL 5.6版本中, 索引類似于IDX_C1(C1,ID);
無論是MySQL 5.5還是MySQL 5.6版本中, 非聚集索引上的數(shù)據(jù)都是先按照非聚集索引鍵在按照聚集索引鍵進行排序, 即在非聚集索引鍵上值相同的記錄會按照聚集索引進行排序.
B+tree 如何進行優(yōu)化? 索引遵循哪些原則?
最左前綴匹配原則, 非常重要的原則, mysql會一直向右匹配直到遇到范圍查詢=和in可以亂序盡量選擇區(qū)分度高的列作為索引查詢時, 索引列不要參與計算
還有什么其他的索引類型, 各自索引有哪些優(yōu)缺點?
B+ Tree, Hash, FullText, R Tree
如何管理 MySQL索引?
主要說一下索引的創(chuàng)建, 修改和刪除, 以及不同的索引類型: 普通索引, 唯一索引, 全文索引, 空間索引, 單列索引, 多列索引
ALTER TABLE 表名 ADD INDEX 索引名(列名);CREATE INDEX 索引名 ON 表名(列名);ALTER TABLE 表名 ADD UNIQUE INDEX 索引名(列名);CREATE UNIQUE INDEX 索引名 ON 表名(列名);ALTER TABLE 表名 ADD INDEX 索引名(列名,列名2);CREATE INDEX 索引名 ON 表名(列名1,列名2);ALTER TABLE 表名 ADD FULLTEXT INDEX 索引名(列名);CREATE FULLTEXT INDEX 索引號 ON 表名(列名);ALTER TABLE 表名 ADD SPATIAL INDEX 索引名(列名);CREATE SPATIAL INDEX 索引號 ON 表名(列名);
對Explain 結(jié)果中參數(shù)的理解?
參數(shù): Type 連接類型
連接類型(the join type), 描述了找到所需數(shù)據(jù)使用的掃描方式, 最為常見的掃描方式從快到慢依次為
system
系統(tǒng)表, 少量數(shù)據(jù), 往往不需要進行磁盤IO, 掃描類型為system 說明數(shù)據(jù)已經(jīng)加載到內(nèi)存, 不需要磁盤IO, 這類掃描是速度最快的
const
常量連接, 主鍵或者唯一鍵上的等值查詢, const掃描的條件為
eq_ref
主鍵索引(primary key)或者非空唯一索引(unique not null)等值掃描, eq_ref掃描的條件為: 對于前表的每一行(row), 后表只有一行被掃描.
ref
非主鍵非唯一索引等值掃描. 對于前表的每一行(row), 后表可能有多于一行的數(shù)據(jù)被掃描.
range
range, 范圍掃描, 它是索引上的范圍查詢, 它會在索引上掃碼特定范圍內(nèi)的值.
index
index, 索引樹掃描, 需要掃描索引上的全部數(shù)據(jù).
ALL
全表掃描
參數(shù): Extra
Using where
SQL使用了where條件過濾數(shù)據(jù).
Using index
SQL所需要返回的所有列數(shù)據(jù)均在一棵索引樹上, 而無需訪問實際的行記錄.
Using index condition
說明確實命中了索引, 但不是所有的列數(shù)據(jù)都在索引樹上, 還需要訪問實際的行記錄.
Using filesort
說明得到所需結(jié)果集, 需要對所有記錄進行文件排序. 典型的, 在一個沒有建立索引的列上進行了order by, 就會觸發(fā)filesort, 常見的優(yōu)化方案是, 在order by的列上添加索引, 避免每次查詢都全量排序.
Using temporary
說明使用了臨時表(temporary table)來暫存中間結(jié)果. 這類SQL語句性能較低, 往往也需要進行優(yōu)化. 例如 group by和order by同時存在, 且作用于不同的字段時, 就會建立臨時表.
索引與鎖有什么關(guān)系?
mysql innodb的鎖是通過鎖索引來實現(xiàn)的select for update, 如果字段沒有索引, 即使使用where條件也會進行表級鎖
如果有索引, 會鎖定對應(yīng)where條件中索引值的所有行, 可理解為對該索引值進行了索引, 所以即使另一事務(wù)查詢的是其他行, 因為索引值相同也會被鎖住.
有索引而且使用了不同的索引值查數(shù)據(jù), 但是查詢 的結(jié)果是同一行, 可以理解為真正的數(shù)據(jù)行鎖
來源：https://www.cnblogs.com/milton/p/15856842.html
“做程序員，圈子和學(xué)習(xí)最重要”因為有有了圈子可以讓你少走彎路，擴寬人脈，擴展思路，學(xué)習(xí)他人的一些經(jīng)驗及學(xué)習(xí)方法！同時在這分享一下是一直以來整理的Java后端進階筆記文檔和學(xué)習(xí)資料免費分享給大家！需要資料的朋友私信我扣【06】
