个人网站尺寸,商标设计logo图案设计软件,成都网站改版,合肥seo建站要想程序跑的快#xff0c;sql优化不可懈怠#xff01;今日来总结一下常用的慢sql的分析和优化的方法。
1、慢sql的执行分析#xff1a;
大家都知道分析一个sql语句执行效率的方法是用explain关键词#xff1a; 举例#xff1a;sql:select * from test where bussiness_…要想程序跑的快sql优化不可懈怠今日来总结一下常用的慢sql的分析和优化的方法。
1、慢sql的执行分析
大家都知道分析一个sql语句执行效率的方法是用explain关键词 举例sql:select * from test where bussiness_date ‘2024-10-30’; 分析 explain select * from test where bussiness_date ‘2024-10-30’ 分析执行结果 给business_date 加上一个索引索引名称test_bussiness_date_IDX 再执行分析 对照着这两个分析结果下边来说下每一列都是什么意思
id
在一个大的查询语句中每个select关键字都对应一个id代表多个表之间的查询顺序或者包含子查询语句中的顺序id顺序分为3中情况 1id相同执行顺序从上到下因为小编举例都是单表所以分析结果只有一行数据如果sql中包括子查询或者多表联查分析结果会有多行感兴趣读者可以试试 2id不同如果是子查询id值会递增执行顺序是从大到小的。id越大优先级越高越先被执行。 3id相同和不同的同时存在此时id相同的为一组id越大越优先执行组内id相同从上到下依次执行比较复杂的sql会出现这种情况。
select_type
select关键字对应的查询类型示例中的 SIMPLE 表示简单子查询。不光这一种类型具体类型解释如下 1simple 简单子查询 2primary 最外层子查询 3subquery 第一层子查询再select 或者where中包含了子查询。 4derived 派生表 再from中包含的子查询会被标记为衍生查询会把查询结果放到一个临时表中。 5union 出现在union后面的查询 6union result union联合查询获取结果的select如果有两个select查询语句他们之间用union连接起来查询那么第二个select会被标记为unionunion的结果被标记为union result。
table
表名表示这一行的数据是那个表的如果有别名会显示别名。
partitions
访问的分区表信息。
type优化重要信息字段
针对单表的访问方法一般来说保证查询至少达到range级别最好达到ref。type其他类型详解 性能从好–差依次是 system-const-eq_ref-ref-range-index-all. 如果出现index或者all就需要优化了以下对每个值做详细的解释 1system表中只有一行记录system是const的特例几乎不会出现这种情况可以忽略不计。 2const: 将主键索引或者唯一索引放到where条件中查询mqsql可以将查询条件转变成一个常量只匹配一行数据索引依次就找到数据了。 3eq_ref: 同ref差不多但返回结果只有一条记录。 4ref不是主键索引也不是唯一索引就是普通的索引可能会返回多个符合条件的行。 5range只用一个索引来选择行key列显示所用的索引名称。 6index也是读取全表但是是从索引中读取。 7all全表查询从磁盘中读取。效率最差。
possible_keys
可能用到的索引查询中涉及字段上若存在索引则会被列出来表示可能用到的索引但是并不是实际上一定会用到的索引。
key
实际用到的索引。
key_len
表示索引中使用的字节数。通过该属性可以知道在查询中使用的索引长度这个长度是最大可能长度并非实际使用长度在不损失准确性的情况下长度越短查询效率越高。
ref
关联id等信息。当使用索引列等值查询时与索引列进行等值匹配的对象信息。
rows
预估的需要读取的记录条数。根据表信息统计及索引的使用情况大致估算找到所需记录需要读取的行数row越小越好。
filtered
查到到所需记录占总扫描记录数的比例。
Extra优化重要信息字段
Extra一些额外的信息。 当此字段中出现以下的两个值时意味着mqSql根本不能使用索引效率会收到重大影响要尽可能的对此进行优化。 Using fileSort(使用文件排序) 和 Using temporary使用临时表 下面对每个值进行详细的解释 1Using index使用了覆盖索引避免访问了表的数据行效率不错。如果同时出现了Using where表明索引被用来执行索引键值的查找如果没有同时出现Using where表明索引用来读取数据而非执行查找动作。 索引覆盖有两种理解方式1就是select的数据列只用从索引中就能取得不必读取数据行Mqsql可以利用索引返回select列表中的字段而不必根据索引再次读取数据文件换句话说也就是查询列要被所建的索引列覆盖。2索引是高效找到行的一个方法但是一般数据库也能使用索引找到一个列的数据因此它不必读取整个行毕竟索引叶子节点存储了他们索引的数据当然通过读取索引就可以得到想要的数据那就不需要读取行了一个索引包含了满足查询结果的数据就叫做覆盖索引。 2Using index condition用了条件索引索引下推 3Using where从索引查出来数据后继续使用where条件过滤。 4Using join buffer(Block Nested Loop)join的时候利用了join buffer优化方法去掉外连接增大join buffer的大小 5Using fileSort重点优化用了文件排序俗称“文件排序”排序的时候没有用到索引在数据量大的时候几乎是“九死一生”在order by 或者group by过程中order by的字段不是索引字段或者select查询字段存在不是索引字段或者select 查询字段都是索引字段但是order by的字段顺序和select的索引字段的顺序不一致都会导致fileSort。 6Using temporary重点优化用了临时表保存中间结果常见于order by和group by中优化方法增加条件以减少结果集、怎讲索引总之就是要么减少待排序的数量要么提前排好序 7Start temporary,End temporary子查询的时候可以优化成半连接但是使用的是通过临时表来去重。 8FirstMatch(tb1_name)子查询的时候可以优化成半连接但是使用的是直接进行数据比较来去重。 9impossible where表示where子句的值总是false不能用来获取任何元素。 10select tables optimized away 在没有group by 子句的情况下基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化Count(*)操作不必等到执行阶段再进行计算查询执行计划生成的阶段即完成优化。 11distinct优化distinct,在找到第一匹配的元组后即停止找同样值的工作。
2、常见的慢sql优化手段
1select * 语句减少使用会增加很多不必要的消耗cpu,io,内存,网络带宽等可以在select语句后指明具体的字段名称增加了使用覆盖索引的可能性 2排序时注意是否能用到索引确保order by和group by涉及的列上有索引尽量减少排序和分组操作的数据量。 3使用like模糊查询的时候尽量使用最左匹配模式即like ‘abc%’,这样可以使用索引。 4避免对where语句中字段使用函数或运算会导致索引失效高版本的mysql数据库函数也可以使用索引 5 在多条件查询的时候最好创建联合索引因为多个单列索引在多条件查询时一般只会生效一个索引mysql会选择其中一个限制最为严格的作为索引。 6对于联合索引要遵守最左前缀法则使用联合索引的一部分字段时需要考虑最左原则否则会失效如果使用全部字段就不需要考虑了不会失效 7使用合理的分页方式以提高分页的效率。 8使用limit限制返回的行数如只需要一条数据使用limit 1; 9对大表进行分区可根据时间、范围、哈希等方式分区查询时只扫描相关分区减少不必要的数据扫描。 10避免使用临时表 11优化联合查询在联合查询中尽量能使用UNION ALL。UNION会去除重复的记录需要额外的排序和去重操作性能较差。UNION ALL直接合并结果集不进行去重性能更好。如果业务逻辑允许邮箱使用UNION。 12拆分大字段将包含大字段如BLOB,TEXT的表进行拆分将大字段放在单独的表中已减少主表的存储开销和查询压力。 13读写分离将读操作和写操作分离通过主从复制实现读写分离减少主库压力提高读写性能。 14根据业务需求从业务逻辑入手优化查询需求和频率避免不必要的频繁查询。
