MySQL 索引失效几种情况实战

1 条件中使用or

| idnameageindex | CREATE TABLE `idnameageindex` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  KEY `idx_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------------+--------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------+
1 row in set (0.00 sec)

mysql> select * from idnameageindex where name='cc';
+----+------+------+
| id | name | age  |
+----+------+------+
|  3 | cc   |    3 |
+----+------+------+
1 row in set (0.00 sec)


mysql> explain select * from idnameageindex where name='cc';
+----+-------------+----------------+------+---------------+----------+---------+-------+------+-----------------------+

| id | select_type | table          | type | possible_keys | key      | key_len | ref   | rows | Extra                 |

+----+-------------+----------------+------+---------------+----------+---------+-------+------+-----------------------+

|  1 | SIMPLE      | idnameageindex | ref  | idx_name      | idx_name | 768     | const |    1 | Using index condition |

+----+-------------+----------------+------+---------------+----------+---------+-------+------+