mysql索引

一、索引

索引（index）是帮助mysql快速获取数据的数据结构

1.1 索引的分类

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

• 主键索引（primary key）

  • 唯一的标识，不可重复，只有一个

• 唯一索引（unique key）

  • 避免出现重复的列数据，可以有多个唯一索引

• 常规索引（key/index）

  • 默认的，使用key/index来标识

• 全文索引（fulltext）

  • 快速定位数据

1.2 索引的使用

1. 创建索引
   1、创表时指定索引
   2、使用alter给已存在的表添加索引

2. 查看所有的索引信息

show index from student;

3. 为某一列添加一个全文索引

alter table student add fulltext index sn(`studentName`);

4. 使用explain分析sql执行状态

explain select * from student;
explain select * from student where match(studentName) against('乌鸦');	-- 使用了前面创建的fulltext index sn
explain select * from student where studentName='乌鸦哥';	-- 这里没有使用索引，查询行数多

二、创建100万条数据来测试索引

首先创建一个表 app_user

create table `app_user`(
  `id` int(10) unsigned not null auto_increment,
  `name` varchar(20) default '' comment '用户昵称',
  `email` varchar(30) not null comment '用户邮箱',
  `phone` varchar(20) default '' comment '手机号',
  `gender` tinyint(4) unsigned default 0 comment '0:男, 1:女',
  `password` varchar(50) 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用户表';

然后编写函数插入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),'1497943433@qq.com',concat('15',floor(rand()*999999999)),floor(rand()*2),uuid(),floor(rand()*100));
	set i = i+1;
  end while;
  return i;
end;
delimiter ;
select mock_data();

未创建索引前，查询用时422ms，查询了99万行。使用索引后查询用时17ms，查询了一行。

select * from app_user where name='用户699981';
explain select * from app_user where name='用户699981';

创建索引语句：

create index id_app_user_name on app_user(`name`);

查询了99万行数据

创建索引后，用时17ms

三、索引的原则

什么时候使用索引，索引的使用场景
四个基本的使用原则

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

Reference：MySQL索引背后的数据结构及算法原理

        MySQL索引背后的数据结构及算法原理