sql时间排序_这些经常被忽视的SQL错误⽤法，你有没有踩过
坑？
之前已经讲过mysql的性能优化，也介绍了⼀些⾯试过程中经常被问到的⼀些问题。想了解的请看我之前的⽂章：《能避开很多坑的mysql ⾯试题，你知道吗》。其实不只是在数据库设计的过程中容易犯⼀些低级的错误，⽇常开发过程中会经常忽略⼀些Sql的写法，从⽽导致系统性能低等⼀系列问题。今天就来总结哪些经常被我们忽视的SQL错误写法，看看你都踩过哪些坑？
⼀、LIMIT语句
Limit是分页查询是最常⽤的场景之⼀，但也通常也是最容易出问题的地⽅。⽐如对于下⾯简单的语句，⼀般我们觉得在type, name,
create_time字段上加组合索引。这样条件排序都能有效的利⽤到索引，性能迅速提升。
SELECT * FROM  operation WHERE  type = 'xxx'        AND name = 'xxx' ORDER  BY create_time LIMIT  1000, 10;
但是当数据量很⼤的时候，当我们查询最后⼏页数据时，分页会越来越慢。这就是我们经常碰到的海量数据的分页问题。这是为什么呢？计算机程序设计语言发展视频
优化⽅案
因为数据库也并不知道第1000000条记录从什么地⽅开始，即使有索引也需要从头计算⼀次，即进⾏全表扫描。出现这种性能问题，主要还是我们没有考虑到⼤量数据的情况。
其实在前端数据浏览翻页时，是可以将上⼀页的最⼤值作为查询条件传给后台的。SQL 重新设计如下：
select *from operationwhere id>1000000AND type = 'xxx'AND name = 'xxx'ORDER BY create_timelimit 10
经过这种优化，可以保证系统不会随着数据量的增长⽽变慢。
⼆、隐式转换
SQL语句中查询变量和字段定义类型不匹配是另⼀个常见的错误。⽐如下⾯的语句：
explain extendedselect * from my_balance bwhere b.bpn = 14000000123and b.isverified is null;
字段 bpn 的定义为 varchar 类型，⽽查询条件传⼊的却是int 类型。MySQL 会将字符串转换为数字之后再⽐较。函数作⽤于表字段，导致所以索引失效。如下图所⽰：
这个坑我们以前也遇见过，花了好半天才发现是这个问题。 所以程序员在开发的过程中，⼀定要认真仔细，确保查询变量和字段类型匹配。
优化⽅案
保证传⼊的参数类型和字段定义的类型⼀致。
所以，上⾯的sql语句改为如下即可：
explain extendedselect * from my_balance bwhere b.bpn = '14000000123'and b.isverified is null;
三、关联更新、删除
MySQL会⾃动把SQL语句中的嵌套⼦查询优化为关联查询(join)，所以有些时候你会发现嵌套⼦查询的效率和关联查询的效率差不多。但是需要特别注意mysql⽬前仅仅针对查询语句的优化。对于更新或删除需要⼿⼯重写成 JOIN。
⽐如下⾯ UPDATE 语句，MySQL 实际执⾏的还是嵌套⼦查询(DEPENDENT SUBQUERY)，其执⾏时间可想⽽知。
explain extendedUPDATE operation oSET status = 'applying'WHERE o.id IN (SELECT id              FROM (SELECT o.id,o.status                    FROM opera 执⾏计划：
优化⽅案
将嵌套⼦查询改为 JOIN 之后，⼦查询的选择模式从嵌套⼦查询(DEPENDENT SUBQUERY) 变成了关联查询(DERIVED)，执⾏速度⼤⼤
mysql语句的执行顺序加快
桌面删除的文件如何回UPDATE operation o  JOIN (SELECT o.id,                o.status        FROM operation o          up = 123              AND o.status NOT IN ('done') 执⾏计划简化为：
四、Order by排序
MySQL中的两种排序⽅式：
1、通过有序索引顺序扫描直接返回有序数据，因为索引的结构是B+树，索引中的数据是按照⼀定顺序进⾏排列的，所以在排序查询中如果
能利⽤索引，就能避免额外的排序操作。EXPLAIN分析查询时，Extra显⽰为Using index。
2、Filesort排序，对返回的数据进⾏排序，所有不是通过索引直接返回排序结果的操作都是Filesort排序，也就是说进⾏了额外的排序操
作。EXPLAIN分析查询时，Extra显⽰为Using filesort。
优化⽅案
⼀般排序的原则就是：尽量减少额外的排序，通过索引直接返回有序数据。
所以我们需要注意以下这些情况：
1、排序字段在多个索引中，⽆法使⽤索引排序，查询⼀次只能使⽤⼀个索引：
explain select store_id,email,last_name from my_user order by store_id,email,last_name;
查询计划显⽰，没有⾛所以直接返回有序数据，额外排序放回结果：
2、排序字段顺序与索引列顺序不⼀致，同样也⽆法利⽤索引排序。这个就不举例了跟where条件是⼀样的。
需要注意的是：这些都是细节的东西，经常会在开发过程中忽略。然后SQL就莫名其妙的不⾛索引了。
五、混合排序
索引中的数据是按照⼀定顺序进⾏排列的，所以在排序查询中如果能利⽤索引直接返回数据，就能避免额外的排序操作。但是如果出现这种混合了升序和降序的情况，MySQL ⽆法利⽤索引直接返回排序结果的。
SELECT *FROM my_order o    INNER JOIN my_appraise a derid = o.id ORDER BY a.is_reply ASC,        a.appraise_time DESC LIMIT 0, 20
执⾏计划显⽰为全表扫描：
优化⽅案
此类问题根据实际常见优化，原则就是应该避免这种排序的情况。如果确实有多种排序的需求，最好使⽤其他的⽅法提升性能。
六、where 条件的顺序
有些⼈会容易忽视where 条件的顺序问题，如果where 条件的顺序不对，很有可能会导致索引失效，查询性能慢等问题。以下两点是需要
matlab for循环数组特别注意的：
1、排除数据越多的条件越靠前，where 条件从左往右执⾏的，在数据量⼩的时候不⽤考虑，但数据量⼤的时候必须要考虑条件的先后顺
序。
2、最左前缀原则，如果查询的时候查询条件精确匹配索引的左边连续⼀列或⼏列，则此列就可以被⽤到。如下：
select * from user where name=xx and city=xx ; ／／可以命中索引select * from user where name=xx ; // 可以命中索引select * from user where city=xx ; // ⽆法命中
这⾥需要注意的是，查询的时候如果两个条件都⽤上了，但是顺序不同，如 city= xx and name ＝xx，那么现在的查询引擎会⾃动优化为
匹配联合索引的顺序，这样是能够命中索引的。
由于最左前缀原则，在创建联合索引时，索引字段的顺序需要考虑字段值去重之后的个数，较多的放前⾯。ORDER BY⼦句也遵循此规
则。
七、EXISTS语句
MySQL 对待 EXISTS ⼦句时，会采⽤嵌套⼦查询的执⾏⽅式。如下⾯的 SQL 语句：
explain SELECT *FROM my_order n    LEFT JOIN my_appraise sra            ON n.id = derid            WHERE 1=1      AND EXISTS(SELECT 1
执⾏计划为：
优化⽅案
去掉 exists 更改为 join，能够避免嵌套⼦查询，这样会⼤⼤提⾼查询效率。
explain SELECT *FROM my_neighbor n    LEFT JOIN my_neighbor_apply sra            ON n.id = ighbored            AND sra.user_id = 'xxx'      INNER
新的执⾏计划显⽰没有了嵌套⼦查询：
⼋、条件下推
外部查询条件不能够下推到复杂的视图或⼦查询的情况有：
聚合⼦查询；
含有 LIMIT 的⼦查询；
UNION 或 UNION ALL ⼦查询；
输出字段中的⼦查询；
digitalinput是什么接口如下⾯的语句，从执⾏计划可以看出其条件作⽤于聚合⼦查询之后
explain SELECT *FROM(SELECT target,            Count(*)    FROM operation    GROUPBY target) tWHERE target = 'rm-xxxx'
优化⽅案
确定从语义上查询条件可以直接下推后，重写如下：
explain SELECT target,        Count(*)FROM operationWHERE target = 'rm-xxxx'GROUPBY target
执⾏计划变为：
九、提前缩⼩数据范围
先上初始 SQL 语句：
SELECT *FROM my_order o    LEFT JOIN my_userinfo u            ON o.uid = u.uid    LEFT JOIN my_productinfo p            ON o.pid = p.pid WHERE o.disp 通过查询计划可以看出90万，时间消耗为12秒。
php网络课程
优化⽅案
由于最后 WHERE 条件以及排序均针对最左主表，因此可以先对 my_order 排序提前缩⼩数据量再做左连接。SQL 重写后如下，执⾏时间缩⼩为1毫秒左右。
SELECT *FROM (SELECT *        FROM my_order o        WHERE o.display = 0            AND o.ostaus = 1          ORDER BY o.selltime DESC LIMIT 0, 15 )oL 再检查执⾏计划：
⼦查询物化后(select_type=DERIVED)参与 JOIN，虽然估算⾏扫描仍然为4万，但是利⽤了索引以及 LIMIT ⼦句后，实际执⾏时间变得很⼩。
⼗、中间结果集下推
看下⾯这个sql语句，：
SELECT a.*        c.allocatedFROM        FROM my_distribute d      LEFT JOIN        (            SELECT resourcesid, sum(allocation) allocated            FROM 那么该语句存在什么问题呢？不难看出⼦查询 c 是全表聚合查询，在表数量特别⼤的情况下会导致整个语句的性能下降。
其实对于⼦查询 c，左连接最后结果集只关⼼能和主表 resourceid 能匹配的数据。因此我们可以重写语句如下，执⾏时间⼤⼤降低 。SELECT a.*,        c.allocatedFROM (        SELECT resourceid        FROM my_distribute d        WHERE isdelete = 0        AND cusmanagercode = '1234567其实，这段sql我们还可以优化，可以将 my_distribute的查询结果作为临时表，这样能避免⼤量重复的代码。
最后
以上总结了⼀些sql语句常见的坑。⾥⾯很多都是不仔细导致的。只有仔细认真尽量考虑⼀些⼤数据的情况，这样才能写出⾼性能的SQL语句。
同时，程序员在设计数据模型以及编写SQL语句时，要把索引及性能提前考虑进去，这样才能避免后期遇到的⼀些坑。
推荐阅读：
SpringBoot⼊门系列(四)如何整合Thymeleaf模板引擎
Spring Boot⼊门系列(六)Spring整合Mybatis详解「附详细步骤」能避开很多坑的mysql⾯试题，你知道吗

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。