MySQL索引特性

主键索引 (primary key)

唯一索引 (unique)

普通索引 (index)

全文索引 (fulltext)-- 解决中子文索引问题。

1. 认识磁盘

在看看磁盘中一个盘片

数据库文件，本质其实就是保存在磁盘的盘片当中。也就是上面的一个个小格子中，就是我们经常所说的扇区。当然，数据库文件很大，也很多，一定需要占据多个扇区。

# 数据库文件，本质其实就是保存在磁盘的盘片当中，就是一个一个的文件

[root@VM-0-3-centos ~] # ls /var/lib/mysql -l # 我们目前 MySQL 中的文件

total 319592

drwxr-x--- 2 mysql mysql 4096 Apr 15 21 :46 57test

-rw-r----- 1 mysql mysql 56 Apr 12 15 :27 auto.cnf

drwxr-x--- 2 mysql mysql 4096 May 17 13 :52 bit_index

-rw------- 1 mysql mysql 1676 Apr 12 15 :27 ca-key.pem

-rw-r--r-- 1 mysql mysql 1112 Apr 12 15 :27 ca.pem

drwx------ 2 mysql mysql 4096 Apr 13 21 :26 ccdata_pro

-rw-r--r-- 1 mysql mysql 1112 Apr 12 15 :27 client-cert.pem

故，系统读取磁盘，是以块为单位的，基本单位是 4KB 。

随机访问：本次 IO 所给出的扇区地址和上次 IO 给出扇区地址不连续，这样的话磁头在两次 IO 操作之间需要作比较大的移动动作才能重新开始读/ 写数据。

连续访问：如果当次 IO 给出的扇区地址与上次 IO 结束的扇区地址是连续的，那磁头就能很快的开始这次IO操作，这样的多个 IO 操作称为连续访问。

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

而 MySQL 作为一款应用软件，可以想象成一种特殊的文件系统。它有着更高的IO场景，所以，为了提高基本的IO效率， MySQL 进行IO的基本单位是 16KB (后面统一使用 InnoDB 存储引擎讲解)

1.MySQL 中的数据文件，是以 page 为单位保存在磁盘当中的。

2.MySQL 的 CURD 操作，都需要通过 计算 ，找到对应的插入位置，或者找到对应要修改或者查询的数据。

3.而只要涉及计算，就需要 CPU 参与，而为了便于 CPU 参与，一定要能够先将数据移动到内存当中。

4.所以在特定时间内，数据一定是磁盘中有，内存中也有。后续操作完内存数据之后，以特定的刷新策略，刷新到磁盘。而这时，就涉及到磁盘和内存的数据交互，也就是IO 了。而此时 IO 的基本单位就是Page 。

5.为了更好的进行上面的操作， MySQL 服务器在内存中运行的时候，在服务器内部，就申请了被称为 Buffer Pool 的的大内存空间，来进行各种缓存。其实就是很大的内存空间，来和磁盘数据进行IO 交互。

6.为何更高的效率，一定要尽可能的减少系统和磁盘 IO 的次数

?3.索引的理解

create table if not exists user (

????????id int primary key, -- 一定要添加主键哦，只有这样才会默认生成主键索引

????????age int not null ,

????????name varchar ( 16 ) not null

);

mysql> show create table user \G

*************************** 1 . row ***************************

????????????????Table : user

