數(shù)據(jù)分析工作中，免不了與SQL數(shù)據(jù)庫打交道，尤其是對(duì)庫表的使用，所以如何對(duì)庫表進(jìn)行創(chuàng)建、修改和刪除，是一項(xiàng)基礎(chǔ)技能。

 

DDL（DataDefinition Language的簡(jiǎn)寫形式）是SQL語言集中負(fù)責(zé)數(shù)據(jù)結(jié)構(gòu)定義與數(shù)據(jù)庫對(duì)象定義的語言，由CREATE、ALTER與DROP三個(gè)語法所組成。

 

接下來分為庫、表兩部分給出示例代碼供讀者學(xué)習(xí)了解。

 

• 庫相關(guān)操作

# 創(chuàng)建數(shù)據(jù)庫  
CREATE DATABASE testdatabase;  
# 選擇數(shù)據(jù)庫  
USE testdatabase;  
# 刪除數(shù)據(jù)庫  
DROP DATABASE testdatabase;  
# 列出已有數(shù)據(jù)庫  
SHOW DATABASES;  

以上操作語句如果是在命令行中執(zhí)行，需注意要以分號(hào)結(jié)束。

 

CREATE DATABASE關(guān)鍵字后面的testdatabase就是新創(chuàng)建的庫名，庫名需要是唯一的，也就意味著不能和已經(jīng)存在的庫重名。USE testdatabase命令的作用就是切換到testdatabase庫下進(jìn)行后續(xù)操作。SHOW DATABASES會(huì)列出所有當(dāng)前用戶能訪問到的數(shù)據(jù)庫庫名。

 

• 表相關(guān)操作

 

