MySQL 优化建议

Hefery 的个人网站 Hefery's Personal Website 本文由博客端 http://hefery.icu 主动推送

MySQL 性能影响

数据库设计、SQL 查询速度

MySQL

存储引擎

参数配置

服务器硬件

网卡流量

网卡 IO:减少从服务器数量、分级缓存、避免使用“select *”、分离业务网络和服务器网络

大表

大表:记录行数巨大,单表超过千万行 或 表数据文件巨大,表数据文件超过 10G

影响数据库性能

处理大表

大事务

大事务:运行时间比较长,操作的数据比较多的事务

风险

处理大事务

MySQL 常用存储引擎

MyISAM

MySQL 5.5 之前默认存储引擎

MyISAM 存储引擎表由 MYD 和 MYI 组成(frm:记录表结构信息)

特性

限制

场景

Innodb

MySQL5.5 之前默认存储引擎

Innodb 使用表空间进行数据存储:show variables like 'Innodb_file_per_table'

特性

限制

场景

建议

选择存储引擎

存储引擎 事务 锁粒度 应用 忌用
MyISAM 不支持 并发插入的表级锁 select、insert 读写操作频繁
MRG_MyISAM 不支持 并发插入的表级锁 数据仓库 全局查找过多
Innodb 支持 MVCC 行级锁 事务处理
Archive 不支持 行级锁 日志记录 需要随机读取、更新、删除
Ndb cluster 支持 行级锁 高可用 大部分应用

MySQL 优化建议

原则

优化方面

优化数据库结构

数据库结构优化目的

数据库设计步骤

数据库范式

不要过分的反范式化为表建立太多的列

不要过分的范式化造成太多的表关联

将字段很多且部分字段不频繁使用,可以将这些字段分离出一张新表

分析经常联合查询的表的字段,使用这些字段建立中间表,将原来联合查询的表数据插入到中间表,使用中间表查询提高查询效率

优化插入记录的速度

优化表

如何修改大表的表结构

对表中的列的字段类型进行修改
改变字段的宽度时还是会锁表
无法解决主从数据库延迟的问题

pt-online-schema-change 
--alter="MODIFY c VARCHAR(50) NOT NULL DEFAULT ''"
--user=root --password=hefery D=数据库名,t=表名
--charset=utf8 
--execute

设计 MySQL 高可用架构

读写分离

负载均衡

优化数据库索引

索引优化策略

索引使用原则

索引优化查询

在 WHERE 和 GROUP BY 涉及的字段建立索引
WHERE 条件避免使用 NULL,可使用 0/-1
WHERE 条件避免使用 != 或 <>,转化为 < <= = > >= BETWEEN-AND
WHERE 条件避免使用 OR 连接查询条件
WHERE 条件避免使用 [NOT] IN,使用 EXISTS 代替
select id from stu where id in(select id from dtu)
select id from stu where exists(select 1 from dtu where id=stu.id)
WHERE 条件避免使用 LIKE:
select id from stu where name like '%abc' # 索引失效
select id from stu where name like 'abc%' # 索引生效
WHERE 条件避免使用表达式或函数操作
WHERE 条件避免使用

优化 SQL 查询

获取有性能问题 SQL

如何确定查询处理各个阶段所消耗的时间

set profiling = 1;
执行查询语句
show profiles;
show profile for query 1

优化 not in 和 <> 查询

SELECT customer_id, first name, last_name, email FROM customer 
WHERE customer_id NOT IN(SELECT customer_id FROM payment)

SELECT a.customer _id,a.first_name,a.last_name,a.email FROM customer a 
LEFT JOIN payment b 
ON a.customer_id=b.customer_id 
WHERE b.customer_id IS NULL

优化 MySQL 性能参数

# 连接MySQL服务器次数
SHOW STATUS LIKE '%CONNECTION%'
# 服务器上线时间
SHOW STATUS LIKE '%Uptime%'
# 慢查询次数
SHOW STATUS LIKE 'Slow_queries'
# 查询查询操作次数
SHOW STATUS LIKE 'Com_select'
# 查询修改操作次数
SHOW STATUS LIKE 'Com_update'
# 查询删除操作次数
SHOW STATUS LIKE 'Com_delete'

优化 MySQL 服务器

硬件优化

参数优化

  • 数据库

    据说 99% 的性能瓶颈都在数据库。

    320 引用 • 602 回帖 • 1 关注
  • MySQL

    MySQL 是一个关系型数据库管理系统,由瑞典 MySQL AB 公司开发,目前属于 Oracle 公司。MySQL 是最流行的关系型数据库管理系统之一。

    625 引用 • 528 回帖 • 442 关注
1 操作
Hefery 在 2022-01-03 12:21:54 更新了该帖

相关帖子

欢迎来到这里!

我们正在构建一个小众社区,大家在这里相互信任,以平等 • 自由 • 奔放的价值观进行分享交流。最终,希望大家能够找到与自己志同道合的伙伴,共同成长。

注册 关于
请输入回帖内容 ...