ddl形式化方法

1.DDL(Data Definition Language):數(shù)據(jù)定義語言，用來定義數(shù)據(jù)庫對象，庫、表、列等；創(chuàng)建、刪除、修改：庫，表結(jié)構(gòu)；

成都創(chuàng)新互聯(lián)公司-專業(yè)網(wǎng)站定制、快速模板網(wǎng)站建設(shè)、高性價(jià)比沽源網(wǎng)站開發(fā)、企業(yè)建站全套包干低至880元,成熟完善的模板庫,直接使用。一站式沽源網(wǎng)站制作公司更省心,省錢,快速模板網(wǎng)站建設(shè)找我們，業(yè)務(wù)覆蓋沽源地區(qū)。費(fèi)用合理售后完善，10余年實(shí)體公司更值得信賴。

2.DML(Data Manipulation Language):數(shù)據(jù)操作語言，用來定義數(shù)據(jù)庫記錄（數(shù)據(jù)）；增、刪、改：表記錄

3.DCL(Data Control Language)：數(shù)據(jù)控制語言，用來定義訪問權(quán)限和安全級別

4.DQL（Data Query Language）:數(shù)據(jù)查詢語言，用來查詢記錄（數(shù)據(jù)）

DDL之操作數(shù)據(jù)庫

創(chuàng)建數(shù)據(jù)庫：

create database mydb1;

1

1

刪除數(shù)據(jù)庫：

drop database mysql;

1

1

數(shù)據(jù)類型（列類型）

int :整型

double :浮點(diǎn)型，例如double(5,2)表示最多5位，其中必須有兩位小數(shù)。即最大值為999.99

decimal:浮點(diǎn)型，在表單錢方面使用該類型，因?yàn)椴粫?huì)出現(xiàn)精度缺失問題

char:固定長度字符串類型 char(255)，數(shù)據(jù)最長為255，數(shù)據(jù)的長度不足指定長度，補(bǔ)足到指定長度

varchar: 可變字符串類型，varchar(65535),不補(bǔ)

text(club):字符串類型(mysql獨(dú)有)

tinytext小

text：

mediumtext:中

longtext:大

blob:字節(jié)型

data:日期類型：格式為yyyy-MM-dd;

time:時(shí)間類型：格式為hh:mm:ss

timestamp:時(shí)間戳類型，既有日期，又有時(shí)間

DDL之操作表

創(chuàng)建表：

create table student(

- sno char(5),

- sname varchar(10),

- sage int

- ),

1

2

3

4

5

1

2

3

4

5

查詢表結(jié)構(gòu)

desc student;

1

1

刪除表：

drop table student;

1

1

表中增加列：

alter table student

- add(

- educadtion varchar(50)

- );

1

2

3

4

1

2

3

4

表中修改列屬性：

alter table student

- modify sage varchar(20);

1

2

1

2

表中刪除列：

alter table student

- drop sage;

1

2

1

2

表的重命名：

mysql alter table student

- rename to ss;

關(guān)于mysql 5.6 哪些操作可以進(jìn)行online ddl 詳解

實(shí)際測試中add primary key 和修改字段類型是需要copy tmp table的并且阻塞dml操作，另外在5.6.17版本之前時(shí)候用alter table table_name engine=innodb 是需要 copy table的并且也阻塞dml。

本來打算翻譯成中文，后來發(fā)現(xiàn)這些英文實(shí)在是太簡單了，就直接貼出來吧。

請看下圖：

mysql 存儲過程 DDL 參數(shù)

MySQL8.0 開始支持原? DDL(atomic DDL)，數(shù)據(jù)字典的更新，存儲引擎操作，寫?進(jìn)制日志結(jié)合成了一個(gè)事務(wù)。在沒有原?DDL之前，DROP TABLE test1,test2；如遇到server crash，可能會(huì)有test1被drop了，test2沒有被drop掉。下面來看下在MySQL8.0之前和MySQL8.0 數(shù)據(jù)字典的區(qū)別

在MySQL8.0 之前，Data Dictionary除了存在與.FRM, .TRG, .OPT ?件外，還存在于系統(tǒng)表中（MyISAM ?事務(wù)引擎表中），在MySQL8.0 ，Data Dictionary 全部存在于Data Dictionary Storage Engine（即 InnoDB表中），這使crash recovery 維持原?性成為了可能

存儲引擎?持

目前，只有InnoDB存儲引擎?持原子DDL，為了實(shí)現(xiàn)原子DDL，Innodb要寫DDL logs 到 mysql.innodb_ddl_log 表，這是?個(gè)隱藏在mysql.ibd 數(shù)據(jù)字典表空間?的數(shù)據(jù)字典表。要看mysql.innodb_ddl_log 中的內(nèi)容，需要

SET GLOBAL?LOG_ERROR_VERBOSITY=3;（MySQL 8.0 默認(rèn)為2，error log 記錄Errors and

warnings，不不記錄notes）

SET GLOBAL innodb_print_ddl_logs=1;

CREATE TABLE?t1 (c1 INT)?ENGINE?=?InnoDB;

查看error log

[Note] [MY-011066] InnoDB: DDL log?insert?: [DDL?record:?DELETE SPACE,?id=30,

thread_id=25, space_id=9, old_file_path=./test/t1.ibd]

[Note] [MY-011066]?InnoDB:?DDL log?delete?:?by id?30

[Note] [MY-011066]?InnoDB:?DDL log?insert?: [DDL?record: REMOVE?CACHE,?id=31,

thread_id=25, table_id=1066, new_file_path=test/t1]

[Note] [MY-011066]?InnoDB:?DDL log?delete?:?by id?31

