MySQL學習筆記（5）

本文主要內(nèi)容：事務滋捶、鎖痛悯、SQL Mode、分區(qū)(這個有點意思)

事務控制和鎖定語句

LOCK TABLE和UNLOCK TABLE

• LOCK TABLES可鎖定用于當前線程的表重窟，如果表被其他線程鎖定载萌，則當前線程會等待知道可以獲取所有鎖定為止。
• UNLOCK TABLES可以釋放當前線程獲得的任何鎖定巡扇。

LOCK TABLES
    tab_name [AS alias]{READ [LOCAL]|[LOW_PRIORITY] WRITE}
    [,tbl_name [AS alias] {READ [LOCAL]|[LOW_PRIOEITY] WRITE}]...
    
UNLOCK TABLES

在數(shù)據(jù)導出時加-x參數(shù)可以鎖全表

事務控制

START TRANSACTION |BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET AUTOCOMMIT={0|1} 

默認情況下MySQL是自動提交的

• START TRANSACTION或BEGIN開始一項事務
• COMMIT和ROLLBACK用于提交或者回滾事務
• CHAIN和RELEASE字句用來定義事務提交或者回滾之后的操作扭仁，CHAIN會立即開啟一個新事務，并和剛才的事務具有相同的隔離級別厅翔，RELEASE則會斷開和客戶端的連接
• SET AUTOCOMMIT修改當前連接提交方式

在進行一些敏感操作時乖坠，可是開個事務，以防刪庫跑路的風險刀闷。

敏感操作請記得在終端操作之前
start transaction;
...
commit / rollback;

分布式事務

MySQL使用分布式事務的應用程序涉及一個或多個資源管理器和一個事務管理器

• 資源管理器（RM）用于提供通向事務資源的途徑熊泵，數(shù)據(jù)庫服務器就是一個資源管理器。
• 事務管理器（TM）用于協(xié)調(diào)作為一個分布式事務一部分的事務甸昏。

執(zhí)行分布式事務過程使用兩階段提交

• 第一階段顽分，所有分支被預備好。即他們被TM告知要準備提交施蜜。通常以為著用于管理分支的每個RM會記錄對于被穩(wěn)定保存的分支的行動卒蘸。
• 第二階段，TM告知RMs是否要提交或回滾

如果一個事務資源只由一個事務資源組成（單一分支）,則該資源可以被告知同事進行預備和提交

分布式事務（XA事務）的SQL語法：

XA {START|BEGIN} xid [JOIN|RESUME]

xid: gtrid [,bqual[,formatID]]

使事務進入PREPARE狀態(tài)翻默，兩階段提交的第一個提交階段
XA END xid [SUSPEND[FOR MIGRATE]]
XA PREPARE xid

提交或者回滾分布式事務缸沃，為第二階段
XA COMMIT xid [ONE PHASE]
XA ROLLBACK xid

返回當前數(shù)據(jù)庫中處于PREPARE狀態(tài)的分支事務的詳細信息
XA RECOVER

eg:
xa start 'test','dept';

insert into dept values(3,'xiaobai');

xa end 'test','dept';

xa prepare 'test','dept';

xa recover \G

xa commit 'test','dept'

啟用mysqlbinlog恰起，用于恢復數(shù)據(jù)用

SQL中的安全問題

在日常開發(fā)中，開發(fā)人員一般只關心SQL是否能實現(xiàn)預期的功能趾牧，而對于SQL的安全問題一般不太重視村缸。最常見的就是SQL注入的安全威脅。

預防SQL注入措施：

• 使用預編譯語句綁定變量
• 使用應用程序提供的轉換函數(shù)
• 自己定義函數(shù)進行校驗
  • 整理數(shù)據(jù)使之有效
  • 拒絕已知的非法數(shù)據(jù)
  • 只接受已知的合法輸入

SQL Model及相關問題

SQL Mode定義了MySQL應支持的SQL語法武氓、數(shù)據(jù)校驗等梯皿，這樣可以更容易地在不同的環(huán)境中使用MySQL。

在MySQL中县恕，SQL Model常用來解決以下問題：

• 通過設置SQL Model东羹，可以完成不同嚴格程度的數(shù)據(jù)校驗，有效地保障數(shù)據(jù)準確性
• 通過設置SQL Model為ANSI模式忠烛，來保證大多數(shù)SQL符合標準的SQL語法属提，這樣應用在不同數(shù)據(jù)庫之間進行遷移時，不需要對業(yè)務SQL進行較大修改
• 不同數(shù)據(jù)庫間遷移美尸，通過設置SQL Mode可以使MySQL上的數(shù)據(jù)更方便地遷移到目標數(shù)據(jù)庫中

