优秀的字体设计网站,商业合作及运营方案,北京注册公司哪个区好,手机网站建设在哪儿SQL优化
插入数据优化
如果我们需要一次性往数据库表中插入多条记录#xff0c;可以从以下三个方面进行优化。
insert into tb_test values(1,tom);
insert into tb_test values(2,cat);
insert into tb_test values(3,jerry);-- 优化方案一#xff1a;批量插入数据
Inser…SQL优化
插入数据优化
如果我们需要一次性往数据库表中插入多条记录可以从以下三个方面进行优化。
insert into tb_test values(1,tom);
insert into tb_test values(2,cat);
insert into tb_test values(3,jerry);-- 优化方案一批量插入数据
Insert into tb_test values(1,Tom),(2,Cat),(3,Jerry);-- 优化方案二手动控制事务
start transaction;
insert into tb_test values(1,Tom),(2,Cat),(3,Jerry);
insert into tb_test values(4,Tom),(5,Cat),(6,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开启从本地加载文件导入数据的开关
select 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 ;主键优化
在上一小节我们提到主键顺序插入的性能是要高于乱序插入的。 这一小节就来介绍一下具体的原因然后再分析一下主键又该如何设计。 1). 数据组织方式 在InnoDB存储引擎中表数据都是根据主键顺序组织存放的这种存储方式的表称为索引组织表(index organized table IOT)。行数据都是存储在聚集索引的叶子节点上的。
在InnoDB引擎中数据行是记录在逻辑结构 page 页中的而每一个页的大小是固定的默认16K。那也就意味着 一个页中所存储的行也是有限的如果插入的数据行row在该页存储不小将会存储到下一个页中页与页之间会通过指针连接。
2). 页分裂
页可以为空也可以填充一半也可以填充100%。每个页包含了2-N行数据(如果一行数据过大会行溢出)根据主键排列。
A. 主键顺序插入效果
①. 从磁盘中申请页 主键顺序插入
②. 第一个页没有满继续往第一页插入
③. 当第一个也写满之后再写入第二个页页与页之间会通过指针连接
④. 当第二页写满了再往第三页写入B. 主键乱序插入效果
涉及移动数据到新页重新设置链表指针。3). 页合并
当删除一行记录时实际上记录并没有被物理删除只是记录被标记flaged为删除并且它的空间变得允许被其他记录声明使用。
当页中删除的记录达到 MERGE_THRESHOLD默认为页的50%InnoDB会开始寻找最靠近的页前或后看看是否可以将两个页合并以优化空间使用。删除数据并将页合并之后再次插入新的数据。
MERGE_THRESHOLD合并页的阈值可以自己设置在创建表或者创建索引时指定。
4). 索引设计原则 满足业务需求的情况下尽量降低主键的长度。 插入数据时尽量选择顺序插入选择使用AUTO_INCREMENT自增主键。 尽量不要使用UUID做主键或者是其他自然主键如身份证号。 业务操作时避免对主键的修改。
3.3 order by优化 MySQL的排序有两种方式 Using filesort : 通过表的索引或全表扫描读取满足条件的数据行然后在排序缓冲区sort buffer中完成排序操作所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
Using index : 通过有序索引顺序扫描直接返回有序数据这种情况即为 using index不需要额外排序操作效率高。
对于以上的两种排序方式Using index的性能高而Using filesort的性能低我们在优化排序操作时尽量要优化为 Using index。
drop index idx_user_phone on tb_user;
drop index idx_user_phone_name on tb_user;
drop index idx_user_name on tb_user;-- Extra 以下两个查询均为 Using filesort
explain select id,age,phone from tb_user order by age ;
explain select id,age,phone from tb_user order by age, phone ;-- 创建age,phone的索引
create index idx_user_age_phone_aa on tb_user(age,phone);
explain select id,age,phone from tb_user order by age; -- ExtraUsing index
explain select id,age,phone from tb_user order by age , phone; -- ExtraUsing index-- 根据age, phone进行降序排序
-- ExtraBackward index scan;Using index
-- Backward index scan这个代表反向扫描索引在MySQL8版本中支持降序索引我们也可以创建降序索引
explain select id,age,phone from tb_user order by age desc , phone desc ; -- 根据phoneage进行升序排序phone在前age在后。 排序时,也需要满足最左前缀法则,否则也会出现 filesort。
-- ExtraUsing index; Using filesort 违背最左前缀索引失效创建的agephone索引
explain select id,age,phone from tb_user order by phone , age;-- 根据age, phone进行降序一个升序一个降序
-- 因为创建索引时如果未指定顺序默认都是按照升序排序的而查询时一个升序一个降序此时就会出现Using filesort。
explain select id,age,phone from tb_user order by age asc , phone desc ;
创建联合索引(age 升序排序phone 倒序排序)
-- 创建联合索引(age 升序排序phone 倒序排序)
create index idx_user_age_phone_ad on tb_user(age asc ,phone desc);
explain select id,age,phone from tb_user order by age asc , phone desc ; -- ExtraUsing index由上述的测试,我们得出order by优化原则: A. 根据排序字段建立合适的索引多字段排序时也遵循最左前缀法则。 B. 尽量使用覆盖索引。 C. 多字段排序, 一个升序一个降序此时需要注意联合索引在创建时的规则ASC/DESC。 D. 如果不可避免的出现filesort大数据量排序时可以适当增大排序缓冲区大小sort_buffer_size(默认256k)。
group by优化
分组操作我们主要来看看索引对于分组操作的影响。
drop index idx_user_pro_age_sta on tb_user;
drop index idx_email_5 on tb_user;
drop index idx_user_age_phone_aa on tb_user;
drop index idx_user_age_phone_ad on tb_user;pdf 55 blbl 91-34
出现 Using temporary时使用到临时表 性能较低
-- ExtraUsing temporary; Using filesort
explain select profession,count(*) from tb_user group by profession;-- 对于 profession age status 创建一个联合索引
create index idx_user_pro_age_sta on tb_user(profession , age , status);
-- Extra Using index
explain select profession,count(*) from tb_user group by profession;-- 单独查age 不满足最左前缀法则
-- ExtraUsing index; Using temporary; Using filesort 性能不高explain select age,count(*) from tb_user group by age;-- 联合查profession,age
-- Extra Using index
explain select profession,age,count(*) from tb_user group by profession,age;-- 添加where条件提高效率
-- Extra Using where; Using index
explain select age,count(*) from tb_user where profession 软件工程 group by age;
所以在分组操作中我们需要通过以下两点进行优化以提升性能
A. 在分组操作时可以通过索引来提高效率。
B. 分组操作时索引的使用也是满足最左前缀法则的。
limit优化
在数据量比较大时如果进行limit分页查询在查询时越往后分页查询效率越低。
优化思路: 一般分页查询时通过创建 覆盖索引 能够比较好地提高性能可以通过覆盖索引加子查询形式进行优化。
select * from tb_sku limit 10; -- 10 rows in set (0.00 sec) 很快
select * from tb_sku limit 5000000,10; -- 10 rows in set (42.42 sec) 很慢-- 优化查询
select id from tb_sku order by id limit 5000000,10; -- 10 rows in set (18.69 sec)
-- 将上面的结果看成临时表优化后-- 10 rows in set (17.09 sec)
select * from tb_sku t , (select id from tb_sku order by id limit 5000000,10) a where t.id a.id;count优化
3.6.1 概述
MyISAM 引擎把一个表的总行数存在了磁盘上因此执行 count(*) 的时候会直接返回这个数效率很高 但是如果是带条件的countMyISAM也慢。
InnoDB 引擎就麻烦了它执行 count(*) 的时候需要把数据一行一行地从引擎里面读出来然后累积计数。主要的优化思路自己计数(可以借助于redis这样的数据库进行,但是如果是带条件的count又比较麻烦了)。
count用法
count() 是一个聚合函数对于返回的结果集一行行地判断如果 count 函数的参数不是NULL累计值就加 1否则不加最后返回累计值。
用法count*、count主键、count字段、count数字
-- 尽量使用count(*)
按照效率排序的话count(字段) count(主键 id) count(1) ≈ count(*)count(主键)InnoDB 引擎会遍历整张表把每一行的 主键id 值都取出来返回给服务层。服务层拿到主键后直接按行进行累加(主键不可能为null)count(字段)没有not null 约束 : InnoDB 引擎会遍历整张表把每一行的字段值都取出来返回给服务层服务层判断是否为null不为null计数累加。有not null 约束InnoDB 引擎会遍历整张表把每一行的字段值都取出来返回给服务层直接按行进行累加。count(数字)InnoDB 引擎遍历整张表但不取值。服务层对于返回的每一行放一个数字“1”进去直接按行进行累加。count(*)InnoDB引擎并不会把全部字段取出来而是专门做了优化不取值服务层直接按行进行累加。update优化
我们主要需要注意一下update语句执行时的注意事项。
InnoDB的行锁是针对索引加的锁不是针对记录加的锁 ,并且该索引不能失效否则会从行锁升级为表锁 。
-- 如下执行删除的SQL语句时会锁定id为1这一行的数据然后事务提交之后行锁释放。
begin;
update course set name javaEE where id 1 ;
-- 新的终端开启事务执行执行是OK的
begin;
update course set name javaEE where id 2 ;-- name无索引 行锁升级为了表锁。 导致该update语句的性能大大降低
update course set name SpringBoot where name PHP ;
-- create index idx_coruse_name on course(name); 创建索引后再执行不会锁表。视图
视图View是一种虚拟存在的表。视图中的数据并不在数据库中实际存在行和列数据来自定义视图的查询中使用的表并且是在使用视图时动态生成的。 通俗的讲视图只保存了查询的SQL逻辑不保存查询结果。所以我们在创建视图的时候主要的工作就落在创建这条SQL查询语句上。
4.1.2 语法
1). 创建
CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]
-- 创建视图
create or replace view stu_v_1 as select id,name from student where id 10;2). 查询
查看创建视图语句SHOW CREATE VIEW 视图名称;
查看视图数据SELECT * FROM 视图名称 ...... ;
show create view stu_v_1;
select * from stu_v_1;
select * from stu_v_1 where id 3;3). 修改
方式一CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]
方式二ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]
create or replace view stu_v_1 as select id,name,no from student where id 10;
alter view stu_v_1 as select id,name from student where id 10;4). 删除
DROP VIEW [IF EXISTS] 视图名称 [,视图名称] ...
drop view if exists stu_v_1;-- 插入数据
insert into stu_v_1 values(6,Tom); -- 插入后可以查询到
insert into stu_v_1 values(17,Tom22); -- 查不到数据创建视图时的条件为 id104.1.3 检查选项 使用WITH CHECK OPTION子句创建视图时MySQL会通过视图检查正在更改的每个行不符合执行失败。 MySQL允许基于另一个视图创建视图它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围mysql提供了两个选项 CASCADED 默认级联检查依赖的视图和 LOCAL本地检查。
4.1.4 视图的更新 要使视图可更新视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一 项则该视图不可更新
A. 聚合函数或窗口函数SUM()、 MIN()、 MAX()、 COUNT()等
B. DISTINCT
C. GROUP BY
D. HAVING
E. UNION 或者 UNION ALL1
create view stu_v_count as select count(*) from student;
insert into stu_v_count values(10); --插入失败视图作用
1). 简单 视图不仅可以简化用户对数据的理解也可以简化他们的操作。那些被经常使用的查询可以被定义为视图从而使得用户不必为以后的操作每次指定全部的条件。 2). 安全 数据库可以授权但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据从而将敏感的字段隔离开。 3). 数据独立 视图可帮助用户屏蔽真实表结构变化带来的影响。
-- 案例
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;
