資料庫基礎面試第三彈

来源:https://www.cnblogs.com/beyond-tester/archive/2023/09/10/17692183.html
-Advertisement-
Play Games

1. mysql資料庫四種常見資料庫引擎 1. MyISAM： MyISAM是MySQL最早的資料庫引擎之一。它被設計成處理大量的插入和查詢操作。MyISAM表格的數據存儲在三個文件上：.frm文件存儲表結構，.MYD文件存儲數據，.MYI文件存儲索引。MyISAM表格不支持事務處理和崩潰恢復，因此 ...


1. mysql資料庫四種常見資料庫引擎

1. MyISAM：

MyISAM是MySQL最早的資料庫引擎之一。它被設計成處理大量的插入和查詢操作。MyISAM表格的數據存儲在三個文件上：.frm文件存儲表結構，.MYD文件存儲數據，.MYI文件存儲索引。MyISAM表格不支持事務處理和崩潰恢復，因此在併發寫入操作和故障發生時可能會導致數據不一致。然而，MyISAM引擎非常適合於讀密集型的應用，例如數據倉庫和日誌分析。

2. InnoDB：

innoDB是MySQL的預設資料庫引擎，也是目前最常用的引擎之一。與MyISAM不同，InnoDB支持事務處理、行級鎖定和崩潰恢復。它採用了多版本併發控制（MVCC）來提供高併發性能。InnoDB表格的數據存儲在表空間中，可以跨多個文件組織和管理。由於其支持事務和強大的ACID特性，InnoDB引擎適用於大多數應用場景，尤其是需要事務支持和高併發讀寫的應用。

3. Memory：

Memory（也稱為Heap）是一種基於記憶體的資料庫引擎，它將數據存儲在記憶體中而不是磁碟上。這使得它具有非常快的讀寫速度，但也意味著數據在資料庫重新啟動時會丟失。Memory引擎非常適合於需要快速讀寫和臨時數據存儲的應用，例如緩存表格或會話管理。

4. Archive：

Archive引擎是一種用於存檔數據的特殊引擎。它通過使用壓縮演算法將數據以非常高的壓縮比率存儲在磁碟上，並提供了快速的插入性能。Archive引擎不支持索引，只支持順序訪問。它適用於存儲和查詢歷史數據或用於數據備份的場景。

主要區別

MyISAM和InnoDB是MySQL中最常見的兩種資料庫引擎，它們具有以下區別和特點：

  1. MyISAM：
  • 不支持事務處理和崩潰恢復。在併發寫入操作和故障發生時，可能導致數據不一致。
  • 適合於讀密集型應用，如數據倉庫和日誌分析。
  • 具有較快的插入和查詢速度，因為它採用了簡單的表格鎖定機制。
  • 存儲結構使用三個文件：.frm文件存儲表結構，.MYD文件存儲數據，.MYI文件存儲索引。
  1. InnoDB：
  • 支持事務處理、行級鎖定和崩潰恢復。具備強大的ACID特性。
  • 適合於大多數應用場景，尤其是需要事務支持和高併發讀寫的應用。
  • 採用多版本併發控制（MVCC）來提供高併發性能和高度的數據一致性。
  • 數據存儲在表空間中，可以跨多個文件組織和管理。

總結：

  • MyISAM適合讀密集型應用，而InnoDB適合讀寫併發性要求較高的應用。
  • MyISAM不支持事務處理和崩潰恢復，而InnoDB具備強大的事務支持和崩潰恢復能力。
  • MyISAM使用表鎖定，而InnoDB使用行級鎖定，使得InnoDB能夠提供更好的併發性能和數據一致性。
  • MyISAM的插入和查詢速度較快，而InnoDB的讀寫性能相對較好。
  • MyISAM的存儲結構簡單，而InnoDB的存儲結構更複雜，支持更多高級特性。

2. Delete 和 Drop區別是什麼

