1 概述

mysql经验汇总，都是在烘焙帮在自坑了一次又一次的经验呀

2 存储结构

2.1 引擎

mysql的默认引擎是MyISAM，所以建表时请务必明确要求使用innodb引擎，支持行锁，强事务性。别问我为什么，用了MyISAM引擎丢过数据，查询锁过表导致超时默默不语

2.2 字符集

mysql的默认字符集是utf-8，但是是坑爹的最长三字节的utf-8，导致emoji字符存不下，用户输入的表情数据全表丢失。所以建表时请务必明确要求使用utf8mb4字符集，完美解决。

2.3 主键自增

为了我好你好大家好，inndb引擎上最好带有一个自增的主键ID，自增ID一般从是10001开始，从1开始也可以，不过比较难看就是了。带有自增主键的好处是，数据按创建时间排序时，可以改为用自增ID倒序排列，这样速度会快很多。另外，自增主键唯一方便修改数据，业务也比较好做。

2.4 not null

最好指定字段都是not null的。当然，mysql的指定not null后，当你插入null数据时，mysql会将其转换为空数据。另外，特别注意的是，不要将空值数据设置为有效数据，有效数据都必须是非空值数据，例如字段用integer做enum的话，就不能用0作为enum的其中一个值。因为这样会容易忽略了程序写入0的错误。

2.5 not enum

mysql中的enum体不利于迁移数据库，而且扩展时也会不方便，造成业务增加enum值时，数据库也需要同步更新。所以enum的最好还是使用integer类型来代替。

2.6 整数

mysql的整数类型太多了，常用的是int和bigint，要注意大多数的情况下我们都需要选择bigint，以避免后期的字段升级带来的问题。

2.7 浮点数

要注意，浮点数的固有缺陷就是有计算精度的问题,会造成0.1+0.2=0.30000000000000004的问题。这意味着用浮点数进行加减乘除的运算后，结果是不精确的，而且浮点数直接进行等于号比较也是会出问题的。因此，在mysql中，浮点数类型仅能在科学计算和图形编程上，但这种场合本身就非常少。

浮点数为什么不精确，这主要是因为两个原因：

• 计算机存储浮点数是用二进制，但我们记录数据时是用十进制，当有穷的十进制的小数转换成二进制时，就可能产生无穷的二进制位，但是浮点数的存储位数是有限的，就会造成精度丢失，例如0.1的十进制小数转换为二进制是0.000110011……0011…… (0011无限循环)。
• 整数部分是没有进制转换的精度丢失，因为整数的最小精度就是1，从十进制转换到二进制时是从低位到高位逐步计算的，不会有精度丢失。但是，小数是没有最小精度的，从十进制转换到二进制时是从高位到低位逐步计算的，可能会产生精度丢失。

注意，这个问题不能简单地通过四舍五入来解决：

1.35.toFixed(1)，结果为1.4，是正确的
1.335.toFixed(2)，结果为1.33，是错误的
Math.round((3.6123-3.1782)*3.5*10000)/10000，结果为1.5193，是错误的

针对这两个问题，有两个对应的解决办法：

• 使用十进制浮点数存储和计算数据，因为我们的输入数据是十进制的，当使用十进制数据来记录数据时就不会有进制转换导致的精度丢失。这种方法的优点在于可以表示的范围非常大（因为保留了浮点的表示方式），前端展示数据时不需要转化，缺点在于需要创建特殊的数据结构来进行运算，并且不能转储数据时必须要用string，不能用float64，否则会产生精度丢失的问题。
• 使用二进制定点数来存储和计算数据，将小数乘以一个固定的倍数转换为整数，然后用大整数进行存储和计算，例如金额元就用金额分来代替，就能避免产生小数了。这种方法的精度取决于小数转换为大整数时的倍数，例如所有的小数都乘以10000，那么它的精度就是0.0001，因此它是固定精度的。这种方法的优点在于可以用普通的整数加减方法来进行运算，转储数据时直接用整数来存储，缺点在于它可以表示的范围非常有限，因为它是定点运算，不是浮点运算。要注意的是，在前端浏览器中，js的安全可用最大整数是9007199254740991，如果保留4位小数，那么实际可用的整数位是12位，大概为9千亿。也就是使用这种办法的话，再保留4位小数的基础上，要兼容前后端前后json数据传递和运算，最大只能支持到9千亿的数值。另外一个缺点是前端展示数据时需要转化之后才能使用。

要注意的是，对于除法生成的无限循环的十进制小数，以上的两种方法依然会有精度损失。

详情看这里

2.8 定点数

mysql中提供了专门进行十进制存储和计算的decimal类型，其和整数一样执行运算时是精确的，可预测的，并且能直接比较，也拥有和浮点数一样能存储小数的能力。因此，它能方便并且准确地在数据库对小数执行聚合统计操作。要注意，映射到业务层的时候，不能用float64，要用string，否则会产生精度丢失的问题。所以，对于需要精确的小数运算的场景，就要使用decimal类型，绝不能使用float和double类型。

bond_bottom decimal(20,8) not null,

看这里，代表全位数为20，小数位数为8，整数位数为12.

2.8 时间

表中都带有创建时间和修改时间两个字段，方便出问题时排错用。创建时间设置为default CURRENT_TIMESTAMP，修改时间设置为default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP。

另外，要密切注意的是，mysql的时间如果设置为

publishTime timestamp not null

默认就是与以下代码一样

publishTime timestamp not null on update CURRENT_TIMESTAMP

没错，mysql默认时间字段在修改数据时自动会更新为当前时间。++，这是多么隐藏的坑。你需要显式将时间字段设置为以下的方式，才能让时间字段设置为想要的方式。

publishTime timestamp not null default 0

另外timestamp的范围为’1970-01-01 00:00:01’到’2038-01-19 03:14:07’，插入时间是需要注意在这个范围内才可以插入成功。如果你需要更大的时间范围，那应该使用datetime，而不是timestamp。datetime的范围为’0000-01-01 00:00:00.000000’ 到 ‘9999-12-31 23:59:59.999999’。注意，目前在2019年的情况下，后续的时间类型应该尽可能避免使用timestamp类型，而应该只使用datetime类型。

2.9 字符串

mysql存储字符串有多种数据类型可以选择，需要根据场景来选择

要注意，char和varchar中指定的长度是以字符为单位的，而不是字节为单位的。例如，varchar(128)，同时设置utf8mb4编码，那么这个varchar的最大长度是512字节。另外，在mysql 5.7之前，默认索引的长度最大是767字节，对应在utf8mb4编码中是191个字符。在mysql 5.7之后，默认索引的长度最大是3072字节，在utf8mb4编码中是768个字符。

2.11 总结

create table t_appmsg_broadcast(
    appmsgBroadcastId bigint not null auto_increment,
    message varchar(256) not null,
    deviceType int not null,
    afterOpenType int not null,
    afterOpenData varchar(256) not null,
    state int not null,
    createTime datetime not null default CURRENT_TIMESTAMP,
    modifyTime datetime not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
    primary key( appmsgBroadcastId )
)engine=innodb default charset=utf8mb4 auto_increment = 10001;

以上是一个常用的sql模板

3 查询执行步骤

3.1 基础操作

<SELECT clause> 
[<FROM clause>] 
[<WHERE clause>] 
[<GROUP BY clause>] 
[<HAVING clause>] 
[<ORDER BY clause>] 
[<LIMIT clause>]   

这是mysql的select操作，在实际执行时它的步骤为：

• FROM子句，组装来自不同数据源的数据
• WHERE子句，对数据进行筛选
• GROUP BY子句，对数据划分为不同的分组，计算聚集操作的结果，并为每个分组内的数据仅保留一行数据
• HAVING子句，对分组后的数据进行筛选
• SELECT子句，对数据提取相应的字段
• DISTINCT子句，对重复数据进行去重操作
• ORDER BY子句，对数据进行排序操作
• LIMIT子句，对数据取限制行数的操作

注意，切勿在查询语句中使用隐式类型转换，这可能会导致查询失败或者索引失效的问题。例如

userId是整数类型，但是查询时用where userId = '10001'的方式，这可能会导致索引失效
having count(itemId) = '3'，这可能会导致查询失败，直接返回空集

3.2 聚合操作

这是mysql的聚合操作运算符，当查询中没有group by操作时，其会对所有数据进行聚合操作。如果查询中有group by操作时，其会对每个分组内的数据执行一次聚合操作。

3.3 分组操作

