【详解】MySQL索引的基本操作，索引（主键索引，普通索引，组合索引，唯一索引）

索引底层原理：

【详解】面试必问：MySQL索引底层原理（基于B+Tree）_CodingLJ-优快云博客

前言

索引是什么？

        索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。

        索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。

一、索引的基本操作

①索引的优点

        大大加快了数据的查询速度。

②索引的缺点

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

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

        3.当对表的数据进行增删改的时候，增删改的速度会变慢，增删改对底层数据结构进行排序。因为要维护索引，速度会受到影响。

③索引的分类

1、InnoDB搜索引擎：

a.主键索引

       数据库表中设定主键后，数据库会自动创建主键索引，在InnoDB引擎中主键索引也称之为局促索引。

b.单值索引（普通索引）

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

c.唯一索引

        索引列的值必须唯一，但允许有空值。

d.组合索引（复合索引、联合索引）

        即一个索引包含多个列。

2、MyISAM

        full  Text 全文索引

        全文索引的索引类型为FULL Text，在定义索引的列上支持全文查找，运行插入重复的值和空值，只有MYISAM存储引擎支持的全文索引。        

④索引的创建

（1）主键索引

主键索引在建表的时候自动创建。

--主键索引是在建表的时候自动创建
create table t_user(
	`id` VARCHAR(20) PRIMARY KEY,
	`name` VARCHAR(20)
);

#查看索引
SHOW INDEX FROM t_user;

 （2）单列索引(普通索引)

   创建普通索引，共有2种方式：创建表的时候创建，创建表后进行创建索引

--创建普通索引，共有2种方式：创建表的时候创建，创建表后进行创建索引
CREATE INDEX name_index ON t_user(`name`);

--创建表的时候构建索引
CREATE TABLE t_user1(
	`id` VARCHAR(20) PRIMARY KEY,
	`name` VARCHAR(20),
	KEY(`name`)
);
-- 索引的名默认是字段名
SHOW INDEX FROM t_user1;

 （3）唯一索引

--创建唯一索引:唯一索引允许为null，只允许一个值为null
CREATE TABLE t_user2(
	`id` VARCHAR(20) PRIMARY KEY,
	`name` VARCHAR(20),
	UNIQUE(`name`)
);

SHOW INDEX FROM t_user2;

（4）复合索引（组合索引）

--创建复合索引（组合索引）
CREATE TABLE t_user3(
	id VARCHAR(20) PRIMARY KEY,
	`name` VARCHAR(20),
	age INT,
	KEY(`name`,age)
);
-- 创建复合索引，
CREATE INDEX nameageindex ON t_user3(`name`,`age`);

SHOW INDEX FROM t_user3;

  （5）删除索引

drop index 索引名 on 表名

二、索引面试题

创建的索引顺序是：

create index  idx_user on user(name,age,bir);

问：以下能否；使用索引查询出name，age，bir？

①where name = # and bir = # and age = #  能否利用索引？    能

②where name = # and age = # and bir = #  能否利用索引？    能

③where age = # and bir = # 能否利用索引？                          不能

④where bir = # and age = # and name = # 能否利用索引？      能

⑤where age = # and bir = # 能否利用索引？                           不能

解释：

1、能否使用索引必须遵循的是最左前缀法则

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

这个问题解决了，要遵循1,2法则。但新的问题又来了。

为什么①②④可以使用索引，③⑤使用索引会失效？也就是为什么不遵循最左前缀原理，中间有间隙的查询或者不从第一个字段查询，会造成索引失效？为什么字段不会被命中？

好，下一篇博客就来讲一讲为什么索引会失效？什么情况下会失效？底层实现是什么样的？

MySQL索引为什么会失效？

【详解】面试必问：MySQL索引为什么会失效？最左匹配原则中间有间隙，为什么索引不会被命中？%开头索引为什么失效？范围查询索引为什么会失效？_CodingLJ-优快云博客

索引底层原理：【详解】面试必问：MySQL索引底层原理（基于B+Tree）_CodingLJ-优快云博客