MySQL架构优化

一、MySQL存储引擎选择

1、常用存储引擎对比

存储引擎的主要作用是进行数据的存取和检索，也是真正执行 SQL 语句的组件。

1）MyISAM 和 InnoDB 的主要区别 

MyISAM：

• 5.5 版本之前的默认引擎。
• 支持全文索引、压缩、空间函数等。
• 不支持事务和行级锁，所以一般用于读多写少的场景。
• 不支持外键。
• 索引和数据是分开存储的。

InnoDB：

• 5.5 版本之后的默认引擎。
• 支持事务、支持外键。
• 通过 MVCC 来支持高并发。
• 基于聚簇索引建立的，索引和数据存储在一起。

2、InnoDB 特性

InnoDB 存储引擎（Storage Engine）是 MySQL 默认之选，所以非常典型。

InnoDB 的整体架构分为两个部分：内存架构和磁盘架构，如图：

存储引擎的内容非常多，并不是一篇文章能说清楚的，本文不过多展开，我们在此只需要了解内存架构和磁盘架构的大致组成即可。

InnoDB 引擎是面向行存储的，数据都是存储在磁盘的数据页中，数据页里面按照固定的行格式存储着每一行数据。

行格式主要分为四种类型：Compact、Redundant、Dynamic 和 Compressed，默认为 Compact 格式。

Innodb 存储引擎和 MyISAM 存储引擎最大区别主要有以下几点：

1. 支持事务
2. 支持行锁
3. MVCC（即多版本并发控制，是一种并发控制的方法）
4. Crash 快速恢复
5. BufferPool 数据缓存

在整体性能表现方面， Innodb 和 MyISAM 两个存储引擎在不同的场景下差异比较大，主要原因也是上面的主要区别所造成的。

3、选择合适的存储引擎

在选择存储引擎时，应根据应用特点选择合适的存储引擎，对于复杂的应用系统可以根据实际情况选择多种存储引擎进行组合。

InnoDB

• MySQL 5.5 之后默认的 MySQL 插件式存储引擎。
• 用于事务处理的应用程序，支持外键。
• 如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询以外，还包括很多的更新、删除操作，那么 InnoDB 存储引擎是比较合适的选择。
• InnoDB 存储引擎除了有效地降低由于删除和更新导致的锁定，还可以确保事务的完整提交（Commit）和回滚（Rollback）。
• 对于类似计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB 都是合适的选择。

MyISAM

• MySQL 5.5 之前默认的 MySQL 插件式存储引擎。
• 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常适合的。
• MyISAM 是在 Web 数据仓储和其他应用环境下最常使用的存储引擎之一。

MEMORY

• 将所有数据保存在内存中，在需要快速定位记录和其他类似数据的环境下，可提供极快的访问。
• MEMORY 的缺陷是对表的大小有限制，太大的表无法 CACHE 在内存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。
• MEMORY 表通常用于更新不太频繁的小表，用以快速得到访问结果。

MERGE

• 用于将一系列等同的 MyISAM 表以逻辑方式组合在一起，并作为一个对象引用它们。
• MERGE 表的优点在于可以突破对单个 MyISAM 表大小的限制，并且通过将不同的表分布在多个磁盘上，可以有效地改善 MERGE 表的访问效率。
• 对于诸如数据仓储等 VLDB 环境十分适合。

4、查询存储引擎信息的相关SQL

-- 查看 mysql 现在已提供什么存储引擎
show engines;

-- 查看 mysql 当前默认的存储引擎
show variables like '%storage_engine%';

-- 查看某个表用了什么引擎（引擎是表级别的概念，在显示结果里的engine就表示该表当前用的存储引擎）
show create table 表名;

-- 修改表的存储引擎
alter table 表名 engine=myisam;

二、MySQL高性能索引策略

1、索引作用

生产上为了高效地查询数据库中的数据，我们常常会给表中的字段添加索引。那么如何添加索引才能使索引更高效：

• 添加的索引是越多越好吗？

• 索引有哪些类型？

• 为啥有时候明明添加了索引却不生效？

• 如何评判一个索引设计的好坏？

索引是对数据库表中一列或多列的值进行排序的一种数据结构，好比是一本书前面的目录，可以增加对特定信息的查询速度。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中的（可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）。

1. 索引作用

1）索引能极大地减少扫描行数

当我们要在新华字典里查某个字（如「先」）具体含义的时候，通常都会拿起一本新华字典来查，你可以先从头到尾查询每一页是否有「先」这个字，这样做（对应数据库中的全表扫描）确实能找到，但效率无疑是非常低下的，更高效的方法相信大家也都知道，就是在首页的索引里先查找「先」对应的页数，然后直接跳到相应的页面查找，这样查询时间大大减少了，可以是 O(1)。

数据库中的索引也是类似的，通过索引定位到要读取的页，大大减少了需要扫描的行数，能极大地提升效率。简而言之，索引主要有以下几个作用：

1. 即上述所说，索引能极大地减少扫描行数。

2. 索引可以帮助服务器避免排序和临时表。

3. 索引可以将随机 IO 变成顺序 IO。

第一点上文已经解释了，我们来看下第二点和第三点。

2） 索引可以帮助服务器避免排序和临时表

先来看第二点，假设我们不用索引，试想运行如下语句：

select * from user order by age desc;

MySQL 的执行流程是这样的：扫描所有行，把所有行加载到内存后，按 age 排序生成一张临时表，再把这表结果返回给客户端。更糟的情况是，如果这张临时表的大小大于 tmp_table_size 的值（默认为 16 M），内存临时表会转为磁盘临时表，性能会更差。

如果加了索引，因为索引本身是有序的，所以从磁盘读的行数据本身就是按 age 排序好的，也就不会生成临时表（空间消耗）和额外排序（CPU 消耗），无疑提升了性能。

3）索引可以将随机 I/O 变成顺序 I/O

再来看随机 I/O 和顺序 I/O，先来解释下这两个概念。相信不少人应该吃过旋转火锅，服务员把一盘盘的菜放在旋转传输带上，然后等到这些菜转到我们面前，我们就可以拿到菜了。假设转一圈需要 4 分钟，则最短等待时间是 0（即菜就在你跟前），最长等待时间是 4 分钟（菜刚好在你跟前错过），那么平均等待时间即为 2 分钟。

假设我们现在要拿四盘菜，这四盘菜随机分配在传输带上，则可知拿到这四盘菜的平均等待时间是 8 分钟（随机 I/O），如果这四盘菜刚好紧邻着排在一起，则等待时间只需 2 分钟（顺序 I/O）。

上述中传输带就类比磁道，磁道上的菜就类比扇区（sector）中的信息，扇区是硬盘读写的基本单位；而磁盘块（block）是由多个相邻的扇区组成的，是操作系统读取的最小单元。这样如果信息能以 block 的形式聚集在一起，就能极大减少磁盘 I/O 时间，这就是顺序 I/O 带来的性能提升，下文中我们将会看到 B+ 树索引就起到这样的作用。 

而如果信息在一个磁道中分散地分布在各个扇区中，或者分布在不同磁道的扇区上（寻道时间是随机 I/O 主要瓶颈所在），将会造成随机 I/O，影响性能。

我们来看一下一个随机 I/O 的时间分布：

1. Seek Time（寻道时间）：磁头移动到扇区所在的磁道。

2. Rotational Latency（旋转时延）：完成步骤 1 后，磁头移动到同一磁道扇区对应的位置所需求时间。

3. Transfer Time（传输时间）：从磁盘读取信息传入内存时间。

MySQL 的数据是一行行存储在磁盘上的，并且这些数据并非物理连续地存储，这样的话要查找数据就无法避免随机在磁盘上读取和写入数据。对于 MySQL 来说，当出现大量磁盘随机 I/O 时，大部分时间都被浪费到寻道上（大概占据随机 I/O 时间的 40%）。

随机 I/O 和顺序 I/O 大概相差百倍 (随机 I/O：10 ms/ page，顺序 I/O：0.1ms / page)，可见顺序 I/O 性能之高，索引带来的性能提升显而易见！

2. MySQL优化器做选择

注意，即使 SQL 完全符合索引生效的场景，考虑到实际数据量等原因，最终是否使用索引还要看 MySQL 优化器判断全表扫描和走索引的成本哪个更低（当然也可以在 SQL 中写明强制走某个索引）。

3. 建立索引也有不好之处

1. 索引需要占用物理空间，因此也增加了磁盘存储空间。
2. 索引虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改操作，MySQL 不仅要保存数据，还要保存或者更新对应的索引文件。

也就是说创建索引和维护索引要耗费空间和时间，这种耗费随着数据量的增加而增加，因此索引也不是越多越好，在数据量小的情况下则没必要建索引。

4. 索引不适合的场景

• 数据量少
• 数据更新频繁
• 区分度低的字段（如性别）

2、索引类型 

1. 索引类型 

• 单列索引：即一个索引只包含单个列。一个表可以有多个单列索引，但这不是组合索引。
• 联合索引：即一个索引包含多个列。
• 普通索引/二级索引（INDEX/KEY）：最基本的索引，没有任何限制。
• 唯一索引（UNIQUE）：与“普通索引”类似，不同的是：索引列的值必须唯一，但允许有 NULL。
• 主键索引（PRIMARY）：一种特殊的唯一索引，不允许为 NULL。
• 全文索引（FULLTEXT）：仅可用于 MyISAM 表， 主要用于在长篇文章中检索关键字信息。针对较大的数据，生成全文索引很耗时好空间。

2. MySQL索引语法

1）创建索引

CREATE TABLE table_name[col_name data type][unique|fulltext][index|key][index_name](col_name[length])[asc|desc]

参数介绍：

• unique|fulltext 为可选参数，分别表示唯一索引、全文索引。
• index 和 key 为同义词，两者作用相同，用来指定创建索引。
• col_name 为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择。
• index_name 指定索引的名称，为可选参数，如果不指定，默认 col_name 为索引值。
• length 为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度。
• asc 或 desc 指定升序或降序的索引值存储。

2）查看索引

-- 方式1 
show index from `table_name`; 

-- 方式2 
show keys from `table_name`;

查询结果字段解释：

• Table：表的名称。
• Non_unique：如果索引不能包括重复词，则为 0；如果可以，则为 1。
• Key_name：索引的名称。
• Seq_in_index：索引中的列序号，从 1 开始。
• Column_name：列名称。
• Collation：列以什么方式存储在索引中。在 MySQL 中，有“A”（升序）或“NULL”（无分类）。
• Cardinality：索引中唯一值的数目的估计值。通过运行 ANALYZE TABLE 或 myisamchk -a 可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL 使用该索引的机 会就越大。
• Sub_part：如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为 NULL。
• Packed：指示关键字如何被压缩。如果没有被压缩，则为 NULL。
• Null：如果列含有 NULL，则含有 YES；如果没有，则该列含有 NO。
• Index_type：用过的索引方法（BTREE、FULLTEXT、HASH、RTREE）。

3）删除索引

-- 方式1
ALTER TABLE `table_name` DROP INDEX index_name;

-- 方式2
drop INDEX indexname on `table_name`;

3. 索引类型详解

1）普通索引/二级索引（INDEX/KEY）

普通索引是最基本的索引，它没有任何限制，允许被索引的数据列包含重复的值。

普通索引的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件（WHERE column =）或排序条件（ORDER BY column）中的数据列创建索引。

只要有可能，就应该选择一个数据最整齐、最紧凑的数据列（如一个整数类型的数据列）来创建索引。

普通索引有以下几种创建方式：

-- 方式1：直接创建索引
CREATE INDEX index_name ON table(column(length));

-- 方式2：修改表结构的方式添加索引
ALTER TABLE table_name ADD INDEX index_name (column(length));

-- 方式3：创建表的时候同时创建索引
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `title` char(255) NOT NULL,
    `content` text,
    `time` int(10) DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX index_name (title(length))  -- INDEX 还可以替换为 KEY
);

2）唯一索引（UNIQUE）

与前面的普通索引类似，不同的就是：索引列的值必须唯一（包括空字符串），但允许有空值（NULL）。

唯一索引可以有多个，如果是组合索引，则列值的组合必须唯一。

如果在一个列上同时建唯一索引和普通索引的话，Mysql 会自动选择唯一索引。

MySQL 会在有新记录插入数据表时，会自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过；如果是，MySQL 将拒绝插入那条新记录。也就是说，唯一索引可以保证数据记录的唯一性。事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。

唯一索引和普通索引使用的结构都是 B-tree，执行时间复杂度都是 O(logn)。

唯一索引有以下几种创建方式：

-- 方式1：创建唯一索引
CREATE UNIQUE INDEX indexName ON table(column(length));
‍
-- 方式2：修改表结构
ALTER TABLE table_name ADD UNIQUE indexName ON (column(length));
‍
-- 方式3：创建表的时候直接指定
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `title` char(255) CHARACTER NOT NULL,
    `content` text CHARACTER NULL,
    `time` int(10) NULL DEFAULT NULL,
    UNIQUE indexName (title(length))
);

