[別被脫庫系列]1 資料庫的初戀

  • 2020 年 3 月 16 日
  • 筆記

目錄

此資料庫系列，是學習工作中的總結，具體章節系列如下圖所示。如果您需要掃盲資料庫，突擊資料庫的面試，那就盤他，盤他！所謂初戀，初次見面，下凡資料庫基礎。請多多關照！

童鞋別被脫褲系列

1 了解sql—-長生不老，異常穩定

查看近幾年的TIOBE發現了，一直在前十,可見是個老且管用的東西。

來自網路

了解幾個術語：

  • DDL(Data Definition Language)，定義資料庫對象。

  • DML:(DATA Manipulation Language)，用來操作和資料庫相關記錄。

  • DCL:(Data Cnotrol Language)定義訪問許可權和安全級別。

  • DQL(Data Query Language)。查詢記錄。

sql大小寫規範

  • 表名、表別名、欄位名、欄位別名等可以小寫

  • SQL保留字，函數名，綁定變數大寫

SELECT name,age FROM student WHERE id="1";

2 為啥要存儲數據

我們大部分的系統都會考慮到數據的存儲，那麼如何更有效地保存好數據，做好數據備份。當我們擁有了數據，可以進行數據挖掘，大數據分析，輿情預測，自然語言處理等等一系列的操作，可見存儲數據是多麼的重要。這一篇文章對資料庫基礎知識的掃盲，下一節將是數據存儲，數據備份等。另外我需要知道硬碟的處理速度比cpu,記憶體，網卡都會慢。

3 DBMS是什麼

  • DB、DBS和DBMS區別
全稱 功能
DBMS DataBase Management System 可對多個資料庫進行管理
DB DataBase 存儲樹的集合，理解為多個數據表
DBS DataBase system 老大哥。包含了資料庫管理系統和資料庫管理人員DBA

4 當前主流的DBMS有哪些

5 sql與Nosql

Nosql的timestamp：

1970：Nosql=we have no sql

1980：Nosql=know sql

2000:Nosql=No SQL

2005:Nosql=not only sql

2013:Nosql=No,SQL

鍵值資料庫

通過key-value方式存儲，key為唯一表示，優點，查詢快，缺點是無法像關係型資料庫一樣使用條件過濾，這樣可能導致遍歷所有的鍵，消耗大量的計算。所以經常用來作為快取，比如redis。

文檔資料庫

管理文檔，一個文檔相當於一條記錄，MongoDB。

搜索引擎

雖然關係型資料庫常常通過索引的方式提高檢索效率(不一定)，但是對於全文檢索卻比較低。搜索引擎的優勢比如Elasticsearch、Splunk和Solr採用全文搜索，核心原理為倒排索引

列式資料庫

相對於行式資料庫，將數據按照列存儲，這樣可以大量降低系統的IO(因為相鄰的數據類型一樣，方便壓縮，自然就會降低IO)，適合分散式文件系統，比如

圖資料庫

典型的就是網路中的人與人的關係，節點和邊關係。

6 Oracle中的sql如何執行的

查詢執行流程

  • 語法檢查：檢查SQL拼寫是否正確。
  • 語義檢查：檢查SQL訪問對象是否存在。
  • 許可權檢查：檢查用戶是否有訪問許可權。
  • 共享池檢查：包含了庫快取、數據字典緩衝區等。主要用來緩衝執行計劃或者表、視圖等對象。
  • 優化器：進行硬解析，決定創建解析樹和生成執行計劃應該怎麼做
  • 執行器：有了優化器，那麼在執行器思考如何被執行

7 mysql中sql如何執行的

查詢執行流程

  • 查詢快取

首先注意，mysql8.0之後已經放棄了這個功能(因為如果數據更新，快取會情況，如果為動態數據經常更新，這樣反而增加SQL查詢時間)。快取通常的理解是一個中間層，如果在中間層存在查詢語句就直接返回，如果沒有則給解析器處理。

  • 解析層

主要進行語法分析和語義分析。

  • 優化器

確定SQL語句的執行路徑。是根據全表檢索還是根據索引。

  • 執行器

