完美解决90%的性能毛病，数据库优化八大通用绝招！
毫不夸张的说咱们后端工程师，无论在哪家公司，呆在哪个团队，做哪个系统，遇到的第一个让人头疼的问题绝对是数据库性能问题。如果我们有一套成熟的方法论，能让大家快速、准确的去选择出合适的优化方案，我相信能够快速准备解决咱们日常遇到的80%甚至90%的性能问题。
从解决问题的角度出发，我们得先了解到**问题的原因；其次我们得有一套思考、判断问题的流程方式，**让我们合理的站在哪个层面选择方案；最后从众多的方案里面选择一个适合的方案进行解决问题，找到一个合适的方案的前提是我们自己对各种方案之间的优缺点、场景有足够的了解，没有一个方案是完全可以通吃通用的，软件工程没有银弹。
下文的我工作多年以来，曾经使用过的八大方案，结合了平常自己学习收集的一些资料，以系统、全面的方式整理成了这篇博文，也希望能让一些有需要的同行在工作上、成长上提供一定的帮助。
为什么数据库会慢？
慢的本质：
慢的本质
查找的时间复杂度
查找算法
存储数据结构
存储数据结构
数据总量
数据拆分
高负载
CPU、磁盘繁忙
无论是关系型数据库还是NoSQL，任何存储系统决定于其查询性能的主要有三种：
查找的时间复杂度数据总量高负载
而决定于查找时间复杂度主要有两个因素：
查找算法存储数据结构
无论是哪种存储，数据量越少，自然查询性能就越高，随着数据量增多，资源的消耗（CPU、磁盘读写繁忙）、耗时也会越来越高。
从关系型数据库角度出发，索引结构基本固定是B+Tree，时间复杂度是O(log n)，存储结构是行式存储。因此咱们对于关系数据库能优化的一般只有数据量。
而高负载造成原因有高并发请求、复杂查询等，导致CPU、磁盘繁忙等，而服务器资源不足则会导致慢查询等问题。该类型问题一般会选择集群、数据冗余的方式分担压力。
应该站在哪个层面思考优化？
从上图可见，自顶向下的一共有四层，分别是硬件、存储系统、存储结构、具体实现。层与层之间是紧密联系的，每一层的上层是该层的载体；因此越往顶层越能决定性能的上限，同时优化的成本也相对会比较高，性价比也随之越低。以最底层的具体实现为例，那么索引的优化的成本应该是最小的，可以说加了索引后无论是CPU消耗还是响应时间都是立竿见影降低；然而一个简单的语句，无论如何优化加索引也是有局限的，当在具体实现这层没有任何优化空间的时候就得往上一层【存储结构】思考，思考是否从物理表设计的层面出发优化（如分库分表、压缩数据量等)，如果是文档型数据库得思考下文档聚合的结果；如果在存储结构这层优化得没效果，得继续往再上一次进行考虑，是否关系型数据库应该不适合用在现在得业务场景？如果要换存储，那么得换怎样得NoSQL?
所以咱们优化的思路，出于性价比的优先考虑具体实现，实在没有优化空间了再往上一层考虑。当然如果公司有钱，直接使用钞能力，绕过了前面三层，这也是一种便捷的应急处理方式。
该篇文章不讨论顶与底的两个层面的优化，主要从存储结构、存储系统中间两层的角度出发进行探讨。
八大方案总结
数据库的优化方案核心本质有三种：减少数据量、用空间换性能、选择合适的存储系统，这也对应了开篇讲解的慢的三个原因：数据总量、高负载、*查找的时间复杂度。*
这里大概解释下收益类型：短期收益，处理成本低，能紧急应对，久了则会有技术债务；长期收益则跟短期收益相反，短期内处理成本高，但是效果能长久使用，扩展性会更好。
静态数据意思是，相对改动频率比较低的，也无需过多联表的，where过滤比较少。动态数据与之相反，更新频率高，通过动态条件筛选过滤。
减少数据量
减少数据量类型共有四种方案：数据序列化存储、数据归档、中间表生成、分库分表。
就如上面所说的，无论是哪种存储，数据量越少，自然查询性能就越高，随着数据量增多，资源的消耗（CPU、磁盘读写繁忙）、耗时也会越来越高。目前市面上的NoSQL基本上都支持分片存储，所以其天然分布式写的能力从数据量上能得到非常的解决方案。而关系型数据库，查找算法与存储结构是可以优化的空间比较少，因此咱们一般思考出发点只有从如何减少数据量的这个角度进行选择优化，因此本类型的优化方案主要针对关系型数据库进行处理。
数据归档
注意点：别一次性迁移数量过多，建议低频率多次限量迁移。像MySQL由于删除数据后是不会释放空间的，可以执行命令OPTIMIZE TABLE释放存储空间，但是会锁表，如果存储空间还满足，可以不执行。
关注公众号：码猿技术专栏，回复关键词：1111 获取阿里内部的java性能调优手册
建议优先考虑该方案，主要通过数据库作业把非热点数据迁移到历史表，如果需要查历史数据，可新增业务入口路由到对应的历史表（库）。
中间表（结果表）
中间表（结果表）其实就是利用调度任务把复杂查询的结果跑出来存储到一张额外的物理表，因为这张物理表存放的是通过跑批汇总后的数据，因此可以理解成根据原有的业务进行了高度的数据压缩。以报表为例，如果一个月的源数据有数十万，我们通过调度任务以月的维度生成，那么等于把原有的数据压缩了几十万分之一；接下来的季报和年报可以根据月报*N来进行统计，以这种方式处理的数据，就算三年、五年甚至十年数据量都可以在接受范围之内，而且可以精确计算得到。
那么数据的压缩比率是否越低越好？下面有一段口诀：
字段越多，粒度越细，灵活性越高，可以以中间表进行不同业务联表处理。字段越少，粒度越粗，灵活性越低，一般作为结果表查询出来。数据序列化存储
在数据库以序列化存储的方式，对于一些不需要结构化存储的业务来说是一种很好减少数据量的方式，特别是对于一些M*N的数据量的业务场景，如果以M作为主表优化，那么就可以把数据量维持最多是M的量级。另外像订单的地址信息，这种业务一般是不需要根据里面的字段检索出来，也比较适合。
这种方案我认为属于一种临时性的优化方案，无论是从序列化后丢失了部份字段的查询能力，还是这方案的可优化性都是有限的。
分库分表
分库分表作为数据库优化的一种非常经典的优化方案，特别是在以前NoSQL还不是很成熟的年代，这个方案就如救命草一般的存在。
如今也有不少同行也会选择这种优化方式，但是从我角度来看，分库分表是一种优化成本很大的方案。这里我有几个建议：
分库分表是实在没有办法的办法，应放到最后选择。优先选择NoSQL代替，因为NoSQL诞生基本上为了扩展性与高性能。究竟分库还是分表？量大则分表，并发高则分库不考虑扩容，一部做到位。因为技术更新太快了，每3-5年一大变。拆分方式
只要涉及到这个拆，那么无论是微服务也好，分库分表也好，拆分的方式主要分两种：垂直拆分、水平拆分。
垂直拆分更多是从业务角度进行拆分，主要是为了**降低业务耦合度；**此外以SQL Server为例，一页是8KB存储，如果在一张表里字段越多，一行数据自然占的空间就越大，那么一页数据所存储的行数就自然越少，那么每次查询所需要IO则越高因此性能自然也越慢；因此反之，减少字段也能很好提高性能。之前我听说某些同行的表有80个字段，几百万的数据就开始慢了。
水平拆分更多是从技术角度进行拆分，拆分后每张表的结构是一模一样的，简而言之就是把原有一张表的数据，通过技术手段进行分片到多张表存储，从根本上解决了数据量的问题。
路由方式
进行水平拆分后，根据分区键（sharding key）原来应该在同一张表的数据拆解写到不同的物理表里，那么查询也得根据分区键进行定位到对应的物理表从而把数据给查询出来。
路由方式一般有三种区间范围、Hash、分片映射表，每种路由方式都有自己的优点和缺点，可以根据对应的业务场景进行选择。
区间范围根据某个元素的区间的进行拆分，以时间为例子，假如有个业务我们希望以月为单位拆分那么表就会拆分像 table_2022-04，这种对于文档型、ElasticSearch这类型的NoSQL也适用，无论是定位查询，还是日后清理维护都是非常的方便的。那么缺点也明显，会因为业务独特性导致数据不平均，甚至不同区间范围之间的数据量差异很大。
Hash也是一种常用的路由方式，根据Hash算法取模以数据量均匀分别存储在物理表里，缺点是对于带分区键的查询依赖特别强，如果不带分区键就无法定位到具体的物理表导致相关所有表都查询一次，而且在分库的情况下对于Join、聚合计算、分页等一些RDBMS的特性功能还无法使用。
一般分区键就一个，假如有时候业务场景得用不是分区键的字段进行查询，那么难道就必须得全部扫描一遍？其实可以使用分片映射表的方式，简单来说就是额外有一张表记录额外字段与分区键的映射关系。举个例子，有张订单表，原本是以UserID作为分区键拆分的，现在希望用OrderID进行查询，那么得有额外得一张物理表记录了OrderID与UserID的映射关系。因此得先查询一次映射表拿到分区键，再根据分区键的值路由到对应的物理表查询出来。可能有些朋友会问，那这映射表是否多一个映射关系就多一张表，还是多个映射关系在同一张表。我优先建议单独处理，如果说映射表字段过多，那跟不进行水平拆分时的状态其实就是一致的，这又跑回去的老问题。
用空间换性能
该类型的两个方案都是用来应对高负载的场景，方案有以下两种：分布式缓存、一主多从。
与其说这个方案叫用空间换性能，我认为用空间换资源更加贴切一些。因此两个方案的本质主要通数据冗余、集群等方式分担负载压力。
对于关系型数据库而言，因为他的ACID特性让它天生不支持写的分布式存储，但是它依然天然的支持分布式读。
分布式缓存
缓存层级可以分好几种：客户端缓存、API服务本地缓存和分布式缓存，咱们这次只聊分布式缓存。一般我们选择分布式缓存系统都会优先选择NoSQL的键值型数据库，例如Memcached、Redis，如今Redis的数据结构多样性，高性能，易扩展性也逐渐占据了分布式缓存的主导地位。
缓存策略也主要有很多种：Cache-Aside、Read/Wirte-Through、Write-Back，咱们用得比较多的方式主要**Cache-Aside，**具体流程可看下图：
我相信大家对分布式缓存相对都比较熟悉了，但是我在这里还是有几个注意点希望提醒一下大家：
关注公众号：码猿技术专栏，回复关键词：1111 获取阿里内部的java性能调优手册避免滥用缓存
缓存应该是按需使用，从28法则来看，80%的性能问题由主要的20%的功能引起。滥用缓存的后果会导致维护成本增大，而且有一些数据一致性的问题也不好定位。特别像一些动态条件的查询或者分页，key的组装是多样化的，量大又不好用keys指令去处理，当然我们可以用额外的一个key把记录数据的key以集合方式存储，删除时候做两次查询，先查Key的集合，然后再遍历Key集合把对应的内容删除。这一顿操作下来无疑是非常废功夫的，谁弄谁知道。
避免缓存击穿
当缓存没有数据，就得跑去数据库查询出来，这就是缓存穿透。假如某个时间临界点数据是空的例如周排行榜，穿透过去的无论查找多少次数据库仍然是空，而且该查询消耗CPU相对比较高，并发一进来因为缺少了缓存层的对高并发的应对，这个时候就会因为并发导致数据库资源消耗过高，这就是缓存击穿。数据库资源消耗过高就会导致其他查询超时等问题。
该问题的解决方案也简单，对于查询到数据库的空结果也缓存起来，但是给一个相对快过期的时间。有些同行可能又会问，这样不就会造成了数据不一致了么？一般有数据同步的方案像分布式缓存、后续会说的一主多从、CQRS，只要存在数据同步这几个字，那就意味着会存在数据一致性的问题，因此如果使用上述方案，对应的业务场景应允许容忍一定的数据不一致。
不是所有慢查询都适用
一般来说，慢的查询都意味着比较吃资源的（CPU、磁盘I/O）。举个例子，假如某个查询功能需要3秒时间，串行查询的时候并没什么问题，我们继续假设这功能每秒大概QPS为100，那么在第一次查询结果返回之前，接下来的所有查询都应该穿透到数据库，也就意味着这几秒时间有300个请求到数据库，如果这个时候数据库CPU达到了100%，那么接下来的所有查询都会超时，也就是无法有第一个查询结果缓存起来，从而还是形成了缓存击穿。
一主多从
常用的分担数据库压力还有一种常用做法，就是读写分离、一主多从。咱们都是知道关系型数据库天生是不具备分布式分片存储的，也就是不支持分布式写，但是它天然的支持分布式读。一主多从是部署多台从库只读实例，通过冗余主库的数据来分担读请求的压力，路由算法可有代码实现或者中间件解决，具体可以根据团队的运维能力与代码组件支持视情况选择。
一主多从在还没找到根治方案前是一个非常好的应急解决方案，特别是在现在云服务的年代，扩展从库是一件非常方便的事情，而且一般情况只需要运维或者DBA解决就行，无需开发人员接入。当然这方案也有缺点，因为数据无法分片，所以主从的数据量完全冗余过去，也会导致高的硬件成本。从库也有其上限，从库过多了会主库的多线程同步数据的压力。
选择合适的存储系统
NoSQL主要以下五种类型：键值型、文档型、列型、图型、搜素引擎，不同的存储系统直接决定了查找算法、存储数据结构，也应对了需要解决的不同的业务场景。NoSQL的出现也解决了关系型数据库之前面临的难题（性能、高并发、扩展性等）。
例如，ElasticSearch的查找算法是倒排索引，可以用来代替关系型数据库的低性能、高消耗的Like搜索（全表扫描）。而Redis的Hash结构决定了时间复杂度为O(1)，还有它的内存存储，结合分片集群存储方式以至于可以支撑数十万QPS。
因此本类型的方案主要有两种：**CQRS、替换（选择）存储，**这两种方案的最终本质基本是一样的主要使用合适存储来弥补关系型数据库的缺点，只不过切换过渡的方式会有点不一样。
CQRS
CQS（命令查询分离）指同一个对象中作为查询或者命令的方法，每个方法或者返回的状态，要么改变状态，但不能两者兼备
讲解CQRS前得了解CQS，有些小伙伴看了估计还没不是很清晰，我这里用通俗的话解释：某个对象的数据访问的方法里，要么只是查询，要么只是写入（更新）。而CQRS（命令查询职责分离）基于CQS的基础上，用物理数据库来写入（更新），而用另外的存储系统来查询数据。因此我们在某些业务场景进行存储架构设计时，可以通过关系型数据库的ACID特性进行数据的更新与写入，用NoSQL的高性能与扩展性进行数据的查询处理，这样的好处就是关系型数据库和NoSQL的优点都可以兼得，同时对于某些业务不适于一刀切的替换存储的也可以有一个平滑的过渡。
从代码实现角度来看，不同的存储系统只是调用对应的接口API，因此CQRS的难点主要在于如何进行数据同步。
数据同步方式
一般讨论到数据同步的方式主要是分推和拉：
推指的是由数据变更端通过直接或者间接的方式把数据变更的记录发送到接收端，从而进行数据的一致性处理，这种主动的方式优点是实时性高。
拉指的是接收端定时的轮询数据库检查是否有数据需要进行同步，这种被动的方式从实现角度来看比推简单，因为推是需要数据变更端支持变更日志的推送的。
而推的方式又分两种：CDC（变更数据捕获）和领域事件。对于一些旧的项目来说，某些业务的数据入口非常多，无法完整清晰的梳理清楚，这个时候CDC就是一种非常好的方式，只要从最底层数据库层面把变更记录取到就可。
对于已经服务化的项目来说领域事件是一种比较舒服的方式，因为CDC是需要数据库额外开启功能或者部署额外的中间件，而领域事件则不需要，从代码可读性来看会更高，也比较开发人员的维护思维模式。
替换（选择）存储系统
因为从本质来看该模式与CQRS的核心本质是一样的，主要是要对NoSQL的优缺点有一个全面认识，这样才能在对应业务场景选择与判断出一个合适的存储系统。这里我像大家介绍一本书马丁.福勒《NoSQL精粹》，这本书我重复看了好几遍，也很好全面介绍各种NoSQL优缺点和使用场景。
当然替换存储的时候，我这里也有个建议：加入一个中间版本，该版本做好数据同步与业务开关，数据同步要保证全量与增加的处理，随时可以重来，业务开关主要是为了后续版本的更新做的一个临时型的功能，主要避免后续版本更新不顺利或者因为版本更新时导致的数据不一致的情况出现。在跑了一段时间后，验证了两个不同的存储系统数据是一致的后，接下来就可以把数据访问层的底层调用替换了。如此一来就可以平滑的更新切换。
MySQL常见的优化手段分为下面几个方面：
SQL优化、设计优化，硬件优化等，其中每个大的方向中又包含多个小的优化点
下面我们具体来看看
SQL优化
此优化方案指的是通过优化 SQL 语句以及索引来提高 MySQL 数据库的运行效率，具体内容如下：
分页优化
例如：
优化方案：
延迟关联先通过where条件提取出主键，在将该表与原数据表关联，通过主键id提取数据行，而不是通过原来的二级索引提取数据行例如：书签方式书签方式说白了就是找到limit第一个参数对应的主键值，再根据这个主键值再去过滤并limit例如： 索引优化
正确使用索引
假如我们没有添加索引，那么在查询时就会触发全表扫描，因此查询的数据就会很多，并且查询效率会很低，为了提高查询的性能，我们就需要给最常使用的查询字段上，添加相应的索引，这样才能提高查询的性能
建立覆盖索引
InnoDB使用辅助索引查询数据时会回表，但是如果索引的叶节点中已经包含要查询的字段，那它没有必要再回表查询了，这就叫覆盖索引
例如对于如下查询：
我们将被查询的字段建立到联合索引中，这样查询结果就可以直接从索引中获取
在 MySQL 5.0 之前的版本尽量避免使用or查询
在 MySQL 5.0 之前的版本要尽量避免使用 or 查询，可以使用 union 或者子查询来替代，因为早期的 MySQL 版本使用 or 查询可能会导致索引失效，在 MySQL 5.0 之后的版本中引入了索引合并
索引合并简单来说就是把多条件查询，比如or或and查询对多个索引分别进行条件扫描，然后将它们各自的结果进行合并，因此就不会导致索引失效的问题了
如果从Explain执行计划的type列的值是index_merge可以看出MySQL使用索引合并的方式来执行对表的查询
关于Explain的使用可以参考我之前的文章：最完整的Explain总结，SQL优化不再困难
避免在 where 查询条件中使用 != 或者 <> 操作符
SQL中，不等于操作符会导致查询引擎放弃索引索引，引起全表扫描，即使比较的字段上有索引
解决方法：通过把不等于操作符改成or，可以使用索引，避免全表扫描
例如，把column<>’aaa’，改成column>’aaa’ or column<’aaa’，就可以使用索引了
适当使用前缀索引
MySQL 是支持前缀索引的，也就是说我们可以定义字符串的一部分来作为索引
我们知道索引越长占用的磁盘空间就越大，那么在相同数据页中能放下的索引值也就越少，这就意味着搜索索引需要的查询时间也就越长，进而查询的效率就会降低，所以我们可以适当的选择使用前缀索引，以减少空间的占用和提高查询效率
比如，邮箱的后缀都是固定的“@xxx.com”，那么类似这种后面几位为固定值的字段就非常适合定义为前缀索引
使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本
需要注意的是，前缀索引也存在缺点，MySQL无法利用前缀索引做order by和group by 操作，也无法作为覆盖索引
查询具体的字段而非全部字段
要尽量避免使用select *，而是查询需要的字段，这样可以提升速度，以及减少网络传输的带宽压力
优化子查询
尽量使用 Join 语句来替代子查询，因为子查询是嵌套查询，而嵌套查询会新创建一张临时表，而临时表的创建与销毁会占用一定的系统资源以及花费一定的时间，同时对于返回结果集比较大的子查询，其对查询性能的影响更大
关于Join语句使用，可以参考我之前的文章：写出好的Join语句，前提你得懂这些
小表驱动大表
我们要尽量使用小表驱动大表的方式进行查询，也就是如果 B 表的数据小于 A 表的数据，那执行的顺序就是先查 B 表再查 A 表，具体查询语句如下：
不要在列上进行运算操作
不要在列字段上进行算术运算或其他表达式运算，否则可能会导致查询引擎无法正确使用索引，从而影响了查询的效率
一个很容易踩的坑：隐式类型转换：
skuId这个字段上有索引，但是explain的结果却显示这条语句会全表扫描
原因在于skuId的字符类型是varchar(32)，比较值却是整型，故需要做类型转换
适当增加冗余字段
增加冗余字段可以减少大量的连表查询，因为多张表的连表查询性能很低，所有可以适当的增加冗余字段，以减少多张表的关联查询，这是以空间换时间的优化策略
正确使用联合索引
使用了 B+ 树的 MySQL 数据库引擎，比如 InnoDB 引擎，在每次查询复合字段时是从左往右匹配数据的，因此在创建联合索引的时候需要注意索引创建的顺序
例如，我们创建了一个联合索引是idx(name,age,sex)，那么当我们使用，姓名+年龄+性别、姓名+年龄、姓名等这种最左前缀查询条件时，就会触发联合索引进行查询；然而如果非最左匹配的查询条件，例如，性别+姓名这种查询条件就不会触发联合索引
Join优化
MySQL的join语句连接表使用的是nested-loop join算法，这个过程类似于嵌套循环，简单来说，就是遍历驱动表（外层表），每读出一行数据，取出连接字段到被驱动表（内层表）里查找满足条件的行，组成结果行
要提升join语句的性能，就要尽可能减少嵌套循环的循环次数
一个显著优化方式是对被驱动表的join字段建立索引，利用索引能快速匹配到对应的行，避免与内层表每一行记录做比较，极大地减少总循环次数。另一个优化点，就是连接时用小结果集驱动大结果集，在索引优化的基础上能进一步减少嵌套循环的次数
如果难以判断哪个是大表，哪个是小表，可以用inner join连接，MySQL会自动选择小表去驱动大表
关于Join语句使用，可以参考我之前的文章：写出好的Join语句，前提你得懂这些
避免使用JOIN关联太多的表
对于 MySQL 来说，是存在关联缓存的，缓存的大小可以由join_buffer_size参数进行设置
在 MySQL 中，对于同一个 SQL 多关联（join）一个表，就会多分配一个关联缓存，如果在一个 SQL 中关联的表越多，所占用的内存也就越大
如果程序中大量的使用了多表关联的操作，同时join_buffer_size设置的也不合理的情况下，就容易造成服务器内存溢出的情况，就会影响到服务器数据库性能的稳定性
排序优化
利用索引扫描做排序
MySQL有两种方式生成有序结果：其一是对结果集进行排序的操作，其二是按照索引顺序扫描得出的结果自然是有序的
但是如果索引不能覆盖查询所需列，就不得不每扫描一条记录回表查询一次，这个读操作是随机IO，通常会比顺序全表扫描还慢
因此，在设计索引时，尽可能使用同一个索引既满足排序又用于查找行
例如：
只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向都一样时，才能够使用索引来对结果做排序
UNION优化
MySQL处理union的策略是先创建临时表，然后将各个查询结果填充到临时表中最后再来做查询，很多优化策略在union查询中都会失效，因为它无法利用索引
最好手工将where、limit等子句下推到union的各个子查询中，以便优化器可以充分利用这些条件进行优化
此外，除非确实需要服务器去重，一定要使用union all，如果不加all关键字，MySQL会给临时表加上distinct选项，这会导致对整个临时表做唯一性检查，代价很高
慢查询日志
出现慢查询通常的排查手段是先使用慢查询日志功能，查询出比较慢的 SQL 语句，然后再通过 Explain 来查询 SQL 语句的执行计划，最后分析并定位出问题的根源，再进行处理
慢查询日志指的是在 MySQL 中可以通过配置来开启慢查询日志的记录功能，超过long_query_time值的 SQL 将会被记录在日志中
我们可以通过设置“slow_query_log=1”来开启慢查询
需要注意的是，在开启慢日志功能之后，会对 MySQL 的性能造成一定的影响，因此在生产环境中要慎用此功能
设计优化
尽量避免使用NULL
NULL在MySQL中不好处理，存储需要额外空间，运算也需要特殊的运算符，含有NULL的列很难进行查询优化
应当指定列为not null，用0、空串或其他特殊的值代替空值，比如定义为int not null default 0
最小数据长度
越小的数据类型长度通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快
使用最简单数据类型
简单的数据类型操作代价更低，比如：能使用 int 类型就不要使用 varchar 类型，因为 int 类型比 varchar 类型的查询效率更高
尽量少定义 text 类型
text 类型的查询效率很低，如果必须要使用 text 定义字段，可以把此字段分离成子表，需要查询此字段时使用联合查询，这样可以提高主表的查询效率
适当分表、分库策略
分表是指当一张表中的字段更多时，可以尝试将一张大表拆分为多张子表，把使用比较高频的主信息放入主表中，其他的放入子表，这样我们大部分查询只需要查询字段更少的主表就可以完成了，从而有效的提高了查询的效率
分库是指将一个数据库分为多个数据库。比如我们把一个数据库拆分为了多个数据库，一个主数据库用于写入和修改数据，其他的用于同步主数据并提供给客户端查询，这样就把一个库的读和写的压力，分摊给了多个库，从而提高了数据库整体的运行效率
常见类型选择
整数类型宽度设置
MySQL可以为整数类型指定宽度，例如int(11)，实际上并没有意义，它并不会限制值的范围，对于存储和计算来说，int(1)和int(20)是相同的
VARCHAR和CHAR类型
char类型是定长的，而varchar存储可变字符串，比定长更省空间，但是varchar需要额外1或2个字节记录字符串长度，更新时也容易产生碎片
需要结合使用场景来选择：如果字符串列最大长度比平均长度大很多，或者列的更新很少，选择varchar较合适；如果要存很短的字符串，或者字符串值长度都相同，比如MD5值，或者列数据经常变更，选择使用char类型
DATETIME和TIMESTAMP类型
datetime的范围更大，能表示从1001到9999年，timestamp只能表示从1970年到2038年。datetime与时区无关，timestamp显示值依赖于时区。在大多数场景下，这两种类型都能良好地工作，但是建议使用timestamp，因为datetime占用8个字节，timestamp只占用了4个字节，timestamp空间效率更高
BLOB和TEXT类型
blob和text都是为存储很大数据而设计的字符串数据类型，分别采用二进制和字符方式存储
在实际使用中，要慎用这两种类型，它们的查询效率很低，如果字段必须要使用这两种类型，可以把此字段分离成子表，需要查询此字段时使用联合查询，这样可以提高主表的查询效率
范式化
当数据较好范式化时，修改的数据更少，而且范式化的表通常要小，可以有更多的数据缓存在内存中，所以执行操作会更快
缺点则是查询时需要更多的关联
第一范式：字段不可分割，数据库默认支持
第二范式：消除对主键的部分依赖，可以在表中加上一个与业务逻辑无关的字段作为主键，比如用自增id
第三范式：消除对主键的传递依赖，可以将表拆分，减少数据冗余
硬件优化
MySQL 对硬件的要求主要体现在三个方面：磁盘、网络和内存
磁盘
磁盘应该尽量使用有高性能读写能力的磁盘，比如固态硬盘，这样就可以减少 I/O 运行的时间，从而提高了 MySQL 整体的运行效率
磁盘也可以尽量使用多个小磁盘而不是一个大磁盘，因为磁盘的转速是固定的，有多个小磁盘就相当于拥有多个并行运行的磁盘一样
网络
保证网络带宽的通畅（低延迟）以及够大的网络带宽是 MySQL 正常运行的基本条件，如果条件允许的话也可以设置多个网卡，以提高网络高峰期 MySQL 服务器的运行效率
内存
MySQL 服务器的内存越大，那么存储和缓存的信息也就越多，而内存的性能是非常高的，从而提高了整个 MySQL 的运行效率
SQL优化13连问
1.日常工作中，你是怎么优化SQL的？
大家可以从这几个维度回答这个问题：
分析慢查询日志使用explain查看执行计划索引优化深分页优化避免全表扫描避免返回不必要的数据（如select具体字段而不是select*）使用合适的数据类型（如可以使用int类型的话，就不要设计为varchar）优化sql结构（如join优化等等）适当分批量进行 (如批量更新、删除)定期清理无用的数据适当分库分表读写分离2. 是否遇到过深分页问题，如何解决
我们可以通过减少回表次数来优化。一般有标签记录法和延迟关联法。
标签记录法
就是标记一下上次查询到哪一条了，下次再来查的时候，从该条开始往下扫描。就好像看书一样，上次看到哪里了，你就折叠一下或者夹个书签，下次来看的时候，直接就翻到啦。
假设上一次记录到100000，则SQL可以修改为：
这样的话，后面无论翻多少页，性能都会不错的，因为命中了id索引。但是这种方式有局限性：需要一种类似连续自增的字段。
延迟关联法
延迟关联法，就是把条件转移到主键索引树，然后减少回表。假设原生SQL是这样的的，其中id是主键，create_time是普通索引
使用延迟关联法优化，如下：
优化思路就是，先通过idx_create_time二级索引树查询到满足条件的主键ID，再与原表通过主键ID内连接，这样后面直接走了主键索引了，同时也减少了回表。
3. 聊聊explain执行计划
当explain与SQL一起使用时，MySQL将显示来自优化器的有关语句执行计划的信息。即MySQL解释了它将如何处理该语句，包括有关如何连接表以及以何种顺序连接表等信息。
一条简单SQL，使用了explain的效果如下：
一般来说，我们需要重点关注type、rows、filtered、extra、key。
3.1 type
type表示连接类型，查看索引执行情况的一个重要指标。以下性能从好到坏依次：system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
system：这种类型要求数据库表中只有一条数据，是const类型的一个特例，一般情况下是不会出现的。const：通过一次索引就能找到数据，一般用于主键或唯一索引作为条件，这类扫描效率极高，，速度非常快。eq_ref：常用于主键或唯一索引扫描，一般指使用主键的关联查询ref : 常用于非主键和唯一索引扫描。ref_or_null：这种连接类型类似于ref，区别在于MySQL会额外搜索包含NULL值的行index_merge：使用了索引合并优化方法，查询使用了两个以上的索引。unique_subquery：类似于eq_ref，条件用了in子查询index_subquery：区别于unique_subquery，用于非唯一索引，可以返回重复值。range：常用于范围查询，比如：between ... and 或 In 等操作index：全索引扫描ALL：全表扫描3.2 rows
该列表示MySQL估算要找到我们所需的记录，需要读取的行数。对于InnoDB表，此数字是估计值，并非一定是个准确值。
3.3 filtered
该列是一个百分比的值，表里符合条件的记录数的百分比。简单点说，这个字段表示存储引擎返回的数据在经过过滤后，剩下满足条件的记录数量的比例。
3.4 extra
该字段包含有关MySQL如何解析查询的其他信息，它一般会出现这几个值：
Using filesort：表示按文件排序，一般是在指定的排序和索引排序不一致的情况才会出现。一般见于order by语句Using index ：表示是否用了覆盖索引。Using temporary: 表示是否使用了临时表,性能特别差，需要重点优化。一般多见于group by语句，或者union语句。Using where : 表示使用了where条件过滤.Using index condition：MySQL5.6之后新增的索引下推。在存储引擎层进行数据过滤，而不是在服务层过滤，利用索引现有的数据减少回表的数据。3.5 key
该列表示实际用到的索引。一般配合possible_keys列一起看。
注意:有时候，explain配合show WARNINGS; （可以查看优化后,最终执行的sql），效果更佳哦。
4.说说大表的优化方案
数据库设计优化
合理的数据库设计可以极大地提高查询效率。我们在设计大表时，可以考虑拆分表、使用分区表、添加索引等方式来优化表结构。同时也要避免使用大量冗余字段、避免频繁使用join查询等操作。
索引优化
对于大表的查询操作，索引优化是非常重要的一环。可以考虑增加或者修改索引、使用覆盖索引、使用联合索引等方式来提高查询效率。同时也要注意定期清理冗余的索引以及对于经常使用的查询语句建立索引。
分区优化
将大表按照某个列分成多个分区表，每个分区表的数据量较小，可以提高查询和更新的性能。分区表还可以帮助在维护表结构的同时，减少锁表时间，提高并发处理能力。
数据清理归档
对于一些历史数据或者无用数据，可以进行定期归档，避免数据过多造成SQL查询效率降低。同时也要注意对于大表进行定期的数据备份以及紧急数据恢复的准备工作。
缓存优化
对于一些经常被查询的数据，可以使用缓存优化。使用Redis等缓存中间件来缓存常用的数据，以减少查询数据库的次数，提高查询效率。
SQL语句优化
在编写SQL查询语句时，要尽可能地简单明了，避免复杂的查询语句，同时也要避免一些不必要的查询操作。对于复杂的查询语句，可以使用Explain执行计划来进行优化。同时也要注意避免使用OR等耗费性能的操作符。
分库分表
如果数据量千万级别，需要考虑分库分表哈。分库分表相关知识点，可以看我之前这篇文章哈，我们为什么要分库分表？
5.哪些因素可能导致MySQL慢查询？
慢查询一般有以下这些原因：
大家有兴趣可以看下。我之前写的这篇文章哈：盘点MySQL慢查询的12个原因
6.如何使用索引优化SQL查询？
添加合适索引（在where、group by、order by等后面的字段添加合适索引）选择合适的索引类型 (B-tree索引适合范围查询、哈希索引适合等值查询)注意不适合加索引的场景（数据量少的表，更新频繁的字段，区分度低的字段）加索引的时候，需要考虑覆盖索引，减少回表，考虑联合索引的最左前缀原则explain查看SQL的执行计划，确认是否会命中索引。注意索引并不是越多越好，通常建议在单个表中不要超过5个索引。因为索引会占用磁盘空间，索引更新代价高。结束
本文到这里就把八大方案介绍完了，在这里再次提醒一句，每个方案都有属于它的应对场景，咱们只能根据业务场景选择对应的解决方案，没有通吃，没有银弹。
这八个方案里，大部分都存在数据同步的情况，只要存在数据同步，无论是一主多从、分布式缓存、CQRS都好，都会有数据一致性的问题导致，因此这些方案更多适合一些只读的业务场景。当然有些写后既查的场景，可以通过过渡页或者广告页通过用户点击关闭切换页面的方式来缓解数据不一致性的情况。