mysql优化

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用版 2核4GB 50GB
简介: 错误浮现,排查,改bug,重写提交,测试,发布SQL性能下降原因:1. 查询语句写的烂2. 索引失效3. 关联查询太多join(设计缺陷或不得已的需求,七八张表关联一块)4.

错误浮现,排查,改bug,重写提交,测试,发布

SQL性能下降原因:
1. 查询语句写的烂
2. 索引失效
3. 关联查询太多join(设计缺陷或不得已的需求,七八张表关联一块)
4. 服务器调优及各个参数设置不合理(缓存,线程数等)
...

索引:(Index) 是帮助mysql高效获取数据的 数据结构

1. 索引目的在于提高查询效率,可以类比字典

2. 可以简单理解为"排好序的快速查询数据结构"

3. 索引会影响到where 后面的查询,以及order by 后面的排序

img_86d69a06272916a6fc089cc7a47249c0.png

1. 单值:
select * from user where name = ''
create index idx_user_name on user(name)

2. 复合:
select * from user where name = '' and email = ''
create index idx_user_name_email on user(name, email)
SQL执行顺序:

1. 手写顺序

SELECT DISTINCT
<select_list>
FROM
<left_table> <join_type> JOIN <right_table> ON <join_condition>
WHERE
<where_condition>
GROUP BY
<group_by_list>
HAVING
<having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_number>

2. 机读顺序

FROM
ON <join_condition>
<join_type> JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT
DISTINCT <select_list>
ORDER BY <order_by_condition>
LIMIT <limit_number>

img_c07e956024a85dc08f26d760e973dad8.png
image

SQL1七种join

img_eb574517456849f72be481f5ef19d15a.png

1、mysql服务器配置文件优化

1. MySQL允许最大的进程连接数,
2. 每个主机的连接请求异常中断的最大次数,
3. 设置表高速缓存的数目,
4. 指定单个查询能够使用的缓冲区大小,缺省为1M
.....
2、根据不同的业务和需求选择核心的存储引擎(mysql可插拔引擎机制MYISAM INNODB)
MyISAM:
1. 不支持主外键、不支持事务
2. 表锁:即使操作一条记录一会锁住整个表,不适合高并发
3. 只缓存索引,不缓存真实数据
4. 表空间小
5. 关注性能(高)
InnoDB:
1. 支持主外键、支持事务
2. 行锁:操作一条记录只会锁住一行,不影响其他行,适合高并发
3. 不仅缓存索引,还缓存真实数据,对内存要求高,内存性能直接影响数据库性能
4. 表空间占用大
5. 关注事务

img_0a35e340df4edbc8a07060b731c82337.png
img_88a1bbfd2f1dbb6e844a528bb24c5c13.png
image

Explain + SQL语句

多表:
索引创建:left 连接 在右表创建索引 right 连接在左表建立索引

3、sql语句优化:

多表:
索引创建:left 连接 在右表创建索引 right 连接在左表建立索引

条件查询时,如果创建的有复合索引,最后按照复合索引的顺序编写where条件

索引优化口诀
全值匹配我最爱,最左前缀要遵守;
带头大哥不能死,中间兄弟不能断;
索引列上少计算,范围之后全失效;
LIKE百分写最右,覆盖索引不写星;
不等空值不用or,索引失效要少用。

慢SQL分析步骤:

1. 观察,至少跑一天,看看生产的慢SQL情况
2. 开启慢查询日志,设置阔值,比如超过5s的就是慢SQL,并将其抓取出来
3. 使用explain+慢sql分析
4. show profile 查询SQL在Mysql服务器里面的执行细节和生命周期情况
5. 运维经理 DBA,进行数据库服务器的参数调优。

查询优化:
1. 永远小表驱动大表,即小的数据集驱动大的数据集

select * from A where id in (select id from B)
等价于
for select id from B
for select * from A where A.id = B.id

当B表的数据集小于A表的数据集时,用in优于exists

