[MySQL]mySql的show processlist指令 另外好用的方法

一、當一個連線處於睡眠狀態，它沒有做任何事情的時刻。直接過濾掉睡覺的連線。

mysql> \P grep -v Sleep
PAGER set to 'grep -v Sleep'
mysql> show full processlist;
這樣就不會出現是Sleep的連線了。

另一種有整理顯示的指令
mysql> SHOW FULL PROCESSLIST\G


二、如果想要查看各連線，何不產出文字檔來看呢。

mysql -uroot  -proot -e "SHOW FULL PROCESSLIST\G" > /tmp/processlist-`date +%F-%H-%M`.log;

在這個產出文字檔的指令為
mysql -uroot  -proot -e "SHOW FULL PROCESSLIST\G" > 產出文字檔路徑

三、也可以下sql
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST where COMMAND != 'SLEEP'

參考：
   http://dev.mysql.com/doc/refman/5.5/en/show-processlist.html

[MySQL]使用正則表達式(REGEXP)的用法

[MySQL]使用正則表達式(REGEXP)的用法


練習

需求說明：
                 有多筆資料，每筆有多種商品代號碼(如：AAA,BBB,AAA_BBB)
                 每種商品用此附號 ( , )分開，
                 請找出商品代號為AAA的資料。
語法如下：

SELECT*
FROM
(#測試資料
      SELECT "AAA,BBB,CCC,DDD,EEE,AAA_BBB" AS number
      UNION ALL
      SELECT "AAA_BBB,CCC_DDD,EEE_AAA_BBB" AS number
      UNION ALL
      SELECT "AAA_BBB_CCC,DDD_EEE,AAA_BBB" AS number
      UNION ALL
      SELECT "BBB,CCC_DDD,EEE_AAA_BBB,AAA" AS number
      UNION ALL
      SELECT "CCC_DDD,EEE_AAA_BBB" AS number
      UNION ALL
      SELECT "CCC_DDD" AS number
      UNION ALL
      SELECT "AAA" AS number
      )AS testData
where testData.number REGEXP '[[:<:]]AAA[[:>:]]' > 0;#判斷語法

UNION ALL 的用法

結果如下：
會將有AAA的商品找出來，而上述的第二筆中的AAA_BBB，是不會背找出來的。

其它用法如下：



參考資料：
G MySQL Regular Expressions

http://dev.mysql.com/doc/refman/5.1/en/regexp.html


以下轉貼
來源：八克里-正規表示式 Regular expressions 使用在 mysql 的 sql 查詢


正規表示式 Regular expressions 使用在 mysql 的 sql 查詢
果然是各殺手級的應用
mysql RE用法
http://dev.mysql.com/doc/refman/5.1/en/regexp.html

測試 RE
http://osteele.com/tools/rework/

RFC email正規表示
http://www.regular-expressions.info/email.html

官方網站的例子是這樣的
mysql> SELECT 'a word a' REGEXP '[[:<:]]word[[:>:]]'; -> 1
mysql> SELECT 'a xword a' REGEXP '[[:<:]]word[[:>:]]'; -> 0
字串的比對透過 RE 比對後得到 ture or flase 兩種結果
結果應用在 where 查詢上面，就可以找到符合我們描述字串的東西了
WHERE phone REGEXP '(435)';
會用到這個功能原因是，在原始的資料檔案中在email這個欄位，因為是大批的匯入系統
但使佣者在建立的時候，每個人的格式不同創造出了眾多的 email表達方式，又剛好要用到這些
email來發電子文宣，只好用 RE 來做簡單的 email過濾了.....

需要兩個校友的資料，以 .CSV 檔案
1.有email的校友，學號,姓名,email 且 email 合乎 REF 標準

SELECT `sid`,`username`,`email` FROM `data` WHERE `email` regexp '^[[:alnum:]]+@([[:alnum:]\.{1,}])+\.[a-z]{2,3}$' ;

2.有地址的校友，學號,姓名,郵遞區號,地址，地址做簡單的過濾
SELECT `username`,`register_zip`,`register_addr` FROM `data`
where register_zip AND register_addr REGEXP '高雄|高市|台南|高縣';

