创建索引

官方文档：

mysql> show create table test_index_1\G * 1. row * Table: test_index_1 Create Table: CREATE TABLE test_index_1 ( a int(11) DEFAULT NULL, b int(11) DEFAULT NULL, c int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec)

mysql> insert into test_index_1 values -> (1,10,100),(2,20,200), -> (3,30,300),(4,40,400); Query OK, 4 rows affected (0.03 sec) Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from test_index_1; +———+———+———+ | a | b | c | +———+———+———+ | 1 | 10 | 100 | | 2 | 20 | 200 | | 3 | 30 | 300 | | 4 | 40 | 400 | +———+———+———+ 4 rows in set (0.00 sec)

mysql> explain select from test_index_1 where a=3\G — 看执行计划，使用的是扫描整张表的方式 ** 1. row * id: 1 select_type: SIMPLE table: test_index_1 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4 filtered: 25.00 Extra: Using where 1 row in set, 1 warning (0.00 sec)

— 给字段a 增加索引 mysql> alter table test_index_1 add index idx_a (a); — 给字段a添加索引。索引名为idx_a Query OK, 0 rows affected (0.15 sec) Records: 0 Duplicates: 0 Warnings: 0

mysql> explain select from test_index_1 where a=3\G — 看执行计划，使用的key为idx_a，走了索引 ** 1. row * id: 1 select_type: SIMPLE table: test_index_1 partitions: NULL type: ref possible_keys: idx_a key: idx_a key_len: 5 ref: const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec)

— 使用create index mysql> explain select from test_index_1 where b=30\G — 同样b字段也没有索引 ** 1. row * id: 1 select_type: SIMPLE table: test_index_1 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4 filtered: 25.00 Extra: Using where 1 row in set, 1 warning (0.00 sec)

— 给b字段增加索引 mysql> create index idx_b on test_index_1 (b); Query OK, 0 rows affected (0.14 sec) Records: 0 Duplicates: 0 Warnings: 0

