内射美女网站,日韩熟妇免费在线观看,亚洲日韩2016

## 一、兩個引擎的對比

### MyISAM

- 不支持事務，但是整個操作是原子性的(事務具備四種特性：原子性、一致性、隔離性、持久性)

- 不支持外鍵，支持表鎖，每次鎖住的是整張表

MyISAM的表鎖有讀鎖和寫鎖(兩個鎖都是表級別)

一個MyISAM表有三個文件：索引文件，表結構文件，數(shù)據(jù)文件

存儲表的總行數(shù)，執(zhí)行select count(*) from table時只要簡單的讀出保存好的行數(shù)即可

采用非聚集索引，索引文件的數(shù)據(jù)域存儲指向數(shù)據(jù)文件的指針。輔索引與主索引基本一致，但是輔索引不用保證唯一性。

對于AUTO_INCREMENT類型的字段，在MyISAM表中，可以和其他字段一起建立聯(lián)合索引。

### InnoDB

- 支持事務，支持事務的四種隔離級別;是一種具有事務(commit)、回滾(rollback)和崩潰修復能力(crash recovery capabilities)的事務安全(transaction-safe (ACID compliant))型表。

- 支持行鎖和外鍵約束，因此可以支持寫并發(fā)

- 不存儲總行數(shù)；也就是說，執(zhí)行select count(*) from table時，InnoDB要掃描一遍整個表來計算有多少行。注意的是，當count(*)語句包含 where條件時，兩種表的操作是一樣的。

- 對于AUTO_INCREMENT類型的字段，InnoDB中必須包含只有該字段的索引

- DELETE FROM table時，InnoDB不會重新建立表，而是一行一行的刪除

- 一個Innodb表存儲在一個文件內(nèi)(共享表空間，表大小不受操作系統(tǒng)的限制)，也可能為多個(設置為獨立表空間，表大小受操作系統(tǒng)限制，大小為2G)，受操作系統(tǒng)文件大小的限制

- 主鍵索引采用聚集索引（索引的數(shù)據(jù)域存儲數(shù)據(jù)文件本身），輔索引的數(shù)據(jù)域存儲主鍵的值；因此從輔索引查找數(shù)據(jù)，需要先通過輔索引找到主鍵值，再訪問主鍵索引；最好使用自增主鍵，防止插入數(shù)據(jù)時，為維持B+樹結構，文件的大調(diào)整。

? ### 兩者的區(qū)別

![img](12.11.assets/1266222-20181002095044128-1277628264.png)

## 二、MySQL磁盤碎片

### 碎片產(chǎn)生的原因

- 表的存儲會出現(xiàn)碎片化，每當刪除了一行內(nèi)容，該段空間就會變?yōu)榭瞻住⒈涣艨?，而在一段時間內(nèi)的大量刪除操作，會使這種留空的空間變得比存儲列表內(nèi)容所使用的空間更大；

- 當執(zhí)行插入操作時，MySQL會嘗試使用空白空間，但如果某個空白空間一直沒有被大小合適的數(shù)據(jù)占用，仍然無法將其徹底占用，就形成了碎片；

- 當MySQL對數(shù)據(jù)進行掃描時，它掃描的對象實際是列表的容量需求上限，也就是數(shù)據(jù)被寫入的區(qū)域中處于峰值位置的部分

如：一個表有1萬行，每行10字節(jié)，會占用10萬字節(jié)存儲空間，執(zhí)行刪除操作，只留一行，實際內(nèi)容只剩下10字節(jié)，但MySQL在讀取時，仍看做是10萬字節(jié)的表進行處理，所以，碎片越多，就會越來越影響查詢性能。

#### 查看表碎片的大?。?/p>

```MySQL

show table status like '表名';

```

結果中'Data_free'列的值就是碎片大小

![img](12.11.assets/20171019111511165.png)

#### 列出所有已經(jīng)產(chǎn)生碎片的表：

```MySQL

select table_schema db, table_name, data_free, engine from information_schema.tables where table_schema not in ('information_schema', 'mysql') and data_free > 0;

```

#### 清理表碎片

1. MyISAM表：

? ```MySQL

? optimize table 表名;

? ```