要特别注意group by与order by的顺序，group by在分组操作时，会将同一分组中的第一行取出来作为这一行的代表，然后再将组间数据进行排序。因此分组时同一分组内的多条数据会聚合成单条数据，我们是无法简单地直接通过order by来确定group by后留下的哪一条数据是什么。

select * 
from t_order
group by userId
order by createTime desc

例如，我们无法通过以上的sql语句，来实现获取每个用户最近的订单是什么。因为上面的语句是先分组再排序，而不是先排序再分组。

select * 
from (select * from t_order order by userId,createTime desc) as order
group by order.userId

以上的这条语句就可以了，先在派生表中排序，得出结果后再分组，从而实现了组内排序。

3.4 窗口函数操作

在mysql 8之后新增了窗口函数的操作，它大大提高了数据分析的便利性。详情的代码看这里

3.4.1 窗口聚合操作

在mysql 8之前，聚合操作都是将多条数据聚合成一条数据的，例如是count,sum,max,min,avg的这些操作。但是有一些操作需要聚合多行的数据，但是每行数据的结果都是不一样的，例如是计算当前行的排名，它需要多行数据比较过后得出，但是每行的数据都不一样。或者，计算当前行的累加求和值。这个时候，我们就需要窗口操作了，他在引入聚合的操作的同时，不会聚合多个行的数据到一行去。

select userId,total,rank() over(order by total desc) as totalRank from t_order;

计算订单中的排名

select userId,total,createTime,sum(total) over(order by createTime asc) from t_order;

计算订单中的累加金额

3.4.2 窗口范围指定

同样地，有了窗口聚合函数后，我们甚至可以指定窗口的大小，例如，默认的sum是累加从第一行到当前行的窗口，但是，我们甚至可以指定累加当前行附近的3行数据作为窗口。