mysql> explain select from test_index_1 where b=30\G — 查看执行计划，使用的key为idx_b，走了索引 ** 1. row * id: 1 select_type: SIMPLE table: test_index_1 partitions: NULL type: ref possible_keys: idx_b key: idx_b key_len: 5 ref: const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec)

  1. <a name="46MHh"></a>
  2. ### 查看索引
  3. ```sql
  4. --
  5. -- 方式一
  6. --
  7. mysql> desc orders;
  8. +-----------------+-------------+------+-----+---------+-------+
  9. | Field           | Type        | Null | Key | Default | Extra |
  10. +-----------------+-------------+------+-----+---------+-------+
  11. | o_orderkey      | int(11)     | NO   | PRI | NULL    |       | -- 索引
  12. | o_custkey       | int(11)     | YES  | MUL | NULL    |       | -- 索引
  13. | o_orderstatus   | char(1)     | YES  |     | NULL    |       |
  14. | o_totalprice    | double      | YES  |     | NULL    |       |
  15. | o_orderDATE     | date        | YES  | MUL | NULL    |       | -- 索引
  16. | o_orderpriority | char(15)    | YES  |     | NULL    |       |
  17. | o_clerk         | char(15)    | YES  |     | NULL    |       |
  18. | o_shippriority  | int(11)     | YES  |     | NULL    |       |
  19. | o_comment       | varchar(79) | YES  |     | NULL    |       |
  20. +-----------------+-------------+------+-----+---------+-------+
  21. 9 rows in set (0.00 sec)
  23. --
  24. -- 方式二
  25. --
  26. mysql> show create table orders\G
  27. *************************** 1. row ***************************
  28.        Table: orders
  29. Create Table: CREATE TABLE `orders` (
  30.   `o_orderkey` int(11) NOT NULL,
  31.   `o_custkey` int(11) DEFAULT NULL,
  32.   `o_orderstatus` char(1) DEFAULT NULL,
  33.   `o_totalprice` double DEFAULT NULL,
  34.   `o_orderDATE` date DEFAULT NULL,
  35.   `o_orderpriority` char(15) DEFAULT NULL,
  36.   `o_clerk` char(15) DEFAULT NULL,
  37.   `o_shippriority` int(11) DEFAULT NULL,
  38.   `o_comment` varchar(79) DEFAULT NULL,
  39.   PRIMARY KEY (`o_orderkey`),  -- 索引
  40.   KEY `i_o_orderdate` (`o_orderDATE`), -- 索引
  41.   KEY `i_o_custkey` (`o_custkey`),  -- 索引
  42.   CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`o_custkey`) REFERENCES `customer` (`c_custkey`)
  43. ) ENGINE=InnoDB DEFAULT CHARSET=latin1
  44. 1 row in set (0.00 sec)
  46. --
  47. -- 方式三
  48. --
  49. mysql> show index from orders\G
  50. *************************** 1. row ***************************
  51.         Table: orders
  52.    Non_unique: 0        -- 表示唯一的 
  53.      Key_name: PRIMARY  -- key的name是primary
  54.  Seq_in_index: 1
  55.   Column_name: o_orderkey
  56.     Collation: A
  57.   Cardinality: 1306748  -- 基数，这个列上不同值的记录数
  58.      Sub_part: NULL
  59.        Packed: NULL
  60.          Null: 
  61.    Index_type: BTREE    -- 索引类型是BTree
  62.       Comment: 
  63. Index_comment: 
  64. *************************** 2. row ***************************
  65.         Table: orders
  66.    Non_unique: 1       -- Non_unique为True，表示不唯一
  67.      Key_name: i_o_orderdate
  68.  Seq_in_index: 1
  69.   Column_name: o_orderDATE
  70.     Collation: A
  71.   Cardinality: 2405
  72.      Sub_part: NULL
  73.        Packed: NULL
  74.          Null: YES
  75.    Index_type: BTREE
  76.       Comment: 
  77. Index_comment: 
  78. *************************** 3. row ***************************
  79.         Table: orders
  80.    Non_unique: 1
  81.      Key_name: i_o_custkey
  82.  Seq_in_index: 1
  83.   Column_name: o_custkey
  84.     Collation: A
  85.   Cardinality: 95217
  86.      Sub_part: NULL
  87.        Packed: NULL
  88.          Null: YES
  89.    Index_type: BTREE
  90.       Comment: 
  91. Index_comment: 
  92. 3 rows in set (0.00 sec)
  94. mysql>  select count(*) from orders; 
  95. +----------+
  96. | count(*) |
  97. +----------+
  98. |  1500000 |  -- orders中有150W条记录，和Cardinality 是不一致的
  99. +----------+
  100. 1 row in set (0.25 sec)

Cardinality（基数）

Cardinality 表示该索引列上有多少不同的记录，这个是一个预估的值，是采样得到的（由 InnoDB 触发，采样20个页，进行预估），该值越大越好，即当 Cardinality / RowNumber 越接近1越好，表示该列是高选择性的。

  • 高选择性：身份证 、手机号码、姓名、订单号等。
  • 低选择性：性别、年龄等。

即该列是否适合创建索引，就看该字段是否具有高选择性。

  1. mysql>  show create table lineitem\G
  2. *************************** 1. row ***************************
  3.        Table: lineitem
  4. Create Table: CREATE TABLE `lineitem` (
  5.   `l_orderkey` int(11) NOT NULL,
  6.   `l_partkey` int(11) DEFAULT NULL,
  7.   `l_suppkey` int(11) DEFAULT NULL,
  8.   `l_linenumber` int(11) NOT NULL,
  9.   `l_quantity` double DEFAULT NULL,
  10.   `l_extendedprice` double DEFAULT NULL,
  11.   `l_discount` double DEFAULT NULL,
  12.   `l_tax` double DEFAULT NULL,
  13.   `l_returnflag` char(1) DEFAULT NULL,
  14.   `l_linestatus` char(1) DEFAULT NULL,
  15.   `l_shipDATE` date DEFAULT NULL,
  16.   `l_commitDATE` date DEFAULT NULL,
  17.   `l_receiptDATE` date DEFAULT NULL,
  18.   `l_shipinstruct` char(25) DEFAULT NULL,
  19.   `l_shipmode` char(10) DEFAULT NULL,
  20.   `l_comment` varchar(44) DEFAULT NULL,
  21.   PRIMARY KEY (`l_orderkey`,`l_linenumber`),  -- 两个列作为primary
  22.   KEY `i_l_shipdate` (`l_shipDATE`),
  23.   KEY `i_l_suppkey_partkey` (`l_partkey`,`l_suppkey`),
  24.   KEY `i_l_partkey` (`l_partkey`),
  25.   KEY `i_l_suppkey` (`l_suppkey`),
  26.   KEY `i_l_receiptdate` (`l_receiptDATE`),
  27.   KEY `i_l_orderkey` (`l_orderkey`),
  28.   KEY `i_l_orderkey_quantity` (`l_orderkey`,`l_quantity`),
  29.   KEY `i_l_commitdate` (`l_commitDATE`),
  30.   CONSTRAINT `lineitem_ibfk_1` FOREIGN KEY (`l_orderkey`) REFERENCES `orders` (`o_orderkey`),
  31.   CONSTRAINT `lineitem_ibfk_2` FOREIGN KEY (`l_partkey`, `l_suppkey`) REFERENCES `partsupp` (`ps_partkey`, `ps_suppkey`)
  32. ) ENGINE=InnoDB DEFAULT CHARSET=latin1
  33. 1 row in set (0.00 sec)
  35. mysql>  show index from lineitem\G  -- 省略其他输出，只看PRIMARY
  36. *************************** 1. row ***************************
  37.         Table: lineitem
  38.    Non_unique: 0
  39.      Key_name: PRIMARY
  40.  Seq_in_index: 1  -- 索引中的顺序，该列的顺序为1
  41.   Column_name: l_orderkey
  42.     Collation: A
  43.   Cardinality: 1416486  -- 约140W
  44.      Sub_part: NULL
  45.        Packed: NULL
  46.          Null: 
  47.    Index_type: BTREE
  48.       Comment: 
  49. Index_comment: 
  50. *************************** 2. row ***************************
  51.         Table: lineitem
  52.    Non_unique: 0
  53.      Key_name: PRIMARY
  54.  Seq_in_index: 2  -- 索引中的顺序，该列的顺序为2
  55.   Column_name: l_linenumber
  56.     Collation: A
  57.   Cardinality: 5882116  -- 约580W
  58.      Sub_part: NULL
  59.        Packed: NULL
  60.          Null: 
  61.    Index_type: BTREE
  62.       Comment: 
  63. Index_comment:

对应当前例子：

  • 第一个索引（Seq_in_index = 1）的 Cardinality 的值表示当前列（l_orderkey）的不重复的值。
  • 第二个索引（Seq_in_index = 2）的 Cardinality 的值表示前两列（l_orderkey）和（l_linenumber）不重复的值。

    ```sql

    — SQL-1

    mysql> select * from lineitem limit 10;
    +——————+—————-+—————-+———————+——————+————————-+——————+———-+———————+———————+——————+———————+———————-+—————————-+——————+————————————————————+ | l_orderkey | l_partkey | l_suppkey | l_linenumber | l_quantity | l_extendedprice | l_discount | l_tax | l_returnflag | l_linestatus | l_shipDATE | l_commitDATE | l_receiptDATE | l_shipinstruct | l_shipmode | l_comment | +——————+—————-+—————-+———————+——————+————————-+——————+———-+———————+———————+——————+———————+———————-+—————————-+——————+————————————————————+ | 1 | 155190 | 7706 | 1 | 17 | 21168.23 | 0.04 | 0.02 | N | O | 1996-03-13 | 1996-02-12 | 1996-03-22 | DELIVER IN PERSON | TRUCK | blithely regular ideas caj | | 1 | 67310 | 7311 | 2 | 36 | 45983.16 | 0.09 | 0.06 | N | O | 1996-04-12 | 1996-02-28 | 1996-04-20 | TAKE BACK RETURN | MAIL | slyly bold pinto beans detect s | | 1 | 63700 | 3701 | 3 | 8 | 13309.6 | 0.1 | 0.02 | N | O | 1996-01-29 | 1996-03-05 | 1996-01-31 | TAKE BACK RETURN | REG AIR | deposits wake furiously dogged, | | 1 | 2132 | 4633 | 4 | 28 | 28955.64 | 0.09 | 0.06 | N | O | 1996-04-21 | 1996-03-30 | 1996-05-16 | NONE | AIR | even ideas haggle. even, bold reque | | 1 | 24027 | 1534 | 5 | 24 | 22824.48 | 0.1 | 0.04 | N | O | 1996-03-30 | 1996-03-14 | 1996-04-01 | NONE | FOB | carefully final gr | | 1 | 15635 | 638 | 6 | 32 | 49620.16 | 0.07 | 0.02 | N | O | 1996-01-30 | 1996-02-07 | 1996-02-03 | DELIVER IN PERSON | MAIL | furiously regular accounts haggle bl | | 2 | 106170 | 1191 | 1 | 38 | 44694.46 | 0 | 0.05 | N | O | 1997-01-28 | 1997-01-14 | 1997-02-02 | TAKE BACK RETURN | RAIL | carefully ironic platelets against t | | 3 | 4297 | 1798 | 1 | 45 | 54058.05 | 0.06 | 0 | R | F | 1994-02-02 | 1994-01-04 | 1994-02-23 | NONE | AIR | blithely s | | 3 | 19036 | 6540 | 2 | 49 | 46796.47 | 0.1 | 0 | R | F | 1993-11-09 | 1993-12-20 | 1993-11-24 | TAKE BACK RETURN | RAIL | final, regular pinto | | 3 | 128449 | 3474 | 3 | 27 | 39890.88 | 0.06 | 0.07 | A | F | 1994-01-16 | 1993-11-22 | 1994-01-23 | DELIVER IN PERSON | SHIP | carefully silent pinto beans boost fur | +——————+—————-+—————-+———————+——————+————————-+——————+———-+———————+———————+——————+———————+———————-+—————————-+——————+————————————————————+ 10 rows in set (0.00 sec)

