数据库优化的方向
- 数据库设计:数据表设计遵循三范式,使用合适的数据类型,使用合适的存储引擎
- 适当创建索引
- 数据库扩展:数据库的分表分库,读写分离等
- SQL语句优化等
数据库设计
数据表结构
第一范式1NF:字段原子性
所有的字段都需要细分到不能再分的地步,如在一个地址字段中包含很多信息,如一个字段中包含省市两个信息,必须对他进行再次细分。
第二范式2NF: 每一列都要与主键相关,且不存在部分依赖
MySQL属于关系型数据库,如果某个字段和主键无关,那它将不应该出现在本表中,如果某个字段只和联合主键中的部分主键相关也是不合理的。
商品id | 商品名 | 用户名 |
---|---|---|
1 | 自行车 | 张三 |
比如如上情况是不合理的,用户名与主键无关。
商品id | 订单id | 订单价格 | 商品描述 |
---|---|---|---|
1 | 1 | 12 | 好东西 |
联合主键为商品id和订单id,商品描述只和商品id相关,而不与订单id相关,不合理。
第三范式3NF:任何非主属性不依赖于其它非主属性(在2NF基础上消除传递依赖)
如下图,用户信息都依赖于用户名,而用户名依赖于订单id。中间存在着传递依赖。
拆分后,使用用户id代替用户名,拆除传递依赖。
三大范式只是一般设计数据库的基本理念,可以建立冗余较小、结构合理的数据库。如果有特殊情况,当然要特殊对待,数据库设计最重要的是看需求跟性能,需求>性能>表结构。所以不能一味的去追求范式建立数据库!需求才是粑粑
数据字段优化
尽量使用可以正确存储数据的最小数据类型
更小的数据类型意味着更快,占用更少的磁盘,内存、缓存和处理时间
尽量使用整型表示字符串
因为字符集和校对规则,使处理字符比整型更复杂,比如:我们使用数据库内置的datetime类型存储时间而不是字符类型,我们使用整型存储ip而不是直接将ip字符串存到数据库中
尽可能使用not null
这个值是很烦人的,建字段时请尽量指定是否非空,NULL使得索引,统计,比较都变得更复杂,而且索引尽量不要创建到可以为null的字段上
恰当建立索引
- 在经常需要搜索的列上建立索引,可以加快搜索的速度
- 在作为主键的列上创建索引,强制该列的唯一性,并组织表中数据的排列结构,在InnoDB存储引擎,默认给主键添加索引
- 在经常使用表连接的列上创建索引,这些列主要是一些外键,可以加快表连接的速度
- 在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,所以其指定的范围是连续的
- 在经常需要排序的列上创建索引,因为索引已经排序,所以查询时可以利用索引的排序,加快排序查询
- 在经常出现在WHERE子句中的列上添加索引,加快条件的判断速度
- 大数据量下建立索引,小数据量不建立索引。
数据库拓展
在数据库的并发访问量过大时或者数据库存储的数据量极大时,又或者实现数据库的高可用时,可以对数据库本身进行优化。
- 当数据库访问量过大时,可以为数据库进行集群配置。
- 当数据库存储的数据量过大时,可以为数据库进行分库分表。
数据库集群配置
分库分表
SQL语句优化
-
查询尽量避免全表扫描,首先考虑在where、order by字段上添加索引
-
避免在where字段上使用NULL值,所以在设计表时尽量使用NOT NULL约束,有些数据会默认为NULL,可以设置默认值为0或者-1
-
避免在where子句中使用!=或<>操作符,Mysql只对<,<=,=,>,>=,BETWEEN,IN,以及某些时候的LIKE使用索引
-
避免在where中使用OR来连接条件,否则可能导致引擎放弃索引来执行全表扫描,可以使用UNION进行合并查询
select id from t where num = 30 union select id from t where num = 40;
-
尽量避免在where子句中进行函数或者表达式操作
-
最好不要使用select * from t,用具体的字段列表代替"*",不要返回用不到的任何字段
-
in 和 not in 也要慎用,否则会导致全表扫描,如select id from t where num IN(1,2,3)如果是连续的值建议使用between and,select id from t where between 1 and 3;
-
select id from t where col like %a%;模糊查询左侧有%会导致全表检索,如果需要全文检索可以使用全文搜索引擎比如es,slor
-
limit offset rows关于分页查询,尽量保证不要出现大的offset,比如limit 10000,10相当于对已查询出来的行数弃掉前10000行后再取10行,完全可以加一些条件过滤一下(完成筛选),而不应该使用limit跳过已查询到的数据。这是一个offset做无用功的问题。对应实际工程中,要避免出现大页码的情况,尽量引导用户做条件过滤
参考数据库优化