数据库基础-索引

1.索引概念

索引是对数据库表中一列或多列的值进行排序的一种数据结构，使用索引可快速访问数据库表中的特定信息。简而言之，数据库索引是排好序的数据结构，其一个主要目的就是加快检索表中数据，即能协助信息搜索者尽快找到符合限制条件的记录ID的辅助数据结构。

2. 磁盘

• 柱面（磁道）：多盘磁盘，每盘都是双面，大小完全相等。那么同半径的磁道，整体上便构成了一个柱面。

只要知道磁头（Heads）、柱面（Cylinder）、扇区（Sector）对应的编号。就可定位要访问的位置，这种磁盘数据定位的方式叫做CHS。但是我们软件上定位并不是这样定位的，而是LBA一种线性地址，系统会将这个地址转化成CHS。扇区的基本单位大部分是4KB。

3. MySQL与磁盘交互基本单位

MySQL 作为一款应用软件，可以想象成一种特殊的文件系统。它有着更高的IO场景，所以，为了提高基本的IO效率， MySQL 进行IO的基本单位是 16KB。

也就是说，磁盘这个硬件设备的基本单位是 512 字节，而 MySQL InnoDB引擎 使用 16KB 进行IO交互。即， MySQL 和磁盘进行数据交互的基本单位是 16KB 。这个基本数据单元，在 MySQL 这里叫做page（注意和系统的page区分）。

4.MySQL理解

• MySQL中的数据文件，是以page为单位保存在磁盘当中的。
• MySQL的CURD操作，都需要通过计算，找到对应的插入位置，或者找到对应要修改或者查询的数据。
• 而只要设计计算，就需要CPU参与，而为了便于CPU参与，一定要能够先将数据移动到内存当中。
• 所以在特定时间内，数据一定是磁盘中有，内存中也有。后续操作完内存数据之后，以特定的刷新策略，刷新到磁盘。而这时，就涉及到磁盘和内存的交互，也就是IO。此时IO的基本单位是Page。
• 为了更好的进行上面的操作，MySQL服务器在内存中运行的时候，在服务器内部，就申请了Buffer Pool的大内存空间，来进行各种缓存。其实就是很大的内存空间，来和磁盘进行交互。
• 为了更高的效率，硬顶要尽可能的减少系统和磁盘IO的次数。

5.索引进一理解

5.1 建立测试表