—

— SQL-2

mysql> select l_orderkey, l_linenumber from lineitem limit 10; +——————+———————+ | l_orderkey | l_linenumber | +——————+———————+ | 721220 | 2 | | 842980 | 4 | | 904677 | 1 | | 990147 | 1 | | 1054181 | 1 | | 1111877 | 3 | | 1332613 | 1 | | 1552449 | 2 | | 2167527 | 3 | | 2184032 | 5 | +——————+———————+ 10 rows in set (0.00 sec)

—- SQL-1 和 SQL-2 其实都是在没有排序的情况下，取出前10条数据。但是结果不一样

—

— SQL-3

mysql> select l_orderkey, l_linenumber from lineitem order by l_orderkey limit 10; — 和上面的sql相比，多了一个order by的操作 +——————+———————+ | l_orderkey | l_linenumber | +——————+———————+ | 1 | 1 | ——- | 1 | 2 | — 看orderkey为1，对应的linenumber有6条 | 1 | 3 | — 这就是orderkey的Cardinality仅为140W | 1 | 4 | — 而(orderkey + linenumber)就有580W | 1 | 5 | | 1 | 6 | ——- | 2 | 1 | | 3 | 1 | | 3 | 2 | | 3 | 3 | +——————+———————+ 10 rows in set (0.01 sec)

