[MySQL]数据库的效率问题与索引的底层原理
目录
前言：数据库的效率问题
一、软硬件-如何提高与磁盘交互的效率
1.硬件-如何在硬件层面读取MySQL文件
2.软件-MySQL与操作系统的关系
3.如何提高与磁盘交互的效率
二、索引-如何提高查找数据的效率
1.单个Page的理解
Page结构
页目录
Page结构图
2.多页Page的理解
三、索引
1.理论阐述
默认索引
聚簇索引和非聚簇索引
2.索引的相关操作
主键索引与唯一索引的创建
普通索引的创建
查看索引
删除索引
3.索引创建的原则
4.全文索引的创建与使用
前言：数据库的效率问题
对于MySQL的所有操作其实都是在内存当中完成的，MySQL服务在运行的时候，会申请一块内存空间用来做数据库的CURD操作。然后会定期的将内存中操作完毕的数据更新到磁盘的数据库文件当中，完成数据持久化的操作。
数据库想要实现数据的CURD操作就必须完成以下两步骤，第一将数据从磁盘中读取出来，第二是在读取的数据中查找我们想要的内容。所以提高数据库效率也是从这两个方式入手。想要和磁盘进行交互的效率，最本质的就是减少I/O的次数，一定会涉及到操作系统与软硬件的协同完成。
一、软硬件-如何提高与磁盘交互的效率
1.硬件-如何在硬件层面读取MySQL文件
MySQL虽然给用户提高存储服务，但是存储的也是数据，都会存放在磁盘外设当中，那么对于和外设打交道的话，就会大大的降低效率，再加上I/O操作本身的特性，那么如何提高MySQL的效率是一个非常重要的内容。
在读取文件的时候，首先就是要找到文件在磁盘中所在的位置，从软件上来说的话，会经过操作系统的文件管理模块，通过inode找到文件数据在逻辑存储中的位置。也就相当于找到了LBA地址，在经过LBA线性地址转化为CHS物理地址，就能找到文件数据存档的位置了，进而就可以读取出来了。一般的磁盘每个扇区都是512Byte，那么显然上述的操作有些繁琐，如果读取的数据大于512的话要读取多次吗？那么这样的话效率会更加低下，所以操作系统在和磁盘进行I/O操作的时候并不是以512字节为单位，而是一般会以4KB为一个数据块，以数据块为单位进行存取的。
为什么是4KB呢，因为内存是被划分成了一个个4KB的内存块，为了让内存和磁盘进行更好的交互，所以才会以4KB做为数据块进行读取数据，还有一个原因就是基于局部性原理。但MySQL服务会有大量的I/O操作，所以为了进一步的提高效率，减少I/O交互的次数，所以MySQL进行I/O操作的基本单位是16KB。(不同搜索引擎的规定不同)
所以说在读取MySQL文件数据的时候，会根据CHS地址进行定位扇区，然后会以16KB数据块为单位进行读取数据。
mysql> show global status like 'innodb_page_size';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.09 sec)
2.软件-MySQL与操作系统的关系
MySQL是一个应用软件，而操作系统是软件的管理者，一定是不允许MySQL与磁盘进行直接交互的，而且MySQL也没有这个能力，所以说还是需要通过操作系统获取数据给到MySQL，那么操作系统与磁盘交互是以4KB为单位，而MySQL上述我们说是16KB为单位，那么是怎么回事呢？所以说这个16KB其实是MySQL对于操作系统提出的要求，操作系统会连续读取，直到读取16KB数据了之后在给MySQL。
3.如何提高与磁盘交互的效率
那么这样看的话，读取16KB的数据还需要访问磁盘4次，效率提高在哪里呢？提高了MySQL与操作系统交互次数的减少，在MySQL层面上，会一次向操作系统读取16KB，那么操作系统只有读取到16KB数据之后才会给MySQL，所以说减少了3次与操作系统之间的I/O操作。写入的时候也是一样的，MySQL内部也提供了一个buffer pool内存空间，用来缓存数据，之后会一次性的刷新给操作系统。
所以说如果谈论应用程序和磁盘的I/O次数的减少，其实底层上来说是减少了应用程序和操作系统之间的I/O交互次数，因为操作系统一次写入磁盘的大小是固定的，但是操作系统接收和传递给应用的数据大小是可以变化的。
还有就是我们一次交互一个16KB的page，根据局部性原理，可能在很多查找的形况下，数据已经在内存中了，就不需要再去磁盘中寻找了。
二、索引-如何提高查找数据的效率
数据库的查找操作的效率好坏，也就是算法效率的好坏，那么影响算法效率的因素无非就是两种，第一种是数据的组织方式，另一种是算法本身的好坏了。索引其实就是就是改变组织数据的方式，数据结构变了，那么对应的搜索方法也就变化了，进而提高了数据库的性能。
首先我们先来看一个现象，我们创建了一个数据库表，有id和name字段，并将id字段设置为主键属性，然后我们乱序插入几条数据，最后select显示出来发现顺序会按照id的大小进行显示，这是为什么呢？
mysql> create table info ( id int primary key, name varchar(20) );
Query OK, 0 rows affected (0.04 sec)
mysql> insert into info values(2, '李四'), (4, '赵六'), (3, '王五'), (1, '张三');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from info;
+----+--------+
| id | name |
+----+--------+
| 1 | 张三 |
| 2 | 李四 |
| 3 | 王五 |
| 4 | 赵六 |
+----+--------+
1.单个Page的理解
Page结构
mysql内部，一定会进行多次的I/O交互操作，那么也就决定了mysql内部会同时存在多个page数据块，那么就需要将这些数据块管理起来，还是那六个字，先描述再组织。所以page并不仅仅是一个内存块，而是一个带有数据和描述字段的的结构体。内部含有指向前一个和后一个page页的指针、数据以及页目录。
页目录
对于page内部的一条条数据是如何存储的呢？采用的是链表的形式，因为链表对于增删操作具有天然的优势，再对数据增删的时候，不需要移动和修改任何其他的数据内容，最有可能影响的也就是上一个数据块的指针指向的位置罢了，所以采用链表的形式。但是链表也有缺陷，对于查找操作和数组一样，都是一个O(N)的效率，修改也是一样，首先要查找到对应的数据位置。所以进行排序的话，会让查找的时候，每遍历的一个数据都是有用的，因为都在无线的接近我们要找的元素的值，所以提高了一点点效率。
但是上面的描述对于效率的提高微不足道了，如果数据量很大的话，或者查找的数据再链表的末尾的话，还不如不排序，碰碰运气说不定再查找前几个的时候就找到了呢？所以排序真正的奇高查找效率的原因，是要配合着页目录的。
页目录其实本质上就是跟我们平时读的书的目录是一样的，书中的内容是有序的，目录会记录一些特定内容的位置，从而让读者通过目录就可以快速定位到我们想要寻找的数据最近的位置，然后再在这个特定的区间，遍历我们要找的内容。这种方式会多废几张纸的空间，但是大大提高了查找的效率，也是典型的空间还时间的操作。
所以排序的真正原因是：方便引入页目录
Page结构图
那么如此的话，我们想要找id为6的数据，首先我们会去目录页中寻找目录指向的位置大于6的前一个目录，然后访问该目录指向的区域，也就是4的位置，然后从4开始遍历寻找id为6的数据。那么这样的话，访问第6个位置的数据就会大大减少的遍历数据的个数了。
2.多页Page的理解
如果说我们MySQL内部管理的数据非常大，一个page页肯定是放不下的，就需要多个Page页了，那么想要找到一个数据的位置就需要按个遍历目录页了，那么就相当于又开始遍历目录页的一个个目录了。那么效率又开始变得非常低，怎么办呢？对于遍历数据块的解决方案是引入目录页，那么对于遍历目录的解决方案难道不可以使用一个目录页吗。相当于一级标题和二级标题的作用。
对于多个Page的管理，MySQL内部使用的是空的Page页，也就是没有数据的Page充当目录的页目录，首先先遍历一级页目录找到数据所在的二级页目录，然后再二级页目录中找到数据对应的区域，最后再该区域中遍历寻找数据。
如果说目录Page内部的目录项页非常大了，那么还可以继续创建他们的目录页。这个结构其实就是我们所说的B+树结构，所以对于Page的组织MySQL采用的是B+树的数据结构。基于这这结构，我们查找数据的时候遍历的次数就会大大较少，效率也就提高了很多。
三、索引
1.理论阐述
索引分为主键索引、唯一索引、普通索引以及全文索引。上述我们形成的B+树结构去管理数据的操作，就是索引提高查找效率的底层实现原理。提高效率的不仅仅是减少遍历数据的个数，最重要的是，减少了加载到内存中的数据量。数据库表中的数据是上述的结构，那么在数据从磁盘加载到内存的时候，只需要加载目录页Page即可，根据目录页记录的内容，在将我们所需要的数据加载到内存当中。
默认索引
所以我们上面的代码为id设置主键之后，就是设置了主键索引，所以打印出来的数据就是有序的了。如果我们不设置任何索引的话，那么MySQL不会对表的数据做任何的管理操作吗，让这些数据都是乱的吗？一定是不会的。如果我们不设置主键的话，会自己默认生成一列作为主键，然后按照默认列的主键作为索引的排序字段。
如果说都有主键的话？为什么我们不设置的时候，默认设置的主键查找效率会这么慢，因为索引的创建是方便按照主键索引列查找的，默认生成的隐藏列，我们不直到是什么，所以说无法执行搜索的列名称，也就无法享受到索引带来的查找效率的提高。如果说我们设置了主键，但是查找的时候不按照主键列进行找到，而是指定where name = 'xx'字段的话，查找效率也是不高的。
聚簇索引和非聚簇索引
对于B+树内部的page存放数据是一种方案，而存放数据的指针也是一种方案，例如MyISAM存储引擎，他叶子节点的page内部存放的就是数据的指针，而并非数据本身，我们将这种用户数据与索引数据分离的索引方案叫做非聚簇索引，在一起的称为聚簇索引。Innodb的存储就是采用聚簇索引的方案。
索引就是一个B+树数据结构，对于索引不只有主键索引，还有其他索引，对于MyISAM存储引擎的非聚簇索引来说，创建任何索引都是一样的，当创建一个普通索引也是在形成一个B+树，叶子节点存放的是数据的指针。而对于聚簇索引的版本来说的话，那就不是这样的了，如果说每建立一个索引，都要在一个B+树上存放一遍数据，那么就太浪费时间了，所以聚簇索引版本在创建其他索引的时候，采用的方式是，叶子节点存放的是主键的值，通过其他索引获取到主键的值后，在去主键索引的B+树种寻找对应的数据。这种过程也叫回表查询。
如果说没有主键的话，怎么办呢？在 InnoDB 存储引擎中，如果表没有显式定义主键，InnoDB 会选择第一个唯一键作为聚簇索引。如果没有唯一键，InnoDB 会自动生成一个隐藏的 6 字节的行 ID 作为聚簇索引。
2.索引的相关操作
主键索引与唯一索引的创建
这两种索引的创建非常简单，就是为字段设置主键属性和唯一键属性的操作，设置完毕之后就自动默认是添加了主键索引和唯一索引了。
//方式一：建表的时候指定索引
create table user1 (id int primary key, name varchar(20) );
create table user2 (id int, name varchar(20), primary key(id) );
create table user1 (id int primary key, name varchar(20) unique);
create table user2 (id int primary key, name varchar(20), primary key(id), unique(name));
//方式二：建表之后再添加
create table user3 (id int, name varchar(20) );
alter table user3 add primary key(id);
create table user3 (id int primary key, name varchar(20) );
alter table user3 add unique(name);普通索引的创建
//方式一：在建表的时候指定索引
create table user1 (id int, name varchar(20), index(id) );
//方式二：在建表之后添加字段属性
create table user1 (id int, name varchar(20));
alter table user1 add index(id);
//方式三：建表之后创建一个名为xxxxx的索引
create table user1 (id int, name varchar(20));
create index xxxxx on user1(id);查看索引
语法：show keys from table_name；
show index from table_name;
desc table_name; //很简略
删除索引
语法：alter table table_name drop primary key; //删除主键
alter table table_name drop index index_name;
drop index index_name on table_name;
3.索引创建的原则
- 索引最主要的作用就是加快查找效率，所以对于频繁查询的列一定要建立索引，但是前提是该列的唯一性不能太差，如果太差的话就不适合了。
- 还有就是更新频繁的字段不适合做索引，因为如果更新的话，更新的是非索引列的数据还好说，更新的是索引列的数据，该数据在B+树的位置就需要改变了，那么就需要改变B+树的多个地方。
- 基本上不在where子句中出现的列一般不会创建索引，因为一个列如果不是用于筛选数据行的关键因素的话，那么就说明该列对于查询指定数据并不能起到太大的作用，所以为他建立索引的话，一般也不会有多大的效率提升。而且B+树也要占用空间的，不提高效率还占用空间，就得不偿失了。
4.全文索引的创建与使用
我们上述创建索引的列都是一些整数或者说一些非常短的字符串作为索引的列，而且是为了去找到指定的数据时候使用的，是对一条条数据为单位而言的。那么如果是大文本的数据，我们想找到一些数据在哪个大文本中存在怎么办呢？也就是说想找一个列内部有没有哪些数据。
MySQL为我们提供了全文索引的机制，方便我们去以大文本的列作为索引进行查询。但是也有一些要求，表的存储引擎必须是MyISAM，而且默认的全文索引只会支持英文。如果想支持中文进行全文索引的话，可以使用sphinx的中文版(coreseek)。
语法：FULLTEXT(column [,column, ...])
mysql> create table test1 (
-> id int unsigned primary key auto_increment,
-> title varchar(30) not null,
-> body text,
-> fulltext(title, body)
-> )engine=MyISAM;
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO test1 (title, body)
-> VALUES
-> ('Title 1', 'This is the body text for title 1.'),
-> ('Title 2', 'The body content of title 2 is here.'),
-> ('Title 3', 'Some text for title 3 in the body.'),
-> ('Title 4', 'Body text related to title 4.'),
-> ('Title 5', 'This is the body for the fifth title.'),
-> ('Title 6', 'The sixth title has this body text.'),
-> ('Title 7', 'Body content for title 7.'),
-> ('Title 8', 'Some words in the body for title 8.'),
-> ('Title 9', 'The body of title 9 is here.'),
-> ('Title 10', 'This is the final body text for title 10.');
Query OK, 10 rows affected (0.03 sec)
Records: 10 Duplicates: 0 Warnings: 0
对于全文索引，默认是不会使用的，只有我们明确的指明要使用的时候，才会使用全文索引。
关键字：explain //可以查看SQL语句查询大的时候是否使用了索引
语法：SELECT column FROMtable_name WHERE MATCH (xxxx, xxxx) AGAINST ('yyyy');
match函数的作用是指定要搜索的列，它接受一个或多个列名作为参数，这些列必须是全文索引的一部分。只有对已经创建了全文索引的列使用MATCH()函数，才能进行全文搜索。
AGAINST()函数用于指定搜索的关键词。它接受一个字符串作为参数，这个字符串就是要搜索的关键词。MySQL 会根据这个关键词在MATCH()指定的列中进行全文搜索
mysql> explain select * from test1 where body like 'content'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 10
filtered: 11.11
Extra: Using where
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from test1 where match(title, body) against ('content')\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test1
partitions: NULL
type: fulltext
possible_keys: title
key: title
key_len: 0
ref: const
rows: 1
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)