mysql系列--sql优化

索引优化规则

-- 以下测试用到的表结构
CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  `a` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `test` (`a`,`b`,`c`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `test1` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

尽量全值匹配

EXPLAIN select * from test where a = "a";
EXPLAIN select * from test where a = "a" and b = 1;
EXPLAIN select * from test where a = "a" and b = 1 and c = "c";

最佳左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

EXPLAIN select * from test where b = 1;
EXPLAIN select * from test where b = 1 AND c = "c";
EXPLAIN select * from test where a = "a" ;

不在索引列上做任何操作

不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描

EXPLAIN select * from test where left(a, 2) = "a" ;

范围条件放最后???

EXPLAIN select * from test where a = "a";
EXPLAIN select * from test where a = "a" and b = 1;
EXPLAIN select * from test where a = "a" and b = 1 and c = "c";
EXPLAIN select * from test where a = "a" and b >= 1 and c = "c";

覆盖索引尽量用

尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select *

EXPLAIN select a from test where a = "a";

不等于要慎用

mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描

EXPLAIN select a from test where a = "a";
EXPLAIN select a from test where a != "a";
EXPLAIN select a from test where a <> "a";

null和not null对性能有影响

EXPLAIN select a from test where a is not null;
EXPLAIN select a from test where a is null;

• 在查询字段设置为可以为null时，查询is not null会扫描全部，查询is null则会使用索引

• 在查询字段设置为不可以为null时，查询is null则报Impossible WHERE，查询is not null则索引失效

like查询要注意 ???

like以通配符开头(’%abc…’)mysql索引失效会变成全表扫描的操作

EXPLAIN select * from test where a = "a";
EXPLAIN select * from test where a like "a%";
EXPLAIN select * from test where a like "%a";
EXPLAIN select * from test where a like "%a%";
EXPLAIN select * from test where a like "a%a%";

字符串类型要加引号

EXPLAIN select * from test where a = "a";
EXPLAIN select * from test where a = 1;

or改成union效率高

使用union，两个语句都会走索引

EXPLAIN select * from test where a = "a" or a = "b";
EXPLAIN select * from test where a = "a" union select * from test where a = "b";

#####案例

假设查询索引为联合索引 index(a,b,c)

条件	索引是否被用到
where a = 3	Y，使用了索引a
where a = 3 and b = 5	Y，使用了索引a，b
where a = 3 and b = 5 and c = 4	Y，使用了索引a，b，c
where b = 3 或者 where b = 3 and c = 4 或者 where c = 4	N
where a = 3 and c = 5	使用到a， 但是c不可以，b中间断了
where a = 3 and b > 4 and c = 5	使用到a和b， c不能用在范围之后，b断了
where a = 3 and b like ‘kk%’ and c = 4	Y，使用到a，b，c
where a = 3 and b like ‘%kk’ and c = 4	Y，使用到了a
where a = 3 and b like ‘%kk%’ and c = 4	Y，使用到了a
where a = 3 and b like ‘k%kk%’ and c = 4	Y，使用到了a，b，c

批量导入

#####insert sql优化

• 提交前关闭自动提交
• 尽量使用批量插入
• 可以使用MyISAN引擎

#####LOAD DATA INFLIE

需要设置参数–secure-file-priv=’’

如果secure-file-priv=null：不允许导入或导出

如果secure-file-priv=/path：则只允许在这个目录下导入或导出

如果secure-file-priv=""：则只不限制，可以在任意目录导入导出

使用LOAD DATA INFLIE ,比一般的insert语句快20倍

-- 默认存储路径为mysql data目录下的数据库名称对应的文件夹下
select * into OUTFILE 'product.txt' from product_info

load data INFILE 'product.txt' into table product_info