CREATE TABLE t (
   id int(10) primary key,
   u_name varchar(20),
   u_year int(5)
) ENGINE=MyISAM;
  

MyISAM引擎的表t, 表t中只有一条数据, 进行简单查询时
样例SQL如下
SELECT * FROM t
SELECT * FROM t LIMIT 1
SELECT * FROM t WHERE id=1
SELECT * FROM t WHERE id IN (1,2)

MyISAM引擎的表

  
CREATE TABLE other (
   id int(10) primary key,
   u_name varchar(20),
   u_year int(5)
) ENGINE=InnoDB;
  

当子查询仅返回一条数据时
样例SQL如下
SELECT * FROM (SELECT * FROM other LIMIT 1) t
SELECT * FROM (SELECT * FROM other WHERE id=1) t
SELECT * FROM (SELECT id FROM other LIMIT 1) t
SELECT * FROM (SELECT u_name FROM other LIMIT 1) t
SELECT * FROM (SELECT u_name FROM other WHERE id=1) t

与表other的引擎类型无关

  
CREATE TABLE t (
   id int(10),
   u_name varchar(20),
   u_year int(5)
) ENGINE=InnoDB;
  

ALTER TABLE t add constraint pk primary key(id)
ALTER TABLE t add constraint uq_name unique key(u_name)

根据主键索引或唯一索引进行等值查询, 且条件不参与计算时
样例SQL如下
SELECT * FROM t WHERE id=1
SELECT * FROM t WHERE id<=>2
SELECT * FROM t WHERE u_name = 'Libai'
反例SQL如下
SELECT * FROM t WHERE id + 1 = 3

  
CREATE TABLE t (
   id int(10) primary key,
   u_name varchar(20),
   u_sex varchar(2)
) ENGINE=InnoDB;
  

ALTER TABLE t add constraint uq_name_sex unique key(u_name,u_sex)

唯一联合索引全部参与等值比较, 且条件不参与计算时
样例SQL如下
SELECT * FROM t WHERE u_name = 'Libai' AND u_sex = '1'
SELECT * FROM t WHERE u_name = 'Libai' AND u_sex <=> '1'
反例SQL如下
SELECT * FROM t WHERE u_name = 'Libai'
SELECT * FROM t WHERE u_sex = '1'

  
CREATE TABLE t (
   id int(10),
   u_name varchar(20),
   u_year int(5)
) ENGINE=InnoDB;
  

ALTER TABLE t add constraint pk primary key(id,u_name)

主键联合索引全部参与等值比较, 且条件不参与计算时
样例SQL如下
SELECT * FROM t WHERE id=2 AND u_name ='Dufu'
SELECT * FROM t WHERE id<=>2 AND u_name ='Dufu'
反例SQL如下
SELECT * FROM t WHERE id=2
SELECT * FROM t WHERE u_name ='Dufu'
SELECT * FROM t WHERE id + 1 = 3 AND u_name ='Dufu'

  
CREATE TABLE t (
   id int(10) primary key,
   u_name varchar(20) NOT NULL,
   u_year int(5)
) ENGINE=InnoDB;

CREATE TABLE other (
   id int(10) primary key,
   u_name varchar(20) NOT NULL,
   u_address varchar(15)
) ENGINE=InnoDB;
  

ALTER TABLE t add constraint uq_name unique key(u_name)
ALTER TABLE other add constraint uq_name unique key(u_name)

根据主键索引或非空唯一索引进行 JOIN 等值查询, 且条件不参与计算时

可使用的操作符: = 或者 <=>
样例SQL如下
SELECT * FROM t, other WHERE t.id = other.id
SELECT * FROM other, t WHERE t.id = other.id
SELECT * FROM t LEFT JOIN other ON t.id <=> other.id
SELECT * FROM t LEFT JOIN other ON t.id = other.id
SELECT * FROM other RIGHT JOIN t ON t.id = other.id
SELECT * FROM other INNER JOIN t ON t.id = other.id
SELECT * FROM t INNER JOIN other ON t.id = other.id
反例SQL如下
SELECT * FROM t, other WHERE t.id - other.id = 0
SELECT * FROM other INNER JOIN t ON t.id - other.id = 0

根据联合索引(包括主键联合索引或非空唯一联合索引)进行 JOIN 等值查询, 且条件不参与计算时
ALTER TABLE t add constraint uq_id_name unique key(id,u_name)
ALTER TABLE other add constraint uq_id_name unique key(id,u_name)
或
ALTER TABLE t add constraint pk_id primary key(id)
ALTER TABLE other add constraint uq_id_name unique key(id,u_name)

可使用的操作符: =
样例SQL如下
SELECT * FROM t, other WHERE t.id = other.id AND t.u_name = other.u_name
SELECT * FROM t, other WHERE t.id = other.id AND 'Libai' = other.u_name

  
CREATE TABLE t (
   id int(10) primary key,
   u_name varchar(20),
   u_year int(5)
) ENGINE=InnoDB;

