沈阳公司建设网站,wordpress时间代码,国家建筑工程信息平台,wordpress中国企业主题1.日常工作中#xff0c;你是怎么优化SQL的#xff1f;
大家可以从这几个维度回答这个问题#xff1a; 分析慢查询日志 使用explain查看执行计划 索引优化 深分页优化 避免全表扫描 避免返回不必要的数据#xff08;如select具体字段而不是select*#xff09; 使用…
1.日常工作中你是怎么优化SQL的
大家可以从这几个维度回答这个问题 分析慢查询日志 使用explain查看执行计划 索引优化 深分页优化 避免全表扫描 避免返回不必要的数据如select具体字段而不是select* 使用合适的数据类型如可以使用int类型的话就不要设计为varchar 优化sql结构如join优化等等 适当分批量进行 (如批量更新、删除) 定期清理无用的数据 适当分库分表 读写分离
2. 是否遇到过深分页问题如何解决
我们可以通过减少回表次数来优化。一般有标签记录法和延迟关联法。
标签记录法 就是标记一下上次查询到哪一条了下次再来查的时候从该条开始往下扫描。就好像看书一样上次看到哪里了你就折叠一下或者夹个书签下次来看的时候直接就翻到啦。 假设上一次记录到100000则SQL可以修改为
select id,name,balance FROM account where id 100000 limit 10;这样的话后面无论翻多少页性能都会不错的因为命中了id索引。但是这种方式有局限性需要一种类似连续自增的字段。
延迟关联法
延迟关联法就是把条件转移到主键索引树然后减少回表。假设原生SQL是这样的的其中id是主键create_time是普通索引
select id,name,balance from account where create_time 2020-09-19 limit 100000,10;使用延迟关联法优化如下
select acct1.id,acct1.name,acct1.balance FROM account acct1 INNER JOIN
(SELECT a.id FROM account a WHERE a.create_time 2020-09-19 limit 100000, 10)
AS acct2 on acct1.id acct2.id;优化思路就是先通过idx_create_time二级索引树查询到满足条件的主键ID再与原表通过主键ID内连接这样后面直接走了主键索引了同时也减少了回表。
3. 聊聊explain执行计划
当explain与SQL一起使用时MySQL将显示来自优化器的有关语句执行计划的信息。即MySQL解释了它将如何处理该语句包括有关如何连接表以及以何种顺序连接表等信息。
一条简单SQL使用了explain的效果如下 一般来说我们需要重点关注type、rows、filtered、extra、key。
3.1 type
type表示连接类型查看索引执行情况的一个重要指标。以下性能从好到坏依次system const eq_ref ref ref_or_null index_merge unique_subquery index_subquery range index ALL system这种类型要求数据库表中只有一条数据是const类型的一个特例一般情况下是不会出现的。 const通过一次索引就能找到数据一般用于主键或唯一索引作为条件这类扫描效率极高速度非常快。 eq_ref常用于主键或唯一索引扫描一般指使用主键的关联查询 ref : 常用于非主键和唯一索引扫描。 ref_or_null这种连接类型类似于ref区别在于MySQL会额外搜索包含NULL值的行 index_merge使用了索引合并优化方法查询使用了两个以上的索引。 unique_subquery类似于eq_ref条件用了in子查询 index_subquery区别于unique_subquery用于非唯一索引可以返回重复值。 range常用于范围查询比如between ... and 或 In 等操作 index全索引扫描 ALL全表扫描
3.2 rows
该列表示MySQL估算要找到我们所需的记录需要读取的行数。对于InnoDB表此数字是估计值并非一定是个准确值。
3.3 filtered
该列是一个百分比的值表里符合条件的记录数的百分比。简单点说这个字段表示存储引擎返回的数据在经过过滤后剩下满足条件的记录数量的比例。
3.4 extra
该字段包含有关MySQL如何解析查询的其他信息它一般会出现这几个值 Using filesort表示按文件排序一般是在指定的排序和索引排序不一致的情况才会出现。一般见于order by语句 Using index 表示是否用了覆盖索引。 Using temporary: 表示是否使用了临时表,性能特别差需要重点优化。一般多见于group by语句或者union语句。 Using where : 表示使用了where条件过滤. Using index conditionMySQL5.6之后新增的索引下推。在存储引擎层进行数据过滤而不是在服务层过滤利用索引现有的数据减少回表的数据。
3.5 key
该列表示实际用到的索引。一般配合possible_keys列一起看。
注意:有时候explain配合show WARNINGS; 可以查看优化后,最终执行的sql效果更佳哦。
4.说说大表的优化方案 数据库设计优化
合理的数据库设计可以极大地提高查询效率。我们在设计大表时可以考虑拆分表、使用分区表、添加索引等方式来优化表结构。同时也要避免使用大量冗余字段、避免频繁使用join查询等操作。 索引优化
对于大表的查询操作索引优化是非常重要的一环。可以考虑增加或者修改索引、使用覆盖索引、使用联合索引等方式来提高查询效率。同时也要注意定期清理冗余的索引以及对于经常使用的查询语句建立索引。 分区优化
将大表按照某个列分成多个分区表每个分区表的数据量较小可以提高查询和更新的性能。分区表还可以帮助在维护表结构的同时减少锁表时间提高并发处理能力。 数据清理归档
对于一些历史数据或者无用数据可以进行定期归档避免数据过多造成SQL查询效率降低。同时也要注意对于大表进行定期的数据备份以及紧急数据恢复的准备工作。 缓存优化
对于一些经常被查询的数据可以使用缓存优化。使用Redis等缓存中间件来缓存常用的数据以减少查询数据库的次数提高查询效率。 SQL语句优化
在编写SQL查询语句时要尽可能地简单明了避免复杂的查询语句同时也要避免一些不必要的查询操作。对于复杂的查询语句可以使用Explain执行计划来进行优化。同时也要注意避免使用OR等耗费性能的操作符。 分库分表
如果数据量千万级别需要考虑分库分表哈。分库分表相关知识点可以看我之前这篇文章哈我们为什么要分库分表
5.哪些因素可能导致MySQL慢查询
慢查询一般有以下这些原因 大家有兴趣可以看下。我之前写的这篇文章哈盘点MySQL慢查询的12个原因
6.如何使用索引优化SQL查询 添加合适索引在where、group by、order by等后面的字段添加合适索引 选择合适的索引类型 (B-tree索引适合范围查询、哈希索引适合等值查询) 注意不适合加索引的场景数据量少的表更新频繁的字段区分度低的字段 加索引的时候需要考虑覆盖索引减少回表考虑联合索引的最左前缀原则 explain查看SQL的执行计划确认是否会命中索引。 注意索引并不是越多越好通常建议在单个表中不要超过5个索引。因为索引会占用磁盘空间索引更新代价高。
7.聊聊慢SQL的优化思路 查看慢查询日志记录分析慢SQL explain分析SQL的执行计划 profile 分析执行耗时 Optimizer Trace分析详情 确定问题并采用相应的措施
7.1 查看慢查询日志记录分析慢SQL
如何定位慢SQL呢、我们可以通过slow log来查看慢SQL。默认的情况下呢MySQL数据库是不开启慢查询日志slow query log呢。所以我们需要手动把它打开。
查看下慢查询日志配置我们可以使用show variables like slow_query_log%命令如下 slow query log表示慢查询开启的状态 slow_query_log_file表示慢查询日志存放的位置
我们还可以使用show variables like long_query_time命令查看超过多少时间才记录到慢查询日志如下 long_query_time表示查询超过多少秒才记录到慢查询日志。
我们可以通过慢查日志定位那些执行效率较低的SQL语句重点关注分析。
7.2 explain查看分析SQL的执行计划
当定位出查询效率低的SQL后可以使用explain查看SQL的执行计划。
当explain与SQL一起使用时MySQL将显示来自优化器的有关语句执行计划的信息。即MySQL解释了它将如何处理该语句包括有关如何连接表以及以何种顺序连接表等信息。
一条简单SQL使用了explain的效果如下 一般来说我们需要重点关注type、rows、filtered、extra、key。
7.3 profile 分析执行耗时
explain只是看到SQL的预估执行计划如果要了解SQL真正的执行线程状态及消耗的时间需要使用profiling。开启profiling参数后后续执行的SQL语句都会记录其资源开销包括IO上下文切换CPU内存等等我们可以根据这些开销进一步分析当前慢SQL的瓶颈再进一步进行优化。
profiling默认是关闭我们可以使用show variables like %profil%查看是否开启如下 可以使用set profilingON开启。开启后可以运行几条SQL然后使用show profiles查看一下。 show profiles会显示最近发给服务器的多条语句条数由变量profiling_history_size定义默认是15。如果我们需要看单独某条SQL的分析可以show profile查看最近一条SQL的分析。也可以使用show profile for query id其中id就是show profiles中的QUERY_ID查看具体一条的SQL语句分析。 除了查看profile 还可以查看cpu和io如上图。
7.4 Optimizer Trace分析详情
profile只能查看到SQL的执行耗时但是无法看到SQL真正执行的过程信息即不知道MySQL优化器是如何选择执行计划。这时候我们可以使用Optimizer Trace它可以跟踪执行语句的解析优化执行的全过程。
我们可以使用set optimizer_traceenabledon打开开关接着执行要跟踪的SQL最后执行select * from information_schema.optimizer_trace跟踪如下 大家可以查看分析其执行树会包括三个阶段 join_preparation准备阶段 join_optimization分析阶段 join_execution执行阶段 7.5 确定问题并采用相应的措施 最后确认问题就采取对应的措施。 多数慢SQL都跟索引有关比如不加索引索引不生效、不合理等这时候我们可以优化索引。 我们还可以优化SQL语句比如一些in元素过多问题分批深分页问题基于上一次数据过滤等进行时间分段查询 SQl没办法很好优化可以改用ES的方式或者数仓。 如果单表数据量过大导致慢查询则可以考虑分库分表 如果数据库在刷脏页导致慢查询考虑是否可以优化一些参数跟DBA讨论优化方案 如果存量数据量太大考虑是否可以让部分数据归档
我之前写了一篇文章有关于导致慢查询的12个原因大家看一下哈:盘点MySQL慢查询的12个原因
8.一条sql执行过长的时间你如何优化从哪些方面入手
这道面试题其实跟慢SQl排查解决有点像所以大家回答得时候可以参考上一小节哈。我们可以从这几个方面入手哈 确定瓶颈 索引优化 优化SQL语句 数据库参数优化 分析锁的情况 数据库硬件升级
确定瓶颈
首先通过查看MySQL日志慢查询日志explain分析SQL的执行计划profile 分析执行耗时Optimizer Trace分析详情等操作确定查询执行的瓶颈在哪里。只有确定了瓶颈才能有针对性地进行优化。
索引优化
在确定了瓶颈之后可以考虑通过增加索引来优化查询效率。可以根据查询语句的条件增加相应的索引从而加快查询速度。但是索引也会带来一些负面影响如占用磁盘空间降低写入效率等所以需要根据具体情况权衡。
优化SQL语句
有些SQL语句本身可能存在一些问题如join操作过于频繁使用了不必要的子查询等这些都会导致查询效率低下。可以通过优化SQL语句来减少不必要的操作从而提高查询效率。
数据库参数优化
数据库参数也会影响查询效率可以通过修改数据库参数来优化查询效率如修改内存缓存大小、修改连接池大小等。不同的数据库参数优化方式不同需要根据具体情况进行调整。
分析锁的情况
查询执行时间过长有可能是由于锁的问题导致的需要分析查询语句中是否存在锁的问题如果存在锁的问题可以考虑增加锁的并发度从而提高查询效率。
数据库硬件升级
如果以上方法都无法解决问题可以考虑对数据库硬件进行升级如增加 CPU 数量、加快磁盘读写速度等从而提高数据库的整体性能。
9. 列举一下常用的数据库设计优化技巧 字段尽量避免使用NULL 合理选择数据类型 字段选择合适的长度 正确使用索引 尽量少定义text类型 合理的数据表结构设计 适当的冗余设计 优化SQL查询语句 一张表的字段不宜过多
10.列举日常开发中列举十个书写高质量SQL的小技巧 查询SQL尽量不要使用select *而是select具体字段。 小表驱动大表 优化你的like语句 尽量避免在索引列上使用mysql的内置函数 如果插入数据过多考虑批量操作。 多用limit 小表驱动大表 exist in合理利用 in元素不要过多 尽量用union all替换union
大家可以参考我之前这篇文章哈 后端程序员必备书写高质量SQL的30条建议
11.index merge了解过嘛
index merge是什么 在MySQL中当执行一个查询语句需要使用多个索引时MySQL可以使用索引合并(Index Merge)来优化查询性能。具体来说索引合并是将多个单列索引或多个联合索引合并使用以满足查询语句的需要。 当使用索引合并时MySQL会选择最优的索引组合来执行查询从而避免了全表扫描和排序操作提高了查询效率。而对于使用多个单列索引的查询语句MySQL也可以使用索引合并来优化查询性能。
大家可以看一个使用index merge的例子
假设有一个名为orders的表包含order_id、customer_id、product_id、order_date等字段其中order_id、customer_id、product_id三个字段都建有索引。
如果要查询customer_id为1order_date在2022年1月1日到2022年2月1日之间的订单记录可以使用以下SQL语句
SELECT *
FROM orders
WHERE customer_id 1
AND order_date 2022-01-01
AND order_date 2022-02-01在执行该查询语句时MySQL可以使用customer_id索引和order_date索引来优化查询。如果使用单个索引则需要扫描整个索引树来匹配查询条件但如果使用索引合并则可以先使用customer_id索引来过滤出符合条件的记录然后再使用order_date索引来进一步过滤记录从而大大减少了扫描的记录数提高了查询效率。
大家可以使用EXPLAIN关键字可以查看查询计划确认是否使用了索引合并。例如执行以下语句
EXPLAIN SELECT *
FROM orders
WHERE customer_id 1
AND order_date 2022-01-01
AND order_date 2022-02-01如果查询计划中出现了Using index merge的信息则表示该查询使用了索引合并优化。
12. order by查询效率慢,如何优化.
大家是否还记得order by查询为什么会慢嘛?
order by排序分为全字段排序和rowid排序。它是拿max_length_for_sort_data和结果行数据长度对比如果结果行数据长度超过max_length_for_sort_data这个值就会走rowid排序相反则走全字段排序。
rowid排序一般需要回表去找满足条件的数据所以效率会慢一点.如果是order by排序,可能会借助磁盘文件排序的话效率就更慢一点.
如何优化order by的文件排序? 因为数据是无序的所以就需要排序。如果数据本身是有序的那就不会再用到文件排序啦。而索引数据本身是有序的我们通过建立索引来优化order by语句。 我们还可以通过调整max_length_for_sort_data、sort_buffer_size等参数优化
大家忘记order by的话,可以看我之前的这篇文章哈:看一遍就理解order by详解
13. group by 查询慢的话,如何优化呀.
group by一般用于分组统计它表达的逻辑就是根据一定的规则进行分组。日常开发中我们使用得比较频繁。如果不注意很容易产生慢SQL。
group by可能会慢在哪里因为它既用到临时表又默认用到排序。有时候还可能用到磁盘临时表。 如果执行过程中会发现内存临时表大小到达了上限控制这个上限的参数就是tmp_table_size会把内存临时表转成磁盘临时表。 如果数据量很大很可能这个查询需要的磁盘临时表就会占用大量的磁盘空间。
如何优化group by呢? group by 后面的字段加索引 order by null 不用排序 尽量只使用内存临时表 使用SQL_BIG_RESULT
大家可以看下我这篇文章哈看一遍就理解group by详解
