MySQL常用语句.txt

##### 查看MySQL进程
SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE,info
  FROM INFORMATION_SCHEMA.PROCESSLIST
 WHERE DB IS NOT NULL
   AND COMMAND != 'Sleep'
 ORDER BY TIME DESC LIMIT 10;
 
 
##### 通过tcpdump查看慢语句
https://www.percona.com/blog/2008/11/07/poor-mans-query-logging/
https://bugs.launchpad.net/percona-toolkit/+bug/1402776
#!/bin/bash
#this script used monitor mysql network traffic.echo sql
tcpdump -i eno1 -s 0 -l -w - dst port 3306 | strings | perl -e '
while(<>) { chomp; next if /^[^ ]+[ ]*$/;
    if(/^(SELECT|UPDATE|DELETE|INSERT|SET|COMMIT|ROLLBACK|CREATE|DROP|ALTER|CALL)/i)
    {
        if (defined $q) { print "$q\n"; }
        $q=$_;
    } else {
        $_ =~ s/^[ \t]+//; $q.=" $_";
    }
}'


##### 查看MySQL表信息
SELECT
        IFNULL(@@hostname, @@server_id) SERVER_NAME,
        %s as HOST,
        t.TABLE_SCHEMA,
        t.TABLE_NAME,
        t.TABLE_ROWS,
        t.DATA_LENGTH,
        t.INDEX_LENGTH,
        t.AUTO_INCREMENT,
      c.COLUMN_NAME,
      c.DATA_TYPE,
      LOCATE('unsigned', c.COLUMN_TYPE) COL_UNSIGNED
      # CONCAT(c.DATA_TYPE, IF(LOCATE('unsigned', c.COLUMN_TYPE)=0, '', '_unsigned'))
    FROM
        information_schema.`TABLES` t
    LEFT JOIN information_schema.`COLUMNS` c ON t.TABLE_SCHEMA = c.TABLE_SCHEMA
    AND t.TABLE_NAME = c.TABLE_NAME
    AND c.EXTRA = 'auto_increment'
    WHERE
        t.TABLE_SCHEMA NOT IN (
            'mysql',
            'information_schema',
            'performance_schema',
            'sys'
        )
    AND t.TABLE_TYPE = 'BASE TABLE'
	

##### 查看索引统计信息
SELECT a.TABLE_NAME,a.INDEX_NAME,a.SEQ_IN_INDEX,a.COLUMN_NAME,a. COLLATION,(a.CARDINALITY / b.TABLE_ROWS * 100) AS CARDINALITY1,b.TABLE_ROWS,a.INDEX_TYPE
FROM 
(SELECT TABLE_NAME,INDEX_NAME,SEQ_IN_INDEX,COLUMN_NAME,COLLATION,CARDINALITY,INDEX_TYPE FROM information_schema.STATISTICS WHERE table_schema = 'test' AND INDEX_NAME <> 'PRIMARY')
AS a ,
(SELECT TABLE_NAME,TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'test')
 AS b
 WHERE a.TABLE_NAME = b.TABLE_NAME and b.TABLE_ROWS != 0 order by CARDINALITY1;
 
 
##### 查看InnoDB无效空间大小
http://jfg-mysql.blogspot.com/2017/07/innodb-compaction.html?m=1&from=groupmessage&isappinstalled=0
# sts="show table status like 'table1'\G"
# mysql my_schema <<< "$sts" | awk '$1=="Data_free:"{$2 /= 1024*1024*1024; print}'
Data_free: 117.709

# date; ls -lh table2.ibd; \
  mysql my_schema <<< "stop slave; optimize table local table2; start slave"; \
  ls -lh table2.ibd; date