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