mysql索引2

案例1:两个索引列条件，一个用于排序，一个用于检索,根据优化器选择只会用到一个索引

CREATE TABLE `t1` (
  `a` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c` int(10) unsigned NOT NULL,
  PRIMARY KEY (`a`),
  KEY `idx_b` (`b`),
  KEY `idx_c` (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

insert into t1(b,c) values(1,1),(1,1),(3,3),(4,4),(5,5),(6,6);

 查看索引信息

root@localhost:mysql.sock  19:11:51 [school]>show index from t1 \G;
*************************** 1. row ***************************
        Table: t1
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: a
    Collation: A
  Cardinality: 6
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: t1
   Non_unique: 1
     Key_name: idx_b
 Seq_in_index: 1
  Column_name: b
    Collation: A
  Cardinality: 6
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 3. row ***************************
        Table: t1
   Non_unique: 1
     Key_name: idx_c
 Seq_in_index: 1
  Column_name: c
    Collation: A
  Cardinality: 6
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
3 rows in set (0.00 sec)

ERROR: 
No query specified

如下查询语句只会用到b列上的索引

root@localhost:mysql.sock  19:13:36 [school]>desc select * from t1 where b = 1 order by c\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ref
possible_keys: idx_b
          key: idx_b
      key_len: 5
          ref: const
         rows: 1
        Extra: Using where; Using filesort
1 row in set (0.00 sec)

 案例2:两个独立索引列用and查询,这时可以用到index merege,这叫做交集.

如果b或者c上的值是唯一的,则只会用到一个索引,这也是mysql优化器的聪明之处. 

root@localhost:mysql.sock  19:23:05 [school]>desc select * from t1 where b = 1 and  c=1 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: index_merge
possible_keys: idx_b,idx_c
          key: idx_b,idx_c
      key_len: 5,4
          ref: NULL
         rows: 1
        Extra: Using intersect(idx_b,idx_c); Using where; Using index
1 row in set (0.00 sec)

 案例3:如下查询由于返回的结果集大于了总数据量的30%,优化器选择不走索引,变成全表扫描

root@localhost:mysql.sock  19:23:20 [school]>desc select * from t1 where b=3 or c=1 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ALL
possible_keys: idx_b,idx_c
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
        Extra: Using where
1 row in set (0.07 sec)

 案例4:对于上述的查询,如果想强制走索引,使用了index merge,叫做并集

root@localhost:mysql.sock  19:26:59 [school]>desc select * from t1 force index(idx_b,idx_c)  where b=3 or c=1 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: index_merge
possible_keys: idx_b,idx_c
          key: idx_b,idx_c
      key_len: 5,4
          ref: NULL
         rows: 3
        Extra: Using union(idx_b,idx_c); Using where
1 row in set (0.13 sec)

 案例5:大表的例子,关于索引选择

create table t2
(
 aid int UNSIGNED not null auto_increment PRIMARY key,
 terminalid varchar(255) not null,
 dttime datetime not null,
 id int not null,
 trantype int default null,
 paymentid int UNSIGNED not null default 0,
 key `id`(id),
 key `paymentid`(paymentid)
) engine=innodb ;

root@localhost:mysql.sock  19:57:13 [school]>select count(*) from t2;
+----------+
| count(*) |
+----------+
|        1500000 |
+----------+

root@localhost:mysql.sock  19:57:15 [school]>show index from t2 \G;
*************************** 1. row ***************************
        Table: t2
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: aid
    Collation: A
  Cardinality: 1500000
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: t2
   Non_unique: 1
     Key_name: id
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 500
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 3. row ***************************
        Table: t2
   Non_unique: 1
     Key_name: paymentid
 Seq_in_index: 1
  Column_name: paymentid
    Collation: A
  Cardinality: 80000
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
3 rows in set (0.10 sec)

因为paymentid的选择性高,所以只用到了索引paymentid
mysql>desc select * from t2 where id=200 and paymentid=700;
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | t2    | ref  | id,paymentid  | paymentid   | 4       | const |    1 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.02 sec)