# 創(chuàng)建表 
CREATE TABLE test1 ( 
    id INT unsigned NOT NULL AUTO_INCREMENT, 
    name VARCHAR(225), 
    price DECIMAL(10,2), 
    PRIMARY KEY (id) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
# 刪除表 
DROP TABLE test1; 
# 修改表字段類型 
ALTER TABLE test1 MODIFY name VARCHAR(100); 
# 添加表字段 
ALTER TABLE test1 ADD COLUMN age INT(3) FIRST; 
# 刪除表字段 
ALTER TABLE test1 DROP age; 
# 修改表字段名稱 
ALTER TABLE test1 CHANGE age age2 INT(20); 
# 表重命名 
ALTER TABLE test1 RENAME test2; 
# 查看表結(jié)構(gòu) 
DESC test2; 

同樣，以上操作語句如果是在命令行中執(zhí)行，需注意要以分號(hào)結(jié)束。

 

CREATE TABLE 的時(shí)候，要求新指定的表名必須不存在，否則會(huì)出錯(cuò)，這主要是為了防止意外覆蓋已有的表。

 

ALTER TABLE后面給出的要更改信息的表名必須存在，否則將報(bào)錯(cuò)。使用ALTERTABLE要極為小心，應(yīng)該在進(jìn)行改動(dòng)前做完整的備份（表結(jié)構(gòu)和數(shù)據(jù)的備份），增加列會(huì)對(duì)數(shù)據(jù)存儲(chǔ)造成影響，因此要盡量避免此類操作。

 

類似地，如果刪除了不應(yīng)該刪除的列，可能會(huì)丟失該列中的所有數(shù)據(jù)。刪除表操作同樣無法撤銷，所以執(zhí)行該操作之前需十分謹(jǐn)慎小心。

 

另外和大家分享下，工作中常用的建表小技巧：

 

1. 創(chuàng)建表時(shí)，盡量使用一個(gè)自增的整型字段做主鍵。這樣做，如果后續(xù)需要使用spark等框架分析這個(gè)表時(shí)，是非常方便的。

 

2. 創(chuàng)建表時(shí)，可以增加兩個(gè)字段create_time和update_time。create_time存儲(chǔ)記錄的創(chuàng)建時(shí)間，update_time存儲(chǔ)記錄的最后一次變更時(shí)間，方便后續(xù)排查數(shù)據(jù)的變更情況。如果是使用MySQL，需要5.7及以上版本，具體語法示例如下：

CREATE TABLE test1 ( 
    id INT  unsigned NOT NULL AUTO_INCREMENT, 
    name VARCHAR(225), 
    price DECIMAL(10,2), 
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
    update_time TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
    PRIMARY KEY (id) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

3. 可以考慮增加一個(gè)邏輯刪除列，存儲(chǔ)記錄的生效狀態(tài)。這樣在刪除數(shù)據(jù)時(shí)，可以進(jìn)行邏輯刪除，即把狀態(tài)為改為失效，而不是真的把數(shù)據(jù)刪掉。

MySQL索引的建立對(duì)于MySQL的高效運(yùn)行是很重要的，索引可以大大提高M(jìn)ySQL的檢索速度。主要分為兩種類型，單列索引和組合索引。

 

接下來，我們一起來看下如何創(chuàng)建不同類型的索引：

 

• 建表時(shí)創(chuàng)建

語法：

CREATE TABLE 表名( 
字段名 數(shù)據(jù)類型 [完整性約束條件], 
       ……， 
[UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY 
[索引名](字段名1 [(長度)] [ASC | DESC]) [USING 索引方法] 
); 
  
說明： 
UNIQUE:可選。表示索引為唯一性索引。 
FULLTEXT:可選。表示索引為全文索引。 
SPATIAL:可選。表示索引為空間索引。 
INDEX和KEY:用于指定字段為索引，兩者作用是一樣的。 
索引名:可選。給創(chuàng)建的索引取一個(gè)新名稱。 
字段名1:指定索引對(duì)應(yīng)的字段的名稱，該字段必須是前面定義好的字段。 
長度:可選。指索引的長度，必須是字符串類型才可以使用。 
ASC:可選。表示升序排列。 
DESC:可選。表示降序排列。 
  
BTree是最常見的索引方法，所有值（被索引的列）都是排過序的，每個(gè)葉節(jié)點(diǎn)到根節(jié)點(diǎn)距離相等。所以BTree適合用來查找某一范圍內(nèi)的數(shù)據(jù)，而且可以直接支持?jǐn)?shù)據(jù)排序（ORDER BY）。還有其他幾種索引方法，讀者可自行百度了解一下。 

建表時(shí)創(chuàng)建單列索引和組合索引示例：

CREATE TABLE classInfo( 
    id INT AUTO_INCREMENT COMMENT 'id', 
    classname VARCHAR(128) COMMENT '課程名稱', 
    classid INT COMMENT '課程id', 
    classtype VARCHAR(128) COMMENT '課程類型', 
    classcode VARCHAR(128) COMMENT '課程代碼', 
-- 主鍵本身也是一種索引 
    PRIMARY KEY (id), 
-- 給classid字段創(chuàng)建了唯一索引(注:也可以在上面創(chuàng)建字段時(shí)使用unique來創(chuàng)建唯一索引) 
    UNIQUE INDEX (classid), 
-- 給classname字段創(chuàng)建普通索引 
    INDEX (classname), 
-- 創(chuàng)建組合索引 
    INDEX (classtype,classcode) 
-- 指定使用INNODB存儲(chǔ)引擎(該引擎支持事務(wù))、utf8字符編碼 
) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT '課程明細(xì)表'; 

• 建表后創(chuàng)建

語法:

ALTER TABLE 表名 ADD [UNIQUE| FULLTEXT | SPATIAL]  INDEX | KEY  [索引名] (字段名1 [(長度)] [ASC | DESC]) [USING 索引方法]； 
或 
CREATE  [UNIQUE | FULLTEXT | SPATIAL] INDEX  索引名 ON  表名(字段名) [USING 索引方法] 

建表后創(chuàng)建單列索引和組合索引示例：

--將id列設(shè)置為主鍵 
ALTER TABLE classInfo ADD PRIMARY KEY(id) ; 
--給classInfo表中的classid創(chuàng)建唯一索引 
ALTER TABLE classInfo ADD UNIQUE INDEX (classid); 
--給classInfo表中的classname創(chuàng)建普通索引 
ALTER TABLE classInfo ADD INDEX (classname); 
--給classInfo表中的classtype和classcode創(chuàng)建組合索引 
ALTER TABLE classInfo ADD INDEX (classtype,classcode); 

索引建立以后，來看下如何對(duì)索引進(jìn)行查看和刪除操作。

 

查看：

show index from classInfo; 

結(jié)果：

刪除：

DROP INDEX 索引名 ON 表名 
或 
ALTER TABLE 表名 DROPINDEX 索引名 

示例：

drop index classname on classInfo; 
alter table classInfo drop index classid; 

索引的優(yōu)點(diǎn)：

 

1. 大大加快數(shù)據(jù)的查詢速度
2. 使用索引字段分組和排序進(jìn)行數(shù)據(jù)查詢時(shí)，可以顯著減少查詢時(shí)分組和排序的時(shí)間
3. 創(chuàng)建唯一索引，能夠保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性
4. 在實(shí)現(xiàn)數(shù)據(jù)的參考完整性方面，可以加速表和表之間的連接

索引的缺點(diǎn)：

 

1. 創(chuàng)建索引和維護(hù)索引需要消耗時(shí)間，并且隨著數(shù)據(jù)量的增加，時(shí)間也會(huì)增加
2. 索引需要占據(jù)磁盤空間
3. 對(duì)數(shù)據(jù)表中的數(shù)據(jù)進(jìn)行增加，修改，刪除時(shí)，索引也要?jiǎng)討B(tài)的維護(hù)，降低了維護(hù)的速度

