純干貨|史上最全的技術(shù)崗面試筆記—數(shù)據(jù)庫篇（下）
SELECT city FROM weather WHERE temp_lo = max(temp_lo); WRONG不過這個(gè)方法不能運(yùn)轉(zhuǎn)，因?yàn)榫奂?max 不能用于 WHERE 子句中。（存在這個(gè)限制是因?yàn)閃HERE 子句決定哪些行可以進(jìn)入聚集階段；因此它必需在聚集函數(shù)之前計(jì)算。） 不 過，我們通常都可以用其它方法實(shí)現(xiàn)我們的目的；這里我們就可以使用子查詢：SELECT city FROM weather WHERE temp_lo = (SELECT max(temp_lo) FROMweather);理解聚集和SQL的 WHERE 以及 HAVING 子句之間的關(guān)系對我們非常重要。WHERE 和 HAVING 的基本區(qū)別如下： WHERE 在分組和聚集計(jì)算之前選取輸入行（因此，它控制哪些行進(jìn)入聚集計(jì)算）， 而 HAVING 在分組和聚集之后選取分組的行。因此，WHERE 子句不能包含聚集函數(shù)；因?yàn)樵噲D用聚集函數(shù)判斷那些行輸入給聚集運(yùn)算是沒有意義的。相反，HAVING 子句總是包含聚集函數(shù)。（嚴(yán)格說來，你可以寫不使用聚集的 HAVING 子句， 但這樣做只是白費(fèi)勁。同樣的條件可以更有效地用于 WHERE 階段。）當(dāng)應(yīng)用于空集時(shí)，SUM、AVG、MAX 和 MIN 函數(shù)可以返回 null 值。當(dāng) COUNT函數(shù)應(yīng)用于空集時(shí)，它返回零（0）。如果返回值可能是 NULL，那么使用包裝器類型；否則，容器會顯示 ObjectNotFound 異常。drop、truncate、 delete區(qū)別● truncate刪除表中數(shù)據(jù)，再插入時(shí)自增長id又從1開始。● delete刪除表中數(shù)據(jù)，可以加where字句。非關(guān)系型數(shù)據(jù)庫和關(guān)系型數(shù)據(jù)庫區(qū)別，優(yōu)勢比較?非關(guān)系型數(shù)據(jù)庫的優(yōu)勢：● 性能：NOSQL是基于鍵值對的，可以想象成表中的主鍵和值的對應(yīng)關(guān)系，而且不需要經(jīng)過SQL層的解析，所以性能非常高。● 可擴(kuò)展性：同樣也是因?yàn)榛阪I值對，數(shù)據(jù)之間沒有耦合性，所以非常容易水平擴(kuò)展。關(guān)系型數(shù)據(jù)庫的優(yōu)勢：● 復(fù)雜查詢：可以用SQL語句方便的在一個(gè)表以及多個(gè)表之間做非常復(fù)雜的數(shù)據(jù)查詢。● 事務(wù)支持：使得對于安全性能很高的數(shù)據(jù)訪問要求得以實(shí)現(xiàn)。%百分號通配符:表示任何字符出現(xiàn)任意次數(shù)(可以是0次).**_下劃線通配符:**表示只能匹配單個(gè)字符,不能多也不能少,就是一個(gè)字符.多列索引：ALTER TABLE people ADD INDEX lname_fname_age (lame,fname,age);為了提高搜索效率，我們需要考慮運(yùn)用多列索引,由于索引文件以B－Tree格式保存，所以我們不用掃描任何記錄，即可得到最終結(jié)果。注：在mysql中執(zhí)行查詢時(shí)，只能使用一個(gè)索引，如果我們在lname,fname,age上分別建索引,執(zhí)行查詢時(shí)，只能使用一個(gè)索引，mysql會選擇一個(gè)最嚴(yán)格(獲得結(jié)果集記錄數(shù)最少)的索引。最左前綴原則：顧名思義，就是最左優(yōu)先，上例中我們創(chuàng)建了lname_fname_age多列索引,相當(dāng)于創(chuàng)建了(lname)單列索引，(lname,fname)組合索引以及(lname,fname,age)組合索引。ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3')https://blog.csdn.net/qq_19557947/article/details/76951912MySQL B+Tree索引和Hash索引的區(qū)別? ● Hash索引結(jié)構(gòu)的特殊性，其檢索效率非常高，索引的檢索可以一次定位; ● B+樹索引需要從根節(jié)點(diǎn)到枝節(jié)點(diǎn)，最后才能訪問到頁節(jié)點(diǎn)這樣多次的IO訪 問;Hash索引僅僅能滿足'=','IN'和''查詢，不能使用范圍查詢Hash索引遇到大量Hash值相等的情況后性能并不一定就會比B+樹索引高為什么說B+比B樹更適合實(shí)際應(yīng)用中操作系統(tǒng)的文件索引和數(shù)據(jù)庫索引？B+的內(nèi)部結(jié)點(diǎn)并沒有指向關(guān)鍵字具體信息的指針。因此其內(nèi)部結(jié)點(diǎn)相對B樹更小。如果把所有同一內(nèi)部結(jié)點(diǎn)的關(guān)鍵字存放在同一盤塊中，那么盤塊所能容納的關(guān)鍵字?jǐn)?shù)量也越多。一次性讀入內(nèi)存中的需要查找的關(guān)鍵字也就越多。相對來說IO讀寫次數(shù)也就降低了。(2) B+tree的查詢效率更加穩(wěn)定由于非終結(jié)點(diǎn)并不是最終指向文件內(nèi)容的結(jié)點(diǎn)，而只是葉子結(jié)點(diǎn)中關(guān)鍵字的索引。所以任何關(guān)鍵字的查找必須走一條從根結(jié)點(diǎn)到葉子結(jié)點(diǎn)的路。所有關(guān)鍵字查詢的路徑長度相同，導(dǎo)致每一個(gè)數(shù)據(jù)的查詢效率相當(dāng)。聚集索引表記錄的排列順序和索引的排列順序一致，所以查詢效率快。聚集索引指定了表中記錄的邏輯順序，但是記錄的物理和索引不一定一致。聚集索引和非聚集索引的根本區(qū)別是表記錄的排列順序和與索引的排列順序是否一致。當(dāng)并發(fā)事務(wù)同時(shí)訪問一個(gè)資源時(shí)，有可能導(dǎo)致數(shù)據(jù)不一致，因此需要一種機(jī)制來將數(shù)據(jù)訪問順序化，以保證數(shù)據(jù)庫數(shù)據(jù)的一致性。多個(gè)事務(wù)同時(shí)讀取一個(gè)對象的時(shí)候，是不會有沖突的。同時(shí)讀和寫，或者同時(shí)寫才會產(chǎn)生沖突。共享鎖(S)表示對數(shù)據(jù)進(jìn)行讀操作。因此多個(gè)事務(wù)可以同時(shí)為一個(gè)對象加共享鎖。排他鎖(Exclusive Lock，也叫X鎖)排他鎖表示對數(shù)據(jù)進(jìn)行寫操作。如果一個(gè)事務(wù)對對象加了排他鎖，其他事務(wù)就不能再給它加任何鎖了。就是通常我們所說的鎖級別。MySQL有三種鎖的級別：頁級、表級、行級。表級鎖：開銷小，加鎖快；不會出現(xiàn)死鎖；鎖定粒度大，發(fā)生鎖沖突的概率最高,并發(fā)度最低。行級鎖：開銷大，加鎖慢；會出現(xiàn)死鎖；鎖定粒度最小，發(fā)生鎖沖突的概率最低,并發(fā)度也最高。頁面鎖：開銷和加鎖時(shí)間界于表鎖和行鎖之間；會出現(xiàn)死鎖；鎖定粒度界于表鎖和行鎖之間，并發(fā)度一般。通常用在DML語句中，如INSERT, UPDATE, DELETE等。對整個(gè)表加鎖，影響標(biāo)準(zhǔn)的所有記錄。通常用在DDL語句中，如DELETETABLE,ALTER TABLE等。很明顯，表鎖影響整個(gè)表的數(shù)據(jù)，因此并發(fā)性不如行鎖好。https://www.cnblogs.com/wenxiaofei/p/9853682.html總是假設(shè)最好的情況，每次去拿數(shù)據(jù)的時(shí)候都認(rèn)為別人不會修改，所以不會上鎖，但是在更新的時(shí)候會判斷一下在此期間別人有沒有去更新這個(gè)數(shù)據(jù)，可以使用版本號機(jī)制和CAS算法實(shí)現(xiàn)。樂觀鎖適用于多讀的應(yīng)用類型，這樣可以提高吞吐量。總是假設(shè)最壞的情況，每次去拿數(shù)據(jù)的時(shí)候都認(rèn)為別人會修改，所以每次在拿數(shù)據(jù)的時(shí)候都會上鎖，這樣別人想拿這個(gè)數(shù)據(jù)就會阻塞直到它拿到鎖（共享資源每次只給一 個(gè)線程使用，其它線程阻塞，用完后再把資源轉(zhuǎn)讓給其它線程）。傳統(tǒng)的關(guān)系型數(shù)據(jù)庫里邊就用到了很多這種鎖機(jī)制，比如行鎖，表鎖等，讀鎖，寫鎖等，都是在做操作之前先上鎖。所謂臟讀是指一個(gè)事務(wù)中訪問到了另外一個(gè)事務(wù)未提交的數(shù)據(jù)，獲得更新前的值 一個(gè)事務(wù)讀取2次，得到的記錄條數(shù)不一致一個(gè)事務(wù)讀取同一條記錄2次，得到的結(jié)果不一致所謂的同步復(fù)制，意思是master的變化，必須等待slave-1,slave-2,...,slave-n完成后才能返回。這樣，顯然不可取，也不是MySQL復(fù)制的默認(rèn)設(shè)置。比如，在WEB前端頁面上，用戶增加了條記錄，需要等待很長時(shí)間。如同AJAX請求一樣。master只需要完成自己的數(shù)據(jù)庫操作即可。至于slaves是否收到二進(jìn)制日志，是否完成操作，不用關(guān)心,MySQL的默認(rèn)設(shè)置。master只保證slaves中的一個(gè)操作成功，就返回，其他slave不管。這個(gè)功能，是由google為MySQL引入的。master的寫操作，slaves被動(dòng)的進(jìn)行一樣的操作，保持?jǐn)?shù)據(jù)一致性。如果slave可以主動(dòng)的進(jìn)行寫操作，slave又無法通知master，這樣就導(dǎo)致了master和slave數(shù)據(jù)不一致了。主從復(fù)制中，可以有N個(gè)slave，實(shí)現(xiàn)數(shù)據(jù)備份，異地容災(zāi)。插入數(shù)據(jù) insert into t1 values(5,'xiaoming',null); insert into t1 (id,name) values(2,'aa'); insert into t1 values(5,'xiaoming',null),(5,'xiaoming',null),(5,'xiaoming',null);insert into t1 (id,name) values (2,'aa'),(2,'aa'),(2,'aa');查詢 select * from t1; select name from t1; select * from t1 where id=10;修改 update t1 set age=100 where id=10;刪除 delete from t1 where id=10;修改表名 rename table t1 to t2;修改表屬性 alter table t1 engine=myisam/innodb charset=utf8/gbk;添加表字段 alter table t1 add age int first/after xxx;刪除表字段 alter table t1 drop age;修改表字段名和類型 alter table t1 change age newAge int;修改表的類型和位置 alter table t1 modify age int first/after xx;MySQL 主從復(fù)制概念MySQL 主從復(fù)制是指數(shù)據(jù)可以從一個(gè)MySQL數(shù)據(jù)庫服務(wù)器主節(jié)點(diǎn)復(fù)制到一個(gè)或多個(gè)從節(jié)點(diǎn)。MySQL 默認(rèn)采用異步復(fù)制方式，這樣從節(jié)點(diǎn)不用一直訪問主服務(wù)器來更新自己的數(shù)據(jù)，數(shù)據(jù)的更新可以在遠(yuǎn)程連接上進(jìn)行，從節(jié)點(diǎn)可以復(fù)制主數(shù)據(jù)庫中的所有數(shù)據(jù)庫或者特定的數(shù)據(jù)庫，或者特定的表。在開發(fā)工作中，有時(shí)候會遇見某個(gè)sql 語句需要鎖表，導(dǎo)致暫時(shí)不能使用讀的服務(wù)，這 樣就會影響現(xiàn)有業(yè)務(wù)，使用主從復(fù)制，讓主庫負(fù)責(zé)寫，從庫負(fù)責(zé)讀，這樣，即使主庫 出現(xiàn)了鎖表的情景，通過讀從庫也可以保證業(yè)務(wù)的正常運(yùn)作。l 數(shù)據(jù)實(shí)時(shí)備份，當(dāng)系統(tǒng)中某個(gè)節(jié)點(diǎn)發(fā)生故障時(shí)，可以方便的故障切換隨著系統(tǒng)中業(yè)務(wù)訪問量的增大，如果是單機(jī)部署數(shù)據(jù)庫，就會導(dǎo)致I/O訪問頻率過高。有了主從復(fù)制，增加多個(gè)數(shù)據(jù)存儲節(jié)點(diǎn)，將負(fù)載分布在多個(gè)從節(jié)點(diǎn)上，降低單機(jī)磁盤I/O訪問的頻率，提高單個(gè)機(jī)器的I/O性能。一主一從，一主多從，提高系統(tǒng)的讀性能。一主一從和一主多從是最常見的主從架構(gòu)，實(shí)施起來簡單并且有效，不僅可以實(shí)現(xiàn)HA，而且還能讀寫分離，進(jìn)而提升集群的并發(fā)能力。多主一從 （從5.7開始支持）多主一從可以將多個(gè)mysql數(shù)據(jù)庫備份到一臺存儲性能比較好的服務(wù)器上。雙主復(fù)制，也就是互做主從復(fù)制，每個(gè)master既是master，又是另外一臺服務(wù)器的slave。這樣任何一方所做的變更，都會通過復(fù)制應(yīng)用到另外一方的數(shù)據(jù)庫中。級聯(lián)復(fù)制。級聯(lián)復(fù)制模式下，部分slave的數(shù)據(jù)同步不連接主節(jié)點(diǎn)，而是連接從節(jié)點(diǎn)。因?yàn)槿绻鞴?jié)點(diǎn)有太多的從節(jié)點(diǎn)，就會損耗一部分性能用復(fù)制，那么我們可以讓3~5個(gè)從節(jié)點(diǎn)連接主節(jié)點(diǎn)，其它從節(jié)點(diǎn)作為二級或者三級與從節(jié)點(diǎn)連接，這樣不僅可以緩解主節(jié)點(diǎn)的壓力，并且對數(shù)據(jù)一致性沒有負(fù)面影響。MySQL主從復(fù)制涉及到三個(gè)線程，一個(gè)運(yùn)行在主節(jié)點(diǎn)（log dump thread），其余兩個(gè)(I/O thread, SQL thread)運(yùn)行在從節(jié)點(diǎn)。主節(jié)點(diǎn) binary log dump 線程當(dāng)從節(jié)點(diǎn)連接主節(jié)點(diǎn)時(shí)，主節(jié)點(diǎn)會創(chuàng)建一個(gè)log dump 線程，用于發(fā)送bin-log的內(nèi)容。在讀取bin-log中的操作時(shí)，此線程會對主節(jié)點(diǎn)上的bin-log加鎖，當(dāng)讀取完成，甚至在發(fā)動(dòng)給從節(jié)點(diǎn)之前，鎖會被釋放。當(dāng)從節(jié)點(diǎn)上執(zhí)行`start slave`命令之后，從節(jié)點(diǎn)會創(chuàng)建一個(gè)I/O線程用來連接主節(jié)點(diǎn)，請求主庫中更新的bin-log。I/O線程接收到主節(jié)點(diǎn)binlog dump 進(jìn)程發(fā)來的更新之后，保存在本地relay-log中。SQL線程負(fù)責(zé)讀取relay log中的內(nèi)容，解析成具體的操作并執(zhí)行，最終保證主從數(shù)據(jù)的一致性。從節(jié)點(diǎn)上的I/O 進(jìn)程連接主節(jié)點(diǎn)，并請求從指定日志文件的指定位置（或者從最開始的日志）之后的日志內(nèi)容；主節(jié)點(diǎn)接收到來自從節(jié)點(diǎn)的I/O請求后，通過負(fù)責(zé)復(fù)制的I/O進(jìn)程根據(jù)請求信息讀取指定日志指定位置之后的日志信息，返回給從節(jié)點(diǎn)。返回信息中除了日志所包含的信息之外，還包括本次返回的信息的bin-log file 的以及bin-log position；從節(jié)點(diǎn)的I/O進(jìn)程接收到內(nèi)容后，將接收到的日志內(nèi)容更新到本機(jī)的relay log中，并將讀取到的binarylog文件名和位置保存到master-info 文件中，以便在下一次讀取的時(shí)候能夠清楚的告訴Master“我需要從某個(gè)bin-log 的哪個(gè)位置開始往后的日志內(nèi)容，請發(fā)給我”；Slave 的 SQL線程檢測到relay-log 中新增加了內(nèi)容后，會將relay-log的內(nèi)容解析成在祝節(jié)點(diǎn)上實(shí)際執(zhí)行過的操作，并在本數(shù)據(jù)庫中執(zhí)行。https://zhuanlan.zhihu.com/p/50597960
最后，也歡迎大家關(guān)注這位同學(xué)的Github：https://github.com/TG-yang。
本站僅提供存儲服務(wù)，所有內(nèi)容均由用戶發(fā)布，如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請
點(diǎn)擊舉報(bào)。
