回顾MySQL中的大表优化过程

Myth丶恋晨 2022-11-03 00:49 94阅读 0赞

## 一 概述 ##

在我们的系统中，随着使用时间的推移，数据库中的数据量越来越大，当达到千万级时，查询速度会非常的慢，当数据达到亿量级时，可能直接卡死，所以我们需要对数据库进行优化。

## 二 优化方案 ##

*  方案一：优化现有的MySQL数据库，这样不需要修改源代码，对业务没有实际的影响，成本低。这样无法根治问题，当数据量到达一定的瓶颈时，问题还是会再出现。
 *  方案二：升级数据库类型，而且该数据库能够兼容MySQL，这样也无需修改源代码，对业务也没有实际的影响。这样几乎不需要任何操作就能提升数据库性能，但是需要一定的金钱成本。
 *  方案三：大数据解决方案，更换[newSQL][]/noSQL数据库，这样虽然没有数据瓶颈的问题，但是需要对源代码进行修改，会影响到业务，同时需要比较高的成本。

## 三 优化现有的数据库 ##

**数据设计角度优化**

1.  表字段避免null值的出现，null值很难查询优化且占用额外的索引空间，推荐默认数字0代替null值的出现。
2.  尽量使用INT而非BIGINT，如果非负则加上UNSIGNED(这样数值就会扩大一倍)，使用TINYINT，SMALLINT，MEDIUM\_INT更好。
3.  使用枚举和整数代替字符串类型。
4.  尽量使用TIMESTAMP而非DATETIME。
5.  单表不要太多字段，建议20个以内。
6.  用整数型存放IP地址等。

**SQL编写角度优化**

1.  使用limit对查询结果的记录进行限定。
2.  避免select\*，将需要查找的字段列出来。
3.  使用连接（join）来代替字句查询。
4.  拆分大delete或者insert语句。
5.  不做列运算：select id where age + 1 = 9，任何队列的操作都将导致表扫描，它包括数据库函数，计算表达式等，查询时要尽可能将操作移至等号右边。
6.  SQL语句尽可能简单，一条SQL只能在一个CPU中运算，大语句拆成小语句，减少锁时间；一条大SQL可以堵死整个库。
7.  OR改写成IN，因为OR的效率是n级别，而IN效率是log(n)级别的，IN的个数建议控制在200以内。
8.  不用函数和触发器，在应用程序中实现。
9.  避免类似于%\*\*\*的模糊查询。
10. 少用JOIN。
11. 使用同类型进行比较，比如用‘666’比较'888'，666比较888等。
12. 尽量避免在WHERE字句中使用!=或者<,>等操作符，否则数据库引擎将放弃使用索引而进行全表扫描。
13. 对于连续数值，使用BETWEEN而不用IN：select in form table where num between 1 and 5。
14. 列表数据不要使用全表，使用limit子句进行分页，分页数量也不要太大。

**索引设计角度优化**

1.  索引是需要存储空间的，所以索引并非越多越好，要根据查询进行有针对性的创建，考虑在WHERE和ORDER BY命令上涉及的列上建立索引，同时可根据EXPLAIN来查看是否用了索引还是全表扫描。
2.  在设计索引的时候，我们应该避免在WHERE字句中对字段进行NULL值判断，否则将导致存储引擎放弃使用索引而进行全表扫描。
3.  值分布稀少的字段不适合建立索引，如最多只有三种值的“性别字段”。
4.  字符字段只建前缀索引。
5.  字符字段最好不要作为主键使用。
6.  不适用外键，可以使用程序来保证约束。
7.  尽量不使用UNIQUE索引类型，可以使用程序来保证约束。
8.  使用多列索引时需要注意顺序和查询条件的一致，同时删除不必要的单列索引。
9.  使用可存放下数据的最小数据类型，整型<data，time<char，varchar等。
10. 使用简单的数据类型，整型比字符处理开销更小，因为字符串的比较更加复杂。如int类型存储时间类型，bigint类型转ip函数。
11. 使用合理的字段属性长度，可长度的表更快。使用enum，char而非varchar。
12. 尽可能使用not null定义字段。
13. 尽量少用text，使用时最好进行分表。
14. 查询频繁的列，在where，group by，order by，on从句中出现的类常见索引。
15. where子句中<，<=，=，>，>=，between，in以及like字符串 + 通配符（%）出现的列。
16. 长度小的列，索引字段越小越好，因为数据的存储单位是页，一页中能存下的数据越多越好。
17. 离散度大（不同值多）的列，放在组合索引前面。查看离散度，通过统计不同列值来实现，count越大，离散程度就越高。

