Mysql索引

目录

1.什么是索引

2.索引分类

InnoDB

a.主键索引

b.单值索引 单列索引 普通索引

c.唯一索引

d.复合索引

MyISAM

e.Full Text 全文索引（My5.7版本之前，只能用于MYISAM引擎）

3.索引的基本操作

1.主键索引 自动创建

建表 主键自动创建主键索引

查看索引

2.单列索引（普通索引|单值索引） 创建两种方式 ：

建表时创建

建表后创建

3.唯一索引

建表时创建

建表后创建

4.复合索引

建表时创建

建表后创建

4.索引的底层原理 index

1.插入id无序数据

2.查询后，发现id是有序的：

3.为什么上面数据明明没有按顺序插入，为什么查询时却是有序的呢？

4.为了进一步提高效率，MySQL索引又进行了优化

5.上面这种索引结构称之为B+树数据结构，那么什么是B+树呢

6.B树

---

1.什么是索引

官方定义：一种帮助MySQL提高查询效率的数据结构

索引的优点：

1.大大加快数据查询速度

索引的缺点：

1.维护索引需要消耗数据库资源

2.索引需要占用磁盘空间

3.当对表的数据进行增删改的时候，因为要维护索引，速度会受到影响。

2.索引分类

从数据结构角度上，可以分为B+tree索引，hash索引，fulltext索引（InnoDB，MyISAM都支持）

存储角度分为聚簇索引和非聚簇索引

逻辑角度上可以为primary key（主键索引） ,唯一索引, 单列，复合和Full Text 全文索引

InnoDB

a.主键索引

设定为主键后，数据库会自动建立索引，innodb为聚簇索引 主键索引，索引列值不能有空

b.单值索引 单列索引 普通索引

即一个索引只包含单个列，一个表可以有多个单例索引 id name index age index bir

c.唯一索引

索引列的值必须唯一，但允许有空值 唯一索引，索引列值可以存在null，但是只能存在一个null

d.复合索引

即一个索引包含多个列 id (name age) index bir

MyISAM

e.Full Text 全文索引（My5.7版本之前，只能用于MYISAM引擎）

全文索引类型为FULL TEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR，VARCHAR，TEXT类型列上创建。MYSQL只有MYISAM存储引擎支持全文索引。

3.索引的基本操作

1.主键索引 自动创建

建表 主键自动创建主键索引

CREATE TABLE t_user(id VARCHAR(20) PRIMARY KEY,name VARCHAR(20));

查看索引

show index from t_user

2.单列索引（普通索引|单值索引） 创建两种方式 ：

建表时创建

create table t_user1(id varchar(20) primary key , name varchar(20) , key(name));

建表后创建

create index name_index on t_user(name);

3.唯一索引

建表时创建

create table t_user2(id varchar(20) primary key , name varchar(20) , UNIQUE(name));

建表后创建

CREATE unique index name_index on t_user2(name);

4.复合索引

建表时创建

CREATE TABLE t_user3(id VARCHAR(20) PRIMARY KEY,name VARCHAR(20),age INT,KEY(name,age));

1.最左前缀原则

定义：符合左前缀的顺序，才可以利用符合索引

eg: name age bir

name 可以利用索引

name age 可以利用索引

name age bir 可以利用索引

2.mysql引擎在查询为了更好利用索引，在查询过程中会动态调整查询字段顺序，以便利用索引

能否利用索引

name bir age 能

name age bir 能

age bir 否

bir age name 能

建表后创建

CREATE INDEX name_age_index ON t_user3(name,age);

4.索引的底层原理 index

1.插入id无序数据

CREATE TABLE t_emp(id int PRIMARY KEY,name VARCHAR(20), age int);

INSERT INTO t_emp VALUES(5,'d',22);
INSERT INTO t_emp VALUES(6,'d',22);
INSERT INTO t_emp VALUES(7,'e',21);
INSERT INTO t_emp VALUES(1,'a',23);
INSERT INTO t_emp VALUES(2,'b',26);
INSERT INTO t_emp VALUES(3,'c',27);
INSERT INTO t_emp VALUES(4,'a',32);
INSERT INTO t_emp VALUES(8,'f',53);
INSERT INTO t_emp VALUES(9,'v',13);

2.查询后，发现id是有序的：

3.为什么上面数据明明没有按顺序插入，为什么查询时却是有序的呢？

原因：

mysql底层为主键自动创建索引，主键索引进行排序

为什么要排序呢？

因为排序后查询就相对较快了。如查询id = 34，只需要按照顺序查找到34就可以了。如果没有排序，就像大海捞针

4.为了进一步提高效率，MySQL索引又进行了优化

就是基于页面形式进行管理索引

如查询id =3 4，直接先比较页，先去页目录找，再去数据目录找

5.上面这种索引结构称之为B+树数据结构，那么什么是B+树呢

8位(bit)=1字节(Byte),1024字节=1KB

CREATE TABLE t_emp(id int PRIMARY KEY,name VARCHAR(20), age int);

page:

4（int)+20+4+8（指针）=36字节

16*1024/36=455 条数据 （主键+内容+指针）

页目录：

4（int)+8（指针）=12字节

16*1024/12=1365 条数据（主键+指针）

1365*1365*455 = 847974400 3层数可以存的数据条数

6.B树