学习笔记0805

SQL优化

insert优化

insert优化 批量插入 手动提交事务 主键顺序插入
使用load指令插入大批量数据

#客户端连接服务器时，加上 --local-infile
mysql --local-infile -u root -p
#设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile=1;
#执行load指令将准备好的数据，加载到表结构中
load data local infile '/root/sql1.log' into table `tb_user` fields terminated by ',' lines terminated by '\n';

主键优化

主键顺序插入的性能高于乱序插入

IOT（index organized table） 在InnoDB存储引擎中，表数据是根据主键顺序存放的

主键设计原则
满足业务需求的情况下，尽量降低主键的长度
插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键
尽量不要使用UUID做主键或者是其他自然主键，如身份证号
业务操作时，避免对主键的修改

order by优化

using filesort 全表扫描，在缓冲区排序
using index 不需要额外排序，操作效率高
根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则
尽量使用覆盖索引
多字段排序，一个升序一个降序，此时需注意联合索引在创建时的规则（ASC/DESC）
如果不可避免的出现filesort，我们可以适当增加排序缓冲区sort_buffer_size(默认256K)的大小

group by优化

在分组操作时，可以通过索引来提高效率
分组操作时，索引的使用也是满足最左前缀法则的

limit优化

使用主键进行排序再用limit，取到的id作为一张表进行多表查询

- 例如
select * from tb_sku s, (select id from tb_sku order by id limit 9000000,10) a where s.id = a.id;

count优化

MyISAM引擎把一个表的总行数存在磁盘上
InnoDB引擎，它执行count(*)，需要一行一行读取之后进行计数
优化思路：自己计数

count几种用法
count(*)、count(主键)、count(字段)、count(1)
count(主键) InnoDB引擎会遍历整张表，把每一行的主键id的值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加（主键不可能为null）
count(字段) 没有not null约束：InnoDB引擎会遍历整张表，服务器判断是否为null，不为null，计数累加
有not null约束：InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加
count(1)：InnoDB引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”，直接按行进行累加
count(*)：InnoDB引擎并不会把全部字段取出来，而是专门进行优化，不取值，服务层直接按行进行累加
按照效率排序的话，count(字段)<count(主键id)<count(1)≈count(*)，所以尽量count(*)

update优化

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁

视图

视图（View）是一种虚拟存在的表
通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果

- 创建
CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH[CASCADE|LOCAL] CHECK OPTION]
- 查询创建视图的语句
SHOW CREATE VIEW 视图名称
- 查看视图数据
SELECT * FROM 视图名称......;
- 修改
CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH[CASCADE|LOCAL] CHECK OPTION]
ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH[CASCADE|LOCAL] CHECK OPTION]
- 删除
DROP VIEW [IF EXISTS] 视图名称 [视图名称]...

视图检查选项
当使用WITH CHECK OPTION子句创建视图时，MySQL会通过视图检查正在更改的每个行，使其符合视图的定义。
为了确定检查范围，mysql提供了两个选项：CASCADED和LOCAL，默认为CASCADED
cascaded会递归检查，不管有没有加检查选项
local会递归检查

作用
简单，视图不仅可以简化用户对数据的理解，也可以简化他们的操作。可以把常用的查询指定为视图。
安全，数据库可以授权，但不能授权到特定的行和列，通过视图用户只能查询和修改他们所能见到的数据
数据独立，视图可以帮助用户屏蔽真实表结构变化带来的影响