亿级数据如何设计数据库 explan各个参数及优化 mysql主从复制原理 innodb结构 一般有用 看3 速

电商亿级数据库设计-原理实

战

1. 电商

数据库架构体系剖析

1.1 背景概述

 

随着互联网普及率的不断提高，中国电商稳步发展，市场零售额

不断增长。根据数据显示，2019年，全国网上零售额106324亿

元，比上年增长16.5%。与此同时，得益于社交电商的流量效

应，电商用户持续增加。数据显示，截至2019年底，中国移动电

商用户规模预计将突破7亿人。

而对于电商系统来说, 具有以下几个特点: 高并发、分布式、高可

用、负载均衡、业务复杂、系统安全、海量数据 。对于一个中大

型的电商系统来说， 每天都会产生大量的订单、用户、日志等数

据，长年累月，将是非常庞大的数据量，而对于这些数据的存

储、处理、搜索是一个非常棘手的问题。

1.2 难题分析

1). 大数据量的存储

单台服务器的存储能力及数据处理能力都是有限的, 因此需要增加

服务器, 搭建集群来存储海量数据。

 

 

2). 读写性能瓶颈

单台数据库服务器的数据存储和数据处理能力都是有限的， 而大

多数互联网业务，往往读多写少，而互联网特别是中大型的电商

系统，业务都是非常繁忙的， 这个时候最容易出现的就是读性能

瓶颈。

3). 扩容

问题

电商系统的数据量是每天/每月以非常大的数据量在增长的，那么

对于我们的数据库服务器的存储能力，也是一个巨大的考验。也

随着时间的推移，原有的集群中的机器不能够存储这么多的数据

量时，这个时候我们就需要考虑扩容。

1.3 架构设计

在一个系统中,我们设计数据库架构,需要考虑以下几个原则:可用

性、 读性能、 一致性、 拓展性。

1.3.1 可用性设计

A. 单点

 

 

在最原始的架构中，是单一数据库，一旦数据库宕机之后，整个

服务都不可用，不存在高可用。

B. 主从复制

解决高可

用的思路，就是冗余、复制；

在这种主从的架构中，即使Master节点挂掉，还有Slave节点，

整个数据库的数据依赖存在，但是在这种架构中，无法保证读、

写的高可用，而且会存在一致性问题（后面讲解）；

C. "读"高可用

为保证读的高可用，可以对读（从）库进行冗余，但是冗余读

库，也会存在副作用： 读写有延时，可能存在不一致。

 

 

 

 

 

在上图中确实是保证了"读"高可用，但是写节点依然是单点，不

能保证写高可用。

D. "写"高可用

保证"写"高可用，就可以在上述架构的基础上,再冗余写库，采用

双主双从模式。而在这种架构下，两个主库，都会执行写请求，而且互相同步。

那么可能会存在以下问题： 双主同步，主键如果是自增的，会存

在冲突？ 解决方案：

a. 两个写库

使

用

不

同

的

初

始

值

，

相

同

的步长来增加id ; 1写库的id

为0,2,4,6...

；2

写

库

的

id

为

1,

3,

5,

7…

。

b. 不适用

数据库的自增ID，业务层机子生成一个唯一的ID。

1.3.2 读性能设计

在数据库中，我们为了提高读的性能，最常用的做法就是建立索

引，但是如果索引过多，又会存在其副作用：

a. 降低了增删改性能；

b. 索引占内存多了，放在内存中的数据减少，数据命中率降低，

IO次数增多；

A. 不同库建立不同的索引

 

 

 

 

 

主库只提供写操作， 不建立索引；

从库提供读操作，在从库上建立适当的索引 ；

B. 增加从库，负载均衡

这种做法上面已经提到，会存在主从不一致的问题，从库数量越

多，主从延时越长，不一致问题越严重。

C. 缓存

①. 发生写请求时，先淘汰缓存，再写数据库

②. 发生读请求时，先读缓存，缓存命中则直接返回，没有命中，

则查询数据库，并将查询的结果缓存在redis中（而此时旧数据可

能入缓存）。

1.3.3 一致性设计

A. 引入中间件

 

 

 

 

 

通过中间件将key写操作路由到主， 在一定时间范围内，该key上

的读也路由到主，当主从同步完成后再将读操作路由到从。

B. 读写都到主

读写都到主，不做读写分离，也就不存在主从不一致的情况。

C. 缓存两次淘汰异常的读写时序，或导致旧数据入缓存，一次淘汰不够，要进行

二次淘汰

a. 发生写请求时，先淘汰缓存，再写数据库，额外增加一个

timer，一定时间（主从同步完成的经验时间）后再次淘汰

b. 发生读请求时，先读缓存，hit则返回，miss则读数据库并将数

据入缓存（此时可能旧数据入缓存，但会被二次淘汰淘汰掉，最

终不会引发不一致）

1.3.4 拓展性设计

在上述的架构中，针对于单库的可用性、读性能、一致性进行了

分析，在电商系统的数据库中，数据量是特别大的，而单台服务

器的容量、性能都是有限的，如果来完成扩容，则我们需要考虑

到拓展性的设计。

A. 垂直拆分

根据业务划分，将不同的数据库表切分到不同的数据库上，以实

现扩容的目的；

 

 

B. 水平拆分

将同一块业务的数据库表，进行拆分，将一张表的数据根据一定

的规则（取模，hash等）切分到不同的数据库上。

而上述的架构中是没有考虑高可用的，当其中的任何一个节点挂

掉之后，数据就不完整了，考虑高可用的架构如下：

 

 

 

 

 

C. 平滑、高效扩容

随着业务系统的扩张，数据库中的数据量会不断增加，如果实现

扩容，最为直接了当的办法就是直接增加服务器，从而实现更多

数据的存储；

如果按照以下的思路扩容，会存在大量的数据迁移。

如何来完成高效、平滑的扩容呢， 可以按照以下架构进行。2. SQL语句性能提升

2.1 环境准备

2.1.1 MySQL安装

1). 卸载 centos 中预安装的 mysql

rpm -qa | grep -i mysql

rpm -e mysql-libs-5.1.71-1.el6.x86_64 --nodeps

2). 上传 mysql 的安装包

alt + p -------> put E:/test/mysql-5.7.25-

1.el6.x86_64.rpm-bundle.tar

3). 解压 mysql 的安装包

mkdir mysql

1

2

3

4

5

6

7

8

9

10

11

12

13

 

 

2.1.2 启动MySQL服务

tar -xvf mysql-5.7.25-1.el6.x86_64.rpm-bundle.tar -

C /root/mysql

4). 安

装依赖包

y

um -y install libaio.so.1 libgcc_s.so.1 libstdc++.so.6

lib

ncurses.so.5 --setopt=protected_multilib=false

yum update libstdc++-4.4.7-4.el6.x86_64

5). 安装 mysql-client

rpm -ivh mysql-community-common-5.7.25-

1.el6.x86_64.rpm

rpm -ivh mysql-community-libs-5.7.25-

1.el6.x86_64.rpm

rpm -ivh mysql-community-client-5.7.25-

1.el6.x86_64.rpm

6). 安装 mysql-server

rpm -ivh mysql-community-server-5.7.25-

1.el6.x86_64.rpm

 

 

 

2.1.3 登录MySQL

service mysqld start

service mysqld stop

service

mysqld status

serv

ice mysqld restart

1

2

3

4

5

6

7

mysql 安装完成之后, 会自动生成一个随机的密码, 记录在

输出的日志文件中， 日志文件为： /var/log/mysqld.log

cat /var/log/mysqld.log |grep password --color

mysql -u root -p

登录之后, 修改密码 :

set password = password('itcast');

授权远程访问 :

grant all privileges on *.* to 'root' @'%' identifified by

'itcast';

flflush privileges;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

 

 

2.1.4 数据准备

1). 准备tb_sku表, 导入数据 - 数据1000w

2). 准备tb_seller表, 导入数据 - 数据12条

2.2 慢查询分析

2.2.1 show profifiles

show profifiles 是mysql提供可以用来分析当前会话中语句执行的

资源消耗情况。可以用于SQL的调优测量,show profifiles 能够在

做SQL优化时帮助我们了解时间都耗费到哪里去了。

通过 have_profifiling 参数，能够看到当前MySQL是否支持

profifile：

默认profifiling是关闭的，可以通过set语句在Session级别开启

