当MySQL单表记录数过大时，增删改查性能都会急剧下降，可以参考以下步骤来优化：

除非单表数据未来会一直不断上涨，否则不要一开始就考虑拆分，拆分会带来逻辑、部署、运维的各种复杂度，一般以整型值为主的表在千万级以下，字符串为主的表在五百万以下是没有太大问题的。而事实上很多时候MySQL单表的性能依然有不少优化空间，甚至能正常支撑千万级以上的数据量：

• VARCHAR的长度只分配真正需要的空间

• 使用枚举或整数代替字符串类型

• 单表不要有太多字段，建议在20以内

• 避免使用NULL字段，很难查询优化且占用额外索引空间

• 索引并不是越多越好，要根据查询有针对性的创建，考虑在WHERE和ORDER BY命令上涉及的列建立索引，可根据EXPLAIN来查看是否用了索引还是全表扫描

• 应尽量避免在WHERE子句中对字段进行NULL值判断，否则将导致引擎放弃使用索引而进行全表扫描

• 值分布很稀少的字段不适合建索引，例如"性别"这种只有两三个值的字段

• 字符字段最好不要做主键

• 不用外键，由程序保证约束

• 尽量不用UNIQUE，由程序保证约束

• 使用多列索引时主意顺序和查询条件保持一致，同时删除不必要的单列索引

• 可通过开启慢查询日志来找出较慢的SQL

• 不做列运算：SELECT id WHERE age + 1 = 10，任何对列的操作都将导致表扫描，它包括数据库教程函数、计算表达式等等，查询时要尽可能将操作移至等号右边

• sql语句尽可能简单：一条sql只能在一个cpu运算；大语句拆小语句，减少锁时间；一条大sql可以堵死整个库

• OR改写成IN：OR的效率是n级别，IN的效率是log(n)级别，in的个数建议控制在200以内

• 不用函数和触发器，在应用程序实现

• 使用同类型进行比较，比如用'123'和'123'比，123和123比

• 尽量避免在WHERE子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描

• 列表数据不要拿全表，要使用LIMIT来分页，每页数量也不要太大

目前广泛使用的是MyISAM和InnoDB两种引擎：

MyISAM引擎是MySQL 5.1及之前版本的默认引擎，它的特点是：

• 不支持行锁，读取时对需要读到的所有表加锁，写入时则对表加排它锁

• 不支持崩溃后的安全恢复

• 在表有读取查询的同时，支持往表中插入新纪录

• 支持BLOB和TEXT的前500个字符索引，支持全文索引

• 支持延迟更新索引，极大提升写入性能

• 对于不会进行修改的表，支持压缩表，极大减少磁盘空间占用

• 支持行锁，采用MVCC来支持高并发

可以使用下面几个工具来做基准测试：

• ：一个模块化，跨平台以及多线程的性能测试工具

具体的调优参数内容较多，具体可参考官方文档，这里介绍一些比较重要的参数：

• back_log：back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。也就是说，如果MySql的连接数据达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。可以从默认的50升至500

• wait_timeout：数据库连接闲置时间，闲置连接会占用内存资源。可以从默认的8小时减到半小时

• skip_name_resolve：禁止对外部连接进行DNS解析，消除DNS解析时间，但需要所有远程主机用IP访问

• innodb_additional_mem_pool_size：InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小，当数据库对象非常多的时候，适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率，当过小的时候，MySQL会记录Warning信息到数据库的错误日志中，这时就需要该调整这个参数大小

• query_cache_size：缓存MySQL中的ResultSet，也就是一条SQL语句执行的结果集，所以仅仅只能针对select语句。当某个表的数据有任何任何变化，都会导致所有引用了该表的select语句在Query Cache中的缓存数据失效。所以，当我们的数据变化非常频繁的情况下，使用Query

• read_buffer_size：MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分配一段内存缓冲区。如果对表的顺序扫描请求非常频繁，可以通过增加该变量值以及内存缓冲区大小提高其性能

• sort_buffer_size：MySql执行排序使用的缓冲大小。如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段。如果不能，可以尝试增加sort_buffer_size变量的大小

