MySQL整理及学习（五）：单列索引与联合索引

下面做了一个实验

实验原理
单列索引：给一张表的其中一个列加索引
联合索引：给一张表的N个列加索引，遵循最左前缀原则

实验目的

1. 粗略比较了单列索引和联合索引的速度；
2. 验证最左前缀原则：比如，alter table tablename add index name_sex_age(name,sex,age);，实际上创建了name单列索引，name,sex联合索引，name,sex,age联合索引

实验环境
WAMP，Navicat，MySQL

实验步骤
步骤一： 先建立数据表及数据

// 用户表1 
CREATE TABLE `users` (
	`userID` INT (11) NOT NULL AUTO_INCREMENT,
	`userName` VARCHAR (20) NOT NULL,
	`password` VARCHAR (20) NOT NULL,
	PRIMARY KEY (`userID`)
);

// 生成测试数据的存储过程
delimiter $
CREATE PROCEDURE genUsers ()
BEGIN

DECLARE i INT DEFAULT 0 ;
WHILE i < 100000 DO

SET i = i + 1 ; INSERT INTO users (userID, userName, `password`)
VALUES
	(
		i,
		concat('username', i),
		concat('password', i)
	) ;
END
WHILE ; END$
delimiter ;

// 调用存储过程，生成测试数据
call genUsers();

// 创建用户表2，结构和数据复制用户表1
CREATE table users2 SELECT * from users;

步骤二：建立索引
给用户表1的userName、password列建立单列索引

ALTER TABLE users ADD INDEX username(userName);
ALTER TABLE users ADD INDEX passwd(`password`);

给用户表2的userName、password列建立联合索引

ALTER TABLE users2 ADD INDEX name_passwd (userName, `password`);

实验结果一
1.比较单列索引和联合索引的速度（数据10万条）
单列索引查询：0.085s

联合索引查询：0.071s

实验结论一
从结果看，10万条数据，单列索引和组合索引查询速度差不多。

实验结果二
userName使用了索引

password没有使用索引

实验结论二
在user2表中，我们建立的是联合索引ALTER TABLE users2 ADD INDEX name_passwd (userName, password);。从结果看，结果符合最左前缀原则，即在建立联合索引name_passwd的时候，在userName列建立了单列索引。