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

1、前言

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

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

【狂神说Java】MySQL最新教程通俗易懂

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

1.1、上一篇文章

MySQL学习笔记6：聚合函数与事务初识

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('张');

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

索引在小数据量的时候，用处不大，但是在大数据的时候，区别十分明显

8.3、索引原则

• 索引不是越多越好
• 不要对经常变动的数据加索引
• 小数据量的表不需要加索引
• 索引一般加在常用来查询的字段上

索引的数据结构

Hash类型的索引

Btree：默认Innodb的默认数据结构

阅读：http://blog.codinglabs.org/articles/theory-of-mysql-index.html