# mysql进阶-下

# 第六章 索引

学习本章节内容，我们最好能模拟一个数据量比较大的环境，我使用nodejs模拟了600多万条数据，大家可自行下载：

数据库表如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `user_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `user_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用户账号',
  `nick_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用户昵称',
  `email` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '' COMMENT '用户邮箱',
  `sex` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '0' COMMENT '用户性别（0男 1女 2未知）',
  `avatar` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '' COMMENT '头像地址',
  `password` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '' COMMENT '密码',
  `login_ip` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '' COMMENT '最后登录IP',
  `login_date` datetime NULL DEFAULT NULL COMMENT '最后登录时间',
   `text` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci COMMENT '测试文本',
  PRIMARY KEY (`user_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '用户信息表' ROW_FORMAT = Dynamic;

我这里使用nodejs（后边会学）的脚本导入了600万条数据，用来模拟，脚本如下，大家也可以在我们网站自行获取：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59

// 使用 Mock
var Mock = require('mockjs')
// mysql8.0需要执行下边的sql，否则nodejs不支持
// ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '自己的密码';
var mysql = require('mysql');
// 定义连接池
const pool = mysql.createPool({
    host: "127.0.0.1", // 主机地址
    port: 3306,
    database: "ydl", // 数据库名字
    user: "root", // 连接数据库的用户名
    password: "root", // 连接数据库密码
    connectionLimit: 30, // 连接池最大连接数
    multipleStatements: true // 允许执行多条sql语句
})


function insert(connection) {
    let content = '';
    for (var i = 0; i < 100; i++) {
        // 使用mock.js模拟数据
        var user = Mock.mock({
            userName: Mock.mock('@name(true)'),
            nickName: Mock.mock('@cname()'),
            email: Mock.mock('@email()'),
            sex: Math.random() > 0.5 ? '男' : '女',
            loginIp: Mock.mock('@ip()'),
            loginDate: Mock.mock('@datetime()'),
            password: Mock.mock('@word(5, 10)'),
            avatar: Mock.mock('@url()'),
            text: Mock.mock('@cparagraph(30)'),
        });

        // 每次存入1000条
        let insertData = '('
        for (key in user) {
            insertData += '\'' + user[key] + '\'' + ','
        }
        insertData = insertData.substring(0, insertData.length - 1) + ')';
        content += insertData + ',';
    }

    content = content.substring(0, content.length - 1);
    let sql = `insert into user (user_name,
        nick_name,email,sex,login_ip,login_date,password,avatar,text) values
        ${content}`;
    connection.query(sql);
    connection.release();
}
// 循环60000万次，总计能插入600万条
for (let i = 0; i < 60000; i++)
 {

    pool.getConnection(function (err, connection) {
        // 代码
        insert(connection)
        console.log('第'+i+'条sql执行成功！')
    }) 
}

对应的sql文件也准备好了，文件大小1.18G，大家可以自行下载：

准备工作做好之后，我们就可以深入的去学习这些知识了。

1
2
3

select count(*) from ydl_user;  -- 5.429
select * from ydl_user where user_id = 1000000; -- 0.355s
select * from ydl_user where user_name = 'Jennifer Susan Johnson';   -- 4.715s

# 一、数据结构

一方面mysql的数据是存储在磁盘上的，另一方面还要满足对日常操作如【增删改查】的高效稳定的支持，我们当然可以采用更好的硬件来提升性能，但是选用合适的数据结构也很关键，innodb采用的是一种名为【b+树】的数据结构。

我们之前已经学习过innodb中的数据是以【行】为单位，存在一个个大小为16k的【页】中，刚才的b+树的作用就是按照一个的组织形式，将所有的【页】组织关联起来。

# 1、B-树

我们要了解【B+树】，首先要了解一下【B-树】，这里的 B 表示 balance( 平衡的意思)，B-树是一种【多路自平衡的搜索树】，它类似普通的平衡二叉树，不同的一点是B-树允许每个节点有更多的子节点。下图是 B-树的简化图.

B-树有如下特点:

1. 所有键值分布在整颗树中；
2. 任何一个关键字出现且只出现在一个结点中；
3. 搜索有可能在非叶子结点结束；
4. 在关键字全集内做一次查找，性能逼近二分查找；

# 2、B+树

【B+树】是【B-树】的变体，也是一种多路搜索树, 它与 B- 树的不同之处在于:

1. 所有关键字存储在叶子节点
2. 为所有叶子结点增加了一个双向指针

简化 B+树 如下图：

# 3、选型缘由

问题一：为什么在b-树或b+树中选择？

• mysql数据模型更适合用这类数据结构，一条数据中通常包含【id】+【其他列数据】，我们可以很轻松的根据id组织一颗B+树。
• 我们知道innodb使用【页】（这是inndb管理数据的最小单位）保存数据，一页（16k），b+树中的每个节点都是一页数据。

问题二：为什么选择B+树？

• 相同的空间，不存放【整行数据】就能存【更多的id】，b+树能使每个节点能检索的【范围更大、更精确，极大的减少了I/O操作，保证b+树的层高较低，通常3到4层的层高就能支持百万级别的访问】。
• Mysql是一种关系型数据库，【区间访问】是很常见的一种情况，B+树叶节点增加的双向指针，加强了区间访问性，可使用在范围区间查询的情况。

# 4、发现索引

我们发现当使用id去查询数据时，效率很高，因为使用id可以利用B+树的特性，加速查询，请看以下两条sql的执行效率：

1
2

select * from ydl_user where id = 1                              -- 使用时间0.011s
select * from ydl_user where email = 'm.szi@xwsrnhp.pl'          -- 使用时间4.284s

我们发现，查询相同的记录，使用【id列】比使用【emil列】快了389倍，原因如下：

• 使用id列可以利用B+树的特性，由上自下查询。
• 使用email列只能从叶子节点进行【全表扫描】，一个一个的比较。

那么如果我想提升使用其他字段的查询效率，应该怎么做呢？

首先，我们应该想到的思路就是，按照这个逻辑再给其他的字段也创建一个这样的结构不就好了，如下：

但是我们会发现，如果我们不断的创建类似的结构，数据会保存很多次，1个G的数据可以膨胀为5G甚至10G，所以我们可以进行优化，在叶子节点中只【保存id】而不保存全部数据，查到id后再【回表】（回到原来的结构中根据id进行查询）查询整条记录，其结构如下：

其实这就是我们日常工作中经常创建的【索引】。

# 二、索引的分类和创建

# 1、聚簇索引和非聚簇索引

我们在上边的例子中，【主键和数据】共存的索引被称之为【聚簇索引】，其他的，比如我们使用【姓名列+主键】建立的索引，可以称为【非聚簇索引】，或者【辅助索引】，或者【二级索引】，同时聚簇索引只有在innodb引擎中才存在，而在myIsam中是不存在的，如下图：

InnoDB使用的是【聚簇索引】，他会将【主键】组织到一棵B+树中，而【行数据】就储存在叶子节点上，若使用where id = 14这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。

若对Name列进行条件搜索，且name列已建立【索引】，则需要两个步骤：

• 第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。
• 第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。（重点在于通过其他键需要建立辅助索引）

如下图：

MyIsam使用的是【非聚簇索引】，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助列。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个【地址指向真正的表数据】，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

tips：

• 聚簇索引【默认使用主键】，如果表中没有定义主键，InnoDB 会选择一个【唯一且非空】的列代替。如果没有这样的列，InnoDB 会隐式定义一个主键【类似oracle中的RowId】rowid来作为聚簇索引的列。

• 如果涉及到大数据量的排序、全表扫描、count之类的操作的话，还是MyIsam占优势些，因为索引所占空间小，这些操作是需要在内存中完成的。

小问题：主键为什么建议使用自增id?

• 主键最好不要使用uuid，因为uuid的值太过离散，不适合排序且可能出现新增加记录的uuid，会插入在索引树中间的位置，出现页分裂，导致索引树调整复杂度变大，消耗更多的时间和资源。
• 聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，它会不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但如果是自增的id，它只需要一 页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。

本章节中讲述了聚簇索引和二级键索引，对于【二级索引】而言，根据其不同的特性，我们又可以分为普通索引、唯一索引、复合索引等，接下来会一一讲解。

# 2、普通索引 （常规索引）(normal)

就是普普通通的索引，没有什么特殊要求，理论上任何列都可以当做普通索引，创建方式如下：

**第一步：**创建索引前先执行下列语句，观察执行时间：

1

select * from user where user_name ='Dorothy William Harris';  -- 整个执行时间为4.297s

第二步：创建user_name列的索引：

1

create index idx_user_name on user(user_name);   -- 整个索引创建时间为24.502s

**结论：**创建索引是一个很费时间的操作。

**第三步：**再次执行下列语句

1

select * from ydl_user where user_name ='Dorothy William Harris';   -- 执行时间0.031s

**结论：**创建索引后，我们的执行效率提升了138倍。

**第四部：**删除索引

1

drop index idx_user_name on ydl_user; 

其他创建索引的方法，如下：

（1）创建email列的索引，索引可以截取length长度，只使用这一列的前几个字符

1

create index idx_email on user(email(5));     --执行时间16.174s

重点：

有的列【数据量比较大】，使用前几个字符就能【很快标识】出来一行数据，那我们就可以使用这种方式建立索引，比如我们的邮箱，邮箱很多后缀是相同的我们完全可以忽略。

（2）使用修改表的方式添加索引

1

alter table user add index idx_email (email);

（3）建表时时，同时创建索引

1
2
3
4
5
6

create table tbl_name(
    tid int,
    tname varchar(20),
    gender varchar(1),
    index [indexName] (fieldName(length))
)

# 3、唯一索引（UNIQUE ）

对列的要求：索引列的值不能重复

创建表的同时，创建索引：

1
2
3
4
5
6

create table tbl_name(
    tid int,
    tname varchar(20),
    gender varchar(1),
    unique index unique_index_tname (tname)
)

独立的sql语句创建索引，我们的邮箱，用户名就应该创建唯一索引，姓名就应该是普通索引：

1

create unique index idx_email on user(email);

通过alter语句添加索引：

1

ALTER table mytable ADD UNIQUE [ux_indexName] (username(length))

唯一索引和主键的区别：

• 唯一索引列允许空值，而主键列不允许为空值。
• 主键列在创建时，已经默认为非空值 + 唯一索引了。
• 主键可以被其他表引用为外键，而唯一索引不能。
• 一个表最多只能创建一个主键，但可以创建多个唯一索引。
• 主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。

唯一约束和唯一索引的区别：

• 唯一约束和唯一索引，都可以实现列数据的唯一，列值可以为null。
• 创建唯一约束，会自动创建一个同名的唯一索引，该索引不能单独删除，删除约束会自动删除索引。唯一约束是通过唯一索引来实现数据唯一。
• 创建一个唯一索引，这个索引就是独立的索引，可以单独删除。
• 如果一个列上想有约束和索引，且两者可以单独的删除。可以先建唯一索引，再建同名的唯一约束。

# 4、多个二级索引的组合使用

记住一点：mysql在执行查询语句的时候一般只会使用【一个索引】，除非是使【用or连接的两个索引列】会产生索引合并。

我们针对某电商平台的检索功能做了优化，添加了三个索引，三个字段分别为【品牌】、【价格】、【销量】这三个的索引结构如下:

（1）品牌的索引结构：

（2）价格的索引结构：

（3）销量的索引结构：

针对以上的索引我们进行如下的查询，分析检索过程：

1. 我们要检索品牌为阿玛尼（Armani）的包包

   **第一步：**通过【品牌索引】检索出所有阿玛尼的商品id，回表查询，得到结果。

   **结论：**会使用一个索引。

2. 我们要检索名称为阿玛尼（Armani），价格在1万到3万之间的包包

   查询的步骤如下：

   **第一步：**通过【品牌索引】检索出所有阿玛尼的商品id。

   **第二步：**直接回表扫描，根据剩余条件检索结果。

   **结论：**只会使用第一个索引。