profifiling：

1 set profifiling=1; //开启profifiling 开关；

 

 

通过profifile，我们能够更清楚地了解SQL执行的过程。

首先，我们可以执行一系列的操作，如下图所示：

执行完上述命令之后，再执行show profifiles 指令， 来查看SQL

语句执行的耗时：

 

通过show profifile for query query_id 语句可以查看到该SQL执

行过程中每个线程的状态和消耗的时间：

show databases;

use db01;

sho

w tables;

select * from tb_ksu where id < 5;

select count(*) from tb_ksu;

1

2

3

4

5

6

7

8

9

 

 

字段

含义

Status

sql 语句执行的状态

Duration

sql 执行过程中每一个步骤的耗时

CPU_user

当前用户占有的cpu

CPU_system

系统占有的cpu

在获取到

最消耗时间的线程状态后，MySQL支持进一步选择all、

cpu、block io 、context switch、page faults等明细类型类查

看MySQL在使用什么资源上耗费了过高的时间。例如，选择查看

CPU的耗费时间 ：

TIP ：

Sending data 状态表示MySQL线程开始访问数据行并把

结果返回给客户端，而不仅仅是返回个客户端。由于在

Sending data状态下，MySQL线程往往需要做大量的磁盘

读取操

作，所以经常是整各查询中耗时最长的状态。

1

2

 

 

2.2.2 慢查询日志

慢查询日志记录了所有执行时间超过参数 long_query_time 设置

值并且扫描记录数不小于 min_examined_row_limit 的所有的

SQL语句的日志。long_query_time 默认为 10 秒，最小为 0，

精度可以到微

秒。

2.2.2.1

文件位置和格式

慢查询日志默认是关闭的 。可以通过两个参数来控制慢查询日志

：

2.2.2.2 日志的读取

慢查询日志记录的格式也是纯文本，可以被直接读取。

1） 查询long_query_time 的值。

# 该参数用来控制慢查询日志是否开启， 可取值： 1 和 0 ，

1 代表开启， 0 代表关闭

slow_query_log=1

# 该参数用来指定慢查询日志的文件名

slow_query_log_fifile=slow_query.log

# 该选项用来配置查询的时间限制， 超过这个时间将认为是

慢查询， 将进行日志记录， 默认10s

long_query_time=10

1

2

3

4

5

6

7

8

9

 

 

2） 执行查询操作

1 select * from tb_sku where id = '100000030074'\G;

 

 

 

由于该语句执行时间很短，为0s ， 所以不会记录在慢查询日志

中。

1 select * from tb_sku where name like '%HuaWei手机

Meta87384 Pro%'\G;

该SQL语句 ， 执行时长为 24.28s ，超过10s ， 所以会记录在慢

查询日志文件中。3） 查看慢查询日志文件

直接通过cat 指令查询该日志文件 ：

如果慢查询日志内容很多， 直接查看文件，比较麻烦， 这个时候

可以借助于mysql自带的 mysqldumpslow 工具， 来对慢查询日

志进行分类汇总。

2.3 explain执行计划

通过以上步骤查询到效率低的 SQL 语句后，可以通过 EXPLAIN

或者 DESC命令获取 MySQL如何执行 SELECT 语句的信息，包括

在 SELECT 语句执行过程中表如何连接和连接的顺序。

查询SQL语句的执行计划 ：

explain select * from tb_sku where id =

'100000030074';

1

 

 

具体字段

含义：

explain select * from tb_sku where name like

'%HuaWei手机Meta87384 Pro%';

1

 

 

字段

含义

 

 

id

select查询的序列号，是一组数字，表示的

是查询中执行select子句或者是操作表的顺

序。

select

_type

表示 SELECT 的类型，常见的取值有

SIMPLE（简单表，即不使用表连接或者子查

询）、PRIMARY（主查询，即外层的查

询）、UNION（UNION 中的第二个或者后

面的查询语句）、SUBQUERY（子查询中的

第一个 SELECT）等

table

输出结果集的表

type

表示表的连接类型，性能由好到差的连接类

型为( system ---> const -----> eq_ref ---

---> ref -------> ref_or_null---->

index_merge ---> index_subquery -----

> range -----> index ------> all )

possible_keys 表示查询时，可能使用的索引

key

表示实际使用的索引

key_len

索引字段的长度

rows

扫描行的数量

extra

执行情况的说明和描述

1). id

 

 

id 字段是 select查询的序列号，是一组数字，表示的是查询中执

行select子句或者是操作表的顺序。id 情况有三种 ：

A. id 相同表示加载表的顺序是从上到下。

B. id 不同id

值越大，优先级越高，越先被执行。

C. id 有相

同，也有不同，同时存在。id相同的可以认为是一组，

从上往下

顺

序执行；在所有的组中，id的值越大，优先级越高，

越先执行

。

2). select_type

表示 SELECT 的类型，常见的取值，如下表所示：

 

 

select_type 含义

 

SIMPLE

简单的select查询，查询中不包含子查询或者

UNION

PRIMARY

查

询

中若

包含

任何复杂的子查询，最外层查询

标

记

为

该标

识

SUBQUERY

在SELECT 或 WHERE 列表中包含了子查询

DERIVED

在FROM 列表中包含的子查询，被标记为

DERIVED（衍生） MYSQL会递归执行这些子

查询，把结果放在临时表中

UNION

若第二个SELECT出现在UNION之后，则标记

为UNION ； 若UNION包含在FROM子句的子

查询中，外层SELECT将被标记为 ： DERIVED

UNION

RESULT

从UNION表获取结果的SELECT

3). type

type 显示的是访问类型，是较为重要的一个指标，可取值为：

 

 

 

 

 

 

type

含义

NULL

MySQL不访问任何表，索引，直接返回结果

system 表只有一行记录(等于系统表)，这是const类型的特

例，一般不会出现

const

表示通过索引一次就找到了，const 用于比较

primary key 或者 unique 索引。因为只匹配一行

数据，所以很快。如将主键置于where列表中，

MySQL 就能将该查询转换为一个常亮。const于将

"主键" 或 "唯一" 索引的所有部分与常量值进行比较

eq_ref

类似ref，区别在于使用的是唯一索引，使用主键的

关联查询，关联查询出的记录只有一条。常见于主

键或唯一索引扫描

ref

非唯一性索引扫描，返回匹配某个单独值的所有

行。本质上也是一种索引访问，返回所有匹配某个

单独值的所有行（多个）

range

只检索给定返回的行，使用一个索引来选择行。

where 之后出现 between ， < , > , in 等操作。

index

index 与 ALL的区别为 index 类型只是遍历了索引

树， 通常比ALL 快， ALL 是遍历数据文件。

all

将遍历全表以找到匹配的行

结果值从最好到最坏以此是：

 

 

一般来

说， 我们需要保证查询至少达到 range 级别， 最好达到

ref 。

4). key

A. possible_keys : 显示可能应用在这张表的索引， 一个或多

个。

B. key ： 实际使用的索引， 如果为NULL， 则没有使用索引。

C. key_len : 表示索引中使用的字节数， 该值为索引字段最大可

能长度，并非实际使用长度，在不损失精确性的前提下， 长度越

短越好 。

5). rows

扫描行的数量。

6). fifiltered

这个字段表示存储引擎返回的数据在server层过滤后，剩下多少

满足查询的记录数量的比例。

NULL > system > const > eq_ref > ref > fulltext >

ref_or_null > index_merge > unique_subquery >

index_subquery > range > index > ALL

syst

em > const > eq_ref > ref > range > index > ALL

1

2

3

4

 

 

2.4 索引的使用

2.4.1 概述及作用

MySQL官方

对索引的定义为：索引（

index）是帮助MySQL高效

获取数据

的数据结构（有序）。在数据之外，数据库系统还维护

者满足特

定查找算法的数据结构，这些数据结构以某种方式引用

（指向）数据， 这样就可以在这些数据结构上实现高级查找算

法，这种数据结构就是索引。

优势：

1） 类似于书籍的目录索引，提高数据检索的效率，降低数据库

的IO成本。

2） 通过索引列对数据进行排序，降低数据排序的成本，降低

CPU的消耗。

劣势：

1） 实际上索引也是一张表，该表中保存了主键与索引字段，并

指向实体类的记录，所以索引列也是要占用空间的。

