MySQL 学习笔记 进阶（SQL优化，视图，存储过程 上）
SQL优化
SQL优化-插入数据
- insert优化
· 批量插入
insert into tb_user values(1, 'Tom'), (2, 'Cat'), (3, 'Jerry');
· 手动提交事务
start transaction; insert into tb_user values(1, 'Tom'), (2, 'Cat'), (3, 'Jerry'); insert into tb_user values(4, 'Tom'), (5, 'Cat'), (6, 'Jerry'); insert into tb_user values(7, 'Tom'), (8, 'Cat'), (9, 'Jerry'); commit;
· 主键顺序插入
主键乱序插入：8 1 9 21 88 2 4 15 89 5 7 3 主键顺序插入：1 2 3 4 5 7 8 9 15 21 88 89
- 大批量插入数据
如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的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';
主键顺序插入性能高于乱序插入
SQL优化-主键优化
- 数据组织方式
在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（index organized table IOT）。
- 页分裂
页可以为空，也可以填充一半，也可以填充100%。每个页包含了2-N行数据（如果一行数据多大，会行溢出），根据主键排列。
主键乱序插入会产生页分裂
- 页合并
当删除一条记录时，实际上记录并没有被物理删除，只是被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用。
当页中删除的记录达到MERGE_THRESHOLD（默认为页的50%），InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个合页并以优化空间使用。
MERGE_THRESHOLD：合并页的阈值，可以自己设置，在创建表或者创建索引时指定。
- 主键设计原则
· 满足业务需求的情况下，尽量降低主键的长度。
· 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。
· 尽量不要使用UUID做主键或是其他自然主键，如身份证号。
· 业务操作时，避免对主键的修改。
SQL优化-order by优化
- Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort排序。
- Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。
- 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
- 尽量使用覆盖索引。
- 多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。
- 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区的大小sort_buffer_size（默认256k）。
SQL优化-group by优化
- 在分组操作时，可以通过索引来提高效率。
- 分组操作时，索引的使用也是满足最左前缀法则的。
SQL优化-limit优化
一个常见又非常头疼的问题就是limit 2000000, 10，此时需要MySQL排序前2000010记录，仅仅返回2000000~2000010的记录，其他记录丢弃，查询排序的代价非常大。
优化思路：一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。
explain select * from tb_sku t, (select id from tb_sku order by id limit 2000000, 10) a where t.id = a.id;
SQL优化-count优化
explain select count(*) from tb_user;
- MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高。
- InnoDB引擎就麻烦了，它执行count(*)的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。
优化思路：自己计数。
- count的几种用法
· count()是一个聚合函数，对于返回的结果集，一行行地判断，如果count函数的参数不是NULL，累计值就加1，否则不加，最后返回累计值。
· 用法：count(*)、count(主键)、count(字段)、count(1)
· count(主键)：InnoDB引擎会遍历整张表，把每一行的主键id都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加（主键不可能为null）。
· count(字段)
没有not null约束：InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加。
有not null约束：InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。
· count(1)：InnoDB引擎会遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加。
· count(*)：InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。
SQL优化-update优化
InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁。
视图
视图-介绍及基本语法
视图（view）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。
通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。
- 创建
CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH[CASCADED | LOCAL] CHECK OPTION]
- 查询
查看创建视图语句：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] 视图名称 [,视图名称] ...
视图-检查选项
- 视图的检查选项
当使用WITH CHECK OPTION子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如插入、更新、删除，以使其符合视图的定义。MySQL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，MySQL提供了两个选项：CASCADED和LOCAL，默认值为CASCADED。
CASCADED：
LOCAL：
视图-更新及作用
- 视图的更新
要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下一项，则该视图不可更新：
- 聚合函数或窗口函数（SUM()、MIN()、MAX()、COUNT()等）
- DISTINCT
- GROUP BY
- HAVING
- UNION 或者 UNION ALL
- 作用
· 简单
视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部条件。
· 安全
数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据。
· 数据独立
视图可帮助用户屏蔽真实表结构变化带来的影响。
存储过程
存储过程-介绍
- 介绍
存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。
存储过程思想上很简单，就是数据库SQL语言层面的代码封装与重用。
- 特点
· 封装，复用
· 可以接收参数，也可以返回数据
· 减少网络交互，效率提升
存储过程-基本语法
- 创建
CREATE PROCEDURE 存储过程名称 ([ 参数列表 ]) BEGIN -- SQL语句 END ;
- 调用
CALL 名称 ([ 参数 ]);
- 查看
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'xxx'; -- 查询指定数据库的存储过程及状态信息 SHOW CREATE PROCEDURE 存储过程名称; -- 查询某个存储过程的定义
- 删除
DROP PROCEDURE [ IF EXISTS ] 存储过程名称;
注意：在命令行中，执行创建存储过程的SQL时，需要通过关键字delimiter指定SQL语句的结束符。
存储过程-变量-系统变量
系统变量是MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量（GLOBAL）、会话变量（SESSION）。
· 查看系统变量
SHOW [ SESSION | GLOBAL ] VARIABLES; -- 查看所有系统变量 SHOW [ SESSION | GLOBAL ] VARIABLES LIKE '......'; -- 可以通过LIKE模糊匹配方式查找变量 SELECT @@[ SESSION | GLOBAL ] 系统变量名; -- 查看指定变量的值
· 设置系统变量
SET [ SESSION | GLOBAL ] 系统变量名 = 值; SET @@[ SESSION | GLOBAL ] 系统变量名 = 值;
注意：
如果没有指定SESSION/GLOBAL，默认是SESSION，会话变量。
mysql服务器重新启动之后，所设置的全局参数会失效，要想不失效，可以在 /etc/my.cnf 中配置。
存储过程-变量-用户自定义变量
用户自定义变量是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用“@变量名”使用就可以。其作用域为当前连接。
- 赋值
SET @var_name = expr [, @var_name = expr] ...; SET @var_name := expr [, @var_name = expr] ...;
SELECT @var_name := expr [, @var_name := expr] ...; SELECT 字段名 INTO @var_name FROM 表名;
- 使用
SELECT @var_name;
存储过程-变量-局部变量
局部变量是根据需要定义的在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的BEGIN ... END块。
- 声明
DECLARE 变量名 变量类型 [DEFAULT ...];
变量类型就是数据库字段类型：INT、BIGINT、CHAR、VARCHAR、DATE、TIME等
- 赋值
SET 变量名 = 值; SET 变量名 := 值; SELECT 字段名 INTO 变量名 FROM 表名 ...;
