MySQL中InnoDB引擎如何对索引的扩展

Mysql中InnoDB引擎如何对索引的扩展，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。

InnoDB引擎对索引的扩展，自动追加主键值及其对执行计划的影响。

CREATE TABLE t1 (
  i1 INT 
NOT NULL DEFAULT 0,
  i2 INT 
NOT NULL DEFAULT 0,
  d DATE 
DEFAULT NULL,
  PRIMARY 
KEY (i1, i2),
  INDEX k_d (d)
) 
ENGINE = 
InnoDB;

root@database-one 15:15:  [gftest]> 
CREATE TABLE t1 (
    ->   i1 INT 
NOT NULL DEFAULT 0,
    ->   i2 INT 
NOT NULL DEFAULT 0,
    ->   d DATE 
DEFAULT NULL,
    ->   PRIMARY 
KEY (i1, i2),
    ->   
INDEX k_d (d)
    -> ) 
ENGINE = 
InnoDB;
Query OK, 0 rows affected (0.06 sec)

root@database-one 15:15:  [gftest]> 
INSERT INTO t1 
VALUES
    -> (1, 1, 
'1998-01-01'), (1, 2, 
'1999-01-01'),
    -> (1, 3, 
'2000-01-01'), (1, 4, 
'2001-01-01'),
    -> (1, 5, 
'2002-01-01'), (2, 1, 
'1998-01-01'),
    -> (2, 2, 
'1999-01-01'), (2, 3, 
'2000-01-01'),
    -> (2, 4, 
'2001-01-01'), (2, 5, 
'2002-01-01'),
    -> (3, 1, 
'1998-01-01'), (3, 2, 
'1999-01-01'),
    -> (3, 3, 
'2000-01-01'), (3, 4, 
'2001-01-01'),
    -> (3, 5, 
'2002-01-01'), (4, 1, 
'1998-01-01'),
    -> (4, 2, 
'1999-01-01'), (4, 3, 
'2000-01-01'),
    -> (4, 4, 
'2001-01-01'), (4, 5, 
'2002-01-01'),
    -> (5, 1, 
'1998-01-01'), (5, 2, 
'1999-01-01'),
    -> (5, 3, 
'2000-01-01'), (5, 4, 
'2001-01-01'),
    -> (5, 5, 
'2002-01-01');
Query OK, 25 rows affected (0.01 sec)
Records: 25  Duplicates: 0  Warnings: 0

root@database-one 15:21:  [gftest]> 
show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | 
Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1    |          0 | PRIMARY  |            1 | i1          | A         |           5 |     
NULL | 
NULL   |      | BTREE      |         |               |
| t1    |          0 | PRIMARY  |            2 | i2          | A         |          25 |     
NULL | 
NULL   |      | BTREE      |         |               |
| t1    |          1 | k_d      |            1 | d           | A         |           5 |     
NULL | 
NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 
rows in set (0.01 sec)

SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = ‘2000-01-01’

mysql> 
EXPLaiN SELECT COUNT(*) 
FROM t1 
WHERE i1 = 3 
AND d = 
'2000-01-01'\G
*************************** 1. 
row ***************************
           
id: 1
  select_type: SIMPLE
        table: t1
         
type: 
ref
possible_keys: PRIMARY,k_d
          key: k_d
      key_len: 4
          ref: const
         
rows: 5
        Extra: 
Using where; Using index

mysql> 
EXPLAIN SELECT COUNT(*) 
FROM t1 
WHERE i1 = 3 
AND d = 
'2000-01-01'\G
*************************** 1. 
row ***************************
           
id: 1
  select_type: SIMPLE
        table: t1
         
type: 
ref
possible_keys: PRIMARY,k_d
          key: k_d
      key_len: 8
          ref: const,const
         
rows: 1
        Extra: 
Using index

root@database-one 15:35:  [gftest]> 
EXPLAIN SELECT COUNT(*) 
FROM t1 
WHERE i1 = 3 
AND d = 
'2000-01-01'\G
*************************** 1. 
row ***************************
           
id: 1
  select_type: SIMPLE
        table: t1
   
partitions: 
NULL
         
type: 
ref
possible_keys: PRIMARY,k_d
          key: k_d
      key_len: 8
          ref: const,const
         
rows: 1
     filtered: 100.00
        Extra: 
Using index
1 
row in set, 1 
warning (0.01 sec)

• key_len从4字节变为8字节，表明键查找使用列d和i1，而不仅仅是d。

• ref从const更改为const，const，表明查找使用两个键值，而不是一个。

• rows从5减少到1，表明检索更少的行。