3. 我们要检索名称为阿玛尼（Armani），价格为26800，且销量在50以上的包包

   查询的步骤如下：

   **第一步：**通过【品牌索引】检索出所有阿玛尼的商品id，进行缓存。

   **第二步：**直接回表扫描，根据剩余条件检索结果。

   **结论：**只会使用第一个索引。

4. 我们要检索名称为阿玛尼（Armani）或名称为LV的包包

   **第一步：**通过【品牌索引】检索出所有阿玛尼的商品id，得到结果。

   **结论：**我们经常听说，有or索引会失效，但是像这样的【type =‘Armani’ or type = ‘LV’】并不会，他相当于一个in关键字，会使用一个索引。

5. 我们要检索名称为阿玛尼（Armani）或价格大于8000的包包

   **第一步：**通过【品牌索引】检索出所有阿玛尼的商品id，进行缓存。

   **第二步：**通过【价格索引】检索出价格在5万到7万之间的商品id，这是一个连接条件带有【or的查询】，所以需要和上一步的结果进行【并集】，得到结果。

   **结论：**这个过程叫【索引合并】当检索条件有or但是所有的条件都有索引时，索引不失效，可以走【两个索引】。

6. 我们要检索名称为阿玛尼（Armani），且价格大于8000，且【产地（该列无索引）】在北京的包包

   **第一步：**通过【品牌索引】检索出所有阿玛尼的商品id。

   **第二步：**直接回表扫描，根据剩余条件检索结果。

   **结论：**只会使用第一个索引。

7. 我们要检索名称为阿玛尼（Armani）或价格大于8000，或【产地（该列无索引）】在北京的包包

   **第一步：**优化器发现【产地列】无索引，同时连接的逻辑是【or】没有办法利用【索引】优化，只能全表扫描，索引失效。

   **结论：**发生全表扫描，索引失效，条件中有没建立索引的列，同时关联条件是or。

# 5、复合索引（联合索引）重要

当【查询语句】中包含【多个查询条件，且查询的顺序基本保持一致】时，我们推荐使用复合索引，索引的【组合使用】效率是低于【复合索引】的。

比如：我们经常按照A列、B列、C列进行查询时，通常的做法是建立一个由三个列共同组成的【复合索引】而不是对每一个列建立【普通索引】。

创建联合索引的方式如下：

1

alert table test add idx_a1_a2_a3 table (a1,a2,a3) 

1
2

-- 28.531s
create index idx_user_nick_name on ydl_user(user_name,nick_name,email(7));

复合索引的结构如下，复合索引会优先按照第一列排序，第一列相同的情况下会按照第二列排序，以此类推，如下图：

我们不妨把上边的图，转化为下边的表格，看起来会好一些：

认真阅读了上边的介绍和图形，我们再次思考以下几个问题：

1. 我们要检索名称为阿玛尼（Armani）的包包

   **第一步：**通过【品牌索引】检索出所有阿玛尼的商品id，回表查询，得到结果。

   **结论：**会使用第一部分索引。

2. 我们要检索名称为阿玛尼（Armani），价格在1万到3万之间的包包

   查询的步骤如下：

   **第一步：**通过【品牌索引】检索出所有阿玛尼的叶子节点。

   **第二步：**在【满足上一步条件的叶子节点中】查询价格在1万到3万之间的包包的列，查询出对应的id，回表查询列数据。

   **结论：**会使用复合索引的两个部分。

3. 我们要检索名称为阿玛尼（Armani）或价格大于8000的包包

   **第一步：**优化器发现我们并没有一个【价格列】的单独的二级索引，此时要查询价格大于8000的包，必须进行全表扫描。

   **结论：**但凡查询的条件中没有【复合索引的第一部分】，索引直接【失效】，全表扫描。

4. 我们要检索名称为阿玛尼（Armani），且价格大于8000，且【产地（该列无索引）】在北京的包包

   **第一步：**通过【品牌索引】检索出所有阿玛尼的叶子节点。

   **第二步：**在【满足上一步条件的叶子节点中】查询价格大于8000元的包包的叶子节点。

   **第三步：**因为【产地列】无索引，但是是【and】的关系，我们只需要将上一步得到的结果回表查询，在这个很小的范围内，检索产地是不是北京即可。

   **结论：**可以使用复合索引的两个部分。

5. 我们要检索名称为阿玛尼（Armani）和LV之间，价格为在1万到3万的包包

   查询的步骤如下：

   **第一步：**通过【品牌索引】检索出所有阿玛尼和LV的所有叶子节点。

   **第二步：**我们本想在第一步的结果中，快速定位价格的范围，但是发现一个问题，由于第一步不是等值查询，会导致后边的结果不连续，必须对【上一步的结果】全部遍历，才能拿到对应的结果。

   **结论：**只会使用复合索引的第一个部分，这个就引出了【复合索引中特别重要的一个概念】-【最左前缀原则】。

**重点：**最左前缀原则：

（1）最左前缀匹配原则，非常重要的原则，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的顺序可以任意调整。

（2）=和in可以乱序，比如a = 1 and b < 2 and c = 3 ，咱们建立的索引就可以是（a,c,b）或者（c,a,b）。

**思考：**为什么联合索引的性能会比索引的组合使用效率高。

# 6、全文索引（FULLTEXT）

做全文检索（不如百度的搜索功能）使用的索引，但是这种场景，我们有更好的替代品，如：ElacticSearch，所以实际使用不多，只当了解。

使用 like + % 实现的模糊匹配有点类似全文索引。但是对于大量的文本数据检索，全文索引比 like + % 快 N 倍，速度不是一个数量级，但是全文索引可能存在【精度问题】。同时普通索引在使用like时如果%放在首位，索引会失效。

全文索引的版本支持

1. MySQL 5.6 以前的版本，只有 MyIsam 存储引擎支持全文索引。
2. MySQL 5.6 及以后的版本，MyIsam 和 InnoDB 存储引擎均支持全文索引。
3. 只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。

使用全文索引的注意

1. 使用全文索引前，搞清楚版本支持情况。
2. 全文索引比 like + % 快 N 倍，但是可能存在精度问题。
3. 如果需要全文索引的是大量数据，建议先添加数据，再创建索引。
4. 对于中文，可以使用 MySQL 5.7.6 之后的版本，或者第三方插件。

（1）创建表时创建全文索引

1
2
3
4

create table ydlclass_user (    
    ..   
    FULLTEXT KEY fulltext_text(text)  
) 

（2）在已存在的表上创建全文索引

1

create fulltext index fulltext_text  on ydlclass_user(text);

本次创建用时143s：

（3）通过 SQL 语句 ALTER TABLE 创建全文索引

1

alter table ydlclass_user add fulltext index fulltext_text(text);

（4）直接使用 DROP INDEX 删除全文索引

1

drop index fulltext index on ydlclass_user;

（5）全文检索的语法

1

select * from ydlclass_user where match(text) against('高号便法还历只办二主厂向际');

# 8、hash索引

hash索引是Memory存储引擎的默认方式，而且只有memory引擎支持hash索引，memory的数据是放在内存中的，一旦服务关闭，表中的数据就会丢失，我们可以使用如下的sql创建一张表:

1
2
3
4
5
6

CREATE TABLE `hash_user`  (
  `user_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `user_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用户账号',
  ......
) ENGINE = Memory CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '用户信息表' ROW_FORMAT = Dynamic;

合理的使用memory引擎可以极大的提升性能，针对memory引擎的特点重启丢失），我们最好在其中存储一些公共的、常用的、不经常发生改变的数据，比如一些字典数据、配置数据等。同时，这些数据最好持久化在一些其他的地方，比如配置文件、其他的表，在程序启动的时候，主动的进行加载，我们可以使用如下sql，将一张表的数据加载到内存中：

1

insert into hash_user select * from ydl_user where user_id < 2000000;

我们在执行的过程种，可能有如下错误：

他告诉我，这个表使用的内存满了，放不下了，我们只需要调节下边两个参数，修改配置文件重启即可：

1
2

tmp_table_size = 4096M
max_heap_table_size = 4096M

基础工作完成，写几个sql语句尝试一下，我们发现真的一个字：快。

我们执行一下的sql

1

select * from hash_user where email = 'i.jnoyelrsg@rpnglcvh.museum'  -- 0.189s

创建一个hash索引

1

create index hash_idx_user_name using hash on hash_user(email);

再次查询

1

select * from hash_user where email = 'i.jnoyelrsg@rpnglcvh.museum'  -- 0.017s

也有不错的效果。

关于hash索引需要了解的几点：

• hash是一种key-value形式的数据结构。实现一般是数组+链表的结构，通过hash函数计算出key在数组中的位置，然后如果出现hash冲突就通过链表来解决。当然还有其他的解决hash冲突的方法。hash这种数据结构是很常用的，比如我们系统使用HashMap来构建热点数据缓存，存取效率很好。
• 即使是相近的key，hash的取值也完全没有规律，索引hash索引不支持范围查询。
• hash索引存储的是hash值和行指针，所以通过hash索引查询数据需要进行两次查询（首先查询行的位置，然后找到具体的数据）。
• hash索引查询数据的前提就是计算hash值，也就是要求key为一个能准确指向一条数据的key，所以对于like等一类的匹配查询是不支持的。
• 只要是只需要做等值比较查询，而不包含排序或范围查询的需求，都适合使用哈希索引。

# 7、空间索引（SPATIAL）

MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型。这是在地理位置领域使用的一种索引，其他场景用的很少，所以不需要深入学习。

# 三、explain的用法

explain关键字可以模拟MySQL优化器执行SQL语句，可以很好的分析SQL语句或表结构的性能瓶颈。

explain的使用很简单，只需要在目标sql前加上这个关键字就可以了：

执行explain会产生以下11列内容，如下：

# 1、id字段

select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序

（1） id相同

id如果相同，可以认为是一组，执行顺序从上至下，如下查询语句：

1

explain select * from student s, scores sc where s.id = sc.s_id

（2） id不同

如果是子查询，id的序号会递增，id的值越大优先级越高，越先被执行例子

1
2
3

explain select * from student where age > (
    select age from student where name = '连宇栋'
);

（3）id部分相同部分不同

id如果相同，可以认为是一组，从上往下顺序执行在所有组中，id值越大，优先级越高，越先执行例子：

1
2
3
4

explain 
select * from student s, scores sc where s.id = sc.s_id
union
select * from student s, scores sc where s.id = sc.s_id;

# 2、select_type字段

（1）SIMPLE

简单查询，不包含子查询或Union查询的sql语句。

（2）PRIMARY

查询中若包含任何复杂的子部分，最外层查询则被标记为主查询。

（3） SUBQUERY

在select或where中包含子查询。

（4）UNION

若第二个select出现在uion之后，则被标记为UNION。

（6）UNION RESULT

从UNION表获取结果的合并操作。

# 3、type字段

最好到最差备注：掌握以下10种常见的即可NULL>system>const>eq_ref>ref>ref_or_null>index_merge>range>index>ALL

（1） NULL

MySQL能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引，比如通过id没有找到例子：

1

explain select min(id) from student;

（2）system

表只有一行记录（等于系统表），这是const类型的特列，平时不大会出现，可以忽略，我也没有实测出来。

1

explain select * from mysql.proxies_priv

我实测一个只有一行记录的系统表，同样是all。

（3） const

表示通过索引一次就找到了，const用于比较primary key或uique索引，因为只匹配一行数据，所以很快，如主键置于where列表中，MySQL就能将该查询转换为一个常量例子：

1

explain select * from student where id = 1;

4. eq_ref

唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描例子：

被驱动表使用主键索面，结果唯一

1

explain select * from scores sc left join student s on s.id = sc.s_id

5. ref

非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，返回所有匹配某个单独值的行，然而可能会找到多个符合条件的行，应该属于查找和扫描的混合体例子：

1
2

explain select * from student where name = '白杰'
explain select * from student s left join scores sc on s.id = sc.s_id

6. ref_or_null

类似ref，但是可以搜索值为NULL的行例子：

