1. JDBC連接的過程

1. 加載驅(qū)動
   1. Class.forName("com.mysql.jdbc.Driver");
2. 通過協(xié)議,子協(xié)議,ip,端口,URL創(chuàng)建連接,獲取到連接對象
   1. Connection connection = DriverManager.getConnection("jdbc:mysql://xxx.xxx.xxx.xxx/jdbc?xxxxxx&xxxxx")
3. 編寫sql語句
4. 傳入sql,獲得statement對象
   1. PreparedStatement ps = connection.prepareStatement
   2. 如果是加參數(shù)的sql(?作為占位符),需要填充參數(shù)
   3. ps.setObject(1, obj)
5. 執(zhí)行statement中的sql,獲得結(jié)果集
   1. ResultSet resultSet=statement.executeQuery();
   2. ResultSet resultSet=statement.executeUpdate();
   3. ResultSet resultSet=statement.execute();
6. 處理結(jié)果集
   1. rs.next() 類似于迭代器

2. 連接池

? 在程序運行中,如果每次訪問數(shù)據(jù)庫都需要建立連接,使用完畢之后在關(guān)閉連接.網(wǎng)絡(luò)開銷和對系統(tǒng)資源的占用都非常大.那么依據(jù)資源池的思想,將連接使用完之后不進行關(guān)閉,而是放入連接池中,下一次進行重用.這就誕生了連接池

(1). 連接池的工作原理

1. 連接池的建立
   1. 系統(tǒng)初始化時會根據(jù)系統(tǒng)配置建立連接池,并初始化幾個連接對象.
2. 連接池的管理
   1. 請求連接
      1. 當(dāng)線程請求數(shù)據(jù)庫連接時,如果有空閑連接,分配給線程使用
      2. 如果沒有空閑連接,那么判斷當(dāng)前連接數(shù)量是否超出連接池上限
      3. 如果沒有,那么創(chuàng)建新的連接
      4. 如果已經(jīng)達到上限,任務(wù)等待
      5. 任務(wù)等待時間超出最大等待時間時,拋出異常
   2. 釋放連接
      1. 當(dāng)線程釋放數(shù)據(jù)庫連接時,會判斷當(dāng)前連接的引用次數(shù)是否超過規(guī)定值
      2. 如果超過,就從連接池中刪除這個連接,否則進行白柳
3. 連接池的關(guān)閉
   1. 當(dāng)應(yīng)用程序退出時,關(guān)閉連接池中的所有連接,釋放相關(guān)資源

(2). 連接池主要參數(shù)

• 最小連接數(shù):初始化時就要達到的連接數(shù)量
• 最大連接數(shù):字面意思
• 最大空閑時間:連接不被使用的最大時間,為了避免過多占用系統(tǒng)內(nèi)存
• 獲取連接超時時間
• 超時重連次數(shù)

3. 數(shù)據(jù)庫范式

? 如何理解關(guān)系型數(shù)據(jù)庫的常見設(shè)計范式？

? 主碼是指主鍵的集合(一張表被多個鍵唯一確定)

? 主碼中的每一個屬性都是主屬性.

(1). 第一范式(1NF)

? 屬性域不可分割,要具有原子性.

(2). 第二范式(2NF)

? 非主屬性必須完全依賴于主碼(完全依賴是指不能被其一部分決定).

(3). 第三范式(3NF)

? 非主屬不能依賴于其他非主屬性.

(4). 巴斯范式(BCNF)

? 所有屬性(這里主要針對的是主屬性)不能對主屬性有傳遞依賴.(不能傳遞依賴,也就是主屬性之間不能相互依賴)

4. InnoDB和MyISAM區(qū)別

(1). 區(qū)別

mysql中innodb和myisam對比及索引原理區(qū)別

? 因為輔助索引存儲指針,多查詢的表使用MyISAM效率會高很多.

? InnoDB的表鎖會讓表的插入刪除更新操作的效率高于MyISAM

(2). InnoDB存儲引擎的4大特性

• 插入緩沖
  • 對于不唯一的輔助索引,會先在緩沖中查找有沒有該索引值,如果有直接插入到該位置(相同索引值在索引表中的位置一樣),如果沒有先添加到插入緩沖中,在按一定頻率合并到緩沖池中,然后在持久化在磁盤中
