我們知道m(xù)ysql中參數(shù)變量分為session 會話級別和global 全局級別。
如long_query_time 就是一個典型的會話級別參數(shù)辽狈。
set session long_query_time = 1.5; 設置后僅僅在當前會話中有效糊秆。重新打開一個會話仍然是默認值。session 可以省略
set global long_query_time =1.5 ；設置后重新打開一個會話勋锤，重新打開一個會話，值是1.5
注意：我們將一個會話級別的參數(shù)如long_query_time 的global 全局范圍值修改為新的值侥祭，那么以后新創(chuàng)建的會話會是這個新的值叁执。但是對于原來已經創(chuàng)建的連接，它們的參數(shù)值還是老樣子矮冬。很多線上修改話級別的參數(shù)不生效就是這個原因（需要set session一下才能影響到當前會話）谈宛。
通過這樣來查看當前會話和全局的參數(shù)值：
show session variables like '%long_query_time%';
show global variables like '%long_query_time %';
但是這樣的show session variables 只能查看自己會話的參數(shù)值，而別的會話的參數(shù)值是什么情況我們不知道呀胎署，所以5.7版本推出了一種方式來查詢：
(root@localhost) [performance_schema]>select * from performance_schema.variables_by_thread where variable_name='long_query_time';
+-----------+-----------------+----------------+
| THREAD_ID | VARIABLE_NAME | VARIABLE_VALUE |
+-----------+-----------------+----------------+
| 29 | long_query_time | 10.000000 |
| 30 | long_query_time | 1.500000 |
| 31 | long_query_time | 10.000000 |
+-----------+-----------------+----------------+
3 rows in set (0.00 sec)
thread_id 為30的線程對應long_query_time值為1.5吆录，我們還想知道具體是哪個process 連接的值是1.5？
5.7提供了這個performance_schema.threads表琼牧，它保存了THREAD_ID和PROCESSLIST_ID還有THREAD_OS_ID的對應關系恢筝。
(root@localhost) [performance_schema]>select * from performance_schema.threads limit 1 \G
*************************** 1. row ***************************
THREAD_ID: 1
NAME: thread/sql/main
TYPE: BACKGROUND
PROCESSLIST_ID: NULL
PROCESSLIST_USER: NULL
PROCESSLIST_HOST: NULL
PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: NULL
PROCESSLIST_TIME: 22857
PROCESSLIST_STATE: NULL
PROCESSLIST_INFO: NULL
PARENT_THREAD_ID: NULL
ROLE: NULL
INSTRUMENTED: YES
HISTORY: YES
CONNECTION_TYPE: NULL
THREAD_OS_ID: 26446
1 row in set (0.00 sec)
那么我們查下THREAD_ID =30 的記錄
(root@localhost) [performance_schema]>select * from threads where THREAD_ID =30 limit 1 \G
*************************** 1. row ***************************
THREAD_ID: 30
NAME: thread/sql/one_connection
TYPE: FOREGROUND
PROCESSLIST_ID: 5
PROCESSLIST_USER: root
PROCESSLIST_HOST: localhost
PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Sleep
PROCESSLIST_TIME: 333
PROCESSLIST_STATE: NULL
PROCESSLIST_INFO: set session long_query_time = 1.5
PARENT_THREAD_ID: NULL
ROLE: NULL
INSTRUMENTED: YES
HISTORY: YES
CONNECTION_TYPE: Socket
THREAD_OS_ID: 26480
1 row in set (0.00 sec)
得到了 THREAD_ID: 30對應的 PROCESSLIST_ID: 5
得出結論：那么show processlist 結果的第二條連接的long_query_time的值是1.5
(root@localhost) [performance_schema]>show processlist;
+----+------+-------------------+--------------------+---------+-------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-------------------+--------------------+---------+-------+----------+------------------+
| 4 | root | 192.168.6.1:20193 | mysql | Sleep | 17649 | | NULL |
| 5 | root | localhost | NULL | Sleep | 2500 | | NULL |
| 6 | root | localhost | performance_schema | Query | 0 | starting | show processlist |
+----+------+-------------------+--------------------+---------+-------+----------+------------------+
processlistId 對應thread id哀卫，然后又對應操作系統(tǒng)進程的線程id thread_os_id
那么上面操作可以使用join來直接完成：
select a.processlist_id,
a.thread_id,
a.thread_os_id,
a.processlist_user,
a.processlist_host,
a.processlist_db,
a.processlist_command,
a.processlist_state,
a.processlist_info,
b.* from performance_schema.threads a ,performance_schema.variables_by_thread b where a.thread_id=b.thread_id and b.variable_name='long_query_time';
結果如下
(root@localhost) [performance_schema]>select a.processlist_id, a.thread_id, a.thread_os_id, a.processlist_user, a.processlist_host, a.processlist_db, a.processlist_command, a.processlist_state, a.processlist_info, b.* from performance_schema.threads a ,performance_schema.variables_by_thread b where a.thread_id=b.thread_id and b.variable_name='long_query_time' limit 1\G
*************************** 1. row ***************************
processlist_id: 4
thread_id: 29
thread_os_id: 26605
processlist_user: root
processlist_host: 192.168.6.1
processlist_db: mysql
processlist_command: Sleep
processlist_state: NULL
processlist_info: SELECT STATE AS `Status`, ROUND(SUM(DURATION),7) AS `Duration`, CONCAT(ROUND(SUM(DURATION)/0.003109*100,3), '') AS `Percentage` FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=152 GROUP BY SEQ, STATE ORDER BY SEQ
THREAD_ID: 29
VARIABLE_NAME: long_query_time
VARIABLE_VALUE: 10.000000
1 row in set (0.00 sec)