3）主键（PRIMARY）

是一种特殊的唯一索引，一个表只能有一个主键，主键列不允许有空值（NULL）。

• 主键可作外键，唯一索引不可。
• 主键不可为空（NULL），唯一索引可以。

主键也可以包含个字段（这叫联合主键），所以可以在主键的其中一个字段建唯一索引。

主键的优势：

1. 表数据的存储在磁盘中是按照主键顺序存放的，所以使用主键查询数据时速度最快。
2. 主键具有约束性，可以作为一定业务场景的数据校验约束。
3. 建议：建表时一定要带有主键，后续优化效果最好。

在定义主键时会自动创建唯一索引：

CREATE TABLE `table` (
    `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `title` char(255) NOT NULL 
);

4）联合索引

联合索引指一个索引中包含了多个字段。

使用联合索引时遵循最左匹配原则：当创建 (a,b,c) 联合索引时，相当于创建了 (a) 单列索引、(a,b) 联合索引以及 (a,b,c) 联合索引。要想索引生效的话，只能使用 a 和 a,b 和 a,b,c 这三种组合（a,c 组合也可以，但实际上只用到了 a 的单列索引，c 并没有用到）。

通俗理解：联合索引的结构与电话簿类似，人名由姓和名构成，电话簿首先按姓氏对进行排序，然后按名字对有相同姓氏的人进行排序。如果您知道姓，电话簿将非常有用；如果您知道姓和名，电话簿则更为有用，但如果只知道名不知道姓，电话簿将没有用处。

所以说创建联合索引时，应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时，联合索引非常有用；仅对后面的任意列执行搜索时，联合索引则没有用处。

示例：

创建一个表，包括 c1,c2,c3,c4,c5 字段：

创建联合索引 (c1,c2,c3,c4)： 

只有 where 的情况：遵从最左原则，条件必须有左边的字段，才会用到索引，中间如果断开了，则后面的索引都不会用到：

-- 这种情况因为 c3 没有在条件中，所以只会用到 c1,c2 索引
where c1 = '1' and c2 = '1' and c4 = '1'

特殊情况：使用范围条件或 like 时，也会使用到该处的索引，但后面的索引都不会用到：

-- 这种情况从 c2 处已经断开，会使用到 c1,c2 索引，不会再使用到后面的 c3,c4 索引
where c1 = '1' and c2 > '1' and c3 = '1'

group by 和 order by 也遵从最左原则，可以看做继承 where 的条件顺序，但需要 where 作为基础铺垫，即如果没有 where 语句，单纯的 group by 或 order by 也是不会使用任何索引的，并且需要和联合索引顺序一致才会使用索引。

select ... group | order by c1  -- 由于没有 where 的铺垫，不使用任何索引
select ... where c1 = '1' group | order by c2  -- 使用 c1,c2 索引
select ... where c1 = '1' group | order by c2, c3  -- 使用 c1,c2,c3 索引
select ... where c1 = '1' group | order by c3  -- 只使用 c1 索引
select ... where c1 = '1' group | order by c3, c2  -- 只使用 c1 索引
select ... where c1 > '1' group | order by c2  -- 范围搜索会断掉连接，所以也只会使用 c1 索引
select ... where c1 = '1' group by c2 order by c3  -- 使用 c1,c2 索引

联合索引的优化建议：

1. 多个单列索引在（简单查询中）多条件查询时只会生效其中一个索引，所以多条件联合查询时最好建联合索引；并且联合索引相比多个单列索引，更节省索引的存储空间（索引建立得越多就越占磁盘空间，在更新数据的时候速度会更慢）。
2. 在创建联合索引的时候因该把频繁使用、区分度高的列放在前面，频繁使用代表索引的利用率高，区分度高代表数据量的筛选力度大。
3. 如果 WHERE 条件均为联合索引的字段，也要尽量按照联合索引的顺序来。如果不按照顺序来，索引也同样会用到，但是在执行前，SQL 优化器也会将条件调整为联合索引的顺序，既然可以直接避免这种情况，就没必要再让 SQL 优化器去处理，毕竟处理也是有开销的。
4. 当前索引存在频繁使用作为返回字段的列，这个时候就可以考虑当前列是否可以加入到当前已经存在索引上，使其查询语句可以使用到覆盖索引。

联合索引的创建方式：

-- 方式1：直接创建索引
CREATE INDEX index_name ON table(column1(length), column2(length), column3(length));

-- 方式2：修改表结构的方式添加索引
ALTER TABLE table_name ADD INDEX index_name (column1(length), column2(length), column3(length));

-- 方式3：创建表的时候同时创建索引
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `title` char(255) NOT NULL,
    `content` text,
    `time` int(10) DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX index_name (column1(length), column2(length), column3(length))  -- INDEX 还可以替换为 KEY
);

5）全文索引（FULLTEXT）

主要用来查找文本中的关键字，而不是直接与索引中的值相比较。

fulltext 索引跟其它索引大不相同，它更像是一个搜索引擎，主要为了解决 like %keyword% 这类查询的匹配问题。

它可以在 create table、alter table、create index 时使用，不过目前只有 char、varchar、（tiny、medium、long）text 列上可以创建全文索引。

值得一提的是，在数据量较大时候，先将数据放入一个没有全文索引的表中，然后再用 CREATE index 创建 fulltext 索引，要比先为一张表建立 fulltext 索引然后再将数据写入的速度快很多。

如下图所示，fulltext 索引一般配合 match() against() 操作使用：

存储引擎：

• MyISAM 支持 FULLTEXT 类型的全文索引。
• Innodb 不支持 FULLTEXT 类型的全文索引，但是 Innodb 可以使用 Sphinx 插件支持全文索引，并且效果更好（Sphinx 是一个开源软件，提供多种语言的 API 接口，可以优化 Mysql 的各种查询）。

全文索引有以下几种创建方式：

--方式1：创建表的适合添加全文索引
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    FULLTEXT (content)
);

--方式2：修改表结构添加全文索引
ALTER TABLE article ADD FULLTEXT index_content(content);

--方式3：直接创建索引
CREATE FULLTEXT INDEX index_content ON article(content);

3、索引数据结构

索引一般需要解决下面两个问题：

1. 等值查询，比如 select * from user where id=1234
2. 范围查询，比如 select * from user where id > 1234 and id < 2345

1）哈希表

在 Java 中的 HashMap、TreeMap 就是 Hash 表结构，以键值对的方式存储数据。

假设我们对名字建立了哈希索引，则查找过程如下图所示：

对于每一行数据，存储引擎都会对所有的索引列（上图中的 name 列）计算一个哈希值（上图散列表的位置），散列表里的每个元素指向数据行的指针。由于索引自身只存储对应的哈希值，所以索引的结构十分紧凑，这让哈希索引查找速度非常快！

当然了，哈希表的劣势也是比较明显的，不支持区间查找（需要挨个数据遍历，效率低），所以更多的时候哈希表是与 B+ 树等一起使用的。在 InnoDB 引擎中就有一种名为「自适应哈希索引」的特殊索引，当 InnoDB 注意到某些索引值使用非常频繁时，就会在内存中基于 B+ 树索引之上再创建哈希索引，这样也就让 B+ 树索引也有了哈希索引的快速查找等优点，这是完全自动的内部的行为，用户无法控制或配置，不过如果有必要，可以关闭该功能。

显然，单纯的哈希索引并不适合作为经常需要范围查找的数据库使用。

2）B+ 树

为什么 B+ 树成为了数据库索引的主流实现？我们可以结合下述各种树结构的优劣势进行分析推导。

① 二叉查找树（BST）

二叉查找树（BST）是综合了顺序表（查找效率高）和链表（增删效率高）优势的折中方案，其特点是：每个节点最多有 2 个子节点，且左子树和右子树数据顺序是左小右大。

这个特点就是为了保证每次查找都可以这折半而减少 I/O 次数，但是二叉树就很考验第一个根节点的取值，因为很容易在这个特点下出现我们并发想发生的情况“树不分叉了”，这就很难受很不稳定。

显然这种不稳定的情况，是需要我们在选择设计上必然要避免的。  

② 平衡二叉查找树（AVL）

平衡二叉查找树是采用二分法思维，平衡二叉查找树除了具备二叉查找树的特点，最主要的特征是树的左右两个子树的层级最多相差 1。在插入删除数据时通过左旋/右旋操作保持二叉树的平衡，不会出现左子树很高、右子树很矮的情况。

使用平衡二叉查找树查询的性能接近于二分查找法，时间复杂度是 O(logn)。如下图所示，查询 id=6 时，只需要两次 I/O。

就这个特点来看，可能觉得这就很好了，可以达到二叉树的理想的情况。然而依然存在一些问题： 

1. 时间复杂度和树高相关。树有多高就需要检索多少次，每个节点的读取，都对应一次磁盘 IO 操作。即树的高度等于每次查询数据时磁盘 I/O 操作的次数。磁盘每次寻道时间为 10ms，在表数据量大时，查询性能就会很差。（一百万的数据量，logn 约等于 20 次磁盘 I/O，时间为 20*10=0.2s）
2. 平衡二叉树不支持范围查询的快速查找，范围查询时需要从根节点多次遍历，查询效率不高。

3） B 树：改造二叉树

MySQL 的数据是存储在磁盘文件中的，查询处理数据时，需要先把磁盘中的数据加载到内存中，磁盘 I/O 操作非常耗时，所以我们优化的重点就是尽量减少磁盘 I/O 操作。访问二叉树的每个节点就会发生一次 I/O，如果想要减少磁盘 I/O 操作，就需要尽量降低树的高度。那如何降低树的高度呢？

假如 key 为 bigint = 8 字节，每个节点有两个指针，每个指针为 4 个字节，一个节点占用的空间 16 个字节（8+4*2=16）。

因为在 MySQL 的 InnoDB 存储引擎一次 I/O 会读取的一页（默认一页为 16K）的数据量，而二叉树一次 I/O 有效数据量只有 16 字节，空间利用率极低。为了最大化利用一次 I/O 空间，一个简单的想法是在每个节点存储多个元素，在每个节点尽可能多的存储数据。

当每个节点可以存储 1000 个索引（16k/16=1000）时，这样就可以将二叉树改造成多叉树，通过增加树的叉树，将树从高瘦变为矮胖。此时构建一百万条数据，树的高度只需要 2 层就可以（1000*1000=一百万），也就是说只需要 2 次磁盘 I/O 就可以查询到数据。磁盘 I/O 次数变少了，查询数据的效率也就提高了。

① B 树结构

这种数据结构我们称为 B 树，B 树是一种多叉平衡查找树，如下图主要特点：

1. B 树的节点中存储着多个元素，每个节点有多个分叉。
2. 节点中的元素包含键值和数据，节点中的键值从大到小排列。也就是说，在所有的节点都储存数据。
3. 父节点当中的元素不会出现在子节点中。
4. 所有的叶子节点都位于同一层，且叶子节点之间没有指针连接。

②  在 B 树中查询数据

假如我们查询值等于 10 的数据。则查询路径为磁盘块 1 -> 磁盘块 2 -> 磁盘块 5。

1. 第一次磁盘 I/O：将磁盘块1加载到内存中，在内存中从头遍历比较，10 < 15，走左路，到磁盘寻址磁盘块 2。
2. 第二次磁盘 I/O：将磁盘块2加载到内存中，在内存中从头遍历比较，7 < 10，到磁盘中寻址定位到磁盘块 5。
3. 第三次磁盘 I/O：将磁盘块5加载到内存中，在内存中从头遍历比较，10 = 10，找到 10，取出 data，如果 data 存储的是行记录，取出 data，查询结束。如果存储的是磁盘地址，还需要根据磁盘地址到磁盘中取出数据，查询终止。

相比二叉平衡查找树，在整个查找过程中，虽然数据的比较次数并没有明显减少，但是磁盘 I/O 次数会大大减少。同时，由于我们的比较是在内存中进行的，比较的耗时可以忽略不计。

看到这里一定觉得 B 树就很理想了，但是前辈们会告诉你依然存在可以优化的地方：

1. B 树也不支持范围查询的快速查找，你想想这么一个情况如果我们想要查找 10 和 35 之间的数据，查找到 15 之后，还需要多次回到根节点重新遍历查找剩余数据，其查询效率有待提高。
2. 如果 data 存储的是行记录，行的大小随着列数的增多，所占空间会变大。这时，一个页中可存储的数据量就会变少，树相应就会变高，磁盘 I/O 次数就会变大。

4）B+ 树：改造 B 树

MySQL 在 B 树的基础上继续改造，使用 B+ 树构建索引。作为 B 树的升级版，B+ 树和 B 树最主要的区别在于非叶子节点是否存储数据的问题。

• B 树：非叶子节点和叶子节点都会存储数据。
• B+ 树：只有叶子节点才会存储数据，非叶子节点至存储键值。叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表。

B+ 树的最底层叶子节点包含了所有的索引项。从图上可以看到，B+ 树在查找数据的时候，由于数据都存放在最底层的叶子节点上，所以每次查找都需要检索到叶子节点才能查询到数据。

所以在需要查询数据的情况下每次的磁盘的 I/O 跟树高有直接的关系，但是从另一方面来说，由于数据都被放到了叶子节点，因此非叶子节点的磁盘块所存放的索引数量是会跟着增加的。相对于 B 树来说，B+ 树的树高理论上情况下是比 B 树要矮的。

也存在索引覆盖查询（后文会讲述）的情况，在索引中数据满足了当前查询语句所需要的全部数据，此时只需要找到索引即可立刻返回，不需要检索到最底层的叶子节点。

示例 1：等值查询

假如我们查询值等于 9 的数据，则查询路径为磁盘块 1 -> 磁盘块 2 -> 磁盘块 6。

1. 第一次磁盘 I/O：将磁盘块 1 加载到内存中，在内存中从头遍历比较， 9 < 15，走左路，到磁盘寻址磁盘块 2。
2. 第二次磁盘 I/O：将磁盘块 2 加载到内存中，在内存中从头遍历比较， 7 < 9 < 12，到磁盘中寻址定位到磁盘块 6。
3. 第三次磁盘 I/O：将磁盘块 6 加载到内存中，在内存中从头遍历比较，在第三个索引中找到 9，取出 data，如果 data 存储的行记录，取出 data，查询结束。如果存储的是磁盘地址，还需要根据磁盘地址到磁盘中取出数据，查询终止。（这里需要区分的是在 InnoDB 中 data 存储的为行数据，而 MyIsam 中存储的是磁盘地址。）

示例 2：范围查询

假如我们想要查找 9 和 26 之间的数据。则查找路径为磁盘块 1 -> 磁盘块 2 -> 磁盘块 6 -> 磁盘块 7。

1. 首先查找值等于 9 的数据，将值等于 9 的数据缓存到结果集。这一步和前面等值查询流程一样，发生了三次磁盘 I/O。查找到 9 之后，底层的叶子节点是一个有序列表，我们从磁盘块 6，键值 9 开始向后遍历筛选所有符合筛选条件的数据。
2. 第四次磁盘I/O：根据磁盘 6 后继指针到磁盘中寻址定位到磁盘块 7，将磁盘 7 加载到内存中，在内存中从头遍历比较，9 < 25 < 26，9 < 26 <= 26，将 data 缓存到结果集。由于主键具备唯一性（后面不会有 <= 26的数据），不需再向后查找，查询终止。将结果集返回给用户。

可以看到 B+ 树可以保证等值和范围查询的快速查找，因此 MySQL 的索引就采用了 B+ 树的数据结构。

一般情况下，3-4 层的 B+ 树足以支撑千万行的数据量存储，当数据量再往上时就已经需要考虑分库分表了。

4、MySQL索引实现

介绍完了索引数据结构，那肯定是要带入到 MySQL 里面看看真实的使用场景的，所以这里分析 MySQL 的两种存储引擎的索引实现：MyISAM 索引和 InnoDB 索引。

1）MyISAM 索引

以一个简单的 user 表为例。user 表存在两个索引，id 列为主键索引，age 列为普通索引：

CREATE TABLE `user`
(
  `id`       int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  `age`      int(11)     DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_age` (`age`) USING BTREE
) ENGINE = MyISAM
  AUTO_INCREMENT = 1
  DEFAULT CHARSET = utf8;