2. InnoDB表：

? ```MySQL

? alter table 表名 engine = InnoDB;

? ```

? ## 三、數(shù)據(jù)庫優(yōu)化

是否需要清理碎片、上的鎖是否為表鎖

select后不要用*，不然會回表

#### 選取最適用的字段屬性

數(shù)據(jù)庫中的表越小，在它上面執(zhí)行的查詢也就會越快。因此，在創(chuàng)建表的時候，為了獲得更好的性能，我們可以將表中字段的寬度設得盡可能小

**另外一個提高效率的方法是在可能的情況下，應該盡量把字段設置為NOT NULL**，這樣在將來執(zhí)行查詢的時候，數(shù)據(jù)庫不用去比較NULL值

因為在MySQL中，ENUM類型被當作數(shù)值型數(shù)據(jù)來處理，而數(shù)值型數(shù)據(jù)被處理起來的速度要比文本類型快得多

#### 使用連接（JOIN）來代替子查詢(Sub-Queries）

使用子查詢可以一次性的完成很多邏輯上需要多個步驟才能完成的SQL操作，同時也可以避免事務或者表鎖死，并且寫起來也很容易。但是，有些情況下，子查詢可以被更有效率的連接（JOIN）替代。

#### 使用聯(lián)合(UNION)來代替手動創(chuàng)建的臨時表（不懂）

可以把需要使用臨時表的兩條或更多的select查詢合并的一個查詢中。在客戶端的查詢會話結束的時候，臨時表會被自動刪除，從而保證數(shù)據(jù)庫整齊、高效

用union來創(chuàng)建查詢的時候，我們只需要用UNION作為關鍵字把多個select語句連接起來就可以了，要注意的是所有select語句中的字段數(shù)目要想同。

#### 事務

事務原理是：要么語句塊中每條語句都操作成功，要么都失敗。換句話說，就是可以保持數(shù)據(jù)庫中數(shù)據(jù)的一致性和完整性。事物以BEGIN關鍵字開始，COMMIT關鍵字結束。在這之間的一條SQL操作失敗，那么，rollback命令就可以把數(shù)據(jù)庫恢復到BEGIN開始之前的狀態(tài)。

事務的另一個重要作用是當多個用戶同時使用相同的數(shù)據(jù)源時，它可以利用鎖定數(shù)據(jù)庫的方法來為用戶提供一種安全的訪問方式，這樣可以保證用戶的操作不被其它的用戶所干擾。

#### 鎖定表

盡管事務是維護數(shù)據(jù)庫完整性的一個非常好的方法，但卻因為它的獨占性，有時會影響數(shù)據(jù)庫的性能，尤其是在很大的應用系統(tǒng)中。由于在事務執(zhí)行的過程中，數(shù)據(jù)庫將會被鎖定，因此其它的用戶請求只能暫時等待直到該事務結束。如果一個數(shù)據(jù)庫系統(tǒng)只有少數(shù)幾個用戶來使用，事務造成的影響不會成為一個太大的問題；但假設有成千上萬的用戶同時訪問一個數(shù)據(jù)庫系統(tǒng)，就會產(chǎn)生比較嚴重的響應延遲。此時通過鎖定表的方法來獲得更好的性能

#### 使用外鍵

鎖定表的方法可以維護數(shù)據(jù)的完整性，但是它卻不能保證數(shù)據(jù)的關聯(lián)性。這個時候我們就可以使用外鍵。

#### 使用索引

索引是提高數(shù)據(jù)庫性能的常用方法，它可以令數(shù)據(jù)庫服務器以比沒有索引快得多的速度檢索特定的行，尤其是在查詢語句當中包含有MAX(),MIN()和ORDERBY這些命令的時候，性能提高更為明顯。

一般說來，索引應建立在那些將用于JOIN,WHERE判斷和ORDER BY排序的字段上。盡量不要對數(shù)據(jù)庫中某個含有大量重復的值的字段建立索引。

#### 優(yōu)化的查詢語句

絕大多數(shù)情況下，使用索引可以提高查詢的速度，但如果SQL語句使用不恰當?shù)脑?lt;/font>，索引將無法發(fā)揮它應有的作用。

