數(shù)據(jù)庫(MySQL) 入門實踐

1 數(shù)據(jù)庫

存放數(shù)據(jù)的倉庫。例如你的賬號信息，訂單記錄等。

2 SQL

Structured Query Language，用于訪問和處理關系數(shù)據(jù)庫的標準的計算機語言。

按照功能又可分為四大類；

  1. DQL

    查詢語言，基本語句 SELECT；

  2. DML

    操縱語言，主要有三種形式，INSERT、UPDATE 和 DELETE；

  3. DDL

    定義語言，創(chuàng)建表、視圖、索引等，CREATE TABLE；

  4. DCL

    控制語言，用來授權或回收某種特權，基本形式有 GRANT、 COMMIT 和 ROLLBACK；

3 NoSQL

Not Only SQL，泛指非關系型的數(shù)據(jù)庫，通常以鍵值對或者文檔形式存儲。例如 Redis、MongoDB。

關系型數(shù)據(jù)庫（MySQL）能通過外鍵建立表之間的聯(lián)系，且相比 NoSQL 而言，還具備 ACID 特性。

但 NoSQL 操作無須 SQL 解析，讀寫性能較高，相比關系型數(shù)據(jù)庫來說，不用預設存儲結構，且天然支持分布式存儲。

4 范式

數(shù)據(jù)庫滿足一定要求的條件稱為數(shù)據(jù)庫范式。又能根據(jù)程度的不同，簡稱為第 N 范式。

  1. 第一范式 1NF

    所有屬性不可再分，例如屬性 product 就不能分為 title 和 price，可以單獨設置兩個屬性 productTitle、productPrice；

  2. 第二范式 2NF

    每張表都有一個屬性作為唯一標識，其他屬性完全依賴該標識，例如自增主鍵ID；

  3. 第三范式 3NF

    所有的非主屬性不依賴于其他的非主屬性。例如訂單表中可以關聯(lián)商品ID，但不應該關聯(lián)商品非主屬性 title 和 price 等；

為了提高查詢效率，通常會添加冗余字段，這也就違背了 3NF，也稱之為反三范式。

5 MySQL

MySQL 是一個 Oracle 旗下的關系型數(shù)據(jù)庫，使用 SQL 語言進行增刪改查操作。

開源免費，性能也比較好，和 PHP、Java 等 Web 開發(fā)語言完美配合，在中小型企業(yè)應用非常廣泛。

后續(xù)內容都是基于 MySQL 數(shù)據(jù)庫的前提下。

6 存儲引擎

常見的有 MyISAM 和 InnoDB 引擎；

引擎 默認版本 外鍵 鎖粒度 count(*) 事務
MyISAM < 5.5 不支持 表鎖 變量存儲 不支持
InnoDB >= 5.5 支持 行鎖 全表掃描 支持

7 事務

一條或多條 SQL 組成一個事務（transaction），具備 ACID 四個特性；

  1. Atomicity 原子性

    一個事務內的所有操作，要么全部完成，要么全部失??；

  2. Consistency 一致性

    事務開始前后結束后不會破壞數(shù)據(jù)庫的完整性，也就是說寫入或修改的結構需要符合預設的規(guī)則；

  3. Isolation 隔離性

    防止事務交叉執(zhí)行時導致數(shù)據(jù)的不一致。根據(jù)隔離程度分為 read uncommitted、read committed、repeatable read 和 serializable；

  4. Durability 持久性

    事務結束后，對數(shù)據(jù)的修改是永久的；

事務交叉執(zhí)行可能會造成“臟讀”、“幻讀” 和 “不可重復讀”；

  • 臟讀

    一個事務讀取到另外一個事務還未提交的數(shù)據(jù)；

  • 不可重復讀

    一個事務內，多次讀取同一數(shù)據(jù)返回結果不同；由于在此期間在數(shù)據(jù)被其他事務修改并已提交；

  • 幻讀

    一個事務內，多次讀取，返回不存在的記錄；由于在此期間有其他事務寫入數(shù)據(jù)；

