7-索引+测试代码-2022-12-1

高效获取数据的数据结构 ， 高速跑车 ，拖拉机

提取句子主干

--索引分类

1、主键索引 PRIMARY KEY   唯一的标识，不可重复 只有一个列作为主键索引

2、唯一索引 UNIQUE KEY      避免重复的列出现 多个列可以标为唯一索引         可以有多个 

3、常规索引  KEY / INDEX    key 或 INDEX

4、全文索引 FullText    在特定的数据库中才有，快速定位数据 

-- 显示索引
SHOW INDEX FROM student

-- 增加一个全文索引 索引名（列名）
ALTER TABLE school.`student` ADD FULLTEXT INDEX `studentname`(`studentname`);

-- 分析SQL执行的情况 explain
EXPLAIN SELECT * FROM student -- 非全文索引

EXPLAIN SELECT * FROM student WHERE MATCH(studentname) AGAINST('张')

 

-- 测试索引

-- 建表
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 NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'

-- 插入100w条数据
DROP FUNCTION IF EXISTS mock_data;
DELIMITER $$ -- 写函数之前必写
CREATE FUNCTION mock_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), '552456727@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; -- 第一次执行到这里
SELECT mock_data(); -- 然后再单独执行这条语句

-- 对比耗时
-- 无索引
SELECT * FROM app_user WHERE NAME = '用户9999' -- 1.041 sec
EXPLAIN SELECT * FROM app_user WHERE NAME = '用户9999' -- 0.008 sec

-- 创建索引
CREATE INDEX idx_app_user_name ON app_user(NAME) -- 10.031 sec
EXPLAIN SELECT * FROM app_user WHERE NAME = '用户9999' -- 0.001 sec
SELECT * FROM app_user WHERE NAME = '用户9999' -- 0 sec