foghost / foghost.github.io Goto Github PK

mysql> show create table employ\G;
*************************** 1. row ***************************
       Table: employ
Create Table: CREATE TABLE `employ` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `email` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8

// 通过Clustered Index（主键）查询
mysql> explain select * from employ where id=1\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employ
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
        Extra: NULL
1 row in set (0.00 sec)

// 通过Secondary Index（副键）查询
mysql> explain select * from employ where name='aa'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employ
         type: ref
possible_keys: name
          key: name
      key_len: 152
          ref: const
         rows: 1
        Extra: Using index condition
1 row in set (0.00 sec)


// 通过Covering Index来查询（Extra: Using index)
// name是Seconary Index的Key，
// id是主键存储在Secondary Index的叶子节点上
// 所以只需要一次索引查询就能取到实际数据
mysql> explain select id,name from employ where name='aa'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employ
         type: ref
possible_keys: name
          key: name
      key_len: 152
          ref: const
         rows: 1
        Extra: Using where; Using index
1 row in set (0.00 sec)