最近項(xiàng)目當(dāng)中一直沒有注意數(shù)據(jù)庫(kù)連接池的問(wèn)題
今天查了些資料鹏秋。做一個(gè)小總結(jié)

從程序當(dāng)中看連接

• Engine Configuration

Engine 是訪問(wèn)數(shù)據(jù)庫(kù)的入口，Engine引用Connection Pool和 Dialect實(shí)現(xiàn)了對(duì)數(shù)據(jù)庫(kù)的訪問(wèn), Dialect指定了具體的數(shù)據(jù)庫(kù)類型 MYSQL, SQLSERVER等, 三者關(guān)系如圖所示:

file-list

只有當(dāng)調(diào)用connect(),execute()函數(shù)的時(shí)候窍仰，才會(huì)創(chuàng)建數(shù)據(jù)庫(kù)的連接

create_engine

使用 create_engine創(chuàng)建我們需要的DB starting point

from sqlalchemy import create_engine

scheme = 'mysql+pymysql://root:123456@localhost:3306/dev_shopping?charset=utf8'
engine = create_engine(scheme, pool_size=10 , max_overflow=-1, pool_recycle=1200)

create_engine 函數(shù)常用參數(shù)：

pool_size=10 # 連接池的大小，0表示連接數(shù)無(wú)限制

pool_recycle=-1 # 連接池回收連接的時(shí)間，如果設(shè)置為-1阱飘，表示沒有no timeout, 注意，mysql會(huì)自動(dòng)斷開超過(guò)8小時(shí)的連接虱颗，所以sqlalchemy沿用被mysql斷開的連接會(huì)拋出MySQL has gone away

max_overflow=-1 # 連接池中允許‘溢出’的連接個(gè)數(shù)沥匈，如果設(shè)置為-1，表示連接池中可以創(chuàng)建任意數(shù)量的連接

pool_timeout=30 # 在連接池獲取一個(gè)空閑連接等待的時(shí)間

echo=False # 如果設(shè)置True, Engine將會(huì)記錄所有的日志忘渔，日志默認(rèn)會(huì)輸出到sys.stdout

創(chuàng)建Engine之后高帖，接下來(lái)的問(wèn)題，就是如何使用Engine

在單進(jìn)程中畦粮，建議在在初始化的模塊的時(shí)候創(chuàng)建Engine, 使Engine成為全局變量散址， 而不是為每個(gè)調(diào)用Engine的對(duì)象或者函數(shù)中創(chuàng)建, Engine不同于connect, connect函數(shù)會(huì)創(chuàng)建數(shù)據(jù)庫(kù)連接的資源，Engine是管理connect創(chuàng)建的連接資源

在多進(jìn)程中宣赔，為每個(gè)子進(jìn)程都創(chuàng)建各自的Engine, 因?yàn)檫M(jìn)程之間是不能共享Engine

connect

使用connect 創(chuàng)建連接數(shù)據(jù)庫(kù)資源, 如上所說(shuō)预麸，即使創(chuàng)建了Engine, 還是沒有創(chuàng)建對(duì)數(shù)據(jù)庫(kù)的連接，調(diào)用connect才會(huì)創(chuàng)建真正的連接

connection = engine.connect()
result = connection.execute("select * from tmp")
print type(result) # <class 'sqlalchemy.engine.result.ResultProxy'>
for row in result:
    print "target_name:", row['target_name']
connection.close()

這里有兩個(gè)問(wèn)題需要搞清楚儒将，result返回對(duì)象類型和對(duì)象提供的方法吏祸，第二個(gè)是close函數(shù)調(diào)用之后，發(fā)生了什么事情钩蚊，先說(shuō)close

close

當(dāng)調(diào)用connection.close()之后贡翘，由connect函數(shù)創(chuàng)建的連接會(huì)被釋放到連接池中, 可以供下次使用.

上面這段代碼可以簡(jiǎn)寫為:

result = engine.execute("select username from users")
for row in result:
    print "username:", row['username']

execute函數(shù)會(huì)創(chuàng)建自己的連接蹈矮，并執(zhí)行聲明的sql語(yǔ)句，返回ResultProxy對(duì)象鸣驱，在這個(gè)情況下泛鸟，ResultProxy會(huì)有個(gè)標(biāo)記close_with_result， 如果ResultProxy的值被全部取出來(lái)踊东，Engine會(huì)自動(dòng)close本次連接北滥，并把連接釋放到連接池里面去