1

explain select * from student s where name = '白杰' or name is null

7. index_merge

表示使用了索引合并的优化方法例子：

1

explain select * from student where id = 1 or name ='李兴';

8. range

只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引一般就是在你的where语句中出现between、<>、in等的查询。例子：

1

explain select * from student where id between 4 and 7;

9. index（全索引扫描）

Full index Scan，Index与All区别：index只遍历索引树，通常比All快因为索引文件通常比数据文件小，也就是虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘读的。例子：

1

explain select name from student;

10. ALL（全表扫）

Full Table Scan，将遍历全表以找到匹配行例子：

1

explain select * from student;

# 4、table字段

表示数据来自哪张表

# 5、possible_keys字段

显示可能应用在这张表中的索引，一个或多个查询涉及到的字段若存在索引，则该索引将被列出，但不一定被实际使用

# 6、key字段

实际使用到的索引，如果为NULL，则没有使用索引查询中若使用了覆盖索引（查询的列刚好是索引），则该索引仅出现在key列表

# 7、key_len字段

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度在不损失精确度的情况下，长度越短越好key_len显示的值为索引字段最大的可能长度，并非实际使用长度即key_len是根据定义计算而得，不是通过表内检索出的

# 8、ref字段

哪些列或常量被用于查找索引列上的值

# 9、rows字段

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数

# 10、partitions字段

匹配的分区

# 11、filtered字段

它指返回结果的行占需要读到的行(rows列的值)的百分比

# 12、Extra字段

该列包含不适合在其它列中显示，但十分重要的额外信息，我们列举几个例子：

（1）Using filesort

只要使用非索引字段排序，就会出现这样的内容。

（2）Using temporary

使用了临时表保存中间结果，MySQL在对结果排序时使用临时表，常见于排序order by 和分组查询group by例子：

（3）Using where

使用了where条件例子：

（4）impossible where

where子句的值总是false，不能用来获取任何数据：

1

explain select * from student where name = '白洁' and name = '李兴';

（5）Select tables optimized away

SELECT操作已经优化到不能再优化了（MySQL根本没有遍历表或索引就返回数据了）例子：

1

explain select min(id) from student;

（6）no matching row in const table

1

explain select * from student where id < 100 and id > 200;

# 三、使用索引的问题

设计好MySql的索引可以让你的数据库飞起来。但是，不合理的创建索引同样会产生很多问题？我们在设计MySql索引的时候有一下几点注意：

# 1、哪些情况下适合建索引

• 频繁作为where条件语句查询的字段
• 关联字段需要建立索引
• 分组，排序字段可以建立索引
• 统计字段可以建立索引，例如count()，max()等

小案例：还记得在学习临时表时，分析过group by的执行流程吗（分组字段没有索引）？有了索引之后的分组执行流程如下：

直接使用索引信息，统计每个组的人数，直接返回。

# 2、哪些情况下不适合建索引

• 频繁更新的字段不适合建立索引

• where条件中用不到的字段不适合建立索引

• 表数据可以确定比较少的不需要建索引

• 数据重复且发布比较均匀的的字段不适合建索引（唯一性太差的字段不适合建立索引），例如性别，真假值

• 参与列计算的列不适合建索引，索引会失效

# 3、能用复合索引的要使用复合索引

# 4、null值也是可以走索引的，他被处理成最小值放在b+树的最左侧

# 5、使用短索引

对字符串的列创建索引，如果可能，应该指定一个前缀长度。例如，如果有一个CHAR(255)的 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

# 6，排序的索引问题

mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要，最好给这些列创建复合索引。

# 7、MySQL索引失效的几种情况

• 如果条件中有or，即使其中有条件带索引也不会使用走索引，除非全部条件都有索引
• 复合索引不满足最左原则就不能使用全部索引
• like查询以%开头
• 存在列计算

1

explain select * from student where age = (18-1)

• 如果mysql估计使用全表扫描要比使用索引快，则不使用索引，比如结果的量很大
• 存在类型转化

1
2
3
4
5

-- 索引不失效
explain select * from student where age = '18'  
explain select * from ydl_user where login_date = '2008-05-31 17:20:54'
-- 索引失效 本来是字符串，你使用数字和他比较
explain select * from student where gander = 1

# 第七章 锁机制

锁是为了保证数据库中数据的一致性，使各种【共享资源】在被访问时变得【有序】而设计的一种规则。

MysQL中不同的存储引擎支持不同的锁机制。 InoDB支持【行锁】，有时也会升级为表锁，MyIsam只支持表锁。

• 【表锁】的特点就是开销小、加锁快，不会出现死锁。锁粒度大，发生锁冲突的概率小，并发度相对低。

• 【行锁】的特点就是开销大、加锁慢，会出现死锁。锁粒度小，发生锁冲突的概率高，并发度高。

  今天我们讲锁主要从InnoDB引擎来讲，因为它既支持行锁、也支持表锁。

# 一、InnoDB的锁类型

InnoDB的锁类型主要有读锁(共享锁)、写锁(排他锁)、意向锁和MDL锁。

# 1、s锁

读锁（共享锁，shared lock）简称S锁。一个事务获取了一个数据行的读锁，其他事务也能获得该行对应的读锁，但不能获得写锁，即一个事务在读取一个数据行时，其他事务也可以读，但不能对该数行增删改的操作。

**注：**读锁是共享锁，多个事务可以同时持有，当有一个或多个事务持有共享锁时，被锁的数据就不能修改。

简而言之：就是可以多个事务读，但只能一个事务写。

读锁是通过【select.... lock in share mode】语句给被读取的行记录或行记录的范围上加一个读锁,让其他事务可以读，但是要想申请加写锁，那就会被阻塞。

事务一：

1
2

begin;
select * from ydl_student where id = 1 lock in share mode;

事务二：

1
2

begin;
update ydl_student set score = '90' where id = 1;

卡住了，说明程序被阻塞，确实加了锁。

s锁是可以被多个事务同时获取的，我们在两个不同的事务中分别对同一行数据加上s锁，结果都可以成功，如下图：

# 2、x锁

写锁，也叫排他锁，或者叫独占所，简称x锁（exclusive）。一个事务获取了一个数据行的写锁，既可以读该行的记录，也可以修改该行的记录。但其他事务就不能再获取该行的其他任何的锁，包括s锁，直到当前事务将锁释放。【这保证了其他事务在当前事务释放锁之前不能再修改数据】。

**注：**写锁是独占锁，只有一个事务可以持有，当这个事务持有写锁时，被锁的数据就不能被其他事务修改。

（1）一些DML语句的操作都会对行记录加写锁。

事务一：

1
2

begin;
update ydl_student set score = '90' where id = 1;

事务二：

1
2

begin;
update ydl_student set score = '88' where id = 1;

卡住了，说明程序被阻塞，确实加了锁。但是，我们发现其他事务还能读，有点不符合逻辑，这是应为mysql实现了MVCC模型，后边会详细介绍。

（2）比较特殊的就是select for update，它会对读取的行记录上加一个写锁，那么其他任何事务不能对被锁定的行上加任何锁了，要不然会被阻塞。

事务一：

1
2

begin;
select * from ydl_student where id = 1 for update;

事务二：

1
2

begin;
update teacher set name = 'lucy2' where id = 1;

卡住了，说明加了锁了。

（3）x锁是只能被一个事务获取，我们在两个不同的事务中分别对同一行数据加上x锁，发现后者会被阻塞，如下图：

# 3、记录锁（Record Lock）

记录锁就是我们常说的行锁，只有innodb才支持，我们使用以下四个案例来验证记录锁的存在：

（1）两个事务修改【同一行】记录，该场景下，where条件中的列不加索引。

事务一：

1
2

begin;
update ydl_student set score = '77' where name = 'jack';

事务二：

1
2

begin;
update ydl_student set score = '80' where name = 'jack';

发现事务二卡住了，只有事务一提交了，事务二才能继续执行，很明显，这一行数据被【锁】住了。

（2）两个事务修改同表【不同行】记录，此时where条件也不加索。

事务一：

1
2

begin;
update ydl_student set score = '76' where name = 'hellen';

事务二：

1
2

begin;
update ydl_student set score = '66' where name = 'jack';

现事务二卡住了，只有事务一提交了，事务二才能继续执行，很明显，表被【锁】住了。

（3）两个事务修改【同一行】记录，where条件加索引

事务一：

1
2

begin;
update ydl_student set score = '99' where name = 'jack';

事务二：

1
2

begin;
update ydl_student set score = '79' where name = 'jack';

现事务二卡住了，只有事务一提交了，事务二才能继续执行，很明显，这一行数据被【锁】住了。

（4）两个事务修改同表【不同行】记录，此时where条件加索。

事务一：

1
2

begin;
update ydl_student set score = '77' where name = 'hellen';

事务二：

1
2

begin;
update ydl_student set score = '77' where name = 'jack';

发现都可以顺利修改，说明锁的的确是行。

**证明：**行锁是加在索引上的，这是标准的行级锁。

# 4、间隙锁（GAP Lock）

间隙锁帮我们解决了mysql在rr级别下的一部分幻读问题。间隙锁锁定的是记录范围，不包含记录本身，也就是不允许在某个范围内插入数据。

间隙锁生成的条件：

1、A事务使用where进行范围检索时未提交事务，此时B事务向A满足检索条件的范围内插入数据。

2、where条件必须有索引。

第一步把teacher表的id的4改成8

事务一：

1
2

begin;
select * from ydl_student where id between 3 and 7 lock in share mode;

事务二：

1
2

begin;
insert into ydl_student values (5,'tom',66,'d');

发现卡住了，第一个事务会将id在3到7之间的数据全部锁定，不允许在缝隙间插入。

事务三：

1
2

begin;
insert into ydl_student values (11,'tom',66,'d');

插入一个id为11的数据，竟然成功了，因为11不在事务一的检索的范围。

# 5、记录锁和间隙锁的组合（next-key lock）

临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含【索引记录】，又包含【索引区间】。

注：临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。

# 6、MDL锁

MySQL 5.5引入了meta data lock，简称MDL锁，用于保证表中元数据的信息。在会话A中，表开启了查询事务后，会自动获得一个MDL锁，会话B就不可以执行任何DDL语句，不能执行为表中添加字段的操作，会用MDL锁来保证数据之间的一致性。

元数据就是描述数据的数据，也就是你的表结构。意识是在你开启了事务之后获得了意向锁，其他事务就不能更改你的表结构。MDL锁都是为了防止在事务进行中，执行DDL语句导致数据不一致。

# 7、死锁问题

发生死锁的必要条件有4个，分别为互斥条件、不可剥夺条件、请求与保持条件和循环等待条件：

• 互斥条件，在一段时间内，计算机中的某个资源只能被一个进程占用。此时，如果其他进程请求该资源，则只能等待。
• 不可剥夺条件，某个进程获得的资源在使用完毕之前，不能被其他进程强行夺走，只能由获得资源的进程主动释放。
• 请求与保持条件，进程已经获得了至少一个资源，又要请求其他资源，但请求的资源已经被其他进程占有，此时请求的进程就会被阻塞，并且不会释放自己已获得的资源。
• 循环等待条件，系统中的进程之间相互等待，同时各自占用的资源又会被下一个进程所请求。例如有进程A、进程B和进程C三个进程，进程A请求的资源被进程B占用，进程B请求的资源被进程C占用，进程C请求的资源被进程A占用，于是形成了循环等待条件，如图1-7所示。

我模拟了一个死锁场景，如下：

InnoDB使用的是行级锁，在某种情况下会产生死锁问题，所以InnoDB存储引擎采用了一种叫作等待图（wait-for graph）的方法来自动检测死锁，如果发现死锁，就会自动回滚一个事务。

在MySQL中，通常通过以下几种方式来避免死锁。