• read_rnd_buffer_size：MySql的随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySql会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。

• record_buffer：每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，可能想要增加该值

• thread_cache_size：保存当前没有与连接关联但是准备为后面新的连接服务的线程，可以快速响应连接的线程请求而无需创建新的

Scale up，这个不多说了，根据MySQL是CPU密集型还是I/O密集型，通过提升CPU和内存、使用SSD，都能显著提升MySQL性能

也是目前常用的优化，从库读主库写，一般不要采用双主或多主引入很多复杂性，尽量采用文中的其他方案来提高性能。同时目前很多拆分的解决方案同时也兼顾考虑了读写分离

缓存可以发生在这些层次：

• MySQL内部：在系统调优参数介绍了相关设置

• 数据访问层：比如MyBatis针对SQL语句做缓存，而Hibernate可以精确到单个记录，这里缓存的对象主要是持久化对象Persistence Object

• 应用服务层：这里可以通过编程手段对缓存做到更精准的控制和更多的实现策略，这里缓存的对象是数据传输对象Data Transfer Object

• Web层：针对web页面做缓存

• 浏览器客户端：用户端的缓存

可以根据实际情况在一个层次或多个层次结合加入缓存。这里重点介绍下服务层的缓存实现，目前主要有两种方式：

• 直写式（Write Through）：在数据写入数据库后，同时更新缓存，维持数据库与缓存的一致性。这也是当前大多数应用缓存框架如Spring Cache的工作方式。这种实现非常简单，同步好，但效率一般。

• 回写式（Write Back）：当有数据要写入数据库时，只会更新缓存，然后异步批量的将缓存数据同步到数据库上。这种实现比较复杂，需要较多的应用逻辑，同时可能会产生数据库与缓存的不同步，但效率非常高。

MySQL在5.1版引入的分区是一种简单的水平拆分，用户需要在建表的时候加上分区参数，对应用是透明的无需修改代码

对用户来说，分区表是一个独立的逻辑表，但是底层由多个物理子表组成，实现分区的代码实际上是通过对一组底层表的对象封装，但对SQL层来说是一个完全封装底层的黑盒子。MySQL实现分区的方式也意味着索引也是按照分区的子表定义，没有全局索引

用户的SQL语句是需要针对分区表做优化，SQL条件中要带上分区条件的列，从而使查询定位到少量的分区上，否则就会扫描全部分区，可以通过EXPLAIN PARTITIONS来查看某条SQL语句会落在那些分区上，从而进行SQL优化，如下图5条记录落在两个分区上：

• 可以让单表存储更多的数据

• 分区表的数据更容易维护，可以通过清楚整个分区批量删除大量数据，也可以增加新的分区来支持新插入的数据。另外，还可以对一个独立分区进行优化、检查、修复等操作

• 部分查询能够从查询条件确定只落在少数分区上，速度会很快

• 分区表的数据还可以分布在不同的物理设备上，从而搞笑利用多个硬件设备

• 可以使用分区表赖避免某些特殊瓶颈，例如InnoDB单个索引的互斥访问、ext3文件系统的inode锁竞争

• 可以备份和恢复单个分区

• 一个表最多只能有1024个分区

• 如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来

• 分区表无法使用外键约束

• NULL值会使分区过滤无效

• 所有分区必须使用相同的存储引擎

• RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区

• LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择

• HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式

• KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值

• 最适合的场景数据的时间序列性比较强，则可以按时间来分区，如下所示：

查询时加上时间范围条件效率会非常高，同时对于不需要的历史数据能很容的批量删除。

• 如果数据有明显的热点，而且除了这部分数据，其他数据很少被访问到，那么可以将热点数据单独放在一个分区，让这个分区的数据能够有机会都缓存在内存中，查询时只访问一个很小的分区表，能够有效使用索引和缓存

另外MySQL有一种早期的简单的分区实现 - 合并表（merge table），限制较多且缺乏优化，不建议使用，应该用新的分区机制来替代

