PHP面試之三：MySQL資料庫

104828720發表於2019-02-16
PHP面試MySql資料庫

基礎考點

  • MySQL 資料型別

整型：tinyint、smallint、mediumint、int、bigint
小數型：float、double、decimal
字串型：varchar、char、text、blob
時間日期型：time、date、datetime、timestamp
列舉型別：enum

在定義整型資料型別時，經常看到 int(4) 括號裡指定的是顯示長度，不限制值合法範圍，這個顯示長度要配合zerofill這個屬性才有意義。

1、 VARCHAR 比 CHAR 更節約空間
2、 CHAR 比 VARCHAR 儲存效率更好
3、 VARCHAR 與 CHAR 的長度，如果儲存內容超出指定長度，會被截斷
4、 儲存經常改變的資料，CHAR 不容易產生碎片

儘量避免使用 BLOB/TEXT 型別，導致嚴重的效能開銷

1、 有時可以使用列舉型別代替字串型別
2、 列舉型別內部儲存成整型（節省空間）
3、 避免使用數字作為 enum列舉 的常量，易混亂

1、 儘量使用 TIMESTAMP 來儲存時間日期資料
2、 如果需要儲存微秒， 可以使用 BIGINT 型別

  • MySQL 基礎操作

連線與關閉：mysql -u -p -h -P

  • MySQL 儲存引擎（資料表）

MyISAM與InnoDB的區別

1、 InnoDB支援事務，MyISAM不支援事務
2、 InnoDB支援行級鎖，MyISAM支援表鎖
3、 InnoDB資料儲存在共享表空間，MyISAM資料儲存在MyD檔案和MyI檔案
4、 InnoDB支援奔潰後的安全恢復，MyISAM不支援
5、 InnoDB對主鍵查詢的效能高於其他儲存引擎
6、 MyISAM擁有全文索引、壓縮、空間函式

  • MySQL 鎖機制

由於併發操作的產生，所以需要使用鎖進行併發控制
鎖分為2種：共享鎖和排他鎖，就是讀鎖和寫鎖

共享鎖（讀鎖），不堵塞，多個使用者可以同時讀同一資源，互不干擾

排他鎖（寫鎖），一個寫鎖會阻塞其他讀鎖和寫鎖，這樣只允許一個人進行寫入操作，防止其他人進行讀取或者寫入

注意：InnoDB支援行級鎖，MyISAM支援表鎖

  • MySQL 事務處理

注意：使用事務的前提是 表必須是InnoDB引擎

安全性考點

  • SQL隱碼攻擊

使用 預處理 來防止SQL隱碼攻擊

  • 特殊字元轉義

寫入資料庫的資料必須經過特殊字元轉義操作，此操作在應用層進行

  • 錯誤記錄

當發生查詢錯誤時，不能把錯誤資訊返回給使用者，把錯誤記錄到日誌

索引考察點

  • 使用索引的優缺點

優點：
1、減少伺服器掃描的資料量
2、避免排序和臨時表
3、隨機I/O變順序I/O
4、提高查詢速度

缺點：
1、降低寫的速度
2、佔用磁碟

  • 索引使用場景

非常小的表（1~1000） 不使用索引
中型（1000~100W）   使用索引
大型（100W~1000W）  使用索引
超大（1000W以上）   索引 + 分割槽技術

  • 索引型別

1、普通索引
2、唯一索引 （在普通索引的基礎上，加上唯一約束）
3、主鍵索引 （在唯一索引的基礎上，加上不能為Null）
4、組合索引
5、全文索引 （少用，只適合英文）
6、外來鍵索引 （少用，使用業務邏輯進行資料關聯）

  • 索引建立原則

1、最適合索引的列是出現在where子句中的列，或連線子句中的列，而不是出現在SELECT關鍵字後的列
2、索引列基數越大，效果越好
3、對字串進行索引，應該制定一個字首長度，可以節省大量索引空間
4、根據情況建立複合索引，複合索引能提高查詢效率
5、避免建立過多的索引
6、主鍵儘量選擇較短的資料型別

  • 索引注意事項

1、複合索引遵循字首原則（重要，按順序）
2、like查詢，%不能在前面（在前面索引會失效，如果要在前面請使用第三方全文索引）
3、列is Null也是可以使用索引
4、如果OR條件想用到索引，必須or前後的欄位都需要是索引欄位
5、列是字串型別時，查詢時一定要使用引號（單或雙），索引才會生效

SQL語句關聯考察點

  • 關聯操作

1、關聯更新
2、關聯查詢

  • 連線方式

1、交叉連線 Cross join (無條件)
    
    select * from A CROSS JOIN B CROSS JOIN C;

2、內連線 INNER JOIN (有條件的交叉連線) 簡寫 JOIN

    select * from A INNER JOIN B INNER JOIN C ON A.id = B.id;
    select * from A,B where A.id = B.id;
    select * from A T1 INNER JOIN A T2 ON T1.id = T2.id;
    
3、外連線
    
    左外連線 LEFT JOIN
    右外連線 RIGHT JOIN
    
4、聯合連線 (UNION重複的合併，UNION ALL重複的不合並)

    SELECT * FROM position UNION SELECT * FROM team; 
    SELECT * FROM position UNION ALL SELECT * FROM team; 
    **UNION ALL效能高於UNION**
    
5、全連線（MySQL不支援）

MySQL高擴充套件與高可用

  • 分庫

  • 分表（水平與垂直）

  • 讀寫分離

  • 主從複製

  • 負載均衡

相關文章