如果ResultProxy里面還有數(shù)據(jù)沒有取出來(lái)(rows remaining)，可使用result.close()釋放本次連接递胧，如果沒有使用result.close()釋放連接碑韵，python garbage collection 最終為釋放本次連接到連接池中

ResultProxy

現(xiàn)在來(lái)看一下execute()執(zhí)行之后返回的結(jié)果類型 詳細(xì)文檔。

常用的API如下：

fetchone() 取出一行缎脾， 當(dāng)所有的行被取出來(lái)之后 connect resource會(huì)被釋放到連接池中祝闻，再次調(diào)用fetchone()將返回None

result = connection.execute("select * from tmp")

row = result.fetchone()
print row[0] # access via integer position
print row['id'] # access via name
print type(row) # <class 'sqlalchemy.engine.result.RowProxy'>

# 類似還有
first()  獲取第一行，同時(shí)無(wú)條件的釋放連接
scalar() 獲取第一行第一列的數(shù)據(jù)遗菠，同時(shí)無(wú)條件的釋放連接
rowcount 獲取row count
lastrowid 使用insert()方法的時(shí)候联喘，獲取最后一行的id

連接池

在這種情況下，當(dāng)你使用了session后就算顯式地調(diào)用session.close()辙纬，也不能把連接關(guān)閉豁遭。連接會(huì)由QueuePool連接池進(jìn)行管理并復(fù)用。

從mysql角度看連接

• mysql查看當(dāng)前連接數(shù)的命令是 show processlist;
  show processlist;查看正在運(yùn)行的線程贺拣，列出前100條
  show full processlist;列出所有蓖谢。
  +--------+-------------+-------------+---------+-------------+---------+------------------------------------------
  | Id | User | Host | db | Command | Time | State | Info | Progress
  +--------+-------------+-------------+---------+-------------+---------+------------------------------------------
  Id 標(biāo)識(shí)，kill某個(gè)mysql線程時(shí)使用
  User 顯示當(dāng)前用戶譬涡，如果不是root闪幽，那么就只顯示授權(quán)范圍內(nèi)的sql語(yǔ)句的用戶
  host 顯示sql語(yǔ)句是從哪個(gè)ip，哪個(gè)端口發(fā)出的涡匀，可以用來(lái)追蹤出問(wèn)題的用戶和端口
  db 顯示該線程連接的是哪個(gè)數(shù)據(jù)庫(kù)
  command 顯示當(dāng)前執(zhí)行的命令盯腌，
  一般：sleep 休眠，query 查詢陨瘩，connect 連接腕够。
  time 狀態(tài)持續(xù)時(shí)間，單位秒
  state 顯示使用當(dāng)前連接的sql語(yǔ)句的狀態(tài)舌劳，很重要列帚湘。請(qǐng)注意，state只是語(yǔ)句執(zhí)行中的某一個(gè)狀態(tài)甚淡。以查詢?yōu)槔兔牵枰?jīng)過(guò)copying to tmp table，sorting result材诽，sending data等狀態(tài)才算完成底挫。
  info 顯示這個(gè)sql語(yǔ)句，判斷問(wèn)題的重要依據(jù)脸侥。

