个人备案经营网站备案吗,网上代理商,wordpress 分类 如何修改,cfa一级看多久一.SQL优化
1.插入优化 优化1#xff1a;批量插入 insert into 表名 values(记录1),(记录2),……;优化2#xff1a;手动提交事务 start transaction;
insert into 表名 values(记录1),(记录2);
insert into 表名 values(记录1),(记录2);
……
commit;优化3#xff1a;主键顺…一.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_infile1;
#执行load指令将准备好的数据加载到表结构中
load data local infile .sql数据库文件路径 into 表名 fields terminated by , lines terminated by \n;2.主键优化
页分裂
页可以为空也可以填充一半也可以填充100%主键顺序插入如果某一行数据较多再插入会发生数据溢出会产生新的页进而移动一办再插入产生页分裂
页合并 当删除一行记录时实际上记录并没有被物理删除只是记录被标记(flaged)为删除并且它的空间变得允许被其他记录声明使用 当页中删除的记录达到 MERGE_THRESHOLD(默认为页的50%)innoDB会开始寻找最靠近的页(前或后)看看是否可以将两个页合并以优化空间使用。
主键设计原则
满足业务需求的情况下尽量降低主键的长度。插入数据时尽量选择顺序插入选择使用AUTO INCREMENT自增主键尽量不要使用UUIDJava中随机数函数做主键或者是其他自然主键如身份证号业务操作时避免对主键的修改。
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.DISTINCT3.GROUP BY4.HAVING5.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;
