慢查询优化1-7

目录

慢查询优化

1.查询语句的注意事项

1.尽量使用简单的查询，避免使用表链接,请尽量避免全表扫描，包括但不限于: where子句条件横真或为空
2.使用LIKE
3.使用不等操作符(<>、!=)
4.查询含义is null的列
5.在非索引列上使用or
6.多条件查询时，请把简单查询条件或索引列查询置于前面,
7.尽量指定需要查询的列，不要偷懒使用select * 如果不指定，一方面会返回多余的数据，占用宽带等 另一方面MySQL执行查询的时候，没有字段时会先去查询表结构有哪些字段大写的查询关键字比小写快一点点 使用子查询会创建临时表，会比链接(JOIN)和联合(UNION)稍慢 
8.在索引字段上查询尽量不要使用数据库函数，不便于缓存查询结果 
9.当只要一行数据时，请使用LIMIT 1，如果数据过多，请适当设定LIMIT，分页查询 千万不要 ORDER BY RAND()，性能极低

2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描

select id from t where num is null 
	可以在num上设置默认值0，确保表中num列没有null值
	然后这样查询: select id from t where num=0

3.创建索引注意事项

# 创建时注意
	1.一般来说，每张表都需要有一个主键id字段
	2.常用于查询的字段应该设置索引
	3.varchar类型的字段，在建立索引的时候，最好指定长度 
  4.查询有多个条件时，优先使用具有索引的条件
  5.像LIKE条件这样的模糊搜索对于字段索引是无效的，需要另外建立关键词索引来解决 
  6.请尽量不要在数据库层面约束表和表之间的关系，这些表之间的依赖应该在代码层面去解决

4.使用聚集索引和非聚集索引

5.常见慢查询优化方案

mysql中最常见的慢查询优化方案是什么？
1.加索引，优化索引。
2.在where和order by 列上加索引
3.减少where 字段值null判断
	SELECT * FROM m_user where type = null
	改为：
	SELECT * FROM m_user where type = 0
4.应尽量避免在 where 子句中使用!=或<>操作符
5.应尽量避免在 where 子句中使用 or 来连接条件
	SELECT * FROM "tb_real_time_car" where pay_status != null or enter_time = null;
	改为：union连表操作
	SELECT * FROM "tb_real_time_car" where pay_status != null union all SELECT * FROM "tb_real_time_car" where enter_time = null;
6.in 和 not in 也要慎用
	SELECT * FROM "tb_real_time_car" where rowed in [1,2,3,4];
	改为：
	SELECT * FROM "tb_real_time_car" where rowed between 1 and 5;
7.少使用模糊匹配 like
8.应尽量避免在 where 子句中对字段进行表达式操作
	SELECT * FROM "tb_real_time_car" where rowid/4 =100;
	改为：
	SELECT * FROM "tb_real_time_car" where rowid =4*100;
9.不要在条件判断时进行算数运算
10.很多时候用 exists代替 in 是一个好的选择
	SELECT * FROM "tb_real_time_car" where rowed in (select rowed from "tb_real");
	改为：
	替换为SELECT * FROM "tb_real_time_car" where exists (select rowed from "tb_real" where rowed = tb_real.rowid)
11.尽量使用前戳索引
	字符串类型，截取前面字符串长度为索引
12.使用索引扫描进行排序
13.union all,in,or都能够使用索引，但是推荐使用in
14.强制类型转换会全表扫描
15.尽量不允许为空，默认值0，或者其他
16.能使用limitd的时候尽量使用limit
17.单表索引建议控制在5个以内
18.单索引字段数不允许超过5个（组合索引）
19.不要过早优化，在不了解系统的情况下进行优化

6.你们是怎么做sql优化的？

慢sql处理：
开发阶段
1.首先在开发阶段创建表的时候，就把应该用到的索引创建好了
日常维护阶段：
2.观察此慢sql是否为临时业务，如果是则忽略
3.explan sql检查执行，观察扫描多少行，结果多少行，有无用到索引
4.有可能explan时用到了索引，实际中没有用到，原因是mysql的优化器会自动帮你选择一个认为"最优"的索引，实际不优，此时可以使用FORCE INDEX 指定索引
如：SELECT * FROM table_name FORCE INDEX (index_name)WHERE condition;
5.是否可以加缓存，比如：排行榜，实效性要求不高，加缓存5分钟
6.还是不满足的话就需要：是否考虑分库分表，读写分离了

7.MySQL中通过创建时间排序的翻页怎么做？越往后越慢的

user(id,name,age,created,updated)
created,updated都有索引

explain查看执行计划
1.常规分页：
select * from user where created > '2023-05-04' limit 0,10; //很快
select * from user where created > '2023-05-04' limit 1000000,10; 
//分析：很耗时，因为created不是聚簇索引，需要回表

2.使用子查询：
select * from user where id in (select id from (select id from user where created>'2023-05-04' limit 100000,10 ) as t); 
//分析：需要查询嵌套，否则报错。性能提升3倍。原因：子查询使用了覆盖索引

3.使用inner join关联查询
select * from user inner join (select id from user where created>'2023-05-04' limit 100000,10) as t on user.id=t.id;
//分析：与子查询性能一样

4.使用游标分页(推荐)
实现方式就是：当我们查询第二页的时候，把第一页的查询结果放到第二页的查询条件中。
例如：首先查询第一页
select * from user where created>'2022-07-03' limit 10;
然后查询第二页，把第一页的查询结果放到第二页查询条件中：
select * from user where created>'2022-07-03' and id>10 limit 10;
这样相当于每次都是查询第一页，也就不存在深分页的问题了，推荐使用。

5.使用存储过程，接收两个参数：page_size 表示每页显示的行数，page_number 表示要查询的页码

6.开发过程中，给前端使用pageToken。翻页逻辑后端控制，翻页到后面就使用“>”等条件

7.一般业务不会提供翻页几百几千页的需求，一般20页
posted @ 2022-03-06 15:38  Jeff的技术栈  阅读(244)  评论(0)  编辑  收藏  举报
回顶部