表结构:mysql> show create table student\G
*************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
`sex` char(4) NOT NULL,
`birth` date NOT NULL,
`department` varchar(30) NOT NULL,
PRIMARY KEY (`id`),
KEY `index_birth_department` (`birth`,`department`),
KEY `index_name` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)表数据:mysql> select * from student;
+----+------+-----+------------+------------+
| id | name | sex | birth | department |
+----+------+-----+------------+------------+
| 1 | 张三 | 男 | 1985-02-06 | 信息学院 |
| 2 | 赵六 | 女 | 1986-10-24 | 计算机学院 |
| 3 | 李四 | 男 | 1991-02-15 | 英语系 |
| 4 | 王五 | 女 | 1989-12-25 | 旅游系 |
+----+------+-----+------------+------------+
4 rows in set (0.00 sec)使用or的查询:mysql> explain select * from student where name='张三' or id=2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: student
type: ALL
possible_keys: PRIMARY,index_name
key: NULL
key_len: NULL
ref: NULL
rows: 4
Extra: Using where
1 row in set (0.00 sec)疑问,为什么没有使用索引?
*************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
`sex` char(4) NOT NULL,
`birth` date NOT NULL,
`department` varchar(30) NOT NULL,
PRIMARY KEY (`id`),
KEY `index_birth_department` (`birth`,`department`),
KEY `index_name` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)表数据:mysql> select * from student;
+----+------+-----+------------+------------+
| id | name | sex | birth | department |
+----+------+-----+------------+------------+
| 1 | 张三 | 男 | 1985-02-06 | 信息学院 |
| 2 | 赵六 | 女 | 1986-10-24 | 计算机学院 |
| 3 | 李四 | 男 | 1991-02-15 | 英语系 |
| 4 | 王五 | 女 | 1989-12-25 | 旅游系 |
+----+------+-----+------------+------------+
4 rows in set (0.00 sec)使用or的查询:mysql> explain select * from student where name='张三' or id=2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: student
type: ALL
possible_keys: PRIMARY,index_name
key: NULL
key_len: NULL
ref: NULL
rows: 4
Extra: Using where
1 row in set (0.00 sec)疑问,为什么没有使用索引?
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货