mysql> create table if not exists user (
    -> id int primary key,    - -要添加主键，只有这样才会默认生成主键索引
    -> age int not null,
    -> name varchar(16) not null
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> show create table user\G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `id` int NOT NULL,
  `age` int NOT NULL,
  `name` varchar(16) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk  -默认就是InnoDB存储引擎
1 row in set (0.00 sec)

5.2 插入多条记录

• 我们并没有按id的顺序去查，但是查询出来的结果给我们排好序了。

mysql> select * from user;
+----+-----+--------+
| id | age | name   |
+----+-----+--------+
|  3 |  18 | 杨过   |
+----+-----+--------+
1 row in set (0.00 sec)

mysql> select d.dname,e.* from emp e left join dept d on e.^C
mysql> insert into user (id, age,name) values(4, 16, '小龙女');
Query OK, 1 row affected (0.01 sec)

mysql> insert into user (id, age,name) values(2, 26, '黄蓉');
Query OK, 1 row affected (0.02 sec)

mysql> insert into user (id, age,name) values(5, 36, '郭靖');
Query OK, 1 row affected (0.00 sec)

mysql> select * from user;
+----+-----+-----------+
| id | age | name      |
+----+-----+-----------+
|  2 |  26 | 黄蓉      |
|  3 |  18 | 杨过      |
|  4 |  16 | 小龙女    |
|  5 |  36 | 郭靖      |
+----+-----+-----------+
4 rows in set (0.00 sec)

mysql> insert into user (id, age,name) values(1, 56, '欧阳锋');
Query OK, 1 row affected (0.01 sec)

mysql> select * from user;
+----+-----+-----------+
| id | age | name      |
+----+-----+-----------+
|  1 |  56 | 欧阳锋    |
|  2 |  26 | 黄蓉      |
|  3 |  18 | 杨过      |
|  4 |  16 | 小龙女    |
|  5 |  36 | 郭靖      |
+----+-----+-----------+
5 rows in set (0.00 sec)

5.3 理解Page

单个Page

MySQL 中要管理很多数据表文件，要想管理好这些文件，类似于PCB和页表。

• 不同的Page，在MySQL中，都是双链表。因为有主键，所以MySQL会默认按照主键给我们的数据进行排序。

而插入数据排序，为的就是优化查找效率，Page内部，存储数据的模块，实质上也是一个链表的结构，链表的特点是增删快，查找修改慢，所以优化查询的效率是必须的。
因为数据有序，在查找的时候都是从头往后查找，没有任何有一个查找是浪费的。

5.4 页目录

多个Page

• 通过上面的页模式内部发现，采用了链表结构，前一条数据指向后一条数据，本质上还是通过数据的逐条比较来取出特定的数据。
• 如果有1千万条数据，一定需要多个Page来保存1千万条数据，多个Page彼此使用双链表链接起来，而且每个Page内部的数据也是基于链表的。这样一个页表一个页表的搜索，效率太低了。

• 单页的情况
  
  有了页目录之后，要查找id=4的记录，只需要直接查找目录2，仅仅需要2次遍历。没有页目录，需要遍历4次。

注意：因此，MySQL键值会自动排序是为了使用‘目录’这个概念，加速查询的效率。

• 多页的情况
  
  单表如果数据不足，MySQL会在容量不足的时候，自动开辟新的Page来保存新数据，然后通过指针的方式，将所有Page组织起来。
  因此，我们仅仅需要遍历多个Page，在Page内部通过目录来快速定位数据。但是在Page之间也是需要遍历的，也会涉及大量的IO。因此为了解决这个问题，给Page也加上目录。

解决办法：

• 使用一个目录项来指向一页，而这个目录项存放的是将要指向页中存放的最小数据的键值。

• 和页目录不同的地方在于，这种目录的管理的级别是页，页内目录管理的级别是行。

• 其中，每个页目录项的构成是：键值+指针。（下图并未画全）
  
  **页目录解释：**存在一个目录页来管理页目录，目录页中存放的就是指向那一页中最小的数据。有数据，就可以通过比较，找到该访问的那个Page。进而找到该Page。其实目录页的本质也是页，普通页中存的数据是用户数据，而目录页中村的数据是普通页的地址。

• Page分为目录页和数据页。目录页只放各个下级Page的最小键值。

• 查找的时候，自顶向下找，只需要加载部分目录页到内存，即可完成算法的整个查找过程，大大减少了IO次数。

5.5 B+树和B树

B+树：

B树：

5.6 聚簇索引与非聚簇索引

• MyISAM存储引擎-主键索引
  MyISAM使用B+树作为索引结果，叶节点的data域存放的是数据记录的地址。下图为MyISAM表的主索引，Col1为主键。
  
  MyISAM最大特点就是索引Page和数据Page分离，就是叶子节点没有数据，只有对应数据的地址(非聚簇索引)

• InnoDB是将索引和数据放在一起的，叫做聚簇索引。

• MyISAM的普通（辅助）索引，对于MyISAM普通索引和主键索引没有区别，无非就是主键不可以重复但是非主键可以重复。
  下图是基于MyISAM的Col2建立的索引，和主键索引没有差别（索引和数据分离）。

• InnoDB除了主键索引，用户也可以建立辅助（普通）索引，以上表中的Col3建立对应的辅助索引如下：
  
  解释：InnoDB的非主键索引叶子节点并没有数据，而只有对应记录的key值。通过辅助索引，找到目标记录，需要两边索引：首先检索辅助索引获得主键，然后用主键到索引中检索获得记录。这种过程，叫做回表查询

5.7总结

如果我们在建表时没有主键，他仍然会有默认主键，并且仍然会以B+树的形式存在
。只不过我们搜索列的时候，搜索的并不是（默认主键的列），所以只能线性遍历，速度肯定会慢。
如果我们构建了主键，默认我们的表就配上了主键索引（以主键为key），如果后来给其他的列设置索引的话，MySQL会给我们重新构建B+树（这里就要区分ISAM和innoDB）。
换句话说，不管我们建立主键索引还是辅助索引，都会建立索引。也就是一张表可能会对应多张B+树。

6.索引操作

索引创建原则：

• 比较频繁作为查询条件的字段应该创建索引。
• 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件。
• 更新非常频繁的字段不适合作为创建索引。
• 不会出现在where子句中的字段不该创建索引。（不会充当筛选条件的）

6.0 查询索引

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

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

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

• show keys from 表名
• show index from 表名
• desc 表名

6.1 创建主键索引

• primary key 第一种

- 在创建表的时候，直接在字段名后指定 primary key，就相当于把id当作了主键索引
create table user1(id int primary key, name varchar(30));

• primary key 第二种

- 在创建表的最后，指定某列或某几列为主键索引
create table user2(id int, name varchar(30), primary key(id))

• 第三种方式，创建后，修改表

create table user3(id int, name varchar(30));
- 创建表以后再添加主键
alter table user3 add primary key(id);

注意：唯一索引的特点

• 一个表中，最多有一个主键索引，可以使用复合主键。
• 主键索引效率高（不可重复）。
• 创建主键索引的列，它的值不能为null，且不能重复。
• 主键索引的列的基本上都是int。

6.2 唯一键索引（unique）

• 第一种方式

- 在表定义时，在某列后直接指定unique唯一属性。
create table user4(id int primary key, name varchar(30) unique);

• 第二种方式

-- 创建表时，在表的后面指定某列或某几列为unique
create table user5(id int primary key, name varchar(30), unique(name));

• 第三种方式

- 创建完之后修改表
create table user6(id int primary key, name varchar(30)）；
alter table user6 add unique(name);

注意：唯一索引的特点

• 一个表中，可以有多个唯一索引。
• 查询效率高
• 如果在某一列建立唯一索引，必须保证这列不能有重复数据
• 如果一个索引上指定not null，等价于主键索引。

6.3 普通索引

• 第一种方式

create table user8(id int primary key,
name varchar(20),
email varchar(30),
index(name) -在表的定义最后，指定某列为索引
);

• 第二种方式

create table user9(id int primary key, name varchar(20), email varchar(30));
alter table user9 add index(name); -创建完表以后指定某列为普通索引

• 第三种

create table user10(id int primary key, name varchar(20), email varchar(30));
- 创建一个索引名为 idx_name 的索引
create index idx_name on user10(name);

注意：唯一索引的特点：

• 一个表中可以有多个普通索引，普通索引在实际开发中用的比较多。
• 如果某列需要创建索引，但是该列有重复的值，那么就应该使用普通索引。

6.4 删除索引

• 删除主键索引

alter table 表名 drop primary key;

• 其他类型索引删除

alter table 表名 drop index 索引名;
索引名就是show keys from 表名中的 Key_name 字段

• 第三种方法

drop index 索引名 on 表名