JAVA面試二（資料庫部分）

1. 事務ACID特性

• 事務是併發控制的單位，是使用者定義的一個操作序列，這些操作要麼都做，要麼都不做，是一個不可分割的工作單位（如轉賬，A賬號扣錢成功，B賬號加錢成功，才算真正的轉賬成功）；
• 事務必須滿足四大特徵：原子性、一致性、隔離性和永續性/持續性
  1. 原子性：表示事務內操作不可分割，要麼都成功，要麼都失敗；
  2. 一致性：要麼都成功，要麼都失敗，失敗的話要對前面的操作進行回滾；
  3. 隔離性：一個事務開始後，不能受其他事務干擾；
  4. 永續性/持續性：表示事務開始了，就不能終止；

2. mysql資料庫預設的最大連線數

• 首先要明白為什麼需要最大連線數，因為特定伺服器上面的資料庫只能支援一定數目同時連線，這時候我們一般都會設定最大連線數（最多同時服務多少連線）；
• 在資料庫安裝時都會有一個預設的最大連線數，為100（在mysql的my.ini檔案中可查到）；

# The maximum amount of concurrent sessions the MySQL server will
# allow. One of these connections will be reserved for a user with
# SUPER privileges to allow the administrator to login even if the
# connection limit has been reached.
max_connections=100

3. mysql和oracle的分頁

• mysql的分頁是使用關鍵字limit來進行分頁的，limit offset，size表示從多少索引取多少位；

String sql=
    "select * from students order by id limit"+pageSize*(pageNumber-1)+","+pageSize;

• oracle的分頁，採用的是三層巢狀查詢

String sql=
    "select * from"+
    "(select *,rownum rid from(select * from students order by postime desc)where rid<="+
   pagesize*pagenumber+") as t" +
    "where t>"+pageSize*(pageNumber-1);

4. 觸發器

• 觸發器，需要有觸發條件，當條件滿足後做一些操作；
• 觸發器用處很多，如每插入一個帖子，都希望將版面表中的最後發帖時間、帖子總數欄位進行同步更新，用觸發器做效率就很高；

create table board1(
	id int primary auto_increment,
    name varchar(50),
    articleCount int
);
create table article1(
	id int primary key auto_increment,
    title varchar(50),
    bid int references board1(id)
);
delimiter |
create trigger insertArticle_Trigger after insert on article1 for each row
begin
	update board1 set articleCount=articleCount+1 where id=new bid;
end;
	|
delimiter ;
insert into board1 value(null,'test',0);
insert into article1 value(null,'test',1);

5. 寫一個簡單的jdbc

• 賈璉欲執事

  1. 載入驅動：com.mysql.Driver / oracle.jdbc.driver.OracleDriver；

  2. 獲取連線：DriverManager.getConnect(url,username,password);

  3. 設定引數：Statement / PreparedStatement cstmt

     ​ cstmt.setXXX(index,value)；

  4. 執行：executeQuery等；

  5. 釋放連線：釋放要從小到大，必須放到finnaly；

• PreparedStatement和Statement的區別

  1. PreparedStatement是預編譯的，比Statement速度快；
  2. PreparedStatement程式碼的可讀性高；
  3. PreparedStatement可以防止SQL隱碼攻擊，安全性高；

//sql注入舉例
String sql = "select * from student where name="'+name+'"and password="'+pwd';
//如果我們用(' 'or'1'='1')這一項作為pwd傳入，那麼sql語句就會變成如下：
select * from student where name='隨意' and password='隨意' or '1'=1;
//因為'1'='1'肯定成立，所以可以通過任何驗證了，甚至後面加';drop table student'就可怕了

6. 資料庫優化

• 查詢、定位慢查詢

  1. 在專案自驗專案轉測試之前，在啟動mysql資料庫時開啟慢查詢，並且把執行慢的語句寫到日誌中，在執行一定時間後，通過檢視日誌找到慢查詢語句；

  2. 使用explain和慢查詢語句，來詳細分析語句的問題；

