文章目录写在前面sql语句片段的执行顺序一、MySQLSQL优化1、时间格式化问题字符串2、in/inner join的问题3、MySQL深分页优化4、批量删除千万级表数据5、like优化问题6、判断数据是否存在7、范围查询优化1大小符号改为between and2联合查询增加范围写在前面sql语句片段的执行顺序FROMleft_tableONjoin_conditionjoin_typeJOINright_tableWHEREwhere_conditionGROUPBYgroup_by_listHAVINGhaving_conditionSELECTDISTINCTselect_listORDERBYorder_by_conditionLIMITlimit_number一、MySQLSQL优化1、时间格式化问题字符串-- 优化前SELECT*FROMtest_tableWHEREdate_format(begin_time,%Y-%m-%d)2025-03-12-- 优化-- 1、加上begin_time字段为普通索引并且优化了sqlSELECT*FROMtest_tableWHEREbegin_timebetweenconcat(2025-03-12, 00:00:00)andconcat(2025-03-12, 23:59:59)原理解释在where条件中对字段进行函数操作即使加了索引也会导致索引失效最终是走全表扫描如果使用between全表扫描会变成范围搜索索引就会正常使用了如果是mysql8版本可以考虑使用函数索引2、in/inner join的问题-- 优化前SELECT*FROMtest_tableWHEREidIN(SELECTmax(id)FROMtest_tableGROUPBYDATE_FORMAT(begin_time,%H时))-- 优化后SELECT*FROMtest_table t1innerjoin(SELECTmax(id)idFROMtest_tableGROUPBYDATE_FORMAT(begin_time,%H时))t2ont1.idt2.id原理解释mysql5版本对于in并不是很友好in参数过多就会走全表扫描。而8版本对in做了优化。如果用的是mysql5版本in的过程比较慢或许尝试用join操作来代替in可能会有奇效同时看情况用exists等能够替代in的方式。3、MySQL深分页优化-- 单表查询涉及回表问题深分页性能会略微下降select*fromt5orderbytextlimit1000000,10;-- 1、优化可以考虑覆盖索引selectid,textfromt5orderbytextlimit1000000,10;-- 2、优化利用索引覆盖 书签记录通过记录上次查询的主键位置直接从该位置继续查询。-- 后续查询假设上次最后一条记录的created_atXidYSELECTid,nameFROMusersWHEREcreated_atXOR(created_atXANDidY)ORDERBYcreated_at,idLIMIT100;-- 1、优化关联查询 可以通过把分页的SQL语句改写成子查询的方法获得性能上的提升-- 这种写法要求主键ID必须是连续的、Where子句不允许再添加其他条件select*fromt5whereid(selectidfromt5orderbytextlimit1000000,1)limit10;-- 或者记录上次查询的位置同样需要条件递增es也可以参考相当于一个查询分为多次select*fromt5whereid1000000limit10;-- 改成between and同样需要条件递增selectid,name,balanceFROMaccountwhereidbetween100000and100010orderbyid;-- 2、优化也可以考虑使用时间因为是天然递增的SELECT*FROMt_orderORDERBYcreate_timeASCLIMIT1000000,1SELECT*FROMt_orderWHEREcreate_time2025-01-01 00:00:00ORDERBYcreate_timedescLIMIT100;-- 3、优化延迟关联先查主表的id然后inner join子表性能提升以上的限制也解除了selecta.*fromt5 ainnerjoin(selectidfromt5orderbytextlimit1000000,10orderbytext)bona.idb.id;4、批量删除千万级表数据-- 删除一个月以前的数据DELETEFROMuser_logWHEREcreate_time2025-05-09;如果是小表以上行为是没问题的但是千万级大表那么delete操作就会进行全表扫描进行大范围的加锁甚至效果相当于锁表而锁表给业务带来的影响就是业务都无法进行写操作了。-- 优化1、分批不断循环查询数据每10条删一次SELECTidFROMuser_logWHEREcreate_time2025-05-09limit10;-- 2、根据ID删除deletefromuser_logwhereid123;5、like优化问题1前缀匹配索引前几个字符索引只存储字段的前 N 个字符减少索引体积加速匹配。前缀长度需根据业务字段长度分布调整通过EXPLAIN验证索引是否被使用。-- 为username字段创建前缀索引取前20个字符ALTERTABLEusersADDINDEXidx_username(username(20));-- 查询优化前缀匹配可利用索引SELECT*FROMusersWHEREusernameLIKEalice%;2全文索引Full-Text Index-- 创建全文索引ALTERTABLEarticlesADDFULLTEXTINDEXidx_content(content);-- 使用MATCH AGAINST替代LIKESELECT*FROMarticlesWHEREMATCH(content)AGAINST(keywordINNATURALLANGUAGEMODE);3反转字符串 前缀索引针对后缀匹配-- 添加反转字符串列ALTERTABLEurlsADDCOLUMNreversed_urlVARCHAR(255)AS(REVERSE(url))STORED;-- 为反转列创建前缀索引ALTERTABLEurlsADDINDEXidx_reversed_url(reversed_url(20));-- 查询时反转搜索词如果想要查询%com%只需要加一个union查询即可SELECT*FROMurlsWHEREreversed_urlLIKEREVERSE(%com);-- 等价于 url LIKE %com4范围查询替代模糊匹配-- 原查询SELECT*FROMproductsWHEREcategoryLIKE电子%ORcategoryLIKE家电%;-- 优化为范围查询 ~是 ASCII 码中排在最后的可打印字符确保范围覆盖所有以指定前缀开头的字符串。SELECT*FROMproductsWHEREcategoryBETWEEN电子AND电子~ORcategoryBETWEEN家电AND家电~;6、判断数据是否存在-- 不推荐性能查SELECTcount(*)FROMtableWHEREa1ANDb2-- Java写法:intnumsxxDao.countXxxxByXxx(params);if(nums0){//当存在时执行这里的代码}else{//当不存在时执行这里的代码}-- 推荐SELECT1FROMtableWHEREa1ANDb2LIMIT1-- javaIntegerexistxxDao.existXxxxByXxx(params);if(exist!NULL){//当存在时执行这里的代码}else{//当不存在时执行这里的代码}7、范围查询优化1大小符号改为between and-- 走索引select*fromorderowhereo.addTimebetween1751288870and1778601600-- 性能差select*fromorderowhereo.addTime1751288870ando.addTime17786016002联合查询增加范围-- 性能差超过50条数据的话是秒查少于50条数据直接超时-- 先走createTime 索引然后再根据id过滤然后再根据id排序数据量大之后性能非常差-- 考虑了强制走主键索引、缩小id和createTime的范围发现都很慢select*fromorders owhereo.createTime1751288870ando.id121824694orderbyo.iddesclimit50-- 优化更换排序方式会快一些 400ms但是超过50条数据性能同样400ms比上面性能差一些上面超过50条10msselect*fromorders owhereo.createTime1751288870ando.id121824694orderbyo.createTimedesclimit50-- 创建联合索引没试过不知道怎么样CREATEINDEXidx_cre_idONOrders(createTime,id);
MySQL：SQL优化实际案例解析（持续更新）
文章目录写在前面sql语句片段的执行顺序一、MySQLSQL优化1、时间格式化问题字符串2、in/inner join的问题3、MySQL深分页优化4、批量删除千万级表数据5、like优化问题6、判断数据是否存在7、范围查询优化1大小符号改为between and2联合查询增加范围写在前面sql语句片段的执行顺序FROMleft_tableONjoin_conditionjoin_typeJOINright_tableWHEREwhere_conditionGROUPBYgroup_by_listHAVINGhaving_conditionSELECTDISTINCTselect_listORDERBYorder_by_conditionLIMITlimit_number一、MySQLSQL优化1、时间格式化问题字符串-- 优化前SELECT*FROMtest_tableWHEREdate_format(begin_time,%Y-%m-%d)2025-03-12-- 优化-- 1、加上begin_time字段为普通索引并且优化了sqlSELECT*FROMtest_tableWHEREbegin_timebetweenconcat(2025-03-12, 00:00:00)andconcat(2025-03-12, 23:59:59)原理解释在where条件中对字段进行函数操作即使加了索引也会导致索引失效最终是走全表扫描如果使用between全表扫描会变成范围搜索索引就会正常使用了如果是mysql8版本可以考虑使用函数索引2、in/inner join的问题-- 优化前SELECT*FROMtest_tableWHEREidIN(SELECTmax(id)FROMtest_tableGROUPBYDATE_FORMAT(begin_time,%H时))-- 优化后SELECT*FROMtest_table t1innerjoin(SELECTmax(id)idFROMtest_tableGROUPBYDATE_FORMAT(begin_time,%H时))t2ont1.idt2.id原理解释mysql5版本对于in并不是很友好in参数过多就会走全表扫描。而8版本对in做了优化。如果用的是mysql5版本in的过程比较慢或许尝试用join操作来代替in可能会有奇效同时看情况用exists等能够替代in的方式。3、MySQL深分页优化-- 单表查询涉及回表问题深分页性能会略微下降select*fromt5orderbytextlimit1000000,10;-- 1、优化可以考虑覆盖索引selectid,textfromt5orderbytextlimit1000000,10;-- 2、优化利用索引覆盖 书签记录通过记录上次查询的主键位置直接从该位置继续查询。-- 后续查询假设上次最后一条记录的created_atXidYSELECTid,nameFROMusersWHEREcreated_atXOR(created_atXANDidY)ORDERBYcreated_at,idLIMIT100;-- 1、优化关联查询 可以通过把分页的SQL语句改写成子查询的方法获得性能上的提升-- 这种写法要求主键ID必须是连续的、Where子句不允许再添加其他条件select*fromt5whereid(selectidfromt5orderbytextlimit1000000,1)limit10;-- 或者记录上次查询的位置同样需要条件递增es也可以参考相当于一个查询分为多次select*fromt5whereid1000000limit10;-- 改成between and同样需要条件递增selectid,name,balanceFROMaccountwhereidbetween100000and100010orderbyid;-- 2、优化也可以考虑使用时间因为是天然递增的SELECT*FROMt_orderORDERBYcreate_timeASCLIMIT1000000,1SELECT*FROMt_orderWHEREcreate_time2025-01-01 00:00:00ORDERBYcreate_timedescLIMIT100;-- 3、优化延迟关联先查主表的id然后inner join子表性能提升以上的限制也解除了selecta.*fromt5 ainnerjoin(selectidfromt5orderbytextlimit1000000,10orderbytext)bona.idb.id;4、批量删除千万级表数据-- 删除一个月以前的数据DELETEFROMuser_logWHEREcreate_time2025-05-09;如果是小表以上行为是没问题的但是千万级大表那么delete操作就会进行全表扫描进行大范围的加锁甚至效果相当于锁表而锁表给业务带来的影响就是业务都无法进行写操作了。-- 优化1、分批不断循环查询数据每10条删一次SELECTidFROMuser_logWHEREcreate_time2025-05-09limit10;-- 2、根据ID删除deletefromuser_logwhereid123;5、like优化问题1前缀匹配索引前几个字符索引只存储字段的前 N 个字符减少索引体积加速匹配。前缀长度需根据业务字段长度分布调整通过EXPLAIN验证索引是否被使用。-- 为username字段创建前缀索引取前20个字符ALTERTABLEusersADDINDEXidx_username(username(20));-- 查询优化前缀匹配可利用索引SELECT*FROMusersWHEREusernameLIKEalice%;2全文索引Full-Text Index-- 创建全文索引ALTERTABLEarticlesADDFULLTEXTINDEXidx_content(content);-- 使用MATCH AGAINST替代LIKESELECT*FROMarticlesWHEREMATCH(content)AGAINST(keywordINNATURALLANGUAGEMODE);3反转字符串 前缀索引针对后缀匹配-- 添加反转字符串列ALTERTABLEurlsADDCOLUMNreversed_urlVARCHAR(255)AS(REVERSE(url))STORED;-- 为反转列创建前缀索引ALTERTABLEurlsADDINDEXidx_reversed_url(reversed_url(20));-- 查询时反转搜索词如果想要查询%com%只需要加一个union查询即可SELECT*FROMurlsWHEREreversed_urlLIKEREVERSE(%com);-- 等价于 url LIKE %com4范围查询替代模糊匹配-- 原查询SELECT*FROMproductsWHEREcategoryLIKE电子%ORcategoryLIKE家电%;-- 优化为范围查询 ~是 ASCII 码中排在最后的可打印字符确保范围覆盖所有以指定前缀开头的字符串。SELECT*FROMproductsWHEREcategoryBETWEEN电子AND电子~ORcategoryBETWEEN家电AND家电~;6、判断数据是否存在-- 不推荐性能查SELECTcount(*)FROMtableWHEREa1ANDb2-- Java写法:intnumsxxDao.countXxxxByXxx(params);if(nums0){//当存在时执行这里的代码}else{//当不存在时执行这里的代码}-- 推荐SELECT1FROMtableWHEREa1ANDb2LIMIT1-- javaIntegerexistxxDao.existXxxxByXxx(params);if(exist!NULL){//当存在时执行这里的代码}else{//当不存在时执行这里的代码}7、范围查询优化1大小符号改为between and-- 走索引select*fromorderowhereo.addTimebetween1751288870and1778601600-- 性能差select*fromorderowhereo.addTime1751288870ando.addTime17786016002联合查询增加范围-- 性能差超过50条数据的话是秒查少于50条数据直接超时-- 先走createTime 索引然后再根据id过滤然后再根据id排序数据量大之后性能非常差-- 考虑了强制走主键索引、缩小id和createTime的范围发现都很慢select*fromorders owhereo.createTime1751288870ando.id121824694orderbyo.iddesclimit50-- 优化更换排序方式会快一些 400ms但是超过50条数据性能同样400ms比上面性能差一些上面超过50条10msselect*fromorders owhereo.createTime1751288870ando.id121824694orderbyo.createTimedesclimit50-- 创建联合索引没试过不知道怎么样CREATEINDEXidx_cre_idONOrders(createTime,id);
