MySQL数据库6——SQL优化

一.SQL优化

1.插入优化

• 优化1：批量插入

  insert into 表名 values(记录1),(记录2),……;
  

• 优化2：手动提交事务

  start transaction;
  insert into 表名 values(记录1),(记录2);
  insert into 表名 values(记录1),(记录2);
  ……
  commit;
  

• 优化3：主键顺序插入

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

2.主键优化

页分裂：

• 页可以为空，也可以填充一半，也可以填充100%
• 主键顺序插入
• 如果某一行数据较多，再插入会发生数据溢出，会产生新的页进而移动一办再插入，产生页分裂

页合并：

• 当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记(flaged)为删除并且它的空间变得允许被其他记录声明使用

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

主键设计原则：

• 满足业务需求的情况下，尽量降低主键的长度。
• 插入数据时，尽量选择顺序插入，选择使用AUTO INCREMENT自增主键
• 尽量不要使用UUID（Java中随机数函数）做主键或者是其他自然主键，如身份证号
• 业务操作时，避免对主键的修改。

3.order by优化

MySQL两种排序逻辑方式：

• Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSot排序
• Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。

创建索引时，系统默认是按字段升序建立索引结构，但你可以指定排序方式

当使用order by排序时存在对应的索引结构，那么extra对应的额外信息应该是using index

这里的前提是覆盖查询，即查询信息满足在一个二级索引结构里面而不需要回表查询

4.group by优化

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

5.limit优化

常见又非常头疼的问题就是limit 2000000,10，此时需要MySQL排序前2000010 记录，仅仅返回2000000-2000010的记录，其他记录丢弃，查询排序的代价非常大。
优化思路：一般分页查询时，通过创建覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。

6.count优化

• MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高
• InnoDB引擎就麻烦了，它执行count()的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数

count的几种用法：

• 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(*)。

7.update优化

innoDB三大特性：事务、外键、行级锁。

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

二.视图

1.视图语法

​ 视图(View)是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。通俗的讲，视图只保存了查询的SOL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条SOL查询语句上。

创建视图：

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

• 如果创建是新视图，替换旧视图，加上[OR REPLACE]选项
• 把SELECT语句中的查询表叫基表

查看视图：

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];               #方式二

• 方式一类似于创造时的覆盖重写

删除视图：

DROP VIEW [IF EXISTS] 视图名称[(列名列表)];

2.检查选项（cascaded）

视图理解（暂时）：视图相当于从基表中分离出来的一个虚拟子表，可以对视图进行增删改查操作，就等于对这个虚拟子表进行增删改查等操作，但是由于基表和子表数据的一致性，子表的增删改查一定会引起基表的操作，但是子表是受限的基表数据，对视图进行操作时需要检查是否受限，所以有检查选项。

当使用WITH CHECK OPTION子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如 插入、更新、删除，以使其符合视图的定
义。MySQL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，mysql提供了两个选项：CASCADED 和 LOCAL，默认值为 CASCADED。

CASCADED翻译是级联，他代表要向上满足各级依赖的条件，无论上级依赖是否定义检查选项。

3.检查选项（local）

LOCAL选项，也需要向上递归判断是否满足依赖的条件，但是如果某一级依赖没有定义检查选项，则该级依赖的条件无效。

4.更新及作用

要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新：

• 1.聚合函数或窗口函数 （SUM()、MIN()、MAX()、COUNT()等）
• 2.DISTINCT
• 3.GROUP BY
• 4.HAVING
• 5.UNION 或者 UNION ALL

作用：

• 简单：视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。
• 安全：数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据
• 数据独立：视图可帮助用户屏蔽真实表结构变化带来的影响。

5.案例

-- 1. 为了保证数据库表的安全性，开发人员在操作tb_user表时，只能看到的用户的基本字段，屏蔽手机号和邮箱两个字段。
create view tb_user_view as select id,name,profession,age,gender,status,createtime from tb_user;select * from tb_user_view;-- 2. 查询每个学生所选修的课程（三张表联查），这个功能在很多的业务中都有使用到，为了简化操作，定义一个视图。create view tb_stu_course_view as select s.name student_name , s.no student_no , c.name course_name from student s, student_course sc , course c where s.id = sc.studentid and sc.courseid = c.id;select * from tb_stu_course_view;