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;

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

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

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

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分析一下：

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

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

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

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

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

查看执行结果：

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

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

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

耗时：0.001s。

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

用EXPLAIN分析一下：

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

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

SELECT id, username, email, PASSWORD FROM test_user WHERE `