一、背景

在数据爆炸的年代，单表数据达到千万级别，甚至过亿的量，都是很常见的情景。这时候再对数据库进行操作就是非常吃力的事情了，select个半天都出不来数据，这时候业务已经难以维系。技术在进步虽然有了NoSQL、NewSQL数据库，但是它们都有各自的应用场景，在不适合的场景下还只能老老实实的使用关系型数据库。

1.1 数据库的不同类型

1.1.1 常用的关系型数据库

Oracle：功能强大，主要缺点就是贵。
MySQL：互联网行业中最流行的数据库，这不仅仅是因为MySQL的免费。可以说关系数据库场景中你需要的功能，MySQL都能很好的满足，后面详解部分会详细介绍MySQL的一些知识点。
MariaDB：是MySQL的分支，由开源社区维护，MariaDB虽然被看作MySQL的替代品，但它在扩展功能、存储引擎上都有非常好的改进。
PostgreSQL：也叫PGSQL，PGSQL类似于Oracle的多进程框架，可以支持高并发的应用场景，PG几乎支持所有的SQL标准，支持类型相当丰富。PG更加适合严格的企业应用场景，而MySQL更适合业务逻辑相对简单、数据可靠性要求较低的互联网场景。

1.1.2 NoSQL数据库（非关系型数据库）

Redis：提供了持久化能力，支持多种数据类型。Redis适用于数据变化快且数据大小可预测的场景。
MongoDB：一个基于分布式文件存储的数据库，将数据存储为一个文档，数据结构由键值对组成。MongoDB比较适合表结构不明确，且数据结构可能不断变化的场景，不适合有事务和复杂查询的场景。
HBase：建立在HDFS，也就是Hadoop文件系统之上的分布式面向列的数据库。类似于谷歌的大表设计，HBase可以提供快速随机访问海量结构化数据。在表中它由行排序，一个表有多个列族以及每一个列族可以有任意数量的列。HBase依赖HDFS可以实现海量数据的可靠存储，适用于数据量大，写多读少，不需要复杂查询的场景。
Cassandra：一个高可靠的大规模分布式存储系统。支持分布式的结构化Key-value存储，以高可用性为主要目标。适合写多的场景，适合做一些简单查询，不适合用来做数据分析统计。
Pika：一个可持久化的大容量类Redis存储服务，兼容五种主要数据结构的大部分命令。Pika使用磁盘存储，主要解决Redis大容量存储的成本问题。

1.1.3 NewSQL数据库（新一代关系型数据库）

TiDB：开源的分布式关系数据库，几乎完全兼容MySQL，能够支持水平弹性扩展、ACID事务、标准SQL、MySQL语法和MySQL协议，具有数据强一致的高可用特性。既适合在线事务处理，也适合在线分析处理。
OceanBase：OceanBase是蚂蚁金服的数据库，OB是可以满足金融级的可靠性和数据一致性要求的数据库系统。当你需要使用事务，并且数据量比较大，就比较适合使用OB。不过目前OB已经商业化，不再开源。

二、结构设计优化

2.1 表场设计

数据库主键设计，推荐带时间属性自增长数字ID（分布式自增长ID生成算法）如：雪花算法、百度分布式ID算法、美团分布式ID算法。
表字段不为空，因为空值很难查询和优化，占用额外的索引空间，建议默认数字0。
对于数据状态类型字段，比如status、type等，尽量不要定义负数，比如-1。因为可以加上unseigned，所以数值容量会翻倍。
如果可能，使用 tinyint，smallint 代替 int，并且尽量不要使用 bigint，因为它占用的空间更少。
字符串类型的字段会比数字类型的字段占用更多空间，所以尽量使用整数而不是字符串。在许多情况下，编码逻辑可以使用整数来代替。
字符串类型的长度不能随意设置，在满足业务需求的前提下尽量小。
使用整数来存储 IP。
一个表中不要有太多字段。建议20以内。
提前为可预测的字段预留，因为数据量越大，修改数据结构越耗时。

2.2 分割

分区是一种水平分区，它按照一定的规则将一个表分成几个更小、更容易管理的部分。对应用程序完全透明，不影响应用程序的业务逻辑，即无需修改代码。因此，它可以存储更多的数据，查询和删除，还支持分区操作，从而达到优化的目的。如果考虑分区，可以提前做好准备，避免以下限制：
一张表最多只能有1024个分区 mysql 5.6以后支持8192个分区）。但实际操作时，最好不要一次打开超过100个分区，因为打开分区也有时间损失。
如果分区字段中有主键或唯一索引列，则必须包括所有主键列和唯一索引列。如果表中有主键或唯一索引，则分区键必须是主键或唯一索引。
外键约束不能在分区表中使用。
空值会使分区过滤失效，所以会放入默认分区。请不要让分区字段显示为空。
所有分区必须使用相同的存储引擎。

2.3 子表

子表包括水平子表和垂直子表。

