请选择 进入手机版 | 继续访问电脑版

[MySql] MYSQL 的10大经典优化案例场景实战

[复制链接]
查看193 | 回复31 | 2021-9-13 01:06:51 | 显示全部楼层 |阅读模式
目次

MYSQL 的10大经典优化案例场景实战

一、SQL优化一样寻常 步骤

通过慢查日志 等定位那些实验 服从 较低的SQL语句

1、explain 分析SQL的实验 计划

必要 重点关注

  1. type
复制代码
  1. rows
复制代码
  1. filtered
复制代码
  1. extra
复制代码

type由上至下,服从 越来越高

  • ALL 全表扫描
  • index 索引全扫描
  • range 索引范围扫描,常用语<,<=,>=,between,in等操作
  • ref 利用 非唯一索引扫描或唯一索引前缀扫描,返回单条记录,常出现在 关联查询中
  • eq_ref 类似 ref,区别在于利用 的是唯一索引,利用 主键的关联查询
  • const/system 单条记录,体系 会把匹配行中的其他列作为常数处理,如主键或唯一索引查询
  • null MySQL不访问任何表或索引,直接返回效果

固然 上至下,服从 越来越高,但是根据cost模子 ,假设有两个索引idx1(a, b, c),idx2(a, c),SQL为"select * from t where a = 1 and b in (1, 2) order by c";假如 走idx1,那么是type为range,假如 走idx2,那么type是ref;当必要 扫描的行数,利用 idx2大约是idx1的5倍以上时,会用idx1,否则会用idx2

Extra

  • Using filesort:MySQL必要 额外的一次传递,以找出怎样 按排序次序 检索行。通过根据联接范例 欣赏 全部 行并为全部 匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序,并按排序次序 检索行。
  • Using temporary:利用 了暂时 表保存中心 效果 ,性能特殊 差,必要 重点优化
  • Using index:表示相应的
    1. select
    复制代码
    操作中利用 了覆盖索引(
    1. Coveing Index
    复制代码
    ),避免访问了表的数据行,服从 不错!假如 同时出现 using where,意味着无法直接通过索引查找来查询到符合条件的数据。
  • Using index condition:
    1. MySQL5.6
    复制代码
    之后新增的
    1. ICP
    复制代码
    1. using index condtion
    复制代码
    就是利用 了
    1. ICP
    复制代码
    (索引下推),在存储引擎层举行 数据过滤,而不是在服务层过滤,利用 索引现有的数据减少回表的数据。

2、show profile 分析

相识 SQL实验 的线程的状态及斲丧 的时间。
默认是关闭的,开启语句“set profiling = 1;”

  1. SHOW PROFILES ;
  2. SHOW PROFILE FOR QUERY #{id};
复制代码

3、trace

trace分析优化器怎样 选择实验 计划,通过trace文件可以或许 进一步相识 为什么优惠券选择A实验 计划而不选择B实验 计划。

  1. set optimizer_trace="enabled=on";
  2. set optimizer_trace_max_mem_size=1000000;
  3. select * from information_schema.optimizer_trace;
复制代码

4、确定标题 并采用相应的措施

  • 优化索引
  • 优化SQL语句:修改SQL、IN 查询分段、时间查询分段、基于上一次数据过滤
  • 改用其他实现方式:ES、数仓等
  • 数据碎片处理

二、场景分析(案例)

1、 最左匹配

索引

  1. KEY `idx_shopid_orderno` (`shop_id`,`order_no`)
复制代码

SQL语句

  1. select * from _t where orderno=''
复制代码

查询匹配从左往右匹配,要利用 order_no走索引,必须查询条件携带shop_id或者索引(shop_id,order_no)调换前后次序

2、隐式转换

索引

  1. KEY `idx_mobile` (`mobile`)
复制代码

SQL语句

  1. select * from _user where mobile=12345678901
复制代码

隐式转换相称 于在索引上做运算,会让索引失效。mobile是字符范例 ,利用 了数字,应该利用 字符串匹配,否则MySQL会用到隐式更换 ,导致索引失效。

3、大分页

索引

  1. KEY `idx_a_b_c` (`a`, `b`, `c`)
复制代码

SQL语句

  1. select * from _t where a = 1 and b = 2 order by c desc limit 10000, 10;
复制代码

对于大分页的场景,可以优先让产品优化需求,假如 没有优化的,有如下两种优化方式,

一种是把上一次的末了 一条数据,也即上面的c传过来,然后做“

  1. c < xxx
复制代码
”处理,但是这种一样寻常 必要 改接口协议,并不肯定 可行。

另一种是采用耽误 关联的方式举行 处理,减少SQL回表,但是要记得索引必要 完全覆盖才有用 果,SQL改动如下

  1. select t1.* from _t t1, (select id from _t where a = 1 and b = 2 order by c desc limit 10000, 10) t2 where t1.id = t2.id;
复制代码

4、in + order by

