栏目分类:
子分类:
返回
名师互学网用户登录
快速导航关闭
当前搜索
当前分类
子分类
实用工具
热门搜索
名师互学网 > IT > 软件开发 > 后端开发 > Java

MySQL单表插入&查询性能测试

Java 更新时间: 发布时间: IT归档 最新发布 模块sitemap 名妆网 法律咨询 聚返吧 英语巴士网 伯小乐 网商动力

MySQL单表插入&查询性能测试

测试SQL(52个字段)
CREATE DEFINER=`root`@`%` PROCEDURE `generateData`()
BEGIN
    DROp TABLE
    IF
        EXISTS USER;
    CREATE TABLE `user` (
        `id` INT ( 11 ) AUTO_INCREMENT,
        `username` VARCHAr ( 255 ),
        `addr1` VARCHAr ( 255 ),
        `addr2` VARCHAr ( 255 ),
        `addr3` VARCHAr ( 255 ),
        `addr4` VARCHAr ( 255 ),
        `addr5` VARCHAr ( 255 ),
        `addr6` VARCHAr ( 255 ),
        `addr7` VARCHAr ( 255 ),
        `addr8` VARCHAr ( 255 ),
        `addr9` VARCHAr ( 255 ),
        `addr10` VARCHAr ( 255 ),
        `addr11` VARCHAr ( 255 ),
        `addr12` VARCHAr ( 255 ),
        `addr13` VARCHAr ( 255 ),
        `addr14` VARCHAr ( 255 ),
        `addr15` VARCHAr ( 255 ),
        `addr16` VARCHAr ( 255 ),
        `addr17` VARCHAr ( 255 ),
        `addr18` VARCHAr ( 255 ),
        `addr19` VARCHAr ( 255 ),
        `addr20` VARCHAr ( 255 ),
        `addr21` VARCHAr ( 255 ),
        `addr22` VARCHAr ( 255 ),
        `addr23` VARCHAr ( 255 ),
        `addr24` VARCHAr ( 255 ),
        `addr25` VARCHAr ( 255 ),
        `addr26` VARCHAr ( 255 ),
        `addr27` VARCHAr ( 255 ),
        `addr28` VARCHAr ( 255 ),
        `addr29` VARCHAr ( 255 ),
        `addr30` VARCHAr ( 255 ),
        `addr31` VARCHAr ( 255 ),
        `addr32` VARCHAr ( 255 ),
        `addr33` VARCHAr ( 255 ),
        `addr34` VARCHAr ( 255 ),
        `addr35` VARCHAr ( 255 ),
        `addr36` VARCHAr ( 255 ),
        `addr37` VARCHAr ( 255 ),
        `addr38` VARCHAr ( 255 ),
        `addr39` VARCHAr ( 255 ),
        `addr40` VARCHAr ( 255 ),
        `addr41` VARCHAr ( 255 ),
        `addr42` VARCHAr ( 255 ),
        `addr43` VARCHAr ( 255 ),
        `addr44` VARCHAr ( 255 ),
        `addr45` VARCHAr ( 255 ),
        `addr46` VARCHAr ( 255 ),
        `addr47` VARCHAr ( 255 ),
        `addr48` VARCHAr ( 255 ),
        `addr49` VARCHAr ( 255 ),
        `addr50` VARCHAr ( 255 ),
    PRIMARY KEY ( `id` )) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;
    
    SET @n = 1;
    WHILE
            @n <= 2000000 DO
            INSERT INTO `user`
        VALUES
            (    NULL,
                CONCAt( 'user', @n ),
                CONCAt( 'user', @n, '_', 'addr', 1 ),
                CONCAt( 'user', @n, '_', 'addr', 2 ),
                CONCAt( 'user', @n, '_', 'addr', 3 ),
                CONCAt( 'user', @n, '_', 'addr', 4 ),
                CONCAt( 'user', @n, '_', 'addr', 5 ),
                CONCAt( 'user', @n, '_', 'addr', 6 ),
                CONCAt( 'user', @n, '_', 'addr', 7 ),
                CONCAt( 'user', @n, '_', 'addr', 8 ),
                CONCAt( 'user', @n, '_', 'addr', 9 ),
                CONCAt( 'user', @n, '_', 'addr', 10 ),
                CONCAt( 'user', @n, '_', 'addr', 11 ),
                CONCAt( 'user', @n, '_', 'addr', 12 ),
                CONCAt( 'user', @n, '_', 'addr', 13 ),
                CONCAt( 'user', @n, '_', 'addr', 14 ),
                CONCAt( 'user', @n, '_', 'addr', 15 ),
                CONCAt( 'user', @n, '_', 'addr', 16 ),
                CONCAt( 'user', @n, '_', 'addr', 17 ),
                CONCAt( 'user', @n, '_', 'addr', 18 ),
                CONCAt( 'user', @n, '_', 'addr', 19 ),
                CONCAt( 'user', @n, '_', 'addr', 20 ),
                CONCAt( 'user', @n, '_', 'addr', 21 ),
                CONCAt( 'user', @n, '_', 'addr', 22 ),
                CONCAt( 'user', @n, '_', 'addr', 23 ),
                CONCAt( 'user', @n, '_', 'addr', 24 ),
                CONCAt( 'user', @n, '_', 'addr', 25 ),
                CONCAt( 'user', @n, '_', 'addr', 26 ),
                CONCAt( 'user', @n, '_', 'addr', 27 ),
                CONCAt( 'user', @n, '_', 'addr', 28 ),
                CONCAt( 'user', @n, '_', 'addr', 29 ),
                CONCAt( 'user', @n, '_', 'addr', 30 ),
                CONCAt( 'user', @n, '_', 'addr', 31 ),
                CONCAt( 'user', @n, '_', 'addr', 32 ),
                CONCAt( 'user', @n, '_', 'addr', 33 ),
                CONCAt( 'user', @n, '_', 'addr', 34 ),
                CONCAt( 'user', @n, '_', 'addr', 35 ),
                CONCAt( 'user', @n, '_', 'addr', 36 ),
                CONCAt( 'user', @n, '_', 'addr', 37 ),
                CONCAt( 'user', @n, '_', 'addr', 38 ),
                CONCAt( 'user', @n, '_', 'addr', 39 ),
                CONCAt( 'user', @n, '_', 'addr', 40 ),
                CONCAt( 'user', @n, '_', 'addr', 41 ),
                CONCAt( 'user', @n, '_', 'addr', 42 ),
                CONCAt( 'user', @n, '_', 'addr', 43 ),
                CONCAt( 'user', @n, '_', 'addr', 44 ),
                CONCAt( 'user', @n, '_', 'addr', 45 ),
                CONCAt( 'user', @n, '_', 'addr', 46 ),
                CONCAt( 'user', @n, '_', 'addr', 47 ),
                CONCAt( 'user', @n, '_', 'addr', 48 ),
                CONCAt( 'user', @n, '_', 'addr', 49 ),
                CONCAt( 'user', @n, '_', 'addr', 50 )
            );
            SET @n = @n + 1;
    END WHILE;
