1. 优化手段

基本上分成四个手段，数据库表结构优化，SQL语句优化，数据库参数配置的优化，硬件和系统级别的优化。其中最后一个是要花钱的，这里先不讨论。

2. 数据库表结构和设计的优化

这里首先要知道一下，表的设计的基本原则是表越窄越好，越小越好。
常见的数据库表一级的优化有如下一些方法：

2.1 读写分离，分库分表

我们经常会听到针对大的数据，要分库分表，读写分离
分库分表常用的框架有Sharding Sphere和MyCat，其中Sharding Sphere里面又分为Sharding JDBC，Sharding Proxy，Sharding SideCar三个子集，Sharding JDBC直接作用于Dao层，相当于对原JDBC操作的封装改造，Sharding Proxy相当于在数据库和Dao层中间加了一个代理层，和MyCat作用的位置相似，Sharding SideCar现在还没实现，先不用关注。下个主题会介绍一个基于Sharding JDBC做的分库分表的一个实验。

2.2 大表的水平拆分

这里的大表主要指的是表的行数，我们对于数据量的大小要有一个基本预期，一般而言单表的超过千万行记录就需要考虑分库分表的方案，建议单表数据量尽量不要超过5000万，单表的物理空间大小不要超过20G。
当然这里所谓大表的其实也依赖于表的宽度，当表的宽度足够小的时候，上亿行在一张表里面性能也可以接受，而当你的表很宽的时候，几十万行的表也可能慢的让你没法忍受。

2.3 表数据的冷热分离

我们在表设计的时候还要考虑表被访问修改的频率，也就是我们常说的“冷热表”，当然冷热分离的方式除了在表一级去做分离，其实也可以在库一级做分离，具体选择主要依赖我们的业务场景。

2.4 选取合适的数据类型

数据类型的选择原则是保留数据扩展能力情况下，优先选择小的数据结构。通常的情况是够用就好，没有必要预留更多的存储空间，这里也符合上面说的表越窄越好的道理。
这里通常的技巧有：
1）存储时间戳或者存储IPV4地址的时候可以使用无符号整形代替存储，来回之间的转换可以用INET_ATON和INET_NTOA或者其他办法，具体方法看实现语言，总之是他们的转换很舒适，没有啥代价也能带来一定程度查询效率的提升，比较划算。
关于表的宽窄可以使用show table status命令，通过里面的avg_row_length来查看，这个值说明了表的行平均长度，通常情况下这个值超过100就可以考虑一些办法去减小行平均长度。

image.png

除了avg_row_length，还有一些值需要关注，比如data_free，这个值标示了表的碎片，值越大碎片率越高。

2.5 宽表的垂直拆分

对于字段过多的表可以做拆分，具体多少字段需要看实际的执行效率，必要时可以增加中间表。字段过多还会有本身维护复杂度的问题，比如三四十个或者更多的字段的表通常也会认为过于复杂不便于维护。

2.6 适度使用反范式

这种方式慎重使用，反范式也就是某些场景可以不严格遵守三范式的要求，这样做可以提高查询效率，如果对查询效率提示不大就不用考虑了。

2.7 正确的使用索引

可以为经常作为查询条件的字段加索引，创建联合索引要考虑最左原则提供的索引复用能力，避免重复建索引，对唯一性可以保证的字段创建唯一索引等等。
而且索引不宜过多，建议索引的数量不要超过5个。

2.8 字段尽量设置为NOT NULL

因为MySQL需要为空字段做很多特殊的处理，MySQL的优化器也很难对空字段做优化，空字段需要的存储空间也更多。

2.9 字符集和库表的设计要一致

字符集不一致可能导致报错，还有一个潜在的问题是join类型转换无法走索引。

3. SQL语句优化

常见的优化有如下一些方法：
1）首先需要分析慢查询日志，找到需要优化的语句，或者是执行频率非常高的语句。
2）利用分析工具：explain、profile，其中explain用来分析执行计划的，主要看是否使用了索引，使用哪个索引，扫描了多少记录（这里主要看扫描的记录是否符合预期）。profile命令分析某个语句执行过程中分步的耗时。
3）避免使用SELECT *，只取需要的列，这样做一个是避免拿到多余的字段，对于大而无用的字段，会增加io操作，二是可以避免查询列字段的元信息，最后他会失去MySQL优化器“覆盖索引”策略优化的可能性。
4）使用preparedstatement，一个是它性能更好，一个是可以防止SQL注入。
5）尽量在有索引的字段上进行排序操作，这样排序的时候会用上索引扫描。
6）对于写操作要尽快的完成，事务要尽快的提交或者回滚，因为这些操作都会加锁（不管是表锁还是行锁或者其他细粒度的锁），如果表锁不释放，其他的SQL或者事务就会被阻塞。这里建议监控MySQL的线程状态以及InnoDB的事务状态，对于超过5秒的线程执行和事务操作要监控起来。

4. 数据库配置文件的优化

