MySQL -- 09 -- MySQL调优之explain工具（8.0）

最新推荐文章于 2024-05-09 22:32:27 发布
最新推荐文章于 2024-05-09 22:32:27 发布
阅读量352 收藏 1
点赞数
分类专栏： MySQL 文章标签： MySQL explain
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Goodbye_Youth/article/details/108121607
版权

原文链接：MySQL – 09 – MySQL调优之explain工具（8.0）

相关文章：

使用 explain 可以模拟优化器执行 SQL 语句，从而知道 MySQL 是如何处理 SQL 语句、如何分析查询语句、如何分析表结构的性能瓶颈

  • 表的读取顺序

  • 数据读取操作的操作类型

  • 哪些索引可以使用

  • 哪些索引被实际使用

  • 表之间的引用

  • 每张表有多少行数据被优化器查询

此外，除了 select 语句，其他如：insert、update、delete、replace 等语句也可以使用 explain 来查询执行计划

官方文档：EXPLAIN Output Format

一、创建测试数据

  • 生成思路

    • 利用 MySQL 内存表插入快的特点，可以先利用函数和存储过程在内存表中生成数据，然后再从内存表插入普通表中

    • 这里我们来生成一张具有 1000 条数据的学生信息表

  • 创建普通表和内存表

    • 普通表

      DROP TABLE IF EXISTS tbl_student;