read uncommitted read committed repeatable read serializable
臟讀 √ × × ×
不可重復讀 √ √ × ×
幻讀 √ √ √ ×

8 索引

數(shù)據(jù)庫的“目錄”，在數(shù)據(jù)量較大的情況下，可以極大地提高查詢效率。

常見的索引數(shù)據(jù)結構有 B+ 樹、Hash。以最常用的 B+ 樹為例；

image

按照 B+ 樹存儲方式可以把索引分為兩大類；

  1. 聚簇索引；

    葉子節(jié)點存放了一整行的信息；

  2. 非聚簇索引；

    葉子節(jié)點存放的是對應那行數(shù)據(jù)的主鍵，和該索引的值；

為什么是 B+ 樹？

  1. 磁盤代價低；
  2. 查詢更加穩(wěn)定；
  3. 便于遍歷；
  4. 支持范圍查詢；

一張結構為 id，groupId，name 的 t_user 表，id 為主鍵（聚簇索引），groupId 為普通索引（非聚簇索引）。

select name from t_user where groupId = 123;

先在葉子節(jié)點上得到對應的主鍵 id，然后再根據(jù)主鍵 id 得到 name 的值，這種行為稱之為回表。

select groupId from t_user where groupId = 123;

直接在葉子節(jié)點上就能得到 groupId 的值，不用回表操作，這種索引也被稱為覆蓋索引。

按照功能類型又可以把索引分為三大類；

  1. 普通索引；

    最基本的索引類型，沒有限制條件；

  2. 唯一索引；

    保證索引字段的值唯一，允許有 NULL；主鍵是一種特殊的唯一索引，不允許有 NULL；

  3. 聯(lián)合索引；

    多個字段組成一個索引，具有“最左前綴”的原則；

什么是最左前綴？

a、b、c 三個字段組成聯(lián)合索引，那么生效的列為 a、ab、abc、ac。（等值判斷時順序可交換，范圍查詢時會停止匹配）

9 鎖

宏觀來看，鎖分為兩種；行鎖可歸納為兩類；

  1. 共享鎖（S）

    share，又稱為讀鎖，已有 S 鎖，可以加其他 S 鎖，但不能加 X 鎖；

  2. 排他鎖（X）

    exclusive，又稱為寫鎖，X 與其他任何鎖互斥；

InnoDB 是通過給索引項加鎖實現(xiàn)的行鎖，可分為三種類型；

  1. record lock

    行級鎖，鎖定對應索引項；

  2. gap lock

    間隙鎖，鎖定索引項之間的間隙，左開右閉；

  3. next-key lock

    前兩種的結合；

如果不通過索引項檢索數(shù)據(jù)，會鎖住整個表。

InnoDB 加鎖方法：

  • 對于 UPDATE、DELETE、INSERT 自動加 X 鎖；
  • 對于普通 SELECT 不會加任何鎖；
  • SELECT ... LOCK IN SHARE MODE 顯示加 S 鎖；
  • SELECT ... FOR UPDATE 顯示加 X 鎖；

查詢當前數(shù)據(jù)庫鎖狀態(tài)；

select * from information_schema.innodb_locks;

對于不同類型的索引，加鎖的方式也不一樣；

  1. 普通索引

    加 next-key lock；

  2. 唯一索引

    加 record lock；

由于普通索引葉子節(jié)點存儲了主鍵，所以加鎖的字段是：普通索引 + 主鍵索引；

假設有如下數(shù)據(jù)表 t_ lock，其中 id 為主鍵，xid 為 普通索引；

+-----+----+
| xid | id |
+-----+----+
|   1 | 10 |
|   3 | 20 |
|   5 | 30 |
|   8 | 40 |
|  11 | 50 |
+-----+----+

給 (8, 40) 這條記錄加 X 鎖；

select * from t_lock where xid = 8 for update;

那么根據(jù) next-key lock 的定義，鎖住區(qū)間為 (5, 30) 到 (8, 40)，(8, 40) 到 (11, 50) 這兩個區(qū)間；

便于理解我會合并為一個區(qū)間 (5, 30) 到 (11, 50)。

