MySQL：SQL优化实际案例解析（持续更新）

写在前面：sql语句片段的执行顺序

FROM<left_table>ON<join_condition><join_type>JOIN<right_table>WHERE<where_condition>GROUPBY<group_by_list>HAVING<having_condition>SELECTDISTINCT<select_list>ORDERBY<order_by_condition>LIMIT<limit_number>

一、MySQL：SQL优化

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.id=t2.id

原理解释：
mysql5版本，对于in并不是很友好，in参数过多就会走全表扫描。而8版本对in做了优化。
如果用的是mysql5版本，in的过程比较慢，或许尝试用join操作来代替in可能会有奇效！
同时，看情况用exists等能够替代in的方式。

3、MySQL：深分页优化

-- 单表查询，涉及回表问题，深分页性能会略微下降select*fromt5orderbytextlimit1000000,10;-- 1、优化：可以考虑覆盖索引selectid,`text`fromt5orderbytextlimit1000000,10;-- 2、优化：利用索引覆盖 + 书签记录：通过记录上次查询的主键位置，直接从该位置继续查询。-- 后续查询（假设上次最后一条记录的created_at=X，id=Y）SELECTid,nameFROMusersWHEREcreated_at>XOR(created_at=XANDid>Y)ORDERBYcreated_at,idLIMIT100;

-- 1、优化：关联查询 ，可以通过把分页的SQL语句改写成子查询的方法获得性能上的提升-- 这种写法，要求主键ID必须是连续的、Where子句不允许再添加其他条件select*fromt5whereid>=(selectidfromt5orderbytextlimit1000000,1)limit10;-- 或者记录上次查询的位置，同样需要条件递增（es也可以参考，相当于一个查询分为多次）select*fromt5whereid>=1000000limit10;-- 改成between and，同样需要条件递增selectid,name,balanceFROMaccountwhereidbetween100000and100010orderbyid;-- 2、优化：也可以考虑使用时间，因为是天然递增的SELECT*FROMt_orderORDERBYcreate_timeASCLIMIT1000000,1SELECT*FROMt_orderWHEREcreate_time<='2025-01-01 00:00:00'ORDERBYcreate_timedescLIMIT100;-- 3、优化：延迟关联，先查主表的id，然后inner join子表，性能提升，以上的限制也解除了selecta.*fromt5 ainnerjoin(selectidfromt5orderbytextlimit1000000,10orderbytext)bona.id=b.id;

4、批量删除千万级表数据

-- 删除一个月以前的数据DELETEFROMuser_logWHERE`create_time`<'2025-05-09';

如果是小表以上行为是没问题的，但是千万级大表，那么delete操作就会进行全表扫描，进行大范围的加锁，甚至效果相当于锁表，而锁表给业务带来的影响就是业务都无法进行写操作了。

-- 优化：1、分批，不断循环查询数据，每10条删一次SELECTidFROMuser_logWHERE`create_time`<'2025-05-09'limit10;-- 2、根据ID删除deletefromuser_logwhereid='123';

5、like优化问题

（1）前缀匹配+索引前几个字符
索引只存储字段的前 N 个字符，减少索引体积，加速匹配。
前缀长度需根据业务字段长度分布调整，通过EXPLAIN验证索引是否被使用。

-- 为username字段创建前缀索引（取前20个字符）ALTERTABLEusersADDINDEXidx_username(username(20));-- 查询优化（前缀匹配可利用索引）SELECT*FROMusersWHEREusernameLIKE'alice%';

（2）全文索引（Full-Text Index）

-- 创建全文索引ALTERTABLEarticlesADDFULLTEXTINDEXidx_content(content);-- 使用MATCH AGAINST替代LIKESELECT*FROMarticlesWHEREMATCH(content)AGAINST('keyword'INNATURALLANGUAGEMODE);

（3）反转字符串 + 前缀索引（针对后缀匹配）

-- 添加反转字符串列ALTERTABLEurlsADDCOLUMNreversed_urlVARCHAR(255)AS(REVERSE(url))STORED;-- 为反转列创建前缀索引ALTERTABLEurlsADDINDEXidx_reversed_url(reversed_url(20));-- 查询时反转搜索词，如果想要查询%com%，只需要加一个union查询即可SELECT*FROMurlsWHEREreversed_urlLIKEREVERSE('%com');-- 等价于 url LIKE '%com'

（4）范围查询替代模糊匹配

-- 原查询SELECT*FROMproductsWHEREcategoryLIKE'电子%'ORcategoryLIKE'家电%';-- 优化为范围查询 ~是 ASCII 码中排在最后的可打印字符，确保范围覆盖所有以指定前缀开头的字符串。SELECT*FROMproductsWHEREcategoryBETWEEN'电子'AND'电子~'ORcategoryBETWEEN'家电'AND'家电~';

6、判断数据是否存在

-- 不推荐，性能查SELECTcount(*)FROMtableWHEREa=1ANDb=2-- Java写法:intnums=xxDao.countXxxxByXxx(params);if(nums>0){//当存在时，执行这里的代码}else{//当不存在时，执行这里的代码}

-- 推荐SELECT1FROMtableWHEREa=1ANDb=2LIMIT1-- javaIntegerexist=xxDao.existXxxxByXxx(params);if(exist!=NULL){//当存在时，执行这里的代码}else{//当不存在时，执行这里的代码}

7、范围查询优化

（1）大小符号改为between and

-- 走索引select*fromorderowhereo.addTimebetween1751288870and1778601600-- 性能差select*fromorderowhereo.addTime>1751288870ando.addTime1778601600

（2）联合查询，增加范围

-- 性能差，超过50条数据的话是秒查，少于50条数据直接超时-- 先走createTime 索引，然后再根据id过滤，然后再根据id排序，数据量大之后，性能非常差-- 考虑了强制走主键索引、缩小id和createTime的范围，发现都很慢select*fromorders owhereo.createTime>1751288870ando.id<121824694orderbyo.iddesclimit50-- 优化：更换排序方式，会快一些 400ms，但是超过50条数据性能同样400ms，比上面性能差一些（上面超过50条10ms）select*fromorders owhereo.createTime>1751288870ando.id<121824694orderbyo.createTimedesclimit50-- 创建联合索引（没试过）不知道怎么样CREATEINDEXidx_cre_idONOrders(createTime,id);