MyISAM 的数据文件和索引文件是分开存储的。MyISAM 使用 B+ 树构建索引树时，叶子节点中存储的键值为索引列的值，数据为索引所在行的磁盘地址。 

① 主键索引

表 user 的索引存储在索引文件 user.MYI 中，数据文件存储在数据文件 user.MYD 中。

下面简单分析下查询时的磁盘 I/O 情况。

② 根据主键等值查询数据 

select * from user where id = 28;

1. 先在主键树中从根节点开始检索，将根节点加载到内存，比较 28 < 75，走左路。（1 次磁盘 I/O）
2. 将左子树节点加载到内存中，比较 16 < 28 < 47，向下检索。（1 次磁盘 I/O）
3. 检索到叶节点，将节点加载到内存中遍历，比较 16 < 28、18 < 28、28 = 28，查找到值等于 30 的索引项。（1 次磁盘 I/O）
4. 从索引项中获取磁盘地址，然后到数据文件 user.MYD 中获取对应整行记录。（1 次磁盘 I/O）
5. 将记录返给客户端。

磁盘 I/O 次数 = 3 次（索引检索） + 1 次（记录数据检索）

③ 根据主键范围查询数据

select * from user where id between 28 and 47;

1. 先在主键树中从根节点开始检索，将根节点加载到内存，比较 28 < 75，走左路。（1 次磁盘 I/O）
2. 将左子树节点加载到内存中，比较 16 < 28 < 47，向下检索。（1 次磁盘 I/O）
3. 检索到叶节点，将节点加载到内存中遍历比较 16 < 28、18 < 28、28 = 28 < 47。查找到值等于 28 的索引项。根据磁盘地址从数据文件中获取行记录缓存到结果集中。（1 次磁盘 I/O）。由于我们的查询语句是范围查找，因此需要向后遍历底层叶子链表，直至到达最后一个不满足筛选条件。
4. 向后遍历底层叶子链表，将下一个节点加载到内存中，遍历比较，28 < 47 = 47，根据磁盘地址从数据文件中获取行记录缓存到结果集中。（1 次磁盘 I/O）
5. 最后得到两条符合筛选条件，将查询结果集返给客户端。

磁盘 I/O 次数 = 4 次（索引检索）+1 次（记录数据检索）

PS：以上分析仅供参考，MyISAM 在查询时，会将索引节点缓存在 MySQL 缓存中，而数据缓存依赖于操作系统自身的缓存，所以并不是每次都是走的磁盘，这里只是为了分析索引的使用过程。 

④  辅助索引（二级索引）

在 MyISAM 中，辅助索引和主键索引的结构是一样的，没有任何区别，叶子节点的数据存储的都是行记录的磁盘地址。只是主键索引的键值是唯一的，而辅助索引的键值可以重复。

由于辅助索引的键值不唯一，可能存在多个拥有相同的记录，所以即使是等值查询，也需要按照范围查询的方式在辅助索引树中检索数据。

2）InnoDB 索引

① 聚簇索引（主键索引）

每个 InnoDB 表都有一个聚簇索引 ，聚簇索引使用 B+ 树构建，叶子节点存储的数据是整行记录。

一般情况下，聚簇索引等同于主键索引，当一个表没有创建主键索引时，InnoDB 会自动创建一个 ROWID 字段来构建聚簇索引。InnoDB 创建索引的具体规则如下：

1. 在表上定义主键 PRIMARY KEY，InnoDB 将主键索引用作聚簇索引。
2. 如果表没有定义主键，InnoDB 会选择第一个不为 NULL 的唯一索引列用作聚簇索引。
3. 如果以上两个都没有，InnoDB 会使用一个 6 字节长整型的隐式字段 ROWID 字段构建聚簇索引，且该 ROWID 字段会在插入新行时自动递增。

除聚簇索引之外的所有索引都称为辅助索引（二级索引）。在 InnoDB 中，辅助索引中的叶子节点存储的数据是该行的主键值。在检索时，InnoDB 使用此主键值在聚簇索引中搜索行记录。

聚簇索引中主键的逻辑顺序决定了表记录的物理存储顺序；而辅助索引的逻辑顺序与索引值的物理存储顺序不同。

这里以 user_innodb 为例，user_innodb 的 id 列为主键，age 列为普通索引：

CREATE TABLE `user_innodb`
(
  `id`       int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  `age`      int(11)     DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_age` (`age`) USING BTREE
) ENGINE = InnoDB;

InnoDB 的数据和索引存储在一个文件 t_user_innodb.ibd 中。InnoDB 的数据组织方式是聚簇索引。

主键索引的叶子节点会存储数据行，辅助索引只会存储主键值。

等值查询数据： 

select * from user_innodb where id = 28;

1. 先在主键树中从根节点开始检索，将根节点加载到内存，比较 28<75，走左路。（1 次磁盘 I/O）
2. 将左子树节点加载到内存中，比较 16 < 28 < 47，向下检索。（1 次磁盘 I/O）
3. 检索到叶节点，将节点加载到内存中遍历，比较 16 < 28、18 < 28、28 = 28。查找到值等于 28 的索引项，直接可以获取整行数据。将改记录返回给客户端。（1 次磁盘 I/O）

磁盘 I/O 次数：3 次

② 非聚簇索引（辅助索引）

除聚簇索引之外的所有索引都称为辅助索引，InnoDB 辅助索引的叶子节点只会存储主键值而非数据行。

由于辅助索引的键值不唯一，可能存在多个拥有相同的记录，所以即使是等值查询，也需要按照范围查询的方式在辅助索引树中检索数据。

以表 user_innodb 的 age 列为例，age 索引的索引结果如下图：

底层叶子节点的按照 (age, id) 的顺序排序，先按照 age 列从小到大排序，age 列相同时按照 id 列从小到大排序。

使用辅助索引需要检索两遍索引：首先检索辅助索引获得主键，然后使用主键到聚簇索引中检索获得记录。

等值查询数据：

select * from t_user_innodb where age=19;

根据在辅助索引树中获取的主键 id，到聚簇索引树中检索数据的过程称为回表查询。

磁盘 I/O 次数：辅助索引 3 次 + 获取记录回表 3 次 = 6 次

③ 组合索引 

以表 abc_innodb 为例，id 为主键索引，创建了一个联合索引 idx_abc(a, b, c)： 

复制代码
CREATE TABLE `abc_innodb`
(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a`  int(11)     DEFAULT NULL,
  `b`  int(11)     DEFAULT NULL,
  `c`  varchar(10) DEFAULT NULL,
  `d`  varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_abc` (`a`, `b`, `c`)
) ENGINE = InnoDB;

组合索引的数据结构：

组合索引的查询过程：

select * from abc_innodb where a = 13 and b = 16 and c = 4;

最左匹配原则：最左前缀匹配原则和组合索引的索引存储结构和检索方式是有关系的。

在组合索引树中，最底层的叶子节点按照第一列 a 列从左到右递增排列，但是 b 列和 c 列是无序的，b 列只有在 a 列值相等的情况下小范围内递增有序，而 c 列只能在 a、b 两列相等的情况下小范围内递增有序。

就像上面的查询，B+ 树会先比较 a 列来确定下一步应该搜索的方向，往左还是往右。如果 a 列相同再比较 b 列。但是如果查询条件没有 a 列，B+ 树就不知道第一步应该从哪个节点查起。

可以说创建的 idx_abc(a,b,c) 索引，相当于创建了 (a)、(a, b)、(a, b, c) 三个单列索引。

注意，在使用组合索引查询时，MySQL 会一直向右匹配，直至遇到范围查询（>、<、between、like）就停止匹配。

④ 覆盖索引

覆盖索引并不是索引结构，而是一种很常用的优化手段。

因为在使用辅助索引的时候，我们只可以拿到主键值，相当于获取到数据后还需要再根据主键查询聚簇索引才获取到最终结果数据。但是试想下这么一种情况，在上面 abc_innodb 表中的组合索引查询时，如果我只需要 a、b、c 三个字段，那是不是意味着我们查询到组合索引的叶子节点就可以直接返回了，而不需要回表。这种情况就是覆盖索引。

使用到覆盖索引的标识：

未使用到覆盖索引： 

优化示例：使用覆盖索引，避免回表。

在 InnoDB 的存储引擎中，使用辅助索引查询的时候，因为辅助索引叶子节点保存的数据不是当前记录的数据而是当前记录的主键索引，索引如果需要获取当前记录完整数据就必然需要根据主键值从主键索引继续查询。这个过程我们称为回表查询。回表必然是会消耗性能影响性能。

那如何避免呢？使用索引覆盖。

举个例子：现有 User 表( id(PK), name(key), sex, address, hobby...)

select id, name, sex from user where name = 'zhangsan'; 

如果上述语句在业务上频繁使用到，而 user 表的其他字段使用频率远低于它，在这种情况下，如果我们在建立 name 字段的索引的时候，不应使用单一索引，而应使用联合索引(name, sex)。这样的话在执行这个查询语句时，是不是直接根据辅助索引查询到的结果就可以获取当前语句的完整数据了，也就可以有效地避免了回表再获取 sex 的数据。 

这就是一个典型的使用覆盖索引的优化策略减少回表的情况。

3）索引案例

现有一张数据库表，表里的字段只有主键索引 (id) 和联合索引 (a, b, c)，然后执行 select * from t where c = 0; 发现这条语句发现走的是索引，在此产生了两点疑惑：

1. where c 这个条件并不符合组合索引的最左匹配原则，怎么查询的时候走了组合索引呢？
2. 在这个数据表加了非索引字段，执行同样的查询语句后，怎么变成走全表扫描呢？

问题一 解答：

首先，这张表的字段没有「非索引」字段，所以「select *」相当于「select id, a, b, c」，由于这个查询的内容和条件都在组合索引树里，因为联合索引树的叶子节点包含「索引列+主键」，所以查组合索引树就能查到全部结果了，即覆盖索引。

但是执行计划里的 type 是 index，这代表着是通过全扫描组合索引树的方式查询到数据的，这是因为 where c 并不符合联合索引最左匹配原则。而如果写了个符合最左原则的 select 语句，那么 type 就是 ref，这个效率就比 index 全扫描要高一些。

那为什么选择全扫描组合索引树，而不扫描全表（聚簇索引树）呢？

因为组合索引树的记录比聚簇索引树的记录要少得多，而且这个 select * 不用执行回表操作，所以直接遍历组合索引树的代价要比遍历聚集索引树要小得多，因此 MySQL 执行器选择了全扫描组合索引树。

问题二 解答：

因为加了其他字段后，select * from t where c = 0; 查询的内容无法直接在组合索引树里找到，且查询条件也不符合最左匹配原则，这样既不能覆盖索引也不能执行回表操作，所以这时只能通过扫描全表（type 为 all，key 为 null）来查询到所有的数据。 

5、高性能索引策略

不同的索引设计选择能对性能产生很大的影响，有人可能会发现生产中明明加了索引却不生效，有时候加了虽然生效但对搜索性能并没有提升多少，对于多列联合索引，哪列在前，哪列在后也是有讲究的，我们一起来看看。

加了索引，为何却不生效？加了索引却不生效可能会有以下几种原因：

1）索引列是表达式或函数的一部分

如下 SQL：

SELECT book_id FROM BOOK WHERE book_id + 1 = 5;

或者：

SELECT book_id FROM BOOK WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(gmt_create) <= 10;

上述两个 SQL 虽然在列 book_id 和 gmt_create 设置了索引 ，但由于它们是表达式或函数的一部分，导致索引无法生效，最终全表扫描。

2）隐式类型转换

以上两种情况相信不少人都知道索引不能生效，但下面这种隐式类型转换估计会让不少人栽跟头，来看下面这个例子。

假设有以下表：

CREATE TABLE `tradelog` (
  `id` int(11) NOT NULL,
  `tradeid` varchar(32) DEFAULT NULL,
  `operator` int(11) DEFAULT NULL,
  `t_modified` datetime DEFAULT NULL,
   PRIMARY KEY (`id`),
   KEY `tradeid` (`tradeid`),
   KEY `t_modified` (`t_modified`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

执行 SQL：

SELECT * FROM tradelog WHERE tradeid=110717;

交易编号 tradeid 上有索引，但用 EXPLAIN 执行却发现使用了全表扫描，为啥呢？因为 tradeid 的类型是 varchar(32)，而此 SQL 用 tradeid 与一个数字类型进行比较，发生了隐式转换，即会隐式地将字符串转成整型，如下：

SELECT * FROM tradelog WHERE CAST(tradid AS signed int) = 110717;

这样也就触发了上文中第一条的规则 ，即：索引列不能是函数的一部分。

3）隐式编码转换

这种情况非常隐蔽，来看下面这个例子：

CREATE TABLE `trade_detail` ( 
 `id` int(11) NOT NULL, 
 `tradeid` varchar(32) DEFAULT NULL, 
 `trade_step` int(11) DEFAULT NULL, /*操作步骤*/ 
 `step_info` varchar(32) DEFAULT NULL, /*步骤信息*/ 
   PRIMARY KEY (`id`), KEY `tradeid` (`tradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

trade_detail 是交易详情， tradelog 是操作此交易详情的记录，现在要查询 id=2 的交易的所有操作步骤信息，则我们会采用如下方式：

SELECT d.* FROM tradelog l, trade_detail d WHERE d.tradeid=l.tradeid AND l.id=2;

由于 tradelog 与 trade_detail 这两个表的字符集不同，且 tradelog 的字符集是 utf8mb4，而 trade_detail 字符集是 utf8，utf8mb4 是 utf8 的超集，所以会自动将 utf8 转成 utf8mb4。即上述语句会发生如下转换：

SELECT d.* FROM tradelog l, trade_detail d WHERE (CONVERT(d.traideid USING utf8mb4)))=l.tradeid AND l.id=2;

