MySQL学习笔记7：MySQL索引配合大量数据的使用

# 1、前言

学习视频源自：【狂神说Java】MySQL最新教程通俗易懂

B站视频链接：https://www.bilibili.com/video/BV1NJ411J79W

[video(video-4IYyPI1C-1589717216258)(type-bilibili)(url-https://player.bilibili.com/player.html?aid=83395424)(image-https://ss.csdn.net/p?http://i0.hdslb.com/bfs/archive/efacc6aa636d653b8b5c2984988eaed4f428132c.jpg)(title-【狂神说Java】MySQL最新教程通俗易懂)]

笔记为看视频自行撰写，仅限学习交流使用

# 1.1、上一篇文章

MySQL学习笔记6：聚合函数与事务初识 (opens new window)

# 8、索引

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。

# 8.1、索引的分类

在一个表中，主键索引只能有一个，唯一索引可以有多个

主键索引（PRIMARY KEY）
- 唯一的标识，主键不可重复，只能有一个列作为主键
唯一索引（UNIQUE KEY）
- 避免重复的列出现，唯一索引可以重复，一个表中可以有多个唯一索引
常规索引（KEY/INDEX）
- 默认的，INDEX、KEY关键字来设置
全文索引（FULLTEXT）
- 在特定的数据库引擎下才有，MyISAM
- 快速定位数据

-- 索引的使用
-- 在创建表的时候给字段增加索引
-- 创建完毕后，增加索引

-- 显示所有的索引信息
SHOW INDEX FROM student

-- 增加一个索引
ALTER TABLE `student` ADD FULLTEXT INDEX `studentname`(`studentname`)

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# 8.2、测试索引

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


-- 插入一百万条数据
SET GLOBAL log_bin_trust_function_creators = 1; --解决报错的问题
DELIMITER $$
-- 写函数之前必须要写，标志
CREATE FUNCTION mock_data ()
RETURNS INT
BEGIN
	DECLARE num INT DEFAULT 10000;
	DECLARE i INT DEFAULT 0;
	WHILE i<num DO
		INSERT INTO `app_user`(`name`,`email`,`phone`,`gender`,`password`,`age`)VALUES(CONCAT('用户',i),'19224305@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';  --耗时0.75s

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

-- id_表名_字段名
-- CREATE INDEX 索引名 on 表（字段）
CREATE INDEX id_app_user_name ON app_user(`name`);
SELECT * FROM app_user WHERE `name` = '用户9999';  --耗时0.01s