CREATE TABLE tbl_student (
  id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid VARCHAR(40) NOT NULL COMMENT '学生唯一标识',
  name VARCHAR(20) NOT NULL COMMENT '学生姓名',
  age INT(3) UNSIGNED NOT NULL COMMENT '学生年龄',
  sex TINYINT(1) UNSIGNED NOT NULL COMMENT '学生性别: 1=男，0=女',
  PRIMARY KEY (id),
  UNIQUE KEY uk_uuid (uuid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生信息表';

    • 内存表

      DROP TABLE IF EXISTS tbl_student_memory;
CREATE TABLE tbl_student_memory (
    id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    uuid VARCHAR(40) NOT NULL COMMENT '学生唯一标识',
    name VARCHAR(20) NOT NULL COMMENT '学生姓名',
    age INT(3) UNSIGNED NOT NULL COMMENT '学生年龄',
    sex TINYINT(1) UNSIGNED NOT NULL COMMENT '学生性别: 1=男，0=女',
    PRIMARY KEY (id),
    UNIQUE KEY uk_uuid (uuid)
) ENGINE=Memory DEFAULT CHARSET=utf8 COMMENT='学生信息表';

  • 创建随机函数

    • 生成随机姓名

      DROP FUNCTION IF EXISTS generate_student_name;
CREATE FUNCTION generate_student_name(n INT) RETURNS VARCHAR(20)
BEGIN 
    DECLARE source_name VARCHAR(60) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'; 
    DECLARE return_name VARCHAR(20) DEFAULT '' ;
    DECLARE i INT DEFAULT 0; 
    WHILE i < n DO 
        SET return_name = CONCAT(return_name, SUBSTRING(source_name , FLOOR(1 + RAND() * 62 ), 1)); 
        SET i = i +1; 
    END WHILE; 
    RETURN return_name; 
END;

    • 生成随机年龄

      DROP FUNCTION IF EXISTS generate_student_age;
CREATE FUNCTION generate_student_age() RETURNS INT(2)
BEGIN 
    DECLARE return_age INT(2) DEFAULT 20;
    SET return_age = return_age + RAND() * 10; 
    RETURN return_age; 
END;

    • 生成随机性别

      DROP FUNCTION IF EXISTS generate_student_sex;
CREATE FUNCTION generate_student_sex() RETURNS TINYINT(1)
BEGIN 
    DECLARE return_sex TINYINT(1) DEFAULT 1 ;
    SET return_sex = (CASE WHEN RAND() >= 0.5 THEN 1 ELSE 0 END); 
    RETURN return_sex; 
END;

  • 创建存储过程

    DROP PROCEDURE IF EXISTS add_student_record;
CREATE PROCEDURE add_student_record(IN n int)
BEGIN  
    DECLARE i INT DEFAULT 1;
    WHILE (i <= n) DO
      INSERT INTO tbl_student_memory (uuid, name, age, sex ) 
      VALUES (UUID(), generate_student_name(20), generate_student_age(), generate_student_sex());
      SET i = i + 1;
    END WHILE;
END;

  • 调用存储过程

    CALL add_student_record(1000);

  • 插入普通表中

    INSERT INTO tbl_student SELECT * FROM tbl_student_memory;

  • 统计学生表

    mysql> select count(*) from tbl_student;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)

  • PS：如果要生成大量数据，需要提前在 MySQL 配置文件中设置两个参数，然后重启 MySQL

    tmp_table_size=1024M
max_heap_table_size=1024M

    • tmp_table_size

      • 内存临时表的最大值，超过该限制后会记录到磁盘当中，存储在指定的 tmpdir 目录下

        mysql> show variables like 'tmpdir';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| tmpdir        | /tmp  |
+---------------+-------+
1 row in set (0.00 sec)

    • max_heap_table_size

      • 用户可以创建的内存表大小，该值用于计算内存表的最大行数值

二、调用 explain

mysql> explain select name from tbl_student t order by name desc;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

三、参数说明

  • id

    • 查询序号，表示执行 select 语句的执行顺序

    • 当 id 相同时，从上到下顺序执行；当 id 不同时，id 越大的越先执行

      mysql> explain select t1.uuid, t2.name from tbl_student t1, tbl_student t2 where t2.name = t1.name;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 | NULL                                               |
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

      mysql> explain select name from tbl_student t1 union select name from tbl_student t2;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | t1         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 | NULL            |
|  2 | UNION        | t2         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 | NULL            |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
3 rows in set, 1 warning (0.00 sec)

  • select_type

    • 查询类型，主要用于区别普通查询、联合查询、子查询等复杂查询，此处只展示了常见的分类

      • SIMPLE

        • 最简单的 select 语句，查询中不包括子查询和 union

          mysql> explain select name from tbl_student t;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

      • PRIMARY

        • 最外层的 select 语句，在使用 union 的查询语句中是最前面的 select 语句

          mysql> explain select name from tbl_student t1 where id = (select id from tbl_student t2 where id = 1);
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | t1    | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL        |
|  2 | SUBQUERY    | t2    | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

          mysql> explain select name from tbl_student t1 union select name from tbl_student t2;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | t1         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 | NULL            |
|  2 | UNION        | t2         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 | NULL            |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
3 rows in set, 1 warning (0.00 sec)

      • UNION

        • union 后的第二个或之后的 select 语句

          mysql> explain select name from tbl_student t1 union select name from tbl_student t2;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | t1         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 | NULL            |
|  2 | UNION        | t2         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 | NULL            |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
3 rows in set, 1 warning (0.00 sec)

      • DEPENDENT UNION

        • union 后的第二个或之后的 select 语句，同时依赖于外部查询

          mysql> explain select name from tbl_student t where id in (select id from tbl_student t1 union select id from tbl_student t2);
+----+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-----------------+
| id | select_type        | table      | partitions | type   | possible_keys | key     | key_len | ref  | rows | filtered | Extra           |
+----+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-----------------+
|  1 | PRIMARY            | t          | NULL       | ALL    | NULL          | NULL    | NULL    | NULL | 1000 |   100.00 | Using where     |
|  2 | DEPENDENT SUBQUERY | t1         | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | func |    1 |   100.00 | Using index     |
|  3 | DEPENDENT UNION    | t2         | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | func |    1 |   100.00 | Using index     |
| NULL | UNION RESULT       | <union2,3> | NULL       | ALL    | NULL          | NULL    | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-----------------+
4 rows in set, 1 warning (0.00 sec)

      • UNION RESULT

        • union 查询的结果

          mysql> explain select name from tbl_student t1 union select name from tbl_student t2;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | t1         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 | NULL            |
|  2 | UNION        | t2         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 | NULL            |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
3 rows in set, 1 warning (0.00 sec)

      • SUBQUERY

        • 子查询中的第一个 select 语句

          mysql> explain select name from tbl_student t1 where id = (select id from tbl_student t2 where id = 1);
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | t1    | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL        |
|  2 | SUBQUERY    | t2    | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

      • DEPENDENT SUBQUERY

        • 子查询中的第一个 select 语句，同时依赖于外部查询

          mysql> explain select name from tbl_student t1 where id = (select id from tbl_student t2 where id = t1.id);
+----+--------------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------------+
| id | select_type        | table | partitions | type   | possible_keys | key     | key_len | ref        | rows | filtered | Extra       |
+----+--------------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------------+
|  1 | PRIMARY            | t1    | NULL       | ALL    | NULL          | NULL    | NULL    | NULL       | 1000 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | t2    | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | test.t1.id |    1 |   100.00 | Using index |
+----+--------------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)

      • DERIVED

        • 派生表，from 子句中的 select 语句

          mysql> explain select name from (select name from tbl_student t1 union select name from tbl_student t2) t3;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 2000 |   100.00 | NULL            |
