共计 4265 个字符，预计需要花费 11 分钟才能阅读完成。
MySQL 单机数据库优化的实践有哪些，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。
数据库优化有很多可以讲，按照支撑的数据量来分可以分为两个阶段：单机数据库和分库分表，前者一般可以支撑 500W 或者 10G 以内的数据，超过这个值则需要考虑分库分表。另外，一般大企业面试往往会从单机数据库问起，一步一步问到分库分表，中间会穿插很多数据库优化的问题。丸趣 TV 小编试图描述单机数据库优化的一些实践，数据库基于 mysql，如有不合理的地方，欢迎指正。
1、表结构优化
在开始做一个应用的时候，数据库的表结构设计往往会影响应用后期的性能，特别是用户量上来了以后的性能。因此，表结构优化是一个很重要的步骤。
1.1、字符集
一般来说尽量选择 UTF-8，虽然在存中午的时候 GBK 比 UTF- 8 使用的存储空间少，但是 UTF- 8 兼容各国语言，其实我们不必为了这点存储空间而牺牲了扩展性。事实上，后期如果要从 GBK 转为 UTF- 8 所要付出的代价是很高的，需要进行数据迁移，而存储空间完全可以用花钱扩充硬盘来解决。
1.2、主键
在使用 mysql 的 innodb 的时候，innodb 的底层存储模型是 B + 树，它使用主键作为聚簇索引，使用插入的数据作为叶子节点，通过主键可以很快找到叶子节点，从而快速获取记录。因此在设计表的时候需要增加一个主键，而且最好要自增。因为自增主键可以让插入的数据按主键顺序插入到底层的 B + 树的叶子节点中，由于是按序的，这种插入几乎不需要去移动已有的其它数据，所以插入效率很高。如果主键不是自增的，那么每次主键的值近似随机，这时候就有可能需要移动大量数据来保证 B + 树的特性，增加了不必要的开销。
1.3、字段
1.3.1、建了索引的字段必须加上 not null 约束，并且设置 default 值
1.3.2、不建议使用 float、double 来存小数，防止精度损失，建议使用 decimal
1.3.3、不建议使用 Text/blob 来保存大量数据，因为对大文本的读写会造成比较大的 I / O 开销，同时占用 mysql 的缓存，高并发下会极大的降低数据库的吞吐量，建议将大文本数据保存在专门的文件存储系统中，mysql 中只保存这个文件的访问地址，比如博客文章可以保存在文件中，mysql 中只保存文件的相对地址。
1.3.4、varchar 类型长度建议不要超过 8K。
1.3.5、时间类型建议使用 Datetime，不要使用 timestamp，虽然 Datetime 占用 8 个字节，而 timestamp 只占用 4 个字节，但是后者要保证非空，而且后者是对时区敏感的。
1.3.6、建议表中增加 gmt_create 和 gmt_modified 两个字段，用来记录数据创建的修改时间。这两个字段建立的原因是方便查问题。
1.4、索引创建
1.4.1、这个阶段由于对业务并不了解，所以尽量不要盲目加索引，只为一些一定会用到索引的字段加普通索引。
1.4.2、创建 innodb 单列索引的长度不要超过 767bytes，如果超过会用前 255bytes 作为前缀索引
1.4.3、创建 innodb 组合索引的各列索引长度不要超过 767bytes，一共加起来不要超过 3072bytes
2、SQL 优化
一般来说 sql 就那么几种：基本的增删改查，分页查询，范围查询，模糊搜索，多表连接
2.1、基本查询
一般查询需要走索引，如果没有索引建议修改查询，把有索引的那个字段加上，如果由于业务场景没法使用这个字段，那么需要看这个查询调用量大不大，如果大，比如每天调用 10W+，这就需要新增索引，如果不大，比如每天调用 100+，则可以考虑保持原样。另外，select * 尽量少用，用到什么字段就在 sql 语句中加什么，不必要的字段就别查了，浪费 I / O 和内存空间。
2.2、高效分页
limit m,n 其实质就是先执行 limit m+n，然后从第 m 行取 n 行，这样当 limit 翻页越往后翻 m 越大，性能越低。比如
select * from A
limit 100000,10，这种 sql 语句的性能是很差的，建议改成下面的版本:
selec id,name,age
from A where id =(select id from A limit 100000,1) limit 10
2.3、范围查询
范围查询包括 between、大于、小于以及 in。Mysql 中的 in 查询的条件有数量的限制，若数量较小可以走索引查询，若数量较大，就成了全表扫描了。而 between、大于、小于等，这些查询不会走索引，所以尽量放在走索引的查询条件之后。
2.4、模糊查询 like
使用 like %name% 这样的语句是不会走索引的，相当于全表扫描，数据量小的时候不会有太大的问题，数据量大了以后性能会下降的很厉害，建议数据量大了以后使用搜索引擎来代替这种模糊搜索，实在不行也要在模糊查询前加个能走索引的条件。
2.5、多表连接
子查询和 join 都可以实现在多张表之间取数据，但是子查询性能较差，建议将子查询改成 join。对于 mysql 的 join，它用的是 Nested Loop Join 算法，也就是通过前一个表查询的结果集去后一个表中查询，比如前一个表的结果集是 100 条数据，后一个表有 10W 数据，那么就需要在 100*10W 的数据集合中去过滤得到最终的结果集。因此，尽量用小结果集的表去和大表做 join，同时在 join 的字段上建立索引，如果建不了索引，就需要设置足够大的 join buffer size。如果以上的技巧都无法解决 join 所带来的性能下降的问题，那干脆就别用 join 了，将一次 join 查询拆分成两次简单查询。另外，多表连接尽量不要超过三张表，超过三张表一般来说性能会很差，建议拆分 sql。
3、数据库连接池优化
数据库连接池本质上是一种缓存，它是一种抗高并发的手段。数据库连接池优化主要是对参数进行优化，一般我们使用 DBCP 连接池，它的具体参数如下：
3.1 initialSize
初始连接数，这里的初始指的是第一次 getConnection 的时候，而不是应用启动的时候。初始值可以设置为并发量的历史平均值
3.2、minIdle
最小保留的空闲连接数。DBCP 会在后台开启一个回收空闲连接的线程，当该线程进行空闲连接回收的时候，会保留 minIdle 个连接数。一般设置为 5，并发量实在很小可以设置为 1.
3.3、maxIdle
最大保留的空闲连接数，按照业务并发高峰设置。比如并发高峰为 20，那么当高峰过去后，这些连接不会马上被回收，如果过一小段时间又来一个高峰，那么连接池就可以复用这些空闲连接而不需要频繁创建和关闭连接。
3.4、maxActive
最大活跃连接数，按照可以接受的并发极值设置。比如单机并发量可接受的极值是 100，那么这个 maxActive 设置成 100 后，就只能同时为 100 个请求服务，多余的请求会在最大等待时间之后被抛弃。这个值必须设置，可以防止恶意的并发攻击，保护数据库。
3.5、maxWait
获取连接的最大等待时间，建议设置的短一点，比如 3s，这样可以让请求快速失败，因为一个请求在等待获取连接的时候，线程是不可以被释放的，而单机的线程并发量是有限的，如果这个时间设置的过长，比如网上建议的 60s，那么这个线程在这 60s 内是无法被释放的，只要这种请求一多，应用的可用线程就少了，服务就变得不可用了。
3.6、minEvictableIdleTimeMillis
连接保持空闲而不被回收的时间，默认 30 分钟。
3.7、validationQuery
用于检测连接是否有效的 sql 语句，一般是一条简单的 sql，建议设置
3.8、testOnBorrow
申请连接的时候对连接进行检测，不建议开启，严重影响性能
3.9、testOnReturn
归还连接的时候对连接进行检测，不建议开启，严重影响性能
3.10、testWhileIdle
开启了以后，后台清理连接的线程会没隔一段时间对空闲连接进行 validateObject，如果连接失效则会进行清除，不影响性能，建议开启
3.11、numTestsPerEvictionRun
代表每次检查链接的数量，建议设置和 maxActive 一样大，这样每次可以有效检查所有的链接。
3.12、预热连接池
对于连接池，建议在启动应用的时候进行预热，在还未对外提供访问之前进行简单的 sql 查询，让连接池充满必要的连接数。
4、索引优化
当数据量增加到一定程度后，靠 sql 优化已经无法提升性能了，这时候就需要祭出大招：索引。索引有三级，一般来说掌握这三级就足够了，另外，对于建立索引的字段，需要考虑其选择性。
4.1、一级索引
在 where 后面的条件上建立索引，单列可以建立普通索引，多列则建立组合索引。组合索引需要注意最左前缀原则。
4.2、二级索引
如果有被 order by 或者 group by 用到的字段，则可以考虑在这个字段上建索引，这样一来，由于索引天然有序，可以避免 order by 以及 group by 所带来的排序，从而提高性能。
4.3、三级索引
如果上面两招还不行，那么就把所查询的字段也加上索引，这时候就形成了所谓的索引覆盖，这样做可以减少一次 I / O 操作，因为 mysql 在查询数据的时候，是先查主键索引，然后根据主键索引去查普通索引，然后根据普通索引去查相对应的记录。如果我们所需要的记录在普通索引里都有，那就不需要第三步了。当然，这种建索引的方式比较极端，不适合一般场景。
4.4、索引的选择性
在建立索引的时候，尽量在选择性高的字段上建立。什么是选择性高呢？所谓选择性高就是通过这个字段查出来的数据量少，比如按照名字查一个人的信息，查出来的数据量一般会很少，而按照性别查则可能会把数据库一半的数据都查出来，所以，名字是一个选择性高的字段，而性别是个选择性低的字段。
5、历史数据归档
当数据量到了一年增加 500W 条的时候，索引也无能为力，这时候一般的思路都是考虑分库分表。如果业务没有爆发式增长，但是数据的确在缓慢增加，则可以不考虑分库分表这种复杂的技术手段，而是进行历史数据归档。我们针对生命周期已经完结的历史数据，比如 6 个月之前的数据，进行归档。我们可以使用 quartz 的调度任务在凌晨定时将 6 个月之前的数据查出来，然后存入远程的 hbase 服务器。当然，我们也需要提供历史数据的查询接口，以备不时之需。
看完上述内容，你们掌握 MySQL 单机数据库优化的实践有哪些的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注丸趣 TV 行业资讯频道，感谢各位的阅读！