• 尽量让数据表中的数据检索都通过索引来完成，避免无效索引导致行锁升级为表锁。
• 合理设计索引，尽量缩小锁的范围。
• 尽量减少查询条件的范围，尽量避免间隙锁或缩小间隙锁的范围。
• 尽量控制事务的大小，减少一次事务锁定的资源数量，缩短锁定资源的时间。如果一条SQL语句涉及事务加锁操作，则尽量将其放在整个事务的最后执行。

# 二、表锁

1、对于InnoDB表，在绝大部分情况下都应该使用【行级锁】，因为事务和行锁往往是我们之所以选择InnoDB表的理由。但在个另特殊事务中，也可以考虑使用表级锁。

• 第一种情况是：事务需要更新【大部分或全部数据】，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。

• 第二种情况是：事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销。

2、在InnoDB下 ，主动上表锁的方式如下：

1
2
3
4

lock tables teacher write,student read;
select * from teacher;
commit;
unlock tables;

使用时有几点需要额外注意：

• 使用【LOCK TALBES】虽然可以给InnoDB加表级锁，但必须说明的是，表锁不是由InnoDB存储引擎层管理的，而是由其上一层ＭySQL Server负责的，仅当autocommit=0、innodb_table_lock=1（默认设置）时，InnoDB层才能感知MySQL加的表锁，ＭySQL Server才能感知InnoDB加的行锁，这种情况下，InnoDB才能自动识别涉及表级锁的死锁；否则，InnoDB将无法自动检测并处理这种死锁。

• 在用LOCAK TABLES对InnoDB加锁时要注意，事务结束前，不要用UNLOCAK TABLES释放表锁，因为UNLOCK TABLES会隐含地提交事务；COMMIT或ROLLBACK不能释放用LOCAK TABLES加的表级锁，必须用UNLOCK TABLES释放表锁，正确的方式见如下语句。

• 表锁的力度很大，慎用。

# 三、从另一个角度区分锁的分类

# 1、乐观锁

乐观锁大多是基于数据【版本记录机制】实现，一般是给数据库表增加一个"version"字段。

读取数据时，将此版本号一同读出，

更新时，对此版本号加一。此时将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。

事务一：

1
2

select * from ydl_student where id = 1;

事务二：

1
2
3

select * from ydl_student where id = 1;
update ydl_student set score = 99,version = version + 1 where id = 1 and version = 1;
commit;

事务一：

1
2

update ydl_student set score = 100,version = version + 1 where id = 1 and version = 1;
commit;

发现更新失败，应为版本号被事务二、提前修改了，这使用了不加锁的方式，实现了一个事务修改期间，禁止其他事务修改的能力。

# 2、悲观锁

总有刁民想害朕

悲观锁依靠数据库提供的锁机制实现。MySQL中的共享锁和排它锁都是悲观锁。数据库的增删改操作默认都会加排他锁，而查询不会加任何锁。此处不赘述。

# 第八章 日志系统

mysql给我们提供了很多有用的日志，这是mysql服务层给我们提供的：

# 一、bin log日志

# 1、概述

二进制日志（binnary log）以【事件形式】记录了对MySQL数据库执行更改的所有操作。

binlog记录了所有数据库【表结构】变更（例如CREATE、ALTER TABLE…）以及【表数据】修改（INSERT、UPDATE、DELETE…）的二进制日志。不会记录SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改，但可以通过查询通用日志来查看MySQL执行过的所有语句。

binlog是mysql server层维护的，跟采用何种引擎没有关系，记录的是所有的更新操作的日志记录。binlog是在事务最终commit前写入的。我们执行SELECT等不涉及数据更新的语句是不会记binlog的，而涉及到数据更新则会记录。要注意的是，对支持事务的引擎如innodb而言，必须要提交了事务才会记录binlog。

binlog 文件写满后，会自动切换到下一个日志文件继续写，而不会覆盖以前的日志，这个也区别于 redo log，redo log 是循环写入的，即后面写入的可能会覆盖前面写入的。

binlog有两个常用的使用场景：

• 主从复制：我们会专门有一个章节代领大家搭建一个主从同步的两台mysql服务。
• 数据恢复：通过mysqlbinlog工具来恢复数据。

mysql8中的binLog默认是开启的，5.7默认是关闭的，可以通过参数log_bin控制：

# 2、数据恢复

（1）确认binlog开启，log_bin变量的值为ON代表binlog是开启状态：

1

show variables like '%log_bin%';

（2）为了防止干扰，我们flush刷新log日志，自此刻会产生一个新编号的binlog日志文件：

1

flush logs;

（3）查看所有binlog日志列表：

1

show master logs;

（4）查看master状态，即最后(最新)一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值，这一步可有可无： （5）执行sql

先创建表，并插入一些数据：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

DROP TABLE IF EXISTS ydl_student;
CREATE TABLE `ydl_student` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `score` int(255) DEFAULT NULL,
  `grade` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `ydl_student`(`id`, `name`, `score`, `grade`) VALUES (1, 'lucy', 80, 'a');
INSERT INTO `ydl_student`(`id`, `name`, `score`, `grade`) VALUES (2, 'lily', 90, 'a');
INSERT INTO `ydl_student`(`id`, `name`, `score`, `grade`) VALUES (3, 'jack', 60, 'c');
INSERT INTO `ydl_student`(`id`, `name`, `score`, `grade`) VALUES (4, 'hellen', 40, 'd');
INSERT INTO `ydl_student`(`id`, `name`, `score`, `grade`) VALUES (5, 'tom', 60, 'c');
INSERT INTO `ydl_student`(`id`, `name`, `score`, `grade`) VALUES (6, 'jerry', 10, 'd');
INSERT INTO `ydl_student`(`id`, `name`, `score`, `grade`) VALUES (7, 'sily', 20, 'd');

执行删除操作，假装误删除，直接全部删除也可以，把表删了都行，一样的道理：

1

delete from ydl_student where id in (3,5);

（6）查看binlog日志，我们因为刷新了日志，所以本次操作都会在最新的日志文件上：

因为 binlog 的日志文件是二进制文件，不能用文本编辑器直接打开，需要用特定的工具来打开，MySQL 提供了 mysqlbinlog 来帮助我们查看日志文件内容：

1
2

# 查看全部的日志信息
/www/server/mysql/bin/mysqlbinlog -v mysql-bin.000008

1
2
3
4

# 指定位置范围
/usr/bin/mysqlbinlog -v mysql-bin.000013 --start-position=0 --stop-position=986
# 指定时间范围
/usr/bin/mysqlbinlog -v mysql-bin.000013 --start-datetime="2022-06-01 11:18:00" --stop-datetime="2022-06-01 12:18:00" 

真实的情况下，我们的日志文件比较复杂，内容比较多使用时间范围查询后任然可能需要花费时间去排查问题，这里我们找到了误删除的位置：

（7）执行恢复，通过上一步的操作，我们找到了删除的位置3228（即第二个红框），执行下面的语句：

1

/www/server/mysql/bin/mysqlbinlog -v mysql-bin.000008 --stop-position=3228 -v | mysql -uroot -p

（8）至此，数据已完全恢复了：

binlog的数据恢复的本质，就是将之前执行过的sql，从开始到指定位置全部执行一遍，如果报错【当前表已经存在】，就将数据库的表删除，重新恢复。

# 3、格式分类

binlog 有三种格式， 使用变量binlog_format查看当前使用的是哪一种：

• Statement（Statement-Based Replication,SBR）：每一条会修改数据的 SQL 都会记录在 binlog 中。
• Row（Row-Based Replication,RBR）：不记录 SQL 语句上下文信息，仅保存哪条记录被修改。
• Mixed（Mixed-Based Replication,MBR）：Statement 和 Row 的混合体，当前默认的选项，5.7中默认row。

我们举一个例子来说明row和statement的区别，在下面的插入语句中我们有一个函数uuid()，如果日志文件仅仅保存sql语句，下一次执行的结果可能不一致，所以Row格式的文件，他保存的是具体哪一行，修改成了什么数据，记录的是数据的变化，不是简单的sql：

1

insert into ydl_student values (8,UUID(),45,'d');

Statement和row的优劣

• Statement 模式只记录执行的 SQL，不需要记录每一行数据的变化，因此极大的减少了 binlog 的日志量，避免了大量的 IO 操作，提升了系统的性能。
• 由于 Statement 模式只记录 SQL，而如果一些 SQL 中 包含了函数，那么可能会出现执行结果不一致的情况。比如说 uuid() 函数，每次执行的时候都会生成一个随机字符串，在 master 中记录了 uuid，当同步到 slave 之后，再次执行，就得到另外一个结果了。所以使用 Statement 格式会出现一些数据一致性问题。
• 从 MySQL5.1.5 版本开始，binlog 引入了 Row 格式，Row 格式不记录 SQL 语句上下文相关信息，仅仅只需要记录某一条记录被修改成什么样子了。
• 不过 Row 格式也有一个很大的问题，那就是日志量太大了，特别是批量 update、整表 delete、alter 表等操作，由于要记录每一行数据的变化，此时会产生大量的日志，大量的日志也会带来 IO 性能问题。

# 4、日志格式

• binlog文件以一个值为0Xfe62696e的魔数开头，这个魔数对应0xfebin。
• binlog由一系列的binlog event构成。每个binlog event包含header和data两部分。
  • header部分提供的是event的公共的类型信息，包括event的创建时间，服务器等等。
  • data部分提供的是针对该event的具体信息，如具体数据的修改。

常见的事件类型有：

• FORMAT_DESCRIPTION_EVENT：该部分位于整个文件的头部，每个binlog文件都必定会有唯一一个该event
• WRITE_ROW_EVENT：插入操作。
• DELETE_ROW_EVENT：删除操作。
• UPDATE_ROW_EVENT：更新操作。记载的是一条记录的完整的变化情况，即从前量变为后量的过程
• ROTATE_EVENT：Binlog结束时的事件，用于说明下一个binlog文件。

一个event的结构如下，我们在恢复数据的时候已经看到了：

• 每个日志的最后都包含一个rotate event用于说明下一个binlog文件。
• binlog索引文件是一个文本文件，其中内容为当前的binlog文件列表，比如下面就是一个mysql-bin.index文件的内容。

# 5、binlog刷盘

​ 二进制日志文件并不是每次写的时候同步到磁盘。因此当数据库所在操作系统发生宕机时，可能会有最后一部分数据没有写入二进制日志文件中，这给恢复和复制带来了问题。 ​ 参数sync_binlog=[N]表示每写多少次就同步到磁盘。如果将N设为1，即sync_binlog=1表示采用同步写磁盘的方式来写二进制日志，这时写操作不使用操作系统的缓冲来写二进制日志。（备注：该值默认为0，采用操作系统机制进行缓冲数据同步）。

# 6、binlog实现主从同步

数据库单点部署的问题：

• 服务器宕机，会导致业务停顿，影响客户体验。
• 服务器损坏，数据丢失，不能及时备份，造成巨大损失。
• 读写操作都在同一台服务器，在并发量大的情况下性能存在瓶颈。

那么我们就可以使用mysql的binlog搭建一个一主多从的mysql集群服务。这样的服务可以帮助我们异地备份数据、进行读写分离，提高系统的可用性。

# （1） 主从复制工作原理剖析

• Master 数据库只要发生变化，立马记录到Binary log 日志文件中
• Slave数据库启动一个I/O thread连接Master数据库，请求Master变化的二进制日志
• Slave I/O获取到的二进制日志，保存到自己的Relay log 日志文件中。
• Slave 有一个 SQL thread定时检查Realy log是否变化，变化那么就更新数据

# （2）怎么配置mysql主从复制

环境准备

安装两个mysql，使用vmvare安装两个linux系统就可以：

1
2

mysql1(master): 42.192.181.133:3306
mysql2(slave):  124.220.197.17:3306

mysql 配置文件配

mysql1（master）: 配置文件设置，开启bin_log（已经开启的可以忽略）且需要配置一个server-id

1
2
3
4
5
6

#mysql master1 config 
[mysqld]
server-id = 1            # 节点ID，确保唯一

# log config
log-bin = master-bin     #开启mysql的binlog日志功能

mysql2（slave）: 需要开启中继日志