Delete和Drop是在資料庫中執行不同操作的兩個關鍵詞，它們的區別如下：

  1. Delete（刪除）：
    Delete用於從資料庫表中刪除行或記錄。它是一個DML（數據操作語言）命令，可以通過指定條件來選擇要刪除的行。Delete語句執行後，被選中的行將從表中永久刪除。

關鍵點：

  • Delete語句只刪除表中的行，而不刪除表本身。
  • Delete語句可以使用WHERE子句來指定刪除的條件。如果沒有指定WHERE子句，將刪除表中的所有行。
  • Delete操作是事務安全的，可以通過回滾操作撤銷刪除操作。
  • Delete操作會觸發表上的觸發器（如果有定義）。
  • Delete操作可能會影響表中的其他行和相關的約束。
  1. Drop（刪除）：
    Drop用於從資料庫中完全刪除資料庫對象，如表、視圖、索引、存儲過程等。它是一個DDL（數據定義語言）命令，可以刪除整個對象及其相關的結構和數據。Drop操作是不可逆的，一旦執行，對象將被永久刪除。

關鍵點：

  • Drop語句可以用於刪除表、視圖、索引、存儲過程等資料庫對象。
  • Drop語句將刪除對象本身以及與該對象相關的約束、索引、觸發器等。
  • Drop操作是不可逆的，一旦執行將無法恢復。
  • Drop操作會導致相關的數據和結構完全丟失。

總結：
Delete是用於刪除表中的行或記錄，是DML命令，可以通過條件選擇要刪除的行，操作是可撤銷的。而Drop是用於完全刪除資料庫對象，是DDL命令，操作是不可撤銷的，刪除後對象及其相關的結構和數據都將丟失。

 

3. 資料庫中的連接，例如內，左，右等連接。

  1. 內連接（Inner Join）：
    內連接根據連接條件從兩個或多個表中獲取匹配的行。它只返回符合連接條件的行，即兩個表中連接列的值相等的行。內連接只返回匹配的行，未匹配的行將被排除。

示例：
假設我們有兩個表：Customers（客戶）和 Orders（訂單），它們通過 CustomerID（客戶ID）列連接。以下是一個內連接的示例，將返回具有匹配 CustomerID 的行：

  
SELECT Customers.CustomerID, Customers.CustomerName, Orders.OrderID
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

應用：
內連接常用於需要從多個表中獲取相關數據的操作，例如在訂單管理系統中，通過內連接可以獲取每個訂單對應的客戶信息。

  1. 左連接（Left Join）：
    左連接返回左表中所有的行，以及與右表匹配的行。如果右表中沒有匹配的行，則返回 NULL 值。

示例：
繼續使用上述 Customers 和 Orders 表，以下是一個左連接的示例，將返回所有客戶以及與之相關聯的訂單（如果有的話）：

SELECT Customers.CustomerID, Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

　　

應用：
左連接常用於需要獲取左表中所有數據以及與之相關聯的右表數據的場景，例如在客戶管理系統中，可以使用左連接獲取所有客戶及其對應的訂單信息，即使某些客戶沒有訂單。

  1. 右連接（Right Join）：
    右連接返回右表中所有的行，以及與左表匹配的行。如果左表中沒有匹配的行，則返回 NULL 值。

示例：
繼續使用上述 Customers 和 Orders 表，以下是一個右連接的示例，將返回所有訂單以及與之相關聯的客戶（如果有的話）：

SELECT Customers.CustomerID, Customers.CustomerName, Orders.OrderID
FROM Customers
RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

　　

應用：
右連接常用於需要獲取右表中所有數據以及與之相關聯的左表數據的場景，例如在訂單管理系統中，可以使用右連接獲取所有訂單及其對應的客戶信息，即使某些訂單沒有關聯的客戶。

  1. 外連接（Full Outer Join）：
    外連接返回左表和右表中的所有行，如果沒有匹配的行，則返回 NULL 值。

示例：
繼續使用上述 Customers 和 Orders 表，以下是一個外連接的示例，將返回所有客戶和訂單，包括匹配和非匹配的行：

