MySQL 学习笔记 进阶（SQL优化，视图，存储过程 上）

标签：count 存储 进阶 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优化

1. Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort排序。
2. 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：

 

视图-更新及作用

• 视图的更新

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

1. 聚合函数或窗口函数（SUM()、MIN()、MAX()、COUNT()等）
2. DISTINCT
3. GROUP BY
4. HAVING
5. 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 表名 ...;

 

标签：count,存储,进阶,MySQL,视图,SQL,优化,主键
From： https://www.cnblogs.com/handsome-zyc/p/18329725