查看SQL Mode：select @@sql_mode

修改sql_mode：SET [SESSION|GLOBAL] sql_mode='modes'

啟動時指定：--sql-mode="modes"

SQL Mode的常見功能

• 檢驗日期數(shù)據(jù)合法性冤议，在ANSI模式下，非法日期可以插入师坎，但值為"0000-00-00 00:00:00",系統(tǒng)提示warning恕酸，在TRADITIONAL模式下，會拒絕插入并報錯胯陋。
• 在INSERT或UPDATE過程中蕊温，如果SQL Mode處于TRADITIONAL嚴格模式，運行MOD(x,0)會產(chǎn)生錯誤遏乔，而在非嚴格該模式返回NULL
• 啟用NO_BACKSLASH_ESCAPES模式义矛，使反斜線成為普通字符，在導入數(shù)據(jù)時或許會用到盟萨。
• 啟用PIPES_AS_CONCAT模式凉翻，將“||”視為字符串連接操作符，在Oracle數(shù)據(jù)庫中“||”被作為連接操作符捻激，在其他數(shù)據(jù)庫中則無法執(zhí)行制轰。

常見的SQL Mode

MySQL的SQL Mode（具體版本建議參考相關版本官方網(wǎng)文檔）

SQL Mode在遷移中如何使用

MySQL提供了很多數(shù)據(jù)庫的組合模式名稱，在異構數(shù)據(jù)庫之間遷移數(shù)據(jù)時可以嘗試使用這些模式來導出適合于目標數(shù)據(jù)庫格式的數(shù)據(jù)

SQL Mode的嚴格模式為MySQL提供了很好的數(shù)據(jù)校驗功能赢织，保證了數(shù)據(jù)的準確性，TRANDITIONAL和STRICT_TRANS_TABLES是常用的兩種嚴格模式馍盟。

MySQL分區(qū)

MySQL從5.1開始支持分區(qū)于置。分區(qū)指根據(jù)一定規(guī)則，數(shù)據(jù)庫把一個表分解成多個更小更容易管理的部分贞岭。就數(shù)據(jù)庫的應用而言八毯，邏輯上只有一個表或一個索引，但實際該表可能由多個物理分區(qū)對象組成瞄桨，每個分區(qū)都是一個獨立對象宪彩，可以獨自處理，可以作為表的一部分處理讲婚。

MySQL分區(qū)優(yōu)點：

• 和單磁盤或文件系統(tǒng)分區(qū)比尿孔，可以存儲更多數(shù)據(jù)
• 優(yōu)化查詢，在Where字句中包含分區(qū)條件時筹麸，可以只掃描必要的一個或多個分區(qū)活合；同時在涉及SUM()和COUNT()這類聚合函數(shù)的查詢時，可以容易地在每個分區(qū)上并行處理物赶。
• 對已過期或不需要的數(shù)據(jù)白指，可以通過刪除分區(qū)來快速刪除
• 跨磁盤分散數(shù)據(jù)查詢，獲得更大的查詢吞吐量

分區(qū)概述

分區(qū)有利管理大表酵紫，引入了分區(qū)鍵的概念告嘲，分區(qū)鍵用于根據(jù)區(qū)間值、特定值列表或HASH函數(shù)值執(zhí)行數(shù)據(jù)的聚集奖地，讓數(shù)據(jù)更具規(guī)則分布在不同的分區(qū)中橄唬，讓大對象變成一些小對象。

查看MySQL是否支持分區(qū)：SHOW variables like %partition%

和非分區(qū)表設置存儲引擎一樣参歹，分區(qū)表設置存儲引擎仰楚，只能用[STORAGE] ENGINE字句。如下創(chuàng)建一個使用InnoDB引擎并有6個HASH分區(qū)的表：

create table emp(empid int,salary decimal(7,2),birth_date DATE)
engine=innodb
prrtition by hash(month(birth_date))
partition 6;

分區(qū)類型（MySQL5.1）

• RANGE分區(qū)：給予一個給定連續(xù)區(qū)間范圍犬庇，吧數(shù)據(jù)分配到不同分區(qū)僧界。
• LIST分區(qū)：類似RANGE分區(qū)，LIST分區(qū)是基于枚舉出的值列表分區(qū)臭挽，RANGE是基于給定的連續(xù)區(qū)間范圍分區(qū)捂襟。
• HASH分區(qū)：基于給定的分區(qū)個數(shù)，把數(shù)據(jù)分配到不同的分區(qū)欢峰。
• KEY分區(qū)：類似HASH分區(qū)葬荷。