進行許可權檢查。

那麼mysql和oracle兩者執行情況有啥不一樣呢

MySql具有多種存儲引擎且可以自定義存儲引擎，那麼有哪些存儲引擎，優點缺點是啥？

描述
InnoDB Mysql5.5以後默認存儲引擎，支援事務，行級鎖，外鍵約束
MyISAM Mysql5.5以前為默認存儲引擎，不支援事務和外鍵，最大特點速度快，佔資源少
Memory存儲引擎 使用系統記憶體為存儲介質，更快的響應速度。
NDB存儲引擎 用於Mysql Cluster分散式集群環境
Archive存儲引擎 壓縮機制的特點便於文件的歸檔，常用來做倉庫

8 如何查看一條sql的資源使用情況

  • prifiling是否開啟

mysql> select @@profiling;如果為0代表關閉，設置為1表示打開。

  • 執行sql查詢語句

mysql> select *from student;

  • 查看當前所有profiles

show profiles;查看當前會話的profiles

  • 查看執行時間show profile

9 DDL

  • 創建資料庫

創建公眾號原創作者資料庫

CREATE DATABASE WeChat_Official_Account_Author;

  • 創建表結構(注意語句最後；結束，最後一個欄位定義結束沒有逗號)

創建表 作者名(author_id,author_name且id為遞增)

CREATE TABLE authors_name(author_id int(15) NOT NULL AUTO_INCREMENT)，

author_name varchar(255) NOT NULL);

  • 添加欄位

ALTER TABLE authors_name ADD(age int(12));

  • 修改欄位名

ALTER TABLE authors_name RENAME_COLUME age to author_age;

  • 刪除欄位

ALTER TABLE author_names DROP COLUME author_age;

10 資料庫的常見約束

不成規矩，不成方圓

  • 主鍵約束

唯一標識一條記錄，不重複且不能為空(UNIQUE+NOT NULL)。主鍵可以使是一個欄位或者多個欄位的組合，一個數據表主鍵只能有一個

  • 外鍵約束

外鍵確保表與表之間引用的完整性。外鍵可以重複也可以為空。

  • 唯一性約束

欄位在表中可以使唯一的。

  • NOT NULL約束

表明欄位不應為空，必須有取值。

  • CHECK約束

檢查特定欄位取值範圍的有效性

11 常見查詢語句

  • 查詢姓名列

查詢單列

SELECT author_name FROM authors_name;

查詢所有列

SELECT * FROM authors_name;

  • 去除重複行

SELECT DISTICT age FROM authors_name;

  • 查詢排序

ASC 遞增排序，DESC遞減排序

SELECT author_name FROM authors_names ORDER BY age DESC;

  • 約束返回結果的個數

返回5條數據 LIMIT需要放在最後

SELECT author_name FROM authors_names ORDER BY age DESC LIMIT 5;

  • SELECT語句中關鍵字順序

關鍵字的順序：SELECT…..FROM…WHERE…GROUP BY

  • SELECT語句的執行順序

FROM>WHERE>GROUP BY HAVING>SELECT

  • 那麼一句sql的執行原理是什麼？請看下圖。

12 sql運算符

  • 比較運算符(不同的DBMS支援的運算符可能不同)

比較運算符

  • 邏輯運算符

邏輯運算符

當WHERE字句中同時出現OR和AND的時候，AND執行優先順序會更高。一般來說()優先順序最高，其次是AND,然後是OR。

  • 通配符過濾

通配符是對文本類型進行模糊哈訊，但是通常是全表掃描，所以效率很低。只有當LIKE後面沒有通配符，並對欄位進行索引的時候不會進行全表掃描。匹配一部分特殊字元。"LIKE"操作符。

  • 通配符匹配之任意字元串出現的任意次數(%)

  • 通配符匹配之耽擱字元(_)

13 常見sql函數

提供函數，類似介面，更方便快速的得出想要的結果。

