有无索引的性能比较：mysql插入100万条数据后查询

小鱼儿 2022-08-29 09:52 144阅读 0赞

## 1 环境 ##

win 7 +mysql8.0 +nvicat  
内存12个G

## 2 建表 ##

CREATE TABLEIF NOT EXISTS `app_user`(
    id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    name varchar(50) NOT NULL,
    email varchar(50) NOT NULL,
    phone varchar(20) NOT NULL,
    gender tinyint(4) unsigned DEFAULT 0,
    password  varchar(100) NOT NULL,
    age tinyint(2)  NOT NULL,
    `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

## 3 尝试插入1条 ##

INSERT INTO app_user(name, email, phone,password)
    VALUES(CONCAT('用户1'),'18888888@qq.com','1234567', FLOOR(RAND()*100));

![在这里插入图片描述][eba0a1b36c58483da4e33a4d95855e67.png]

## 4 写函数 ##

/* 第一个语句 delimiter 将 mysql 解释器命令行的结束符由”;” 改成了”$$”， 让存储过程内的命令遇到”;” 不执行 */
    DELIMITER $$
    CREATE FUNCTION mock_data()
    RETURNS INT
    DETERMINISTIC
    
    BEGIN
    	DECLARE num INT DEFAULT 1000000;
    	DECLARE i INT DEFAULT 0;
    	WHILE i<num DO
    		INSERT INTO `app_user`(`name`,`email`,`phone`,`gender`,`password`)VALUES(CONCAT('用户',i),'19224305@qq.com','123456789',FLOOR(RAND()*2), FLOOR(RAND()*100));
    		SET i=i+1;
    	END WHILE;
    	RETURN i;
    END;$$

注意，5.0版本不需要DETERMINISTIC，8.0需要加这行命令。

## 5 执行函数 ##

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

![在这里插入图片描述][4b71f4e6abea401380ed86904f30e03a.png]  
生成数据花了168秒，恐怖！！

查看下数据:  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NDYzMTc1_size_16_color_FFFFFF_t_70]

## 6 性能分析 ##

用主键查数据：

SELECT * FROM app_user WHERE id = 99990;

![在这里插入图片描述][25eb5a9cad54402e81aa36302b1ef52c.png]  
用非主键查数据：

SELECT * FROM app_user WHERE name = '用户9999';

![在这里插入图片描述][24e9eecfe86440fdb09c00ff33423b60.png]  
区别还是很明显的。在用户体验上，体现出有无索引的好处。

进一步分析：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NDYzMTc1_size_16_color_FFFFFF_t_70 1]  
使用，explain命令，查询了994825行才查到。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NDYzMTc1_size_16_color_FFFFFF_t_70 2]

## 7 创建索引 ##

之前查询太慢了，试试给nam字段添加索引

-- id __表名字_字段名
    	
    	CREATE INDEX id_app_user_name on app_user(name)

![在这里插入图片描述][eb619eaccaa548acb8a278ed4c4344ca.png]  
只查询了一行，速度变快了。原因是重新建了一颗索引树，空间换时间。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NDYzMTc1_size_16_color_FFFFFF_t_70 3]  
查询时间：

![在这里插入图片描述][835df08e768a409c9e4db52ede488f56.png]  
这就是建立索引的好处。

[eba0a1b36c58483da4e33a4d95855e67.png]: /images/20220829/1ab81c5535b74358b9b60773448f00b5.png
[4b71f4e6abea401380ed86904f30e03a.png]: /images/20220829/10e9a5f49fce460ba779c287c698e1ce.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NDYzMTc1_size_16_color_FFFFFF_t_70]: /images/20220829/38b04062277a4a3da61f43afed1ddacc.png
[25eb5a9cad54402e81aa36302b1ef52c.png]: /images/20220829/7bbd732c0cda4673a5e2b581f12156a8.png
[24e9eecfe86440fdb09c00ff33423b60.png]: /images/20220829/bb12a724ae5d4d6eb2f7ed62cdb932b7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NDYzMTc1_size_16_color_FFFFFF_t_70 1]: /images/20220829/c69609ba84014657bd08711280ef859a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NDYzMTc1_size_16_color_FFFFFF_t_70 2]: /images/20220829/22868eb87c9047689e52107276810220.png
[eb619eaccaa548acb8a278ed4c4344ca.png]: /images/20220829/11d74f6d95bf4ab89e92cc28a303585d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NDYzMTc1_size_16_color_FFFFFF_t_70 3]: /images/20220829/a55c876d372d42b191834b4353dbaecc.png
[835df08e768a409c9e4db52ede488f56.png]: /images/20220829/582e94da8879430e9e557df28d84b7ab.png