13、Mysql索引

1、什么是索引

索引是一种帮助mysql提高查询效率的数据结构

2、索引的优缺点

优点：大大加快数据查询速度

缺点：

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

(2)索引需要占用磁盘空间

(3)当对标进行增删改时，需要维护索引，速度会受到影响

3、索引的分类

-InnoDB： 

(1)主键索引（自动创建）--主键索引属于聚簇索引

设定为主键后数据库会自动建立索引，逐渐索引的列值不能为空

(2)唯一索引

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

(3)单值索引（普通索引）

一个索引只包含单个列，一个表可以有多个单列索引

(4)复合索引

一个索引包含多个列

-MyISAM

(5)Full Text 全文索引

全文索引类型为Full Text，在定义索引的列上支持全文查找，允许这些索引列中插入重复值和空值。全文索引可以在char、varchar、text类型列上创建

4、索引的基本操作

(1)主键索引（在创建表时自动创建）

create table user (id varchar(20) primary key, name varchar(20));
show index from user;    # 查看索引

(2)唯一索引

# 创建表时添加唯一索引
create table user(id varchar(20) primary key, name varchar(20),unique(name));


# 创建表后添加唯一索引
create table user(id varchar(20) primary key, name varchar(20));
create unique index name_index on user(name);

(3)单值索引（或普通索引）

# 创建表时添加单值索引
create table user(id varchar(20) primary key, name varchar(20),key(name)); 


# 创建表后添加单值索引
create table user(id varchar(20) primary key, name varchar(20));
create index name_index on user(name);

# 删除索引
drop index name_index on user;

(4)符合索引

# 创建表时添加复合索引
create table user(id varchar(20) primary key, name varchar(20), age int, key(name,age)); 


# 创建表后添加复合索引
create table user(id varchar(20) primary key, name varchar(20), age int);
create index name_age_index on user(name,age);

【经典面试题】

复合索引：name  age birth

判断下列组合能否利用索引

name birth age 能

name age birth 能

age birth 否

birth age name 能

age birth 否

【解析】

判断能否利用索引的原则是：

(1)最左前缀原则

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

5、索引底层原理解析

(1)当我们向数据库中的插入一组数据时（主键无序插入的），Mysql数据库在为主键自动创建索引时，会基于主键对数据进行排序(这也很好的回答了乱序插入数据，但是查询的数据却是有序的);

(2)排序后会将这些数据以链表的形式进行存储；(查询的时间复杂度为O(n))

(3)为了提高查询效率，Mysql索引又进行了优化，采用B+树这种数据结构进行存储，即基于页的形式进行管理索引，页的默认大小为16KB；

(4)站在整个B+树的存储结构上讲，一个3层的B+树可以存储的数据量在8~10亿左右。

6、聚簇索引和非聚簇索引

聚簇索引：将数据存储与索引放到一块，索引结构的叶子结点保存了行数据

非聚簇索引：将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置