垂直分库是根据数据库里面的数据表的相关性进行拆分，比如：一个数据库里面既存在用户数据，又存在订单数据，那么垂直拆分可以把用户数据放到用户库、把订单数据放到订单库。垂直分表是对数据表进行垂直拆分的一种方式，常见的是把一个多字段的大表按常用字段和非常用字段进行拆分，每个表里面的数据记录数一般情况下是相同的，只是字段不一样，使用主键关联

• 可以使得行数据变小，一个数据块(Block)就能存放更多的数据，在查询时就会减少I/O次数(每次查询时读取的Block 就少)

• 可以达到最大化利用Cache的目的，具体在垂直拆分的时候可以将不常变的字段放一起，将经常改变的放一起

• 主键出现冗余，需要管理冗余列

• 会引起表连接JOIN操作（增加CPU开销）可以通过在业务服务器上进行join来减少数据库压力

• 依然存在单表数据量过大的问题（需要水平拆分）

水平拆分是通过某种策略将数据分片来存储，分库内分表和分库两部分，每片数据会分散到不同的MySQL表或库，达到分布式的效果，能够支持非常大的数据量。前面的表分区本质上也是一种特殊的库内分表

库内分表，仅仅是单纯的解决了单一表数据过大的问题，由于没有把表的数据分布到不同的机器上，因此对于减轻MySQL服务器的压力来说，并没有太大的作用，大家还是竞争同一个物理机上的IO、CPU、网络，这个就要通过分库来解决

前面垂直拆分的用户表如果进行水平拆分，结果是：

实际情况中往往会是垂直拆分和水平拆分的结合，即将Users_A_M和Users_N_Z再拆成Users和UserExtras，这样一共四张表

• 不存在单库大数据和高并发的性能瓶颈

• 提高了系统的稳定性和负载能力

• 分片事务一致性难以解决

• 跨节点Join性能差，逻辑复杂

• 数据多次扩展难度跟维护量极大

• 能不分就不分，参考单表优化

• 分片数量尽量少，分片尽量均匀分布在多个数据结点上，因为一个查询SQL跨分片越多，则总体性能越差，虽然要好于所有数据在一个分片的结果，只在必要的时候进行扩容，增加分片数量

• 分片规则需要慎重选择做好提前规划，分片规则的选择，需要考虑数据的增长模式，数据的访问模式，分片关联性问题，以及分片扩容问题，最近的分片策略为范围分片，枚举分片，一致性Hash分片，这几种分片都有利于扩容

• 尽量不要在一个事务中的SQL跨越多个分片，分布式事务一直是个不好处理的问题

• 查询条件尽量优化，尽量避免Select * 的方式，大量数据结果集下，会消耗大量带宽和CPU资源，查询尽量避免返回大量结果集，并且尽量为频繁使用的查询语句建立索引。

• 通过数据冗余和表分区赖降低跨库Join的可能

这里特别强调一下分片规则的选择问题，如果某个表的数据有明显的时间特征，比如订单、交易记录等，则他们通常比较合适用时间范围分片，因为具有时效性的数据，我们往往关注其近期的数据，查询条件中往往带有时间字段进行过滤，比较好的方案是，当前活跃的数据，采用跨度比较短的时间段进行分片，而历史性的数据，则采用比较长的跨度存储。

总体上来说，分片的选择是取决于最频繁的查询SQL的条件，因为不带任何Where语句的查询SQL，会遍历所有的分片，性能相对最差，因此这种SQL越多，对系统的影响越大，所以我们要尽量避免这种SQL的产生。

由于水平拆分牵涉的逻辑比较复杂，当前也有了不少比较成熟的解决方案。这些方案分为两大类：客户端架构和代理架构。

通过修改数据访问层，如JDBC、Data Source、MyBatis，通过配置来管理多个数据源，直连数据库，并在模块内完成数据的分片整合，一般以Jar包的方式呈现

这是一个客户端架构的例子：

可以看到分片的实现是和应用服务器在一起的，通过修改Spring JDBC层来实现

• 应用直连数据库，降低外围系统依赖所带来的宕机风险

