无标签

数据库 mysql

发布日期: 2022-08-05

文章字数: 11.4k

阅读时长: 40 分

预备知识

局部性原理

程序和数据的访问都有聚集成群的倾向，在一个时间段内，仅使用其中一小部分，称为空间局部性

最近访问过的程序代码和数据，很快又被访问的可能性很大，称为时间局部性

磁盘预读

预读的长度一般为页（page）的整数倍
页是存储器的逻辑块，操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页大小通常为4K），主存和磁盘以页为单位交换数据
考虑到磁盘 IO 是非常高昂的操作，计算机操作系统做了一些优化，当一次 IO 时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次 IO 读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为 4 k或 8 k，也就是我们读取一页内的数据时候，实际上才发生了一次 IO，这个理论对于索引的数据结构设计非常有帮助。

索引概述

MySql 官方对索引的定义为：索引是帮助 MySql 高效获取数据的数据结构。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
索引存储在文件系统中
索引的文件存储形式与存储引擎有关
索引文件的结构：B+树

索引的分类

通过给字段添加索引可以提高数据的读取速度，提高项目的并发能力和抗压能力

普通索引

基本的索引类型，值可以为空，没有唯一性的限制

-- 创建索引的第一种方式
CREATE INDEX idx_nickname ON account(nickname); 

-- 创建索引的第二种方式(不常用)
ALTER TABLE account ADD INDEX idx_nickname1(nickname);

-- 创建索引的第三种方式
CREATE TABLE u1(nickname VARCHAR(64),age TINYINT UNSIGNED,KEY idx_nickname(nickname));

主键索引

一种唯一性索引，但它必须指定为PRIMARY KEY，每个表只能有一个主键

主键索引的列的数据非空，唯一的

1 2	-- 声明为主键的列就是自动添加主键索引 CREATE TABLE u1(id INT, nickname VARCHAR(64), age TINYINT UNSIGNED, PRIMARY KEY(id));

唯一索引

索引列的值都只能出现一次，即值必须唯一，值可以为空
如果一列被约束为UNIQUE，则在这一列默认添加唯一索引

添加唯一索引的列可以为NULL值，这也是和主键索引，不同的地方

-- 创建索引的第一种方式
CREATE UNIQUE INDEX uk_nickname ON u1(nickname); 

-- 创建表时创建唯一索引
CREATE TABLE u1 (
  id INT,
  nickname VARCHAR (64) ,
  age TINYINT UNSIGNED,
  PRIMARY KEY (id),
  UNIQUE KEY uk_nickname(nickname) 
) ;

全文索引

全文索引，通过建立倒排索引，可以提高数据的检索效率，解决判断字段中 是否包含 的问题;

索引类型为FULLTEXT。全文索引在varchar、char、text类型的列上创建

-- 我们已经给nickname字段添加了普通索引
-- 会使用索引
SELECT * FROM account WHERE nickname='小明';
-- 会使用索引
SELECT * FROM account WHERE nickname LIKE '小明%';
-- 不会使用索引
SELECT * FROM account WHERE nickname LIKE '%小明%';

不使用到索引我们如果进行大规模数据检索时，效率会大大的降低，所以前面我们说过 我们只在简单业务或者数据量小的时候才考虑使用like关键字;

全文索引注意的地方:

mysql5.6以前,只有MYISAM存储引擎支持全文索引
在5.6中INNODB存储引擎加入了对全文索引的支持,但是只支持英文的全文索引，不支持中文的全文索引
在5.7.6中,mysql内置了ngram分词器，用来支持中文;

配置 ngram 分词的最小长度：

默认长度为2，当然我们也可以设置成1，但是设置成1的话就会浪费大量的空间，不是很好，mysql建议我们配置为2;

1
2
3

#ngram分词器对分词最小长度(也就是说分词器,分词的时候最小也是两个词一分)
[mysqld]
ft_min_word_len=2

创建全文索引

-- 创建索引的第一种方式
CREATE FULLTEXT INDEX ft_nickname ON account(nickname) WITH PARSER ngram; 

-- 创建索引的第二种方式(不常用)
ALTER TABLE account ADD FULLTEXT INDEX ft_nickname(nickname) WITH PARSER ngram;

-- 创建索引的第三种方式
CREATE TABLE u1(nickname VARCHAR(64),age TINYINT UNSIGNED,FULLTEXT KEY ft_nickname(nickname) WITH PARSER ngram);