創(chuàng)建索引的原則：

 

1. 更新頻繁的列不應(yīng)設(shè)置索引
2. 數(shù)據(jù)量小的表不要使用索引（畢竟總共2頁的文檔，還要目錄嗎？）
3. 重復(fù)數(shù)據(jù)多的字段不應(yīng)設(shè)為索引（比如性別，只有男和女，一般來說：重復(fù)的數(shù)據(jù)超過百分之十五就不適合建索引）
4. 首先應(yīng)該考慮對(duì)where 和 order by 使用的列上建立索引

如果一個(gè)SQL執(zhí)行緩慢，遠(yuǎn)低于預(yù)期，我們?cè)撛趺慈?yōu)化它呢？

 

關(guān)于這個(gè)問題，MySQL提供了一個(gè)explain命令，它可以對(duì)select語句進(jìn)行分析，并輸出SQL執(zhí)行的詳細(xì)過程和細(xì)節(jié)信息，以供開發(fā)人員進(jìn)行針對(duì)性的優(yōu)化。

 

explain的語法很簡(jiǎn)單，首先我們通過一個(gè)簡(jiǎn)單的sql查詢來了解一下：

explain select * from user_info where id = 2 

其返回結(jié)果如下：

返回的每一個(gè)字段代表什么意思呢？

 

簡(jiǎn)單總結(jié)一下：

id: SELECT 查詢的標(biāo)識(shí)符. 每個(gè) SELECT 都會(huì)自動(dòng)分配一個(gè)唯一的標(biāo)識(shí)符. 
select_type: SELECT 查詢的類型. 
table: 查詢的是哪個(gè)表. 
type: 訪問類型. 
possible_keys: 此次查詢中可能選用的索引. 
key: 此次查詢中確切使用到的索引，如果沒有選擇索引,鍵是NULL. 
key_len:表示查詢優(yōu)化器使用了索引的字節(jié)數(shù). 這個(gè)字段可以評(píng)估組合索引是否完全被使用, 或只有最左部分字段被使用到，如果鍵是NULL,則長度為NULL。 
ref: 哪個(gè)字段或常數(shù)與key一起被使用. 
rows: 顯示此查詢一共掃描了多少行. 這個(gè)是一個(gè)估計(jì)值. 
extra: 額外的信息. 

以上各個(gè)字段中，我們來重點(diǎn)講解下select_type、type和extra，其他字段通過以上注釋相信大家已經(jīng)基本能夠理解其含義了。

 

• select_type

表示查詢的類型，它的常用取值有:

 

（1）SIMPLE，表示此查詢不包含 UNION 查詢或子查詢。示例見上文。

 

（2）PRIMARY，表示此查詢是最外層的查詢；

DEPENDENT UNION，子查詢UNION語句的第二個(gè)或后面的SELECT，取決于外面的查詢， 即子查詢依賴于外層查詢的結(jié)果；