自然也就触碰了「索引列不能是函数的一部分」这条规则。怎么解决呢？第一种方案当然是把两个表的字符集改成一样，如果业务量比较大，生产上不方便改的话，还有一种方案是手动用函数把 utf8mb4 转成 utf8（当然从超集向子集转换是有数据截断风险的），如下：

SELECT d.* FROM tradelog l, trade_detail d WHERE d.tradeid=CONVERT(l.tradeid USING utf8) AND l.id=2; 

这样索引列就生效了。

4）使用 order by 造成的全表扫描

SELECT * FROM user ORDER BY age DESC;

上述语句在 age 上加了索引，但依然造成了全表扫描，这是因为我们使用了 SELECT * 导致回表查询，MySQL 认为回表的代价比全表扫描更大，所以不选择使用索引。

如果想使用到 age 的索引，我们可以用覆盖索引来代替：

SELECT age FROM user ORDER BY age DESC;

 或者加上 limit 的条件（条数较少）：

SELECT * FROM user ORDER BY age DESC limit 10;

这样就能利用到索引。

5）like 通配符可能会导致索引失效

当 like 查询以 % 开头时，会导致索引失效。解决办法有两种：

将 % 移到后面，如：

select * from `user` where `name` like '李%';

利用覆盖索引来命中索引：

select name from `user` where `name` like '%李%';

6）where 语句中包含 or 时，可能会导致索引失效

使用 or 并不是一定会使索引失效，你需要看 or 左右两边的查询列是否有创建索引。

假设 user 表中的 user_id 列为主键，age 列没有索引。

索引有效（可以使用 explain 验证下）：

select * from `user` where user_id = 1 or user_id = 2;

and 和 or 的区别：

select * from `user` where user_id = 1 and age = 20;  -- 走 user_id 主键索引
select * from `user` where user_id = 1 or age = 20;  -- 索引失效

若为 age 列加上普通索引的话：

select * from `user` where user_id = 1 and age = 20;  -- 走 user_id 主键索引
select * from `user` where user_id = 1 or age = 20;  -- 走 age 普通索引

因此建议：

• 尽量避免使用 or 语句，可以根据情况使用 union all 或者 in 来代替，这两个语句的执行效率也比 or 好些。
• 为 or 左右的列建索引。

无法避免对索引列使用函数时，怎么使用索引？

有时候我们无法避免对索引列使用函数，但这样做会导致全表索引，是否有更好的方式呢？

比如我现在就是想记录 2016 ~ 2018 所有年份 7月份的交易记录总数：

SELECT count(*) FROM tradelog WHERE month(t_modified)=7;

由于索引列是函数的参数，所以显然无法用到索引，我们可以将它改造成基本字段区间的查找：

SELECT count(*) FROM tradelog WHERE
    -> (t_modified >= '2016-7-1' AND t_modified<'2016-8-1') or
    -> (t_modified >= '2017-7-1' AND t_modified<'2017-8-1') or 
    -> (t_modified >= '2018-7-1' AND t_modified<'2018-8-1');

前缀索引与索引选择性

之前我们说过，对于长字符串的字段（如 url），我们可以用伪哈希索引的形式来创建索引，以避免索引变得既大又慢，除此之外其实还可以用前缀索引（字符串的部分字符）的形式来达到我们的目的。那么这个前缀索引应该如何选取呢？这就涉及到一个叫索引选择性的概念。

索引选择性：不重复的索引值总数（也称为基数——cardinality）和数据表的记录总数的比值。比值越高，代表索引的选择性越好。

• 索引的选择性越高，则查询效率越高，因为选择性高的索引可以让 MySQL 在查询时过滤掉更多的行。
• 主键或唯一索引的选择性是 1，这是最好的索引选择性，性能也是最好的。

举例说明：假如有一张一万行记录的表，其中有一个性别列 sex，这个列的包含选项就两个：男、女。那么为该列创建索引的话，其索引的选择性为万分之二，此时在性别这一列创建索引是没有啥意义的。再假设个极端情况，列内的数据都是女，那么索引的选择性为万分之一，其效率还不如直接进行全表扫描。

我们可以通过 SHOW INDEXES FROM table 来查看每个索引 cardinality 的值以评估索引设计的合理性。

怎么选择这个比例呢，我们可以分别取前 3，4，5，6，7 的前缀索引，然后再比较下选择这几个前缀索引的选择性，执行以下语句：

SELECT 
 COUNT(DISTINCT LEFT(city,3))/COUNT(*) as sel3,
 COUNT(DISTINCT LEFT(city,4))/COUNT(*) as sel4,
 COUNT(DISTINCT LEFT(city,5))/COUNT(*) as sel5,
 COUNT(DISTINCT LEFT(city,6))/COUNT(*) as sel6,
 COUNT(DISTINCT LEFT(city,7))/COUNT(*) as sel7
FROM city_demo;

获得结果如下：

可以看到当前缀长度为 7 时，索引选择性提升的比例已经很小了，也就是说应该选择 city 的前六个字符作为前缀索引，如下：

ALTER TABLE city_demo ADD KEY(city(6));

我们当前是以平均选择性为指标的，有时候这样是不够的，还得考虑最坏情况下的选择性，以这个 demo 为例，可能一些人看到选择 4，5 的前缀索引与选择 6，7 的选择性相差不大，那就得看下选择 4，5 的前缀索引分布是否均匀了：

SELECT 
    COUNT(*) AS  cnt, 
    LEFT(city, 4) AS pref
  FROM city_demo GROUP BY pref ORDER BY cnt DESC LIMIT 5;

可能会出现以下结果：

可以看到分布极不均匀，以 Sant、Toul 为前缀索引的数量极多，这两者的选择性都不是很理想，所以要选择前缀索引时也要考虑最差的选择性的情况。

前缀索引虽然能实现索引占用空间小且快的效果，但它也有明显的弱点，MySQL 无法使用前缀索引做 ORDER BY 和 GROUP BY ，而且也无法使用前缀索引做覆盖扫描，前缀索引也有可能增加扫描行数。

假设有以下表数据及要执行的 SQL：

SELECT id,email FROM user WHERE email = 'zhangssxyz@xxx.com';

如果我们针对 email 设置的是整个字段的索引，则上表中根据 「zhangssxyz@163.com」查询到相关记录后，再查询此记录的下一条记录，发现没有，停止扫描，此时可知只扫描一行记录。如果我们以前六个字符（即 email(6)）作为前缀索引，则显然要扫描四行记录，并且获得行记录后不得不回到主键索引再判断 email 字段的值，所以使用前缀索引要评估它带来的这些开销。

另外有一种情况我们可能需要考虑一下，如果前缀基本都是相同的该怎么办，比如现在我们为某市的市民建立一个人口信息表，则这个市人口的身份证虽然不同，但身份证前面的几位数都是相同的，这种情况该怎么建立前缀索引呢？

一种方式就是我们上文说的，针对身份证建立哈希索引，另一种方式比较巧妙，将身份证倒序存储，查的时候可以按如下方式查询：

SELECT field_list FROM t WHERE id_card = reverse('input_id_card_string');

这样就可以用身份证的后六位作前缀索引了，是不是很巧妙。

实际上，上文所述的索引选择性同样适用于联合索引的设计，如果没有特殊情况，我们一般建议在建立组合索引时，把选择性最高的列放在最前面，比如，对于以下语句：

SELECT * FROM payment WHERE staff_id = xxx AND customer_id = xxx;

单就这个语句而言， (staff_id，customer_id) 和  (customer_id，staff_id) 这两个联合索引我们应该建哪一个，可以统计下这两者的选择性。

SELECT 
 COUNT(DISTINCT staff_id)/COUNT(*) as staff_id_selectivity,
 COUNT(DISTINCT customer_id)/COUNT(*) as customer_id_selectivity,
 COUNT(*)
FROM payment;

结果为：

staff_id_selectivity: 0.0001
customer_id_selectivity: 0.0373
COUNT(*): 16049

从中可以看出 customer_id 的选择性更高，所以应该选择 customer_id 作为第一列。

优化建议：

• 建议在建立联合索引时，把选择性最高的列放在最前面。
• 索引应尽可能少地占用存储空间。
• 不建议在更新频繁、区分度不高的属性上建立索引。
  • 为更新频繁的字段建立索引会频繁更变 B+ 树，导致大大降低数据库性能。
  • 像“性别”这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似。

6、索引设计准则

上面我们得出了一个索引列顺序的经验法则：将选择性最高的列放在联合索引的最前列，这种建立在某些场景可能有用，但通常不如避免随机 IO 和排序那么重要，这里引入索引设计中非常著名的一个准则：三星索引。