[Note] [MY-011066]?InnoDB:?DDL log?insert?: [DDL?record: FREE,?id=32, thread_

id=25, space_id=9, index_id=143, page_no=4]

[Note] [MY-011066]?InnoDB:?DDL log delete?:?by id?32

[Note] [MY-011066]?InnoDB:?DDL log?post ddl :?begin for thread id?: 25

[Note] [MY-011066]?InnoDB:?DDL log?post ddl :?end for thread id?: 25

原子DDL 操作步驟

準(zhǔn)備：創(chuàng)建所需的對象并將DDL?志寫入 mysql.innodb_ddl_log表中。DDL日志定義了如何前滾和回滾DDL操作。

執(zhí)行：執(zhí)?DDL操作。例如，為CREATE TABLE操作執(zhí)?創(chuàng)建。

提交：更新數(shù)據(jù)字典并提交數(shù)據(jù)字典事務(wù)。

Post-DDL：重播并從mysql.innodb_ddl_log表格中刪除DDL?志。為確?；貪L可以安全執(zhí)??不引?不?致性，在此最后階段執(zhí)??件操作（如重命名或刪除數(shù)據(jù)文件）。這一階段還從 mysql.innodb_dynamic_metadata的數(shù)據(jù)字典表刪除的動(dòng)態(tài)元數(shù)據(jù)為了DROP TABLE，TRUNCATE和其它重建表的DDL操作。

?論事務(wù)是提交還是回滾，DDL日志都會(huì)mysql.innodb_ddl_log在Post-DDL階段重播并從表中刪除 。mysql.innodb_ddl_log如果服務(wù)器在DDL操作期間暫停，DDL?志應(yīng)該只保留在表中。在這種情況下，DDL?志會(huì)在恢復(fù)后重播并刪除。

在恢復(fù)情況下，當(dāng)服務(wù)器重新啟動(dòng)時(shí)，可能會(huì)提交或回退DDL事務(wù)。如果在重做?志和?進(jìn)制日志中存在DDL操作的提交階段期間執(zhí)?的數(shù)據(jù)字典事務(wù)，則該操作被認(rèn)為是成功的并且被前滾。否則，在InnoDB重放數(shù)據(jù)字典重做日志時(shí)回滾不完整的數(shù)據(jù)字典事務(wù) ，并且回滾DDL事務(wù)。

原?DDL ?持類型

? DROP TABLES , all tables dropped or none

? DROP SCHEMA, all entities in the schema are dropped, or none

? Note that atomic DDL statements will be rolled back or committed even in case of crash, e.g. RENAME TABLES

? CREATE TABLE would be successfully committed or rolled back (no orphan ibd left)

? TRUNCATE TABLE (including InnoDB tables with FTS AUX tables) would be successfully committed or rolled back

? RENAME TABLES, all or none

? ALTER TABLE successful or not done

示例

請點(diǎn)擊輸入圖片描述

請點(diǎn)擊輸入圖片描述

結(jié)論

在MySQL8.0之前，alter table 操作在server crash的情況下，會(huì)遺留.frm,.ibd文件。MySQL8.0 能實(shí)現(xiàn)原?DDL（包括 DROP TABLE, DROP SCHEMA, CREATE TABLE, TRUNCATE TABLE, ALTER TABLE），alter table 操作，在server crash的情況下，不會(huì)遺留.frm，.ibd臨時(shí)文件。讓我們?起期待MySQL8.0 GA的到來吧！

技術(shù)分享 | MySQL 并行 DDL

隨著 MySQL 版本的不斷更新，對 DDL 操作的支持也在不斷的完善和更新：比如從 MySQL 5.6 引入 Online DDL ，在 MySQL 5.7 對 Online DDL 進(jìn)一步完善，到現(xiàn)在的 8.0 版本，則對 DDL 的實(shí)現(xiàn)重新進(jìn)行了設(shè)計(jì)，比如 DDL 操作支持原子特性，在 MySQL 8.0.27 引入并行 DDL 。本篇就來探究一下 MySQL 8.0.27 的并行 DDL 對于 DDL 操作速度的提升。

MySQL 8.0.14 引入了 innodb_parallel_read_threads 變量來控制掃描聚簇索引的并行線程。MySQL 8.0.27 引入了 innodb_ddl_threads 變量來控制用于創(chuàng)建二級索引時(shí)的并行線程數(shù)量，此參數(shù)一般和一并引入的 innodb_ddl_buffer_size 一起使用，innodb_ddl_buffer_size 用于指定進(jìn)行并行 DDL 操作時(shí)能夠使用的 buffer 大小，buffer 是在所有的 DDL 并行線程中平均分配的，所以一般如果調(diào)大 innodb_ddl_threads 變量時(shí)，也需要調(diào)大 innodb_ddl_buffer_size 的大小。

innodb_ddl_threads 、innodb_ddl_buffer_size 和 innodb_parallel_read_threads 的默認(rèn)大小分別為：

接下來測試一下調(diào)大 innodb_ddl_threads 、innodb_ddl_buffer_size 和 innodb_parallel_read_threads 參數(shù)值對 DDL 操作的性能提升。

首先創(chuàng)建一張 5000 萬的表：

分別測試不同的線程數(shù)量和緩沖區(qū)大小的 DDL 操作時(shí)間，例如：

通過不斷調(diào)整相關(guān)參數(shù)得到以下結(jié)果：

可以看到，隨著并發(fā)線程的增多和 buffer 的增加，DDL 操作所占用的資源也越多，而 DDL 操作所花費(fèi)的時(shí)間則越少。不過通過對比資源的消耗和 DDL 速度的提升比例，最合理的并行線程數(shù)量為4-8個(gè)，而 buffer 大小可以根據(jù)情況進(jìn)行調(diào)整。

參考鏈接：