数据库配置文件的优化一般不需要普通开发人员掌握，经常用到的就是根据具体业务场景调整各种缓冲区的大小，属于DBA的工作范畴。配置文件如果是在windows环境的话一般在MySQL安装目录下就有my.ini文件，linux环境则是my.conf文件，可以通过find -name命令查询文件路径。
这里指出一下配置文件的路径在5.7已经修改了，我的MySQL环境8.0.13的版本没有在安装目录下找到my.ini文件了，默认的安装路径改成C:\ProgramData\MySQL\MySQL Server 8.0这个地方了，在这个目录下一样可以看到，5.7之前的版本的安装路径现在看都是没有变化，和MySQL的安装是在同一个目录下面的。
这里我们简单了解一下配置文件里面的内容：

port=3306   //默认端口
default-character-set=utf8   //默认字符集
basedir="D:/MySql/"     //安装地址
datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"    //数据库的根地址
character-set-server=utf8   //服务端默认字符集
default-storage-engine=INNODB   //默认存储引擎
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" //数据库模式
max_connections=100   //服务端允许的最大连接数，这个也是经常会根据业务场景修改提升性能的，不过也不能无限制改大还是要根据机器性能来
query_cache_size=0   //查询缓存大小，建议是读非常多，很少写的时候可以开启提升性能，其他的情况不建议使用
table_cache=256    //每个线程能够处理的表数上限
tmp_table_size=18M   //临时表的大小限制
thread_cache_size=8  //线程池中的线程数

/*myisam引擎特有的配置*/
myisam_max_sort_file_size=100G  //重建索引时myisam引擎的临时文件允许的大小
myisam_sort_buffer_size=35M     //建索引时缓冲区大小
key_buffer_size=25M     //主键缓冲区的大小
read_buffer_size=64K     //全表扫描myisam表的时候的缓冲池大小
read_rnd_buffer_size=256K
sort_buffer_size=256K              //重建索引的时候分配的缓冲区大小

/*innodb特有的配置*/
innodb_additional_mem_pool_size=2M   //innodb用于存放元信息的内存池大小
innodb_flush_log_at_trx_commit=1    //设置1会让innodb在每次事务提交的时候刷事务日志
innodb_log_buffer_size=1M      //innodb缓存日志数据的大小
innodb_buffer_pool_size=47M   //innodb使用了缓冲池来缓存索引和行数据，这个值设的越大磁盘IO就越少，通常用作MySQL的服务器该值可以设置到物理内存的60%到80%
innodb_log_file_size=24M     //每个innodb日志文件的大小
innodb_thread_concurrency=10    //innodb引擎允许的线程数，依赖操作系统硬件资源等
/*binlog相关配置*/
binlog-format=ROW     //开启Binlog，并指定模式为ROW
log-bin=mysqlbinlog    //指定log-bin名字为mysqlbinlog

对于配置文件里面的内容，有一些选择不依赖于复杂场景判断的其实我们也可以掌握，比如innodb_buffer_pool_size这种参数，只需要知道我们机器总的内存大小和机器的主要用途（是否还有其他必须要占用大量内存的操作）就可以准确的设置他的值了。

5. 一些重要的设计细节

MySQL在设计优化的时候，还会有一些重要的设计细节需要遵循。

基础原则：

1）尽量小的原则。
2）禁止使用外键[增加行锁]（高并发不建议）。
3）自增INT/BIGINT主键（InnoDB引擎表）。
4）字符集和库表的设计要一致，否则报错，join类型转换无法走索引。

实例维度：

1）表的总的大小不要超过500G。
2）总表数量不超过5000个（包括分区表）。

库表字段设计规范:

1）每个表建议不超过50个字段。
2）优先选择utf8mb4字符集（支持移动终端的emoji符号，表情包）。
3）严禁在数据库中明文存储用户的一些核心数据（最好要单向加密）。
4）遇到BLOB，TEXT字段，尽量要拆出去，再用主键做关联。
5）字符类型尽可能采用varchar的数据类型（灵活、高效），最好不要变长更新。
6）日期时间数据建议采用datetime（0000-9999年）类型，5.6以后多个datetime数据类型自动更新为当前时间，5.6之前只有一个可用更新为当前时间。

SQL开发建议：

1）多表join时，join列的数据类型要一致（长度，类型，字符集）。
2）多表join时，把过滤后结果集较小的表作为驱动表，建议统一采用inner join让优化器自动优化，如果优化器优化错误，可用采用straight_join强制执行顺序。
3）不要执行sellect * 操作，会导致io代价高。
4）不要执行like '%x%'这种前缀有%的操作
5）尽量不用'!='条件，因为扫描的数据量超过20%~30%范围时，会把执行计划变成全表扫描，不管有无索引。
6）优先使用union all代替union，这样会减少临时表的生成。
7）所有SQL都要通过SQL审核系统检查符合标准后才能上线。
8）可以监控MySQL的线程状态，监控InnoDB的事务状态，一般是大于5秒。
9）设置修改锁定行数的阈值，比如大于10行，可以避免潜在长时间锁或者事务SQL运行的风险。
10）检查或者监控SQL注入的风险，比如SLEEP函数，UNION ALL函数。