如果一个查询满足三星索引中三颗星的所有索引条件，理论上可以认为我们设计的索引是最好的索引。那么什么是三星索引：

1. 第一颗星：参与条件查询（where、join、order by、group by）的列可以组成单列索引或联合索引。

2. 第二颗星：避免排序，如果 SQL 语句中出现 order by column，那么取出的结果集就应该已经是按照 column 排序好的，而不需要再进行排序生成临时表。

3. 第三颗星：SELECT 的列应尽量都是索引列，即尽量使用覆盖索引，避免回表查询。

所以对于如下语句：

SELECT age, name, city where age = xxx and name = xxx order by age;

设计的索引应该是 (age, name, city) 或 (name, age, city)。

当然了，三星索引是一个比较理想化的标准，实际操作往往只能满足期望中的一颗或两颗星，考虑如下语句：

SELECT age, name, city where age >= 10 and age <= 20 and city = xxx order by name desc;

• 假设我们分别为这三列建了联合索引（不限顺序），则显然都符合第三颗星（使用了覆盖索引）；
• 如果索引是 (city, age, name)，则虽然满足了第一颗星，但排序无法用到索引，不满足第二颗星；
• 如果索引是 (city, name, age)，则第二颗星满足了，但此时 age 在 WHERE 中的搜索条件又无法满足第一星。

另外第三颗星（尽量使用覆盖索引）也无法完全满足，试想我要 SELECT 多列，要把这么多列都设置为联合索引吗，这对索引的维护是个问题，因为每一次表的 CURD 都伴随着索引的更新，很可能频繁伴随着页分裂与页合并。

综上所述，三星索引只是给我们构建索引提供了一个参考，索引设计应该尽量靠近三星索引的标准，但实际场景我们一般无法同时满足三星索引，一般我们会优先选择满足第三颗星（因为回表代价较大），至于第一，二颗星就要依赖于实际的成本及实际的业务场景考虑。

三、MySQL视图引用

视图与表（有时为与视图区别，也称表为基本表——Base Table）不同，视图是虚拟的表，即视图所对应的数据并不进行实际存储，数据库中只存储视图的定义，也就是说视图本身没有数据，只是通过执行相应的 select 语句获得相应的数据。

1）优点

1. 重用性：视图就像是函数，那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。

2. 简单性：看到的就是需要的。用户不必理解查询细节。

3. 安全性：可以对不同的用户，设定不同的视图。且用户只能以只读视图检索数据，但无法更新。

4. 独立性：更改数据格式和表示。视图可返回与基本表的表示和格式不同的数据。

5. 重构性：数据库视图可以实现向后兼容。假设我们有一个中央数据库，许多应用程序正在使用它。有一天，我们决定重新设计数据库以适应新的业务需求，希望删除一些表并创建新的表，并且不希望更改影响其他应用程序。在这种情况下，就可以创建与将要删除的旧表相同的模式的数据库视图。

2）缺点

1. 视图不是一种物化视图，它相当于一个虚拟表，本身并不存储数据，视图的所有数据都是从其他表中查出来的。这带来的问题是，使用视图并不能直接将常用数据分离出来（只是查询展示出来）从而优化查询速度。
2. 操作视图的很多命令都与普通表一样，这会导致在业务代码中无法通过 SQL 区分表和视图，使代码变得复杂。
3. 实现视图的算法有两种，分别为合并算法和临时表算法：
   • 合并算法是指查询视图时将视图定义的 SQL 合并到查询 SQL 中，比如 create view v1 as select * from user where sex=m; 当我们要查询视图时，会将 select id,name from v1; 合并成 select id,name from user where sex=m……;
   • 临时表算法是先将视图查出来的数据保存到一个临时表中，查询的时候查这个临时表。
   • 不管是合并算法和临时表算法都会带来额外的开销，而且使用临时表后会使 MySQL 的优化变得很困难，比如索引。
4. 当然，视图在某些情况下可以帮助提升性能，但视图的性能很难预测。且在 MySQL 的优化器中，视图的代码执行路径也完全不同，无法直观的预测其执行性能。

3）视图的使用场景

1. 在 Web+MySQL 设计中，由于追求高伸缩性，不依赖于数据库本身实现，一般不使用视图来做数据查询，而是 Web 程序拼好 SQL 字符串，让数据库执行。这样将业务逻辑写在程序代码中，方便调试、修改、分布式运行。
2. 在 Web+MySQL 设计中，如果支持多种数据库，这个是由 ORM 或类似 mybatis 配置来实现的，这样不用每个数据库写一个视图查询，做到了低耦合。低耦合，也就是不和具体数据库绑死。
3. 视图可以作为镜像表，如 a 数据库镜像 b 数据库中的表，可以在这个视图中进行增删改查，这个功能在一些系统设计中是有用的，如我在 b 数据库中做开发，可以镜像 a 数据库中的表过来，不用每次手工同步，在上线时直接指向 a 数据库即可。
4. 如果你做一些数据分析或数据仓库查询，可以使用视图，这样不用写程序和写 SQL 来实现。有的管理工具有视图创建工具，可以做可视化设计，提高工作效率。

4）视图的使用规则

• 与表名一样，视图必须唯一命名。
• 可创建的视图数目没有限制。
• 为了创建视图必须有足够的权限。
• 视图可以嵌套，即可以使用其它视图来构造一个视图。
• ORDER BY 可以用在视图中，但如果从该视图检索数据的 SELECT 语句中也含有 ORDER BY，那么视图中的 ORDER BY 将被覆盖。
• 视图无法创建索引，也不能有关联的触发器或默认值。
• 视图可以和表一起使用。

5）视图使用

① 创建视图 

CREATE VIEW v_ecs_order_info AS
    SELECT
        order_id,
        order_sn,
        order_amount,
        consignee
    FROM
        ecs_order_info;-- 使用视图来查询数据select * from v_ecs_order_info;

② 查看视图

show table status where comment='view';  -- 查看现有的视图及其信息

③  删除视图

DROP VIEW v_ecs_order_info;

④ 更新视图

--方式1：先用 DROP 删除视图，再用 CREATE 创建视图。

--方式2：使用 CREATE OR REPLACE VIEW
-- 如果要更新的视图不存在则会创建一个视图，若存在则会替换原来的视图
CREATE OR REPLACE VIEW v_ecs_order_info AS
  SELECT
    order_id,
    order_sn,
    order_amount,
    pay_status,
    consignee
FROM
    ecs_order_info;

-- 方式3：使用 ALTER
ALTER VIEW v_ecs_order_info AS
  SELECT
    order_id,
    order_sn,
    order_amount,
    pay_status,
    consignee
FROM
    ecs_order_info;

四、MySQL高并发事务调优

1、事务

1）什么是事务？

数据库事务是数据库系统执行过程中的一个逻辑处理单元，保证一组数据库操作要么全部成功（提交），要么全部失败（回滚）。

比如银行转账业务，步骤一：从 A 账户减少 300 元；步骤二：向 B 账户增加 300 元。为了确保总的金额不变，就要维持数据的一致性，那么步骤一和步骤二两个操作必须全确认或者全取消。这里的每个步骤就可以理解为每个 SQL 语句。

我们知道，在 Java 并发编程中，可以多线程并发执行程序，然而并发虽然提高了程序的执行效率，却给程序带来了线程安全问题。事务跟多线程一样，为了提高数据库处理事务的吞吐量，数据库同样支持并发事务，而在并发运行中，同样也存在着安全性问题，例如，修改数据丢失，读取数据不一致等。

2）事务的特性：ACID

• 原子性（Atomicity）：一个事务中的所有操作要么全部成功（提交），要么全部失败（回滚），不能只完成其中的一部分操作。
• 一致性（Consistency）：事务的执行不能破坏数据的完整性和一致性。一个事务在执行之前和执行之后，数据库总是从一个一致性的状态转换到另外一个一致性的状态。
• 隔离性（Isolation）：一个事务的修改在最终提交前，对其他事务是不可见的。主要针对并发场景。
• 持久性（Durability）：事务一旦提交，那么它所做的修改就会永久保存到数据库中。

正是这些特性，才保证了数据库事务的安全性。而在 MySQL 中，鉴于 MyISAM 存储引擎不支持事务，所以接下来的内容都是基于 InnoDB 存储引擎的。

3）ACID 靠什么保证的呢？

1. A（原子性）：由 undo log 日志保证，它记录了需要回滚的日志信息。在事务回滚时会撤销已经执行成功的 SQL。
2. C（一致性）：一般由代码层面来保证。
3. I（隔离性）：由 MVCC 来保证。
4. D（持久性）：由内存 + redo log 来保证。MySQL 修改数据同时在内存和 redo log 记录这次操作，事务提交的时候通过 redo log 刷盘，宕机的时候可以从 redo log 恢复。

4）事务语句

• egin（start transaction）：显式开启一个事务。
• commit：提交事务。
• rollback：回滚事务。
• set autocommit：设置自动提交模式。

autocommit 默认为 on（打开），即我们每执行一条 SQL 都相当于一个事务并自动提交。

场景 1：commit

start transaction;  -- 显式开启一个事务

delete from emp where ename='wang';

select * from emp;  -- 数据"wang"已删除

select * from emp;  -- 数据"wang"未删除

commit;  -- 提交事务

select * from emp;  -- 数据"wang"已删除

select * from emp;  -- 数据"wang"已删除

场景 2：rollback

begin;  -- 显式开启一个事务

insert into emp values('wang', now(), 4000, 2); 

select * from emp;  -- 新增了"wang"数据

insert into emp values('liu', now(), 90000, 3);

select * from emp;  -- 新增了"liu"数据

select * from emp;  -- 未新增两条数据

select * from emp;  -- 未新增两条数据

5）事务的隔离级别

在数据库事务中，事务的隔离是解决并发事务问题的关键， 下文将简述事务隔离的实现原理，以及如何优化事务隔离带来的性能问题。

并发事务带来的问题，我们可以通过以下几个例子来了解下并发事务带来的几个问题：

1. 数据丢失：一个事务的更新被另一个事务的更新所覆盖。

2. 脏读：一个事务读到另一个事务没有提交的数据。 

3. 不可重复读：一个事务读到另一个事务已提交的数据（update）。 

4. 幻读：一个事务读到另一个事务已提交的数据（insert）。

事务隔离解决并发问题

以上 4 个并发事务带来的问题，其中，数据丢失问题可以基于数据库中的悲观锁来避免发生，即在查询时通过在事务中使用 select xx for update 语句来实现一个排他锁，保证在该事务结束之前其他事务无法更新该数据；也可以基于乐观锁来避免，即将某一字段作为版本号，如果更新时的版本号跟之前的版本一致，则更新，否则更新失败。

剩下 3 个问题，其实是数据库读一致性造成的，需要数据库提供一定的事务隔离机制来解决。

在操作数据的事务中，不同的锁机制会产生以下几种不同的事务隔离级别，不同的隔离级别分别可以解决并发事务产生的几个问题，对应如下：

1. 读未提交（Read Uncommitted）：可能会读到其他事务未提交的数据（也叫脏读）。
   • 在事务 A 读取数据时，事务 B 读取数据时加了共享锁，修改数据时加了排他锁。
   • 这种隔离级别，存在脏读、不可重复读以及幻读的问题。
2. 读已提交（Read Committed）：两次读取结果不一致（也叫不可重复读）。
   • 在事务 A 读取数据时增加了共享锁，一旦读取，立即释放锁，事务 B 读取和修改数据时增加了行级排他锁，直到事务结束才释放锁。
   • 也就是说，事务 A 在读取数据时，事务 B 只能读取数据，不能修改。当事务 A 读取到数据后，事务 B 才能修改。
   • 这种隔离级别，可以避免脏读，但依然存在不可重复读以及幻读的问题。
3. 可重复读（Repeatable Read）：每次读取结果都一样。
   • 在事务 A 读取数据时增加了共享锁，事务结束，才释放锁，事务 B 读取修改数据时增加了行级排他锁，直到事务结束才释放锁。
   • 也就是说，事务 A 在没有结束事务时，事务 B 只能读取数据，不能修改。当事务 A 结束事务，事务 B 才能修改。
   • 这种隔离级别，可以避免脏读、不可重复读，但依然存在幻读的问题。
4. 可序列化（Serializable）：
   • 在事务 A 读取数据时增加了共享锁，事务结束，才释放锁，事务 B 读取修改数据时增加了表级排他锁，直到事务结束才释放锁。
   • 可序列化解决了脏读、不可重复读、幻读等问题，但隔离级别越来越高的同时，并发性会越来越低。
   • 一般是不会使用的，他会给每一行读取的数据加锁，会导致大量超时和锁竞争的问题。 

InnoDB 中的 RC 和 RR 隔离事务是基于多版本并发控制（MVCC）实现高性能事务的。一旦数据被加上排他锁，其他事务将无法加入共享锁，且处于阻塞等待状态，如果一张表有大量的请求，这样的性能将是无法支持的。

