百万数据测试数据库索引

上一篇博客只讲了索引的定义及性质，其实用性并未测试，本篇讲生成一百万挑数据以供测试

测试数据

首先创建一个测试用的表

CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT'' COMMENT'用户昵称',
`email` VARCHAR(50) NOT NULL COMMENT'用户邮箱',
`phone` VARCHAR(20) DEFAULT'' COMMENT'手机号',
`gender` TINYINT(4) UNSIGNED DEFAULT '0'COMMENT '性别（0：男;1:女）',
`password` VARCHAR(100) NOT NULL COMMENT '密码',
`age` TINYINT(4) DEFAULT'0'  COMMENT '年龄',
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表'

接着创建能生成百万数据的函数

-- 插入100万数据.
DELIMITER $$
-- 写函数之前必须要写，这是标志
CREATE FUNCTION rand_data ()
RETURNS INT
BEGIN
	DECLARE num INT DEFAULT 1000000;
	DECLARE i INT DEFAULT 0;
	WHILE i<num DO
		INSERT INTO `app_user`(`name`,`email`,`phone`,`gender`,`password`,`age`)
		VALUES(CONCAT('用户',i),'123456@qq.com',CONCAT(182,FLOOR(RAND()*10000000)),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
		SET i=i+1;
	END WHILE;
	RETURN i;
END;

SELECT rand_data() -- 执行此函数 生成一百万条数据

drop FUNCTION if EXISTS rand_data;
-- 插入100万数据.
DELIMITER $$
-- 写函数之前必须要写，这是标志
CREATE FUNCTION rand_data ()
RETURNS INT
BEGIN
	set @num = 100;
	set @i = 0;
	WHILE @i < @num DO
		set @paid = CONVERT(CONCAT(200,CEIL(RAND()*4)),SIGNED);
		set @name = (select `name` from `ihealth`.`patient` where `id`= @paid);
		set @date = CONCAT('2023-03-',(10+CEIL(RAND()*20)));
		INSERT INTO `ihealth`.`record`(`type`, `time`, `count`, `average`, `paid`, `name`) 
		VALUES ('深膝蹲', @date, (5+CEIL(RAND()*10)), CONVERT(70+RAND()*30,DECIMAL(5,2)), @paid, @name);
		SET @i = @i + 1;
	END WHILE;
	RETURN @i;
END;

SELECT rand_data(); -- 执行此函数 生成一百条数据

执行函数后可能会要等待一段时间，我是9代i7，花了13s左右
再打开表（刷新表）就会出现这生成的百万数据啦
在当前这个仅有id主键的情况下执行查询语句

SELECT * FROM app_user WHERE NAME='用户10086';

用时
可以在select语句前加上explain语句分析

EXPLAIN SELECT * FROM app_user WHERE NAME='用户10086';

可以看到这里遍历了多少行
如果我们加上索引（执行此语句）

CREATE INDEX id_app_user_name ON app_user(`name`);

会自动为你创建B+树

再执行查找语句
可以看到这里耗时的质变

解析一下只查找了一行
典型的用空间换时间（索引是占用内存的）
可以类比一页一页翻书找资料，和根据目录找资料的速度

索引在数据量很大（百万以上）的时候才有明显优势

索引的原则

索引不是越多越好（占空间）
不要对进程变动加索引（更改已经创建索引的内容会降低效率）
小数据量不要加索引（没什么用，且占空间）
索引一般用在常常查询的字段上