全表扫描(遍历)
ALL
索引扫描(最差到最优)
index index与ALL区别为index类型只遍历索引树
range 索引范围扫描,对于我们使用select索引最低的类型标准应该达到range
ref 辅助索引等值查询
eq_ref
const 主键或唯一键等值查询
system 主键或唯一键等值查询
Null
重要列信息
重要列信息:
table 表信息
type 访问类型,索引的应用级别
possible_keys 可能会使用到的索引
key 实际上使用的索引
key_len 联合索引覆盖长度(越小越好)
rows 查询的行数(越少越好)
Extra 额外的信息
案例
环境准备
use world
create table t1 select * from city;
insert into t1 select * from t1;(多次执行至数据行数达到400万)
1.select count(1) from t1;
+----------+
| count(1) |
+----------+
| 4176896 |
+----------+
1 row in set (1.04 sec)
2.desc t1;
+-------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| ID | int(11) | NO | | 0 | |
| Name | char(35) | NO | | | |
| CountryCode | char(3) | NO | | | |
| District | char(20) | NO | | | |
| Population | int(11) | NO | | 0 | |
+-------------+----------+------+-----+---------+-------+
5 rows in set (0.00 sec)
比较索引带来的优化
案例一:
1.desc select * from t1 where t1.countrycode='usa'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4050625
filtered: 10.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
可以看见这里的type是all(全局扫描)
2.create index idx_country on t1(countrycode);创建索引
3.desc select * from t1 where t1.countrycode='usa'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ref
possible_keys: idx_country
key: idx_country
key_len: 3
ref: const
rows: 586394
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
可以看见这里的type是ref,搜索的效率提高了
案例二:
1.desc select * from t1 where t1.countrycode='usa' or t1.countrycode='chn'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ALL
possible_keys: idx_country
key: NULL
key_len: NULL
ref: NULL
rows: 4050625
filtered: 32.57
Extra: Using where
1 row in set, 1 warning (0.00 sec)
可以看见在使用范围查询之后,type又变为all了
2.desc select * from t1 where t1.countrycode='usa' or t1.countrycode='chn' limit 50\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: range
possible_keys: idx_country
key: idx_country
key_len: 3
ref: NULL
rows: 1319294
filtered: 100.00
Extra: Using index condition; Using MRR
1 row in set, 1 warning (0.00 sec)
在加上limit之后,又走了索引,效率提高了(根据实际业务进行分页)
3.desc select * from t1 where t1.countrycode='usa' union all select * from t1 where t1.countrycode='chn'\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: t1
partitions: NULL
type: ref
possible_keys: idx_country
key: idx_country
key_len: 3
ref: const
rows: 586394
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 2
select_type: UNION
table: t1
partitions: NULL
type: ref
possible_keys: idx_country
key: idx_country
key_len: 3
ref: const
rows: 732900
filtered: 100.00
Extra: NULL
2 rows in set, 1 warning (0.00 sec)
改造成union all写法之后效率也会提高
案例三:
1.desc select * from t1 where t1.countrycode like '%us'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4050625
filtered: 11.11
Extra: Using where
1 row in set, 1 warning (0.00 sec)
这里看见type是all,%写在模糊查询的条件前面相当于还是全表扫描
2.desc select * from t1 where t1.countrycode like 'us%'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: range
possible_keys: idx_country
key: idx_country
key_len: 3
ref: NULL
rows: 586394
filtered: 100.00
Extra: Using index condition; Using MRR
1 row in set, 1 warning (0.00 sec)
将%放在后面type就改为range了
在使用select语句的时候,注意事项:
1.不要使用select *
2.一定要带where
3.where中的条件,要用索引
4.select要查询的字段,尽量在where的条件字段出现,甚至在后续的group by,order by中出现
查询所有大于60分的学生信息