MVCC 对普通的 Select 不加锁，如果读取的数据正在执行 Delete 或 Update 操作，这时读取操作不会等待排它锁的释放，而是直接利用 MVCC 读取该行的数据快照（数据快照是指在该行的之前版本的数据，而数据快照的版本是基于 undo 实现的，undo 是用来做事务回滚的，记录了回滚的不同版本的行记录）。MVCC 避免了对数据重复加锁的过程，大大提高了并发性能。

6）隔离级别配置 

mysql> show variables like 'transaction%';  -- 查看当前的隔离级别
+----------------------------------+-----------------+
| Variable_name                    | Value           |
+----------------------------------+-----------------+
| transaction_alloc_block_size     | 8192            |
| transaction_allow_batching       | OFF             |
| transaction_isolation            | REPEATABLE-READ |
| transaction_prealloc_size        | 4096            |
| transaction_read_only            | OFF             |
| transaction_write_set_extraction | XXHASH64        |
+----------------------------------+-----------------+
6 rows in set, 1 warning (0.01 sec)

-- 修改当前的隔离级别
mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

2、锁

锁是一种使各种共享资源在被并发访问变得有序的机制，目的是为了保证数据的一致性。

MySQL 的锁分为共享锁（S，读锁）和排他锁（X，写锁）。

• 读锁：是共享的，可以通过 lock in share mode 实现，这时候只能读不能写。
• 写锁：是排他的，它会阻塞其他的写锁和读锁。从颗粒度来区分，又可以分为表锁和行锁两种。

表锁会锁定整张表并且阻塞其他用户对该表的所有读写操作，比如 alter 修改表结构的时候会锁表。

• 表锁的优势：开销小；加锁快；无死锁。
• 表锁的劣势：锁粒度大，发生锁冲突的概率高，并发处理能力低。

行锁又可以分为乐观锁和悲观锁，悲观锁可以通过 for update 实现，乐观锁则通过版本号实现。

• 乐观锁：

  • 一段执行逻辑加上乐观锁，不同线程同时执行时，线程可以同时进入执行阶段，在最后更新数据的时候要检查这些数据是否被其他线程修改了，没有修改则进行更新，否则放弃本次操作。

  • 乐观锁做事比较乐观，它假定冲突的概率很低，放弃后如何重试，这跟业务场景息息相关，虽然重试的成本很高，但是冲突的概率足够低的话，还是可以接受的。可见，乐观锁的心态是，不管三七二十一，先改了资源再说。另外，你会发现乐观锁全程并没有加锁。

• 悲观锁：

  • 一段执行逻辑加上悲观锁，不同线程同时执行时，只能有一个线程执行，其他的线程在入口处等待，直到锁被释放。

  • 悲观锁做事比较悲观，它认为并发修改共享资源的概率比较高，于是很容易出现冲突，所以在访问共享资源前，先要上锁（排他锁）。
• 使用场景：

  • 乐观锁适用于书写比较少的情况下，即冲突很少发生的时候，这样可以省去锁的开销，加大在整个系统的吞吐量。

  • 如果是多写的情况，会产生冲突，导致上层应用会不断地进行充实，这样反倒降低了性能，因此在多写的情况下用悲观锁就比较合适。

加锁的方式：

• 自动加锁：查询操作（SELECT）会自动给涉及的所有表加读锁；更新操作（UPDATE、DELETE、INSERT）会自动给涉及的表加写锁。
• 也可以显式加锁：
  • 共享读锁：lock table tableName read
  • 独占写锁：lock table tableName write
  • 批量解锁：unlock tables

3、死锁

1）死锁场景

死锁发生在当两个事务均尝试获取对方已经持有的排他锁时。在innodb 中，select 不会对数据加锁，而 update/delete 会加行级别的排他锁。

当数据库的隔离级别为 Repeatable Read 或 Serializable 时，我们来看以下会发生死锁的并发事务场景。

表结构示例：

mysql> desc user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  | MUL | NULL    |       |
| age   | int         | YES  | MUL | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.12 sec)

场景 1：表锁

我们知道，InnoDB 既实现了行锁，也实现了表锁。行锁是通过索引实现的，如果不通过索引条件检索数据，那么 InnoDB 将对表中所有的记录进行加锁，其实就是升级为表锁了。

当 update 的数据未作用于索引时，会发生表锁：

场景 2：行锁

当 update 数据作用于索引时，会发生行锁：

场景 3：

由下图可知，由于两个事物对这条记录同时持有 S 锁（共享锁）的情况下，再次尝试获取该条记录的 X 锁（排他锁），从而导致互相等待引发死锁。

场景 4：死锁回滚

当 InnoDB 检测到死锁时，会回滚其中一个事务，让另一个事务得以完成。

ysql>  update user set name='test2' where id=1;  -- Session 2
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

2）死锁调优

在下面这个并发场景下，两个事务均能成功提交，而不会有死锁。

3）排查死锁

1. 查看死锁日志：show engine innodb status;
2. 找出死锁 SQL
3. 分析 SQL 加锁情况
4. 模拟死锁案发
5. 分析死锁日志
6. 分析死锁结果

4）如何减少死锁发生

1. 使用合适的索引。
2. 使用更小的事务。
3. 经常性的提交事务，避免事务被挂起。

4、高并发事务调优

1）结合业务场景，使用低级别事务隔离

在高并发业务中，为了保证业务数据的一致性，操作数据库时往往会使用到不同级别的事务隔离。隔离级别越高，并发性能就越低。

那换到业务场景中，我们如何判断用哪种隔离级别更合适呢？我们可以通过两个简单的业务来说下其中的选择方法。

我们在修改用户最后登录时间的业务场景中，这里对查询用户的登录时间没有特别严格的准确性要求，而修改用户登录信息只有用户自己登录时才会修改，不存在一个事务提交的信息被覆盖的可能。所以我们允许该业务使用最低隔离级别。

而如果是账户中的余额或积分的消费，就存在多个客户端同时消费一个账户的情况，此时我们应该选择 RR 级别来保证一旦有一个客户端在对账户进行消费，其他客户端就不可能对该账户同时进行消费了。

2）避免行锁升级表锁

在 InnoDB 中，行锁是通过索引实现的，如果不通过索引条件检索数据，行锁将会升级到表锁。我们知道，表锁是会严重影响到整张表的操作性能的，所以我们应该避免他。

3）控制事务的大小，减少锁定的资源量和锁定时间长度

你是否遇到过以下 SQL 异常呢？在抢购系统的日志中，在活动区间，我们经常可以看到这种异常日志：

MySQLQueryInterruptedException: Query execution was interrupted

由于在抢购提交订单中开启了事务，在高并发时对一条记录进行更新的情况下，由于更新记录所在的事务还可能存在其他操作，导致一个事务比较长，当有大量请求进入时，就可能导致一些请求同时进入到事务中。

又因为锁的竞争是不公平的，当多个事务同时对一条记录进行更新时，极端情况下，一个更新操作进去排队系统后，可能会一直拿不到锁，最后因超时被系统打断踢出。

在用户购买商品时，首先我们需要查询库存余额，再新建一个订单，并扣除相应的库存。这一系列操作是处于同一个事务的。

以上业务若是在两种不同的执行顺序下，其结果都是一样的，但在事务性能方面却不一样：

这是因为，虽然这些操作在同一个事务，但锁的申请在不同时间，只有当其他操作都执行完，才会释放所有锁。因为扣除库存是更新操作，属于行锁，这将会影响到其他操作该数据的事务，所以我们应该尽量避免长时间地持有该锁，尽快释放该锁。

又因为先新建订单还是先扣除库存都不会影响业务逻辑，所以我们可以将扣除库存操作放到后面，也就是使用执行顺序 1，以此尽量减小锁的持有时间。

总计：

其实 MySQL 的并发事务调优和 Java 的多线程编程调优非常类似，都是可以通过减小锁粒度和减少锁的持有时间进行调优。在 MySQL 的并发事务调优中，我们尽量在可以使用低事务隔离级别的业务场景中，避免使用高事务隔离级别。

在功能业务开发时，开发人员往往会为了追求开发速度，习惯使用默认的参数设置来实现业务功能。例如，在 service 方法中，你可能习惯默认使用 transaction，很少再手动变更事务隔离级别。但要知道，transaction 默认是 RR 事务隔离级别，在某些业务场景下，可能并不合适。因此，我们还是要结合具体的业务场景，进行考虑。 

5、MVCC

1. MVCC简介

1）什么是 MVCC？

MVCC（Multi-Version Concurrency Control，多版本并发控制）是一种并发控制的方法，一般是在数据库管理系统中实现对数据库的并发访问，在编程语言中实现事务内存。

通常情况下，数据一旦被加上排他锁，其他事务将无法加入共享锁，且处于阻塞等待状态。如果一张表有大量的请求，这样的性能将是无法支持的。

而 InnoDB 中的 RC 和 RR 隔离事务是基于多版本并发控制（MVCC）实现高性能事务的。

MVCC 对普通的 Select 不加锁，如果读取的数据正在执行 Delete 或 Update 操作，这时读取操作不会等待排它锁的释放，而是直接利用 MVCC 读取该行的数据快照（数据快照是指在该行的之前版本的数据，而数据快照的版本是基于 undo 实现的，undo 是用来做事务回滚的，记录了需要回滚的不同版本的行记录）。

MVCC 在 MySQL InnoDB 中主要是为了提高数据库事务的并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读，避免了对数据重复加锁的过程，大大提高了并发性能。

2）什么是当前读和快照读？

在学习 MVCC 多版本并发控制之前，我们必须先了解一下，什么是 MySQL InnoDB 下的当前读和快照读？

当前读

• 像 select lock in share mode（共享锁）、select ... for update、update、insert、delete（排他锁）这些操作都是一种当前读，为什么叫当前读？就是它读取的是数据的最新版本。
• 读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。

快照读

• 像不加锁的 select 操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读。
• 之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即 MVCC。可以认为 MVCC 是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销。
• 既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是历史版本。

说白了 MVCC 就是为了实现读写冲突时不加锁，而这个读指的就是快照读，而非当前读。当前读实际上是一种加锁的操作，是悲观锁的实现。

3）当前读、快照读和 MVCC 的关系

• 准确的说，MVCC 多版本并发控制指的是 “维持一个数据的多个版本，使得读写操作没有冲突”这么一个概念，仅仅是一个理想概念。
• 而在 MySQL 中，实现这么一个 MVCC 理想概念，我们就需要 MySQL 提供具体的功能去实现它，而快照读就是 MySQL 为我们实现 MVCC 理想模型的其中一个具体非阻塞读功能。相对而言，当前读就是悲观锁的具体功能实现。
• 要说的再细致一些，快照读本身也是一个抽象概念。再深入研究，MVCC 模型在 MySQL 中的具体实现则是由 3 个隐式字段、undo 日志 和 Read View 这三大部分来完成的，具体可以看下面的 MVCC 实现原理。

4）MVCC能解决什么问题，好处是？

数据库并发场景有三种，分别为：

1. 读-读：不存在任何问题，也不需要并发控制。
2. 读-写：有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读、幻读、不可重复读。
3. 写-写：有线程安全问题，可能会存在更新丢失问题，比如第一类更新丢失、第二类更新丢失。

MVCC带来的好处是：

多版本并发控制（MVCC）实际上就是保存了数据在某个时间节点的快照，是一种用来解决读-写冲突的无锁并发控制的方案。

MVCC 为每个事务分配递增的时间戳，为每次修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。

因此，MVCC 可以为数据库解决以下问题：

1. 在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能。
2. 同时还可以解决脏读、幻读、不可重复读等事务隔离问题，但不能解决更新丢失问题。

总结：

总之，MVCC 就是因为大牛们不满意只让数据库采用悲观锁这样性能不佳的形式去解决读-写冲突问题，而提出的解决方案。所以在数据库中，因为有了 MVCC，我们可以形成两个组合：

1. MVCC + 悲观锁：MVCC 解决读写冲突，悲观锁解决写写冲突。
2. MVCC + 乐观锁：MVCC 解决读写冲突，乐观锁解决写写冲突

这种组合的方式就可以最大程度的提高数据库并发性能，并解决读写冲突和写写冲突导致的问题。

2. MVCC 的实现原理

MVCC 的目的就是多版本并发控制，在数据库中的实现，就是为了解决读写冲突。它的实现原理主要是依赖记录中的 3 个隐式字段、undo 日志、Read View 这 3 大部分实现的。

1）隐式字段

实际上每行记录除了我们自定义的字段外，还有数据库隐式定义的 DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID 等字段。

1. DB_TRX_ID：6byte，最近修改（修改/插入）事务 ID，记录创建这条记录/最后一次修改该记录的事务 ID。
2. DB_ROLL_PTR：7byte，回滚指针，指向这条记录的上一个版本（存储于 rollback segment 里）。
3. DB_ROW_ID：6byte，隐含的自增 ID（隐藏主键），如果数据表没有主键，InnoDB 会自动以 DB_ROW_ID 产生一个聚集索引。
4. 实际还有一个删除 flag 隐藏字段, 既记录被更新或删除并不代表真的删除，而是删除 flag 变了。

