MySQL SQL Explain輸出學習

来源:https://www.cnblogs.com/dbabd/archive/2019/03/04/10471068.html
-Advertisement-
Play Games

正文 MySQL的explain命令語句提供瞭如何執行SQL語句的信息，解析SQL語句的執行計劃並展示，explain支持select、delete、insert、replace和update等語句，也支持對分區表的解析。通常explain用來獲取select語句的執行計劃，通過explain展示的 ...


正文

MySQL的explain命令語句提供瞭如何執行SQL語句的信息，解析SQL語句的執行計劃並展示，explain支持select、delete、insert、replace和update等語句，也支持對分區表的解析。通常explain用來獲取select語句的執行計劃，通過explain展示的信息我們可以瞭解到表查詢的順序，表連接的方式等，並根據這些信息判斷select執行效率，決定是否添加索引或改寫SQL語句優化表連接方式以提高執行效率。本文參考官方文檔：EXPLAIN Output Format對explain輸出的內容進行說明，同時也對自己之前使用explain不清晰的方面進行總結。

本文使用的MySQL版本為官方社區版 5.7.24。

mysql root@localhost:(none)> select version();
+------------+
| version()  |
+------------+
| 5.7.24-log |
+------------+
1 row in set
Time: 0.066s

主要用法

{ EXPLAIN | DESCRIBE } [EXTENDED | PARTITIONS | FORMAT=[TRADITIONAL | JSON]] SQL_STATEMENT;

  1. EXPLAIN和DESCRIBE(可以簡寫成DESC)都可以用來查看語句的執行計劃，但通常使用EXPLAIN較多；
  2. FORMAT選項可以指定執行計劃輸出信息為JSON格式，而且包含一些更詳細的指標說明；
  3. EXTENDED和PARTITIONS選項可以輸出更詳細選項說明，語法上是為了相容低版本MySQL，未來會廢棄，預設使用EXPLAIN命令即可。

測試數據

本文基於MySQL官方示例資料庫employee：Example Databases進行解析說明，使用到的表如下：

-- employees：
mysql root@localhost:employees> show create table employees\G;
***************************[ 1. row ]***************************
Table        | employees
Create Table | CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`),
  KEY `idx_first_last` (`first_name`,`last_name`),
  KEY `idx_birth_hire` (`birth_date`,`hire_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set
Time: 0.008s

-- dept_emp：
mysql root@localhost:employees> show create table dept_emp\G;
***************************[ 1. row ]***************************
Table        | dept_emp
Create Table | CREATE TABLE `dept_emp` (
  `emp_no` int(11) NOT NULL,
  `dept_no` char(4) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`dept_no`),
  KEY `dept_no` (`dept_no`),
  CONSTRAINT `dept_emp_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,
  CONSTRAINT `dept_emp_ibfk_2` FOREIGN KEY (`dept_no`) REFERENCES `departments` (`dept_no`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set
Time: 0.010s

-- departments：
mysql root@localhost:employees> show create table departments\G;
***************************[ 1. row ]***************************
Table        | departments
Create Table | CREATE TABLE `departments` (
  `dept_no` char(4) NOT NULL,
  `dept_name` varchar(40) NOT NULL,
  PRIMARY KEY (`dept_no`),
  UNIQUE KEY `dept_name` (`dept_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set
Time: 0.012s

輸出說明

mysql root@localhost:employees> explain select count(*) from employees;
+----+-------------+-----------+------------+-------+---------------+---------+---------+--------+--------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref    | rows   | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+---------+---------+--------+--------+----------+-------------+
| 1  | SIMPLE      | employees | <null>     | index | <null>        | PRIMARY | 4       | <null> | 299512 | 100.0    | Using index |
+----+-------------+-----------+------------+-------+---------------+---------+---------+--------+--------+----------+-------------+
1 row in set
Time: 0.026s

通過以上示例語句得出explain輸出有12個欄位，主要說明如下表：

欄位(Column) JSON名稱(JSON Name) 含義(Meaning)
id select_id 標識符，語句涉及表的執行順序
select_type None 表查詢類型
table table_name 表名稱
partitions partitions 涉及表哪個分區
type access_type 表的查詢(連接)類型
possible_keys possible_keys 表可能使用到的索引
key key 表實際使用到的索引
key_len key_length 表實際使用索引的長度，單位:位元組
ref ref 表哪些欄位或者常量用於連接查找索引上的值
rows rows 查詢預估返回表的行數
filtered filtered 表經過條件過濾之後與總數的百分比
Extra None 額外的說明信息

id

id為select標識符，語句在執行計劃當中的執行順序。id值的出現有如下幾種情況：

  1. id值全相同，則按由上到下順序執行；
  2. id值全不相同，則按id值大小，由大到小順序執行；
  3. id值部分相同，部分不相同，則同組id值大的優先執行(組內id值相同的順序執行)。
-- id全相同
mysql root@localhost:employees> explain select * from employees e,dept_emp d,departments de where e.emp_no = d.emp_no and de.dept_name = 'Human
                                Resources';

+----+-------------+-------+------------+-------+---------------+-----------+---------+--------------------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref                | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-----------+---------+--------------------+--------+----------+-------------+
| 1  | SIMPLE      | de    | <null>     | const | dept_name     | dept_name | 122     | const              | 1      | 100.0    | Using index |
| 1  | SIMPLE      | e     | <null>     | ALL   | PRIMARY       | <null>    | <null>  | <null>             | 299512 | 100.0    | <null>      |
| 1  | SIMPLE      | d     | <null>     | ref   | PRIMARY       | PRIMARY   | 4       | employees.e.emp_no | 1      | 100.0    | <null>      |
+----+-------------+-------+------------+-------+---------------+-----------+---------+--------------------+--------+----------+-------------+
3 rows in set
Time: 0.018s

-- id全不相同
mysql root@localhost:employees> explain select * from employees e where e.emp_no = (select d.emp_no from dept_emp d where d.dept_no = (select de.d
                                ept_no from departments de where de.dept_name = 'Development') and d.emp_no = 10023);