|  2 | DERIVED      | t1         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 | NULL            |
|  3 | UNION        | t2         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 | NULL            |
| NULL | UNION RESULT | <union2,3> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
4 rows in set, 1 warning (0.00 sec)

  • table

    • 输出行所引用的表，也有可能是以下值之一

      • <unionM,N>

        • 该行引用的表为 id 是 M 和 N 行的并集

          mysql> explain select name from tbl_student t1 union select name from tbl_student t2;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | t1         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 | NULL            |
|  2 | UNION        | t2         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 | NULL            |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
3 rows in set, 1 warning (0.00 sec)

      • <derivedN>

        • 该行引用的表为 id 是 N 行的派生表结果 (派生表可能来源于 from 子句中的 select 语句)

          mysql> explain select name from (select name from tbl_student t1 union select name from tbl_student t2) t3;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 2000 |   100.00 | NULL            |
|  2 | DERIVED      | t1         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 | NULL            |
|  3 | UNION        | t2         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 | NULL            |
| NULL | UNION RESULT | <union2,3> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
4 rows in set, 1 warning (0.00 sec)

      • <subqueryN>

        • 该行引用的表为 id 是 N 行的实例化子查询结果

  • partitions

    • 查询输出行对应的分区，对于未分区的表而言，该值为 null

  • type

    • 连接类型，用于说明表与表之间是如何进行关联操作的，此处只展示了常见的连接类型

      • system

        • 表示仅有一行记录，是 const 类型的特例

      • const

        • 表示通过索引查找一次就能找到指定的数据，常用于常量

        • const 相比较于 primary key 或者 unique 索引，因此只匹配一行数据，所以很快

          mysql> explain select name from tbl_student where id = 1;
+----+-------------+-------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table       | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tbl_student | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+-------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

      • eq_ref

        • 唯一性索引扫描，对于每个索引键，表中只有一行数据与之匹配，常用于主键或唯一键

          mysql> explain select t1.name from tbl_student t1, tbl_student t2 where t2.id = t1.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref        | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL       | 1000 |   100.00 | NULL        |
|  1 | SIMPLE      | t2    | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | test.t1.id |    1 |   100.00 | Using index |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

      • ref

        • 非唯一性索引扫描，对于每个索引键，表中有一行或多行数据与之匹配

          mysql> alter table tbl_student add index idx_name (name);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select name from tbl_student where name = 'cLJqIcFALVbZfGVo';
