江苏龙网

  1. 主页 > 生活百科 > >

SQL优化最干货总结 - MySQL( 五 )

mysql


7. 使用合理的分页方式以提高分页效率
使用合理的分页方式以提高分页效率 针对展现等分页需求，合适的分页方式能够提高分页的效率 。
案例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子句涉及的字段）有对应覆盖索引时，且中间结果集很大的情况时适用 。
五、建表优化1. 在表中建立索引，优先考虑where、order by使用到的字段 。
2. 尽量使用数字型字段（如性别，男：1 女：2），若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销 。
这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了 。
3. 查询数据量大的表 会造成查询缓慢 。主要的原因是扫描行数过多 。这个时候可以通过程序，分段分页进行查询，循环遍历，将结果合并处理进行展示 。要查询100000到100050的数据，如下：
SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID ASC) AS rowid,*FROM infoTab)t WHERE t.rowid > 100000 AND t.rowid <= 1000504. 用varchar/nvarchar 代替 char/nchar
尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些 。
不要以为 NULL 不需要空间，比如：char(100) 型，在字段建立时，空间就固定了，不管是否插入值（NULL也包含在内），都是占用 100个字符的空间的，如果是varchar这样的变长字段，null 不占用空间 。




推荐阅读


上一篇：正岩水仙茶盒多少钱,白茶多少钱盒

下一篇：一篇文章带你搞懂Python中的类