使用全文索引

1	SELECT * FROM account WHERE MATCH(nickname) AGAINST("你觉得华为笔记本合小米手机哪个好");

match中的字段和创建全文建索引时的字段必须一致;

全文索引的检索流程

用户输入词—>sql执行引擎—>ngram分词器对用户输入的词进行分词(配置了最小的分词个数)—>把分词器分的词依次的去倒排索引中去查找,找出相应的记录返回;

组合索引

多列值组成一个索引，专门用于组合搜索

包含多个字段的索引称为组合索引;

组合索引包含

组合普通索引
组合主键索引
组合唯一索引
组合全文索引

1 2	-- 创建复合索引时必须指定索引的名称,不能省略 CREATE INDEX mu_title_content ON article(title,content,publish_time);

1
2
3

SELECT * FROM article WHERE title LIKE '小米%'; -- 使用到索引
SELECT * FROM article WHERE title LIKE '小米%' AND content LIKE '小米%'; -- 使用了索引
SELECT * FROM article WHERE  content LIKE '小米%'; -- 没有索引

建议多列索引的列不要超过2个列

以上这个复合索引相当于建立了这3个索引
1
(title),(title,content),(title,content,publish_time)
多列索引遵循最左前缀的原则
多列索引在创建的时候，如果其中有字段时TEXT或BLOB类型，就必须指定索引的长度;

MySQL存储引擎

	MyISAM	InnoDB
索引类型	非聚簇索引	聚簇索引
支持事务	否	是
支持表锁	是	是
支持行锁	否	是
支持外键	否	是
支持全文索引	是	是
适合操作类型	大量select	大量insert、delete、updata

索引优化-基本知识

原因：

索引在 MySQL 中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。

优点：

使用主键索引或者唯一索引，可以保证数据库中的表的数据是唯一
通过建立索引可以大大的提高数据检索的效率，减少表扫描的行数(避免进行全表扫描)
我们在进行多表连接的时候，可以使用索引加速表之间的连接

缺点：

在创建索引和维护索引时都需要耗费时间；
索引文件会占用物理存储空间，除了表的数据占用一部分空间，索引文件也会占用一部分空间；
设置为text和blob类型的字段强烈不建议添加索引

索引优化-联合索引

回表：

在 InnoDB 存储引擎下，二级索引查询到的索引列，如果需要查找所有列的数据，则需要到主键索引里面去取出数据。这个过程就称为回表。因为行的数据都是存在主键 B+tree 的叶子节点里面，二级索引的B+树叶子节点都是存放的(索引列,主键)。

覆盖索引：

select * from s1 where id=123;
该 sql命中了索引，但未覆盖索引。
利用 id=123 到索引的数据结构中定位到该 id 在硬盘中的位置，或者说在数据表中的位置。
但是我们 select 的字段为*，除了id以外还需要其他字段，这就意味着，我们通过索引结构取到id还不够，
还需要利用该id再去找到该id所在行的其他字段值，这是需要时间的，很明显，如果我们只select id，
就减去了这份苦恼，如下
select id from s1 where id=123;
这条就是覆盖索引了，命中索引，且从索引的数据结构直接就取到了id在硬盘的地址，速度很快

最左匹配：

create index ix_name_email on s1(name,email,)
- 最左前缀匹配：必须按照从左到右的顺序匹配
select * from s1 where name='egon'; #可以
select * from s1 where name='egon' and email='asdf'; #可以
select * from s1 where email='alex@oldboy.com'; #不可以
select * from s1 where email='asdf' and  name='egon'; #可以
mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，
比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，
d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

索引下推：

索引条件下推(Index Condition Pushdown)，简称 ICP。MySQL5.6新添加，用于优化数据的查询。
当你不使用 ICP，通过使用非主键索引（普通索引or二级索引）进行查询，存储引擎通过索引检索数据，然后返回给 MySQL 服务器，服务器再判断是否符合条件。
使用 ICP，当存在索引的列做为判断条件时，MySQL 服务器将这一部分判断条件传递给存储引擎，然后存储引擎通过判断索引是否符合 MySQL 服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给 MySQL 服务器。
示例
- 当我们创建一个用户表(userinfo)，其中有字段：id,name,age,addr。我们将 name,age 建立联合索引。
  1
  当我们执行：select * from userinfo where name like "ming%" and age=20;
