解决一个mysql慢查询
mysql慢查询可以记录那些执行过慢的sql语句，在分析问题的过程中非常有用。
这里记录了一个mysql慢查询的例子。其实一句话概括就是关联字段需要使用索引。
在这个例子里有些基础的东西可以供新手了解：
查询mysql变量
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 | OFF |
| slow_query_log_file | /var/lib/mysql/b7ff8fec3869-slow.log |
+---------------------------+--------------------------------------+
5 rows in set (0.01 sec)
修改mysql变量
mysql> set global log_slow_admin_statements=off
-> ;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql> show variables like '%slow%';
+---------------------------+--------------------------------------+
| Variable_name | Value |
+---------------------------+--------------------------------------+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 3 |
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/b7ff8fec3869-slow.log |
+---------------------------+--------------------------------------+
5 rows in set (0.00 sec)
mysql参数表
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
31 rows in set (0.00 sec)
mysql> desc slow_log
-> ;
+----------------+---------------------+------+-----+----------------------+--------------------------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------------+------+-----+----------------------+--------------------------------+
| start_time | timestamp(6) | NO | | CURRENT_TIMESTAMP(6) | on update CURRENT_TIMESTAMP(6) |
| user_host | mediumtext | NO | | NULL | |
| query_time | time(6) | NO | | NULL | |
| lock_time | time(6) | NO | | NULL | |
| rows_sent | int(11) | NO | | NULL | |
| rows_examined | int(11) | NO | | NULL | |
| db | varchar(512) | NO | | NULL | |
| last_insert_id | int(11) | NO | | NULL | |
| insert_id | int(11) | NO | | NULL | |
| server_id | int(10) unsigned | NO | | NULL | |
| sql_text | mediumblob | NO | | NULL | |
| thread_id | bigint(21) unsigned | NO | | NULL | |
+----------------+---------------------+------+-----+----------------------+--------------------------------+
12 rows in set (0.07 sec)
加入索引只是mysql优化的一部分，还有许多其他需要注意的问题在这里：