2） 虽然索引大大提高了查询效率，同时却也降低更新表的速

度，如对表进行INSERT、UPDATE、DELETE。因为更新表时，

MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了

索引列的字段，都会调整因为更新所带来的键值变化后的索引信

息。

2.4.2 索引结构

 

 

MySQL数据库中默认的存储引擎InnoDB的索引结构为B+树，而

根据叶子节点的内存存储不同，索引类型分为主键索引和非主键

索引。

主键索引的

叶子节点存储的是整行数据，在InnoDB中主键索引页

被称为聚

簇索引。其结构如下：

而非主键索引的叶子节点内容存储时的主键的值，在InnoDB中，

非主键索引也被称为二级索引或辅助索引。其结构如下：

2.4.3 验证索引

 

 

在tb_sku表中一共存在1000w的记录 ;

A. 根据主键ID查询速度很快

 

 

 

B. 根据name查询速度变慢

C. 对name字段建立索引再次查询

1 create index idx_sku_name on tb_sku(name);2.4.4 索引使用规则

没有建立索引之前, 执行计划如下:

建立索引 :

1). 全值匹配 ，对索引中所有列都指定具体值。

该情况下，索引生效，执行效率高。

create index idx_seller_name_status_address on

tb_seller(name, status, seller);

1

explain select * from tb_seller where name='小米科技'

and status='1' and address='北京市';

1

 

 

2). 最左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最

左前列开始

，并且不跳过索引中的列。

匹配最左

前缀法则，走索引：

违法最左前缀法则 ， 索引失效:

如果符合最左法则，但是出现跳跃某一列，只有最左列索引生

效：

3). 范围查询右边的列，不能使用索引 。

 

 

根据前面的两个字段name ， status 查询是走索引的， 但是最

后一个条件

address 没有用到索引。

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

5). 字符串不加单引号，造成索引失效。

由于，在查询是，没有对字符串加单引号，MySQL的查询优化

器，会自动的进行类型转换，造成索引失效。

6). 用or分割开的条件， 如果or前的条件中的列有索引，而后面

的列中没有索引，那么涉及的索引都不会被用到。

 

 

示例，name字段是索引列 ， 而createtime不是索引列，中间是

or进行连接是不走索引的 ：

1 explain select * from tb_seller where name='黑马程序

员' or createtime = '2088-01-01 12:00:00'\G;

 

 

 

7). 以%开头的Like模糊查询，索引失效。

如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹

配，索引失效。

解决方案 ：

通过覆盖索引来解决8). 如果MySQL评估使用索引比全表更慢，则不使用索引。

9). is NULL ， is NOT NULL 有时索引失效。

10). in ， not in 有时索引失效。

 

 

11). 尽量

使用覆盖索引，避免select *

尽量使用覆盖索引（只访问索引的查询（索引列完全包含查询

列）），减少select * 。

如果查询列，超出索引列，也会降低性能。

 

 

2.4.5 索引设计原则

索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考

虑符合这些原则，便于提升索引的使用效率，更高效的使用索

引。

对查询频次较高，且数据量比较大的表建立索引。

索引字段的选择，最佳候选列应当从where子句的条件中提

取，如果where子句中的组合比较多，那么应当挑选最常用、

过滤效果最好的列的组合。

使用唯一索引，区分度越高，使用索引的效率越高。

索引可以有效的提升查询数据的效率，但索引数量不是多多益

善，索引越多，维护索引的代价自然也就水涨船高。对于插

入、更新、删除等DML操作比较频繁的表来说，索引过多，会

引入相当高的维护代价，降低DML操作的效率，增加相应操作

TIP :

using index ：使用覆盖索引的时候就会出现

usin

g where：在查找使用索引的情况下，需要回表去查

询所

需的数据

u

sing index condition：查找使用了索引，但是需要回表

查询数据

using index ; using where：查找使用了索引，但是需

要的数据都在索引列中能找到，所以不需要回表查询数据

1

2

3

4

5

6

7

8

9

 

 

的时间消耗。另外索引过多的话，MySQL也会犯选择困难病，

虽然最终仍然会找到一个可用的索引，但无疑提高了选择的代

价。

使用短索引，索引创建之后也是使用硬盘来存储的，因此提升

索引访问

的

I/O

效

率

，

也

可

以

提

升

总

体

的

访

问

效

率

。

假

如

构

成

索引的字

段

总

长

度

比

较

短

，

那

么

在

给

定

大

小

的

存

储

块

内

可

以

存

储更多

的索引值，相应的可以有效的提升MySQL访问索引的

I/O效

率。

利用最左前缀，N个列组合而成的组合索引，那么相当于是创

建了N个索引，如果查询时where子句中使用了组成该索引的

前几个字段，那么这条查询SQL可以利用组合索引来提升查询

效率。

2.5 常见的SQL优化

2.5.1 环境准备

CREATE TABLE `emp` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(100) NOT NULL,

`age` int(3) NOT NULL,

`salary` int(11) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into `emp` (`id`, `name`, `age`, `salary`)

values('1','Tom','25','2300');

insert into `emp` (`id`, `name`, `age`, `salary`)

values('2','Jerry','30','3500');

1

2

3

4

5

6

7

8

9

10

 

 

2.5.2 order by优化

2.5.2.1 两种排序方式

insert into `emp` (`id`, `name`, `age`, `salary`)

values('3','Luci','25','2800');

insert into `emp` (`id`, `name`, `age`, `salary`)

values('4','Jay','36','3500');

inser

t

i

n

to

`e

mp

`

(

`i

d

`

,

`

n

a

me

`, `age`, `salary`)

value

s

(

'5

',

'T

o

m2

'

,

'2

1

'

,

'

2

2

0

0

');

ins

ert into `emp` (`id`, `name`, `age`, `salary`)

va

lues('6','Jerry2','31','3300');

insert into `emp` (`id`, `name`, `age`, `salary`)

values('7','Luci2','26','2700');

insert into `emp` (`id`, `name`, `age`, `salary`)

values('8','Jay2','33','3500');

insert into `emp` (`id`, `name`, `age`, `salary`)

values('9','Tom3','23','2400');

insert into `emp` (`id`, `name`, `age`, `salary`)

values('10','Jerry3','32','3100');

insert into `emp` (`id`, `name`, `age`, `salary`)

values('11','Luci3','26','2900');

insert into `emp` (`id`, `name`, `age`, `salary`)

values('12','Jay3','37','4500');

create index idx_emp_age_salary on

emp(age,salary);

11

12

13

14

15

16

17

18

19

20

21

22

 

 

1). 第一种是通过对返回数据进行排序，也就是通常说的 fifilesort

排序，所有不是通过索引直接返回排序结果的排序都叫 FileSort

排序。

 

 

 

2). 第二种通过有序索引顺序扫描直接返回有序数据，这种情况即

为 using index，不需要额外排序，操作效率高。

多字段排序了解了MySQL的排序方式，优化目标就清晰了：尽量减少额外的

排序，通过索引直接返回有序数据。where 条件和Order by 使

用相同的索引，并且Order By 的顺序和索引顺序相同， 并且

Order by 的字段都是升序，或者都是降序。否则肯定需要额外的

操作，这样就

会出现FileSort。

2.5.2.

2 Filesort 的优化

通过创建合适的索引，能够减少 Filesort 的出现，但是在某些情

况下，条件限制不能让Filesort消失，那就需要加快 Filesort的排

序操作。对于Filesort ， MySQL 有两种排序算法：

1） 两次扫描算法 ：MySQL4.1 之前，使用该方式排序。首先根

据条件取出排序字段和行指针信息，然后在排序区 sort buffffer 中

排序，如果sort buffffer不够，则在临时表 temporary table 中存

储排序结果。完成排序之后，再根据行指针回表读取记录，该操

作可能会导致大量随机I/O操作。

2）一次扫描算法：一次性取出满足条件的所有字段，然后在排序

区 sort buffffer 中排序后直接输出结果集。排序时内存开销较大，

但是排序效率比两次扫描算法要高。

MySQL 通过比较系统变量 max_length_for_sort_data 的大小

和Query语句取出的字段总大小， 来判定是否那种排序算法，如

 

果max_length_for_sort_data 更大，那么使用第二种优化之后

的算法；否则使用第一种。

可以适当提高 sort_buffffer_size 和 max_length_for_sort_data

