美文网首页
MYSQL 8 優化之02 (explain分析)

MYSQL 8 優化之02 (explain分析)

作者: 轻飘飘D | 来源:发表于2019-08-17 19:54 被阅读0次

    1.檢索測試表

    root@127.0.0.1 : testdb【04:17:50】102 SQL->desc country;
+-------------+------------------+------+-----+-------------------+-----------------------------------------------+
| Field       | Type             | Null | Key | Default           | Extra                                         |
+-------------+------------------+------+-----+-------------------+-----------------------------------------------+
| country_id  | int(10) unsigned | NO   | PRI | NULL              | auto_increment                                |
| country     | varchar(50)      | NO   |     | NULL              |                                               |
| last_update | datetime         | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+------------------+------+-----+-------------------+-----------------------------------------------+

root@127.0.0.1 : testdb【04:18:39】103 SQL->desc city;
+-------------+------------------+------+-----+-------------------+-----------------------------------------------+
| Field       | Type             | Null | Key | Default           | Extra                                         |
+-------------+------------------+------+-----+-------------------+-----------------------------------------------+
| city_id     | int(10) unsigned | NO   | PRI | NULL              | auto_increment                                |
| city_name   | varchar(50)      | NO   |     | NULL              |                                               |
| country_id  | int(10) unsigned | NO   | MUL | NULL              |                                               |
| last_update | datetime         | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+------------------+------+-----+-------------------+-----------------------------------------------+

root@127.0.0.1 : testdb【04:18:46】104 SQL->select * from country;
+------------+-----------+---------------------+
| country_id | country   | last_update         |
+------------+-----------+---------------------+
|       1001 | 美利坚    | 2019-07-13 04:03:56 |
|       1002 | 中國      | 2019-07-13 04:10:51 |
|       1003 | 日本      | 2019-07-13 04:11:02 |
|       1004 | 德國      | 2019-07-13 04:11:09 |
|       1005 | 英國      | 2019-07-13 04:11:23 |
+------------+-----------+---------------------+

root@127.0.0.1 : testdb【04:19:04】105 SQL->select * from city;
+---------+-----------+------------+---------------------+
| city_id | city_name | country_id | last_update         |
+---------+-----------+------------+---------------------+
|    1001 | 旧金山    |       1001 | 2019-07-13 04:05:14 |
|    1002 | 紐約      |       1001 | 2019-07-13 04:11:58 |
|    1003 | 華盛頓    |       1001 | 2019-07-13 04:12:14 |
|    1004 | 波士頓    |       1001 | 2019-07-13 04:12:30 |
|    1005 | 北京      |       1002 | 2019-07-13 04:12:58 |
|    1006 | 南京      |       1002 | 2019-07-13 04:13:12 |
|    1007 | 廣州      |       1002 | 2019-07-13 04:13:21 |
|    1008 | 珠海      |       1002 | 2019-07-13 04:13:28 |
|    1009 | 東京      |       1003 | 2019-07-13 04:14:00 |
|    1010 | 大阪      |       1003 | 2019-07-13 04:14:15 |
|    1011 | 柏林      |       1004 | 2019-07-13 04:14:29 |
|    1012 | 汉堡      |       1004 | 2019-07-13 04:15:45 |
|    1013 | 倫敦      |       1005 | 2019-07-13 04:16:00 |
|    1014 | 牛津      |       1005 | 2019-07-13 04:17:19 |
|    1015 | 劍橋      |       1005 | 2019-07-13 04:17:36 |
+---------+-----------+------------+---------------------+
    1. explain 基礎
    root@127.0.0.1 : testdb【04:21:37】109 SQL->explain select count(1) from country a,city b where a.country_id=b.country_id and country='中國' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
     filtered: 20.00
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
   partitions: NULL
         type: ref
possible_keys: idx_fk_country_id
          key: idx_fk_country_id
      key_len: 4
          ref: testdb.a.country_id
         rows: 1
     filtered: 100.00
        Extra: Using index