CREATE TABLE other (
   id int(10) primary key,
   u_name varchar(20),
   u_address varchar(20)
) ENGINE=InnoDB;
  

ALTER TABLE t add constraint uq_name unique key(u_name)
ALTER TABLE other add constraint uq_name unique key(u_name)

ALTER TABLE t add constraint idx_name index(u_name)
ALTER TABLE other add constraint idx_name index(u_name)


唯一索引(不是非空唯一索引)或普通索引进行等值查询时
可使用的操作符: = 或者 <=>
样例SQL如下
SELECT * FROM t,other WHERE t.u_name = other.u_name
SELECT * FROM t RIGHT JOIN other ON t.u_name = other.u_name
SELECT * FROM t RIGHT JOIN other ON t.u_name <=> other.u_name
SELECT * FROM t INNER JOIN other ON t.u_name = other.u_name
SELECT * FROM t LEFT JOIN other ON t.u_name = other.u_name
SELECT * FROM other LEFT JOIN t ON t.u_name = other.u_name
SELECT * FROM t WHERE u_name = 'Libai'

联合索引亦符合以上结论.

作用于索引列

=, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, or IN()

BETWEEN num1 AND num2 如果范围内匹配到的数据行太多,也会索引失效,type = ALL
< num 如果范围内匹配到的数据行太多,也会索引失效,type = ALL
> num1 AND < num2 如果范围内匹配到的数据行太多,也会索引失效,type = ALL
> num 如果范围内匹配到的数据行太多,也会索引失效, type = ALL
即, > 和 < , BETWEEN .. AND .. 要根据范围内匹配到的数据行多少, 决定是否type = ALL 或 type = range

  
CREATE TABLE t (
   id int(10) primary key,
   u_id int(10),
   u_name varchar(20),
   u_year int(5),
   u_address varchar(20)
) ENGINE=InnoDB;
  

CREATE INDEX idx_u_id on t(u_id);
CREATE INDEX idx_name_year on t(u_name,u_year);

Using index
索引覆盖
EXPLAIN SELECT u_name,u_year FROM t WHERE u_name = 'Libai'
EXPLAIN SELECT u_name,u_year FROM t WHERE u_name = 'Libai' and u_year = 2021
EXPLAIN SELECT id,u_id FROM t WHERE u_id = 2022
EXPLAIN SELECT u_id FROM t

Using WHERE; Using index
索引范围查询(包括整个索引树) & 索引覆盖
EXPLAIN SELECT u_name,u_year FROM t WHERE u_year = 2021
EXPLAIN SELECT u_name,u_year FROM t WHERE u_year > 2021
EXPLAIN SELECT u_name,u_year FROM t WHERE u_name > 'Libai'
EXPLAIN SELECT u_name,u_year FROM t WHERE u_name = 'Libai' AND u_year > 2022
EXPLAIN SELECT id,u_id FROM t WHERE u_id > 2022

Using index condition
索引范围查询(包括整个索引树) & 回表 { 索引下推 }
EXPLAIN SELECT * FROM t WHERE u_name = 'Libai' AND u_year > 2020
EXPLAIN SELECT * FROM t WHERE u_id BETWEEN 200 AND 4000 索引范围内匹配到的数据行不多, 如果匹配到的数据行太多, 直接 type = ALL, Extra = Using WHERE . 案例

Using WHERE
1.全表扫描,未使用索引,Server层在接收到存储引擎层返回的记录后, 再根据WHERE条件(全部WHERE或剩下部分WHERE)判断之后返回给客户端.
2.使用索引(如果是非聚簇索引,还存在回表),Server层在接收到存储引擎层返回的记录后, 再根据WHERE条件(全部WHERE或剩下部分WHERE)判断之后返回给客户端.
EXPLAIN SELECT * FROM t WHERE u_name > 'Libai'
EXPLAIN SELECT * FROM t WHERE u_year = 2021
EXPLAIN SELECT * FROM t WHERE u_year > 2021
EXPLAIN SELECT * FROM t WHERE u_id > 2022
EXPLAIN SELECT * FROM t WHERE u_id = 209 AND u_name = 'Libai'
EXPLAIN SELECT * FROM t WHERE u_id >= 10 AND u_id <= 10000
EXPLAIN SELECT * FROM t WHERE u_id BETWEEN 1 AND 100000
EXPLAIN SELECT * FROM t WHERE u_address = 'sichuan'

Extra = NULL
EXPLAIN SELECT * FROM t WHERE u_name = 'Libai'
EXPLAIN SELECT u_name,u_year,u_address FROM t WHERE u_name = 'Libai'
EXPLAIN SELECT * FROM t WHERE u_name = 'Libai' AND u_year = 2021
EXPLAIN SELECT * FROM t WHERE id=3021
EXPLAIN SELECT * FROM t WHERE u_id=2022