数据库之SQL（一）-CSDN博客

本文链接：https://blog.csdn.net/L_peanut/article/details/140183260

一、简述数据库分页

1、MySQL分页语法：

在MySQL中，SELECT语句默认返回所有匹配的行，它们可能是指定表中的每个行。为了返回第一行或前几行，可使用LIMIT子句，以实现分页查询。LIMIT子句的语法如下：

-- 在所有的查询结果中，返回前5行记录。
SELECT prod_name FROM products LIMIT 5;
-- 在所有的查询结果中，从第5行开始，返回5行记录。
SELECT prod_name FROM products LIMIT 5,5;

总之，带一个值的LIMIT总是从第一行开始，给出的数为返回的行数。带两个值的LIMIT可以指定从行号为第一个值的位置开始。

2、优化LIMIT分页：

在偏移量非常大的时候，例如 LIMIT 10000,20 这样的查询，这时MySQL需要查询10020条记录然后只返回最后20条，前面的10000条记录都将被抛弃，这样的代价是非常高的。如果所有的页面被访问的频率都相同，那么这样的查询平均需要访问半个表的数据。要优化这种查询，要么是在页面中限制分页的数量，要么是优化大偏移量的性能。

优化此类分页查询的一个最简单的办法就是尽可能地使用索引覆盖扫描，而不是查询所有的列，然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候，这样做的效率会提升非常大。考虑下面的查询：

SELECT film_id,description FROM sakila.film ORDER BY title LIMIT 50,5;

如果这个表非常大，那么这个查询最好改写成下面的样子：

SELECT film.film_id,film.description
FROM sakila.film
INNER JOIN (
SELECT film_id FROM sakila.film ORDER BY title LIMIT 50,5
) AS lim USING(film_id);

这里的“延迟关联”将大大提升查询效率，它让MySQL扫描尽可能少的页面，获取需要访问的记录后再根据关联列回原表查询需要的所有列。这个技术也可以用于优化关联查询中的LIMIT子句。有时候也可以将LIMIT查询转换为已知位置的查询，让MySQL通过范围扫描获得对应的结果。例如，如果在一个位置列上有索引，并且预先计算出了边界值，上面的查询就可以改写为：

SELECT film_id,description FROM skila.film
WHERE position BETWEEN 50 AND 54 ORDER BY position;

对数据进行排名的问题也与此类似，但往往还会同时和GROUP BY混合使用，在这种情况下通常都需要预先计算并存储排名信息。

LIMIT和OFFSET的问题，其实是OFFSET的问题，它会导致MySQL扫描大量不需要的行然后再抛弃掉。如果可以使用书签记录上次取数的位置，那么下次就可以直接从该书签记录的位置开始扫描，这样就可以避免使用OFFSET。例如，若需要按照租赁记录做翻页，那么可以根据最新一条租赁记录向后追溯，这种做法可行是因为租赁记录的主键是单调增长的。首先使用下面的查询获得第一组结果：

SELECT * FROM sakila.rental ORDER BY rental_id DESC LIMIT 20;

假设上面的查询返回的是主键16049到16030的租赁记录，那么下一页查询就可以从16030这个点开始：

SELECT * FROM sakila.rental
WHERE rental_id < 16030 ORDER BY rental_id DESC LIMIT 20;

该技术的好处是无论翻页到多么后面，其性能都会很好。

二、简述SQL中的聚合函数

常用的聚合函数有COUNT()、AVG()、SUM()、MAX()、MIN()。下面以MySQL为例，说明这些函数的作用。

1、COUNT()函数。统计数据表中包含的记录行的总数，或者根据查询结果返回列中包含的数据行数，它有两种用法：①COUNT(*)计算表中总的行数，不管某列是否有数值或者为空值；②COUNT(字段名)计算指定列下总的行数，计算时将忽略空值的行。COUNT()函数可以与GROUP BY一起使用来计算每个分组的总和。

2、AVG()函数。通过计算返回的行数和每一行数据的和，求得指定列数据的平均值。AVG()函数可以与GROUP BY一起使用，来计算每个分组的平均值。

3、SUM()函数。一个求总和的函数，返回指定列值的总和。SUM()可以与GROUP BY一起使用，来计算每个分组的总和。

4、MAX()函数。返回指定列中的最大值。MAX()也可以和GROUP BY关键字一起使用，求每个分组中的最大值。MAX()函数不仅适用于查找数值类型，也可应用于字符类型。

5、MIN()函数。返回查询列中的最小值。MIN()也可以和GROUP BY关键字一起使用，求出每个分组中的最小值。MIN()函数与MAX()函数类似，不仅适用于查找数值类型，也可应用于字符类型。