MySQL调优之数据类型选择原则

我会带着你远行 2024-04-18 20:30 54阅读 0赞

> 本文涉及：高可用数据库设计时数据类型的选择原则

在进行数据库设计时，如果能够选择最恰当的数据类型就可以为后期的数据库调优打好最坚实的基础

**选择数据类型的原则**

**更小的通常更好**

例如存储订单状态字段很多时候使用0、1、2表示使用tinyint类型存储就够了，没必要搞个int、long甚至varchar来。

越小的数据类型代表着占用越小的磁盘、内存和CPU，也就代表着越快的速度。当然，不要为了追求小而不考虑实际的场景、通常选择一个不会超过范围的最小数据类型就好了

**越简单越好**

数据类型越简单操作所需要的CPU周期就越少，例如说ip地址通常使用int而不是varchar来存储

**尽量避免NULL**

包含NULL的列会使得索引、索引统计和值比较都变得复杂

**各个数据类型占用的字节**

<table> 
 <tbody> 
  <tr> 
   <td>数据类型</td> 
   <td>所占字节（byte）</td> 
  </tr> 
  <tr> 
   <td>tinyint</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>smallint</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>medumint<br></td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td colspan="1">int</td> 
   <td colspan="1">4</td> 
  </tr> 
  <tr> 
   <td>bigint‍</td> 
   <td><p>8</p></td> 
  </tr> 
  <tr> 
   <td colspan="1">float</td> 
   <td colspan="1">4</td> 
  </tr> 
  <tr> 
   <td colspan="1">double</td> 
   <td colspan="1">8</td> 
  </tr> 
  <tr> 
   <td colspan="1">decimal(m,d）</td> 
   <td colspan="1">m+2</td> 
  </tr> 
  <tr> 
   <td colspan="1">date</td> 
   <td colspan="1">3</td> 
  </tr> 
  <tr> 
   <td colspan="1">datetime</td> 
   <td colspan="1">8</td> 
  </tr> 
  <tr> 
   <td colspan="1">timestamp</td> 
   <td colspan="1">4</td> 
  </tr> 
  <tr> 
   <td colspan="1">char（n）</td> 
   <td colspan="1">1-n-255</td> 
  </tr> 
  <tr> 
   <td colspan="1">varchar（n）</td> 
   <td colspan="1">1-n-65532‍‍</td> 
  </tr> 
  <tr> 
   <td colspan="1">tinyblob</td> 
   <td colspan="1">255</td> 
  </tr> 
  <tr> 
   <td colspan="1">blob</td> 
   <td colspan="1">65535</td> 
  </tr> 
  <tr> 
   <td colspan="1">medumblob</td> 
   <td colspan="1">16777215&nbsp;</td> 
  </tr> 
  <tr> 
   <td colspan="1">longblob</td> 
   <td colspan="1">4294967295</td> 
  </tr> 
  <tr> 
   <td colspan="1">tinytext</td> 
   <td colspan="1">255</td> 
  </tr> 
  <tr> 
   <td colspan="1">text</td> 
   <td colspan="1">65535</td> 
  </tr> 
  <tr> 
   <td colspan="1">medumtext</td> 
   <td colspan="1">16777215&nbsp;</td> 
  </tr> 
  <tr> 
   <td colspan="1">longtext</td> 
   <td colspan="1">4294967295</td> 
  </tr> 
 </tbody> 
</table>

![640?wx\_fmt=gif][640_wx_fmt_gif]

不得不看

1.[SpringCloud系列博客汇总][SpringCloud]

2.[为啥一线大厂面试必问Redis，有啥好问的？][Redis]

3.[Java多线程面试必备基础知识汇总][Java]

4.[Java集合源码分析汇总][Java 1]

5.[Linux常用命令汇总][Linux]

6.[JVM系列文章汇总][JVM]

![640?wx\_fmt=jpeg][640_wx_fmt_jpeg]

万水千山总是情，点个 “在看” 行不行！！！

![640?wx\_fmt=png][640_wx_fmt_png]

[640_wx_fmt_gif]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/abd88b5fe40d4efc9ad11f9ac7c32691.gif
[SpringCloud]: http://mp.weixin.qq.com/s?__biz=MzU5MDgzOTYzMw==&mid=2247484030&idx=1&sn=7ad29239aca876e29155f5b1439281bb&chksm=fe396840c94ee156adfcd44c63a3b5c1cf93805cf592ad7d45bc5222c349ba6f552b553a5c09&scene=21#wechat_redirect
[Redis]: http://mp.weixin.qq.com/s?__biz=MzU5MDgzOTYzMw==&mid=2247484172&idx=1&sn=cad78126f0367dbd67e0336b0b369b5d&chksm=fe396932c94ee0248ce1527844d9664a01e29a43b11b1910aa415facdc569c82a63cb07e44cf&scene=21#wechat_redirect
[Java]: http://mp.weixin.qq.com/s?__biz=MzU5MDgzOTYzMw==&mid=2247484030&idx=2&sn=ddf120fcbfc45238e64662fd183156de&chksm=fe396840c94ee156a80b744033ac34d67e331545193d2f9d4f2943995aa4412a53fec023d599&scene=21#wechat_redirect
[Java 1]: http://mp.weixin.qq.com/s?__biz=MzU5MDgzOTYzMw==&mid=2247484098&idx=1&sn=ba605ae1b35d6f2a8caef5901f0b86f6&chksm=fe3968fcc94ee1ea2493ce7faaab40316fa3954effa1613165b47955f5c097ef96c400b1485b&scene=21#wechat_redirect
[Linux]: http://mp.weixin.qq.com/s?__biz=MzU5MDgzOTYzMw==&mid=2247484117&idx=1&sn=b4c6eb9b4027390767e92efad018a08e&chksm=fe3968ebc94ee1fdf40b5192f574b0e7cdc176663411c7f9f44c814a71a14139c510b0c35bf2&scene=21#wechat_redirect
[JVM]: http://mp.weixin.qq.com/s?__biz=MzU5MDgzOTYzMw==&mid=2247484238&idx=1&sn=a30cd56a84f93da11bf916591612ef8d&chksm=fe396970c94ee066ef23c65375f88dd6ff727fd1c03c60ba61d1a9072a879cbb54084f779107&scene=21#wechat_redirect
[640_wx_fmt_jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/79406f39d48f425eadfb19ee5f0d3e22.png
[640_wx_fmt_png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/ff47d482b80b4671ab04211fa2eaf615.png