+----+-------------+-------+------------+-------+-----------------+-----------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys   | key       | key_len | ref         | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+-----------------+-----------+---------+-------------+------+----------+-------------+
| 1  | PRIMARY     | e     | <null>     | const | PRIMARY         | PRIMARY   | 4       | const       | 1    | 100.0    | <null>      |
| 2  | SUBQUERY    | d     | <null>     | const | PRIMARY,dept_no | PRIMARY   | 16      | const,const | 1    | 100.0    | Using index |
| 3  | SUBQUERY    | de    | <null>     | const | dept_name       | dept_name | 122     | const       | 1    | 100.0    | Using index |
+----+-------------+-------+------------+-------+-----------------+-----------+---------+-------------+------+----------+-------------+
3 rows in set
Time: 0.027s

-- id部分相同，部分不相同
mysql root@localhost:employees> explain select * from^Iemployees e where^Ie.emp_no in (select d.emp_no from dept_emp d where d.dept_no = (select d
                                e.dept_no from departments de where de.dept_name = 'Human Resources'));
+----+-------------+-------+------------+--------+-----------------+-----------+---------+--------------------+-------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys   | key       | key_len | ref                | rows  | filtered | Extra       |
+----+-------------+-------+------------+--------+-----------------+-----------+---------+--------------------+-------+----------+-------------+
| 1  | PRIMARY     | d     | <null>     | ref    | PRIMARY,dept_no | dept_no   | 12      | const              | 33212 | 100.0    | Using index |
| 1  | PRIMARY     | e     | <null>     | eq_ref | PRIMARY         | PRIMARY   | 4       | employees.d.emp_no | 1     | 100.0    | <null>      |
| 3  | SUBQUERY    | de    | <null>     | const  | dept_name       | dept_name | 122     | const              | 1     | 100.0    | Using index |
+----+-------------+-------+------------+--------+-----------------+-----------+---------+--------------------+-------+----------+-------------+
3 rows in set
Time: 0.020s

select_type

select_type為表查詢的類型，根據官方文檔總結幾種常見類型如下表：

select_type值(Value) JSON名稱(JSON Name) 含義(Meaning)
SIMPLE None 簡單查詢，不包含unino查詢或子查詢
PRIMARY None 位於最外層的查詢
UNION None 當出現union查詢時第二個或之後的查詢
DEPENDENT UNION dependent(true) 當出現union查詢時第二個或之後的查詢，取決於外部查詢
UNION RESULT union_result union查詢的結果
SUBQUERY None 子查詢當中第一個select查詢
DEPENDENT SUBQUERY dependent(true) 子查詢當中第一個select查詢，取決於外部的查詢
DERIVED None 派生表，from子句中出現的子查詢
  • SIMPLE：最常見的查詢類型，通常情況下沒有子查詢、union查詢就是SIMPLE類型。
mysql root@localhost:employees> explain select * from employees where emp_no = 10001;
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+--------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra  |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+--------+
| 1  | SIMPLE      | employees | <null>     | const | PRIMARY       | PRIMARY | 4       | const | 1    | 100.0    | <null> |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+--------+
1 row in set
Time: 0.019s
  • PRIMARY和SUBQUERY：在含有子查詢的語句中會出現。
