大量数据下不同数据结构查询效率的差异及原因猜测

欢迎指正，转载注明出处。大胡子_java

测试0 批量插入和逐条插入

批量插入10万条数据大约是1.162秒，逐条插入大约是86秒

测试1 自增id和uuid

表A结构如下

表B结构如下

• 表A主键为自增长整数，parentid为十位重复数字。
• 表B的uuid和parentid由mysqluuid()函数获得。
• 两个表的name字段值相同，nowtime值相同。

累计插入数据150W条。

以下为两个表count(*)

查询5次

表b查询时间为0.829s 0.863s 0.868s 0.835s 0.821s

表a查询时间为0.384s 0.383s 0.372s 0.368s 0.366s

结果 表b的平均查询时间约为表a的两倍

结果猜测1

数据库的数据以聚合的形式存储在磁盘块中，内存读取数据后在内存中的操作占整体时间的比例微乎其微，因此主要时间为读取磁盘数据的时间。磁盘块是IO的最小单位，由于表b的单条数据量大于表a，所以整体时间大于表a。

测试2 留空余空间和刚好占满

uuid的实际长度是32+4个分隔符=36，所以实际varchar(36)也足以存放数据，这里将uuid表的字段长度修改为36，别名为c

查询五次
分别时间为 0.469s 0.472s 0.464s 0.466s

结果猜测2

理论上来讲，varchar36和varchar40占据的空间应该是一样的，字符长度标志+实际字符串长度。
但是这里有较大出入，我尝试将varchar改为37，查询时间和36没有太大差距。
将varchar改为50，和varchar40没有太大差别，甚至会快0.01-0.03秒。
暂时没有理论依据支持。

测试3 varchar改为char

将表c的varcha改为char，插入一百五十万条数据，表结构如下，设为表d

查询5次
结果分别为
0.464s 0.460s 0.459s 0.464s 0.459s

结果猜测3

字符长度位对整体影响并不大。

测试4 int设置长度

由于id表数字最大长度是7位，设置int长度8位，查询五次，结果如下。
0.348s 0.345s 0.349s 0.345s 0.343s

相比测试1而言确实有约10%的提升。

结果猜测4

缩减int长度确实可以优化查询

总结

1、数据查询主要取决于磁盘块IO的次数，浪费空间造成磁盘块占据多IO次数多，查询速度慢，设计表时最好不要浪费空间。

2、由于数据库的优化，在数据长度普遍相同的情况下（都是30的长度，而不是大多数只有5，少部分30这种情况）varchar和char并没有太大区别