欢迎观临
路漫漫其修远兮,吾将上下而求索

数据库优化整理总结

数据库优化有很多种策略,基本方向有结构设计优化,索引优化,语句优化,分库分表,读写分离,缓存利用以及系统配置优化和硬件优化。其中最关键的就是设计初期建立合理的数据结构。这对于后期性能优化至关重要。因为数据库结构是系统的基石,基础打不好,使用各种优化策略,也不能达到太好的效果。


一. 数据库设计

  1. 数据库设计有一个经典的三范式规则,目的是为了建立冗余较小、结构合理的数据库。
    • 第一范式:确保每列数据保持原子性
    • 第二范式:确保表中的每列都和主键相关
    • 第三范式:确保每列都和主键列直接相关,而不是间接相关
      >>> 数据库三范式详解
      三范式作为一个基本依据,但不要生搬硬套,有时候合理的冗余设计是必要的。
  2. 合理的使用字段类型和约束。SQL数据库有五大约束: PRIMARY KEY, FOREIGN KEY, UNIQUE, DEFAULT, NOT NULL

二. 索引优化

索引是用来对数据进行定位的方法,索引的类型通常有:主键索引,唯一索引,聚集索引,非聚集索引。
合理的使用索引会让查询更高效,但是索引的使用是有一定代价的。
好处:

  1. 可以大大加快数据的查询速度,可以加速表和表之间的连接操作。
  2. 在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。
  3. 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。

代价:

  1. 索引需要占用硬盘存储空间。
  2. 当对表中的数据进行增删改的时候,索引也要动态的维护,会占用了一定时间。

>>> 索引详解

索引通常建立在使用频繁, 数据重复不多, 数据不太复杂的列。对于很少使用,或者重复量多(如枚举类型或者允许NULL),或者数据复杂(如text)的列,不应该建立索引。

注意有些使用情境下,虽然建立了索引,但是并没有起到作用。

  • 当在where子句中对字段进行null值判断时,将导致引擎放弃使用索引而进行全表扫描。
  • 当在where子句中对索引字段进行非等值判断时,如!=,<,>,NOT IN等,将导致引擎放弃使用索引而进行全表扫描。
  • 当在where子句中对索引字段进行运算操作时,包括函数,计算表达式等,将导致引擎放弃使用索引而进行全表扫描: 如where age + 10 = 30;where substr(name) = ‘Kevin’;
  • 当对索引字段进行like模糊查询时,如果待匹配字符串第一个字符为模糊匹配符%,则不会用到索引:如where name like ‘%kevin’;
  • 当在 where 子句中使用or来连接条件时,如果其中一个字段没有索引,将导致引擎放弃使用索引而进行全表扫描。

三. 语句优化

语句查询涉及到很多琐碎的方面,这里大概列举一些:

  1. 对于频繁where查询,尽量利用上索引;对于没有建立索引或者索引无效的,建立合适的索引或者修改语句以便利用索引。
  2. 合理调整where条件的顺序,将能过滤掉更多数据的条件放在末尾。
  3. 避免采用 select *, 这会占用更多时间。
  4. 理论上,尽量使用多表连接(join)查询,避免子查询。
  5. 对于LIMIT查询,先在索引上完成排序分页操作,最后根据主键关联回表查询所需要的其他列内容。
  6. 充分利用查询优化器,多处存在相同的查询语句时,尽量保持一样(大小写也一致)。
  7. 利用explain语句来分析语句执行情况,针对性优化。

四. 分库分表

MySQL单表的数据量在500w-1000w之间性能比较好,单库数据量在5000w以内性能比较好,,超过阈值性能都会下降。同时单库单表在IO读写和数据库连接上都存在着限制。为了提升性能和解决以上问题, 在数据量和并发访问量大时,分库分表时必要的。

分库分表有水平拆分和垂直拆分。
>>> 分库分表详解

五. 读写分离

一台数据库支持最大连接数是有限的,如果用户的并发访问很多,一台服务器无法满足需求。这时候可以采用读写分离的方式进行集群。读写分离采用主从配置,一台master,多台slave, 所有写操作都往master上写,读操作往从Slave上读。Slave数据库从Master同步数据。
mysql支持读写分离配置。
>>> mysql读写分离介绍和搭建

六. 缓存利用

缓存可以在一定程度上避免磁盘IO读写,节省查询时间。常见的缓存服务有Redis/Tair/Memcached等。
>>> redis作为mysql缓存

七. 系统配置优化

通过修改数据库的配置,来提升数据库性能,如mysql的my.cnf配置。
有时候优化过程中,需要找到影响性能的sql语句,可以通过配置my.cnf打开mysql的慢查询日志。
>>> 慢查询详解

赞(0) 打赏
未经允许不得转载:云海鹰影博客 » 数据库优化整理总结
分享到: 更多 (0)

欢迎留言 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