青岛网站建设市场分析,百度关键词排名用什么软件,北京城乡建设部网站首页,江苏建设管理信息网站#x1f468;#x1f393;作者简介#xff1a;一位大四、研0学生#xff0c;正在努力准备大四暑假的实习 #x1f30c;上期文章#xff1a;MySQL进阶#xff1a;强推#xff0c;冲大厂必精通#xff01;MySQL索引底层#xff08;BTree#xff09;、性能分析、使用… 作者简介一位大四、研0学生正在努力准备大四暑假的实习 上期文章MySQL进阶强推冲大厂必精通MySQL索引底层BTree、性能分析、使用规则 订阅专栏MySQL进阶 希望文章对你们有所帮助 上一篇文章介绍了MySQL底层的索引以及如何巧用索引去优化MySQL的搜索性能但是我们的业务除了查询可能还涉及大量的增、改、计数的操作他们的调优方式也是有不少区别的。 大厂高频面试——各类SQL语句性能调优经验 插入数据insert大批量插入数据 主键优化order by优化group by优化limit优化count优化概述count用法 update优化防止行锁升级成表锁 插入数据
insert
如果我们需要一次性往数据库表中插入多条记录可以从以下三个方面进行优化。
1、批量插入数据
Insert into tb_test values(1,Tom),(2,Cat),(3,Jerry);2、手动控制事务MySQL自动控制事务的方式是每次在insert之前开启事务插入后再关闭事务频繁insert就会多次开关闭事务导致时间浪费
start transaction; -- 手动开启事务
insert into tb_test values(1,Tom),(2,Cat),(3,Jerry);
insert into tb_test values(4,Tom),(5,Cat),(6,Jerry);
insert into tb_test values(7,Tom),(8,Cat),(9,Jerry);
commit;3、主键顺序插入性能会高于乱序插入思考二级索引的叶子节点挂载了主键的id且是顺序的、以双向链表的方式连接的
大批量插入数据
如果一次性需要插入大批量数据(比如: 几百万的记录)使用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 ; sql1.log中的数据也尽量让主键是顺序排列的将会比乱序排列的方式插入更高效。
主键优化
刚刚提到主键顺序插入的性能是要高于乱序插入的在这里会分析一下原因再分析一下主键该如何设计
1、数据的组织方式 在InnoDB存储引擎中表数据都是根据主键顺序组织存放的这种存储方式的表称为索引组织表 行数据都是存储在聚集索引的叶子节点上的。而我们之前也讲解过InnoDB的逻辑结构图 在InnoDB引擎中数据行是记录在逻辑结构 page 页中的而每一个页的大小是固定的默认16K。 那也就意味着一个页中所存储的行也是有限的如果插入的数据行row在该页存储不小将会存储到下一个页中页与页之间会通过指针连接。
2、页分裂 页可以为空也可以填充一半也可以填充100%。每个页包含了2-N行数据(如果一行数据过大会行溢出)根据主键排列。
1主键顺序插入的效果演示 ①从磁盘中申请页 主键顺序插入 ②第一个页没有满继续往第一页插入 ③当第一个页写满之后再写入第二个页页与页之间会通过指针连接 ④当第二页写满了再往第三页写入
2主键乱序插入效果演示 ①加入1#,2#页都已经写满了存放了如图所示的数据 ②此时再插入id为50的记录并不会开启一个新的页因为索引结构的叶子节点是有顺序的按照顺序应该存储在47之后由于47所在的1#页已经写满了存储不了50对应的数据了。 那么此时会开辟一个新的页3#但是并不会直接将50存入3#页而是会将1#页后一半的数据移动到3#页然后在3#页插入50。 移动数据并插入id为50的数据之后那么此时这三个页之间的数据顺序是有问题的。 1#的下一个页应该是3# 3#的下一个页是2#。 所以此时需要重新设置链表指针。 整体的流程如下所示非常繁琐显然是非常耗费性能的 3、页合并 目前表中已有数据的索引结构(叶子节点)如下 当删除一行记录时实际上记录并没有被物理删除只是记录被标记flaged为删除并且它的空间变得允许被其他记录声明使用。 当页中删除的记录达到 MERGE_THRESHOLD默认为页的50%InnoDB会开始寻找最靠近的页前或后看看是否可以将两个页合并以优化空间使用。 删除数据并将页合并之后再次插入新的数据21则直接插入3#页 MERGE_THRESHOLD合并页的阈值可以自己设置在创建表或者创建索引时指定。 4、索引设计原则
满足业务需求的情况下尽量降低主键的长度。因为聚集索引只有一个但是二级索引有很多个每个二级索引树的叶子节点都挂载了主键主键太长就会浪费空间插入数据时尽量选择顺序插入选择使用AUTO_INCREMENT自增主键。尽量不要使用UUID做主键或者是其他自然主键如身份证号。乱序不适合业务操作时避免对主键的修改
order by优化
MySQL的排序有两种方式 1Using filesort : 通过表的索引或全表扫描读取满足条件的数据行然后在排序缓冲区sort buffer中完成排序操作所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。 2Using index : 通过有序索引顺序扫描直接返回有序数据这种情况即为 using index不需要额外排序操作效率高。 因此我们在优化排序操作的适合尽量要优化为Using index。
测试
1、数据准备 把之前测试时为tb_user表所建立的部分索引直接删除掉
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;2、执行排序
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 都没有索引所以此时再排序时出现Using filesort 排序性能较低
3、创建索引
-- 创建索引
create index idx_user_age_phone_aa on tb_user(age,phone);4、创建索引后根据age, phone进行升序排序
explain select id,age,phone from tb_user order by age;
explain select id,age,phone from tb_user order by age ,phone;查询方式变为了Using index性能是比较高的。
5、创建索引后根据age, phone进行降序排序
explain select id,age,phone from tb_user order by age desc , phone desc;排序时,也需要满足最左前缀法则,否则也会出现 filesort。因为在创建索引的时候 age是第一个字段phone是第二个字段所以排序时也就该按照这个顺序来否则就会出现Using filesort。 排序查询需要和条件查询区分开条件查询中的where子句之所以无所谓字段的位置只要存在age就可以满足最左前缀法则是因为where中的and是并列的意思而排序查询中的顺序就代表了排序的权重顺序不同排序的结果就不同。 6、根据age, phone进行降序一个升序一个降序
explain select id,age,phone from tb_user order by age asc ,phone desc;因为创建索引时如果未指定顺序默认都是按照升序排序的而查询时一个升序一个降序此时就会出现Using filesort。 为了解决上述的问题我们可以创建一个索引这个联合索引中 age 升序排序phone倒序排序。
7、创建联合索引(age 升序排序phone 倒序排序)
create index idx_user_age_phone_ad on tb_user(age asc,phone desc);8、再次执行如下SQL
explain select id,age,phone from tb_user order by age asc, phone desc;升序/降序联合索引结构图示 从测试中可以得出order by的优化原则
根据排序字段建立合适的索引多字段排序时也遵循最左前缀法则尽量使用覆盖索引否则回表查询还是会发生filesort多字段排序, 一个升序一个降序此时需要注意联合索引在创建时的规则ASC/DESC如果不可避免的出现filesort大数据量排序时可以适当增大排序缓冲区大小sort_buffer_size默认256K
group by优化
分组操作我们主要来看看索引对于分组操作的影响。
首先我们先将 tb_user 表的索引全部删除掉
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;在没有索引的情况下执行下列SQL查看执行计划
explain select profession , count(*) from tb_user group by profession;然后我们在针对于professionagestatus创建一个联合索引。
create index idx_user_pro_age_sta on tb_user(profession,age,status);紧接着再执行前面相同的SQL查看执行计划 再执行如下的分组查询SQL查看执行计划 可以发现如果仅仅根据age分组会出现效率低下的临时表即Using temporary而如果根据profession,age两个字段同时分组则不会出现 Using temporary。原因是因为对于分组操作在联合索引中也是符合最左前缀法则的。
所以在分组操作中我们需要通过以下两点进行优化以提升性能
在分组操作时可以通过索引来提高效率。分组操作时索引的使用也是满足最左前缀法则的。
limit优化
在数据量比较大时如果进行limit分页查询在查询时越往后分页查询效率越低。大家面试可能容易被问到limit的深分页查询效率低下时候的调优策略。
我们可以先通过下面几条测试语句的执行时间来进行分析 这是因为select *的操作在底层会回表排序深分页查询的时候排序的记录很多但是返回的仅仅是后面的10条记录丢弃了之前的所有记录查询排序的代价就非常大。
优化思路: 一般分页查询时通过创建覆盖索引能够比较好地提高性能所以可以通过覆盖索引子查询的方式进行优化
-- 覆盖索引select id from tb_sku order by id limit 2000000,10
-- 上述查询出来的一系列id可以看作是一张表再利用子查询这样就不会发生回表查询后再排序
explain select * from tb_sku t, (select id from tb_sku order by id limit 2000000,10) a where t.id a.id;注意子查询中不能有LIMIT所以不能使用explain select * from tb_sku where id in (select id from tb_sku order by id limit 2000000,10)的方式来优化只能用上述的方式来 count优化
概述
select count(*) from tb_user;若数据量很大上述指令是非常耗时的。
MyISAM 引擎把一个表的总行数存在了磁盘上因此执行 count(*) 的时候会直接返回这个数效率很高但是如果是带条件的countMyISAM也慢InnoDB 引擎就麻烦了它执行 count(*) 的时候需要把数据一行一行地从引擎里面读出来然后累积计数。
如果说要大幅度提升InnoDB表的count效率主要的优化思路自己计数(可以借助于redis这样的数据库进行,但是如果是带条件的count又比较麻烦了)。
count用法
count() 是一个聚合函数对于返回的结果集一行行地判断如果 count 函数的参数不是NULL累计值就加1否则不加最后返回累计值。
用法count*、count主键、count字段、count数字
count用法含义count(主键)InnoDB 引擎会遍历整张表把每一行的 主键id 值都取出来返回给服务层。服务层拿到主键后直接按行进行累加(主键不可能为null)count(字段)没有not null 约束 : InnoDB 引擎会遍历整张表把每一行的字段值都取出来返回给服务层服务层判断是否为null不为null计数累加。有not null 约束InnoDB 引擎会遍历整张表把每一行的字段值都取出来返回给服务层直接按行进行累加。count(数字)InnoDB 引擎遍历整张表但不取值。服务层对于返回的每一行放一个数字“1”进去其他数字也都行直接按行进行累加count(*)InnoDB引擎并不会把全部字段取出来而是专门做了优化不取值服务层直接按行进行累加。
因此按照效率来排序count(字段) count(主键 id) count(1) ≈ count(*)因此尽量使用count(*)操作。
update优化防止行锁升级成表锁
我们需要注意一下下列update语句执行时候的注意事项
update course set name javaEE where id 1 ;当我们执行上述语句的时候会先锁定id1的数据锁住这一行即行锁然后事务提交后行锁解放。
当我们执行下列语句
update course set name SpringBoot where name PHP ;我们可以开启多个事务验证并发性可以发现无法再操作表了这是因为此时行锁被升级成了表锁导致update语句性能大大降低。
要解决这个问题就需要我们的更新操作的条件判断要尽量针对主键或者被建立索引的字段防止行锁被升级成了表锁。
