mysql 常用查詢

標籤：

1.unix時間戳記的使用

unix_timesamp、from_unixtime 函數 和 datatime_format函數。

// 從datetime 類型取做整形 unixtime時間戳記;

select unix_timestamp( datetime ) from examplestables;

// 從整形轉換成datetime類型，時間格式

select from_unixtime( datetime ) from exampletables;

// 對unix 時間戳記自訂時間段分組統計 distinct 不重複 dateformat 時間串自訂格式輸出

select count(distinct(roleid)), dateformat( from_unixtime(datetime),"%Y-%m-%d"  ) days from tmptable  group by days;

2. sleep連線逾時時間 --避免過多的sleep串連佔用資源

 set global wait_timeout=305;

3.建立索引 表不加主鍵，不加索引的查詢非常慢
create index idx_datarow on exampletables(datarow);

4.myisam 引擎 和 innodb引擎

myisam在 update insert 使用表級鎖

innodb 在update ，insert時使用 的是按照索引和索引值的行級鎖，並發性更高

innodb 在建立的表行屬性 為fixed的時候，blob類型欄位過長，過多會報錯。

Row size too large. The maximum row size for the used table type, not counting BLOBs, is 1982. You have to change some columns to TEXT or BLOBs。

需要改成dynamic。動態長度的行資料。

5.查詢某列資料重複的資料值 exampletables  表中，exrow行重複的列

select * from exampletables where exrow in ( select exrow from exampletables group by exrow  having count(exrow) > 1 );

6.修改表的引擎,修改表的列類型

alter table exampletable ENGINE=InnoDB;

alter table exampletable modify column exrowname varchar(64);

7.查看正在執行的sql命令，show processlist 

select * from information_schema.processlist where command <> ‘Sleep‘;

8.查看慢查詢日誌,有助於捕捉耗時查詢，異常查詢

show variables like ‘slow‘;

log-slow-queries=/data/mysqldata/slowquery。log
long_query_time=2

9.GTID(Global Transaction ID)是對於一個已提交事務的編號，並且是一個全域唯一的編號。
GTID實際上是由UUID+TID組成的。其中UUID是一個MySQL執行個體的唯一標識。TID代表了該執行個體上已經提交的事務數量，並且隨著事務提交單調遞增。下面是一個GTID的具體形式

3E11FA47-71CA-11E1-9E33-C80AA9429562:23

9.mysql 備份--set-gtid-purged=OFF  是忽略 變數中記錄的是本機上已經執行過，但是已經被purge binary logs to命令清理的gtid_set。

mysqldump -uexuser -p -h127.0.0.1 --databases --no-data dbname   --set-gtid-purged=OFF  >/data/bk.sql

附上備份和恢複指令碼：命令列執行

mysqldump -uroot -p -h 11.111.111.111 -P3306 --default-character-set=utf8 --set-gtid-purged=OFF --password --databases test > /home/sqlbackup

mysql -h11.111.111.111 -uroot -P3306 -p --default-character-set=utf8  < /home/sqlbackup

注意，上述指令碼中，備份的部分要加入--set-gtid-purged=OFF參數，防止在備份出的sql指令碼中產生 SET @@global.gtid_purged 語句：

Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don‘t want to restore GTIDs, pass --set-gtid-purged=OFF.

官方文檔關於set-gtid-purged是這樣寫的：

This option enables control over global transaction ID (GTID) information written to the dump file, by indicating whether to add a SET @@global.gtid_purged statement to the output.

10.查詢目前的 執行非sleep命令

select * from information_schema.processlist where command <>‘Sleep‘; 

mysql 常用查詢