如上图，DB_ROW_ID 是数据库默认为该行记录生成的唯一隐式主键，DB_TRX_ID 是当前操作该记录的事务 ID，而 DB_ROLL_PTR 是一个回滚指针，用于配合 undo 日志，指向上一个版本。 

2）Undo 日志 

Undo Log 主要分为两种：

• insert undo log：代表事务在 insert 新记录时产生的 undo log，只在事务回滚时需要，并且在事务提交后可以被立即丢弃。
• update undo log：事务在进行 update 或 delete 时产生的 undo log，不仅在事务回滚时需要，在快照读时也需要，所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被 purge 线程统一清除。

purge

• 为了实现 InnoDB 的 MVCC 机制，更新或者删除操作都只是设置一下老记录的 deleted_bit，并不真正将过时的记录删除。
• 为了节省磁盘空间，InnoDB 有专门的 purge 线程来清理 deleted_bit 为 true 的记录。为了不影响 MVCC 的正常工作，purge 线程自己也维护了一个 read view（这个 read view 相当于系统中最老活跃事务的 read view）。如果某个记录的 deleted_bit 为 true，并且 DB_TRX_ID 相对于 purge 线程的 read view 可见，那么这条记录一定是可以被安全清除的。

对 MVCC 有帮助的实质是 update undo log ，undo log 实际上就是存在 rollback segment 中旧记录链，它的执行流程如下：

比如有个事务在 person 表中插入了一条新记录（如下图所示），那么该记录的隐式主键则是 1，事务 ID 和回滚指针，我们假设为 NULL。

现在来了一个事务 1 对该记录的 name 做出了修改，改为 Tom。

1. 在事务 1 修改该行数据时，数据库会先对该行加排他锁。
2. 然后把该行数据拷贝到 undo log 中，作为旧记录，即在 undo log 中有当前行的拷贝副本。
3. 拷贝完毕后，修改该行 name 为 Tom，并且修改隐藏字段的事务 ID 为当前事务 1 的 ID。我们默认从 1 开始，之后递增，回滚指针指向拷贝到 undo log 的副本记录，既表示我的上一个版本就是它。
4. 事务提交后，释放锁。

又来了个事务 2 修改 person 表的同一个记录，将 age 修改为 30 岁。

1. 在事务 2 修改该行数据时，数据库也先为该行加锁。
2. 然后把该行数据拷贝到 undo log 中，作为旧记录，发现该行记录已经有 undo log 了，那么最新的旧数据作为链表的表头，插在该行记录的 undo log 最前面。
3. 修改该行 age 为 30 岁，并且修改隐藏字段的事务 ID 为当前事务 2 的 ID, 也就是 2，回滚指针指向刚刚拷贝到 undo log 的副本记录。
4. 事务提交，释放锁。

从上述例子中，我们就可以看出，不同事务或者相同事务的对同一记录的修改，会导致该记录的 undo log 成为一条记录版本线性表（链表），undo log 的链首就是最新的旧记录，链尾就是最早的旧记录（当然就像之前说的该 undo log 的节点可能是会 purge 线程清除掉，像图中的第一条 insert undo log，其实在事务提交之后可能就被删除丢失了，不过这里为了演示，所以还放在这里）。 

3）Read View（读视图）

什么是Read View，说白了 Read View 就是事务进行快照读操作的时候生产的读视图（Read View）。在事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的 ID（当每个事务开启时，都会被分配一个 ID, 这个 ID 是递增的，所以最新的事务，ID 值越大）。

所以我们知道 Read View 主要是用来做可见性判断的，即当我们某个事务执行快照读的时候，对该记录创建一个 Read View 读视图，把它比作条件用来判断当前事务能够看到哪个版本的数据，既可能是当前最新的数据，也有可能是该行记录的 undo log 里面的某个版本的数据。

Read View 遵循一个可见性算法，主要是将要被修改的数据的最新记录中的 DB_TRX_ID（即当前事务 ID）取出来，与系统当前其他活跃事务的 ID 去对比（由 Read View 维护），如果 DB_TRX_ID 跟 Read View 的属性做了某些比较，不符合可见性，那就通过 DB_ROLL_PTR 回滚指针去取出 undo log 中的 DB_TRX_ID 再比较，即遍历链表的 DB_TRX_ID（从链首到链尾，即从最近的一次修改查起），直到找到满足特定条件的 DB_TRX_ID，那么这个 DB_TRX_ID 所在的旧记录就是当前事务能看见的最新老版本。

4）可见性算法

那么这个判断条件是什么呢？

如上是一段 MySQL 判断可见性的一段源码，即 changes_visible 方法（不完全哈，但能看出大致逻辑），该方法展示了我们拿 DB_TRX_ID 去跟 Read View 某些属性进行怎么样的比较。

在展示之前，先简化一下 Read View，我们可以把 Read View 简单的理解成有三个全局属性：

trx_list（名字随便取的）：一个数值列表，用来维护 Read View 生成时刻系统正活跃的事务 ID。

up_limit_id：记录 trx_list 列表中事务 ID 最小的 ID。

low_limit_id：Read View 生成时刻系统尚未分配的下一个事务 ID，也就是目前已出现过的事务ID的最大值 +1。

1. 首先比较DB_TRX_ID < up_limit_id，如果小于，则当前事务能看到 DB_TRX_ID 所在的记录，如果大于等于进入下一个判断。
2. 接下来判断 DB_TRX_ID 大于等于 low_limit_id，如果大于等于则代表 DB_TRX_ID 所在的记录在 Read View 生成后才出现的，那对当前事务肯定不可见，如果小于则进入下一个判断。
3. 判断 DB_TRX_ID 是否在活跃事务之中，trx_list.contains（DB_TRX_ID），如果在，则代表在 Read View 的生成时刻，你这个事务还在活跃，还没有 Commit，你修改的数据，我当前事务也是看不见的；如果不在，则说明，你这个事务在 Read View 生成之前就已经 Commit 了，你修改的结果，我当前事务是能看见的。3.3 MVCC 整体流程我们在了解了隐式字段、Undo Log 以及 Read View 的概念之后，就可以来看看 MVCC 实现的整体流程是怎么样了。我们可以模拟一下：

当事务 2 对某行数据执行了快照读，数据库为该行数据生成一个 Read View 读视图，假设当前事务 ID 为 2，此时还有事务 1 和事务 3 在活跃中，事务 4 在事务 2 快照读前一刻提交更新了，所以 Read View 记录了系统当前活跃事务 1、3的 ID，维护在一个列表上，假设我们称为 trx_list。

Read View 不仅仅会通过一个列表 trx_list 来维护事务 2 执行快照读那刻系统正活跃的事务 ID，还会有两个属性，分别是 up_limit_id（记录 trx_list 列表中事务 ID 最小的 ID）和 low_limit_id（记录 trx_list 列表中事务 ID 最大的 ID，也有人说快照读那刻系统尚未分配的下一个事务 ID，也就是目前已出现过的事务 ID 的最大值 +1，我更倾向于后者）。所以在这里例子中 up_limit_id 就是 1，low_limit_id 就是 4 + 1 = 5，trx_list 集合的值是 1、3，Read View 如下图： 

在我们的例子中，只有事务 4 修改过该行记录，并在事务 2 执行快照读前，就提交了事务，所以当前该行当前数据的 undo log 如下图所示；我们的事务 2 在快照读该行记录的时候，就会拿该行记录的 DB_TRX_ID 去跟 up_limit_id、low_limit_id 和活跃事务 ID 列表（trx_list）进行比较，判断当前事务 2 能看到该记录的版本是哪个。 

所以先拿该记录 DB_TRX_ID 字段记录的事务 ID 4 去跟 Read View 的 up_limit_id 比较，看 4 是否小于 up_limit_id（即 1），所以不符合条件，继续判断 4 是否大于等于 low_limit_id（5），也不符合条件，最后判断 4 是否处于 trx_list 中的活跃事务, 最后发现事务 ID 为 4 的事务不在当前活跃事务列表中，符合可见性条件，所以事务 4 修改后提交的最新结果对事务 2 快照读时是可见的，因此事务 2 能读到的最新数据记录是事务 4 所提交的版本，而事务 4 提交的版本也是全局角度上最新的版本。 

5）RC、RR 的快照度

正是 Read View 生成时机的不同，从而造成 RC、RR 级别下快照读的结果的不同。

场景 1：

场景 2：  

在场景 2 的顺序中，事务 B 在事务 A 提交后的快照读和当前读都是实时的新数据 400，这是为什么呢？

• 这里与场景 1 的唯一区别仅仅是场景 1 的事务 B 在事务 A 修改金额前快照读过一次金额数据，而场景 2 的事务 B 在事务 A 修改金额前没有进行过快照读。

所以我们知道事务中快照读的结果是非常依赖该事务首次出现快照读的地方，即某个事务中首次出现快照读的地方非常关键，它有决定该事务后续快照读结果的能力。

我们这里测试的是更新，其实删除和更新也是一样的，如果事务 B 的快照读是在事务 A 操作之后进行的，那么事务 B 的快照读也是能读取到最新的数据的。

RR 是如何在 RC 的基础上解决不可重复读的？

• 在 RR 级别下的某个事务对某条记录的第一次快照读会创建一个 Read View，将当前系统活跃的其他事务记录起来，此后在调用快照读的时候，还是使用的是同一个 Read View，所以只要当前事务在其他事务提交更新之前使用过快照读，那么之后的快照读使用的都是同一个 Read View，因此对之后的修改不可见；
• 即 RR 级别下快照读生成 Read View 时，Read View 会记录此时所有其他活动事务的快照，这些事务的修改对于当前事务都是不可见的。而早于 Read View 创建的事务所做的修改均是可见。
• 而在 RC 级别下的，事务中每次快照读都会新生成一个快照和 Read View，这就是我们在 RC 级别下的事务中可以看到别的事务提交的更新的原因。

简而言之，在 RC 隔离级别下，每个快照读都会生成并获取最新的 Read View；而在 RR 隔离级别下，同一个事务中的第一个快照读才会创建 Read View，之后的快照读获取的都是同一个 Read View。

6、Redo Log

我们知道磁盘随机读写的效率要低于顺序读写，那么为了保证数据的一致性，可以先将数据通过顺序读写的方式写到日志文件中，然后再将数据写入到对应的磁盘文件中。在这个过程中，顺序的效率要远高于随机的效率。

换句话说，如果实际的数据没有写入到磁盘，只要日志文件保存成功了，那么数据就不会丢失，可以根据日志来进行数据的恢复。

五、MySQL主从复制与读写分离架构

1、主从复制&读写分离简介

随着用户和数据的增多，单机的数据库往往支撑不住快速发展的业务，所以数据库集群就产生了！

读写分离顾名思义就是读和写分离，对应到数据库集群一般都是一主一从（一个主库，一个从库）或者一主多从（一个主库，多个从库），业务服务器把需要写的操作都写到主数据库中，读的操作都去从库查询。主库会同步数据到从库保证数据的一致性。

这种集群方式的本质是把访问的压力从主库转移到从库，也就是在单机数据库无法支撑并发读写，并且读的请求很多的情况下适合这种读写分离的数据库集群。如果写的操作很多的话不适合这种集群方式，因为你的数据库压力还是在写操作上，即使主从了之后压力还是在主库上，这样和单机的区别就不大了。

在单机的情况下，一般我们做数据库优化都会加索引，但是加了索引对查询有优化，但会影响写入，因为写入数据会更新索引。所以做了主从之后，我们可以单独地针对从库（读库）做索引上的优化，而主库（写库）可以减少索引而提高写的效率。

2、实现原理

初始状态时，master 和 slave 的数据要保持一致。

1. master 提交完事务后，写入 binlog。
2. slave 连接到 master，获取 binlog。
3. master创建 dump 线程，推送 binlog 到 slave。
4. slave 启动一个 I/O 线程读取同步过来的 master 的 binlog，记录到 relay log（中继日志）中。
5. slave 再开启一个 SQL 线程从 relay log 中读取内容并在 slave 执行（从 Exec_Master_Log_Pos 位置开始执行读取到的更新事件），完成同步。
6. slave 记录自己的 binlog。

由于 MySQL 默认的复制方式是异步的，主库把日志发送给从库后不关心从库是否已经处理。这样会产生一个问题，假设主库挂了，从库处理失败了，这时从库升为主库后，日志就丢失了。由此产生以下两个概念：

全同步复制

主库写入 binlog 后，强制同步日志到从库，等所有的从库都执行完成后，才返回结果给客户端，显然这个方式的性能会受到严重影响。

半同步复制

从库写入日志成功后返回 ACK（确认）给主库，主库收到至少一个从库的确认就可以认为写操作完成，返回结果给客户端。