+----+-------------+-------------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table       | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | tbl_student | NULL       | ref  | idx_name      | idx_name | 62      | const |    1 |   100.00 | Using index |
+----+-------------+-------------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> alter table tbl_student drop index idx_name;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

      • fulltext

        • 表示使用全文索引来查找数据

          mysql> alter table tbl_student add fulltext index ft_name (name);
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select name from tbl_student where match (name) against ('cLJqIcFALVbZfGVo');
+----+-------------+-------------+------------+----------+---------------+---------+---------+-------+------+----------+-------------------------------+
| id | select_type | table       | partitions | type     | possible_keys | key     | key_len | ref   | rows | filtered | Extra                         |
+----+-------------+-------------+------------+----------+---------------+---------+---------+-------+------+----------+-------------------------------+
|  1 | SIMPLE      | tbl_student | NULL       | fulltext | ft_name       | ft_name | 0       | const |    1 |   100.00 | Using where; Ft_hints: sorted |
+----+-------------+-------------+------------+----------+---------------+---------+---------+-------+------+----------+-------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> alter table tbl_student drop index ft_name;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

      • ref_or_null

        • ref_or_null 与 ref 类似，不同之处在于使用该连接类型 MySQL 会额外搜索包含 null 值的行

          mysql> alter table tbl_student add index idx_name (name);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table tbl_student modify name varchar(20) default null comment '学生姓名';
