web开发之mysql优化总结

导语:MySQL是存储网站数据的地方,如果不进行优化的话,会导致请求很慢,响应很慢,影响数据渲染,给产品带来不好的体验,我就自己之前的项目经验,说一下如何进行MySQL数据表方面的优化工作。

# 目录

  • 硬件配置
  • 服务器参数
  • 数据库设计
  • sql语句优化

下面就这四个方面进行阐述,分析总结我在接口项目中进行的优化总结经验方法。

# 硬件配置

  • 一般项目

如果你的项目比较小,预算比较节省,可以使用1C1G1M配置的服务器,安装mysql5.6即可;如果项目比较大,预算充足,可以配置2C4G5M等服务器,安装mysql8即可。我目前使用的是MySQL8版本。

  • 大型项目

如果数据量压力特别大,可以考虑购买多台服务器,进行读写分离,主从同步,主服务器用来写入,然后同步数据到辅服务器,辅服务器用来读取数据,这样压力会变得小一些,但是给开发工作带来的巨大的负担。

主从同步

可以考虑使用官方的mysql-proxy (opens new window)来作为一个代理中间件,具体实现方法可以参考这篇文章1

mysql代理

# 服务器参数

主要是安装mysql程序后,如果进行配置项优化,以便达到最大性能,发挥出MySQL的优势价值。

在配置文件my.ini或者my.cnf中写入以下参数:

#my.ini
max_connections=200
max_connect_errors=10
default-storage-engine=INNODB
#mysql_native_password
default_authentication_plugin=mysql_native_password
ngram_token_size=2
default-time_zone='+8:00'
1
2
3
4
5
6
7
8

包括最大连接数,最大连接错误,密码兼容性,时区设置东八区等。

我觉得这些就基本上可以了,如果你还想进一步优化,可以参考以下文章2

# 数据库设计

# 设计规范

  • 范式设计

当我们进行关系型数据库设计时,要遵循不同的规范,设计出合理的关系型数据库,那么这些不同的规范就会产生不同的范式,越高的范式冗余越小。

目前关系型数据库有六种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF,又称完美范式)。

  • 第三范式

一般来说,数据库只需要满足第三范式就行了,当然了,这个范式也是人定的,必然有优缺点,你可以根据你自己的实际情况来定义你所采用的规范。

第三范式主要是表中的字段和主键直接对应不依靠其他中间字段,说白了就是,决定某字段值的必须是主键;缺点在于查询时通常需要join很多表,导致查询效率很低。可以适当的进行反范式,进行适当的冗余,以便提高查询效率。

# 最佳实践

  • 选择合适的引擎

MySQL为我们提供的很多的引擎,比如myisaminnodbmemory等,你可以针对不同的存储需求选择最合适你的存储引擎。具体的可以参考这两篇文章:3

我这个项目选择的是innodb,看中它的自动增长列以及外键约束特性、提交、回滚和崩溃恢复能力等事务安全。

  • 建立适当的索引

建立一个索引即可,不加内存,不改程序,不调sql

  • 选择适当的字段类型

比如id可以采用int类型,类型等字段可以采用tinyint;文本内容根据大小优先级可以是char>varchar>text;日期根据存储字节可以是timestamp>datetime>date,文件资源只需要存储在服务器的路径即可。经过以上一系列的字段类型优化,可以有效降低存储大小,字节节省,减轻读写压力,提供数据库性能。

  • 适当时机进行写入

先写入和后写入都会对数据库产生影响。

  • 尽量批量操作读写

如果单个频繁的读写,会对服务器产生很大压力,可以采用批量脚本,批量录入数据。

  • 对数据表进行水平和垂直划分

一个表记录的东西太多,会影响读写效率,可以考虑化整为零,进行逻辑划分,一个表拆成多张表。

# sql语句优化

最后一个要优化的地方就是sql语句,一条好的sql语句可以帮助我们实现便捷的curd操作,提高读写效率。

  • 慢日志

如果某一天发现系统查询变慢了,又找不到原因。可以使用这个进行问题定位,但是由于慢查询日志记录信息较多,会影响mysql的性能,所以生产环境不建议长期开启。

在配置文件my.ini或者my.cnf中写入以下参数。

# slow log
slow_query_log=1 # 1开启 0关闭
slow_query_log_file=/usr/local/mysql/data/slow-query.log # 慢查询日志存储文件
long_query_time = 2 # 慢查询日志的时间定义(秒),默认为10秒,多久就算慢查询的日志
log_queries_not_using_indexes=1
1
2
3
4
5
  • 语句优化技巧

避免全表字段查询,可以查询某一个,根据条件。

这里有可以参考下。4

对了,我最近写了一个mysql语句生成npm包,有兴趣的可以去了解下,包地址xqsql (opens new window)

参考文章:

好了,以上就是我做项目过程中,发现的,总结的一些关于mysql优化方面的内容,如果有什么问题,可以底部发我邮箱联系。

分享至:

  • qq
  • qq空间
  • 微博
  • 豆瓣
  • 贴吧