索引的概念

1. 数据库表中存储的数据都是以记录为单位的，如果在查询数据时直接一条条遍历表中的数据记录，那么查询的时间复杂度将会是O(n)
2. 索引的价值在于提高海量数据的检索速度，只要执行了正确的创建索引的操作，查询速度就可能提高成百上千倍。当一张表创建索引后，在数据库底层就会为表中的数据记录构建特定的数据结构，后续在查询表中数据时就能通过查询该数据结构快速定位到目标数据
3. 索引虽然提高了数据的查询速度，但在一定程度上也会降低数据增删改的效率，因为这时在对表中的数据进行增删改操作时，除了需要进行对应的增删改操作之外，可能还需要对底层建立的数据结构进行调整维护

常见的索引分为：

• 主键索引（primary key）
• 唯一索引（unique）
• 普通索引（index）
• 全文索引（fulltext）

MySQL与磁盘交互的基本单位

MySQL可以被认为是一种特殊的文件系统，它有着更高频的IO场景，因此为了提高基本的IO效率，MySQL与磁盘交互的基本单位是16KB，这个基本数据单元在MySQL这里也叫做Page。

通过show命令查看系统中的全局变量，可以看到InnoDB存储引擎交互的基本单位是16KB。如下：

mysql> show global status like 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_page_size | 16384 |
+------------------+-------+

MySQL与磁盘进行交互时以Page为基本单位，可以减少与磁盘IO交互的次数，进而提高IO的效率。

索引的理解

主键索引示例

mysql> create table if not exists person (
    -> id int primary key,
    -> name varchar(10) not null
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc person;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(10) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into person values (10, 'zhangsan'), (3, 'lisi'), (1, 'wangwu'), (5, 'zhaoliu'), (2, 'qianqi');
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from person;
+----+----------+
| id | name     |
+----+----------+
|  1 | wangwu   |
|  2 | qianqi   |
|  3 | lisi     |
|  5 | zhaoliu  |
| 10 | zhangsan |
+----+----------+
5 rows in set (0.00 sec)

现象：

我们在插入数据时是乱序的， 但是在显示的时候却是有序的。

根本原因就是，因为我们创建表时设置了主键，即便向表中插入数据时是乱序插入的，MySQL底层也会自动按照主键对插入的数据进行排序。

主键索引的构建

单个Page

• MySQL中要管理很多数据文件，在运行期间一定有大量的Page需要被换入换出，因此MySQL一定需要将内存中大量的Page管理起来。
• MySQL将内存中的每一个Page都用一个结构体描述起来，然后再将各个结构体以双链表的形式组织起来，因此一个Page结构体内部既包含数据字段，也包含属性字段。
• 此外，为了方便后续数据的插入和删除，每个Page结构体内部存储的数据记录会以单链表的形式组织起来，并且各个记录之间会按照主键进行排序。

单个Page内创建页内目录

• Page结构体内部存储的数据记录是以单链表的形式组织起来的，当页内部的数据量增多时，本质在页内部进行的还是线性遍历，效率低下。
• 这时可以在Page结构体内部引入页内目录，将Page结构体内部存储的数据记录按照主键划分为若干区域，页内目录中就存储着这若干区域的最小键值。
• 在Page结构体内部引入页内目录后，在页内部查询数据时就可以先通过页内目录找到目标数据所在区域的起始记录，然后再从该记录开始向后遍历找到目标记录。

像这样，Page之上创建页目录，之后形成这样的索引结构：

总结：

• 最终构建出来的实际就是一棵B+树，这棵B+树就是InnoDB的索引结构，其中每一层Page的作用就是加速它的下一层的查找效率。
• 如果我们创建表时设置了主键，那么MySQL在底层就会自动将这张表中的的数据以B+树的形式组织起来，保存在Buffer Pool当中，当我们查询数据时就可以通过查询这棵B+树来提高查询效率。
• MySQL中可能同时有大量的表正在被处理，因此Buffer Pool中可能会存在多个索引结构，也就是同时存在多个B+树结构，当我们查询表时访问的就是这张表对应的B+树结构。

索引的操作

创建主键索引

1. 方式1：

-- 建表的语句中指定primary key，直接创建主键索引
mysql> create table if not exists person (
    -> id int primary key,
    -> name varchar(10) not null
    -> );

2. 方式2：

-- 建表的最后指定某列或者某几列为主键索引
mysql> create table if not exists person (
    -> id int,
    -> name varchar(10) not null
    -> primary key (id)
    -> );

3. 方式3：

mysql> create table if not exists person (
    -> id int,
    -> name varchar(10) not null
    -> );
mysql> alter table person add primary key (id);

主键索引的特点如下：

• 一个表中，最多只能有一个主键索引，一个主键可以由多个列同时承担。
• 主键索引的查询效率高。
• 创建主键索引的列，其列值不能为NULL，且不能重复。
• 主键索引的列一般是数字类型。

创建普通索引

1. 方式1：

mysql> create table if not exists person ( id int primary key, name varchar(10), index(name) );
Query OK, 0 rows affected (0.02 sec)

2. 方式2：

mysql> create table if not exists person ( id int primary key, name varchar(10) );
Query OK, 0 rows affected (0.02 sec)

mysql> alter table person add index(name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc person;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(10) | YES  | MUL | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

普通索引的特点如下：

• 一个表中，可以有多个普通索引，一个普通索引可以由多个列同时承担。
• 创建普通索引的列，其列值可以为NULL，也可以重复。

创建唯一索引

方法同上：

mysql> create table if not exists person (
    -> id int primary key,
    -> name varchar(10) unique
    -> );

唯一索引的特点如下：

• 一个表中，可以有多个唯一索引，一个唯一键可以由多个列同时承担。
• 唯一索引的查询效率高。
• 创建唯一索引的列，其列值可以为NULL，但是不能重复。
• 如果给唯一索引设置NOT NULL属性，则等价于主键索引。

查询索引

语法：show index from 表名

mysql> show index from person\G;
*************************** 1. row ***************************
        Table: person
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 5
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
1 row in set (0.00 sec)

删除索引

删除主键索引

使用alter table 表名 drop primary keySQL即可删除主键索引

删除非主键索引

使用alter table 表名 drop index 索引名SQL即可删除指定的非主键索引