按照所以排序規(guī)則，假設另插入 (xid, id) 記錄，那么總是滿足以下條件；

  1. xid < 5；id 無限制；（正常）
  2. xid = 5；id < 30；（正常）
  3. xid = 5；id > 30；（阻塞）
  4. xid > 5 && xid < 11；id 無限制；（阻塞）
  5. xid = 11；id < 50；（阻塞）
  6. xid = 11；id > 50；（正常）
  7. xid > 11；id 無限制；（正常）

簡單圖示，當插入的數(shù)據(jù)落在這個區(qū)間則會阻塞，反之亦然；

image

10 RR 幻讀

上面事務章節(jié)描述 RR 會導致幻讀，MySQL 在 RR 下通過如下兩點規(guī)避掉了；

  1. MVCC

    Multi-Version Concurrency Control，多版本并發(fā)控制。在普通 SELECT （快照度）時引入版本，同一個事務中只能讀取不大于當前版本的數(shù)據(jù)快照；

  2. next-key lock

    需要加 X 鎖的操作（當前讀），加 next-key lock 可以有效避免產生幻讀；

11 SQL 執(zhí)行順序

根據(jù)創(chuàng)建時間升序，查找支付成功超過 3 單的用戶，需要去重；

select distinct t1.nickname
from t1 inner join t2
on t1.uid = t2.uid
where t2.pay_time > 0
group by t1.uid, t1.nickname
having count(*) > 3
order by t2.create_time
limit 10
  1. from
  2. on
  3. join
  4. where
  5. group
  6. having
  7. order
  8. select
  9. distinct
  10. limit

12 binlog

binlog 是 MySQL 最重要的日志，記錄了所有的 DDL 和 DML 語句，主要目的是；

  1. 主從復制；

    在 Master 開啟 binlog，并傳遞到 Slave 節(jié)點來達到 Master-Slave 數(shù)據(jù)一致性；

  2. 數(shù)據(jù)恢復；

    通過 mysqlbinlog 恢復數(shù)據(jù)；

image

檢查 binlog 是否開啟；

show variables like 'log_bin';

編輯 mysql 配置文件/etc/mysql/mysql.conf.d/mysqld.cnf，開啟 binlog 功能；

[mysqld]
server-id=1
log-bin=/var/lib/mysql/mysql-bin

常用幾個命令；

  • show master status;
  • show binary logs;
  • mysqlbinlog -v --start-position 2755 --stop-position 3076 mysql-bin.000003；

例如誤刪除了某條記錄；

  1. 通過 mysqlbinlog 定位到誤操作的 position；
  2. 通過 mysqlbinlog 定位到誤刪之前最早入庫的 position；
  3. 截取中間 binlog 日志， echo > db.sql 輸出到可執(zhí)行 SQL 文件中；
  4. 執(zhí)行恢復數(shù)據(jù)即可；

可直接流式執(zhí)行：mysqlbinlog -v --start-position 2432 --stop-position 2533 mysql-bin.000003 | mysql -uroot -p

mysqldump 是用來備份數(shù)據(jù)庫的，例如備份 db_test 數(shù)據(jù)庫；

mysqldump -h127.0.0.1 -uroot -p123456 db_test > db.sql

13 性能優(yōu)化

  1. 索引；

    給經常用作查詢條件，且區(qū)分度較高的字段建立索引；

  2. 分頁查詢；

    where id > {lastId} order by id limit{size}，提高大表分頁效率；

  3. 批量操作；

    批量插入用 insert into xxx values (xxx...), (xxx...)，批量更新用 case when id；

  4. not null；

    null 會額外占用空間，且 count(xxx) 不會參與統(tǒng)計，若是索引列 is not null 也會失效；

?著作權歸作者所有,轉載或內容合作請聯(lián)系作者
【社區(qū)內容提示】社區(qū)部分內容疑似由AI輔助生成，瀏覽時請結合常識與多方信息審慎甄別。
平臺聲明：文章內容（如有圖片或視頻亦包括在內）由作者上傳并發(fā)布，文章內容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務。

相關閱讀更多精彩內容

友情鏈接更多精彩內容