阿里内部MySQL索引优化最佳实践

本次分享内容

1、Explain工具

2、Explain中的列

3、索引优化最佳实践

​

MySQL版本：8.2.0

在数据库优化中，索引优化是一种重要的技术。在MySQL中，我们可以使用Explain工具来分析SQL查询，从而更好地理解查询的执行计划，找出可能的性能瓶颈，进行相应的优化。

Explain工具

Explain是MySQL提供的一种工具，可以用来分析SQL查询的执行计划。使用Explain工具，我们可以看到MySQL如何使用索引来处理SQL查询，包括哪些索引被使用，如何被使用，以及数据的检索顺序等信息。

使用Explain的基本语法是：

EXPLAIN SELECT * FROM table_name;

这将显示出查询的执行计划。

好的，这是按照您的要求修改后的SQL建表语句和INSERT语句。

示例表格

创建名为demo的数据库的SQL脚本：

CREATE DATABASE demo CHARACTER SET utf8 COLLATE utf8_general_ci;

假设我们有以下三个表：游戏（game）、玩家（player）和玩家记录表（player_record）。

-- 游戏表
DROP TABLE IF EXISTS game;
CREATE TABLE game (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 玩家表
DROP TABLE IF EXISTS player;
CREATE TABLE player (
  id INT PRIMARY KEY,
  name VARCHAR(50)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 玩家记录表
DROP TABLE IF EXISTS player_record;
CREATE TABLE player_record (
    id INT  PRIMARY KEY AUTO_INCREMENT,
    player_id INT,
    game_id INT,
    play_times INT,
    KEY `idx_game_player_id` (`game_id`,`player_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO game (id, name) VALUES (1, 'Game1'), (2, 'Game2'), (3, 'Game3');
INSERT INTO player (id, name) VALUES (1, 'Player1'), (2, 'Player2'), (3, 'Player3');
INSERT INTO player_record (id, player_id, game_id, play_times) VALUES (1, 1, 1, 10);
INSERT INTO player_record (id, player_id, game_id, play_times) VALUES (2, 2, 2, 8);
INSERT INTO player_record (id, player_id, game_id, play_times) VALUES (3, 3, 3, 12);

Explain两个变种

Explain有两个变种：EXPLAIN FORMAT=JSON(MySQL 5.6引入) 和 EXPLAIN FORMAT=TREE(MySQL 8.0.16引入)。这两个变种提供了更详细的查询执行计划信息。

注意：EXPLAIN EXTENDED 是在 MySQL 4.1 版本中引入的。这个命令可以提供关于 MySQL 如何执行 SQL 查询的更多信息。然而，从 MySQL 8.0.23 开始，EXPLAIN EXTENDED 已经被弃用，因为它们的功能已经被包含在 EXPLAIN FORMAT=JSON 和 EXPLAIN FORMAT=TREE 中。

EXPLAIN FORMAT=JSON

EXPLAIN FORMAT=JSON 提供了JSON格式的查询执行计划。例如：

EXPLAIN FORMAT=JSON SELECT * FROM game;

输出结果：

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "0.55"
    },
    "table": {
      "table_name": "game",
      "access_type": "ALL",
      "rows_examined_per_scan": 3,
      "rows_produced_per_join": 3,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "0.25",
        "eval_cost": "0.30",
        "prefix_cost": "0.55",
        "data_read_per_join": "480"
      },
      "used_columns": [
        "id",
        "name"
      ]
    }
  }
}