MySQL整理及学习(五):单列索引与联合索引
程序员文章站
2022-04-05 23:35:36
...
下面做了一个实验
实验原理
单列索引:给一张表的其中一个列加索引
联合索引:给一张表的N个列加索引,遵循最左前缀原则
实验目的
- 粗略比较了单列索引和联合索引的速度;
- 验证最左前缀原则:比如,
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列建立了单列索引。