索引

  1. KEY `idx_shopid_status_created` (`shop_id`, `order_status`, `created_at`)
复制代码

SQL语句

  1. select * from _order where shop_id = 1 and order_status in (1, 2, 3) order by created_at desc limit 10
复制代码

  1. in
复制代码
查询在MySQL底层是通过n*m的方式去搜索 ,类似
  1. union
复制代码
,但是服从 比
  1. union
复制代码
高。
  1. in
复制代码
查询在举行
  1. cost
复制代码
代价计算时(代价 = 元组数 *
  1. IO
复制代码
均匀 值),是通过将in包含的数值,一条条去查询获取元组数的,因此这个计算过程会比较的慢,以是 MySQL设置了个临界值(
  1. eq_range_index_dive_limit
复制代码
),5.6之后超过这个临界值后该列的cost就不参与计算了。因此会导致实验 计划选择不正确 。默认是200,即in条件超过了200个数据,会导致in的代价计算存在标题 ,大概 会导致Mysql选择的索引不正确 。

处理方式:可以(

  1. order_status, created_at
复制代码
)
  1. 互换前后顺序
复制代码
,并且调整SQL为耽误 关联。

5、范围查询阻断,后续字段不能走索引

索引

  1. KEY `idx_shopid_created_status` (`shop_id`, `created_at`, `order_status`)
复制代码

SQL语句

  1. select * from _order where shop_id = 1 and created_at > '2021-01-01 00:00:00' and order_status = 10
复制代码

范围查询还有“

  1. IN、between
复制代码

6、不等于、不包含不能用到索引的快速搜索

可以用到ICP

  1. select * from _order where shop_id=1 and order_status not in (1,2)
  2. select * from _order where shop_id=1 and order_status != 1
复制代码

在索引上,避免利用

  1. NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE
复制代码

7、优化器选择不利用 索引的环境

假如 要求访问的数据量很小,则优化器还是会选择辅助索引,但是当访问的数据占整个表中数据的蛮大一部分时(一样寻常 是

  1. 20%
复制代码
左右),优化器会选择通过聚集索引来查找数据。

  1. select * from _order where order_status = 1
复制代码

查询出全部 未付出 的订单,一样寻常 这种订单是很少的,即使建了索引,也没法利用 索引。

8、复杂查询

  1. select sum(amt) from _t where a = 1 and b in (1, 2, 3) and c > '2020-01-01';
  2. select * from _t where a = 1 and b in (1, 2, 3) and c > '2020-01-01' limit 10;
复制代码

假如 是统计某些数据,大概 改用数仓举行 办理 ;

假如 是业务上就有那么复杂的查询,大概 就不建议继续走SQL了,而是采用其他的方式举行 办理 ,比如利用 ES等举行 办理 。

9、asc和desc混用

  1. select * from _t where a=1 order by b desc, c asc
复制代码

desc 和asc混用时会导致索引失效

10、大数据

对于推送业务的数据存储,大概 数据量会很大,假如 在方案的选择上,终极 选择存储在MySQL上,并且做7天等有用 期的保存。

那么必要 注意 ,频仍 的清算 数据,会照成数据碎片,必要 接洽 DBA举行 数据碎片处理

到此这篇关于10大经典 MYSQL 优化案例场景实战的文章就先容 到这了,更多干系 MYSQL 优化案例场景实战内容请搜索 脚本之家从前 的文章或继续欣赏 下面的干系 文章渴望 大家以后多多支持脚本之家!


免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作!

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
回复

使用道具 举报

avatar 忧伤428 | 2021-9-13 03:53:10 | 显示全部楼层
好东西,赞一个!
回复

使用道具 举报

avatar 彩云彩2017 | 2021-9-17 22:13:02 | 显示全部楼层
很给力!
回复

使用道具 举报

avatar 虎哥726 | 2021-9-17 22:13:05 | 显示全部楼层
admin楼主,我告诉你一个你不知道的的秘密,有一个牛逼的网站,影视频道的网站所有电影和连续剧都可以免费看的。访问地址:http://tv.mxswl.com
回复

使用道具 举报

avatar 流动532 | 2021-9-20 02:57:26 | 显示全部楼层
论坛的人气不行了!
回复

使用道具 举报

avatar 宝贝不哭不t | 2021-9-26 23:22:05 | 显示全部楼层
有钱、有房、有车,人人都想!
回复

使用道具 举报

avatar 散粉的火把煌 | 2021-10-1 22:01:19 | 显示全部楼层
有机会找admin楼主好好聊聊!
回复

使用道具 举报

avatar 老三是叔叔 | 2021-10-2 17:04:51 | 显示全部楼层
admin楼主很有激情啊!
回复

使用道具 举报

avatar 荷叶224 | 2021-10-3 05:35:57 | 显示全部楼层
经典,收藏了!
回复

使用道具 举报

avatar 弄乐诟 | 2021-10-3 08:52:04 | 显示全部楼层
帖子很有深度!
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则