mysql虚拟列(Generated Columns)及JSON字段类型的使用

今天药忘吃喽~ 2022-12-17 08:38 139阅读 0赞

mysql 5.7中有很多新的特性，但平时可能很少用到，这里列举2个实用的功能：虚拟列及json字段类型

一、先创建一个测试表：

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> <p>4</p> <p>5</p> <p>6</p> <p>7</p> <p>8</p> <p>9</p> </td> 
   <td> <p><code>drop</code>&nbsp;<code>table</code>&nbsp;&nbsp;<code>if exists t_people;</code></p> <p>&nbsp;</p> <p><code>CREATE</code>&nbsp;<code>TABLE</code>&nbsp;<code>t_people(</code></p> <p><code>&nbsp;&nbsp;</code><code>`id`&nbsp;</code><code>INT</code><code>(11)&nbsp;</code><code>NOT</code>&nbsp;<code>NULL</code>&nbsp;<code>AUTO_INCREMENT,</code></p> <p><code>&nbsp;&nbsp;</code><code>`</code><code>name</code><code>`&nbsp;</code><code>varchar</code><code>(50)&nbsp;</code><code>NOT</code>&nbsp;<code>NULL</code>&nbsp;<code>DEFAULT</code>&nbsp;<code>''</code><code>,</code></p> <p><code>&nbsp;&nbsp;</code><code>`profile` json&nbsp;</code><code>not</code>&nbsp;<code>null</code>&nbsp;<code>,</code></p> <p><code>&nbsp;&nbsp;</code><code>`created_at`&nbsp;</code><code>TIMESTAMP</code><code>(3)&nbsp;</code><code>DEFAULT</code>&nbsp;<code>CURRENT_TIMESTAMP</code><code>(3)&nbsp;</code><code>ON</code>&nbsp;<code>UPDATE</code>&nbsp;<code>CURRENT_TIMESTAMP</code><code>(3),</code></p> <p><code>&nbsp;&nbsp;</code><code>`updated_at`&nbsp;</code><code>TIMESTAMP</code><code>(3)&nbsp;</code><code>DEFAULT</code>&nbsp;<code>CURRENT_TIMESTAMP</code><code>(3)&nbsp;</code><code>ON</code>&nbsp;<code>UPDATE</code>&nbsp;<code>CURRENT_TIMESTAMP</code><code>(3),</code></p> <p><code>&nbsp;&nbsp;</code><code>PRIMARY</code>&nbsp;<code>KEY</code>&nbsp;<code>(id));</code></p> </td> 
  </tr> 
 </tbody> 
</table>

注：这里profile是一个json类型的字段，另db编码采用[utf8mb4][]

二、生成测试数据

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> <p>4</p> <p>5</p> <p>6</p> <p>7</p> <p>8</p> <p>9</p> <p>10</p> <p>11</p> <p>12</p> <p>13</p> <p>14</p> <p>15</p> <p>16</p> </td> 
   <td> <p><code>delimiter //</code></p> <p>&nbsp;</p> <p><code>-- 写一段存储过程，方便后面生成测试数据</code></p> <p><code>create</code>&nbsp;<code>procedure</code>&nbsp;<code>batchInsert()</code></p> <p><code>&nbsp;</code><code>begin</code></p> <p><code>&nbsp;&nbsp;</code><code>declare</code>&nbsp;<code>i&nbsp;</code><code>int</code><code>;</code></p> <p><code>&nbsp;&nbsp;</code><code>declare</code>&nbsp;<code>v_name&nbsp;</code><code>varchar</code><code>(50);</code></p> <p><code>&nbsp;&nbsp;</code><code>declare</code>&nbsp;<code>v_profile&nbsp;</code><code>varchar</code><code>(100);</code></p> <p><code>&nbsp;&nbsp;</code><code>set</code>&nbsp;<code>i=0;</code></p> <p><code>&nbsp;&nbsp;</code><code>while i&lt;100000 do</code></p> <p><code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>set</code>&nbsp;<code>v_name = concat(</code><code>substring</code><code>(</code><code>'赵钱孙李周吴郑王张杨'</code><code>,floor(1+(rand()*10)),1),</code><code>substring</code><code>(</code><code>'菩提树下的杨过'</code><code>,floor(1+(rand()*7)),1),</code><code>substring</code><code>(</code><code>'我爱北京天安门'</code><code>,floor(1+(rand()*7)),1),i);</code></p> <p><code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>set</code>&nbsp;<code>v_profile&nbsp; = concat(</code><code>"{\"phone\":\""</code><code>,concat(</code><code>'13'</code><code>,floor(1+(rand()*9)),floor(1+(rand()*9)),floor(1+(rand()*9)),floor(1+(rand()*9)),floor(1+(rand()*9)),floor(1+(rand()*9)),floor(1+(rand()*9)),floor(1+(rand()*9)),floor(1+(rand()*9))) ,&nbsp;</code><code>"\",\"age\":"</code><code>,i,</code><code>"}"</code><code>);</code></p> <p><code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>insert</code>&nbsp;<code>into</code>&nbsp;<code>t_people(`</code><code>name</code><code>`,profile)&nbsp;</code><code>values</code><code>(v_name,v_profile);</code></p> <p><code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>set</code>&nbsp;<code>i=i+1;</code></p> <p><code>&nbsp;&nbsp;</code><code>end</code>&nbsp;<code>while;</code></p> <p><code>&nbsp;</code><code>end</code><code>; //</code></p> </td> 
  </tr> 
 </tbody> 