Query OK, 0 rows affected (0.22 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> update tbl_student set name = null where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> explain select name from tbl_student where name = 'cLJqIcFALVbZfGVo' or name is null;
+----+-------------+-------------+------------+-------------+---------------+----------+---------+-------+------+----------+--------------------------+
| id | select_type | table       | partitions | type        | possible_keys | key      | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+-------------+------------+-------------+---------------+----------+---------+-------+------+----------+--------------------------+
|  1 | SIMPLE      | tbl_student | NULL       | ref_or_null | idx_name      | idx_name | 63      | const |    2 |   100.00 | Using where; Using index |
+----+-------------+-------------+------------+-------------+---------------+----------+---------+-------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> update tbl_student set name = 'cLJqIcFALVbZfGVo' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> alter table tbl_student modify name varchar(20) not null comment '学生姓名';
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table tbl_student drop index idx_name;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

      • index_merge

        • 表示使用索引合并优化

        • 在这种情况下，key 输出行中包含使用的索引列表，key_len 输出行中包含使用的索引的最大字节长度列表

        • 该连接类型仅适用于单个表的索引扫描，而不适合于多个表的索引扫描

          mysql> alter table tbl_student add index idx_name (name);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select name from tbl_student where uuid = '53bd65ec-c76b-11ea-84aa-00163e0d9873' or name = 'RluhKMuVaAgnZFDxF';
+----+-------------+-------------+------------+-------------+------------------+------------------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table       | partitions | type        | possible_keys    | key              | key_len | ref  | rows | filtered | Extra                                      |
+----+-------------+-------------+------------+-------------+------------------+------------------+---------+------+------+----------+--------------------------------------------+
|  1 | SIMPLE      | tbl_student | NULL       | index_merge | uk_uuid,idx_name | uk_uuid,idx_name | 122,62  | NULL |    2 |   100.00 | Using union(uk_uuid,idx_name); Using where |
+----+-------------+-------------+------------+-------------+------------------+------------------+---------+------+------+----------+--------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> alter table tbl_student drop index idx_name;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

      • range

        • 只检索指定范围的行，常用于使用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, IN 等操作且比较值为常量的情况

          mysql> explain select name from tbl_student where id = 1 or id = 2;
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table       | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tbl_student | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL |    2 |   100.00 | Using where |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

          mysql> explain select name from tbl_student where id > 1;
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table       | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tbl_student | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL |  999 |   100.00 | Using where |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

      • index

        • index 与 ALL 类似，不同之处在于使用该连接类型只会去扫描索引树，通常要比 ALL 块，因为索引文件通常要比数据文件小

        • 虽然仍然为全表扫描，但优点在于索引是有序的，不需要借助外部索引来进行排序

          mysql> explain select uuid from tbl_student;
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table       | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tbl_student | NULL       | index | NULL          | uk_uuid | 122     | NULL | 1000 |   100.00 | Using index |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

      • ALL

        • 全表扫描，是效率最低的查找方式

          mysql> explain select * from tbl_student;
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | tbl_student | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 | NULL  |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

  • possible_keys

    • MySQL 在查询时可能使用的索引，查询涉及到的字段若存在索引， 则该索引会被列出，但不一定被查询使用

  • key

    • MySQL 实际使用的索引

    • 如果没有使用索引，则 key 为 null；如果是使用了覆盖索引 (指 select 查询的数据只需要从索引中就能取得，而不必读取数据行，换句话说就是，查询列要被所建的索引覆盖)，则 key 为该索引

      mysql> explain select uuid from tbl_student;
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table       | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tbl_student | NULL       | index | NULL          | uk_uuid | 122     | NULL | 1000 |   100.00 | Using index |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

  • key_len

    • MySQL 实际使用的索引的字节长度，可通过 key_len 来计算查询中使用的索引长度，在不损失精度的情况下，长度越短越好

    • key_len 显示的是索引字节的最大可能长度，而非实际使用长度，即 key_len 是根据表定义计算得到的，而不是通过表内检索得到的

    • 举例说明

      • VARCHAR (变长字段)

        mysql> explain select uuid from tbl_student;
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table       | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tbl_student | NULL       | index | NULL          | uk_uuid | 122     | NULL | 1000 |   100.00 | Using index |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

        • 如上所示，查询 uuid 字段，key_len 为 122

        • 在创建表时，uuid 字段类型为 VARCHAR，最大长度为 40 (uuid VARCHAR(40) NOT NULL COMMENT '学生唯一标识')，字符集为 utf-8，也就是说一个字符占 3 个字节，40 * 3 = 120 个字节，剩余还有 2 个字节是由于该字段为变长字段 (VARCHAR) 而预留的，若将其该为定长字段 (CHAR)，就不需要额外的字节了

      • CHAR (定长字段)

        mysql> alter table tbl_student modify column uuid CHAR(40) NOT NULL COMMENT '学生唯一标识';
Query OK, 1000 rows affected (0.14 sec)
Records: 1000  Duplicates: 0  Warnings: 0

mysql> explain select uuid from tbl_student;
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table       | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tbl_student | NULL       | index | NULL          | uk_uuid | 120     | NULL |  757 |   100.00 | Using index |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
        • 如上所示，我们将 uuid 字段类型改为 CHAR，然后再调用 explain，此时显示 key_len 为 120，即 40 * 3 = 120 个字节，符合预期

      • 最后我们再把 uuid 字段类型改回去

        mysql> alter table tbl_student modify column uuid VARCHAR(40) NOT NULL COMMENT '学生唯一标识';
Query OK, 1000 rows affected (0.13 sec)
Records: 1000  Duplicates: 0  Warnings: 0

  • ref

    • 显示索引的哪一列被使用了，可能为列或者常量

      mysql> explain select name from tbl_student where id = 1;
+----+-------------+-------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table       | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tbl_student | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+-------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

      mysql> explain select t1.uuid, t2.name from tbl_student t1, tbl_student t2 where t2.id = t1.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref        | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | index  | PRIMARY       | uk_uuid | 122     | NULL       | 1000 |   100.00 | Using index |
|  1 | SIMPLE      | t2    | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | test.t1.id |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

  • rows

    • 根据表统计信息以及索引索引的选用情况，大致估算出找到指定记录所需要读取的行数

  • filtered

    • 通过过滤的行数对比总行数的百分比

    • 最大值为 100，表示没有进行过滤；值从 100 开始减小，表示过滤量增加

    • rows 为读取的估计行数，rows * filtered 的结果表示与下表连接的行数，如：rows = 1000，filtered = 50.00 (50%)，则与下表连接的行数为 1000 * 50% = 500

  • Extra

    • 不适合在其他列中显示，但十分重要的额外信息，此处只展示了常见的额外信息

      • Using filesort

        • 表示 MySQL 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，MySQL 中无法利用索引完成的排序操作称为 “文件排序”

          mysql> explain select name from tbl_student order by uuid desc;
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | tbl_student | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 | Using filesort |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

      • Using temporary

        • 表示使用了临时表来保存中间结果，MySQL 在对查询结果排序时使用了临时表，常用于排序 order by 和分组查询 group by

          mysql> explain select name from tbl_student group by name;
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
|  1 | SIMPLE      | tbl_student | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 | Using temporary; Using filesort |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec)

      • Using index

        • 表示使用了覆盖索引，避免了访问表的行数据

        • 如果同时出现 Using where，则表示索引被用于执行索引键值的查找

          mysql> alter table tbl_student add index idx_uuid_name (uuid, name);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select uuid from tbl_student where name = 'cLJqIcFALVbZfGVo';