• 二次寫
  • 為了防止將一個頁寫入磁盤時,中途發(fā)生宕機,磁盤上的數(shù)據(jù)已經(jīng)臟了,這種情況是不能使用重做日志進行恢復(fù)的
  • 重做日志之能將數(shù)據(jù)庫從一個狀態(tài)更改為另一個狀態(tài),不能恢復(fù)這種隨機性的數(shù)據(jù)錯亂
  • 在內(nèi)存中有一個doublewrite buffer大小為2MB,磁盤中也會有一片連續(xù)的128個頁,2MB
  • 先將緩沖中的數(shù)據(jù)寫入這2MB磁盤中,然后在寫入原本應(yīng)該寫入的位置
  • 這樣保證了磁盤中要么保存了原先的數(shù)據(jù),要么保存了新的數(shù)據(jù),不會出現(xiàn)中間狀態(tài)
• 自適應(yīng)哈希索引
  • 如果某個二級索引被頻繁訪問(連續(xù)訪問模式必須一樣),成為熱數(shù)據(jù).這個二級索引會被自動生成到hash索引中去
  • 根據(jù)哈希算法計算索引值得出的值作為下標(biāo),在數(shù)組中存儲指向聚集索引的頁面地址的指針
• 預(yù)讀
  • InnoDB可能會再一次讀取中順便讀取多量數(shù)據(jù),減少磁盤訪問的次數(shù)
  • 線性預(yù)讀:根據(jù)當(dāng)前發(fā)生順序讀取的次數(shù)判斷是否在本次磁盤訪問中將下一個區(qū)中的數(shù)據(jù)順便讀入到緩沖池
  • 隨機預(yù)讀:隨機的將當(dāng)前訪問的頁中的一些其他數(shù)據(jù)順便讀入緩沖池,已經(jīng)棄用

5. 堆組織表,索引組織表和索引聚簇表

(1). 堆組織表

? 物理存放順序是隨機的,索引中記錄了數(shù)據(jù)所在位置的rowid,查找的時候先找索引,然后根據(jù)rowid找到行數(shù)據(jù)

? 數(shù)據(jù)和索引是分離的.

(2). 索引組織表

? 行數(shù)據(jù)和索引一起存放,找到索引就找到了行數(shù)據(jù).

(3). 索引聚簇表

? 一組表如果有共同的列,那么存儲在相同的數(shù)據(jù)庫塊中.(可以理解為連接操作)

6. mysql優(yōu)化

• 開啟查詢緩存，優(yōu)化查詢
• explain你的select查詢，這可以幫你分析你的查詢語句或是表結(jié)構(gòu)的性能瓶頸。EXPLAIN 的查詢結(jié)果還會告訴你你的索引主鍵被如何利用的，你的數(shù)據(jù)表是如何被搜索和排序的
• 當(dāng)只要一行數(shù)據(jù)時使用limit 1，MySQL數(shù)據(jù)庫引擎會在找到一條數(shù)據(jù)后停止搜索，而不是繼續(xù)往后查少下一條符合記錄的數(shù)據(jù)
• 為搜索字段建索引
• 使用 ENUM 而不是 VARCHAR。如果你有一個字段，比如“性別”，“國家”，“民族”，“狀態(tài)”或“部門”，你知道這些字段的取值是有限而且固定的，那么，你應(yīng)該使用 ENUM 而不是VARCHAR
• Prepared Statements
  • Prepared Statements很像存儲過程，是一種運行在后臺的SQL語句集合，我們可以從使用 prepared statements 獲得很多好處，無論是性能問題還是安全問題。
  • Prepared Statements 可以檢查一些你綁定好的變量，這樣可以保護你的程序不會受到“SQL注入式”攻擊
• 垂直分表
• 選擇正確的存儲引擎

7. B+樹

? InnoDB存儲引擎中的索引是使用B+樹來存儲的.

(1). 特點

• 非葉子節(jié)點中指針域(子節(jié)點)和索引域(索引值)的數(shù)量一致,B樹的指針域要加一
• 指針指向的子樹中的所有數(shù)據(jù)都要小于此指針對應(yīng)的索引域的值
• 所有數(shù)據(jù)都存放在葉節(jié)點中,非葉子節(jié)點中保存的索引值只是其中數(shù)據(jù)的復(fù)制
• 所有葉子節(jié)點上的數(shù)據(jù)都有兩個指針,分別指向上一個和下一個節(jié)點(葉子節(jié)點雙向鏈表連接)