1
2
3
4
5
6
7

[mysqld]
server-id=2
relay-log=mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=sys.%
replicate-wild-ignore-table=information_schema.%
replicate-wild-ignore-table=performance_schema.%

重启两个mysql，让配置生效。

第三步 在master数据库创建复制用户并授权

1.进入master的数据库，为master创建复制用户

1

CREATE USER 'repl'@'124.220.197.17' IDENTIFIED BY 'Root12345_';

2.赋予该用户复制的权利

1
2

grant replication slave on *.* to 'repl'@'124.220.197.17' 
FLUSH PRIVILEGES;

3.查看master的状态

1
2
3
4
5
6
7
8

show master status;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005      120|              | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

4,配置从库

1
2
3
4
5
6
7
8
9
10
11

CHANGE MASTER TO 
MASTER_HOST = '42.192.181.133',  
MASTER_USER = 'repl', 
MASTER_PASSWORD = 'Root12345_',
MASTER_PORT = 3306,
MASTER_LOG_FILE='mysql-bin.000020',
MASTER_LOG_POS=2735,
MASTER_HEARTBEAT_PERIOD = 10000; 

# MASTER_LOG_FILE与主库File 保持一致
# MASTER_LOG_POS=120 , #与主库Position 保持一致

解释：MASTER_HEARTBEAT_PERIOD表示心跳的周期。当MASTER_HEARTBEAT_PERIOD时间之内，master没有binlog event发送给slave的时候，就会发送心跳数据给slave。

5.启动从库slave进程

1
2

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

6.查看是否配置成功

1

show slave status \G;

• Slave_IO_Running：从库的IO线程，用来接收master发送的binlog，并将其写入到中继日志relag log

• Slave_SQL_Running：从库的SQL线程，用来从relay log中读取并执行binlog。

• Slave_IO_Running、Slave_SQL_Running：这两个进程的状态需全部为 YES，只要有一个为 NO，则复制就会停止。

• Master_Log_File：要同步的主库的binlog文件名。

• Read_Master_Log_Pos：已同步的位置，即同步的 binlog 文件内的字节偏移量，该值会随着主从同步的进行而不断地增长。

• Relay_Log_File：从库的中继日志文件，对接收到的主库的 binlog 进行缓冲。从库的SQL线程不断地从 relay log 中读取 binlog 并执行。

• Relay_Log_Pos：relay log 中已读取的位置偏移量。

• Seconds_Behind_Master: 主从同步延时, 值为 0 为正常情况，正值表示已经出现延迟，数字越大从库落后主库越多。

7.在主库创建一个数据库、创建一张表，执行一些sql语句进行测试。

# （3）可能遇到的问题

在配置mysql主从复制的时候可能出现一下错误：

1

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

原因

如果你使用了两台虚拟机，一主一从，从库的mysql是直接克隆的。在mysql 5.6的复制引入了uuid的概念，各个复制结构中的server_uuid得保证不一样，但是查看到直接克隆data文件夹后server_uuid是相同的。

解决

找到data文件夹下的auto.cnf文件，修改里面的server_uuid值，保证各个db的server_uuid不一样，重启db即可。

1

cd /www/server/data

修改server_uuid的值

使用

1

select uuid();

生成一个uuid即可，重启数据库。

# 二、其他日志

# 1、通用查询日志，默认关闭

MySQL通用查询日志，它是记录建立的客户端连接和执行的所有DDL和DML语句(不管是成功语句还是执行有错误的语句)，默认情况下，它是不开启的。请注意，它也是一个文本文件。

可以通过以下的sql查看查询日志的状态：

使用以下命令开启通用查询日志，一般不开启，这是为了测试，当然也可以修改配置文件，重启服务：

1
2

# 在全局模式下，开启通用查询日志，1表示开启，0表示关闭
SET global general_log=1;

开启后，我们随便执行sql语句之后，你会发现data目录多了以下文件：

使用more命令查看该文件：

1

more VM-12-17-centos.log 

# 2、慢查询日志

当前版本慢查询日志默认是开启的，有的版本是关闭的，使用如下命令查看慢查询日志的状态：

那么，何为慢？mysql通过一个变量‘long_query_time’来确定sql慢不慢，执行时间大于该值就会被记录在慢查询日志中，默认是3s：

1

show variables like 'long_query_time'

以下是【慢查询日志】的记录文本：

# 3、错误日志

错误日志（Error Log）主要记录 MySQL 服务器启动和停止过程中的信息、服务器在运行过程中发生的故障和异常情况等。一旦发生mysql服务无法启动、程序崩溃一定要记得去查询错误日志：

我们随便人为一个错误导致他无法启动，重新启动mysql命令如下：

1
2

service mysqld restart
systemctl mysqld restart

我们将inndb的系统表空间文件重命名，重新启动mysql服务，发生问题：

查询错误日志，寻找蛛丝马迹：

修改回正确的名字，重新启动成功：

# 三、redo log日志

接下来的两个日志，是innodb为解决不同问题而引出的两类日志文件。

redo log（重做日志）的设计主要是为了防止因系统崩溃而导致的数据丢失，其实解决因系统崩溃导致数据丢失的思路如下：

1、每次提交事务之前，必须将所有和当前事务相关的【buffer pool中的脏页】刷入磁盘，但是，这个效率比较低，可能会影响主线程的效率，产生用户等待，降低响应速度，因为刷盘是I/O操作，同时一个事务的读写操作也不是顺序读写。

2、把当前事务中修改的数据内容在日志中记录下来，日志记录是顺序写，性能很高。其实mysql就是这么做的，这个日志被称为redo log。执行事务中，每执行一条语句，就可能有若干redo日志，并按产生的顺序写入磁盘，redo日志占用的空间非常小，当redo log空间满了之后又会从头开始以循环的方式进行覆盖式的写入。

redo log的格式比较简单，包含一下几个部分：

• type：该日志的类型，在5.7版本中，大概有53种不同类型的redo log，占用一个字节

• space id：表空间id

• page number：页号

• data：日志数据

# 1、MTR

在innodb执行任务时，有很多操作，必须具有原子性，我们把这一类操作称之为MIni Transaction，我们以下边的例子为例：

在我们向B+树中插入一条记录的时候，需要定位这条数据将要插入的【数据页】，因为插入的位置不同，可能会有以下情况：

1、待插入的页拥有【充足的剩余空间】，足以容纳这条数据，那就直接插入就好了，这种情况需要记录一条【MLOG_COMP_REC_INSERT类型】的redo日志就好了，这种情况成为乐观插入。

2、待插入的页【剩余空间不足】以容纳该条记录，这样就比较麻烦了，必须进行【页分裂】了。必须新建一个页面，将原始页面的数据拷贝一部分到新页面，然后插入数据。这其中对应了好几个操作，必须记录多条rede log，包括申请新的数据页、修改段、区的信息、修改各种链表信息等操作，需要记录的redo log可能就有二三十条，但是本次操作必须是一个【原子性操作】，在记录的过程中，要全部记录，要么全部失败，这种情况就被称之为一个MIni Transaction（最小事务）。

（1）MTR的按组写入

对于一个【MTR】操作必须是原子的，为了保证原子性，innodb使用了组的形式来记录redo 日志，在恢复时，要么这一组的的日志全部恢复，要么一条也不恢复。innodb使用一条类型为MLO_MULTI_REC_END类型的redo log作为组的结尾标志，在系统崩溃恢复时只有解析到该项日志，才认为解析到了一组完整的redo log，否则直接放弃前边解析的日志。

（2）单条redolog的标识方法

有些操作只会产生一条redo log，innodb是通过【类型标识】的第一个字符来判断，这个日志是单一日志还是组日志，如下图：

（3）事务、sql、MTR、redolog的关系如下

• 一个事务包含一条或多条sql
• 一条sql包含一个或多个MTR
• 一个MTR包含一个或多个redo log

# 2、log buffer

任何可能产生大量I/O的操作，一般情况下都会设计【缓冲层】，mysql启动时也会向操作系统申请一片空间作为redo log的【缓冲区】，innodb使用一个变量buf_free来标记下一条redo log的插入位置（标记偏移量），log buffer会在合适的时机进行刷盘：

• log buffer空间不足。logbuffer的容量由innodb_log_buffer_size指定，当写入log buffer的日志大于容量的50%，就会进行刷盘。
• 提交事务时，如果需要实现崩溃恢复，保证数据的持久性，提交事务时必须提交redo log，当然你也可以为了效率不去提交，可以通过修改配置文件设置该项目。
• 后台有独立线程大约每隔一秒会刷新盘一次。
• 正常关闭服务器。
• 做checkpoint时，后边会讲。

有缓冲就可能存在数据不一致，咱们接着往下看。

# 3、checkpoint

redolog日志文件容量是有限的，需要循环使用，redo log的作用仅仅是为了在崩溃时恢复脏页数据使用的，如果脏页已经刷到磁盘上，其对应的redo log也就没用了，他也就可以被重复利用了。checkpoint的作用就是用来标记哪些旧的redo log可以被覆盖。

我们已经知道，判断redo log占用的磁盘空间是否可以被重新利用的标志就是，对应的脏页有没有被刷新到磁盘。为了实现这个目的，我们需要了解一下下边几个记录值的作用：

（1）lsn

lsn（log sequence number）是一个全局变量。mysql在运行期间，会不断的产生redo log，日志的量会不断增加，innodb使用lsn来记录当前总计写入的日志量，lsn的初始值不是0，而是8704，原因未知。系统在记录lsn时是按照【偏移量】不断累加的。lsn的值越小说明redo log产生的越早。

每一组redo log都有一个唯一的lsn值和他对应，你可以理解为lsn是redo log的年龄。

（2）flush_to_disk_lsn

flush_to_disk_lsn也是一个全局变量，表示已经刷入磁盘的redo log的量，他小于等于lsn，举个例子：

1、将redo log写入log buffer，lsn增加，假如：8704+1024 = 9728，此时flush_to_disk_lsn不变。

2、刷如512字节到磁盘，此时flush_to_disk_lsn=8704+512=9256。

如果两者数据相同，说明已经全部刷盘。

（3）flush链中的lsn

其实要保证数据不丢失，核心的工作是要将buffer pool中的脏页进行刷盘，但是刷盘工作比较损耗性能，需要独立的线程在后台静默操作。

回顾一下flush链，当第一次修改某个已经加载到buffer pool中的页面时，他会变成【脏页】，会把他放置在flush链表的头部，flush链表是按照第一次修改的时间排序的。再第一次修改缓冲页时，会在【缓冲页对应的控制块】中，记录以下两个属性：

• oldest_modification：第一次修改缓冲页时，就将【修改该页面的第一组redo log的lsn值】记录在对应的控制块。
• newest_modification：每一次修改缓冲页时，就将【修改该页面的最后组redo log的lsn值】记录在对应的控制块。

既然flush链表是按照修改日期排序的，那么也就意味着，oldest_modification的值也是有序的。

（4）checkpoint过程

执行一个check point可以分为两个步骤

**第一步：**计算当前redo log文件中可以被覆盖的redo日志对应的lsn的值是多少：

1、flush链是按照第一次修改的时间排序的，当然控制块内的【oldest_modification】记录的lsn值也是有序的。

2、我们找到flush链表的头节点上的【oldest_modification】所记录的lsn值，也就找到了一个可以刷盘的最大的lsn值，小于这个值的脏页，肯定已经刷入磁盘。

3、所有小于这个lsn值的redo log，都可以被覆盖重用。

4、将这个lsn值赋值给一个全局变量checkpoint_lsn，他代表可以被覆盖的量。

**第二步：**将checkpoint_lsn与对应的redo log日志文件组偏移量以及此次checkpoint的编号（checkpoint_no也是一个变量，记录了checkpoint的次数）全部记录在日志文件的管理信息内。

# 4、一个事务的执行流程

主线程

1、客户端访问mysql服务，在buffer pool中进行操作（如果目标页不在缓冲区，需要加载进入缓冲区），此时会形成脏页。

