MySQL大表優(yōu)化

SQL語句優(yōu)化

一、DDL(Data Definition Language 數(shù)據(jù)定義語言)

用于操作對象和對象的屬性，這種對象包括數(shù)據(jù)庫本身，以及數(shù)據(jù)庫對象，像：表、視圖等等，DDL 對這些對象和屬性的管理和定義具體表現(xiàn)在 create、drop 和 alter 上。特別注意：DDL 操作的“對象”的概念，”對象“包括對象及對象的屬性，而且對象最小也比記錄大個層次。以表舉例：create 創(chuàng)建數(shù)據(jù)表，alter 可以更改該表的字段，drop 可以刪除這個表。由此得知，DDL 不涉及具體的數(shù)據(jù)。所有的 DDL 均將被隱式提交，無法 ROLLBACK。

1??DDL的主要操作

1. 【create】
   可以創(chuàng)建數(shù)據(jù)庫和數(shù)據(jù)庫的一些對象。
2. 【drop】

• drop 是 DDL，會隱式提交，所以不能回滾，不會觸發(fā)觸發(fā)器。
• drop 刪除表結(jié)構(gòu)及所有數(shù)據(jù)，并將表所占用的空間全部釋放。
• drop 將刪除表的結(jié)構(gòu)，所依賴的約束，觸發(fā)器，索引以及數(shù)據(jù)表的權(quán)限等，依賴于該表的存儲過程/函數(shù)將保留，但是變?yōu)?invalid 狀態(tài)。

3. 【truncate】
   truncate table tabname一次性地從表中刪除所有的數(shù)據(jù)頁并不把單獨的刪除操作記錄記入日志保存，刪除行是不能恢復(fù)的。并且在刪除的過程中不會激活與表有關(guān)的刪除觸發(fā)器。執(zhí)行速度快。

• truncate 是 DDL，會隱式提交，所以不能回滾，不會觸發(fā)觸發(fā)器。
• truncate 會刪除表中所有記錄，并且將重新設(shè)置高水線和所有的索引，缺省情況下將空間釋放到 minextents 個 extent，除非使用 reuse storage。不會記錄日志，所以執(zhí)行速度很快，但不能通過 rollback 撤消操作(如果一不小心把一個表 truncate 掉，也是可以恢復(fù)的，只是不能通過 rollback 來恢復(fù))。
• 對于外鍵(foreign key)約束引用的表，不能使用 truncate table，而應(yīng)使用不帶 where 子句的 delete 語句。
• truncate table不能用于參與了索引視圖的表。

4. 【alter】
   修改數(shù)據(jù)表定義及屬性。

2??DDL的操作對象(表)

1. 表的概念
   表的創(chuàng)建就是用來存放數(shù)據(jù)用的，由于存放的數(shù)據(jù)的不同，所以需要定義些數(shù)據(jù)類型，以方便管理。

2. 表的屬性

【主鍵屬性】
主鍵就是主鍵約束，主鍵的起名偏向于虛的(就是描述這件事)，主鍵約束起名偏向于實得(就是描述操作的實施)，描述的都是同一件事，主鍵約束就是表中的一個屬性。在一個表中最多可以有一個主鍵，一個主鍵可以定義在一個或多個字段。主鍵使一個或多個字段的值必須唯一且不為空，這樣做可以通過該字段或該組字段中的值唯一的代表一條記錄。

【唯一屬性】
一個表中只能有一個主鍵屬性，為了方表用戶，提出唯一約束。唯一約束可以定義在一個或多個字段上。唯一約束使該字段或該組字段中的值唯一，可以為空，但是不能重復(fù)。

【外鍵屬性】
又叫外鍵、外鍵約束，跟主鍵和主鍵約束的關(guān)系是一樣的。外鍵約束針對的兩個表，如果表A的主關(guān)鍵字是表B中的字段，則該字段稱為表B的外鍵，表A稱為主表，表B稱為從表。但要注意，必須要系統(tǒng)知道是這種關(guān)系。

【核查、Null 和缺省屬性】
核查屬性又叫核查約束，Null 屬性又叫 Null 約束，缺省屬性又叫缺省約束。這些名稱是描述一件事，描述一種情況，這件事或這張情況。當(dāng)然可以人為的那樣特意做(輸入數(shù)據(jù)時注意就行)，但本意是實現(xiàn)自動化，也就是讓計算機(jī)做這件事。