横向分表拆分成数据结构相同的小表，如table1、table2……以缓解数据库读写压力
垂直分表是指一些字段被分离出来形成一个新的表。每张表的数据结构不同，可以优化高低锁表的情况。

可以想象，在分表的情况下需要修改程序的逻辑。因此，在项目前期预见到大量数据时，一般会考虑分表。后期不建议分表，所以成本很高。

2.4 子库

分库一般是主从模式。将数据库服务器的主节点复制到一个或多个从节点和多个数据库。主库负责写操作，从库负责读操作，从而达到主从分离、高可用和数据备份的优化目的。

当然，主从模式也有一些缺陷，比如主从同步的延迟，binlog文件过大带来的问题等等。

2.4 其他

热表和冷表是隔离的。对于历史数据，当查询和使用的人数较少时，可以将其移至另一个冷库，仅用于查询，以缓解热量表数据量大的情况。

2.5 索引设计

索引，空间换时间的优化策略，基本根据业务需要设计好的索引，足以应付百万数据，养成使用explain的习惯，关于explain也可以访问：explain让你的SQL写得更多脚踏实地，了解更多。
一个常识：索引越多越好。索引会降低数据写入的性能。
索引字段的长度尽可能的短，这样可以节省大量的索引空间；
取消外键可以通过程序进行约束，具有更好的性能。
复合索引匹配的最左列规则，索引顺序和查询条件一致，尽可能去掉不必要的单列索引。
值分布较少（不重复较少）的字段不适合索引。例如，当只有性别等两个或三个值时，对字段进行索引是没有意义的。
建议对需要排序的字段添加索引，因为索引会进行排序，可以提高查询性能。
前缀索引用于字符串字段而不是全字段索引，可以大大减少索引空间。

三、模糊查询优化

我们经常在数据库中使用 LIKE 操作符来完成对数据的模糊搜索，LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。

如果需要查找客户表中所有姓氏是“马”的数据，可以使用下面的 SQL 语句：

SELECT * FROM Customer WHERE Name LIKE ‘马%’

如果需要查找客户表中所有手机尾号是“阳”的数据，可以使用下面的 SQL 语句：

SELECT * FROM Customer WHERE Phone LIKE ‘%阳’

如果需要查找客户表中所有名字中包含“东”的数据，可以使用下面的 SQL 语句：

SELECT * FROM Customer WHERE Name LIKE ‘%东%’

以上三种分别对应了：左前缀匹配、右后缀匹配和模糊查询，并且对应了不同的查询优化方式。

左前缀匹配查询优化：索引就可以

我们可以采用“以空间换时间”的方式来解决右后缀匹配查询时效率低下的问题。

简单来说，我们可以将字符串倒过来，让右后缀匹配变成左前缀匹配。以“防着古海回来再抓孙悟空”为例，将其倒置之后的字符串是“空悟孙抓再来回海古着防”。当需要查找结尾为“孙悟空”的数据时，去查找以“空悟孙”开头的数据即可。

具体做法是：在该表中增加“txt_back”列，将“txt”列的值倒置后，填入“txt_back”列中，最后为 “txt_back”列增加索引。

模糊查询，根据用户量和接口qps决定使用哪种优化方式，如果用户量级小，是面向B端的系统，可以采用mysql的feature进行查询优化，如果用户量级大，我们需要的就是一个全文检索引擎。

3.1 MySQL是如何优化模糊匹配like的SQL

3.1.1 全文索引

语法

1
2
3

alter table fulltext_test add fulltext index content_tag_fulltext(content,tag);
MySQL 中的全文索引，有两个变量，最小搜索长度和最大搜索长度，对于长度小于最小搜索长度和大于最大搜索长度的词语，都不会被索引。
从MySQL 5.7开始内置了ngram全文检索插件，用来支持中文分词，并且对MyISAM和InnoDB引擎有效。

mysql 官方文档对match策略的解释：https://dev.mysql.com/doc/internals/en/full-text-search.html

3.1.2 生成列

MySQL 5.7开始支持生成列，生成列是由表达式的值计算而来，有两种模式：VIRTUAL和STORED，如果不指定默认是VIRTUAL，创建语法如下

1	col_name data_type [GENERATED ALWAYS] AS (expr) [VIRTUAL \| STORED] [NOT NULL \| NULL]

可以解决右后缀匹配的问题，但是不能解决模糊查询的问题。也是将字符串倒过来。

3.1.3 索引条件下推ICP

MySQL 5.6开始支持ICP（Index Condition Pushdown），不支持ICP之前，当进行索引查询时，首先根据索引来查找数据，然后再根据where条件来过滤，扫描了大量不必要的数据，增加了数据库IO操作。在支持ICP后，MySQL在取出索引数据的同时，判断是否可以进行where条件过滤，将where的部分过滤操作放在存储引擎层提前过滤掉不必要的数据，减少了不必要数据被扫描带来的IO开销。在某些查询下，可以减少Server层对存储引擎层数据的读取，从而提供数据库的整体性能。

