mysql5 操作命令（1）

 

mysql5 操作命令（1）

 

/* 查看表大小 */

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;

 

/*查看数据库大小*/

SELECT (sum(DATA_LENGTH)+sum(INDEX_LENGTH))/1024*1024 AS SIZE FROM information_schema.TABLES
where TABLE_SCHEMA='db_name';

 得到的结果是以字节为单位，除1024为K，除1048576(=1024*1024)为M。

 

/* 调优必备 返回语句执行的过程信息 */

explain [extended] select ... from ... where ...

/* 分析表*/

ANALYZE TABLE tb_name;

/*查看表的索引使用情况*/

SHOW INDEX FROM tb_name;

 

查看mysql参数信息

select @@参数名

mysql> select @@innodb_lock_wait_timeout;
+----------------------------+
| @@innodb_lock_wait_timeout |
+----------------------------+
|                         20 |
+----------------------------+

 

临时设置参数名，退出后即失效

set @@参数名

set @@innodb_lock_wait_timeout=20;

 

 

1.获取mysql所有表

SELECT * FROM information_schema.TABLES; (WHERE TABLE_SCHEMA='数据库名称';) 

SELECT * FROM information_schema.TABLES WHERE TABLE_NAME='t001_user_remain';

 

2.获取表字段

SELECT * FROM information_schema.COLUMNS; (WHERE TABLE_NAME='表名'；) 

SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME='t001_user_remain';

 

3.获取表键值

SELECT * FROM information_schema.KEY_COLUMN_USAGE; (WHERE TABLE_NAME='表名') 

SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_NAME='t001_user_remain';

 

4.获取表check约束

SELECT * FROM information_schema.TABLE_CONSTRAINTS; (WHERE TABLE_NAME='表名';)

SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_NAME='t001_user_remain';

 

5.获取表索引

SELECT * FROM information_schema.STATISTICS; (WHERE TABLE_NAME='表名') 

SELECT * FROM information_schema.STATISTICS where TABLE_NAME='t001_user_remain'

 

提供了关于存储子程序（存储程序和函数）的信息。

SELECT * FROM information_schema.ROUTINES; 

 

给出了关于数据库中的视图的信息

SELECT * FROM information_schema.VIEWS;

 

提供了关于触发程序的信息

SELECT * FROM information_schema.TRIGGERS; 

 

 

描述了存在约束的表。以及表的约束类型。

SELECT * FROM information_schema.TABLE_CONSTRAINTS where TABLE_NAME='t001_user_remain';

 

描述了具有约束的键列

SELECT * FROM information_schema.KEY_COLUMN_USAGE where TABLE_NAME='t001_user_remain'; 

 

 

(字符集）表：提供了mysql实例可用字符集的信息

SELECT * FROM information_schema.CHARACTER_SETS; 

 

提供了关于各字符集的对照信息

SELECT * FROM information_schema.COLLATIONS; 

 

指明了可用于校对的字符集。这些列等效于COLLATION的前两个显示字段

SELECT * FROM information_schema.COLLATION_CHARACTER_SET_APPLICABILITY; 

 

用户权限表：给出了关于用户权限的信息。该信息源自mysql.user授权表。是非标准表。

SELECT * FROM information_schema.USER_PRIVILEGES;

 

方案权限表：给出了关于方案（数据库）权限的信息。该信息来自mysql.db授权表。是非标准表。

SELECT * FROM information_schema.SCHEMA_PRIVILEGES;

 

（表权限）表：给出了关于表权限的信息。该信息源自mysql.tables_priv授权表。是非标准表。

SELECT * FROM information_schema.TABLE_PRIVILEGES; 

 

（列权限）表：给出了关于列权限的信息。该信息源自mysql.columns_priv授权表。是非标准表。

SELECT * FROM information_schema.COLUMN_PRIVILEGES; 

 

 

 

 

显示mysql中所有数据库信息。

SELECT * FROM information_schema.SCHEMATA;

 

显示mysql中所有数据库中的表的信息（包括视图）。详细表述了某个表属于哪个数据库，表类型，表引擎，创建时间等详细信息。

SELECT * FROM information_schema.TABLES where TABLE_SCHEMA='mysql';

 

 

显示mysql中所有表中的列信息。详细表述了某张表的所有列以及每个列的详细信息。（类型，长度，字符集等）

SELECT * FROM information_schema.COLUMNS where TABLE_NAME='t001_user_remain'; 

 

显示了所有表索引的详细信息（主键，外键，所属数据库，列名等详细信息）

SELECT * FROM information_schema.STATISTICS where TABLE_NAME='t001_user_remain'; 

 

/*查看运行比较久的事务*/ 

SELECT * FROM information_schema.INNODB_TRX\G

mysql> SELECT * FROM information_schema.INNODB_TRX\G
*************************** 1. row ***************************
                    trx_id: 1743662
                 trx_state: LOCK WAIT
               trx_started: 2014-08-16 16:44:09
     trx_requested_lock_id: 1743662:209:3:2
          trx_wait_started: 2014-08-16 16:44:09
                trx_weight: 2
       trx_mysql_thread_id: 349
                 trx_query: update t018_project set pr_code='mmm' where pr_id=2
       trx_operation_state: starting index read
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 376
           trx_rows_locked: 1
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
          trx_is_read_only: 0
trx_autocommit_non_locking: 0

 

 

 查看锁信息，显示当前被锁的信息 /*查看正在锁的事务*/

 select * from information_schema.innodb_locks

mysql> select * from information_schema.innodb_locks;
+-----------------+-------------+-----------+-----------+---------------------------+------------+------------+-----------+----------+-----------+
| lock_id         | lock_trx_id | lock_mode | lock_type | lock_table                | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-----------------+-------------+-----------+-----------+---------------------------+------------+------------+-----------+----------+-----------+
| 1743661:209:3:2 | 1743661     | X         | RECORD    | `nowagame`.`t018_project` | PRIMARY    |        209 |         3 |        2 | 2         |
| 1743660:209:3:2 | 1743660     | X         | RECORD    | `nowagame`.`t018_project` | PRIMARY    |        209 |         3 |        2 | 2         |
+-----------------+-------------+-----------+-----------+---------------------------+------------+------------+-----------+----------+-----------+
2 rows in set (0.01 sec)

 

/*查看等待锁的事务*/

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;