2、记录redo log，可能产生很多组日志，redo log优先记录在缓冲区，会在提交事务前刷盘。

3、刷盘时根据checkpoint的结果，选择可以使用的日志空间进行记录。

4、成功后即可返回，此时数据不会落盘，这个过程很多操作只在内存进行，只需要记录redo log（顺序写），所以速度很快。

线程1：

1、不断的对flush链表的脏页进行刷盘，对响应时间没有过高要求。

线程2：

1、不断的进行checkpoin操作，保证redo log可以及时写入。

# 5、系统崩溃的影响

（1）**log buffer中的日志丢失，**log buffer中的日志会在每次事务前进行刷盘，如果在事务进行中崩溃，事务本来就需要回滚。

（2）buffer pool中的脏页丢失，崩溃后可以通过redo log恢复，通过checkpoint操作，我们可以确保，内存中脏页对应的记录都会在redo log日志中存在。

redo log保证了崩溃后，数据不丢失，但是一个事务进行中，如果一部分redo log已经刷盘，那么系统会将本应回滚的数据同样恢复，为了解决回滚的问题，innodb提出了undo log。

# 四、undo log日志

# 1、概述

undo log（也叫撤销日志或者回滚日志），他的主要作用是为了实现回滚操作。同时，他是MVCC多版本控制的核心模块。undo log保存在共享表空间【ibdata1文件】中。

**注意：**8.0以后undolog有了独立的表空间：

在讲undo log之前需要先了解行数据中的两个隐藏列：

# 2、事务id（trx_id）

我们已经讲过，在innodb的行数据中，会自动添加两个隐藏列，一个是【trx_id】，一个是【roll_pointer】，本章节会详细介绍这两列的具体作用，如果该表中没有定义主键，也没有定义【非空唯一】列，则还会生成一个隐藏列【row_id】，这个我们之间也讲过，是为了生成聚簇索引使用的。

事务id是一个自增的全局变量，如果一个【事务】对任意表做了【增删改】的操作，那么innodb就会给他分配一个独一无二的事务id。

冷知识：

• 事务id保存在一个全局变量【MAX_TRX_ID】上，每次事务需要分配事务id，就会从这个全局变量中获取，然后自增1。
• 该变量每次自增到256的倍数会进行一个落盘（保存在表空间页号为5的页面中），发生服务停止或者系统崩溃后，再起启动服务，会读取这个数字，然后再加256。这样做既保证不会有太多I/O操作，还能保证id的有序增长。比如：读到256进行落盘，后来有涨到302，突然崩溃了，下次启动后，第一个事务的id就是256+256=512，保证新的事务id一定大。

# 3、roll_pointer

undo log在记录日志时是这样记录的，每次修改数据，都会将修改的数据标记一个【新的版本】，同时，这个版本的数据的地址会保存在修改之前的数据的roll_pointer列中，如下：

# 4、分类

当我们对数据库的数据进行一个操作时必须记录之前的信息，将来才能【悔棋】，如下：

• 插入一条数据时，至少要把这条数据的主键记录下来，以后不想要了直接根据主键删除。
• 删除一条数据时，至少要把这个数据所有的内容全部记录下来，以后才能全量恢复。但事实上不需要，每行数据都有一个delete_flag，事务中将其置1，记录id，如需要回滚根据id复原即可，提交事务后又purge线程处理垃圾。
• 修改一条数据时，至少要将修改前后的数据都保存下来。

innodb将undo log分为两类：

• 一类日志只记录插入类型的操作（insert）
• 一类日志只记录修改类型的操作（delete，update）

什么分为这两类呢？

• 插入型的记录不需要记录版本，事务提交以后这一片空间就可以重复利用了。
• 修改型的必须将每次修改作为一个版本记录下来，即使当前事务已经提交，也不一定能回收空间，应为其他事务可能在用。

# 5、物理存储结构

undo同样是以页的形式进行存储的，多个页是使用链表的形式进行管理，针对【普通表和零时表】，【插入型和修改型】的数据，一个事务可能会产生以下四种链表：

这是物理存储模型，分成四种类型，是为了更好的管理。

# 6、记录流程

1. 开启事务，执行【增删改】时获得【事务id】。

2. 在系统表空间中第5号页中，分配一个回滚段，回滚段是轮动分配的，比如，当前事务使用第5个回滚段，下个事务就使用第6个。

   【回滚段】是一个【数据页】，里边划分了1024个undo slot，用来存储日志链表的头节点地址。

3. 在当前回滚段的cached链表（回收可复用的）和空闲solt中，找到一个可用的slot，找不到就报错。

4. 创建或复用一个undo log页，作为first undo page，并把他的地址写入undo solt中。

# 7、回滚过程

1. 服务再次启动时，通过表空间5号页面定位到128个回滚段的位置，
2. 遍历所有的slot，找到所有状态不为空闲的slot，并且通过undolog的标记为找到现在活跃（未提交）的所有的事务id
3. 根据undo log的记录，将数据全部回滚

# 第九章、隔离级别和MVCC

【MVCC】，全称Multi-Version Concurrency Control，即【多版本并发控制】。MVCC在MySQL InnoDB中的实现主要是为了提高数据库的并发性能，用更好的方式去处理【读-写冲突】，做到即使有【读写冲突】时，也能做到不加锁，非阻塞并发读，学习mvcc之前我们需要学习一些新的概念。

# 一、Read View（读视图）

在学习MVCC多版本并发控制之前，我们必须先了解一下，什么是MySQL InnoDB下的【当前读】和【快照读】，我们都知道undo log会记录一个事务对一条数据的所有修改，并形成版本链：

• 当前读：像select lock in share mode（锁）、 select for update、 update、insert、delete（排他锁）这些操作都是【当前读】，他读取的是记录的【最新版本】，读取时还要保证其他【并发事务】不能修改当前记录，会对读取的记录进行加锁。
• 快照读：像不加锁的select操作就是快照读，即不加锁的【非阻塞读】；快照读的前提是【隔离级别不是串行级别】，串行级别下的快照读会【退化成当前读】，顾名思义，快照读读取的是【快照】，他是通过readView实现的。

# 1、实现原理

Read View就是事务进行【快照读】的时候生产的【读视图】(Read View)，在该事务【执行快照读】的那一刻，会生成数据库系统当前的一个快照。

注意：【快照】不是说将数据库复制一份，【Read View】的主要作用是做【可见性判断】， 快照的实现逻辑是通过undo log的【版本链】，配合一些【参数】，比如事务id，来确定当前事务可以读取的版本。

# 2、readView的结构

举一个列子，当前有事务id为12、13、14、16、20的五个事务，他们在同时修改一条数据，此时，事务13发生读取行为，在【事务13】读取之前【事务14】已经提交，当前场景下，将产生一个readview如下：

一个readView就是一个【结构体】，你甚至可以理解成为java里的实例（readview）和属性，包含属性如下：

• m_ids：生成该readview时，当前系统中【活跃的事务】id列表。对于当前案例，因为14已经提交，就不活跃了，所以该变量的值为[12,13,16,20]。
• min_trx_id：当前系统【活跃事务】中最小的【事务id】，他也是m_ids的最小值，当前案例的值就是12。
• max_trx_id：当前系统中计划分配给下一个事务的id，他可能是m_ids的最大值+1，也可能比他大。当前案例值假设为22。
• creator_trx_id：生成这个readView的事务id，当前案例的值为12。

以上readview配合undo log就可以形成一个【快照】，那他是怎么读取的呢？

# 二、快照读原理解析

在一个事务读取数据时，会根据当前数据形成一个readview，读取时会按照以下逻辑进行读取：

• 如果【被访问数据的事务trx_id】和readView中的【creator_trx_id值】相同，意味着自己在访问自己修改过的记录，当然可以被访问。

• 如果【被访问数据的事务trx_id】小于readView中的【min_trx_id】值，说明生成这个版本数据的事务，在生成readview前已经提交，这样的数据也可以访问。

  **通俗一点：**这个数据之前被其他的事务修改过，但是事务已经提交，所以这个版本的数据是可以使用的，这样不会产生脏读。

• 如果【被访问数据的事务trx_id】大于等于readView中的max_trx_id值，说明生成这个版本数据的事务，是在生成readview后开启，这样的数据不应该被访问。

  **通俗一点：**你读取数据之后，有人修改了当前数据，那人家后边修改的数据，你也不能读。

• 如果【被访问数据的事务trx_id】如果在min_trx_id和max_trx_id范围内，则需要判断是不是在【m_ids】中（目的是判断这个数据是不是已经提交）。如果在，说明生成这个版本的事务还是活跃的，没有提交的事务产生的数据当然不能读，如果不在，说明事务已经提交，该数据可以被访问。

  **通俗一点：**这个数据被现在活跃的其他事务正在修改中，读取时要看此时这个事务是不是已经提交，目的也是为了不要读取别人未提交的事务。

我们用下边的案例来看一下这个过程：

# 三、解决脏读和不可重复读

对于RU隔离级别的事务来说，由于可以读取到未提交的事务，所有直接读取【最新的记录】（当前读）就可以，对于serializable的事务来说，必须使用加锁的方式来访问。

# 1、解决脏读

先思考一个问题，脏读指的是在当前事务中读取到了其他事务未提交的数据，那解决的思路是什么：

（1）没有undo+mvcc

一个事务读取了数据之后，立马给这个数据加写锁，不允许其他事务进行修改，这是加锁解决脏读。

（2）使用undo+mvcc

所有事务对数据的修改，记录成版本链，使用readview进行版本选择，每个事务只能读取满足条件的数据，这个过程不需要加锁。

使用mvcc很好的解决了【读写操作】的并发执行，而且采用了无锁机制。

# 2、解决不可重复读

RC和RR两个隔离级别解决不可重复读是通过生成readview时间不同

（1）RC隔离级别，同一个事务中【每次读取数据时都生成一个新的ReadView】，两次读取时，如果中间有其他事务进行提交，可能会生成两个不同的readview，导致当前事务中，两次读取的数据不一致，这就是不可重复读。具体的执行流程如下：

（2）RR隔离级别，同一个事务中【只在第一次读取数据时生成一个ReadView】，以后这个事务中一直使用这个readview，那么同一个事务中就能保证多次读取的数据是一致的，具体的执行流程如下：

# 3、解决幻读

他是通过间隙锁实现的，一旦锁定某一个范围的数据，就会对这个范围的数据加锁，间隙锁保证我们不能在这个范围内插入新的数据。

# 第十章 其他知识

# 一、触发器

与表有关的数据对象，在满足某种条件的时候，被动执行的SQL语句。

# 1、触发器的特性

1. 有begin、end的结构体（多条sql语句）
2. 需要指定触发的条件：INSERT，UPDATE，DELETE
3. 有指定的触发时间：BEFORE，AFTER

# 2、触发器的创建

• 单条业务逻辑的触发器创建

1
2
3
4
5
6
7

/*
CREATE TRIGGER 触发器名称 BEFORE|AFTER INSERT|UPDATE|DELETE ON 表名
FOR EACH ROW 业务逻辑
*/
#当b_user表中插入数据后，b_log表中也插入一条数据
CREATE TRIGGER trigger_insert AFTER INSERT ON b_user
FOR EACH ROW INSERT INTO b_log(字段) VALUES('插入数据')

• 多条业务逻辑的触发器

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

/*
DELIMITER $
CREATE TRIGGER 触发器名称 BEFORE|AFTER INSERT|UPDATE|DELETE ON 表名
FOR EACH ROW
BIGIN
INSERT...;
UPDATE...;
END;$
*/
#在b_user表中插入数据前，b_log表中插入2条数据
DELIMITER $
CREATE TRIGGER trigger_ insert_before BEFORE INSERT ON b_user
FOR EACH ROW
BEGIN
INSERT INTO b_log(comments,name) values('insert1' ，NEW.name);
INSERT INTO b_log(comments,name) values('insert2' , NEW.name) ;
END;$

总结