DEPENDENT SUBQUERY，子查詢中的第一個(gè) SELECT，取決于外面的查詢，即子查詢依賴于外層查詢的結(jié)果；

UNION RESULT， UNION 語句的結(jié)果集；

 

示例代碼如下，相同顏色標(biāo)示sql語句與select_type值的相對(duì)應(yīng)。

（3）UNION, 表示此查詢是使用UNION語句的第二個(gè)或后面的SELECT

（4）SUBQUERY, 子查詢中的第一個(gè) SELECT

那么DEPENDENT UNION和UNION， DEPENDENT SUBQUERY與SUBQUERY之間有什么區(qū)別呢？

 

顧名思義，關(guān)鍵點(diǎn)就在于DEPENDENT了，它的作用在于標(biāo)示子查詢依賴于外層查詢的結(jié)果。

 

在以上第（2）點(diǎn)示例中，內(nèi)部“student_info.user_id=user_info.id” 與“order_info.user_id=user_info.id”條件會(huì)自動(dòng)添加到UNION所使用的SELECT查詢的WHERE條件，然后再執(zhí)行。

 

由于外部定義的user_info數(shù)據(jù)表的id數(shù)據(jù)列要在子查詢中使用，所以DEPENDENT UNION和DEPENDENT SUBQUERY關(guān)鍵字出現(xiàn)在select_type中。

 

• type

type表示的是訪問類型，以上示例中，已經(jīng)出現(xiàn)了幾種type，接下來將常見type值及含義匯總一下：

類型 說明 All 最壞的情況,全表掃描 index 和全表掃描一樣。只是掃描表的時(shí)候按照索引次序進(jìn)行而不是行。主要優(yōu)點(diǎn)就是避免了排序, 但是開銷仍然非常大。如在Extra列看到Using index，說明正在使用覆蓋索引，只掃描索引的數(shù)據(jù)，它比按索引次序全表掃描的開銷要小很多。 range 表示使用索引范圍查詢, 通過索引字段范圍獲取表中部分?jǐn)?shù)據(jù)記錄. 這個(gè)類型通常出現(xiàn)在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中。 ref 一種索引訪問，它返回所有匹配某個(gè)單個(gè)值的行。此類索引訪問只有當(dāng)使用非唯一性索引或唯一性索引非唯一性前綴時(shí)才會(huì)發(fā)生。這個(gè)類型跟eq_ref不同的是，它用在關(guān)聯(lián)操作只使用了索引的最左前綴，或者索引不是UNIQUE和PRIMARY KEY。ref可以用于使用=或<=>操作符的帶索引的列。 eq_ref 最多只返回一條符合條件的記錄。此類型通常出現(xiàn)在多表的 join 查詢, 表示對(duì)于前表的每一個(gè)結(jié)果, 都只能匹配到后表的一行結(jié)果. 并且查詢的比較操作通常是 =, 查詢效率較高 const 當(dāng)確定最多只會(huì)有一行匹配的時(shí)候，MySQL優(yōu)化器會(huì)在查詢前讀取它而且只讀取一次，因此非?？臁．?dāng)主鍵放入where子句時(shí)，mysql把這個(gè)查詢轉(zhuǎn)為一個(gè)常量（高效） system 這是const連接類型的一種特例，表僅有一行滿足條件。 Null 意味說mysql能在優(yōu)化階段分解查詢語句，在執(zhí)行階段甚至用不到訪問表或索引（高效）

Null >system > const > eq_ref > ref > range > index > ALL ，一般來說，得保證查詢至少達(dá)到range級(jí)別，最好能達(dá)到ref。結(jié)果值從好到壞依次是：

 

 

• extra

EXPLAIN 中的很多額外的信息會(huì)在 Extra 字段顯示, 常見的是以下四種：