开启ICP特性后，由于like条件可以通过索引筛选，存储引擎层通过索引与where条件的比较来去除不符合条件的记录，这个过程不需要读取记录，同时只返回给Server层筛选后的记录，减少不必要的IO开销。

3.2 全文检索引擎

3.2.1 Sphinx

如果数据源存放在MySQL，可是使用：Sphinx 。

其实咱们KM早期就是使用Sphinx实现全文检索查询的，Sphinx可以非常容易的与SQL数据库和脚本语言集成。当前系统内置MySQL和PostgreSQL 数据库数据源的支持，也支持从标准输入读取特定格式的XML数据。通过修改源代码，用户可以自行增加新的数据源。现在KM使用的是搜搜的底层实现搜索；

Sphinx：原生支持基于MySQL的表建索引

ElasticSearch官方文档上，数据都是使用RESTful接口一条一条插入的，也就是增量更新。在数据量非常大的时候，遍历全表重建一次索引会非常消耗时间。而elasticsearch-rivel-mysql这个项目并不是很靠谱，开发者甚至曾经在git上标明deprecated（现在没了）。反正我是自己另外写了一套。

在导入MySQL数据生成索引时，从易用性、可靠性、速度上来看，Sphinx优于ElasticSearch。

资源占用，Sphinx优于ElasticSearch。不得不说，java在这方面比不上C++。CPU还好，差距不大，内存占用方面真心天差地别。

3.2.2 Elasticsearch

如果你是使用MongoDB，中文分词同样是个坑！业界通用方案是使用Elasticsearch 实现中文检索。大致的路数是：使用 mongo-connector 将数据同步到Elasticsearch中；

增量更新支持，ElasticSearch优于Sphinx。ElasticSearch把增量更新作为首选CURD方式；而Sphinx使用辅助表的方案不但不优雅，还会让你的其他系统变得复杂起来，在你频繁更改单条数据的时候很容易出错。

搜索算法支持，ElasthcSearch的搜索底层功能基于Lucene，Sphinx也该有的都有。然而ElasticSearch的Query DSL支持更复杂的查询逻辑，这一点是超越Sphinx的。

在自定义Ranker方面，ElasticSearch的Function Score Query比Sphinx的expression-ranker强大许多。

横向扩展与高可用，ElasticSearch是天生为了集群化而设计的。索引如果没有Replica就会显示黄灯，有才会亮绿灯。每个节点分为Client Node、Data Node、Master Node三种角色，在合理的配置之下，任意一台（甚至多台）机器炸了，整个集群都能正常运行。ElasticSearch还支持动态加机器等等功能，暂不赘述。

Sphinx也有master searchd和slave searchd的概念，可以分布式，但想实现高可用就相当复杂了。

ElasticSearch优于Sphinx。Sphinx的劣势不在于做不到，而在于不好用。

3.2.3 RediSearch

最后还有一个好东西RediSearch是一款基于redis的搜索组件。基于redis，性能高效，实时更新索引，支持Suggest前缀、拼音查找(AutoComplete 功能) ，支持单个或多个分词搜索，可根据字段进行结果排序。RediSearch的吞吐量高、延迟低，速度相比ElasticSearch 和 Solr要快120%到500%，欠缺中文的模糊搜索支持的不是很好。

主要特性

RediSearch 是在Redis基础上从0开始开发的一个全文搜索索引，使用新的Redis Modules API来扩展Redis新命令和能力，它的主要特性包括：

简单，快速索引和搜索
数据存储在内存中，使用内存-有效的自定义数据结构
支持多种使用UTF-8编码的语言
文档和字段评分
结果的数值过滤
通过词干扩展查询
精确的短语搜索
按特定属性过滤结果（例如仅在标题中搜索“foo”）
强大的自动提示引擎
增量索引（不需要对索引进行优化和压缩）
支持用作存储在另一数据库中的文档的搜索索引
支持已经在Redis中存在的HASH对象作为文件的索引
扩展到多个Redis实例

四、普通SQL查询优化

尝试使用短查询而不是复杂的内联查询。
查询不使用select *，尽量查询带索引的字段，避免返回表。
尝试使用limit来限制查询的数量。
查询字段应尽可能在索引上，尤其是复合索引。我们应该更加注意最左边的前缀匹配。
拆分大的删除/插入操作会锁定表，影响其他业务操作。另一方面，MySQL 对 SQL 的长度也有限制。
不建议使用MySQL函数、计算等，可以先由程序处理。从上面提到的一些点，我们会发现，程序可以处理的，尽量不要把压力转给数据库。因为大部分服务器的性能瓶颈都在数据库中。
查询计数，性能：count（1）=count（*）>count（主键）>count（其他字段）。
如果可以使用between，则不需要in。如果可以使用in，则不需要or。
避免使用=或<>、is null或is not null、in、not in等，因为这些查询不能使用索引。
SQL 应该尽可能简单，并使用更少的连接。不建议使用两个以上的连接。

多读书多实践，勤思考善领悟

MySQL性能优化