MySQL監控常用語法

1 mysql連線數

一、取得mysql最大連線數

show status like ‘Max_used_connections’

二、取得mysql目前開啟的連線數

show status like ‘Threads_connected’

2 mysql 暫存

show status like ‘Innodb_buffer_pool_reads’

show status like ‘Innodb_buffer_pool_read_requests’

show status like ‘Innodb_buffer_pool_pages_total’

show status like ‘Innodb_buffer_pool_pages_free’

(1-Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests)*100%

((Innodb_buffer_pool_pages_total-Innodb_buffer_pool_pages_free)/Innodb_buffer_pool_pages_total)*100%

3 鎖

show status like ‘Innodb_row_lock_waits’

show status like ‘Innodb_row_lock_time_avg’

show open TABLES where in_use>0;

4 sql語法

一、慢查詢mysql 語法開關是否打開

show variables like ‘slow_query_log’;

二、慢查詢mysql語法打開

set global slow_query_log=1

三、設定慢sql的時間(秒)

set long_query_time=1

三、慢sql路徑

show variables like ‘slow_query_log_file’;

四、透過慢sql分析工具格式化記錄

mysqldumpslow -s at -t 10 /export/data/mysql/log/slow.log

四.1、取出使用最多的10條慢sql

./mysqldumpslow -s c -t 10 /export/data/mysql/log/slow.log

四.2、取出查詢時間最慢的3條sql

./mysqldumpslow -s t -t 3 /export/data/mysql/log/slow.log

幾個會導致sql變慢的原因

1 子查詢

SELECT FROM t1 WHERE id (SELECT id FROM t2 WHERE name=’hechunyang’);

改用 join ,但update/delete沒作用

2 避免用函數

SELECT FROM t WHERE YEAR(d) >= 2016;

改用 SELECT FROM t WHERE d >= ‘2016-01-01’;

3 用in 取代 or

SELECT FROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30;

改用  SELECT FROM t WHERE LOC_IN IN (10,20,30);

4 雙%號無法用到索引

SELECT FROM t WHERE name LIKE ‘%de%’;

改為 SELECT FROM t WHERE name LIKE ‘de%’;

5 分組統計可以禁止排序

SELECT goods_id,count() FROM t GROUP BY goods_id;

改為  SELECT goods_id,count () FROM t GROUP BY goods_id ORDER BY NULL;

6不要用無意義的order by