[MySQL]EXPLAIN使用範例

EXPLAIN 使用的方式：
只要在SELECT語法前面加上 EXPLAIN 指令即可。

以下為執行畫面範例



其中針對EXPLAIN的欄位說明如下：
table：關連到的資料表(Table)會顯示在此。
type：顯示使用了何種類型。從最優至最差的類型為const、eq_reg、ref、range、indexhe、ALL。
         System：system資料表，此為const連接類型的特殊情況。          
         Const：資料表中的一個記錄的最大值能夠符合這個查詢。因為只有一行，這個值就是
                      常數，因為MySQL會先讀這個值然後把它當做常數。
         eq_ref：MySQL在連接查詢時，會從最前面的資料表，對每一個記錄的聯合，從資料
                       表中讀取一個記錄，在查詢時會使用索引為主鍵或唯一鍵的全部。
         ref：只有在查詢使用了非唯一鍵或主鍵時才會發生。
         range：使用索引返回一個範圍的結果。例如：使用大於>或小於<查詢時發生。
         index：此為針對索引中的資料進行查詢。
         ALL：針對每一筆記錄進行完全掃描。
possible_keys：顯示可能使用到的索引。此為從WHERE語法中選擇一個適合的欄位名稱。
key：實際使用到的索引。如果為NULL，則是沒有使用索引。
key_len：使用索引的長度。長度越短 準確性越高。
ref：顯示那一列的索引被使用。一般是一個常數(const)。
rows：MySQL用來返回資料的筆數。
Extra：MySQL用來解析額外的查詢訊息。如果此欄位的值為：Using temporary和Using filesort，表示MySQL無法使用索引。
Extra為MySQL用來解析額外的查詢訊息，其中欄位值所代表的意義如下：
         Distinct：當MySQL找到相關連的資料時，就不再搜尋。
         Not exists：MySQL優化 LEFT JOIN，一旦找到符合的LEFT JOIN資料後，就不再搜
                            尋。
         Range checked for each Record(index map:#)：無法找到理想的索引。此為最慢的
                                                                                          使用索引。
         Using filesort：當出現這個值時，表示此SELECT語法需要優化。因為MySQL必須進
                                    行額外的步驟來進行查詢。
         Using index：返回的資料是從索引中資料，而不是從實際的資料中返回，當返回的資
                               料都出現在索引中的資料時就會發生此情況。
         Using temporary：同Using filesort，表示此SELECT語法需要進行優化。此為MySQL
                                       必須建立一個暫時的資料表(Table)來儲存結果，此情況會發生在針
                                       對不同的資料進行ORDER BY，而不是GROUP BY。
         Using where：使用WHERE語法中的欄位來返回結果。

其它文章

[MySQL]執行順序與效率概論

MySQL執行順序如下
1.FROM
2.ON
3.JOIN
4.WHERE
5.GROUP BY
6.WITH CUBE or WITH ROLLUP
7.HAVING
8.SELECT
9.DISTINCT
10.ORDER BY
11.TOP / LIMIT

如果想要知道自己SELECT語句的效率如何，可以在SELECT之前加上EXPLAIN 關鍵字
即可得知相關的執行順序及效率分析<此篇不對此做討論>

如上所列查詢語句有其執行的順序，所以要讓自己的查詢語句提昇效率就必須從這上述各點來下手，在此以例子直接說明
SELECT
         *
FROM
         user
         JOIN
                  userProfile AS pro
         ON
                  user.id = pro.userId
         JOIN
                  userAgreement AS agr
        ON
                  agr.userId = user.id
WHERE
         user.birthday = '1984-06-25'
         AND
         pro.local = '員林'

 這句SQL執行的順序是
1.FROM    user
2.ON         user.id = pro.userId
3.JOIN      userProfile AS pro
4.ON         agr.userId = user.id
5.JOIN      userAgreement AS agr
6.WHERE user.birthday = '1984-06-25'
                  AND
                  pro.local = '員林'
7.SELECT *

查詢顧名思義如同在電話簿中找尋某人的名字一樣，資料量越大、所知的條件越少查詢所需時間就越長，相對的資料量越少、知道的條件越明確查詢所需的時間就越短。

那麼在這樣的執行順序下我們要怎麼讓資料量縮小以加快速度呢?
照執行順序來走
1.先碰到的就是user這個table，這個table相對於其他兩個table是否資料量最少?以此例討論的確是如此，因為user對userProfile、userAgreement都是一對多的關係，當然以user當開頭是一個好開頭。

2.接下來限制條件，以明確的條件來限制、以及少量的資料來比對大量的資料，自然就能保持較佳的查詢效率了。

3.有了明確條件以及少量資料比對的限制，JOIN table效率自然會比較好。

4.5.如上兩點所言，越明確的條件以及少量資料的比對，效率會更好，所以這裡值得注意的是ON         agr.userId = user.id  以大量的資料去比對少量的資料，這是反其道而行會適得起反降慢速度，ON   user.id = agr.userId 如果改成這樣就會變成以少量資料去比對大量資料，則又可獲得較佳的效率了。

6.當上述的TABLE都JOIN完成之後，再用WHERE來做整體的條件限制，一直以來都是這麼做的沒錯吧~那就錯大了!!如果條件可以在上面ON的時候就限制，資料量便可再更縮小，JOIN出來的TABLE就會很小甚至精準到需求的資料，有時候根本連WHERE都不用寫，那該怎麼寫呢?稍後將會把改完的SQL寫在下方給大家參考。

7.JOIN完TABLE，限制條件也結束了，才開始SELECT，此時會將使用者所需要的資料一一列出，大家都會用*來簡便的將一切列出，這也是降低效率的兇手之一，SELECT越明確，越少的欄位會讓整體速度稍微加快，因此在SELECT部份也盡量少濫用*，可獲得較佳的效率

透過上述幾點，將修改後的SQL寫出如下︰
SELECT
         user.id,
         user.username,
         pro.phone,
         agr.agreementEndDate
FROM
         user
         JOIN
                  userProfile AS pro
         ON
                  user.birthday = '1984-06-25'
                  AND
                  pro.local = '員林'
                  AND
                  user.id = pro.userId
         JOIN
                  userAgreement AS agr
         ON
                  agr.userId = user.id

如上所示，在第一次ON的條件限制下就將資料量降到最少，甚至精準，如此往後JOIN就會讓資料量大為減少，最後連WHERE都不需要額外限制其他條件，而SELECT部份也只挑出所需的資料就好， 達到最佳的效率

總結︰資料量越小、條件越明確就能達到最好的查詢效率

其它文章

[MySQL]好用的工具Toad for MySQL 6.0

Toad for MySQL 6.0 Freeware
Toad 官網下Download

安裝後

建立database
File->new->database
建立連線Create New Connection
File->new->Connection

Connection type: TCP(預設)
Host: localhost(預設)or(你要連線的ip)
User:帳號
Password:密碼
Database:你的database
port:3306(預設)

連線完成後：
Tools->Editor
會開啟一個視窗，
就可以開始下指令了。

其它文章

[MySQL]tinyint的取值方法

tinyint的取值方法

型態	byte	範圍
tinyint tinyint unsigned	1byte	-128~127 UNSIGNED： 0~255

tinyint

1byte=8bit

1 111 1111 -> -128

0 111 1111 ->  127

tinyint unsigned
1byte=8bit

0000 0000 ->0
1111 1111->255
例：我以權限來permission
1 代表--新增功能
2 代表--修改功能
4 代表--刪除功能
如果一位使用者的
permission=3
那判斷
permission & 1 = 1
    0000 0011
& 0000 0001
    0000 0001   = 1 成立代是有新增功能
permission & 2 = 2

    0000 0011
& 0000 0010
    0000 0010 = 2 成立代是有修改功能


permission & 4 = 4

    0000 0011
& 0000 0100
    0000 0000 = 0  不成立沒有刪除功能

參考資料

其它文章