mysql root@localhost:employees> explain select * from dept_emp d where d.dept_no = (select de.dept_no from departments de where de.dept_name = 'De
                                velopment');
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref   | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+--------+----------+-------------+
| 1  | PRIMARY     | d     | <null>     | ref   | dept_no       | dept_no   | 12      | const | 148054 | 100.0    | Using where |
| 2  | SUBQUERY    | de    | <null>     | const | dept_name     | dept_name | 122     | const | 1      | 100.0    | Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+--------+----------+-------------+
2 rows in set
Time: 0.021s
  • UNION和UNION RESULT：在有union查詢的語句中出現。
mysql root@localhost:employees> explain select * from departments where dept_no = 'd005' union select * from departments where dept_no = 'd004';
+--------+--------------+-------------+------------+-------+---------------+---------+---------+--------+--------+----------+-----------------+
| id     | select_type  | table       | partitions | type  | possible_keys | key     | key_len | ref    | rows   | filtered | Extra           |
+--------+--------------+-------------+------------+-------+---------------+---------+---------+--------+--------+----------+-----------------+
| 1      | PRIMARY      | departments | <null>     | const | PRIMARY       | PRIMARY | 12      | const  | 1      | 100.0    | <null>          |
| 2      | UNION        | departments | <null>     | const | PRIMARY       | PRIMARY | 12      | const  | 1      | 100.0    | <null>          |
| <null> | UNION RESULT | <union1,2>  | <null>     | ALL   | <null>        | <null>  | <null>  | <null> | <null> | <null>   | Using temporary |
+--------+--------------+-------------+------------+-------+---------------+---------+---------+--------+--------+----------+-----------------+
3 rows in set
Time: 0.020s
  • DEPENDENT UNION和DEPENDENT SUBQUERY：當語句中子查詢和union查詢依賴外部查詢會出現。
mysql root@localhost:employees> explain select * from employees e where e.emp_no in (select d.emp_no from dept_emp d where d.from_date = '1986-06-
                                26' union select d.emp_no from dept_emp d where d.from_date = '1996-08-03');
+--------+--------------------+------------+------------+------+---------------+---------+---------+--------+--------+----------+-----------------+
| id     | select_type        | table      | partitions | type | possible_keys | key     | key_len | ref    | rows   | filtered | Extra          |
+--------+--------------------+------------+------------+------+---------------+---------+---------+--------+--------+----------+-----------------+
| 1      | PRIMARY            | e          | <null>     | ALL  | <null>        | <null>  | <null>  | <null> | 299512 | 100.0    | Using where    |
| 2      | DEPENDENT SUBQUERY | d          | <null>     | ref  | PRIMARY       | PRIMARY | 4       | func   | 1      |  10.0    | Using where    |
| 3      | DEPENDENT UNION    | d          | <null>     | ref  | PRIMARY       | PRIMARY | 4       | func   | 1      |  10.0    | Using where    |
| <null> | UNION RESULT       | <union2,3> | <null>     | ALL  | <null>        | <null>  | <null>  | <null> | <null> | <null>   | Using temporary|
+--------+--------------------+------------+------------+------+---------------+---------+---------+--------+--------+----------+-----------------+
4 rows in set
Time: 0.022s
  • DERIVED：當查詢涉及生成臨時表時出現。
mysql root@localhost:employees> explain select * from (select * from departments limit 5) de;
+----+-------------+-------------+------------+-------+---------------+-----------+---------+--------+------+----------+-------------+
| id | select_type | table       | partitions | type  | possible_keys | key       | key_len | ref    | rows | filtered | Extra       |
+----+-------------+-------------+------------+-------+---------------+-----------+---------+--------+------+----------+-------------+
| 1  | PRIMARY     | <derived2>  | <null>     | ALL   | <null>        | <null>    | <null>  | <null> | 5    | 100.0    | <null>      |
| 2  | DERIVED     | departments | <null>     | index | <null>        | dept_name | 122     | <null> | 9    | 100.0    | Using index |
+----+-------------+-------------+------------+-------+---------------+-----------+---------+--------+------+----------+-------------+
2 rows in set
Time: 0.012s

table

指執行計劃當中當前是從哪張表獲取數據，如果為表指定了別名，則顯示別名，如果沒有涉及對錶的數據讀取，則顯示NULL，還有如下幾種情形：

  1. <unionM,N>：數據來自union查詢的id為M和N的結果集；
  2. ：數據來自派生表id為N的結果集；
  3. ：數據來自子查詢id為N的結果集。

partitions

指執行計劃中當前從分區表哪個表分區獲取數據，如果不是分區表，則顯示為NULL。

-- 示例資料庫employees的分區表salaries
mysql root@localhost:employees> show create table salaries;
+----------+-----------------------------------------------------------------+
| Table    | Create Table                                                    |
+----------+-----------------------------------------------------------------+
| salaries | CREATE TABLE `salaries` (                                       |
|          |   `emp_no` int(11) NOT NULL,                                    |
|          |   `salary` int(11) NOT NULL,                                    |
|          |   `from_date` date NOT NULL,                                    |
|          |   `to_date` date NOT NULL,                                      |
|          |   PRIMARY KEY (`emp_no`,`from_date`)                            |
|          | ) ENGINE=InnoDB DEFAULT CHARSET=utf8                            |
|          | /*!50500 PARTITION BY RANGE  COLUMNS(from_date)                 |
|          | (PARTITION p01 VALUES LESS THAN ('1985-12-31') ENGINE = InnoDB, |
|          |  PARTITION p02 VALUES LESS THAN ('1986-12-31') ENGINE = InnoDB, |
|          |  PARTITION p03 VALUES LESS THAN ('1987-12-31') ENGINE = InnoDB, |
|          |  PARTITION p04 VALUES LESS THAN ('1988-12-31') ENGINE = InnoDB, |
|          |  PARTITION p05 VALUES LESS THAN ('1989-12-31') ENGINE = InnoDB, |
|          |  PARTITION p06 VALUES LESS THAN ('1990-12-31') ENGINE = InnoDB, |
|          |  PARTITION p07 VALUES LESS THAN ('1991-12-31') ENGINE = InnoDB, |
|          |  PARTITION p08 VALUES LESS THAN ('1992-12-31') ENGINE = InnoDB, |
|          |  PARTITION p09 VALUES LESS THAN ('1993-12-31') ENGINE = InnoDB, |
|          |  PARTITION p10 VALUES LESS THAN ('1994-12-31') ENGINE = InnoDB, |
|          |  PARTITION p11 VALUES LESS THAN ('1995-12-31') ENGINE = InnoDB, |
|          |  PARTITION p12 VALUES LESS THAN ('1996-12-31') ENGINE = InnoDB, |
|          |  PARTITION p13 VALUES LESS THAN ('1997-12-31') ENGINE = InnoDB, |
|          |  PARTITION p14 VALUES LESS THAN ('1998-12-31') ENGINE = InnoDB, |
|          |  PARTITION p15 VALUES LESS THAN ('1999-12-31') ENGINE = InnoDB, |
|          |  PARTITION p16 VALUES LESS THAN ('2000-12-31') ENGINE = InnoDB, |
|          |  PARTITION p17 VALUES LESS THAN ('2001-12-31') ENGINE = InnoDB, |
|          |  PARTITION p18 VALUES LESS THAN ('2002-12-31') ENGINE = InnoDB, |
|          |  PARTITION p19 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */  |
+----------+-----------------------------------------------------------------+
1 row in set
Time: 0.018s

mysql root@localhost:employees> explain select * from salaries where from_date > '1985-12-31' and from_date < '1990-12-31';
+----+-------------+----------+---------------------+------+---------------+--------+---------+--------+--------+----------+-------------+
| id | select_type | table    | partitions          | type | possible_keys | key    | key_len | ref    | rows   | filtered | Extra       |
+----+-------------+----------+---------------------+------+---------------+--------+---------+--------+--------+----------+-------------+
| 1  | SIMPLE      | salaries | p02,p03,p04,p05,p06 | ALL  | <null>        | <null> | <null>  | <null> | 384341 | 11.11    | Using where |
+----+-------------+----------+---------------------+------+---------------+--------+---------+--------+--------+----------+-------------+
1 row in set
Time: 0.023s

type

type應該被認為是解讀執行計劃當中最重要的部分，根據type顯示的內容可以判斷語句總體的查詢效率。主要有以下幾種類型：

  • system：表只有一行(系統表)，是const的一種特殊情況。
-- 測試表departments_1生成：
mysql root@localhost:employees> create table departments_1 as select * from departments where dept_no='d005';
Query OK, 1 row affected
Time: 0.107s 

mysql root@localhost:employees> alter table departments_1 add primary key(dept_no);
Query OK, 0 rows affected

mysql root@localhost:employees> create index idx_dept_name on departments_1(dept_name);
Query OK, 0 rows affected

mysql root@localhost:employees> show create table departments_1\G;
***************************[ 1. row ]***************************
Table        | departments_1
Create Table | CREATE TABLE `departments_1` (
  `dept_no` char(4) NOT NULL,
  `dept_name` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`dept_no`),
  KEY `idx_dept_name` (`dept_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set
Time: 0.010s

-- 系統表：
mysql root@localhost:employees> explain select * from mysql.proxies_priv;
+----+-------------+--------------+------------+--------+---------------+--------+---------+--------+------+----------+--------+
| id | select_type | table        | partitions | type   | possible_keys | key    | key_len | ref    | rows | filtered | Extra  |
+----+-------------+--------------+------------+--------+---------------+--------+---------+--------+------+----------+--------+
| 1  | SIMPLE      | proxies_priv | <null>     | system | <null>        | <null> | <null>  | <null> | 1    | 100.0    | <null> |
+----+-------------+--------------+------------+--------+---------------+--------+---------+--------+------+----------+--------+
1 row in set
Time: 0.023s

-- 普通表：
mysql root@localhost:employees> explain select * from (select * from departments_1 where dept_no = 'd005' limit 1) de;
+----+-------------+---------------+------------+--------+---------------+---------+---------+--------+------+----------+--------+
| id | select_type | table         | partitions | type   | possible_keys | key     | key_len | ref    | rows | filtered | Extra  |
+----+-------------+---------------+------------+--------+---------------+---------+---------+--------+------+----------+--------+
| 1  | PRIMARY     | <derived2>    | <null>     | system | <null>        | <null>  | <null>  | <null> | 1    | 100.0    | <null> |
| 2  | DERIVED     | departments_1 | <null>     | const  | PRIMARY       | PRIMARY | 12      | const  | 1    | 100.0    | <null> |
+----+-------------+---------------+------------+--------+---------------+---------+---------+--------+------+----------+--------+
2 rows in set
Time: 0.015s
  • const：對於主鍵或者唯一索引鍵的等值查詢，只返回一行數據。
mysql root@localhost:employees> explain select * from departments_1 where dept_no = 'd005';
+----+-------------+---------------+------------+-------+---------------+---------+---------+-------+------+----------+--------+
| id | select_type | table         | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra  |
+----+-------------+---------------+------------+-------+---------------+---------+---------+-------+------+----------+--------+
| 1  | SIMPLE      | departments_1 | <null>     | const | PRIMARY       | PRIMARY | 12      | const | 1    | 100.0    | <null> |
+----+-------------+---------------+------------+-------+---------------+---------+---------+-------+------+----------+--------+
1 row in set
Time: 0.018s
  • eq_ref：對於前表的每一行數據，都只能匹配當前表唯一一行數據。除了system與const之外這是最好的一種連接查詢類型，主鍵或者是非空唯一索引的所有部分都可以在連接時被使用，通常使用的是'='操作符，比較值可以是一個常量，也可以是一個在該表之前讀取該表的欄位表達式。
explain select * from departments d,departments_1 d1 where d.dept_no = d1.dept_no;
+----+-------------+-------+------------+--------+---------------+---------------+---------+----------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key           | key_len | ref                  | rows | filtered | Extra      |
+----+-------------+-------+------------+--------+---------------+---------------+---------+----------------------+------+----------+-------------+
| 1  | SIMPLE      | d1    | <null>     | index  | PRIMARY       | idx_dept_name | 123     | <null>               | 1    | 100.0    | Using index|
| 1  | SIMPLE      | d     | <null>     | eq_ref | PRIMARY       | PRIMARY       | 12      | employees.d1.dept_no | 1    | 100.0    | <null>     |
+----+-------------+-------+------------+--------+---------------+---------------+---------+----------------------+------+----------+-------------+
2 rows in set
Time: 0.037s
  • ref：對於前表的每一行數據，都從當前表讀取所有匹配索引值的行。與eq_ref相比，連接查詢欄位不是主鍵或者唯一索引，又或者是複合索引的部分左首碼，如果連接查詢匹配的是少量幾行數據，ref是個不同錯的選擇，通常使用的運算符是'='、'<='或者'>='等。
mysql root@localhost:employees> explain select * from dept_emp where dept_no ='d005';
+----+-------------+----------+------------+------+---------------+---------+---------+-------+--------+----------+--------+
| id | select_type | table    | partitions | type | possible_keys | key     | key_len | ref   | rows   | filtered | Extra  |
+----+-------------+----------+------------+------+---------------+---------+---------+-------+--------+----------+--------+
| 1  | SIMPLE      | dept_emp | <null>     | ref  | dept_no       | dept_no | 12      | const | 148054 | 100.0    | <null> |
+----+-------------+----------+------------+------+---------------+---------+---------+-------+--------+----------+--------+
1 row in set
Time: 0.059s

mysql root@localhost:employees> explain select * from dept_emp d,departments_1 d1 where d.dept_no = d1.dept_no;
+----+-------------+-------+------------+------+---------------+---------+---------+----------------------+-------+----------+--------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref                  | rows  | filtered | Extra  |
+----+-------------+-------+------------+------+---------------+---------+---------+----------------------+-------+----------+--------+
| 1  | SIMPLE      | d1    | <null>     | ALL  | <null>        | <null>  | <null>  | <null>               | 1     | 100.0    | <null> |
| 1  | SIMPLE      | d     | <null>     | ref  | dept_no       | dept_no | 12      | employees.d1.dept_no | 41392 | 100.0    | <null> |
+----+-------------+-------+------------+------+---------------+---------+---------+----------------------+-------+----------+--------+
2 rows in set
Time: 0.012s
  • ref_or_null：同ref類型，但是包含了對NULL值的搜索。
mysql root@localhost:employees> explain select dept_name from departments_1 where dept_name = 'd005' or dept_name is null;
+----+-------------+---------------+------------+-------------+---------------+---------------+---------+-------+------+----------+--------------------------+
| id | select_type | table         | partitions | type        | possible_keys | key           | key_len | ref   | rows | filtered | Extra                   |
+----+-------------+---------------+------------+-------------+---------------+---------------+---------+-------+------+----------+--------------------------+
| 1  | SIMPLE      | departments_1 | <null>     | ref_or_null | idx_dept_name | idx_dept_name | 123     | const | 2    | 100.0    | Using where; Using index |
+----+-------------+---------------+------------+-------------+---------------+---------------+---------+-------+------+----------+--------------------------+
1 row in set
Time: 0.011s
  • index_merge：使用了索引合併優化進行查詢。如果查詢指定條件涉及對多個索引的使用時，會將多個索引合併操作。
mysql root@localhost:employees> explain select * from dept_emp where emp_no = 10001 or dept_no = (select dept_no from departments_1);
+----+-------------+---------------+------------+-------------+-----------------+-----------------+---------+--------+--------+----------+-------------------------------------------+
| id | select_type | table         | partitions | type        | possible_keys   | key             | key_len | ref    | rows   | filtered | Extra                                    |
+----+-------------+---------------+------------+-------------+-----------------+-----------------+---------+--------+--------+----------+-------------------------------------------+
| 1  | PRIMARY     | dept_emp      | <null>     | index_merge | PRIMARY,dept_no | PRIMARY,dept_no | 4,12    | <null> | 148055 | 100.0    | Using union(PRIMARY,dept_no); Using where |
| 2  | SUBQUERY    | departments_1 | <null>     | index       | <null>          | idx_dept_name   | 123     | <null> | 1      | 100.0    | Using index                               |
+----+-------------+---------------+------------+-------------+-----------------+-----------------+---------+--------+--------+----------+-------------------------------------------+
2 rows in set
Time: 0.014s
  • range：使用索引掃描條件指定範圍內的數據。常用的操作符有'>'、'<'、'is null'、'between'、'in'和'like'等。
mysql root@localhost:employees> explain select de.* from dept_emp de,departments_1 d where de.dept_no = d.dept_no and de.emp_no < 10010;
+----+-------------+-------+------------+-------+-----------------+---------------+---------+--------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys   | key           | key_len | ref    | rows | filtered | Extra                                             |
+----+-------------+-------+------------+-------+-----------------+---------------+---------+--------+------+----------+----------------------------------------------------+
| 1  | SIMPLE      | d     | <null>     | index | PRIMARY         | idx_dept_name | 123     | <null> | 1    | 100.0    | Using index                                       |
| 1  | SIMPLE      | de    | <null>     | range | PRIMARY,dept_no | PRIMARY       | 4       | <null> | 9    |  12.5    | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+-----------------+---------------+---------+--------+------+----------+----------------------------------------------------+
2 rows in set
Time: 0.019s
  • index：使用索引全掃描。類似於全表掃描，只是掃描對象是索引，出現於以下兩種情況：
  1. 如果索引是覆蓋索引，即索引包含查詢所需要的所有表數據，就只掃描索引，並且在Extra中出現Using index。通常情況下掃描索引比打描表要更快，因為索引一般比表來的小；
  2. 全表掃描採用索引的順序來讀取數據，本質上還是全表掃描，並且在Extra中不會出現Using index，避免再進行排序消耗性能，因為索引本身就是排序好的。
mysql root@localhost:employees> explain select dept_name from departments_1;
+----+-------------+---------------+------------+-------+---------------+---------------+---------+--------+------+----------+-------------+
| id | select_type | table         | partitions | type  | possible_keys | key           | key_len | ref    | rows | filtered | Extra       |
+----+-------------+---------------+------------+-------+---------------+---------------+---------+--------+------+----------+-------------+
| 1  | SIMPLE      | departments_1 | <null>     | index | <null>        | idx_dept_name | 123     | <null> | 1    | 100.0    | Using index |
+----+-------------+---------------+------------+-------+---------------+---------------+---------+--------+------+----------+-------------+
1 row in set
Time: 0.020s
  • all：使用全表掃描。
mysql root@localhost:employees> drop index idx_dept_name on departments_1;
Query OK, 0 rows affected
Time: 0.052s

mysql root@localhost:employees> explain select * from departments_1;
+----+-------------+---------------+------------+------+---------------+--------+---------+--------+------+----------+--------+
| id | select_type | table         | partitions | type | possible_keys | key    | key_len | ref    | rows | filtered | Extra  |
+----+-------------+---------------+------------+------+---------------+--------+---------+--------+------+----------+--------+
| 1  | SIMPLE      | departments_1 | <null>     | ALL  | <null>        | <null> | <null>  | <null> | 1    | 100.0    | <null> |
+----+-------------+---------------+------------+------+---------------+--------+---------+--------+------+----------+--------+
1 row in set
Time: 0.018s

通過以上各種主要類型的分析，可以總結出各個類型性能排序(從左到右性能從高到低)：

system > const > eq_ref > ref > range > index > all

possible_keys

顯示了MySQL在查找當前表中數據的時候可能使用到的索引，如果該欄位值為NULL，則表明沒有相關索引可用。

key

顯示了MySQL在實際查找數據時決定使用的索引，如果該欄位值為NULL，則表明沒有使用索引。

key_len

顯示了MySQL實際使用索引的鍵大小，單位位元組。可以通過key_len的大小判斷評估複合索引使用了哪些部分，如果key欄位值為NULL，則key_len的值也為NULL。
幾種常見欄位類型索引長度大小如下，假設字元編碼為UTF8：

  • 欄位屬性是否允許NULL，如果允許NULL，則需要額外增加一個位元組；
  • 字元型：
    • char(n)：3n個位元組
    • varchar(n)：3n+2個位元組
  • 數值型：
    • tinyint：1個位元組
    • int：4個位元組
    • bigint：8個位元組
  • 時間型：
    • date：3個位元組
    • datetime：5個位元組+秒精度位元組
    • timestamp：4個位元組+秒精度位元組
    • 秒精度位元組(最大6位)：
      • 1~2位：1個位元組
      • 3~4位：2個位元組
      • 5~6位：3個位元組

ref

顯示哪些常量或者欄位被用於查詢索引列鍵值，以獲取表中數據行。

  • 如果是常量等值查詢，則顯示為const；
  • 如果是連接查詢，則被驅動表的該欄位會顯示驅動表的所關聯欄位；
  • 如果條件當中使用函數表達式，或者值導致條件欄位發生隱式轉換，這裡顯示為func。
mysql root@localhost:employees> explain select * from departments d,departments_1 d1 where d.dept_no = d1.dept_no;
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+--------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                  | rows | filtered | Extra  |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+--------+
| 1  | SIMPLE      | d1    | <null>     | ALL    | PRIMARY       | <null>  | <null>  | <null>               | 1    | 100.0    | <null> |
| 1  | SIMPLE      | d     | <null>     | eq_ref | PRIMARY       | PRIMARY | 12      | employees.d1.dept_no | 1    | 100.0    | <null> |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+--------+
2 rows in set
Time: 0.038s

rows

顯示預估需要查詢的行數。對InnoDB表來說這是個預估值，並非是個準確值。

filtered

顯示按表條件過濾的表行的估計百分比。

Extra

顯示查詢時的額外信息。常見的有如下幾種：

  • Using index
    僅查詢索引樹就可以獲取到所需要的數據行，而不需要讀取表中實際的數據行。通常適用於select欄位就是查詢使用索引的一部分，即使用了覆蓋索引。
mysql root@localhost:employees> explain select dept_name from departments_1;
+----+-------------+---------------+------------+-------+---------------+---------------+---------+--------+------+----------+-------------+
| id | select_type | table         | partitions | type  | possible_keys | key           | key_len | ref    | rows | filtered | Extra       |
+----+-------------+---------------+------------+-------+---------------+---------------+---------+--------+------+----------+-------------+
| 1  | SIMPLE      | departments_1 | <null>     | index | <null>        | idx_dept_name | 123     | <null> | 1    | 100.0    | Using index |
+----+-------------+---------------+------------+-------+---------------+---------------+---------+--------+------+----------+-------------+
1 row in set
Time: 0.015s
  • Using index condition
    顯示採用了Index Condition Pushdown (ICP)特性通過索引去表中獲取數據。關於ICP特性可以參考官方文檔：Index Condition Pushdown Optimization。簡單說法如下：
  1. 如果開啟ICP特性，部分where條件部分可以下推到存儲引擎通過索引進行過濾，ICP可以減少存儲引擎訪問基表的次數；
  2. 如果沒有開啟ICP特性，則存儲引擎根據索引需要直接訪問基表獲取數據並返回給server層進行where條件的過濾。
-- employees表創建複合索引idx_birth_hire
mysql root@localhost:employees> create index idx_birth_hire on employees(birth_date,hire_date);
Query OK, 0 rows affected
Time: 0.768s

mysql root@localhost:employees> explain select * from employees where birth_date = '1960-01-01' and hire_date > '1980-01-01';
+----+-------------+-----------+------------+-------+----------------+----------------+---------+--------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys  | key            | key_len | ref    | rows | filtered | Extra                |
+----+-------------+-----------+------------+-------+----------------+----------------+---------+--------+------+----------+-----------------------+
| 1  | SIMPLE      | employees | <null>     | range | idx_birth_hire | idx_birth_hire | 6       | <null> | 63   | 100.0    | Using index condition |
+----+-------------+-----------+------------+-------+----------------+----------------+---------+--------+------+----------+-----------------------+
1 row in set
Time: 0.016s
  • Using index for group-by
    跟Using index訪問表的方式類似，顯示MySQL通過索引就可以完成對GROUP BY或DISTINCT欄位的查詢，而無需再訪問表中的數據。
mysql root@localhost:employees> explain select distinct dept_no from dept_emp;
+----+-------------+----------+------------+-------+-----------------+---------+---------+--------+------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys   | key     | key_len | ref    | rows | filtered | Extra                    |
+----+-------------+----------+------------+-------+-----------------+---------+---------+--------+------+----------+--------------------------+
| 1  | SIMPLE      | dept_emp | <null>     | range | PRIMARY,dept_no | dept_no | 12      | <null> | 9    | 100.0    | Using index for group-by |
+----+-------------+----------+------------+-------+-----------------+---------+---------+--------+------+----------+--------------------------+
1 row in set
Time: 0.020s
  • Using where
    顯示MySQL通過索引條件定位之後還需要返回表中獲得所需要的數據。
mysql root@localhost:employees> explain select * from employees where birth_date < '1970-01-01';
+----+-------------+-----------+------------+------+----------------+--------+---------+--------+--------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys  | key    | key_len | ref    | rows   | filtered | Extra       |
+----+-------------+-----------+------------+------+----------------+--------+---------+--------+--------+----------+-------------+
| 1  | SIMPLE      | employees | <null>     | ALL  | idx_birth_hire | <null> | <null>  | <null> | 299512 | 50.0     | Using where |
+----+-------------+-----------+------------+------+----------------+--------+---------+--------+--------+----------+-------------+
1 row in set
Time: 0.016s
  • Impossible WHERE
    where子句的條件永遠都不可能為真。
mysql root@localhost:employees> explain select * from employees where 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>   | Impossible WHERE |
+----+-------------+--------+------------+--------+---------------+--------+---------+--------+--------+----------+------------------+
1 row in set
Time: 0.015s
-- Block Nested Loop
mysql root@localhost:employees> explain select * from employees e,dept_emp d where e.emp_no > 10001 and e.emp_no <> d.emp_no;
+----+-------------+-------+------------+-------+---------------+---------+---------+--------+--------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref    | rows   | filtered | Extra                                             |
+----+-------------+-------+------------+-------+---------------+---------+---------+--------+--------+----------+----------------------------------------------------+
| 1  | SIMPLE      | e     | <null>     | range | PRIMARY       | PRIMARY | 4       | <null> | 149756 | 100.0    | Using where                                       |
| 1  | SIMPLE      | d     | <null>     | ALL   | <null>        | <null>  | <null>  | <null> | 331143 |  90.0    | Using where; Using join buffer(Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+---------+---------+--------+--------+----------+----------------------------------------------------+
2 rows in set
Time: 0.020s

-- Batched Key Access
mysql root@localhost:employees> explain SELECT /*+ bka(a)*/ a.gender, b.dept_no FROM employees a, dept_emp b WHERE a.birth_date = b.from_date;
+----+-------------+-------+------------+------+----------------+----------------+---------+-----------------------+--------+----------+----------------------------------------+
| id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref                   | rows   | filtered | Extra                                 |
+----+-------------+-------+------------+------+----------------+----------------+---------+-----------------------+--------+----------+----------------------------------------+
| 1  | SIMPLE      | b     | <null>     | ALL  | <null>         | <null>         | <null>  | <null>                | 331143 | 100.0    | <null>                                |
| 1  | SIMPLE      | a     | <null>     | ref  | idx_birth_hire | idx_birth_hire | 3       | employees.b.from_date | 63     | 100.0    | Using join buffer (Batched Key Access) |
+----+-------------+-------+------------+------+----------------+----------------+---------+-----------------------+--------+----------+----------------------------------------+
2 rows in set
Time: 0.014s
  • Using MRR
    讀取數據採用多範圍讀(Multi-Range Read)的優化策略。關於MRR特性也可以參考官方文檔：Multi-Range Read Optimization
mysql root@localhost:employees> set optimizer_switch='mrr=on,mrr_cost_based=off';
Query OK, 0 rows affected
Time: 0.001s
mysql root@localhost:employees> explain select * from employees where birth_date = '1970-01-01' and hire_date > '1990-01-01';
+----+-------------+-----------+------------+-------+----------------+----------------+---------+--------+------+----------+----------------------------------+
| id | select_type | table     | partitions | type  | possible_keys  | key            | key_len | ref    | rows | filtered | Extra                           |
+----+-------------+-----------+------------+-------+----------------+----------------+---------+--------+------+----------+----------------------------------+
| 1  | SIMPLE      | employees | <null>     | range | idx_birth_hire | idx_birth_hire | 6       | <null> | 1    | 100.0    | Using index condition; Using MRR |
+----+-------------+-----------+------------+-------+----------------+----------------+---------+--------+------+----------+----------------------------------+
1 row in set
Time: 0.014s
  • Range checked for each record (index map: N)
    MySQL在獲取數據時發現在沒有索引可用，但當獲取部分先前表欄位值時發現可以採用當前表某些索引來獲取數據。index map展示的是一個掩碼值，如index map:0x19，對應二進位值為11001，表示當前表索引編號為1、4和5號索引可能被用來獲取數據，索引編號通過SHOW INDEX語句獲得。
mysql root@localhost:employees> explain select * from employees e,dept_emp d where e.emp_no > d.emp_no;
+----+-------------+-------+------------+------+---------------+--------+---------+--------+--------+----------+------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key    | key_len | ref    | rows   | filtered | Extra                                         |
+----+-------------+-------+------------+------+---------------+--------+---------+--------+--------+----------+------------------------------------------------+
| 1  | SIMPLE      | d     | <null>     | ALL  | PRIMARY       | <null> | <null>  | <null> | 331143 | 100.0    | <null>                                        |
| 1  | SIMPLE      | e     | <null>     | ALL  | PRIMARY       | <null> | <null>  | <null> | 299512 |  33.33   | Range checked for each record (index map: 0x1) |
+----+-------------+-------+------------+------+---------------+--------+---------+--------+--------+----------+------------------------------------------------+
2 rows in set
Time: 0.038s
  • Select tables optimized away
    MySQL優化器能夠確定以下兩點：
  1. 最多只有一行記錄被返回；
  2. 為了獲取這一行數據，有一定的結果集需要獲取。

當語句在優化器階段過程中可以獲取查詢結果(如獲取行數，只需要讀取相應索引數據)，而無需再返回表中查詢數據，可能會出現Select tables optimized away。例如針對MyISAM引擎的表，使用select count(*)獲取表的總行數，而且又沒有where子句或者條件總是為真，也沒有GROUP BY子句時，其實就包含了以上的條件且隱式含有GROUP BY分組的效果。

-- 創建MyISAM引擎的employees表
mysql root@localhost:employees> create table employees_myisam like employees;
Query OK, 0 rows affected
Time: 0.040s
mysql root@localhost:employees> insert into employees_myisam select * from employees;
Query OK, 300024 rows affected
Time: 5.023s
mysql root@localhost:employees> alter table employees_myisam engine=MyISAM;
Query OK, 300024 rows affected
Time: 1.515s

-- 獲取執行count(*)查詢行數執行計劃
mysql root@localhost:employees> explain select count(*) from employees_myisam;
+----+-------------+--------+------------+--------+---------------+--------+---------+--------+--------+----------+------------------------------+
| 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>   | Select tables optimized away |
+----+-------------+--------+------------+--------+---------------+--------+---------+--------+--------+----------+------------------------------+
1 row in set
Time: 0.024s
  • Using temporary
    MySQL需要創建臨時表來存放查詢結果集。通常發生在有GROUP BY或ORDER BY子句的語句當中。
mysql root@localhost:employees> explain select hire_date from employees group by hire_date;
+----+-------------+-----------+------------+-------+----------------+----------------+---------+--------+--------+----------+----------------------------------------------+
| id | select_type | table     | partitions | type  | possible_keys  | key            | key_len | ref    | rows   | filtered | Extra
                          |
+----+-------------+-----------+------------+-------+----------------+----------------+---------+--------+--------+----------+----------------------------------------------+
| 1  | SIMPLE      | employees | <null>     | index | idx_birth_hire | idx_birth_hire | 6       | <null> | 299512 | 100.0    | Using index; Using temporary; Using filesort |
+----+-------------+-----------+------------+-------+----------------+----------------+---------+--------+--------+----------+----------------------------------------------+
1 row in set
Time: 0.018s
  • Using filesort
    MySQL需要對獲取的數據進行額外的一次排序操作，無法通過索引的排序完成。通常發生在有ORDER BY子句的語句當中。
mysql root@localhost:employees> explain select * from employees order by hire_date;
+----+-------------+-----------+------------+------+---------------+--------+---------+--------+--------+----------+----------------+
| id | select_type | table     | partitions | type | possible_keys | key    | key_len | ref    | rows   | filtered | Extra          |
+----+-------------+-----------+------------+------+---------------+--------+---------+--------+--------+----------+----------------+
| 1  | SIMPLE      | employees | <null>     | ALL  | <null>        | <null> | <null>  | <null> | 299512 | 100.0    | Using filesort |
+----+-------------+-----------+------------+------+---------------+--------+---------+--------+--------+----------+----------------+
1 row in set
Time: 0.015s

總結

以上內容總結了MySQL獲取執行計劃explain命令執行時輸出的主要欄位說明，還有許多未仔細說明的參數和選項，以後還需多多實踐總結。可以看出explain命令輸出內容當中比較重要的是：

  1. type：展示了表的查詢/連接類型，體現查詢效率；
  2. key/key_len：實際使用了什麼索引，使用了哪些部分索引；
  3. Extra：對執行計劃步驟額外的說明，採用了哪些查詢特性。

參考

☆〖本人水平有限，文中如有錯誤還請留言批評指正！〗☆


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • SUSE12Sp3安裝配置.net core 生產環境（7）-離線安裝zookeeper,並安裝可視化工具
    直接使用root賬號 1、zookeeper安裝 將zookeeper 3.4.13.tar.gz安裝包放置指定目錄 2、zookeeper安裝可視化工具 需要安裝 java和maven環境 1、安裝maven環境 線上下載地址，不用管 http://mirrors.shu.edu.cn/apach ...
  • 常用操作之增、刪、改、查
    1、增增加目錄命令：mkdir 映射--1.新建目錄名為dir1 eg:mkdir dir1 2.一次建多個文件夾eg:temp1,temp2,temp3 mkdir temp1 temp2 temp3 3.新建父目錄,文件已存在也不會報錯； eg:mkdir -p t1/t2/t3增加文件命令：t ...
  • 大數據---Ranger-1
    背景：從軟通出來，告別華為外包，離開H區，進入了一家搞大數據的創業公司，感覺周圍都好陌生，記錄下自己大數據的career！ 2019-03-4新的徵程-入職第一天： 一、辦理入職手續 公司人比較少，沒有入職培訓等操作；用了企業微信和企業郵箱，填下個人信息，然後開搞； 二、搭建Java開發環境和虛擬機 ...
  • MySQL8.0關於caching_sha2_password Plugin的一個Bug
    今天在調試使用ansible進行標準化安裝MySQL8.0時，發現關於caching_sha2_password plugin的一個bug。 在搭建主從複製時，按照手冊說明需要創建用戶： 然後在從庫執行： 悲催的是，發現從庫的IO_Thread提示連不上主庫。 而如果在從庫上直接使用mysql命令行 ...
  • 主鍵和外鍵
    1：主鍵和外鍵的定義 主鍵（primary key）：一列（或一組列），其值能夠唯一區分表中每個行 。外鍵（foreign key） 外鍵為某個表中的一列，它包含另一個表的主鍵值，定義了兩個表之間的關係 借用其他博客中的三個數據表解析一下： 2：是哪個表的外鍵？ 以上面的成績表為例，學號和課程號是成 ...
  • MySQL 筆記整理（8.a） --事務到底是隔離還是不隔離的？
    筆記記錄自林曉斌（丁奇）老師的《MySQL實戰45講》 8.a） --事務到底是隔離還是不隔離的？ 這部分內容不太容易理解，筆者也是進行了多次閱讀。因此引用原文： 之前有提到過，如果是在可重覆讀隔離級別，事務T啟動的時候會創建一個視圖read-view,之後事務T執行期間，即使有其他事務修改了數據， ...
  • MySQL8.0 on Windows下重置root密碼的BUG
    很多人都知道MySQL忘記root密碼之後可以通過skip-grant-tables來暫時免密登錄MySQL，從而修改root密碼，但是這種方式一方面有安全隱患，另一方面也並不怎麼適用於Windows系統，今天在通過此方式修改MySQL8.0 on Windows的root密碼時遇到了這個問題，寫個 ...
  • Accesss資料庫的DBhelper類（帶分頁）
    首先配置web.config，使配置文件連接access資料庫： <connectionStrings> <add name="DBConnection" connectionString="Provider=Microsoft.Jet.Oledb.4.0;Data Source=|DataDire ...
一周排行
    -Advertisement-
    Play Games
  • WPF 已知問題 監聽 WMI 事件導致觸摸失效
    示例項目結構 在 Visual Studio 中創建一個 WinForms 應用程式後，項目結構如下所示： MyWinFormsApp/ │ ├───Properties/ │ └───Settings.settings │ ├───bin/ │ ├───Debug/ │ └───Release/ ...
  • [STAThread]特性
    [STAThread] 特性用於需要與 COM 組件交互的應用程式，尤其是依賴單線程模型（如 Windows Forms 應用程式）的組件。在 STA 模式下，線程擁有自己的消息迴圈，這對於處理用戶界面和某些 COM 組件是必要的。 [STAThread] static void Main(stri ...
  • UWP WinUI3 傳入 AddHandler 的 RoutedEventHandler 類型與事件所需不匹配將拋出參數異常
    在WinForm中使用全局異常捕獲處理 在WinForm應用程式中，全局異常捕獲是確保程式穩定性的關鍵。通過在Program類的Main方法中設置全局異常處理，可以有效地捕獲並處理未預見的異常，從而避免程式崩潰。 註冊全局異常事件 [STAThread] static void Main() { / ...
  • .NET 多版本 WinForm 開源控制項庫 SunnyUI
    前言 給大家推薦一款開源的 Winform 控制項庫，可以幫助我們開發更加美觀、漂亮的 WinForm 界面。 項目介紹 SunnyUI.NET 是一個基於 .NET Framework 4.0+、.NET 6、.NET 7 和 .NET 8 的 WinForm 開源控制項庫，同時也提供了工具類庫、擴展 ...
  • .net core8 使用Swagger（附當前源碼）
    說明 該文章是屬於OverallAuth2.0系列文章，每周更新一篇該系列文章（從0到1完成系統開發）。 該系統文章，我會儘量說的非常詳細，做到不管新手、老手都能看懂。 說明：OverallAuth2.0 是一個簡單、易懂、功能強大的許可權+可視化流程管理系統。 有興趣的朋友，請關註我吧(*^▽^*) ...
  • WPF 什麼時候 VisualTreeHelper.GetDescendantBounds 將返回無窮大
    一、下載安裝 1.下載git 必須先下載並安裝git，再TortoiseGit下載安裝 git安裝參考教程：https://blog.csdn.net/mukes/article/details/115693833 2.TortoiseGit下載與安裝 TortoiseGit，Git客戶端，32/6 ...
  • C# 開源教程帶你輕鬆掌握數據結構與演算法
    前言 在項目開發過程中，理解數據結構和演算法如同掌握蓋房子的秘訣。演算法不僅能幫助我們編寫高效、優質的代碼，還能解決項目中遇到的各種難題。 給大家推薦一個支持C#的開源免費、新手友好的數據結構與演算法入門教程：Hello演算法。 項目介紹 《Hello Algo》是一本開源免費、新手友好的數據結構與演算法入門 ...
  • 第22篇 生成proto文件bat腳本
    1.生成單個Proto.bat內容 @rem Copyright 2016, Google Inc. @rem All rights reserved. @rem @rem Redistribution and use in source and binary forms, with or with ...
  • 記一次 .NET某上位機視覺程式 卡死分析
    一：背景 1. 講故事 前段時間有位朋友找到我，說他的窗體程式在客戶這邊出現了卡死，讓我幫忙看下怎麼回事？dump也生成了，既然有dump了那就上 windbg 分析吧。 二：WinDbg 分析 1. 為什麼會卡死 窗體程式的卡死，入口門檻很低，後續往下分析就不一定了，不管怎麼說先用 !clrsta ...
  • 醫學DICOM文件與PACS系統進行數據傳輸
    前言 人工智慧時代，人臉識別技術已成為安全驗證、身份識別和用戶交互的關鍵工具。 給大家推薦一款.NET 開源提供了強大的人臉識別 API，工具不僅易於集成，還具備高效處理能力。 本文將介紹一款如何利用這些API，為我們的項目添加智能識別的亮點。 項目介紹 GitHub 上擁有 1.2k 星標的 C# ...
所有分類