SELECT count(1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id WHERE 1 = 1 ORDER BY u.create_time DESC;

改為

SELECT count (1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id;

 

MySQL求出二經緯度之間的距離(公尺)

假設自己的經緯度為 LAT,LNG
資料庫欄位_LAT,_LNG,_NAME,
SELECT CEIL((((ACOS(SIN((LAT*PI()/180)) * SIN((_LAT*PI()/180))+COS((LAT*PI()/180))
* COS((_LAT*PI()/180)) * COS(((LNG- _LNG)*PI()/180))))*180/PI())*60*1.1515*1.609344)*1000)
AS DISTANCE,_NAME
FROM TABLE_NAME
ORDER BY DISTANCE ASC
算出距離幾公尺

Delphi透過MySQL存入檔案備份

MySQL存Blog的大小
TINYBLOB – 255 bytes
BLOB – 65535 bytes
MEDIUMBLOB – 16,777,215 bytes (2^24 – 1)
LONGBLOB – 4G bytes (2^32 – 1)

透過資料庫存取的方式重點:

TBlobField – LoadFromStream、SaveToStream
Tmemorystream – LoadFromFile、SaveToFile(.Position :=0)

存入資料庫

[pascal]
var
astream : Tmemorystream;
begin
AStream := TMemoryStream.Create;
try
astream.LoadFromFile(filename);
AStream.Position := 0;
if adotable1.Active then
begin
adotable1.Edit;
TBlobField(adotable1.FieldByName(‘t2’)).LoadFromStream(AStream);
adotable1.Post;
end;
finally
AStream.free;
end;
end;
[/pascal]

存回檔案

[pascal]
var
Ms:Tmemorystream;
begin
Ms := Tmemorystream.Create;
try
if adotable1.Active then
begin
TBlobField(adotable1.FieldByName(‘t2’)).SaveToStream(Ms);
Ms.Position := 0;
MS.SaveToFile(sname);
end;
finally
ms.Free;
end;
end;
[/pascal]

MySQL預設的BLOB為1M
別忘了 My.ini調整
max_allowed_packet = XXM

Delphi將Image存入MySQL資料庫

存入
[php]
var astream : Tmemorystream;
begin
AStream := TMemoryStream.Create;
try
Image1.Picture.Graphic.SaveToStream(AStream);
AStream.Position := 0;
if adoquery1.Active then
begin
adoquery1.Edit;
TBlobField(adoquery1.FieldByName(‘File’)).LoadFromStream(AStream);
adoquery1.Post;
end;
finally
AStream.Free;
end;
[/php]

取出
[php]
var
Ms:TStringStream;
jpg1:Tjpegimage;
begin
Ms := TStringStream.Create(”);
jpg1 := Tjpegimage.create;
try
if adoquery1.Active then
begin
TBlobField(adoquery1.FieldByName(‘File’)).SaveToStream(Ms);
Ms.Position := 0;
jpg1.LoadFromStream(ms);
image1.Picture.Bitmap.Assign(jpg1);
end;
finally
ms.Free;
jpg1.Free;
end;
[/php]

注意,如果出現Got a packet bigger than ‘max_allowed_packet’ bytes
在My.ini加上
max_allowed_packet = 10M

MAMP- Mac上架設Apache、MySQL、PHP的好工具

MAMPWindows架設Apache、MySQL、PHP的環境主要是使用Appserv或可以直接解壓縮執行的XAMPP,雖然XAMPP也提供在Mac上安裝,不過似乎不是很穩定(也許是因為跨平台的關係吧)。anyway~在Mac上架設Apache、MySQL、PHP難道就要一個個安裝?

終於找到了MAMP這個簡單的懶人包!而且還是全圖形介面~

螢幕快照 2013-11-01 上午9.35.27

安裝完成後,可以在應用程式看到WAMP及WAMP PRO(付費版本)的資料夾,我們點開WAMP資料夾下面的WAMP應用程式。

螢幕快照 2013-11-01 上午9.35.47

接著會詢問你要開啟的是免費版本或付費版本,我們點Launch MAMP

 

螢幕快照 2013-11-01 上午9.36.00

打開之後,點選Start Servers讓Apache Server、MySQL Server亮綠燈

螢幕快照 2013-11-01 上午9.36.49

按Open Start Page可以看到MAMP的首頁,就表示完成了!

就著我們點進Preferences看看

螢幕快照 2013-11-01 上午9.38.13

在Start/Stop可以選擇,打開(關閉)MAMP時是否自動開啟(關閉)Apache/MySQL伺服器、程式開啟時是否檢查開啟MAMP Pro、以及開啟程式是否自動開啟 Start page以及Start Page的位置。

螢幕快照 2013-11-01 上午9.38.37

這邊可以稍徵調整一下Port位置,apache為80,MySQL則是3306。

螢幕快照 2013-11-01 上午9.38.46

還可以選PHP版本

螢幕快照 2013-11-01 上午9.38.53

 

指定Apache Document Root。

MAMP圖形化且穩定可以減少開發上許多的不便,朋友們可以嘗試使用囉。

 

相關連結:
Appserv官網: http://www.appservnetwork.com

XAMPP官網: http://www.apachefriends.org/zh_tw/xampp.html

MAMP官網: http://www.mamp.info/en/index.html

 

Delphi XE LiveBindings連結MySQL中文亂碼問題

 

 

 

一直都嘗試用原來的寫法,沒試過LiveBindings的功能,昨天看到 CodeRage8的介紹後,今天嘗試一下,結果剛好遇到MySQL中文亂碼問題,一起分享。

Data Explorer

DataExplorer01

1打開Data Explorer,在MySQL按右鍵 >> Add New ConnectionDataExplorer02

2新增名字DataExplorer03

3輸入連線資訊

HostName >> 主機名稱

Database >> 資料庫名稱

User Name >> 帳號
Password >> 密碼

接著可以打開 Advanced看看裡面有什麼

DataExplorer04 DataExplorer05

4按OK完成

 

LiveBindings

liveBindings01

1從上面的功能表點選 View -> LiveBindings Designer打開 LiveBindings DesignerliveBindings02

2會看到下面多了一塊 LiveBindings Designer的畫面,接著按左下角的LiveBindings Wizard

liveBindings03

3可以選擇Binding的方式,我們選第二個 Link a grid with a data source

liveBindings04

4選擇TGrid

liveBindings05

5選DBX(DBExpress)

liveBindings06

6選擇 Driver: MySQL;Connection Name:POS(剛才Data Explorer建立的);Command Type: ctTable(連接Table);Command Text:下拉要連結的Table

liveBindings07

7勾選 Add Data Source navigator,新增一個navigatorliveBindings08

8完成後就會發現 LiveBinding都做好了XD 稍徵排版一下liveBindings09

9發現裡面的中文變成????怎麼辦?

liveBindings10

10點選 TSQLConnection,在Driver裡面有一個 ServerCharSet設成big5,接著把上面的Connected勾掉再勾回去(重新連線)

liveBindings11

11再回到TBindSourceDBX,一樣在Active的地方勾掉再勾回去重新連線

liveBindings1212亂碼的地方回復成中文了!

 

Delphi-將POS系統的Interbase匯到MySQL資料庫

前一陣子研究各家的餐飲、簡餐POS系統,意外的發現不少系統都是用Delphi寫的,資料庫也大多使用Interbase或是Firebird。

為了方便研究,就嘗試將資料庫轉到MySQL上去~記錄一下轉移方式。

工具:

做法:

在MySQL建立空白資料庫(utf8)

4

 

資料來源 (ODBC)新增 MySQL ODBC 3.51 Driver,注意一點要記得打開Details,將 Character Set 選成 Big5(不是utf8喔)

5

SQL Explorer→New Database→Database Driver Name選INTRBASE,SERVER NAME選擇您要連結的GDB檔案

8

6

7

打開Datapump,Source Alias選擇在SQL Explorer建立的名稱,User Name是SYSDBA(注意大小寫),password是 masterkey(注意大小寫)。接著Target Alias選擇MySQL ODBC建立的名稱。

11

10

9

12

13

 

 

 

14