|
1
2
3
4
5
6
7
8
|
/* 查看表大小 */
SELECT
CONCAT(
TRUNCATE
(
SUM
( data_length ) /1024 /1024, 2 ) ,
'MB'
)
AS
data_size,
CONCAT(
TRUNCATE
(
SUM
( max_data_length ) /1024 /1024, 2 ) ,
'MB'
)
AS
max_data_size,
CONCAT(
TRUNCATE
(
SUM
( data_free ) /1024 /1024, 2 ) ,
'MB'
)
AS
data_free,
CONCAT(
TRUNCATE
(
SUM
( index_length ) /1024 /1024, 2 ) ,
'MB'
)
AS
index_size
FROM
information_schema.tables
WHERE
TABLE_NAME =
'db_name.table_name'
limit 0, 30;
|
|
1
2
3
4
|
/*查看数据库大小*/
SELECT
sum
(DATA_LENGTH)+
sum
(INDEX_LENGTH)
AS
SIZE
FROM
information_schema.TABLES
where
TABLE_SCHEMA=
'db_name'
;
得到的结果是以字节为单位，除1024为K，除1048576(=1024*1024)为M。
|
|
1
2
3
4
5
6
7
8
9
|
/*查看表的最后mysql修改时间*/
select
TABLE_NAME,UPDATE_TIME
from
information_schema.TABLES
where
TABLE_SCHEMA=
'db_name'
order
by
UPDATE_TIME
desc
limit 1;
select
TABLE_NAME,UPDATE_TIME
from
information_schema.TABLES
where
TABLE_SCHEMA=
'db_name'
and
information_schema.TABLES.TABLE_NAME =
'tb_name'
;
|
|
1
2
3
4
|
/* 调优必备 返回语句执行的过程信息 */
explain [extended]
select
...
from
...
where
...
/* */
ANALYZE
TABLE
tb_name;
|
|
1
2
|
/* 查看表结构 */
DESCRIBE db_name.tb_name
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
/* 查看变量 */
SHOW [
GLOBAL
| SESSION] VARIABLES [like_or_where]
/*查看运行状态 */
SHOW STATUS;
/*查看进程*/
show processlist;
/*查看运行比较久的事务*/
SELECT
*
FROM
INFORMATION_SCHEMA.INNODB_TRX;
/*kill ID */
kill 7
/*查看正在锁的事务*/
SELECT
*
FROM
INFORMATION_SCHEMA.INNODB_LOCKS;
/*查看等待锁的事务*/
SELECT
*
FROM
INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
本文转自 拖鞋崽 51CTO博客，原文链接:http://blog.51cto.com/1992mrwang/1249997
|
