mysql百万数据实践-索引
1.生成数据
//建表 CREATE TABLE `person` ( `id` bigint(20) unsigned NOT NULL, `username` varchar(100) NOT NULL, `age` tinyint(3) unsigned NOT NULL, `sex` tinyint(1) unsigned NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; //创建生成数据procedure，num代表生成数据量的大小 CREATE PROCEDURE `generate`(IN num INT) BEGIN DECLARE char_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'; DECLARE username VARCHAR(25) DEFAULT ''; DECLARE id int UNSIGNED; DECLARE len int; set id=1; DELETE from person; WHILE id <= num DO set len = FLOOR(1 + RAND()*25); set username = ''; WHILE len > 0 DO SET username = CONCAT(username,substring(char_str,FLOOR(1 + RAND()*62),1)); SET len = len - 1; END WHILE; INSERT into person VALUES (id,username, FLOOR(RAND()*100), FLOOR(RAND()*2)); set id = id + 1; END WHILE; END //最后执行generate，生成100万数据 CALL generate(1000000);
2.查询
select * from person ORDER BY username desc limit 100;
[SQL] SELECT * FROM person ORDER BY username DESC limit 100;
受影响的行: 0
时间: 3.900s
3.建立索引查询
create index index_user on person(username(100));
4.测试
5.联合索引(复合索引)
例如索引是key index （a,b,c）。 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找
应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时，复合索引非常有用；仅对后面的任意列执行搜索时，复合索引则没有用处。
参考：https://segmentfault.com/a/1190000012918964
