MYSQL数据库优化原理解析

MYSQL数据库优化

• 1.InnoDB简介
  本篇博客我们使用的是InnoDB作为存储引擎，它是事务型数据库的首选引擎，支持ACID事务，支持行级锁定，InnoDB存储引擎在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB存储它的表&索引在一个表空间中，表空间可以包含数个文件（或原始磁盘分区）。

• 引入思考
  通常我们新建的表结构中，指定了一个主键作为索引，在插入数据之后，通过select * from user;查询出的记录，主键是有序的，这在插入的过程中发生了什么事情？没错就是对插入的数据按照主键索引进行了排序，为什么要排序，插入的效率不就变低了吗？下面我们来探讨它的执行过程。

  知识延伸：在操作系统中，以页为单位分配内存空间，通常1页=4kb(内存)，操作系统在磁盘中取数据的时候按照页来取，InnoDB也是按页分配，此页的大小是内存分配单位的整数倍。（一页16kb=4个内存页）
  在navicat中查询即可：通过SHOW GLOBAL STATUS LIKE 'InnoDB_page_size'查询出的页的大小为（1kb=1024bit）：
  

• 2.InnoDB页结构
  
  模拟插入时的过程：在刚插入数据时用户数据区为空，操作系统会首先分配给你16kb的存储空间（按页分配），插入的数据为：4，1，1 ,‘a’；1，1，1,‘b’； 8，8，8，‘h’;
  （1）首先插入4，1，1，‘a’记录。
  （2）插入1，1，1,‘b’；记录，比较主键索引，修改指针，插入到（1）的前面。
  （3）插入记录8，8，8，‘h’，比较主键，修改指针，插入到4，1，1 ,'a’的后面。
  
  在客户端查询select * from user where id = 3, 首先将查询的字符串发送给mysql的服务端进行解析，然后将上面的这一页从磁盘取到内存（不考虑索引问题），然后查找，id=3的记录，逐行比较，比较到第二行的时候就结束，因为主键是按照递增排序的，但是如果数据量比较大，比如有10万条数据，如果要查询的id=99999,那么它的查询速度和乱序的还是基本一样的（链表都要从头开始查）。这要怎么解决嘞？学计算机的人不能轻易认输
  
  这就需要使用页目录了。存在即合理。。。。。
  众所周知，一本书有目录，每一条目录的下标都是每一个章节中最小的下标且下标按照递增方式排列。这样我们在查找某一页的时候，是不是先看书目录（缩小查找范围），然后在查找，这样是不是比刚才的那种方式快很多。
  假如：对递增的用户数据区域进行分组，每两条数据为一组，可以分为三组，那么页目录的每一个目录记录的是每一组最小的id号，那么我们在来看看查询id=3时是怎么操作的？首先通过查询页目录，查询到在第一组中，在第一组的两条数据中进行顺序查找，这样是不是快很多了。

假如每一页的用户数据区只能存储6条数据（每一页要分配!多少内存空间，除去页头，页目录，当然还有其他一些占用的空间，然后就是用户数据区的空间，存储的数据条数=用户数据空间/每一条所占空间大小），当大于6条数据时，系统又开辟新的一页，每一页直接用指针连接，在增加了两条数据,如下所示(先不考虑指向前面的指针，循序渐进的解释才有乐趣):

现在考虑一个问题：如果我们要查找id=16的记录，如果是这样的结构，那么是不是还是要从第一页的页目录开始查找（根据表名找到对应的存储文件先把第一页从磁盘加载到内存），发现不在第一页，在根据页之间的指针查找第二页…依次递推（假设开辟了很多页）。如果用户数据区数据量太多，那么它的页目录是不是也要增加许多，查找效率比原来的逐条查找的效率达不到最大值。因此，需要进一步改造。

解决办法：新开辟一页用于存储每一页最小的id号，当进行查询的时候，首先从磁盘中把目录页加载到内存（是不是有点像操作系统中分页那味了），然后通过你要查询id=16的记录，根据目录页查找到那一页数据页，将找到的数据页加载到内存，查找即可，是不是很快！！！！！

随着数据页的增多，目录页也随着增多，如上假设每一页只能存储6条数据，那么当目录页的记录大于6时，便会继续开辟出目录页，目录页增多，又继续向上增加目录页，记录下一层的每一页目录页的最小id号，依次递推，…在开始查找数据的时候，都是首先将最上层的目录页加载到内存，然后在进行查找数据，由上面的结构可知，索引和数据都在一起（聚集索引||聚簇索引），这就形成了数据结构中所谓的B+树。
考虑如下场景：
(1) 对于要查找id>9的记录，应该怎么查找？
首先查找id=9的记录，然后将id=9的右边的数据全部返回即可。
（2）查找id<9的记录？？
首先查找id=9的记录，然后将id=9的左边的数据全部返回即可（这就是使用双向链表的好处）。
B+树的特性：
一棵m阶的B+树需满足如下的特性：
（1） 每个分支节点最多有m棵子树
（2）非页根节点至少有两颗子树，其他每个分支节点至少有m/2向上取整棵子树。
（3）节点的字数个数和关键字个数相等
（4）所有叶节点包含全部关键字及指向相应记录的指针，而叶节点中将关键字按大小顺序排列，且相邻页节点按大小顺序相互链接起来。
（5）所有分支节点（索引的索引）仅仅包含它的各个子节点（下一级索引块）中关键字的最大值及其指向子节点的指针。

说明:叶节点 等价于 数据页； 分支节点 等价于 目录页； 根节点 等价于 最上层的目录页；在innodb中主键索引是必须要存在的，有时候也存在辅助索引，数据页与数据页之间是双向链表，页内数据为单向链表。
联合索引：
主键索引+自定义索引
创建一个表user,主键索引为id,给表user自定义一个索引，索引为abc，create index inx_user_abc on user(a,b,c)，user表如下：

在插入数据的时候，已经对插入的数据按照主键索引进行排序形成了一棵B+树，在使用abc作为索引创建索引表的时候，常规来说是不是还需要把数据页的数据复制一份按照abc作为索引排序形成一棵B+树，如果是这样空间消耗特别大，那么，在按照abc作为索引形成B+树的时候，数据项只存储作为索引的abc的值和相应的主键索引，其他的数据不存储。查询select * from user where a =1 and b=1 and c=1 ,查询原理：根据索引abc=111查找B+树，找到对应的主键索引，然后在按照主键索引查询按照id(主键索引)形成的B+树《这个过程叫做回表》，找到数据项返回即可。