• 集成成本低，无需额外运维的组件

• 限于只能在数据库访问层上做文章，扩展性一般，对于比较复杂的系统可能会力不从心

• 将分片逻辑的压力放在应用服务器上，造成额外风险

通过独立的中间件来统一管理所有数据源和数据分片整合，后端数据库集群对前端应用程序透明，需要独立部署和运维代理组件

这是一个代理架构的例子：

代理组件为了分流和防止单点，一般以集群形式存在，同时可能需要Zookeeper之类的服务组件来管理

• 能够处理非常复杂的需求，不受数据库访问层原来实现的限制，扩展性强

• 对于应用服务器透明且没有增加任何额外负载

• 需部署和运维独立的代理中间件，成本高

• 应用需经过代理来连接数据库，网络上多了一跳，性能有损失且有额外风险

如此多的方案，如何进行选择？可以按以下思路来考虑：

1. 确定是使用代理架构还是客户端架构。中小型规模或是比较简单的场景倾向于选择客户端架构，复杂场景或大规模系统倾向选择代理架构

2. 具体功能是否满足，比如需要跨节点ORDER BY，那么支持该功能的优先考虑

3. 不考虑一年内没有更新的产品，说明开发停滞，甚至无人维护和技术支持

4. 最好按大公司->社区->小公司->个人这样的出品方顺序来选择

5. 选择口碑较好的，比如github星数、使用者数量质量和使用者反馈

6. 开源的优先，往往项目有特殊需求可能需要改动源代码

按照上述思路，推荐以下选择：

兼容MySQL且可水平扩展的数据库

目前也有一些开源数据库兼容MySQL协议，如：

但其工业品质和MySQL尚有差距，且需要较大的运维投入，如果想将原始的MySQL迁移到可水平扩展的新数据库中，可以考虑一些云数据库：

在MySQL上做Sharding是一种戴着镣铐的跳舞，事实上很多大表本身对MySQL这种RDBMS的需求并不大，并不要求ACID，可以考虑将这些表迁移到NoSQL，彻底解决水平扩展问题，例如：

• 日志类、监控类、统计类数据

• 非结构化或弱结构化数据

• 对事务要求不强，且无太多关联操作的数据

绝大部分写业务的程序员，在实际开发中使用 Redis 的时候，只会 Set Value 和 Get Value 两个操作，对 Redis 整体缺乏一个认知。这里对 Redis 常见问题做一个总结，解决大家的知识盲点。

在项目中使用 Redis，主要考虑两个角度：性能和并发。如果只是为了分布式锁这些其他功能，还有其他中间件 Zookpeer 等代替，并非一定要使用 Redis。

如下图所示，我们在碰到需要执行耗时特别久，且结果不频繁变动的 SQL，就特别适合将运行结果放入缓存。这样，后面的请求就去缓存中读取，使得请求能够迅速响应。

特别是在秒杀系统，在同一时间，几乎所有人都在点，都在下单。。。执行的是同一操作———向数据库查数据。

根据交互效果的不同，响应时间没有固定标准。在理想状态下，我们的页面跳转需要在瞬间解决，对于页内操作则需要在刹那间解决。

如下图所示，在大并发的情况下，所有的请求直接访问数据库，数据库会出现连接异常。这个时候，就需要使用 Redis 做一个缓冲操作，让请求先访问到 Redis，而不是直接访问数据库。

• 缓存和数据库双写一致性问题

这个问题是对 Redis 内部机制的一个考察。很多人都不知道 Redis 是单线程工作模型。

• 单线程操作，避免了频繁的上下文切换

• 采用了非阻塞 I/O 多路复用机制

仔细说一说 I/O 多路复用机制，打一个比方：小名在 A 城开了一家快餐店店，负责同城快餐服务。小明因为资金限制，雇佣了一批配送员，然后小曲发现资金不够了，只够买一辆车送快递。加君羊：即可免费领取架构资料一份。

客户每下一份订单，小明就让一个配送员盯着，然后让人开车去送。慢慢的小曲就发现了这种经营方式存在下述问题：

