目录
架构优化
设计优化
使用优化
查询优化
MySQL优化主要分为架构优化、设计优化、使用优化、查询优化。
架构优化
解决系统规模大,并发量大、数据量大等问题。
-
解决表数据量大
-
分库分表
垂直分库:将相关的表,放入不同的数据库,如将电商数据库分为:订单数据库、库存数据库。
水平分库:将一个数据库的数据,分开存储到不同的服务器上。
垂直分表:将表的字段分开,放到不同的表中。
如:商品表(id,名称,价格,详细信息,图片。。。)分为商品和详情表
某些数据在分页中查看,某些大的数据textblob放详情表
水平分表:将一个表的数据分到多张表中
数据量特别大,会严重影响查询
-
-
解决并发量大
-
MySQL集群,主从复制
多台MYSQL服务器,分为主(master)和从(slave)服务器
-
读写分离
建立在MySQL主从复制的基础上,分为主数据库和从数据库
主数据库负责写(增删改)从数据库负责读(查询)
主数据库写入数据后,会记录到bin-log文件中,从数据库会将bin-log中的数据同步过来
-
-
使用数据库中间件 Sharding-jdbc、myCat等
设计优化
-
规范化设计
-
范式
优点: 规范数据库设计,消除冗余,方便数据的修改
缺点: 降低查询效率
反范式 在表中加入冗余字段
提高查询效率
表的设计按具体情况范式和反范式结合使用
-
选用合适的存储引擎
存储引擎是数据库中存储数据的方式,如存储的位置、存储约束、数据结构等的结合
不同的存储引擎有不同的功能和性能
常用存储引擎:
- InnoDB
- MyIsam
- Memory (不能持久化)
- Blackhole
- Performance Schema
- ....
不同点:
InnoDB MyIsam 事务 支持 不支持 查询性能 略低 高 锁 支持表锁和行锁,并发性能高 支持表锁 外键 支持 不支持 行数保存 不保存,需要用count(*) 保存 全文索引 不支持 支持 容错 支持数据恢复,通过bin-log日志 不支持
推荐使用InnoDB
-
字段优化
-
主键
必须给表设置主键id
尽量不用业务字段,使用没有意义字段如(int自增)
int自增效率高于UUID
-
数据类型
字符串尽量使用varchar,不使用char (varchar存储空间更小,数据少查询效率高)
尽量使用小的数据类型,如:性别 varchar(1) 男 女 --> int 1 0 --> small int --> tiny int (1字节 -128~127)
有限的值使用 enum, 而不是 varchar
-
字段尽量加not null约束
使用优化
规范化设计
-
范式
优点: 规范数据库设计,消除冗余,方便数据的修改
缺点: 降低查询效率
反范式 在表中加入冗余字段
提高查询效率
表的设计按具体情况范式和反范式结合使用
选用合适的存储引擎
存储引擎是数据库中存储数据的方式,如存储的位置、存储约束、数据结构等的结合
不同的存储引擎有不同的功能和性能
常用存储引擎:
- InnoDB - MyIsam - Memory (不能持久化) - Blackhole - Performance Schema - ....
不同点:
InnoDB | MyIsam | |
---|---|---|
事务 | 支持 | 不支持 |
查询性能 | 略低 | 高 |
锁 | 支持表锁和行锁,并发性能高 | 支持表锁 |
外键 | 支持 | 不支持 |
行数保存 | 不保存,需要用count(*) | 保存 |
全文索引 | 不支持 | 支持 |
容错 | 支持数据恢复,通过bin-log日志 | 不支持 |
推荐使用InnoDB
字段优化
-
主键
必须给表设置主键id
尽量不用业务字段,使用没有意义字段如(int自增)
int自增效率高于UUID
-
数据类型
字符串尽量使用varchar,不使用char (varchar存储空间更小,数据少查询效率高)
尽量使用小的数据类型,如:性别 varchar(1) 男 女 --> int 1 0 --> small int --> tiny int (1字节 -128~127)
有限的值使用 enum, 而不是 varchar
-
字段尽量加not null约束
1) 加索引
介绍索引的应用场景、分类
2) 加缓存
介绍Redis缓存、MyBatis缓存
3) 使用连接池
介绍DruidHikaric3p0dbcp
4) 分页查询
查询优化
1. 查询之前,使用explain查看执行计划
2. 尽量避免select *
3. 尽量避免where中使用<>和!=
4. 尽量避免where中列避免使用函数和表达式
5. 尽量避免模糊查询时,通配符在前面
6. 尽量使用exists代替in
7. 尽量避免where中使用or,union 代替