二、DML(Data Manipulation Language 數(shù)據(jù)操控語言)

用于操作數(shù)據(jù)庫對象中包含的數(shù)據(jù)，也就是說操作的單位是記錄。

1??DML的主要語句操作

1. 【insert】
   向數(shù)據(jù)表張插入一條記錄。
2. 【delete】
   刪除數(shù)據(jù)表中的一條或多條記錄，也可以刪除數(shù)據(jù)表中的所有記錄。但是，它的操作對象仍是記錄。

• delete 是DML，執(zhí)行時，每次從表中刪除一行，并且同時將該行的刪除操作記錄在 redo 和 undo 表空間中以便進(jìn)行回滾(rollback)和重做操作，但要注意表空間要足夠大，需要手動提交(commit)操作才能生效，可以通過 rollback 撤消操作。

• delete 可根據(jù)條件刪除表中滿足條件的數(shù)據(jù)，如果不指定 where 子句，那么刪除表中所有記錄。

• delete 不影響表所占用的 extent，高水線(high watermark)保持原位置不變。

3. 【update】
   用于修改已存在表中的記錄的內(nèi)容。

2??DML的操作對象——記錄

三、DCL(Data Control Language 數(shù)據(jù)控制語句)

DCL的操作是數(shù)據(jù)庫對象的權(quán)限，這些操作的確定使數(shù)據(jù)更加的安全。

1??DCL的主要語句操作

1. 【grant】
   允許對象的創(chuàng)建者給某用戶或某組或所有用戶(PUBLIC)某些特定的權(quán)限。
2. 【revoke】
   可以廢除某用戶或某組或所有用戶訪問權(quán)限

2??DCL的操作對象(用戶)：
此時的用戶指的是數(shù)據(jù)庫用戶。

四、MySQL 相關(guān)基本語句

1??插入記錄

insert into tab (field1,field2) values (value1,value2);

2??刪除記錄

delete from tab where conditions;

3??更新記錄

update tab set field1=value1,field2=value2 where conditions;

MySQL 給一個字段遞增賦值：
①首先設(shè)置一個變量，初始值為0：set @r:=0;
②然后更新表中對應(yīng)的ID列：update tab set id=(@r:=@r+1)
③如果是插入，那就找一個記錄多的表 t1：
set @r:=0;
insert into t select @r:=@r+1 from t1 limit 0, 2000

4??查詢數(shù)據(jù)

select * from tab where conditions order by field desc;(精準(zhǔn)查詢)
asc升序【默認(rèn)】 / desc降序
select * from tab where field like '%value%'(模糊查詢)

order by id desc,time desc-----id 降序排列優(yōu)先；id 一樣的話，再按 time 降序排列(前提是滿足 id 降序排列)。后面再加第三列的話，同理。
中文排序異常解決(第十點)

5??提交數(shù)據(jù)/回滾數(shù)據(jù)

commit;//提交數(shù)據(jù)
rollback;//回滾數(shù)據(jù)

6??總數(shù)

select count(*) from tab;

7??求和/求平均值

select sum(field) as sumvalue from tab;//求和
select avg(field) as avgvalue from tab;//求平均值

8??最大/最小

select max(field) as maxvalue from tab;//最大
select min(field) as minvalue from tab;//最小

9??分組

grounp by：一張表，一旦分組完成后，查詢只能得到組相關(guān)的信息。

??between限制查詢數(shù)據(jù)范圍時包括了邊界值。

select * from tab where time between time1 and time2;
select a,b,c from tab where a not between num1 and num2;

1??1??in的用法

select * from tab where a [not] in ('值1','值2','值3','值4')

1??2??兩張關(guān)聯(lián)表，刪除主表中已經(jīng)在副表中沒有的信息

delete from tab1 where not exists 
(select * from tab2 where tab1.field1=tab2.field2)

1??3??四表聯(lián)查

select * from a 
left inner join b on a.a=b.b 
right inner join c on a.a=c.c 
inner join d on a.a=d.d 
where conditions

1??4??查詢相同條目并記錄重復(fù)次數(shù)(大于2)

select a,count(a) from tab group by a having count(*) > 2

1??5??創(chuàng)建數(shù)據(jù)庫/刪除數(shù)據(jù)庫

create database database_name;//創(chuàng)建數(shù)據(jù)庫
drop database database_name;## 刪除數(shù)據(jù)庫

1??6??創(chuàng)建新表/刪除表

