workshop.cn.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Mini OSS Insight Workshop\n",
    "\n",
    "👏 欢迎来到 Mini OSS Insight 的 Workshop!\n",
    "\n",
    "这是关于 Workshop 的完整指南，你可以通过阅读该指南一步一步地搭建起一个 Mini 版的 OSS Insight，并在过程掌握一些使用 TiDB / TiDB Cloud 进行数据分析的技巧。\n",
    "\n",
    "你可以在终端上运行一下命令来获取该指南的幻灯片版本：\n",
    "\n",
    "```shell\n",
    "jupyter nbconvert workshop.cn.ipynb --to slides --post serve\n",
    "```\n",
    "\n",
    "> **注意**\n",
    "> \n",
    "> 在开始后续步骤之前，请在 [pingcap/ossinsight](https://github.com/pingcap/ossinsight) 仓库页面的右上角点击 Fork 按钮来创建你的 Fork 仓库，然后点击 “Code” > “New with Options” 按钮来创建你的 Codespaces 开发环境，以便在后续的步骤当中你有足够的权限修改 Github Codespaces Secrets。\n",
    "> "
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## 准备\n",
    "\n",
    "在开始之前，你需要确保在你的开发环境中已经安装了以下软件/工具：\n",
    "\n",
    "- GitHub 账号 - 用于访问 GitHub API 获取 events 数据\n",
    "- GitHub Codespaces 开发环境 - 本教程通过 [GitHub Codespaces](https://github.com/codespaces) 准备开发环境\n",
    "- 可供连接的 TiDB 集群 - 用于存储 events 数据以及通过 SQL 语句进行数据分析\n",
    "- MyCLI - 连接到 TiDB 集群\n",
    "- Python 3.x and pip - Jupyter Notebook 需要的运行时环境\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### 1. 创建 Personal Access Token\n",
    "\n",
    "> **Note**\n",
    ">\n",
    "> 如果你使用 GitHub Codespaces 来操作该指南, 你可以跳过下面的步骤，因为在 Codespaces [默认的环境变量](https://docs.github.com/en/codespaces/developing-in-codespaces/default-environment-variables-for-your-codespace)当中已经包含了 `GITHUB_TOKEN` 变量。\n",
    ">  \n",
    "\n",
    "在登录到你的 GitHub 账号之后，你可以在个人设置页面[创建一个新的 Personal Access Token](https://github.com/settings/personal-access-tokens/new)，在后续步骤当中将使用该 Token 来访问 GitHub API。\n",
    "\n",
    "<center>\n",
    "  <img align=\"middle\" width=\"600\" alt=\"Create a New GitHub Personal Access Token\" src=\"https://user-images.githubusercontent.com/5086433/204564273-93cccbe4-d10a-4d1b-a9d1-112a1144712a.png\">\n",
    "  <p><i>创建一个新的 Personal Access Token</i></p>\n",
    "</center>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### 2. 准备 TiDB 集群\n",
    "\n",
    "你需要开启一个 TiDB 集群。你可以通过查看文档 [TiDB Cloud Quick Start](https://docs.pingcap.com/tidbcloud/tidb-cloud-quickstart#step-1-create-a-tidb-cluster) 文档学习如何创建一个 **Serverless tier** 集群，这可能会需要 20～25s 的时间。\n",
    "\n",
    "在你创建完集群之后，可以进入到集群的详情页。在这个页面，你可以在 **Connection** 面板中找到数据库集群的连接信息。\n",
    "\n",
    "<center>\n",
    "  <img align=\"middle\" width=\"800\" alt=\"Serverless Tier Cluster Manage Interface\" src=\"https://user-images.githubusercontent.com/5086433/204476069-0ddbdf6f-419c-4291-b929-ccfbd2f5ea5f.png\">\n",
    "  <p><i>Serverless Tier 集群管理界面</i></p>\n",
    "</center>\n",
    "\n",
    "你可以通过点开集群详情页右上角的集群修改菜单，点击其中的 “Security Settings” 按钮进入到安全设置窗口。\n",
    "\n",
    "<center>\n",
    "  <img width=\"480\" alt=\"The Cluster Modify Menu\" src=\"https://user-images.githubusercontent.com/85985765/204876779-3a4c6ac4-8814-47cd-b82a-40eb5e4d8f96.png\">\n",
    "  <p><i>TiDB Cloud 集群修改菜单</i></p>\n",
    "</center>\n",
    "\n",
    "在该窗口上点击 “Generate” 按钮来生成 root 用户密码，保存以便后续使用，\n",
    "\n",
    "<center>\n",
    "  <img width=\"720\" alt=\"Security Settings\" src=\"https://user-images.githubusercontent.com/85985765/204877348-5c3e9012-f7bf-42e9-8a03-fd9f14bfc826.png\">\n",
    "  <p><i>TiDB Cloud 集群安全设置窗口</i></p>\n",
    "</center>\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## 设置环境变量\n",
    "\n",
    "点击代码块左侧的执行按钮，运行下面的 Python 代码块。这段代码块将会一步一步引导你将上面准备的数据库连接信息配置到环境变量当中，以便后续步骤使用。\n",
    "\n",
    "下面的程序会将你输入的配置自动添加到 [GitHub Codespaces Secrets](https://docs.github.com/en/codespaces/managing-your-codespaces/managing-encrypted-secrets-for-your-codespaces) 的设置当中，待程序执行完成后点击右下角的 “Reload to apply” 按钮将环境变量加载到 Codespaces 环境。\n",
    "\n",
    "<center>\n",
    "    <img width=\"467\" alt=\"image\" src=\"https://user-images.githubusercontent.com/88522432/206100583-518b0465-79b0-46c0-bedb-c5aef3657cee.png\">\n",
    "    <p><i>Reload to apply the GitHub Codepsace Secrets</i></p>\n",
    "</center>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Setup successfully!\n"
     ]
    }
   ],
   "source": [
    "# ← Execute the following script to load environment variable.\n",
    "import getpass\n",
    "import os\n",
    "\n",
    "github_user = os.getenv(\"GITHUB_USER\")\n",
    "github_repo = os.getenv(\"GITHUB_REPOSITORY\")\n",
    "\n",
    "# Config GitHub personal access token.\n",
    "github_token = \"\"\n",
    "if os.getenv('GITHUB_TOKEN') is None:\n",
    "    github_token = getpass.getpass(prompt='Enter your personal access token of GitHub: ')\n",
    "else:\n",
    "    github_token = os.getenv('GITHUB_TOKEN')\n",
    "\n",
    "# Config database connection.\n",
    "db_endpoint = input(\"Enter the endpoint of TiDB cluster: \")\n",
    "db_port = \"4000\"\n",
    "db_username = input(\"Enter the username of TiDB cluster: \")\n",
    "db_password = getpass.getpass(prompt='Enter the password of TiDB cluster: ')\n",
    "db_name = \"ossinsight\"\n",
    "db_enable_ssl = \"y\"\n",
    "db_ssl_config='&ssl={\"minVersion\":\"TLSv1.2\"}'\n",
    "if db_enable_ssl == \"n\":\n",
    "    db_ssl_config = \"\"\n",
    "\n",
    "api_database_url = \"mysql://{}:{}@{}:{}/{}?connectionLimit=100&queueLimit=10000{}\\n\".format(\n",
    "    db_username, db_password, db_endpoint, db_port, db_name, db_ssl_config\n",
    ")\n",
    "\n",
    "etl_database_url = \"tidb://{}:{}@{}:{}/{}\".format(\n",
    "    db_username, db_password, db_endpoint, db_port, db_name\n",
    ")\n",
    "\n",
    "os.system(f\"gh secret set DB_ENDPOINT --body {db_endpoint} -a codespaces --repo {github_repo}\")\n",
    "os.system(f\"gh secret set DB_PORT --body {db_port} -a codespaces --repo {github_repo}\")\n",
    "os.system(f\"gh secret set DB_USERNAME --body {db_username} -a codespaces --repo {github_repo}\")\n",
    "os.system(f\"gh secret set DB_PASSWORD --body {db_password} -a codespaces --repo {github_repo}\")\n",
    "os.system(f\"gh secret set DB_NAME --body {db_name} -a codespaces --repo {github_repo}\")\n",
    "os.system(f\"gh secret set DB_ENABLE_SSL --body {db_enable_ssl} -a codespaces --repo {github_repo}\")\n",
    "os.system(f\"gh secret set ETL_DATABASE_URL --body '{etl_database_url}' -a codespaces --repo {github_repo}\")\n",
    "os.system(f\"gh secret set API_DATABASE_URL --body '{api_database_url}' -a codespaces --repo {github_repo}\")\n",
    "\n",
    "# export ETL_DATABASE_URL=tidb://${DB_USERNAME}:${DB_PASSWORD}@${DB_ENDPOINT}:4000/${DB_NAME}\n",
    "\n",
    "# Write to ./packages/api-server/.env file.\n",
    "api_server_dot_env=\"./packages/api-server/.env\"\n",
    "with open(api_server_dot_env, \"w\") as file:\n",
    "    file.write(\n",
    "        \"DATABASE_URL={}\".format(api_database_url)\n",
    "    )\n",
    "    file.write(\"ENABLE_CACHE=false\\n\")\n",
    "    file.write(\"GITHUB_ACCESS_TOKENS={}\\n\".format(github_token))\n",
    "\n",
    "print(\"Setup successfully!\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 创建连接"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### 使用 MyCLI 连接到 TiDB Serverless Tier 集群\n",
    "\n",
    "执行下面的 SQL 语句验证是否能够连接到 TiDB 集群:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "vscode": {
     "languageId": "shellscript"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------------------------------------------------------+\n",
      "| \u001b[38;5;47;01mtidb_version()\u001b[39;00m                                            |\n",
      "+-----------------------------------------------------------+\n",
      "| Release Version: v6.3.0-serverless                        |\n",
      "| Edition: Community                                        |\n",
      "| Git Commit Hash: e87c16b215d518aed4921b8ef3b13e90e3ed6e2d |\n",
      "| Git Branch: release-6.3-serverless                        |\n",
      "| UTC Build Time: 2022-11-25 09:31:28                       |\n",
      "| GoVersion: go1.19                                         |\n",
      "| Race Enabled: false                                       |\n",
      "| TiKV Min Version: 6.1.0                                   |\n",
      "| Check Table Before Drop: false                            |\n",
      "| Store: tikv                                               |\n",
      "+-----------------------------------------------------------+\n"
     ]
    }
   ],
   "source": [
    "!mycli -h ${DB_ENDPOINT} -P ${DB_PORT} -u ${DB_USERNAME} -p ${DB_PASSWORD} -t \\\n",
    "    --ssl-ca=/etc/ssl/certs/ca-certificates.crt \\\n",
    "    --ssl-verify-server-cert \\\n",
    "    -e 'SELECT tidb_version()\\G'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "如果成功执行，你会看到 TiDB 集群的版本信息。"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### 使用 Python 连接到 TiDB 集群 (必须)\n",
    "\n",
    "首先，请执行下面的 Python 代码，初始化一个 `query` 函数和一个 `execute` 函数用于后续的 SQL 查询："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import mysql.connector as connection\n",
    "import pandas as pd\n",
    "\n",
    "db_host = os.getenv('DB_ENDPOINT')\n",
    "db_port = os.getenv(\"DB_PORT\")\n",
    "db_username = os.getenv('DB_USERNAME')\n",
    "db_password = os.getenv('DB_PASSWORD')\n",
    "db_name = os.getenv('DB_NAME')\n",
    "\n",
    "mydb = connection.connect(\n",
    "  host=db_host,\n",
    "  port=db_port,\n",
    "  user=db_username,\n",
    "  password=db_password,\n",
    "  database=db_name\n",
    ")\n",
    "\n",
    "def execute(sql):\n",
    "  try:\n",
    "    mycursor = mydb.cursor()\n",
    "    mycursor.execute(sql)\n",
    "    mydb.commit()\n",
    "    return  \"OK, {} record(s) affected\".format(mycursor.rowcount)\n",
    "  except Exception as e:\n",
    "    print(str(e))\n",
    "\n",
    "def query(sql):\n",
    "  try:\n",
    "    df = pd.read_sql(sql, mydb)\n",
    "    return df.style.set_properties(**{'text-align': 'left'})\n",
    "  except Exception as e:\n",
    "    print(str(e))"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## 导入数据\n",
    "\n",
    "### GitHub Events API\n",
    "\n",
    "通过 [GitHub 的 API 文档](https://docs.github.com/en/rest/activity/events), 我们知道可以通过请求 `/events` API 来 GitHub 上所有公开仓库的公开事件数据，这个数据的推送是准实时的，GitHub 设定了 5 分钟的延迟。\n",
    "\n",
    "你可以执行下面的命令查看这个接口返回数据的结构："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!curl -s \\\n",
    "  -H \"Accept: application/vnd.github.v3+json\" \\\n",
    "  -H \"Authorization: token $GITHUB_TOKEN\" \\\n",
    "  https://api.github.com/events?per_page=1"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### GhArchive\n",
    "\n",
    "由于 `/events` API 的限制，我们无法获取到历史的所有事件数据，于是我们找寻了该 API 的归档数据。通过 [GhArchive](https://www.gharchive.org/) 项目，我们可以下载以天或小时为粒度的归档数据。\n",
    "\n",
    "| Query | Downloadable Files |\n",
    "| ---- | ---- |\n",
    "| Activity for 1/1/2015 @ 3PM UTC |\t`https://data.gharchive.org/2015-01-01-15.json.gz` |\n",
    "| Activity for 1/1/2015 | `https://data.gharchive.org/2015-01-01-{0..23}.json.gz` |\n",
    "| Activity for all of January 2015 | `https://data.gharchive.org/2015-01-{01..31}-{0..23}.json.gz` |\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### OSS Insight 数据总量\n",
    "\n",
    "截止目前（2022-12-06），OSS Insight 已经收集了超过 53 亿条公开事件数据，你可以通过访问 API 或在网站上查看当前收集的事件总数："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[1;39m{\n",
      "  \u001b[0m\u001b[34;1m\"cnt\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m5343816396\u001b[0m\u001b[1;39m,\n",
      "  \u001b[0m\u001b[34;1m\"latest_created_at\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"2022-12-07T06:13:00.000Z\"\u001b[0m\u001b[1;39m,\n",
      "  \u001b[0m\u001b[34;1m\"latest_timestamp\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m1670393580\u001b[0m\u001b[1;39m\n",
      "\u001b[1;39m}\u001b[0m\n"
     ]
    }
   ],
   "source": [
    "!curl -s https://api.ossinsight.io/q/events-total | jq -r \".data[0]\""
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 导入示例数据\n",
    "\n",
    "\n",
    "为了方便演示，我们从中筛选出了部分数据，使用 [Dumpling](https://docs.pingcap.com/zh/tidb/stable/dumpling-overview) 工具导出到 AWS S3 文件存储当中，你可以使用 TiDB Cloud 的 Import 功能将示例数据导入到你的 Severless Tier 集群：\n",
    "\n",
    "<details>\n",
    "\n",
    "<summary>导出示例数据的 Dumpling 命令</summary>\n",
    "\n",
    "`github_events` 表:\n",
    "\n",
    "```bash\n",
    "tiup dumpling --host <endpoint> --port 4000 --user <uername> --password <password> --filetype sql --filter \"gharchive_dev.github_events\" --where=\"type in ('WatchEvent', 'IssuesEvent', 'PullRequestEvent', 'PushEvent') and repo_id in (507775,41986369,16563587,60246359,108110,11008207,6358188,48833910,114187903,105944401,50874442,44781140,166515022,20089857,19816070,206444,31006158,99919302,28738447,402945349,30753733,372536760,302827809,196353673,393235957,417731747,417726876,453068084,5349565,422821402,496505424,927442,138754790,49876476,208728772)\" -o \"s3://ossinsight/workshop/oss_database_sample\" --s3.region \"us-west-2\"\n",
    "```\n",
    "\n",
    "`github_users` 表:\n",
    "\n",
    "```bash\n",
    "tiup dumpling --host <endpoint>  --port 4000 --user <uername> --password <password> --filetype sql --filter \"gharchive_dev.github_users\" --where=\"login IN (SELECT ge.actor_login FROM gharchive_dev.github_events ge WHERE ge.type IN ('WatchEvent', 'IssuesEvent', 'PullRequestEvent', 'PushEvent') AND ge.repo_id IN (507775,41986369,16563587,60246359,108110,11008207,6358188,48833910,114187903,105944401,50874442,44781140,166515022,20089857,19816070,206444,31006158,99919302,28738447,402945349,30753733,372536760,302827809,196353673,393235957,417731747,417726876,453068084,5349565,422821402,496505424,927442,138754790,49876476,208728772))\" -o \"s3://ossinsight/workshop/oss_database_sample\" --s3.region \"us-west-2\"\n",
    "```\n",
    "\n",
    "</details>\n",
    "\n",
    "请复制下面的 `S3 URI` 和 `Role ARN` 粘贴到 Cloud Import 的配置当中，根据页面提示开启导入任务：\n",
    "\n",
    "S3 URI:\n",
    "\n",
    "```\n",
    "s3://ossinsight/workshop/oss_database_sample/\n",
    "```\n",
    "\n",
    "Role ARN:\n",
    "\n",
    "```\n",
    "arn:aws:iam::494090988690:role/tidb-cloud-reader\n",
    "```\n",
    "\n",
    "<details>\n",
    "\n",
    "<summary>操作过程截图</summary>\n",
    "\n",
    "<center>\n",
    "    <img width=\"700\" alt=\"image\" src=\"https://user-images.githubusercontent.com/85985765/205849289-de1203f7-80dd-4520-8e40-fc91f0a76631.png\">\n",
    "    <p><i>TiDB Cloud 导入功能 - 准备页面</i></p>\n",
    "</center>\n",
    "\n",
    "<center>\n",
    "    <img width=\"700\" alt=\"TiDB Cloud - Importing\" src=\"https://user-images.githubusercontent.com/85985765/205808913-9c32d61f-f9aa-43fa-ad4d-1bba4201b41d.png\">\n",
    "    <p><i>TiDB Cloud 导入功能 - 状态页面</i></p>\n",
    "</center>\n",
    "\n",
    "</details>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 导入实时数据\n",
    "\n",
    "执行下面的 Docker Compose 命令启动 ETL 脚本，该脚本负责将 Github Events API 返回结果中的 JSON 数据导入到数据库当中。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!docker compose pull etl\n",
    "!docker compose up -d etl"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "通过下面的命令查看 etl 容器的运行状态："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[36mossinsight-etl-1  | \u001b[0m== 20221120144415 CreateMentionLogs: migrating ================================\n",
      "\u001b[36mossinsight-etl-1  | \u001b[0m-- create_table(:mention_logs)\n",
      "\u001b[36mossinsight-etl-1  | \u001b[0m   -> 0.2656s\n",
      "\u001b[36mossinsight-etl-1  | \u001b[0m== 20221120144415 CreateMentionLogs: migrated (1.7977s) =======================\n",
      "\u001b[36mossinsight-etl-1  | \u001b[0m\n",
      "\u001b[36mossinsight-etl-1  | \u001b[0mAnnotated (7): app/models/mention_log.rb, test/models/mention_log_test.rb, test/fixtures/mention_logs.yml, test/fixtures/mention_logs.yml, app/models/github_event.rb, test/models/github_event_test.rb, test/fixtures/github_events.yml\n",
      "\u001b[36mossinsight-etl-1  | \u001b[0m+ bundle exec rake db:seed\n",
      "\u001b[36mossinsight-etl-1  | \u001b[0mmysql: unknown variable 'ssl-mode=VERIFY_IDENTITY'\n",
      "\u001b[36mossinsight-etl-1  | \u001b[0m+ bundle exec rake gh:set_tiflash_replica\n",
      "\u001b[36mossinsight-etl-1  | \u001b[0m+ bundle exec rails runner 'Realtime.new(ENV[\"GITHUB_TOKEN\"].to_s.split(\",\"), 100).run'\n"
     ]
    }
   ],
   "source": [
    "!docker compose logs etl --tail 10"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "重复执行以下命令行几次，如果 events 总数在不断增长说明 ETL 实时导入正在运行："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+\n",
      "| \u001b[38;5;47;01mCOUNT(*)\u001b[39;00m |\n",
      "+----------+\n",
      "| 1159186  |\n",
      "+----------+\n"
     ]
    }
   ],
   "source": [
    "!mycli -h ${DB_ENDPOINT} -P ${DB_PORT} -u ${DB_USERNAME} -p ${DB_PASSWORD} -D ${DB_NAME} -t \\\n",
    "    --ssl-ca=/etc/ssl/certs/ca-certificates.crt \\\n",
    "    --ssl-verify-server-cert \\\n",
    "    -e 'SELECT COUNT(*) FROM github_events\\G'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 背景知识"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "#### 优化器\n",
    "\n",
    "SQL 是一种声明式语言，而非过程性的语言。也就是说，它描述的是最终结果应该如何，而非按顺序执行的步骤。因此在 TiDB 在处理 SQL 语句的过程中，除了要确保能够正确返回语句所描述的最终结果，还要确定 SQL 语句的执行过程，并且尽可能的对 SQL 语句进行性能优化，从而得到最佳的执行计划。\n",
    "\n",
    "SQL 性能优化的过程，可以理解为 GPS 导航的过程。你提供地址后，GPS 软件利用各种统计信息（例如以前的行程、速度限制等元数据，以及实时交通信息）规划出一条最省时的路线。这与 TiDB 中的 SQL 性能优化过程相对应。\n",
    "\n",
    "我们通常会把负责这部分 SQL 优化工作的组件成为**优化器（Optimizer）**。\n",
    "\n",
    "如下图所示，一条 SQL 语句在经过语法解析和校验之后，经过优化器的逻辑优化和物理优化来制定最终的执行计划，交给执行器（Executor）去执行。和 GPS 软件一样，优化器在进行执行计划规划的时候也会利用到一些统计信息来进行评估。\n",
    "\n",
    "<center>\n",
    "  <img width=\"640\" alt=\"The Cluster Modify Menu\" src=\"https://user-images.githubusercontent.com/85985765/205136445-3d9dd676-56bc-4f75-bf6d-cdb1af8c1b79.png\">\n",
    "  <p><i>SQL 优化流程图</i></p>\n",
    "</center>\n",
    "\n",
    "上图其实是一个简化的示意图，因为它只描述了以 TiKV 为存储引擎的情况。但是在 TiDB 支持 HTAP 能力后，引进了负责分析负载的 TiFlash 存储引擎。这给优化器带来了新的挑战，优化器需要在制定执行计划和进行物理\n",
    "优化时需要考虑：\n",
    "\n",
    "> 这条 SQL 应该使用哪个存储引擎执行性能会更好，选 TiKV 还是 TiFlash？"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "#### Optimizer Hints\n",
    "\n",
    "> 如果优化器难以抉择，那我们不妨给它一点提示，告诉它我们希望使用哪种存储引擎执行?\n",
    "\n",
    "TiDB 的优化器提供了 `READ_FROM_STORAGE` Hint 来允许开发者在 SQL 语句级别来控制哪一张表应该使用哪个执行引擎去执行：\n",
    "\n",
    "```sql\n",
    "SELECT /*+ READ_FROM_STORAGE(TIKV[t1]) */ a FROM t1;\n",
    "```\n",
    "\n",
    "如果查询中表定义了别名，指定存储引擎时填写的也应该是别名：\n",
    "\n",
    "```sql\n",
    "SELECT /*+ READ_FROM_STORAGE(TIFLASH[t2]) */ a FROM t t2;\n",
    "```\n",
    "\n",
    "从 TiFlash 上读取 t1 表的数据，在 TiKV 上读取 t2 的数据：\n",
    "\n",
    "```sql\n",
    "SELECT /*+ READ_FROM_STORAGE(TIFLASH[t1], TIKV[t2]) */ t1.a FROM t t1, t t2 WHERE t1.a = t2.a;\n",
    "```\n",
    "\n",
    "如果使用了 [CTE](https://docs.pingcap.com/tidb/dev/dev-guide-use-common-table-expression) 语句，应该在原始查询当中添加 Hint：\n",
    "\n",
    "```sql\n",
    "WITH cte AS (\n",
    "    SELECT /*+ READ_FROM_STORAGE(TIFLASH[t2]) */ a, COUNT(*) AS cnt\n",
    "    FROM t t2\n",
    "    GROUP BY a\n",
    ")\n",
    "SELECT a, cnt\n",
    "FROM cte;\n",
    "```"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "#### 代价模型\n",
    "\n",
    "> 使用 Optimizer Hints 很简单，但是有没有更智能的方法，特别是在做临时查询时，优化器能否智能的选择应该使用哪种引擎查询效率最高？\n",
    "\n",
    "<center>\n",
    "  <img width=\"640\" alt=\"物理优化中的代码模型\" src=\"https://user-images.githubusercontent.com/85985765/205192450-c23f2199-1ab1-41d2-90f3-c17c5d6e252e.png\">\n",
    "  <p><i>SQL 优化流程图</i></p>\n",
    "</center>\n",
    "\n",
    "TiDB v6.2.0 引入了新的代价模型 Cost Model Version 2。\n",
    "\n",
    "Cost Model Version 2 对代价公式进行了更精确的回归校准，调整了部分代价公式，比此前版本的代价公式更加准确。\n",
    "\n",
    "OSS Insight 使用了该版本的 Cost Model, 但是因为目前 Cost Model Version 2 还处于实验阶段，需要通过执行以下 SQL 语句进行启用：\n",
    "\n",
    "```sql\n",
    "SET GLOBAL tidb_enable_new_cost_interface = 'ON';\n",
    "SET GLOBAL tidb_cost_model_version = 2;\n",
    "```\n",
    "\n",
    "在后续版本当中，Cost Model Version 2 会替代掉原有的 Cost Model 作为默认的代码模型。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Example SQL\n",
    "\n",
    "下面，我们会以 OSS Insight 为示例，讲解如何使用 TiDB 进行数据分析以及在数据分析时如何更好的通过优化器来发挥行存 TiKV 和列存 TiFlash 各自的优势。\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 开启 Cost Model V2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'OK, 0 record(s) affected'"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "execute(\"\"\"\n",
    "SET GLOBAL tidb_cost_model_version = 2;\n",
    "\"\"\")\n",
    "execute(\"\"\"\n",
    "SET GLOBAL tidb_enable_new_cost_interface = 'ON';\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 检查健康度"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "因为在 OSS Insight 当中 `github_events` 表使用了[分区表](https://docs.pingcap.com/zh/tidb/dev/partitioned-table)的功能，并且开启了[动态分区裁剪模式](https://docs.pingcap.com/zh/tidb/dev/partitioned-table#%E5%8A%A8%E6%80%81%E8%A3%81%E5%89%AA%E6%A8%A1%E5%BC%8F)。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style type=\"text/css\">\n",
       "#T_095a2_row0_col0 {\n",
       "  text-align: left;\n",
       "}\n",
       "</style>\n",
       "<table id=\"T_095a2_\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th class=\"blank level0\" >&nbsp;</th>\n",
       "      <th class=\"col_heading level0 col0\" >@@tidb_partition_prune_mode;\n",
       "</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th id=\"T_095a2_level0_row0\" class=\"row_heading level0 row0\" >0</th>\n",
       "      <td id=\"T_095a2_row0_col0\" class=\"data row0 col0\" >dynamic</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n"
      ],
      "text/plain": [
       "<pandas.io.formats.style.Styler at 0x7f846019f240>"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query(\"\"\"\n",
    "SELECT @@tidb_partition_prune_mode;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "因为优化器需要根据统计信息来制定执行计划，我们需要留意统计信息是否过期。我们可以通过 `SHOW STATS_HEALTHY` 命令来查看表的健康度，通过表的[健康度](https://docs.pingcap.com/zh/tidb/dev/statistics#%E8%A1%A8%E7%9A%84%E5%81%A5%E5%BA%B7%E5%BA%A6%E4%BF%A1%E6%81%AF)，可以粗略估计表上统计信息的准确度。\n",
    "\n",
    "特别是在你使用分区表时，动态分区裁剪模式需要依赖 `global` 的统计信息。\n",
    "\n",
    "为保证开启全局 dynamic 动态裁剪模式时，SQL 可以用上正确的统计信息，此时需要手动触发一次 analyze 来更新汇总统计信息."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style type=\"text/css\">\n",
       "#T_7c1e9_row0_col0, #T_7c1e9_row0_col1, #T_7c1e9_row0_col2, #T_7c1e9_row0_col3, #T_7c1e9_row1_col0, #T_7c1e9_row1_col1, #T_7c1e9_row1_col2, #T_7c1e9_row1_col3, #T_7c1e9_row2_col0, #T_7c1e9_row2_col1, #T_7c1e9_row2_col2, #T_7c1e9_row2_col3, #T_7c1e9_row3_col0, #T_7c1e9_row3_col1, #T_7c1e9_row3_col2, #T_7c1e9_row3_col3, #T_7c1e9_row4_col0, #T_7c1e9_row4_col1, #T_7c1e9_row4_col2, #T_7c1e9_row4_col3, #T_7c1e9_row5_col0, #T_7c1e9_row5_col1, #T_7c1e9_row5_col2, #T_7c1e9_row5_col3, #T_7c1e9_row6_col0, #T_7c1e9_row6_col1, #T_7c1e9_row6_col2, #T_7c1e9_row6_col3, #T_7c1e9_row7_col0, #T_7c1e9_row7_col1, #T_7c1e9_row7_col2, #T_7c1e9_row7_col3, #T_7c1e9_row8_col0, #T_7c1e9_row8_col1, #T_7c1e9_row8_col2, #T_7c1e9_row8_col3, #T_7c1e9_row9_col0, #T_7c1e9_row9_col1, #T_7c1e9_row9_col2, #T_7c1e9_row9_col3, #T_7c1e9_row10_col0, #T_7c1e9_row10_col1, #T_7c1e9_row10_col2, #T_7c1e9_row10_col3, #T_7c1e9_row11_col0, #T_7c1e9_row11_col1, #T_7c1e9_row11_col2, #T_7c1e9_row11_col3, #T_7c1e9_row12_col0, #T_7c1e9_row12_col1, #T_7c1e9_row12_col2, #T_7c1e9_row12_col3, #T_7c1e9_row13_col0, #T_7c1e9_row13_col1, #T_7c1e9_row13_col2, #T_7c1e9_row13_col3, #T_7c1e9_row14_col0, #T_7c1e9_row14_col1, #T_7c1e9_row14_col2, #T_7c1e9_row14_col3, #T_7c1e9_row15_col0, #T_7c1e9_row15_col1, #T_7c1e9_row15_col2, #T_7c1e9_row15_col3, #T_7c1e9_row16_col0, #T_7c1e9_row16_col1, #T_7c1e9_row16_col2, #T_7c1e9_row16_col3, #T_7c1e9_row17_col0, #T_7c1e9_row17_col1, #T_7c1e9_row17_col2, #T_7c1e9_row17_col3, #T_7c1e9_row18_col0, #T_7c1e9_row18_col1, #T_7c1e9_row18_col2, #T_7c1e9_row18_col3, #T_7c1e9_row19_col0, #T_7c1e9_row19_col1, #T_7c1e9_row19_col2, #T_7c1e9_row19_col3, #T_7c1e9_row20_col0, #T_7c1e9_row20_col1, #T_7c1e9_row20_col2, #T_7c1e9_row20_col3, #T_7c1e9_row21_col0, #T_7c1e9_row21_col1, #T_7c1e9_row21_col2, #T_7c1e9_row21_col3 {\n",
       "  text-align: left;\n",
       "}\n",
       "</style>\n",
       "<table id=\"T_7c1e9_\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th class=\"blank level0\" >&nbsp;</th>\n",
       "      <th class=\"col_heading level0 col0\" >Db_name</th>\n",
       "      <th class=\"col_heading level0 col1\" >Table_name</th>\n",
       "      <th class=\"col_heading level0 col2\" >Partition_name</th>\n",
       "      <th class=\"col_heading level0 col3\" >Healthy</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th id=\"T_7c1e9_level0_row0\" class=\"row_heading level0 row0\" >0</th>\n",
       "      <td id=\"T_7c1e9_row0_col0\" class=\"data row0 col0\" >ossinsight</td>\n",
       "      <td id=\"T_7c1e9_row0_col1\" class=\"data row0 col1\" >github_events</td>\n",
       "      <td id=\"T_7c1e9_row0_col2\" class=\"data row0 col2\" >global</td>\n",
       "      <td id=\"T_7c1e9_row0_col3\" class=\"data row0 col3\" >100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_7c1e9_level0_row1\" class=\"row_heading level0 row1\" >1</th>\n",
       "      <td id=\"T_7c1e9_row1_col0\" class=\"data row1 col0\" >ossinsight</td>\n",
       "      <td id=\"T_7c1e9_row1_col1\" class=\"data row1 col1\" >github_events</td>\n",
       "      <td id=\"T_7c1e9_row1_col2\" class=\"data row1 col2\" >push_event</td>\n",
       "      <td id=\"T_7c1e9_row1_col3\" class=\"data row1 col3\" >100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_7c1e9_level0_row2\" class=\"row_heading level0 row2\" >2</th>\n",
       "      <td id=\"T_7c1e9_row2_col0\" class=\"data row2 col0\" >ossinsight</td>\n",
       "      <td id=\"T_7c1e9_row2_col1\" class=\"data row2 col1\" >github_events</td>\n",
       "      <td id=\"T_7c1e9_row2_col2\" class=\"data row2 col2\" >create_event</td>\n",
       "      <td id=\"T_7c1e9_row2_col3\" class=\"data row2 col3\" >100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_7c1e9_level0_row3\" class=\"row_heading level0 row3\" >3</th>\n",
       "      <td id=\"T_7c1e9_row3_col0\" class=\"data row3 col0\" >ossinsight</td>\n",
       "      <td id=\"T_7c1e9_row3_col1\" class=\"data row3 col1\" >github_events</td>\n",
       "      <td id=\"T_7c1e9_row3_col2\" class=\"data row3 col2\" >pull_request_event</td>\n",
       "      <td id=\"T_7c1e9_row3_col3\" class=\"data row3 col3\" >100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_7c1e9_level0_row4\" class=\"row_heading level0 row4\" >4</th>\n",
       "      <td id=\"T_7c1e9_row4_col0\" class=\"data row4 col0\" >ossinsight</td>\n",
       "      <td id=\"T_7c1e9_row4_col1\" class=\"data row4 col1\" >github_events</td>\n",
       "      <td id=\"T_7c1e9_row4_col2\" class=\"data row4 col2\" >watch_event</td>\n",
       "      <td id=\"T_7c1e9_row4_col3\" class=\"data row4 col3\" >100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_7c1e9_level0_row5\" class=\"row_heading level0 row5\" >5</th>\n",
       "      <td id=\"T_7c1e9_row5_col0\" class=\"data row5 col0\" >ossinsight</td>\n",
       "      <td id=\"T_7c1e9_row5_col1\" class=\"data row5 col1\" >github_events</td>\n",
       "      <td id=\"T_7c1e9_row5_col2\" class=\"data row5 col2\" >issue_comment_event</td>\n",
       "      <td id=\"T_7c1e9_row5_col3\" class=\"data row5 col3\" >100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_7c1e9_level0_row6\" class=\"row_heading level0 row6\" >6</th>\n",
       "      <td id=\"T_7c1e9_row6_col0\" class=\"data row6 col0\" >ossinsight</td>\n",
       "      <td id=\"T_7c1e9_row6_col1\" class=\"data row6 col1\" >github_events</td>\n",
       "      <td id=\"T_7c1e9_row6_col2\" class=\"data row6 col2\" >issues_event</td>\n",
       "      <td id=\"T_7c1e9_row6_col3\" class=\"data row6 col3\" >100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_7c1e9_level0_row7\" class=\"row_heading level0 row7\" >7</th>\n",
       "      <td id=\"T_7c1e9_row7_col0\" class=\"data row7 col0\" >ossinsight</td>\n",
       "      <td id=\"T_7c1e9_row7_col1\" class=\"data row7 col1\" >github_events</td>\n",
       "      <td id=\"T_7c1e9_row7_col2\" class=\"data row7 col2\" >delete_event</td>\n",
       "      <td id=\"T_7c1e9_row7_col3\" class=\"data row7 col3\" >100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_7c1e9_level0_row8\" class=\"row_heading level0 row8\" >8</th>\n",
       "      <td id=\"T_7c1e9_row8_col0\" class=\"data row8 col0\" >ossinsight</td>\n",
       "      <td id=\"T_7c1e9_row8_col1\" class=\"data row8 col1\" >github_events</td>\n",
       "      <td id=\"T_7c1e9_row8_col2\" class=\"data row8 col2\" >fork_event</td>\n",
       "      <td id=\"T_7c1e9_row8_col3\" class=\"data row8 col3\" >100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_7c1e9_level0_row9\" class=\"row_heading level0 row9\" >9</th>\n",
       "      <td id=\"T_7c1e9_row9_col0\" class=\"data row9 col0\" >ossinsight</td>\n",
       "      <td id=\"T_7c1e9_row9_col1\" class=\"data row9 col1\" >github_events</td>\n",
       "      <td id=\"T_7c1e9_row9_col2\" class=\"data row9 col2\" >pull_request_review_comment_event</td>\n",
       "      <td id=\"T_7c1e9_row9_col3\" class=\"data row9 col3\" >100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_7c1e9_level0_row10\" class=\"row_heading level0 row10\" >10</th>\n",
       "      <td id=\"T_7c1e9_row10_col0\" class=\"data row10 col0\" >ossinsight</td>\n",
       "      <td id=\"T_7c1e9_row10_col1\" class=\"data row10 col1\" >github_events</td>\n",
       "      <td id=\"T_7c1e9_row10_col2\" class=\"data row10 col2\" >pull_request_review_event</td>\n",
       "      <td id=\"T_7c1e9_row10_col3\" class=\"data row10 col3\" >100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_7c1e9_level0_row11\" class=\"row_heading level0 row11\" >11</th>\n",
       "      <td id=\"T_7c1e9_row11_col0\" class=\"data row11 col0\" >ossinsight</td>\n",
       "      <td id=\"T_7c1e9_row11_col1\" class=\"data row11 col1\" >github_events</td>\n",
       "      <td id=\"T_7c1e9_row11_col2\" class=\"data row11 col2\" >gollum_event</td>\n",
       "      <td id=\"T_7c1e9_row11_col3\" class=\"data row11 col3\" >100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_7c1e9_level0_row12\" class=\"row_heading level0 row12\" >12</th>\n",
       "      <td id=\"T_7c1e9_row12_col0\" class=\"data row12 col0\" >ossinsight</td>\n",
       "      <td id=\"T_7c1e9_row12_col1\" class=\"data row12 col1\" >github_events</td>\n",
       "      <td id=\"T_7c1e9_row12_col2\" class=\"data row12 col2\" >release_event</td>\n",
       "      <td id=\"T_7c1e9_row12_col3\" class=\"data row12 col3\" >100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_7c1e9_level0_row13\" class=\"row_heading level0 row13\" >13</th>\n",
       "      <td id=\"T_7c1e9_row13_col0\" class=\"data row13 col0\" >ossinsight</td>\n",
       "      <td id=\"T_7c1e9_row13_col1\" class=\"data row13 col1\" >github_events</td>\n",
       "      <td id=\"T_7c1e9_row13_col2\" class=\"data row13 col2\" >member_event</td>\n",
       "      <td id=\"T_7c1e9_row13_col3\" class=\"data row13 col3\" >100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_7c1e9_level0_row14\" class=\"row_heading level0 row14\" >14</th>\n",
       "      <td id=\"T_7c1e9_row14_col0\" class=\"data row14 col0\" >ossinsight</td>\n",
       "      <td id=\"T_7c1e9_row14_col1\" class=\"data row14 col1\" >github_events</td>\n",
       "      <td id=\"T_7c1e9_row14_col2\" class=\"data row14 col2\" >commit_comment_event</td>\n",
       "      <td id=\"T_7c1e9_row14_col3\" class=\"data row14 col3\" >100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_7c1e9_level0_row15\" class=\"row_heading level0 row15\" >15</th>\n",
       "      <td id=\"T_7c1e9_row15_col0\" class=\"data row15 col0\" >ossinsight</td>\n",
       "      <td id=\"T_7c1e9_row15_col1\" class=\"data row15 col1\" >github_events</td>\n",
       "      <td id=\"T_7c1e9_row15_col2\" class=\"data row15 col2\" >public_event</td>\n",
       "      <td id=\"T_7c1e9_row15_col3\" class=\"data row15 col3\" >100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_7c1e9_level0_row16\" class=\"row_heading level0 row16\" >16</th>\n",
       "      <td id=\"T_7c1e9_row16_col0\" class=\"data row16 col0\" >ossinsight</td>\n",
       "      <td id=\"T_7c1e9_row16_col1\" class=\"data row16 col1\" >github_events</td>\n",
       "      <td id=\"T_7c1e9_row16_col2\" class=\"data row16 col2\" >gist_event</td>\n",
       "      <td id=\"T_7c1e9_row16_col3\" class=\"data row16 col3\" >100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_7c1e9_level0_row17\" class=\"row_heading level0 row17\" >17</th>\n",
       "      <td id=\"T_7c1e9_row17_col0\" class=\"data row17 col0\" >ossinsight</td>\n",
       "      <td id=\"T_7c1e9_row17_col1\" class=\"data row17 col1\" >github_events</td>\n",
       "      <td id=\"T_7c1e9_row17_col2\" class=\"data row17 col2\" >follow_event</td>\n",
       "      <td id=\"T_7c1e9_row17_col3\" class=\"data row17 col3\" >100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_7c1e9_level0_row18\" class=\"row_heading level0 row18\" >18</th>\n",
       "      <td id=\"T_7c1e9_row18_col0\" class=\"data row18 col0\" >ossinsight</td>\n",
       "      <td id=\"T_7c1e9_row18_col1\" class=\"data row18 col1\" >github_events</td>\n",
       "      <td id=\"T_7c1e9_row18_col2\" class=\"data row18 col2\" >event</td>\n",
       "      <td id=\"T_7c1e9_row18_col3\" class=\"data row18 col3\" >100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_7c1e9_level0_row19\" class=\"row_heading level0 row19\" >19</th>\n",
       "      <td id=\"T_7c1e9_row19_col0\" class=\"data row19 col0\" >ossinsight</td>\n",
       "      <td id=\"T_7c1e9_row19_col1\" class=\"data row19 col1\" >github_events</td>\n",
       "      <td id=\"T_7c1e9_row19_col2\" class=\"data row19 col2\" >download_event</td>\n",
       "      <td id=\"T_7c1e9_row19_col3\" class=\"data row19 col3\" >100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_7c1e9_level0_row20\" class=\"row_heading level0 row20\" >20</th>\n",
       "      <td id=\"T_7c1e9_row20_col0\" class=\"data row20 col0\" >ossinsight</td>\n",
       "      <td id=\"T_7c1e9_row20_col1\" class=\"data row20 col1\" >github_events</td>\n",
       "      <td id=\"T_7c1e9_row20_col2\" class=\"data row20 col2\" >team_add_event</td>\n",
       "      <td id=\"T_7c1e9_row20_col3\" class=\"data row20 col3\" >100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_7c1e9_level0_row21\" class=\"row_heading level0 row21\" >21</th>\n",
       "      <td id=\"T_7c1e9_row21_col0\" class=\"data row21 col0\" >ossinsight</td>\n",
       "      <td id=\"T_7c1e9_row21_col1\" class=\"data row21 col1\" >github_events</td>\n",
       "      <td id=\"T_7c1e9_row21_col2\" class=\"data row21 col2\" >fork_apply_event</td>\n",
       "      <td id=\"T_7c1e9_row21_col3\" class=\"data row21 col3\" >100</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n"
      ],
      "text/plain": [
       "<pandas.io.formats.style.Styler at 0x7fe8940894a8>"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query(\"\"\"\n",
    "SHOW STATS_HEALTHY WHERE db_name = 'ossinsight' AND table_name = 'github_events';\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "通过 `ANALYZE TABLE` 命令手动触发收集表的统计信息："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'OK, 0 record(s) affected'"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "execute(\"\"\"\n",
    "ANALYZE TABLE ossinsight.github_events;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Example 1: How many pull request does `ti-chi-bot` created?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "熟悉 TiDB 的用户可能会知道，在 TiDB 的版本规则里会存在两种版本：\n",
    "\n",
    "- 一种是为了能够更快交付迭代而设计的开发里程碑版本 (Development Milestone Releases, DMR)\n",
    "- 另外一种是提供长期技术支持的长期支持版本 (Long-Term Support Releases, LTS)\n",
    "\n",
    "在 LTS 生命周期内会按需发布补丁版本 (Patch Release)。如果一个修复 BUG 的 Pull Request 会被 Cherry-Pick 到受影响的 LTS 分支进行修复，这部分工作会由机器人根据研发和 QA 团队确定的受影响版本列表进行自动的 Cherry—Pick，所以你会在 TiDB 的代码仓库当中看见不少[由机器人发起的 Pull Request](https://github.com/pingcap/tidb/pulls/ti-chi-bot)。\n",
    "\n",
    "> 🤔 那么，TiDB 的社区机器人（`ti-chi-bot`）一共创建了多少个 Pull Request 呢？\n",
    "\n",
    "我们可以通过下面的 SQL 语句进行查询："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style type=\"text/css\">\n",
       "#T_9d8e8_row0_col0, #T_9d8e8_row0_col1 {\n",
       "  text-align: left;\n",
       "}\n",
       "</style>\n",
       "<table id=\"T_9d8e8_\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th class=\"blank level0\" >&nbsp;</th>\n",
       "      <th class=\"col_heading level0 col0\" >repo_name</th>\n",
       "      <th class=\"col_heading level0 col1\" >prs</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th id=\"T_9d8e8_level0_row0\" class=\"row_heading level0 row0\" >0</th>\n",
       "      <td id=\"T_9d8e8_row0_col0\" class=\"data row0 col0\" >pingcap/tidb</td>\n",
       "      <td id=\"T_9d8e8_row0_col1\" class=\"data row0 col1\" >138</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n"
      ],
      "text/plain": [
       "<pandas.io.formats.style.Styler at 0x7f8451bca518>"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query(\"\"\"\n",
    "SELECT repo_name, COUNT(1) AS prs\n",
    "FROM github_events\n",
    "WHERE\n",
    "    type = 'PullRequestEvent'\n",
    "    AND action = 'opened'\n",
    "    AND actor_login = 'ti-chi-bot'\n",
    "GROUP BY repo_name\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "通过 `EXPLAIN ANALYZE` 查看上面 SQL 语句的执行计划："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "vscode": {
     "languageId": "shellscript"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style type=\"text/css\">\n",
       "#T_dbb9f_row0_col0, #T_dbb9f_row0_col1, #T_dbb9f_row0_col2, #T_dbb9f_row0_col3, #T_dbb9f_row0_col4, #T_dbb9f_row1_col0, #T_dbb9f_row1_col1, #T_dbb9f_row1_col2, #T_dbb9f_row1_col3, #T_dbb9f_row1_col4, #T_dbb9f_row2_col0, #T_dbb9f_row2_col1, #T_dbb9f_row2_col2, #T_dbb9f_row2_col3, #T_dbb9f_row2_col4, #T_dbb9f_row3_col0, #T_dbb9f_row3_col1, #T_dbb9f_row3_col2, #T_dbb9f_row3_col3, #T_dbb9f_row3_col4, #T_dbb9f_row4_col0, #T_dbb9f_row4_col1, #T_dbb9f_row4_col2, #T_dbb9f_row4_col3, #T_dbb9f_row4_col4, #T_dbb9f_row5_col0, #T_dbb9f_row5_col1, #T_dbb9f_row5_col2, #T_dbb9f_row5_col3, #T_dbb9f_row5_col4, #T_dbb9f_row6_col0, #T_dbb9f_row6_col1, #T_dbb9f_row6_col2, #T_dbb9f_row6_col3, #T_dbb9f_row6_col4 {\n",
       "  text-align: left;\n",
       "}\n",
       "</style>\n",
       "<table id=\"T_dbb9f_\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th class=\"blank level0\" >&nbsp;</th>\n",
       "      <th class=\"col_heading level0 col0\" >id</th>\n",
       "      <th class=\"col_heading level0 col1\" >estRows</th>\n",
       "      <th class=\"col_heading level0 col2\" >task</th>\n",
       "      <th class=\"col_heading level0 col3\" >access object</th>\n",
       "      <th class=\"col_heading level0 col4\" >operator info</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th id=\"T_dbb9f_level0_row0\" class=\"row_heading level0 row0\" >0</th>\n",
       "      <td id=\"T_dbb9f_row0_col0\" class=\"data row0 col0\" >Projection_5</td>\n",
       "      <td id=\"T_dbb9f_row0_col1\" class=\"data row0 col1\" >13.76</td>\n",
       "      <td id=\"T_dbb9f_row0_col2\" class=\"data row0 col2\" >root</td>\n",
       "      <td id=\"T_dbb9f_row0_col3\" class=\"data row0 col3\" ></td>\n",
       "      <td id=\"T_dbb9f_row0_col4\" class=\"data row0 col4\" >ossinsight.github_events.repo_name, Column#34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_dbb9f_level0_row1\" class=\"row_heading level0 row1\" >1</th>\n",
       "      <td id=\"T_dbb9f_row1_col0\" class=\"data row1 col0\" >└─HashAgg_18</td>\n",
       "      <td id=\"T_dbb9f_row1_col1\" class=\"data row1 col1\" >13.76</td>\n",
       "      <td id=\"T_dbb9f_row1_col2\" class=\"data row1 col2\" >root</td>\n",
       "      <td id=\"T_dbb9f_row1_col3\" class=\"data row1 col3\" ></td>\n",
       "      <td id=\"T_dbb9f_row1_col4\" class=\"data row1 col4\" >group by:ossinsight.github_events.repo_name, funcs:count(Column#38)->Column#34, funcs:firstrow(ossinsight.github_events.repo_name)->ossinsight.github_events.repo_name</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_dbb9f_level0_row2\" class=\"row_heading level0 row2\" >2</th>\n",
       "      <td id=\"T_dbb9f_row2_col0\" class=\"data row2 col0\" >  └─IndexLookUp_19</td>\n",
       "      <td id=\"T_dbb9f_row2_col1\" class=\"data row2 col1\" >13.76</td>\n",
       "      <td id=\"T_dbb9f_row2_col2\" class=\"data row2 col2\" >root</td>\n",
       "      <td id=\"T_dbb9f_row2_col3\" class=\"data row2 col3\" >partition:pull_request_event</td>\n",
       "      <td id=\"T_dbb9f_row2_col4\" class=\"data row2 col4\" ></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_dbb9f_level0_row3\" class=\"row_heading level0 row3\" >3</th>\n",
       "      <td id=\"T_dbb9f_row3_col0\" class=\"data row3 col0\" >    ├─IndexRangeScan_15(Build)</td>\n",
       "      <td id=\"T_dbb9f_row3_col1\" class=\"data row3 col1\" >14569.55</td>\n",
       "      <td id=\"T_dbb9f_row3_col2\" class=\"data row3 col2\" >cop[tikv]</td>\n",
       "      <td id=\"T_dbb9f_row3_col3\" class=\"data row3 col3\" >table:github_events, index:index_ge_on_actor_login(actor_login)</td>\n",
       "      <td id=\"T_dbb9f_row3_col4\" class=\"data row3 col4\" >range:[\"ti-chi-bot\",\"ti-chi-bot\"], keep order:false</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_dbb9f_level0_row4\" class=\"row_heading level0 row4\" >4</th>\n",
       "      <td id=\"T_dbb9f_row4_col0\" class=\"data row4 col0\" >    └─HashAgg_7(Probe)</td>\n",
       "      <td id=\"T_dbb9f_row4_col1\" class=\"data row4 col1\" >13.76</td>\n",
       "      <td id=\"T_dbb9f_row4_col2\" class=\"data row4 col2\" >cop[tikv]</td>\n",
       "      <td id=\"T_dbb9f_row4_col3\" class=\"data row4 col3\" ></td>\n",
       "      <td id=\"T_dbb9f_row4_col4\" class=\"data row4 col4\" >group by:ossinsight.github_events.repo_name, funcs:count(1)->Column#38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_dbb9f_level0_row5\" class=\"row_heading level0 row5\" >5</th>\n",
       "      <td id=\"T_dbb9f_row5_col0\" class=\"data row5 col0\" >      └─Selection_17</td>\n",
       "      <td id=\"T_dbb9f_row5_col1\" class=\"data row5 col1\" >906.06</td>\n",
       "      <td id=\"T_dbb9f_row5_col2\" class=\"data row5 col2\" >cop[tikv]</td>\n",
       "      <td id=\"T_dbb9f_row5_col3\" class=\"data row5 col3\" ></td>\n",
       "      <td id=\"T_dbb9f_row5_col4\" class=\"data row5 col4\" >eq(ossinsight.github_events.action, \"opened\"), eq(ossinsight.github_events.type, \"PullRequestEvent\")</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_dbb9f_level0_row6\" class=\"row_heading level0 row6\" >6</th>\n",
       "      <td id=\"T_dbb9f_row6_col0\" class=\"data row6 col0\" >        └─TableRowIDScan_16</td>\n",
       "      <td id=\"T_dbb9f_row6_col1\" class=\"data row6 col1\" >14569.55</td>\n",
       "      <td id=\"T_dbb9f_row6_col2\" class=\"data row6 col2\" >cop[tikv]</td>\n",
       "      <td id=\"T_dbb9f_row6_col3\" class=\"data row6 col3\" >table:github_events</td>\n",
       "      <td id=\"T_dbb9f_row6_col4\" class=\"data row6 col4\" >keep order:false</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n"
      ],
      "text/plain": [
       "<pandas.io.formats.style.Styler at 0x7f842e5cd438>"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query(\"\"\"\n",
    "EXPLAIN SELECT repo_name, COUNT(1) AS prs\n",
    "FROM github_events\n",
    "WHERE\n",
    "    type = 'PullRequestEvent'\n",
    "    AND action = 'opened'\n",
    "    AND actor_login = 'ti-chi-bot'\n",
    "GROUP BY repo_name\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "通过执行计划，我们可以知道这个 SQL 会使用 TiKV 存储引擎来读取 `github_events` 表中的数据，并且使用了索引 `index_github_events_on_actor_login`。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Example 2: The history of the number of stars"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> 🤔 除了通过聚合函数来进行简单数据分析，TiDB 是否可以做更复杂的数据分析吗？\n",
    "\n",
    "比如我们想要知道某个指标的增长趋势，以代码仓库 `pingcap/tidb` 为例，能否通过 SQL 语句查询该仓库 Star 的增长趋势 (按月累计)，这样的 SQL 语句我们会怎么写?\n",
    "\n",
    "我们可以利用[窗口函数](https://docs.pingcap.com/zh/tidb/stable/window-functions)来实现这一个需求。窗口函数通常也被称为 “OLAP 函数”，又或者是 “开窗函数”。\n",
    "\n",
    "例如，在下面的 SQL 语句当中，我们通过 `OVER` 语句定义窗口的范围，TiDB 会将数据行按照 `created_at` 列进行排序，然后对这些数据行按顺序扫描，将当前行之前的所有行作为一个窗口。\n",
    "\n",
    "随着扫描的行数越多，窗口也会逐步扩大。对于每个窗口都会执行一次聚合函数 `COUNT(1)` 来统计窗口内的事件总数，从而可以得到一组随着时间累积增长的统计数据。\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {
    "vscode": {
     "languageId": "shellscript"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style type=\"text/css\">\n",
       "#T_080b2_row0_col0, #T_080b2_row0_col1, #T_080b2_row1_col0, #T_080b2_row1_col1, #T_080b2_row2_col0, #T_080b2_row2_col1, #T_080b2_row3_col0, #T_080b2_row3_col1, #T_080b2_row4_col0, #T_080b2_row4_col1, #T_080b2_row5_col0, #T_080b2_row5_col1, #T_080b2_row6_col0, #T_080b2_row6_col1, #T_080b2_row7_col0, #T_080b2_row7_col1, #T_080b2_row8_col0, #T_080b2_row8_col1, #T_080b2_row9_col0, #T_080b2_row9_col1, #T_080b2_row10_col0, #T_080b2_row10_col1, #T_080b2_row11_col0, #T_080b2_row11_col1, #T_080b2_row12_col0, #T_080b2_row12_col1, #T_080b2_row13_col0, #T_080b2_row13_col1, #T_080b2_row14_col0, #T_080b2_row14_col1, #T_080b2_row15_col0, #T_080b2_row15_col1, #T_080b2_row16_col0, #T_080b2_row16_col1, #T_080b2_row17_col0, #T_080b2_row17_col1, #T_080b2_row18_col0, #T_080b2_row18_col1, #T_080b2_row19_col0, #T_080b2_row19_col1 {\n",
       "  text-align: left;\n",
       "}\n",
       "</style>\n",
       "<table id=\"T_080b2_\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th class=\"blank level0\" >&nbsp;</th>\n",
       "      <th class=\"col_heading level0 col0\" >event_month</th>\n",
       "      <th class=\"col_heading level0 col1\" >stars</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th id=\"T_080b2_level0_row0\" class=\"row_heading level0 row0\" >0</th>\n",
       "      <td id=\"T_080b2_row0_col0\" class=\"data row0 col0\" >2022-12-01</td>\n",
       "      <td id=\"T_080b2_row0_col1\" class=\"data row0 col1\" >35734</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_080b2_level0_row1\" class=\"row_heading level0 row1\" >1</th>\n",
       "      <td id=\"T_080b2_row1_col0\" class=\"data row1 col0\" >2022-11-01</td>\n",
       "      <td id=\"T_080b2_row1_col1\" class=\"data row1 col1\" >35730</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_080b2_level0_row2\" class=\"row_heading level0 row2\" >2</th>\n",
       "      <td id=\"T_080b2_row2_col0\" class=\"data row2 col0\" >2022-10-01</td>\n",
       "      <td id=\"T_080b2_row2_col1\" class=\"data row2 col1\" >35506</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_080b2_level0_row3\" class=\"row_heading level0 row3\" >3</th>\n",
       "      <td id=\"T_080b2_row3_col0\" class=\"data row3 col0\" >2022-09-01</td>\n",
       "      <td id=\"T_080b2_row3_col1\" class=\"data row3 col1\" >35215</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_080b2_level0_row4\" class=\"row_heading level0 row4\" >4</th>\n",
       "      <td id=\"T_080b2_row4_col0\" class=\"data row4 col0\" >2022-08-01</td>\n",
       "      <td id=\"T_080b2_row4_col1\" class=\"data row4 col1\" >34899</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_080b2_level0_row5\" class=\"row_heading level0 row5\" >5</th>\n",
       "      <td id=\"T_080b2_row5_col0\" class=\"data row5 col0\" >2022-07-01</td>\n",
       "      <td id=\"T_080b2_row5_col1\" class=\"data row5 col1\" >34615</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_080b2_level0_row6\" class=\"row_heading level0 row6\" >6</th>\n",
       "      <td id=\"T_080b2_row6_col0\" class=\"data row6 col0\" >2022-06-01</td>\n",
       "      <td id=\"T_080b2_row6_col1\" class=\"data row6 col1\" >34324</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_080b2_level0_row7\" class=\"row_heading level0 row7\" >7</th>\n",
       "      <td id=\"T_080b2_row7_col0\" class=\"data row7 col0\" >2022-05-01</td>\n",
       "      <td id=\"T_080b2_row7_col1\" class=\"data row7 col1\" >34027</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_080b2_level0_row8\" class=\"row_heading level0 row8\" >8</th>\n",
       "      <td id=\"T_080b2_row8_col0\" class=\"data row8 col0\" >2022-04-01</td>\n",
       "      <td id=\"T_080b2_row8_col1\" class=\"data row8 col1\" >33697</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_080b2_level0_row9\" class=\"row_heading level0 row9\" >9</th>\n",
       "      <td id=\"T_080b2_row9_col0\" class=\"data row9 col0\" >2022-03-01</td>\n",
       "      <td id=\"T_080b2_row9_col1\" class=\"data row9 col1\" >33345</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_080b2_level0_row10\" class=\"row_heading level0 row10\" >10</th>\n",
       "      <td id=\"T_080b2_row10_col0\" class=\"data row10 col0\" >2022-02-01</td>\n",
       "      <td id=\"T_080b2_row10_col1\" class=\"data row10 col1\" >32975</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_080b2_level0_row11\" class=\"row_heading level0 row11\" >11</th>\n",
       "      <td id=\"T_080b2_row11_col0\" class=\"data row11 col0\" >2022-01-01</td>\n",
       "      <td id=\"T_080b2_row11_col1\" class=\"data row11 col1\" >32703</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_080b2_level0_row12\" class=\"row_heading level0 row12\" >12</th>\n",
       "      <td id=\"T_080b2_row12_col0\" class=\"data row12 col0\" >2021-12-01</td>\n",
       "      <td id=\"T_080b2_row12_col1\" class=\"data row12 col1\" >32360</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_080b2_level0_row13\" class=\"row_heading level0 row13\" >13</th>\n",
       "      <td id=\"T_080b2_row13_col0\" class=\"data row13 col0\" >2021-11-01</td>\n",
       "      <td id=\"T_080b2_row13_col1\" class=\"data row13 col1\" >31931</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_080b2_level0_row14\" class=\"row_heading level0 row14\" >14</th>\n",
       "      <td id=\"T_080b2_row14_col0\" class=\"data row14 col0\" >2021-10-01</td>\n",
       "      <td id=\"T_080b2_row14_col1\" class=\"data row14 col1\" >31586</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_080b2_level0_row15\" class=\"row_heading level0 row15\" >15</th>\n",
       "      <td id=\"T_080b2_row15_col0\" class=\"data row15 col0\" >2021-09-01</td>\n",
       "      <td id=\"T_080b2_row15_col1\" class=\"data row15 col1\" >31476</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_080b2_level0_row16\" class=\"row_heading level0 row16\" >16</th>\n",
       "      <td id=\"T_080b2_row16_col0\" class=\"data row16 col0\" >2021-08-01</td>\n",
       "      <td id=\"T_080b2_row16_col1\" class=\"data row16 col1\" >31158</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_080b2_level0_row17\" class=\"row_heading level0 row17\" >17</th>\n",
       "      <td id=\"T_080b2_row17_col0\" class=\"data row17 col0\" >2021-07-01</td>\n",
       "      <td id=\"T_080b2_row17_col1\" class=\"data row17 col1\" >30848</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_080b2_level0_row18\" class=\"row_heading level0 row18\" >18</th>\n",
       "      <td id=\"T_080b2_row18_col0\" class=\"data row18 col0\" >2021-06-01</td>\n",
       "      <td id=\"T_080b2_row18_col1\" class=\"data row18 col1\" >30475</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_080b2_level0_row19\" class=\"row_heading level0 row19\" >19</th>\n",
       "      <td id=\"T_080b2_row19_col0\" class=\"data row19 col0\" >2021-05-01</td>\n",
       "      <td id=\"T_080b2_row19_col1\" class=\"data row19 col1\" >30130</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n"
      ],
      "text/plain": [
       "<pandas.io.formats.style.Styler at 0x7f842e1643c8>"
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query(\"\"\"\n",
    "SELECT\n",
    "  event_month, stars\n",
    "FROM (\n",
    "  SELECT\n",
    "    DATE_FORMAT(created_at, '%Y-%m-01') as event_month,\n",
    "    COUNT(1) OVER (ORDER BY DATE_FORMAT(created_at, '%Y-%m-01')) AS stars,\n",
    "    ROW_NUMBER() OVER (PARTITION BY DATE_FORMAT(created_at, '%Y-%m-01')) AS row_num\n",
    "  FROM\n",
    "    github_events\n",
    "  WHERE\n",
    "    type = 'WatchEvent'\n",
    "    AND repo_name = 'pingcap/tidb'\n",
    "  ORDER BY 1\n",
    ") sub\n",
    "WHERE row_num = 1\n",
    "ORDER BY event_month DESC\n",
    "LIMIT 20\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "通过 `EXPLAIN` 语句查看其执行计划："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {
    "vscode": {
     "languageId": "shellscript"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style type=\"text/css\">\n",
       "#T_0326c_row0_col0, #T_0326c_row0_col1, #T_0326c_row0_col2, #T_0326c_row0_col3, #T_0326c_row0_col4, #T_0326c_row1_col0, #T_0326c_row1_col1, #T_0326c_row1_col2, #T_0326c_row1_col3, #T_0326c_row1_col4, #T_0326c_row2_col0, #T_0326c_row2_col1, #T_0326c_row2_col2, #T_0326c_row2_col3, #T_0326c_row2_col4, #T_0326c_row3_col0, #T_0326c_row3_col1, #T_0326c_row3_col2, #T_0326c_row3_col3, #T_0326c_row3_col4, #T_0326c_row4_col0, #T_0326c_row4_col1, #T_0326c_row4_col2, #T_0326c_row4_col3, #T_0326c_row4_col4, #T_0326c_row5_col0, #T_0326c_row5_col1, #T_0326c_row5_col2, #T_0326c_row5_col3, #T_0326c_row5_col4, #T_0326c_row6_col0, #T_0326c_row6_col1, #T_0326c_row6_col2, #T_0326c_row6_col3, #T_0326c_row6_col4, #T_0326c_row7_col0, #T_0326c_row7_col1, #T_0326c_row7_col2, #T_0326c_row7_col3, #T_0326c_row7_col4, #T_0326c_row8_col0, #T_0326c_row8_col1, #T_0326c_row8_col2, #T_0326c_row8_col3, #T_0326c_row8_col4, #T_0326c_row9_col0, #T_0326c_row9_col1, #T_0326c_row9_col2, #T_0326c_row9_col3, #T_0326c_row9_col4, #T_0326c_row10_col0, #T_0326c_row10_col1, #T_0326c_row10_col2, #T_0326c_row10_col3, #T_0326c_row10_col4, #T_0326c_row11_col0, #T_0326c_row11_col1, #T_0326c_row11_col2, #T_0326c_row11_col3, #T_0326c_row11_col4, #T_0326c_row12_col0, #T_0326c_row12_col1, #T_0326c_row12_col2, #T_0326c_row12_col3, #T_0326c_row12_col4 {\n",
       "  text-align: left;\n",
       "}\n",
       "</style>\n",
       "<table id=\"T_0326c_\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th class=\"blank level0\" >&nbsp;</th>\n",
       "      <th class=\"col_heading level0 col0\" >id</th>\n",
       "      <th class=\"col_heading level0 col1\" >estRows</th>\n",
       "      <th class=\"col_heading level0 col2\" >task</th>\n",
       "      <th class=\"col_heading level0 col3\" >access object</th>\n",
       "      <th class=\"col_heading level0 col4\" >operator info</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th id=\"T_0326c_level0_row0\" class=\"row_heading level0 row0\" >0</th>\n",
       "      <td id=\"T_0326c_row0_col0\" class=\"data row0 col0\" >Projection_17</td>\n",
       "      <td id=\"T_0326c_row0_col1\" class=\"data row0 col1\" >20.00</td>\n",
       "      <td id=\"T_0326c_row0_col2\" class=\"data row0 col2\" >root</td>\n",
       "      <td id=\"T_0326c_row0_col3\" class=\"data row0 col3\" ></td>\n",
       "      <td id=\"T_0326c_row0_col4\" class=\"data row0 col4\" >Column#34, Column#40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_0326c_level0_row1\" class=\"row_heading level0 row1\" >1</th>\n",
       "      <td id=\"T_0326c_row1_col0\" class=\"data row1 col0\" >└─TopN_20</td>\n",
       "      <td id=\"T_0326c_row1_col1\" class=\"data row1 col1\" >20.00</td>\n",
       "      <td id=\"T_0326c_row1_col2\" class=\"data row1 col2\" >root</td>\n",
       "      <td id=\"T_0326c_row1_col3\" class=\"data row1 col3\" ></td>\n",
       "      <td id=\"T_0326c_row1_col4\" class=\"data row1 col4\" >Column#34:desc, offset:0, count:20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_0326c_level0_row2\" class=\"row_heading level0 row2\" >2</th>\n",
       "      <td id=\"T_0326c_row2_col0\" class=\"data row2 col0\" >  └─Selection_24</td>\n",
       "      <td id=\"T_0326c_row2_col1\" class=\"data row2 col1\" >17789.34</td>\n",
       "      <td id=\"T_0326c_row2_col2\" class=\"data row2 col2\" >root</td>\n",
       "      <td id=\"T_0326c_row2_col3\" class=\"data row2 col3\" ></td>\n",
       "      <td id=\"T_0326c_row2_col4\" class=\"data row2 col4\" >eq(Column#38, 1)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_0326c_level0_row3\" class=\"row_heading level0 row3\" >3</th>\n",
       "      <td id=\"T_0326c_row3_col0\" class=\"data row3 col0\" >    └─Window_25</td>\n",
       "      <td id=\"T_0326c_row3_col1\" class=\"data row3 col1\" >22236.68</td>\n",
       "      <td id=\"T_0326c_row3_col2\" class=\"data row3 col2\" >root</td>\n",
       "      <td id=\"T_0326c_row3_col3\" class=\"data row3 col3\" ></td>\n",
       "      <td id=\"T_0326c_row3_col4\" class=\"data row3 col4\" >count(1)->Column#40 over(order by Column#39 range between unbounded preceding and current row)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_0326c_level0_row4\" class=\"row_heading level0 row4\" >4</th>\n",
       "      <td id=\"T_0326c_row4_col0\" class=\"data row4 col0\" >      └─Sort_41</td>\n",
       "      <td id=\"T_0326c_row4_col1\" class=\"data row4 col1\" >22236.68</td>\n",
       "      <td id=\"T_0326c_row4_col2\" class=\"data row4 col2\" >root</td>\n",
       "      <td id=\"T_0326c_row4_col3\" class=\"data row4 col3\" ></td>\n",
       "      <td id=\"T_0326c_row4_col4\" class=\"data row4 col4\" >Column#39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_0326c_level0_row5\" class=\"row_heading level0 row5\" >5</th>\n",
       "      <td id=\"T_0326c_row5_col0\" class=\"data row5 col0\" >        └─Projection_26</td>\n",
       "      <td id=\"T_0326c_row5_col1\" class=\"data row5 col1\" >22236.68</td>\n",
       "      <td id=\"T_0326c_row5_col2\" class=\"data row5 col2\" >root</td>\n",
       "      <td id=\"T_0326c_row5_col3\" class=\"data row5 col3\" ></td>\n",
       "      <td id=\"T_0326c_row5_col4\" class=\"data row5 col4\" >Column#34, Column#38, date_format(ossinsight.github_events.created_at, %Y-%m-01)->Column#39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_0326c_level0_row6\" class=\"row_heading level0 row6\" >6</th>\n",
       "      <td id=\"T_0326c_row6_col0\" class=\"data row6 col0\" >          └─Shuffle_40</td>\n",
       "      <td id=\"T_0326c_row6_col1\" class=\"data row6 col1\" >22236.68</td>\n",
       "      <td id=\"T_0326c_row6_col2\" class=\"data row6 col2\" >root</td>\n",
       "      <td id=\"T_0326c_row6_col3\" class=\"data row6 col3\" ></td>\n",
       "      <td id=\"T_0326c_row6_col4\" class=\"data row6 col4\" >execution info: concurrency:5, data sources:[Projection_28]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_0326c_level0_row7\" class=\"row_heading level0 row7\" >7</th>\n",
       "      <td id=\"T_0326c_row7_col0\" class=\"data row7 col0\" >            └─Window_27</td>\n",
       "      <td id=\"T_0326c_row7_col1\" class=\"data row7 col1\" >22236.68</td>\n",
       "      <td id=\"T_0326c_row7_col2\" class=\"data row7 col2\" >root</td>\n",
       "      <td id=\"T_0326c_row7_col3\" class=\"data row7 col3\" ></td>\n",
       "      <td id=\"T_0326c_row7_col4\" class=\"data row7 col4\" >row_number()->Column#38 over(partition by Column#37 rows between current row and current row)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_0326c_level0_row8\" class=\"row_heading level0 row8\" >8</th>\n",
       "      <td id=\"T_0326c_row8_col0\" class=\"data row8 col0\" >              └─Sort_39</td>\n",
       "      <td id=\"T_0326c_row8_col1\" class=\"data row8 col1\" >22236.68</td>\n",
       "      <td id=\"T_0326c_row8_col2\" class=\"data row8 col2\" >root</td>\n",
       "      <td id=\"T_0326c_row8_col3\" class=\"data row8 col3\" ></td>\n",
       "      <td id=\"T_0326c_row8_col4\" class=\"data row8 col4\" >Column#37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_0326c_level0_row9\" class=\"row_heading level0 row9\" >9</th>\n",
       "      <td id=\"T_0326c_row9_col0\" class=\"data row9 col0\" >                └─Projection_28</td>\n",
       "      <td id=\"T_0326c_row9_col1\" class=\"data row9 col1\" >22236.68</td>\n",
       "      <td id=\"T_0326c_row9_col2\" class=\"data row9 col2\" >root</td>\n",
       "      <td id=\"T_0326c_row9_col3\" class=\"data row9 col3\" ></td>\n",
       "      <td id=\"T_0326c_row9_col4\" class=\"data row9 col4\" >date_format(ossinsight.github_events.created_at, %Y-%m-01)->Column#34, ossinsight.github_events.created_at, date_format(ossinsight.github_events.created_at, %Y-%m-01)->Column#37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_0326c_level0_row10\" class=\"row_heading level0 row10\" >10</th>\n",
       "      <td id=\"T_0326c_row10_col0\" class=\"data row10 col0\" >                  └─TableReader_34</td>\n",
       "      <td id=\"T_0326c_row10_col1\" class=\"data row10 col1\" >22236.68</td>\n",
       "      <td id=\"T_0326c_row10_col2\" class=\"data row10 col2\" >root</td>\n",
       "      <td id=\"T_0326c_row10_col3\" class=\"data row10 col3\" >partition:watch_event</td>\n",
       "      <td id=\"T_0326c_row10_col4\" class=\"data row10 col4\" >data:Selection_33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_0326c_level0_row11\" class=\"row_heading level0 row11\" >11</th>\n",
       "      <td id=\"T_0326c_row11_col0\" class=\"data row11 col0\" >                    └─Selection_33</td>\n",
       "      <td id=\"T_0326c_row11_col1\" class=\"data row11 col1\" >22236.68</td>\n",
       "      <td id=\"T_0326c_row11_col2\" class=\"data row11 col2\" >cop[tiflash]</td>\n",
       "      <td id=\"T_0326c_row11_col3\" class=\"data row11 col3\" ></td>\n",
       "      <td id=\"T_0326c_row11_col4\" class=\"data row11 col4\" >eq(ossinsight.github_events.repo_name, \"pingcap/tidb\"), eq(ossinsight.github_events.type, \"WatchEvent\")</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_0326c_level0_row12\" class=\"row_heading level0 row12\" >12</th>\n",
       "      <td id=\"T_0326c_row12_col0\" class=\"data row12 col0\" >                      └─TableFullScan_32</td>\n",
       "      <td id=\"T_0326c_row12_col1\" class=\"data row12 col1\" >1304850.00</td>\n",
       "      <td id=\"T_0326c_row12_col2\" class=\"data row12 col2\" >cop[tiflash]</td>\n",
       "      <td id=\"T_0326c_row12_col3\" class=\"data row12 col3\" >table:ge</td>\n",
       "      <td id=\"T_0326c_row12_col4\" class=\"data row12 col4\" >keep order:false</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n"
      ],
      "text/plain": [
       "<pandas.io.formats.style.Styler at 0x7f842e18bd30>"
      ]
     },
     "execution_count": 103,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query(\"\"\"\n",
    "EXPLAIN SELECT\n",
    "  event_month, stars\n",
    "FROM (\n",
    "  SELECT\n",
    "    DATE_FORMAT(created_at, '%Y-%m-01') as event_month,\n",
    "    COUNT(1) OVER (ORDER BY DATE_FORMAT(created_at, '%Y-%m-01')) AS stars,\n",
    "    ROW_NUMBER() OVER (PARTITION BY DATE_FORMAT(created_at, '%Y-%m-01')) AS row_num\n",
    "  FROM\n",
    "    github_events ge\n",
    "  WHERE\n",
    "    type = 'WatchEvent'\n",
    "    AND repo_name = 'pingcap/tidb'\n",
    "  ORDER BY 1\n",
    ") sub\n",
    "WHERE row_num = 1\n",
    "ORDER BY event_month DESC\n",
    "LIMIT 20;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 创建 TiFlash 副本\n",
    "\n",
    "我们发现对于 OLAP 函数，TiDB 的优化器仍然会使用 TiKV 作为存储引擎进行数据查询。\n",
    "\n",
    "这是因为在默认情况下，TiDB 不会是为 `github_events` 表创建 TiFlash 副本的，你需要手动地进行创建，创建 TiFlash 副本的方法也十分简单，你只需要执行下面这条 SQL 语句，就可以为 `github_events` 表创建一个 TiFlash 副本："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'OK, 0 record(s) affected'"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "execute(\"\"\"\n",
    "ALTER TABLE ossinsight.github_events SET TIFLASH REPLICA 1;\n",
    "\"\"\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "执行完上面的 SQL 语句后，TiFlash 会在后台自动地完成副本的创建，你可以通过重复执行下面的 SQL 语句来查看副本的创建进度："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style type=\"text/css\">\n",
       "#T_0acc1_row0_col0, #T_0acc1_row0_col1, #T_0acc1_row0_col2, #T_0acc1_row0_col3, #T_0acc1_row0_col4, #T_0acc1_row0_col5, #T_0acc1_row0_col6, #T_0acc1_row0_col7 {\n",
       "  text-align: left;\n",
       "}\n",
       "</style>\n",
       "<table id=\"T_0acc1_\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th class=\"blank level0\" >&nbsp;</th>\n",
       "      <th class=\"col_heading level0 col0\" >TABLE_SCHEMA</th>\n",
       "      <th class=\"col_heading level0 col1\" >TABLE_NAME</th>\n",
       "      <th class=\"col_heading level0 col2\" >TABLE_ID</th>\n",
       "      <th class=\"col_heading level0 col3\" >REPLICA_COUNT</th>\n",
       "      <th class=\"col_heading level0 col4\" >LOCATION_LABELS</th>\n",
       "      <th class=\"col_heading level0 col5\" >AVAILABLE</th>\n",
       "      <th class=\"col_heading level0 col6\" >PROGRESS</th>\n",
       "      <th class=\"col_heading level0 col7\" >TABLE_MODE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th id=\"T_0acc1_level0_row0\" class=\"row_heading level0 row0\" >0</th>\n",
       "      <td id=\"T_0acc1_row0_col0\" class=\"data row0 col0\" >ossinsight</td>\n",
       "      <td id=\"T_0acc1_row0_col1\" class=\"data row0 col1\" >github_events</td>\n",
       "      <td id=\"T_0acc1_row0_col2\" class=\"data row0 col2\" >128</td>\n",
       "      <td id=\"T_0acc1_row0_col3\" class=\"data row0 col3\" >1</td>\n",
       "      <td id=\"T_0acc1_row0_col4\" class=\"data row0 col4\" ></td>\n",
       "      <td id=\"T_0acc1_row0_col5\" class=\"data row0 col5\" >1</td>\n",
       "      <td id=\"T_0acc1_row0_col6\" class=\"data row0 col6\" >1.000000</td>\n",
       "      <td id=\"T_0acc1_row0_col7\" class=\"data row0 col7\" >NORMAL</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n"
      ],
      "text/plain": [
       "<pandas.io.formats.style.Styler at 0x7f842e314ac8>"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query(\"\"\"\n",
    "SELECT * FROM information_schema.tiflash_replica WHERE TABLE_SCHEMA = 'ossinsight' and TABLE_NAME = 'github_events';\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "当 AVAILABLE 状态为 1, PROCESS 进度为 1.0（即 100%）时，表示该表的 TiFlash 副本已经创建完成。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 再次执行\n",
    "\n",
    "再次执行上面的分析查询语句："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {
    "vscode": {
     "languageId": "shellscript"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style type=\"text/css\">\n",
       "#T_22b93_row0_col0, #T_22b93_row0_col1, #T_22b93_row0_col2, #T_22b93_row0_col3, #T_22b93_row0_col4, #T_22b93_row1_col0, #T_22b93_row1_col1, #T_22b93_row1_col2, #T_22b93_row1_col3, #T_22b93_row1_col4, #T_22b93_row2_col0, #T_22b93_row2_col1, #T_22b93_row2_col2, #T_22b93_row2_col3, #T_22b93_row2_col4, #T_22b93_row3_col0, #T_22b93_row3_col1, #T_22b93_row3_col2, #T_22b93_row3_col3, #T_22b93_row3_col4, #T_22b93_row4_col0, #T_22b93_row4_col1, #T_22b93_row4_col2, #T_22b93_row4_col3, #T_22b93_row4_col4, #T_22b93_row5_col0, #T_22b93_row5_col1, #T_22b93_row5_col2, #T_22b93_row5_col3, #T_22b93_row5_col4, #T_22b93_row6_col0, #T_22b93_row6_col1, #T_22b93_row6_col2, #T_22b93_row6_col3, #T_22b93_row6_col4, #T_22b93_row7_col0, #T_22b93_row7_col1, #T_22b93_row7_col2, #T_22b93_row7_col3, #T_22b93_row7_col4, #T_22b93_row8_col0, #T_22b93_row8_col1, #T_22b93_row8_col2, #T_22b93_row8_col3, #T_22b93_row8_col4, #T_22b93_row9_col0, #T_22b93_row9_col1, #T_22b93_row9_col2, #T_22b93_row9_col3, #T_22b93_row9_col4, #T_22b93_row10_col0, #T_22b93_row10_col1, #T_22b93_row10_col2, #T_22b93_row10_col3, #T_22b93_row10_col4, #T_22b93_row11_col0, #T_22b93_row11_col1, #T_22b93_row11_col2, #T_22b93_row11_col3, #T_22b93_row11_col4, #T_22b93_row12_col0, #T_22b93_row12_col1, #T_22b93_row12_col2, #T_22b93_row12_col3, #T_22b93_row12_col4, #T_22b93_row13_col0, #T_22b93_row13_col1, #T_22b93_row13_col2, #T_22b93_row13_col3, #T_22b93_row13_col4 {\n",
       "  text-align: left;\n",
       "}\n",
       "</style>\n",
       "<table id=\"T_22b93_\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th class=\"blank level0\" >&nbsp;</th>\n",
       "      <th class=\"col_heading level0 col0\" >id</th>\n",
       "      <th class=\"col_heading level0 col1\" >estRows</th>\n",
       "      <th class=\"col_heading level0 col2\" >task</th>\n",
       "      <th class=\"col_heading level0 col3\" >access object</th>\n",
       "      <th class=\"col_heading level0 col4\" >operator info</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th id=\"T_22b93_level0_row0\" class=\"row_heading level0 row0\" >0</th>\n",
       "      <td id=\"T_22b93_row0_col0\" class=\"data row0 col0\" >Sort_15</td>\n",
       "      <td id=\"T_22b93_row0_col1\" class=\"data row0 col1\" >19020.37</td>\n",
       "      <td id=\"T_22b93_row0_col2\" class=\"data row0 col2\" >root</td>\n",
       "      <td id=\"T_22b93_row0_col3\" class=\"data row0 col3\" ></td>\n",
       "      <td id=\"T_22b93_row0_col4\" class=\"data row0 col4\" >Column#34:desc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_22b93_level0_row1\" class=\"row_heading level0 row1\" >1</th>\n",
       "      <td id=\"T_22b93_row1_col0\" class=\"data row1 col0\" >└─Projection_17</td>\n",
       "      <td id=\"T_22b93_row1_col1\" class=\"data row1 col1\" >19020.37</td>\n",
       "      <td id=\"T_22b93_row1_col2\" class=\"data row1 col2\" >root</td>\n",
       "      <td id=\"T_22b93_row1_col3\" class=\"data row1 col3\" ></td>\n",
       "      <td id=\"T_22b93_row1_col4\" class=\"data row1 col4\" >Column#34, Column#40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_22b93_level0_row2\" class=\"row_heading level0 row2\" >2</th>\n",
       "      <td id=\"T_22b93_row2_col0\" class=\"data row2 col0\" >  └─Sort_18</td>\n",
       "      <td id=\"T_22b93_row2_col1\" class=\"data row2 col1\" >19020.37</td>\n",
       "      <td id=\"T_22b93_row2_col2\" class=\"data row2 col2\" >root</td>\n",
       "      <td id=\"T_22b93_row2_col3\" class=\"data row2 col3\" ></td>\n",
       "      <td id=\"T_22b93_row2_col4\" class=\"data row2 col4\" >Column#34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_22b93_level0_row3\" class=\"row_heading level0 row3\" >3</th>\n",
       "      <td id=\"T_22b93_row3_col0\" class=\"data row3 col0\" >    └─Selection_20</td>\n",
       "      <td id=\"T_22b93_row3_col1\" class=\"data row3 col1\" >19020.37</td>\n",
       "      <td id=\"T_22b93_row3_col2\" class=\"data row3 col2\" >root</td>\n",
       "      <td id=\"T_22b93_row3_col3\" class=\"data row3 col3\" ></td>\n",
       "      <td id=\"T_22b93_row3_col4\" class=\"data row3 col4\" >eq(Column#38, 1)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_22b93_level0_row4\" class=\"row_heading level0 row4\" >4</th>\n",
       "      <td id=\"T_22b93_row4_col0\" class=\"data row4 col0\" >      └─Window_21</td>\n",
       "      <td id=\"T_22b93_row4_col1\" class=\"data row4 col1\" >23775.46</td>\n",
       "      <td id=\"T_22b93_row4_col2\" class=\"data row4 col2\" >root</td>\n",
       "      <td id=\"T_22b93_row4_col3\" class=\"data row4 col3\" ></td>\n",
       "      <td id=\"T_22b93_row4_col4\" class=\"data row4 col4\" >count(1)->Column#40 over(order by Column#39 range between unbounded preceding and current row)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_22b93_level0_row5\" class=\"row_heading level0 row5\" >5</th>\n",
       "      <td id=\"T_22b93_row5_col0\" class=\"data row5 col0\" >        └─Sort_37</td>\n",
       "      <td id=\"T_22b93_row5_col1\" class=\"data row5 col1\" >23775.46</td>\n",
       "      <td id=\"T_22b93_row5_col2\" class=\"data row5 col2\" >root</td>\n",
       "      <td id=\"T_22b93_row5_col3\" class=\"data row5 col3\" ></td>\n",
       "      <td id=\"T_22b93_row5_col4\" class=\"data row5 col4\" >Column#39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_22b93_level0_row6\" class=\"row_heading level0 row6\" >6</th>\n",
       "      <td id=\"T_22b93_row6_col0\" class=\"data row6 col0\" >          └─Projection_22</td>\n",
       "      <td id=\"T_22b93_row6_col1\" class=\"data row6 col1\" >23775.46</td>\n",
       "      <td id=\"T_22b93_row6_col2\" class=\"data row6 col2\" >root</td>\n",
       "      <td id=\"T_22b93_row6_col3\" class=\"data row6 col3\" ></td>\n",
       "      <td id=\"T_22b93_row6_col4\" class=\"data row6 col4\" >Column#34, Column#38, date_format(ossinsight.github_events.created_at, %Y-%m-01)->Column#39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_22b93_level0_row7\" class=\"row_heading level0 row7\" >7</th>\n",
       "      <td id=\"T_22b93_row7_col0\" class=\"data row7 col0\" >            └─Shuffle_36</td>\n",
       "      <td id=\"T_22b93_row7_col1\" class=\"data row7 col1\" >23775.46</td>\n",
       "      <td id=\"T_22b93_row7_col2\" class=\"data row7 col2\" >root</td>\n",
       "      <td id=\"T_22b93_row7_col3\" class=\"data row7 col3\" ></td>\n",
       "      <td id=\"T_22b93_row7_col4\" class=\"data row7 col4\" >execution info: concurrency:5, data sources:[Projection_24]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_22b93_level0_row8\" class=\"row_heading level0 row8\" >8</th>\n",
       "      <td id=\"T_22b93_row8_col0\" class=\"data row8 col0\" >              └─Window_23</td>\n",
       "      <td id=\"T_22b93_row8_col1\" class=\"data row8 col1\" >23775.46</td>\n",
       "      <td id=\"T_22b93_row8_col2\" class=\"data row8 col2\" >root</td>\n",
       "      <td id=\"T_22b93_row8_col3\" class=\"data row8 col3\" ></td>\n",
       "      <td id=\"T_22b93_row8_col4\" class=\"data row8 col4\" >row_number()->Column#38 over(partition by Column#37 rows between current row and current row)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_22b93_level0_row9\" class=\"row_heading level0 row9\" >9</th>\n",
       "      <td id=\"T_22b93_row9_col0\" class=\"data row9 col0\" >                └─Sort_35</td>\n",
       "      <td id=\"T_22b93_row9_col1\" class=\"data row9 col1\" >23775.46</td>\n",
       "      <td id=\"T_22b93_row9_col2\" class=\"data row9 col2\" >root</td>\n",
       "      <td id=\"T_22b93_row9_col3\" class=\"data row9 col3\" ></td>\n",
       "      <td id=\"T_22b93_row9_col4\" class=\"data row9 col4\" >Column#37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_22b93_level0_row10\" class=\"row_heading level0 row10\" >10</th>\n",
       "      <td id=\"T_22b93_row10_col0\" class=\"data row10 col0\" >                  └─Projection_24</td>\n",
       "      <td id=\"T_22b93_row10_col1\" class=\"data row10 col1\" >23775.46</td>\n",
       "      <td id=\"T_22b93_row10_col2\" class=\"data row10 col2\" >root</td>\n",
       "      <td id=\"T_22b93_row10_col3\" class=\"data row10 col3\" ></td>\n",
       "      <td id=\"T_22b93_row10_col4\" class=\"data row10 col4\" >date_format(ossinsight.github_events.created_at, %Y-%m-01)->Column#34, ossinsight.github_events.created_at, date_format(ossinsight.github_events.created_at, %Y-%m-01)->Column#37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_22b93_level0_row11\" class=\"row_heading level0 row11\" >11</th>\n",
       "      <td id=\"T_22b93_row11_col0\" class=\"data row11 col0\" >                    └─TableReader_30</td>\n",
       "      <td id=\"T_22b93_row11_col1\" class=\"data row11 col1\" >23775.46</td>\n",
       "      <td id=\"T_22b93_row11_col2\" class=\"data row11 col2\" >root</td>\n",
       "      <td id=\"T_22b93_row11_col3\" class=\"data row11 col3\" >partition:watch_event</td>\n",
       "      <td id=\"T_22b93_row11_col4\" class=\"data row11 col4\" >data:Selection_29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_22b93_level0_row12\" class=\"row_heading level0 row12\" >12</th>\n",
       "      <td id=\"T_22b93_row12_col0\" class=\"data row12 col0\" >                      └─Selection_29</td>\n",
       "      <td id=\"T_22b93_row12_col1\" class=\"data row12 col1\" >23775.46</td>\n",
       "      <td id=\"T_22b93_row12_col2\" class=\"data row12 col2\" >cop[tiflash]</td>\n",
       "      <td id=\"T_22b93_row12_col3\" class=\"data row12 col3\" ></td>\n",
       "      <td id=\"T_22b93_row12_col4\" class=\"data row12 col4\" >eq(ossinsight.github_events.repo_name, \"pingcap/tidb\"), eq(ossinsight.github_events.type, \"WatchEvent\")</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_22b93_level0_row13\" class=\"row_heading level0 row13\" >13</th>\n",
       "      <td id=\"T_22b93_row13_col0\" class=\"data row13 col0\" >                        └─TableFullScan_28</td>\n",
       "      <td id=\"T_22b93_row13_col1\" class=\"data row13 col1\" >1192661.00</td>\n",
       "      <td id=\"T_22b93_row13_col2\" class=\"data row13 col2\" >cop[tiflash]</td>\n",
       "      <td id=\"T_22b93_row13_col3\" class=\"data row13 col3\" >table:ge</td>\n",
       "      <td id=\"T_22b93_row13_col4\" class=\"data row13 col4\" >keep order:false</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n"
      ],
      "text/plain": [
       "<pandas.io.formats.style.Styler at 0x7f842e31ff98>"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query(\"\"\"\n",
    "EXPLAIN SELECT\n",
    "  event_month, stars\n",
    "FROM (\n",
    "  SELECT\n",
    "    DATE_FORMAT(created_at, '%Y-%m-01') as event_month,\n",
    "    COUNT(1) OVER (ORDER BY DATE_FORMAT(created_at, '%Y-%m-01')) AS stars,\n",
    "    ROW_NUMBER() OVER (PARTITION BY DATE_FORMAT(created_at, '%Y-%m-01')) AS row_num\n",
    "  FROM\n",
    "    github_events ge\n",
    "  WHERE\n",
    "    type = 'WatchEvent'\n",
    "    AND repo_name = 'pingcap/tidb'\n",
    "  ORDER BY 1\n",
    ") sub\n",
    "WHERE row_num = 1\n",
    "ORDER BY event_month DESC;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "我们会发现此时的执行计划当中已经出现了 `cop[tiflash]` 的字样，说明此时的查询已经在使用 TiFlash 存储引擎来读取数据。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Example 3: Trends in the growth of the number of robots"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> 🤔 看上去上面的查询的速度并没有多大的差异？\n",
    "\n",
    "我们来看一下更加复杂的例子，在前面的查询当中，我们只是在查询一个仓库的数据，TiDB 也许还能够借助索引找到一条更高效的基于 TiKV 的执行计划。\n",
    "\n",
    "但是有的时候，我们的数据分析可能并没有这么简单，可能需要同时分析 N 个代码仓库，甚至所有代码仓库的数据。又或者是我们希望能够进行大批量的数据报表统计，这个时候需要扫描更为大量的数据，这时候优化器即便仍然选择了索引查询的方式，查询的效率可能仍然不会很高。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "首先，我们应用我们在背景知识章节当中学习到的技巧，通过 `/*+ READ_FROM_STORAGE(TIKV[ge]) */` Hints 来告诉优化器在查询 `github_events` 表时强制使用 TiKV 存储引擎："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {
    "vscode": {
     "languageId": "shellscript"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style type=\"text/css\">\n",
       "#T_22a94_row0_col0, #T_22a94_row0_col1, #T_22a94_row1_col0, #T_22a94_row1_col1, #T_22a94_row2_col0, #T_22a94_row2_col1, #T_22a94_row3_col0, #T_22a94_row3_col1, #T_22a94_row4_col0, #T_22a94_row4_col1, #T_22a94_row5_col0, #T_22a94_row5_col1, #T_22a94_row6_col0, #T_22a94_row6_col1, #T_22a94_row7_col0, #T_22a94_row7_col1, #T_22a94_row8_col0, #T_22a94_row8_col1, #T_22a94_row9_col0, #T_22a94_row9_col1, #T_22a94_row10_col0, #T_22a94_row10_col1, #T_22a94_row11_col0, #T_22a94_row11_col1 {\n",
       "  text-align: left;\n",
       "}\n",
       "</style>\n",
       "<table id=\"T_22a94_\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th class=\"blank level0\" >&nbsp;</th>\n",
       "      <th class=\"col_heading level0 col0\" >bots_total</th>\n",
       "      <th class=\"col_heading level0 col1\" >event_year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th id=\"T_22a94_level0_row0\" class=\"row_heading level0 row0\" >0</th>\n",
       "      <td id=\"T_22a94_row0_col0\" class=\"data row0 col0\" >160</td>\n",
       "      <td id=\"T_22a94_row0_col1\" class=\"data row0 col1\" >2011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_22a94_level0_row1\" class=\"row_heading level0 row1\" >1</th>\n",
       "      <td id=\"T_22a94_row1_col0\" class=\"data row1 col0\" >237</td>\n",
       "      <td id=\"T_22a94_row1_col1\" class=\"data row1 col1\" >2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_22a94_level0_row2\" class=\"row_heading level0 row2\" >2</th>\n",
       "      <td id=\"T_22a94_row2_col0\" class=\"data row2 col0\" >615</td>\n",
       "      <td id=\"T_22a94_row2_col1\" class=\"data row2 col1\" >2013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_22a94_level0_row3\" class=\"row_heading level0 row3\" >3</th>\n",
       "      <td id=\"T_22a94_row3_col0\" class=\"data row3 col0\" >1364</td>\n",
       "      <td id=\"T_22a94_row3_col1\" class=\"data row3 col1\" >2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_22a94_level0_row4\" class=\"row_heading level0 row4\" >4</th>\n",
       "      <td id=\"T_22a94_row4_col0\" class=\"data row4 col0\" >2490</td>\n",
       "      <td id=\"T_22a94_row4_col1\" class=\"data row4 col1\" >2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_22a94_level0_row5\" class=\"row_heading level0 row5\" >5</th>\n",
       "      <td id=\"T_22a94_row5_col0\" class=\"data row5 col0\" >3546</td>\n",
       "      <td id=\"T_22a94_row5_col1\" class=\"data row5 col1\" >2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_22a94_level0_row6\" class=\"row_heading level0 row6\" >6</th>\n",
       "      <td id=\"T_22a94_row6_col0\" class=\"data row6 col0\" >4982</td>\n",
       "      <td id=\"T_22a94_row6_col1\" class=\"data row6 col1\" >2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_22a94_level0_row7\" class=\"row_heading level0 row7\" >7</th>\n",
       "      <td id=\"T_22a94_row7_col0\" class=\"data row7 col0\" >6361</td>\n",
       "      <td id=\"T_22a94_row7_col1\" class=\"data row7 col1\" >2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_22a94_level0_row8\" class=\"row_heading level0 row8\" >8</th>\n",
       "      <td id=\"T_22a94_row8_col0\" class=\"data row8 col0\" >8526</td>\n",
       "      <td id=\"T_22a94_row8_col1\" class=\"data row8 col1\" >2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_22a94_level0_row9\" class=\"row_heading level0 row9\" >9</th>\n",
       "      <td id=\"T_22a94_row9_col0\" class=\"data row9 col0\" >10156</td>\n",
       "      <td id=\"T_22a94_row9_col1\" class=\"data row9 col1\" >2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_22a94_level0_row10\" class=\"row_heading level0 row10\" >10</th>\n",
       "      <td id=\"T_22a94_row10_col0\" class=\"data row10 col0\" >11954</td>\n",
       "      <td id=\"T_22a94_row10_col1\" class=\"data row10 col1\" >2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_22a94_level0_row11\" class=\"row_heading level0 row11\" >11</th>\n",
       "      <td id=\"T_22a94_row11_col0\" class=\"data row11 col0\" >15483</td>\n",
       "      <td id=\"T_22a94_row11_col1\" class=\"data row11 col1\" >2022</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n"
      ],
      "text/plain": [
       "<pandas.io.formats.style.Styler at 0x7f842e288668>"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query(\"\"\"\n",
    "WITH bots_with_first_seen AS (\n",
    "    SELECT\n",
    "        /*+ READ_FROM_STORAGE(TIKV[ge]) */\n",
    "        actor_login, MIN(YEAR(created_at)) AS first_seen_at\n",
    "    FROM github_events ge\n",
    "    WHERE\n",
    "        actor_login REGEXP '^(bot-.+|.+bot|.+\\\\[bot\\\\]|.+-bot-.+|robot-.+|.+-ci-.+|.+-ci|.+-testing|.+clabot.+|.+-gerrit|k8s-.+|.+-machine|.+-automation|github-.+|.+-github|.+-service|.+-builds|codecov-.+|.+teamcity.+|jenkins-.+|.+-jira-.+)$'\n",
    "    GROUP BY actor_login\n",
    "    ORDER BY first_seen_at\n",
    "),  acc AS (\n",
    "    SELECT\n",
    "        COUNT(actor_login) OVER (ORDER BY first_seen_at) AS cnt,\n",
    "        first_seen_at AS event_year\n",
    "    FROM\n",
    "        bots_with_first_seen AS bwfs\n",
    "    ORDER BY event_year\n",
    ")\n",
    "SELECT ANY_VALUE(cnt) AS bots_total, event_year\n",
    "FROM acc\n",
    "GROUP BY event_year\n",
    "ORDER BY event_year;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "然后我们通过 `/*+ READ_FROM_STORAGE(TIFLASH[ge]) */` Hints 来强制优化器使用 TiFlash 存储引擎来读取 `github_events` 表，再次执行："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {
    "vscode": {
     "languageId": "shellscript"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style type=\"text/css\">\n",
       "#T_f86f6_row0_col0, #T_f86f6_row0_col1, #T_f86f6_row1_col0, #T_f86f6_row1_col1, #T_f86f6_row2_col0, #T_f86f6_row2_col1, #T_f86f6_row3_col0, #T_f86f6_row3_col1, #T_f86f6_row4_col0, #T_f86f6_row4_col1, #T_f86f6_row5_col0, #T_f86f6_row5_col1, #T_f86f6_row6_col0, #T_f86f6_row6_col1, #T_f86f6_row7_col0, #T_f86f6_row7_col1, #T_f86f6_row8_col0, #T_f86f6_row8_col1, #T_f86f6_row9_col0, #T_f86f6_row9_col1, #T_f86f6_row10_col0, #T_f86f6_row10_col1, #T_f86f6_row11_col0, #T_f86f6_row11_col1 {\n",
       "  text-align: left;\n",
       "}\n",
       "</style>\n",
       "<table id=\"T_f86f6_\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th class=\"blank level0\" >&nbsp;</th>\n",
       "      <th class=\"col_heading level0 col0\" >bots_total</th>\n",
       "      <th class=\"col_heading level0 col1\" >event_year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th id=\"T_f86f6_level0_row0\" class=\"row_heading level0 row0\" >0</th>\n",
       "      <td id=\"T_f86f6_row0_col0\" class=\"data row0 col0\" >160</td>\n",
       "      <td id=\"T_f86f6_row0_col1\" class=\"data row0 col1\" >2011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_f86f6_level0_row1\" class=\"row_heading level0 row1\" >1</th>\n",
       "      <td id=\"T_f86f6_row1_col0\" class=\"data row1 col0\" >237</td>\n",
       "      <td id=\"T_f86f6_row1_col1\" class=\"data row1 col1\" >2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_f86f6_level0_row2\" class=\"row_heading level0 row2\" >2</th>\n",
       "      <td id=\"T_f86f6_row2_col0\" class=\"data row2 col0\" >615</td>\n",
       "      <td id=\"T_f86f6_row2_col1\" class=\"data row2 col1\" >2013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_f86f6_level0_row3\" class=\"row_heading level0 row3\" >3</th>\n",
       "      <td id=\"T_f86f6_row3_col0\" class=\"data row3 col0\" >1364</td>\n",
       "      <td id=\"T_f86f6_row3_col1\" class=\"data row3 col1\" >2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_f86f6_level0_row4\" class=\"row_heading level0 row4\" >4</th>\n",
       "      <td id=\"T_f86f6_row4_col0\" class=\"data row4 col0\" >2490</td>\n",
       "      <td id=\"T_f86f6_row4_col1\" class=\"data row4 col1\" >2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_f86f6_level0_row5\" class=\"row_heading level0 row5\" >5</th>\n",
       "      <td id=\"T_f86f6_row5_col0\" class=\"data row5 col0\" >3546</td>\n",
       "      <td id=\"T_f86f6_row5_col1\" class=\"data row5 col1\" >2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_f86f6_level0_row6\" class=\"row_heading level0 row6\" >6</th>\n",
       "      <td id=\"T_f86f6_row6_col0\" class=\"data row6 col0\" >4982</td>\n",
       "      <td id=\"T_f86f6_row6_col1\" class=\"data row6 col1\" >2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_f86f6_level0_row7\" class=\"row_heading level0 row7\" >7</th>\n",
       "      <td id=\"T_f86f6_row7_col0\" class=\"data row7 col0\" >6361</td>\n",
       "      <td id=\"T_f86f6_row7_col1\" class=\"data row7 col1\" >2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_f86f6_level0_row8\" class=\"row_heading level0 row8\" >8</th>\n",
       "      <td id=\"T_f86f6_row8_col0\" class=\"data row8 col0\" >8526</td>\n",
       "      <td id=\"T_f86f6_row8_col1\" class=\"data row8 col1\" >2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_f86f6_level0_row9\" class=\"row_heading level0 row9\" >9</th>\n",
       "      <td id=\"T_f86f6_row9_col0\" class=\"data row9 col0\" >10156</td>\n",
       "      <td id=\"T_f86f6_row9_col1\" class=\"data row9 col1\" >2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_f86f6_level0_row10\" class=\"row_heading level0 row10\" >10</th>\n",
       "      <td id=\"T_f86f6_row10_col0\" class=\"data row10 col0\" >11954</td>\n",
       "      <td id=\"T_f86f6_row10_col1\" class=\"data row10 col1\" >2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_f86f6_level0_row11\" class=\"row_heading level0 row11\" >11</th>\n",
       "      <td id=\"T_f86f6_row11_col0\" class=\"data row11 col0\" >15483</td>\n",
       "      <td id=\"T_f86f6_row11_col1\" class=\"data row11 col1\" >2022</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n"
      ],
      "text/plain": [
       "<pandas.io.formats.style.Styler at 0x7f842e239c88>"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query(\"\"\"\n",
    "WITH bots_with_first_seen AS (\n",
    "    SELECT\n",
    "        /*+ READ_FROM_STORAGE(TIFLASH[ge]) */\n",
    "        actor_login, MIN(YEAR(created_at)) AS first_seen_at\n",
    "    FROM github_events ge\n",
    "    WHERE\n",
    "        actor_login REGEXP '^(bot-.+|.+bot|.+\\\\[bot\\\\]|.+-bot-.+|robot-.+|.+-ci-.+|.+-ci|.+-testing|.+clabot.+|.+-gerrit|k8s-.+|.+-machine|.+-automation|github-.+|.+-github|.+-service|.+-builds|codecov-.+|.+teamcity.+|jenkins-.+|.+-jira-.+)$'\n",
    "    GROUP BY actor_login\n",
    "    ORDER BY first_seen_at\n",
    "),  acc AS (\n",
    "    SELECT\n",
    "        COUNT(actor_login) OVER (ORDER BY first_seen_at) AS cnt,\n",
    "        first_seen_at AS event_year\n",
    "    FROM\n",
    "        bots_with_first_seen AS bwfs\n",
    "    ORDER BY event_year\n",
    ")\n",
    "SELECT ANY_VALUE(cnt) AS bots_total, event_year\n",
    "FROM acc\n",
    "GROUP BY event_year\n",
    "ORDER BY event_year;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "在更大数据量的情况下，使用 TiFlash 查询相比 TiKV 查询速度上有了明显的提升。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Example 4: Top Contributor List"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "除了单表查询，更多的时候我们需要将多张表的数据结合在一起进行分析，这个时候我们就需要进行表连接查询。\n",
    "\n",
    "> 如果在一个查询当中需要从多个表中访问数据，TiDB 会怎么进行处理？"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style type=\"text/css\">\n",
       "#T_7fe89_row0_col0, #T_7fe89_row0_col1, #T_7fe89_row0_col2, #T_7fe89_row0_col3, #T_7fe89_row1_col0, #T_7fe89_row1_col1, #T_7fe89_row1_col2, #T_7fe89_row1_col3, #T_7fe89_row2_col0, #T_7fe89_row2_col1, #T_7fe89_row2_col2, #T_7fe89_row2_col3, #T_7fe89_row3_col0, #T_7fe89_row3_col1, #T_7fe89_row3_col2, #T_7fe89_row3_col3, #T_7fe89_row4_col0, #T_7fe89_row4_col1, #T_7fe89_row4_col2, #T_7fe89_row4_col3, #T_7fe89_row5_col0, #T_7fe89_row5_col1, #T_7fe89_row5_col2, #T_7fe89_row5_col3, #T_7fe89_row6_col0, #T_7fe89_row6_col1, #T_7fe89_row6_col2, #T_7fe89_row6_col3, #T_7fe89_row7_col0, #T_7fe89_row7_col1, #T_7fe89_row7_col2, #T_7fe89_row7_col3, #T_7fe89_row8_col0, #T_7fe89_row8_col1, #T_7fe89_row8_col2, #T_7fe89_row8_col3, #T_7fe89_row9_col0, #T_7fe89_row9_col1, #T_7fe89_row9_col2, #T_7fe89_row9_col3 {\n",
       "  text-align: left;\n",
       "}\n",
       "</style>\n",
       "<table id=\"T_7fe89_\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th class=\"blank level0\" >&nbsp;</th>\n",
       "      <th class=\"col_heading level0 col0\" >repo_id</th>\n",
       "      <th class=\"col_heading level0 col1\" >repo_name</th>\n",
       "      <th class=\"col_heading level0 col2\" >stars</th>\n",
       "      <th class=\"col_heading level0 col3\" >created_at</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th id=\"T_7fe89_level0_row0\" class=\"row_heading level0 row0\" >0</th>\n",
       "      <td id=\"T_7fe89_row0_col0\" class=\"data row0 col0\" >41986369</td>\n",
       "      <td id=\"T_7fe89_row0_col1\" class=\"data row0 col1\" >pingcap/tidb</td>\n",
       "      <td id=\"T_7fe89_row0_col2\" class=\"data row0 col2\" >32602</td>\n",
       "      <td id=\"T_7fe89_row0_col3\" class=\"data row0 col3\" >2015-09-06 04:01:52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_7fe89_level0_row1\" class=\"row_heading level0 row1\" >1</th>\n",
       "      <td id=\"T_7fe89_row1_col0\" class=\"data row1 col0\" >16563587</td>\n",
       "      <td id=\"T_7fe89_row1_col1\" class=\"data row1 col1\" >cockroachdb/cockroach</td>\n",
       "      <td id=\"T_7fe89_row1_col2\" class=\"data row1 col2\" >25883</td>\n",
       "      <td id=\"T_7fe89_row1_col3\" class=\"data row1 col3\" >2014-02-06 00:18:47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_7fe89_level0_row2\" class=\"row_heading level0 row2\" >2</th>\n",
       "      <td id=\"T_7fe89_row2_col0\" class=\"data row2 col0\" >60246359</td>\n",
       "      <td id=\"T_7fe89_row2_col1\" class=\"data row2 col1\" >ClickHouse/ClickHouse</td>\n",
       "      <td id=\"T_7fe89_row2_col2\" class=\"data row2 col2\" >25827</td>\n",
       "      <td id=\"T_7fe89_row2_col3\" class=\"data row2 col3\" >2016-06-02 08:28:18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_7fe89_level0_row3\" class=\"row_heading level0 row3\" >3</th>\n",
       "      <td id=\"T_7fe89_row3_col0\" class=\"data row3 col0\" >108110</td>\n",
       "      <td id=\"T_7fe89_row3_col1\" class=\"data row3 col1\" >mongodb/mongo</td>\n",
       "      <td id=\"T_7fe89_row3_col2\" class=\"data row3 col2\" >22653</td>\n",
       "      <td id=\"T_7fe89_row3_col3\" class=\"data row3 col3\" >2009-01-15 16:15:18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_7fe89_level0_row4\" class=\"row_heading level0 row4\" >4</th>\n",
       "      <td id=\"T_7fe89_row4_col0\" class=\"data row4 col0\" >196353673</td>\n",
       "      <td id=\"T_7fe89_row4_col1\" class=\"data row4 col1\" >taosdata/TDengine</td>\n",
       "      <td id=\"T_7fe89_row4_col2\" class=\"data row4 col2\" >19462</td>\n",
       "      <td id=\"T_7fe89_row4_col3\" class=\"data row4 col3\" >2019-07-11 08:33:48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_7fe89_level0_row5\" class=\"row_heading level0 row5\" >5</th>\n",
       "      <td id=\"T_7fe89_row5_col0\" class=\"data row5 col0\" >11008207</td>\n",
       "      <td id=\"T_7fe89_row5_col1\" class=\"data row5 col1\" >vitessio/vitess</td>\n",
       "      <td id=\"T_7fe89_row5_col2\" class=\"data row5 col2\" >14875</td>\n",
       "      <td id=\"T_7fe89_row5_col3\" class=\"data row5 col3\" >2013-06-27 21:20:28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_7fe89_level0_row6\" class=\"row_heading level0 row6\" >6</th>\n",
       "      <td id=\"T_7fe89_row6_col0\" class=\"data row6 col0\" >5349565</td>\n",
       "      <td id=\"T_7fe89_row6_col1\" class=\"data row6 col1\" >prestodb/presto</td>\n",
       "      <td id=\"T_7fe89_row6_col2\" class=\"data row6 col2\" >14086</td>\n",
       "      <td id=\"T_7fe89_row6_col3\" class=\"data row6 col3\" >2012-08-09 01:03:37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_7fe89_level0_row7\" class=\"row_heading level0 row7\" >7</th>\n",
       "      <td id=\"T_7fe89_row7_col0\" class=\"data row7 col0\" >927442</td>\n",
       "      <td id=\"T_7fe89_row7_col1\" class=\"data row7 col1\" >postgres/postgres</td>\n",
       "      <td id=\"T_7fe89_row7_col2\" class=\"data row7 col2\" >11082</td>\n",
       "      <td id=\"T_7fe89_row7_col3\" class=\"data row7 col3\" >2010-09-21 11:35:45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_7fe89_level0_row8\" class=\"row_heading level0 row8\" >8</th>\n",
       "      <td id=\"T_7fe89_row8_col0\" class=\"data row8 col0\" >44781140</td>\n",
       "      <td id=\"T_7fe89_row8_col1\" class=\"data row8 col1\" >greenplum-db/gpdb</td>\n",
       "      <td id=\"T_7fe89_row8_col2\" class=\"data row8 col2\" >5417</td>\n",
       "      <td id=\"T_7fe89_row8_col3\" class=\"data row8 col3\" >2015-10-23 00:25:17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_7fe89_level0_row9\" class=\"row_heading level0 row9\" >9</th>\n",
       "      <td id=\"T_7fe89_row9_col0\" class=\"data row9 col0\" >302827809</td>\n",
       "      <td id=\"T_7fe89_row9_col1\" class=\"data row9 col1\" >datafuselabs/databend</td>\n",
       "      <td id=\"T_7fe89_row9_col2\" class=\"data row9 col2\" >4678</td>\n",
       "      <td id=\"T_7fe89_row9_col3\" class=\"data row9 col3\" >2020-10-10 06:04:42</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n"
      ],
      "text/plain": [
       "<pandas.io.formats.style.Styler at 0x7f842e298b38>"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query(\"\"\"\n",
    "SELECT\n",
    "    repo_id, repo_name, stars, created_at\n",
    "FROM github_repos\n",
    "ORDER BY stars DESC\n",
    "LIMIT 10;\n",
    "\"\"\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "例如，可以通过下面的 SQL 语句在这些仓库当中，来自中国的最活跃的 Code Contributors 是哪些人？"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style type=\"text/css\">\n",
       "#T_e3ea7_row0_col0, #T_e3ea7_row0_col1, #T_e3ea7_row0_col2, #T_e3ea7_row0_col3, #T_e3ea7_row1_col0, #T_e3ea7_row1_col1, #T_e3ea7_row1_col2, #T_e3ea7_row1_col3, #T_e3ea7_row2_col0, #T_e3ea7_row2_col1, #T_e3ea7_row2_col2, #T_e3ea7_row2_col3, #T_e3ea7_row3_col0, #T_e3ea7_row3_col1, #T_e3ea7_row3_col2, #T_e3ea7_row3_col3, #T_e3ea7_row4_col0, #T_e3ea7_row4_col1, #T_e3ea7_row4_col2, #T_e3ea7_row4_col3, #T_e3ea7_row5_col0, #T_e3ea7_row5_col1, #T_e3ea7_row5_col2, #T_e3ea7_row5_col3, #T_e3ea7_row6_col0, #T_e3ea7_row6_col1, #T_e3ea7_row6_col2, #T_e3ea7_row6_col3, #T_e3ea7_row7_col0, #T_e3ea7_row7_col1, #T_e3ea7_row7_col2, #T_e3ea7_row7_col3, #T_e3ea7_row8_col0, #T_e3ea7_row8_col1, #T_e3ea7_row8_col2, #T_e3ea7_row8_col3, #T_e3ea7_row9_col0, #T_e3ea7_row9_col1, #T_e3ea7_row9_col2, #T_e3ea7_row9_col3, #T_e3ea7_row10_col0, #T_e3ea7_row10_col1, #T_e3ea7_row10_col2, #T_e3ea7_row10_col3, #T_e3ea7_row11_col0, #T_e3ea7_row11_col1, #T_e3ea7_row11_col2, #T_e3ea7_row11_col3, #T_e3ea7_row12_col0, #T_e3ea7_row12_col1, #T_e3ea7_row12_col2, #T_e3ea7_row12_col3, #T_e3ea7_row13_col0, #T_e3ea7_row13_col1, #T_e3ea7_row13_col2, #T_e3ea7_row13_col3, #T_e3ea7_row14_col0, #T_e3ea7_row14_col1, #T_e3ea7_row14_col2, #T_e3ea7_row14_col3, #T_e3ea7_row15_col0, #T_e3ea7_row15_col1, #T_e3ea7_row15_col2, #T_e3ea7_row15_col3, #T_e3ea7_row16_col0, #T_e3ea7_row16_col1, #T_e3ea7_row16_col2, #T_e3ea7_row16_col3, #T_e3ea7_row17_col0, #T_e3ea7_row17_col1, #T_e3ea7_row17_col2, #T_e3ea7_row17_col3, #T_e3ea7_row18_col0, #T_e3ea7_row18_col1, #T_e3ea7_row18_col2, #T_e3ea7_row18_col3, #T_e3ea7_row19_col0, #T_e3ea7_row19_col1, #T_e3ea7_row19_col2, #T_e3ea7_row19_col3 {\n",
       "  text-align: left;\n",
       "}\n",
       "</style>\n",
       "<table id=\"T_e3ea7_\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th class=\"blank level0\" >&nbsp;</th>\n",
       "      <th class=\"col_heading level0 col0\" >actor_id</th>\n",
       "      <th class=\"col_heading level0 col1\" >login</th>\n",
       "      <th class=\"col_heading level0 col2\" >prs</th>\n",
       "      <th class=\"col_heading level0 col3\" >repos</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th id=\"T_e3ea7_level0_row0\" class=\"row_heading level0 row0\" >0</th>\n",
       "      <td id=\"T_e3ea7_row0_col0\" class=\"data row0 col0\" >825729</td>\n",
       "      <td id=\"T_e3ea7_row0_col1\" class=\"data row0 col1\" >sangshuduo</td>\n",
       "      <td id=\"T_e3ea7_row0_col2\" class=\"data row0 col2\" >1945</td>\n",
       "      <td id=\"T_e3ea7_row0_col3\" class=\"data row0 col3\" >taosdata/TDengine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_e3ea7_level0_row1\" class=\"row_heading level0 row1\" >1</th>\n",
       "      <td id=\"T_e3ea7_row1_col0\" class=\"data row1 col0\" >28882257</td>\n",
       "      <td id=\"T_e3ea7_row1_col1\" class=\"data row1 col1\" >guanshengliang</td>\n",
       "      <td id=\"T_e3ea7_row1_col2\" class=\"data row1 col2\" >1511</td>\n",
       "      <td id=\"T_e3ea7_row1_col3\" class=\"data row1 col3\" >taosdata/TDengine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_e3ea7_level0_row2\" class=\"row_heading level0 row2\" >2</th>\n",
       "      <td id=\"T_e3ea7_row2_col0\" class=\"data row2 col0\" >8252296</td>\n",
       "      <td id=\"T_e3ea7_row2_col1\" class=\"data row2 col1\" >hjxilinx</td>\n",
       "      <td id=\"T_e3ea7_row2_col2\" class=\"data row2 col2\" >911</td>\n",
       "      <td id=\"T_e3ea7_row2_col3\" class=\"data row2 col3\" >taosdata/TDengine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_e3ea7_level0_row3\" class=\"row_heading level0 row3\" >3</th>\n",
       "      <td id=\"T_e3ea7_row3_col0\" class=\"data row3 col0\" >891222</td>\n",
       "      <td id=\"T_e3ea7_row3_col1\" class=\"data row3 col1\" >coocood</td>\n",
       "      <td id=\"T_e3ea7_row3_col2\" class=\"data row3 col2\" >768</td>\n",
       "      <td id=\"T_e3ea7_row3_col3\" class=\"data row3 col3\" >pingcap/tidb</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_e3ea7_level0_row4\" class=\"row_heading level0 row4\" >4</th>\n",
       "      <td id=\"T_e3ea7_row4_col0\" class=\"data row4 col0\" >4242506</td>\n",
       "      <td id=\"T_e3ea7_row4_col1\" class=\"data row4 col1\" >zimulala</td>\n",
       "      <td id=\"T_e3ea7_row4_col2\" class=\"data row4 col2\" >701</td>\n",
       "      <td id=\"T_e3ea7_row4_col3\" class=\"data row4 col3\" >pingcap/tidb</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_e3ea7_level0_row5\" class=\"row_heading level0 row5\" >5</th>\n",
       "      <td id=\"T_e3ea7_row5_col0\" class=\"data row5 col0\" >7499936</td>\n",
       "      <td id=\"T_e3ea7_row5_col1\" class=\"data row5 col1\" >qw4990</td>\n",
       "      <td id=\"T_e3ea7_row5_col2\" class=\"data row5 col2\" >563</td>\n",
       "      <td id=\"T_e3ea7_row5_col3\" class=\"data row5 col3\" >pingcap/tidb</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_e3ea7_level0_row6\" class=\"row_heading level0 row6\" >6</th>\n",
       "      <td id=\"T_e3ea7_row6_col0\" class=\"data row6 col0\" >5085485</td>\n",
       "      <td id=\"T_e3ea7_row6_col1\" class=\"data row6 col1\" >amosbird</td>\n",
       "      <td id=\"T_e3ea7_row6_col2\" class=\"data row6 col2\" >505</td>\n",
       "      <td id=\"T_e3ea7_row6_col3\" class=\"data row6 col3\" >ClickHouse/ClickHouse,yandex/ClickHouse,greenplum-db/gpdb,taosdata/TDengine,cockroachdb/cockroach</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_e3ea7_level0_row7\" class=\"row_heading level0 row7\" >7</th>\n",
       "      <td id=\"T_e3ea7_row7_col0\" class=\"data row7 col0\" >4352397</td>\n",
       "      <td id=\"T_e3ea7_row7_col1\" class=\"data row7 col1\" >jackysp</td>\n",
       "      <td id=\"T_e3ea7_row7_col2\" class=\"data row7 col2\" >491</td>\n",
       "      <td id=\"T_e3ea7_row7_col3\" class=\"data row7 col3\" >pingcap/tidb</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_e3ea7_level0_row8\" class=\"row_heading level0 row8\" >8</th>\n",
       "      <td id=\"T_e3ea7_row8_col0\" class=\"data row8 col0\" >3427324</td>\n",
       "      <td id=\"T_e3ea7_row8_col1\" class=\"data row8 col1\" >hawkingrei</td>\n",
       "      <td id=\"T_e3ea7_row8_col2\" class=\"data row8 col2\" >479</td>\n",
       "      <td id=\"T_e3ea7_row8_col3\" class=\"data row8 col3\" >pingcap/tidb,singularity-data/risingwave</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_e3ea7_level0_row9\" class=\"row_heading level0 row9\" >9</th>\n",
       "      <td id=\"T_e3ea7_row9_col0\" class=\"data row9 col0\" >52318143</td>\n",
       "      <td id=\"T_e3ea7_row9_col1\" class=\"data row9 col1\" >plum-lihui</td>\n",
       "      <td id=\"T_e3ea7_row9_col2\" class=\"data row9 col2\" >469</td>\n",
       "      <td id=\"T_e3ea7_row9_col3\" class=\"data row9 col3\" >taosdata/TDengine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_e3ea7_level0_row10\" class=\"row_heading level0 row10\" >10</th>\n",
       "      <td id=\"T_e3ea7_row10_col0\" class=\"data row10 col0\" >528332</td>\n",
       "      <td id=\"T_e3ea7_row10_col1\" class=\"data row10 col1\" >lysu</td>\n",
       "      <td id=\"T_e3ea7_row10_col2\" class=\"data row10 col2\" >463</td>\n",
       "      <td id=\"T_e3ea7_row10_col3\" class=\"data row10 col3\" >pingcap/tidb</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_e3ea7_level0_row11\" class=\"row_heading level0 row11\" >11</th>\n",
       "      <td id=\"T_e3ea7_row11_col0\" class=\"data row11 col0\" >5268763</td>\n",
       "      <td id=\"T_e3ea7_row11_col1\" class=\"data row11 col1\" >zz-jason</td>\n",
       "      <td id=\"T_e3ea7_row11_col2\" class=\"data row11 col2\" >454</td>\n",
       "      <td id=\"T_e3ea7_row11_col3\" class=\"data row11 col3\" >pingcap/tidb</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_e3ea7_level0_row12\" class=\"row_heading level0 row12\" >12</th>\n",
       "      <td id=\"T_e3ea7_row12_col0\" class=\"data row12 col0\" >44069</td>\n",
       "      <td id=\"T_e3ea7_row12_col1\" class=\"data row12 col1\" >drmingdrmer</td>\n",
       "      <td id=\"T_e3ea7_row12_col2\" class=\"data row12 col2\" >449</td>\n",
       "      <td id=\"T_e3ea7_row12_col3\" class=\"data row12 col3\" >datafuselabs/databend,datafuselabs/datafuse</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_e3ea7_level0_row13\" class=\"row_heading level0 row13\" >13</th>\n",
       "      <td id=\"T_e3ea7_row13_col0\" class=\"data row13 col0\" >3325189</td>\n",
       "      <td id=\"T_e3ea7_row13_col1\" class=\"data row13 col1\" >sundy-li</td>\n",
       "      <td id=\"T_e3ea7_row13_col2\" class=\"data row13 col2\" >432</td>\n",
       "      <td id=\"T_e3ea7_row13_col3\" class=\"data row13 col3\" >datafusedev/fuse-query,datafuselabs/databend,datafuselabs/datafuse,ClickHouse/ClickHouse,yandex/ClickHouse,datafuselabs/fuse-query,pingcap/tidb</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_e3ea7_level0_row14\" class=\"row_heading level0 row14\" >14</th>\n",
       "      <td id=\"T_e3ea7_row14_col0\" class=\"data row14 col0\" >7846227</td>\n",
       "      <td id=\"T_e3ea7_row14_col1\" class=\"data row14 col1\" >winoros</td>\n",
       "      <td id=\"T_e3ea7_row14_col2\" class=\"data row14 col2\" >402</td>\n",
       "      <td id=\"T_e3ea7_row14_col3\" class=\"data row14 col3\" >pingcap/tidb</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_e3ea7_level0_row15\" class=\"row_heading level0 row15\" >15</th>\n",
       "      <td id=\"T_e3ea7_row15_col0\" class=\"data row15 col0\" >17380469</td>\n",
       "      <td id=\"T_e3ea7_row15_col1\" class=\"data row15 col1\" >wjhuang2016</td>\n",
       "      <td id=\"T_e3ea7_row15_col2\" class=\"data row15 col2\" >377</td>\n",
       "      <td id=\"T_e3ea7_row15_col3\" class=\"data row15 col3\" >pingcap/tidb</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_e3ea7_level0_row16\" class=\"row_heading level0 row16\" >16</th>\n",
       "      <td id=\"T_e3ea7_row16_col0\" class=\"data row16 col0\" >63628712</td>\n",
       "      <td id=\"T_e3ea7_row16_col1\" class=\"data row16 col1\" >liuyq-617</td>\n",
       "      <td id=\"T_e3ea7_row16_col2\" class=\"data row16 col2\" >366</td>\n",
       "      <td id=\"T_e3ea7_row16_col3\" class=\"data row16 col3\" >taosdata/TDengine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_e3ea7_level0_row17\" class=\"row_heading level0 row17\" >17</th>\n",
       "      <td id=\"T_e3ea7_row17_col0\" class=\"data row17 col0\" >12077877</td>\n",
       "      <td id=\"T_e3ea7_row17_col1\" class=\"data row17 col1\" >disksing</td>\n",
       "      <td id=\"T_e3ea7_row17_col2\" class=\"data row17 col2\" >357</td>\n",
       "      <td id=\"T_e3ea7_row17_col3\" class=\"data row17 col3\" >pingcap/tidb</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_e3ea7_level0_row18\" class=\"row_heading level0 row18\" >18</th>\n",
       "      <td id=\"T_e3ea7_row18_col0\" class=\"data row18 col0\" >27468951</td>\n",
       "      <td id=\"T_e3ea7_row18_col1\" class=\"data row18 col1\" >castermode</td>\n",
       "      <td id=\"T_e3ea7_row18_col2\" class=\"data row18 col2\" >356</td>\n",
       "      <td id=\"T_e3ea7_row18_col3\" class=\"data row18 col3\" >taosdata/TDengine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_e3ea7_level0_row19\" class=\"row_heading level0 row19\" >19</th>\n",
       "      <td id=\"T_e3ea7_row19_col0\" class=\"data row19 col0\" >24713065</td>\n",
       "      <td id=\"T_e3ea7_row19_col1\" class=\"data row19 col1\" >tangenta</td>\n",
       "      <td id=\"T_e3ea7_row19_col2\" class=\"data row19 col2\" >351</td>\n",
       "      <td id=\"T_e3ea7_row19_col3\" class=\"data row19 col3\" >pingcap/tidb</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n"
      ],
      "text/plain": [
       "<pandas.io.formats.style.Styler at 0x7f842e1a4630>"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query(\"\"\"\n",
    "SELECT ge.actor_id, gu.login, COUNT(1) AS prs, GROUP_CONCAT(DISTINCT ge.repo_name) AS repos\n",
    "FROM github_events ge\n",
    "JOIN github_users gu ON ge.actor_id = gu.id\n",
    "WHERE\n",
    "    ge.type = 'PullRequestEvent'\n",
    "    AND ge.action = 'opened'\n",
    "    AND gu.country_code = 'CN'\n",
    "GROUP BY ge.actor_id\n",
    "ORDER BY prs DESC\n",
    "LIMIT 20\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "查看其执行计划："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style type=\"text/css\">\n",
       "#T_bc001_row0_col0, #T_bc001_row0_col1, #T_bc001_row0_col2, #T_bc001_row0_col3, #T_bc001_row0_col4, #T_bc001_row1_col0, #T_bc001_row1_col1, #T_bc001_row1_col2, #T_bc001_row1_col3, #T_bc001_row1_col4, #T_bc001_row2_col0, #T_bc001_row2_col1, #T_bc001_row2_col2, #T_bc001_row2_col3, #T_bc001_row2_col4, #T_bc001_row3_col0, #T_bc001_row3_col1, #T_bc001_row3_col2, #T_bc001_row3_col3, #T_bc001_row3_col4, #T_bc001_row4_col0, #T_bc001_row4_col1, #T_bc001_row4_col2, #T_bc001_row4_col3, #T_bc001_row4_col4, #T_bc001_row5_col0, #T_bc001_row5_col1, #T_bc001_row5_col2, #T_bc001_row5_col3, #T_bc001_row5_col4, #T_bc001_row6_col0, #T_bc001_row6_col1, #T_bc001_row6_col2, #T_bc001_row6_col3, #T_bc001_row6_col4, #T_bc001_row7_col0, #T_bc001_row7_col1, #T_bc001_row7_col2, #T_bc001_row7_col3, #T_bc001_row7_col4, #T_bc001_row8_col0, #T_bc001_row8_col1, #T_bc001_row8_col2, #T_bc001_row8_col3, #T_bc001_row8_col4, #T_bc001_row9_col0, #T_bc001_row9_col1, #T_bc001_row9_col2, #T_bc001_row9_col3, #T_bc001_row9_col4, #T_bc001_row10_col0, #T_bc001_row10_col1, #T_bc001_row10_col2, #T_bc001_row10_col3, #T_bc001_row10_col4 {\n",
       "  text-align: left;\n",
       "}\n",
       "</style>\n",
       "<table id=\"T_bc001_\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th class=\"blank level0\" >&nbsp;</th>\n",
       "      <th class=\"col_heading level0 col0\" >id</th>\n",
       "      <th class=\"col_heading level0 col1\" >estRows</th>\n",
       "      <th class=\"col_heading level0 col2\" >task</th>\n",
       "      <th class=\"col_heading level0 col3\" >access object</th>\n",
       "      <th class=\"col_heading level0 col4\" >operator info</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th id=\"T_bc001_level0_row0\" class=\"row_heading level0 row0\" >0</th>\n",
       "      <td id=\"T_bc001_row0_col0\" class=\"data row0 col0\" >Projection_13</td>\n",
       "      <td id=\"T_bc001_row0_col1\" class=\"data row0 col1\" >20.00</td>\n",
       "      <td id=\"T_bc001_row0_col2\" class=\"data row0 col2\" >root</td>\n",
       "      <td id=\"T_bc001_row0_col3\" class=\"data row0 col3\" ></td>\n",
       "      <td id=\"T_bc001_row0_col4\" class=\"data row0 col4\" >ossinsight.github_events.actor_id, ossinsight.github_users.login, Column#56, Column#57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_bc001_level0_row1\" class=\"row_heading level0 row1\" >1</th>\n",
       "      <td id=\"T_bc001_row1_col0\" class=\"data row1 col0\" >└─TopN_16</td>\n",
       "      <td id=\"T_bc001_row1_col1\" class=\"data row1 col1\" >20.00</td>\n",
       "      <td id=\"T_bc001_row1_col2\" class=\"data row1 col2\" >root</td>\n",
       "      <td id=\"T_bc001_row1_col3\" class=\"data row1 col3\" ></td>\n",
       "      <td id=\"T_bc001_row1_col4\" class=\"data row1 col4\" >Column#56:desc, offset:0, count:20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_bc001_level0_row2\" class=\"row_heading level0 row2\" >2</th>\n",
       "      <td id=\"T_bc001_row2_col0\" class=\"data row2 col0\" >  └─HashAgg_20</td>\n",
       "      <td id=\"T_bc001_row2_col1\" class=\"data row2 col1\" >7478.45</td>\n",
       "      <td id=\"T_bc001_row2_col2\" class=\"data row2 col2\" >root</td>\n",
       "      <td id=\"T_bc001_row2_col3\" class=\"data row2 col3\" ></td>\n",
       "      <td id=\"T_bc001_row2_col4\" class=\"data row2 col4\" >group by:ossinsight.github_events.actor_id, funcs:count(1)->Column#56, funcs:group_concat(distinct ossinsight.github_events.repo_name separator \",\")->Column#57, funcs:firstrow(ossinsight.github_events.actor_id)->ossinsight.github_events.actor_id, funcs:firstrow(ossinsight.github_users.login)->ossinsight.github_users.login</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_bc001_level0_row3\" class=\"row_heading level0 row3\" >3</th>\n",
       "      <td id=\"T_bc001_row3_col0\" class=\"data row3 col0\" >    └─Projection_22</td>\n",
       "      <td id=\"T_bc001_row3_col1\" class=\"data row3 col1\" >71834.93</td>\n",
       "      <td id=\"T_bc001_row3_col2\" class=\"data row3 col2\" >root</td>\n",
       "      <td id=\"T_bc001_row3_col3\" class=\"data row3 col3\" ></td>\n",
       "      <td id=\"T_bc001_row3_col4\" class=\"data row3 col4\" >ossinsight.github_events.repo_name, ossinsight.github_events.actor_id, ossinsight.github_users.login</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_bc001_level0_row4\" class=\"row_heading level0 row4\" >4</th>\n",
       "      <td id=\"T_bc001_row4_col0\" class=\"data row4 col0\" >      └─HashJoin_43</td>\n",
       "      <td id=\"T_bc001_row4_col1\" class=\"data row4 col1\" >71834.93</td>\n",
       "      <td id=\"T_bc001_row4_col2\" class=\"data row4 col2\" >root</td>\n",
       "      <td id=\"T_bc001_row4_col3\" class=\"data row4 col3\" ></td>\n",
       "      <td id=\"T_bc001_row4_col4\" class=\"data row4 col4\" >inner join, equal:[eq(ossinsight.github_users.id, ossinsight.github_events.actor_id)]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_bc001_level0_row5\" class=\"row_heading level0 row5\" >5</th>\n",
       "      <td id=\"T_bc001_row5_col0\" class=\"data row5 col0\" >        ├─IndexReader_54(Build)</td>\n",
       "      <td id=\"T_bc001_row5_col1\" class=\"data row5 col1\" >20027.00</td>\n",
       "      <td id=\"T_bc001_row5_col2\" class=\"data row5 col2\" >root</td>\n",
       "      <td id=\"T_bc001_row5_col3\" class=\"data row5 col3\" ></td>\n",
       "      <td id=\"T_bc001_row5_col4\" class=\"data row5 col4\" >index:Selection_53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_bc001_level0_row6\" class=\"row_heading level0 row6\" >6</th>\n",
       "      <td id=\"T_bc001_row6_col0\" class=\"data row6 col0\" >        │ └─Selection_53</td>\n",
       "      <td id=\"T_bc001_row6_col1\" class=\"data row6 col1\" >20027.00</td>\n",
       "      <td id=\"T_bc001_row6_col2\" class=\"data row6 col2\" >cop[tikv]</td>\n",
       "      <td id=\"T_bc001_row6_col3\" class=\"data row6 col3\" ></td>\n",
       "      <td id=\"T_bc001_row6_col4\" class=\"data row6 col4\" >eq(ossinsight.github_users.country_code, \"CN\")</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_bc001_level0_row7\" class=\"row_heading level0 row7\" >7</th>\n",
       "      <td id=\"T_bc001_row7_col0\" class=\"data row7 col0\" >        │   └─IndexFullScan_52</td>\n",
       "      <td id=\"T_bc001_row7_col1\" class=\"data row7 col1\" >127648.00</td>\n",
       "      <td id=\"T_bc001_row7_col2\" class=\"data row7 col2\" >cop[tikv]</td>\n",
       "      <td id=\"T_bc001_row7_col3\" class=\"data row7 col3\" >table:gu, index:index_gu_on_login_is_bot_organization_country_code(login, is_bot, organization_formatted, country_code)</td>\n",
       "      <td id=\"T_bc001_row7_col4\" class=\"data row7 col4\" >keep order:false</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_bc001_level0_row8\" class=\"row_heading level0 row8\" >8</th>\n",
       "      <td id=\"T_bc001_row8_col0\" class=\"data row8 col0\" >        └─TableReader_60(Probe)</td>\n",
       "      <td id=\"T_bc001_row8_col1\" class=\"data row8 col1\" >71834.93</td>\n",
       "      <td id=\"T_bc001_row8_col2\" class=\"data row8 col2\" >root</td>\n",
       "      <td id=\"T_bc001_row8_col3\" class=\"data row8 col3\" >partition:pull_request_event</td>\n",
       "      <td id=\"T_bc001_row8_col4\" class=\"data row8 col4\" >data:Selection_59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_bc001_level0_row9\" class=\"row_heading level0 row9\" >9</th>\n",
       "      <td id=\"T_bc001_row9_col0\" class=\"data row9 col0\" >          └─Selection_59</td>\n",
       "      <td id=\"T_bc001_row9_col1\" class=\"data row9 col1\" >71834.93</td>\n",
       "      <td id=\"T_bc001_row9_col2\" class=\"data row9 col2\" >cop[tiflash]</td>\n",
       "      <td id=\"T_bc001_row9_col3\" class=\"data row9 col3\" ></td>\n",
       "      <td id=\"T_bc001_row9_col4\" class=\"data row9 col4\" >eq(ossinsight.github_events.action, \"opened\"), eq(ossinsight.github_events.type, \"PullRequestEvent\")</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_bc001_level0_row10\" class=\"row_heading level0 row10\" >10</th>\n",
       "      <td id=\"T_bc001_row10_col0\" class=\"data row10 col0\" >            └─TableFullScan_58</td>\n",
       "      <td id=\"T_bc001_row10_col1\" class=\"data row10 col1\" >1214915.00</td>\n",
       "      <td id=\"T_bc001_row10_col2\" class=\"data row10 col2\" >cop[tiflash]</td>\n",
       "      <td id=\"T_bc001_row10_col3\" class=\"data row10 col3\" >table:ge</td>\n",
       "      <td id=\"T_bc001_row10_col4\" class=\"data row10 col4\" >keep order:false</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n"
      ],
      "text/plain": [
       "<pandas.io.formats.style.Styler at 0x7f842e131b38>"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query(\"\"\"\n",
    "EXPLAIN SELECT ge.actor_id, gu.login, COUNT(1) AS prs, GROUP_CONCAT(DISTINCT ge.repo_name) AS repos\n",
    "FROM github_events ge\n",
    "JOIN github_users gu ON ge.actor_id = gu.id\n",
    "WHERE\n",
    "    ge.type = 'PullRequestEvent'\n",
    "    AND ge.action = 'opened'\n",
    "    AND gu.country_code = 'CN'\n",
    "GROUP BY ge.actor_id\n",
    "ORDER BY prs DESC\n",
    "LIMIT 20\n",
    "\"\"\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "可以发现优化智能的选择是使用能够利用索引能力的 TiKV 还是使用能够胜任大量数据快速扫描的 TiFlash 来查询数据。\n",
    "\n",
    "通过优化器，TiDB 已经为我们智能地决定在何时何表使用何种存储引擎效率更高，这很大程度地能减轻了编写 SQL 时的工作量，让我们将注意力更好地聚焦到我们的实际业务当中。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 图表可视化\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "在上面的章节当中，我们已经掌握了通过 SQL 语句来进行简单的数据分析，现在，我们可以使用各种 BI 工具或者前端图表框架来将 SQL 语句查询到的数据结果进行可视化。\n",
    "\n",
    "你可以通过 [Data visualization tools for application developers](https://awesome.cube.dev/) 到这些工具的集合。\n",
    "\n",
    "这里我们以 [Grafana Cloud](https://grafana.com/products/cloud/) 为例，介绍如何将 TiDB Cloud Serverless tier 与其他数据可视化工具进行集成。\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 创建账号\n",
    "\n",
    "你可以通过你的 GitHub 账号快速[注册](https://grafana.com/auth/sign-up/create-user)一个 Grafana Cloud 账号。\n",
    "\n",
    "在其 Overview 页面，可以在 Grafana 实例的卡片当中点击 “Launch” 按钮来启动一个 Grafana 实例。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 连接 TiDB Cloud"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "在进入到 Grafana 的管理界面时，我们可以点击左侧菜单中的 “Configuration” > “Data sources” 菜单项进入到数据源配置页面。\n",
    "\n",
    "<center>\n",
    "    <img width=\"200\" alt=\"image\" src=\"https://user-images.githubusercontent.com/88522432/205971297-ab1b822a-2461-445c-8f85-99a5f834dc7d.png\">\n",
    "    <p><i>Data source config entry</i></p>\n",
    "</center>\n",
    "\n",
    "添加新的 Data Source，Data Source 的类型可以选择使用 “MySQL”。\n",
    "\n",
    "<center>\n",
    "    <img width=\"600\" alt=\"image\" src=\"https://user-images.githubusercontent.com/88522432/205973144-85da8262-7efd-4762-b958-6b710a6f1c43.png\">\n",
    "    <p><i>Add data source</i></p>\n",
    "</center>\n",
    "\n",
    "将 TiDB Cloud Serverless Tier 集群的连接信息填写到对应的位置。\n",
    "\n",
    "因为 Serverless Tier 要求必须使用 TLS 连接，因此还需要勾选 “With CA Cert” 选项，然后将受信任的 CA 证书填写到文本框当中（例如：[isrgrootx1.pem.txt](https://letsencrypt.org/certs/isrgrootx1.pem.txt)）。\n",
    "\n",
    "<center>\n",
    "    <img width=\"540\" alt=\"image\" src=\"https://user-images.githubusercontent.com/88522432/205973887-a1fb69be-2d0d-4166-b234-61d17a1196a2.png\">\n",
    "    <p><i>Config data source</i></p>\n",
    "</center>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 新建 Dashboard\n",
    "\n",
    "点击左侧菜单中 “New Dashboard” 按钮以创建一个新的 Dashboard。\n",
    "\n",
    "<center>\n",
    "    <img width=\"254\" alt=\"image\" src=\"https://user-images.githubusercontent.com/88522432/205975900-472c39c4-7eb2-45a4-b928-cd89567fe6bc.png\">\n",
    "    <p><i>New Dashboard</i></p>\n",
    "</center>\n",
    "\n",
    "在 Dashboard 上新建创建新的 Panel，然后将数据源选择了上面创建的 “TiDB” 数据库，编写 SQL 语句，执行，然后选择合适的图表用于数据可视化。\n",
    "\n",
    "<center>\n",
    "    <img width=\"1000\" alt=\"image\" src=\"https://user-images.githubusercontent.com/88522432/205977281-234203cc-bf3b-44d3-920e-9b17c077c357.png\">\n",
    "    <p><i>New Panel</i></p>\n",
    "</center>"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.7.3 64-bit",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "31f2aee4e71d21fbe5cf8b01ff0e069b9275f58929596ceb00d14d90e3e16cd6"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}