最關(guān)鍵的參數(shù)就是state列建邓，狀態(tài)主要有：
Checking table
　正在檢查數(shù)據(jù)表（這是自動(dòng)的）。
Closing tables
　正在將表中修改的數(shù)據(jù)刷新到磁盤中睁枕，同時(shí)正在關(guān)閉已經(jīng)用完的表官边。這是一個(gè)很快的操作，如果不是這樣的話外遇，就應(yīng)該確認(rèn)磁盤空間是否已經(jīng)滿了或者磁盤是否正處于重負(fù)中注簿。
Connect Out
　復(fù)制從服務(wù)器正在連接主服務(wù)器。
Copying to tmp table on disk
　由于臨時(shí)結(jié)果集大于tmp_table_size跳仿，正在將臨時(shí)表從內(nèi)存存儲(chǔ)轉(zhuǎn)為磁盤存儲(chǔ)以此節(jié)省內(nèi)存诡渴。
Creating tmp table
　正在創(chuàng)建臨時(shí)表以存放部分查詢結(jié)果。
deleting from main table
　服務(wù)器正在執(zhí)行多表刪除中的第一部分菲语，剛刪除第一個(gè)表妄辩。
deleting from reference tables
　服務(wù)器正在執(zhí)行多表刪除中的第二部分，正在刪除其他表的記錄山上。
Flushing tables
　正在執(zhí)行FLUSH TABLES眼耀，等待其他線程關(guān)閉數(shù)據(jù)表。
Killed
　發(fā)送了一個(gè)kill請(qǐng)求給某線程佩憾，那么這個(gè)線程將會(huì)檢查kill標(biāo)志位哮伟，同時(shí)會(huì)放棄下一個(gè)kill請(qǐng)求。MySQL會(huì)在每次的主循環(huán)中檢查kill標(biāo)志位妄帘，不過(guò)有些情況下該線程可能會(huì)過(guò)一小段才能死掉楞黄。如果該線程程被其他線程鎖住了，那么kill請(qǐng)求會(huì)在鎖釋放時(shí)馬上生效寄摆。
Locked
　被其他查詢鎖住了谅辣。
Sending data
　正在處理SELECT查詢的記錄，同時(shí)正在把結(jié)果發(fā)送給客戶端婶恼。
Sorting for group
　正在為GROUP BY做排序桑阶。
　Sorting for order
　正在為ORDER BY做排序。
Opening tables
　這個(gè)過(guò)程應(yīng)該會(huì)很快勾邦，除非受到其他因素的干擾蚣录。例如，在執(zhí)ALTER TABLE或LOCK TABLE語(yǔ)句行完以前眷篇，數(shù)據(jù)表無(wú)法被其他線程打開萎河。正嘗試打開一個(gè)表吭历。
Removing duplicates
　正在執(zhí)行一個(gè)SELECT DISTINCT方式的查詢，但是MySQL無(wú)法在前一個(gè)階段優(yōu)化掉那些重復(fù)的記錄作彤。因此灯帮，MySQL需要再次去掉重復(fù)的記錄，然后再把結(jié)果發(fā)送給客戶端擎椰。
Reopen table
　獲得了對(duì)一個(gè)表的鎖支子，但是必須在表結(jié)構(gòu)修改之后才能獲得這個(gè)鎖。已經(jīng)釋放鎖达舒，關(guān)閉數(shù)據(jù)表值朋，正嘗試重新打開數(shù)據(jù)表。
Repair by sorting
　修復(fù)指令正在排序以創(chuàng)建索引巩搏。
Repair with keycache
　修復(fù)指令正在利用索引緩存一個(gè)一個(gè)地創(chuàng)建新索引昨登。它會(huì)比Repair by sorting慢些。
Searching rows for update
　正在講符合條件的記錄找出來(lái)以備更新贯底。它必須在UPDATE要修改相關(guān)的記錄之前就完成了丰辣。
Sleeping
　正在等待客戶端發(fā)送新請(qǐng)求.
System lock
　正在等待取得一個(gè)外部的系統(tǒng)鎖。如果當(dāng)前沒有運(yùn)行多個(gè)mysqld服務(wù)器同時(shí)請(qǐng)求同一個(gè)表丈甸，那么可以通過(guò)增加--skip-external-locking參數(shù)來(lái)禁止外部系統(tǒng)鎖糯俗。
Upgrading lock
　INSERT DELAYED正在嘗試取得一個(gè)鎖表以插入新記錄。
Updating
　正在搜索匹配的記錄睦擂，并且修改它們得湘。
User Lock
　正在等待GET_LOCK()。
Waiting for tables
　這個(gè)是我經(jīng)常會(huì)遇到的顿仇，意思是該線程得到通知淘正，數(shù)據(jù)表結(jié)構(gòu)已經(jīng)被修改了，需要重新打開數(shù)據(jù)表以取得新的結(jié)構(gòu)臼闻。然后鸿吆，為了能的重新打開數(shù)據(jù)表，必須等到所有其他線程關(guān)閉這個(gè)表述呐。以下幾種情況下會(huì)產(chǎn)生這個(gè)通知：FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE,或OPTIMIZE TABLE惩淳。
waiting for handler insert
　INSERT DELAYED已經(jīng)處理完了所有待處理的插入操作，正在等待新的請(qǐng)求乓搬。
　大部分狀態(tài)對(duì)應(yīng)很快的操作思犁，只要有一個(gè)線程保持同一個(gè)狀態(tài)好幾秒鐘，那么可能是有問(wèn)題發(fā)生了进肯，需要檢查一下激蹲。