系统变量，来增大排序区的大小，提高排序的效率。

 

 

2.5.3 group by优化

由于GROUP BY 实际上也同样会进行排序操作，而且与ORDER

BY 相比，GROUP BY 主要只是多了排序之后的分组操作。当然，

如果在分组的时候还使用了其他的一些聚合函数，那么还需要一

些聚合函数的计算。所以，在GROUP BY 的实现过程中，与

ORDER BY 一样也可以利用到索引。

如果查询包含 group by 但是用户想要避免排序结果的消耗， 则

可以执行order by null 禁止排序。如下 ：

优化后

drop index idx_emp_age_salary on emp;

explain select age,count(*) from emp group by age;

1

2

3

explain select age,count(*) from emp group by age

order by null;

1

 

 

从上面的例子可以看出，第一个SQL语句需要进行"fifilesort"，而

第二个SQL由于order by null 不需要进行 "fifilesort"， 而上文提

过Filesort往往非常耗费时间。

创建索引 ：

2.5.4 limit优化

一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一

个常见又非常头疼的问题就是 limit 5000000,10 ，此时需要

MySQL排序前5000010 记录，仅仅返回5000000 - 5000010 的

记录，其他记录丢弃，查询排序的代价非常大 。

limit分页操作, 越往后, 性能越低 :

优化方案:

cre

ate index idx_emp_age_salary on emp(age,salary);

1

 

 

2.5.5 count优化

在很多的业务系统中，都需要考虑进行分页操作，但是当我们执

行分页操作时，都需要进行一次count操作，求取总记录数，如果

数据库表的数据量大，在InnoDB引擎中，执行count操作的性能

是比较低的，需要遍历全表数据，对计数进行累加。

优化方案：

①. 在大数据量的查询中，只查询数据， 而不展示总记录数 ；

②. 通过缓存redis维护一个表的计数，来记录数据库表的总记录

数，在执行插入/删除时，需要动态更新；

③. 在数据库表中定义一个大数据量的计数表，在执行插入/删除

时，需要动态更新。

3. 存储引擎选择

select * from tb_sku t , (select id from tb_sku order by

id limit 9000000,1) a where t.id = a.id;

1

 

 

3.1 MySQL体系架构

整个MySQL Server由以下组成

Connection Pool : 连接池组件

Management Services & Utilities : 管理服务和工具组件

SQL Interface : SQL接口组件

Parser : 查询分析器组件

Optimizer : 优化器组件

Caches & Buffffers : 缓冲池组件

Pluggable Storage Engines : 存储引擎

File System : 文件系统

1） 连接层

最上层是一些客户端和链接服务，包含本地sock 通信和大多数基

于客户端/服务端工具实现的类似于 TCP/IP的通信。主要完成一

些类似于连接处理、授权认证、及相关的安全方案。在该层上引

入了线程池的概念，为通过认证安全接入的客户端提供线程。同

 

 

样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入

的每个客户端验证它所具有的操作权限。

2） 服务层

第二层架构主

要

完

成

大

多

数

的

核

心

服

务

功

能

，

如

SQ

L接

口

，

并

完

成缓存的查

询

，S

Q

L的

分

析

和

优

化

，

部

分

内

置

函

数

的

执行

。

所

有

跨存储引

擎的功能也在这一层实现，如 过程、函数等。在该层，

服务器会

解析查询并创建相应的内部解析树，并对其完成相应的

优化如确定表的查询的顺序，是否利用索引等， 最后生成相应的

执行操作。如果是select语句，服务器还会查询内部的缓存，如

果缓存空间足够大，这样在解决大量读操作的环境中能够很好的

提升系统的性能。

3） 引擎层

存储引擎层， 存储引擎真正的负责了MySQL中数据的存储和提

取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不

同的功能，这样我们可以根据自己的需要，来选取合适的存储引

擎。

4）存储层

数据存储层， 主要是将数据存储在文件系统之上，并完成与存储

引擎的交互。

和其他数据库相比，MySQL有点与众不同，它的架构可以在多种

不同场景中应用并发挥良好作用。主要体现在存储引擎上，插件

式的存储引擎架构，将查询处理和其他的系统任务以及数据的存

储提取分离。这种架构可以根据业务的需求和实际需要选择合适

的存储引擎。

 

 

3.2 存储引擎的介绍

和大多数的数据库不同, MySQL中有一个存储引擎的概念, 针对不

同的存储需求可以选择最优的存储引擎。存储引擎就是存储数

据，建立索引，更新查询数据等等技术的实现方式 。存储引擎是

基于表的，而不是基于库的。所以存储引擎也可被称为表类型。

MySQL中支持的存储引擎比较多, 可以通过SQL(show engines)

查看当前数据库支持的存储引擎 :

3.3 存储引擎的特点

 

 

特点

InnoDB

MyISAM

存储限制

64TB

256TB

事务安全

支持

-

锁机制

行锁(适合高并发)

表锁

B树索引

支持

支持

哈希索引

-（具有自适应哈希索引功能）

-

全文索引

支持(5.6版本之后)

支持

集群索引

支持

-

数据索引

支持

-

索引缓存

支持

支持

数据可压缩

支持

支持

空间使用

高

低

内存使用

高

低

批量插入速度

低

高

支持外键

支持

-

MySQL中支持的存储引擎比较多,我们这里重点讲解两种, InnoDB

与 MyISAM 。

示例:

 

 

create table goods_innodb(

id int NOT NULL AUTO_INCREMENT,

name varchar(20) NOT NULL,

primary key(id)

)ENG

INE=innodb DEFAULT CHARSET=utf8;

cre

ate table goods_myisam(

id int NOT NULL AUTO_INCREMENT,

name varchar(20) NOT NULL,

primary key(id)

)ENGINE=myisam DEFAULT CHARSET=utf8;

1

2

3

4

5

6

7

8

9

10

11

create table country_innodb(

country_id int NOT NULL AUTO_INCREMENT,

country_name varchar(100) NOT NULL,

primary key(country_id)

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table city_innodb(

city_id int NOT NULL AUTO_INCREMENT,

city_name varchar(50) NOT NULL,

country_id int NOT NULL,

primary key(city_id),

key idx_fk_country_id(country_id),

1

2

3

4

5

6

7

8

9

10

11

12

13

 

 

CONSTRAINT `fk_city_country` FOREIGN

KEY(country_id) REFERENCES

country_innodb(country_id) ON DELETE RESTRICT ON

UPDATE CASCADE

)ENG

INE=InnoDB DEFAULT CHARSET=utf8;

ins

ert into country_innodb values(null,'China'),

(n

ull,'America'),(null,'Japan');

insert into city_innodb values(null,'Xian',1),

(null,'NewYork',2),(null,'BeiJing',1);

create table country_myisam(

country_id int NOT NULL AUTO_INCREMENT,

country_name varchar(100) NOT NULL,

primary key(country_id)

)ENGINE=myisam DEFAULT CHARSET=utf8;

create table city_myisam(

city_id int NOT NULL AUTO_INCREMENT,

city_name varchar(50) NOT NULL,

country_id int NOT NULL,

primary key(city_id),

key idx_fk_country_id(country_id),

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

 

 

3.4 InnoDB存储引擎深度剖析

3.4.1 InnoDB体系结构

缓冲池

1). 介绍

CONSTRAINT `fk_city_country` FOREIGN

KEY(country_id) REFERENCES

country_myisam(country_id) ON DELETE RESTRICT ON

UPDATE CASCADE

)ENG

INE=myisam DEFAULT CHARSET=utf8;

ins

ert into country_myisam values(null,'China'),

(n

ull,'America'),(null,'Japan');

insert into city_myisam values(null,'Xian',1),

(null,'NewYork',2),(null,'BeiJing',1);

35

36

37

38

39

 

 

InnoDB存储引擎基于磁盘文件存储，访问物理硬盘和在内存中进

行访问，速度相差很大，为了尽可能弥补这两者之间的I/O效率的

差值，就需要把经常使用的数据加载到缓冲池中，避免每次访问

都进行磁盘I/O。

在InnoDB的

缓冲池中不仅缓存了索引页和数据页，还包含了

undo页、

插入缓存、自适应哈希索引以及InnoDB的锁信息等

等。

2). 读取

在数据库中进行读取页的操作时， 首先将磁盘中读取到的页数据

存放在缓冲池中， 下一次再读相同的页时， 首先判断缓冲池中是

