数据库(DataBase)-百万条数据索引效果检验

2.百万条数据测试

索引在查询数据中的用途，在小数据中一般难以体现，我们创建一个百万数据的用户表来测试索引的效果。

(1)创建测试表格：

-- 一百万条数据检验索引的效果。
use school;-- 数据库school

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`  timestamp NULL DEFAULT NULL COMMENT '创建时间',
    `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
    -- Mysql 5.5版本，需要注意：`create_time`和`update_time` 插入数据时，针对创建时间字段：在sql里now()  或者在代码里new date()更改后的sql，把默认值给个空，否则报错。
	PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表';

MySQL 5.5版本，需要注意：create_time和update_time 插入数据时，针对创建时间字段：在sql里now() 或者在代码里new date()更改后的sql，把默认值给个空，否则报错。

(2)插入百万条数据：

DROP FUNCTION IF EXISTS mock_data;
-- 写函数之前必须要写，标志:$$
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
-- 注意returns，否则报错。
BEGIN
DECLARE num INT DEFAULT 1000000;
-- num 作为截止数字，定义为百万，
DECLARE i INT DEFAULT 0;
WHILE i < num DO
   INSERT INTO app_user(`name`, `email`, `phone`, `gender`, `password`, `age`)
   VALUES(CONCAT('用户', i), CONCAT('100',i,'@qq.com'), CONCAT('13', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
   SET i = i + 1;
END WHILE;
RETURN i;
END;

/* 部分数据插入分析：
`name` ——> CONCAT('用户', i):
CONCAT(str1,str2,……)
CONCAT函数，将“str1,str2,……”等字段连接起来，构成一个字符串。
连接一个“用户”+i的字符串，当做用户名。

`email` ——> CONCAT('100',i,'@qq.com')：
连接“100”+i+“@qq.com”，构成整个邮箱。

`phone` ——> CONCAT('13', FLOOR(RAND()*(999999999-100000000)+100000000))：
RAND()生成一个0-1的随机数，然后乘以一个足够大的数(999999999-100000000)之后，加上100000000，
然后，使用FLOOR()强转成舍弃小数的整数。

*/

前两步执行结果：

(3)数据查询检验：

·查询数据总量：耗时14.032sec。

SELECT mock_data();

输出：

·查询特定数据的耗时：

-- 对不同用户的查询，耗时一般不同，即使是同一个用户，多次查询的耗时也可能不同。
SELECT * FROM app_user WHERE name = '用户7777'; -- 1.188sec
SELECT * FROM app_user WHERE name = '用户7777'; -- 0.766sec
SELECT * FROM app_user WHERE name = '用户77777'; -- 0.641sec
SELECT * FROM app_user WHERE name = '用户777777'; -- 0.594sec

·查询查找过程经历的数据量：

explain SELECT * FROM app_user WHERE name = '用户7777'; -- 寻找了2009736条数据后才找到用户7777
explain SELECT * FROM app_user WHERE name = '用户77777'; -- 寻找了2995119条数据后才找到用户77777

寻找了2009736条数据后，才找到“用户7777”

·修改表格，增添索引：

创建索引的格式：create index 索引名 on 表名(加持字段)（注：索引的名字惯用格式：id _ 表名 _ 字段名）

-- 索引的名字惯用格式：id_表名_字段名
-- 创建索引的格式：create index 索引名 on 表名(加持字段)
create index id_app_user_name on app_user(`name`);

-- 添加索引后再次测试
explain SELECT * FROM app_user WHERE name = '用户7777';  -- 寻找了3条数据后才找到用户7777
explain SELECT * FROM app_user WHERE name = '用户77777';  -- 寻找了3条数据后才找到用户77777

索引在小数据中的用处不大，但是在大数据中效果很明显。

参考文献

《【狂神说Java】MySQL最新教程通俗易懂》
视频连接：https://www.bilibili.com/video/BV1NJ411J79W

《狂神说MySQL06：事务和索引》
原文连接

《mysql5.5 timestamp类型create_time和update_time更新时间》

原文连接
2020.05.18