• BEFORE|AFTER INSERT用于获取将要插入的数据
• BEFORE|AFTER UPDATE|DELETE用于获取已经修改或删除的数据

# 3、删除触发器

1

DROP TRIGGER 触发器名称

# 二、存储过程

# 1、 变量

# 1.1 系统变量

由mysql数据库管理系统提供的，变量名称固定，可以修改和查看值，分为全局变量和会话变量

全局变量：当mysql服务没有重启时，我们可以查看和修改的变量

会话变量：和MySQL连接形成的会话，生命周期在整个会话过程中

全局变量用global修饰，会话变量用session修饰，通常session可以省略

• 查看系统变量

1
2
3
4
5
6

SHOW GLOBAL variables; -- 查看全局变量
SHOW SESSION variables; -- 查看会话变量
SHOW variables; -- 查看会话变量
SHOW GLOBAL variables like '%dir%'; -- 模糊查询环境变量
SELECT @@datadir; -- 查看全局系统变量
SELECT @@session_track_transaction_info;

• 修改系统变量

1
2
3
4
5
6

SHOW GLOBAL variables like 'autocommit'; -- 全局系统变量中为自动提交事务
SET GLOBAL autocommit=0; -- 将全局的自动提交的事务改为手动提交
SHOW SESSION variables link 'autocommit'; -- 查看会话变量中自动提交事务
SET SESSION autocommit=0; -- 将会话变量中自动提交的事务改为手动提交
SET @@session.autocommit=1;
SET @@global.autocommit=1;

全局变量在修改后，在不同的会话中都会立即生效，但是在重新启动mysql服务后，全局变量会恢复为默认值，如果想让全局变量依旧有效，需要去修改.ini文件（MySQL配置文件）

会话变量在修改后只对当前会话有效。一般在开发过程中修改会话变量。如：字符编码格式等可以在ini文件中进行设置

# 1.2 用户变量

MySQL允许用户自定义变量，分为用户变量和局部变量

• 用户变量

  作用域：当前会话有效

  1 2 3 4 5 6

  #设置方式1，先去声明并初始化用户变量，赋值操作既可以使用=进行赋值，也可以使用:=进行赋值 SET @变量名=值; SET @变量名:=值; SELECT @变量名:=值; #设置方式2 SELECT 字段 into @变量名 FROM 表名;

• 局部变量

  作用域：在begin end的结构体中，声明必须是begin end结构体的第一句

  1 2 3 4 5 6 7 8

  #声明方式，必须在begin后面从第一行开始 DECLARE 变量名 类型; DECLARE 变量名 类型 DEFAULT 值; #局部变量的赋值 SET 变量名:=值; SELECT @变量名:=值; SELECT 字段 into 变量名 FROM 表名;

# 2、存储过程的创建

存储过程是一组已经预先编译好的sql语句的集合，理解为批处理语句（增加流程控制语句），一般在复杂逻辑中才会使用存储过程

• 存储过程的优点

  • 提供了代码的可用性
  • 简化了数据库操作，将业务逻辑的细节隐藏在存储过程中
  • 减少了编译次数，减少了网络IO的次数，从而提高操作效率

• 存储过程的创建

  1 2 3 4 5 6 7 8 9

  /* DELIMITER $ CREATE PROCEDURE 存储过程的名称(参数列表) BEGIN 局部变量的定义 多条sql语句 流程控制语句 END;$ */

  如果存储过程中只有一条SQL语句可以省略BEGIN END

  参数列表

  参数模式	形参名称	参数类型
  IN	username	mysql数据库中的数据类型（数值型，字符型，日期型）
  OUT	pwd	mysql数据库中的数据类型（数值型，字符型，日期型）
  INOUT	xxx	mysql数据库中的数据类型（数值型，字符型，日期型）

  IN：声明该参数是一个输入姓参数（类似于java中的形参）

  OUT：声明该参数为一个输出型参数（类似于java中的返回值），在一个存储过程中可以定义多个out类型的参数

  INOUT：声明该参数可以为输入型参数，也可以为输出型参数

  • 存储过程调用

    1 2	CALL 存储过程的名称(实参列表) -- 实参列表中包含由输出类型的参数

  • 存储过程演示

    • 无参的存储过程

      1 2 3 4 5 6 7 8 9

      #用于向b_user表中插入2条数据 DELIMITER $ CREATE PROCEDURE pro_insert() BEGIN INSERT INTO b_user(name,sex) VALUES('1','1'); INSERT INTO b_user(name,sex) VALUES('2','2'); END;$ CALL pro_insert();

    • 带有IN模式参数的存储过程

      1 2 3 4 5 6 7 8 9

      #用于向b_user插入2条数据，性别由客户输入 DELIMITER $ CREATE PROCEDURE pro_insert2(IN sex CHAR(1)) BEGIN INSERT INTO b_user(name,sex) VALUES('1',sex); INSERT INTO b_user(name,sex) VALUES('2',sex); END;$ CALl pro_insert2('男');

    • 多个带有IN参数的存储过程

      1 2 3 4 5 6 7 8 9

      #用于向b_user插入2条数据，用户名和密码由客户输入 DELIMITER $ CREATE PROCEDURE pro_insert3(IN name VARCHAR(10),IN sex VARCHAR(20)) BEGIN INSERT INTO b_user(name,sex) VALUES(name,sex); INSERT INTO b_user(name,sex) VALUES(name,sex); END;$ CALL pro_insert2('uname','男');

    • 带IN，OUT参数的存储过程

      1 2 3 4 5 6 7 8 9 10 11 12 13 14

      #判断用户登录，如果用户名和密码输入正确登录成功，否则登录失败 #根据输入的用户名和密码作为条件去b_user表中查询，如果查询总行数==1，则认为登录成功，让result返回登录成功，否则登录失败 DELIMITER $ CREATE PROCEDURE pro_login(IN name VARCHAR(20),IN pwd VARCHAR(20),OUT result VARCHAR(20)) BEGIN DECLARE total INT DEFAULT 0;-- 用于存放查询总行数 select count(*) from b_user u where u.name=name and u.pwd=pwd;-- 将查询结果赋值给total局部变量 SET result:=IF(total=1,'登录成功','登录失败'); END;$ #存储过程如何执行 -- 解决判断，使用自定义变量 SET @result:=''; CAll pro_login('李四','123',@result); select @result;

    • 删除存储过程

      1	DROP PROCEDURE 存储过程名称

    • 查看存储过程

      1	SHOW CREATE PROCEDURE 存储过程名称;

    • 修改存储过程

      1 2	DROP CREATE

# 2.1 流程控制语句

选择结构

• IF函数

  • 功能：三目运算
  • 语法：IF(逻辑表达式，表达式1，表达式2)

• IF结构

  • 功能：实现多路选择

  • 注意：只能用在BEGIN END结构体中

    1 2 3 4 5 6 7

    /* IF 逻辑表达式 THEN 语句1; ELSEIF 逻辑表达式2 THEN 语句2; ... ELSE 语句n; END IF; */

• CASE结构

  • 等值选择
  1 2 3 4 5 6

  CASE 字段|变量|表达式 WHEN 值 THEN 值|语句 WHEN 值 THEN 值 ... ELSE 值 END
  • 不等值选择
  1 2 3 4 5

  CASE WHEN 逻辑表达式 THEN 语句1 ... ELSE 语句n END

循环结构

• WHILE

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

  /* WHILE 逻辑表达式 DO 循环体 END WHILE; */ #需求：创建存储过程，输入一个值，返回1到该值的和 #分析：一个输入参数，一个返回值，在结构体中，从1循环到输入的值，求和 DELIMITER // CREATE PROCEDURE pro_sum(IN input INT,OUT total INT) BEGIN DECLARE i INT DEFAULT 1; DECLARE sum_ INT DEFAULT 0; WHILE i<=input do SET sum_=sum_+i; SET i=i+1; END WHILE; SET totle:=sum_; END;// SET @result=0; CALL por_sun(10,@result); SELECT @result;

• LOOP

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

  #Loopnaem是定义的循环名称，为了跳出循环时指定跳出的循环 loopname:LOOP; IF 逻辑表达式 THEN LEAVE loopname; -- 跳出当前指定的循环，类似于java中的break END IF; END LOOP; DElIMITER // CREATE PROCEDURE pro_sum_loop(IN input INT,OUT total INT) BEGIN DECLARE i INT DEFAULT 1; DECLARE sum_ INT DEFAULT 0; a:LOOP; SET sum_:=sum_+i; SET i:=i+1; IF i>input THEN LEAVE a; END IF; END LOOP; SET total:=sum_; END;// SET @result=0; CALL por_sum_loop(10,@result); SELECT @result;

• REPEAT

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

  REPEAT 循环体 UNTIL 逻辑表达式 -- 当满足逻辑表达式，跳出循环 END REPEAT; DELIMITER // CREATE PROCEDURE pro_sum_loop(IN input INT,OUT total INT) BEGIN DECLARE i INT DEFAULT 1; DECLARE sum_ INT DEFAULT 0; REPEAT SET sum_:=sum_+i; SET i:=i+1 UNTIL i>input END REPEAT; SET total:=sum_; END;// SET @result=0; CALL por_sum_loop(10,@result); SELECT @result;

# 三、存储函数

函数也是一组预先编译好的sql的集合，基本和存储过程相似

函数和存储过程的区别

1. 存储过程可以有0个，1个或多个返回值，适用于insert、update、delete操作
2. 函数只能有一个返回值，适用于在处理数据以后，返回一个已知的结果

# 1、创建函数

1
2
3
4

CREATE FUNCTION 函数名称(参数列表) RETURNS 返回类型 BINLOG参数
BEGIN
函数体
END

参数列表：参数名称 参数类型

BINLOG参数

• NO SQL：函数体中没有sql语句， 也不会改参数
• READS SQL DATE：函数体中存在sql语句，但是整个数据是只读的，不会修改数据
• MODIFIES SQL DATE ：函数体中存在SQL语句，并且会修改数据
• CONTAINS SQL：函数体中包含有SQL语句

函数体：在函数体汇总必须包含return语句，将return放在函数体最后一行执行

1
2
3
4
5
6

#写一个函数，用于求两数之和
DELIMITER //
CREATE FUNCTION sum_(input1 INT,input2 INT) RETURNS INT NO SQL
BEGIN
return input1+input2;
END;//

# 2、使用函数

1

SELECT 函数名(参数列表);

# 3、查看函数

1

SHOW CREATE FUNCTION 函数名;

# 4、删除函数

1

DROP FUNCTION 函数名;

# 四、定时任务

# 1、查看定时策略是否开启

1

show variables like '%event_sche%';

开启定时策略：

1

set global event_scheduler=1;

# 2、创建定时任务

1
2
3
4

create event run_event
on schedule every 1 minute
on completion preserve disable
do call test_procedure ();

1、create event day_event：是创建名为run_event的事件 2、创建周期定时的规则，意思是每分钟执行一次 3、on completion preserve disable是表示创建后并不开始生效。 4、do call test_procedure ()是该event(事件)的操作内容

# 3、定时任务操作

1、查看定期任务

1
2

SELECT event_name,event_definition,interval_value,interval_field,status 
FROM information_schema.EVENTS;

2、开启或关闭定时任务

1
2

alter event run_event on completion preserve enable;//开启定时任务
alter event run_event on completion preserve disable;//关闭定时任务

# 4、定时规则

1、周期执行–关键字 EVERY 单位有：second、minute、hour、day、week(周)、quarter(季度)、month、year

1

on schedule every 1 week //每周执行1次

2、在具体某个时间执行–关键字 AT

1
2

on schedule at current_timestamp()+interval 5 day //5天后执行
on schedule at '2019-01-01 00:00:00' //在2019年1月1日，0点整执行

3、在某个时间段执行–关键字STARTS ENDS

1
2

on schedule every 1 day starts current_timestamp()+interval 5 day ends current_timestamp()+interval 1 month //5天后开始每天都执行执行到下个月底
on schedule every 1 day ends current_timestamp()+interval 5 day //从现在起每天执行，执行5天