- 不能將一個建有索引的int字段和bigint字段進行比較；但是作為特殊的情況，在CHAR類型的字段和VARCHAR類型字段的字段大小相同的時候，可以將它們進行比較。

- 在建有索引的字段上盡量不要使用函數(shù)進行操作

- 在搜索字符型字段時，我們有時會使用LIKE關鍵字和通配符，這種做法雖然簡單，但卻也是以犧牲系統(tǒng)性能為代價的。

## 四、慢查詢?nèi)罩?/p>

MySQL的慢查詢?nèi)罩臼荕ySQL提供的一種日志記錄，它用來記錄在MySQL中響應時間超過閥值的語句，具體指運行時間超過long_query_time（long_query_time的默認值為10，意思是運行10S以上的語句）值的SQL，則會被記錄到慢查詢?nèi)罩局小?/p>

#### 慢查詢?nèi)罩鞠嚓P參數(shù)

```MySQL

slow_query_log：是否開啟慢查詢?nèi)罩荆?表示開啟，0表示關閉。

show variables like '%slow_query_log';

slow-query-log-file：MySQL數(shù)據(jù)庫慢查詢?nèi)罩敬鎯β窂??？梢圆辉O置該參數(shù)，系統(tǒng)則會默認給一個缺省的文件host_name-slow.log

show variables like '%slow_query_log-file';

long_query_time ：慢查詢閾值，當查詢時間多于設定的閾值時，記錄日志。

show VARIABLES like 'long_query_time%';

log_queries_not_using_indexes：未使用索引的查詢也被記錄到慢查詢?nèi)罩局校蛇x項）。

show variables like '%log_queries_not_using_indexes';

```

log_output：日志存儲方式。log_output='FILE'表示將日志存入文件，默認值是'FILE'。log_output='TABLE'表示將日志存入數(shù)據(jù)庫，這樣日志信息就會被寫入到mysql.slow_log表中。MySQL數(shù)據(jù)庫支持同時兩種日志存儲方式，配置的時候以逗號隔開即可，如：log_output='FILE,TABLE'。日志記錄到系統(tǒng)的專用日志表中，要比記錄到文件耗費更多的系統(tǒng)資源，因此對于需要啟用慢查詢?nèi)罩?，又需要能夠獲得更高的系統(tǒng)性能，那么建議優(yōu)先記錄到文件。

```mysql

查詢慢查詢?nèi)罩敬鎯Ψ椒?/p>

show variables like '%log_output'

修改慢查詢?nèi)罩敬鎯ξ恢?/p>

set global log_output = 'FILE,TABLE'

打印在MySQL中慢查詢?nèi)罩镜奈恢?/p>

SELECT * FROM mysql.slow_log

```

默認情況下slow_query_log的值為OFF，表示慢查詢?nèi)罩臼墙玫?/p>

那么開啟了慢查詢?nèi)罩竞?，什么樣的SQL才會記錄到慢查詢?nèi)罩纠锩婺兀?lt;/font> 這個是由參數(shù)long_query_time控制，默認情況下long_query_time的值為10秒，可以使用命令修改，也可以在my.cnf參數(shù)里面修改。關于運行時間正好等于long_query_time的情況，并不會被記錄下來。也就是說，在mysql源碼里是判斷大于long_query_time，而非大于等于。

## 五、本日重點講解（不重要的東西）

可移植性不好，不易調(diào)試

### 1、觸發(fā)器

與表有關的數(shù)據(jù)對象，在滿足某種條件的時候，被動執(zhí)行SQL語句

指事先為某張表綁定一段代碼，當表中的某些內(nèi)容發(fā)生改變（增、刪、改）的時候，系統(tǒng)會自動觸發(fā)代碼并執(zhí)行。

#### 作用：

- 可在寫入數(shù)據(jù)前，強制檢驗或者轉換數(shù)據(jù)(保證護數(shù)據(jù)安全)

- 觸發(fā)器發(fā)生錯誤時，前面用戶已經(jīng)執(zhí)行成功的操作會被撤銷，類似事務的回滾

#### 創(chuàng)建觸發(fā)器

基本語句

```MySQL

delimiter 自定義結束符號

create trigger 觸發(fā)器名字觸發(fā)時間觸發(fā)事件 on 表 for each row

begin

? ? -- 觸發(fā)器內(nèi)容主體，每行用分號結尾

end 自定義的結束符合

```

on 表 for each：觸發(fā)對象，觸發(fā)器綁定的實質(zhì)是表中的所有行，因此當每一行發(fā)生指定改變時，觸發(fā)器就會發(fā)生

##### 觸發(fā)時間

當 SQL 指令發(fā)生時，會令行中數(shù)據(jù)發(fā)生變化，而每張表中對應的行有兩種狀態(tài)：**數(shù)據(jù)操作前和操作后**

- before：表中數(shù)據(jù)發(fā)生改變前的狀態(tài)

- after：表中數(shù)據(jù)發(fā)生改變后的狀態(tài)

**PS**：如果 before 觸發(fā)器失敗或者語句本身失敗，將不執(zhí)行 after 觸發(fā)器(如果有的話)

##### 觸發(fā)事件

觸發(fā)器是針對數(shù)據(jù)發(fā)送改變才會被觸發(fā)，對應的操作只有

- INSERT：沒有 old，只有 new，new 表示將要(插入前)或者已經(jīng)增加(插入后)的數(shù)據(jù)

- DELETE：既有 old 也有 new，old 表示更新之前的數(shù)據(jù)，new 表示更新之后的數(shù)據(jù)

- UPDATE：沒有 new，只有 old，old 表示將要(刪除前)或者已經(jīng)被刪除(刪除后)的數(shù)據(jù)

#### 查看觸發(fā)器

1.查看全部觸發(fā)器

```MySQL

show triggers;

```

2.查看觸發(fā)器的創(chuàng)建語句

```MySQL

show create trigger 觸發(fā)器名字;

```

3.觸發(fā)觸發(fā)器

```MySQL

drop trigger 觸發(fā)器名字;

```

#### 刪除觸發(fā)器

觸發(fā)器不能修改，只能刪除

```MySQL

drop trigger + 觸發(fā)器名字

```

### 2、存儲過程

#### 變量

##### 局部變量

局部變量只在BEGIN和END的代碼塊中有效，執(zhí)行完該代碼塊，變量就消失，在存儲過程中最為常見。DECLARE語句專門用于定義局部變量，可以使用DEFAULT語句來指明默認值。定義多個變量：DECLARE x, y INT DEFAULT 0。賦值用的是`SET`或`SELECT`語句：

```MySQL

DECLARE total_count INT DEFAULT 0

SET total_count = 10;

SELECT COUNT(*) INTO total_count FROM products

```

其中total_count為局部變量

##### 用戶變量

用戶變量與數(shù)據(jù)庫連接有關，一個客戶端定義的變量不能被其它客戶端看到或使用。當客戶端退出時，該客戶端連接的所有變量將失效。用戶變量不需要聲明，可以直接使用，用戶變量以@var_name的形式命名變量，變量名必須以@開頭。

賦值語法，一般用SET：

```MySQL

set @varName=value;

SELECT @var_name := value;

```

賦值后輸出用select @varName。

##### 系統(tǒng)變量

MySQL可以訪問許多系統(tǒng)變量。當服務器運行時許多變量允許動態(tài)更改。這樣就可以修改服務器操作而不需要停止并重啟服務器。系統(tǒng)變量又包含兩種：會話變量影響具體客戶端連接的操作，全局變量影響服務器整體操作。

##### 全局變量

全局變量在 MySQL 啟動的時候由服務器自動初始化他們的值, 這些默認的值可以在/etc/my.cnf中修改。要想更改全局變量，必須具有SUPER權限。全局變量作用于SERVER的整個生命周期，但重啟后所有設置的全局變量均失效。要想讓全局變量重啟后繼續(xù)生效，需要更改相應的配置文件。

```MySQL

show global variables like 'wait_timeout%';

```

```mysql

更改等待時間

set global wait_timeout=4;

```

#### 存儲過程創(chuàng)建

