mysql数据量对索引的影响_MySQL 千万 级数据量根据(索引)优化 查询 速度

DROP TABLE IF EXISTS `test_user`;

CREATE TABLE `test_user` (

`id` bigint(20) PRIMARY key not null AUTO_INCREMENT,

`username` varchar(50) DEFAULT NULL,

`email` varchar(30) DEFAULT NULL,

`password` varchar(32) DEFAULT NULL,

`status` tinyint(1) NULL DEFAULT 0

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

cfcbd7c5ce62635a3badcdb04838be96.gif

存储引擎使用MyISAM是因为此引擎没有事务,插入速度极快,方便我们快速插入千万条测试数据,等我们插完数据,再把存储类型修改为InnoDB。

2).  使用存储过程插入1千万条数据

cfcbd7c5ce62635a3badcdb04838be96.gif

create procedure myproc()

begin

declare num int;

set num=1;

while num <= 10000000 do

insert into test_user(username,email,password) values(CONCAT(‘username_‘,num), CONCAT(num ,‘@qq.com‘), MD5(num));

set num=num+1;

end while;

end

cfcbd7c5ce62635a3badcdb04838be96.gif

3).  执行  call myproc();

由于使用的MyISAM引擎,插入1千万条数据,仅耗时246秒,若是InnoDB引擎,插入100万条数据就要花费数小时了。

MyISAM引擎之所以如此之快,一个原因是使用了三个文件来存储数据,frm后缀存储表结构、MYD存储真实数据、MYI存储索引数据。

每次进行插入时,MYD的内容是递增插入,MYI是一个B+树结构,每次的索引变更需要重新组织数据。

但相对于InnoDB来说,MyISAM更快。

4). sql测试

1. SELECT id,username,email,password FROM test_user WHERE id=999999

耗时:0.114s。

因为我们建表的时候,将id设成了主键,所以执行此sql的时候,走了主键索引,查询速度才会如此之快。

2. 我们再执行: SELECT id,username,email,password FROM test_user WHERE username=‘username_9000000‘

耗时:4.613s。

用EXPLAIN分析一下:

20190713201153670051.png

信息显示进行了全表扫描。

3. 那我们给username列加上普通索引。

ALTER TABLE `test_user` ADD INDEX index_name(username) ;

此时,Mysql开始对test_user表建立索引,查看mysql 数据目录:

20190713201154144678.png

20190713201154174953.png

查看目录文件列表,可以看到新建了三个临时文件,新的临时数据表MYD文件大小并未变更,临时索引文件MYI文件大小增加了很多。

查看执行结果:

20190713201154223783.png

此过程大约耗时 221.792s,建索引的过程会全表扫描,逐条建索引,当然慢了。

等执行完毕后,mysql把旧的数据库文件删除,再用新建立的临时文件替换掉之。(删除索引过程也是同样的步骤)。

4. 再来执行:select id,username,email,password from test_user where username=‘username_9000000‘

耗时:0.001s。

可见查询耗时提高的很可观。

用EXPLAIN分析一下:

20190713201154259917.png

Extra 字段告诉我们使用到了索引 index_name,和之前的EXPLAIN结果对比,未建立索引前进行了全部扫描,建立索引后使用到了索引,查询耗时对比明显。

5. 再用username和password来联合查询

SELECT id, username, email, PASSWORD FROM test_user WHERE &#

  • 0
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值