江苏龙网

  1. 首页 > 资讯 > 科技 > >

|搞懂这些SQL优化技巧，面试横着走( 六 )


案例 1：
select * from t where thread_id = 10000 and deleted = 0 order by gmt_create asc limit 0, 15;上述例子通过一次性根据过滤条件取出所有字段进行排序返回 。 数据访问开销=索引 IO+索引全部记录结果对应的表数据 IO 。
因此 ， 该种写法越翻到后面执行效率越差 ， 时间越长 ， 尤其表数据量很大的时候 。
适用场景：当中间结果集很小(10000 行以下)或者查询条件复杂(指涉及多个不同查询字段或者多表连接)时适用 。
案例 2：
select t.* from (select id from t where thread_id = 10000 and deleted = 0 order by gmt_create asc limit 0, 15) a, t where a.id = t.id;上述例子必须满足 t 表主键是 id 列 ， 且有覆盖索引 secondary key：(thread_id, deleted, gmt_create) 。
通过先根据过滤条件利用覆盖索引取出主键 id 进行排序 ， 再进行 join 操作取出其他字段 。
数据访问开销=索引 IO+索引分页后结果(例子中是 15 行)对应的表数据 IO 。 因此 ， 该写法每次翻页消耗的资源和时间都基本相同 ， 就像翻第一页一样 。
适用场景：当查询和排序字段(即 where 子句和 order by 子句涉及的字段)有对应覆盖索引时 ， 且中间结果集很大的情况时适用 。
建表优化
①在表中建立索引 ， 优先考虑 where、order by 使用到的字段 。
②尽量使用数字型字段(如性别 ， 男：1 女：2) ， 若只含数值信息的字段尽量不要设计为字符型 ， 这会降低查询和连接的性能 ， 并会增加存储开销 。
这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符 ， 而对于数字型而言只需要比较一次就够了 。
③查询数据量大的表 会造成查询缓慢 。 主要的原因是扫描行数过多 。 这个时候可以通过程序 ， 分段分页进行查询 ， 循环遍历 ， 将结果合并处理进行展示 。
要查询 100000 到 100050 的数据 ， 如下：
SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID ASC) AS rowid,* FROM infoTab)t WHERE t.rowid > 100000 AND t.rowid <= 100050④用 varchar/nvarchar 代替 char/nchar 。
尽可能的使用 varchar/nvarchar 代替 char/nchar， 因为首先变长字段存储空间小 ， 可以节省存储空间 ， 其次对于查询来说 ， 在一个相对较小的字段内搜索效率显然要高些 。
不要以为 NULL 不需要空间 ， 比如：char(100) 型 ， 在字段建立时 ， 空间就固定了 ，不管是否插入值(NULL 也包含在内) ， 都是占用 100 个字符的空间的 ， 如果是 varchar 这样的变长字段 ，null 不占用空间 。
作者：_陈哈哈
编辑：陶家龙
出处：https://sohu.gg/FGG98i


推荐阅读


上一篇：华为手机|几部华为手机最多降了500元，加上时尚的外观，真香！

下一篇：42号车库|为什么特斯拉实现不了 L5 自动驾驶？