- 对于MySQL5.6之前：我们在索引内部首先通过 name 进行查找，再联合索引 name,，age 树形查询结果可能存在多个，然后再拿着 id 值去回表查询，整个过程需要回表多次。
- 对于MySQL5.6之后：我们是在索引内部就判断 age 是否等于20，对于不等于 20 跳过。因此在联合索引 name,age 索引树只匹配一个记录，此时拿着这个 id 去主键索引树种回表查询全部数据，整个过程就回一次表。
- 如下：
  1
  当Extra值为：Using index condition.表示使用索引下推。
- 通过索引下推对于非主键索引进行优化，可有效减少回表次数，从而提高效率。
- 关闭索引下推命令
  1
  set optimizer_switch='index_condition_pushdown=off';

索引优化-聚簇索引与非聚簇索引

聚簇(集)索引的叶子节点就是数据节点。
非聚簇(集)索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针。非聚簇(集)索引在 innodb 引擎中，又叫做二级索引，辅助索引等。
聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。术语‘聚簇’表示数据行和相邻的键值聚簇的存储在一起。如下图，左侧的索引就是聚簇索引，因为数据行在磁盘的排列和索引排序保持一致。、

聚集索引（聚簇索引）

以 InnoDB 作为存储引擎的表，表中的数据都会有一个主键，即使你不创建主键，系统也会帮你创建一个隐式的主键。

这是因为 InnoDB 是把数据存放在 B+树中的，而 B+树的键值就是主键，在 B+树的叶子节点中，存储了表中所有的数据。

这种以主键作为 B+树索引的键值而构建的 B+树索引，我们称之为聚集索引。

非聚集索引（非聚簇索引）

以主键以外的列值作为键值构建的 B+树索引，我们称之为非聚集索引。

非聚集索引与聚集索引的区别在于

非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。

明白了聚集索引和非聚集索引的定义，我们应该明白这样一句话：数据即索引，索引即数据。

聚簇索引的好处：

按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的 io 操作。

聚簇索引的限制：

对于 mysql 数据库目前只有 innodb 数据引擎支持聚簇索引，而 Myisam 并不支持聚簇索引。由于数据物理存储排序方式只能有一种，所以每个 Mysql 的表只能有一个聚簇索引。一般情况下就是该表的主键。

为了充分利用聚簇索引的聚簇的特性，所以 innodb 表的主键列尽量选用有序的顺序 id，而不建议用无序的 id，比如uuid 这种。

索引失效

范围查询右边的列，不能使用索引

1	select * from t where name ='test' and status >'1' and address='北京市'

前面的两个字段name，status查询是走索引的，都是最后一个条件address没有用到索引

不要在索引列上进行运算操作，索引将失效

1	select * from t where substring(name,3,2)='科技'

字符串不加单引号，造成索引失效

1	select * from t where name ='test' and status =1

用or分隔的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及到的索引都不会被用到。

1	select * from t where name ='test' or createtime='2020-04-05 12:00:00'

name是索引列，createtime不是索引列，之间or进行连接，那么会导致name列也不走索引

以%开头的like模糊查询，索引失效

如果仅仅是尾部的模糊匹配，索引不会失效，如果是头部模糊匹配，索引失效，但是如果使用覆盖索引，那么索引仍然会生效

1	select name from t where name like '%test'

如果MySql评估使用索引比全表扫描更慢，则不使用索引
is null，is not null 有时索引失效
is null，如果数据库中，该字段为空值的记录数更多，那么MySql评估使用索引比全表扫描更慢，则不使用索引
is not null 如果数据库中，该字段不为空值的记录数更多，那么MySql评估使用索引比全表扫描更慢，则不使用索引
in 走索引，not in 索引失效
使用不等于(!=或者<>)的时候，索引失效，会导致全表扫描

1	select name from t where name != 'test'

MYSQL针对函数或存储过程中传递进的参数,如果是varchar类型时则默认会进行转换字符集校对规则与数据库保持一致，这个时候如果数据库编码和表编码不一致时（比如utf8和utf8mb4），就会出现索引失效的情况

最佳索引使用策略

对查询频次较高,且数据量比较大的表建立索引
索引字段的选择,最佳候选列应当从 where子句的条件中提取,如果where子句中的组合比较多,那么应当挑选最常用、过滤效果最好的列的组合
索引可以有效的提升查询数据的效率,但索引数昰不是多多益善,索引越多,维护索引的代价自然也就水涨船高。对于插入、更新、删除等DML操作比较频繁的表来说,索引过多,会引入相当高的维护代价,降低DM操作的效率,增加相应操作的时间消耗。另外索引过多的话, MySQL也会犯选择困难病,虽然最终仍然会找到一个可用的索引,但无疑提高了选择的代价
独立的列