在MySQL5.1版本中涨共，RANGE、LIST闯狱、HASH要求分區(qū)鍵必須是INT，KEY可以是其他類型（BLOB或TEXT除外）抛计。無論哪種MySQL分區(qū)類型哄孤，不能使用主鍵/唯一鍵字段之外的其他字段分區(qū)。

Range分區(qū)

區(qū)間連續(xù)不能互相重疊吹截，使用VALUES LESS THAN操作符進行分區(qū)定義瘦陈。

CREATE TABLE emp(
id int not null,
ename varchar(30),
hired date not null default '1970-01-01',
separated date not null default '9999-12-31',
job varchar(30) not null,
store_id int not null
)
partition by range (store_id)(
    partition p0 values less than (10),
    partition p1 values less than (20),
    partition p2 values less than (30)
)

以上在store_id在1~9的在分區(qū)p0中，以此類推波俄，每個分區(qū)按順序進行定義晨逝，從最低到最高。如出現(xiàn)id大于30的商品,會報錯懦铺，服務器不知道把記錄保存在哪里

可以在設置分區(qū)使用VALUES LESS THAN MAXVALUE子句捉貌，該子句提供給所有大于明確指定的最高值的值，MAXVALUE表示最大的可能的整數(shù)值冬念。

MySQL支持在VALUES LESS THAN子句中使用表達式趁窃，如：

CREATE TABLE emp_date(
id int not null,
ename varchar(30),
separated date not null default '9999-12-31',
job varchar(30) not null,
store_id int not null
)
partition by range(YEAR(separated))(
    partition p0 values less than (1995),
    partition p1 values less than (2000),
    partition p2 values less than (2005)
)

MySQL5.1要在日期或者字符串上進行分區(qū)，需要使用函數(shù)轉換急前，5.5改進了功能醒陆，提供RANGE COLUMNS分區(qū)支持非整數(shù)分區(qū)，創(chuàng)建日期分區(qū)就不需要轉換了裆针。

RANGE分區(qū)功能特別適用于以下情況：

• 當數(shù)據(jù)過期刪除時刨摩，只需要簡單ALTER TABLE emp DROP PARTITION p0來刪除p0分區(qū)數(shù)據(jù)
• 經(jīng)常運行包括分區(qū)鍵的查詢，如檢索商品ID大于25的記錄世吨，MySQL掃描p2分區(qū)即可澡刹。

List分區(qū)

LIST分區(qū)是建立離散的值來分區(qū)，LIST分區(qū)是一個枚舉列表的值得集合耘婚，RANGE分區(qū)是一個連續(xù)區(qū)間值得集合像屋。例如：

CREATE TABLE expenses(
expense_date DATE NOT NULL,
category INT,
amount DECIMAL(10,3)
)PARTITION BY LIST(category)(
    PARTITION p0 VALUES IN (3,5),
    PARTITION p1 VALUES IN (1,10),
    PARTITION p2 VALUES IN (4,9),
    PARTITION p3 VALUES IN (2),
    PARTITION p4 VALUES IN (6)
)

如果插入的列值不在包含分區(qū)值列表中，那么INSERT操作會失敗并報錯边篮。

5.5版本支持非整數(shù)分區(qū)己莺，如：

CREATE TABLE expenses(
    expense_date DATE NOT NULL,
    category VARCHAR(30),
    amount DECIMAL(10,3)
)PARTITION BY LIST COLUMNS(category)(
    PARTITION p0 VALUES IN ('loding','food'),
    PARTITION p1 VALUES IN ('flights','ground transportation'),
    PARTITION p2 VALUES IN ('lesiure','customer entertainment'),
    PARTITION p3 VALUES IN ('communications'),
    PARTITION p4 VALUES IN ('fees')
)

Columns分區(qū)

Columns分區(qū)在MySQL5.5引入的分區(qū)類型，結局了RANGE和LIST只支持整數(shù)分區(qū)的問題戈轿，除此之外凌受，Columns分區(qū)還支持多列分區(qū)。如：

CREATE TABLE rc3(
    a INT,
    b INT
)
PARTITION BY RANGE COLUMNS(a,b)(
    PARTITION p01 VALUES LESS THAN (0,10),
    PARTITION p02 VALUES LESS THAN (10,10),
    PARTITION p03 VALUES LESS THAN (10,20),
    PARTITION p03 VALUES LESS THAN (10,MAXVALUE),
    PARTITION p03 VALUES LESS THAN (MAXVALUES,MAXVALUE),
)