select userId,total,createTime,sum(total) over(order by createTime asc ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as sumTotal from t_order;

通过指定Between和and来实现。

select userId,total,createTime,sum(total) over(order by createTime asc RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as sumTotal from t_order;

默认的窗口为，从UNBOUNDED PRECEDING（第一行）到CURRENT ROW（当前行），要注意，当前行是有两种定义的，以数值定义的当前行称为RANGE，也就是和当前createTime相同数值的行都称为当前行，这个时候，当前行可能包含多行数据。

select userId,total,createTime,sum(total) over(order by createTime asc RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as sumTotal from t_order;

以行号定义的当前行称为ROW，也就是排序后的当前位置称为当前行，这个时候，当前行只可能包含一行数据。这个时候，要特别注意，排序的顺序应该是唯一的和确定的，不然会造成排序歧义的问题。

3.4.3 分组窗口操作

我们也可以指定窗口是在分组内指定的，不同分组的数据不参与聚合。

select userId,total,rank() over(partition by userId order by total desc) as totalRank from t_order;

分组内的订单排名

select userId,total,createTime,sum(total) over(partition by userId order by createTime asc) from t_order;

分组内的订单累加金额

3.5 连接操作

mysql的连接支持以上的四种类型，其不支持全外连接，其中内连接，左连接和右连接都是需要等号来将两个表连接起来，而笛卡尔积则直接将两个表连接起来就可以了，不需要等号。默认逗号连接就是笛卡尔积，即交叉连接。

select *
from a join b
on a.xxx = b.xxx
where a.xxx = xxx,b.xxx = xxx

表的连接操作是这样运行的：

• 首先选择一个驱动表，如果是left join，就只能选左表，如果是right join，就只能选右表，如果是inner joi或者cross join，那么mysql就会选择过滤条件更多，总规模尽可能小的表来确定为驱动表。
• 根据驱动表，遍历每一行的数据，根据连接条件on，找出另外一个表上的对应数据，生成一个临时表。
• 对于临时表上的每一行数据，执行where的过滤操作，剩下的就是结果集了。

根据表的连接操作，我们有以下的优化原则：

• 尽可能选择inner join，而不是left join或者right join。如果需要强制指定驱动表，则将驱动表放在左边，然后使用left join。
• 将连接条件尽可能填写在on，而不是where，这样在连接的过程中。就能一边连接一边过滤了，生成的临时表尽可能的小。

drop database if exists Test;

create database Test;

use Test;

create table a(
    id integer not null,
    primary key(id)
)engine=innodb;

create table b(
    id integer not null,
    primary key(id)
)engine=innodb;

create table c(
    id integer not null,
    primary key(id)
)engine=innodb;

insert into a(id) values(1),(2),(3),(4);
insert into b(id) values(1),(2),(3);
insert into c(id) values(1),(2),(3),(4);

# a表与b表，内关联以后生成临时表K
# 然后临时表K与C进行左外关联
select * from a 
    inner join b on a.id = b.id 
    left join c on a.id = c.id;

# a表与b表，左外关联以后生成临时表K
# 然后临时表K与C进行内关联
select * from a 
    left join b on a.id = b.id 
    inner join c on a.id = c.id;

多表关联的顺序是这样的，从左到右，依次关联生成临时表，然后临时表与后面的表关联，生成新的临时表。

3.6 派生操作

派生表就是在查询语句中的from子句临时生成的表，执行步骤很简单，mysql将from子句中的派生表提前执行，生成一个临时表，然后在临时表上的执行外层的where条件。

with derived_table_name as(
    select column_list
    from table_1
)select column_list 
from derived_table_name
where derived_table_name.c1 > 0;

由于派生表使用很频繁，mysql 8之后支持了cte操作，就是在select语句之前用with语句来指定生成一个派生表，而这个派生表能在后面的语句中直接使用。

根据派生表的执行步骤，我们有以下的优化原则：

• 尽可能在派生表中执行过滤操作，这样生成的临时表能足够小，以便在后续的操作中执行更快。

3.7 子查询操作

子查询操作就是在where条件中包含其他表的操作。子查询操作可以分为两种，相关子查询和非相关子查询。

select sales_id,tot_amt
from sales 
where sale_id in(select sale_id from employee where sex='F')

这是非相关子查询，在子查询表中，employee的生成，不需要外部变量的传入。

select sales_id,tot_amt
from sales 
where exists (select * from employee where employee.sex='F'and employee.sale_id = sales.sale_id)

这是相关子查询，在子查询表中，employee的生成，需要外部的sales表的配合。

按照正常的思路中，子查询的执行步骤是这样的：

• 对于非相关子查询，先对内部的子查询表执行一次，然后将数据导入到临时表中。再执行外部查询，这时候对子查询的调用改为一个对常量的临时表数据调用就可以了。显然，在有sex和sale_id的索引下，非相关子查询的效率是很高的。
• 对于相关子查询，先执行外部查询，对于sales表的每一行数据，取出它的sale_id，然后代入到内部的子查询表中执行一次，看一下是否有数据。显然，即使有了sex和sale_id的索引，相关子查询依然很慢，因为它总是需要遍历一下外部的sales表中的每一行数据。

但是，在mysql 5.7以下的版本中，子查询的优化一直都是一坨屎，其将所有的子查询都看成是相关子查询来执行，这意味着sales_id in的操作会慢出天际，因为既要遍历外部sales表，又要遍历内部筛选条件很少的employee表（因为被看成是相关子查询，所以内部的employee表的查询结果没有缓存）。只有在mysql 8以后，子查询这个问题才得到正视，非相关子查询的内部查询只需要执行一次内部查询表就可以了，这种情况下就会比exists的操作快得多。

所以，在需要兼容多个mysql的历史版本情况下，根据子查询的执行步骤，我们有以下的优化原则：

• 我们尽量不要使用子查询，而改用连接操作或派生表操作。
• 万不得已要使用子查询时，就只用相关子查询，禁止使用非相关子查询，并且让外部查询表的规模尽可能小，内部查询表的筛选条件尽可能走索引。

4 查询优化

4.1 数据筛选优化

mysql中的innodb引擎只支持B-Tree索引，不支持Hash索引。在经常需要查询的字段上建立索引即可，这个没有什么好说的。

4.2 数据聚合优化

select max(score) from t_user where class = xxx
select count(*) from t_user where class = xxx

如果经常查询的数据是聚合操作，例如是以上的两种max,count。在mysql上是没有对应的索引的，它的做法是将数据全部筛选一遍，然后数数字来获取count，逐个遍历来获取max。啥都别说，慢得要死。

4.2.1 非聚簇索引

一般情况下，调用count(*)时走的是聚簇索引，聚簇索引看这里，聚簇索引由于数据量大，全表也是比较慢的。

然后，我们在非主键字段上建索引，让全表时走非聚簇索引扫描，这样速度会稍快一点。不过，当然也是一样的时间复杂度O(n)。

4.2.2 冗余表

建立一个冗余表，每次更新原始数据时，都将对应的max，count数值算好放到一个冗余的表上。很明显，这是一个反范式的操作，相当于手动实现一个物化视图。下次取max,count数值时直接到冗余表上取即可。时间复杂度是O(logn)，典型的空间换时间的方法。

举个例子，我们要统计到某个用户下的收藏食谱数量。很明显，计算这个用户下的收藏食谱数量是一个聚合操作。那么通过冗余表的方式，我们有三种实现方法：

• 在用户收藏时，同步执行count操作来更新该用户的收藏食谱数量到冗余表中。这样会导致堵塞用户收藏操作的操作，提高请求延迟。
• 在用户收藏后，发送异步消息，然后异步消息根据是取消收藏，或者添加收藏，来更新用户收藏食谱的计数器。这种方法没有延迟，而且执行也很快，避免了聚合操作，但要仔细地写各种情况下对应的代码。
• 在用户收藏后，发送异步消息，然后异步消息来执行count操作来更新该用户的收藏食谱数量到冗余表中。这种方法没有延迟，执行较慢一点，仍然是聚合操作，但改动比较小。

4.2.3 不可变数据

对于部分业务，我们可以想办法将表设置为只能添加，不能删除或修改的不可变数据。那么，冗余数据就可以直接写在新的一列上。例如，对于资金流水表，每一行不仅包含变动的这笔交易的金额，也包含这笔交易以后账户余额。由于资金流水的不可变动性，我们不需要考虑表修改时以前数据的余额修改问题，并且获取余额的速度也很快，直接取最新数据行的余额字段就可以了。

4.3 唯一插入优化

数据行数 ＝ 查询数据库
if 数据行数为空 {
    插入数据到数据库
}else{
    更新数据到数据库
}

我们经常会遇到以上的这种业务，这样做小量跑是没有问题的，大数据跑起来后会导致多个重复数据的出现。因为查询与更新的操作是两个操作，不在同一个事务上。你不能确保插入数据时，之前查询到的数据可能已经是过期的了。

4.3.1 加事务

开启数据库事务
数据行数 ＝ 查询数据库 for update
if 数据行数为空 {
    插入数据到数据库
}else{
    更新数据到数据库
}
提交数据库事务

简单的办法是直接前后加事务，问题是这样会直接导致表锁，因为查询操作大多是一个范围操作，这样会导致innodb的表锁，查询性能急剧下降。

4.3.2 唯一索引

insert ignore t_user values(clientId,xxx) values(xxx)
insert into t_user values(clientId,xxx) values(xxx) on duplicate update(xxxx)
replace into t_user values(clientId,xxx)

在查询字段上建立唯一索引，然后用insert ignore或insert into来做插入操作，将查询，插入操作合并成一条语句，然后交给mysql做单条语句的事务操作。大幅提高插入的执行效率，同时保证了事务性，数据不会有重复。问题是，这样做的话自增主键会留下很多空洞。造成上一次插入的clientId是10001，这次就变成了19999。中间的自增ID数据凭空消失了。

这是一个mysql的bug，官网说除了将auto_incr设置为锁表操作外，没有其他办法。但是一旦将auto_incr设置为锁表，就会掉入到上一节所说的解决方案的性能上，非常悲催。

4.3.3 唯一索引+query

数据行数 ＝ 查询数据库
if 数据行数为空 {
    插入数据到数据库( insert into on duplicate update )
}else{
    更新数据到数据库
}

我们暂时能想到的办法是这样的，插入前先查询数据，不为空走原逻辑，为空的话，走insert on duplicate key update的逻辑。这样绝大部分冲突的行为都走下面的更新操作，不走insert操作，不会触发insert into xx on duplicate update的自增凭空操作。万一真的冲突了，我们还会有insert into on duplicate update保底，不会导致数据重复。由于冲突情况总是少见的，所以造成的gap也不会多。这不算是一个完美的方案，仍然会导致auto_incr的gap。但是，在保证性能的情况下，auto_incr的gap的数量会少很多很多。如果你有更好的办法，也请留言给我噢

4.4 分页优化

数据量大时，业务要求肯定需要分页。有几个方案，可以试试。假设我们面对的是一个千万级的用户表。

4.4.1 limit,offset+count(*)

使用limit,offset来获取数据分页下的具体数据，用count操作来确定是否有剩余数据。杯具的问题是，mysql没有对limit,offset进行优化的办法，他只能获取到所有数据，然后偏移到offset的位置，然后取limit长度。每次都是全表扫描，百万数据量时这样做就开始卡爆了。

• 优点：支持跳页操作
• 缺点：非常慢，顶部新增数据，会导致页面间数据重复。
• 场景：业务后台管理系统

4.4.2 子查询获取offset的id，然后用id做查询，再排序分页

使用子查询找到offset对应的userId，然后外部查询根据userId的值做where条件，取limit

• 优点：稍快
• 缺点：仍然没有走索引，还是在同一个数量级上
• 场景：无

4.4.3 id做offset

直接用userId来代替offset，limit保持不变来获取分页下的具体数据。由于这样写的话全部走索引，所以速度快到爆。userId来自于上一个分页尾部userId，也就是这种方法不能跳页，只能顺序往下慢慢翻页。

• 优点：非常快，页面间数据不重复。
• 缺点：不支持跳页操作
• 场景：业务前端系统

这种做法要充分使用索引的话，有几个条件：

• 分页偏移的列需要和order by的列是一致的
• 分页偏移的列是唯一的，没有数据是重复的，例如用createTime就不可以
• where里面其他条件必须是等号匹配，这样才能满足前缀匹配法则

4.4.4 子查询获取offset的id，然后排序分页，最后用id做查询

使用子查询找到order排序下，指定的limit和offset对应的userId，然后外部查询根据userId的取全部字段

• 优点：最快，而且避免了非聚簇索引在select *会产生不断回表的随机读问题
• 缺点：无
• 场景：几乎全部场景都能用

注意一旦order by的字段改为createTime就会超级慢，因为索引仅仅建立在age单个列上面。要对createTime排序的话，又要回表到聚簇索引里面获取createTime字段，再排序。

如果业务必须要求用createTime排序的话，就需要建立age与createTime的复合索引，这时候查找就不需要回表了。

但是，就最好的方案而言，尽可能用主键作为排序字段是最好的，因为这样的索引更小，而且主键排序没有歧义，createTime会有歧义（不同行可能有同一个createTime）

这是分页优化问题下的默认最佳做法，具体可以看这里

4.4.5 去掉count

我们在方法1和方法2时，在取到分页数据后，难免都要取一次count操作来决定是否有下一页数据。其实我们完全没必要这么做，是否有下一页的数据，仅需要对limit再取多一行就可以了。这样就能避免通过count来做聚合操作，和一次多余的sql查询。

4.4.6 分页数据重复

至此，如何优化mysql的分页优化已经很明显了，根据场景使用即可。在这要提示的一个坑是，mysql的分页查询中均需要带上order by，而order by的列必须是唯一的，不然会重现，换页间数据重复的问题。

例如，数据集为:

[userId,createTime]
[1,'2015-11-01'],
[2,'2015-11-03'],
[3,'2015-11-03'],
[4,'2015-11-08'],

如果order by createTime asc limit 0,2，这个数值为：

[userId,createTime]
[1,'2015-11-01'],
[2,'2015-11-03'],

但是，当你多运行多两次时，结果有可能为：

[userId,createTime]
[1,'2015-11-01'],
[3,'2015-11-03'],

两次结果不一样的，毕竟你指定mysql，仅仅对createTime进行排序，对于重复的createTime，数据间如何排序则由mysql来指定。所以，当你运行两次分页时，有可能就出现以下情况。

offset 2,0
[userId,createTime]
[1,'2015-11-01'],
[3,'2015-11-03'],

offset 2,2
[userId,createTime]
[3,'2015-11-03'],
[4,'2015-11-08'],

解决办法是，在order by后加入其他列，让排序的结果是唯一的，例如是order by createTime asc,clientId asc。解决了无论如何分页，数据都不会重叠的问题。

offset 2,0
[userId,createTime]
[1,'2015-11-01'],
[2,'2015-11-03'],

offset 2,2
[userId,createTime]
[3,'2015-11-03'],
[4,'2015-11-08'],

4.5 全文索引查询优化

在论坛，社区，电商等业务都会看到需要用根据关键词搜索对应数据。建立全文索引是mysql处理的一个弱项。

4.5.1 自带的fulltext索引

mysql的全文索引在mysql 5.7之前一直就是个鸡肋，没有自带中文分词，效率也不高，只支持MyISAM引擎。可在mysql 5.7之后，终于有了一点改善，自带ngram的分词，能支持innodb引擎，性能和主流全文搜索依然还有点距离，但算是勉强也能用了。Demo在这里

4.5.1.1 分词

alter table t_recipe add fulltext index contentIndex(title,summary)

mysql默认的分词引擎就是按照空格来分词，对于英文句子就没啥问题。但是对于像中日韩的语言就很头疼，因为他们的句子就没有空格这一说法。一个较为折衷的办法是，对输入的句子进行分词，然后分词间用空间分开，变成一个独立的句子，然后再插入到数据库中。要注意，这种情况下，要预先配置好innodb_ft_min_token_size变量为2，否则会因为输入的单词太短而搜索不出来。

alter table t_recipe add fulltext index contentIndex(title,summary) with parser ngram;

而在mysql 5.7中，默认自带了ngram作为分词引擎。

ngram_token_size=1 : '信', '息', '系', '统' 
ngram_token_size=2 : '信息', '息系', '系统';
ngram_token_size=3 : '信息系', '息系统';
ngram_token_size=4 : '信息系统'; 

ngram的分词方式相当暴力，就是隔开多少个字就直接切分出来，例如“信息系统”四个字在不同的配置下可以划分为以上的四种分词短语。一般来说，都是将ngram_token_size设置为2。ngram_token_size设置得越小，索引结构就越小，查询速度就越慢。例如，假设所有的文本只包含27个小写字母，如果ngram_token_size设置为1，那么字典最多只有27个分词。如果ngram_token_size设置为2，那么字典最多会有\(27*27=729\)个分词。如果ngram_token_size设置为3，那么字典最多会有\(27*27*27=19683\)个分词。字典越大，分词之间的区分度就越高，分词对应的倒排链就越短，查询速度就越快，相应的索引结构也越大。

雷峰塔是一个著名的景点
尖峰塔防是一款非常火爆的游戏，其是以雷峰作为原型开发的

例如，在以上的两个句子中，以ngram为2分词。当输入的单词是“雷峰塔”三个字时，会被划分为”雷峰”,“峰塔”的两个短语，然后将这两个短语扔进去全文索引引擎中搜索，寻找同时包含这两个短语的句子。因此，结果就是，我输入的是“雷峰塔”，竟然这两个句子都出来了，而且和关键字相关性都是相同的！

4.5.1.2 搜索方式

mysql支持两种全文搜索方式，自然语言搜索，和布尔全文搜索，他们之间的区别是：

• 自然语言搜索默认有50%阀值限制，如果某个短语匹配超过了50%的文档，那么会被认为是停用词，mysql直接返回一个空集，代表没有搜索到任何结果。而布尔全文搜索就没有这个限制了。
• 布尔全文搜索严格要求短语的顺序和句子中短语的顺序是一致的，自然语言搜索就没有这个限制。例如，输入的是“雷峰塔”这个关键词时，会被ngram划分为两个短语”雷锋”和“峰塔”，在布尔全文搜索规则下，句子中的“雷锋”和“峰塔”必须接连先后出现时才会被选进，因此上一节所出现的问题就不复存在了。
• 布尔全文搜索支持+，-，>，<，*等的搜索语法规格，自然语言搜索就没有这些搜索语法规则了。一个常见的语法规则是*雷锋*，这样的不仅雷锋短语的文档能搜索出来，而且包含雷锋两个字的短语的文档都能搜索出来。
• 自然语言搜索默认以搜索相关性排序，布尔全文搜索则没有。

另外，无论是自然语言搜索，还是布尔全文搜索，都会受到innodb_ft_min_token_size（默认为3）和innodb_ft_max_token_size（默认为84）的影响，输入的词组长度不在这个范围内的都会直接返回空集。

4.5.1.3 总结

根据mysql的全文搜索规则，使用mysql的全文索引的最佳实践是：

• 数据量不大，规模小
• 优先使用自己的分词系统，用空格划分后扔进去，搜索时使用布尔全文搜索+星号匹配的方式查找。
• 在没有分词系统的条件下，才退而求其次选用ngram分词系统。

4.5.2 Lucene与solr

lucene是一个全文搜索的sdk包，在这个包上可以实现一整套全文索引的框架，分词，倒排索引，相关性，排序等等。solr是在lucene的基础上封装一整套全文搜索的框架，开箱即用。

4.5.3 spinx与coreseek

spinx与coreseek是一个C++的全文索引引擎，性能较快。coreseek是基于spinx的基础上加入中文分词的搜索引擎，集成的方法有两种。

• 作为mysql中间层，过滤mysql请求中全文索引查询，全文索引做spinx，其他的原封不动地走mysql。原来调用mysql的代码都不用改。
• 作为mysql的辅助客户端，定时从mysql获取数据来增量更新索引，搜索时向客户端提出搜索。调用mysql的代码都要改。

4.5.4 阿里云opensearch

和阿里云的数据库产品结合比较好，而且经历大规模的实践尝试，功能比较多，可以实时更改排序表达式（精排和粗排），比较灵活好用，值得推荐。

4.6 地理位置查询优化

随着LBS程序的兴起，我们经常会碰到说计算某个位置附近有什么其他用户，或者某个位置附近有什么餐厅的查询。

4.6.1 自带的索引

mysql并不适合做地理位置查询，强做的话有三种办法。

• 数据库存入经纬度信息，然后输入用户的经纬度信息，然后全表搜索，用三角函数算出最近的附近用户有哪些。优点是准，缺点是慢，非常的慢，每次都是全表搜索。
• 数据库存入经纬度信息，然后根据用户输入的经纬度信息，计算最近经度的范围与维度的范围。然后用SELECT * FROM checkinTable WHERE ((lat BETWEEN ? AND ?) AND (lng BETWEEN ? AND ?))来获取这个范围的所有数据。优点是略快（要看数据的分布），缺点是大概准，客户端需要自己排序。
• 数据库存入经纬度的geohash信息，然后用户输入的经纬度信息进行一次geohash，然后获取该geohash附近的八个geohash，然后在数据库直接匹配到数据。优点是非常快，缺点是还算准，客户端需要自己排序。

4.6.2 mongodb的地理索引

mongodb有专门的地理位置索引，mysql的数据实时更新到mongodb，然后在mongodb上查询就可以了。优点是简单，缺点是比mysql快，比postgres慢。

4.6.3 PostgreSQL的地理索引

PostgreSQL也有专门的地理位置索引，使用方法跟mongodb类似，将mysql数据实时更新到postgres上就可以了。优点是简单，支持数据量大，速度快。

4.6.4 elasticsearch引擎

基于luceue的搜索引擎，优点是速度快，缺点是集成困难。

4.6.5 阿里云opensearch

优点是，简单，快，支持数据量无限大，成熟。缺点是要钱。

4.7 集合结构查询优化

数据表中某个字段是一个集合结构，例如是每一行数据都包含一个整数数组字段array。我们希望能输入一个数字int，查询这个int包含在这个数组的行有哪些。或者输入一个数组array2，查询这个array2与数组array有交集的行有哪些。

4.7.1 mysql的全文索引

存储数组时用将整数用空格符作为分割连接起来，然后转换成字符串存放到字段中，然后搜索时有两种办法

• 搜索时可以通过%like%来实现全表扫描
• 将数组所在的字符串建立全文索引，搜索时使用布尔模式进行搜索就可以了，要注意每个搜索符要加+号，不然或被认为是多个词组的或操作，Demo在这里

4.7.2 monogodb的索引

monogodb的GiST和GIN索引可以解决这个问题。

• GIN索引的本质是倒排索引，为每个int建立一个倒排索引链，然后得到多个倒排索引链后通过归并排序来实现distinct。优点是查找速度快，缺点是实时更新慢。
• GIST索引的本质是区间树，每个树节点将数据划分为三部分，都在集合元素都在左边区间的节点，集合元素都在右边区间的节点，和集合元素既在左边又在右边区间的节点。那么搜索时，需要不断递归搜索左右区间，和交叉区间的数据，期望的时间复杂度为树的高度。优点是实时更新快，缺点是在不幸的情况下，数据都落在交叉区间时，时间复杂度会退化为全表扫描。

4.7.3 elasticsearch引擎

实际上就是倒排索引的实现

4.8 去重和计数优化

对于特定场合下的去重和计数，是有特别的方法，例如是这个。例如，统计某个时间段内某个页面的访问用户数量。

4.8.1 索引内的全扫描

select count(distinct(userId)) from t_page_log
where createTime >= 'xxx' and createTime <= 'xxx' and pageName == 'xxx'

在mysql中，我们最多只能通过索引，定位到某个时间段和页面下的访问日志，然后再将这些海量的访问日志全部提取出来，将userId进行排序去重然后计数，速度相当的慢。

4.8.2 redis的位图索引

我们可以引入位图索引，假设有100万个用户，每个位代表特定小时内特定页面的特定用户是否访问过。那么122kb的内存就能展示特定小时内特定页面的所有用户的访问信息，然后我们对多个小时内的数据取或操作，就能得出某个时间段内特定页面的所有用户的访问信息了，最后直接对结果数据统计该位为1的数据即可。假设，需要统计最近一周的数据，那么，我们需要对122kb✖️24✖️7=20.5m的数据遍历一下就可以了，相当的快速。

要注意这种去重和计数的方法是有条件的，计数和去重的字段是固定的，筛选和归并的字段的值是可数的，并且是事先已经知道的值。

4.9 联合查询优化

create table t_user(
    userId integer not null auto_increment,
    name varchar(32) not null,
    createTime timestamp not null default CURRENT_TIMESTAMP,
    modifyTime timestamp not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, 
    primary key( userId )
)engine=innodb default charset=utf8mb4 auto_increment = 10001;

alter table t_user add index nameIndex(name);

create table t_article(
    articleId integer not null auto_increment,
    userId integer not null,
    title varchar(32) not null,
    createTime timestamp not null default CURRENT_TIMESTAMP,
    modifyTime timestamp not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, 
    primary key( articleId )
)engine=innodb default charset=utf8mb4 auto_increment = 10001;

联合查询，就是需要联合多个表的数据才能查询的结果。假设我们有以上的两个表，user表10w数据，article表有100w数据，目前我们要查询article的title为4tLu7E8iMfL0Zjr8时对应的用户名称是什么。具体代码看这里

4.9.1 子查询优化

select sql_no_cache * from t_user where userId in (select userId from t_article where title="4tLu7E8iMfL0Zjr8")\G;
select sql_no_cache * from t_user where exists (select userId from t_article where title="4tLu7E8iMfL0Zjr8" and t_user.userId = t_article.userId)\G;

这是非相关子查询和相关子查询的语句

可以看出，在mysql 5.6版本中，in查找都很慢，这是因为之前说过的mysql对所有的子查询都看成是相关子查询导致的。而在mysql 8中，exists是相关子查询，外层表循环是不可忽略的，所以即使加了title索引也无济于事。而in查询就快多了，有效地利用了非相关子查询和索引的特性。因此，对于in查询对于有旧的mysql兼容有要求时就不能使用了，exists查找的方式效率就一般般了，不过胜在开发简单，适合查询次数不多数据量不大的场景中使用。

4.9.2 派生表优化

select * from t_user,(select userId from t_article where title="4tLu7E8iMfL0Zjr8") as t2 where t_user.userId = t2.userId;

派生表有效地保证了t_article表只会被读取一次，所以速度也是飞快，并且能有效地利用了索引。

4.9.3 连接优化

select sql_no_cache * from t_user join t_article on t_user.userId = t_article.userId and t_article.title="4tLu7E8iMfL0Zjr8"\G;
select sql_no_cache * from t_user left join t_article on t_user.userId = t_article.userId and t_article.title="4tLu7E8iMfL0Zjr8"\G;

可以看出，使用内连接时，mysql能有效地根据筛选条件来优先选择t_article作为驱动表，大大提高了搜索的速度。但是，在使用左连接时，强制指定了t_user必须为驱动表，所以这个时候即使加了title索引也无济于事。

4.9.4 反范式优化

select sql_no_cache * from t_user_article where title = "4tLu7E8iMfL0Zjr8"\G;

还有一个反范式的设计，将t_article的内容和t_user的内容用手动同步的策略同步到同一个表上，造成了表数据的冗余，虽然违反了数据库的三大范式设计，但这样做就不需要联合查询了。不仅速度快得多，还能支持跨多表的多列联合索引，这是前面三种方法都不具备的。这种方法，适合对大数据量的经常性查询时使用。

4.10 更新并获取优化

有时候，我们需要做更新操作，并需要获取更新后的那一行的新数据。例如，我们要对fish账户减100余额，并且将这一行执行后的余额，和操作金额写入资金流水表。

4.10.1 事务

begin;
select money from t_money where name = 'fish' for update;
update money set money = money - 100 where name = 'fish';
insert into t_money_log(name,money,totalMoney,remark) values('fish',-100,money-100,'xxxx');

通过事务来保证，在事务内部，这个fish的账户余额没有变化过。

4.10.2 last_insert_id

update money set money = last_insert_id(money - 100) where name = 'fish';
select last_insert_id() as totalMoney;
insert into t_money_log(name,money,totalMoney,remark) values('fish',-100,totalMoney,'xxxx');

利用last_insert_id来获取更新数据时的快照结果，就能避免使用事务了。

4.11 update join

一般情况下，update只能拿出一个表，将同一个表的一个字段，赋值到同一个表的另外一个字段。update join能够实现，先将两个表进行join操作，实现，从一个表的一个字段，赋值到另外一个表的一个字段。

代码看这里

4.11.1 脚本

drop database if exists update_join;

create database update_join;

use update_join;

create table item(
    id integer not null,
    name varchar(64) not null,
    primary key(id)
)engine=innodb default charset=utf8mb4;

create table item_order(
    id integer not null,
    item_id integer not null,
    item_name varchar(64) not null,
    price decimal(18,4) not null,
    primary key(id)
)engine=innodb default charset=utf8mb4;

insert into item(id,name) values
(101,'item_1'),
(102,'item_2'),
(103,'item_3');

insert into item_order(id,item_id,item_name,price)values
(201,101,'',100.0),
(202,103,'',90),
(203,104,'',80),
(204,101,'',79),
(205,102,'',667);

初始化数据，init.sql

select * from item_order;

查询数据，query.sql

update item_order 
inner join item on item_order.item_id = item.id
set item_order.item_name = item.name;

update inner join的用法，内连接，update_inner.sql

update item_order 
left join item on item_order.item_id = item.id
set item_order.item_name = 'unknown_item'
where item.id is null;

update left join的用法，左外连接，一般用来设置默认值，update_left.sql

select 'update_inner_join_test';

source ./init.sql
source ./query.sql
source update_inner.sql
source ./query.sql

select 'update_left_join_test';

source ./init.sql
source ./query.sql
source update_left.sql
source ./query.sql

整体的测试脚本，run.sql

4.11.2 结果

update_inner_join_test
update_inner_join_test
id      item_id item_name       price
201     101             100.0000
202     103             90.0000
203     104             80.0000
204     101             79.0000
205     102             667.0000
id      item_id item_name       price
201     101     item_1  100.0000
202     103     item_3  90.0000
203     104             80.0000
204     101     item_1  79.0000
205     102     item_2  667.0000
update_left_join_test
update_left_join_test
id      item_id item_name       price
201     101             100.0000
202     103             90.0000
203     104             80.0000
204     101             79.0000
205     102             667.0000
id      item_id item_name       price
201     101             100.0000
202     103             90.0000
203     104     unknown_item    80.0000
204     101             79.0000
205     102             667.0000

最终结果，这个不太难呀。

5 事务

5.1 ACID

数据库的事务有ACID的四个特性的：

• 原子性（atomicity），事务里的事情要么全部做完，要么执行过程中失败，此时回滚。
• 一致性（consistency），数据库总是从一个一致性的状态转换到另一个一致性的状态，在该事务的进行的过程中，其他事务不会读取到该事务的一个中间状态。
• 独立性（isolation），并发的事务之间不会相互影响。
• 持久性（durability），事务一旦提交，所做的修改就会永久保存在数据库上。

假设，我们要从头开始设计一个数据库的事务管理器。

update t_money set money = money - 100 where name = 'fish' where money - 100 >= 0
update t_money set money = money + 100 where name = 'cat'

我们要做的操作是从fish的账户转钱到cat的账户。

5.1.1 没有ACID

var (
    a = 102
    b = 103
    c = 103
)
func getA(){
    return a
}
func getB(){
    return b
}
func getC(){
    return c
}
func transferB(){
    if( a - 100 > 0 ){
        a = a - 100
        b = b + 100
    }
}
func transferC(){
    if( a - 100 > 0 ){
        a = a - 100
        c = c + 100
    }
}

从fish的账户减100，然后在cat的账户加100。很显然，如果在cat的账户加100的操作失败后，我们没有考虑到要在fish的账户的加回100，这就是没有原子性。

5.1.2 只有A

func transferB(){
    oldA := a
    oldB := b
    try{
        if( a - 100 > 0 ){
            a = a - 100
            b = b + 100
        }
    }catch(e){
        a = oldA
        b = oldB
    }
}
func transferC(){
    oldA := a
    oldC := c
    try{
        if( a - 100 > 0 ){
            a = a - 100
            c = c + 100
        }
    }catch(e){
        a = oldA
        c = oldC
    }
}

我们加入了改进，在数据库程序中捕捉异常，当第二步失败后，自动回滚恢复第一步的数据。但是，我们没有考虑到数据库在断电的状态下，没有自动回滚数据的问题，这就是没有持久性。

5.1.3 只有AD

func transferB(){
    oldA := a
    oldB := b
    writeLog('begin',a,b,c);
    try{
        if( a - 100 > 0 ){
            a = a - 100
            b = b + 100
        }
        writeLog('commit',a,b,c);
    }catch(e){
        a = oldA
        b = oldB
        writeLog('rollback',a,b,c);
    }
}
func transferC(){
    oldA := a
    oldC := c
    writeLog('begin',a,b,c);
    try{
        if( a - 100 > 0 ){
            a = a - 100
            c = c + 100
        }
        writeLog('commit',a,b,c);
    }catch(e){
        a = oldA
        c = oldC
        writeLog('rollback',a,b,c);
    }
}

只有AD，我们进一步改进，在第一步开始前就进行先写入磁盘日志，表明要开始的两个操作，在事务完成后，再写入一个磁盘日志，表明这个事务操作执行成功了。如果断电发生了，数据库程序通过检查磁盘日志就知道有一个事务没有完整完成，需要执行对应的回滚操作，从而保证即使在断电的状况下，数据依然是原子的。但是，我们没有考虑到，数据库在回滚的过程中，其他事务会看到fish的账户从减了100，又到加了100的状态，这意味让其他事务看到该事务的一个中间状态了，这就是没有一致性。

5.1.4 只有ACD

type Db struct{
    a int
    b int
    c int
}
var (
    db = &Db{102,102,102}
)
func getA(){
    return db.a
}
func getB(){
    return db.b
}
func getC(){
    return db.c
}
func transferB(){
    newDb := &Db{db.a,db.b,db.c}
    writeLog('begin',db);
    try{
        if( newDb.a - 100 > 0 ){
            newDb.a = newDb.a - 100
            newDb.b = newDb.b + 100
        }
        db = newDb
        writeLog('commit',db);
    }catch(e){
        writeLog('rollback',db);
    }
}
func transferC(){
    newDb := &Db{db.a,db.b,db.c}
    writeLog('begin',db);
    try{
        if( newDb.a - 100 > 0 ){
            newDb.a = newDb.a - 100
            newDb.c = newDb.c + 100
        }
        db = newDb
        writeLog('commit',db);
    }catch(e){
        writeLog('rollback',db);
    }
}

只有ACD，我们进一步改进，使用多版本B+树来实现一致性。每个事务在更新前都获得一个递增的版本号，只有这个事务执行成功后才会与当前版本的B树合并。这相当于函数式编程的不可变数据结构，也被称为可持久化数据结构。但是，我们没有考虑到，并发环境下的数据冲突的问题，即使建立多版本的B+树结构，如果fish的账号是102元，如果两个事务在并发的情况下，都先执行第一步了，发现成功，然后再都执行第二步，就会出现两个账号都收到100元，但是fish只扣了一次100元的问题，这就是没有独立性，也被称为隔离性的问题。

5.1.5 包含完整的ACID

type Db struct{
    a int
    b int
    c int
}
var (
    db = &Db{102,102,102}
    mutex = &sync.Mutex{}
)
func getA(){
    return db.a
}
func getB(){
    return db.b
}
func getC(){
    return db.c
}
func transferB(){
    mutex.Lock()
    defer Mutex.Unlock()
    
    newDb := &Db{db.a,db.b,db.c}
    writeLog('begin',db);
    try{
        if( newDb.a - 100 > 0 ){
            newDb.a = newDb.a - 100
            newDb.b = newDb.b + 100
        }
        db = newDb
        writeLog('commit',db);
    }catch(e){
        writeLog('rollback',db);
    }
}
func transferC(){
    mutex.Lock()
    defer Mutex.Unlock()
    
    newDb := &Db{db.a,db.b,db.c}
    writeLog('begin',db);
    try{
        if( newDb.a - 100 > 0 ){
            newDb.a = newDb.a - 100
            newDb.c = newDb.c + 100
        }
        db = newDb
        writeLog('commit',db);
    }catch(e){
        writeLog('rollback',db);
    }
}

包含ACID，我们做最后的改进，通过引入加锁来解决这个问题。锁可以是乐观锁，也可以是悲观锁。结合之前的多版本的B+树，我们就实现了MVCC来同时保证一致性和隔离性的问题了。要注意我们最终实现的版本中，读数据是不阻塞的，只有写数据才会阻塞等待，同时我们保证了读出来的数据总是一致的，不会读出事务的中间状态。

5.2 并发隔离

5.2.1 锁类型

处理并发问题中，乐观锁与悲观锁是主要的两种锁类型，例如在转账的例子中

func transferC(){
    mutex.Lock()
    defer Mutex.Unlock()
    
    newDb := &Db{db.a,db.b,db.c}
    if( newDb.a - 100 > 0 ){
        newDb.a = newDb.a - 100
        newDb.c = newDb.c + 100
    }
    db = newDb
}

通过显示的引入互斥锁来让并发的两个请求等待，就是悲观锁。悲观锁的特点是假设并发场景是比较多的，共享资源每次只给一个请求使用，其它请求阻塞，用完后再把资源转让给其它线程。

func transferC(){
    newDb := &Db{db.a,db.b,db.c}
    if( newDb.a - 100 > 0 ){
        newDb.a = newDb.a - 100
        newDb.c = newDb.c + 100
    }
    oldPointer = (*unsafe.Pointer)&db
    newPointer = (*unsafe.Pointer)&newDb
    if( sync.CompareAndSwapPointer(oldPointer,*oldPointer,*newPointer) == false ){
        panic("fail!")
    }
}

通过直接更新数据，然后在最终的数据更新时通过比较数据库版本来确定这一次的修改是否成功，如果失败的话要么直接报错说遇到并发问题，要么重试设置，这就是乐观锁。乐观锁的特点是假设并发场景是比较少的，共享资源可以同时给多个请求来设置，如果遇到并发错误就直接gg。

可以看出，乐观锁适用于并发量较少的场景，性能高，但在高并发的环境下可能会遇到忙等或者失败，但保证不会造成死锁。悲观锁适用于并发亮较大的场景，性能一般，但在高并发的环境下保证不会遇到忙等或者失败，但可能会遇到死锁。

在mysql中，乐观锁需要手动实现开启，悲观锁在开启事务时需要自动与手动相结合的开启。而在postgres中，乐观锁与悲观锁是全自动开启的。

5.2.2 锁粒度

在mysql中，锁粒度主要划分两种，表锁和行锁。

• 表锁，就是以表为粒度的锁结构，在mysql中，myisam引擎和innodb引擎都有表锁。
• 行锁，就是以行为粒度的锁结构，在mysql中，只有innodb引擎有行锁，并且行锁只有在查询在走索引的情况下才会触发，如果查询条件没有走索引，依然会用表锁。

行锁的分类

• 行锁（Record Lock），以键为索引，在索引树上记录锁定某几行的数据。
• 间隙锁（Gap Lock），在索引树上锁定一个开区间内的多行数据。
• 后码锁（Next-Key Lock），是行锁和间隙锁的组合，同时锁定区间和当前行的数据，相当于锁定一个半开半闭区间内的多行数据。

5.2.3 隔离级别

在有了不同的锁类型和锁粒度，mysql就会根据用户需要的不同需求的并发隔离级别来选择。

mysql> create database mvcc;
Query OK, 1 row affected (0.03 sec)

mysql> use mvcc;
Database changed

mysql> create table t_money(name varchar(127),money integer)engine=innodb;
Query OK, 0 rows affected (0.10 sec)

mysql> insert into t_money(name,money)values('fish',102),('cat',102);
Query OK, 2 rows affected (0.11 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t_money;
+------+-------+
| name | money |
+------+-------+
| fish |   102 |
| cat  |   102 |
+------+-------+
2 rows in set (0.00 sec)

我们会以fish转账cat共200元为例子，举例不同的隔离模式下会有什么不同的结果。

5.2.3.1 读未提交（read-uncommitted）

在读未提交的隔离模式下，读到的数据可能是未提交的数据，很明显，这种情况下会造成严重错误。

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
select @@transaction_isolation;

首先设置当前session的隔离模式为read uncommited;

因此，这个时候，即使fish的余额有102，有两个事务请求转账都没有成功，正常的结果应该是让事务2执行成功。

5.2.3.2 读提交（read-committed）

在读提交的隔离模式下，读到的数据都是已经提交的数据，但不保证在同一个事务中，两次读到的数据不一致。

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
select @@transaction_isolation;

首先设置当前session的隔离模式为read commited;

成功解决上一节的问题，在事务1掉线的情况下，事务2依然能成功转账。

但是，会导致新的问题，由于只有提交的数据才会看到，两次select看到的余额都是102，每个事务都认为余额是充足的，因此都允许转账，最直接导致了cat账户多了200元，fish账户被减到负数。这个问题归根到底是由于，在同一个事务2中，第一个select看到的余额是102元，但第二个update看到的余额是2元，也就是在同一个事务中看到的同一行数据是不一样的，这就是不可重复读的问题。

5.2.3.3 可重复读（repeatable-read）

在可重复读的隔离模式下，读到的数据都是已经提交的数据，并且保证在同一个事务中，两次读到的数据是一样的。但是，在mysql中，默认的RR隔离模式下，在同一个事务中两次读到的同一行是一样的，并不代表这一行的数据在这个事务中是不变的，因为它仅仅是读取事务开始前这一行数据的快照。要让，在同一个事务中两次读到的同一行是不变的，我们需要手动加入for update命令。

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
select @@transaction_isolation;

首先设置当前session的隔离模式为repeatable read;

完美解决所有问题，但是需要手动和自动相结合的方式。如果没有加入for update，就会产生幻读问题，幻读就是保证在同一个事务中所读取的同一行数据是一样的，但是读取的都是事务发生前的快照数据，并没有保证这一行数据是真的不变的。

5.2.3.4 串行化（serializable）

串行化就是将所有事务都是串行执行的，相当于使用一个全局锁来执行各个事务。这样做的好处是保证最好的隔离性，而且不需要手动指定，但并发程度也是最低的。要注意的是，串行化的实现是对每个select操作的数据加读锁，对更新的数据加写锁，来实现模拟的全局锁的效果，这样做既能实现串行化的效果，又能提高并发性，但是这样做的加锁粒度是行锁，而不是全局锁，因此会有可能造成死锁。

如果你要实现一个最严格的串行化而且能避免死锁，可以选择在程序中对每个POST请求都竞争获取一个mutex来实现，但是这样做的并发性是非常低的。

5.2.3.5 总结

并发隔离的问题描述：

• 脏读，读取的是未提交数据
• 不可重复读，读取的都是已提交的数据，但在同一个事务中，不保证对同一行数据的两次读取结果是一样的。
• 幻读，读取的都是已提交的数据，在同一个事务中，也保证对同一行数据的两次读取结果是一样的。但是，读取的数据都是事务开始时的快照数据，事务开始后其他事务依然可以修改这一行的数据，因此不保证这一行的数据是不可变的。

mysql默认在可重复读的隔离级别，对于幻读问题，mysql将解决方法交给了程序员，让程序员手动加上for update来避免，很明显，这是一种显式的悲观锁的方法。

而在postgres,sql server上，在可重复读的隔离级别上，对于幻读问题，它们是采用隐式的乐观锁的方法，虽然读出来的都是快照数据。但是如果其他事务修改了这一行数据时，该事务就会自动检测出来，并抛出异常来回滚整个事务。

在并发隔离的这个问题上，没有完美的解决方案，你需要仔细考虑业务的需求和开发的资源才能做出最好的决策。

6 维护

6.1 安装与启动

6.1.1 mac环境dmg安装

在mysql官网选择dmg包下载安装就可以了

注意在这种情况下安装的mysql，需要在系统设置面板里面启动和停止mysql，不能通过命令行进行操作，不太建议这么安装

6.1.2 mac环境brew安装

brew update
brew install mysql
mysqld

brew安装mysql 8，安装完以后直接用mysqld启动mysql，初始登录密码为空，注意不要用brew services来启动mysql，这样启动的mysql初始登录密码不为空。

brew services start mysql
brew services restart mysql
brew services stop mysql

通过brew services将mysql设置为服务，开机就会自动重启了

mysql.server stop
mysql.server start

支持命令行的临时的mysql.server启动与停止

mysqld --help --verbose|grep -A1 -B1 cnf

配置文件的加载顺序，一般放在/usr/local/etc/my.cnf

6.1.3 linux环境apt安装

sudo apt-get install mysql-server

就一句安装，超级简单

sudo service mysql start
sudo service mysql restart
sudo service mysql stop

通过自带的service对mysql设置为服务，开机就会自动启动了

mysql.server stop
mysql.server start

支持命令行的临时的mysql.server启动与停止

mysqld --help --verbose|grep -A1 -B1 cnf

配置文件的加载顺序，一般放在/usr/local/etc/my.cnf

6.1.4 linux环境yum安装

sudo yum update
sudo yum -y install mysql-server

安装

sudo systemctl start mysqld.service
sudo systemctl stop mysqld.service
sudo systemctl restart mysqld.service

服务维护

配置文件与目录介绍：

• 命令目录 /usr/bin
• 程序目录 /usr/share/mysql
• 数据文件目录 /var/lib/mysql
• 配置目录 /etc/my.cnf.d
• 客户端配置文件 /etc/my.cnf.d/client.cnf
• 服务器配置文件 /etc/my.cnf.d/mysql-server.cnf
• 默认权限配置文件 /etc/my.cnf.d/mysql-default-authentication-plugin.cnf

sudo mysql_secure_installation

配置向导，root密码等信息

mysql -uroot -p 

登入mysql，看是否可以用

6.1.5 Windows环境安装

我们举例，在Windows 10下如何安装mysql 8，后面的所有工具都需要使用cmd或者PowerShell命令行，不要使用MinGW bash工具。

到这里下载Windows版本，选择Zip Archinve版本就可以了。

然后把他解压到Program Files文件夹中

[mysqld]
# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir=C:\\Program Files\\mysql-8.0.28-winx64
# 设置mysql数据库的数据的存放目录
datadir=C:\\Program Files\\mysql-8.0.28-winx64\\data
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8mb4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8

在目录中创建my.ini文件，以及创建data文件夹

MYSQL_HOME = C:\Program Files\mysql-8.0.28-winx64
PATH = %PATH%:%MYSQL_HOME%\bin

加入MYSQL_HOME和PATH两个环境变量

mysqld --initialize --user=mysql --console

初始化mysql，注意初始化的时候会出现初始的root密码

# 添加mysql服务
mysqld --install

# 启动mysql服务
net start mysql

# 停止mysql服务
# net stop mysql

添加和启动mysql服务

mysql -uroot -p

启动mysql即可

6.2 密码设置

6.2.1 密码校验方式

default-authentication-plugin=mysql_native_password

在my.cnf文件的mysqld下设置以上的密码校验方式，mysql 8之前的使用的密码验证方式为mysql_native_password，mysql 8以后的密码验证方式改为了caching-sha2-password，导致很多mysql客户端都用不了了，以上的方法能把密码验证方式改了。

6.2.2 记得密码

use mysql;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的新密码';
flush privileges;

已经知道密码的情况下，直接登录，然后输入以上命令即可。

use mysql;
update user set host='%' where user='root';
flush privileges;

这样可以设置用户的可登陆域名为任意，也就是允许外网登录

6.2.3 密码安全策略

SHOW VARIABLES LIKE 'validate_password%';
set global validate_password.policy=0;
set global validate_password.mixed_case_count=0;
set global validate_password.special_char_count=0;
set global validate_password.length=1;

自mysql 8以后，密码就会严格很多了，不能随便设置像123456的简单密码了。但是对于，测试环境，为了统一调试的方便，我们需要设置为简单的统一密码。

6.2.4 忘记密码

mysqld --console --skip-grant-tables

先停止mysql服务，然后以以上命令启动mysql服务

use mysql;
update user set authentication_string='' where user='root';

进入sql后，输入以上命令，将密码设置为空。然后启动mysql服务，以空密码登录即可。

6.2.5 向导设置

mysql_secure_installation

一个简单的方法是通过mysql_secure_installation来设置密码

6.3 用户与授权

6.3.1 用户

create user 'username'@'localhost' identified by 'password';

创建一个username用户，密码为password，只允许通过localhost登录。我们也可以设置为%，表示允许任意的外网登录

drop user 'username'@'host';

删除username用户

6.3.2 授权

# GRANT privileges ON databasename.tablename TO 'username'@'host'

grant all on database.* to 'username'@'localhost';

赋予username用户，可以操控database任意表的任意操作权限

6.4 备份与恢复

mysqldump -uroot -p1 Yinghao > a.sql

备份Yinghao数据库到a.sql

mysql -uroot -p1 database_name < a.sql

恢复a.sql的数据到本地数据库

6.5 连接池

db.SetMaxIdleConns(xxx)

为了减少频繁使用短连接连接数据库所造成的socket资源开销，我们一般会使用连接池的方式向mysql进行连接，只需要设置SetMaxIdleConns就可以了。

mysql write:broken pipe

但是，连接池里面的连接不是长期能用的，我们在使用的时候发现，有些长期不使用的连接就会被mysql主动断开，造成客户端使用这个连接时会返回broken pipe的错误。

SHOW GLOBAL VARIABLES LIKE '%timeout%'

mysql对于某个连接的主动断开的时间，是由wait_timeout变量所配置的，该值默认为28800秒，也就是8个小时。

db.SetConnMaxLifetime(time.Hours*7)

因此，我们可以在客户端设置连接池的最大生存时间为7小时，就主动切开和mysql的连接就可以了，这样就能避免broken pipe的问题。

6.6 日志

6.6.1 总体信息

show engine innodb status

这个命令很重要，可以看出当前线程数量，最近死锁情况，并发的负载情况等信息。

6.6.2 连接信息

mysql> show status like  'Threads%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 32    |
| Threads_connected | 10    |
| Threads_created   | 50    |
| Threads_rejected  | 0     |
| Threads_running   | 1     |
+-------------------+-------+
5 rows in set (0.00 sec)

输入以上命令，可以查看当前连接到mysql的连接数量。Threads_connected是总的连接数量，Threads_running是活跃的连接数量。一般来说，活跃连接数量要少于总的连接数量的，活跃连接是当前执行操作的连接，而其他不活跃的连接可能是在客户端的持久连接，它保持着与数据库的连接，等待客户端的处理。thread_cache_size是连接的缓存数量，当连接断开后，mysql可以根据这个参数让它缓存下来，而不是直接就销毁线程，这样下次创建线程时就能快多了。Threads_created是创建过的总的线程数量。

show full processlist

输入以上命令，可以查看当前所有连接的情况，他们当前的工作状态，运行的时间等等。Time列是该线程处于当前State状态下的持续时间，单位是秒。Info列是在运行状态时，该线程正在执行的sql语句。注意，这个语句尽可能在root用户下执行，才能查看到所有用户的连接执行情况。

6.7 schema

6.7.1 查看所有表格

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'mydb';

可以查看mydb下面的所有table

6.7.2 查看表信息

show create table abc

查看abc表的列

show index from abc;

查看abc表的索引

6.7.2 修改列

alter table abc drop column efg;

删除abc表的efg列

alter table abc add column efg integer not null default 0;

添加abc表的efg列

alter table abc change column efg efg2 decimal(12,4) not null;

修改abc表的efg列，到efg2列

7 架构

在中小型系统中，我们可以采用mysql的数据全包方案。

7.1 简单查询

面向用户端的接口，需要保证是高并发，低延迟的。因此，这些接口核心是，只让这些接口做基本的增删改查操作，不要出现聚合，连接，子查询，窗口，in，exist，not in，not exist等复杂类型操作。分页操作下尽可能使用limit多一行，或者索引id偏移的方法，避免获取全局count。

时效性在毫秒级别

7.2 组合查询

多条件组合查询，我们需要借助搜索引擎来优化，elasticsearch和open search。例如是

• 对食谱的关键词查询操作
• 后台多条件过滤查询操作

时效性在毫秒级别

7.3 实时计算

但是面向用户端的接口，难免会遇到一些需要做聚合的查询。我们的思路就要改为以空间换时间，以微批量换小量。

• 将用户的操作转换为一个个不可变的事件，然后让这些不可变的事件写入本地的日志，用flume写入到kafka消息队列中。另外一个简单的方法是直接写入到kafka队列中，但有可能遇到写入失败的情况。
• 启动daemon，以微批量的方式从消息队列中获取数据，在本地聚合后批量更新到当前的预聚合数据库。当然，如果做好了多个以字段分区的方法，就能避免多个客户端获取消息时需要加锁的问题。由于kafka中由客户端保存版本号，需要注意做好幂等特性以保证数据的准确性。
• 预聚合数据库的设计相当考究，例如可以用按小时，周，天，年的各个维度预先聚合，那么查询任意时间范围的结果的时候就会超级快。
• 30天内最热的食谱，作品，课程信息信息。以每天发布的内容进行聚合。

例子：

• 对个人的食谱，收藏，点赞数量的聚合查询操作。
• 对个人最近30天已浏览的食谱ID，已购买课程等信息。注意可以预聚合每天的浏览食谱，然后预聚合30天的浏览食谱

时效性在秒级别

当数据更大的时候，采用的方法是：

• 导入，flume+kafka（日志数据导入），canal+kafka（数据库导入）
• 计算，spark streaming，或者storm（现在这个少人用了，内存状态数据会丢失，但实时性好）
• 存储，hbase和cassandra（分布式二级索引结构）

另外一种方法是将计算和存储合起来的，Druid。

实时计算的特点是预聚合数据模型，需要的结果的时候只需要从多维度组合数据就可以了

7.4 离线计算

最后有一些十分复杂的面向用户端的接口，必须对全体数据进行离线计算的。我们的思路是将数据扔到hdfs，或者其他独立部署的数据库中，以定时的方式批量计算一遍。

• 复杂的排序，食谱，作品，课程推荐，根据热度，新度，特征相似度给与每个内容打上不同的权重，排序后写入到结果表中
• 图计算，计算cookie用户和登录态用户的等价，以计算出准确的访客数

时效性在小时级别

当数据更大的时候，采用的方法是：

• 导入，kafka+hdfs
• 计算，spark，spark sql。或者map/reduce，hive（速度较慢，但能处理的数据量更大）

离线计算的特点是精确，复杂的全数据计算，但时效性较差。

另外一类特殊的列式数据库，它的特点是：

• 只支持新增数据，不支持删除，修改数据，更不支持事务特性
• 以列的方式存储数据，数据之间相似性大，容易压缩数据，这样能读取小量的数据块，就能读取大量的数据，大幅减少IO操作
• 每一列的数据按照行键进行一大块一大块的分区，每个分区有概要的分布信息，和聚合信息
• 多列数据组合查询时，将各列筛选后得到的顺序行键做交集就可以了

这些特点造就了这种类型的数据库的适用场景是：

• 大量的行查询，小量的列查询，动不动就需要全表扫一遍的聚合操作
• 查询的工作量大，但并发量小

这类数据库的凸出代表就是ClickHouse，在没有图查询的业务下，它可以代替spark sql+hdfs的组合，性能很高，而且部署简单。

8 FAQ

8.1 Invalid default value for ‘modify_time’

create table user(
                     id integer not null,
                     name char(64) not null,
                     password char(60) not null,
                     roles varchar(128) not null,
                     remark varchar(255) not null,
                     is_enabled char(16) not null,
                     create_time timestamp not null,
                     modify_time timestamp not null,
                     primary key( id )
)engine=innodb default charset=utf8mb4 auto_increment = 10001;

就是一个这么简单的语句在mysql 8上面就会崩，那是因为使用mysql的默认时间字段赋值的问题，这真的是个坑。看这里

9 总结

mysql的坑还有很多，就先写到这了。

参考资料：

• 十步完全理解SQL
• Join的使用技巧和优化
• Mysql 5.7中文全文索引
• MySql 8窗口函数
• MySql 8窗口函数和CTE支持
• MySql 5.6子查询优化

• 本文作者： fishedee
• 版权声明： 本博客所有文章均采用 CC BY-NC-SA 3.0 CN 许可协议，转载必须注明出处！

相关文章

• 2016-03-18-分布式存储概览
• 2015-03-15-数据库分类
• 2018-06-04-schema数据库管理工具
• 2020-12-09-《数据密集型应用系统设计》读书笔记
• 2020-09-01-《PostgresSQL修炼之道》读书笔记