SELECT Customers.CustomerID, Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

　　

應用：
外連接常用於需要獲取左表和右表所有數據的場景，即使某些行沒有匹配的數據。它可以用於數據比較和合併操作。

需要註意的是，連接操作不僅限於兩個表，可以連接多個表。連接條件可以是多個列的組合，不僅僅限於相等條件。在實際應用中，根據具體的數據關係和查詢需求，選擇適合的連接類型來獲取所需的數據。

4. 資料庫的三大範式：

  1. 第一範式（1NF）：
    第一範式要求資料庫中的每個列都是原子性的，即每個列都應該包含不可再分的數據，不允許重覆的列或多值的列。

關鍵點：

  • 每個表中的每個列都應該是原子的，不可再分的。
  • 消除重覆數據，避免在同一列中存儲多個值。
  • 每個表應該有一個唯一的主鍵來標識每一行。

應用：
例如，假設有一個包含學生信息的表，每個學生可能有多個電話號碼。為了滿足第一範式，可以將電話號碼拆分為獨立的表，並使用學生ID作為關聯鍵。

  1. 第二範式（2NF）：
    第二範式要求資料庫中的每個非主鍵列都完全依賴於主鍵，即每個非主鍵列都應該與主鍵具有完整依賴關係，而不是部分依賴。

關鍵點：

  • 每個非主鍵列都應該完全依賴於主鍵。
  • 如果存在部分依賴，應該將相關的列拆分到獨立的表中。

應用：
繼續以學生信息表為例，假設主鍵是學生ID和課程ID的組合。如果存在一個非主鍵列是課程名稱，那麼課程名稱應該與學生ID和課程ID一起作為複合主鍵，以滿足第二範式。

  1. 第三範式（3NF）：
    第三範式要求資料庫中的每個非主鍵列都不傳遞依賴於主鍵，即每個非主鍵列都應該直接依賴於主鍵，而不是依賴於其他非主鍵列。

關鍵點：

  • 每個非主鍵列都應該直接依賴於主鍵，而不是依賴於其他非主鍵列。
  • 消除傳遞依賴，避免非主鍵列之間的依賴關係。

應用：
繼續以上述學生信息表為例，假設除了學生ID和課程ID之外，還存在一個非主鍵列是學生姓名。如果學生姓名依賴於課程ID而不是學生ID，那麼應該將學生姓名拆分到與課程ID相關的獨立表中，以滿足第三範式。

5. 產生慢查詢的原因以及優化方法

  1. 索引問題：
  • 原因：查詢沒有使用到合適的索引，導致資料庫需要進行全表掃描或大量的數據頁讀取，從而降低查詢性能。
  • 優化方法：通過分析查詢語句和表結構，確定合適的索引並創建/優化索引。使用EXPLAIN語句來查看查詢執行計劃，確保查詢能夠充分利用索引。
  1. 資料庫統計信息不准確：
  • 原因：資料庫統計信息（如表的行數、索引的唯一值等）不准確，導致優化器無法做出正確的查詢執行計劃。
  • 優化方法：更新資料庫統計信息，可以使用ANALYZE TABLE語句手動重新計算統計信息，或者配置自動統計信息更新。
  1. 複雜查詢邏輯：
  • 原因：查詢語句過於複雜，包含多個嵌套的子查詢、連接操作或函數運算，導致查詢執行時間較長。
  • 優化方法：簡化查詢邏輯，儘量減少不必要的子查詢和連接操作。考慮使用更有效的查詢方式，如JOIN操作替代子查詢。
  1. 資料庫連接或伺服器配置問題：
  • 原因：資料庫連接池配置不合理、資料庫伺服器負載過高或配置不足，導致查詢響應時間延長。
  • 優化方法：優化資料庫連接池配置，增加資料庫伺服器的硬體資源，如CPU、記憶體等。調整資料庫參數和伺服器參數，以提高性能和併發處理能力。
  1. 資料庫設計問題：
  • 原因：資料庫表結構設計不合理，導致查詢需要涉及大量的表連接、冗餘數據或無效數據。
  • 優化方法：重新評估資料庫表結構，進行合理的資料庫設計和範式化。優化數據模型，消除冗餘和無效數據，以提高查詢性能。
  1. 數據量過大：
  • 原因：資料庫中的數據量過大，導致查詢需要處理大量的數據，影響查詢性能。
  • 優化方法：考慮對大表進行分區或分片，以減少單個查詢需要處理的數據量。使用分頁查詢、緩存、非同步處理等技術來優化數據訪問方式。
  1. 鎖競爭和併發問題：
  • 原因：併發查詢導致鎖競爭，或者長時間的事務導致其他查詢等待。
  • 優化方法：優化事務的設計，儘量縮短事務的執行時間。合理設置事務隔離級別，減少鎖競爭。使用行級鎖而不是表級鎖，以提高併發性能。
  1. 硬體和網路問題：
  • 原因：資料庫伺服器硬體配置不足，網路帶寬不足等問題。
  • 優化方法：增加硬體資源，如CPU、記憶體、磁碟等，以提高資料庫伺服器的性能。優化網路連接，確保網路帶寬和延遲滿足需求。