其中元組分開比較大小 (10,9)<(10,10) 

Hash分區(qū)

HASH分區(qū)主要用來分散熱點度思杯，確保數(shù)據(jù)在預先確定個數(shù)的分區(qū)中盡可能平均分布胜蛉。MySQL支持常規(guī)HASH分區(qū)和線性HASH分區(qū)挠进，常規(guī)使用取模算法，線性使用一個線性的2的冪的運算法則誊册。如：

CREATE TABLE emp(
    id INT NOT NULL,
    ename VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job VARCHAR(30) NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY HASH(store_id) PARTITIONS 4;

以上表有4個分區(qū)领突，數(shù)據(jù)將保存記錄的分區(qū)編號為N，N=MOD(expr,num)案怯。以上expr為store_id君旦，num為4。

常規(guī)HASH在新增分區(qū)或者合并分區(qū)時候嘲碱，大部分數(shù)據(jù)需要重新計算金砍，管理代價太大，不適合靈活變動的需求麦锯，這個時候就有了線性的分區(qū)PARTITION BY LINEAR恕稠，其在分區(qū)維護時能處理更加迅速。但是數(shù)據(jù)分布不如常規(guī)HASH分布不太均勻扶欣。

Key分區(qū)

類似于HASH分區(qū)鹅巍，只不過HASH分區(qū)允許使用用戶自定義的表達式，而Key分區(qū)需要使用MySQL服務器提供的HASH函數(shù)料祠；KEY分區(qū)支持除BLOB or Text類型外其他類型的列作為分區(qū)鍵昆著。如：

CREATE TABLE emp(
    id int not null,
    ename varchar(30),
    hired date nogt null default '1970-01-01',
    separated date not null default '9999-12-31',
    job varchar(30) not null
)
PARTITION BY KEY(job) PARTITIONS 4;

創(chuàng)建Key分區(qū)不指定分區(qū)鍵時默認首先使用主鍵作為分區(qū)鍵，沒有主鍵時术陶，選擇非空唯一鍵作為分區(qū)鍵凑懂。沒有主鍵和唯一鍵時，必須指定分區(qū)鍵梧宫。

和HASH分區(qū)類似接谨，在KEY分區(qū)中使用LINEAR可通過取模算法分散熱點數(shù)據(jù)讀寫。

子分區(qū)和分區(qū)中處理NULL值方式

分區(qū)表中對每個分區(qū)的再次分割塘匣，即復合分區(qū)脓豪。適合保存非常大量的數(shù)據(jù)記錄。如：

CREATE TABLE ts(id int,purchased date)
partition by range(year(purchased))
subpartition by hash(to_days(purchased))
subpartitions 2
(
    partition p0 values less than (1999),
    partition p1 values less than (2000),
    partition p2 values less than maxvalue
)

在RANGE分區(qū)中忌卤，NULL作為最小值處理扫夜；LIST必須出現(xiàn)在枚舉列表中；HASH/KEY分區(qū)驰徊，NULL值被當做零值處理笤闯。

分區(qū)管理

MySQL提供添加、刪除棍厂、重定義颗味、合并、拆分分區(qū)命令牺弹。

• RANGE & LIST分區(qū)管理

  • 刪除分區(qū)：ALTER TABLE tablename DROP PARTITION pname
  • 增加分區(qū)：ALTER TABLE tbl_name ADD PARTITION(expr)
  • 重新定義分區(qū)：ALTER TABLE REORGANIZE PARTITION INFO

  刪除分區(qū)：
  alter table emp_date drop partition p2;
  增加分區(qū)：
  alter table emp_date add partition (aprtititon p4 values less than (2030))
  
  alter table expenses add partition(partition p6 values in (6,11))
  重定義分區(qū)合并：
  alter table emp_date reorganize partition p1,p2,p3 into (
  partition p1 values less than (2015)
  )
  

重新定義分區(qū)只能重新定義相鄰的分區(qū)浦马，重新定義的分區(qū)區(qū)間必須和原來分區(qū)區(qū)間覆蓋相同时呀，也不能使用重新定義分區(qū)來改變表分區(qū)的類型。

HASH & KEY分區(qū)

修改操作：ALTER TABLE COALESCE PARTITION

減少分區(qū)：
alter table emp coalesce partition 2
增加8個分區(qū)：
alter table emp add partititon partititons 8

[Github傳送門][1]
[1]: https://github.com/Melody12ab/db_mysql_note