初识MySQL（四）单表select查询操作

数据准备：

　　　　用户表 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列的编号就是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

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操作，在找到第一匹配的元组后即停止找同样值的动作