前言

脚本之家：
《首页 >> 数据库 >> 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 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其他功能

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-各年国家产品汇总的汇总

尾部截取：

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`;