查看 MySQL 服务器运行的各种状态值：
mysql> show global status；
1. 慢查询
mysql> show variables like '%slow%'; +---------------------------+-------------------------------+ | Variable_name | Value | +---------------------------+-------------------------------+ | log_slow_admin_statements | OFF | | log_slow_slave_statements | OFF | | slow_launch_time | 2 | | slow_query_log | ON | | slow_query_log_file | /var/db/my3306/logs/slows.log | +---------------------------+-------------------------------+ 5 rows in set (0.00 sec) mysql> show global status like '%slow%'; +---------------------+-------+ | Variable_name | Value | +---------------------+-------+ | Slow_launch_threads | 0 | | Slow_queries | 18384 | +---------------------+-------+
打开慢查询日志可能会对系统性能有一点儿影响，如果你的 MySQL 是主-从结构，可以考虑打开其中一条从服务器的慢查询日志，这样既可以监控慢查询，对系统性能的影响也会很小。
显示哪些线程正在运行：
mysql> show processlist; +------------+-----------------+--------------------+------------+-------------+----------+-----------------------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +------------+-----------------+--------------------+------------+-------------+----------+-----------------------------------------------------------------------+------------------+ | 1 | event_scheduler | localhost | NULL | Daemon | 42 | Waiting for next activation | NULL | | 1060236184 | my_sync | 172.16.88.33:60726 | NULL | Binlog Dump | 21244918 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL | | 4874532774 | athena | 172.16.88.12:46808 | NULL | Sleep | 6945 | | NULL | | 4875812388 | california | 172.16.88.11:59687 | california | Sleep | 8 | | NULL | | 4875812768 | california | 172.16.88.11:59698 | california | Sleep | 5 | | NULL | | 4875813123 | athena | 172.16.88.12:45683 | NULL | Query | 0 | init | show processlist | | 4875813758 | california | 172.16.88.11:59711 | california | Sleep | 1 | | NULL | | 4875813759 | california | 172.16.88.11:59713 | california | Sleep | 1 | | NULL | | 4875813760 | california | 172.16.88.11:59712 | california | Sleep | 1 | | NULL | | 4875813915 | athena | 172.16.88.12:50003 | athena | Sleep | 0 | | NULL | | 4875813916 | athena | 172.16.88.12:50004 | athena | Sleep | 0 | | NULL | | 4875813917 | athena | 172.16.88.12:50005 | athena | Sleep | 0 | | NULL | +------------+-----------------+--------------------+------------+-------------+----------+-----------------------------------------------------------------------+------------------+ 12 rows in set (0.00 sec)
若发现大量的 unauthenticated user 连接，数据库每次肯定都要响应，索引速速越来越慢，在 mysql.conf 里添加 skip-name-resolve 即可，表示不启用 DNS 反应解析。
再看 State 这列，若长期处于 Sending data (正在处理SELECT查询的记录，同时正在把结果发送给客户端)状态，则可怀疑是磁盘 I/O 压力过大，可用 explain 来分析语句。
mysql> explain SELECT count(new_cheat_id) FROM new_cheat WHERE account_id='14348612' AND offer_id='689'G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: new_cheat type: ALL possible_keys: NULL key:NULL key_len: NULL ref: NULL rows: 2529529 Extra: Using where 1 row in set (0.00 sec)
从结果可看出来此表没建索引，导致一次查询扫描了2529529行记录。
建立好索引后，用 show index 查看表索引：
mysql> show index from xk_order_ready_5; +-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | new_cheat | 0 | PRIMARY | 1 | new_cheat_id| A | 2577704 | NULL | NULL | | BTREE | | | | new_cheat | 1 | ip | 1 | ip | A | 1288852 | NULL | NULL | | BTREE | | | | new_cheat | 1 | account_id| 1 | account_id | A | 1288852 | NULL | NULL | | BTREE | | | +-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0.00 sec)
再次查看 explain 结果：
mysql> explain SELECT count(new_cheat_id) FROM new_cheat WHERE account_id='14348612' AND offer_id='689'G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: new_cheat type: ref possible_keys: account_id key: account_id key_len: 4 ref: const rows: 6 Extra: Using where 1 row in set (0.00 sec)
可以发现直接读取了6条记录就获取了查询结果。