(2). 較B樹的優(yōu)點

• 由于所有數(shù)據(jù)都存放在葉子節(jié)點,樹更加矮胖,相同樹高能存放更多數(shù)據(jù)
• 所有數(shù)據(jù)都在一層,查詢效率更高
• 對數(shù)據(jù)的掃描通過雙向鏈表實現(xiàn),不需要中序遍歷整棵樹

8. 添加索引

(1). alter方式

alter table 表名
add 索引類型 索引名
(索引列[,索引列2]);

(2). create方式

create 索引類型 索引名
on 表名 (索引列[,索引列2]);

9. MySQL 5.6對DDL的優(yōu)化

? 在MySQL 5.6之前MySQL本身不增加任何腳本的情況下,進行DDL操作(改變表或者庫的結(jié)構(gòu))時都是建立一張包含新的結(jié)構(gòu)的表,然后將數(shù)據(jù)導(dǎo)入新表.

? MySQL 5.6開始支持Online DDL,其本質(zhì)是在進行結(jié)構(gòu)更改的時候,將此時的insert,update,delete這類操作日志寫入緩存中,當(dāng)DDL完成后,在重做到表上.

10. 事務(wù)

(1). 事務(wù)的特性

• 原子性:事務(wù)中的操作要么全部完成,要么全部失敗
• 一致性:事務(wù)執(zhí)行前后,數(shù)據(jù)庫都要有一致性(事務(wù)中的操作要合法,否則回滾)
• 隔離性:事物之間根據(jù)隔離級別有不同程度的不可見
• 持久性:事務(wù)一旦提交,就永久的改變數(shù)據(jù)庫中的數(shù)據(jù)

(2). 事務(wù)的隔離級別

(3). 事務(wù)中出現(xiàn)的并發(fā)問題

1). 臟讀

? 一個事務(wù)已經(jīng)修改但還未提交的數(shù)據(jù)成為臟數(shù)據(jù).臟讀就是一個事務(wù)讀到了另一個事務(wù)還未提交的事務(wù),當(dāng)另一個事務(wù)回滾時,這個事務(wù)當(dāng)前得到的數(shù)據(jù)與數(shù)據(jù)庫中不一致.

2). 不可重復(fù)讀

? 一個事務(wù)多次讀取表的同時,另一個事務(wù)對數(shù)據(jù)進行了修改,并進行了提交.這時該事務(wù)沒有進行修改數(shù)據(jù)但是讀取的數(shù)據(jù)發(fā)生了改變.

3). 幻讀

? 一個事務(wù)多次讀取表,另一個事務(wù)新增或者刪除了數(shù)據(jù).這時該數(shù)據(jù)讀取到的數(shù)據(jù)數(shù)量發(fā)生了改變.

? 幻讀和不可重復(fù)讀的區(qū)別在于側(cè)重點不同,不可重復(fù)讀側(cè)重數(shù)據(jù)的更新,而幻讀側(cè)重于數(shù)據(jù)的增減.

? 這就決定了,不可重復(fù)讀可以通過行鎖解決,而幻讀需要一些別的措施(InnoDB中使用間隙鎖完成).

11. MySQL主從復(fù)制原理

主服務(wù):binlog(二進制日志,存儲數(shù)據(jù)庫的所有更改操作)線程會記錄下所有改變數(shù)據(jù)庫的語句,放入二進制日志中

從服務(wù):io線程請求主服務(wù)的binlog,寫入relay log(中繼日志,只在這里使用)中

從服務(wù):sql執(zhí)行線程執(zhí)行relay log中的語句

12. 表分區(qū)

(1). 水平分區(qū)

橫向的將某張表截斷成不同的分區(qū)(分離的是行,每一行的數(shù)據(jù)是完整的),有4中分區(qū)方式:

• range:按照給定的取值區(qū)間進行分區(qū)
• hash:按照給定的hash算法進行分區(qū)
• key:使用mysql提供的方法進行hash
• list:離散型的進行選擇分區(qū)

(2). 垂直分區(qū)

? 縱向的將某張表劃分成不同的分區(qū)(將數(shù)據(jù)庫的列進行分離),查詢時需要連接查詢.