优化细节

对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
1
select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
1
select id from t where num=0
应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
1
select id from t where num=10 or num=20
可以这样查询：
1
2
3
select id from t where num=10
union all
select id from t where num=20
下面的查询也将导致全表扫描：
1
select id from t where name like '%abc%'
若要提高效率，可以考虑全文检索。
in 和 not in 也要慎用，否则会导致全表扫描，如：
1
select id from t where num in(1,2,3)
对于连续的数值，能用 between 就不要用 in 了：
1
select id from t where num between 1 and 3
如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：
1
select id from t where num=@num
可以改为强制查询使用索引：
1
select id from t with(index(索引名)) where num=@num
应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
1
select id from t where num/2=100
应改为:
1
select id from t where num=100*2

应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

1 2	select id from t where substring(name,1,3)='abc'--name以abc开头的id select id from t where datediff(day,createdate,'2005-11-30')=0--'2005-11-30'生成的id

应改为:

1 2	select id from t where name like 'abc%' select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'

不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
不要写一些没有意义的查询，如需要生成一个空表结构：
1
select col1,col2 into #t from t where 1=0
这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：
1
create table #t(...)

很多时候用 exists 代替 in 是一个好的选择：

1	select num from a where num in(select num from b)

用下面的语句替换：

1	select num from a where exists(select 1 from b where num=a.num)

并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。
尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。
避免频繁创建和删除临时表，以减少系统表资源的消耗。
临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。
在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。
如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。
尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。
使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。
与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。
在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。
尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
尽量避免大事务操作，提高系统并发能力。

八大数据库性能优化方案

为什么数据库会慢？

无论是关系型数据库还是 NoSQL，任何存储系统决定于其查询性能的主要有三种：

查找的时间复杂度
数据总量
高负载

而决定于查找时间复杂度主要有两个因素：

查找算法
存储数据结构

无论是哪种存储，数据量越少，自然查询性能就越高，随着数据量增多，资源的消耗（CPU、磁盘读写繁忙）、耗时也会越来越高。

从关系型数据库角度出发，索引结构基本固定是 B+Tree，时间复杂度是 O(log n)，存储结构是行式存储。因此咱们对于关系数据库能优化的一般只有数据量。

而高负载造成原因有高并发请求、复杂查询等，导致 CPU、磁盘繁忙等，而服务器资源不足则会导致慢查询等问题。该类型问题一般会选择集群、数据冗余的方式分担压力。

应该站在哪个层面思考优化？

从上图可见，自顶向下的一共有四层，分别是硬件、存储系统、存储结构、具体实现。

层与层之间是紧密联系的，每一层的上层是该层的载体；因此越往顶层越能决定性能的上限，同时优化的成本也相对会比较高，性价比也随之越低。

以最底层的具体实现为例，那么索引的优化的成本应该是最小的，可以说加了索引后无论是 CPU 消耗还是响应时间都是立竿见影降低。

然而一个简单的语句，无论如何优化加索引也是有局限的，当在具体实现这层没有任何优化空间的时候就得往上一层【存储结构】思考，思考是否从物理表设计的层面出发优化（如分库分表、压缩数据量等），如果是文档型数据库得思考下文档聚合的结果。

如果在存储结构这层优化得没效果，得继续往再上一次进行考虑，是否关系型数据库应该不适合用在现在得业务场景？如果要换存储，那么得换怎样得 NoSQL?

所以咱们优化的思路，出于性价比的优先考虑具体实现，实在没有优化空间了再往上一层考虑。当然如果公司有钱，直接使用钞能力，绕过了前面三层，这也是一种便捷的应急处理方式。

不讨论顶与底的两个层面的优化，主要从存储结构、存储系统中间两层的角度出发进行探讨。

八大方案总结

数据库的优化方案核心本质有三种：减少数据量、用空间换性能、选择合适的存储系统，这也对应了开篇讲解的慢的三个原因：数据总量、高负载、查找的时间复杂度。

收益类型：短期收益，处理成本低，能紧急应对，久了则会有技术债务；长期收益则跟短期收益相反，短期内处理成本高，但是效果能长久使用，扩展性会更好。