+----+-------------+-------------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+
| id | select_type | table       | partitions | type  | possible_keys | key           | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | tbl_student | NULL       | index | NULL          | idx_uuid_name | 184     | NULL | 1000 |    10.00 | Using where; Using index |
+----+-------------+-------------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.01 sec)

mysql> alter table tbl_student drop index idx_uuid_name;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

        • 如果没有出现 Using where, 则表示索引被用于读取数据而非执行查找操作

          mysql> explain select uuid from tbl_student;
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table       | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tbl_student | NULL       | index | NULL          | uk_uuid | 122     | NULL | 1000 |   100.00 | Using index |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

      • Using where

        • 表示使用了 where 条件过滤

          mysql> explain select name from tbl_student where name = 'cLJqIcFALVbZfGVo';
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tbl_student | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |    10.00 | Using where |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

      • Using join buffer

        • 表示使用了连接缓存

          mysql> explain select t1.uuid, t2.name from tbl_student t1 inner join tbl_student t2 on t2.name = t1.name;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 | NULL                                               |
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

      • Impossible WHERE

        • 表示使用了 where 条件过滤，但 where 语句的值总为 false，不能用来获取任何数据

          mysql> explain select name from tbl_student where id = 1 and id = 2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra            |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Impossible WHERE |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
1 row in set, 1 warning (0.00 sec)

      • Select tables optimized away

        • 在没有 group by 子句的情况下，基于索引来优化 max()、min() 操作

          mysql> explain select max(uuid) from tbl_student;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.00 sec)

          mysql> explain select min(uuid) from tbl_student;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.00 sec)

        • 对于 MyISAM 存储引擎来优化 count(*) 操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化

      • Distinct

        • 表示 MySQL 正在查找不同的值，在找到第一个匹配的数据后将停止查找相同数据的操作

四、相关思考

  • 让我们来思考一个问题，当我们使用以下 SQL 来统计表中数据时，会走哪个索引？

    explain select count(id) from tbl_student;

  • MySQL 使用的是 B+ 树，它的叶子节点中存在链表，我们统计的时候就会直接走链表，方便我们的统计；那么在到达叶子节点前，MySQL 走的是聚集索引 (主键)，还是非聚集索引 (唯一键) 呢？

  • 正常情况下，我们肯定会说会走聚集索引 (主键)，因为聚集索引可以直接在叶子节点中查找到对应的行数据，那么这条统计 SQL 真的走的是聚集索引 (主键) 吗？让我们来一起运行一下

    mysql> explain select count(id) from tbl_student;
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table       | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tbl_student | NULL       | index | NULL          | uk_uuid | 122     | NULL | 1000 |   100.00 | Using index |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

  • 如上所示，出乎意料，这条统计 SQL 并没有走聚集索引 (主键)，而是走的非聚集索引 (唯一键)，那么为什么不走聚集索引呢？

    • MySQL 走哪个索引是由 MySQL 查询优化器来决定的

    • MySQL 查询优化器最重要的目标是尽可能的使用索引，并且使用最严格的索引来消除尽可能多的数据行，最终目标是提交给 select 语句来查找数据行，MySQL 查询优化器会根据自己的分析和判断的标准来决定使用哪一个索引

    • 这里统计 SQL 没有走聚集索引 (主键)，大致是因为聚集索引 (主键) 的叶子节点还存放了主键对应的行数据，当数据都放在一起后，查询效率会比非聚集索引 (唯一键) 要低；而非聚集索引 (唯一键) 只存储关键字以及主键的值，这样我们在内存里就可以加载更多的关键字以及主键的值，来做 count 查询操作，从而提高了性能

  • 为了验证上面的说话，我们把表 tbl_student 的数据增加到 100w 条 (调用步骤一中的操作 (CALL add_student_record(1000000);))

    • 使用非聚集索引 (唯一键)

      mysql> select count(id) from tbl_student;
