玩转Mysql 七 （索引的创建与设计原则）

一路走来，所有遇到的人，帮助过我的、伤害过我的都是朋友，没有一个是敌人。

一、索引的创建与使用

1、索引的分类

MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。
（1）从 功能逻辑 上说，索引主要有 4 种，分别是普通索引、唯一索引、主键索引、全文索引。
（2）按照 物理实现方式 ，索引可以分为 2 种：聚簇索引和非聚簇索引。
（3）按照 作用字段个数 进行划分，分成单列索引和联合索引。

2、mysql索引的拓扑图

3、mysql索引概述

索引是排好序的数据结构，能够就少I/O次数。
MySQL-INNODB引擎使用B+树数据结构作为索引。
B树和B+树最大的区别是将数据存储到了叶子节点，并且叶子节点之间用指针相连。

• 主键索引PRIMARY KEY：不为空、不能重复
• 唯一索引UNIQUE：可为空，不能重复
• 普通索引INDEX：加速查找
• 联合索引：PRIMARY KEY(id,name)：联合主键索引 UNIQUE(id,name)：联合唯一索引INDEX(id,name)：联合普通索引

4、创建索引的三种方式

（1）创建表时同时创建索引

CREATE TABLE users(
userid INT ,
username VARCHAR(100),
age YEAR,
email VARCHAR(100),
INDEX email_inx(email) 
);

（2）创建完表后再单独一列创建索引

CREATE INDEX name_inx ON users(username);

（3）将表的一列更改为索引

ALTER TABLE users ADD INDEX age_index(age);

4、查看表的索引

方式一：SHOW INDEX FROM users;

方式二：

SHOW CREATE TABLE users \G;

SHOW CREATE TABLE users ；

二、索引类型的优缺点

1、普通索引

普通索引是数据库中最基本的索引结构，也被称为单列索引或简单索引。它只包含一个列的值和指向该行的指针，用于加速对该列的单列查询。可以对表的任意列创建普通索引，但通常建议对经常进行查询和排序的列创建索引，例如主键列和外键列等。

普通索引的原理是将所需要查询的列作为索引列，按照索引列的值建立索引。当查询该列时，数据库系统会先在索引结构中进行查找，然后根据索引中的指针到数据表中找到对应的行。由于普通索引只包含一个列的值和指向该行的指针，因此查询时需要在数据表中找到其他所需的列的值。

普通索引的优点包括：

（1）可以加速单列查询的速度，特别是对于大型数据表和频繁的查询操作。

（2）可以提高数据的访问效率，从而加快数据的处理速度。

普通索引的缺点包括：

（1）当需要查询的列不在索引列中时，需要进行额外的查找操作，从而降低查询效率。

（2）创建普通索引需要占用额外的磁盘空间和内存空间，可能会对写入操作的性能产生一定的影响。

普通索引的使用场景包括：

（1）经常进行查询和排序操作的列。

（2）需要经常进行连接操作的表的外键列。

（3）数据表中需要保证唯一性的列，如主键列等。

普通索引是数据库中最基本的索引结构，用于加速对单列查询的速度，提高数据的访问效率。在创建普通索引时需要根据具体的业务需求进行选择，避免对写入操作的性能产生过大的影响。

示例：创建普通索引

在book表中的year_publication字段上建立普通索引，SQL语句如下：
（1）在创建表中创建索引

（2）查看索引信息

方式一 ：SHOW INDEX FROM users;

方式二：

（3）删除索引

方式一：ALTE