6. 如何防止SQL 註入：

當應用程式未正確過濾用戶輸入時，攻擊者可以通過各種方式註入惡意的SQL代碼。以下是一個簡單的SQL註入示例：

假設有一個登錄表單，用戶需要輸入用戶名和密碼來進行認證。應用程式的代碼可能是這樣的：

username = getRequestParameter("username");
password = getRequestParameter("password");

sql = "SELECT * FROM users WHERE username='" + username + "' AND password='" + password + "'";

在正常情況下，應用程式會將用戶輸入的用戶名和密碼與資料庫中的記錄進行比較以驗證用戶身份。然而，如果未對用戶輸入進行適當的驗證和過濾，攻擊者可以輸入惡意的數據來註入SQL代碼。

例如，攻擊者可以在用戶名欄位輸入以下內容：

' OR '1'='1'; --

修改後的查詢語句將變為：

SELECT * FROM users WHERE username='' OR '1'='1'; --' AND password='password'

由於'1'='1'永遠為真，所以查詢將返回所有用戶的記錄，繞過了密碼驗證。

攻擊者還可以利用註釋符（--）將原始的密碼驗證部分註釋掉，從而達到繞過密碼驗證的目的。

為了防止SQL註入攻擊，可以採取以下措施：

  1. 使用參數化查詢（Prepared Statements）或存儲過程：

    • 參數化查詢：使用預編譯的SQL語句，並將用戶輸入的值作為參數傳遞給查詢。這樣可以確保用戶輸入的數據不會被解釋為SQL代碼的一部分。
    • 存儲過程：將SQL邏輯封裝在存儲過程中，並通過調用存儲過程來執行查詢。存儲過程可以有效防止SQL註入攻擊。
    • username = getRequestParameter("username")
password = getRequestParameter("password")

# 使用參數化查詢
sql = "INSERT INTO users (username, password) VALUES (?, ?)"
params = (username, password)

# 執行查詢
cursor.execute(sql, params)
connection.commit()

  2. 輸入驗證和過濾：

    • 對用戶輸入進行嚴格驗證和過濾，確保輸入數據符合預期的格式和類型。可以使用白名單或正則表達式驗證來過濾特定的字元或模式，防止惡意代碼的註入。

  3. 最小許可權原則：

    • 為資料庫用戶分配最小許可權，限制其對資料庫的操作。確保應用程式連接資料庫的用戶只具有執行必要操作的許可權，避免攻擊者利用註入漏洞對整個資料庫進行操作。

  4. 輸入數據的轉義：

    • 對用戶輸入的特殊字元進行轉義，以確保這些字元被正確地解釋為數據而不是代碼。這可以通過使用資料庫提供的轉義函數或工具來實現。

  5. 安全更新和維護：

    • 及時應用資料庫供應商發佈的安全更新和補丁，以修複已知的安全漏洞。
    • 定期審查和更新應用程式代碼，以及相關的資料庫查詢語句，確保全全性和可靠性。

  6. 日誌記錄和監控：

    • 啟用詳細的日誌記錄機制，記錄所有資料庫操作和錯誤信息。監控和分析日誌，及時檢測和響應潛在的SQL註入攻擊。

