select table_schema,sum(data_length)/1024/1024/1024 as data_length,
sum(index_length)/1024/1024/1024 as index_length,
sum(data_length+index_length)/1024/1024/1024 as sum_data_length
from information_schema.tables
where table_schema != ‘information_schema‘ and table_schema != ‘mysql‘ group by table_schema;mysql> use information_schema;
Database changed
mysql> select table_schema,sum(data_length)/1024/1024/1024 as data_length,sum(index_length)/1024/1024/1024 as index_length,sum(data_length+index_length)/1024/1024/1024 as sum_data_length from information_schema.tables where table_schema != ‘information_schema‘ and table_schema != ‘mysql‘ group by table_schema;
+--------------------+----------------+----------------+-----------------+
| table_schema | data_length | index_length | sum_data_length |
+--------------------+----------------+----------------+-----------------+
| performance_schema | 0.000000000000 | 0.000000000000 | 0.000000000000 |
| test | 0.000015258789 | 0.000001907349 | 0.000017166138 |
+--------------------+----------------+----------------+-----------------+
2 rows in set (0.00 sec)select table_name,data_length,index_length,sum(data_length+index_length) as total_size
from information_schema.tables
where table_schema=‘test‘ group by table_name;mysql> select table_name,data_length,index_length,sum(data_length+index_length) as total_size from information_schema.tables where table_schema=‘test‘ group by table_name;
+------------+-------------+--------------+------------+
| table_name | data_length | index_length | total_size |
+------------+-------------+--------------+------------+
| t1 | 16384 | 0 | 16384 |
| t2 | 0 | 1024 | 1024 |
| t3 | 0 | 1024 | 1024 |
+------------+-------------+--------------+------------+
3 rows in set (0.00 sec)select sum(data_length+index_length)/1024/1024/1024 from information_schema.tables;mysql> select sum(data_length+index_length)/1024/1024/1024 from information_schema.tables;
+----------------------------------------------+
| sum(data_length+index_length)/1024/1024/1024 |
+----------------------------------------------+
| 0.000862423331 |
+----------------------------------------------+
1 row in set (0.03 sec)netstat -anlp|grep 3306|grep tcp|awk ‘{print $5}‘|awk -F: ‘{print $1}‘|sort|uniq -c|sort -nr|head -n20
mysql -uroot -proot -e "show processlist"|awk ‘{print $3}‘|awk -F: ‘{print $1}‘|sort|uniq -c|sort -nr[root@ning ~]# netstat -anlp|grep 3306|grep tcp|awk ‘{print $5}‘|awk -F: ‘{print $1}‘|sort|uniq -c|sort -nr|head -n20
1
[root@ning ~]# mysql -uroot -proot -e "show processlist"|awk ‘{print $3}‘|awk -F: ‘{print $1}‘|sort|uniq -c|sort -nr
Warning: Using a password on the command line interface can be insecure.
1 localhost
1 Hostmysql> desc t4;
+-------+--------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| id | int(1) | YES | | NULL | |
+-------+--------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into t4 values(1),(11),(111);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from t4;
+------+
| id |
+------+
| 1 |
| 11 |
| 111 |
+------+mysql> insert into t4 values(1000000000000);
Query OK, 1 row affected, 1 warning (0.02 sec)
mysql> select * from t4;
+------------+
| id |
+------------+
| 1 |
| 11 |
| 111 |
| 2147483647 |
+------------+
4 rows in set (0.00 sec)mysql> SELECT INET_ATON(‘192.168.100.111‘);
+------------------------------+
| INET_ATON(‘192.168.100.111‘) |
+------------------------------+
| 3232261231 |
+------------------------------+
1 row in set (0.01 sec)mysql> SELECT INET_NTOA(‘3232261231‘);
+-------------------------+
| INET_NTOA(‘3232261231‘) |
+-------------------------+
| 192.168.100.111 |
+-------------------------+
1 row in set (0.00 sec)mysql> SELECT UNIX_TIMESTAMP(NOW()),UNIX_TIMESTAMP(‘2019-04-01 17:10:10‘);
+-----------------------+---------------------------------------+
| UNIX_TIMESTAMP(NOW()) | UNIX_TIMESTAMP(‘2019-04-01 17:10:10‘) |
+-----------------------+---------------------------------------+
| 1617268330 | 1554109810 |
+-----------------------+---------------------------------------+
1 row in set (0.00 sec)mysql> SELECT FROM_UNIXTIME(‘1617268237‘),FROM_UNIXTIME(‘1554109810‘);
+-----------------------------+-----------------------------+
| FROM_UNIXTIME(‘1617268237‘) | FROM_UNIXTIME(‘1554109810‘) |
+-----------------------------+-----------------------------+
| 2021-04-01 17:10:37.000000 | 2019-04-01 17:10:10.000000 |
+-----------------------------+-----------------------------+
1 row in set (0.00 sec)mysql> use test;
Database changed
mysql> create table su(
-> id int unsigned not null auto_increment,
-> c1 int not null default ‘0‘,
-> c2 int not null default ‘0‘,
-> c3 int not null default ‘0‘,
-> c4 int not null default ‘0‘,
-> c5 timestamp not null,
-> c6 varchar(200) not null default ‘‘,
-> primary key(id)
-> );
Query OK, 0 rows affected (0.04 sec)mysql> use test;
Database changed
mysql> delimiter $$ --delimiter+空格+$$:定义结束符为$$,即碰到$$，就可以执行了
mysql> drop procedure if exists `insert_su` $$
Query OK, 0 rows affected (0.00 sec)
mysql> create procedure `insert_su`(in row_num int)
-> begin
-> declare i int default 0;
-> while i < row_num do
-> insert into su(c1,c2,c5,c3,c4) values(floor(rand()*row_num),floor(rand()*row_num),floor(rand()*row_num),now(),repeat(‘su‘,floor(rand()*20)));
-> set i = i+1;
-> end while;
-> end $$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;--注意delimiter后的空格mysql> use test;
Database changed
mysql> call insert_su(50000);
Query OK, 1 row affected, 3 warnings (57.06 sec)
mysql> select count(*) from su;
+----------+
| count(*) |
+----------+
| 50000 |
+----------+
1 row in set (0.02 sec)mysql> delete from su;
Query OK, 50000 rows affected (0.34 sec)
mysql> call insert_su(50000);
Query OK, 1 row affected, 3 warnings (7 min 20.63 sec)
mysql> select count(*) from su;
+----------+
| count(*) |
+----------+
| 50000 |
+----------+
1 row in set (0.01 sec)mysql> show table status like ‘su‘ \G;
*************************** 1. row ***************************
Name: su
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 49156 --该值是统计信息的大小，该值接近实际表数量，则统计信息是准确的
Avg_row_length: 53
Data_length: 2637824
Max_data_length: 0
Index_length: 0
Data_free: 4194304
Auto_increment: 100001
Create_time: 2021-04-02 10:16:04
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)--Data_length+Index_length-Rows*Avg_row_length
mysql> select 2637824-49156*53;
+------------------+
| 2637824-49156*53 |
+------------------+
| 32556 |
+------------------+
1 row in set (0.00 sec)mysql> alter table su engine=innodb; --目的是重新整理表(放在夜间执行，因为数据量大会很费时间)
Query OK, 0 rows affected (0.61 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> show table status like ‘su‘ \G;
*************************** 1. row ***************************
Name: su
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 50256
Avg_row_length: 52
Data_length: 2637824
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 100001
Create_time: 2021-04-02 11:27:31
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql> select 2637824-50256*52;
+------------------+
| 2637824-50256*52 |
+------------------+
| 24512 |
+------------------+
1 row in set (0.00 sec)mysql> show create table su \G;
*************************** 1. row ***************************
Table: su
Create Table: CREATE TABLE `su` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`c1` int(11) NOT NULL DEFAULT ‘0‘,
`c2` int(11) NOT NULL DEFAULT ‘0‘,
`c3` int(11) NOT NULL DEFAULT ‘0‘,
`c4` int(11) NOT NULL DEFAULT ‘0‘,
`c5` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`c6` varchar(200) NOT NULL DEFAULT ‘‘,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)mysql> use information_schema;
Database changed
mysql> select * from tables; --访问这张表就是收集统计信息的一种方法。
--建议写一个定时任务，每天访问一下这张表，收集一下统计信息。原文：https://www.cnblogs.com/youdiancaidaren/p/14866870.html