网站关键词分布,专业提供网站建设服务包括,全国论坛网站建设,wordpress自动压缩图片文章目录 1. SQL性能分析1.1 SQL执行频率1.2 慢查询日志1.3 profile详情1.4 explain 2. SQL优化2.1 Insert 优化2.2 Group By 优化2.3 Order By 优化2.4 Limit 优化2.5 Count() 优化2.6 Update 优化 3. 拓展3.1 请你说一下MySQL中的性能调优的方法#xff1f;3.2 执行 SQL 响应… 文章目录 1. SQL性能分析1.1 SQL执行频率1.2 慢查询日志1.3 profile详情1.4 explain 2. SQL优化2.1 Insert 优化2.2 Group By 优化2.3 Order By 优化2.4 Limit 优化2.5 Count() 优化2.6 Update 优化 3. 拓展3.1 请你说一下MySQL中的性能调优的方法3.2 执行 SQL 响应比较慢你有哪些排查思路 1. SQL性能分析
1.1 SQL执行频率 MySQL 客户端连接成功后通过 show [session|global] status 命令可以提供服务器状态信息。通过如下指令可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次 SHOW GLOBAL STATUS LIKE Com_______;通过上述指令我们可以查看到当前数据库到底是以查询为主还是以增删改为主从而为数据库优化提供参考依据。 如果是以增删改为主我们可以考虑不对其进行索引的优化。 如果是以查询为主那么就要考虑对数据库的索引进行优化了。
1.2 慢查询日志 慢查询日志记录了所有执行时间超过指定参数long_query_time单位秒默认10秒的所SQL语句的日志。 MySQL的慢查询日志默认没有开启我们可以查看一下系统变量 slow_query_log。如果要开启慢查询日志需要在MySQL的配置文件/etc/my.cnf中配置如下信息 # 开启MySQL慢日志查询开关
slow_query_log1
# 设置慢日志的时间为2秒SQL语句执行时间超过2秒就会视为慢查询记录慢查询日志
long_query_time2查看慢日志文件中记录的信息/var/lib/mysql/localhost-slow.log。
1.3 profile详情 show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数能够看到当前MySQL是否支持profile操作 SELECT have_profiling ;可以看到当前MySQL是支持 profile操作的但是开关是关闭的。可以通过set语句在session/global级别开启profiling SET profiling 1;案例执行一系列的业务SQL的操作然后通过如下指令查看指令的执行耗时 -- 查看每一条SQL的耗时基本情况
show profiles;
-- 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
-- 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;查看每一条SQL的耗时情况 查看指定SQL各个阶段的耗时情况 :
1.4 explain EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息包括在 SELECT 语句执行过程中表如何连接和连接的顺序。 EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;Explain 执行计划中各个字段的含义: 字段含义idselect查询的序列号表示查询中执行select子句或者是操作表的顺序 (id相同执行顺序从上到下id不同值越大越先执行)。select_type表示 SELECT 的类型常见的取值有 SIMPLE简单表即不使用表连接 或者子查询、PRIMARY主查询即外层的查询、 UNIONUNION 中的第二个或者后面的查询语句、 SUBQUERYSELECT/WHERE之后包含了子查询等type表示连接类型性能由好到差的连接类型为NULL、system、const、 eq_ref、ref、range、 index、all 。possible_key显示可能应用在这张表上的索引一个或多个。key实际使用的索引如果为NULL则没有使用索引。key_len表示索引中使用的字节数 该值为索引字段最大可能长度并非实际使用长 度在不损失精确性的前提下 长度越短越好 。rowsMySQL认为必须要执行查询的行数在innodb引擎的表中是一个估计值 可能并不总是准确的。filtered表示返回结果的行数占需读取行数的百分比 filtered 的值越大越好。
2. SQL优化
2.1 Insert 优化 使用一条SQL语句插入多条数据而不是多条SQL语句 在 MySQL 中插入多条数据有 2 种方式。第一种是使用一个 INSERT 语句插入多条数据。INSERT 语句的情形如下 INSERT INTO items(name,city,price,number,picture) VALUES (耐克运动鞋,广州,500,1000,001.jpg),(耐克运动鞋 2,广州 2,500,1000,002.jpg);第二种是一个 INSERT 语句只插入一条数据执行多个 INSERT 语句来插入多条数据。INSERT 语句的情形如下 INSERT INTO items(name,city,price,number,picture) VALUES(耐克运动鞋,广州,500,1000,001.jpg);
INSERT INTO items(name,city,price,number,picture) VALUES(耐克运动鞋 2,广州,500,1000,002.jpg);一次性插入多条数据和多次插入数据所耗费的时间是不一样的。第一种方式减少了与数据库之间的连接等操作其速度比第二种方式要快一些。所以插入大量数据时建议使用第一种方法。 手动提交事务MySQL 的事务自动提交模式默认是开启的其对 MySQL 的性能也有一定得影响。比如你插入了 1000 条数据MySQL 就会提交 1000 次这大大影响了插入数据的速度。而如果我们把自动提交关掉通过程序来控制只要一次提交就可以了。 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;主键顺序插入性能要高于乱序插入 主键乱序插入 : 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 ;2.2 Group By 优化
在分组操作中我们需要通过以下两点进行优化以提升性能 在分组操作时可以通过索引来提高效率。分组操作时索引的使用也是满足最左前缀法则的。
2.3 Order By 优化 MySQL的排序有两种方式 Using filesort : 通过表的索引或全表扫描读取满足条件的数据行然后在排序缓冲区sort buffer中完成排序操作所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。 Using index : 通过有序索引顺序扫描直接返回有序数据这种情况即为 using index不需要额外排序操作效率高。 以上两种Using index的性能高而Using filesort的性能低我们在优化排序操作时尽量要优化为 Using index。 优化原则 根据排序字段建立合适的索引多字段排序时也遵循最左前缀法则。尽量使用覆盖索引。多字段排序, 一个升序一个降序此时需要注意联合索引在创建时的规则ASC/DESC。如果不可避免的出现filesort大数据量排序时可以适当增大排序缓冲区大小 sort_buffer_size(默认256k)。
2.4 Limit 优化 在数据量比较大时如果进行limit分页查询在查询时越往后分页查询效率越低。 优化思路: 一般分页查询时通过创建 覆盖索引 能够比较好地提高性能可以通过覆盖索引子查询形式进行优化。 select * from tb_sku t , (select id from tb_sku order by id limit 2000000,10) a where t.id a.id;2.5 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(星)。
2.6 Update 优化 尽量使用索引作为修改的限制条件这样MySQL的锁是行锁性能更高否则行锁将升级为表锁。 案例表中有两个字段分别是id和name其中只有id为主键索引。 行锁 update course set name javaEE where id 1 ;表锁 update course set name SpringBoot where name PHP ;3. 拓展
3.1 请你说一下MySQL中的性能调优的方法 Mysql 性能调优方法可以从四个方面来说分别是表结构与索引、SQL 语句优化、Mysql 参数优化、硬件及系统配置。
硬件及系统配置硬件方面、主要是 CPU 核数、磁盘的读写性能减小寻道时间、旋转时间、传输时间可以选择 SSD、网卡、内存等方面。MySQL参数优化主要可以设置 Buffer_pool 的大小建议占总内存的 70%左右。设置刷盘策略平衡好数据安全性和性能的关系等。SQL语句优化 通过慢查询分析需要优化的 SQL 进行合理优化、利用 explain、profile 等工具分析 SQL执行计划。少用Select * ,多用Count(*)避免返回不必要的数据列 。尽量使用表连接代替子查询表连接尽量使用内连接inner join而不是外连接left join、right join,内接时以小表驱动大表。 表结构与索引 表结构优化 有些表在设计时设置了很多的字段而有些字段的使用频率非常低。这样当这个表的数据量很大时查询数据的速度 就会很慢。对于这种情况我们可以将这些使用频率较低的字段分离出来形成新表。表连接会降低数据库的查询速度所以对于经常使用表连接查询的表我们可以建立中间表来提高查询速度。一般情况下设计数据库时应尽量让表符合三大范式。但是有时为了提高查询速度可以有意识地在表中增加冗余字段。选择合适的字段类型比如char比varchar性能更高、tinyint比int占据空间更小。 索引方面的优化及相关知识可以参考博主的另一篇文章MySQL-索引INDEX-CSDN博客。 架构设计层面的优化MySQL 是一个磁盘 IO 访问量非常频繁的关系型数据库在高并发和高性能的场景中。MySQL 数据库必然会承受巨大的并发压力而此时我们的优化方式可以分为几个部分。 搭建 Mysql 主从集群单个 Mysql 服务容易单点故障一旦服务器宕机将会导致依赖 Mysql 数据库的应用全部无法响应。 主从集群或者主主集群可以保证服务的高可用性。读写分离设计在读多写少的场景中通过读写分离的方案可以避免读写冲突导致的性能影响。引入分库分表机制通过分库可以降低单个服务器节点的 IO 压力通过分表的方式可以降低单表数据量从而提升 sql 查询的效率。针对热点数据可以引入更为高效的分布式数据库比如 Redis、MongoDB 等他们可以很好的缓解 Mysql 的访问压力同时还能提升数据检索性能。
3.2 执行 SQL 响应比较慢你有哪些排查思路 如果执行 SQL 响应比较慢我觉得可能有以下 4 个原因 索引失效首先可以打开 MySQL 的慢查询日志收集一段时间的慢查询日志内容然后找出耗时最长的 SQL 语句对这些 SQL 语句进行分析。 比如可以利用执行计划 explain 去查看 SQL 是否有命中索引。如果发现慢查询的 SQL 没有命中索引可以尝试去优化这些 SQL 语句保证 SQL 走索引执行。如果 SQL 结构没有办法优化的话可以考虑在表上再添加对应的索引。 单表数据量数据过多导致查询瓶颈的情况。即使 SQL 语句走了索引表现性能也不会特别好。这个时候我们需要考虑对表进行切分。表切分规则一般分为两种一种是水平切分一种是垂直切分。 水平切分把一张数据行数达到千万级别的大表按照业务主键切分为多张小表这些小表可能达到 100 张甚至 1000 张。 垂直切分将一张单表中的多个列按照业务逻辑把关联性比较大的列放到同一张表中去。 网络原因或者机器负载过高的情况我们可以进行读写分离比如 MySQL 支持一主多从的分布式部署我们可以将主库只用来处理写数据的操作而多个从库只用来处理读操作。在流量比较大的场景中可以增加从库来提高数据库的负载能力从而提升数据库的总体性能。 热点数据导致单点负载不均衡的情况除了对数据库本身的调整以外还可以增加缓存。将查询比较频繁的热点数据预存到缓存当中比如 Redis、MongoDB、ES 等以此来缓解数据的压力从而提高数据库的响应速度。