## 創(chuàng)建新表
create table dept(
    id  int(11)  not null  auto_increment,
    name  varchar(255)  default null,
    primary  key(dept_id)
 );
## 刪除表
drop table tab;

1??7??根據(jù)已有的表創(chuàng)建新表

create table ntab like otab;(使用舊表B創(chuàng)建新表A)  (MySQL)

備注：此種方式在將表B復(fù)制到A時候，會將表B完整的字段結(jié)構(gòu)和索引復(fù)制到表A中來。但不會復(fù)制數(shù)據(jù)。

create table ntab as select col1,col2... from otab definition only;      

備注：此種方式只會將B表的字段結(jié)構(gòu)復(fù)制到表A中來，但不會復(fù)制表B中的索引到表A中來。這種方式比較靈活可以在復(fù)制原來表結(jié)構(gòu)的同時指定要復(fù)制哪些字段，并且自身復(fù)制表也可以根據(jù)需要增加字段結(jié)構(gòu)。

1??8??新增表的別名

comment on tab col is '測試';

1??9??更改表名

alter table tab_oldName rename to tab_newName;

2??0??增加一個列

alter table tab add  column_name type;

alter table 表名 ADD 字段名稱 字段類型(字段長短-選填)  
NOT NULL[是否為null] default 0 comment '字段備注';

2??1??添加主鍵/刪除主鍵

alter table tab add primary key(col);//添加主鍵
alter table tab drop primary key(col);//刪除主鍵

一個數(shù)據(jù)表只可以有一個主鍵，所以不存在刪除某一列的主鍵。

2??2??創(chuàng)建索引/刪除索引索引不可以更改，想更改必須刪除重新建。

//創(chuàng)建索引
create [unique] index idxname on tab(col...);
//刪除索引
drop index idxname;
drop index idxname on table;

2??3??查看某個表的索引

show index from table_name

2??4??創(chuàng)建視圖/刪除視圖

//創(chuàng)建視圖
create view viewname as select statement;
//刪除視圖
drop view viewname;

五、pro

select * from dba_blockers;查詢鎖

select * from dba_waiters;查詢被阻塞的會話

select column_name from tab_old intersect select column_name from tab_new;顯示兩表的相同數(shù)據(jù)

select @@version; 或 select version();查看數(shù)據(jù)庫版本

select database();查看當(dāng)前數(shù)據(jù)庫

select user();查看當(dāng)前用戶

show tables;查看所有表

show columns from table;查看表中的列的基本信息

desc table; 或 describe table; 表名后加字段名，查看該字段基本信息

select CHARACTER_LENGTH(col) from table;查詢該字段值的長度

六、Oracle 相關(guān)基本語句

1??查詢表名

select table_name,tablespace_name,temporary 
from user_tables [where table_name=upper('表名')];

1. table_name：表名(varchar2(30))
2. tablespace_name：存儲表名的表空間(varchar2(30))
3. temporary：是否為臨時表(varchar2(1))

2??查詢表列名

select column_name,data_type,data_length,data_precision,data_scale 
from user_tab_columns [where table_name=upper('表名')];

select table_name,column_name
from user_tab_columns
where column_name like '%xxx%' and table_name like '%xxx%';

1. column_name：列名(varchar2(30))
2. data_type：列的數(shù)據(jù)類型(varchar2(106))
3. data_length：列的長度(number)

另外，也可以通過 all_tab_columns來獲取相關(guān)表的數(shù)據(jù)。

select * from all_tab_columns [where table_name='表名'];

3??根據(jù)表名，查詢表的索引

select * from user_indexes [where table_name=upper('表名')];
select* from all_indexes where table_name=upper('表名');
select * from user_ind_columns where table_name=upper('表名');

4??根據(jù)索引名，查詢表的索引字段

select * from user_ind_columns [where index_name=('索引名')];

5??根據(jù)索引名，查詢創(chuàng)建索引的語句

select dbms_metadata.get_ddl('INDEX','索引名', ['用戶名']) from dual ;
-- ['用戶名']可省，默認(rèn)為登錄用戶。

6??查詢數(shù)據(jù)庫版本

select * from v$version;

select * from product_component_version  
where SUBSTR(PRODUCT,1,6)='Oracle';

select version from v$instance;

七、drop、truncate 和 delete 的區(qū)別

1??truncate 和 delete 只刪除數(shù)據(jù)， drop 則刪除整個表(結(jié)構(gòu)和數(shù)據(jù))。