存儲過程是一組已經(jīng)預先編譯好的sql語句的集合，理解為批處理語句（增加流程控制語句），一般在復雜邏輯中才會使用存儲過程

存儲過程的優(yōu)點：

- 提供了代碼的可用性

- 簡化了數(shù)據(jù)庫操作，將業(yè)務邏輯的細節(jié)隱藏在存儲過程中

- 減少了編譯次數(shù)，減少了網(wǎng)絡IO的次數(shù)，從而提高操作效率

存儲過程的創(chuàng)建

```cpp

delimiter $

create procedure 存儲過程的名稱（參數(shù)列表）

begin

局部變量的定義

多條sql語句

流程控制語句

eng;$

```

如果存儲過程中只有一條sql語句可以省略begin end

參數(shù)列表

| 參數(shù)模式 | 形參名稱 | 參數(shù)類型? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |

| :------- | -------- | ------------------------------------------------- |

| in? ? ? | username | MySQL數(shù)據(jù)庫中的數(shù)據(jù)類型（數(shù)值型，字符型，日期型） |

| out? ? ? | pwd? ? ? | MySQL數(shù)據(jù)庫中的數(shù)據(jù)類型（數(shù)值型，字符型，日期型） |

| inout? ? | xxx? ? ? | MySQL數(shù)據(jù)庫中的數(shù)據(jù)類型（數(shù)值型，字符型，日期型） |

in：聲名該參數(shù)是一個輸入型參數(shù)（類似Java中的形參）

out：聲名該參數(shù)為一個輸出型參數(shù)（類型Java中的返回值），在一個存儲過程中可以定義多個out類型的參數(shù)

inout：聲名該參數(shù)可以為輸入型參數(shù)，也可以為輸出型參數(shù)

存儲過程調(diào)用

```mysql

call 存儲過程的名稱（實參列表）

-- 實參列表中包含由輸出類型的參數(shù)

```

存儲過程的演示

- 無參的存儲過程

? ```MySQL

? -- 用于b_user表中插入2條數(shù)據(jù)

? delimiter $ -- 自定義結束標志

? create procedure pro_insert()

? begin

? insert into b_user(name,sex) values('1','1');

? insert into b_user(name,sex) values('2','2');

? end;$

? -- 用這個存儲過程

? call pro_insert();

? ```

- 帶有in模式參數(shù)的存儲過程

? ```MySQL

? -- 用于向b_user插入2條數(shù)據(jù)，性別由客戶輸入

? delimiter $

? create procedure pro_insert2(in sex char(1))

? begin

? insert into b_user(name,sex) values('1',sex);

? insert into b_user(name,sex) values('2',sex);

? end;$

? call pro_insert2('男');

? ```

- 帶有多個in模式參數(shù)的存儲過程

? ```MySQL

? -- 用于向b_user插入2條數(shù)據(jù)，性別由客戶輸入

? delimiter $

? create procedure pro_insert2(in name varchar(10),in sex char(1))

? begin

? insert into b_user(name,sex) values(name,sex);

? end;$

? call pro_insert2('name','男');

? ```

- 帶in，out參數(shù)的存儲過程

? ```MySQL

? -- 判斷用戶登錄，如果用戶名和密碼輸入正確登錄成功，否則登錄失敗

? -- 根據(jù)輸入的用戶名和密碼作為條件b_user表中查詢，如果查詢總行數(shù)==1，則認為登錄成功，讓resukt返回登錄成功；否則登錄失敗

? delimiter $

? create procedure pro_login(in name varchar(20),in pwd varchar(10,out result varchar(20)))

? begin

? declare total int default 0; -- 用于存放查詢總行數(shù)

? select count(*) from b_user u where u.name= name and u.pwd= pwd; -- 將查詢結果賦值給total局部變量

? set result:= if(total=1,'登錄成功','登錄失敗');

? end;$

? -- 存儲過程如何執(zhí)行

? -- 解決判斷，使用自定義變量

? set @result:='';

? call pro_login('李四','123',@result);

? select @result;

? ```

- 刪除存儲過程

? ```MySQL

? drop procedure 存儲過程名稱

? ```

- 查看存儲過程

? ```MySQL

? show create procedure 存儲過程名稱;

? ```

