SQL优化

插入优化

insert

批量插入

批量插入可以避免频繁地与数据库建立连接，用一次链接就插入大量的数据可以大大的提升效率

insert into 表名 [(字段1,字段2,......,字段n)] values (数据1,数据2,......,数据n)[，(数据1,数据2,......,数据n)，(数据1,数据2,......,数据n)];insert

---

手动提交事务

因为每执行一条插入语句，mysql就会自动的开启一个事务，语句结束就提交事务，即使我们使用批量插入，还是会造成大量的浪费，避免这种情况，我们需要手动的开启和提交事务。

start transaction ;
insert ... ;
insert ... ;
insert ... ;
commit ;

可以节省事务方面的开销

---

主键顺序插入

对于有主键的表格，建议按照主键的顺序插入数据

---

load

对于大量数据的插入，insert就不适用了，我们可以使用load大量的去加载本地文件的数据

使用load需要对数据库进行操作：

# 客户端连接服务端 加上参数 --local-infile
MySQL --local-infile -u root -p
# 设置全局参数 local-infile开关，开启从本地文件导入数据的开关
set global local_infile = 1;
# 使用load语句导入数据
load data local infile "文件路径/文件名" into table '表名' fields terminated by '字段分隔符'
	lines terminated by '行分隔符';

---

主键优化

数据组织方式

表数据都是根据主键顺序存放的，这种存储结构叫做索引组织表

因为聚集索引的默认索引是主键索引，索引下挂的数据是行数据

---

页分裂&页合并

数据按照主键存储，如果插入或者删除不是顺序的，为了维护数据表，就会调用这两个操作，会造成性能损耗。

---

设计原则：

1：尽量降低主键的长度 2：主键尽量顺序插入，避免出现页分裂的情况 3：尽量不使用UUID作为主键，如身份证号 4：业务尽量避免对主键的修改，因为维护索引会造成大量的性能开支

---

order by 优化

首先，排序分为两种排序操作：using filesort 和 using Index

using filesort：通过索引或者全表扫描，获取到数据之后，在缓冲区sort Buffer 中完成排序操作。不是通过索引直接读取到排好序的数据都叫做filesort排序

using Index：很显然，直接诶读取到排好序的数据的操作叫做Index排序，操作效率高

设计原则：

1：根据排序的字段建立适合的索引 2：尽量使用覆盖索引 3：注意创建的规则细节（asc/ desc） 4：不可避免出现filesort时，可以调节缓冲区大小，默认为256k

---

group by 优化

好像也没什么注意的，就是要建立联合索引，满足最左前缀法则之类的

---

limit 分页查询 优化

因为数据结构是B+树的原因，分页查询如果页数比较靠后，就会从开头进行遍历操作，相当浪费时间。

可以创建覆盖索引，通过联合查询或者子查询来解决问题

---

count优化

这个对于不同的引擎，count的效率也会不同 MyISAM 引擎把一个表的总行数存在了磁盘上，count( * )会直接返回 InnoDb 引擎执行count( * )会把表的所有数据读取出来，然后计数

count原理：count会对结果集进行一步步地判断，如果结果不是null，累计值就加一，最后返回累计值

count( * )：数据库做了优化，不会取出一行行的数据，所以尽量使用 count( * )

---

update优化

update在更新数据的时候，为了数据的安全，会对数据进行加锁操作。 数据的加锁操作是根据索引添加的行锁，如果索引失效，就会升级为表锁 如果更新数据的条件可以调用索引查找到，就只对当前行进行加行锁，如果条件不能够用索引查询，就只能对整张表进行加锁，此时，数据库的并发性能就会大大降低。 因此，数据库在更新数据时，筛选条件应该尽量依靠索引，避免行锁升级为表锁。

---