描述 例子
ABS() 取絕對值 SELECT ABS(-5)—5
MOD() 取余 SELECT MOD(101,3)—2
ROUND() 四捨五入為指定的小數位數，如果兩個參數，分別為欄位名稱和小數位數 SELECT ROUND(38.29,1)–38.3
LENGTH() 計算欄位長度。一個漢字三個字元。 SELECT LENGTH(‘小藍’)–6
UPPER() 字元轉大寫 SELECT LOWER(‘qwe’)–QWE
LOWER() 字元轉小寫 SELECT LOWER(‘QWE’)–qwe
REPLACE() 替換函數 SELECT REPLACE(‘QWE123D’,’QWE’,789)–789123D
SUBSTRING() 截取字元串 SELECT SUBSTRING(‘QWE123’,1,3)–
CHAR_LENGTH() 電腦欄位的長度，漢字，數字都算一個字元 SELECT CHAR_LENGTH(‘小藍’)–2
CONCAT() 連接字元串 SELECT CONCAT(‘XIAOLAN’,789)—XIAOLAN789
DATA() 返回時間的日期 SELECT DATA(‘2020-03-13 11:30:20’)–2020-03-13
YEAR()/MONTH()/DAY() 返回時間的年份/月份/天數 SELECT YEAR(NOW())–2020
HOUR() 返回時間的小時 SELECT hour(’12:13:14′)–12
MINUTE() 返回時間的分鐘 SELECT MINUTE(’12:13:14′)–13
SECOND() 返回時間的秒部 SELECT SECOND(’12:13:14′)–14
CURRENT_DATE() 系統當前日期 SELECT CURRENT_DATE(‘2020-03-13 11:30:20’)–2020-03-13
CURRENT_TIME() 系統當前時間，沒有具體日期 SELECT CURRENT_TIME(‘2020-03-13 11:30:20’)–11:30:20
CURRENT_TIMESTAMP() 日期+時間 SELECT CURRENT_TIMESTAMP–2020-03-13 11:30:20

14 聚集函數

描述 例子
COUNT() 總行數,不管某個欄位是否為NULL SELECT COUNT(*) FROM authors_name WHERE age>25
MAX() 最大值 SELECT MAX(Age) FROM authors_name
MIN() 最小值 SELECT MIN(Age) FROM authors_name
SUM() 求和 SELECT SUM(Age) FROM authors_name
AVG() 平均值 SELECT AVG(Age) FROM authors_name
  • 數據分組

使用GROUP BY字句進行數據分組。

  • HAVING過濾分組和WHERE的區別

WHERE 是用於數據行，而 HAVING 則作用於分組。如果分組完以後需要排序，就在其後增加ORDER BY完成

17 視圖

  • 什麼是視圖

視圖可以理解為一個中間表(結果集)，咋們叫虛擬表，它主要把我們經常查詢的結果存放於中，從而提升使用的效率。本身不具有數據。如下圖所示。

  • 為什麼使用視圖

?重用SQL語句

?使用表的一部分而不是整個表

?更改數據格式和表示。

?通過授予表的特定訪問許可權來保護數據，。。

  • 如何使用視圖

✌️

  • 使用視圖過濾不想想要的數據
  • 更新視圖

18 事務處理

要麼完全執行，要麼不執行。

  • A(Atomicity)原子性。不可分割，進行數據處理的基本單位。
  • C(Consistency)。在進行事務操作以後，會從一致的狀態變為另一種一致的狀態。即使事務回滾也不能被破壞。
  • I(Isolation)隔離性。事務的獨立性。一個事務在提交之前，對其他的事務不可見。
  • D(Durability)。通過事務日誌保證。即使系統崩潰，通過資料庫日誌的更新讓系統恢復到最後一次成功的更新狀態。

19 事務隔離

我們知道當在高並發的情況下，這個時候需要較高的吞吐量，那麼採取方式之一就是將原來的串列操作變化為並行。這個時候可以通過降低資料庫的隔離標準，來換取事務的並發能力。

講述相關內容之前，我們先定義一個表如下。

ID Age Name
1 18 小藍
2 19 小林
3 20 小旋
  • 臟讀

小藍今天想去看看資料庫內容，並想把朋友小地增加到資料庫中，於是操作如下：

SQL> BEGINT:  SQL> INSERT INTO authors value(4,20，"小地");