- 修改存儲過程

? ```MySQL

? drop

? create

? ```

#### 流程控制語句

- if函數(shù)結構

? ```cpp

? /*

? if 邏輯表達式 then 語句1;

? elseif 邏輯表達式2 then 語句2;

? …………

? else 語句n;

? end if;

? */

? ```

- case結構

? 等值選擇

? ```MySQL

? case 字段|變量|表達式

? when 值 then 值|語句

? when 值 then 值

? …………

? else 值

? end

? ```

? 不等值選擇

? ```MySQL

? case

? when 邏輯表達式 then 語句1

? ……

? else 語句n

? end

? ```

- 循環(huán)結構

? while

? ```mysql

? /*

? while 邏輯表達式 do

? 循環(huán)體

? end while;

? */

? -- 需求：創(chuàng)建存儲過程，輸入一個值，返回1到該值的和

? delimiter $

? create procedure pro_sum(in input int,out total int)

? begin

? declare i int default 1;

? declare sum_ int default 0;

? while i<=input do

? set sum_=sum_+i;

? set i=i+1;

? end while;

? set total:=sum_;

? end;$

? set @result:=0;

? call pro_sum(10,@result);

? select @result;

? ```

- repeat

? ```mysql

? repeat

? 循環(huán)體

? until 邏輯表達式 -- 當滿足邏輯表達式，跳出循環(huán)

? end repeat;

? delimiter $

? create procedure pro_sum_loop(in input int,out total int)

? begin

? declare i int default 1;

? declare sum_ int default 0;

? repeat

? set sum_:=sum_+i;

? set i:=i+1;

? until i>input

? end REPEAT;

? set total:=sum_;

? end;$

? set @result:=0;

? call pro_sum(10,@result);

? select @result;

? ```

### 3、存儲函數(shù)

##### 1. 存儲函數(shù):

- 函數(shù)(Function)為一命名的存儲程序,可帶參數(shù),并返回一計算值.

- 函數(shù)和過程的結構類似,但必須有一個return子句,用于返回函數(shù)值.

##### 2. 創(chuàng)建存儲函數(shù)的語法:

```kotlin

create [or replace] function 函數(shù)名(參數(shù)列表)

return 函數(shù)值類型

PLSQL子程序體;

```

注意事項:

- (1) 與存儲過程注意事項類似,不同的是,**必須有個返回值**;

- (2) 參數(shù)列表可以有,也可以沒有.當沒有時,函數(shù)名后面不要帶括號.

```rust

create or replace function queryempannal(pempno in number)

return number

? psal emp.sal%type;

? pcomm emp.comm%type;

begin

? select sal,comm into psal,pcomm from emp where empno=pempno;

? return psal*12+nvl(pcomm,0);

end;

```

刪除函數(shù)：

```MySQL

drop function 函數(shù)名;

```

### 4、定時任務

#### 查看任務是否開啟

```MySQL

show variables like '%event_sche%';

```

開啟定時策略

```MySQL

set global event_scheduler=1;

```

#### 創(chuàng)建定時任務

```MySQL

create event run_event

on schedule every 1 minute

on completion preserve disable

do call test_procedure ();

```

- create event day_event：是創(chuàng)建名為run_event的事件

- 創(chuàng)建周期定時的規(guī)則，意思是每分鐘執(zhí)行一次

- on completion preserve disable是表示創(chuàng)建后并不開始生效。

- do call test_procedure ()是該event(事件)的操作內(nèi)容

#### 定時任務操作

查看

```MySQL

SELECT event_name,event_definition,interval_value,interval_field,status

FROM information_schema.EVENTS;

```

開啟或關閉

```MySQL

alter event run_event on completion preserve enable;//開啟定時任務

alter event run_event on completion preserve disable;//關閉定時任務

```

#### 定時規(guī)則

周期執(zhí)行–關鍵字 EVERY

單位有：second、minute、hour、day、week(周)、quarter(季度)、month、year

```MySQL

on schedule every 1 week //每周執(zhí)行1次

```

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av

2021-01-01

2021-01-01

相關閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九 欧美,1769亚洲,黄色成人av

2021-01-01

相關閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av