select * from A whre exists (select 1 from B where B.id = A.id)
等价于
for select * from A
for select * from B where B.id = A.id

2. 如果查询语句使用了not in 那么内外表都进行全表扫描,没有用到索引;而not extsts 的子查询依然能用到表上的 索引。所以无论那个表大,用not exists都比not in要快。

3. order by
img_b70d764108b867cc72223137da64a202.png
image
img_4973cd47a0d90ae88ba7dbe1d0a3280b.png
img_60991ec5390e1386e0bded1f0888bb91.png

group by 优化

img_eda0ce445b15f3cecbcd6deabc7cae27.png

慢查询日志:

img_2dd21013975ac6f45a81f06792b8c445.png
image
img_1f1ae5f1eeab5f8386e5b13fc6a35eab.png
img_44179cdc935de300c29b45a83372e33d.png
img_44179cdc935de300c29b45a83372e33d.png
img_6a57b475a9e29e097e8b834dfc178a96.png
img_2af86d27b31289dc72b28063cf92e21a.png
img_c9411bef44debfa06748aaa170a4263a.png
img_5520fd51af01b792b863d0e840cc2927.png
img_bd19b8dd43ae28cc325cfca4ff01c5e7.png
img_b2ab448265a53aad16b5ce1f8cbfe7e3.png
img_564131870cb244d346f7e360d29d0056.png
img_5f5ce6352b041441c72b14773faaddf3.png
相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
20天前
|
关系型数据库 MySQL 数据库
数据迁移脚本优化过程:从 MySQL 到 Django 模型表
在大规模的数据迁移过程中,性能问题往往是开发者面临的主要挑战之一。本文将分析一个数据迁移脚本的优化过程,展示如何从 MySQL 数据库迁移数据到 Django 模型表,并探讨优化前后的性能差异。
|
20天前
|
存储 关系型数据库 MySQL
mysql optimizer_switch : 查询优化器优化策略深入解析
mysql optimizer_switch : 查询优化器优化策略深入解析
|
20天前
|
关系型数据库 MySQL 数据库
MySQL索引优化:深入理解索引合并
MySQL索引优化:深入理解索引合并
|
21天前
|
关系型数据库 MySQL 分布式数据库
PolarDB产品使用问题之 MySQL数据库中,执行delete命令删除数据后,存储空间通常不会立即释放,该如何优化
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
|
22天前
|
存储 关系型数据库 MySQL
Mysql优化之索引相关介绍(笔记)
索引查找从顶层节点开始查找,通过key值,也就是主键的值进行比较,最终定位到存储数据的叶子节点上面,从叶子节点取出响应的数据。
40 0
Mysql优化之索引相关介绍(笔记)
|
4天前
|
关系型数据库 MySQL API
MySQL上亿数据查询优化:实践与技巧
MySQL亿级数据查询优化涉及索引设计、分区表、查询语句优化和数据库架构调整。例如,通过为常用查询列创建索引、使用EXPLAIN分析查询计划、避免全表扫描和SELECT *,以及采用垂直拆分、水平拆分和读写分离来提升性能。分区表能减少查询数据量,API接口测试可验证优化效果。
13 0
|
1月前
|
SQL 缓存 关系型数据库
MySQL慢查询优化实践问答
MySQL慢查询优化实践问答
|
1月前
|
缓存 关系型数据库 MySQL
如何优化MySQL 8.0的性能?
【6月更文挑战第14天】如何优化MySQL 8.0的性能?
106 5
|
1月前
|
关系型数据库 MySQL 数据库
mysql索引优化
【6月更文挑战第16天】mysql索引优化
20 2
|
13天前
|
SQL Oracle 关系型数据库
MySQL单表千万级数据查询优化大家怎么说(评论有亮点)
单表千万级数据是MySQL查询的一个坎,可能还不是天花板。“一个人走的慢,一群人走的快”,通过讨论可以发现MySQL千万数据的全貌大概是怎样的。
27 0