• 时间都花在了抢车上了，大部分配送员都处在闲置状态，抢到车才能去送。

• 随着下单的增多，配送员也越来越多，小明发现快递店里越来越挤，没办法雇佣新的配送员了。

• 配送员之间的协调很花时间。

综合上述缺点，小明痛定思痛，提出了经营方式二。

小明只雇佣一个配送员。当客户下单，小明按送达地点标注好，依次放在一个地方。最后，让配送员依次开着车去送，送好了就回来拿下一个。上述两种经营方式对比，很明显第二种效率更高。

• 订单的送达地点→Socket 的不同状态

• 客户送餐请求→来自客户端的请求

• 明曲的经营方式→服务端运行的代码

• 一辆车→CPU 的核数

• 经营方式一就是传统的并发模型，每个 I/O 流(订单)都有一个新的线程(配送员)管理。

• 经营方式二就是 I/O 多路复用。只有单个线程(一个配送员)，通过跟踪每个 I/O 流的状态(每个配送员的送达地点)，来管理多个 I/O 流。

下面类比到真实的 Redis 线程模型，如图所示：

Redis-client 在操作的时候，会产生具有不同事件类型的 Socket。在服务端，有一段 I/O 多路复用程序，将其置入队列之中。然后，文件事件分派器，依次去队列中取，转发到不同的事件处理器中。

一个合格的程序员，这五种类型都会用到。

最常规的 set/get 操作，Value 可以是 String 也可以是数字。一般做一些复杂的计数功能的缓存。

这里 Value 存放的是结构化的对象，比较方便的就是操作其中的某个字段。我在做单点登录的时候，就是用这种数据结构存储用户信息，以 CookieId 作为 Key，设置 30 分钟为缓存过期时间，能很好的模拟出类似 Session 的效果。

使用 List 的数据结构，可以做简单的消息队列的功能。另外，可以利用 lrange 命令，做基于 Redis 的分页功能，性能极佳，用户体验好。

因为 Set 堆放的是一堆不重复值的集合。所以可以做全局去重的功能。我们的系统一般都是集群部署，使用 JVM 自带的 Set 比较麻烦。另外，就是利用交集、并集、差集等操作，可以计算共同喜好，全部的喜好，自己独有的喜好等功能。

Sorted Set 多了一个权重参数 Score，集合中的元素能够按 Score 进行排列。可以做排行榜应用，取 TOP N 操作。Sorted Set 可以用来做延时任务。

Redis 是否用到家，从这就能看出来。比如你 Redis 只能存 5G 数据，可是你写了 10G，那会删 5G 的数据。怎么删的，这个问题思考过么？

正解：Redis 采用的是定期删除+惰性删除策略。

为什么不用定时删除策略

定时删除，用一个定时器来负责监视 Key，过期则自动删除。虽然内存及时释放，但是十分消耗 CPU 资源。在大并发请求下，CPU 要将时间应用在处理请求，而不是删除 Key，因此没有采用这一策略。

定期删除+惰性删除如何工作

定期删除，Redis 默认每个 100ms 检查，有过期 Key 则删除。需要说明的是，Redis 不是每个 100ms 将所有的 Key 检查一次，而是随机抽取进行检查。如果只采用定期删除策略，会导致很多 Key 到时间没有删除。于是，惰性删除派上用场。

采用定期删除+惰性删除就没其他问题了么

不是的，如果定期删除没删除掉 Key。并且你也没及时去请求 Key，也就是说惰性删除也没生效。这样，Redis 的内存会越来越高。那么就应该采用内存淘汰机制。

甲乙两个互斥建设方案有关数据如下表，基准收益率为10%。

A．采用净现值法，甲方案净现值大于乙方案净现值，甲方案为优选方案

B．采用净现值率法，甲方案净现值率大于乙方案净现值率，甲方案为优选方案

C．采用内部收益率法，乙方案内部收益率大于甲方案内部收益率，乙方案为优选方案

D．采用净年值法，甲方案净年值大于乙方案净年值，甲方案为优选方案

请帮忙给出正确答案和分析，谢谢！