—- SQL-3 和 SQL-2 不同的原因是 他们走了不同的索引 mysql> explain select l_orderkey, l_linenumber from lineitem limit 10\G * 1. row * id: 1 select_type: SIMPLE table: lineitem partitions: NULL type: index possible_keys: NULL key: i_l_shipdate — 使用了shipdate进行了索引 key_len: 4 ref: NULL rows: 5882306 filtered: 100.00 Extra: Using index 1 row in set, 1 warning (0.00 sec)

mysql> explain select l_orderkey, l_linenumber from lineitem order by l_orderkey limit 10\G * 1. row * id: 1 select_type: SIMPLE table: lineitem partitions: NULL type: index possible_keys: NULL key: i_l_orderkey — 使用了orderkey进行了查询 key_len: 4 ref: NULL rows: 10 filtered: 100.00 Extra: Using index 1 row in set, 1 warning (0.00 sec)

mysql> explain select from lineitem limit 10\G ** 1. row * id: 1 select_type: SIMPLE table: lineitem partitions: NULL type: ALL — SQL-1进行了全表扫描 possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 5882306 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec)

— 所以，不使用order by取出的结果，可以理解为不是根据主键排序的结果。

  1. 在 MySQL5.5 之前，执行 show create table 操作会触发采样，而 5.5 之后将该参数（`innodb_stats_on_metadata = off`） off 后，需要主动执行 analyze table 才会去采样，采样不会锁表或者锁记录。
  3. <a name="K9PoR"></a>
  4. ### 复合索引
  5. ```sql
  6. mysql>  show create table lineitem\G
  7. *************************** 1. row ***************************
  8.        Table: lineitem
  9. Create Table: CREATE TABLE `lineitem` (
  10.   `l_orderkey` int(11) NOT NULL,
  11.   `l_partkey` int(11) DEFAULT NULL,
  12.   `l_suppkey` int(11) DEFAULT NULL,
  13.   `l_linenumber` int(11) NOT NULL,
  14.   `l_quantity` double DEFAULT NULL,
  15.   `l_extendedprice` double DEFAULT NULL,
  16.   `l_discount` double DEFAULT NULL,
  17.   `l_tax` double DEFAULT NULL,
  18.   `l_returnflag` char(1) DEFAULT NULL,
  19.   `l_linestatus` char(1) DEFAULT NULL,
  20.   `l_shipDATE` date DEFAULT NULL,
  21.   `l_commitDATE` date DEFAULT NULL,
  22.   `l_receiptDATE` date DEFAULT NULL,
  23.   `l_shipinstruct` char(25) DEFAULT NULL,
  24.   `l_shipmode` char(10) DEFAULT NULL,
  25.   `l_comment` varchar(44) DEFAULT NULL,
  26.   PRIMARY KEY (`l_orderkey`,`l_linenumber`),  -- 两个列作为primary，这个就是复合索引
  27.   KEY `i_l_shipdate` (`l_shipDATE`),
  28.   KEY `i_l_suppkey_partkey` (`l_partkey`,`l_suppkey`),
  29.   KEY `i_l_partkey` (`l_partkey`),
  30.   KEY `i_l_suppkey` (`l_suppkey`),
  31.   KEY `i_l_receiptdate` (`l_receiptDATE`),
  32.   KEY `i_l_orderkey` (`l_orderkey`),
  33.   KEY `i_l_orderkey_quantity` (`l_orderkey`,`l_quantity`),
  34.   KEY `i_l_commitdate` (`l_commitDATE`),
  35.   CONSTRAINT `lineitem_ibfk_1` FOREIGN KEY (`l_orderkey`) REFERENCES `orders` (`o_orderkey`),
  36.   CONSTRAINT `lineitem_ibfk_2` FOREIGN KEY (`l_partkey`, `l_suppkey`) REFERENCES `partsupp` (`ps_partkey`, `ps_suppkey`)
  37. ) ENGINE=InnoDB DEFAULT CHARSET=latin1
  38. 1 row in set (0.00 sec)
  40. --
  41. -- 复合索引举例
  42. -- 
  43. mysql> create table test_index_2(a int, b int , c int);
  44. Query OK, 0 rows affected (0.15 sec)
  46. mysql> alter table test_index_2 add index  idx_mul_ab (a, b);        
  47. Query OK, 0 rows affected (0.11 sec)
  48. Records: 0  Duplicates: 0  Warnings: 0
  50. mysql> insert into test_index_2 values
  51.     -> (1,1,10),
  52.     -> (1,2,9),
  53.     -> (2,1,8),
  54.     -> (2,4,15),
  55.     -> (3,1,6),
  56.     -> (3,2,17);
  57. Query OK, 6 rows affected (0.04 sec)
  58. Records: 6  Duplicates: 0  Warnings: 0
  60. mysql> select * from test_index_2 where a = 1;
  61. +------+------+------+
  62. | a    | b    | c    |
  63. +------+------+------+
  64. |    1 |    1 |   10 |
  65. |    1 |    2 |    9 |
  66. +------+------+------+
  67. 2 rows in set (0.00 sec)
  69. mysql> explain select * from test_index_2 where a = 1\G
  70. *************************** 1. row ***************************
  71.            id: 1
  72.   select_type: SIMPLE
  73.         table: test_index_2
  74.    partitions: NULL
  75.          type: ref
  76. possible_keys: idx_mul_ab
  77.           key: idx_mul_ab   -- 走了索引
  78.       key_len: 5
  79.           ref: const
  80.          rows: 2
  81.      filtered: 100.00
  82.         Extra: NULL
  83. 1 row in set, 1 warning (0.00 sec)
  85. mysql> select * from test_index_2 where a = 1 and b = 2;
  86. +------+------+------+
  87. | a    | b    | c    |
  88. +------+------+------+
  89. |    1 |    2 |    9 |
  90. +------+------+------+
  91. 1 row in set (0.00 sec)
  93. mysql> explain select * from test_index_2 where a = 1 and b = 2\G
  94. *************************** 1. row ***************************
  95.            id: 1
  96.   select_type: SIMPLE
  97.         table: test_index_2
  98.    partitions: NULL
  99.          type: ref   -- 此时也是走了索引
  100. possible_keys: idx_mul_ab
  101.           key: idx_mul_ab  
  102.       key_len: 10
  103.           ref: const,const
  104.          rows: 1
  105.      filtered: 100.00
  106.         Extra: NULL
  107. 1 row in set, 1 warning (0.00 sec)
  109. mysql> explain select * from test_index_2 where b = 2\G  -- 只查询b          
  110. *************************** 1. row ***************************
  111.            id: 1
  112.   select_type: SIMPLE
  113.         table: test_index_2
  114.    partitions: NULL
  115.          type: ALL  -- 没有使用索引
  116. possible_keys: NULL  
  117.           key: NULL
  118.       key_len: NULL
  119.           ref: NULL
  120.          rows: 6
  121.      filtered: 16.67
  122.         Extra: Using where
  123. 1 row in set, 1 warning (0.00 sec)
  126. mysql> explain select * from test_index_2 where a=1 or b = 2\G  -- 使用or，要求结果集是并集
  127. *************************** 1. row ***************************
  128.            id: 1
  129.   select_type: SIMPLE
  130.         table: test_index_2
  131.    partitions: NULL
  132.          type: ALL  -- 没有使用索引，因为b没有索引，所以b是走全表扫描的，既然走扫描，a的值也可以一起过滤
  133.                     -- 就没有必要在去查一次 a 的索引了
  134. possible_keys: idx_mul_ab
  135.           key: NULL
  136.       key_len: NULL
  137.           ref: NULL
  138.          rows: 6
  139.      filtered: 30.56
  140.         Extra: Using where
  141. 1 row in set, 1 warning (0.00 sec)
  143. --
  144. -- 特别的例子
  145. --
  146. ---- 还是只使用b列去做范围查询，发现是走索引了
  147. ---- 注意查询的是 count(*)
  148. mysql> explain select count(*) from test_index_2 where  b >1 and b < 3\G 
  149. *************************** 1. row ***************************
  150.            id: 1
  151.   select_type: SIMPLE
  152.         table: test_index_2
  153.    partitions: NULL
  154.          type: index  -- 走了索引 
  155. possible_keys: NULL
  156.           key: idx_mul_ab
  157.       key_len: 10
  158.           ref: NULL
  159.          rows: 6
  160.      filtered: 16.67
  161.         Extra: Using where; Using index  -- 覆盖索引
  162. 1 row in set, 1 warning (0.00 sec)
  164. -- 因为要求的是count(*), 要求所有的记录的和，
  165. -- 那索引a是包含了全部的记录的，即扫描（a,b)的索引也是可以得到count(*)的
  167. mysql> explain select * from test_index_2 where  b >1 and b < 3\G        
  168. *************************** 1. row ***************************
  169.            id: 1
  170.   select_type: SIMPLE
  171.         table: test_index_2
  172.    partitions: NULL
  173.          type: ALL  -- 查询 * 就没法使用（a，b）索引了，需要全表扫描b的值。
  174. possible_keys: NULL
  175.           key: NULL
  176.       key_len: NULL
  177.           ref: NULL
  178.          rows: 6
  179.      filtered: 16.67
  180.         Extra: Using where
  181. 1 row in set, 1 warning (0.00 sec)
  184. mysql> explain select * from test_index_2 where  a = 1 and c = 10\G
  185. *************************** 1. row ***************************
  186.            id: 1
  187.   select_type: SIMPLE
  188.         table: test_index_2
  189.    partitions: NULL
  190.          type: ref  -- 也是走索引的，先用走a索引得到结果集，再用c=10去过滤
  191. possible_keys: idx_mul_ab
  192.           key: idx_mul_ab
  193.       key_len: 5
  194.           ref: const
  195.          rows: 2
  196.      filtered: 16.67
  197.         Extra: Using where
  198. 1 row in set, 1 warning (0.00 sec)
  200. mysql> explain select * from test_index_2 where  b = 2 and c = 10\G   
  201. *************************** 1. row ***************************
  202.            id: 1
  203.   select_type: SIMPLE
  204.         table: test_index_2
  205.    partitions: NULL
  206.          type: ALL  -- 而b和c是不行的，(b，c)不是有序的
  207. possible_keys: NULL
  208.           key: NULL
  209.       key_len: NULL
  210.           ref: NULL
  211.          rows: 6
  212.      filtered: 16.67
  213.         Extra: Using where
  214. 1 row in set, 1 warning (0.00 sec)

FORCE INDEX & IGNORE INDEX

MySQL 可以使用 FORCE INDEX 和 IGNORE INDEX 命令来强制或者禁止使用某个索引。

  1. -- 没使用 force index 和 ignore index 命令，默认使用 primary 索引
  2. mysql> explain select * from dept_emp where emp_no='10003';
  3. +----+-------------+----------+------------+------+----------------+---------+---------+-------+------+----------+-------+
  4. | id | select_type | table    | partitions | type | possible_keys  | key     | key_len | ref   | rows | filtered | Extra |
  5. +----+-------------+----------+------------+------+----------------+---------+---------+-------+------+----------+-------+
  6. |  1 | SIMPLE      | dept_emp | NULL       | ref  | PRIMARY,emp_no | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
  7. +----+-------------+----------+------------+------+----------------+---------+---------+-------+------+----------+-------+
  8. 1 row in set, 1 warning (0.00 sec)
  10. -- 强制使用 emp_no 索引
  11. mysql> explain select * from dept_emp force index(emp_no) where emp_no='10003';
  12. +----+-------------+----------+------------+------+---------------+--------+---------+-------+------+----------+-------+
  13. | id | select_type | table    | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra |
  14. +----+-------------+----------+------------+------+---------------+--------+---------+-------+------+----------+-------+
  15. |  1 | SIMPLE      | dept_emp | NULL       | ref  | emp_no        | emp_no | 4       | const |    1 |   100.00 | NULL  |
  16. +----+-------------+----------+------------+------+---------------+--------+---------+-------+------+----------+-------+
  17. 1 row in set, 1 warning (0.00 sec)
  19. -- 禁止使用 primary 索引
  20. mysql> explain select * from dept_emp ignore index(primary) where emp_no='10003';
  21. +----+-------------+----------+------------+------+---------------+--------+---------+-------+------+----------+-------+
  22. | id | select_type | table    | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra |
  23. +----+-------------+----------+------------+------+---------------+--------+---------+-------+------+----------+-------+
  24. |  1 | SIMPLE      | dept_emp | NULL       | ref  | emp_no        | emp_no | 4       | const |    1 |   100.00 | NULL  |
  25. +----+-------------+----------+------------+------+---------------+--------+---------+-------+------+----------+-------+
  26. 1 row in set, 1 warning (0.00 sec)

官方文档：https://dev.mysql.com/doc/refman/8.0/en/index-hints.html

MySQL 的官方文档指出这两个命令以后会被弃用。
image.png

作者：殷建卫 链接：https://www.yuque.com/yinjianwei/vyrvkf/yozvqv 来源：殷建卫 - 架构笔记 著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。