類型 說明 Using filesort 當(dāng) Extra 中有 Using filesort 時(shí), 表示 MySQL 需額外的排序操作, 不能通過索引順序達(dá)到排序效果. 一般有 Using filesort, 建議進(jìn)行代碼優(yōu)化, 因?yàn)檫@樣的查詢 CPU 資源消耗大。 Using index "覆蓋索引掃描", 表示查詢?cè)谒饕龢渲芯涂刹檎宜钄?shù)據(jù), 不用掃描表數(shù)據(jù)文件。 Using temporary 查詢有使用臨時(shí)表, 一般出現(xiàn)于排序, 分組和多表 join 的情況, 查詢效率不高, 建議優(yōu)化。 Using where 這說明服務(wù)器在存儲(chǔ)引擎收到行后將進(jìn)行過濾。有些where中的條件會(huì)有屬于索引的列，當(dāng)它讀取使用索引的時(shí)候，就會(huì)被過濾，所以會(huì)出現(xiàn)有些where語句并沒有在extra列中出現(xiàn)using where這么一個(gè)說明。

本章節(jié)之前給出的示例中，有出現(xiàn)Using index和Using where，關(guān)于另外兩種的使用示例讀者感興趣的話可上網(wǎng)百度了解一下，這里就不再繼續(xù)舉例說明了。

 

能夠看懂explain的輸出，是對(duì)SQL或表結(jié)構(gòu)進(jìn)行優(yōu)化的前提。所以，大家需要首先看懂并理解explain輸出內(nèi)容所包含的信息，進(jìn)而優(yōu)化實(shí)現(xiàn)更加高效的查詢。

主從同步，簡(jiǎn)單來說就是將一個(gè)服務(wù)器上的數(shù)據(jù)同步到另一個(gè)服務(wù)器上。

 

數(shù)據(jù)所在的服務(wù)器被稱為主服務(wù)器（Master），接受數(shù)據(jù)拷貝的服務(wù)器被稱為從服務(wù)器（Slave）。

 

主從同步主要有以下好處：

 

1. 數(shù)據(jù)備份：主服務(wù)器上的數(shù)據(jù)出現(xiàn)問題后，可通過從服務(wù)器數(shù)據(jù)進(jìn)行恢復(fù)；
2. 提高主服務(wù)器的性能：在主服務(wù)器上生成實(shí)時(shí)數(shù)據(jù)，而在從服務(wù)器上分析這些數(shù)據(jù)；
3. 提高整個(gè)數(shù)據(jù)庫服務(wù)的性能：在主服務(wù)器上執(zhí)行寫入和更新，在從服務(wù)器上向外提供讀功能，可以動(dòng)態(tài)地調(diào)整從服務(wù)器的數(shù)量，從而提升整個(gè)數(shù)據(jù)庫的性能；

為什么要單獨(dú)強(qiáng)調(diào)一下mysql的主從同步機(jī)制，那是因?yàn)樵趯?shí)際數(shù)據(jù)分析工作中，當(dāng)我們通過hive、spark等分布式框架去訪問mysql數(shù)據(jù)庫的時(shí)候，此時(shí)的分布式讀取會(huì)對(duì)服務(wù)器產(chǎn)生很大的壓力，如果直接讀取主庫的話，極可能會(huì)導(dǎo)致正在運(yùn)行的主庫線上任務(wù)暫停幾分鐘，進(jìn)而對(duì)線上業(yè)務(wù)造成不良影響。

 

所以，一般建議盡量通過從庫進(jìn)行數(shù)據(jù)讀取，避免對(duì)線上服務(wù)造成損害。

 

由于主從同步相關(guān)操作平時(shí)都是運(yùn)維或者DBA他們?cè)诰S護(hù)，作為數(shù)據(jù)分析人員很少會(huì)需要直接實(shí)現(xiàn)這些，所以這里對(duì)如何實(shí)現(xiàn)主從同步等相關(guān)知識(shí)點(diǎn)就不展開細(xì)講，感興趣的小伙伴，可參考一下鏈接，了解一下。

 

https://blog.csdn.net/qq_15092079/article/details/81672920

 

上面提到主從同步是一種實(shí)時(shí)的數(shù)據(jù)備份方案，通常我們還會(huì)定時(shí)去對(duì)數(shù)據(jù)庫做數(shù)據(jù)備份。

 

其目的，是為了防止執(zhí)行一些災(zāi)難性操作后，數(shù)據(jù)仍然可以恢復(fù)。

 

