这篇文章主要是就在公司实习的时候,对SQL优化工作作出的一些整理。 在公司实习的时候,导师分配了SQL慢查询优化的任务,任务是这样的:每周从平台中导出生产数据库的慢查询文件进行分析。...(1)数据库中设置SQL慢查询 一、第一步.开启mysql慢查询 方式一: 修改配置文件 在 my.ini 增加几行: 主要是慢查询的定义时间(超过2秒就是慢查询...使用多列索引的查询语句 MySQL可以为多个字段创建索引。一个索引最多可以包括16个字段。对于多列索引,只有查询条件使用了这些字段中的第一个字段时,索引才会被使用。...一个非常令人头疼问题就是当偏移量非常大的时候,例如可能是limit 10000,20这样的查询,这是mysql需要查询10020条然后只返回最后20条,前面的10000条记录都将被舍弃,这样的代价很高。...对于下面的查询: select id,title from collect limit 90000,10; 该语句存在的最大问题在于limit M,N中偏移量M太大(我们暂不考虑筛选字段上要不要添加索引的影响
SQL145题系列 程序员在编程过程中,经常会在代码中使用到where 1=1,这是为什么呢? SQL注入 初次看到这种写法的同学肯定很纳闷,加不加where 1=1,查询不都一样吗?...是的,上面的查询结果是没有区别,但是这并不是我们要添加它的目的。我们知道1=1表示true,即永真,在SQL注入时配合or运算符会得到意想不到的结果。...拷贝表 在我们进行数据备份时,也经常使用到where 1=1,当然其实这两可以不写,写上之后如果想过滤一些数据再备份会比较方便,直接在后面添加and条件即可。...1=1可能会对有所影响,使用了where 1=1的过滤条件以后数据系统就无法使用索引等查询优化策略,数据库系统将会被迫对每行数据进行扫描(即全表扫描)以比较此行是否满足过滤条件,当表中数据量较大时查询速度会非常慢...但在5.6版本(也可能更早几个版本)以后这个问题被优化了,在写where 1=1时,查询分析器会将1=1处理掉,所以不会对查询造成性能影响,感兴趣的小伙伴可以试验一下,反正我试过了。
问题是一句非常简单的sql. select * from tk_template_product t WHERE t.product_id=1135 前提: product_id已经添加了索引, 可依然慢的无法接受...在这种情况下,可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。...“文件排序” Using join buffer:改值强调了在获取连接条件时没有使用索引,并且需要连接缓冲区来存储中间结果。...如果出现了这个值,那应该注意,根据查询的具体情况可能需要添加索引来改进能。 Impossible where:这个值强调了where语句会导致没有符合条件的行。...这个步骤, 能够得出的结论是, 我的这个sql语句使用了缓存, 缓存字段是product_id, 但是并没有显示出为什么会这么慢 3.
SQL 没加索引 很多时候,我们的慢查询,都是因为没有加索引。如果没有加索引的话,会导致全表扫描的。因此,应考虑在 where 的条件列,建立索引,尽量避免全表扫描。...3. limit 深分页问题 limit 深分页问题,会导致慢查询,应该大家都司空见惯了吧。 3.1 limit 深分页为什么会变慢 limit 深分页为什么会导致 SQL 变慢呢?...如果 B + 树想存储更多的数据,那树结构层级就会更高,查询一条数据时,需要经历的磁盘 IO 变多,因此查询性能变慢。...反例: select user_id,name from user where user_id in (1,2,3…1000000); 如果我们对 in的条件不做任何限制的话,该查询语句一次性可能会查询出非常多的数据...10. delete + in 子查询不走索引! 之前见到过一个生产慢 SQL 问题,当 delete 遇到 in 子查询时,即使有索引,也是不走索引的。
const:通过一次索引就能找到数据,一般用于主键或唯一索引作为条件,这类扫描效率极高,,速度非常快。...在联合索引中,只有查询条件满足最左匹配原则时,索引才正常生效。如下,查询条件列是user_id 2.3 案例3:深分页问题 limit深分页问题,会导致慢查询,应该大家都司空见惯了吧。...反例: select user_id,name from user where user_id in (1,2,3...1000000); 如果我们对in的条件不做任何限制的话,该查询语句一次性可能会查询出非常多的数据...索引一般建议分批进行,一次200个,比如: select user_id,name from user where user_id in (1,2,3...200); in查询为什么慢呢?...之前见到过一个生产慢SQL问题,当delete遇到in子查询时,即使有索引,也是不走索引的。而对应的select + in子查询,却可以走索引。
我拿到上面的SQL在数据库执行发现是走了agent_code索引的,并且查询效率也是正常的,然后删掉了筛选条件,执行结果也是一样。 上面分别是带条件和不带条件的执行计划,可以看到没有什么区别。...,下面的for循环执行也不会特别慢。...这时我突然想到会不会是分页导致的,我们都知道limit在offset非常大的情况下会导致查询慢,但我们这里还没有翻页,也就是第一页,所以不是这个问题。...因为我这条SQL是查询主键索引倒序结果,索引天然有序,不用排序了,所以看到执行计划里面的Extra字段没有了Using filesort,这里比普通索引快,但是这条SQL是有where条件筛选的,那么在拿到有序结果后...看到这里相信读者应该基本明白了,如果没有limit,那么这条SQL会出现全表扫描;而这里有limit 0,30,就会出现下面的情况:一是和where条件匹配的30条记录刚好是排序后的前30条,那么mysql
SQL没加索引 很多时候,我们的慢查询,都是因为没有加索引。如果没有加索引的话,会导致全表扫描的。因此,应考虑在where的条件列,建立索引,尽量避免全表扫描。...3. limit深分页问题 limit深分页问题,会导致慢查询,应该大家都司空见惯了吧。 3.1 limit深分页为什么会变慢 limit深分页为什么会导致SQL变慢呢?...如果B+树想存储更多的数据,那树结构层级就会更高,查询一条数据时,需要经历的磁盘IO变多,因此查询性能变慢。...反例: select user_id,name from user where user_id in (1,2,3...1000000); 如果我们对in的条件不做任何限制的话,该查询语句一次性可能会查询出非常多的数据...10. delete + in子查询不走索引! 之前见到过一个生产慢SQL问题,当delete遇到in子查询时,即使有索引,也是不走索引的。而对应的select + in子查询,却可以走索引。
看图表慢查询在高峰达到了每分钟14w次,在平时正常情况下慢查询数仅在两位数以下,如下图: 赶紧查看慢SQL记录,发现都是同一类语句导致的慢查询(隐私数据例如表名,我已经隐去): select * from...为何突然出现异常慢查询 问:这个查询语句已经在线上稳定运行了非常长的时间,为何这次突然出现了慢查询? 答:以前的语句查询条件返回结果都不为空,limit1很快就能找到那条数据,返回结果。...而这次代码中查询条件实际结果为空,导致了扫描了全部的主键索引。 解决方案 知道了MySQL为何选择这个索引的原因后,我们就可以根据上面的思路来列举出解决办法了。...SQL优化是个很大的工程,我们还有非常多的办法能够解决这句慢查询问题,这里就不一一展开了。留给大家做为思考题了。...平时开发时,尤其是对于特大数据量的表,要注意SQL语句的规范和索引的建立,避免事故的发生。
能够大大提高查询效率。特别是当数据量非常大,查询涉及多个表时,使用索引往往能使查询速度加快成千上万倍....我们可以去mysql的data目录下找到该表,可以看到添加索引后该表占用的硬盘空间多了 3.如果使用没有添加索引的字段进行条件查询,速度依旧会很慢(如图:) ?...select * from mytable where c=4 and b=6 and a=3; #这条语句列出来只想说明 mysql没有那么笨,where里面的条件顺序在查询之前会被mysql自动优化...连表时注意条件类型需一致 10.索引散列值不适合建索引,例:性别不适合 9.查询计划 explain + 查询SQL - 用于显示SQL执行信息参数,根据参考信息可以进行SQL优化...id>100*10 limit 10; 三.最后第三种方法:延迟关联 我们在来分析一下这条语句为什么慢,慢在哪里。
特别是当数据量非常大,查询涉及多个表时,使用索引往往能使查询速度加快成千上万倍。 ...3.在索引建立完毕后,以字段为查询条件时,查询速度提升很明显 select * from userinfo where id = 4567890; ?...mysql没有那么笨,where里面的条件顺序在查询之前会被mysql自动优化,效果跟上一句一样 select * from mytable where a=3 and c=7; #a用到索引,...连表时注意条件类型需一致 10.索引散列值不适合建索引,例:性别不适合 二、查询与慢日志 1、查询计划 explain + 查询SQL - 用于显示SQL执行信息参数,根据参考信息可以进行SQL优化...我们再来分析一下这条语句为什么慢,慢在哪里 select * from tb1 limit 3000000,10; 玄机就处在这个*里面,这个表除了id主键肯定还有其他字段,比如name age 之类的
上面的对话其实在工作中比较常见(同时也说明我们培训没有到位 T_T),这样的想法会导致开发忽略选择性比较低的字段,sql的执行计划使用 using where 匹配更多的数据行,结果执行耗时比较就,性能不理想...如果只是创建 idx_a(a) ,sql请求通过索引 idx_a 访问 10w 条件记录,然后还要逐一匹配10w条记录中的status,找到符合 b=2 的记录。这个动作会导致慢查。...小结 1 创建索引的目的是sql请求通过索引尽可能快速地找到匹配where条件的行,减少不必要的回表,提高查询效率。 2 需要辩证地看待区分度比较低的字段在组合索引中的作用。...索引的有序性 在优化业务sql的过程中,我们经常发现开发将 order by 的字段添加到组合索引里面,但是依然有 file sort 产生,导致慢查。这是为什么呢?...(a,b)字段在索引中存储的顺序如下图,明显和上面的查询条件的结果顺序不一致,就导致sql执行计划出现额外的排序,数据量比较大的情况(比如5000以上)下就出现慢查。 ?
看图表慢查询在高峰达到了每分钟14w次,在平时正常情况下慢查询数仅在两位数以下,如下图: ?...赶紧查看慢SQL记录,发现都是同一类语句导致的慢查询(隐私数据例如表名,我已经隐去): select * from sample_table where 1 = 1 and (city_id...为何突然出现异常慢查询 问:这个查询语句已经在线上稳定运行了非常长的时间,为何这次突然出现了慢查询? 答:以前的语句查询条件返回结果都不为空,limit1很快就能找到那条数据,返回结果。...`af_hot_price_region` where (city_id = 565 and type = 13)) limit 0, 1 还有很多解决办法… SQL优化是个很大的工程,我们还有非常多的办法能够解决这句慢查询问题...平时开发时,尤其是对于特大数据量的表,要注意SQL语句的规范和索引的建立,避免事故的发生。
上面的对话其实在工作中比较常见(同时也说明我们培训没有到位 T_T),这样的想法会导致开发忽略选择性比较低的字段,sql 的执行计划使用 using where 匹配更多的数据行,结果执行耗时比较,性能就不理想...如果只是创建 idx_a(a),sql 请求通过索引 idx_a 访问 10w 条件记录,然后还要逐一匹配 10w 条记录中的 status,找到符合 b=2 的记录。这个动作会导致慢查。...在组合索引的情况下,我们不能只是单纯地看字段的区分度,而是要看符合条件的记录数是多少。符合条件的记录越少,性能越好。...索引的有序性 在优化业务 sql 的过程中,经常发现开发将 order by 的字段添加到组合索引里面,但是依然有 file sort 产生,导致慢查。这是为什么呢?...(a,b) 字段在索引中存储的顺序如下图,明显和上面的查询条件的结果顺序不一致,就导致 sql 执行计划出现额外的排序,数据量比较大的情况(比如 5000 以上)下就出现慢查。 ?
默认情况下,Mysql数据库并不启动慢查询日志,需要我们手动来设置这个参数,当然,如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响。...日志记录到系统的专用日志表中,要比记录到文件耗费更多的系统资源,因此对于需要启用慢查询日志,又需要能够获得更高的系统性能,那么建议优先记录到文件。...数据库开启慢查询: 二,分析慢查询日志 直接分析mysql慢查询日志 ,利用explain关键字可以模拟优化器执行SQL查询语句,来分析sql慢查询语句 例如:执行 EXPLAIN SELECT...一个非常令人头疼问题就是当偏移量非常大的时候,例如可能是limit 10000,20这样的查询,这是mysql需要查询10020条然后只返回最后20条,前面的10000条记录都将被舍弃,这样的代价很高。...对于下面的查询: select id,title from collect limit 90000,10; 该语句存在的最大问题在于limit M,N中偏移量M太大(我们暂不考虑筛选字段上要不要添加索引的影响
在 my.cnf 文件的 [mysqld] 下增加如下配置开启慢查询,如下图: # 开启慢查询功能 slow_query_log=ON # 指定记录慢查询日志SQL执行时间的阈值 long_query_time...Explain 分析慢查询 SQL 分析 MySQL 慢查询日志,利用 Explain 关键字可以模拟优化器执行 SQL 查询语句,来分析 SQL 慢查询语句。...使用到覆盖索引时,SQL 如下:查询耗时:0.091s,查到 141 条数据。...时进行判断,没 where 条件就去掉 where,有 where 条件就加 and。...⑦查询条件不能用 或者 != 使用索引列作为条件进行查询时,需要避免使用或者!=等判断条件。
为什么在 MySQL数据库中,一条慢查询只要添加上合适的索引,查询速度就能提升一个档次?对于 MySQL,如何巧用索引优化SQL语句性能?需要注意什么问题?...查看执行时间对于已经投入生产使用的 SQL查询语句,我们一般会通过查看 SQL执行日志,通过 SQL执行时间来判断是否存在慢 SQL,在 MySQL中,可以使用下面的指令来开启慢查询日志和设置慢SQL时间阈值...最左前缀可以是联合索引的最左 N 个字段,也可以是字符串索引的最左 M 个字符css复制代码比如:联合索引index(a, b, c)查询条件 where a = ?where a = ?...where条件中的a,c只有a 可以匹配 联合索引的a字段。...语句性能,主要是思路是:先确认慢SQL,可从SQL执行日志,也可以通过 EXPLAIN执行计划通过 EXPLAIN执行计划来确认是否为慢SQL,以及该给哪些字段增加索引最后,在使用索引时,我们提供了一些注意点以及使用技巧
从理论上来说,我们认为得到相同结果的不同SQL之间应该有相同的性能,但遗憾的是,查询优化器生成的执行计划很大程度上受到SQL代码影响,有快有慢。...1.1 子查询用EXISTS代替IN 当IN的参数是子查询时,数据库首先会执行子查询,然后将结果存储在一张临时的工作表里(内联视图),然后扫描整个视图。很多情况下这种做法都非常耗费资源。...WHERE id = 1 OR name = 'tom' 这个SQL的执行条件下,很明显id字段查询会走索引,但是对于OR后面name字段的查询是需要进行全表扫描的。...可能需要说明的是最后一条SQL为什么会走索引,简单转化一下,col_2 = 100 AND col_1 = 10, 这个条件就相当于col_1 = 10 AND col_2 = 100,自然就可以走联合做因...这种高度的相似性使得SQL编程具有非常强的灵活性,但是如果不加限制地大量使用中间表,会导致查询性能下降。
看图表慢查询在高峰达到了每分钟14w次,在平时正常情况下慢查询数仅在两位数以下,如下图: [b0944764-3775-465f-bd9e-c355e7483d72.png] 赶紧查看慢SQL记录,发现都是同一类语句导致的慢查询...为何突然出现异常慢查询 问:这个查询语句已经在线上稳定运行了非常长的时间,为何这次突然出现了慢查询? 答:以前的语句查询条件返回结果都不为空,limit1很快就能找到那条数据,返回结果。...而这次代码中查询条件实际结果为空,导致了扫描了全部的主键索引。 解决方案 知道了MySQL为何选择这个索引的原因后,我们就可以根据上面的思路来列举出解决办法了。...SQL优化是个很大的工程,我们还有非常多的办法能够解决这句慢查询问题,这里就不一一展开了。留给大家做为思考题了。...平时开发时,尤其是对于特大数据量的表,要注意SQL语句的规范和索引的建立,避免事故的发生。
有了前面的《动手》,基本上可以进行开发了。本篇我们来试试XCode的基本功功力如何,测试在单表一千万业务数据的环境下查询的速度,添删改等没什么可测试的。...XCode开发模式非常看重分页,基本上所有集合查询方法都带有分页参数。Entity层只负责生成获取满足条件的所有数据的SQL,加上分页参数后传递给下层数据访问层,自身不处理问题。...业务主键还有经常查询的字段,根据情况建立非聚集索引。在千万数据下,没有索引的字段,基本上查不动。 建立索引时,特别注意包含字段include(不是组合索引)。...在SQLServer管理工具里面建立索引时,似乎无法添加include字段。可以先设置好索引,不要保存,点击生成脚本,然后复制到查询窗口,增加include后再执行。 ...前面的测试,都是简单的没有查询条件的测试,下面我们试试带查询条件的测试 ? 屏幕一闪而过,就这样完了!
领取专属 10元无门槛券
手把手带您无忧上云