2???truncate 只能對 table。delete 可以是 table 和 view。

3??一般速度上，drop > truncate > delete。delete 操作不會減少表或索引所占用的空間，truncate 會將該表和索引所占用的空間會恢復(fù)到初始大小，drop 將表所占用的空間全釋放掉。

4??truncate 與不帶 where 的 delete ：只刪除數(shù)據(jù)，而不刪除表的結(jié)構(gòu)(定義)。drop 語句將刪除表的結(jié)構(gòu)被依賴的約束(constrain)、觸發(fā)器(trigger)、索引(index)，依賴于該表的存儲過程/函數(shù)將被保留，但其狀態(tài)會變?yōu)椋篿nvalid。

5??delete 執(zhí)行的過程是每次從表中刪除一行，同時將該刪除操作作為事務(wù)記錄在日志中保存以便進(jìn)行回滾操作。該操作是 DML 會被放到 rollback segment 中，事務(wù)提交后才生效。如果有相應(yīng)的 tigger，執(zhí)行的時候?qū)⒈挥|發(fā)。

truncate table 一次性刪除表所有的數(shù)據(jù)并不記錄日志，刪除行不可恢復(fù)。并且在刪除的過程中不會激活與表有關(guān)的刪除觸發(fā)器。執(zhí)行速度快。truncate、drop 是 DDL 操作立即生效，原數(shù)據(jù)不放到 rollback segment 中，不能回滾。

6??truncate table tabname速度快，效率高。truncate table 在功能上與不帶 WHERE 子句的 delete 語句相同：二者均刪除表中的全部行。但 truncate table 比 delete 速度快，且使用的系統(tǒng)和事務(wù)日志資源少。delete 語句每次刪除一行，并在事務(wù)日志中為所刪除的每行記錄一項。truncate table 通過釋放存儲表數(shù)據(jù)所用的數(shù)據(jù)頁來刪除數(shù)據(jù)，并且只在事務(wù)日志中記錄頁的釋放。

7??truncate table 刪除表中的所有行，但表結(jié)構(gòu)及其列、約束、索引等保持不變。新行標(biāo)識所用的計數(shù)值重置為該列的種子。如果想保留標(biāo)識計數(shù)值，請改用 delete。如果要刪除表定義及其數(shù)據(jù)，請使用 drop table 語句。

8??對于由 foreign key 約束引用的表，不能使用 truncate table，而應(yīng)使用不帶 where 子句的 delete 語句。由于 truncate table 不記錄在日志中，所以它不能激活觸發(fā)器。

9??總結(jié)

1. 在速度上，一般來說，drop> truncate > delete。

2. 在使用 drop 和 truncate 時一定要注意，雖然可以恢復(fù)，但為了減少麻煩，還是要慎重。

3. 如果想刪除部分?jǐn)?shù)據(jù)用 delete，注意帶上 where 子句，回滾段要足夠大；如果想刪除表，當(dāng)然用 drop；如果想保留表而將所有數(shù)據(jù)刪除，如果和事務(wù)無關(guān)，用 truncate 即可；如果和事務(wù)有關(guān)，或者想觸發(fā) trigger，還是用 delete；如果是整理表內(nèi)部的碎片，可以用 truncate 跟上 reuse stroage，再重新導(dǎo)入/插入數(shù)據(jù)。

八、MySQL 和 Oracle 的區(qū)別

MySQL 和 Oracle 都是流行的關(guān)系數(shù)據(jù)庫管理系統(tǒng)(RDBMS)，在世界各地廣泛使用。大多數(shù)數(shù)據(jù)庫以類似的方式工作。兩個數(shù)據(jù)庫的特性是不同的。

1??本質(zhì)的區(qū)別

1. Oracle 數(shù)據(jù)庫是一個對象關(guān)系數(shù)據(jù)庫管理系統(tǒng)(ORDBMS)。它通常被成為 OracleRDBMS 或簡稱為 Oracle，是一個收費的數(shù)據(jù)庫。
2. MySQL 是一個開源的關(guān)系數(shù)據(jù)庫管理系統(tǒng)(RDBMS)。它是世界上使用最多的 RDBMS(Relational Database Management System，關(guān)系數(shù)據(jù)庫管理系統(tǒng))，作為服務(wù)器運行，提供多個數(shù)據(jù)庫的多用戶訪問。它是一個免費的數(shù)據(jù)庫。