否存在，如果缓冲池被命中，则直接读取数据， 如果没有，则读

取磁盘中的页数据。

3). 更新

而对于数据库中页的修改操作，则首先修改在缓冲池中的页，然

后再以一定的频率刷新到磁盘上，从而保证缓冲池中的数据与磁

盘中的数据一致。页从缓冲池刷新回磁盘的操作并不是在每次页

发生更新时，都需要触发，出于整体的性能考虑，而是通过

checkpoint机制刷新回磁盘。

4). 参数配置

在专用服务器上，通常将多达80％的物理内存分配给缓冲池。参

数设置：

1 show variables like 'innodb_buffffer_pool_size';在InnoDB引擎中，允许有多个缓冲池实例，根据页的哈希值分配

到不同的缓冲池实例中，从而减少数据库内部的资源竞争， 提升

并发处理能力。 参数配置：

参数配置:

vi /etc/my.conf

后台线程

1). Master Thread

主要负责将缓冲池中的数据异步刷新到磁盘中, 保持数据的一致

性， 还包括脏页的刷新、合并插入缓存、undo页的回收 。

2). IO Thread

在InnoDB存储引擎中大量使用了AIO来处理IO请求, 这样可以极

大地提高数据库的性能，而IO Thread主要负责这些IO请求的回

调。

1 innodb_buffffer_pool_size=268435456

 

 

Thread

线程数

参数配置

read thre

ad

4

innodb_read_io_threads

write t

hread

4

innodb_write_io_threads

insert

buffffer thread

1

-

log thread

1

-

3). Purge Thread

主要用于回收事务已经提交了的undo log，在事务提交之后，

undo log可能不用了，就用它来回收。

4). Pager Cleaner Thread

新引入的一个用于协助 Master Thread 刷新脏页到磁盘的线程，

它可以减轻 Master Thread 的工作压力，减少阻塞。

 

 

文件

1). frm文件

该文件是用来

保存每个表的元数据信息的， 主要包含表结构定义

。

2). 系统

表空间

系统表空间是InnoDB数据字典，二次写缓冲区，更改缓冲区和撤

消日志的存储区 。系统表空间可以具有一个或多个数据文件， 默

认情况下会在数据存放目录中创建一个名为 ibdata1 表空间数据

文件。该文件名称可以通过参数 innodb_data_fifile_path 指定。

fifile_name:fifile_size[:autoextend[:max:max_fifile_size]]

3). 独占表空间

innodb中设置了参数 innodb_fifile_per_table 为 1/ON，则会将

存储的数据、索引等信息单独存储在一个独占表空间，因此也会

产生一个独占表空间文件（

ibd）

4). redo log

重做日志， 用于恢复提交事务修改的页操作 ， 用来保证事务的

原子性和持久性。主要是解决 提交的事务没有执行完成但是数据

库崩溃了，当数据库恢复之后，可以完整的恢复数据。在执行操

作时，InnoDB存储引擎会首先将重做日志信息放到这个缓冲区

redo log buffffer,然后按照不同的策略和频率将buffffer中的数据刷

新到重做日志中。

 

 

redo log在磁盘中保存的名称为 ib_logfifile0，ib_logfifile1。

5). bin log

二进制日志，其中记录表结构中的数据变更，包含DDL与DML。

6). 其他

错误日志  看到

、查询日志、慢查询日志等。

3.4.2 InnoDB逻辑存储结构

 

1). 表空间

表空间是InnoDB存储引擎逻辑结构的最高层， 大部分数据都存

在于共享表空间ibdata1中。如果用户启用了参数

innodb_fifile_per_table ，则每张表都会有一个表空间

（

xxx.ibd）,里面存放表中的数据、索引和插入缓存Bitmap页。

 

 

其他的数据如undo log、插入缓存索引页、系统事务信息、二次

写缓存都是在共享表空间中。

2). 段

表空间是

由各个段组成的， 常见的段有数据段、索引段、回滚段

等。Inn

o

DB

存

储

引

擎

是

基

于

索

引

组

织

的

，

因此

数据

即是

索

引

，

索

引即数据

。

数

据

段

就

是

B

+树

的

叶

子

节

点

，

索

引段

即为

B+

树

的

非

叶

子节点。

InnoDB中对于段的管理，都是引擎自身完成，不需要人为对其控

制。

3). 区

区是表空间的单元结构，每个区的大小为1M。 默认情况下，

InnoDB存储引擎页大小为16K， 即一个区中一共有64个连续的

页。

4). 页

页是组成区的最小单元，页也是InnoDB 存储引擎磁盘管理的最

小单元，每个页的大小默认为 16KB。为了保证页的连续性，

InnoDB 存储引擎每次从磁盘申请 4-5 个区。

5). 行

 

 

InnoDB 存储引擎是面向行的（

row-oriented)，也就是说数据是

按行进行存放的，每个页存放的行记录也是有硬性定义的，最多

允许存放 16KB/2-200 行，即 7992 行记录。

3.4.3 checkpoint

1). 介绍

由于日常的DML语句操作时，首先操作的是缓冲池，并没有直接

写入到磁盘，这有可能会导致内存中的数据与磁盘中的数据产生

不一致的情况，而与磁盘中数据不一致的页我们成为"脏页"。 而

checkpoint的工作，就是将内存中的脏页，在一定条件下刷新到

磁盘。

如果在从缓冲池将页数据刷新到磁盘的过程中发生宕机，那么数

据就无法恢复了；为了避免这种情况的发生，采用了Write

Ahead Log策略，即当事务提交时，先写重做日志(redo log)，

再修改缓冲池数据页，最后通过Checkpoint刷新到磁盘（事务提

交会触发checkpoint）。这样正在执行的事务，因为存在日志都

可以被恢复，没有日志的事务还没有执行也不会丢失数据。

2). 作用

A. 缩短数据恢复时间

trx_id：每次对某条聚簇索引记录进行改动时，都会把对应

的事务

id赋值给trx_id隐藏列。

roll_

pointer：每次对某条聚簇索引记录进行改动时，都会

把旧

的版本写入到undo日志中，然后这个隐藏列就相当于

一个指针，可以通过它来找到该记录修改前的信息。

1

2

 

 

当数据库发生宕机时，数据库不用重做所有的日志，因为

Checkpoint之前的页都已经刷新会磁盘了，故数据库只需要重做

Checkpoint之后的日志就好，这样就大大缩短了恢复时间。

B. 缓冲池不

够用时，需要先将脏页数据刷新到磁盘中；

当缓冲池

不

够

用

时

,

根

据

L

R

U

算

法

溢

出

最

近

最

少

使

用

的页, 如果此

页是脏页

,

则

强

制

执

行

C

he

c

k

p

o

int

, 刷

新

脏

页

到

磁

盘

。

C. 重做日志不可用时，刷新脏页到磁盘；

redo log大小是固定的, 当前的InnoDB引擎中， 重做日志的设计

都是循环使用的，并不是无限增大的。重做日志可以被重用的部

分是已经不再需要的， 数据库发生宕机也不需要这部分的重做日

志，因此可以被覆盖使用， 如果此时重做日志还需要使用，那么

必须强制执行Checkpoint，将缓冲池中的页至少刷新磁盘,

checkpoint移动到当前重做日志的位置。

 

 

 

 

 

write pos表示日志当前记录的位置，当ib_logfifile_1写满后，会

从ib_logfifile_0从头开始记录；check point表示将日志记录的修

改写进磁盘，完成数据落盘，数据落盘后checkpoint会将日志上

的相关记录擦除掉，即write position ->checkpoint 之间的部分

是redo log空着的部分，用于记录新的记录，checkpoint-

>write position 之间是redo log待落盘的数据修改记录。当

write postion追上checkpoint时，得先停下记录，先推动

checkpoint向前移动，空出位置记录新的日志。

3). 分类

A. Sharp CheckpointSharp Checkpoint 发生在数据库关闭时，将所有的脏页都刷新

回磁盘，这是默认的工作方式，参数：

innodb_fast_shutdown=1。

B. Fuzzy Ch

eckpoint

在InnoD

B

存

储

引

擎

运

行

时，使用Fuzzy Checkpoint进行页刷

新，只刷

新

一

部

分

脏

页

。

3.4.4 InnoDB关键特性

3.4.4.1 插入缓存

