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 0rolton乐廷怎么使用
) 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‘),
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_9000
000‘
耗时：0.001s。
可见查询耗时提⾼的很可观。
⽤EXPLAIN分析⼀下：
Extra 字段告诉我们使⽤到了索引 index_name，和之前的EXPLAIN结果对⽐，未建⽴索引前进⾏了全部扫描，建⽴索引后使⽤到了索引，查询耗时对⽐明显。
5. 再⽤username和password来联合查询
SELECT id, username, email, PASSWORD FROM test_user WHERE `password` =
‘7ece221bf3f5dbddbe3c2770ac19b419‘ AND username = ‘username_9000000‘;
耗时：0.001s
执⾏ EXPLAIN ：
显⽰使⽤到了 index_name 索引，条件语句不分password、useranme先后顺序，结果都是⼀样。说明sql优化器优先⽤索引命中。
6. 我们再执⾏：SELECT id, username, email, PASSWORD FROM test_user WHERE `password` =
‘7ece221bf3f5dbddbe3c2770ac19b419‘ OR username = ‘username_900000‘
此时虽然我们已经对 username 加了索引，但是password列未加索引，索引执⾏password筛选的时候，还是会全表扫描，因此此时查询速度⽴马降了下来。
耗时：5.118s。
EXPLAIN⼀下：
使⽤OR条件的时候，虽然WHERE 语句中有⽤到索引字段，但还是进⾏了全表扫描。
7. 当我们的sql有多个列的筛选条件的时候，就需要对查询的多个列都加索引组成聚合索引：
加上聚合索引：ALTER TABLE `test_user` ADD INDEX index_union_name_password(username,password)创新驱动发展战略的重要成果
通过临时⽂件的⼤⼩来看，索引⽂件的⼤⼩已经超过了数据⽂件很多了。索引侧⾯来说，索引要合理利⽤，索引就是⽤空间换时间。
[SQL]ALTER TABLE `test_user` ADD INDEX index_union_name_password(username,password)
受影响的⾏: 10024725。
时间: 1399.785s。
8. 再来执⾏：[SQL] SELECT id, username, email, PASSWORD FROM test_user WHERE username = ‘username_900000‘OR `password` = ‘7ece221bf3f5dbddbe3c2770ac19b419‘
耗时：4.416s。
EXPLAIN：
竟然是全表扫描，不可思议 使⽤ OR 语句竟然没有启⽤聚合索引，也没使⽤到单索引username，，，
9. 再来执⾏：[SQL] SELECT id, username, email, PASSWORD FROM test_user WHERE username = ‘username_900000‘AND `password` = ‘7ece221bf3f5dbddbe3c2770ac19b419‘
耗时：0.001s。
EXPLAIN：
AND 语句才使⽤到了聚合索引，聚合索引必须使⽤AND条件，同时要符合最左原则，请戳我。
10. 主键区间查询
[SQL]EXPLAIN SELECT id, username, email, PASSWORD FROM test_user WHERE id > 8999990
AND id < 8999999
受影响的⾏: 0
时间: 0.001s。
命中7⾏，查询时间很短。
[SQL]SELECT id, username, email, PASSWORD FROM test_user WHERE id > 8999900 AND id < 8999999
受影响的⾏: 0
html登录时间: 0.010s
mysql语句的执行顺序[SQL]SELECT id, username, email, PASSWORD FROM test_user WHERE id > 8999000 AND id < 8999999
受影响的⾏: 0
时间: 0.029s
[SQL]SELECT id, username, email, PASSWORD FROM test_user WHERE id > 8990000 AND id < 8999999
受影响的⾏: 0
时间: 0.139s
通过不断加⼤区间来看，查询时间跟查询的数据量成相对的正⽐增长，同时使⽤到了主键索引。
11. 字符串区间查询
[SQL]SELECT id, username, email, PASSWORD FROM test_user WHERE username > ‘username_800000‘ AND `password` > ‘7ece221bf3f5dbddbe3c2770ac19b419‘
受影响的⾏: 0
时间: 6.059s
EXPLAIN:
未使⽤索引和聚合索引，进⾏了全表扫描。
[SQL]SELECT id, username, email, PASSWORD FROM test_user WHERE username > ‘username_900000‘ AND `password` > ‘7ece221bf3f5dbddbe3c2770ac19b419‘
受影响的⾏: 0
时间: 11.488s
EXPLAIN:
也使⽤到了索引和聚合索引。
对⽐得出，字符串进⾏区间查询，是否能使⽤到索引的条件得看mysql是如何优化查询语句的。
12.最左原则
1]. 新建 A、B、C 聚合索引
[SQL]ALTER TABLE `test_user` ADD INDEX index_union_name_email_password(username,email,password)
受影响的⾏: 10024725
时间: 3171.056s
2]. SQL 测试
慎⽤ OR 条件，可能将会导致全表扫描。
覆盖了 A、B、C 索引：
该语句使⽤了覆盖索引，WHERE 语句的先后顺序并不影响。MySQL会对SQL进⾏查询优化，最终命中ABC索引。
命中了 A、B、C 索引中的 AB组合，查询耗时很短：
没有命中到 A、B、C 索引，所以进⾏了全表扫描，查询耗时长。
⼩结：
要使⽤覆盖索引必须都是 AND 条件，慎⽤ OR 条件。
要使⽤覆盖索引如ABC，需满⾜条件语句中有 A、AB、ABC才会使⽤覆盖索引，采⽤最左原则。
(三)、InnoDB引擎测试
1). 新建 InnoDB 表
根据上⽂的步骤，新建⼀个 test_user_innodb 表，引擎使⽤MyISAM，然后将存储引擎修改回InnDB。
使⽤如下命令： ALTER TABLE test_user_innodb ENGINE=InnoDB; 此命令执⾏时间⼤约耗时5分钟，耐⼼等待。
[SQL]ALTER TABLE test_user_innodb ENGINE=InnoDB;
受影响的⾏: 10024725
时间: 692.475s
执⾏完毕后， test_user_innodb 表由之前的 三个⽂件 变为 两个⽂件，test_user_innodb.frm 和 test_user_innodb.idb。
其中frm⽂件记录表结构，idb⽂件记录表中的数据，其实就是⼀个B+树索引⽂件，不过该树的叶⼦节点中的数据域记录的是整⾏数据记录。
所以 Innodb 的查次数⽐ MyISAM 表减少⼀次磁盘IO查逻辑，但相对来说，插⼊数据也就没有MyISAM 快了，有所求就有所得吧！
同时 InnoDB ⽀持⾏锁、表锁，InnoDB 的锁机制是建⽴在索引上的，所以如果没命中索引，那么将是加表锁。
2). SQL 测试
1. [SQL]SELECT id,username,email,password FROM test_user_innodb WHERE username=‘username_9000000‘
受影响的⾏: 0
时间: 14.540s
显⽰进⾏了全表扫描，但跟MyISAM表对⽐来说，扫描的⾏数⼩了很多，可能这就是底层B+树布局不⼀样导致的吧。
2. 那我们给username列加上普通索引。
ALTER TABLE `test_user_innodb` ADD INDEX index_name(username) ;
此时，Mysql开始对 test_user_innodb 表建⽴索引，查看mysql 数据⽬录：
仔细观察，发现只⽣成了⼀个表结构临时⽂件。ibd⽂件容量在不断增⼤。这个跟MyISAM表加索引逻
辑不⼀样。
[SQL]ALTER TABLE `test_user_innodb` ADD INDEX index_name(username) ;
受影响的⾏: 0
时间: 157.679snginx api
此过程⼤约耗时 157.679s, 貌似建索引的过程未进⾏全表扫描，对⽐MyISAM表减少60s左右。为何如何？估计需要看底层实现了！
3. 再执⾏ SELECT id,username,email,password FROM test_user_innodb WHERE username=‘username_9000000‘
[SQL]SELECT id,username,email,password FROM test_user_innodb WHERE username=‘username_9000000‘
受影响的⾏: 0
时间: 0.001s
可见查询耗时减少的很可观，对⽐与未加索引。⽤EXPLAIN分析⼀下，和MyISAM表没有多少差别。
4. 再⽤username和password来联合查询
zblog下载站主题SELECT id, username, email, PASSWORD FROM test_user_innodb WHERE `password` =
‘7ece221bf3f5dbddbe3c2770ac19b419‘ AND username = ‘username_9000000‘;
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论