【Mysql】数据库索引，百万数据测试索引效果

– 创建表

CREATE TABLE student(

StudentNo INT(4) NOT NULL COMMENT “学号”,

LoginPwd VARCHAR(20) DEFAULT NULL,

StudentName VARCHAR(20) DEFAULT NULL COMMENT “学生姓名”,

Sex TINYINT(1) DEFAULT NULL COMMENT “性别：0-1”,

GradeId INT(11) DEFAULT NULL COMMENT “年纪编号”,

Phone VARCHAR(50) NOT NULL COMMENT “联系电话”,

Address VARCHAR(255) NOT NULL COMMENT “地址”,

BornDate DATETIME DEFAULT NULL COMMENT “出生日期”,

Email VARCHAR(50) NOT NULL COMMENT “邮箱”,

IdentityCard VARCHAR(18) DEFAULT NULL COMMENT “身份证号”,

PRIMARY KEY (StudentNo), – 主键索引

UNIQUE KEY IdentityCard (IdentityCard), – 唯一索引，前面是索引名称，括号里是字段名

KEY Email (Email) – 常规索引

)ENGINE=INNODB DEFAULT CHARSET=utf8;

执行sql，创建表成功。这时候我继续增加一个全文索引。

– 增加一个全文索引类型，前面是索引名称，括号里是字段名

ALTER TABLE school.student ADD FULLTEXT INDEX StudentName (StudentName);

可以查看student表的所有索引SHOW INDEX FROM student;

还有第三种，CREATE INDEX 索引名 on 表(字段)，到后面演示。

三、百万数据测试索引效果

1. 再来创建个测试表

– 创建表

CREATE TABLE app_user(

id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,

name VARCHAR(50) DEFAULT ‘’ COMMENT ‘昵称’,

email VARCHAR(50) DEFAULT NULL COMMENT “邮箱”,

phone VARCHAR(20) DEFAULT NULL COMMENT “手机号”,

gender TINYINT(4) DEFAULT NULL COMMENT “性别 0-男, 1-女”,

password VARCHAR(100) NOT NULL COMMENT “密码”,

age TINYINT(4) NOT NULL COMMENT “年龄”,

create_time DATETIME DEFAULT CURRENT_TIMESTAMP,

update_time TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

PRIMARY KEY (id)

)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT=‘app用户表’;

执行创建成功，现在插入数据。

2. 插入数据

– 插入百万数据

DELIMITER $$

CREATE FUNCTION mock_data()

RETURNS INT

BEGIN

DECLARE num INT DEFAULT 1000000;

DECLARE i INT DEFAULT 0;

WHILE i < num DO

– 插入语句

INSERT INTO school.app_user(name,email,phone,gender,password,age)VALUES

(CONCAT(‘用户’,i), ‘123456@qq.com’, CONCAT(‘18’, FLOOR(RAND()*((999999999-100000000)+100000000))),

FLOOR(RAND()*2), UUID(), FLOOR(RAND()*100));

SET i = i+1;

END WHILE;

RETURN i;

END;

执行可能会出现This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary报错。

如果出现，可以先执行set global log_bin_trust_function_creators=TRUE ，然后再试下。

执行成功后，再执行：

SELECT mock_data();

大概持续1分钟左右，别急。

3. 测试查询

未加索引

查询一条数据。

SELECT * FROM app_user WHERE name=‘用户9999’

多次执行查询，发现花费时间，稳定在0.63s左右，这个从点击执行到看到结果，已经从肉眼可以感知有点慢了。

我们可以增加关键词EXPLAIN分析sql执行的情况。

EXPLAIN SELECT * FROM app_user WHERE name=‘用户9999’

可以看到这个语句查了99W+条，这都是时间消耗。

添加索引

用上面说的第三种方式，增加一个常规索引。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上软件测试知识点，真正体系化！

5c7.png)

添加索引

用上面说的第三种方式，增加一个常规索引。

[外链图片转存中…(img-racOcVwe-1719244560631)]
[外链图片转存中…(img-kT2QJN51-1719244560631)]
[外链图片转存中…(img-JQOSmztP-1719244560632)]

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上软件测试知识点，真正体系化！