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
查数据总量:
查询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,从而导致性能下降。当然,这个还有具体的表结构的设计有关,最终导致的问题都是内存限制。