您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 創建Anaconda虛擬Python環境的方法
    本文介紹在Anaconda環境下，創建、使用與刪除Python虛擬環境的方法。 在Python的使用過程中，我們常常由於不同Python版本以及不同第三方庫版本的支持情況與相互之間的衝突情況，而需要創建不同的Python虛擬環境；在Anaconda的幫助下，這一步驟就變得十分方便。 首先，我們需要打 ...
  • LeetCode297：hard級別中最簡單的存在，java版，用時擊敗98%，記憶體擊敗百分之九十九
    LeetCode的hard題都很難嗎？不一定，297就非常簡單，隨本文一起，用最基礎的知識寫代碼，執行用時能擊敗98.46%，與此同時，記憶體消耗擊敗99.73% ...
  • 介面使用的最佳時機
    淺聊一下SpringMVC的核心組件以及通過源碼瞭解其執行流程 MVC作為WEB項目開發的核心環節，正如三個單詞的分解那樣，Controller（控制器）將View（視圖、用戶客戶端）與Model（javaBean:封裝數據）分開構成了MVC，今天我們淺聊一下SpringMVC的相關組件以及通過源碼... ...
  • 用OLED屏幕播放視頻(1): 項目介紹
    下麵的系列文章記錄瞭如何使用一塊linux開發扳和一塊OLED屏幕實現視頻的播放: 項目介紹 為OLED屏幕開發I2C驅動 使用cuda編程加速視頻處理 這篇文章主要對項目的實現做整體的介紹, 包括硬體的需求, 最終實現的視頻播放效果, 以及軟體的實現思路. 1). 硬體需求 beaglebone ...
  • 探索電腦的I/O控制方式：瞭解DMA控制器的作用與優勢
    對於有科班背景的讀者，可以跳過本系列文章。這些文章的主要目的是通過簡單易懂的彙總，幫助非科班出身的讀者理解底層知識，進一步瞭解為什麼在面試中會涉及這些底層問題。否則，某些概念將始終無法理解。這些電腦基礎文章將為你打通知識的任督二脈，祝你在編程領域中取得成功！ ...
  • 用OLED屏幕播放視頻(2): 為OLED屏幕開發I2C驅動
    下麵的系列文章記錄瞭如何使用一塊linux開發扳和一塊OLED屏幕實現視頻的播放: 項目介紹 為OLED屏幕開發I2C驅動 使用cuda編程加速視頻處理 這是此系列文章的第2篇, 主要總結和記錄一個I2C從設備的驅動, 在linux內核中如何實現, 如何給用戶態的程式暴露合適的介面, 讓用戶態有機會 ...
  • 讀高性能MySQL（第4版）筆記05_優化伺服器設置
    1. 除非遇到異常情況，否則不需要調整配置 1.1. 不要“調優”伺服器，不要使用比率、公式或“調優腳本”作為設置配置變數的基礎 1.1.1. 在互聯網上搜索配置建議並不總是一個好主意，你會在博客、論壇等找到很多糟糕的建議 1.1.2. 很難判斷誰是真正的專家 1.1.3. 不要相信流行的記憶體消耗公 ...
  • Flink 架構學習總結
    Flink是一個分散式系統，要求有效地分配和管理計算資源以執行流式應用程式。它集成了所有常見的集群資源管理器，如Hadoop YARN和Kubernetes，但也可以設置為作為standalone甚至庫運行。 本節概述了Flink的體繫結構，並描述了其主要組件如何交互以執行應用程式以及從故障中恢復。 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類