es | 0 |
| Com_update | 0 | ——执行update操作次数
| Com_update_multi | 0 |
| Com_xa_commit | 0 |
| Com_xa_end | 0 |
| Com_xa_prepare | 0 |
| Com_xa_recover | 0 |
| Com_xa_rollback | 0 |
| Com_xa_start | 0 |
| Compression | OFF |
+---------------------------+-------+
142 rows in set (0.00 sec)
?
mysql>show status like 'Innodb_%';
+---------------------------------------+-------------+
| Variable_name | Value |
+---------------------------------------+-------------+
| Innodb_buffer_pool_dump_status | not started |
| Innodb_buffer_pool_load_status |not started |
| Innodb_buffer_pool_pages_data | 4436 |
| Innodb_buffer_pool_bytes_data | 72679424 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_bytes_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 2188 |
| Innodb_buffer_pool_pages_free | 3744 |
| Innodb_buffer_pool_pages_misc | 11 |
| Innodb_buffer_pool_pages_total | 8191 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 3328 |
| Innodb_buffer_pool_read_ahead_evicted |0 |
| Innodb_buffer_pool_read_requests | 2182160 |
| Innodb_buffer_pool_reads | 552 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 237898 |
| Innodb_data_fsyncs | 1721 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 65753088 |
| Innodb_data_reads | 3910 |
| Innodb_data_writes | 3630 |
| Innodb_data_written | 79650304 |
| Innodb_dblwr_pages_written | 2188 |
| Innodb_dblwr_writes | 76 |
| Innodb_have_atomic_builtins | ON |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 15742 |
| Innodb_log_writes | 897 |
| Innodb_os_log_fsyncs | 980 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 7911424 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 557 |
| Innodb_pages_read | 3879 |
| Innodb_pages_written | 2188 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
| Innodb_rows_deleted | 0 |——执行delete删除的行数
| Innodb_rows_inserted | 46350 |——执行insert操作插入的行数
| Innodb_rows_read | 1733860 | ——select查询返回的行数。
| Innodb_rows_updated | 0 |——执行update更新的行数
| Innodb_num_open_files | 70 |
| Innodb_truncated_status_writes | 0 |
| Innodb_available_undo_logs | 128 |
+---------------------------------------+-------------+
51 rows in set (0.00 sec)
?
mysql>show status like 'connections';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections | 36 | ——试图连接MYSQL服务器的次数
+---------------+-------+
1 row in set (0.00 sec
mysql>show status like 'uptime';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Uptime | 421477 |——数据库运行时间
?
+---------------+--------+
1 row in set (0.00 sec)
mysql>show status like 'slow_queries';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries | 0 | ——慢查次数
+---------------+-------+
1 row in set (0.00 sec)
2. 定位执行效率较低的SQL
1、通过慢查日志定位哪些执行效率较低的SQL语句：--log-slow-queries=filename 启动时，mysqld写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件。
2、慢查日志在查询结束后才记录，所以应用反映执行效率出现问题的时候，查询慢查询日志并不能定位问题，可以通过show processlist命令来查看当前mysql的进行线程，包括线程状态和是否锁表等，可以时时查看SQL的执行情况，同事对锁表操作进行优化。
?
3. 通过explain分析SQL的执行计划
mysql>expl