MYSQL8-性能调优

explain

使用explain

用于分析sql语句的性能。案例分析：

mysql> show create table employees;

CREATE TABLE `employees` (
  `id` int(11) NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) DEFAULT NULL,
  `hire_date` date NOT NULL,
  `card_no` char(18) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `first_name` (`first_name`),
  KEY `card_no` (`card_no`),
  KEY `first_name2` (`first_name`,`last_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

正常格式

mysql> EXPLAIN SELECT * FROM employees WHERE id = 1;
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 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.00 | NULL  |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

JSON格式

mysql> EXPLAIN FORMAT=JSON SELECT * FROM employees WHERE id = 1;

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1.00"
    },
    "table": {
      "table_name": "employees",
      "access_type": "const",
      "possible_keys": [
        "PRIMARY"
      ],
      "key": "PRIMARY",
      "used_key_parts": [
        "id"
      ],
      "key_length": "4",
      "ref": [
        "const"
      ],
      "rows_examined_per_scan": 1,
      "rows_produced_per_join": 1,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "0.00",
        "eval_cost": "0.20",
        "prefix_cost": "0.00",
        "data_read_per_join": "160"
      },
      "used_columns": [
        "id",
        "first_name",
        "last_name",
        "hire_date",
        "card_no"
      ]
    }
  }
}

输出对照表

以下数据来自官网：https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

Column	JSON Name	Meaning
id	select_id	The SELECT identifier
select_type	None	The SELECT type
table	table_name	The table for the output row
partitions	partitions	The matching partitions
type	access_type	The join type
possible_keys	possible_keys	The