**分区**

*  分区可以让表单存储更多的数据，同时表的数据更加容易维护，可以通过清楚整个分区批量删除大量数据，也可以通过增加新的分区来支持新插入的数据。此外，还可以对一个独立分区进行优化，检查，修复等操作。
 *  部分查询能够从查询条件判断只能在少数上分区上，速度很快。
 *  分区表的数据还可以分布在不同的物理设备上，从而高效利用多个硬件设备。
 *  可以使用分区表来避免某些特殊瓶颈，例如InnoDB单个索引的互斥访问，ext3文件系统的inode所竞争。
 *  可以备份和恢复单个分区。
 *  一个表对最多只能有1024个分区。
 *  如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进去。
 *  分区表无法使用外键约束。
 *  NULL值会使分区过滤无效。
 *  所有分区必须使用相同的存储引擎。

**分表**

*  分表的目的是将一张大表，按照分成多张表，然后将一次查询分成多次查询，最后将查询结果返回给用户。
 *  分表分为垂直拆分和水平拆分，通常以某个字段做拆分项。如将id差分成n张表，表名为tableName\_id%n。

分表需要修改源程序代码，所以会增加开发的工作量，增加开发所需的成本，所以适合在开发初期就考虑到了大量数据存在，做好了分表处理，不适合应用上线之后再做修改，成本就会很高。

**分库**

*  将一个数据库拆分成多个数据库，然后进行读写分离的的设计，实际开发过程中分库的开发成本也是非常大的。

**换大的数据引擎**

*  hadoop家族中的hbase/hive就值得考虑，但是需要很高的运维成本。
 *  选择阿里云的MaxCompute配合DataWorks，按量付费，成本较低。MaxCompute可以理解为开源的Hive，提供了SQL/mapreduce/ai算法/python脚本/shell脚本等方式操作数据，数据以表格的形式展示，以分布式方式存储，采用定时任务和批处理的方式处理数据。DataWords提供了一种工作流的方式管理你的数据处理任务和调度监控。

[参考资料][Link 1]

**升级数据库**

*  开源数据库会代理大量的运维成本且其工业品质会和MySQL尚有差距，但是作为备选方案，我们可以选择：[tiDB][]，[Cubird][]。
 *  阿里云POLARDB，POLARDB是阿里云自研的下一代关系型分布式云原生数据库，100%兼容MySQL，存储容量最高可达100T，性能提升至MySQL的6倍。POLARDB既融合了商业数据库的稳定，可靠，高性能的特征，又具有开源数据库的简单，可扩展，持续迭代的又是，而成本只需商业数据库的1/10。
 *  阿里云的[OceanBase][]，经过淘宝双十一的考研，性能卓著。[参考资料][Link 2]
 *  阿里云的云数据库HybridDB for MySQL(原PetaData)同时支持海量的数据在线事务（OLTP）和在线分析（OLAP）的HTAP(Hybrid Transaction/Analytical Processing)
 *  腾讯云DCDB，DCDB又名[TDSQL][]，一种兼容MySQL协议和语法，支持自动水平拆分的高性能分布式数据库——即业务显示为完整的逻辑表，数据却均匀的拆分到多个分片中；每个分片默认采用主备架构，提供灾备、恢复、监控、不停机扩容等全套解决方案，适用于TB或PB级的海量数据场景。

[newSQL]: https://www.cnblogs.com/GO-NO-1/p/10774226.html
[Link 1]: https://developer.aliyun.com/profile/ongasiu3ykxec?spm=a2c6h.12873639.0.0.d5e72b8eDR5U5D
[tiDB]: https://pingcap.com/
[Cubird]: https://www.cubrid.org/
[OceanBase]: https://help.aliyun.com/product/26458.html
[Link 2]: https://github.com/alibaba/oceanbase
[TDSQL]: https://blog.csdn.net/test_soy/article/details/53259136