END
插入执行耗时

插入1万条数据:6.5s
插入10万条数据:65s
插入50万条数据:334s
插入100万条数据:669s
插入200万条数据:1389s

查询执行耗时(200万条数据)

查数据总量:

查询SQL:SELECT COUNT(*) FROM user
耗时:2.7s

查所有字段,查不同偏移量,1000条数据:

查询SQL:SELECt * FROM user LIMIT 1000
耗时:0.03s

查询SQL:SELECt * FROM user LIMIT 500000,1000
耗时:1.6s

查询SQL:SELECt * FROM user LIMIT 1000000,1000
耗时:3s

查询SQL:SELECt * FROM user LIMIT 1500000,1000
耗时:4.5s

查两个字段,查不同偏移量,1000条数据:

查询SQL:SELECt addr1, addr2 FROM user LIMIT 500000,1000
耗时:0.8s

查询SQL:SELECt addr1, addr2 FROM user LIMIT 1000000,1000
耗时:1.5s

查询SQL:SELECt addr1, addr2 FROM user LIMIT 1500000,1000
耗时:2.1s

查所有字段,查同一偏移量,不同条数据:

查询SQL:SELECt * FROM user LIMIT 1000000,1000
耗时:3s

查询SQL:SELECt * FROM user LIMIT 1000000,2000
耗时:3s

查询SQL:SELECt * FROM user LIMIT 1000000,3000
耗时:3s

查询SQL:SELECt * FROM user LIMIT 1000000,10000
耗时:3s

查询SQL:SELECt * FROM user LIMIT 1000000,100000
耗时:3.8s

测试索引

查所有字段,对 username 列使用 Like 查第1000000到1000999共1000个用户的数据:

查询SQL:
SELECt * FROM user
WHERe username LIKE CONCAt(‘user1000’,‘%’) AND LENGTH(username) = 11
耗时:6.2s

查询所有字段,对 username 列使用 = 号查第1000000个用户的数据:

查询SQL:
SELECt * FROM user
WHERe username = ‘user1000000’
耗时:6s

对 username 列创建索引:

SQL:CREATE INDEX idx_username ON user(username)
耗时:8.5s

创建索引后,对 username 列使用 Like 查第1000000到1000999共1000个用户的数据:

查询SQL:
SELECt * FROM user
WHERe username LIKE CONCAt(‘user1000’,‘%’) AND LENGTH(username) = 11
耗时:0.031s

创建索引后,对 username 列使用 = 号查第1000000个用户的数据:

查询SQL:
SELECt * FROM user
WHERe username = ‘user1000000’
耗时:0.025s

数据存储占用

插入200万条数据后,数据长度:1.86 GB(Navicat中右侧可以看到) ,所占用磁盘空间:2G(user.ibd 文件的大小)

创建索引后,索引长度:48.6MB(Navicat中右侧可以看到)

查询时,mysqld 内存占用并不会增加,只有 Navicat 内存会增加

总结

在查询时,待查询的数据的字段数,偏移量,条数都会影响查询时间。
建立索引后,能够大大减少查询时间。

补充

MySQL本身并没有对单表最大记录数进行限制。

阿里巴巴《Java 开发手册》提出单表行数超过 500 万行或者单表容量超过 2GB,才推荐进行分库分表。

MySql数据库一张表中能存储的最大数据量和实际记录的条数无关,而与 MySQL 的配置以及机器的硬件有关。

MySQL 为了提高性能,会将表的索引装载到内存中。InnoDB buffer size 足够的情况下,其能完成全加载进内存,查询不会有问题。

但是,当单表数据库到达某个量级的上限时,导致内存无法存储其索引,使得之后的 SQL 查询会产生磁盘 IO,从而导致性能下降。当然,这个还有具体的表结构的设计有关,最终导致的问题都是内存限制。

转载请注明:文章转载自 www.mshxw.com
本文地址:https://www.mshxw.com/it/949008.html
我们一直用心在做
关于我们 文章归档 网站地图 联系我们

版权所有 (c)2021-2022 MSHXW.COM

ICP备案号:晋ICP备2021003244-6号