+-----------+
| count(id) |
+-----------+
|   1000000 |
+-----------+
1 row in set (0.19 sec)

    • 使用聚集索引 (主键)

      mysql> select count(id) from tbl_student force index (primary);
+-----------+
| count(id) |
+-----------+
|   1000000 |
+-----------+
1 row in set (0.33 sec)

    • 如上所示，当使用非聚集索引 (唯一键) 时，效率确实要比使用聚集索引 (主键) 高

五、参考资料

Gene Xu
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL8.0分析查询语句EXPLAIN
小草的博客
11-17 542
MySQL8.0分析查询语句EXPLAIN
MySQL Explain详解
过客2019
01-28 185
在日常工作中，我们会有时会开慢查询去记录一些执行时间比较久的SQL语句，找出这些SQL语句并不意味着完事了，些时我们常常用到explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。所以我们深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采用。（QEP：sql生成一个执行计划query Execution plan） mysq
MYSQL-8.调优
最新发布
xrl00的博客
05-09 1292
本文从多个维度简单说明了性能优化的思维与一般情况下的核心步骤，并且从了MySQL性能优化的客户端与连接层优化、参数优化、编码层面优化、结构优化、整体架构优化五个方向如何进行优化进行了简单分析，在进行调优时可作为理论依据支撑；
最全MySQL8.0实战教程 22 MySQL的优化 22.4 explain分析执行计划 22.4.4 Explain之type
谢谢你们的关注
03-11 106
最全MySQL8.0实战教程 22 MySQL的优化 22.4 explain分析执行计划 22.4.4 Explain之type
MySQL 8.0 的执行计划（EXPLAIN）
专注于数据库技术分享，包含但不限于Oracle，MySQL，PostgreSQL，ElasticSearch及国产数据库等
03-11 618
解读执行计划可以帮助数据库管理员（DBA）和开发者理解查询如何执行，识别潜在的性能问题，并据此优化查询。总之，解读 MySQL 8.0 的执行计划需要一定的经验和知识，但通过不断实践和分析，你可以提高查询性能并优化数据库的整体性能。分析表和索引: 使用 ANALYZE TABLE 命令更新表的统计信息，帮助优化器做出更好的决策。重写查询: 有时，通过重写查询逻辑或使用不同的 SQL 结构，可以获得更好的性能。添加或调整索引: 如果查询没有使用索引或使用了不合适的索引，考虑添加或调整索引。
新特性解读 | MySQL 8.0：explain analyze 分析 SQL 执行过程
ActionTech的博客
01-13 1416
作者：胡呈清 爱可生 DBA 团队成员，擅长故障分析、性能优化，个人博客：https://www.jianshu.com/u/a95ec11f67a8，欢迎讨论。 本文来源：原创投稿 *爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。 介绍 MySQL 8.0.16 引入一个实验特性：explain format=tree ，树状的输出执行过程，以及预估成本和预估返回行数。在 MySQL8.0.18 又引入了 EXPLAIN ANALYZE，在 format=tree 基础.
mysql-refman-8.0.zip
03-20
综上所述，`mysql-refman-8.0-en.a4.pdf`包含了丰富的MySQL 8.0知识，无论你是初学者还是经验丰富的数据库管理员，这份文档都是宝贵的参考资料，能够帮助你深入理解和有效利用MySQL 8.0的强大功能。通过阅读和学习，...
mysql-8.0.26-macos11-x86_64.dmg
09-10
MySQL是世界上最受欢迎的开源关系型数据库管理系统之一，其最新版本为8.0.26，专为macOS 11（x86, 64-bit）系统设计的DMG档案名为“mysql-8.0.26-macos11-x86_64.dmg”。这个版本的MySQL为苹果操作系统提供了强大的...
MySQL-8.0.25-winx64及教程
07-20
- 性能调优：定期分析查询性能，使用EXPLAIN查看执行计划，调整索引和内存参数。 - 安全策略：定期更改密码，限制不必要的远程访问，使用参数绑定防止SQL注入。 6. **学习资源** - 官方文档：MySQL提供详尽的...
mysql-shell-8.0-en.html-chapter.zip
08-17
MySQL Shell 是MySQL数据库管理系统提供的一款强大、多功能的命令行工具，它结合了SQL、JavaScript和Python的接口，为用户提供了交互式环境，用于管理MySQL服务器、数据库、表以及数据。本章节将深入探讨MySQL Shell...
mysql-test-8.0.34-macos13-x86-64.tar.gz
10-07
MySQL是世界上最受欢迎的开源关系型数据库管理系统之一，其最新版本为8.0.34，专为macOS 13操作系统设计的x86_64架构提供了优化。这个压缩包"mysql-test-8.0.34-macos13-x86_64.tar.gz"包含了在macOS环境下运行MySQL...
mysql explain
04-27
mysql explain
mysql 8.0.18 安装配置优化教程
09-08
主要为大家详细介绍了mysql 8.0.18 安装配置优化教程，文中安装步骤介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们可以参考一下
Mysql优化工具explain
12-14
explain的使用很简单，只要在select语句的前面加上explain的关键字就好了，来看个例子 先创建两个表，注意两个表的索引 CREATE TABLE `user` ( `id` varchar(32) NOT NULL, `name` varchar(32) DEFAULT NULL, `role` varchar(32) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `user` VALUES ('1', '1', '1'); INSERT INT
最全MySQL8.0实战教程 22 MySQL的优化 22.4 explain分析执行计划 22.4.1 基本使用
谢谢你们的关注
03-11 78
最全MySQL8.0实战教程 22 MySQL的优化 22.4 explain分析执行计划 22.4.1 基本使用
mysql8.0数据库中explain查询及优化方案
bobo_supper的博客
10-08 146
【代码】mysql8.0数据库中explain查询及优化方案。
mysql8.0参数优化_Mysql 8.0 参数调优
asdasde的博客
05-24 732
这配置已经优化的不错了，如果你的mysql没有什么特殊情况的话，可以直接使用该配置参数。硬件：内存16G（可根据各自的服务器内存大小自行调整）此配置是生产线上使用的配置，对各参数添加了中文说明。MYSQL服务器my.ini配置文档详解，
MySQL8数据库 | MySQL调优｜MySQL底层原理
m0_64363449的博客
07-26 372
CREATE TABLE tb_name (建表的字段、类型、长度、约束、默认、注释)6、重新启动 mysql 服务，再以无密码登录 mysql。3、前面窗口不能关闭，再开启一个新的窗口进行无密码登录。：复制保存 MySQL初始化密码。：复制保存 MySQL初始化密码。使用上面方式无法登录的解决方案。1、停止 mysql8。
MySQL 8.0数据库性能优化方案
专注于数据库技术分享，包含但不限于Oracle，MySQL，PostgreSQL，ElasticSearch及国产数据库等
03-11 744
调整MySQL的配置参数，例如innodb_buffer_pool_size（InnoDB缓冲池大小）、key_buffer_size（MyISAM索引缓冲区大小）、table_open_cache（表缓存大小）等。在进行性能优化之前，建议先对数据库进行全面的性能分析和评估，找出性能瓶颈后再进行针对性的优化。使用MySQL性能监控工具，如SHOW PROCESSLIST、SHOW STATUS和SHOW VARIABLES，监控数据库的状态和性能。使用索引来提高查询性能，确保经常查询的列有索引。
MySQL 8.0 OCP (1Z0-908) 模拟测试必考练习 上
11-13
"这是针对MySQL 8.0 OCP (1Z0-908) 认证考试的模拟测试题目集，旨在帮助备考者进行复习和准备。" 在这些模拟试题中，我们可以提取出几个重要的MySQL管理和优化知识点： 试题1讨论了在面临磁盘空间不足的情况下如何...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值