主从同步延迟

主库有数据写入之后，同时也写入在 binlog（二进制日志文件）中，从库是通过 binlog 文件来同步数据的，这期间会有一定时间的延迟，可能是 1 秒，如果同时有大量数据写入的话，时间可能更长。

这会导致什么问题呢？比如有一个付款操作，你付款了，主库是已经写入数据，但是查询是到从库查，从库里还没有你的付款记录，所以页面上查询的时候你还没付款。那可不急眼了啊，吞钱了这还了得！打电话给客服投诉！

所以为了解决主从同步延迟的问题有以下几个方法：

1）二次读取

二次读取的意思就是读从库没读到之后再去主库读一下，只要通过对数据库访问的 API 进行封装就能实现这个功能。很简单，并且和业务之间没有耦合。但是有个问题，如果有很多二次读取相当于压力还是回到了主库身上，等于读写分离白分了。而且如有人恶意攻击，就一直访问没有的数据，那主库就可能爆了。

2）写之后的马上的读操作访问主库

也就是写操作之后，立马的读操作指定为访问主库，之后的读操作则访问从库。这就等于写死了，和业务强耦合了。

3）关键业务读写都由主库承担，非关键业务读写分离

类似付钱的这种业务，读写都到主库，避免延迟的问题，但是例如改个头像啊，个人签名这种比较不重要的就读写分离，查询都去从库查，毕竟延迟一下影响也不大，不会立马打客服电话投诉。

分配机制

分配机制的考虑也就是怎么制定写操作是去主库写，读操作是去从库读。

一般有两种方式：代码封装、数据库中间件。

1）代码封装

代码封装的实现很简单，就是抽出一个中间层，让这个中间层来实现读写分离和数据库连接。讲白点就是搞个 provider 封装了 save、select 等通常数据库操作，内部 save 操作的 dataSource 是主库的，select 操作的 dataSource 是从库的。

优点：

1. 实现简单。
2. 可以根据业务定制化变化，随心所欲。

缺点：

1. 如果哪个数据库宕机了，发生主从切换了之后，就得修改配置重启。
2. 如果系统很大，一个业务可能包含多个子系统，一个子系统是 java 写的，一个子系统用 go 写的，这样的话得分别为不同语言实现一套中间层，重复开发。

2）数据库中间件

就是有一个独立的系统，专门来实现读写分离和数据库连接管理，业务服务器和数据库中间件之间是通过标准的 SQL 协议交流的，所以在业务服务器看来数据库中间件其实就是个数据库。

优点：

1. 因为是通过 SQL 协议的所以可以兼容不同的语言不需要单独写一套。
2. 由中间件来实现主从切换，业务服务器不需要关心这点。

缺点：

1. 多了一个系统其实就等于多了一个关心，比如数据库中间件挂了。
2. 多了一个系统就等于多了一个瓶颈，所以对中间件的性能要求也高，因为所有的数据库操作都要先经过它。
3. 中间件实现较为复杂，难度比代码封装高多了。

常用的开源数据库中间件有 Mysql Proxy、Atlas、LVS 等。

为什么使用 MySQL-Proxy 而不是 LVS？

• LVS：分不清读还是写；不支持事务。
• MySQL-Proxy：自动区分读操作和写操作；支持事务（注意在 MySQL-Proxy 中不要使用嵌套查询，否则会造成读和写的混乱）。

解决单点故障

1）解决 Proxy 单点故障问题

MySQL-Proxy 实际上非常不稳定，在高并发或有错误连接的情况下，进程很容易自动关闭，因此打开 --keepalive 参数让进程自动恢复是个比较好的办法，但还是不能从根本上解决问题，通常最稳妥的做法是在每个应用服务器（如 Tomcat）上安装一个 MySQL-Proxy 供自身使用（解决 Proxy 单点故障问题），虽然比较低效但却能保证稳定性。

2）双（多）主机制

Proxy 之后搭 LVS，LVS 为两台主数据库做负载均衡，从数据库从两台主数据库同步，此方案旨在解决：

• 主数据库的单点故障问题（服务不可用、备份问题）
• 分担写操作的访问压力。

不过多主需要考虑自增长 ID 问题，这个需要特别设置配置文件，比如双主可以使用奇偶。总之，主之间设置自增长 ID 相互不冲突就能解决自增长 ID 冲突问题。

总结：

读写分离相对而言是比较简单的，比分表分库简单，但是它只能分担访问的压力，分担不了存储的压力，也就是你的数据库表的数据逐渐增多，但是面对一张表海量的数据，查询还是很慢的，所以如果业务发展的快数据暴增，到一定时间还是得分库分表。

正常情况下，只有当单机真的顶不住压力了才会集群，不要一上来就集群，没这个必要。有关于软件的东西都是越简单越好，复杂都是形势所迫。

一般我们是先优化单机，如优化一些慢查询，优化业务逻辑的调用或者加入缓存等。如果真的优化到没东西优化了，然后才上集群。先读写分离，读写分离之后顶不住就再分库分表。

3、主从复制&读写分离搭建

主从同步的方式也分很多种，一主多从、链式主从、多主多从，根据你的需要来进行设置。

1. 搭建主从复制（双主）

1）服务器二台：分别安装 MySQL 数据库

1. 安装命令：yum -y install mysql-server
2. 配置登录用户的密码：mysqladmin -u root password '密码'
3. 配置允许第三方机器访问本机 MySQL：

delete from user where password = '';
update user set host='%';
flush privileges;

2）分别修改 MySQL 配置

配置 masterA：

[root@adailinux ~]# vim /etc/my.cnf
[mysqld]
datadir = /data/mysql
socket = /tmp/mysql.sock
server_id = 1  # 指定server-id，必须保证主从服务器的server-id不同
auto_increment_increment = 2  # 设置主键单次增量
auto_increment_offset = 1  # 设置单次增量中主键的偏移量：奇数
log_bin = mysql-bin  # 创建主从需要开启log-bin日志文件
log-slave-updates  # 把更新的日志写到二进制文件（binlog）中，台服务器既做主库又做从库此选项必须要开启

配置 masterB：

[root@adailinux ~]# vim /etc/my.cnf
[mysqld]
datadir = /data/mysql
socket = /tmp/mysql.sock
server_id = 2  # 指定server-id，必须保证主从服务器的server-id不同
auto_increment_increment = 2  # 设置主键单次增量
auto_increment_offset = 2  # 设置单次增量中主键的偏移量：偶数
log_bin = mysql-bin  # 创建主从需要开启log-bin日志文件
log-slave-updates = True  # 把更新的日志写到二进制文件（binlog）中

以上为同步配置的核心参数。

server_id 有两个用途： 

1. 用来标记 binlog event 的源产地，就是 SQL 语句最开始源自于哪里。 
2. 用于 IO_thread 对主库 binlog 的过滤。如果没有设置 replicate-same-server-id=1 ，那么当从库的 IO_thread 发现 event 的源与自己的 server-id 相同时，就会跳过该 event，不把该 event 写入到 relay log 中。从库的 sql_thread 自然就不会执行该 event。这在链式或双主结构中可以避免 sql 语句的无限循环。

注意：如果设置多个从服务器，每个从服务器必须有一个唯一的 server-id 值，且与主服务器的以及其它从服务器的都不相同。

分别重启 masterA 和 masterB 并查看主库状态：

[root@adailinux ~]# /etc/init.d/mysqld restart 
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 

masterA：
[root@adailinux ~]# mysql -uroot
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      419 | TSC          | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

masterB：
[root@adailinux ~]# mysql -uroot
mysql> show master status
    -> ;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      419 | TSC          | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

3）配置同步信息

masterA：

[root@adailinux ~]# mysql -uroot
mysql> change master to master_host='192.168.8.132',master_port=3306,master_user='repl',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=419;
#注：IP为masterB的IP（即，从服务器的IP）
mysql> start slave;
Query OK, 0 rows affected (0.05 sec)

mysql> show slave status\G;
在此查看有如下状态说明配置成功：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

masterB：

[root@adailinux ~]# mysql -uroot
mysql>change master to master_host='192.168.8.131',master_port=3306,master_user='repl',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=419; 
Query OK, 0 rows affected, 2 warnings (0.06 sec)

mysql> start slave;
Query OK, 0 rows affected (0.04 sec)

mysql> show slave status\G
在此查看有如下状态说明配置成功：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

4）测试主从同步

在 masterA 上创建一个库，验证 masterB 是否同步创建了该库。

2. 搭建读写分离

场景描述：

• 数据库 Master 主服务器
• 数据库 Slave 从服务器
• MySQL-Proxy 调度服务器

以下操作，均是在 MySQL-Proxy 调度服务器上进行的。

1）MySQL 服务器安装

2）检查/安装系统所需软件包

yum -y install gcc* gcc-c++* autoconf* automake* zlib* libxml* ncurses-devel* libmcrypt* libtool* flex* pkgconfig* libevent* glib* readline*

3）编译安装 lua

MySQL-Proxy 的读写分离主要是通过 rw-splitting.lua 脚本实现的，因此需要安装 lua。

方式 1：一般系统自带。

方式 2：手工安装。

这里我们建议采用源码包进行安装：

cd /opt/install
wget http://www.lua.org/ftp/lua-5.1.4.tar.gz
tar zvfxlua-5.2.3.tar.gz
cd lua-5.1.4
vi src/Makefile
在CFLAGS= -O2 -Wall $(MYCFLAGS) 这一行记录里加上-fPIC，更改为 CFLAGS= -O2 -Wall -fPIC$(MYCFLAGS) 来避免编译过程中出现错误。
make linux（编译到内存）
make install

4）安装 Mysql-Proxy

MySQL-Proxy 可通过此网址获得：http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/

推荐采用已经编译好的二进制版本，因为采用源码包进行编译时，最新版的 MySQL-Proxy 对 automake、glib 以及 libevent 的版本都有很高的要求，而这些软件包都是系统的基础套件，不建议强行进行更新。

并且这些已经编译好的二进制版本在解压后都在统一的目录内，因此建议选择以下版本：

• 32 位 RHEL5 平台：http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/mysql-proxy-0.8.4-linux-rhel5-x86-32bit.tar.gz
• 64 位 RHEL5 平台：http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/mysql-proxy-0.8.4-linux-rhel5-x86-64bit.tar.gz

tar -xzvf mysql-proxy-0.8.3-linux-rhel5-x86-64bit.tar.gz
mv mysql-proxy-0.8.3-linux-rhel5-x86-64bit /opt/mysql-proxy

创建 Mysql-Proxy 服务管理脚本：

cd /opt/mysql-proxy
mkdir scripts
cp share/doc/mysql-proxy/rw-splitting.lua scripts/
chmod +x /opt/mysql-proxy/scripts
mkdir /opt/mysql-proxy/run
mkdir /opt/mysql-proxy/log
mkdir /opt/mysql-proxy/scripts

5）修改读写分离脚本 rw-splitting.lua

修改默认连接，进行快速测试，不修改的话要达到连接数为 4 时才启用读写分离

vi /opt/mysql-proxy/scripts/rw-splitting.lua

-- connection pool
if not proxy.global.config.rwsplitthen
proxy.global.config.rwsplit = {
min_idle_connections = 1,  //默认为4
max_idle_connections = 1,  //默认为8
is_debug = false
}
end

proxy.conf：

[mysql-proxy]
admin-username=root
admin-password=admin
proxy-read-only-backend-addresses=192.168.188.143,192.168.188.139
proxy-backend-addresses=192.168.188.142
proxy-lua-script=/opt/mysql-proxy/bin/rw-splitting.lua
admin-lua-script=/opt/mysql-proxy/lib/mysql-proxy/lua/admin.lua

bin 目录下执行：

./mysql-proxy --defaults-file=/opt/mysql-proxy/proxy.conf

mysql-proxy 脚本参数详解：

• --proxy_path=/opt/mysql-proxy/bin：定义 mysql-proxy 服务二进制文件路径。
• --proxy-backend-addresses=192.168.10.130:3306：定义后端主服务器地址。
• --proxy-lua-script=/opt/mysql-proxy/scripts/rw-splitting.lua：定义 lua 读写分离脚本路径。
• --proxy-id=/opt/mysql-proxy/run/mysql-proxy.pid：定义 mysql-proxy PID 文件路径。
• --daemon：定义以守护进程模式启动。
• --keepalive：使进程在异常关闭后能够自动恢复。
• --pid-file=$PROXY_PID：定义 mysql-proxy PID 文件路径。
• --user=mysql：以 mysql 用户身份启动服务。
• --log-level=warning：定义 log 日志级别，由高到低分别有(error|warning|info|message|debug)。
• --log-file=/opt/mysql-proxy/log/mysql-proxy.log：定义 log 日志文件路径。

6）测试读写分离 

1. 登录 Proxy：mysql -u -p -h<proxy_ip> -P4040
2. stop slave
3. 在 Proxy 上插数据后，验证主数据库新增数据，而从没有新增数据。