随着MySQL自身的发展和不断完善，整个互联网行业都在不知不觉中离不开这个完美而小巧的关系型数据库。整个生态链也变得非常成熟，即使是初创企业和传统企业也可以放心大胆地将数据库迁移到MySQL。趁着大家玩MySQL数据库玩的不亦乐乎，我来说说MySQL架构设计相关的一些话题。本文大纲：MySQL数据库开发规范MySQL高可用架构选择MySQLSharding拆分使用NoSQL解压MySQL一、MySQL数据库开发规范数据库规范有多重要？有过创业公司经历的朋友应该深有体会。规范是数据库运维的基石，可以有效降低数据库出现问题的概率，保证数据库schema的合理设计，方便后续的自动化管理。我们过去花了半年多的时间在数据库标准化上，比如制定数据库开发指南，培训程序员等等，推进的时候遇到了一些阻力。但是标准化之后，运维质量会得到质的提升，DBA的工作效率也会得到提升。在开发规范方面，我们分为两部分：开发规范和运维规范。1、开发规格表设计规范：字段数建议不要超过20-50个。做好数据评估。建议纯INT不要超过1500万，包含CHAR的不要超过1000万。字段类型越小越符合要求。尽量使用UNSIGNED来存储非负整数，因为实际使用中存储负数的场景并不多。将字符转换为数字存储。比如使用UNSIGNEDINT来存储IPv4地址，而不是CHAR(15)，但是这种方式只能存储IPv4，不能存储IPv6。另外，可以考虑将日期转换成数字，如：from_unixtime()、unix_timestamp()。所有字段都定义为NOTNULL，除非你真的想存储null。索引设计规范：1）所有表必须有明确的主键。InnoDB表是按主键排序和存储的IOT表。使用短的、自动递增的列作为索引复制结构。使用行格式。如果一个表有一个主键，它可以加快复制速度。UNSIGNEDINT自加列，也可以考虑BIGINTTINYINT作为主键可能会导致MySQLCrash类型转换会导致查询效率低下。可以用uuid_short()代替uuid()，转成BIGINT存储索引字段数不超过5个，单表索引数不超过5个，避免冗余索引建立的索引可以覆盖80个主要查询的百分比。不求完美，解决了问题的主要矛盾。复合索引排序问题，使用explain确认SQL编写规范：1）避免在数据库中执行大量计算任务，将大事务拆分成多个事务。批量多个操作慎用text和blob大字段。如果要使用查询频繁且拆分计划好的字典表，可以考虑使用CacheAnti-2）优化join避免大表与大表的join，考虑让小表驱动大表join，最多允许三个表join，最后控制成两个表来控制join后where选择的行数3）注意where条件，用EXPLAIN确认where条件的字段，尽量使用高度分化的字段，使索引的性能更好。当出现子查询的SQL时，先确认MySQL版本，使用explain确认执行计划，进行分页优化；DML时，多个值合并到SchemaReview中：1）字符集问题表字符集选择UTF8，如果需要存储emoj表达式，改为UTF8mb设计，增加冗余字段，减少JOIN大写字段考虑统一*100处理成整数，避免使用小数浮点类型存储日志型表，考虑按创建时间横向切割，定期归档历史数据3）快速实现schema设计目标聚焦功能，保证资源节约，平衡各方面的业务技术，做好选择，不要在DB中进行大的计算，减少复杂的操作总体来说，这部分规范还是比较容易理解的，实现起来也不难，可以实现有效的。2.运维规范(1)SQL审计SQL审计相信会让很多DBA同学吐槽。人工审计不仅效率低下且容易出错，而且对DBA的发展也非常不利。我们上班只是为了审计SQL吗？经过痛苦的人工审核，我们接入了去哪儿开源的Inception，并根据自身业务特点做了一些调整。当然，现在已经有很多开源的SQL审核软件，大家可以自由选择，也可以自行开发。在查看和执行在线DDL语句时，要注意MySQL官方原生的OnlineDDL和Percona的pt-osc的一些区别。比如pt-osc每次执行都需要复制整张表，比较慢，优点是不锁表，有完善的条件检测和延迟加载策略控制。官方OnlineDDL虽然不断完善，但是生产环境不是很靠谱，尤其要注意在执行过程中容易造成MDL锁。官方OnlineDDL也比pt-osc有优势，比如增删索引，重命名列等，如下图。(2)权限控制MySQL从5.6开始逐步完善了权限系统。比如MySQL5.6可以安装插件检查密码强度，5.7增加了密码过期机制和账户锁定等功能，同时也对SSL做了一些优化，8.0版本增加了角色功能，权限系统也逐渐向Oracle数据库靠拢。在日常运维中，还可以使用pt-show-grants工具来增加权限审核的力度。应用账户只需要被授予SELECT、INSERT、UPDATE权限，DELETE的逻辑要用UPDATE来实现，并启用sql_safe_updates选项。另一种有效控制权限的方法是SQL堡垒机。早期我们是通过改造MyWebSQL来实现的，在Web版客户端的基础上增加了一些资源控制策略、审计、语法校验等功能。后来用Python开发了更完善的SQL堡垒机，同时支持MySQL、Oracle、Greenplum等数据库。SQL堡垒机不仅可以控制公司内部人员的数据库权限，追溯各类人员对数据库的操作，还可以避免大查询或全表更新的情况发生，支持审计需求，提高整体运维和维修质量更上一层楼。(3)MySQL版本选择MySQLCommunityEdition，用户组***MySQLEnterpriseEdition，收费PerconaServerEdition，有很多新特性，MySQLCommunityEdition最接近MariaDBEdition，国内用户暂时不要选择priority:MySQLCommunityEdition>PerconaServer>MariaDB>MySQLEnterpriseEdition关于版本选择，建议大家跟进官方社区版。目前比较稳定的版本是MySQL5.6，推荐大家使用。如果有特殊需求，可以选择MySQL5.7、PXC、TiDB、TokuDB等数据库。2、MySQL高可用架构选择在MySQL高可用方面，目前业界主流还是基于异步复制技术，如Keepalived、MHA、ZooKeeper等，对数据强一致性要求的场景逐渐开始使用分布式protocols，这方面的典型代表有PXC、GroupReplication、TiDB。接下来我们重点介绍一下人们使用比较多的架构，比如keepalived、MHA、PXC。1、keepalived高可用架构在业界应用广泛。易于部署，易于维护，节省服务器资源。这种架构的一个好处是，发生切换后，只需要将原来的Master重新拉起，就可以恢复高可用，不需要过多的干预。扩展也方便，可以任意挂载只读库和容灾库。但是它的问题也很明显，比如Keepalived检测机制不完善，存在脑裂隐患，数据一致性弱等。还需要注意主从拓扑的设计。如下图，只读库挂在哪个Master比较合适？很明显是M2，另外两种拓扑切换后会影响只读库的访问。2、MHAMHA自诞生以来，就受到了业界的广泛关注，迅速走红。与keepalived相比，MHA最大的优势在于发生failover后，可以自动填充binlog，最大程度的保证数据的一致性。从服务器可以自动切换，无需人工干预，在读写分离的环境下也能很好的工作。基于Perl语言的脚本也非常方便二次开发。MHA非常适合读写压力大的应用。但是由于MHA工作时需要配置SSH互信，所以在选择这种架构的时候，内网安全一定要做到位。此外，它还可以与BinlogServer一起使用。3.PXCPXC全称PerconaXtraDBCluster，是Percona基于Galera协议开发的产品。PXC牺牲了CAP中的P（PartitionTolerance），保留了C（Consistency）和A（Availability）。这种结构非常适合电子商务和金融服务。自从PXC和GroupReplication的出现，MySQL彻底扫清了进入金融行业的门槛。PXC的优势：同步复制，解决了传统架构中复制延迟和脑裂问题强一致性数据多主复制，每个节点都可以读写数据并行复制，多个事务可以并行推送到其他节点高可用，单点故障不影响集群可用性。自动部署新节点，几乎完全兼容传统MySQL。使用PXC需要注意的问题：不要有大额交易的桶效应。集群性能取决于性能最差的节点。并发效率丢失。推荐更高的网络要求。10G网络，多点并发写时锁冲突，死锁问题，多写无法扩展，无法解决热点更新问题另外还有一种高可用架构，使用DNS/ZooKeeper，通常需要自己开发。没有通用的解决方案，更适合大规模集群的高可用，这里不再赘述。简单回顾一下上述几种高可用架构：Dual-Master架构：非常成熟，应用广泛，注意延迟和数据一致性。PXC：分布式协议，数据一致性强，并发效率略低，可用性好MHA：指标介于M-M和PXC之间，无性能损失，适用于读写分离架构。总而言之，没有最好的架构，只有最合适的架构。只需选择适合您业务的那个。3.MySQLshardingsplit下一题是第三题，mysqlsplit的原理和分库分表的设计。首先我想问一个问题，为什么要拆呢？不拆是不可能的吗？根据我们的经验，当数据和业务达到一定规模时，不可避免地会面临分库分表的问题。这就像汽车的引擎。要达到更高的性能，4缸和6缸显然是不够的。V8和V12才是王道。拆分可以解决以下问题：单库并发大单库物理文件太大，单表太大，DDL不可接受防止性能瓶颈，提高性能防止抖动不稳定，确定拆分数据库。应该怎么拆？垂直拆分优点：拆分简单明了，拆分规则清晰，应用模块清晰，易于集成，数据维护方便，易于定位，易于定位。缺点：需要改表关联到程序来完成事务处理。复杂的热点表中也可能存在性能瓶颈。过度拆分会导致管理复杂。水平拆分的优点：不会影响表关联和事务操作。可以拆分超大型表和高负载表。与小拆分相比，应用程序端更改可以提高性能。,更容易扩展。缺点：数据分散影响聚合函数的使用。切分规则复杂，维护难度较大。后期迁移比较复杂。是先分库还是先分表？分库的优点：实现简单，库与库之间的边界清晰，易于维护。缺点是不利于频繁的跨库操作，无法解决单表数据量大的问题。分表的优点：可以解决分库的缺点，而缺点恰恰是分库的优点。分表的实现比较复杂，尤其是表分规则的划分，程序的编写，以及后期的数据库拆分和移植维护。直接select分库分表，一脸懵逼的做法是不可取的。主要看需要实现什么样的扩展方式再决定是先分库还是分表，根据具体场景来决定。分库分表的最终目的是为了扩展，就看拆分的规划设计是针对哪一层了。上面的问题都解决了，是时候考虑如何实现了。是在应用程序中实现还是使用中间件？个人认为，如果是小范围拆分，可以直接在程序逻辑中实现。考虑使用各种中间件。目前业界已经开源了很多MySQL中间件产品，比如Atlas、DBProxy、MyCAT、OneProxy、DRDS、Vitess等，每个中间件都有自己的特点，个别不成熟的中间件可能会有一些bug。在选择之前做好相关的调研和测试，确保在线使用时能hold住。想要完全贴合自己的业务，更好的掌控，还是需要自己去开发。说说我们分手的经历吧。首先，我们先对压力比较大的数据库做一个垂直拆分，把activity、后台统计等服务剥离出来。这一步也是最容易完成的。接下来如果是消息类型的数据，按照时间维度进行拆分。单表控制在5-10G，行数控制在500-1000w。这个时候我们发现数据库的性能比较好，维护起来也比较容易。如果是用户类数据，按照Hash或者Range拆分。这种情况下使用这种方式拆分会拆分的更均匀。并发还是比较高怎么办？可以在拆分时间维度的基础上按Range或者Hash拆分。***要注意的是不要拆分的过分，会导致复杂度的增加。当schema设计合理的时候，10亿的数据量也能跑的很好。一些非关键的应用，比如日志、监控数据等，也可以通过分区表和TokuDB来抵抗。分裂对应用层总是有损的。做一个“懒惰”的DBA。4.使用NoSQL解压MySQL***一个话题，说说NoSQL。现在NoSQL遍地开花，应用也很广泛。业界使用比较广泛的主要集中在Redis、MongoDB、Cassandra等NoSQL数据库上。今天主要说说与MySQL关系最密切的Redis。为什么要使用Redis？数据存储在内存中，存取速度快。它可以支持大规模操作和爆炸性负载。数据结构丰富，有效缓解MySQL压力。协议简单，支持多种语言的API。存储大量数据时无需担心性能问题。抵抗阅读压力。读操作先到Redis，如果从Redis取不到，再从MySQL数据库访问。从MySQL读取数据后，必须写回Redis。使用Redis需要注意的几点：在性能方面，由于Redis是完全基于内存访问的，所以不用担心性能问题。使用Redis时，注意不要混用Cache和Storage。不要依赖Redis的持久化。持久化Redis还有很多工作要做。另外，如果使用Redis作为存储，一旦Redis的内存被占满，那就惨了，所有的Redis连接都会卡死，没有响应。如果只是把Redis当做缓存，那么问题不大。还存在缓存穿透、缓存雪崩、重建热点key时缓存失效等问题。这些问题也是关注的焦点。如何使用Redis加速MySQL：1）使用K/V结构缓存结果，如存储用户信息、全球排名、统计信息等2）使用其丰富的数据结构对MySQL进行解压，如计数器、排序,Hash(表映射到Redis),消息队列等。在大型MySQL架构设计过程中，除了学习别人的经验外，还要关注各种架构背后的业务场景和架构思想，并结合自己的实际业务场景，设计出好的系统架构来了。
