Mysql之索引

持续创作，加速成长！这是我参与「掘金日新计划 · 6 月更文挑战」的第7天，点击查看活动详情

索引是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构

索引的分类

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

• 主键索引（primary key）

  • 唯一标识，主键不可重复

• 唯一索引（unique key）

  • 避免重复的列出现，唯一索引可以重复，多个列都可以标识唯一索引

• 常规索引（key/index）

  • 默认的，index，key关键字来设置

• 全文索引（fulltext）

  • 在特定的数据库引擎下才有，MyISAM
  • 快速定位数据

索引的使用

1、创建索引

--create [索引类型] index 索引名 on 表名(列名1 [排序方式], 列名2 [排序方式]);
create unique index sno_index on student(sno ASC);

2、修改索引

--alter index 旧索引名 rename to 新索引名
alter index scno rename to sno;

3、删除索引

--drop index 索引名
drop index sno_index;

索引的优化

explain 分析 sql 执行的状况

--增加一个全文索引列名
alter table school.student add fulltext index `studentname`(`studentname`);
​
explain select * from student; --非全文索引
​
explain select * from student where match(studentname) against('李');

关于 explain 的内容可以看这篇博客：www.cnblogs.com/acm-bingzi/…

测试索引

--创建一个表
CREATE table app_user(
    id bigint(20) auto_increment,
    name varchar(50),
    email varchar(20),
    phone varchar(20),
    gender int(4),
    password varchar(100),
    age int(4),
    primary key(id)
);
​
--插入100万条数据
create function mock_data()
returns int
deterministic
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), '12345678@qq.com', concat('1', floor(rand() * ((999999999 - 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';
​
--创建索引
--create index 索引名 on 表(字段);
create index id_app_user_name on app_user(`name`);
​
--有索引时查找
select * from app_user
where name = '用户9999';

在小数据量的时候，用处不大，但是在大数据的时候，区别非常明显

无索引的查询时间

有索引的查询时间

索引原则

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

索引的数据结构

Hash 类型的索引

Btree：InnoDB 的默认数据结构

关于索引的数据结构具体可以看这篇博客：blog.codinglabs.org/articles/th…