MySQL Common query

Use of 1.unix timestamps

Unix_timesamp, From_unixtime functions, and Datatime_format functions.

Take the shaping unixtime timestamp from the datetime type;

Select Unix_timestamp (datetime) from Examplestables;

Convert from reshape to datetime type, time format

Select From_unixtime (datetime) from Exampletables;

Unix timestamp custom time period group statistics distinct non-repeating DateFormat time series custom format output

Select COUNT (Distinct (Roleid)), DateFormat (From_unixtime (DateTime), "%y-%m-%d") days from Tmptable group by days;

2. Sleep connection time-out--avoid excessive sleep connections consuming resources

Set global wait_timeout=305;

3. Create index table without primary key, non-indexed query very slow
Create INDEX Idx_datarow on Exampletables (DataRow);

4.myisam Engine and InnoDB engine

MyISAM using table-level locks on update insert

InnoDB in Update, insert uses row-level locks by index and key values, which is more concurrency

InnoDB The Blob Type field is too long when the table row property created is fixed, too many errors will be made.

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

Needs to be changed into dynamic. The dynamic length of the row data.

5. Query a column data duplicate data value Exampletables table, exrow row Duplicate column

SELECT * from Exampletables where Exrow in (select Exrow from Exampletables GROUP by Exrow have count (exrow) > 1);

6. Modify the table's engine to modify the table's column type

ALTER TABLE exampletable ENGINE=INNODB;

ALTER TABLE exampletable Modify column Exrowname varchar (64);

7. View the SQL command being executed, show processlist

SELECT * from information_schema.processlist where command <> ' Sleep ';

8. Check the slow query log to help capture time-consuming queries, exception queries

Show variables like ' slow ';

Log-slow-queries=/data/mysqldata/slowquery. Log
long_query_time=2

9.GTID (global Transaction ID) is the number of a committed transaction and is a globally unique number.
Gtid is actually made up of Uuid+tid. Where the UUID is the unique identity of a MySQL instance. The TID represents the number of transactions that have been committed on the instance and is monotonically incremented as the transaction commits. Here is a specific form of a Gtid

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

9.mysql backup--set-gtid-purged=off is a record in the ignore variable that has been performed on this machine, but has been purge binary logs to ordered to clean up gtid_set .

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

Attach backup and Recovery scripts: command line execution

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

Note that in the script above, the part of the backup is added to the--set-gtid-purged=off parameter to prevent the generation of the SET @ @global in the SQL script that was backed up. Gtid_purged statement:

Warning:a partial dump from A server which has gtids would by default include the Gtids of all transactions, even those tha T changed suppressed parts of the database. If you don ' t want to restore gtids, pass--set-gtid-purged=off.

Official documentation about Set-gtid-purged is written in this way:

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. Query the current execute non-sleep command

SELECT * from information_schema.processlist where command <> ' Sleep ';

MySQL Common query