静态数据意思是，相对改动频率比较低的，也无需过多联表的，where 过滤比较少。动态数据与之相反，更新频率高，通过动态条件筛选过滤。

减少数据量

减少数据量类型共有四种方案：数据序列化存储、数据归档、中间表生成、分库分表。

就如上面所说的，无论是哪种存储，数据量越少，自然查询性能就越高，随着数据量增多，资源的消耗（CPU、磁盘读写繁忙）、耗时也会越来越高。

主要针对关系型数据库进行处理。

数据归档

注意点：别一次性迁移数量过多，建议低频率多次限量迁移。像 MySQL 由于删除数据后是不会释放空间的，可以执行命令 OPTIMIZE TABLE 释放存储空间，但是会锁表，如果存储空间还满足，可以不执行。

建议优先考虑该方案，主要通过数据库作业把非热点数据迁移到历史表，如果需要查历史数据，可新增业务入口路由到对应的历史表（库）。

在数据库以序列化存储的方式，对于一些不需要结构化存储的业务来说是一种很好减少数据量的方式，特别是对于一些 M*N 的数据量的业务场景，如果以 M 作为主表优化，那么就可以把数据量维持最多是 M 的量级。

另外像订单的地址信息，这种业务一般是不需要根据里面的字段检索出来，也比较适合。

这种方案我认为属于一种临时性的优化方案，无论是从序列化后丢失了部份字段的查询能力，还是这方案的可优化性都是有限的。

中间表（结果表）

中间表（结果表）其实就是利用调度任务把复杂查询的结果跑出来存储到一张额外的物理表，因为这张物理表存放的是通过跑批汇总后的数据，因此可以理解成根据原有的业务进行了高度的数据压缩。

以报表为例，如果一个月的源数据有数十万，我们通过调度任务以月的维度生成，那么等于把原有的数据压缩了几十万分之一。

接下来的季报和年报可以根据月报*N 来进行统计，以这种方式处理的数据，就算三年、五年甚至十年数据量都可以在接受范围之内，而且可以精确计算得到。那么数据的压缩比率是否越低越好？

下面有一段口诀：

字段越多，粒度越细，灵活性越高，可以以中间表进行不同业务联表处理。
字段越少，粒度越粗，灵活性越低，一般作为结果表查询出来。

数据序列化存储

分库分表

分库分表作为数据库优化的一种非常经典的优化方案，特别是在以前 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。

具体流程可看下图：

注意：

避免滥用缓存
缓存应该是按需使用，从 28 法则来看，80% 的性能问题由主要的 20% 的功能引起。滥用缓存的后果会导致维护成本增大，而且有一些数据一致性的问题也不好定位。

特别像一些动态条件的查询或者分页，key 的组装是多样化的，量大又不好用 keys 指令去处理，当然我们可以用额外的一个 key 把记录数据的 key 以集合方式存储，删除时候做两次查询，先查 Key 的集合，然后再遍历 Key 集合把对应的内容删除。
避免缓存击穿

当缓存没有数据，就得跑去数据库查询出来，这就是缓存穿透。

假如某个时间临界点数据是空的例如周排行榜，穿透过去的无论查找多少次数据库仍然是空，而且该查询消耗 CPU 相对比较高，并发一进来因为缺少了缓存层的对高并发的应对，这个时候就会因为并发导致数据库资源消耗过高，这就是缓存击穿。数据库资源消耗过高就会导致其他查询超时等问题。

该问题的解决方案也简单，对于查询到数据库的空结果也缓存起来，但是给一个相对快过期的时间。有些同行可能又会问，这样不就会造成了数据不一致了么？

一般有数据同步的方案像分布式缓存、后续会说的一主多从、CQRS，只要存在数据同步这几个字，那就意味着会存在数据一致性的问题，因此如果使用上述方案，对应的业务场景应允许容忍一定的数据不一致。
不是所有慢查询都适用

一般来说，慢的查询都意味着比较吃资源的（CPU、磁盘 I/O）。

举个例子，假如某个查询功能需要 3 秒时间，串行查询的时候并没什么问题，我们继续假设这功能每秒大概 QPS 为 100，那么在第一次查询结果返回之前，接下来的所有查询都应该穿透到数据库。

也就意味着这几秒时间有 300 个请求到数据库，如果这个时候数据库 CPU 达到了 100%，那么接下来的所有查询都会超时，也就是无法有第一个查询结果缓存起来，从而还是形成了缓存击穿。