查-MySQL 常用语句速查

官方文档

一、变量

1.show variables

show variables where variable_name like '%max_connections%'

mysqlx_max_connections为x协议的最大连接数，仍受max_connections参数影响。

2.set persist

set persist max_connections=300;
show variables like '%max_connections%';
set persist max_connections=default;
select * from performance_schema.persisted_variables;

二、状态查询

1.锁状态查询

-- 事务状态
SELECT  * FROM INFORMATION_SCHEMA.INNODB_TRX;
-- 锁状态
SELECT  * FROM PERFORMANCE_SCHEMA.DATA_LOCKS;
-- 锁等待状态
SELECT * FROM PERFORMANCE_SCHEMA.DATA_LOCK_WAITS;

三、show语句

1.show full processlist

SELECT user, host, time, command, time
  FROM [mysql|information_schema].processlist
 WHERE user = 'me' and state IS NOT NULL;

2.show warnings（利器）

2.1 warnings;和nowarning；

warnings；效果等同于 \W启用warnings信息输出；
nowarning效果等同于\w禁用warnings信息输出；
推荐使用MySQL原生命令终端，navicat命令列界面不可以。

mysql -uroot -p
mysql> warnings;
Show warnings enabled.
mysql> \w
Show warnings disabled.
mysql> \W
Show warnings enabled.
mysql> explain select 1/0;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

Note (Code 1003): /* select#1 */ select (1 / 0) AS `1/0`
mysql> exit

2.2 mysql -uroot -p --show-warnings

推荐使用MySQL原生命令终端，navicat命令列界面不可以。

mysql> explain select 1/0;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

Note (Code 1003): /* select#1 */ select (1 / 0) AS `1/0`

2.3 show warnings;

mysql> explain select 1/0;                                                                                                                            
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+                           
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |                           
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+                           
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |                           
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+                           
1 row in set, 1 warning (0.00 sec)                                                                                                                    
                                                                                                                                                      
mysql> show warnings;                                                                                                                                 
+-------+------+----------------------------------------+                                                                                             
| Level | Code | Message                                |                                                                                             
+-------+------+----------------------------------------+                                                                                             
| Note  | 1003 | /* select#1 */ select (1 / 0) AS `1/0` |                                                                                             
+-------+------+----------------------------------------+                                                                                             
1 row in set (0.00 sec)                                                                                                                               
                                                                                                                                                      
mysql>                                                                                                                                                

3.show profiles（利器）

mysql> show variables like '%profil%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |
| profiling              | OFF   |
| profiling_history_size | 15    |
+------------------------+-------+
3 rows in set (0.01 sec)

mysql> set profiling = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> show profiles;
+----------+------------+--------------------------------+
| Query_ID | Duration   | Query                          |
+----------+------------+--------------------------------+
|        1 | 0.00248475 | show variables like '%profil%' |
+----------+------------+--------------------------------+
1 row in set (0.01 sec)
mysql> show profiles;
+----------+------------+--------------------------------+
| Query_ID | Duration   | Query                          |
+----------+------------+--------------------------------+
|        1 | 0.00248475 | show variables like '%profil%' |     |
|        2 | 0.00143050 | select * from sys_user         |
+----------+------------+--------------------------------+
2 rows in set (0.03 sec)

mysql> show profile for query 2;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000065 |
| checking permissions | 0.000004 |
| Opening tables       | 0.001158 |
| init                 | 0.000021 |
| System lock          | 0.000007 |
| optimizing           | 0.000003 |
| statistics           | 0.000007 |
| preparing            | 0.000006 |
| executing            | 0.000001 |
| Sending data         | 0.000072 |
| end                  | 0.000002 |
| query end            | 0.000005 |
| closing tables       | 0.000006 |
| freeing items        | 0.000066 |
| cleaning up          | 0.000009 |
+----------------------+----------+
15 rows in set (0.05 sec)
mysql> show profile all for query 2;
mysql> show profile cpu,block io for query 2;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000065 | 0.000000 | 0.000000   | NULL         | NULL          |
| checking permissions | 0.000004 | 0.000000 | 0.000000   | NULL         | NULL          |
| Opening tables       | 0.001158 | 0.000000 | 0.000000   | NULL         | NULL          |
| init                 | 0.000021 | 0.000000 | 0.000000   | NULL         | NULL          |
| System lock          | 0.000007 | 0.000000 | 0.000000   | NULL         | NULL          |
| optimizing           | 0.000003 | 0.000000 | 0.000000   | NULL         | NULL          |
| statistics           | 0.000007 | 0.000000 | 0.000000   | NULL         | NULL          |
| preparing            | 0.000006 | 0.000000 | 0.000000   | NULL         | NULL          |
| executing            | 0.000001 | 0.000000 | 0.000000   | NULL         | NULL          |
| Sending data         | 0.000072 | 0.000000 | 0.000000   | NULL         | NULL          |
| end                  | 0.000002 | 0.000000 | 0.000000   | NULL         | NULL          |
| query end            | 0.000005 | 0.000000 | 0.000000   | NULL         | NULL          |
| closing tables       | 0.000006 | 0.000000 | 0.000000   | NULL         | NULL          |
| freeing items        | 0.000066 | 0.000000 | 0.000000   | NULL         | NULL          |
| cleaning up          | 0.000009 | 0.000000 | 0.000000   | NULL         | NULL          |
+----------------------+----------+----------+------------+--------------+---------------+
15 rows in set (0.05 sec)

4.优化器跟踪（神器）

SHOW VARIABLES LIKE '%optimizer%';
SET optimizer_trace = "enabled=on";
SHOW VARIABLES LIKE '%optimizer%';
SELECT * from staff where name= '小明' limit 1; 
SELECT * FROM information_schema.optimizer_trace;

四、索引

1.前缀索引

ALTER TABLE mytable ADD INDEX idx_email(email(6)) USING BTREE;

五、其它

1.模拟慢SQL

select SLEEP(5);

2.表结构导出

SELECT
 TABLE_NAME 表名,
 COLUMN_NAME 列名,
 COLUMN_TYPE 数据类型,
 DATA_TYPE 字段类型,
 CHARACTER_MAXIMUM_LENGTH 长度,
 IS_NULLABLE 是否为空,
 COLUMN_DEFAULT 默认值,
 COLUMN_COMMENT 备注 
FROM
 INFORMATION_SCHEMA.COLUMNS
where
table_schema ='mysql'

MySQL 8 全局变量的修改持久化 set persist
Mysql分析-profile详解
搞定这5大优化思路+10个案例，哪里还有慢SQL？