limit分页查询
select * from user limit 3;
select * from user limit 1,3;
1是指表中的起始的偏移量(offset),3指实际搜索表中内容的个数
select * from user limit 1 offset 1;
select * from user where sex='M' limit 1,3;
explain查看SQL语句的执行计划,
但并不会非常精确,explain 并不会展示MySQL内部进行查询优化后的结果
因为sex是没有添加索引的,下面命令会默认查找表里所有的 sex=‘M’ 的内容,所以默认会遍历整张表。当我们只需要一行数据时,可以加 limit
进行限制,可提高SQL搜索效率
实际执行属于整表搜索
explain select * from user where sex='M';
实际执行属于条件搜索,遇到满足条件即停止搜索,可提高SQL搜索效率
explain select * from user where sex='M' limit 1;
limit分页查询优化
在查询时效率主要低在偏移(offset)所花的时间上,所以在分页查询时,可使用主键id进行辅助查询,可大大提高搜索效率
select * from user where id>上一页最后一条数据的id值 limit 20;
其中id为当前表的主键,提升limit分页查询的效率,优化在limit的偏移量(offset)的搜索
排序order by
select id,name,age,sex from user where sex='M' and age>=20 and age<=25 order by age asc;
select id,name,age,sex from user where sex='M' and age>=20 and age<=25 order by age desc;
- 使用任何字段来作为排序的条件,从而返回排序后的查询结果。可设定多个字段来排序。
- 使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下,按升序排列。
- 添加 WHERE…LIKE 子句来设置条件。
分组group by
select sex from user group by sex;
select count(id),sex from user group by sex;
select count(id),age from user group by age having age>20;
select count(id) as person from user group by sex order by person desc;
- group by语句根据一个或多个列对结果集进行分组。
- 在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。
- 可使用as对聚合函数得到的值起别名,参与之后的排序操作。
- 分组之后可以使用having添加选择条件,或者在选择分组时,使用where添加选择条件,推荐使用where处理,若where后面字段存在索引,可以使用索引,提升SQL执行效率。
当一列数据需要排序和分组查询时,
有索引:使用index 排序 且不需要回表
无索引:使用 filesort排序,速度较慢,且需要回表
表的结构
如果经常对age进行排序查询时,可以通过加索引的方式,显著提升效率
explain select * from user order by name; // Using filesort
explain select name from user order by name; // Using index
explain select age from user order by age; // Using filesort
explain select age from user group by age; // Using filesort
explain select name from user group by name; //Using index