比如說，刪庫刪表。這是因?yàn)橹鲝耐剑ǔＪ遣捎猛讲僮髡Z句的方式，進(jìn)行庫表結(jié)構(gòu)和數(shù)據(jù)拷貝的。因此，如果主庫執(zhí)行刪除數(shù)據(jù)庫或表的操作，從庫也會(huì)同步刪除。如果有定時(shí)備份的數(shù)據(jù)文件，出現(xiàn)這種情況，只需要把數(shù)據(jù)反向?qū)氲綌?shù)據(jù)庫中，就可以恢復(fù)。

 

mysql提供的數(shù)據(jù)備份的命令為mysqldump，通常是由DBA或者運(yùn)維來進(jìn)行備份操作，大家只需要知道這個(gè)知識(shí)點(diǎn)即可，方面平時(shí)溝通交流。

 

另外大家要有備份的意識(shí)，備份操作是十分必要的，相當(dāng)于后悔藥。在筆者的公司，就發(fā)生過幾次數(shù)據(jù)誤刪的問題，都是通過備份完成恢復(fù)的。

show [full] processlist 能顯示用戶正在運(yùn)行的線程，這在數(shù)據(jù)分析工作中對(duì)于協(xié)助故障診斷非常有幫助。

 

full關(guān)鍵字，可以不寫，如果加上后，會(huì)把正在執(zhí)行的SQL語句完整打印出來。

 

我們來直接執(zhí)行一下看看能返回哪些信息：

結(jié)果中的每個(gè)字段含義總結(jié)如下：

字段 說明 Id 就是這個(gè)線程的唯一標(biāo)識(shí)，當(dāng)我們發(fā)現(xiàn)這個(gè)線程有問題的時(shí)候，可以通過 kill 命令，加上這個(gè)Id值將這個(gè)線程殺掉. User 該線程的所屬用戶. Host 記錄了發(fā)送請(qǐng)求的客戶端的 IP 和 端口號(hào). db 當(dāng)前執(zhí)行的命令是在哪一個(gè)數(shù)據(jù)庫上。如果沒有指定數(shù)據(jù)庫，則該值為 NULL. Command 是指此刻該線程正在執(zhí)行的命令. Time 表示該線程處于當(dāng)前狀態(tài)的時(shí)間. State 線程的狀態(tài). Info 一般記錄的是線程執(zhí)行的語句。默認(rèn)只顯示前100個(gè)字符，也就是你看到的語句可能是截?cái)嗔说?，要看全部信息，需要使?show full processlist.

有一種情況，需要大家重點(diǎn)注意下，就是Command中出現(xiàn)Waiting for ... lock字眼時(shí)，表示有語句把庫或表給鎖住了。

 

通常這個(gè)時(shí)候，相關(guān)的操作庫或表的程序就會(huì)處于假死狀態(tài)，表現(xiàn)為程序卡住不動(dòng)。這時(shí)就需要聯(lián)系DBA或運(yùn)維看下是什么原因?qū)е骆i庫或鎖表。所以，在你排查程序假死的問題時(shí)，如果程序有使用數(shù)據(jù)庫，可以考慮是不是這個(gè)因素導(dǎo)致的。

 

注：show processlist 顯示的信息都是來自MySQL系統(tǒng)庫 information_schema 中的 processlist 表。所以使用下面的查詢語句可以獲得相同的結(jié)果：

select * from information_schema.processlist 

數(shù)據(jù)分析工作中的常用操作：

 

1.按客戶端 IP 分組，看哪個(gè)客戶端的鏈接數(shù)最多

select client_ip,count(client_ip) as client_num  
from ( 
     selectsubstring_index(host,':' ,1) as client_ip  
     fromprocesslist ) as connect_info  
group by client_ip  
order by client_num desc; 

2.查看正在執(zhí)行的線程，并按 Time 倒排序，看看有沒有執(zhí)行時(shí)間特別長的線程

select *  
from information_schema.processlist  
where Command != 'Sleep'  
order by Time desc; 

3.找出所有執(zhí)行時(shí)間超過 5 分鐘的線程，拼湊出 kill 語句，方便后面查殺

select concat('kill ', id, ';') 
from information_schema.processlist  
where Command != 'Sleep' and Time > 300 
order by Time desc; 

