explain查看索引使用

最新推荐文章于 2023-06-15 23:30:31 发布

原创最新推荐文章于 2023-06-15 23:30:31 发布 · 1.2k 阅读

1 ·

CC 4.0 BY-SA版权

MySQL 专栏收录该内容

10 篇文章

订阅专栏

本文深入探讨了MySQL中联合索引的使用方式，通过具体的SQL语句解释了如何创建和利用联合索引，以及在不同查询条件下的索引使用情况，为优化数据库查询效率提供了实用指导。

CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `dep_id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `tt` char(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ix` (`name`,`dep_id`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

创建了联合索引：KEY ix (name,dep_id,age)

在这里插入图片描述

explain select * from test where name = ‘qwe’ and dep_id = 1 and age = 9\G;
使用了全部

mysql> explain select * from test where name = 'qwe' and dep_id = 1 and  age = 9\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: NULL
         type: ref
possible_keys: ix
          key: ix
      key_len: 73
          ref: const,const,const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

使用了2个
explain select * from test where name = ‘qwe’ and dep_id = 1\G;

mysql> explain select * from test where name = 'qwe' and dep_id = 1\G; 
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: NULL
         type: ref
possible_keys: ix
          key: ix
      key_len: 68
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

使用了1个
explain select * from test where name = ‘qwe’ and age = 9\G;

mysql> explain select * from test where name = 'qwe'  and age = 9\G; 
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: NULL
         type: ref
possible_keys: ix
          key: ix
      key_len: 63
          ref: const
         rows: 1
     filtered: 33.33
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

没有使用
explain select * from test where dep_id = 1 and age = 9\G;

mysql> explain select * from test where dep_id = 1 and age = 9\G; 
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 33.33
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

explain select * from test where name = ‘qwe’\G; 使用了1个

mysql> explain select * from test where name = 'qwe'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: NULL
         type: ref
possible_keys: ix
          key: ix
      key_len: 63
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

explain select * from test where dep_id = 1 and name = ‘qqw’\G; 优化器会调整顺序，使用了2个

mysql> explain select * from test where dep_id = 1 and name = 'qqw'\G; 
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: NULL
         type: ref
possible_keys: ix
          key: ix
      key_len: 68
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.01 sec)

**explain select * from test where dep_id = 1\G;**没有使用

mysql> explain select * from test where dep_id = 1\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 33.33
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

explain select * from test where age = 1 and name = ‘qqw’\G;调整顺序，使用了1个

mysql> explain select * from test where age = 1 and name = 'qqw'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: NULL
         type: ref
possible_keys: ix
          key: ix
      key_len: 63
          ref: const
         rows: 1
     filtered: 33.33
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)