数据库版本号排序问题
今天我们聊一下文件版本1.0.0号这种数据在数据库排序问题的解决方案。
数据准备
CREATE TABLE `version` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`version_number` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
INSERT INTO `version` (`version_number`) VALUES ('1.0.0');
INSERT INTO `version` (`version_number`) VALUES ('1.0.1');
INSERT INTO `version` (`version_number`) VALUES ('1.0.2');
INSERT INTO `version` (`version_number`) VALUES ('1.0.11');
INSERT INTO `version` (`version_number`) VALUES ('1.0.12');
INSERT INTO `version` (`version_number`) VALUES ('1.1.3');
所以我们现在有如下的表结构version
| id(int(11)) | version_number(varchar(255)) |
|---|---|
| 1 | 1.0.0 |
| 2 | 1.0.1 |
| 3 | 1.0.2 |
| 4 | 1.0.11 |
| 5 | 1.0.12 |
| 6 | 1.1.3 |
问题说明
在数据库执行 order by version_number desc 降序排序，得到的结果就是下述的情况。
| id(int) | version_number(string) |
|---|---|
| 4 | 1.1.3 |
| 3 | 1.0.2 |
| 5 | 1.0.12 |
| 4 | 1.0.11 |
| 2 | 1.0.1 |
| 1 | 1.0.0 |
但是从用户的角度下看，大版本应该在上面
1.0.11 号版本应该在 1.0.2 号版本上面
1.0.11 号版本应该是比 1.0.2 号版本新。
原因说明
这是应为数据库把版本号当成字串串排序了，所以出现上面的结果
解决方案
所以这块的解决方案原理就是版本号拆成3位(我们的这里的版本号位数是固定3位的)
//将1.0.3 分成3个数排序
如：1.0.3 ---> 第一个数:1 第二个数:0 第三个数:3
这样的情况下我们就可以这样排序
//原来排序动作
order by version_number desc;
//拆分后现在的排序动作
order by first_number + 0 desc , second_number + 0 desc , third_number + 0 desc;
这里要注意一个 + 0 的动作，这个动作的原理就是将数据库字符串类型转换成数字类型排序
如果不转换成数字类型， 2 > 11 这种现象还是存在，那么我们如何将版本字段拆成3个字段呢，难道要在业务中做吗？
不用，这里我们要说到mysql中的substring_index()函数
set @version_number := 1.0.3
select substring_index(version_number,'.',1) //得到的结果是1
select substring_index(version_number,'.',2) //得到的结果是1.0
select stbstring_index(version_number,'.',-1) //得到的结果是3
select substring_index(substring_index(version_number,'.',2),'.',-1) //得到结果0
//所以我们的在数据库操作的时候可以这样做。表名：version 字段：id,version_number
select
id,
version_number,
substring_index(version_number,'.',1) + 0 as first_number,
substring_index(substring_index(version_number,'.',2),'.',-1) + 0 as second_number,
substring_index(version_number,'.',-1) + 0 as third_number
from version
order by first_number desc, second_number desc, third_number desc;
这样查询出来的结果如下图所见，能达到我们要的结果
| id | version_number(string) | first_number(string) | second_number(string) | third_number(string) |
|---|---|---|---|---|
| 6 | 1.1.3 | 1 | 1 | 3 |
| 5 | 1.0.12 | 1 | 0 | 12 |
| 4 | 1.0.11 | 1 | 0 | 11 |
| 3 | 1.0.2 | 1 | 0 | 2 |
| 2 | 1.0.1 | 1 | 0 | 1 |
| 1 | 1.0.0 | 1 | 0 | 0 |
这样我们就解决这个问题了。
问题思考
PS: 我们直接在存储的的时候就存储成下面的结构，就不用在查询的时候，现算这个值了，查询效率会快很多。
CREATE TABLE `version` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`version_number` varchar(255) DEFAULT NULL,
`first` int(11) DEFAULT NULL,
`second` int(11) DEFAULT NULL,
`third` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
select
id,
version_number
from version
order by first desc, second desc, third desc;
向这种设计就依赖我们在插入的时候拆解成3个字段。这种情况下，version_number添加和修改的时候，都需要保证同步修改。
Mysql查询最新版本号问题
上面我们解决了版本号排序的问题，大家版本列表可以正常倒序排列了。现在我们有一个查询当前数据中的最新版本号，这个又该怎么解决呢？
有人会说，这有啥好说的，order by 之后 limit 1 就可以了呀？
说的对，但是上面的排序方案在数据量大的情况下，会不会有性能问题呢？
数据准备
我们先准备以下数据：
//建表，上次我们用的version,这次我们就用version2表
CREATE TABLE `version2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`version_number` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
//新建一个存储过程叫idata()
delimiter ;;
create procedure idata()
begin
declare i,j,k,l int;
set i = 1;
while(i<=10000)do
select rand()*10 into j;
select rand()*10 into k;
select rand()*10 into l;
insert into version2(`version_number`) values(concat_ws('.',j,k,l));
set i=i+1;
end while;
end;;
delimiter ;
//调用存储过程，插入10000条数据，可以多调用几次，我准备了100000条数据，共调用了10次
call idata();
我们看一下我们准备的数据：
问题分析
我们先分析以下查询最新版本数据的解决方案：
- 我们在来一张表，记录当前最新的版本号newest_version_number, 在新建和修改的时候，通过其他的方式(如：最后更新时间)找到当前数据中的最新版本号，然后存储在这张表中。这样查询的时候可以直接查到最新的版本号。
- 需求中要求版本号可以随意改变，我们就不能用最后修改时间来判断谁是最新版本，当我们只有通过版本号排序才能确定最新版本号的时候，我们就需要
order by + limit 1实现（os:版本号没事改什么改，好玩吗）。
所以，接下来我们都是基于只有通过版本号排序来找到最新版本的情况下往下探索。
SELECT
id,
version_number,
SUBSTRING_INDEX(version_number, '.', 1) + 0 AS first_number,
SUBSTRING_INDEX(SUBSTRING_INDEX(version_number, '.', 2),
'.',
- 1) + 0 AS second_number,
SUBSTRING_INDEX(version_number, '.', - 1) + 0 AS third_number
FROM
version2
ORDER BY first_number DESC , second_number DESC , third_number DESC limit 1;
我们看一下执行情况(看不懂执行计划的同学，后续会详细介绍，这里不展开说明)
从上面的截图可以看出来：
- 上面的那条Sql语句扫描了100332行记录(PS：表里总共只有100000行记录，去哪扫描的100000行，这里是一个索引估算错误的问题，后续我们会写文章具体介绍，这里不展开说明)，最后只查出来一行记录
- 执行计划中看到了Sql语句使用了文件排序
- 打开Sql分析器看到，该条sql语句执行了0.05s ，而且所有的时间消耗都在重建排序索引上。
这里看好像不慢，没有优化的必要，不过这里不影响我们去分析和优化它，我们抱着极客的精神探查到底 。
问题说明
OK!看到这里我们就知道问题所在了，应为排序没有索引，导致使用了文件排序，需要重建索引。所以Sql不够快
知道问题就简单了，我们想办法让排序走上索引就行了呀，重新分析下sql语句。
SELECT
id,
version_number,
SUBSTRING_INDEX(version_number, '.', 1) + 0 AS first_number,
SUBSTRING_INDEX(SUBSTRING_INDEX(version_number, '.', 2),
'.',
- 1) + 0 AS second_number,
SUBSTRING_INDEX(version_number, '.', - 1) + 0 AS third_number
FROM
version2
ORDER BY first_number DESC , second_number DESC , third_number DESC limit 1;
排序字段有3个，而且都是现算出来了的，好像建不了索引呀
那我们把这三个字段建出来，不就可以加索引了吗，没错，但是这样我们就回到上面拍序问题介绍的那样。需要用到下面的结构：
CREATE TABLE `version` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`version_number` varchar(255) DEFAULT NULL,
`first` int(11) DEFAULT NULL,
`second` int(11) DEFAULT NULL,
`third` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
但是这样，我版本号修改的时候就需要维护这三个字段，太麻烦了。
解决方案
这种情况下，我们使用5.7的新特性，虚拟列就可以很方便的解决这个问题。（虚拟列后续会出详细介绍，这里先不展开说明）
//在version2表中新建3个虚拟列，v_first,v_second,v_third
alter table version2 add v_first int(11) generated always as (SUBSTRING_INDEX(version_number, '.', 1));
alter table version2 add v_second int(11) generated always as (SUBSTRING_INDEX(SUBSTRING_INDEX(version_number, '.', 2),'.',- 1));
alter table version2 add v_third int(11) generated always as (SUBSTRING_INDEX(version_number, '.', - 1));
//在虚拟列上创建一个联合虚拟索引
alter table `version2` add index `vdx_version_number` (`v_first` DESC, `v_second` DESC, `v_third` DESC);
ok,我们看下做完这些操作之后查询语句的表现如何。
从上面的截图我们可以看到整体的表现非常好，查询速度冲0.05s优化到了0.002s
这种情况适合老业务，业务耦合比较中，没法改表结构的情况下，可以无缝利用虚拟列切换查询，提升速度
总结：
对于版本号问题：
- 大多数业务可以直接用文中提到了查询语句，数据量小，无关联操作的情况下，效率应该还好
- 对于新上的业务，又考虑到数据量会增长到很大的情况下，可以直接分解成3个字段存储，只是在业务中多一些维护成本。
- 对于老业务，已经增长到查询很慢的情况下，可以考虑用虚拟列的方案，先提升速度，等有空了，还是建议拆成3个字段存储。
以上言论皆表示个人看法，能力有限，如果有什么不对的地方，也欢迎大家指正，如果大家有更好的方案，也欢迎大家留言分享。
3199
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
