一. 查看库的各链接状态
对于一个mysql连接或者一个线程，任何时刻都有一个状态，表示其当前正在做什么。一般使用show full processlist查看。
+---------+-------------+-------------------+------+---------+------+-------+-----------------------+| Id | User | Host | db | Command | Time | State | Info |+---------+-------------+-------------------+------+---------+------+-------+-----------------------+| 2547225 | operator@RR | 10.3.18.205:60253 | fb | Query | 0 | NULL | show full processlist || 2548100 | operator@RR | 10.4.19.83:51754 | fb | Sleep | 1 | | NULL || 2548101 | operator@RR | 10.4.19.83:53661 | fb | Sleep | 1 | | NULL || 2548102 | operator@RR | 10.4.19.83:45808 | fb | Sleep | 1 | | NULL || 2548103 | operator@RR | 10.4.19.83:58881 | fb | Sleep | 1 | | NULL || 2548104 | operator@RR | 10.4.19.83:33521 | fb | Sleep | 1 | | NULL |+---------+-------------+-------------------+------+---------+------+-------+-----------------------+
二. 查看存储引擎
2.1 查看当前库支持的存储引擎
保存在information_schema.ENGINES表里。
SHOW ENGINES
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+| Engine | Support | Comment | Transactions | XA | Savepoints |+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO || CSV | YES | CSV storage engine | NO | NO | NO || MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO || BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO || MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO || MyISAM | YES | MyISAM storage engine | NO | NO | NO || ARCHIVE | YES | Archive storage engine | NO | NO | NO || InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES || FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
2.2 查看某存储引擎的状态
SHOW ENGINE INNODB STATUS
=====================================2016-01-15 10:12:44 7f4a4c4b6700 INNODB MONITOR OUTPUT=====================================Per second averages calculated from the last 38 seconds-----------------BACKGROUND THREAD-----------------srv_master_thread loops: 1 srv_active, 0 srv_shutdown, 172619 srv_idlesrv_master_thread log flush and writes: 172620----------SEMAPHORES----------OS WAIT ARRAY INFO: reservation count 3OS WAIT ARRAY INFO: signal count 3Mutex spin waits 40, rounds 47, OS waits 1RW-shared spins 2, rounds 60, OS waits 2RW-excl spins 0, rounds 0, OS waits 0Spin rounds per wait: 1.18 mutex, 30.00 RW-shared, 0.00 RW-excl------------TRANSACTIONS------------Trx id counter 1799Purge done for trx's n:o < 0 undo n:o < 0 state: running but idleHistory list length 0LIST OF TRANSACTIONS FOR EACH SESSION:---TRANSACTION 0, not startedMySQL thread id 152, OS thread handle 0x7f4a4c4b6700, query id 137 192.168.122.45 adu_w initshow engine innodb status--------FILE I/O--------I/O thread 0 state: waiting for completed aio requests (insert buffer thread)I/O thread 1 state: waiting for completed aio requests (log thread)I/O thread 2 state: waiting for completed aio requests (read thread)I/O thread 3 state: waiting for completed aio requests (read thread)I/O thread 4 state: waiting for completed aio requests (read thread)I/O thread 5 state: waiting for completed aio requests (read thread)I/O thread 6 state: waiting for completed aio requests (read thread)I/O thread 7 state: waiting for completed aio requests (read thread)I/O thread 8 state: waiting for completed aio requests (read thread)I/O thread 9 state: waiting for completed aio requests (read thread)I/O thread 10 state: waiting for completed aio requests (write thread)I/O thread 11 state: waiting for completed aio requests (write thread)I/O thread 12 state: waiting for completed aio requests (write thread)I/O thread 13 state: waiting for completed aio requests (write thread)I/O thread 14 state: waiting for completed aio requests (write thread)I/O thread 15 state: waiting for completed aio requests (write thread)I/O thread 16 state: waiting for completed aio requests (write thread)I/O thread 17 state: waiting for completed aio requests (write thread)Pending normal aio reads: 0 [0, 0, 0, 0, 0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0, 0, 0, 0, 0] , ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0Pending flushes (fsync) log: 0; buffer pool: 0190 OS file reads, 5 OS file writes, 5 OS fsyncs0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s-------------------------------------INSERT BUFFER AND ADAPTIVE HASH INDEX-------------------------------------Ibuf: size 1, free list len 0, seg size 2, 0 mergesmerged operations: insert 0, delete mark 0, delete 0discarded operations: insert 0, delete mark 0, delete 00.00 hash searches/s, 0.00 non-hash searches/s---LOG---Log sequence number 1626012Log flushed up to 1626012Pages flushed up to 1626012Last checkpoint at 1626012Max checkpoint age 867604194Checkpoint age target 840491563Modified age 0Checkpoint age 00 pending log writes, 0 pending chkp writes8 log i/o's done, 0.00 log i/o's/second----------------------BUFFER POOL AND MEMORY----------------------Total memory allocated 1098907648; in additional pool allocated 0Total memory allocated by read views 88Internal hash tables (constant factor + variable factor) Adaptive hash index 18926128 (18921928 + 4200) Page hash 139112 (buffer pool 0 only) Dictionary cache 4487029 (4426736 + 60293) File system 815920 (812272 + 3648) Lock system 2657536 (2657176 + 360) Recovery system 0 (0 + 0)Dictionary memory allocated 60293Buffer pool size 65528Buffer pool size, bytes 1073610752Free buffers 65354Database pages 174Old database pages 0Modified db pages 0Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 0, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 174, created 0, written 10.00 reads/s, 0.00 creates/s, 0.00 writes/sNo buffer pool page gets since the last printoutPages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 174, unzip_LRU len: 0I/O sum[0]:cur[0], unzip sum[0]:cur[0]----------------------INDIVIDUAL BUFFER POOL INFO-------------------------BUFFER POOL 0Buffer pool size 8191Buffer pool size, bytes 134201344Free buffers 8159Database pages 32Old database pages 0Modified db pages 0Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 0, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 32, created 0, written 10.00 reads/s, 0.00 creates/s, 0.00 writes/sNo buffer pool page gets since the last printoutPages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 32, unzip_LRU len: 0I/O sum[0]:cur[0], unzip sum[0]:cur[0]---BUFFER POOL 1Buffer pool size 8191Buffer pool size, bytes 134201344Free buffers 8187Database pages 4Old database pages 0Modified db pages 0Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 0, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 4, created 0, written 00.00 reads/s, 0.00 creates/s, 0.00 writes/sNo buffer pool page gets since the last printoutPages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 4, unzip_LRU len: 0I/O sum[0]:cur[0], unzip sum[0]:cur[0]---BUFFER POOL 2Buffer pool size 8191Buffer pool size, bytes 134201344Free buffers 8187Database pages 4Old database pages 0Modified db pages 0Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 0, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 4, created 0, written 00.00 reads/s, 0.00 creates/s, 0.00 writes/sNo buffer pool page gets since the last printoutPages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 4, unzip_LRU len: 0I/O sum[0]:cur[0], unzip sum[0]:cur[0]---BUFFER POOL 3Buffer pool size 8191Buffer pool size, bytes 134201344Free buffers 8123Database pages 68Old database pages 0Modified db pages 0Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 0, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 68, created 0, written 00.00 reads/s, 0.00 creates/s, 0.00 writes/sNo buffer pool page gets since the last printoutPages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 68, unzip_LRU len: 0I/O sum[0]:cur[0], unzip sum[0]:cur[0]---BUFFER POOL 4Buffer pool size 8191Buffer pool size, bytes 134201344Free buffers 8129Database pages 62Old database pages 0Modified db pages 0Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 0, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 62, created 0, written 00.00 reads/s, 0.00 creates/s, 0.00 writes/sNo buffer pool page gets since the last printoutPages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 62, unzip_LRU len: 0I/O sum[0]:cur[0], unzip sum[0]:cur[0]---BUFFER POOL 5Buffer pool size 8191Buffer pool size, bytes 134201344Free buffers 8187Database pages 4Old database pages 0Modified db pages 0Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 0, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 4, created 0, written 00.00 reads/s, 0.00 creates/s, 0.00 writes/sNo buffer pool page gets since the last printoutPages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 4, unzip_LRU len: 0I/O sum[0]:cur[0], unzip sum[0]:cur[0]---BUFFER POOL 6Buffer pool size 8191Buffer pool size, bytes 134201344Free buffers 8191Database pages 0Old database pages 0Modified db pages 0Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 0, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 0, created 0, written 00.00 reads/s, 0.00 creates/s, 0.00 writes/sNo buffer pool page gets since the last printoutPages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 0, unzip_LRU len: 0I/O sum[0]:cur[0], unzip sum[0]:cur[0]---BUFFER POOL 7Buffer pool size 8191Buffer pool size, bytes 134201344Free buffers 8191Database pages 0Old database pages 0Modified db pages 0Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 0, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 0, created 0, written 00.00 reads/s, 0.00 creates/s, 0.00 writes/sNo buffer pool page gets since the last printoutPages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 0, unzip_LRU len: 0I/O sum[0]:cur[0], unzip sum[0]:cur[0]--------------ROW OPERATIONS--------------0 queries inside InnoDB, 0 queries in queue0 read views open inside InnoDB0 RW transactions active inside InnoDB0 RO transactions active inside InnoDB0 out of 1000 descriptors usedMain thread process no. 3572, id 139955545581312, state: sleepingNumber of rows inserted 0, updated 0, deleted 0, read 00.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s----------------------------END OF INNODB MONITOR OUTPUT============================
二. 查看表状态
2.1 单表详情
数据库各表的信息都保存在INFORMATION_SCHEMA-->TABLES表里。可以直接在此表查询也可以通过SHOW TABLE STATUS命令来查询。
SHOW TABLE STATUS LIKE 'user'\G;
结果：
*************************** 1. row *************************** Name: user(表名) Engine: InnoDB(存储引擎) Version: 10 Row_format: Compact(行的格式，是否固定或压缩) Rows: 4(行数，对于MyISAM该值是精确的，但对于InnoDB该值是估计值) Avg_row_length: 4096(平均每行的字节数) Data_length: 16384(表数据总的字节数)Max_data_length: 0(表数据的最大容量，和存储引擎有关) Index_length: 0(索引的大小B) Data_free: 7340032(对于MyISAM表示已分配但没有使用的空间) Auto_increment: 5(下一个AUTO_INCREMENT值) Create_time: 2014-06-17 16:45:53(表的创建时间) Update_time: NULL(表数据的最后修改时间) Check_time: NULL(使用CHECK TABLE或myisamchk检查表的时间) Collation: utf8_bin(表的默认字符集和字符列排序规则) Checksum: NULL(整个表的实时检验和) Create_options: (创建表时指定的其他选项) Comment: 1 row in set (0.00 sec)
通过查询资料发现需要设置collate（校对） 。 collate规则：
- *_bin: 表示的是binary case sensitive collation，也就是说是区分大小写的
- *_cs: case sensitive collation，区分大小写
- *_ci: case insensitive collation，不区分大小写
2.2 各表的占用空间大小
SELECT CONCAT(table_schema,'.',table_name) AS 'Table Name', table_rows AS 'Number of Rows', CONCAT(ROUND(data_length/(1024*1024),3),' MB') AS 'Data Size', CONCAT(ROUND(index_length/(1024*1024),3),' MB') AS 'Index Size', CONCAT(ROUND((data_length+index_length)/(1024*1024),3),' MB') AS 'Total Size'FROM information_schema.TABLESWHERE table_schema = 'db_name' AND table_name = 'table_name';
三. 查看执行计划
EXPLAIN sql...
explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。EXPLAIN SELECT id FROM user WHERE id=1 \G;
结果：
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: user type: constpossible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: Using index1 row in set (0.02 sec)
| 列名 | 类型 | 解释 |
|---|---|---|
| id | SELECT语句的ID编号,优先执行编号较大的查询,如果编号相同,则从上向下执行 | |
| select_type | SIMPLE | 一条没有UNION或子查询部分的SELECT语句 |
| PIMARY | 最外层或最左侧的SELECT语句 | |
| UNION | UNION语句里的第二条或最后一条SELECT语句 | |
| DEPENDENT UNION | 和UNION类型的含义相似,但需要依赖于某个外层查询 | |
| UNION RESULT | 一条UNION语句的结果 | |
| SUBQUERY | 子查询中的第一个SELECT子句 | |
| DEPENDENT SUBQUERY | 和SUBQUERY类型的含义相似,但需要依赖于某个外层查询 | |
| DERIVED | FROM子句里的子查询 | |
| table | t1 | 各输出行里的信息是关于哪个数据表的 |
| Partitions | NULL | 将要使用的分区.只有EXPLAIN PARTITIONS ...语句才会显示这一列.非分区表显示为NULL |
| type | 联接操作的类型,性能由好到差依次如下 | |
| system | 表中仅有一行 | |
| const | 单表中最多有一个匹配行 | |
| eq_ref | 联接查询中,对于前表的每一行,在此表中只查询一条记录,使用了PRIMARY或UNIQUE | |
| ref | 联接查询中,对于前表的每一行,在此表中只查询一条记录,使用了INDEX | |
| ref_or_null | 联接查询中,对于前表的每一行,在此表中只查询一条记录,使用了INDEX,但是条件中有NULL值查询 | |
| index_merge | 多个索引合并 | |
| unique_subquery | 举例说明: value IN (SELECT primary_key FROM single_table WHERE some_expr) | |
| index_subquery | 举例说明: value IN (SELECT key_column FROM single_table WHERE some_expr) | |
| range | 只检索给定范围的行,包括如下操作符: =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, or IN() | |
| index | 扫描索引树(略比ALL快,因为索引文件通常比数据文件小) | |
| ALL | 前表的每一行数据都要跟此表匹配,全表扫描 | |
| possible_keys | NULL | MySQL认为在可能会用到的索引.NULL表示没有找到索引 |
| key | NULL | 检索时,实际用到的索引名称.如果用了index_merge联接类型,此时会列出多个索引名称,NULL表示没有找到索引 |
| key_len | NULL | 实际使用的索引的长度.如果是复合索引,那么只显示使用的最左前缀的大小 |
| ref | NULL | MySQL用来与索引值比较的值, 如果是单词const或者???,则表示比较对象是一个常数.如果是某个数据列的名称,则表示比较操作是逐个数据列进行的.NULL表示没有使用索引 |
| rows | MySQL为完成查询而需要在数据表里检查的行数的估算值.这个输出列里所有的值的乘积就是必须检查的数据行的各种可能组合的估算值 | |
| Extra | Using filesort | 需要将索引值写到文件中并且排序,这样按顺序检索相关数据行 |
| Using index | MySQL可以不必检查数据文件, 只使用索引信息就能检索数据表信息 | |
| Using temporary | 在使用 GROUP BY 或 ORDER BY 时,需要创建临时表,保存中间结果集 | |
| Using where | 利用SELECT语句中的WHERE子句里的条件进行检索操作 |