• 建立索引：建立合適的索引，我們就可以在索引中查詢，查詢到以後直接找對應的記錄；

  1. 分類：

     • 普通索引：執行重複的值出現；
     • 唯一索引：除了不能又重複的記錄外，其他和普通索引一樣（使用者名稱、使用者身份證、email等）；
     • 主鍵索引：隨著主鍵而建立的，也就是把某個列設為主鍵的時候，資料庫就會給該列建立索引，這就是主鍵索引，唯一且沒有null值；
     • 全文索引：用來對錶中的文字（char，varchar）進行索引，MyISAM支援全文索引，而innodb不支援；

  2. 弊端：

     • 佔有磁碟空間；
     • 對dml（插入修改刪除）操作有影響，變慢；

  3. 適用場景：

     • 肯定在where條件經常使用的場景，如果不做查詢就沒有意義了；
     • 該欄位的內容不是唯一的幾個值（sex）；
     • 欄位內容不是頻繁變化的；

  4. 注意點：

     • 對於建立的多列索引（複合索引），不是使用第一部分就不會使用索引；

       alter table dept add index my_ind(dname,loc);
       explain select * from dept where dname='aaa' -- 會使用索引
       explain select * from dept loc dname='aaa' -- 不會使用索引
       

     • 對於使用like的查詢，查詢如果是’%aaa’不會使用索引而’aaa%'會使用到索引；

       explain select * from dept where dname like '%aaa' -- 不能使用索引
       explain select * from dept where dname like 'aaa%' -- 能使用索引
       

     • 如果條件中有or，有條件沒有使用索引，即使其他條件有使用索引也不會被使用，也就是說，要求條件中的欄位要使用索引，就必須同時擁有，單獨使用時也能使用索引；

     • 如果列舉型別是字串，則一定要在條件中將資料使用引號引用起來，否則不能使用索引；

     • 如果mysql估計使用全表掃描要比使用索引快時，則不用索引；

• 分表：當一張表的資料比較多或者一張表的某些欄位的值比較多並且很少使用時，採用水平分表和垂直分表來分化；

  1. 水平分表：也就是按行分表，因為當表資料達到百萬級別，查詢效率會很低，容易造成表鎖，甚至堆積很多連線直接掛掉，而水平分表能夠很大程度減少這些壓力；
  2. 垂直分表：也就是按列分表，當一張表中某個欄位值非常多（如長文字、二進位制等），而且只有在很少的情況下會查詢，這時候就可以把欄位多的單獨放在一個表，通過外來鍵關聯起來；
  3. 水平分表策略：
     • 按時間分表：這種分表方式有一定的侷限性，當資料有較強的時效性，如微博傳送記錄，微信訊息記錄等，這種資料很少有使用者查詢幾個月前的資料，就可以按月分表；
     • 按區間範圍分表：一般在有嚴格的自增id需求上使用，如按照user_id水平分表時，可以按照table_1 user_id 1~100w; table_2 user_id 101w~200w
     • hash分表：通過一個原始目標的id或者名稱通過一定的hash演算法計算出資料儲存表的表名，然後訪問相應的表；

• 讀寫分離：當一臺伺服器不能滿足需求時，採用讀寫分離的方式進行叢集；

  1. 詳細原因：一臺資料庫支援的最大併發連線數是有限的，如果使用者併發訪問太多，一臺伺服器滿足不了要求，就可以叢集處理，而mysql的叢集處理技術最常用的就是讀寫分離；
  2. 主從同步：資料庫最終會把資料持久化到磁碟，如果叢集必須確保每個資料庫伺服器的資料是一致的，能改變資料庫的操作都往主資料庫上寫，而其他的資料庫從主資料庫上同步資料；
  3. 讀寫分離：使用負載均衡來實現寫的操作都往主資料庫去，而讀的操作都往從伺服器去；
  4. 負載均衡：負載均衡（Load Balance），意思是將負載（工作任務，訪問請求）進行平衡、分攤到多個操作單元（伺服器，元件）上進行執行。是解決高效能，單點故障（高可用），擴充套件性（水平伸縮）的終極解決方案。