</table>

注：这段存储过程不是本文重点，看不懂的同学不用深研，大概意思就是name随机生成，profile随机生成一个类似\{"phone":"13xxxxxx","age":x\}的内容。

调用一下这个存储过程，生成100000条测试数据，数据大致长下面这样：

![点击查看原图][e9aa00a57f23ac72eaff11ba9f6da4a0.png]

需求来了，假如我们要查姓“张”的人有多少个？

![点击查看原图][52fcbf0795e60ccc1b9e141d2eb74d87.png]

这显然是一个全表扫描！

三、前缀索引

肯定有同学想到了，在name上建一个前缀索引，只对name的第1个字做索引

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>alter</code>&nbsp;<code>table</code>&nbsp;<code>t_people&nbsp;</code><code>add</code>&nbsp;<code>key</code>&nbsp;<code>ix_name(</code><code>name</code><code>(1));</code></p> </td> 
  </tr> 
 </tbody> 
</table>

确实是个好办法，效果也不错

![点击查看原图][d3821fdda020ba37699e843192b87310.png]

但是需求总是变化的，如果想查第2个字是“杨”的人有多少？

![点击查看原图][1f74c0c6ed29eaa1c142e8ed7cdb2a86.png]

依然会全表扫描。

四、虚拟列

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>alter</code>&nbsp;<code>table</code>&nbsp;<code>t_people&nbsp;</code><code>add</code>&nbsp;<code>second_name&nbsp;</code><code>varchar</code><code>(3) generated always&nbsp;</code><code>as</code><code>(</code><code>substring</code><code>(</code><code>name</code><code>,2,1)) stored;</code></p> </td> 
  </tr> 
 </tbody> 
</table>

创建了一个虚拟列second\_name，其值是substring(name,2,1)，即name中的第2个字，最后的stored表示，数据写入时这个列的值就会计算（详情可参考最后的参考链接）

注：虚拟列并不是真正的列，insert时也无法指定字段值。

然后在这个列上创建索引：

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>alter</code>&nbsp;<code>table</code>&nbsp;<code>t_people&nbsp;</code><code>add</code>&nbsp;<code>index</code>&nbsp;<code>ix_second_name(`second_name`);</code></p> </td> 
  </tr> 
 </tbody> 
</table>

再来看下执行计划，索引生效了，扫描行数也明显下降。

![点击查看原图][019897fc19b223db0b39d0d9aabb0c83.png]

当然，sql语句也可以改成：

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>explain&nbsp;</code><code>select</code>&nbsp;<code>count</code><code>(0)&nbsp;</code><code>from</code>&nbsp;<code>t_people&nbsp;</code><code>where</code>&nbsp;<code>second_name=</code><code>'杨'</code><code>;</code></p> </td> 
  </tr> 
 </tbody> 
</table>

这样看上去更直观，效果不变。

五、json检索

又来新需求了：要查profile中手机号为13589135467，并且姓“吴”的人

![点击查看原图][c3a5ab9c79da9d1dda6b477f5bbb4936.png]