插入缓冲是InnoDB存储引擎关键特性中最令人激动的。

主键是行唯一的标识符，在应用程序中行记录的插入顺序一般是

按照主键递增的顺序进行插入的。因此，插入聚集索引一般是顺

序的，不需要磁盘的随机读取。因此，在这样的情况下，插入操

作一般很快就能完成。

但是，不可能每张表上只有一个聚集索引，在更多的情况下，一

张表上有多个非聚集的辅助索引（

secondary index）。比如，

我们还需要按照name这个字段进行查找，并且name这个字段不

是唯一的, 这样的情况下产生了一个非聚集的并且不是唯一的索

引。在进行插入操作时，数据页的存放还是按主键id的执行顺序

存放，但是对于非聚集索引，叶子节点的插入不再是顺序的了。

这时就需要离散地访问非聚集索引页，插入性能在这里变低了。

然而这并不是这个name字段上索引的错误，因为B+树的特性决

定了非聚集索引插入的离散性。

 

 

InnoDB存储引擎开创性地设计了插入缓冲，对于非聚集索引的插

入或更新操作，不是每一次直接插入索引页中，而是先判断插入

的非聚集索引页是否在缓冲池中。如果在，则直接插入；如果不

在，则先放入一个插入缓冲区中，好似欺骗数据库这个非聚集的

索引已经插到

叶

子

节点

了

，

然

后再

以一

定

的频

率执

行

插

入

缓

冲和

非聚集索引

叶子

节

点

的合

并

操

作

，这

时通

常

能将

多

个

插

入合

并

到

一个操作

中（因为在一个索引页中），这就大大提高了对非聚集

索引执行

插入和修改操作的性能。

3.4.4.2 两次写

当数据库写物理页时，如果宕机了，那么可能会导致物理页的一

致性被破坏。

可能有人会说，重做日志不是可以恢复物理页吗？实际上是的，

但是要求是在物理页一致的情况下。

 

 

也就是说，如果物理页完全是未写之前的状态，则可以用重做日

志恢复。如果物理页已经完全写完了，那么也可以用重做日志恢

复。但是如果物理页前面2K写了新的数据，但是后面2K还是旧的

数据，则种情况下就无法使用重做日志恢复了。

这里的两

次写就是保证了物理页的一致性，使得即使宕机，也可

以用重做

日志恢复。

在写物理页时，并不是直接写到真正的物理页上去，而是先写到

一个临时页上去，临时页写完后，再写物理页。这样一来：

A. 如果写临时页时宕机了，物理页还是完全未写之前的状态，可

以用重做日志恢复

B. 如果写物理页时宕机了，则可以使用临时页来恢复物理页

InnoDB中共享表空间中划了2M的空间，叫做double write，专

门存放临时页。

InnoDB还从内存中划出了2M的缓存空间，叫做double write

buffffer，专门缓存临时页

每次写物理页时，先写到double write buffffer中，然后从double

write buffffer写到double write上去。最后再从double write

buffffer写到物理页上去

 

 

3.4.4.3 自适应hash索引

在InnoDB中默认支持的索引结构为 B+ 树，B+ 树索引可以使用

到范围查找，同时是按照顺序的方式对数据进行存储，因此很容

易对数 据进行排序操作，在联合索引中也可以利用部分索引键进

行查询 。而对于Hash索引则只能满足 =，<>,in查询，不能使用

范围查询， 而且数据的存储是没有顺序的。

MySQL 默认使用 B+ 树作为索引，因为 B+ 树有着 Hash 索引没

有的优点，那么为什么还需要自适应 Hash 索引呢？

这是因为B+树的查找次数，取决于B+树的高度，在生产环境中，

B+树的高度一般为3-4层，故需要3-4次查询。而 Hash 索引在进

行数据检索的时候效率非常高，通常只需要 O(1) 的复杂度，也就

是一次就可以完成数据的检索。虽然 Hash 索引的使用场景有很

多限制，但是优点也很明显。InnoDB存储引擎会监控对表上各索

 

 

引页的查询，如果观察到hash索引可以提升速度，则建立hash索

引，称之为自适应hash索引（Adaptive Hash Index，AHI）。

注意，这里的自适应指的是不需要人工来指定，系统会根据情况

自动完成。

什么情况

下

才会

使

用

自适

应

Ha

s

h

索

引

呢

？

如

果

某个

数

据

经

常

被

访问，当

满

足

一

定条

件

的

时

候

，

就

会

将

这

个

数

据

页

的地

址

存

放

到

Hash 表中。这样下次查询的时候，就可以直接找到这个页面的所

在位置。值得注意的是，hash索引只能用于= ，in的查询，对于

其他的查询类型，如范围匹配等是不能使用hash索引的。而且自

适应 Hash 索引只保存热数据（经常被使用到的数据），并非全

表数据。因此数据量并不会很大，因此自适应 Hash 也是存放到

缓冲池中，这样也进一步提升了查找效率。

3.4.4.4 异步IO

为了提高磁盘的操作性能，在InnoDB存储引擎中使用异步非阻塞

AIO的方式来操作磁盘。

与AIO对应的是Sync IO，如果是同步IO操作，则每进行一次IO操

作，需要等待此次操作结束后才可以进行接下来的操作。但是如

果用户发出的是一条索引扫描的查询，那么这条SQL查询语句可

能需要扫描多个索引页，也就是需要进行多次的IO操作。每扫描

 

 

一个页并等待其完成之后，再进行下一次扫描，这是没有必要

的。

用户可以在发出一个IO请求后立即再发出另一个IO请求，当全部

的IO请求发送完毕后，等待所有的IO操作完成，这就是AIO。

3.4.4.

5 刷新临接页

InnoDB提供刷新临近页功能：当刷新一脏页时，同时检测所在区

(extent)的所有页，如果有脏页则一并刷新，好处则是通过AIO特

性合并写IO请求，缺点则是有些页不怎么脏也好被刷新，而且频

繁的更改那些不怎么脏的页又很快变成脏页，造成频繁刷新。对

于固态磁盘则考虑关闭此功能（将innodb_flflush_neighbors设置

为0）。

3.4.5 InnoDB事务

3.4.5.1 概述

事务可由一条简单的SQL语句组成，也可以由一组复杂的SQL语句

组成。事务是访问并更新数据库中各个数据项的一个程序执行单

元。在事务操作时，这组执行单元中的SQL，要么全部成功， 要

么全部失败。

事务具有以下4个特性，简称为事务ACID属性。

 

 

ACID属性

含义

原子性

（Atomicity）

事务是一个原子操作单元，其对数据的修

改，要么全部成功，要么全部失败。

一致性

（Consist

ent）

在

事务

开始和完成时，数据都必须保持一致

状态

。

隔离性

（

Isolation）

数据

库

系

统

提

供

一

定

的隔

离

机

制

，

保

证

事

务

在

不

受

外

部

并

发操

作

影

响

的

“

独

立

”

环

境

下运行。

持久性

（Durable）

事务完成之后，对于数据的修改是永久的。

3.4.5.2 隔离级别

并发事务带来的问题：

 

 

问题

含义

丢失更新

（

Lost

Update）

当两个或多个事务选择同一行，最初的事务修

改的值，会被后面的事务修改的值覆盖。

脏读（D

irty

Reads

）

当一个事务正在访问数据，并且对数据进行了

修改，而这种修改还没有提交到数据库中，这

时，另外一个事务也访问这个数据，然后使用

了这个数据。

不可重复读

（Non

Repeatable

Reads）

一个事务在读取某些数据后的某个时间，再次

读取以前读过的数据，却发现和以前读出的数

据不一致。

幻读

（Phantom

Reads）

一个事务按照相同的查询条件重新读取以前查

询过的数据，却发现其他事务插入了满足其查

询条件的新数据。

为了解决上述提到的事务并发问题，数据库提供一定的事务隔离

机制来解决这个问题。数据库的事务隔离越严格，并发副作用越

小，但付出的代价也就越大，因为事务隔离实质上就是使用事务

在一定程度上“串行化” 进行，这显然与“并发” 是矛盾的。

数据库的隔离级别有4个，由低到高依次为Read

uncommitted、Read committed、Repeatable read、

Serializable，这四个级别可以逐个解决脏写、脏读、不可重复

读、幻读这几类问题。

 

 

隔离级别

丢失更

新

脏

读

不可重复

