- 数据准备:
用户表 user:
create table user(id int unsigned primary key not null auto_increment, name varchar(50) UNIQUE not null, age tinyint not null, sex enum('M','W') not null)engine=INNODB default charset=utf8;
插入数据:
insert into user(name,age,sex) values('zhang san',22,'M'),('li si',21,'M'),('wang wu',22,'W'),('er gou',18,'M'),('cui hua',19,'W');
1.单表查询
*查询表中所有数据
select * from user;
语法 SELECT [字段名称(* 表示所有字段)] FROM [要查询的表名称];
*通过条件过滤出部分字段数据
select id,name,age,sex from user where age >20;
语法 SELECT [字段名称1,字段名称2] FROM [表名称] WHERE (条件表达式);
*去重查询distinct
select distinct name from user;
*空值查询
is [not] null
select * from user where name is null;
*带in子查询
[NOT] IN (元素1,元素2,...)
select * from user where age in (21,22);
*分页查询 limit
limit N 的使用,显示前N行数据
limit N,M 的使用,显示前N+1到M行的数据
N,M 使用是,会发生偏移,例:limit 1,3; 此时发生offset 1位,从2位数据开始取出3个数据
扩展:explain 可以查看执行计划
如果查询的字段没有索引,会进行整表查询
如果有limit 关键字,则系统查询到符合条件的数据,且满足limit输出的行数后便不再查询
explain 使用
id
id列的编号就是select 的序列号,也可以理解为SQL执行顺序的标识,有几个select 就有几个id。
- id值不同:只查询,id的序号会递增,id值越大优先级越高,优先执行
- id值相同:从上向下执行:
- id值为null : 表示是一个结果集,不需要使用它来查询。
select_type
查询的类型,主要是用于区分普通查询、联合查询、子查询等复杂的查询
- SIMPLE:简单的select查询,查询中不包含子查询或者union
- PRIMARY:查询中包含任何复杂的子部分,最外层查询则被标记为primary (且只有一个)
- SUBQUERY:在select 或 where列表中包含了子查询
- DERIVED:在from列表中包含的子查询被标记为derived(衍生),mysql或递归执行这些子查询,把结果放在零时表里
- UNION:若第二个select出现在union之后,则被标记为union;若union包含在from子句的子查询中,外层select将被标记为derived
- UNION RESULT:从union表获取结果的select (没有参与查询,id值此时为null)
table
表示explain 的一行正在访问哪个表。
- 如果查询使用啦别名。那么这里显示的是别名
- 如果不涉及对数据库表的操作。那么显示null
- 如果显示为尖括号控起来 就表示这是个临时表。后面的N 就是执行计划中的id。表示结果来自于这个查询产生
- **如果是尖括号 括起来的<union M,N> 与之类似 ,这也是一个临时表,表示这个结果来自与union查询的id为M,N的结果集 **
partitions
type
它提供了判断查询是否高效的重要依据依据。
type显示查询使用了何种查询范围,主要有以下几种,从最好到最坏依次是:
ALL < index < range ~ index_merge < ref < eq_ref < const < system
ALL,说明效率是很差,需要优化了。
- system: 表中只有一条数据, 这个类型是特殊的 const 类型。
- const: 针对主键或唯一索引的等值查询扫描,表示通过索引一次就找到了,最多只返回一行数据。 const 查询速度非常快, 因为它仅仅读取一次即可。例如下面的这个查询,它使用了主键索引,因此 type 就是 const 类型的:explain select * from user_info where id = 2;
- eq_ref: 唯一性索引扫描此类型通常出现在多表的 join 查询,表示对于前表的每一个结果,都只能匹配到后表的一行结果。并且查询的比较操作通常是 =,查询效率较高。例如:explain select * from user_info, order_info where user_info.id = order_info.user_id;
- ref: 此类型通常出现在多表的 join 查询,针对于非唯一或非主键索引,或者是使用了 最左前缀 规则索引的查询。例如下面这个例子中, 就使用到了 ref 类型的查询:explain select * from user_info, order_info where user_info.id = order_info.user_id AND order_info.user_id = 5
- range: 表示使用索引范围查询,通过索引字段范围获取表中部分数据记录。这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中。例如下面的例子就是一个范围查询:explain select * from user_info where id between 2 and 8;
- index: 表示全索引扫描(full index scan),和 ALL 类型类似,只不过 ALL 类型是全表扫描,而 index 类型则仅仅扫描所有的索引, 而不扫描数据。index 类型通常出现在:所要查询的数据直接在索引树中就可以获取到, 而不需要扫描数据。当是这种情况时,Extra 字段 会显示 Using index。
- ALL: 表示全表扫描,这个类型的查询是性能最差的查询之一。通常来说, 我们的查询不应该出现 ALL 类型的查询,因为这样的查询在数据量大的情况下,对数据库的性能是巨大的灾难。 如一个查询是 ALL 类型查询, 那么一般来说可以对相应的字段添加索引来避免。
- NULL: MySQL在优化过程中分解语句,执行时甚至不用访问表或索引
possible_keys
它表示 mysql 在查询时,可能使用到的索引。 注意,即使有些索引在 possible_keys 中出现,但是并不表示此索引会真正地被 mysql 使用到。 mysql 在查询时具体使用了哪些索引,由 key 字段决定。
key
显示查询实际使用哪个索引来优化对该表的访问;
select_type为index_merge时,这里可能出现两个以上的索引,其他的select_type这里只会出现一个。
key_len
用于处理查询的索引长度,表示索引中使用的字节数。通过这个值,可以得出一个多列索引里实际使用了哪一部分。
注:key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的。另外,key_len只计算where条件用到的索引长度,而排序和分组就算用到了索引,也不会计算到key_len中。
ref
显示索引的哪一列被使用了,如果可能的话,是一个常数。哪些列或常量被用于查找索引列上的值 ,如果可能,是一个常量const。
rows
表示MySQL根据表统计信息及索引选用情况,大致估算的找到所需的目标记录所需要读取的行数,不是精确值。这个值非常直观的显示 sql 效率好坏, 原则上 rows 越少越好
filtered
当你的索引统计直接获取了需要的所有数据时,就会显示100
因此一个比较低filtered值表示需要有一个更好的索引,假如type=all,表示以全表扫描的方式得到1000条记录,且filtered=0.1%,表示只有1条记录是符合搜索条件的。
Extra
Using filesort
MySQL有两种方式可以生成有序的结果,通过排序操作或者使用索引,当Extra中出现了Using filesort 说明MySQL使用了后者,但注意虽然叫filesort但并不是说明就是用了文件来进行排序,只要可能排序都是在内存里完成的。大部分情况下利用索引排序更快,所以一般这时也要考虑优化查询了。使用文件完成排序操作,这是可能是ordery by,group by语句的结果,这可能是一个CPU密集型的过程,可以通过选择合适的索引来改进性能,用索引来为查询结果排序。
Using temporary
用临时表保存中间结果,常用于GROUP BY 和 ORDER BY操作中,一般看到它说明查询需要优化了,就算避免不了临时表的使用也要尽量避免硬盘临时表的使用。
Not exists
MYSQL优化了LEFT JOIN,一旦它找到了匹配LEFT JOIN标准的行, 就不再搜索了。
Using index
说明查询是覆盖了索引的,不需要读取数据文件,从索引树(索引文件)中即可获得信息。如果同时出现using where,表明索引被用来执行索引键值的查找,没有using where,表明索引用来读取数据而非执行查找动作。这是MySQL服务层完成的,但无需再回表查询记录。
Using index condition
这是MySQL 5.6出来的新特性,叫做“索引条件推送”。简单说一点就是MySQL原来在索引上是不能执行如like这样的操作的,但是现在可以了,这样减少了不必要的IO操作,但是只能用在二级索引上。
Using where
使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。注意:Extra列出现Using where表示MySQL服务器将存储引擎返回服务层以后再应用WHERE条件过滤。
Using join buffer
使用了连接缓存:Block Nested Loop,连接算法是块嵌套循环连接;Batched Key Access,连接算法是批量索引连接
impossible where
where子句的值总是false,不能用来获取任何元组
select tables optimized away
在没有GROUP BY子句的情况下,基于索引优化MIN/MAX操作,或者对于MyISAM存储引擎优化COUNT(*)操作,不必等到执行阶段再进行计算,查询执行计划生成的阶段即完成优化。
distinct
优化distinct操作,在找到第一匹配的元组后即停止找同样值的动作
标签:查询,索引,单表,MySQL,where,id,select,user From: https://www.cnblogs.com/testKK/p/17001360.html