• Extra从Using where; Using index改为Using index，表示只用索引读取，不必回表。
  

root@database-one 16:07:  [gftest]> 
CREATE TABLE t1MyISAM (
    ->   i1 INT 
NOT NULL DEFAULT 0,
    ->   i2 INT 
NOT NULL DEFAULT 0,
    ->   d DATE 
DEFAULT NULL,
    ->   PRIMARY 
KEY (i1, i2),
    ->   
INDEX k_d (d)
    -> ) 
ENGINE = MyISAM;
Query OK, 0 rows affected (0.01 sec)

root@database-one 16:07:  [gftest]> 
INSERT INTO t1myisam 
VALUES
    -> (1, 1, 
'1998-01-01'), (1, 2, 
'1999-01-01'),
    -> (1, 3, 
'2000-01-01'), (1, 4, 
'2001-01-01'),
    -> (1, 5, 
'2002-01-01'), (2, 1, 
'1998-01-01'),
    -> (2, 2, 
'1999-01-01'), (2, 3, 
'2000-01-01'),
    -> (2, 4, 
'2001-01-01'), (2, 5, 
'2002-01-01'),
    -> (3, 1, 
'1998-01-01'), (3, 2, 
'1999-01-01'),
    -> (3, 3, 
'2000-01-01'), (3, 4, 
'2001-01-01'),
    -> (3, 5, 
'2002-01-01'), (4, 1, 
'1998-01-01'),
    -> (4, 2, 
'1999-01-01'), (4, 3, 
'2000-01-01'),
    -> (4, 4, 
'2001-01-01'), (4, 5, 
'2002-01-01'),
    -> (5, 1, 
'1998-01-01'), (5, 2, 
'1999-01-01'),
    -> (5, 3, 
'2000-01-01'), (5, 4, 
'2001-01-01'),
    -> (5, 5, 
'2002-01-01');
Query OK, 25 rows affected (0.02 sec)
Records: 25  Duplicates: 0  Warnings: 0

root@database-one 16:07:  [gftest]> 
EXPLAIN SELECT COUNT(*) 
FROM t1myisam 
WHERE i1 = 3 
AND d = 
'2000-01-01'\G
*************************** 1. 
row ***************************
           
id: 1
  select_type: SIMPLE
        table: t1myisam
   
partitions: 
NULL
         
type: 
ref
possible_keys: PRIMARY,k_d
          key: PRIMARY
      key_len: 4
          ref: const
         
rows: 4
     filtered: 16.00
        Extra: 
Using where
1 
row in set, 1 
warning (0.01 sec)

root@database-one 16:12:  [gftest]> 
FLUSH TABLE t1;
Query OK, 0 rows affected (0.00 sec)

root@database-one 16:12:  [gftest]> 
FLUSH STATUS;
Query OK, 0 rows affected (0.14 sec)

root@database-one 16:12:  [gftest]> 
SELECT COUNT(*) 
FROM t1 
WHERE i1 = 3 
AND d = 
'2000-01-01';
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in 
set (0.03 sec)

root@database-one 16:12:  [gftest]> 
SHOW STATUS LIKE 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 1     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+
7 rows in 
set (0.01 sec)

root@database-one 16:13:  [gftest]> 
FLUSH TABLE t1myisam;
Query OK, 0 rows affected (0.01 sec)

root@database-one 16:13:  [gftest]> 
FLUSH STATUS;
Query OK, 0 rows affected (0.00 sec)

root@database-one 16:13:  [gftest]> 
SELECT COUNT(*) 
FROM t1myisam 
WHERE i1 = 3 
AND d = 
'2000-01-01';
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in 
set (0.01 sec)

root@database-one 16:13:  [gftest]> 
SHOW STATUS LIKE 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 5     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+
7 rows in 
set (0.00 sec)

root@database-one 16:26:  [gftest]> 
SET optimizer_switch = 
'use_index_extensions=off';
Query OK, 0 rows affected (0.01 sec)

root@database-one 16:26:  [gftest]> 
EXPLAIN SELECT COUNT(*) 
FROM t1 
WHERE i1 = 3 
AND d = 
'2000-01-01'\G
*************************** 1. 
row ***************************
           
id: 1
  select_type: SIMPLE
        table: t1
   
partitions: 
NULL
         
type: 
ref
possible_keys: PRIMARY,k_d
          key: PRIMARY
      key_len: 4
          ref: const
         
rows: 5
     filtered: 20.00
        Extra: 
Using where
1 
row in set, 1 
warning (0.02 sec)

看完上述内容，你们掌握MySQL中InnoDB引擎如何对索引的扩展的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注编程网数据库频道，感谢各位的阅读！