读

幻

读

Read uncommitted

×

√

√

√

Read commi

tted

×

×

√

√

Repeat

able read（默

认）

×

×

×

√

Serializable

×

×

×

×

3.4.5.3 实现

1). redo log

redo log叫做重做日志，是用来实现事务的持久性。该日志文件

由两部分组成：重做日志缓冲（

redo log buffffer）以及重做日志

文件（

redo log）,前者是在内存中，后者在磁盘中。当事务提交

之后会把所有修改信息都会存到该日志中, 用于在刷新脏页到磁盘

时,发生错误时, 进行数据恢复使用。

例：

原始数据内容：

执行事务操作：

 

 

流程：

mysql 为了提升性能不会把每次的修改都实时同步到磁盘，而是

会先存到Buffffer Pool(缓冲池)里头，把这个当作缓存来用。然后

使用后台线程将缓存池刷新到磁盘。

当在执行刷新时，宕机或者断电，可能会丢失部分数据。所以引

入了redo log来记录已成功提交事务的修改信息，并且在事务提

交时会把redo log持久化到磁盘，系统重启之后在读取redo log

恢复最新数据。

简单来说 ， redo log是用来恢复数据的 用于保障，已提交事务

的持久化特性

start transaction;

select balance from bank where name="Tom";

-- 生成 重做日志 balance=8000

update bank set balance = balance - 2000;

-- 生成

重做日志 account=2000

upd

ate

fifinance set account = account + 2000;

commi

t

;

1

2

3

4

5

6

7

 

 

2). undo log

undo log 叫做回滚日志，用于记录数据被修改前的信息。他正好

跟前面所说的重做日志所记录的相反，重做日志记录数据被修改

后的信息。undo log主要记录的是数据的逻辑变化，为了在发生

错误时回滚之

前的操作，需要将之前的操作都记录下来，然后在

发生错误

时才可以回滚。

undo log 记录事务修改之前版本的数据信息，因此假如由于系统

错误或者rollback操作而回滚的话可以根据undo log的信息来进

行回滚到没被修改前的状态。

3.5 存储引擎应用场景

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引

擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引

擎进行组合。以下是几种常用的存储引擎的使用环境 。

InnoDB : 是Mysql的默认存储引擎，用于事务处理应用程序，

支持外键, 行锁。如果应用对事务的完整性有比较高的要求，

在并发条件下要求数据的一致性，数据操作除了插入和查询以

外，还包含很多的更新、删除操作，那么InnoDB存储引擎是

比较合适的选择。InnoDB存储引擎除了有效的降低由于删除

 

 

和更新导致的锁定， 还可以确保事务的完整提交和回滚，对于

电商系统中的商品（

SPU、SKU、分类、品牌）、订单、用户

等信息的存储，InnoDB是最合适的选择。

MyISAM ： 如果应用是以读操作和插入操作为主，只有很少的

更新和删

除操

作

，

并

且对

事务

的完

整性

、并

发

性

要

求

不

是很

高，那么

选

择

这个

存

储

引擎

是非

常合

适的

。

对

于

电

商系

统

中，

系统的

操作日志、用户评价、足迹等信息的存储，MyISAM是

合适

的选择。

4. 电商粉丝关注数十亿数据库剖析

4.1 分析

1). 业务需求

在B2B2C的电商系统中，用户可以在店铺首页点击“关注/收藏”,

来关注店铺。一个店铺可以被多个用户关注，一个用户也可以关

注多个店铺。

 

 

2). 表结构

 

 

 

3). 存储引擎选择

由于店铺、用户、用户关注店铺这些信息都是业务系统中比较核

心的数据，对于数据的完整性要求是比较高的。所以这里我们采

用InnoDB存储引擎。

4). 分片策略

由于在一个大型的电商系统中，用户数据量和店铺粉丝关注数据

量，都是比较大的，这里在进行数据存储时，需要考虑到分片存

储。表名

是否

分片

分片策略

备注

tb_shop

是

垂直拆分

数据量一般, 几十万

到上百万

tb_use

r

是

水

平拆分 ,

取模

数据量巨大,数亿

tb_shop_user 是

水平拆分 ,

取模

数据量巨大,数十亿

4.2 主键设计方案分析

1). 主键作用

A. 保证实体的完整性；

B. 加快数据库的操作速度；

C. 数据库自动按主键值的顺序显示表中的记录。如果没有定义主

键，则按输入记录的顺序显示表中的记录。

2). 数据类型选择

A. 整数类型

自增主键： 利于排序， 插入性能高， 占用空间小， 数据库自动

维护。 但是不适合数据库分片。

自己生成： 利于排序， 插入性能高， 占用空间小。但是需要手

动在应用程序层面操作。

 

 

B. 字符串类型

UUID： 虽然能够保证生成的id不会重复，但是无法排序，插入性

能低，占用磁

盘空间大。

其他的业

务字段：如用户名 , 身份证号等

C. 分布式ID

由于当前的数据库中我们需要考虑分库分表操作，所以不能选择

使用自增ID，避免主键冲突。所以这里我们需要考虑使用分布式

ID生成器snowflflake 雪花生成算法。

snowflflake是Twitter开源的分布式ID生成算法，结果是一个long

型的ID。使用41bit作为毫秒数，10bit作为机器的ID（5个bit是数

据中心，5个bit的机器ID），12bit作为毫秒内的流水号，最后还

有一个符号位，永远是0。

0 - 0000000000 0000000000 0000000000 0000000000 0

- 00000 - 00000 - 000000000000

1位标识，由于long基本类型在Java中是带符号的，最高

位是符号位，正数是0，负数是1，所以id一般是正数，最

高位是0

 

 

41位时间截(毫秒级)，注意，41位时间截不是存储当前时

间的时间截，而是存储时间截的差值（当前时间截 - 开始

时间截) 得到的值），这里的的开始时间截，一般是我们

的id生成器开始使用的时间，由我们程序来指定的（如下

下面程

序

IdW

o

r

k

er

类

的

s

t

a

r

t

T

i

me属

性

）

。

4

1

位

的

时

间

截，可

以使

用

6

9

年

，

年

T

=

(

1

L

<< 41

) /

(

10

0

0

L

* 6

0

* 60

* 24

* 365) = 69

10

位的数据机器位，可以部署在1024个节点，包括5位

datacenterId和5位workerId。10-bit机器可以分别表示

1024台机器。如果我们对IDC划分有需求，还可以将10-

bit分5-bit给IDC，分5-bit给工作机器。这样就可以表示32

个IDC，每个IDC下可以有32台机器，可以根据自身需求定

义。

12位序列，毫秒内的计数，12位的计数顺序号支持每个节

点每毫秒(同一机器，同一时间截)产生4096个ID序号。12

个自增序列号可以表示2^12个ID，理论上snowflflake方案

的QPS约为409.6w/s，这种分配方式可以保证在任何一个

IDC的任何一台机器在任意毫秒内生成的ID都是不同的。

加起来刚好64位，为一个Long型。

4.3 索引设计

 

 

表

索

引

语句

tb_shop

复

合

索

引

create index

idx_shop_name_company_time on

tb_shop (shopname,

companyname, createtime);

tb_user

唯

一

索

引

create unique index

idx_user_username on

tb_user(name);

tb_shop_user

复

合

索

引

create index idx_shopid_userid on

tb_shop_user(shopid, userid);

tb_shop_user

单

列

索

引

create index idx_userid on

tb_shop_user(userid);

4.4 数据库架构设计

4.4.1 架构介绍

 

 

1). 设计原则

2). 架构介绍

下面的架构中, 我们就主要针对于 tb_shop_user 的数据存储方案

进行分析 , tb_user 表都是类似的。

A. 可用性设计

在高并发的电商系统中， 系统的可用性是必须要考虑

的， 对于系统的可用性， 我们可以使用复制、冗余的思想

进行处

理。

B

. 读性能设计

可以通过读写分离的机制， 来降低主库的压力， 提高的

读性能。

C. 一致性设计

由于在当前的业务中, 粉丝关注店铺, 对于数据的读写一

致性要求并不高, 所以主从的一致性可以不用考虑。

D. 拓展性设计

需要考虑服务器的扩容问题， 如何平滑扩容， 避免大量

的数据迁移。

1

2

3

4

5

6

7

8

9

10

11

 

 

