MySQL查詢最佳化

changwan發表於2024-05-17
MySql

1、獲取伺服器使用資訊

SHOW STATUS

語法規則

show [session | global] status like 'Connections'

這裡的 [session | global]可以省略不寫，預設為session，這個參數列示獲取效能引數的級別，session表示當前會話級別，global表示獲取全域性級別。

引數值 引數說明
Connections 連線MySQL伺服器的次數
Uptime MySQL伺服器啟動後連續工作的時間
Slow_queries 慢查詢的次數
Com_insert 插入資料的次數(只統計insert語句的次數，一次插入多條算一次)
Com_delete 刪除資料的次數
Com_update 修改資料的次數
Com_select 查詢資料的次數
Innodb_rows_read 查詢資料時返回的資料行數
Innodb_rows_inserted 插入資料時返回的記錄數
Innodb_rows_updated 更新資料時返回的記錄數
Innodb_rows_delete 刪除資料時返回的記錄數

例如檢視MySLQ伺服器啟動後連續工作的時間

mysql> show session status like 'connections';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 10    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show global status like 'connections';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 10    |
+---------------+-------+
1 row in set (0.01 sec)

mysql> show status like 'connections';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 10    |
+---------------+-------+
1 row in set (0.00 sec)

2、分析查詢語句

EXPLAIN

語法格式

explain select id,name,age from Student \G;

只需要在查詢語句之前新增explain語句即可，這時這個語句不會真的查詢資料，而是根據explain模擬最佳化器執行SLQ語句，並輸出相關資訊。例如

mysql> explain select name from Student \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Student
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

（1）id：表示select語句的序列號，有多少個select語句就有多少個序列號。

（2）select_type：SQL語句的查詢型別，簡單查詢語句/複雜查詢語句。

型別 說明
SIMPLE 簡單查詢
PRIMARY 主鍵查詢或者包含子查詢時最外層的查詢語句
UNION 連線查詢，表示連線查詢的第二個語句或者更後面
DEPENDENT UNION 與UNION差不多，但是這裡取決於最外層的查詢語句
UNION RESULT 連線查詢的結果資訊
SUBQUERY 子查詢中的第一個查詢語句
DEPENDENT SUBQUERY 取決於外層的查詢語句
DERIVED FROM子句中的子查詢
MATERIALIZED 表示例項化子查詢
UNCACHEABLE SUBQUERY 不快取子查詢的結果資料，重新計算外部查詢的每一行資料
UNCACHEABLE UNION 不快取連線查詢的結果資料，每次執行連線查詢時都會重新計算資料結果

（3）table：當前查詢的資料表

（4）partitions：如果這個是分割槽表、表示查詢結果匹配的分割槽。負責返回null。

（5）type：當前SQL語句使用的關聯型別或者訪問型別。最優到差依次為

system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>All

（6）possible_keys：執行查詢語句時可能使用到的索引。但在實際查詢中未必會使用到。為null時表示沒有可使用的索引。

（7）key：執行查詢語句時實際會使用到的索引，沒有使用則為null。

（8）key_len：實際使用到索引按照位元組計算的長度值。

（9）ref：資料表中的哪個列或者常量用來與key列中的索引做比較來檢索資料。

（10）rows：查詢資料的行數。

（11）filtered：查詢結果符合查詢條件的百分比。

（12）ectra：執行查詢語句時額外的詳細資訊。

3、深入分析

SHOW PROFILE語句解析

檢視MySQL是否支援PROFILE

mysql> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES              |
+------------------+
1 row in set, 1 warning (0.00 sec)

預設profiling時關閉的

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set, 1 warning (0.00 sec)

透過set語句開啟

mysql> set session profiling=1;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           1 |
+-------------+
1 row in set, 1 warning (0.00 sec)

3.1、分析InnoDB資料表

  1. 檢視錶結構
mysql> show create table Student \G;
*************************** 1. row ***************************
       Table: Student
Create Table: CREATE TABLE `Student` (
  `name` varchar(255) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `gender` varchar(255) DEFAULT NULL,
  `school` varchar(255) DEFAULT NULL,
  `id` int NOT NULL AUTO_INCREMENT,
  `phone` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
  1. 檢視資料條數
mysql> select count(*) from Student;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)
  1. 檢視SQL語句資訊
mysql> show profiles;
+----------+------------+--------------------------------+
| Query_ID | Duration   | Query                          |
+----------+------------+--------------------------------+
|        1 | 0.00342200 | select @@profiling             |
|        2 | 0.00027400 | select countct(*) from Student |
|        3 | 0.00028900 | select countct(*) from Student |
|        4 | 0.00436800 | select count(*) from Student   |
|        5 | 0.00045100 | show create tables Student     |
|        6 | 0.00571900 | show create table Student      |
|        7 | 0.00136000 | show create table Student      |
|        8 | 0.00401200 | select count(*) from Student   |
|        9 | 0.00037000 | show profiles
；               |
+----------+------------+--------------------------------+
9 rows in set, 1 warning (0.00 sec)
  1. 查詢執行SQL語句執行過程中的所線上程的具體資訊
mysql> show profile for query 8;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.001623 |
| Executing hook on transaction  | 0.000015 |
| starting                       | 0.000029 |
| checking permissions           | 0.000017 |
| Opening tables                 | 0.000119 |
| init                           | 0.000013 |
| System lock                    | 0.000025 |
| optimizing                     | 0.000014 |
| statistics                     | 0.000060 |
| preparing                      | 0.000051 |
| executing                      | 0.001900 |
| end                            | 0.000008 |
| query end                      | 0.000007 |
| waiting for handler commit     | 0.000030 |
| closing tables                 | 0.000019 |
| freeing items                  | 0.000062 |
| cleaning up                    | 0.000020 |
+--------------------------------+----------+
17 rows in set, 1 warning (0.00 sec)

可以看到這條SQL語句的主要時間花在了executing和starting

  1. 檢視指定SQL語句消耗的BLOCK IO資源
mysql> show profile block io for query 8;
+--------------------------------+----------+--------------+---------------+
| Status                         | Duration | Block_ops_in | Block_ops_out |
+--------------------------------+----------+--------------+---------------+
| starting                       | 0.001623 |            0 |             0 |
| Executing hook on transaction  | 0.000015 |            0 |             0 |
| starting                       | 0.000029 |            0 |             0 |
| checking permissions           | 0.000017 |            0 |             0 |
| Opening tables                 | 0.000119 |            0 |             0 |
| init                           | 0.000013 |            0 |             0 |
| System lock                    | 0.000025 |            0 |             0 |
| optimizing                     | 0.000014 |            0 |             0 |
| statistics                     | 0.000060 |            0 |             0 |
| preparing                      | 0.000051 |            0 |             0 |
| executing                      | 0.001900 |            0 |             0 |
| end                            | 0.000008 |            0 |             0 |
| query end                      | 0.000007 |            0 |             0 |
| waiting for handler commit     | 0.000030 |            0 |             0 |
| closing tables                 | 0.000019 |            0 |             0 |
| freeing items                  | 0.000062 |            0 |             0 |
| cleaning up                    | 0.000020 |            0 |             0 |
+--------------------------------+----------+--------------+---------------+
17 rows in set, 1 warning (0.00 sec)

這條SQL語句沒有消耗資源。也就是沒有IO操作。

相關文章