MYSQL 8 優化之08 (使用SQL提示)

1. use index

root@127.0.0.1 : testdb【05:39:30】16 SQL->show index from city \G
*************************** 1. row ***************************
        Table: city
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: city_id
    Collation: A
  Cardinality: 14
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: city
   Non_unique: 1
     Key_name: idx_fk_country_id
 Seq_in_index: 1
  Column_name: country_id
    Collation: A
  Cardinality: 5
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES

root@127.0.0.1 : testdb【05:39:37】17 SQL->explain select count(*) from city use index(PRIMARY) \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: city
   partitions: NULL
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 14
     filtered: 100.00
        Extra: Using index

2. ignore index

root@127.0.0.1 : testdb【06:46:57】2 SQL->explain select count(*) from city \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: city
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_fk_country_id
      key_len: 4
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index

#可以看到如下 ignore index(idx_fk_country_id) 忽略了idx_fk_country_id 而使用了PRIMARY
root@127.0.0.1 : testdb【06:47:17】3 SQL->root@127.0.0.1 : testdb【06:47:17】3 SQL->explain select count(*) from city ignore index(idx_fk_country_id) \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: city
   partitions: NULL
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index

3 force index

root@127.0.0.1 : testdb【06:53:56】13 SQL->show index from mp_user \G
*************************** 1. row ***************************
        Table: mp_user
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: mp_user_seq
    Collation: A
  Cardinality: 4
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: mp_user
   Non_unique: 1
     Key_name: ind_mp_user_01
 Seq_in_index: 1
  Column_name: mp_user_name
    Collation: A
  Cardinality: 4
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL

#因为大部分mp_user_name 的值都大于 'a1'，因此MySQL 会默认进行全表扫描，而不使用索引
root@127.0.0.1 : testdb【06:58:02】24 SQL->explain select * from mp_user where mp_user_name >'a1' \G;

#尝试使用 use index 的hint 发现依然不行
root@127.0.0.1 : testdb【06:58:12】25 SQL->explain select * from mp_user use index(ind_mp_user_01)  where mp_user_name >'a1' \G;

#当使用force index 提示时，即使使用索引效率不高，MySQL 还是选择走索引
root@127.0.0.1 : testdb【06:59:13】26 SQL->explain select * from mp_user force index(ind_mp_user_01)  where mp_user_name >'a1' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: mp_user
   partitions: NULL
         type: range
possible_keys: ind_mp_user_01
          key: ind_mp_user_01
      key_len: 83
          ref: NULL
         rows: 300
     filtered: 100.00
        Extra: Using where; Using index