SQL执行原理

• mysql8中测试
• 查看profiling 是否开启

select @@profiling;
 
show variables like 'profiling';

• profiling=0 代表关闭，我们需要把 profiling 打开，即设置为 1：

set profiling=1;

• 任意执行1条查询语句

MariaDB [jdbc]> select * from t_account;
+----+----------+-------+
| id | username | money |
+----+----------+-------+
| 1  | goudan   |   900 |
| 2  | gousheng |  1100 |
+----+----------+-------+
2 rows in set (0.000 sec)

• 查看

MariaDB [jdbc]> show profiles;
+----------+------------+---------------------------------+
| Query_ID | Duration   | Query                           |
+----------+------------+---------------------------------+
|        1 | 0.00133903 | show variables like 'profiling' |
|        2 | 0.00015757 | select @@profiling              |
|        3 | 0.00007482 | select * from t_account         |
|        4 | 0.00007830 | select * from t_account         |
|        5 | 0.00006953 | select * from t_account         |
|        6 | 0.00006093 | select * from t_account         |
|        7 | 0.00006677 | select * from t_account         |
|        8 | 0.00007320 | select * from t_account         |
|        9 | 0.00007997 | select * from t_account         |
|       10 | 0.00007702 | select * from t_account         |
|       11 | 0.00006816 | select * from t_account         |
+----------+------------+---------------------------------+
11 rows in set (0.000 sec)
 
MariaDB [jdbc]> show profile;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| Starting                       | 0.000025 |
| Waiting for query cache lock   | 0.000004 |
| Init                           | 0.000003 |
| Checking query cache for query | 0.000007 |
| Checking privileges on cached  | 0.000004 |
| Checking permissions           | 0.000008 |
| Sending cached result to clien | 0.000010 |
| Updating status                | 0.000004 |
| Reset for next command         | 0.000003 |
+--------------------------------+----------+
9 rows in set (0.002 sec)

• 查询指定的 Query ID

MariaDB [jdbc]> show profile for query 7;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| Starting                       | 0.000026 |
| Waiting for query cache lock   | 0.000004 |
| Init                           | 0.000003 |
| Checking query cache for query | 0.000006 |
| Checking privileges on cached  | 0.000003 |
| Checking permissions           | 0.000008 |
| Sending cached result to clien | 0.000010 |
| Updating status                | 0.000004 |
| Reset for next command         | 0.000003 |
+--------------------------------+----------+
9 rows in set (0.000 sec)

• 查询更多信息

MariaDB [jdbc]> show profile cpu,block io for query 6;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status                         | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| Starting                       | 0.000023 | 0.000010 |   0.000012 |            0 |             0 |
| Waiting for query cache lock   | 0.000003 | 0.000001 |   0.000002 |            0 |             0 |
| Init                           | 0.000002 | 0.000001 |   0.000001 |            0 |             0 |
| Checking query cache for query | 0.000005 | 0.000002 |   0.000003 |            0 |             0 |
| Checking privileges on cached  | 0.000003 | 0.000001 |   0.000002 |            0 |             0 |
| Checking permissions           | 0.000006 | 0.000003 |   0.000003 |            0 |             0 |
| Sending cached result to clien | 0.000012 | 0.000005 |   0.000007 |            0 |             0 |
| Updating status                | 0.000004 | 0.000002 |   0.000002 |            0 |             0 |
| Reset for next command         | 0.000003 | 0.000001 |   0.000002 |            0 |             0 |
+--------------------------------+----------+----------+------------+--------------+---------------+
9 rows in set (0.000 sec)

• mysql5中测试

# 在 /etc/my.cnf 中新增一行：开启查询缓存
query_cache_type=1
 
# 重启
systemctl restart mysqld
 
# 开启profiling
set profiling=1;
 
# 任意执行1条查询语句
select * from locations;
 
# 查看
show profile for query 1;

• sql语法顺序