此時小藍還沒有提交這個事務，小林去訪問了這個表(小林去年買了個表，哈哈哈嗝)，於是

SQL>SELECT * FROM authors;

然後得到這個結果:

ID Age Name
1 18 小藍
2 19 小林
3 22 小旋
4 20 小地

結論：小藍還沒有提交事務，小林訪問卻看到了增加的小地，這就是臟讀。

  • 不可重複讀

小藍聽說小地也在表裡，然後想看看是為何人如此牛掰，幾歲了？

SQL> SELECT Age FROM authors where Name="小地"

結果如下：

Age
20

我的天，這麼年輕？小藍試圖用個事務去修改其年齡

SQL>BEGIN;  SQL>UPDATE authors SET Age = 25 where Name='小地'

此時小藍去查詢下修改是否成功

SQL>SELECT Age FROM authors where Name='小地'

結果如下：

Age
25

牛掰，修改成功？那麼問題來了，小藍雖然修改了，但是並沒有提交呀，這就是不可重複讀，兩次查詢出現了不同的結果。

  • 幻讀

今天小旋過來想看看，表裡都有哪些小夥伴。

SQL> SELECT *FROM authors;

結果如下：

ID Age Name
1 18 小藍
2 19 小林
3 22 小旋
4 20 小地

這個時候小林遇到個小妹妹，發現其文采還不錯，開啟個事務將其放入表中。

SQL> BEGIN;  SQL> INSERT INTO authors values(5,'21','小魏')

小林記性太好了，於是還想看看到底有哪些人，

SQL> SELECT * FROM authors;

結果如下

ID Age Name
1 18 小藍
2 19 小林
3 22 小旋
4 20 小地
5 21 小魏

啊！小林驚呆了，怎麼多了個妹妹！！！這就是幻讀！

  • 隔離級別
臟讀 不可重複讀 幻讀
讀未提交 允許 允許 允許
讀已提交 禁止 允許 允許
可重複讀 禁止 禁止 允許
可串列化 禁止 禁止 禁止

總結下：

讀未提交:

允許臟讀，也就是可能讀取到其他會話中未提交事務修改的數據

讀已提交

只能讀取到已經提交的數據。Oracle等多數資料庫默認都是該級別 (不重複讀)

可重複讀

可重複讀。在同一個事務內的查詢都是事務開始時刻一致的，InnoDB默認級別。在SQL標準中，該隔離級別消除了不可重複讀，但是還存在幻讀

串列讀：

全串列化的讀，每次讀都需要獲得表級共享鎖，讀寫相互都會阻塞.

20 python如何操作oracle

簡單操作

  • 插入數據

插入數據

  • 查詢數據

查詢數據

21 初探調優

為什麼調優，無非就是希望響應更快，吞吐量更大，用戶體驗更好。

那麼怎麼獲得回饋

  • 用戶

他們是直接體驗者，來的直接。

  • 日誌匯聚分析,伺服器監控，資料庫內部監控

通過性能工具進行查看，想起一張圖送給大家。

這裡是性能指標圖

一般從哪幾個方面著手資料庫調優，總之沒有最好，只有更合適。

  • 前期DBMS的調研，選擇合適業務的DBMS

比如需要有事務處理能力，可以選擇mysql的InnoDB。如果採用如果考慮大幅度的降低系統IO，那麼可以考慮Nosql中的列式資料庫，之前說過列式存儲方便使用壓縮，但是不適合頻繁的增刪改。

  • 選擇合適的快取比如redis

將經常使用的數據放入快取中(記憶體),提升查詢效率。

  • 庫級別的優化

主從架構優化讀寫策略，具體方法請關注系列篇第二節。

好了，上面的基礎部分學習應該差不多了，那麼資料庫相關的優化，主從架構，讀寫分離，資料庫的分片等都是怎麼樣的呢？盡請期待後續學習分享，一起成長！

參考資料

《mysql必知必會》

《mysql45講》

https://blog.csdn.net/gengkui9897/article/details/89294936

《高性能mysql》

VirMach 便宜 VPS

QNews

QNews