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

标签：查询 索引 单表 MySQL where id select user

• 数据准备：

　　　　用户表 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