单表优化除非未来单表数据会持续上升，一开始不要考虑拆分，拆分会带来逻辑、部署、运维而维护整数值在1000万以下的表，字符串在500万以下的表的各种复杂度问题不大。事实上，在很多情况下，MySQL单表的性能还有很大的优化空间，甚至可以正常支持千万级以上的数据量。尝试使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型，而不是字段的INT。如果是非负数，加上UNSIGNEDVARCHAR的长度，只分配真正需要的空间。使用枚举或整数而不是字符串类型。尝试使用TIMESTAMP而不是DATETIME。单表不要字段太多，建议避免使用20个以内的NULL字段，查询优化困难，占用额外索引空间。使用整数存储IP索引索引并不是越多越好。它应该根据查询创建。考虑WHERE并为ORDERBY命令中涉及的列创建索引。可以根据EXPLAIN查看是否使用了索引或者全表扫描。尽量避免在WHERE子句中判断字段的NULL值，否则引擎会放弃使用索引，进行全表扫描。值分布稀疏的字段不适合建索引。比如“gender”等只有两个或三个取值的字段，只为字符字段建立前缀索引。字符字段最好不要使用主键和外键。程序保证约束使用多列索引时思路和查询条件的顺序一致，同时删除不需要的单列索引。查询SQL可以通过打开慢查询日志找出较慢的SQL，不进行列操作：SELECTidWHEREage+1=10。任何对列的操作都会导致表扫描，包括数据库教程函数，计算表达式等.查询的时候尽量把操作移到等号右边。sql语句越简单越好：一条sql只能在一个cpu上执行计算；大语句拆分成小语句，减少锁时间；一个大的sql不用改写SELECT*OR到IN就可以阻塞整个库：OR的效率是n级，IN的效率是log(n)级，不使用函数和触发器，IN的个数建议控制在200以内,并避免在应用程序中进行%xxx查询。少用JOIN，使用相同类型进行比较，比如比较'123'和'123'，尽量避免在WHERE子句中使用123和123的比值使用!=或<>运算符，否则引擎会给出使用索引并执行全表扫描对于连续值，使用BETWEEN而不是IN：SELECTidFROMtWHEREnumBETWEEN1AND5list数据不要用全表，用LIMIT分页，每页数不要太大。目前广泛使用的是MyISAM和InnoDB引擎：MyISAMMyISAM引擎是MySQL5.1及更早版本的默认引擎，其特点是：不支持行锁，读取时锁定所有需要读取的表，并添加独占锁写入时的表不支持事务不支持外键不支持表崩溃后的安全恢复支持在读取查询时向表中插入新记录支持BLOB和TEXT的前500个字符的索引，支持全文索引支持延迟更新索引，大大提高写入性能对于不会被修改的表，支持压缩表，大大降低了InnoDB占用的磁盘空间InnoDB在MySQL5.5之后成为了默认的索引。其特点是：支持行锁，使用MVCC支持高并发支持事务支持外键支持崩溃后安全恢复不支持全文索引（5.6.4以后的版本逐渐开始支持）一般来说，MyISAM适合SELECT密集型表，而InnoDB适用于INSERT和UPDATE密集型表。系统调优参数可以使用以下工具进行基准测试：sysbench：模块化、跨平台、多线程的性能测试工具iibench-mysql：基于Java的MySQL/Percona/MariaDB索引插入性能测试工具tpcc-mysql：TPC-CPercona开发的测试工具有很多具体的调优参数，请参考官方文档，这里有一些重要的参数：要求。即如果MySql的连接数据达到maxconnections，新的Incomingrequests会入栈等待一个连接释放资源。堆栈的数量是积压。如果等待连接数超过back_log，连接资源将不会被授予。可以从默认的50增加到500wait_timeout：数据库连接的空闲时间，空闲连接会占用内存资源。可以从默认的8小时减少到半小时解析，免去DNS解析时间，但是所有远程主机都需要使用IP访问keybuffersize：索引块的缓存大小，提高索引处理速度会增加性能MyISAM表。对于4G左右的内存，可以设置为256M或者384M。通过查询showstatuslike'key_read%'，最好保证key_reads/key_read_requests在0.1%以下。通过查询showstatuslike'Innodb_buffer_pool_read%'，确保(Innodb_buffer_pool_read_requests–
Innodb_buffer_pool_reads)/Innodb_buffer_pool_read_requests越高越好innodbadditionalmempoolsize：InnoDB存储引擎用于存储数据字典信息和一些内部数据结构的内存空间大小，当databaseobject在很多情况下，适当调整该参数的大小，以保证所有的数据都可以存储在内存中，以提高访问效率。太小时，MySQL会在数据库的错误日志中记录Warning信息。这个时候需要调整这个参数参数大小innodblogbuffer_size：InnoDB存储引擎的事务日志使用的buffer。一般来说，不建议超过32MB。querycachesize：缓存MySQL中的ResultSet，它是一条SQL语句执行的结果集，所以只能用于select语句。当某个表的数据发生变化时，所有引用该表的select语句在QueryCache中的缓存数据都会失效。因此，当我们的数据变化非常频繁时，使用QueryCache可能就得不偿失了。根据命中率调整(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))。一般不建议太大。256MB可能差不多就够了。大的配置静态数据可以适当调整。可以使用命令showstatuslike'Qcache_%'查看当前系统QueryCatchsizereadbuffersize：MySql读取缓冲区大小。对表进行顺序扫描的请求将分配一个读取缓冲区，而MySql将为其分配一个内存缓冲区。如果表的顺序扫描请求非常频繁，可以通过增大该变量的值和内存缓冲区的大小来提高其性能。如果你想提高ORDERBY的速度，首先看看你是否可以让MySQL使用索引而不是额外的排序阶段。如果不行，可以尝试增加sortbuffersize变量readrndbuffer_size的大小：MySql随机读缓冲区大小。当以任意顺序（例如，按排序顺序）读取行时，会分配一个随机读取缓冲区。排序查询时，MySql会先扫描缓冲区，避免磁盘搜索，提高查询速度。如果需要排序的数据量较大，可以适当增加该值。但是MySql会为每个client连接释放这个buffer空间，所以这个值应该尽量设置得当，避免过多的内存开销如果你做大量的顺序扫描，你可能想增加这个值tablecache：类似于threadcache_size，不过是用来缓存表文件的，对InnoDB影响不大。主要用于MyISAM升级硬件Scaleup。我不会说太多。根据MySQL是CPU密集型还是I/O密集型，通过增加CPU和内存以及使用SSD，两者都可以显着提高MySQL的性能。读写分离也是目前常用的优化方式。一般来说，不要用双主或者多主，引入很多从主库读到主库写的复杂度。尝试使用本文中的其他解决方案来提高性能。同时，目前很多拆分方案也考虑到了读写分离。读写分离：教你如何实现MySQL的读写分离。缓存可以发生在这几个层面：MySQL内部：在系统调优参数中引入相关设置数据访问层：比如MyBatis缓存SQL语句，而Hibernate可以精确到单条Record，这里缓存的对象主要是持久化对象PersistenceObject应用服务层：这里可以通过编程手段对缓存实现更精确的控制和更多的实现策略，这里缓存的对象是数据传输对象DataTransferObject(DTO)Web层：对网页的缓存Browserclient：客户端的缓存可以根据实际情况单级加入缓存，也可以多级组合加入缓存。缓存扩展：常见的缓存问题及解决方案这里重点介绍服务层的缓存实现。目前主要有两种方式：WriteThrough：数据写入数据库后，同时更新缓存，保持数据库与缓存的连接。一致性。这就是大多数当前应用程序缓存框架（如SpringCache）的工作方式。这种实现非常简单，同步性好，但通常效率低下。WriteBack：当有数据要写入数据库时??，只更新缓存，然后将缓存的数据异步批量同步到数据库。这种实现比较复杂，需要更多的应用逻辑，可能会导致数据库和缓存不同步，但是效率很高。TablePartitionMySQL在5.1版本引入的分区是一种简单的水平分割。用户创建表时需要添加分区参数，对应用透明，不需要修改代码。对于用户来说，分区表是一个独立的逻辑。表，但底层是由多个物理子表组成的。实现分区的代码其实是由底层表的一组对象封装的，但是对于SQL层来说，它是一个完全封装了底层的黑盒。MySQL实现分区的方式也意味着索引也是根据分区的子表定义的，没有全局索引。用户的SQL语句需要针对分区表进行优化。分区条件的列应该包含在SQL条件中，这样查询就可以定位到少量的分区上。否则，将扫描所有分区。可以通过EXPLAINPARTITIONS查看某条SQL语句会落在那些分区上，从而进行SQL优化，如下图，5条记录落在两个分区上：分区的好处是：可以做单表存储更多的数据，分区表的数据更容易维护。分区是批量删除大量数据，也可以增加新的分区来支持新插入的数据。此外，还可以对独立的分区进行优化、检查、修复等操作。有些查询可以根据查询条件判断，只落在少数分区上，速度会很快。分区表的数据也可以分布在不同的物理设备上，从而实现高效可以使用多个硬件设备来使用分区表，避免一些特殊的瓶颈，比如InnoDB的单个索引的互斥访问，inode锁竞争ext3文件系统可以备份和恢复单个分区分区的限制和缺点：一个表最多只能有1024个分区如果分区字段中有主键或唯一索引列，则所有主键列和唯一索引列必须是包括。分区表不能使用外键约束。NULL值将使分区过滤失效。所有分区必须使用相同的存储引擎。类型：RANGE分区：根据属于给定连续区间的列值将行分配给分区LIST分区：类似于RANGE分区，不同之处在于LIST分区是根据列值匹配一组离散的值valuesforselectionHASHpartitioning:partitioningforselectionbasedonthereturnvalueofauser-definedexpression计算使用要插入表的行的列值。这个函数可以包含任何在MySQL中有效的表达式，它产生一个非负整数值。KEYPARTITIONING：类似于HASH分区，只是KEY分区只支持计算一列或多列，MySQL服务器提供了自己的哈希函数。必须有一个或多个包含整数值的列。分区适合的场景是：最适合的场景。数据的时间序列性比较强，所以可以按时间分区，如下图：查询的时候加上时间范围条件会非常高效。同时方便批量删除不需要的历史数据。如果数据有明显的热点，而除了这部分数据之外其他数据很少被访问，那么可以将热点数据放在一个单独的分区中，让这个分区中的数据有机会缓存??到内存中，且仅在查询时访问高效利用索引和缓存的小型分区表。索引相关：带你从头到尾了解MySQL索引。另外，MySQL有一个早期简单的分区实现——mergetable，限制较多，缺乏优化。不建议使用它。应该使用一种新的分区机制来代替垂直拆分垂直数据库是根据数据库中数据表的相关性进行拆分。拓展：互联网公司常用的分库分表方案总结。比如一个数据库中既有用户数据又有订单数据，垂直拆分可以将用户数据放入用户数据库，订单数据放入订单数据库。垂直拆分表是一种垂直拆分数据表的方法。将一个大的多字段表按常用字段和不使用字段拆分是很常见的。每个表的数据记录条数一般是一样的，只是字段不一样。使用主键关联。比如原来的用户表是：垂直拆分后：垂直拆分的好处是：可以让行数据变小，一个数据块（Block）可以存储更多的数据，期间的I/O次数会减少查询（每次查询读取的块数会更少），可以达到最大化利用Cache的目的。具体来说，在垂直拆分中，可以将不经常变化的字段放在一起，经常变化的放在一起数据，便于维护。缺点是：主键冗余，需要管理冗余列，会造成表连接JOIN操作（增加CPU开销）。在业务服务器上加入可以减轻数据库压力。还存在单表数据量过大的问题（需要水平拆分）事务处理复杂度水平拆分概述水平拆分是通过一定的策略将数据分段存储。分库中有分表和分库两部分。每条数据都会分布到不同的MySQL表或库中，达到分布式的效果，可以支持非常大的数据量。前面的表分区本质上是库中一个特殊的子表。数据库中的分表简单的解决了单表数据量过大的问题。由于表中的数据并没有分布到不同的机器上，所以对减轻MySQL服务器的压力作用不大。大家还在同一台物理机上争IO、CPU、网络，这个需要分库来解决。如果将之前垂直拆分的user表水平拆分，结果是：在实际情况中，往往是垂直拆分和水平拆分Users_A_M和Users_N_Z的组合，拆分成Users和UserExtras。四表水平拆分的优点是：没有单库大数据和高并发的性能瓶颈，应用端修改少，提高了系统的稳定性和负载能力缺点是：一致性sharding事务难解决，跨节点join性能差，复杂逻辑数据多次扩展困难，维护量大。Shards应该尽可能均匀分布在多个数据节点上，因为越多的查询SQL跨shards，整体性能就越差。虽然比所有数据都在一个分片的结果要好，但只有在需要增加分片数量的时候才扩容分片规则需要仔细选择和提前规划。分片规则的选择需要考虑数据的增长方式、数据的访问方式、分片相关性问题、分片扩展问题。最近的分片策略是rangeSharding，enumerationsharding，consistentHashsharding，这几种sharding都是有利于扩展的尽量不要在一个事务中使用SQL跨越多个shards，分布式事务一直是一个比较难处理的问题查询条件尽量尝试优化，尽量避免Select*方式，大量数据结果集会消耗大量带宽和CPU资源，查询尽量避免返回大量结果集，并尽量为经常使用的查询语句建立索引。通过数据冗余和表分区依赖降低跨库join的可能性。这里特别强调分片规则的选择。如果表中的数据有明显的时间特征，比如订单、交易记录等，通常更适合。时间范围分片。由于数据具有时效性，我们往往会关注它最近的数据。查询条件通常使用时间字段进行过滤。更好的解决方案是对当前活动数据使用相对较短的时间跨度。分片，历史数据存储的跨度比较大。一般来说，分片的选择取决于最频繁查询SQL的条件，因为没有任何Where语句的查询SQL会遍历所有分片，性能相对最差，所以这样的SQL越多，影响越大对系统的影响越大，所以要尽量避免这条SQL的产生。解决方案由于水平拆分涉及的逻辑复杂，目前已经有很多成熟的解决方案。这些解决方案分为两类：客户端架构代理架构客户端架构通过修改数据访问层，通过配置管理多个数据源，如JDBC、DataSource、MyBatis，直接连接数据库，完成模块内部的数据传递分片整合，一般以Jar包的形式呈现。这是一个客户端架构的例子：你可以看到分片的实现是在应用服务器上。通过修改SpringJDBC层实现客户端架构的优点是：应用直接与数据库连接，减少依赖外围系统导致宕机的风险集成成本低，不需要额外运维的组件缺点是：仅限于在数据库访问层做文章，一般的可扩展性，对于更复杂的系统可能不足以将分片逻辑的压力施加到应用服务器上，造成额外的风险Dimensionproxycomponent这是一个代理架构的例子：proxycomponentsgenerally为了分流和防止单点，以集群的形式存在，可能需要Zookeeper等服务组件来管理代理架构。代理架构的优点是：可以处理非常复杂的需求，不受数据库访问层原有实现的限制，可扩展性对应用服务器是透明的，不增加任何额外的负载。缺点是：需要部署和维护独立的代理中间件，成本高。应用程序需要通过代理连接数据库，存在多次跳转、性能损失和附加风险。各方案对比打开看这么多方案，怎么选？按照以下几行考虑：决定是使用代理还是客户端架构。中小型或者比较简单的场景倾向于选择客户端架构，复杂场景或者大型系统倾向于选择代理架构。具体功能是否满足，比如跨节点ORDERBY，那么一年内优先考虑不支持该功能。较新的产品表明发展停滞，甚至无人维护和技术支持。最好按照大公司->社区->小公司->个人的顺序选择口碑较好的制作者，比如GitHubstars、用户数量、质量和用户反馈优先开源。通常项目有特殊需求，可能需要更改源代码。根据以上思路，推荐以下方案：客户端架构：ShardingJDBC代理架构：MyCat或Atlas兼容MySQL，目前有一些可水平扩展的数据库开源数据库兼容MySQL协议，如：https:///github.com/pingcap/tidbhttp://www.cubrid.org/，但其工业品质与MySQL相比还有很大差距，需要较大的运维投入。如果想将原来的MySQL迁移到新的可以横向扩展的数据库，可以考虑一些云数据库：https://cn.aliyun.com/product/petadata/?spm=5176.7960203.237031.38.cAzx5rhttps://cn.aliyun.com/product/oceanbase?spm=5176.7960203.237031.40.cAzx5rhttps://www.qcloud.com/product/dcdbfortdsql.htmlNoSQL在MySQL上做Sharding是一种戴着枷锁的舞蹈。事实上，很多大表本身并不适合MySQL等RDBMS。需求不大，不需要ACID。可以考虑将这些表迁移到NoSQL，彻底解决横向扩展的问题。例如：日志、监控、统计数据等非结构化或弱结构化数据，没有很强的事务需求，与操作关联的数据不多
