单表优化除非未来单表数据会持续上升，否则不要一开始就考虑拆分，拆分会带来逻辑、部署、运维等方面的各种复杂度.一般整数值在***以下的表和字符串在500万以下的表问题不大。事实上，在很多情况下，MySQL单表的性能还有很大的优化空间，甚至可以正常支持超过***的数据量。Fields关于字段：尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而不是INT，如果非负则加上UNSIGNED。VARCHAR的长度仅分配真正需要的空间。使用枚举或整数而不是字符串类型。尽可能使用TIMESTAMP而不是DATETIME。单表字段不要太多，建议保持在20个以内。避免使用NULL字段，难以优化，占用额外的索引空间。使用整数存储IP。Indexes关于索引：索引不是越多越好。它们应该根据查询创建。考虑在WHERE和ORDERBY命令中涉及的列上建立索引。您可以使用EXPLAIN查看是否使用了索引或全表扫描。尽量避免在WHERE子句中判断字段的NULL值，否则引擎会放弃使用索引，进行全表扫描。值分布非常大的字段不适合做索引，比如只有两个或三个值的“性别”。只为字符字段建立前缀索引。字符字段***不作为主键。不使用外键，约束由程序保证。尽量不要用UNIQUE，程序保证约束。使用多列索引时，注意保持顺序与查询条件一致，删除不需要的单列索引。查询SQL关于查询SQL：您可以通过启用慢查询日志来查找慢SQL。不进行列操作：SELECTidWHEREage+1=10。任何对列的操作都会引起表扫描，包括数据库教程函数，计算表达式等。查询时尽量将操作移到等号右边尽可能。SQL语句尽可能简单：一条SQL只能在一个CPU上运行；将大语句拆分成小语句，减少锁定时间；一个大的SQL可以阻塞整个库。不要使用SELECT*。OR改写为IN：OR的效率为n级，IN的效率为log(n)级，IN的个数建议控制在200以内。在没有函数和触发器的应用中实现。避免%xxx式查询。谨慎使用JOIN。使用相同的类型进行比较，例如'123'和'123'，123和123。尽量避免在WHERE子句中使用!=或<>运算符，否则引擎将放弃使用索引并执行完整的表扫描。连续的数字，用BETWEEN代替IN：SELECTidFROMtWHEREnumBETWEEN1AND5。list数据不要用全表，用LIMIT分页，每页的页码不要太大。该引擎目前广泛使用的有MyISAM和InnoDB两种引擎：MyISAMMyISAM引擎是MySQL5.1及更早版本的默认引擎，其特点是：不支持行锁，读时锁定所有需要读取的表，写时独占锁被添加到表中。不支持交易。不支持外键。不支持崩溃后的安全恢复。虽然该表具有读取查询，但它支持向表中插入新记录。支持对BLOB和TEXT的前500个字符进行索引，支持全文索引。支持延迟索引更新，大幅提升写入性能。对于不会修改的表，支持表压缩，大大减少磁盘空间占用。InnoDBInnoDB在MySQL5.5之后成为默认索引。它的特点是：支持行锁，使用MVCC支持高并发。支持交易。支持外键。支持崩溃后的安全恢复。不支持全文索引。PS：据说InnoDB在MySQL5.6.4已经支持全文索引了。一般来说，MyISAM适用于SELECT密集型表，而InnoDB适用于INSERT和UPDATE密集型表。可以使用以下工具对系统调整参数进行基准测试：sysbench：一种模块化、跨平台和多线程的性能测试工具。https://github.com/akopytov/sysbenchiibench-mysql：基于Java的MySQL/Percona/MariaDB索引插入性能测试工具。https://github.com/tmcallaghan/iibench-mysqltpcc-mysql：Percona开发的TPC-C测试工具。https://github.com/Percona-Lab/tpcc-mysql有很多调整参数。详情请参考官方文档。下面是一些比较重要的参数：back_log：back_log的值可以表示在MySQL暂时停止响应新请求之前，短时间内可以在堆栈中存储多少请求。也就是说，如果MySQL的连接数据达到了max_connections，新的请求就会入栈等待某个连接释放资源。栈的编号是back_log。如果等待连接数超过back_log，则不会Grant连接资源。可以从默认的50增加到500。wait_timeout：数据库连接空闲时间，空闲连接会占用内存资源。可以从默认的8小时减少到半小时。max_user_connection：最大连接数，默认0没有上限，最好设置一个合理的上限。thread_concurrency：并发线程数，设置为CPU核数的两倍。skip_name_resolve：对外部连接禁用DNS解析，消除DNS解析时间，但要求所有远程主机使用IP访问。key_buffer_size：索引块的缓存大小。增加它会增加索引处理速度并对MyISAM表的性能产生影响***。对于4G左右的内存，可以设置为256M或者384M。通过查询“key_read%”等显示状态，确保key_reads/key_read_requests低于0.1%***。innodb_buffer_pool_size：缓存数据块和索引块，对InnoDB表性能影响很大。通过查询像'Innodb_buffer_pool_read%'这样的显示状态，确保(Innodb_buffer_pool_read_requests–Innodb_buffer_pool_reads)/Innodb_buffer_pool_read_requests尽可能高。innodb_additional_mem_pool_size：InnoDB存储引擎用于存储数据字典信息和一些内部数据结构的内存空间大小。当数据库对象较多时，适当调整该参数的大小，保证所有的数据都存储在内存中，提高访问效率。太小时，MySQL会在数据库的错误日志中记录Warning信息。这时候就需要调整这个参数的大小。innodb_log_buffer_size：InnoDB存储引擎事务日志使用的缓冲区，一般来说不建议超过32MB。query_cache_size：缓存MySQL中的ResultSet，这是一条SQL语句执行的结果集，所以只能用于Select语句。表数据的任何更改都会使引用该表的所有Select语句的QueryCache中的缓存数据失效。因此，当我们的数据变化非常频繁时，使用QueryCache可能得不偿失。根据命中率调整(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))，一般不建议太大，256MB可能差不多，大的配置静态数据可以适当调整。您可以使用命令showstatuslike'Qcache_%'查看当前系统QueryCache使用大小。read_buffer_size：MySQL读入缓冲区大小。顺序扫描表的请求将分配一个读取缓冲区，MySQL将为其分配一个内存缓冲区。如果对表的顺序扫描请求非常频繁，则可以通过增加此变量的值以及内存缓冲区的大小来提高其性能。sort_buffer_size：MySQL用于执行排序的缓冲区大小。如果你想提高ORDERBY的速度，首先看看你是否可以让MySQL使用索引而不是额外的排序阶段。如果不是，请尝试增加sort_buffer_size变量的大小。read_rnd_buffer_size：MySQL的随机读缓冲区大小。当以任意顺序（例如排序顺序）读取行时，会分配一个随机读取缓冲区。排序查询时，MySQL会先扫描缓冲区，避免磁盘搜索，提高查询速度。如果需要对大量数据进行排序，可以适当增加这个值。但是，MySQL会为每个客户端连接释放这个缓冲空间，所以你应该尽量适当地设置这个值，以避免过多的内存开销。record_buffer：每个执行顺序扫描的线程为其扫描的每个表分配一个这个大小的缓冲区。如果进行大量顺序扫描，您可能需要增加此值。thread_cache_size：保存当前未与连接关联但准备好稍后为新连接服务的线程，可以快速响应连接的线程请求而无需创建新的线程。table_cache：与thread_cache_size类似，但用于缓存表文件，对InnoDB影响不大，主要用于MyISAM。升级硬件ScaleUp，不多说，看MySQL是CPU密集型还是I/O密集型，通过增加CPU和内存，使用SSD，可以显着提升MySQL性能。读写分离也是目前常用的优化方式。一般来说，不要用双主或者多主，引入很多从主库读到主库写的复杂度。尝试使用本文中的其他解决方案来提高性能。同时，目前很多拆分方案也考虑到了读写分离。缓存缓存可以发生在这些级别：MySQL内部：相关设置在系统调整参数中描述。数据访问层：比如MyBatis缓存了SQL语句，而Hibernate可以精确到单条记录。这里缓存的对象主要是持久化对象PersistenceObject。应用服务层：可以通过编程手段对缓存实现更精确的控制和更多的实现策略。这里缓存的对象就是数据传输对象DataTransferObject。Web层：网页缓存。浏览器客户端：缓存在客户端。缓存可以根据实际情况单级添加，也可以多级组合。这里着重介绍下服务层的缓存实现。目前主要有两种方式：WriteThrough：数据写入数据库后，同时更新缓存，保持数据库和缓存的一致性。这就是大多数当前应用程序缓存框架（如SpringCache）的工作方式。这种实现非常简单，同步性好，但通常效率低下。WriteBack：当有数据要写入数据库时??，只更新缓存，然后将缓存的数据异步批量同步到数据库。这种实现比较复杂，需要更多的应用逻辑，可能会导致数据库和缓存不同步，但是效率很高。表分区MySQL在5.1版本中引入的分区是一种简单的水平分割。用户创建表时需要添加分区参数，对应用透明，不需要修改代码。对于用户来说，分区表是一个独立的逻辑表，但底层是由多个物理子表组成的。实现分区的代码其实是由底层表的一组对象封装的，但是对于SQL层来说，是一个完整的封装了底层的黑盒。MySQL实现分区的方式也意味着索引也是根据分区的子表定义的，没有全局索引。用户的SQL语句需要针对分区表进行优化，SQL条件中必须包含分区条件的列，这样查询才能定位到少数分区上，否则会扫描所有分区。可以通过EXPLAINPARTITIONS查看某条SQL语句会落在哪些分区上，从而进行SQL优化。如下图，5条记录落在两个分区上：mysql>explainpartitionsselectcount(1)fromuser_partitionwhereidin(1,2,3,4,5);+----+--------------+----------------+------------+--------+----------------+--------+--------+------+------+------------------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|Extra|+----+-------------+----------------+------------+--------+---------------+--------+--------+------+------+-------------------------+|1|SIMPLE|user_partition|p1,p4|range|PRIMARY|PRIMARY|8|NULL|5|Usingwhere;使用索引|+----+------------+----------------+------------+--------+----------------+--------+--------+-----+------+----------------------------+1rowinset(0.00sec)分区的好处是：它可以让单个表存储更多的数据。分区表的数据更容易维护。可以通过清空整个分区来批量删除大量数据，也可以添加新的分区来支持新插入的数据。此外，还可以对独立的分区进行优化、检查、修复等操作。部分查询可以根据查询条件确定只落在少数分区上，速度会很快。分区表的数据也可以分布在不同的物理设备上，以高效利用多个硬件设备。分区表可以用来避免一些特殊的瓶颈，比如InnoDB中对单个索引的独占访问，ext3文件系统中的inode锁争用。可以备份和恢复单个分区。分区的限制和缺点：一张表最多只能有1024个分区。如果分区字段中有主键列或唯一索引列，则必须包括所有主键列和唯一索引列。分区表不能使用外键约束。NULL值禁用分区过滤。所有分区必须使用相同的存储引擎。分区类型：RANGE分区：根据属于给定连续范围的列值将行分配给分区。LIST分区：类似于RANGE分区，不同的是LIST分区是根据一组离散值中的一个值匹配的列值来选择。HASH分区：根据使用要插入表中的行的列值计算的用户定义表达式的返回值进行选择的分区。这个函数可以包含任何在MySQL中有效的表达式，它产生一个非负整数值。KEY分区：类似于HASH分区，不同的是KEY分区只支持一列或多列的计算，MySQL服务器提供了自己的哈希函数。一列或多列必须包含整数值。适合分区的场景有：最适合的场景数据具有比较强的时间序列，所以可以按时间分区。如下所示：CREATETABLEmembers(firstnameVARCHAR(25)NOTNULL,lastnameVARCHAR(25)NOTNULL,usernameVARCHAR(16)NOTNULL,emailVARCHAR(35),joinedDATENOTNULL)PARTITIONBYRANGE(YEAR(joined))(PARTITIONp0VALUESLESSTHAN(1960),PARTITIONp1VALUESLESSTHAN(1970),PARTITIONp2VALUESLESSTHAN(1980),PARTITIONp3VALUESLESSTHAN(1990),PARTITIONp4VALUESLESSTHANMAXVALUE);在查询的时候加上时间范围条件的效率会很高，同时可以方便的批量删除不需要的历史数据。如果数据有明显的热点，而除了这部分数据之外其他数据很少被访问，那么可以将热点数据放在一个单独的分区中，让这个分区中的数据有机会缓存??到内存中，且仅在查询时访问高效利用索引和缓存的小型分区表。另外，MySQL有一个早期简单的分区实现——mergetable，限制较多，缺乏优化。不推荐使用，应该换成新的分区机制。垂直拆分垂直拆分是基于数据库中数据表的相关性。比如一个数据库中既有用户数据又有订单数据，那么垂直拆分可以将用户数据放入用户数据库和订单数据中。数据放在订单库中。垂直拆分表是一种垂直拆分数据表的方法。将一个大的多字段表按常用字段和不使用字段拆分是很常见的。每个表的数据记录条数一般是一样的，只是字段不一样，用的是主键关联。比如原来的用户表是：垂直拆分后：垂直拆分的好处是：可以让行数据变小，一个数据块（Block）可以存储更多的数据，查询时会减少I/O的数量次（每次查询读取的块数较少）。可以达到最大化利用Cache的目的。具体来说，垂直拆分的时候，可以把变化不频繁的字段放在一起，变化频繁的字段放在一起。数据维护简单。缺点是：主键冗余，需要管理冗余列。会造成表连接JOIN操作（增加CPU开销），通过在业务服务器上进行JOIN来减轻数据库压力。仍然存在单表数据量过大的问题（需要水平拆分）。事务处理复杂。水平拆分水平拆分是通过一定的策略将数据分段存储。分库中有分表和分库两部分。每一条数据都会分布到不同的MySQL表或库中，达到分布式的效果。支持非常大的数据量。前面的表分区本质上是库中一个特殊的子表。分库分表只是简单的解决了单表数据量过大的问题，因为表中的数据并没有分布到不同的机器上。所以对于减轻MySQL服务器的压力作用不大。大家还是在同一台物理机上争IO、CPU、网络，必须分库解决。如果将之前垂直拆分的用户表水平拆分，结果是：在实际情况中，往往是垂直拆分和水平拆分的组合，即将Users_A_M和Users_N_Z拆分为Users和UserExtras，所以在中有四张表全部的。水平拆分的优点是：没有单库大数据和高并发的性能瓶颈。应用端修改较少。提高了系统稳定性和负载能力。缺点是：分片事务的一致性很难解决。跨节点JOIN性能差，逻辑复杂。数据多次扩容非常困难，维护量极高。分片原则分片原则如下：不分则不分。请参阅单表优化。分片的个数尽量少，分片尽量均匀分布在多个数据节点上，因为查询SQL跨分片越多，整体性能越差，虽然比结果所有数据在一个分片中，只有在需要扩容的时候，增加分片的数量。分片规则需要提前仔细选择和规划。分片规则的选择需要考虑数据的增长方式、数据的访问方式、分片关联问题、分片扩容问题。最近的分片策略有范围分片、枚举分片和一致性哈希分片，这些都有利于扩展。尽量不要在事务中跨越多个SQL分片。分布式事务一直是一个难以处理的问题。尽量优化查询条件，尽量避免Select*方式。大量的数据结果集会消耗大量的带宽和CPU资源。查询尽量避免返回大量的结果集，尽量为经常使用的查询语句建立索引。通过数据冗余和分表来降低跨库JOIN的可能性。这里特别强调分片规则的选择。如果某个表中的数据有明显的时间特征，比如订单、交易记录等，它们通常更适合做时间范围分片，因为对时间敏感的数据，我们往往会关注它最近的数据，查询条件通常有一个用于过滤的时间字段。更好的解决方案是当前活跃的数据以相对较短的跨度分段，而历史数据以相对长的跨度存储。一般来说，分片的选择取决于最频繁查询SQL的条件，因为没有任何Where语句的查询SQL会遍历所有分片，性能相对最差，所以这样的SQL越多，影响越大对系统的影响越大，所以要尽量避免这条SQL的产生。解决方案由于水平拆分涉及的逻辑复杂，目前已经有很多成熟的解决方案。这些方案分为两大类：客户端架构和代理架构。客户端架构通过配置修改数据访问层，管理多个数据源，如JDBC、DataSource、MyBatis，直接连接数据库，完成模块内数据的分片和整合，一般以如下形式呈现一个Jar包。这是一个客户端架构的例子：可以看到分片的实现是和应用服务器一起的，它是通过修改SpringJDBC层来实现的。客户端架构的优点是：应用程序直接连接到数据库，降低了因依赖外围系统而导致宕机的风险。集成成本低，无需额外运维组件。缺点是：仅限于在数据库访问层做文章，扩展性一般，对于更复杂的系统可能无能为力。将分片逻辑的负担放在应用服务器上会产生额外的风险。代理架构通过独立的中间件管理所有数据源和数据分片整合，后端数据库集群对前端应用透明，需要代理组件独立部署和运维。这是一个代理架构的例子：为了分流和防止单点，代理组件一般以集群的形式存在，可能需要ZooKeeper等服务组件来管理。代理架构的优点是：可以处理非常复杂的需求，不受数据库访问层原有实现的限制，可扩展性强。它对应用服务器是透明的，不会增加任何额外的负载。缺点是：需要部署和运维独立的代理中间件，成本高。应用程序需要通过代理连接到数据库，网络上多了一个跃点，导致性能损失和额外风险。各种方案比较目前业界还有很多方案可供选择，但如何选择呢？在我看来，可以通过以下方式来考虑：确定是使用客户端架构还是代理架构。中小型或者比较简单的场景倾向于选择客户端架构，复杂场景或者大型系统倾向于选择代理架构。是否满足具体功能，比如需要跨节点ORDERBY，则优先支持该功能。如果不考虑一年内没有更新的产品，就意味着发展停滞，甚至没有维护和技术支持。***按照大公司→社区→小公司→个人等生产者顺序选择。选择口碑好的，比如Githubstars，用户数量，质量，用户反馈。开源是首选，往往有特殊需求的项目可能需要改源码。根据以上思路，推荐以下方案：客户端架构：ShardingJDBC代理架构：MyCat或Atlas兼容MySQL和水平扩展的数据库目前也有一些开源数据库兼容MySQL协议，比如：TiDBCubrid，但其工业品质与MySQL相比还是有很大的差距，而且需要较大的运维投入。如果想把原来的MySQL迁移到新的可以横向扩展的数据库，可以考虑一些云数据库：阿里云PetaData阿里云OceanBase腾讯云DCDBNoSQLShardingonMySQL是一种戴着枷锁的舞蹈，其实，很多大表对MySQL等RDBMS的需求并不大，不需要ACID。可以考虑将这些表迁移到NoSQL，彻底解决横向扩展的问题，例如：日志、监控、统计数据等非结构化或弱结构化数据，对事务的要求不强，对关联的数据引用不多操作：MysqlThat'sMysqlPolicyMySQL：MySQL5.6参考手册
