一、SQL优化

（一）插入数据

批量插入

多次插入每一次insert都要与数据库建立连接。

INSERT INTO 表名 VALUES (),(),();

一次插入数据不宜过多，不要超过1000条。

手动提交事务

START TRANSACTION;
INSERT INTO 表名 VALUES (),(),();
INSERT INTO 表名 VALUES (),(),();
INSERT INTO 表名 VALUES (),(),();
COMMIT;

主键顺序插入

主键顺序插入效率高于乱序插入。

大批量插入数据

如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入。

--查看开关是否开启
SELECT @@local_infile;
--开启开关
SET GLOBAL local_infile=1;
--加载数据
LOAD DATA LOCAL INFILE '/root/sql1.log' INTO TABLE '表名' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\\n';

（二）主键优化

数据组织方式：在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表。

 页分裂：页可以为空，也可以填充一半，也可以填充到100%。每个页包含了2-N行数据（如果一行数据足够大，会行溢出），根据主键排序。主键乱序插入时可能会发生。

页合并：当删除一行记录时，实际上记录并没有被物理删除，只是被标记（flaged）为删除并且它的空间变的允许被其他记录声明使用。

当页中删除的记录达到MERGE_THRESHOLD（默认为页的50%），InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个合并以优化空间使用。

MERGE_THRESHOLD：合并页的阈值，可以自己设置，在创建表或者创建索引时指定，在创建表和创建索引的时候可以自己指定。

主键设计原则：

满足业务需求的情况下，尽量降低主键的长度。

插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。

尽量不要使用UUID做主键或者是其他自然主键，如身份证号，无序，长度也较长。

业务操作时，避免对主键的修改。

（三）order by优化

Using filesort：通过表的索引或者全表扫描，读取满足条件的数据行，然后再排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的都叫FileSort排序。

Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。

Backward index scan：反向扫描索引。

--根据字段1，字段进行一个降序，一个升序
EXPLAIN SELECT 字段... FROM 表名 ORDER BY 字段1 asc,字段2 desc;
--创建索引
CREATE INDEX idx_字段1_字段2_ad ON 表名(字段1 ASC,字段2 DESC);

优化：

根据排序字段创建合适的索引，多字段排序时，也遵循最左前缀法则。

尽量使用覆盖索引。

多字段排序时，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。

如果不可避免的出现filesort，大数据排序时，可以适当增大排序缓冲区的大小sort_buffer_size（默认256k）。

SHOW VARIABLES LIKE 'sort_buffer_size';

（四）group by优化

Using temporary：用到了临时表。

分组操作时，建立适当索引来提高效率。

分组操作时，索引的使用也是满足最左前缀原则的。

（五）limit优化

一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询的方法优化。

EXPLAIN SELECT a.* FROM 表1 a,(SELECT id FROM 表2 ORDER BY id LIMIT 开始,数量) b WHERE a.id=b.id; 

（六）count优化

在MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高，前提是后面没有where条件。

InnoDB引擎在执行count(*)时，需要把数据一行行地从引擎里面读出来，然后累积计数。

优化思路：没有特别好的优化方案，可以自己计数。

用法：count(*)、count(主键)、count(字段)、count(1)

count(主键)：InnoDB引擎会遍历整张表，把每一行的主键id值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加（主键值不可能为null）。

count(字段)：InnoDB遍历整张表把每一行的字段值都取出来，返回给服务层。如果有not null约束，直接按行进行累加；如果没有not null约束，服务层判断是否为null，不为null，计数累加。

count(1)：InnoDB引擎会遍历整张表，但不取值。服务层对于返回的每一行，都放一个数字“1”进去，直接按行进行累加。

count(*)：InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。

性能：count(*)~count(1)>count(主键)>count(字段)，尽量使用count( *)。

（七）update优化

执行update语句的注意事项：

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

二、视图/存储过程/触发器

（一）视图

视图是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。

视图不保存数据，只保存了SQL的逻辑。我们在创建视图时，主要的工作就在创建这条SQL查询语句上。

创建

CREATE [OR REPLACE] VIEW 视图名称[(列表列名)] AS SELECT语句 [WITH [CASCADED|LOCAL] CHECK OPTION];

封装的就是select查询的数据。

WITH CASCADED CHECK OPTION：防止视图插入的数据不符合创建视图时的条件。

视图的检查选项：当使用WITH CHECK自居创建视图时，MySQL会通过视图检查正在更改的每个行，以使其符合视图的定义。MySQL允许基于另一个视图创建视图，他还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，MySQL提供了两个选项：CASCADED和LOCAL，默认值是LOCAL。

CASCADED：

LOCAL：

查询

--查看创建视图语句
SHOW CREATE VIEW 视图名称;
--查看视图数据
SELECT * FROM 视图名称...;

视图是一种虚拟的表，可以像操作表一样操作视图。

修改

CREATE OR REPLACE VIEW 视图名称{(列名列表)} AS SELECT语句 [WITH [CASCADED|LOCAL] CHECK OPTION];
ALTER VIEW 视图名称{(列名列表)} AS SELECT语句 [WITH [CASCADED|LOCAL] CHECK OPTION];

CREATE OR REPLACE：创建或替换。

删除

DROP VIEW [IF EXISTS] 视图名称 [,视图名称]...;

IF EXISTS：判断是否存在。