开发者

MySQL中的InnoDB单表访问过程

目录
  • 1、背景
  • 2、环境
  • 3、访问类型
    • 【1】const
    • 【2】ref
    • 【3】ref_or_null
    • 【4】range
    • 【5】index
    • 【6】all
  • 4、总结

    1、背景

    mysql通过查询条件查询到结果的过程就叫访问方法,一条查询语句的访问方法有很多种,接下来我们就来讲一下各种访问方法。

    2、环境

    创建表:

    mysql> CREATE TABLE test2
        -> (
        ->     id INT AUTO_INCREMENT PRIMARY KEY,
        ->     str1 VARCHAR(255),
        ->     str2 VARCHAR(255),
        ->     str3 CHAR(5),
        ->     str4 VARCHAR(255),
        ->     str5 CHAR(10),
        ->     INDEX idx_str1 (str1),
        ->     UNphpIQUE INDEX idx_str3 (str3),
        ->     INDEX idx_str4_str5 (str4, str5)
        -> ) ENGINE = InnoDB DEFAULT CHARSET = utf8;
    Query OK, 0 rows affected, 1 warning (0.03 sec)
    

    插入100条数据:

    mysql> INSERT INTO test2 (str1, str2, str3, str4, str5) VALUES
        ->                                                      ('value1', 'data1', 'abc', 'value4_1', 'value5_1'),
        ->                                                      ('value2', 'data2', 'def', 'value4_2', 'value5_2'),
        ->														...
        ->                                                      ('value99', 'data99', 'yz91', 'value4_99', 'value5_99'),
        ->                                                      ('value100', 'data100', 'yz92', 'value4_100', 'value5_100');
    Query OK, 100 rows affected (0.02 sec)
    Records: 100  Duplicates: 0  Warnings: 0
    

    3、访问类型

    【1】const

    通过主键索引或者唯一索引查询一条记录的方法就为const,可以通过explain关键字来看查询语句的访问方式,通过主键查询示例:

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

    type字段就是访问方式,我们再看看通过唯一索引查询的示例:

    mysql> explain select * from test2 where str3 = 'abc';
    +----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------+
    | id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
    +----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------+
    |  1 | SIMPLE      | test2 | NULL       | const | idx_str3      | idx_str3 | 16      | const |    1 |   100.00 | NULL  |
    +----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------+
    1 row in set, 1 warning (0.00 sec)
    

    【2】ref

    使用普通二级索引进行等值匹配时,访问类型就为ref,示例如下:

    mysql> explain select * from test2 where str1 = 'value7';
    +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
    | id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
    +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
    |  1 | SIMPLE      | test2 | NULL       | ref  | idx_str1      | idx_str1 | 767     | const |    1 |   100.00 | NULL  |
    +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
    1 row in set, 1 warning (0.01 sec)
    

    【3】ref_or_null

    二级索引进行等值匹配时,又想把值为NULL的查询出来,这种查询类型就为ref_or_null,先把上面插入的数据部分记录的str1字段改为NULL,sql如下:

    mysql> update test2 set str1 = NULL where id in (3, 6, 8, 9, 34, 78, 89);
    Query OK, 7 rows affected (0.01 sec)
    Rows matched: 7  Changed: 7  Warnings: 0
    

    再看查询类型:

    mysql> explain select * from test2 where str1 = 'value7' or str1 = null;
    +----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+--------
    ---------------+
    | id | select_type | table | partitions | type        | possible_keys | key      | key_len | ref   | rows | filtered | Extra
                   |
    +----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+--------
    ---------------+
    |  1 | SIMPLE      | test2 | NULL       | ref_or_null | idx_str1      | idx_str1 | 768     | const |    2 |   100.00 | Using i
    ndex condition |
    +----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+---编程客栈-------+--------
    ---------------+
    1 row in set, 1 warning (0.00 sec)
    

    【4】range

    顾名思义范围查询就是range,示例如下:

    mysql> explain select * from test2 where id > 2 and id < 7;
    +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
    | id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
    +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
    |  1 | SIMPLE      | test2 | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    4 |   100.00 | Using where |
    +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
    1 row in set, 1 warning (0.00 sec)
    

    【5】index

    使用组合索引中非最左边作为查询条件时,并且查询的字段不需要回表,这个时候就会将组合索引叶子节点全部扫描一遍,这种查询方式就叫index,示例如下:

    mysql> explain select str4, strpython5 from test2 where str5 = 'value5_15';
    +----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+----------
    ----------------+
    | id | select_type | table | partitions | type  | possible_keys | key           | key_len | ref  | rows | filtered | Extra
                    |
    +----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+----------
    ----------------+
    |  1 | SIMPLE      | test2 | NULL       | index | idx_str4_str5 | idx_str4_str5 | 799     | NULL |  100 |    10.00 | Using whe
    re; Using index |
    +----+-------------+-------+------------+-------+---------------+---------------+---------+-python-----+------+----------+----------
    ----------------+
    1 row in set, 1 warning (0.00 sec)
    

    【6】all

    对主键索引所在的叶子节点进行全表扫描就叫all,示例如下:

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

    4、总结

    mysql中优化器会将我们的查询条件进行优化,我们可以通过explain关键字来查看单表查询的访问方式。

    以上为个人经验,希望能给大家一个参考,也希望大家多多支持编程客栈(www.devze.com)。

    0

    上一篇:

    下一篇:

    精彩评论

    暂无评论...
    验证码 换一张
    取 消

    最新数据库

    数据库排行榜