Create Table : CREATE TABLE `user` (

????????`id` int ( 11 ) NOT NULL ,

????????`age` int ( 11 ) NOT NULL ,

????????`name` varchar ( 16 ) NOT NULL ,

????????PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 -- 默认就是 InnoDB 存储引擎

1 row in set ( 0.00 sec

插入多条记录

-- 插入多条记录，注意，我们并没有按照主键的大小顺序插入哦

mysql> insert into user (id, age, name) values ( 3 , 18 , ' 杨过 ' );

Query OK, 1 row affected ( 0.01 sec)

mysql> insert into user (id, age, name) values ( 4 , 16 , ' 小龙女 ' );

Query OK, 1 row affected ( 0.00 sec)

mysql> insert into user (id, age, name) values ( 2 , 26 , ' 黄蓉 ' );

Query OK, 1 row affected ( 0.01 sec)

mysql> insert into user (id, age, name) values ( 5 , 36 , ' 郭靖 ' );

Query OK, 1 row affected ( 0.00 sec)

mysql> insert into user (id, age, name) values ( 1 , 56 , ' 欧阳锋 ' );

Query OK, 1 row affected ( 0.00 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)

为何IO交互要是 Page

为何 MySQL 和磁盘进行 IO 交互的时候，要采用 Page 的方案进行交互呢 ? 用多少，加载多少不香吗 ?

如上面的 5 条记录，如果 MySQL 要查找 id=2 的记录，第一次加载 id=1 ，第二次加载 id=2 ，一次一条记录，那么就需要2 次 IO 。如果要找 id=5 ，那么就需要 5 次 IO 。

但，如果这 5 条 ( 或者更多 ) 都被保存在一个 Page 中 (16KB ，能保存很多记录 ), 那么第一次 IO 查找 id=2 的时候，整个Page 会被加载到 MySQL 的 Buffer Pool 中，这里完成了一次 IO 。但是往后如果在查找 id=1,3,4,5等，完全不需要进行IO 了，而是直接在内存中进行了。所以，就在单 Page 里面，大大减少了 IO 的次数。

你怎么保证，用户一定下次找的数据，就在这个 Page 里面？我们不能严格保证，但是有很大概率，因为有局部性原理。

往往 IO 效率低下的最主要矛盾不是 IO 单次数据量的大小，而是 IO 的次数

不同的 Page ，在 MySQL 中，都是 16KB ，使用 prev 和 next 构成双向链表

因为有主键的问题， MySQL 会默认按照主键给我们的数据进行排序，从上面的 Page 内数据记录可以看出，数据是有序且彼此关联的

理解多个Page

通过上面的分析，我们知道，上面页模式中，只有一个功能，就是 在查询某条数据的时候直接将一 整页的数据加载到内存中，以减少硬盘 IO 次数，从而提高性能。 但是，我们也可以看到，现在的页模式内部，实际上是采用了链表的结构，前一条数据指向后一条数据，本质上还是通过数据的逐条比较来取出特定的数据。

如果有 1 千万条数据，一定需要多个 Page 来保存 1 千万条数据，多个 Page 彼此使用双链表链接起来，而且每个Page 内部的数据也是基于链表的。那么，查找特定一条记录，也一定是线性查找。这效率也太低了。

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

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

其中，每个目录项的构成是：键值 + 指针。图中没有画全。

?可是，我们每次检索数据的时候，该从哪里开始呢？虽然顶层的目录页少了，但是还要遍历啊？不用担心，可以在加目录页

?这货就是传说中的B+树啊！没错，至此，我们已经给我们的表user构建完了主键索引。

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

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

链表？线性遍历

二叉搜索树？退化问题，可能退化成为线性结构

AVL && 红黑树？虽然是平衡或者近似平衡，但是毕竟是二叉结构，相比较多阶 B+ ，意味着树整体过高，大家都是自顶向下找，层高越低，意味着系统与硬盘更少的IO Page 交互。虽然你很秀，但是有更秀的。

Hash ？官方的索引实现方式中， MySQL 是支持 HASH 的，不过 InnoDB 和 MyISAM 并不支持 .Hash 跟进其算法特征，决定了虽然有时候也很快(O(1)) ，不过，在面对范围查找就明显不行，另外还有其他差别，有兴趣可以查一下。

B树？最值得比较的是 InnoDB 为何不用B树作为底层索引？

B+树

目前这两棵树，对我们最有意义的区别是：

B 树节点，既有数据，又有 Page 指针，而 B+ ，只有叶子节点有数据，其他目录页，只有键值和Page指针

B+ 叶子节点，全部相连，而 B 没有

?

-- 终端 A

mysql> create database myisam_test; -- 创建数据库

Query OK, 1 row affected ( 0.00 sec)

mysql> use myisam_test;

Database changed

mysql> create table mtest(

????????-> id int primary key,

????????-> name varchar ( 11 ) not null

????????-> )engine=MyISAM; -- 使用 engine=MyISAM

Query OK, 0 rows affected ( 0.01 sec)

-- 终端 B

[root@VM- 0 - 3 -centos mysql]# ls myisam_test/ -al --mysql 数据目录下

total 28

drwxr-x --- 2 mysql mysql 4096 Jun 13 13:33 .

drwxr-x --x 13 mysql mysql 4096 Jun 13 13:32 ..

-rw-r ----- 1 mysql mysql 61 Jun 13 13:32 db.opt

-rw-r ----- 1 mysql mysql 8586 Jun 13 13:33 mtest.frm -- 表结构数据

-rw-r ----- 1 mysql mysql 0 Jun 13 13:33 mtest.MYD -- 该表对应的数据，当前没有数

据，所以是 0

-rw-r ----- 1 mysql mysql 1024 Jun 13 13:33 mtest.MYI -- 该表对应的主键索引数据

其中， MyISAM 这种用户数据与索引数据分离的索引方案，叫做非聚簇索引。

-- 终端 A

mysql> create database innodb_test; -- 创建数据库

Query OK, 1 row affected ( 0.00 sec)

mysql> use innodb_test;

Database changed

mysql> create table itest(

????????-> id int primary key,

????????-> name varchar ( 11 ) not null

????????-> )engine=InnoDB; -- 使用 engine=InnoDB

Query OK, 0 rows affected ( 0.02 sec)

-- 终端 B

[root@VM- 0 - 3 -centos mysql]# ls innodb_test/ -al

total 120

drwxr-x --- 2 mysql mysql 4096 Jun 13 13:39 .

drwxr-x --x 14 mysql mysql 4096 Jun 13 13:38 ..

-rw-r ----- 1 mysql mysql 61 Jun 13 13:38 db.opt

-rw-r ----- 1 mysql mysql 8586 Jun 13 13:39 itest.frm -- 表结构数据

-rw-r ----- 1 mysql mysql 98304 Jun 13 13:39 itest.ibd -- 该表对应的主键索引和用户

数据，虽然现在一行数据没有，但是该表并不为 0 ，因为有主键索引数据

?4.索引操作

4.1创建主键索引

-- 在创建表的时候，直接在字段名后指定 primary key

create table user1(id int primary key, name varchar ( 30 ));

第二种方式：

-- 在创建表的最后，指定某列或某几列为主键索引

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

4.2 唯一索引的创建

?-- 在表定义时，在某列后直接指定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 ，等价于主键索引

4.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);

一个表中可以有多个普通索引，普通索引在实际开发中用的比较多

如果某列需要创建索引，但是该列有重复的值，那么我们就应该使用普通索引

4.4全文索引的创建

?CREATE TABLE articles (

????????id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,

????????title VARCHAR ( 200 ),

????????body TEXT ,

????????FULLTEXT (title,body)

)engine=MyISAM;

INSERT INTO articles (title,body) VALUES

????????( 'MySQL Tutorial' , 'DBMS stands for DataBase ...' ),

????????( 'How To Use MySQL Well' , 'After you went through a ...' ),

????????( 'Optimizing MySQL' , 'In this tutorial we will show ...' ),

????????( '1001 MySQL Tricks' , '1. Never run mysqld as root. 2. ...' ),

????????( 'MySQL vs. YourSQL' , 'In the following database comparison ...' ),

????????( 'MySQL Security' , 'When configured properly, MySQL ...' );

?可以用explain工具看一下，是否使用到索引

?如何使用全文索引呢？?

mysql> SELECT * FROM articles

????????-> WHERE MATCH (title,body) AGAINST ('database');

+----+-------------------+------------------------------------------+

|? id | title? ? ? ? ? ? ? ? ? | body |

+----+-------------------+------------------------------------------+

|?? 5 | MySQL vs. YourSQL | In the following database comparison ... |

|?? 1 | MySQL Tutorial | DBMS stands for DataBase ... |

+----+-------------------+------------------------------------------+

通过explain来分析这个sql语句

4.5查询索引

4.6删除索引

mysql> alter table user10 drop index idx_name ;

mysql> drop index name on user8

比较频繁作为查询条件的字段应该创建索引

唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件

更新非常频繁的字段不适合作创建索引

不会出现在 where 子句中的字段不该创建索引