注意：profile->"$.phone"=xxx 就是json字段的检索语法

![点击查看原图][a7e4ac4497aea2d8f1a65dbd2bbb1468.png]

分析执行计划，可以看到前缀索引“ix\_name”生效了，但还有优化空间，仍然可以借助虚拟列，创建2个虚拟列phone、first\_name，并创建联合索引。

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> </td> 
   <td> <p><code>alter</code>&nbsp;<code>table</code>&nbsp;<code>t_people&nbsp;</code><code>add</code>&nbsp;<code>first_name&nbsp;</code><code>varchar</code><code>(3) generated always&nbsp;</code><code>as</code><code>(</code><code>substring</code><code>(</code><code>name</code><code>,1,1)) stored;</code></p> <p><code>alter</code>&nbsp;<code>table</code>&nbsp;<code>t_people&nbsp;</code><code>add</code>&nbsp;<code>phone&nbsp;</code><code>varchar</code><code>(20) generated always&nbsp;</code><code>as</code><code>(profile-&gt;</code><code>"$.phone"</code><code>) stored;</code></p> <p><code>alter</code>&nbsp;<code>table</code>&nbsp;<code>t_people&nbsp;</code><code>add</code>&nbsp;<code>index</code>&nbsp;<code>ix_phone_firstname(phone,first_name);</code></p> </td> 
  </tr> 
 </tbody> 
</table>

加了这2个虚拟列后，数据长这样：

![点击查看原图][d790e4b710da2332200831c39851d28f.png]

注：phone列提取出来后，前后会带上引号。

刚才的需求，可以改写sql:

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>select</code>&nbsp;<code>*&nbsp;</code><code>from</code>&nbsp;<code>t_people&nbsp;</code><code>where</code>&nbsp;<code>phone=</code><code>'\"13589135467\"'</code>&nbsp;<code>and</code>&nbsp;<code>name</code>&nbsp;<code>like</code>&nbsp;<code>'吴%'</code><code>;</code></p> </td> 
  </tr> 
 </tbody> 
</table>

最后看下执行计划：

![点击查看原图][ca93feebe20f92c9258f58da6acefbdb.png]

扫描行数下降到个位数，效果十分明显。

参考文章：

[http://mysqlserverteam.com/generated-columns-in-mysql-5-7-5/][http_mysqlserverteam.com_generated-columns-in-mysql-5-7-5]

[https://dev.mysql.com/doc/refman/5.7/en/json.html][https_dev.mysql.com_doc_refman_5.7_en_json.html]

[utf8mb4]: https://www.cnblogs.com/yjmyzz/p/mysql-utf8mb4-jdbc.html
[e9aa00a57f23ac72eaff11ba9f6da4a0.png]: /images/20221122/4c878d3693fb46a4bd3593791d399955.png
[52fcbf0795e60ccc1b9e141d2eb74d87.png]: /images/20221122/0e43ebbbc6364412807ba6efa950c782.png
[d3821fdda020ba37699e843192b87310.png]: /images/20221122/cfb1d5a7fa7f4c59be7ced57c23af2ba.png
[1f74c0c6ed29eaa1c142e8ed7cdb2a86.png]: /images/20221122/2841d546f2f14af0a1f2caacb12bcd6f.png
[019897fc19b223db0b39d0d9aabb0c83.png]: /images/20221122/6501ee0255ef4ef680f35e98d6f11866.png
[c3a5ab9c79da9d1dda6b477f5bbb4936.png]: /images/20221122/222f9c8ee6c24dffae63c86693a79777.png
[a7e4ac4497aea2d8f1a65dbd2bbb1468.png]: /images/20221122/7072c5dcc7184b5ba5f53fae83dba85a.png
[d790e4b710da2332200831c39851d28f.png]: /images/20221122/61181f34a22f458a9e99e54fbc65cb59.png
[ca93feebe20f92c9258f58da6acefbdb.png]: /images/20221122/409700f7a1d94289be39e0f936cf97b8.png
[http_mysqlserverteam.com_generated-columns-in-mysql-5-7-5]: http://mysqlserverteam.com/generated-columns-in-mysql-5-7-5/
[https_dev.mysql.com_doc_refman_5.7_en_json.html]: https://dev.mysql.com/doc/refman/5.7/en/json.html