13. 關(guān)于MVCC

? MVCC就是多版本并發(fā)控制,在Mysql的InnoDB引擎中就是指在==已提交讀==(READ COMMITTD)和==可重復(fù)讀==(REPEATABLE READ)這兩種隔離級別下的事務(wù)對于SELECT操作會訪問版本鏈中的記錄的過程。

(1). 版本鏈

? 在聚簇索引(主鍵索引)中會有兩個隱藏列.

? 每次當(dāng)有事務(wù)對某條數(shù)據(jù)進行修改的時候都會有一個trx_id來存儲這個事務(wù)的id,然后將老版本地址寫入undo page,并用roll_pointer指向這個版本.

? 通過roll_pointer來找到上個版本,然后通過比對trx_id找到是否是當(dāng)前事務(wù)要查詢的版本,如果不是繼續(xù)在上個版本中找到上上個版本.

(2). 快照讀

? 快照讀是指每個事務(wù)都讀取之前所有版本和當(dāng)前版本的其中之一.

? read view是一個列表來存儲當(dāng)前活躍的讀寫事務(wù),也就是未提交事務(wù),用這個列表來判斷記錄某個版本是否對當(dāng)前版本可見.尋找到數(shù)據(jù)時,查找其當(dāng)前版本對應(yīng)的事務(wù)id,并判斷是否能讀取,如果不能,繼續(xù)查找之前的版本,知道找到一個能讀取的版本為止.

1. 看不到read view創(chuàng)建時刻以后啟動的事務(wù)
2. 看不到read view創(chuàng)建時活躍的事務(wù)

? 讀已提交(不可重復(fù)讀)在每次進行select時創(chuàng)建新的read view,而可重復(fù)讀只在第一次select時創(chuàng)建read view.

14. 什么是索引,其優(yōu)缺點,注意事項

(1). 什么是索引

? 索引是數(shù)據(jù)庫中的一種具有物理存儲的數(shù)據(jù)結(jié)構(gòu).通常使用B樹或者B+樹實現(xiàn).在這個數(shù)據(jù)結(jié)構(gòu)上可以實現(xiàn)數(shù)據(jù)庫維護的高級查找算法.

(2). 索引的作用

? 協(xié)助快速查詢

(3). 索引的優(yōu)缺點

優(yōu)點:

• 提高數(shù)據(jù)的查找速度
• 加快連接速度

缺點:

• 創(chuàng)建和維護索引需要耗費時間
• 索引需要占用物理空間

(4). 建立索引的注意事項

應(yīng)該建立索引的情況:

• 經(jīng)常搜索的列
• 需要連接的列,尤其是外鍵
• 小范圍查找的列
• 經(jīng)常需要排序的列
• 經(jīng)常使用where子句的列

不應(yīng)該創(chuàng)建索引的列:

• 數(shù)據(jù)大量重復(fù)
• 數(shù)據(jù)量非常大(text等類型)
• 對修改性能需求遠大于檢索性能(實時更新數(shù)據(jù),反例是用于統(tǒng)計的數(shù)據(jù))

15. 嵌套事務(wù)

? 嵌套事務(wù)指的是子事務(wù)是父事務(wù)的一部分,進入子事務(wù)之前父事務(wù)會建立保存點.

? 如果子事務(wù)回滾,父事務(wù)會回滾到之前設(shè)置的保存點.

? 如果父事務(wù)回滾,子事務(wù)也會回滾.

? 提交事務(wù)時,子事務(wù)先提交,父事務(wù)再提交.

16. SQL執(zhí)行順序

查詢語句中select, from, where, group by, having, order by的執(zhí)行順序

(1). 總體上的執(zhí)行順序

? from -> where -> group by -> having -> select -> order by

1. from確定數(shù)據(jù)的來源
   1. 從后往前進行連接,所以小表放后面
2. where確定數(shù)據(jù)的篩選條件
   1. orcale從后往前
   2. mysql從前往后
   3. 要點就是盡量先排除盡可能多的數(shù)據(jù)
3. group by先進行分組
4. having對分組進行篩選
   1. 很耗資源,盡量使用where代替
5. select確定要展示的列
   1. 少用*,盡量使用字段名
6. order by確定要展示的順序
   1. 很耗資源