# 附录：

1、配置文件的举例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140

#[client]
#MySQL默认密码
#password=88888888
[mysqld]
#MySQL以什么用户运行
#user=mysql
#MySQL运行在哪个端口
#port=3306
#改参数指定了安装MySQL的安装路径，填写全路径可以解决相对路径所造成的问题
#basedir
#指定MySQL的数据库文件放在什么路径下
datadir=/usr/local/mysql/data
#mysql以socket方式运行的sock文件位置
socket=/usr/local/mysql/mysql.sock
#是否支持符号链接，即数据库或表可以存储在my.cnf中指定datadir之外的分区或者目录，为0不开启
symbolic-links=0
#服务器使用的字符集
character-set-server=utf8
#默认存储引擎
default-storage-engine=INNODB
#表示默认将日志文件存入文件，默认值是'FILE' 
#如果时候log-output=TABLE表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中
log-output=FILE
#1开启，0关闭 将所有到达MySQL Server的SQL语句记录下来
general-log=0
#设置日志文件保存位置
general_log_file="JOYWANG.log"
#慢查询日志是否开启1,0
slow-query-log=1
#慢查询日志文件保存
slow_query_log_file="JOYWANG-slow.log"
#慢查询日志设置时间单位秒 S
long_query_time=10
#是否启用错误日志的功能和错误日志的存储位置
log-error="JOYWANG.err"
#如果不设置则server-id是根据服务器ip地址后2位生成的，默认0或1
#当配置MySQL复制时，是必填项，用来区分复制拓扑中的各个实例
server-id=1
#限制导入和导出的目录权限NULL表示禁止、如果是文件目录，允许该目录下文件（测试子目录不行）、如果为空则表示不限制目录，一定要有等号，否则mysql无法启动
secure-file-priv=
#最大并发连接数，mysql会为每个连接提供缓冲区，会开销越多的内存，所以要适当的调整该值，不能盲目的提高设置值
max_connections=151
#指定高速缓存的大小，每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容单位M
table_open_cache=2000
#增加一张临时表大小，提高查询速度
tmp_table_size=16M
#线程池缓存大小，当客户端断开连接后，将当前线程缓存起来，当在接到新的连接请求时快速响应，无需创建新的线程
thread_cache_size=10
#MySQL重建索引时允许使用的临时文件最大大小
MyIsam_max_sort_file_size=100G
#设置在REPAIR TABLE，或者用 CREATE INDEX 创建索引或 ALTER TABLE 的过程中排序索引所分配的缓冲区大小。可设置范围4Bytes 至 4GB，默认为8MB。
MyIsam_sort_buffer_size=8M
#指定索引缓冲区的大小，决定了索引处理的速度，尤其是索引读的速度，建议设置成物理内存的1/4，甚至物理内存的30%-40%，如果设置太大，系统就会频繁的换页，降低系统性能
key_buffer_size=8M
#MySQL读入缓冲区大小，对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。
read_buffer_size=0
#参数用在sort查询之后 ，以保证获取以顺序的方式获取到查询的数据。如果你有很多order by 查询语句，增长这值能够提升性能
read_rnd_buffer_size=0
#0：log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作。
#1：每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去，该模式为系统默认。
#2：每次事务提交时MySQL都会把log buffer的数据写入log file，但是flush(刷到磁盘)操作并不会同时进行。该模式下，MySQL会每秒执行一次 flush(刷到磁盘)操作。
innodb_flush_log_at_trx_commit=1
#确保有足够大的日志缓冲区来保存脏数据在被写入到日志文件之前
innodb_log_buffer_size=1M
#指定表数据和索引存储的空间，可以是一个或者多个文件。最后一个数据文件必须是自动扩充的，也只有最后一个文件允许自动扩充。这样，当空间用完后，自动扩充数据文件就会自动增长（以8MB为单位）以容纳额外的数据。例如： innodb_data_file_path=/disk1/ibdata1:900M;/disk2/ibdata2:50M:autoextend两个数据文件放在不同的磁盘上。数据首先放在ibdata1中，当达到900M以后，数据就放在ibdata2中。一旦达到50MB，ibdata2将以 8MB为单位自动增长。如果磁盘满了，需要在另外的磁盘上面增加一个数据文件。
innodb_data_file_path=/disk1/ibdata1:900M;/disk2/ibdata2:50M:autoextend
#这是InnoDB最重要的设置，对InnoDB性能有决定性的影响。默认的设置只有8M，所以默认的数据库设置下面InnoDB性能很差。在只有InnoDB存储引擎的数据库服务器上面，可以设置60-80%的内存。更精确一点，在内存容量允许的情况下面设置比InnoDB tablespaces大10%的内存大小。
innodb_buffer_pool_size=8M
#放置表空间数据的目录，默认在mysql的数据目录，设置到和MySQL安装文件不同的分区可以提高性能。
innodb_data_home_dir=
#该参数决定了recovery speed。太大的话recovery就会比较慢，太小了影响查询性能，一般取256M可以兼顾性能和recovery的速度
innodb_log_file_size=48M
#该参数设定了事务提交时内存中log信息的处理。
1) =1时，在每个事务提交时，日志缓冲被写到日志文件，对日志文件做到磁盘操作的刷新。Truly ACID。速度慢。
2) =2时，在每个事务提交时，日志缓冲被写到文件， 但不对日志文件做到磁盘操作的刷新。只有操作系统崩溃或掉电才会删除最后一秒的事务，不然不会丢失事务。
3) =0时， 日志缓冲每秒一次地被写到日志文件，并且对日志文件做到磁盘操作的刷新。任何mysqld进程的崩溃会删除崩溃前最后一秒的事务
innodb_flush_logs_at_trx_commit=2
#设置InnoDB同步IO的方式：
) Default – 使用fsync（）。
2) O_SYNC 以sync模式打开文件，通常比较慢。
3) O_DIRECT，在Linux上使用Direct IO。可以显著提高速度，特别是在RAID系统上。避免额外的数据复制和double buffering（mysql buffering 和OS buffering）。
innodb_flush_method=Default
#InnoDB kernel最大的线程数。
1) 最少设置为(num_disks+num_cpus)*2。
2) 可以通过设置成1000来禁止这个限制
innodb_thread_concurrency=25
#此配置项作用主要是当tablespace 空间已经满了后，需要MySQL系统需要自动扩展多少空间，每次tablespace 扩展都会让各个SQL 处于等待状态。增加自动扩展Size可以减少tablespace自动扩展次数。
innodb_autoextend_increment=64
#可以开启多个内存缓冲池，把需要缓冲的数据hash到不同的缓冲池中，这样可以并行的内存读写。
innodb_buffer_pool_instances=8
#这个参数设置为一种tickets,默认是5000，我也不清楚到底它代表多久，从官方文档来看它和事物处理的行数有关，大事物需要处理的行数自然更多，小事物当然也就越少至少我们可以想成获得CPU的时间，干活期间他会不断减少，如果减少到0，这个线程将被提出innodb层次，进入前面说的等待队列，当然也就在队尾部了，这里假设有一个小的事物正在排队进入innodb层，又或者它已经进入了innodb层没有获得CPU时间轮片，突然一个大的事物tickets耗尽被提出了innodb层，那么这个小事物就自然而然能够获得CPU轮片干活，而小事物执行非常快，执行完成后，另外的事物又能尽快的获得CPU干活，不会由于OS线程调度不均匀的问题而造成的小事物饥饿问题，这很好理解。也就是前面我说的与其依赖OS的调度策略不如自己设置一种规则，让用到了一定时间轮片的线程先处于睡眠态放弃CPU的使用
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=300
innodb_stats_on_metadata=0
#可以存储每个InnoDB表和它的索引在它自己的文件中。
innodb_file_per_table=1
#如果应用程序可以运行在READ-COMMITED隔离级别，做此设定会有一定的性能提升。
transaction-isolation=READ-COMITTED
#这个参数用来表示 页读取到mid位置后，需要等待多久才会被加入到LRU列表的热端。使LRU列表中的热点数据不被刷出
innodb_checksum_algorithm=0
#MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中
back_log=80
flush_time=0
#如果按照检索的性能方式来细分，那么无论是两表 INNER JOIN 还是多表 INNER JOIN，都大致可以分为以下几类：1.JOIN KEY 有索引，主键2.JOIN KEY 有索引， 二级索引3.JOIN KEY 无索引；JOIN BUFFER 是 MySQL 用来缓存以上第二、第三这两类 JOIN 检索的一个 BUFFER 内存区域块。
join_buffer_size=256K
#可以增大此值以便于server端接收更大的SQL
max_allowed_packet=4M
#同一主机最大连续请求错误次数，如果还没成功建立连接，mysql服务器会组织这台主机后续的所有请求
max_connect_errors=100
#限制mysqld能打开文件的最大数
open_files_limit=4161
#一个connection级参数，在每个connection第一次需要使用这个buffer的时候，一次性分配设置的内存
sort_buffer_size=256K
#就是控制总frm文件的数量，还是个hash表，内部维护。如果打开的表实例的数量超过了table_definition_cache设置，LRU机制将开始标记表实例以进行清除，并最终将它们从数据字典缓存中删除。简单通俗点frm文件有多少，就设置多少了
table_definition_cache=1400
#指定基于行的二进制日志事件的最大大小
binlog_row_event_max_size=8K
#本参数用于主从库中配置从库大于0作用为每个命令之后刷盘，小与0作为为永不刷盘，默认均为1000
sync_master_info=10000
#这个参数和sync_binlog是一样的，当设置为1时，slave的I/O线程每次接收到master发送过来的binlog日志都要写入系统缓冲区，然后刷入relay log中继日志里，这样是最安全的，因为在崩溃的时候，你最多会丢失一个事务，但会造成磁盘的大量I/O。当设置为0时，并不是马上就刷入中继日志里，而是由操作系统决定何时来写入，虽然安全性降低了，但减少了大量的磁盘I/O操作。这个值默认是0，可动态修改，建议采用默认值。
sync_relay_log=10000
#这个参数和sync_relay_log参数一样，当设置为1时，slave的I/O线程每次接收到master发送过来的binlog日志都要写入系统缓冲区，然后刷入relay-log.info里，这样是最安全的，因为在崩溃的时候，你最多会丢失一个事务，但会造成磁盘的大量I/O。当设置为0时，并不是马上就刷入relay-log.info里，而是由操作系统决定何时来写入，虽然安全性降低了，但减少了大量的磁盘I/O操作。这个值默认是0，可动态修改，建议采用默认值
sync_relay_log_info=10000
#参数不可动态修改，必须重启数据库
#1:存储在磁盘是小写，比较时不区分大小写
#2:存储为给定的大小写但是比较时是小写
#0:存储为给定的大小写和比较时区分大小写的
lower_case_table_names = 1
#ONLY_FULL_GROUP_BY：归于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中
#NO_AUTO_VALUE_ON_ZERO：该值影响自增常烈的插入。默认设置下，插入0或者NULL代表生成下一个自增长值。如果用户希望插入的值为0，而该列又是自增长的，那么这个选项就有用了
#STRICT_TRANS_TABLES：如果一个值不能插入到一个事物中，则中断当前操作，对非事物不做限制
#NO_ZERO_IN_DATE：在严格模式下，不允许日期和月份为零
#NO_ZERO_DATE：mysql不允许插入零日期，插入零日期会抛出错误而不是警告
#ERROR_FOR_DIVISION_BY_ZERO：在insert或update过程中，如果数据被清除，则产生错误而非警告。如果未给出该模式，那么数据被清除时Mysql返回NULL
#NO_AUTO_CREATE_USER：禁止GRANT创建密码为空的用户
#NO_ENGINE_SUBSTITUTION：如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常
#PIPES_AS_CONCAT：将“||”是为字符串的链接操作符而非运算符，这和Oracle数据库是一样是，也和字符串的拼接函数Concat相类似
#ANSI_QUOTES：不能用双引号来引用字符串，因为它被解释为识别符
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION