MySQL--SQL优化和视图笔记

– SQL优化

1.插入数据：
insert优化：
批量插入：
建议一次性插入不要超过1000条
语法： insert into 表名 values(),(),();

手动提交事务：
语法：
start transaction:
insert into …;
insert into …;
insert into …;
insert into …;
commit;

主键顺序插入：
建议顺序插入而不是乱序插入；

大批量插入数据：
如果一次性需要插入大批量的数据，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入。操作如下：
客户端连接服务端时，加上参数 --local-infile:
mysql --local-infile -u root -p;
设置全局参数local_infile为1，开启从本地加载文件导入数据的开关:
set global local_infile=1;
执行load指令将准备好的数据，加载到表结构中:
load data local infile ‘文件路径’ into table ‘表名’ fields terminated by ‘,’ lines terminated by ‘\n’;

2.主键优化：
数据组织方式：
在InnoDB引擎中，表数据都是根据主键顺序组织存放的，这种储存方式的表称为索引组织表；
页分裂：
页可以为空，也可以填充一半，也可以填充100%。每个页包含了2-N行数据（如果一行数据过大，会出现行溢出），根据主键排列；
页合并：
当删除一行记录时，实际上记录并没有被删除，只是记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用；
当页中删除的记录达到merge_threshold（默认为页的50%），InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。
（merge——threshold：合并页的阈值，可以自己设置，在创建表或者创建索引时指定。）
主键设计原则：
满足业务需求的情况下，尽量减低主键的长度。
插入数据时，尽量选择顺序插入，选择使用auto_increment自增主键。
尽量不要使用UUID做主键或者是其他自然主键，如身份证号。
业务操作时，尽量避免修改主键。

3.order by优化：
Using filesort: 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序
都叫FileSort排序；
Using index： 通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。
根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
尽量使用覆盖索引。
多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC、DESC）。
如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认256k)。

4.group by优化
建立索引，跳过临时表；
索引，多字段分组满足最左前缀法则。

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

6.count语句优化：
MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count()的时候会直接返回这个数，效率很高。
InnoDB引擎就比较麻烦，它执行count()的时候，需要把数据一行一行地从引擎里面读出来，然后累计计数。
优化思路：自己计数。
count的几种用法：
count()是一个聚合函数，一行行地判断，如果count函数的参数不是null，累计值就加1，否则不加，最后返回累计值。
用法：count()、count(主键)、count(字段)、count(1);
按照效率排行：count（字段）<count（主键id）<count（1）= count（），所以尽量使用count（*）。

7.update优化：
InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行数升级为表锁。
一定要根据索引字段去更新，避免行锁升级为表锁，降低数据库并发访问性能。

视图

视图：
介绍：视图是一种虚拟存在表，视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中试用的表，并且是在使用视图时动态生成的。
通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。
创建视图：
select [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：当通过视图更改或创建行中的内容时，cascaded检查选项是，如果当前视图有检查选项，则插入数据要满足包括当前视图条件以及满足所依赖的视图的
条件。如果当前视图没有检查选项，则插入数据要满足当时视图速依赖视图有检查选项及其依赖的视图的条件。

local：local和cascaded的不同之处在于，local向上检查时不会带入之前的条件，而是每一层单独判断条件，也就是拿递归的方式去判断。

视图的更新：
要使视图可更新，视图中的行与基础表的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新：
1.聚合函数或窗口函数(sum(),min(),max(),count())等；
2.distinct;
3.group by;
4.having;
5.union 或 union all;

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