2??數(shù)據(jù)庫安全性

1. MySQL 使用三個參數(shù)來驗證用戶，即用戶名、密碼和位置。
2. Oracle 使用了許多安全功能，如用戶名，密碼，配置文件，本地身份驗證，外部身份驗證，高級安全增強(qiáng)功能等。

3??SQL語法的區(qū)別

Oracle 的 SQL 語法與 MySQL 有很大不同。Oracle 為稱為 PL/SQL 的編程語言提供了更大的靈活性。Oracle 的 SQL*Plus 工具提供了比 MySQL 更多的命令，用于生成報表輸出和變量定義。

4??存儲上的區(qū)別

與 Oracle 相比，MySQL 沒有表空間、角色管理、快照、同義詞和包以及自動存儲管理。

5??對象名稱的區(qū)別

雖然某些模式對象名稱在 Oracle 和 MySQL 中都不區(qū)分大小寫，例如列、存儲過程和索引等。但在某些情況下，兩個數(shù)據(jù)庫之間的區(qū)別大小寫是不同的：

1. Oracle 對所有對象名稱都不區(qū)分大小寫。
2. 某些 MySQL 對象名稱(如數(shù)據(jù)庫和表)區(qū)分大小寫(取決于底層操作系統(tǒng))。

6??運行程序和外部程序支持

• Oracle 數(shù)據(jù)庫支持從數(shù)據(jù)庫內(nèi)部編寫，編譯和執(zhí)行的幾種編程語言。此外，為了傳輸數(shù)據(jù)，Oracle 數(shù)據(jù)庫使用 XML。
• MySQL 不支持在系統(tǒng)內(nèi)執(zhí)行其他語言，也不支持 XML。

7??MySQL和Oracle的字符數(shù)據(jù)類型比較

兩個數(shù)據(jù)庫中支持的字符類型存在一些差異。

1. 對于字符類型，MySQL 具有 CHAR 和 VARCHAR，最大長度允許為65535字節(jié)(CHAR 最多可以為255字節(jié)，VARCHAR 為65535字節(jié))。
2. 而 Oracle 支持四種字符類型，即 CHAR、NCHAR、VARCHAR2和NVARCHAR2。所有四種字符類型都需要至少1個字節(jié)長；CHAR 和 NCAHR 最大可以是2000個字節(jié)，VARCHAR2 和 NVARCHAR2 的最大限制是4000個字節(jié)?？赡軙谧钚掳姹局羞M(jìn)行擴(kuò)展。

8??MySQL 和 Oracle 的額外功能比較

1. MySQL 數(shù)據(jù)庫不支持其服務(wù)器上的任何功能，如 Audit Vault。
2. Oracle支持其數(shù)據(jù)庫服務(wù)器上的幾個擴(kuò)展和程序，例如 Active Data Guard，Audit Vault，Partitioning 和 Data Mining 等。

9??臨時表的區(qū)別：Oracle 和 MySQL 以不同方式處理臨時表。

1. 在 MySQL 中，臨時表是僅對當(dāng)前用戶會話可見的數(shù)據(jù)庫對象，并且一旦會話結(jié)束，這些表將自動刪除。
2. Oracle 中臨時表的定義與 MySQL 略有不同，因為臨時表一旦創(chuàng)建就會存在，直到它們被顯式刪除，并且對具有適當(dāng)權(quán)限的所有會話都可見。但是，臨時表中的數(shù)據(jù)僅對數(shù)據(jù)插入表中的用戶會話可見，并且數(shù)據(jù)可能在事務(wù)或用戶會話期間持續(xù)存在。

??MySQL 和 Oracle 中的備份類型

1. Oracle 提供不同類型的備份工具，如冷備份，熱備份，導(dǎo)出，導(dǎo)入，數(shù)據(jù)泵。Oracle 提供了最流行的稱為 RecoveryManager(RMAN)的備份實用程序，可以使用極少的命令或存儲腳本自動化備份調(diào)度和恢復(fù)數(shù)據(jù)庫。
2. MySQL 有 mysqldump 和 mysqlhotcopy 備份工具。在 MySQL 中沒有像 RMAN 這樣的實用程序。

1??1??Oracle 和 MySQL 的數(shù)據(jù)庫管理

在數(shù)據(jù)庫管理部分，Oracle DBA 比 MySQL DBA 更有收益。與 MySQL 相比，Oracle DBA 有很多可用的范圍。