• 快取：使用redis來進行快取；

  1. 定義：在持久層（dao）和資料庫（db）之間新增一個快取層，如果使用者訪問的資料已經快取起來時，在使用者訪問時直接從快取中獲取，不用訪問資料庫，而快取是在操作記憶體級，訪問速度快；
  2. 作用：減少資料庫伺服器壓力，減少訪問時間；
  3. java常用的快取有：
     • hibernate的二級快取，但該快取不能完成分散式快取，只能儲存在本機上；
     • redis（mernchche等）可以作為中央快取，也就是對快取的資料進行集中處理，在每主機和資料庫之間多一層快取層；

7. 儲存引擎

• 在開發中，我們經常用的儲存引擎有MyISAM、INNODB和Memory；

• MyISAM儲存引擎：

  ​ 如果表對事務要求不高，同時是以查詢和新增為主的，我們考慮使用MyISAM儲存引擎，比如bbs中的發帖表和回覆表等；

• INNODB儲存引擎：

  ​ 對事務要求高，儲存的資料都說重要資料，建議使用INNODB，比如訂單表和賬號表等；

• Memory儲存引擎：

  ​ 資料變化頻繁，不需要入庫，同時又頻繁的查詢和修改，考慮使用Memory，速度極快；

• MyISAM和INNODB的區別：

  • 事務安全：MyISAM不支援事務而innodb支援；
  • 查詢和新增速度：因為MyISAM不用支援事務就不用考慮同步鎖，所以查詢和新增的速度快；
  • 全文索引：MyISAM支援全文索引，而innodb不支援；
  • 鎖機制：MyISAM只支援表鎖，而innodb支援行鎖（事務）；
  • 外來鍵：MyISAM不支援外來鍵，innodb支援外來鍵（通常不設定外來鍵，而是在程式中保證資料的一致）；

8. redis

• 概念：redis 是一個key-value的nosql資料庫，先存到記憶體中，會根據一定的策略持久化到磁碟，即使斷電也不會丟失資料，支援的資料型別比較多；

• 用途：主要用來做快取資料庫的資料和web叢集時當作中央快取存放session；

• redis物件儲存方式

  1. json字串：需要把物件轉換為json字串，當做字串處理，直接使用get，set方法進行處理；

     • 優點：設定和獲取比較簡單；
     • 缺點：沒有提供專門的方法，需要把物件轉換為json（jsonlib）；

  2. 位元組：需要把物件序列化為位元組進行處理；

• redis資料淘汰機制

  1. 原因：記憶體大小有限，需要保留有效的資料；
  2. 方法：當redis記憶體快耗盡時，redis會啟動記憶體淘汰機制，將部分key清掉以騰出記憶體，redis提供6中資料淘汰策略，可在redis.conf中配置，具體如下：
     • noeviction：禁止驅逐資料。預設配置都是這個。當記憶體使用達到閥值的時候，所有引起申請記憶體的命令都會報錯。
     • volatile-lru：從設定了過期時間的資料集中挑選最近最少使用的資料淘汰。
     • volatile-ttl：從已設定了過期時間的資料集中挑選即將要過期的資料淘汰。
     • volatile-random：從已設定了過期時間的資料集中任意選擇資料淘汰。
     • allkeys-lru：從資料集中挑選最近最少使用的資料淘汰。
     • allkeys-random：從資料集中任意選擇資料淘汰。

• java訪問redis

  1. 使用jedis.java客戶端來訪問redis伺服器，有點類似通過jdbc訪問mysql一樣；
  2. 如果是spring進行整合時，可以使用spring data來訪問redis，spring data只是對jedis進行了二次封裝，就跟jdbc和jdbcTemplate一樣；

• redis的叢集

  當一臺資料無法滿足要求，可以使用redis叢集來處理，類似於mysql的讀寫