【MYSQL】mysql单表亿级数据查询优化处理

1、实践表明mysql单表数据超过一亿后，数据进行交并差效率会非常慢，所以这时候就要进行表的优化。

我这里主要是使用索引。

2、表字段精量精简

查索引，建索引，删索引语法

 --查看索引
-- SHOW INDEX FROM 表名;
-- 删除索引
--ALTER TABLE 表名 DROP INDEX username_index;

--建索引
CREATE INDEX 索引名ON 表名(索引字段(10));

 --查看索引
-- SHOW INDEX FROM 表名;
-- 删除索引
--ALTER TABLE 表名 DROP INDEX username_index;--建索引
CREATE INDEX 索引名ON 表名(索引字段(10));

3、mysql聚合函数大全

MySQL 提供了丰富的聚合函数，用于对一组值执行计算并返回单个值。以下是 MySQL 中常用的聚合函数及其用法：

基本聚合函数

1. COUNT()

• 计算行数或非NULL值的数量

SELECT COUNT(*) FROM employees;  -- 计算总行数
SELECT COUNT(department_id) FROM employees;  -- 计算非NULL的department_id数量
SELECT COUNT(DISTINCT department_id) FROM employees;  -- 计算不同department_id的数量

2. SUM()

• 计算数值列的总和

SELECT SUM(salary) FROM employees;
SELECT SUM(salary * 1.1) FROM employees;  -- 可以包含表达式

3. AVG()

• 计算数值列的平均值

SELECT AVG(salary) FROM employees;
SELECT AVG(DISTINCT salary) FROM employees;  -- 计算不同值的平均值

4. MIN()

• 返回列中的最小值

SELECT MIN(salary) FROM employees;
SELECT MIN(hire_date) FROM employees;  -- 也适用于日期

5. MAX()

• 返回列中的最大值

SELECT MAX(salary) FROM employees;
SELECT MAX(hire_date) FROM employees;  -- 也适用于日期

高级聚合函数

6. GROUP_CONCAT()

• 将多行值连接成一个字符串

SELECT department_id, GROUP_CONCAT(last_name) 
FROM employees 
GROUP BY department_id;-- 使用分隔符和排序
SELECT department_id, GROUP_CONCAT(last_name ORDER BY hire_date SEPARATOR ', ') 
FROM employees 
GROUP BY department_id;

7. STD() / STDDEV()

• 计算总体标准差

SELECT STD(salary) FROM employees;

8. STDDEV_POP()

• 计算总体标准差(同STD)

SELECT STDDEV_POP(salary) FROM employees;

9. STDDEV_SAMP()

• 计算样本标准差

SELECT STDDEV_SAMP(salary) FROM employees;

10. VAR_POP()

• 计算总体方差

SELECT VAR_POP(salary) FROM employees;

11. VAR_SAMP()

• 计算样本方差

SELECT VAR_SAMP(salary) FROM employees;

12. VARIANCE()

• 计算总体方差(同VAR_POP)

SELECT VARIANCE(salary) FROM employees;

统计聚合函数

13. BIT_AND()

• 对二进制位执行AND操作

SELECT BIT_AND(flags) FROM permissions;

14. BIT_OR()

• 对二进制位执行OR操作

SELECT BIT_OR(flags) FROM permissions;

15. BIT_XOR()

• 对二进制位执行XOR操作

SELECT BIT_XOR(flags) FROM permissions;

分组函数

16. WITH ROLLUP

• 添加小计和总计行

SELECT department_id, COUNT(*), AVG(salary)
FROM employees
GROUP BY department_id WITH ROLLUP;

窗口函数(MySQL 8.0+)

虽然严格来说不是聚合函数，但窗口函数常与聚合一起使用：

17. ROW_NUMBER()

SELECT employee_id, salary,ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM employees;

18. RANK()

SELECT employee_id, salary,RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

19. DENSE_RANK()

SELECT employee_id, salary,DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

使用注意事项

1. 聚合函数通常与 GROUP BY 子句一起使用

2. 可以在 HAVING 子句中使用聚合函数进行过滤

3. SELECT 列表中非聚合列必须出现在 GROUP BY 中

4. 聚合函数会忽略 NULL 值（除了 COUNT(*)）

5. MySQL 5.7及以上版本对 GROUP BY 有更严格的SQL模式要求

性能优化建议

1. 为 GROUP BY 和 ORDER BY 列创建索引

2. 考虑使用 EXPLAIN 分析查询执行计划

3. 对于大数据集，可能需要调整 group_concat_max_len 系统变量

4. 在可能的情况下，限制聚合数据集的大小