包卜体育直播
当前位置主页 > 体育大生意 >
right

温柔的慈悲吉他谱:优化体系--详解MySQL 5.7虚拟列,值得收藏

发布时间:2019-07-30 来源:包卜体育直播
"\u003Cdiv\u003E\u003Ch1\u003E概述\u003C\u002Fh1\u003E\u003Cp\u003E我们都知道where条件如果在字段上带了函数就不会去走索引,不好优化,无意间了解到mysql一个新特性--虚拟列,专门处理这块问题的,下面一起来了解下吧~\u003C\u002Fp\u003E\u003Chr\u003E\u003Ch1\u003E\u003Cstrong\u003EGenerated Column\u003C\u002Fstrong\u003E\u003C\u002Fh1\u003E\u003Cp\u003E在MySQL 5.7中,支持两种Generated Column,即\u003Cstrong\u003EVirtual Generated Column和Stored Generated Column\u003C\u002Fstrong\u003E,前者只将Generated Column保存在数据字典中(表的元数据),并不会将这一列数据持久化到磁盘上;后者会将Generated Column持久化到磁盘上,而不是每次读取的时候计算所得。很明显,后者存放了可以通过已有数据计算而得的数据,需要更多的磁盘空间,与Virtual Column相比并没有优势,因此,MySQL 5.7中,不指定Generated Column的类型,默认是Virtual Column。\u003C\u002Fp\u003E\u003Cp\u003E如果需要Stored Generated Golumn的话,可能在Virtual Generated Column上建立索引更加合适。综上,一般情况下,都使用Virtual Generated Column,这也是MySQL默认的方式\u003C\u002Fp\u003E\u003Chr\u003E\u003Ch1\u003E\u003Cstrong\u003E语法\u003C\u002Fstrong\u003E\u003C\u002Fh1\u003E\u003Cpre\u003E<type> [ GENERATED ALWAYS ] AS ( <expression> ) [ VIRTUAL|STORED ]\u003Cbr\u003E[ UNIQUE [KEY] ] [ [PRIMARY] KEY ] [ NOT NULL ] [ COMMENT <text> ]\u003Cbr\u003E\u003C\u002Fpre\u003E\u003Chr\u003E\u003Ch1\u003E应用场景\u003C\u002Fh1\u003E\u003Cp\u003E假设有一个表,其中包含一个 date 类型的列 `SimpleDate` date\u003C\u002Fp\u003E\u003Cp\u003ESimpleDate 是一个常用的查询字段,并需要对其执行日期函数,例如\u003C\u002Fp\u003E\u003Cpre\u003ESELECT ... WHERE dayofweek(SimpleDate) = 3 ...\u003Cbr\u003E\u003C\u002Fpre\u003E\u003Cp\u003E此时的问题是 即使对 SimpleDate 建立索引,这个查询语句也无法使用,因为日期函数阻止了索引。\u003C\u002Fp\u003E\u003Cp\u003E为了提高查询效率,通常要进行额外的操作,例如新建一个字段 SimpleDate_dayofweek,存放 dayofweek(SimpleDate) 的计算结果,然后对这列创建索引,SimpleDate_dayofweek 的值需要程序写入,例如使用触发器,在 SimpleDate 有变动时更新这样查询就可以改为\u003C\u002Fp\u003E\u003Cpre\u003ESELECT ... WHERE SimpleDate_dayofweek = 3 ...\u003Cbr\u003E\u003C\u002Fpre\u003E\u003Cp\u003E这么做的好处是提高了查询性能,可以使用 SimpleDate_dayofweek 列的索引了,但又带来了其他麻烦,例如\u003C\u002Fp\u003E\u003Cul\u003E\u003Cli\u003E降低了数据写入性能\u003C\u002Fli\u003E\u003Cli\u003E增加冗余数据,占用了更多的存储空间\u003C\u002Fli\u003E\u003Cli\u003E增加代码维护成本\u003C\u002Fli\u003E\u003C\u002Ful\u003E\u003Cp\u003E\u003Cstrong\u003E虚拟列\u003C\u002Fstrong\u003E Generated Columns 就是用来解决这个问题的,可以增加一个可被索引的列,但实际上并不存在于数据表中,下面用一个实验来说明下:\u003C\u002Fp\u003E\u003Chr\u003E\u003Ch1\u003E\u003Cstrong\u003E实验\u003C\u002Fstrong\u003E\u003C\u002Fh1\u003E\u003Cp\u003E需求:为了实现对json数据中部分数据的索引查询,考虑用MySQL5.7中的虚拟列功能\u003C\u002Fp\u003E\u003Cp\u003E1、创建表\u003C\u002Fp\u003E\u003Cpre\u003Ecreate table user(uid int auto_increment,data json,primary key(uid));\u003Cbr\u003E\u003C\u002Fpre\u003E\u003Cp\u003E2、准备数据\u003C\u002Fp\u003E\u003Cpre\u003Einsert into user values (NULL,'{\"name\":\"hwb\",\"address\":\"guangzhou\"}');\u003Cbr\u003Einsert into user values (NULL,'{\"name\":\"tqy\",\"address\":\"shenzhen\"}');\u003Cbr\u003Ecommit;\u003Cbr\u003E\u003C\u002Fpre\u003E\u003Cdiv class=\"pgc-img\"\u003E\u003Cimg src=\"http:\u002F\u002Fp1.pstatp.com\u002Flarge\u002Fpgc-image\u002Ff9298c66ab524fe3a4297ec59ed9a5d0\" img_width=\"598\" img_height=\"454\" alt=\"优化体系--详解MySQL 5.7虚拟列,值得收藏\" inline=\"0\"\u003E\u003Cp class=\"pgc-img-caption\"\u003E\u003C\u002Fp\u003E\u003C\u002Fdiv\u003E\u003Cp\u003E3、构建姓名的虚拟列\u003C\u002Fp\u003E\u003Cpre\u003Ealter table user add user_name varchar(20) generated always as (data->'$.name');\u003Cbr\u003E\u003C\u002Fpre\u003E\u003Cdiv class=\"pgc-img\"\u003E\u003Cimg src=\"http:\u002F\u002Fp1.pstatp.com\u002Flarge\u002Fpgc-image\u002Ff11f1f7d8831435fabfe0ab38a2935e5\" img_width=\"665\" img_height=\"365\" alt=\"优化体系--详解MySQL 5.7虚拟列,值得收藏\" inline=\"0\"\u003E\u003Cp class=\"pgc-img-caption\"\u003E\u003C\u002Fp\u003E\u003C\u002Fdiv\u003E\u003Cp\u003E4、构建索引\u003C\u002Fp\u003E\u003Cpre\u003Ealter table user add index idx_name(user_name);\u003Cbr\u003E\u003C\u002Fpre\u003E\u003Cdiv class=\"pgc-img\"\u003E\u003Cimg src=\"http:\u002F\u002Fp1.pstatp.com\u002Flarge\u002Fpgc-image\u002F8ea1eae9d7c84d248b3278120a5116b2\" img_width=\"431\" img_height=\"62\" alt=\"优化体系--详解MySQL 5.7虚拟列,值得收藏\" inline=\"0\"\u003E\u003Cp class=\"pgc-img-caption\"\u003E\u003C\u002Fp\u003E\u003C\u002Fdiv\u003E\u003Cp\u003E5、测试是否用到索引\u003C\u002Fp\u003E\u003Cpre\u003Eexplain select * from user where user_name='\"hwb\"' \\G;\u003Cbr\u003E\u003C\u002Fpre\u003E\u003Cdiv class=\"pgc-img\"\u003E\u003Cimg src=\"http:\u002F\u002Fp3.pstatp.com\u002Flarge\u002Fpgc-image\u002F7ea5c94cbb004cb0aec281df0f6d7a59\" img_width=\"528\" img_height=\"286\" alt=\"优化体系--详解MySQL 5.7虚拟列,值得收藏\" inline=\"0\"\u003E\u003Cp class=\"pgc-img-caption\"\u003E\u003C\u002Fp\u003E\u003C\u002Fdiv\u003E\u003Cp\u003E可以看出用了索引了\u003C\u002Fp\u003E\u003Cp\u003E6、插入新数据\u003C\u002Fp\u003E\u003Cp\u003E此时的表的结构由于多出了user_name这一虚拟列,再插入别的数据要注意在表后指明插入列(不能给虚拟列插入数据)\u003C\u002Fp\u003E\u003Cpre\u003Einsert into user(uid,data) values (NULL,'{\"name\":\"test\",\"address\":\"shantou\"}');\u003Cbr\u003E\u003C\u002Fpre\u003E\u003Cdiv class=\"pgc-img\"\u003E\u003Cimg src=\"http:\u002F\u002Fp1.pstatp.com\u002Flarge\u002Fpgc-image\u002F3128c40dd8cf4c11b78550660bd3ddd4\" img_width=\"671\" img_height=\"198\" alt=\"优化体系--详解MySQL 5.7虚拟列,值得收藏\" inline=\"0\"\u003E\u003Cp class=\"pgc-img-caption\"\u003E\u003C\u002Fp\u003E\u003C\u002Fdiv\u003E\u003Chr\u003E\u003Cp\u003E做完发现这个实验好像不是那么好理解...应该对比一下加不加虚拟列有没走索引,可能会更容易让大家理解的...后面会分享更多devops和DBA方面的内容,感兴趣的朋友可以关注一下~\u003C\u002Fp\u003E\u003Cdiv class=\"pgc-img\"\u003E\u003Cimg src=\"http:\u002F\u002Fp1.pstatp.com\u002Flarge\u002Fpgc-image\u002Fe1968b12a3674777bb2bb909415c52fe\" img_width=\"360\" img_height=\"354\" alt=\"优化体系--详解MySQL 5.7虚拟列,值得收藏\" inline=\"0\"\u003E\u003Cp class=\"pgc-img-caption\"\u003E\u003C\u002Fp\u003E\u003C\u002Fdiv\u003E\u003C\u002Fdiv\u003E"'.slice(6, -6),
包卜体育直播
推荐图文
包卜体育直播 沙龙体讯 谍报 体育大生意 时报体育 5U体育 东方体育日报 新闻晨报体育 广东体育频道

9026体育直播网,球皇体育直播,cspn山东体育直播