索引的分类

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

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

基础语法

-- 索引的使用
-- 1、在创建表时给字段增加索引
-- 2、创建完毕后增加索引

-- 显示所有索引信息
show index from student;

-- 增加一个全文索引 索引名（列名）
alter table school.student add fulltext index `studentName_index` (`StudentName`); 

-- EXPLAIN 分析SQL执行情况
explain select * from student;  -- 非全文索引

-- MATCH() AGAINST() 全文搜索的函数  首先建立全文索引
explain select * from student where MATCh(studentName)AGAINST("刘");

MySQL 优化----参考：https://blog.csdn.net/jiadajing267/article/details/81269067

测试索引

测试素材表

-- 建立索引测试表
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 COMMENT '',
    PRIMARY KEY(id)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表';

数据构造

-- 插入数据
-- 单条数据插入
insert into `app_user`(`name`,`email`,`phone`,`gender`,`password`,`age`) 
values(
	CONCAT('用户1'),
	'214535544@qq.com',
	CONCAT('18',FLOOR(RAND()*((999999999-100000000)+100000000))),
	FLOOR(RAND()*2),
	UUID(),
	FLOOR(RAND()*100)
);

-- 编写函数插入100万条数据
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),
			'214535544@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.631
select * from `app_user` where `name`='用户9999'; -- 0.437
select * from `app_user` where `name`='用户9999'; -- 0.451

EXPLAIN select * from `app_user` where `name`='用户9999'; -- rows:992262

select * from student; -- 0.017

-- id_表名_字段名
-- create index 索引名 on 表(字段)
create index id_app_user_name on `app_user`(`name`);


select * from `app_user` where `name`='用户9999'; -- 0.015
select * from `app_user` where `name`='用户9999'; -- 0.025

EXPLAIN select * from `app_user` where `name`='用户9999'; -- rows:1

差别显示

创建索引前

创建索引后

img-0xpt6Bz5-1584691439557

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

索引原则

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

索引的数据结构

Hash 类型的索引

BTree：InnoDB的默认数据结构

每日读一遍最好