数据库大表调优策略总结

数据库大表优化是我们开发中面临的最直观的问题，我将其分为两个层次，一个层次是数据库层次的，另一个层次是服务优化层次。

简介

数据库层次的调优最常见的策略是分表（垂直分表），其次是sql优化，索引优化，而后是缓存。再之后是主从复制，读写分离，分库分表分区。不要以为这就完了，其实对一个大型服务来说，以上措施基本是常规必做，但是注定收效甚微。上面的措施对服务能力的提升非常有限。我们拿最终措施分库分表分区来说，大部分适合我们将其划分为服务高可用范畴，而不是数据库调优范畴，但是分库分表分区首先与数据库集群的协调能力，我们不可能无限分库分表。分区同时受限于数据库支持的文件大小，也不可能无限制分区。另外一个问题是成本问题啊，分库分表分区带来的是数据库服务某一个方面的性能提升，但是对于服务器来说，服务器的整体性能并没有发挥出来，作为企业必须考虑成本问题，比可能大量的浪费机器资源在这上面。但是当这些都达到服务极限的适合我们该怎么进行数据库调优呢？

服务层次的调优比较常见的是全文索引（lunace），数据热点，机器学习。这部分我个人认为核心思想还是基于数据仓库数据集市的理念，通过对数据进行预处理将数据拆分为一个个小的数据集合，然后通过机器学习，实时计算快速匹配到对应的服务。但是不管是那种情况，对用户而言最终面对的数据集合都是非常有限的。在我们之前的文章中提到newsql其实大体就是这个理念，newsql对数据的使用不在局限于数据是否一定要来自数据库，他可以是文件，也可以是内存，也可以是用户缓存。摆脱了数据库本身对数据的约束他也就有了快的资本。newsql对数据的使用也不在局限与具体的数据结构，它可以是数据库记录，也可以是haspmap，list等常见的数据结构，摆脱了数据库数据结构的现在，算法上就可以选择更大的空间，我们知道数据库通过B+Tree存储数据，但是平衡树，跳跃表，双数组等等效率都比B+Tree有过之而无不及。但是这部分内容说实在的实现起来非常的麻烦，首先技术栈这一关很多企业都会避而远之了。（其实有很多变通的方法的，只是对于阉割版的，或许会让你性质缺缺，而且有些东西一点就透，自己好好省核一下自己的代码很容易就会发现，很多东西我们已经不知不觉的在用了）。

如果看到这里你还在看，那么我也不能偷懒了，下面整理了一些我们常见但不常用的数据库层次的优化策略。数据库层次的优化主要有两个点，一个是使用索引的使用，另一个问题是减少单表数据量，第三个是业务分流，提高IO效率。

优化sql

优化sql主要是为了避免全表扫描和提高数据过滤量，常见措施包括：

1. 尽量避免Like的参数以通配符开头，否则数据库引擎会放弃使用索引而进行全表扫描。
2. 不以通配符开头的sql语句，例如：select * from table1 where code like '%jone%'
3. 避免where条件不符合最左前缀原则，最左前缀决定使用的索引，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，也就是说范围查询之前的条件是我们查询中使用的索引。
4. 尽量避免使用！= 或 <>操作符
5. 避免在 where 子句中对字段进行表达式操作
6. 避免在where子句中对字段进行null值判断
7. 避免在where子句中使用or来连接条件
8. 避免在order by子句中使用表达式,合理使用过滤条件减少数据量
9. 提高GROUP BY 语句的效率,合理使用过滤条件减少数据量
10. 用 exists 代替 in
11. 尽量使用DISTINCT的代替GROUP BY
12. 尽量用UNION ALL 代替UNION，UNION ALL不执行SELECT DISTINCT函数
13. 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算
14. 尽量使用表变量来代替临时表
15. 避免频繁创建和删除临时表
16. 尽量使用数字型字段
17. 使用变长 varchar/nvarchar 代替 定长char/nchar
18. 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定
19. 尽量避免使用游标
20. 尽量避免大事务操作，提高系统并发能力
21. 尽量避免向客户端返回大数据量

优化索引

1. 对出现在where子句中的字段加索引
2. 大量非重复值的列，避免使用索引
3. 索引字段避免出现null
4. 频繁更改的列，避免使用索引，会提高索引维护成本，降低服务器效率

以上是sql和索引的常见优化策略，常见但是很垃圾的策略，你能否想象当你费尽心思完成这些之后，你的架构师告诉你扩容服务器一次提高一倍的性能；数据表拆分吧一次性将数据降低好几个数量级的适合，你会是一种什么样的心态。sql优化、索引优化可以成为研发人员的一种素养，自然而然体现出来就好，如果真的要为一个sql去挖空心思，那绝对会得不偿失。下面的部门主要整理了一些常见的数据库层数的优化，其实这部分与其说提升sql效率，我觉得更多的应该归为服务高可用一类。

主从复制、主主复制

数据热备，服务高可用，提高单个机器的I/O性能，性能优化这块，主从复制和主主复制主要是提供IO效率。

读写分离

读写分离通常伴随主从复制，除了具有主从复制的特性外，读写分离将极大程度的缓解X锁和S锁争用和数据查询分离从而提高单机效率。

分区、垂直分表、水平分表

表分区是将数据存在不同的持久化设备上，其实就是将一张大数据量表中的数据按照不同的分区策略分配到不同的系统分区、硬盘或是不同的服务器设备上，实现数据的均衡分配，这样做的好处是均衡大数据量数据到不同的存储介子中，这样每个分区均摊了一部分数据，然后可以定位到指定的分区中，对数据表进行需求操作。在MySql中支持四种表分区的方式，分别为HASH、RANGE、LIST及KEY，HASH分区主要用来确保数据在预先确定数目的分区中平均分布，而在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中，而在HASH分区中，MySQL自动完成这些工作。

• 分表是将数据存放在不同的结构相同的表中，其实就是将一张大数据量的表分成多个小表实现数据均衡。
• 垂直分表是将一个表的数据拆分为多个表，每个表保留一部分的数据集合，确保但表中数据更集中有效精简，以减少空间浪费，提高单位空间数据存储能力。
• 水平分表是将一个表的数据根据不同的维度进行拆分，每个表保存某一维度的所以数据，确保每个表中的数据量比较少。
• 分区和分表策略同样受限于数据库支持的文件大小，如果分区分表后数据量依然比较大则要考虑进一步的拆分。

总结：总结：以上都是一些看起来非常有效，实际上基本是鸡肋的策略。特别是sql调优和索引优化这部分。为什么这么说呢，软件设计不仅仅是技术栈的使用，还有很重要的一部分是逻辑的优化。比如在一个大表里面为了避免数据重复，需要重复多次的去查表验证或者查缓存。此时按理说sql优化，索引优化，所有的数据库优化都很重要。但是问题是我们是不是有必要一定去去重，或者说是不是一定要在这个业务环节去重。如果这个环节去重是飞必要的，那么你想想你做那么多的优化，意义何在。在使用技术栈的适合我们常常会忽略逻辑优化这个更重要的问题。