MySQL默認(rèn)設(shè)置下，一個(gè)連接最長等待時(shí)間為8小時(shí)，如果8小時(shí)都處于空閑狀態(tài)，就會(huì)出現(xiàn)連接超時(shí)問題，在使用MySQL時(shí)相信大家或多或少都會(huì)遇到這種狀況，這里跟大家分享下在遇到這種情況時(shí)，通常采取的措施和解決辦法。

 

首先，查看問題，看下wait_timeout的取值。

 

打開MySQL的控制臺(tái)，運(yùn)行showvariables like '%timeout%'，查看和連接時(shí)間有關(guān)的MySQL系統(tǒng)變量。

 

然后，解決問題，解決方式常用的有三種。

 

1. 增加 MySQL 的 wait_timeout 屬性的值

//修改mysql配置文件，重啟后生效 
wait_timeout = 31536000 
or  
//通過mysql命令修改 
mysql> set wait_timeout= 31536000; 

2. 減少連接池內(nèi)連接的生存周期

 

通過代碼配置，讓線程在mysql提示超時(shí)前回收，并重新連接。以下舉例為c3p0連接池的配置，其他連接池（如Druid、Dbcp）原理類似。

 

修改 c3p0 的配置文件，在 Spring 的配置文件中設(shè)置：

<beanid="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">       
    <property name="maxIdleTime"value="1800"/>   
    <!--other properties -->   
 </bean> 

3. 定期使用連接池內(nèi)的連接

 

定期使用連接池內(nèi)的連接，使得它們不會(huì)因?yàn)殚e置超時(shí)而被 MySQL 斷開。

 

修改 c3p0 的配置文件，在 Spring的配置文件中設(shè)置：

<beanid="dataSource"class="com.mchange.v2.c3p0.ComboPooledDataSource">   
    <propertyname="preferredTestQuery" value="SELECT 1"/>   
    <propertyname="idleConnectionTestPeriod" value="18000"/>   
    <propertyname="testConnectionOnCheckout" value="true"/>   
</bean> 

普通語言里的布爾型只有 true 和 false 兩個(gè)值，這種邏輯體系被稱為二值邏輯。而 SQL 語言里，除此之外還有第三個(gè)值NULL，因此這種邏輯體系被稱為三值邏輯。

 

本章節(jié)對(duì)于NULL值這部分的講解重點(diǎn)在于提醒大家對(duì)NULL 使用比較謂詞后得到的結(jié)果總是 NULL 。

 

這是因?yàn)?，NULL 既不是值也不是變量。NULL 只是一個(gè)表示“沒有值”的標(biāo)記，而比較謂詞只適用于值。因此，對(duì)并非值的 NULL 使用比較謂詞本來就是沒有意義的。比如如下幾種比較，返回的結(jié)果均是NULL 。

1 = NULL 
2 > NULL 
3 < NULL 
4 <> NULL 
NULL = NULL 

所以，當(dāng)SQL語句的where條件里有一個(gè)字段（比如age）有NULL值，用該字段用于謂詞比較判斷的時(shí)候，比如 age <> 30，表面上理解起來age字段中的NULL值跟30不等，那這個(gè)where條件返回的應(yīng)該是true，從而age字段為NULL的記錄應(yīng)該會(huì)被保留下來，實(shí)際上不是的，它們比較后返回的結(jié)果是NULL ，age字段為NULL的記錄會(huì)被過濾掉。

 

因此，要想留下NULL值，正確的寫法為，age <> 30 or age is null。在沒有學(xué)到這個(gè)知識(shí)點(diǎn)之前，這樣進(jìn)行數(shù)據(jù)過濾容易導(dǎo)致提取出來的數(shù)據(jù)結(jié)果與預(yù)期有偏差。

作為上一篇sql基礎(chǔ)的補(bǔ)充，結(jié)合實(shí)際工作經(jīng)驗(yàn)，給大家分享一下經(jīng)常用到的更深層一點(diǎn)的sql技能，包括有DDL、索引、EXPLAIN、主從同步、數(shù)據(jù)備份、show processlist、wait_timeout和NULL值判斷，希望大家有所收獲哦！