（全栈须知）4.百万级SQL语句优化(二) 之参考官方二条件

批量导入

脚本之家：
《首页 >> 数据库 >> Mysql》
mysql导入数据load data infile

下面是官方其他优化：

1、优化查询语句

需要重点处理的 EXPLAIN - Extra:

-- Using filesort 使用了分页缓存：分页查询-避免不必要的键排序搜索
a. FORCE INDEX (index_for_column)强制索引；
b. mysqld的max-seeks-for-key或使用SET max_seeks_for_key=1000告诉优化器：假定任何按键扫描导致1000个以上的键搜索
c. 尽量使用范围查询替代

-- ALL 使用了无索引的全表扫描

-- Using temporary使用了临时表

a. ORDER BY 优化

---

• 避免使用排序 [尽量使用范围查询替代]
• 建立ORDER BY多个项目的组合索引
• 数字列可以使用 ABS(绝对值函数) 、-(负号)，但这相当于增加函数运算--很慢

b. LIMIT 查询优化

---

• 使用LIMIT 0快速返回空集，获取结果列类型
• LIMIT会影响已有的ORDER_BY的排序返回

c. GROUP BY优化

---

复杂查询

SELECT id, FLOOR(vote_num/100) AS val FROM vote_record GROUP BY id, val limit 888888,6;
--ONLY_FULL_GROUP_BY启用
SELECT id, FLOOR(vote_num/100) AS F FROM vote_record GROUP BY id, FLOOR(vote_num/100) limit 888888,6;
--`派生表` ONLY_FULL_GROUP_BY默认的禁用 
SELECT id, F, id+F, concat(id,'+',F) FROM 
    (
    SELECT id, FLOOR(vote_num/100) AS F FROM vote_record GROUP BY id, FLOOR(vote_num/100)
    ) 
    AS dt limit 888888,6;

d. 行构造函数表达式优化

---

SELECT * FROM t1 WHERE column1 = 1 AND column2 = 1; --等价于：
SELECT * FROM t1 WHERE (column1,column2) = (1,1);

"为了获得更好的结果，请避免将行构造函数与AND/ OR 表达式混合 。使用其中一个。"

e. 使用实现优化子查询

---

SELECT * FROM t1 WHERE t1.a IN (SELECT t2.b FROM t2 WHERE where_condition);

SELECT * FROM t1 WHERE EXISTS (SELECT t2.b FROM t2 WHERE where_condition AND t1.a=t2.b);

2、其他功能

• 降序索引 (>=8.0)

MySQL支持降序索引：INDEX idx1 (c1 ASC, c2 ASC),

表12.30其他功能

名称	描述
ANY_VALUE()	抑制ONLY_FULL_GROUP_BY值(非确定性测试)的拒绝：GROUP BY时sql_mode=only_full_group_by问题
BIN_TO_UUID()	将二进制UUID转换为字符串
DEFAULT()	返回表列的默认值
GROUPING()	显示区分聚合列标识，超级聚合 WITH ROLLUP 行(展示汇总)、对常规行NULL列填充
INET_ATON()	返回IP地址的数值 -- 网络地址转换+
INET_NTOA()	从数值返回IP地址 -- 网络地址转换-
INET6_ATON()	返回IPv6地址的数值
INET6_NTOA()	从数值返回IPv6地址
IS_IPV4()	参数是否为IPv4地址
IS_IPV4_COMPAT()	参数是否是IPv4兼容的地址
IS_IPV4_MAPPED()	参数是否是IPv4映射地址
IS_IPV6()	参数是否是IPv6地址
IS_UUID()	参数是否是有效的UUID
MASTER_POS_WAIT()	阻止，直到从站已读取并应用所有更新到指定位置
NAME_CONST()	导致列具有给定名称
SLEEP()	睡几秒钟
UUID()	返回通用唯一标识符（UUID）
UUID_SHORT()	返回整数值通用标识符
UUID_TO_BIN()	将字符串UUID转换为二进制
VALUES()	定义INSERT期间要使用的值

a. GROUPING高级统计

---

SELECT
         IF(GROUPING(year), 'All years', year) AS year,
         IF(GROUPING(country), 'All countries', country) AS country,
         IF(GROUPING(product), 'All products', product) AS product,
         SUM(profit) AS profit
       FROM sales
       GROUP BY year, country, product WITH ROLLUP;
-- 先计算：1-各类产品产品的汇总，2-各国家产品汇总的汇总；3-各年国家产品汇总的汇总

尾部截取：

year	country	product	profit
2001	USA	All products	3000
2001	All countries	All products	3010
All years	All countries	All products	7535

b. DISTINCT优化

---

在大多数情况下，一个DISTINCT条款可以被视为一个特例GROUP BY。适用于GROUP BY查询的优化 也可以应用于带有DISTINCT子句的查询。

c.表达式不确定数

---

下面语句输出行数是不确定的，实测1~8行可见到：

select id from vote_record where id in (FLOOR(1 + RAND() * 1000000), FLOOR(1 + RAND() * 1000000), FLOOR(1 + RAND() * 1000000), FLOOR(1 + RAND() * 1000000));

如下也是这样，可以见到0~3行结果：

select id from vote_record where id = FLOOR(1 + RAND() * 1000000);

d. 30天之前的查询

---

《MySQL时间函数》

DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date_column

mysql> SELECT CURDATE();
        -> '2008-06-13'
mysql> SELECT CURDATE() + 0;
        -> 20080613

mysql自动计算日期区间，DATE_SUB减到之前/DATE_ADD加到明天

3、特性

a.json

---

SELECT c->>"$.name" AS name FROM jemp WHERE g > 2 ORDER BY c->"$.name";

select json_unquote(json_extract(jemp.c,'$.name')) AS c->>"$.name" from jemp where (jemp.g > 2) order by json_extract(jemp.c,'$.name');

b.generate column 生成列

---

-- 生成列-不存储 （虚拟列）：
ALTER TABLE `vote_record` ADD `order_use_vir` VARCHAR(20) AS (concat(`vote_num`,`group_id`)) VIRTUAL AFTER `create_time`;

-- 生成列-存储 （计算列）：
ALTER TABLE `vote_record` ADD `order_use_vir` VARCHAR(20) AS (concat(`vote_num`,`group_id`)) STORED AFTER `create_time`;