web开发之mysql优化总结

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

硬件配置

一般项目

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

大型项目

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

可以考虑使用官方的mysql-proxy来作为一个代理中间件，具体实现方法可以参考这篇文章¹
在这里插入图片描述

服务器参数

主要是安装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'

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

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

数据库设计

设计规范

范式设计

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

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

第三范式

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

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

最佳实践

选择合适的引擎

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

我这个项目选择的是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