#輸出說明
select_type:表示select的類型
【simple：簡單表（不使用表連接或子查詢）,
  primary:主查詢（即外層的查詢）,
  union:union中的第二個或後面的查詢語句
  subquery（子查詢中的第一個select）
】
table：輸出結果集涉及的表
type：訪問類型（以下性能由差到高）
【ALL(全表掃描),index(索引全掃描),range(索引範圍掃描),
  ref(非唯一索引/唯一索引前綴掃描),
  eq_ref(唯一索引掃描，多表連接中都使用主鍵或唯一索引作為關聯條件),
  const,system(主鍵/唯一索引唯一查詢),
  NULL(不訪問表或索引就能查到數據)
】
    1. type=ALL
    root@127.0.0.1 : testdb【04:22:01】110 SQL->explain select * from country where country='中國' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: country
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
     filtered: 20.00
        Extra: Using where
    1. type=index
    root@127.0.0.1 : testdb【05:00:18】111 SQL->create index idx_country_01 on country(country) using btree;

root@127.0.0.1 : testdb【05:04:21】114 SQL->explain select country from country  \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: country
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_country_01
      key_len: 202
          ref: NULL
         rows: 5
     filtered: 100.00
        Extra: Using index
    1. type=range
    root@127.0.0.1 : testdb【05:07:04】116 SQL->explain select * from city where country_id between 1002 and 1004  \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: city
   partitions: NULL
         type: range
possible_keys: idx_fk_country_id
          key: idx_fk_country_id
      key_len: 4
          ref: NULL
         rows: 8
     filtered: 100.00
        Extra: Using index condition
    1. type=ref
    root@127.0.0.1 : testdb【05:07:26】117 SQL->explain select * from country where country='中國' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: country
   partitions: NULL
         type: ref
possible_keys: idx_country_01
          key: idx_country_01
      key_len: 202
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL

root@127.0.0.1 : testdb【05:10:49】120 SQL->explain select a.*,b.* from country a,city b where a.country_id=b.country_id \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
   partitions: NULL
         type: ref
possible_keys: idx_fk_country_id
          key: idx_fk_country_id
      key_len: 4
          ref: testdb.a.country_id
         rows: 2
     filtered: 100.00
        Extra: NULL
    1. type=eq_ref
    root@127.0.0.1 : testdb【05:11:19】121 SQL->explain select a.*,b.* from country a,country b where a.country_id=b.country_id \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: testdb.a.country_id
         rows: 1
     filtered: 100.00
        Extra: NULL
    1. type=const,system
    root@127.0.0.1 : testdb【05:17:06】123 SQL->alter table country drop index idx_country_01;

root@127.0.0.1 : testdb【05:18:53】124 SQL->alter table country add unique index udx_country_01(country);

root@127.0.0.1 : testdb【05:21:39】127 SQL->explain select * from country where country='中國' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: country
   partitions: NULL
         type: const
possible_keys: udx_country_01
          key: udx_country_01
      key_len: 202
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
    1. type=NULL
    mysql> explain select 1 from dual \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: No tables used
    1. explain extended
    # explain 命令 通过加上 show warnings 可以看到sql 真正执行之前优化器做了那些sql改写
mysql> explain select country_id from country where 1=1 and country='美利坚' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: country
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where

#优化器去掉了恒成立的 1=1 的条件
mysql> show warnings\G;
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `TESTDB`.`country`.`country_id` AS `country_id` from `TESTDB`.`country` 
where (`TESTDB`.`country`.`country` = '美利坚')

    相关文章

      网友评论

          本文标题：MYSQL 8 優化之02 (explain分析)

          本文链接：https://www.haomeiwen.com/subject/nltslctx.html

          延伸阅读

          深度阅读

          • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

          栏目导航

          热点阅读

          关于我们|服务条款|联系我们|MYSQL 8 優化之02 (explain分析)|投稿指南|网站地图|RSS订阅|排版工具|手机版

          提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

          Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

          备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

          本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

          所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！