A. 当前的系统架构中， 对数据库表进行水平拆分, 根据数据量的

预估及服务器的性能， 来决定具体切分为多少片 。

B. 为了对应用程序隐藏后端数据库架构的复杂性 , 简化应用程序

的数据库操作处理 , 提升访问性能, 选择使用数据库中间件产品 ,

如: MyCat 。

C. 为了保证数据库的可用性, 采用了双主双从模式 , 当主节点挂掉

之后, 另外的主节点会自动顶替上来, 继续对外提供服务 。

D. 为了保证读写的性能, 采用读写分离的模式, 考虑到读的压力比

较大, 所以一个主库对应两个从库, 通过两个从库来负载均衡读的

压力。

E. 对于系统的拓展性, 我们可以采用前面提到的平滑扩容的方案

来实现动态扩容， 提升数据库的数据存储和处理能力。

4.4.2 架构搭建

1). 主从复制原理

 

 

复制是指将主数据库的DDL 和 DML 操作通过二进制日志传到从

库服务器中，然后在从库上对这些日志重新执行（也叫重做），

从而使得从库和主库的数据保持同步。

MySQL支持一台主库同时向多台从库进行复制， 从库同时也可以

作为其他从服

务器的主库，实现链状复制。

MySQL

的主从复制原理如下:

从上层来看，复制分成三步：

Master 主库在事务提交时，会把数据变更作为时间 Events 记

录在二进制日志文件 Binlog 中。

主库推送二进制日志文件 Binlog 中的日志事件到从库的中继

日志 Relay Log 。

slave重做中继日志中的事件，将改变反映它自己的数据。

2). 双主双从的搭建

 

 

编号

角色

IP地址

端口号

1

Master1

192.168.192.157

3306

2

Slave1

192.168.192.158

3306

3

Master2

192.168.192.159

3306

4

Slave2

192.168.192.160

3306

准备的机器如下:

①. 双主机配置

Master1配置:

#主服务器唯一ID

server-id=1

#启用二进制日志

log-bin=mysql-bin

# 设置不要复制的数据库(可设置多个)

# binlog-ignore-db=mysql

# binlog-ignore-db=information_schema

#设置需要复制的数据库

binlog-do-db=db02

binlog-do-db=db03

binlog-do-db=db04

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

 

 

Master2配置:

#设置logbin格式

binlog_format=STATEMENT

# 在作为从数据库的时候，有写入操作也要更新二进制日

志文件

log-s

lave-updates

16

17

18

19

20

#主服务器唯一ID

server-id=3

#启用二进制日志

log-bin=mysql-bin

# 设置不要复制的数据库(可设置多个)

#binlog-ignore-db=mysql

#binlog-ignore-db=information_schema

#设置需要复制的数据库

binlog-do-db=db02

binlog-do-db=db03

binlog-do-db=db04

#设置logbin格式

binlog_format=STATEMENT

# 在作为从数据库的时候，有写入操作也要更新二进制日

志文件

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

 

 

②. 双从机配置

Slave1配置

:

Salve2配置:

C. 双主机、双从机重启 mysql 服务

④. 主机从机都关闭防火墙

⑤. 在两台主机上建立帐户并授权 slave

20 log-slave-updates

#从服

务器唯一ID

ser

ver-id=2

#启用中继日志

relay-log=mysql-relay

1

2

3

4

5

#从服务器唯一ID

server-id=4

#启用中继日志

relay-log=mysql-relay

1

2

3

4

5

 

 

查询Mas

ter1的状态 :

查询Master2的状态 :

⑥. 在从机上配置需要复制的主机

Slave1 复制 Master1，Slave2 复制 Master2

slave1 指令:

#在主机MySQL里执行授权命令

GRANT REPLICATION SLAVE ON *.* TO 'itcast'@'%'

IDENTIFIED BY 'itcast';

flflush p

rivileges;

1

2

3

4

CHANGE MASTER TO MASTER_HOST='192.168.192.157',

MASTER_USER='itcast',

MASTER_PASSWORD='itcast',

MASTER_LOG_FILE='mysql

bin.000001',MASTER_LOG_POS=409;

1

2

3

4

 

 

slave2 指令:

⑦. 启动两台从服务器复制功能 , 查看主从复制的运行状态

⑧. 两个主机互相复制

Master2 复制 Master1，Master1 复制 Master2

CHANGE MASTER TO MASTER_HOST='192.168.192.159',

MASTER_USER='itcast',

MASTER_PASSWORD='itcast',

MASTE

R_LOG_FILE='mysql

bin.

000001',MASTER_LOG_POS=409;

1

2

3

4

start slave;

show slave status\G;

1

2

3

 

 

Master1 执行指令:

Master2 执行指令:

⑨. 启动两台主服务器复制功能 , 查看主从复制的运行状态

CHANGE MASTER TO MASTER_HOST='192.168.192.159',

MASTER_USER='itcast',

MASTER_PASSWORD='itcast',

MASTE

R_LOG_FILE='mysql

bin.

000001',MASTER_LOG_POS=409;

1

2

3

4

CHANGE MASTER TO MASTER_HOST='192.168.192.157',

MASTER_USER='itcast',

MASTER_PASSWORD='itcast',

MASTER_LOG_FILE='mysql

bin.000001',MASTER_LOG_POS=409;

1

2

3

4

start slave;

show slave status\G;

1

2

3

 

 

⑩. 验证

create database db03;

use db03;

create table user(

id int(11) not null auto_increment,

name varchar(50) not null,

sex varchar(1),

primary key (id)

)engine=innodb default charset=utf8;

insert into user(id,name,sex) values(null,'Tom','1');

insert into user(id,name,sex) values(null,'Trigger','0');

insert into user(id,name,sex) values(null,'Dawn','1');

insert into user(id,name,sex) values(null,'Jack

Ma','1');

insert into user(id,name,sex) values(null,'Coco','0');

insert into user(id,name,sex) values(null,'Jerry','1');

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

 

 

在Master1上创建数据库:

在Mast

er1上创建表 :

⑪. 停止从服务复制功能

⑫. 重新配置主从关系

4.5 避免删库跑路

1 stop slave;

stop slave;

reset master;

1

2

 

 

4.5.1 背景介绍

2015年5月28日中午11时左右，携程官网、APP同时崩溃。将近

两个小时后，携程才发表声明，简单表示服务器遭到不明攻击，

正在紧急恢复。随即，这份声明被携程删除。接着，据携程内部

泄露的言论称

，是服务器某根目录被删除了。同时，网上也传出

携程数据

库被物理删除的说法。这之后，携程网站才提示用户可

以改访问

艺龙，但很快，承载不了过大流量的艺龙网也瘫痪了。

2020年2月23日19点 ，微盟公司收到系统监控报警 ，服务出现

故障 ，微盟研发中心运维部核心运维人员通过VPN登入服务器，

破坏SaaS线上生产环境并删除数据库，随后，微盟内部系统监控

报警，导致大面积服务集群无法响应。

 

 

4.5.2 原因分析

1). 数据库单点

2). 数据库未做冗余备份

3). 数据库权限配置不严谨

4). 数据库操作不规范

5). 法律意识薄弱

4.5.3 如果避免

1). 上层应用程序的严谨性

 

 

2). 数据库做高可用架构搭建

 

 

 

3). 数据库实时备份

4). 数据库严格的权限管理

针对于业务系统数据库严禁使用root用户进行访问， 应用为对应

的业务人员创建单独的账户进行操作 ；

1 create user 'itcast'@'%' identifified by 'itcast';

并为用户分配对应的权限， 对于delete, drop这样的操作，再分

配权限时，应该慎重；

1 grant select,insert,update on db01.* to 'itcast'@'%';

5). 关键应用业务的防删库监控机制

实时监控与告警，比如利用网络可视化和AI实现智能风控，当员

工频繁对敏感数据的操作时，给予告警、暂停，强制走审批等流

程的风控策略。6). 敏感数据操作的双人复核机制

回到微盟和携程的案例，相信你会发现，现在运维人员的权限其

实是很高的

，

高到

可

以

直接

摧

毁

一

个

系统

！

账

户分

等级

、

分权

限

的设定比如研

发

人员

无

法

对数

据

库

本

身

进行

操

作

，只

能进

行

数据

项的简单

运维操作，部分敏感表或数据项的操作需主管审批。

7). 技术业务人员的法律意识